DML数据操作语言与DQL数据查询语言(08.06)

MySQL数据库

作者：孙慧敏

1.DML（数据操作语言）

    INSERT ( 添加数据语句 )

    UPDATE ( 修改数据语句 )

    DELETE ( 删除数据语句 )

1.1添加语句（insert）

1.1.1 语法

    INSERT INTO 表名 [ ( 字段1, 字段2, 字段3, … ) ] VALUES ( '值1', '值2', '值3', …)

1.1.2 注意

1. 字段或值之间用英文逗号隔开 

2. “字段1, 字段2…”该部分可省略，但添加的值务必与表结构数据列顺序相对应,且数量一致 

3. 可同时插入多条数据，values 后用英文逗号隔开

        单个插入： insert into student(id,name,money) values(1,'张三',3.9);
        多条插入： insert into student(id,name,money) values(3,'张三',3.9),(2,'张三',3.9);

1.2修改语句（update）

1.2.1 语法

UPDATE 表名 SET 列名 = 新值 [ , 列名1 = 新值2, …. ] [ WHERE 条件 ]; 

1.2.1 注意

1. 修改多列时，使用逗号（，）隔开 

2. value 为修改后的数据，可以为变量、具体值、表达式或者嵌套的SELECT结果 

3. WHERE条件如果不指定，则修改表中所有数据

        修改ID为1的数据： update student set name = '李四' , money = 3.6 where id = 1; 
        修改表中所有数据： update student set name = '李四' , money = 3.6;

1.3WHERE条件子句（条件筛选）

    运算符 
    =，<> 或 !=，>，<，>=，<=，BETWEEN...AND, AND，OR

1.4 删除语句（delete）

1.4.1 语法

    DELETE FROM 表名 [ WHERE 条件 ]; 

1.4.2 注意

    如不指定则删除该表的所有列数据

2.DQL（数据查询语言）

2.1 基础查询语句（select）

2.1.1 语法-

    SELECT 列名1，列名2 FROM 表名 [ WHERE 条件 ]; 

2.1.2 注意

    1. 当出现多列时要以逗号（，）隔开 

    2. 当不写WHERE条件时，查询当前表所有数据 

    3. 如想查所有列时，可用*号代替所有列（不建议使用） 

    4. 查询出的列中可以加入表达式，如：select 成绩/10 

    5. where条件有比较操作符与逻辑操作符构成

2.2 别名（as）

    别名分为1. 列别名   2. 表别名

2.2.1 语法

    SELECT 列名1 as 列别名，列名2 FROM 表名 as 表别名 [ WHERE 条件 ]; 

2.2.2 注意

    as 可以不用写

2.3 操作符

2.3.1 逻辑操作符

2.3.2 比较操作符

注意 ：

    1、数值数据类型的记录之间才能进行算术运算 

    2、相同数据类型的数据之间才能进行比较

    3、like:
        like '%字符'  匹配N字符
        like '%字符%' 匹配内容中包含此字符的
        like '_字符'  匹配一个字符内容+字符的

    4、between:
        列名 BETWEEN b AND c

   5、IN 
        列名 IN (a1,a2,a3,….)     

2.4 多表连接查询

多表连接查询分为：

    1. 内连接(inner join)

    2. 外连接： 1.左连接(LEFT JOIN) 2.右连接(RIGHT JOIN)

2.4.1 内连接(inner join )

说明 ： 
两张表连接，两张表字段完全匹配才返回数据

语法： 
SELECT 列名 from a表 inner join b表 on a表.连接字段=b表.连接字段 [WHERE 条件] 

如 学生表与年级表
学生表数据：
学生1 年级1
学生2 年级2 
年级表数据：
年级1
select * from student a INNER JOIN grade b on a.GradeId=b.GradeID;
结果：
显示学生表与年级表一条数据 （只显示学生1信息）
分析：
学生2的年级2在年级表中不存在，则不显示学生2

2.4.2 左连接(LEFT JOIN)

    两张表连接，以左边的表为基础进行连接，如果左表有数据就显示左表数据

    语法： 
    SELECT 列名 from a表 left join b表 on a表.连接字段=b表.连接字段 [WHERE 条件] 

如 学生表与年级表
学生表数据：
学生1 年级1
学生2 年级2 
年级表数据：
年级1
select * from student a Left JOIN grade b on a.GradeId=b.GradeID;
结果：
显示学生表与年级表2条数据 （只显示学生1与学生2同时显示）
分析：
以左表为基础，显示左表中所有数据，左表为学生表，2条记录

2.4.3 右连接(RIGHT JOIN)

    两张表连接，以右边的表为基础进行连接，如果右表有数据就显示右表数据

    语法： 
    SELECT 列名 from a表 right join b表 on a表.连接字段=b表.连接字段 [WHERE tiaoj] 

如 学生表与年级表
学生表数据：
学生1 年级1
年级表数据：
年级1
年级2
年级3
select * from student a right JOIN grade b on a.GradeId=b.GradeID;
结果：
显示3条记录
分析：
右表为主表，右表中3条记录

