MySql基础命令总结（DDL篇）

对于Mysql数据库还不太了解的小伙伴们请查看我的Mysql数据库安装详细教程：juejin.cn/post/684490…

今天我们来聊聊常见的Mysql命令：

1、常见数据库操作命令

• 1.1 解决java添加数据到数据库出现乱码问题：

  url：jdbc:mysql://localhost:3306/数据库名？useUnicode=true&characterEncoding=UTF-8;

• 1.2 解决命令框查询数据库出现乱码情况

  mysql -default-character-set=gbk -u 用户名 -p 密码

  或者: mysql -uroot -p密码 SET NAMES GBK;

• 1.3 进入数据库

  mysql -u root -p 数据库密码

• 1.4 查询数据库

  mysql >show databases;

• 1.5 建立自己的数据库

  mysql >create database 数据库名称;

• 1.6 选择数据库

  mysql >use 数据库名称;

• 1.7 查询当前数据库

  mysql >select database();

• 1.8 查询系统内容

  SELECT USER() 得到当前用户

  SELECT VERSION() 得到当前mysql版本号

  SELECT NOW() 得到当前时间

  SELECT DATABASE() 得到当前数据库

• 1.9 查询所有的表

  mysql >show tables;

• 1.10 退出Mysql

  mysql >quit;

• 1.11查看警告信息：

  SHOW WARNINGS;

• 1.12查看数据库的详细信息：

  SHOR CREATE DATABASE db_name;

2、数据库DDL语句

• 2.1创建数据库

-- 创建数据库
CREATE DATABASE IF NOT EXISTS ceshi DEFAULT CHARACTER SET = utf8;

• 2.2使用数据库

use ceshi

• 2.3创建数据库表

-- 创建商品表
CREATE TABLE IF NOT EXISTS goods_info(
	goods_id INT COMMENT'商品编号',
	goods_name VARCHAR(50) NOT NULL COMMENT'商品名称',
	manufactory VARCHAR(200) COMMENT'生产厂家',
	stock INT COMMENT'库存',
	PRIMARY KEY(goods_id),
	UNIQUE(goods_name)
)ENGINE = INNODB,CHARSET = utf8;

• 2.4添加商品信息

-- 添加商品信息
INSERT INTO goods_info VALUES (1,'洗衣粉','上海貂场',1000);

• 2.5查询商品信息

-- 查询商品表
SELECT * FROM goods_info;

• 2.6查看表的列定义

-- 查看表的列定义
DESC student;

• 2.7 修改表的列的类型

--1- 修改表的列的类型（注意：仅仅能修改空列的数据类型）
 ALTER TABLE tb_name MODIFY 字段类型 [字段属性] [FIRST|AFTER 字段名称];

• 2.8 修改列的名称、类型

ALTER TABLE tb_name CHANGE 原字段名称 新字段名称 字段类型 [字段属性] [FIRST|AFTER 字段名称];

• 2.9 添加列

ALTER TABLE tb_name ADD 字段名 类型名;

• 2.10 删除列

ALTER TABLE tb_name DROP COLUMN 列名;

• 2.11 修改表名

RENAME TABLE 旧表名 TO 新表名;

• 2.12 截断表

-- 截断表: 扔到表中所有数据，类似链表的扔掉头部节点
-- 优点：速度快
-- 缺点：不记录日志，无法恢复。
TRUNCATE TABLE student;

• 2.13 删除所有数据

-- 删除所有数据
-- 优点：记录日志，可以从日志中恢复。
-- 缺点：速度慢。
DELETE FROM student;

• 2.14 利用子查询结果，批量插入数据

--  利用子查询结果，批量插入数据
INSERT INTO student2  SELECT tb_name FROM student1;
CREATE TABLE student2 AS SELECT * FROM student1;
COMMIT;

SELECT COUNT(*) FROM student2;
SELECT COUNT(*) FROM student1;

• 2.15 修改大量数据

-- 当某张表，同时修改数据量超过 5000 行时，不推荐逐行修改，推荐，使用下述的优化方式：
-- 将2表中的money列累加到对应id的1表中  很危险
UPDATE student s
	SET stu_money = 
		stu_money + (SELECT stu_money FROM student2 s2 WHERE s2.`stu_id` = s.`stu_id`);

• 2.16 优化修改大量数据带来的性能问题

