MySQL语句——DDL、DML、DQL

第一章 DDL语句

第01节 操作数据库

1、创建数据库

简单的创建语法

-- 语法格式：
CREATE DATABASE 数据库的名称;

-- 案例代码: 创建了一个数据库，数据库的名称叫做 mydb02
CREATE DATABASE mydb02;

完整的创建语法

-- 语法格式
CREATE DATABASE IF NOT EXISTS 数据库的名称 CHARACTER  SET 数据库使用的字符集; 

-- 案例代码：创建一个数据库，指定数据库的名称叫做 mydb03 指定数据库使用的字符集是 utf8 如果不存在则创建
CREATE DATABASE IF NOT EXISTS mydb03 CHARACTER SET utf8; 

2、删除数据库

简单的删除数据库的语法

-- 语法格式：
DROP DATABASE  数据库名称;

-- 案例代码: 删除数据库 mydb02 表示数据库的名称
DROP DATABASE mydb02;

完整的删除数据库的语法

-- 语法格式:
DROP DATABASE IF EXISTS 数据库名称; 

-- 案例代码:删除数据库 mydb03 如果不存在，则删除。
DROP DATABASE IF EXISTS mydb03;

3、修改数据库

修改数据库的字符集

-- 语法格式:
ALTER DATABASE 数据库名称 CHARACTER SET 字符集名称;

-- 案例代码: 修改数据库 mydb03 的字符集为gbk格式
ALTER DATABASE mydb03 CHARACTER SET gbk;

4、查询数据库

查询数据库的字符集

-- 语法格式:
SHOW CREATE DATABASE 数据库名称;

-- 案例代码：查看数据库 mydb03 的创建语句，查看的过程当中，可以看到字符集
SHOW CREATE DATABASE mydb03;

查看当前正在使用的数据库

-- 语法格式: 在DATABASE和小括号中间，可以不用打空格
SELECT DATABASE();

-- 案例代码: 查看当前正在使用的数据库
SELECT DATABASE();

5、使用数据库

使用数据库或者切换数据库

-- 语法格式
USE  数据库名称;

-- 案例代码:  mydb03是数据库的名称
USE  mydb03;

第02节 操作数据表

1、创建数据表

简单的建表格式

-- 语法格式
CREATE TABLE 表名称(
    列名1  数据类型1,
    列名2  数据类型2,
    列名3  数据类型3
);

-- 案例代码 student_one 表名称
CREATE TABLE student_one(
	id INT,
	NAME VARCHAR(20),
	age INT
);

完整的建表格式

-- 语法格式
CREATE TABLE IF NOT EXISTS  表名称(
    列名1  数据类型1  约束类型,
    列名2  数据类型2  约束类型,
    列名3  数据类型3  约束类型
) ENGINE=存储引擎;

-- 案例代码 完整格式写法
CREATE TABLE IF NOT EXISTS  student_two(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20) NOT NULL,
	age INT NOT NULL
) ENGINE=INNODB;

2、删除数据表

简单的删除表格式

-- 语法格式
DROP TABLE 表名称;

-- 案例代码  简写格式 student_one 是数据表的名称
DROP TABLE student_one;

完整的删除表格式

-- 语法格式
DROP TABLE IF EXISTS 表名称;

-- 案例代码 完整格式，存在表 则删除
DROP TABLE IF EXISTS student_two;

3、修改数据表

修改表名称

-- 语法格式
ALTER TABLE 旧的表名称 RENAME TO 新的表名称;

-- 案例代码：修改表名称的操作
ALTER TABLE student_one RENAME TO student_1;

修改表的字符集

-- 语法格式
ALTER TABLE 表名称 CHARACTER SET 字符集;

-- 案例代码： 修改表 student_1 的字符集 gbk
ALTER TABLE student_1 CHARACTER SET gbk;

修改表，添加新的列

-- 语法格式
ALTER TABLE 表名称 ADD 列名称 列的数据类型;

-- 案例代码： 修改表，添加新的一列，添加性别 gender
ALTER TABLE student_1 ADD gender VARCHAR(20);

修改表，修改列的数据类型

-- 语法格式
ALTER TABLE 表名称 MODIFY 列名称 列的数据类型;

