mysql超全语法大全_default encryption='n

mysql安装教程

一、登录（使用可视化工具，可忽略）

打开命令行工具，输入以下命令，根据提示输入 root 用户的密码。
mysql -u root -p 
mysql -u root -p -D 数据库名
1
2
3

二、创建数据库

1. 显示数据库：SHOW DATABASES
2. 创建数据库：CREATE DATABASE

   '''
   1、创建一个school库
   2、运行SHOW DATABASES，会发现多了一个school库
   '''
   CREATE DATABASE school;
   1
   2
   3
   4
   5

3. 数据库不存在创建数据库：CREATE DATABASE IF NOT EXISTS

   '''
   1、如果school库不存在，则创建school库，
   2、不使用IF NOT EXISTS，如果school库存在，会报库已存在的错误
   '''
   CREATE DATABASE IF NOT EXISTS school;
   1
   2
   3
   4
   5

4. 数据库规则设置：DEFAULT CHARACTER SET、DEFAULT COLLATE、DEFAULT ENCRYPTION=‘N’

   '''
   1、如果school库不存在，则创建school库，
   2、DEFAULT CHARACTER SET 设置字符集
   3、DEFAULT COLLATE 设置排序规则
   4、DEFAULT ENCRYPTION='N' 是否加密，否：N，是：Y，如果设置Y，则操作数据库需要密钥
   '''
   CREATE DATABASE IF NOT EXISTS school DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8mb3_general_ci DEFAULT ENCRYPTION='N';
   1
   2
   3
   4
   5
   6
   7

5. 删除数据库：DROP DATABASE

   '''删除school库'''
   DROP DATABASE school;
   1
   2

6. 如果数据库存在删除数据库：DROP DATABASE IF EXISTS

   '''
   1、如果school库存在，则删除school库，
   2、不使用IF EXISTS，如果school库不存在，会报库不存在的错误
   '''
   DROP DATABASE IF EXISTS school;
   1
   2
   3
   4
   5

7. 切换数据库：USE

   '''切换到school库'''
   USE school
   1
   2

8. 查询当前使用数据库：SELECT DATABASE()
9. 显示数据库下的表：SHOW TABLES

三、创建表

1. 创建表：CREATE TABLE

   '''
   1、创建一个包含id、name字段的student表
   2、运行SHOW TABLES，会发现多了一个student表
   3、需要切换到要添加表的库，如没切换可以使用：school.student 创建表（在school库里创建student表）
   '''
   CREATE TABLE student(id INT,name VARCHAR(200));
   1
   2
   3
   4
   5
   6

2. 表不存在创建表：CREATE TABLE IF NOT EXISTS

   '''
   1、如果student表不存在，则创建一个包含id、name字段的student表，
   2、不使用IF NOT EXISTS，如果tudent表存在，会报表已存在的错误
   '''
   CREATE TABLE IF NOT EXISTS student (id INT,name VARCHAR(200));
   1
   2
   3
   4
   5

3. 从另一个表创建一个新表：CREATE TABLE SELECT

   '''
   1、从student表中创建student_copy表
   2、*为通配符，如需要student表中部分字段，可直接使用字段名：id,name
   3、如果student表中有数据，会携带数据一起创建student_copy表
   '''
   CREATE TABLE student_copy SELECT * FROM student;
   1
   2
   3
   4
   5
   6

4. 数据库规则设置：DEFAULT CHARACTER SET、ENGINE=InnoDB

   '''
   1、设置字符集DEFAULT CHARACTER SET
   2、设置储存引擎：ENGINE=InnoDB
   3、储存引擎分类：InnoDB、MyISAM、CSV、Archive、Memory
   '''
   CREATE TABLE student(id INT,name VARCHAR(200)) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB;
   1
   2
   3
   4
   5
   6

5. 字段描述：COMMENT

   '''id添加描述学生id'''
   CREATE TABLE student(id INT COMMENT '学生id',name VARCHAR(200));
   1
   2

