MySql的DDL和DML和DQL的基本语法

DDL(数据定义语言)
    解释：定义和管理数据对象，如数据库，数据表等
    命令：CREATE(创建)、DROP(删除)、ALTER(改变)

基本语法：
创建数据库：create database 库名；
    语法：
        create database 表名(
        字段名 类型属性，
        字段名 类型属性，
        ...
        字段名 类型属性
        );

删除数据库：drop database 库名；
查看所有库：show databases;
选择数据库：use 库名；

修改和删除数据库表：
修改表名：alter table 旧表名 rename as 新表名；
    例：alter table student rename as s;
        # 将表名student改成s
添加字段：alter table 表名 add 字段名 列类型[属性]；
    例：alter table student add sage int;
        # 给student表中添加sage字段属性为int；
修改字段：
    a.更改字段属性：
    alter table 表名 modify 字段名 列类型[属性]
    例：alter table student modify sname varchar(10);
        # 将student表中sname字段属性更改为varchar(10);
    b.更改字段名和属性：
    alter table 表名 change 旧字段名 新字段名 列类型[属性]；
    例：alter table student sname mingzi varchar(10);
        # 将student表中sname字段名改为mingzi 字段属性为varchar(10);
删除字段：alter table 表名 drop 字段名；
    例：alter table student drop sname;
        # 将student表中sname字段删除；

DML(数据操作语言)
    解释：用于操作数据库对象中包含的数据
    命令：INSERT(增加)、DELETE(删除)、UPDATE(修改)
数据增加
    关键字INSERT,主要功能是给数据库表增加记录。
数据删除
    关键字DELETE,主要功能是删除数据库表中已有的记录。可以依照条件去做修改。
数据修改
    关键字UPDATE,主要功能是修改数据库表中已有的记录。可以根据条件去做修改。

增加数据（INSERT）
语法：
    insert into 表名(字段名,字段名,...,字段名) values(值,值,...,值);
例：
    insert into student(sid,sname,birthday,ssex,classid) values(9,'张三','1999-6-1','男',3);
        # 给student表中sid,sname,birthday,ssex,classid字段添加数据；
    -- 一次性插入多条数据
    insert into student(sname,ssex) values('吴承恩','男'),('曹雪芹','男'),('施耐庵','男'),('罗贯中','男');
增加数据（INSERT INTO SELECT）
    INSERT INTO SELECT语句 从一个表复制数据，然后把数据插入到一个已存在的表中。
    目标表中任何已存在的行都不受影响。
语法：a.表都要存在
    INSERT INTO table2(字段名,...,字段名) SELECT 字段名,...,字段名 from table1;
    含义：将table1中数据赋值到table2中
    b.新建表并赋值内容 新表不能存在
    create table 新表 select * from;
例：
a.表都要存在    insert into select
create table stu1(
    xingming varchar(10),
    ssex varchar(2)
)；
insert into stu1 select sname,ssex from student；
    # 将student表中sname,ssex 字段赋值给stu1表
b.新建表的时候插入数据 新表不能存在
create table newstu select sname,birthday,ssex from student；
    # 创建一个新表并把student表中sname,birthday,ssex 字段赋值给新表

修改语句（UPDATE）
语法：
    update 表名 set 字段名=值,字段名=值... where 子句；
例：
    update newstu set ssex= '女' where sname='花木兰'；
        # 将newstu表中 名字为花木兰的性别改为女

WHERE子句
    有条件的从数据库表中获取记录，通常同在修改，删除，和查询语句的时候，协助该类语句从条件中获取
记录。针对修改和删除语句，如果没有条件，则全部修改和全部删除。

运算符	含义	举例	结果
=	等于	5=6	false
<> 或 !=	不等于	5!=6	true
>	大于	5>6	false
<	小于	5<6	true
>=	大于等于	5>=6	false
<=	小于等于	5<=6	true
BETWEEN	在某个范围之间	BETWEEN 5 AND 10	-
AND	并且	5>1 AND 1>2	false
OR	或	5>1 OR 1>2	True
Not	非

DQL(数据查询语言)
    DQL(Data Query Language 数据查询语言)。用途是查询数据库数据，如SELECT语句。是SQL语句
    中最核心、最重要的语句，也是使用频率最高的语句。其中，可以根据表的结构和关系分为单表查询和多
    表联查。
单表查询
    针对数据库中的一张数据表进行查询，可以通过各种查询条件和方式去做相关的优化。
多表联查
    针对数据库中两张或者两张以上的表同时进行查询，依赖的手段有复杂查询和嵌套查询。