2.4.4 自连接

    表A与表A自己连接自己（一般使用PID来标识）

2.4.5 DISTINCT（去除重复）

    去掉SELECT查询返回的记录结果中重复的记录（所有返回列的值都相同），只返回一条

    语法： 
    SELECT DISTINCT 字段名1, 字段名2... FROM 表名 [WHERE 条件] 

2.4.6 排序（ORDER BY）

    语法： 
    SELECT 字段名1, 字段名2... FROM 表名 [WHERE 条件] [order by 字段 desc|asc] 

    注意 ： 
    1、desc降序 asc升序(默认) 
    2、order by 在where之后

2.4.7 LIMIT(限制返回记录数)

    语法： 
    SELECT 字段名1, 字段名2... FROM 表名 [WHERE 条件] [order by 字段 desc|asc] [LIMIT m,n] 

    注意 ： 
    1、m 制定第一个返回记录行的偏移量 

    2、n 制定返回记录行的最大数目 

    3、limit 3 不写偏移量，表示从第0条开始，显示3条 

    4、limt在order by之后

    5、limit(m.n):从第m+1条记录开始，显示n条记录

3. 子查询

    1. 在查询语句中的WHERE条件子句中，又嵌套了另外一个查询语句

    2. 在返回列中嵌套一个查询

3.1 where条件中嵌套

    要求：查询课程为《高等数学-2》且分数不小于80分的学生的学号和姓名
    select 
        a.StudentNo,a.StudentName 
        from student a 
        where a.StudentNo in 
        (
        select b.SubjectNo 
        from `subject` b LEFT JOIN result c on                  
        b.SubjectNo=c.StudentNo
        where b.SubjectName = '高等数学-2' and c.StudentResult>80
        );
 先运行子查询，在运行外面的查询
 分析：条件课程名与分数均不在student表中，也没有外键，表连接无法使用。
 1.以学号为条件，使用in ，因为可能存在多名学生
 2.课程表与成绩表存在外键，所以子查询使用表连接
 3.写出课程表语成绩表中两个条件
 4.返回满足的学生学号

3.2 列字段嵌套子查询

4. MYSQL函数

    1. 数学函数

    2. 字符串函数

    3. 日期和时间函数

    4. 系统信息函数

数学函数
https://www.cnblogs.com/qiudongxu/p/7463928.html

字符串函数
https://www.cnblogs.com/geaozhang/p/6739303.html

日期函数
https://www.cnblogs.com/ggjucheng/p/3352280.html

5.分组查询（group by）

    语法： 
    SELECT 列名1，列名2 FROM 表名 [ WHERE 条件 ] [ GROUP BY …]; 

    注意 ： 
    对所有的数据进行分组统计 
    分组的依据字段可以有多个，并依次分组 
    与HAVING结合使用，进行分组后的数据筛选

5.1 having

sql中的having语句是在使用group by的时候使用的。

通常where语句是在group by之前做数据筛选的，而having语句是对group by之后的结果进行筛选的。

    从商品销售表里取得用户A购买的各种商品的总数：
SELECT
    SUM(TOTAL)
FROM
    COST
WHERE
    USERID = 'A'
GROUP BY 
    PRODUCTID
 
从商品销售表里取得用户A购买的各种商品的总数，并且改总数大于10：
SELECT
    SUM(TOTAL)
FROM
    COST
WHERE
    USERID = 'A'
GROUP BY
    PRODUCTID
HAVING
    SUM(TOTAL) > 10

6.表合并（UNION与UNION ALL）

CREATE TABLE fzh
select * from fz1
UNION ALL
select * from fz2

6.1 UNION与UNION ALL的区别：

    1、对重复结果的处理：UNION在进行表链接后会去重，UNION All不会。

    2、对排序的处理：Union将会按照字段的顺序进行排序；UNION ALL只是简单的将两个结果合并后就返回。

    UNION ALL 要比UNION效率高，所以，如果可以确认合并的两个结果集中不包含重复数据且不需要排序时的话，那么就使用UNION ALL。

7. SQL语句全语法

    语法： 

    SELECT 列名 FROM 表名 [ WHERE 条件 ] [ GROUP BY …] [ HAVING …] [ ORDER BY… ][ LIMIT ]; 

SELECT   [ALL | DISTINCT] 
{  * |  table.* | [ table.field1 [ as  alias1] [,table.field2 [as  alias2]][,…]] }
FROM  table_name  [ as  table_ alias  ]
    [left|out|inner  join  table_name2]    #联合查询
    [ WHERE  … ]    #指定结果需满足的条件
    [ GROUP BY …]   #指定结果按照哪几个字段来分组
    [ HAVING …]     #过滤分组的记录必须满足的次要条件
    [ ORDER BY… ]   #指定查询记录按一个或者多个条件排序
    [ LIMIT  {   [ offset,] row_count   |   row_count OFFSET offset   }] ;              #指定查询的记录从哪条至哪条