6. 默认值约束：如果未设置该字段的值，则默认一个值，DEFAULT

   '''id未设置值泽默认为0'''
   CREATE TABLE student(id INT DEFAULT 0,name VARCHAR(200));
   1
   2

7. 空值约束：该字段可以设置为空，NULL

   '''id可以设置为null'''
   CREATE TABLE student(id INT NULL,name VARCHAR(200));
   1
   2

8. 非空约束：该字段不能是空，NOT NULL

   '''id不能是NULL，可以和DEFAULT搭配使用'''
   CREATE TABLE student(id INT NOT NULL DEFAULT 0,name VARCHAR(200));
   1
   2

9. 自增约束：该字段可以自增，AUTO_INCREMENT

   '''
   1、一个表只能有一个自增字段
   2、且必须为键，如UNIQUE KEY，PRIMARY KEY
   3、可以设置自增长起始值AUTO_INCREMENT=10，不设置从1开始自增
   4、不能保证连续递增，假如最新自增数据是id=10，又把该数据删除，那么再插入数据，id会是11，id=10的数据不会再创建
   '''
   CREATE TABLE student(id INT UNIQUE AUTO_INCREMENT,name VARCHAR(200)) AUTO_INCREMENT=10;
   1
   2
   3
   4
   5
   6
   7

10. 自动补零约束：该字段需要自动补零，ZEROSFILL

    '''
    1、id小于四位的会自动补零，int(4)说明字段是4位数字
    2、加了ZEROSFILL，id：0001、0002、0003...，不加ZEROSFILL，id：1、2、3...
    '''
    CREATE TABLE student(id INT(4) ZEROFILL UNIQUE AUTO_INCREMENT,name VARCHAR(200));
    1
    2
    3
    4
    5

11. 无符号约束：不能添加负数，还可以增加数据长度，只针对整型，UNSIGNED

    '''
    1、id为无符号字段，不能添加负数
    2、INT类型，有符号值：-2147683648 到2147683647
    无符号值：0到4294967295（有符号最大值*2）
    '''
    CREATE TABLE student(id INT UNSIGNED ZEROFILL,name VARCHAR(255));
    1
    2
    3
    4
    5
    6

12. 检查约束：CHECK

    '''检查约束id，大于10，小于20；不在这个范围，插入数据时报错'''
    CREATE TABLE student(id FLOAT,name VARCHAR(5),CHECK(id>10 and id<20));
    1
    2

13. 唯一值约束：UNIQUE KEY

    '''id值是唯一的，不能重复'''
    CREATE TABLE student(id INT UNIQUE,name VARCHAR(200) UNIQUE);
    '''有id和name值有一个存在就报错'''
    CREATE TABLE student(id INT,name VARCHAR(200),UNIQUE(id),UNIQUE(name));
    '''id、name值同时存在就报错'''
    CREATE TABLE student(id INT,name VARCHAR(200),UNIQUE(id,name));
    1
    2
    3
    4
    5
    6

14. 主键约束：主键是一行数据的唯一标识，非空且唯一，PRIMARY KEY

    '''id为主键：列级约束'''
    CREATE TABLE student(id INT(4) PRIMARY KEY ,name VARCHAR(200));
    '''id为主键：表级约束，建议'''
    CREATE TABLE student(id INT(4),name VARCHAR(200),PRIMARY KEY (id));
    '''id和id1为复合主键：当id和id1同时存在时报错'''
    CREATE TABLE student(id INT,id1 INT,name VARCHAR(200),PRIMARY KEY (id,id1));
    1
    2
    3
    4
    5
    6

