一、DDL
DDL：数据定义语言（Data Definition Language）用来定义数据库的数据库，表，列等对象。
1、DDL之数据库操作
- 展示所有数据库
show databases;- 查看当前数据库
select database();- 创建数据库
格式： create database 数据库名 [character set utf8]
create database mydb character set utf8;- 查看数据库的定义信息
格式：show create database 数据库名
show create database mydb;- 使用数据库
use mydb;- 删除数据库
格式：drop database 数据库名
drop database mydb;2、DDL之表操作
- 查看所有表
show tables;- 创建表
格式：create table 表名(字段名 数据类型 [约束]，
字段名 数据类型 [约束],
字段名 数据类型 [约束]
)
create table person(pid varchar(32) primary key auto_increment, pname varchar(50)) comment '人员表';;- primary key：主键
- auto_increment：自动增长
- comment：说明信息
- 查看表的创建信息
格式：show create table 表名
show create table person;- 查看表结构
格式：desc 表名
desc person;- 删除表
格式：drop table 表名
drop table person;- 修改表名
格式：rename table 原表名 to 新表名
rename table person to persons;- 修改表删除列
格式：alter table 表名 drop [column] 列名
alter table pereson drop column psex;- 修改表添加列
格式：atter table 表名 add [column] 列名 数据类型 [约束]
alter table person add column psex varchar(2) not null;- 修改表修改列的默认值约束
格式：alter table 表名 alter [column] 列名 set default 默认值
格式：alter table 表名 alter [column] 列名 drop default
alter table person alter column psex set default 'boy';
alter table person alter column psex drop default;- 修改表修改列的数据类型、约束以及列位置的移动
格式：alter table 表名 modify [column] 列名 数据类型 [约束] [first|alter 列名]
alter table person modify column psex varchar(20) default 'boy' comment '性别';- 修改表修改列的重命名、数据类型以及列位置的移动
格式：alter table 表名 change [column] 原列名 新列名 数据类型[约束] [first|alter 列名]
alter table person change id pid int auto_increment comment '编号';alter column、modify column、change column的区别：
alter column：只能用来设置或移除列的默认值。列的默认值存储在 .frm 文件中，直接修改 .frm 文件而不涉及表数据，所以操作非常快。（主要用于只修改列默认值）
modify column：可以更改列的定义。这个操作会做读写操作，即：拷贝了整张表到一张新表。（主要用于修改列定义）
change column：可以重命名列或者修改列的定义。也会执行读写操作。（主要用于修改列名）
二、DML
DML：数据操作语言（Data Manipulation Language），用来对数据库中表的记录进行更新。
主要包括：
- 插入：insert
- 修改：update
- 删除：delete
1、insert添加记录
格式：insert into 表名(列名1，列名2，列名N)values(值1，值2，值N)
格式：insert into 表名 set 列名1=值1，列名N=值N
- 插入的值的类型要与列的类型一致或兼容
insert into person(pid, pname, psex) values(1, 'Tom', 'boy');- 如果列为自动增长，值可以为null
insert into person(pid,pname,psex) values(null,'Jack','boy');- 列名之间的顺序可以与表字段的顺序不一样，但列与值应该一一对应
insert into person(pname,psex,pid) values('Lucy','girl',null);- 如果需要插入所有字段的值可以简写
insert into person values(null,'Tony','boy');- 如果需要插入多条记录，可以叠加values后的值，记录之间用逗号分隔
insert into person values(null,'Judy','girl'),(null,'Gina', 'girl'),(null, 'Hellen', 'girl');2、 update修改记录
单表格式：
- update 表名 set 列名1=值1，列名N=值N where 筛选条件
多表格式：
- update 表名1，表名2 set 列N=值N where 连接条件 and 筛选条件
- update 表名1 [left|inner|right] join表名2 on 连接条件 set 列N=值N where 筛选条件
update person set pname='Jerry',psex='girl' where pid = 1;3、delete删除记录
单表格式：
- delete from 表名 where 筛选条件
多表格式：
- delete 表名1，表名2 from 表名1，表名2 where 连接条件 and 筛选条件
- delete 表名1，表名2 from 表名1 [left|inner|right] join 表名2 on 连接条件 where 筛选条件
delete from person where psex = 'boy' and pname like 'T%';清空表格式：
- truncate table 表名
truncate table person;delete和truncate的区别
- delete 可以加where 条件，truncate不能加
- 删除全表时，truncate是直接将表删除再建立相同结构的表，而delete是逐条删除，因此truncate比delete效率更高
- 假如要删除的表中有自增长列，如果用delete删除后，再插入数据，自增长列的值从断点开始，而truncate删除后，再插入数据，自增长列的值从1开始。
- truncate删除没有返回值，delete删除有返回值
- truncate删除不能回滚，delete删除可以回滚.
三、DQL
DQL：数据查询语言（Data Query Language）用于查询数据，不会对数据照成实际新的变化。把查询的结果发送到客户端进行显示，查询的结果为一个虚拟表。
基本结构：
select [distinct] 字段1[，字段2，字段N]
from 表1[，表2，表N]
where 过滤条件
[group by 字段1[，字段2，字段N]]
[having by 过滤条件]
[order by 字段1[，字段2，字段N]]
1、简单查询
- *：查询所有信息
select * from product;- 查询指定信息
select pname,price from product- as：别名 as可以省略(表和字段都能用)
select p.pname as '名称', p.price as '价格' from product as p;
select p.pname '名称', p.price '价格' from product p;- distinct：去重复值
select distinct price from product;- 运算查询（查询结果为表达式）
select pname '八折商品', price '原价' , (price * 0.8) '折后' from product;2、条件查询
运算符 | 解释 |
>、<、>=、<=、=、 <>或!=、 | 大于、小于、大于等于、小于等于、等于、不等于 |
and | 多个条件同时成立 |
or | 多个条件任一成立 |
not(set) | 查找多个不符和条件的值。不成立，例：where not(salary>100); |
in(set) | 查找多个符和条件的值。在in列表中的值，例：in(100,200) |
is null | 为空 |
is not null | 不为空 |
between…and… | 显示在某一区间的值(包含头尾) |
like | 模糊查询。通配符：%代表任意多个字符 ；_代表任意一个字符 |
- 查询价格不是800的商品信息
-- 方案一：!=运算符
select * from product where price != 800;
-- 方案二：<>
select * from product where price <> 800;
-- 方案三：not
select * from product where price not(price = 800);- 查询价格在200~1000之间的商品信息
select * from product where price >= 200 and price <= 1000;
select * from product where price between 200 and 1000;
-- 查询价格不在200~1000之间的商品信息
select * from product where price <= 200 and price >= 1000;
select * from product where price not between 200 and 1000;- 查询价格等于300、500的商品信息
select * from product where price = 300 or price = 500;
select * from product where price in(300,500);
-- 查询价格不等于300、500的商品信息
select * from product where price != 300 and price != 500;
select * from product where price not in (300, 500);- 查询没有分类的商品信息
select * from product where cid is null;- 查询有分类的商品信息
select * from product where cid is not null;- 查询名称含有"好"的商品信息
select * from product where cname like '%好%';- 查询名称第2个字为"电"的商品信息
select * from product where cname like '_电%';3、排序查询
语法：order by 字段1 [desc|asc] [，字段N [desc|asc] ]
asc：升序排序(默认)
desc：降序排序
- 展示所有商品并按价格排序升序
select * from product order by price acs- 展示所有商品并先按价格降序，再按商品名称升序
select * from product order by price desc, cname- 查询商品去重复后的价格，并按降序排序
select distinct price from product order by price desc;4、聚合查询
之前的查询都是横向查询，都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询，对一列的值进行计算，然后返回一个单一的值；另外聚合函数会忽略空值。简单来讲,聚合查询就是先把表的数据聚在一起,统一进行计算后,再得出一个结果的查询方式;
聚合函数：
- count：统计指定列不为NULL的记录行数；
- sum：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
- avg：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；
- max：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
- min：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
- 查询商品的总条数
select count(*) from product;- 查询价格大于2000的商品的总条数
select count(*) from product where price > 2000;- 查询分类为1的所有商品的总和
select sum(price) from product where cid = 1;- 查询分类为2的所有商品的平均价格
在使用avg()函数时，因为其忽略空值记录，因此要根据需求考虑是否要将null值记录计算在内，如果想要计算在内的话，我们可以使用这个函数：ifnull(colName,value)，它的作用是如果指定的colName对应的值有null,我们就使用value值,否则使用其本身
-- 如果price为null，则转化为0进行计算
select avg(ifnull(price,0)) from product where cid = 2;
-- 不计算price为null的平均值
select avg(price) from product where cid = 2;- 查询商品的最高价格和最低价格的商品
select max(price),min(price) from product;5、分组查询
分组查询是指使用group by字句对查询信息进行分组。
group by 分组字段 having 分组条件;
分组操作中的having子语句，是用于在分组后对数据进行过滤的，作用类似于where条件。
having与where的区别:
where | having |
在分组前对数据进行过滤 | 在分组后对数据进行过滤 |
后面不可以使用分组函数 | 后面可以使用分组函数(统计函数) |
- 统计各个分类商品的个数
select cid,count(*) from product group by cid;- 统计各个分类商品的个数且只显示个数大于30个信息
select cid,count(*) from product group by cid having count(*) > 30;