-- 案例代码： 修改表，修改表所在列的数据类型，性别gender 修改为INT 
ALTER TABLE student_1 MODIFY gender INT;

修改表，修改列名称和数据类型

-- 语法格式
ALTER TABLE 表名称 CHANGE 旧的列名称 新的列名称 新的列数据类型;

-- 案例代码：修改表，将旧的列性别 gender 修改成为 sex VARCHAR(20)
ALTER TABLE student_1 CHANGE gender sex VARCHAR(20);

修改表，删除表当中某一列

-- 语法格式
ALTER TABLE 表名称 DROP 列名称;

-- 案例代码： 修改表，删除表当中的某一列，删除表 student_1 的 sex这列
ALTER TABLE student_1 DROP sex;

4、查询数据表

查看数据库当中，所有表的信息

-- 语法格式:
SHOW TABLES;

-- 案例代码： 查询当前数据库当中，所有的表名称
SHOW TABLES;

查询数据库当中，单独的某个表的表结构，每一列的列名、约束、数据类型、信息

-- 语法格式
DESC 表名称;

-- 案例代码：查询某个表的表结构，student_1 表结构
DESC student_1;

查询数据库当中，表的存储引擎，数据容量，创建时间等

-- 语法格式
SHOW TABLE STATUS FROM 数据库名称  LIKE  '表名称';

-- 案例代码：查询数据库mydb03当中， student_1 表的存储引擎，数据容量，创建时间等
SHOW TABLE STATUS FROM mydb03 LIKE 'student_1';

第03节 数据类型

1、数值类型

1. 整数精确值（精确值）
		INTEGER
		INT
		BIGINT
2. 定点类型（精确值）
		DECIMAL
		NUMERIC
3. 浮点数（近似值）
		FLOAT
		DOUBLE
4. 位类型（如果用于文件的存储，将一个.png格式的文件，上传到数据库保存）
		BIT

案例代码（研究小数类型的问题）

-- 1. 删除表，如果表存在，则删除表
DROP TABLE IF EXISTS student_three;
-- 2. 创建表，包括有浮点数参与进来
CREATE TABLE IF NOT EXISTS student_three(
	id INT,
	chinese INTEGER,
	math DECIMAL,
	english FLOAT(3,1),
	computer DOUBLE(3,1)
);
-- 3. 查询表结构
DESC student_three;
-- 4. 插入部分数据
INSERT INTO student_three VALUES (1,30,40,100,100);   
INSERT INTO student_three VALUES (2,30,40,99.1,100);
INSERT INTO student_three VALUES (3,30,40,99.1,99.1);
INSERT INTO student_three VALUES (4,30,40,99.12,99.12);
INSERT INTO student_three VALUES (5,30,40,99.19,99.19);
-- 5. 查询表记录信息
SELECT * FROM student_three;
/*
   注意什么问题呢？
   如果我们定义数据类型为 FLOAT(M,D)  DOUBLE(M,D)
   M 指的是什么意思呢?
   M 指的是数字字符，一共有多少个
   D 指的是什么意思呢?
   D 指的是小数部分，保留几位
   例如: FLOAT(4,1) 请问这个数据的最大是多少?
   999.9
   
   注意问题：
   如果我们定义的是 FLOAT(3,1) 传入的数据是 99.19 得到结果是 99.2
   小数部分，存在四舍五入
*/

2、字符类型

1. 字符类型，也可以作为字符串使用
	CHAR
2. 字符串类型(使用较多，类似于Java的 String)
	VARCHAR
3. 文本类型
	TEXT 
4. 多个选项当中，选择其中一个，例如：男或女
	SET

案例代码（常见的字符串类型演示）

-- 1. 删除表，如果表存在，则删除表
DROP TABLE IF EXISTS student_four;

-- 2. 创建表，包括有浮点数参与进来
CREATE TABLE IF NOT EXISTS student_four(
	id INT,
	sname VARCHAR(11),
	gender SET('男','女'),
	address TEXT(20),
	phone CHAR(11)
);

-- 3. 查询表结构
DESC student_four;