15. 外键约束：主要用于表与表之间的关系，FOREIGN KEY

    '''
    1、示例表：班级表、学生表、课程表、学生身份证
    2、班级和学生是一对多关系
    3、课程对学生是多对多关系
    4、学生身份证表和学生表一对一关系，一对一可以创建成一张表，实际中用的不多
    6、依赖于外键的约束：
    REFERENCES：关联键，用于一对多、多对多关联另一张表的键
    ON DELETE RESTRICTT：父表数据被删除，会阻止删除，默认就是这一项
    ON UPDATE RESTRICT：父表数据被修改，会阻止修改，默认就是这一项
    ON DELETE CASCADE：父表数据被删除，子表相关的数据会一起删除
    ON UPDATE CASCADE：父表数据被修改，子表也会跟着修改
    ON DELETE SET NULL：父表数据被删除，子表相关的数据会设置成NULL
    ON UPDATE SET NULL：父表数据被修改，子表相关的数据会设置成NULL
    '''
    
    '''
    1、创建班级表，设置主键为id
    2、创建学生表，设置主键为id，外键为class_id，并关联班级表中的id
    3、未添加班级数据前，不能添加学生；添加学生时，不存在的班级会报错
    '''
    CREATE TABLE classa(id INT NOT NULL AUTO_INCREMENT,name VARCHAR(255),PRIMARY KEY(id));
    CREATE TABLE student(id INT NOT NULL AUTO_INCREMENT,class_id INT,name VARCHAR(255),PRIMARY KEY(id),FOREIGN KEY(class_id) REFERENCES classa(id));
    '''
    1、该学生表增加了外键约束：ON DELETE CASCADE、ON UPDATE CASCADE
    2、当班级表id变化，学生表中的class_id也会跟着变化
    3、当班级表中某个班级删除，学生表中与该班级相关的学生也会删除
    '''
    CREATE TABLE student(id INT NOT NULL AUTO_INCREMENT,class_id INT,name VARCHAR(255),PRIMARY KEY(id),FOREIGN KEY(class_id) REFERENCES classa(id) ON DELETE CASCADE ON UPDATE CASCADE );
    '''
    1、创建课程表
    2、创建课程与学生之间的中间表，分别设置外键关联学生id、课程id
    '''
    CREATE TABLE course(id INT NOT NULL AUTO_INCREMENT,name VARCHAR(255),PRIMARY KEY(id));
    CREATE TABLE stu_course(id INT NOT NULL AUTO_INCREMENT,stu_id INT,course_id INT,PRIMARY KEY(id),FOREIGN KEY(stu_id) REFERENCES student(id) ON DELETE CASCADE ON UPDATE CASCADE,FOREIGN KEY(course_id) REFERENCES course(id) ON DELETE CASCADE ON UPDATE CASCADE);
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34

16. 设置约束名字：CONSTRAINT

    '''约束名字：student_key为主键的约束名字'''
    CREATE TABLE student(id INT NOT NULL AUTO_INCREMENT,name VARCHAR(255),CONSTRAINT student_key PRIMARY KEY(id))
    '''约束名字：设置stu_key为stu_id外键名字，设置course_key为course_id外键名字'''
    CREATE TABLE stu_course(id INT NOT NULL AUTO_INCREMENT,stu_id INT,course_id INT,PRIMARY KEY(id),CONSTRAINT stu_key FOREIGN KEY(stu_id) REFERENCES student(id) ON DELETE CASCADE ON UPDATE CASCADE,CONSTRAINT course_key FOREIGN KEY(course_id) REFERENCES course(id) ON DELETE CASCADE ON UPDATE CASCADE);
    
    '''查询约束名字'''
    SHOW INDEX IN stu_course;
    SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE table_name='stu_course';
    1
    2
    3
    4
    5
    6
    7
    8

17. 查询表约束：SHOW CREATE TABLE 表名；

四、数据类型

1. 整型
   
2. 字符串
   
3. 日期时间
   
4. 其他
   

五、删除表

1. 删除表：DROP TABLE

   #删除表
   DROP TABLE table_name;
   #删除多个表
   DROP TABLE table_name1,table_name2;
   1
   2
   3
   4