单表查询：
语法：
    select 字段名1，字段名2，...，字段名n from 表名；
注：“*”表示所查询的数据库表的全部字段。
例：
    select * from student;
        # 查询student表中所有字段数据
    select sid,sname,birthday,ssex,classid from student;
        # 查询student表中sid,sname,birthday,ssex,classid 字段数据

字段别名
语法：
    select 字段名1 别名，字段名2 别名，...，字段名n 别名 from 表名；
例：
    select distinct ssex from student;
        # 将student表中 distinct字段名改为ssex 

WHERE条件子句
语法：
    select * from 表名 [where 条件];
例：
    select * from student where ssex = '男' and classid = 1
        # 查询student表中ssex是男并且classid为1 的数据

LIKE 关键字
语法：
    select * from 表名 where 字段 like 条件；
    模糊符号 % 任意多的任意字符
    模糊符号_ 一个任意字符
例：
    select * from student where sname like '%明%';
        # 查询student表中sname字段中含 明 的数据
    select * from student where sname like '明%';
        # 查询student表中sname字段中明开头的字段的数据
    select * from student where sname like '%明';
        # 查询student表中sname字段中结尾是 明 的字段的数据
    select * from student where sname like '明_';
        # 查询student表中sname字段中开头是 明 的两位字符的数据

IN 关键字
语法：
    select * from 表名 where 字段 in(值1，值2，...,值n);
    in 在特定的范围内查找
例：    
    select * from student where sid in (2,5,6,8,9,20,300,4000)；
        # 学生编号是 2,5,6,8,9,20,300,4000

NULL 值查询
语法：SELECT * FROM 表名 WHERE 字段 IS NULL | IS NOT NULL
    select * from 表名 where 字段 is null | is not null;
例：
    没有生日的学生  is 是对null的判断
    select * from student where birthday is null;

常用的聚合函数

函数名	意义
count(字段)	统计个数
avg(字段)	平均值
sum(字段)	总和
max(字段)	最大值
min(字段)	最小值

注：count（） 不统计null
例：
    select count(*) from student where ssex = '男'
        # 统计student表中ssex字段为 男 的个数
    select avg(score) from sc;
        # 所有学生的平均分
    select sum(score) from sc;
        # 总成绩
    select max(score) from sc;
        # 最高成绩
    select min(score) from sc;
        # 最低成绩

GROUP BY   
     1、对所有的数据进行分组统计；
     2、分组的依据字段可以有多个，并依次分组。

HAVING
     与GROUP BY结合使用，进行分组后的数据筛选。
注：
where 是对聚合（分组）前的每一条数据的筛选
having 是对聚合（分组）后的每一条数据的筛选
having 不能单独出现
例：
     select sid,avg(score) from sc group by sid having avg(score) >80;
    # 平均分大于80的
     select sid,avg(score) from sc where score < 60 group by sid;
    # 不及格平均分

ORDER BY排序
语法：
    select * from 表名 order by 字段名；
    升序 asc 降序 desc
    多排序规则按照order by后面的字段 先写先排后写后排
例：
    select * from sc order by score desc;
        # 成绩表中成绩降序排列
    select * from sc order by score desc,sid desc;
        # 成绩表中成绩降序排列，编号降序排列

LIMIT关键字（分页）
语法：
    select * from student limit [n,m];
    limit（页码-1）*步长,步长
    n 表示第一条记录的偏移量，m 表示显示记录的数量
例：
    select * from student limit 0,3;
    select * from student limit 3,3;
    select * from student limit 6,3;

多表联查
     多表联查可以通过连接运算实现，即将多张表通过主外键关系关联在一起进行查询。
等值查询和非等值查询
语法：
     非等值查询：SELECT * FROM 表1，表2
     等值查询：SELECT * FROM 表1，表2 WHERE 表1.字段1 = 表2.字段2...
其中：
• 与单表查询类似，都是SELECT语句；
• 把多个表放到FROM后，并用逗号隔开；
• 可使用AS关键字取别名，便于引用；
• 如无重名查询字段则可省略数据表的指定。

连接查询
语法：
     select * from 表1 left|right|inner join 表2 on条件;
例：
     -- 多表联查
     -- 笛卡尔积
     select * from student,class;

INNER JOIN
     在表中至少一个匹配时，则返回记录
例：
-- 等值联查
-- 内联数据
select * from student,class where student.classid = class.classid;

select student.* from student,class,sc,course,teacher where student.classid = class.classid and
student.Sid=sc.Sid and sc.Cid=course.Cid and course.Tid=teacher.Tid and tname = '张三';