-- 4. 插入数据
INSERT INTO student_four VALUES (1,'张三','男','湖北武汉','13812345678');
INSERT INTO student_four VALUES (2,"李四","女","湖南长沙","13112345678");
INSERT INTO student_four VALUES (3,"王五","女","湖南长沙","13312345678");
INSERT INTO student_four VALUES (4,"王五","妖","湖南长沙","13312345678");  -- 报错的

-- 5. 查询表记录
SELECT * FROM student_four;

/*
  1. varchar 和 char 都可以表示字符串。
  2. 字符串和字符，都需要使用引号引起来，单引号和双引号都可以。
  3. 当字符串的长度一旦指定之后，字符的数目不能超过
  4. SET("男","女") 之后，不能指定其他数据了，类似于我们的枚举类型
*/

3、时间类型

1. 只是展示日期，不展示时间。（例如：2008年08月08日）
	DATE
	
2. 展示日期和时间（例如：2008年08月08日08:08:08）
	DATETIME
	
3. 时间戳。底层会保存为标准时间格式，在Java当中直接打印输出Date对象
	TIMESTAMP

案例代码

-- 1. 删除表，如果表存在，则删除表
DROP TABLE IF EXISTS student_five;

-- 2. 创建表，包括有浮点数参与进来
CREATE TABLE IF NOT EXISTS student_five(
	id INT,
	birthday DATE,
	schooltime DATETIME,
	classtime TIMESTAMP
);

-- 3. 查询表结构
DESC student_five;

-- 4. 插入数据
INSERT INTO student_five VALUES (1,NULL,NULL,NULL);
INSERT INTO student_five VALUES (2,'2021-8-13','2021-8-13 11:17:53','2021-8-13 11:17:53');
INSERT INTO student_five VALUES (3,'2021-8-13 11:17:53','2021-8-13 11:17:53','2021-8-13 11:17:53');

-- 5. 查询表记录
SELECT * FROM student_five;

/*
   注意三者的区别:
   1. 如果添加数据全部是 null 
	DATE 和  DATETIME 添加的值为 null
	TIMESTAMP 添加的是当前的系统时间
   2. DATE添加的数据的时候，只会保存 年月日的部分，时分秒即使添加，也不会保存。
   3. 添加数据的时候，格式必须是  yyyy-MM-dd HH:mm:ss
   4. 添加数据的时候，三者必须使用引号引起来，单引号双引号都可以。
*/

区别两个时间 DATETIME 和 TIMESTAMP

1. 相同点：
	都可以表示 日期和时间  yyyy-MM-dd HH:mm:ss
	
2. 不同点:
	A. 如果添加数据为 null, DATETIME 添加的值为 null 但是 TIMESTAMP 添加的值是 当前的系统时间。
	B. 系统底层保存数据的区别。DATETIME底层会原封不动的保存，TIMESTAMP底层会转换成为UTC格式保存（包含时区）
	
3. 版本问题:
	TIMESTAMP 在不同版本当中，区别很大。
	如果是 MySQL5.6.5 版本之前的，TIMESTAMP 在一张表当中，只能存在一列，他的数据类型为 TIMESTAMP
	如果是 MySQL5.6.5 版本之后的，TIMESTAMP 在一张表当中，可以存在多列，他们的数据类型为 TIMESTAMP

第二章 DML语句

第01节 新增表记录

1、简单写法

语法：

INSERT INTO 表名称 VALUES (列1的数值, 列2的数值, 列3的数值);

案例：

-- 1. 删除表
DROP TABLE IF EXISTS student_six;

-- 2. 创建表
CREATE TABLE IF NOT EXISTS student_six(
	sid INT,
	sname VARCHAR(20),
	sage INT,
	sgender ENUM('男','女')
);

-- 3. 新增数据(简单写法)
INSERT INTO student_six VALUES (1,'迪丽热巴',18,'女');
INSERT INTO student_six VALUES (2,'古力娜扎',19);   -- 错误的

-- 4. 查询数据
SELECT * FROM student_six;

/*
   注意事项：
	1. 采用简单写法，必须要把所有列对应的值，全部写上才能通过
*/

2、完整写法

语法：

INSERT INTO 表名称  (列名1,列名2,列名3)  VALUES (列1的数值, 列2的数值, 列3的数值);