2. 表存在删除表：DROP TABLE IF EXISTS

   DROP TABLE IF EXISTS table_name1,table_name2;
   1

3. 清空表：TRUNCATE TABLE

   #清空表
   TRUNCATE TABLE table_name;
   1
   2

六、修改表

同样适用修改库：ALTER DATABASE

1. 添加列：ALTER TABLE ADD

   '''添加一列'''
   ALTER TABLE student ADD name VARCHAR(20);
   '''添加多列'''
   ALTER TABLE student ADD name VARCHAR(20),ADD name1 VARCHAR(20);
   1
   2
   3
   4

2. 重命名列名：ALTER TABLE RENAME COLUMN

   '''重命名name1，多个列名逗号隔开'''
   ALTER TABLE student RENAME COLUMN name1 TO age
   1
   2

3. 重命名表名：RENAME TABLE

   '''student重命名为new_student，多个表逗号隔开'''
   RENAME TABLE student TO new_student
   1
   2

4. 修改列定义：ALTER TABLE MODIFY

   '''修改年龄为INT类型，且检验约束为0-100，多个列逗号隔开'''
   ALTER TABLE student MODIFY age INT CHECK (0<age<100);
   1
   2

5. 重命名并修改列定义：ALTER TABLE CHANGE

   '''name1修改为name，且数据类型为VARCHAR(255)，并添加不能为NULL的约束，多个列逗号隔开'''
   ALTER TABLE student CHANGE name1 name VARCHAR(255) NOT NULL;
   1
   2

6. 删除列：ALTER TABLE DROP

   '''删除列，多个列逗号隔开'''
   ALTER TABLE student DROP name
   1
   2

7. 其他

   ALTER TABLE table_name ADD INDEX: 添加索引
   ALTER TABLE table_name ADD PRIMARY KEY: 添加主键
   ALTER TABLE table_name ADD FOREIGN KEY: 添加外键
   ALTER TABLE table_name ADD UNIQUE INDEX: 添加唯一索引
   ALTER TABLE table_name ADD CHECK: 添加检查约束
   ALTER TABLE table_name DROP INDEX index_name: 删除索引
   ALTER TABLE table_name DROP PRIMARY KEY: 删除主键
   ALTER TABLE table_name DROP FOREIGN KEY fk_symbol: 删除外键
   ALTER TABLE table_name DROP CHECK symbol: 删除检查约束
   ALTER TABLE table_name RENAME INDEX old_index_name TO new_index_name: 重命名索引
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10

七、增加表数据

'''先创建班级表'''
CREATE TABLE class(id INT AUTO_INCREMENT,name VARCHAR(255),PRIMARY KEY(id));
1
2

1. 添加一条数据：INSERT INTO VALUE

   '''需添加全部列：添加id=1、name='一年级一班'的数据 '''''
   INSERT INTO class VALUE(1,'一年级一班')
   '''
   1、添加部分列：添加name='一年级二班'的数据
   2、因id添加的约束类型自增，所以一年级二班id=2
   '''
   INSERT INTO class(name) VALUE('一年级二班')
   1
   2
   3
   4
   5
   6
   7

2. 添加多条数据：INSERT INTO VALUES

   '''
   1、添加部分列：添加一年级一班、一年级二班
   2、支持添加全部列、支持添加一条数据
   '''
   INSERT INTO class(name) VALUES('一年级三班'),('一年级四班');
   1
   2
   3
   4
   5

3. 根据主键来判断，如果主键重复则执行update，不重复则添加数据：ON DUPLICATE KEY UPDATE

   '''
   1、因为id=4存在，则把id=4，更新为二年级一班
   2、多个更新值要都好隔开
   3、支持VALUES
   '''
   INSERT INTO class VALUE(4,'二年级一班') ON DUPLICATE KEY UPDATE name=VALUES(name);
   '''因为id=5不存在，则把id=5，添加到表中'''
   INSERT INTO class VALUE(5,'二年级二班') ON DUPLICATE KEY UPDATE name=VALUES(name);
   1
   2
   3
   4
   5
   6
   7
   8