-- inner join on
select * from student inner join class on student.classid=class.classid;

select * from student inner join class on student.classid=class.classid
inner join sc on student.sid=sc.sid
inner join course on sc.cid=course.cid
inner join teacher on course.tid=teacher.tid where tname='李四';    

LEFT JOIN
     从左表（表1）中返回所有的记录，即便在右（表2）中没有匹配的行。
RIGHT JOIN
     从右表（table_2）中返回所有的记录，即便在左（table_1）中没有匹配的行。
例：
-- 查询学生信息
-- left join  on 左外联 主表在left join 左边
select * from student left join class on student.classid=class.classid;
-- right join  on 右外联 主表在right join 右边
select * from class right join student on student.classid=class.classid;

-- 查询班级信息
-- left join  on 左外联
select * from class left join student on student.classid=class.classid;
-- right join  on 右外联
select * from student right join class on student.classid=class.classid;

连接查询(2)
左表独有的数据：
     -- 没有班级的学生
     select * from student left join class on student.classid=class.classid
where class.classid is null； # 班级为空
右表独有的数据：
     -- 没有学生的班级
     select * from student right join class on student.classid=class.classid
where student.sid is null;# 学生为空

连接查询(2)
UNION
     union是求两个查询的并集。
     union合并的是结果集，不区分来自于哪一张表，所以可以合并多张表查询出来的数据。
语法：
     select A.field1 as f1, A.field2 as f2 from <table1> A
     union
     (select B.field3 as f1, field4 as f2 from <table2> B)
     order by 字段 desc/asc
注意：
     1.列名不一致时，会以第一张表的表头为准，并对其栏目。
     2.会将重复的行过滤掉。
     3.如果查询的表的列数量不相等时，会报错。
     4.在每个子句中的排序是没有意义的，mysql在进行合并的时候会忽略掉。
     5.如果子句中的排序和limit进行结合是有意义的。
     6.可以对合并后的整表进行排序
例：
-- 没有班级的学生和没有学生的班级
-- 没有班级的学生
select * from student left join class on student.classid=class.classid
where class.classid is null  # 班级为空
union
-- 没有学生的班级
select * from student right join class on student.classid=class.classid
where student.sid is null;# 学生为空

UNION ALL
语法：
select A.field1 as f1, A.field2 as f2 from <table1> A
union all
(select B.field3 as f1, field4 as f2 from <table2> B)
order by 字段 desc/asc

union all 是求两个查询的并集，但是不会把重复的过滤掉，而是全部显示出来

例：
-- union 会去除重复的数据（DISTINCT 一致）
-- union all 不会去重
-- 所有学生和班级
select * from student left join class on student.classid=class.classid
union
select * from student right join class on student.classid=class.classid;

select * from student left join class on student.classid=class.classid
union all
select * from student right join class on student.classid=class.classid;

子查询
     子查询，又叫内部查询
1. where 型子查询：

     查询id最大的一个学生(使用排序+分页实现)
例：-- 查询id最大的一个学生
     select * from student order by sid desc limit 1;

     查询id最大的一个学生(使用where子查询实现)
例：
-- 查询id最大的一个学生(子查询)
select * from student where sid=(select max(sid) from student);
-- 查询每个班下id最大的学生
select * from student where sid in (select max(sid) from student group by classid);

2 from型子查询:
     把内层的查询结果当成临时表，供外层sql再次查询。查询结果集可以当成表看待。
     临时表要使用一个别名。

     查询大于5人的班级名称和人数(不使用子查询)
例：
-- 查询大于5人的班级名称和人数
select classname,count(*) from student
left join class on student.classid = class.classid
group by class.classid
     查询大于5人的班级名称和人数(使用from型子查询)
例：
-- from 子查询
select classname,a 人数 from class
left join
(select classid, count(*) a from student group by classid) t1
on class.classid = t1.classid

-- 每个班的平均成绩，班级名称和平均成绩
select classname, avg(score) from class
left join student on class.classid = student.classid
left join sc on student.sid = sc.sid
group by class.classid;

select * from class
left join (
     select classid,avg(score) from sc
          left join student on sc.sid = student.sid
group by classid
)t1 on class.classid = t1.classid;

3 exists型子查询:
     把外层sql的结果，拿到内层sql去测试，如果内层的sql成立，则该行取出。内层查询是exists后的查询。
    
     从学生表中取出学生的信息，（如果该性别下没有男学生则不显示）
     select * from student where exists (select * from student where ssex='男')