案例：

-- 1. 删除表
DROP TABLE IF EXISTS student_six;

-- 2. 创建表
CREATE TABLE IF NOT EXISTS student_six(
	sid INT,
	sname VARCHAR(20),
	sage INT,
	sgender ENUM('男','女')
);

-- 3. 新增数据(完整写法)
INSERT INTO student_six (sid,sname,sage,sgender) VALUES (1,'迪丽热巴',21,'女');
INSERT INTO student_six (sid,sname,sage) VALUES (2,'古力娜扎',23);
INSERT INTO student_six (sage,sid,sname) VALUES (24,3,'马尔扎哈');
INSERT INTO student_six (sid,sname) VALUES (4,'张三'),(5,'李四'),(6,'王五');

-- 4. 查询数据
SELECT * FROM student_six;

/*
   注意事项：
      1. 完整写法当中，需要写出列名称，要求就是 列名和列的值顺序要对应。
      2. 还可以进行批量的添加数据
*/

第02节 修改表记录

1、标准语法

语法格式

UPDATE 表名称 SET 列名1=修改值1, 列名2=修改值2;
UPDATE 表名称 SET 列名1=修改值1, 列名2=修改值2  WHERE 条件;

案例代码

-- 1. 删除表
DROP TABLE IF EXISTS student_six;

-- 2. 创建表
CREATE TABLE IF NOT EXISTS student_six(
	sid INT,
	sname VARCHAR(20),
	sage INT,
	sgender ENUM('男','女')
);

-- 3. 插入表记录
INSERT INTO student_six VALUES (1,'张三',23,'男'),(2,'李四',24,'女'),(3,'王五',25,'男');

-- 4. 查询表数据
SELECT * FROM student_six;

-- 5. 修改操作：将sid=1的数据进行修改。 sname="张三丰",sage=100
UPDATE student_six SET sname='张三丰',sage=100 WHERE sid=1;

-- 6. 修改操作：将sage=50
UPDATE student_six SET sage=50;

2、注意事项

加 WHERE 和 不加 WHERE 有什么区别呢？

1. 如果加上了 WHERE 条件，则符合条件的几行记录，才会被修改。
2. 如果没有加上 WHERE 条件，则所有的记录，都认定为符合条件，会全部进行修改。

第03节 删除表记录

1、标准语法

语法格式

DELETE FROM 表名称;
DELETE FROM 表名称 WHERE 条件;

TRUNCATE 表名称;

案例代码：

-- 1. 删除表
DROP TABLE IF EXISTS student_six;

-- 2. 创建表
CREATE TABLE IF NOT EXISTS student_six(
	sid INT,
	sname VARCHAR(20),
	sage INT,
	sgender ENUM('男','女')
);

-- 3. 插入表记录
INSERT INTO student_six VALUES (1,'张三',23,'男'),(2,'李四',24,'女'),(3,'王五',25,'男');

-- 4. 查询表数据
SELECT * FROM student_six;

-- 5. 删除某一条记录， 删除 sid=2
DELETE FROM student_six WHERE sid=2;

-- 6. 删除记录，如果不写 WHERE 条件会逐行删除表记录
DELETE FROM student_six;

-- 7. 如果想要删除整张表的记录，直接采用 TRUNCATE，先删除表，再创建一个一模一样的空表
TRUNCATE student_six;

2、注意事项

1. 加WHERE条件 和 不加WHERE条件 有什么区别？
	A. 不加 WHERE 条件, 他是逐行删除表记录，直到所有的记录全部删除完毕。
	B. 加上 WHERE 条件, 他是根据条件进行删除，只删除满足条件的数据。
	
2. TRUNCATE 和 WHERE 的区别?
	A. TRUNCATE 的删除方式是：直接删除整张表，然后创建一个一模一样的空表。
	B. WHERE 的删除方式是： 逐行删除表记录，效率非常低。

第三章 DQL语句

第01节 基础查询

1、准备表数据

-- 删除数据库
DROP DATABASE IF EXISTS mydb04;

-- 创建数据库,指定字符集 utf8
CREATE DATABASE  IF NOT EXISTS mydb04 CHARACTER   SET   'utf8';