4. 将一个表中的数据插进另一个表：INSERT INTO SELECT

   '''
   1、创建一个新的班级表
   2、查询class表中的数据，并插入到new_class
   3、全部插入的时候，需要保证表结构一致
   4、部分插入的时候，需要保证插入的列一致
   '''
   CREATE TABLE new_class(id INT AUTO_INCREMENT,name VARCHAR(255),PRIMARY KEY(id));
   INSERT INTO new_class SELECT * FROM class;
   1
   2
   3
   4
   5
   6
   7
   8

5. 添加数据：REPLACE INTO

   '''
   1、REPLACE语句和INSERT语句很像，它们的不同之处在于，当插入过程中出现了重复的主键或者重复的唯一索引的时候，INSERT 语句会产生一个错误，而 REPLACE 语句则先删除旧的行，再插入新的行
   2、只示例VALUE，同时具有INSERT语句相关语法
   '''
   REPLACE INTO new_class VALUE(1,'六年级一班')
   1
   2
   3
   4
   5

八、查询条件

九、查询表数据

'''先创建学生表'''
CREATE TABLE student(
        id INT AUTO_INCREMENT,
        cla_id INT NOT NULL,
        name VARCHAR(255),
        gender ENUM('男','女','未知') DEFAULT '未知',
        age INT,
        PRIMARY KEY(id),
        CONSTRAINT cla_key FOREIGN KEY(cla_id) REFERENCES class(id) ON DELETE CASCADE ON UPDATE CASCADE ,
        CHECK (gender IN ('男','女','未知')));   
                
'''给学生表插入数据'''
INSERT INTO student(cla_id, name, age,gender) VALUES(1,'隗莹',5,'女'),
                                                    (1,'独珠',6,'女'),
                                                    (1,'时霖',7,'男'),
                                                    (1,'尚菊',4,'女'),
                                                    (1,'旷石',5,'男'),
                                                    (1,'张一',6,'未知'),
                                                    (1,'张二',6,'未知'),
                                                    (1,'张三',5,'女'),
                                                    (1,'张四',5,'女'),
                                                    (1,'张三丰',7,'男'),
                                                    (2,'罕子薇',5,'女'),
                                                    (2,'诺长',6,'女'),
                                                    (2,'春怜烟',6,'男'),
                                                    (3,'大珠',8,'女'),
                                                    (3,'庆逸思',7,'男'),
                                                    (4,'李一',6,'未知'),
                                                    (4,'李二',9,'未知'),
                                                    (5,'李三',10,'女'),
                                                    (5,'李四',8,'女'),
                                                    (6,'李三丰',7,'男');                                    
         
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

1. 查询表数据：SELECT FROM

   #查询所有列
   SELECT * FROM student;
   #只查询学生name
   SELECT name FROM student;
   #查询学生name、age
   SELECT name,age FROM student;
   1
   2
   3
   4
   5
   6

2. 增加查询条件：SELECT FROM WHERE

   '''只演示部分查询条件，其他查询条件可参考该文章第八条'''
   
   #查询年龄大于5的学生
   SELECT * FROM student WHERE age>5;
   #查询姓氏为张的数据，%匹配多个字符
   SELECT * FROM student WHERE name LIKE '张%';
   #查询姓氏为张的数据，且只有两位字符的数据，_匹配一个字符
   SELECT * FROM student WHERE name LIKE '张_';
   #查询姓氏为张的数据，并且性别不为未知的数据
   SELECT * FROM student WHERE name LIKE '张%' and gender!='未知';
   #查询姓氏不为张的数据，并且性别不为未知的数据
   SELECT * FROM student WHERE name NOT LIKE '张%' and gender!='未知';
   '''
   1、子查询：查询班级存在的学生，因学生的cla_id外键关联班级表的id，所以都能查出来
   2、使用 WHERE id in (SELECT id FROM class) 查询效果更明显，查询id存在于班级表id中的学生
   '''
   SELECT * FROM student WHERE cla_id in (SELECT id FROM class)
   '''子查询：查询学生表中id、age同时大于班级表中id得最大值、年龄得平均值'''
   SELECT * FROM student WHERE (id,age) > (SELECT MAX(id),AVG(id) FROM class);
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13
   14
   15
   16
   17
   18
   19