-- 优化
-- 1- 用子查询建立新表
CREATE TABLE student3
	AS SELECT s1.stu_id,s1.stu_name,s1.stu_dob,s1.stu_money+s2.stu_money FROM student s1, student2 s2
		WHERE s1.stu_id = s2.stu_id;
-- 2- 删除旧表中所有数据
-- 注意对所有的 drop table 、 truncate table 都很危险，一般先做备份
TRUNCATE TABLE student;
-- 3- 复制新表数据到旧表
INSERT INTO student  SELECT * FROM student3;

• 2.17 复制表数据到新表（注意：没有约束）

--  复制表数据到新表（注意：没有约束）
CREATE TABLE stud2 AS SELECT * FROM stud;

• 2.18 select count(*); 与 select count(1); 与 select count(列名)的区别

--  统计 stu_dob 列不为null的数据行数,将空的数据行数去除掉
SELECT COUNT(stu_dob) FROM student2;
--  统计数据行数
SELECT COUNT(*) FROM student2;
--  统计数据行数
SELECT COUNT(1) FROM student2;

count(*) 和 count(1)和count(列名)区别

执行效果上：

count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL

count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL

count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计.

执行效率上：

列名为主键，count(列名)会比count(1)快

列名不为主键，count(1)会比count(列名)快
如果表多个列并且没有主键，则 count（1） 的执行效率优于 count（*）

如果有主键，则 select count（主键）的执行效率是最优的

如果表只有一个字段，则 select count（*）最优。

3、约束条件

• 3.1非空约束

-- 非空约束： 约束列中的值不能为null,如果明确填入null则错误;
-- 如果不指定填入值，则用数据类型的默认值
CREATE TABLE tab3( 
    tid INT NOT NULL,
    tname VARCHAR(20), 
    dob DATETIME NOT NULL
    );
INSERT INTO tab3(tid, tname) VALUES(NULL,'aa');

• 3.2主键约束

--主键约束： 
/*   语法约束： 不能为null且不能重复
     逻辑意义：主键列是唯一标识一行数据的列
       学生id 、 学生姓名、 得分
          1        张三      300
          2        张三      300
      注意：每张表只能设定一个主键（但是可以同时使用多个列同时作为一个主键的复合主键）
*/
DROP TABLE stu;
-- 定义复合主键（不常用）
CREATE TABLE stu(
	stu_type INT,
	stu_id INT,  
	stu_name VARCHAR(20),
	PRIMARY KEY(stu_type,stu_id));
DESC stu;

INSERT INTO stu VALUES(1,1, 'aa');
INSERT INTO stu VALUES(1,2, 'aa');
INSERT INTO stu VALUES(2,1, 'aa');
-- 复合主键的列不能为null
INSERT INTO stu VALUES(NULL,1, 'aa');

SELECT * FROM stu;

• 3.3唯一性约束

-- 唯一性约束：可以为null但是不能重复
--  战士id、 枪id
DROP TABLE stu;
CREATE TABLE stu(
	stu_id INT, 	
	stu_type INT, 
	stu_name VARCHAR(20),
	PRIMARY KEY(stu_id),
	UNIQUE(stu_type)
	);
DESC stu;

INSERT INTO stu VALUES(1,1, 'aa');
INSERT INTO stu VALUES(2,NULL, 'aa');
INSERT INTO stu VALUES(3,NULL, 'aa');

SELECT * FROM stu;

• 3.4检查性约束

-- 4-检查约束： 对某个列上单独设定的检查条件
-- 注意：MySQl中检查约束不强制(无检查约束)
DROP TABLE stu;
CREATE TABLE stu(
	stu_id INT, 	
	stu_type INT, 
	stu_name VARCHAR(20),
	stu_age INT CHECK(stu_age>=18),
	stu_gender INT(1) CHECK(stu_gender IN (0,1)),
	PRIMARY KEY(stu_id),
	UNIQUE(stu_type)
	);
DESC stu;

INSERT INTO stu VALUES(2,3,'aa',17);

SELECT * FROM stu;

MYSQL对检查性约束check不起强制性作用，可以说没作用，但是MYSQL官方对CHECK改成了ENUM对列属性做了限制

• 3.5 外键约束（子表的外键约束列只能是主表的主键列或者唯一性约束列）

用父表中的主键约束列或者唯一性约束列，才能作为子表的外键约束列，来约束 外键列上的值，只能来自父表的约束列中含有的值或者null

数据库的三范式：

第一范式：所有字段原子性；