-- 使用数据库
USE mydb04;

-- course表
CREATE TABLE IF NOT EXISTS course (
	cs_id INT(11),    -- 课程编
	cs_name VARCHAR(50),  -- 课程名称
	cs_credit TINYINT(255),    -- 课程学分
	cs_type CHAR(12),    -- 课程类别
	cs_depart CHAR(6) -- 院系名
);

INSERT  INTO course(cs_id,cs_name,cs_credit,cs_type,cs_depart) VALUES           
(5200313,'数据库原理及应用',4,'核心专业','信工'),
(5203314,'计算机导论',4,'通识教育','信工'),
(5219314,'数据结构',5,'专业核心','信工'),
(5223013,'大学物理',4,'专业基础','信工'),
(5227614,'毕业实习',4,'集中实践','信工'),
(5230912,'云计算',2,'共同选修','信工'),
(5236212,'机器学习',2,'共同选修','信工'),
(5237514,'c语言',4,'专业基础','信工'),
(5245112,'区块链',2,'任意选修','信工'),
(7200422,'知识产权法',2,'任意选修','文法'),
(20201833,'概率论',3,'专业基础','基础'),
(20202336,'高等数学',6,'专业基础','基础'),
(29299131,'劳动教育',1,'集中实践','学务');

-- student表
CREATE TABLE IF NOT EXISTS student (
    stu_id BIGINT(11),   -- 学号
    stu_name CHAR(12),   --  姓名
    stu_sex ENUM('男','女'),   -- 性别
    stu_age TINYINT(255),   -- 年龄
    stu_major CHAR(9),    -- 专业
    stu_college CHAR(12)  -- 学院
);

INSERT  INTO student(stu_id,stu_name,stu_sex,stu_age,stu_major,stu_college)    
VALUES (201804550101,'郭奎','男',22,'计科','信工学院'),(201804550102,'吕宇航','男',18,'计科','信工学院'),
(201804550103,'张豪辉','女',19,'计科','信工学院'),
(201804550107,'丁志杰','男',17,'金融学','金贸学院'),
(201804550109,'范伟','男',19,'金融学','金贸学院'),(201804550116,'张依婷','女',17,'大数据','信工学院'),
(201804550120,'张维','男',19,'计科','信工学院'),(201804550121,'朱柳阳','女',20,'计科','信工学院'),
(201804550144,'谭兵炎','男',20,'大数据','信工学院'),(201804550153,'杨志强','男',17,'大数据','信工学院');


-- 查询两张表的记录
SELECT * FROM student;

SELECT * FROM course;

学生表 student 的信息

课程表 course 的信息

2、简单查询

语法格式

-- 1. 查询表的全部记录
SELECT * FROM 表名称;

-- 2. 查询表当中的指定列
SELECT 列名1,列名2,列名3 FROM 表名称;

-- 3. 查询去掉重复的列(说明:只有查询完全相同的情况，才能去重)
SELECT DISTINCT 列名1,列名2 FROM 表名称;

-- 4. 进行四则运算的查询
SELECT 列名1+列名2，列名1-数值 FROM 表名称;

-- 5. 非空运算的查询（当列名2里面的数据，包含有NULL的情况下，按0计算）
SELECT 列名1, IFNULL(列名2,0) FROM 表名称;

-- 6. 取别名的查询（取别名的过程当中，AS可以省略不写）
SELECT 列名1 别名1,列名2 AS 别名2 FROM 表名称;

案例代码

-- 1. 查询表的全部记录
SELECT * FROM student;

-- 2. 查询表当中的指定列
SELECT stu_id,stu_name,stu_major FROM student;

-- 3. 查询去掉重复的列, 查询的结果当中, 如果完全相同,才认定为去重
SELECT DISTINCT stu_major FROM student;

-- 4. 进行四则运算的查询
-- 将查询的分数统一增加10分
SELECT cs_name,cs_credit+10 FROM course;

-- 5. 非空运算的查询
UPDATE course SET cs_credit = NULL WHERE cs_name = 'C语言';
SELECT cs_name,IFNULL(cs_credit+10,0) FROM course;