3. 限制返回行数查询：LIMIT

   '''返回前两行'''
   SELECT * FROM student LIMIT 2;
   '''跳过 2 个记录行，并从 第 3 个记录行开始，最多返回 5 个记录行'''
   SELECT * FROM student LIMIT 2,5;
   1
   2
   3
   4

4. 查询没有重复的数据：SELECT DISTINCT

   '''返回性别重复数据'''
   SELECT DISTINCT gender FROM student;
   '''返回性别、年龄都不重复数据'''
   SELECT DISTINCT gender,age FROM student;
   '''全部列都不重复数据'''
   SELECT DISTINCT * FROM student;
   1
   2
   3
   4
   5
   6

5. 排序查询：ORDER BY

   '''根据age降序查询'''
   SELECT * FROM student WHERE age>5 ORDER BY age DESC;
   '''根据age升序查询'''
   SELECT * FROM student WHERE age>5 ORDER BY age ASC;
   '''多个字段排序，先根据age升序查询，再根据id降序查询'''
   SELECT * FROM student WHERE age>5 ORDER BY age ASC,id DESC;
   #自定义排序规则
   SELECT name FROM student WHERE name LIKE '张_' ORDER BY FIELD(name,'张四','张三','张二','张一');
   SELECT name FROM student WHERE name LIKE '张_'
                         ORDER BY CASE name
                             WHEN name='张四' THEN 4
                             WHEN name='张三' THEN 3
                             WHEN name='张二' THEN 2
                             WHEN name='张一' THEN 1
                             END;
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13
   14
   15

6. 分组查询：GROUP BY

   '''性别分组'''
   SELECT gender FROM student GROUP BY gender;
   '''
   1、单独分组没什么意义，经常和函数一起使用
   2、GROUP_CONCAT(name) 把分组后的name，用逗号拼接到一起
   3、COUNT(*) 分别计算分组后的数量
   '''
   SELECT gender,GROUP_CONCAT(name) AS name,COUNT(*) AS count FROM student GROUP BY gender ORDER BY count;
   '''HAVING：分组的条件查询'''
   SELECT gender,GROUP_CONCAT(name) AS name,COUNT(*) AS count FROM student GROUP BY gender HAVING count>5;
   '''WITH ROLLU的作用是：在最后新增一行，来记录当前列里所有记录的总和'''
   SELECT gender,GROUP_CONCAT(name) AS name,COUNT(*) AS count FROM student GROUP BY gender WITH ROLLUP;
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12

7. 重命名列，重命名表名：AS

   '''
   1、name重命名为姓名，age重命名为年龄，gender重命名为性别、表名重命名为stu
   2、一般用于多表查询
   '''
   SELECT name AS '姓名',age AS '年龄',gender AS '性别' FROM student as stu WHERE stu.age>5;
   
   '''
   1、多表查询
   2、查询出学生所在班级的名字及学生信息
   '''
   SELECT cla.name AS '班级',
          cla.id AS '班级id',
          stu.id AS '学生id',
          stu.name AS '学生姓名',
          stu.age AS '学生年龄',
          stu.gender AS '学生性别'
   FROM student AS stu,class AS cla
   WHERE stu.cla_id = cla.id;
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13
   14
   15
   16
   17
   18