第二范式：在满足第一范式的基础上，除了主外键列以外的其他列，和主键唯一对应。

第三范式：在满足第二范式的基础上，每个列和主键直接依赖，不能有传递依赖（要解决列之间的传递依赖就删除传递依赖的列）

/*
	数据库的前三范式：
	第一范式：所有字段原子性；
	  姓名、年龄、联系方式
	  
	第二范式：在满足第一范式的基础上，除了主外键列以外的其他列，和主键唯一对应。
	 ---- 员工表（子表）---
	  员工编号	姓名	部门编号
		1	张三	10
		2	张三	20
		3	李四	20
		4	王五	20
		5	小明	null
	
	 ---- 部门表（父表）---
	 部门编号	名称	位置
		10	行政	301
		20	研发	202
	注意：只有主键列或者唯一性约束列才能作为子表的外键
	
	第三范式：在满足第二范式的基础上，每个列和主键直接依赖，不能有传递依赖
	 员工编号	员工姓名	工资	奖金	年薪
		1	张三		8000	500	(8000+500)*12
	 
	 编号	姓名	生日	年龄
	 
	编号	车id	公里数	加油数	油耗(升/百公里)
	1	10	1000	85	8.5
	 
*/
	DROP TABLE dept;
	CREATE TABLE dept(
		deptno	INT(2) UNIQUE,
		dname	VARCHAR(20),
		loc     VARCHAR(50)
	);
	
	INSERT INTO dept VALUES(10, '销售部', '西安');
	INSERT INTO dept VALUES(20, '研发部', '上海');
	INSERT INTO dept VALUES(30, '行政部', '西安');
	INSERT INTO dept VALUES(40, '维护部', '北京');
	INSERT INTO dept VALUES(50, '财务部', '深圳');
	
	
	DROP TABLE emp;
	CREATE TABLE emp(
		empno INT(5) PRIMARY KEY,
		ename	VARCHAR(20),
		sal	FLOAT(10,2),
		deptno	INT(2),
		FOREIGN KEY(deptno) REFERENCES dept(deptno)
	);

	DESC emp;
	INSERT INTO emp VALUES(1000, '小明', 5000.0, 10);
	INSERT INTO emp VALUES(1001, '小丽', 5000.0, NULL);
	SELECT * FROM dept;
	SELECT * FROM emp;
	```
	**生成表后添加约束：**
	```
/*
	ALTER TABLE table ADD [CONSTRAINT constraintName] 
		constraintType(column);
*/
-- 添加主键约束
ALTER TABLE dept ADD PRIMARY KEY(deptno);
-- 添加外键约束
ALTER TABLE emp ADD CONSTRAINT fk_dept_emp FOREIGN KEY(deptno) REFERENCES dept(deptno);
-- 删除外键约束
ALTER TABLE emp ADD  FOREIGN KEY(deptno) REFERENCES dept(deptno);

删除约束:

-- 删除约束
/*
ALTER TABLE `mydatabase`.`emp`  
  DROP constraintType constraintName;
  */
  -- 添加外键约束
ALTER TABLE emp DROP FOREIGN KEY fk_dept_emp;
  -- 添加主键约束
ALTER TABLE dept DROP primary key;

---

敲黑板：

ERROR 1025 (HY000): Error on rename of '.\test#sql-c68_10' to '.\test\member' (errno: 150)

原因：

member表的id主键字段是另外一张address的外键，修改主表id字段类型会导致主表与外键关联表字段不一致。

解决方法：

找到外键关联表，找到外键名字。

删除外键约束

修改主表(member)id字段类型:alter table member modify column id bigint;

修改辅表(address)member_id字段类型:alter table address modify column member_id bigint;

将删除的外键补上:alter table address add constraint fk_member_address foreign key(member_id) references member(id);

总结：

当要删除主表上的主键或者唯一性约束条件时，一定要先删除子表上的外键，才能删除主键或者唯一性约束，否则报1025错误

要给子表添加外键时，要在主表添加主键约束或者唯一性约束，才能给子表添加外键约束。

在给子表添加外键时，为什么一定要在父表的主键列或者唯一性约束调条件列？？？

因为主键列不可以为null,不允许重复，唯一性约束条件允许为null,也不允许重复

主要是因为不重复的原因，让子表和主表通过外键一一对应

---

好了！今天的内容就分享到这了。想看MYSQL详细的DML语句操作请点击juejin.cn/post/684490…

---