-- 6. 取别名的查询
SELECT cs_name,IFNULL(cs_credit+10,0) AS '成绩' FROM course;
SELECT cs_name '学科',IFNULL(cs_credit+10,0) AS '成绩' FROM course;

3、条件查询

语法格式

SELECT 列名 FROM 表名称 WHERE 条件;

相关的条件

编号	符号	语法
01	>	列名>数值
02	<	列名<数值
03	>=	列名>=数值
04	<=	列名<=数值
05	=	列名=数值
06	!= 或者 <>	列名!=数值 或者 列名<>数值
07	BETWEEN...AND...	列名 BETWEEN 数值1 AND 数值2
08	IN(选项1,选项2,选项3)	列名 IN (数值1,数值2,数值3)
09	LIKE 占位符	列名 LIKE 占位符
10	IS NULL	列名 IS NULL
11	IS NOT NULL	列名 IS NOT NULL
12	AND 或者 &&	列名>数值1 AND 列名<数值2 或者 列名>数值1 && 列名<数值2
13	OR 或者 ||	列名>数值1 OR 列名<数值2 或者 列名>数值1 || 列名<数值2
14	NOT 或者 !

基础案例代码

-- 1. 查询分数在 3分以上的信息
SELECT * FROM course WHERE cs_credit>3;

-- 2. 查询分数为 1 的信息(这里只能写一个等号)
SELECT * FROM course WHERE cs_credit=1;

-- 3. 查询分数不为2的信息
SELECT * FROM course WHERE cs_credit<>2;

-- 4. 查询分数在2到5之间的数据
SELECT * FROM course WHERE cs_credit BETWEEN 2 AND 5;

-- 5. 查询分数为2和5的数据
SELECT * FROM course WHERE cs_credit IN (2,5);

-- 6. 查询分数为 NULL的数据
SELECT * FROM course WHERE cs_credit IS NULL;

-- 7. 查询分数不为 NULL的数据
SELECT * FROM course WHERE cs_credit  IS NOT NULL;  

-- 8. 查询分数低于2分，高于5的数据
SELECT * FROM course WHERE cs_credit<2 OR cs_credit>5; 

模糊查询

/*
    占位符： % 和 _
    % 表示的是0个或者多个
    _ 表示的是1个字符

*/
-- 1. 查询学科名称当中, 包含有"学"字的学科名称
SELECT * FROM course WHERE cs_name LIKE "%学%"

-- 2. 查询学科名称当中, 以"习"字结尾的
SELECT * FROM course WHERE cs_name LIKE "%习";

-- 3. 查询学科名称，由三个字符组成的
SELECT * FROM course WHERE cs_name LIKE "___";

-- 4. 查询学科名称，由"数"开头，包含4个字符的
SELECT * FROM course WHERE cs_name LIKE "数___";

4、聚合函数

语法格式

1. 查个数	COUNT(列名)
2. 最大值	MAX(列名)
3. 最小值	MIN(列名)
4. 平均值	AVG(列名)
5. 总和	 SUM(列名)

案例代码

-- 1. 查询一共有多少个学生
SELECT COUNT(stu_id) FROM student;

-- 2. 查询学生的最大年龄
SELECT MAX(stu_age) FROM student;

-- 3. 查询学生的最小年龄
SELECT MIN(stu_age) FROM student;

-- 4. 查询学生的平均年龄
SELECT AVG(stu_age) FROM student;

-- 5. 查询学生的年龄总和
SELECT SUM(stu_age) FROM student;

5、排序查询

语法格式

-- 1. 升序: 从小到大，默认排序方式，可以省略 ASC
	ORDER BY 列名 ASC；
	
-- 2. 降序: 从大到小 
	ORDER BY 列名 DESC；

案例代码

-- 1. 查询学生表数据，按照年龄升序排列, 默认是升序，ASC可以省略不写
SELECT * FROM student ORDER BY stu_age ASC;

-- 2. 查询学生表数据，按照年龄降序排列
SELECT * FROM student ORDER BY stu_age DESC;

-- 3. 多条件排序，如果第一条件相同，则按照第二条件排序。
-- 说明: 按照年龄降序排列，如果年龄相同，则按照学号升序排列
SELECT * FROM student ORDER BY stu_age DESC, stu_id ASC;