8. 多表查询，交叉连接：根据条件返回交集，CROSS JOIN

   '''查询年龄大于7的学生所在的班级名字及学生信息'''
   SELECT cla.name AS '班级',
          cla.id AS '班级id',
          stu.id AS '学生id',
          stu.name AS '学生姓名',
          stu.age AS '学生年龄',
          stu.gender AS '学生性别'
   FROM student AS stu CROSS JOIN class AS cla ON cla.id = stu.cla_id WHERE stu.age>7;
   1
   2
   3
   4
   5
   6
   7
   8

9. 多表查询，内部连接：根据条件返回交集，与交叉连接一样 INNER JOIN

   '''查询年龄大于7的学生所在的班级名字及学生信息'''
   SELECT cla.name AS '班级',
          cla.id AS '班级id',
          stu.id AS '学生id',
          stu.name AS '学生姓名',
          stu.age AS '学生年龄',
          stu.gender AS '学生性别'
   FROM class AS cla INNER JOIN student AS stu ON cla.id = stu.cla_id WHERE stu.age>7;
   '''等同于'''
   SELECT cla.name AS '班级',
          cla.id AS '班级id',
          stu.id AS '学生id',
          stu.name AS '学生姓名',
          stu.age AS '学生年龄',
          stu.gender AS '学生性别'
   FROM student AS stu,class AS cla
   WHERE stu.cla_id = cla.id AND stu.age>7;
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13
   14
   15
   16
   17

10. 多表查询，左连接：根据条件匹配右表的每一行，匹配成功将左表和右表合并组成新的数据行返回，匹配失败左表的行和NULL组成新的数据行返回 LEFT JOIN

    SELECT cla.name AS '班级',
           cla.id AS '班级id',
           stu.id AS '学生id',
           stu.name AS '学生姓名',
           stu.age AS '学生年龄',
           stu.gender AS '学生性别'
    FROM class AS cla LEFT JOIN student AS stu ON cla.id = stu.cla_id;
    1
    2
    3
    4
    5
    6
    7

11. 多表查询，右连接：根据条件匹配左表的每一行，匹配成功将右表和左表合并组成新的数据行返回，匹配失败右表的行和NULL组成新的数据行返回 LEFT JOIN

    '''同样的sql，使用RIGHT JOIN把表的位置互换实现了LEFT JOIN的效果''''
    SELECT cla.name AS '班级',
           cla.id AS '班级id',
           stu.id AS '学生id',
           stu.name AS '学生姓名',
           stu.age AS '学生年龄',
           stu.gender AS '学生性别'
    FROM student AS stu RIGHT JOIN class AS cla ON cla.id = stu.cla_id;
    1
    2
    3
    4
    5
    6
    7
    8

12. 多表查询，合并多张表的结合集：UNION

    '''创建测试学生表，并把学生表中的数据插入测试学生表'''
    CREATE TABLE test_student(
            id INT(4) ZEROFILL AUTO_INCREMENT,
            cla_id INT NOT NULL,
            name VARCHAR(255),
            gender ENUM('男','女','未知') DEFAULT '未知',
            age INT,
            PRIMARY KEY(id),
            CONSTRAINT test_cla_key FOREIGN KEY(cla_id) REFERENCES class(id) ON DELETE CASCADE ON UPDATE CASCADE ,
            CHECK (gender IN ('男','女','未知'))) AUTO_INCREMENT=100;
    
    INSERT INTO test_student(name,age,cla_id,gender) SELECT name,age,cla_id,gender from student;
    
    '''
    1、UNION内部的SELECT 语句必须拥有相同数量的列，列也必须拥有相似的数据类型，每条 SELECT 语句中的列的顺序必须相同
    2、查询两张表中年龄大于7的学生
    '''
    SELECT * FROM student WHERE age>7 UNION SELECT * FROM test_student WHERE age>7;
    '''
    1、查询两张表中年龄大于7的学生
    2、与直接使用UNION区别在于，把联合查询的结果集创建成new_stu虚拟表，再做条件查询
    '''
    SELECT * FROM (SELECT * FROM student WHERE age>7 UNION SELECT * FROM test_student) AS new_stu WHERE age>7;
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23