4 any, some, all子查询:
(1) any 子查询
     表示满足其中任意一个条件
     假设any内部的查询语句返回的结果个数是三个，
     如:result1,result2,result3,那么，
          select ...from ... where a > any(...);
     ->相当于：
     select ...from ... where a > result1 or a > result2 or a > result3;
some 是 any的别名，所以用法是一样的，可以替代使用

题：查询出一班成绩比二班最低成绩高的学生
--where子查询
select *  from student
inner join sc on student.sid = sc.sid
where classid = 1 and score > (
select min(score) from student
inner join sc on student.sid = sc.sid
where classid = 2
)

--二班成绩
select score from student
inner join sc on student.sid = sc.sid
where classid = 2

70.0
60.0
80.0
50.0
30.0
20.0
31.0
34.0

--where子查询
select *  from student
inner join sc on student.sid = sc.sid
where classid = 1  and (score > 70.0 or score > 60.0 or
score > 80.0 or score > 50.0 or score > 30 or score > 20
or score > 31 or score > 34)
相当于
-- any子查询
select *  from student
inner join sc on student.sid = sc.sid
where classid = 1  and  score > any(select score from student
inner join sc on student.sid = sc.sid
where classid = 2)

（2）all 子查询
     表示满足其中所有条件条件，ALL关键字与any关键字类似，只不过上面的or改成and。
     假设any内部的查询语句返回的结果个数是三个，
          select ...from ... where a > all(...);
     ->
          select ...from ... where a > result1 and a > result2 and a > result3;

题：查询出一班成绩比二班最高成绩高的学生

-- where子查询
select *  from student
inner join sc on student.sid = sc.sid
where classid = 1 and score > (
select max(score) from student
inner join sc on student.sid = sc.sid
where classid = 2
)
相当于
select *  from student
inner join sc on student.sid = sc.sid
where classid = 1  and (score > 70.0 and
score > 60.0 and
score > 80.0 and score > 50.0 and
score > 30 and score > 20
and score > 31 and score > 34)
相当于
-- all子查询
select *  from student
inner join sc on student.sid = sc.sid
where classid = 1  and  score > all(select score from student
inner join sc on student.sid = sc.sid
where classid = 2)

流程控制函数，语句
     流程处理函数可以根据不同的条件，执行不同的处理流程，可以在SQL语句中实现不同的条件选择。
     MySQL中的流程处理函数主要包含IF()、IFNULL()和CASE()函数

函数	用法
IF(value,value1,value2)	如果value的值为TRUE，返回value1，否则返回value2
IFNULL(value1,value2)	如果value1不为NULL，返回value1，否则返回value2
CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 ... [ELSE resultn] END	相当于Java的if...else if ... else...
CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值2 WHEN 值2 ... [ELSE 值n] END	相当于Java的switch...case...

# 控制sql语句
if(v,v1,v2)
v 逻辑表达式 Boolean值
如果真 显示v1
如果假 显示v2
例：
     select tid,tname,if(tsex=1,'男','女'),tbirthday from teacher

ifnull(v,v1)
     v 字段
     v1 如果字段的值是一个null v1就显示
例：
select sid,sname,ifnull(birthday,'1900-1-1') birthday,ssex,ifnull(classid,3) classid from student

case when then end语句
Case具有两种格式。简单Case函数和Case搜索函数。
注：case语句完结以end结尾。
(1)简单Case函数
例：
-- 简单case
select tid,tname,
case tsex
     when 1 then '男'
     when 0 then '女'
     else '未知'
end tsex,
tbirthday, taddress
from teacher；
# 字段tsex中，如果是1 则改为 男 如果是0 则改为 女 否则 未知

(2) Case搜索函数
select stuid,stuname,
case
when stusex = 1 then '男'
when stusex = 0 then '女'
else '其它'
end,
stuaddress
from student

例：
select tid,tname,
case
     when tsex > 1 then '未知'
     when tsex = 1 then '男'
  when tsex < 1 then '女'
     else '不能出现情况'
end ,
tbirthday,taddress from teacher；
# 如果tsex大于1 则改为 未知
如果tsex等于1 则改为 女
如果tsex小于1 则改为 男
否则 不可能出现情况

例：
查询学生的成绩，
并将大于90分的用A显示，
大于80分的用B显示，
大于70分的用C显示，
大于60分的用D显示，
小于60分的显示不及格

select case when score > 90 then 'A' when score > 80 then 'B' when score > 70 then 'C'
when score > 60 then 'D'
when score < 60 then '不及格' end from sc;