十、修改表数据

```
'''把学生表中得第一个学生得名字改为张子凡'''
UPDATE student SET name = '张子凡' LIMIT 1;
'''把学生表中得第一个学生得名字改为张子凡，并把年龄改为20'''
UPDATE student SET name = '张子凡',age=20 LIMIT 1;
'''把三年级二班改为四年级一班'''
UPDATE class SET name = '四年级一班' WHERE name='三年级二班';
'''子查询修改：把student第一个姓李的名字修改成test_student第一个姓张的名字'''
UPDATE student SET name = (SELECT name FROM test_student WHERE name LIKE '张%' LIMIT 1) WHERE name LIKE '李%' LIMIT 1;
```
1
2
3
4
5
6
7
8
9
10

十一、聚合函数

1. 求总和：SUM()
2. 求平均值：AVG()
3. 求最大值: MAX()
4. 求最小值：MIN()
5. 计数：COUNT()

SELECT COUNT(*) AS '总数',
       AVG(age) AS '平均年龄',
       MAX(age) AS '最大年龄',
       MIN(age) AS '最小年龄',
       SUM(age) AS '年龄总数'
FROM student;
1
2
3
4
5
6

十二、日期函数

1. 当前日期：NOW()、CURDATE() 、CURTIME()

   '''查询当前日期'''
   SELECT NOW() AS '当前日期时间',CURDATE() AS '当前日期',CURTIME() AS '当前时间';
   '''给学生表添加currentDate列，并设置成当前日期'''
   ALTER TABLE student MODIFY currentDate DATETIME;
   UPDATE student SET currentDate = NOW() LIMIT 20;
   1
   2
   3
   4
   5

2. 格式化日期：DATE_FORMAT(date,format)

   SELECT DATE_FORMAT(currentDate,'%Y-%m-%d') FROM student;
   1

3. 计算日期 d 加上 n 天的日期：ADDDATE(d,n)

   SELECT ADDDATE(currentDate,2) FROM student;
   1

4. 计算时间 t 加上 n 秒时间：ADDTIME(t,n)

   SELECT ADDTIME(currentDate,3) FROM student;
   1

5. 计算两个日期间隔天数：DATEDIFF(d1,d2)

   SELECT DATEDIFF(NOW(),currentDate) FROM student;
   1

6. 计算起始日期 d 加上一个时间段后的日期：DATE_ADD(d，INTERVAL expr type)
7. 计算起始日期 d 减一个时间段后的日期：DATE_SUB(date,INTERVAL expr type)

   '''
   1、日期加上两天
   2、type：MICROSECOND（微秒）、SECOND（秒）、MINUTE（分）、HOUR（小时）、DAY（天）、WEEK（周）、MONTH（月）
   '''
   SELECT DATE_ADD(currentDate,INTERVAL 2 DAY) FROM student;
   '''日期减两天'''
   SELECT DATE_SUB(currentDate,INTERVAL 2 DAY) FROM student;
   1
   2
   3
   4
   5
   6
   7

8. 返回日期d的天、月、年、小时、分钟、秒、微秒：DAY(d)、MONTH(d)、YEAR(d)、HOUR(d)、MINUTE(d)、SECOND(d)、MICROSECOND(d)、
9. 返回日期d 星期：DAYNAME(d)
10. 计算本月第几天：DAYOFMONTH(d)
11. 本月的最后一天：LAST_DAY(d)
12. 返回当前日期所在的季节：QUARTER(d)
13. 返回当前时间是当年的第几周：WEEKOFYEAR(d)
14. 返回当前时间是当年的第几天： WEEKDAY(d)
15. format参数
    
    

十三、流程控制函数

十四、数值型函数

十五、字符串函数

十六、其他函数