MySQL：SQL通用语法、SQL分类、DDL、DML、DQL、DCL

一.SQL通用语法

1. SQL语句可以单行或多行书写，以分号结尾；
2. SQL语句可以使用空格/缩进来增强语句的可读性；
3. MySQL数据库SQL语句不区分大小写，关键字建议大写；
4. 注释：

• 单行：--或#
• 多行：/*   */

二.SQL分类

DDL（Data Definition Language）：数据定义语言，用来定义数据库对象（数据库，表，字段）；

DML（Data Manipulation Language）：数据操作语言，用来对数据库表中的数据进行增删改；

DQL（Data Query Language）：数据查询语言，用来查询数据库中表的记录；

DCL（Data Control Language）：数据控制语言，用来创建数据库用户、控制数据库的访问权限；

三.DDL

1.查询数据库

查询所有数据库

SHOW DATABASES;

查询当前数据库

SELECT DATABASE();

2.创建数据库

CREATE DATABASES [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];

charset utf8/utf8mb4； 

3.删除数据库

DROP DATABASES [IF EXISTS] 数据库名;

4.使用数据库

USER 数据库名;

5.查询当前数据库所有表 

show tables;

  

6.查找表结构

desc 表名；

  

7.查询指定表的建表语句

show create table 表名;

8.创建表 

create table 表名(
    字段1 字段1类型[comment 字段1注释],
    字段2 字段2类型[comment 字段2注释]
)[comment 表注释];

9.表中添加

alter table 表名 add 字段 类型（长度） [comment 注释][约束];

10.修改数据类型

alter table 表名 modify 字段名 新数据类型（长度）;

11.修改字段名和字段类型 

alter table 表名 change 旧字段名 新字段名 类型（长度） [comment 注释] [约束];

eg：将empploy表中nickname字段修改为username，类型为varchar(30);

 alter table emp change nickname username varchar(30) comment '昵称'; 

12.删除字段 

alter table 表名 drop 字段名;

eg：将employ表的字段username删除：

  alter table emp drop username;

13.修改表名 

alter table 表名 rename to 新表名;

 eg：将employ表的表名修改为emp：

 alter table employ rename to emp;

14.删除表 

删除表：
drop table [if exists] 表名;
删除指定表，并重新创建该表：
truncate table 表名;

四.数据类型分类 

分类	类型	大小	有符号(SIGNED)范围	无符号(UNSIGNED)范围	描述
数值类型	TINYINT	1 byte	(-128，127)	(0，255)	小整数值
	SMALLINT	2 bytes	(-32768，32767)	(0，65535)	大整数值
	MEDIUMINT	3 bytes	(-8388608，8388607)	(0，16777215)	大整数值
	INT或INTEGER	4 bytes	(-2147483648，2147483647)	(0，4294967295)	大整数值
	BIGINT	8 bytes	(-2^63，2^63-1)	(0，2^64-1)	极大整数值
	FLOAT	4 bytes	(-3.402823466 E+38，3.402823466351 E+38)	0 和 (1.175494351 E-38，3.402823466 E+38)	单精度浮点数值
	DOUBLE	8 bytes	(-1.7976931348623157 E+308，1.7976931348623157 E+308)	0 和 (2.2250738585072014 E-308，1.7976931348623157 E+308)	双精度浮点数值
	DECIMAL		依赖于M(精度)和D(标度)的值	依赖于M(精度)和D(标度)的值	小数值(精确定点数)

例如：

年龄：age TINYINT UNSIGNED；

分数：score double(4, 1)；4位，小数点后一位；

分类	类型	大小	描述
字符串类型	CHAR	0-255 bytes	定长字符串
	VARCHAR	0-65535 bytes	变长字符串
	TINYBLOB	0-255 bytes	不超过255个字符的二进制数据
	TINYTEXT	0-255 bytes	短文本字符串
	BLOB	0-65 535 bytes	二进制形式的长文本数据
	TEXT	0-65 535 bytes	长文本数据
	MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
	MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
	LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
	LONGTEXT	0-4 294 967 295 bytes	极大文本数据

例子：

char（10）：即使存储一个字符，也占用10个空间；性别；

varchar（10）：存储一个字符，占用一个空间； 用户名；

分类	类型	大小	范围	格式	描述
日期类型	DATE	3	1000-01-01 至 9999-12-31	YYYY-MM-DD	日期值
	TIME	3	-838:59:59 至 838:59:59	HH:MM:SS	时间值或持续时间
	YEAR	1	1901 至 2155	YYYY	年份值
	DATETIME	8	1000-01-01 00:00:00 至 9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
	TIMESTAMP	4	1970-01-01 00:00:01 至 2038-01-19 03:14:07	YYYY-MM-DD HH:MM:SS	混合日期和时间值，时间戳

五.DML

Data Manipulation Language（数据操作语言），用来对数据库中表的数据记录进行增删改查操作。

1.添加数据（insert）

1.给指定字段添加数据
insert into 表名(字段名1,字段名2) values(值1,值2);
2.给全部字段添加数据
insert into 表名 values(值1,值2);
3.批量添加数据
insert into 表名(字段名1,字段名2) values(值1,值2),(值1,值2),(值1,值2);
insert into 表名 values(值1,值2),(值1,值2),(值1,值2);

注意：

• 插入数据时，指定的字段顺序需要与值的顺序是一一对应的。
• 字符串和日期型数据应该包含在引号中。
• 插入的数据大小，应该在字段的规定范围内。 

2.修改数据（update）

update 表名 set 字段名1=值1,字段名2=值2，...[where 条件];

3.删除数据（delete）

delete from 表名 [where 条件];

•  delete语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
• delete语句不能删除某一个字段的值（可以使用update）。

六.DQL

Data Query Language（数据查询语言），查询数据库中表的记录。

编写顺序、执行顺序（1-6）：

select           4
    字段列表
from             1
    表名列表
where            2
    条件列表
group by         3
    分组字段列表
having
    分组后条件列表
order by         5
    排序字段列表
limit            6
    分页参数

1.基本查询

1.查询多个字段
select 字段1,字段2,字段3...from 表名;
select * from 表名;

2.设置别名
select 字段1[as 别名1],字段2[as 别名2]... from 表名;

3.去除重复记录
select distinct 字段列表 from 表名;

2.条件查询

语法：

select 字段列表 from 表名 where 条件列表;

比较运算符	功能
<>或！=	不等于
between * and *	在某个范围内（含最小、最大值）
in(***)	在in之后的列表中的值，多选一
like 占位符	模糊匹配（_ 匹配单个字符，% 匹配任意字符）
is null	是NULL

逻辑运算符	功能
and或&&	并且
or或||	或者
not或！	非

#查询没有身份证号的员工信息
select * from emp where  idcard is null;

#查询有身份证号的员工信息
select * from emp where idcard is not null;

#查询年龄在15岁到20之间（包含15和20）
select * from emp where age between 15 and 20;
select * from emp where age>=15 && age<=20;
select * from emp where age>=15 and age<=20;#注意15和20顺序

#查询年龄等于18或20或40的员工信息
select * from emp where age in (18,20,40);#或用or代替

#查询姓名为两个字的员工信息
select * from emp where name like '__';#不要忘记' '号

#查询身份证号最后一位是X的员工信息
select * from emp where idcard like '%X';#不要忘记' '号

3.聚合函数

将一列数据作为一个整体，进行纵向计算。

语法：

select 聚合函数（字段列表） from 表名;

函数	功能
count	统计数量（注意NULL，不参与统计）
max	最大值
min	最小值
avg	平均值
sum	求和

#1.统计该企业员工数量
select count(*) from emp; #注意NULL，不参与统计；

#2.统计该企业员工的平均年龄
select avg(age) from emp;

#3.统计该企业员工的最大年龄
select max(age) from emp;

#4.统计该企业员工的最小年龄
select min(age) from emp;

#5.统计西安地区员工的年龄之和
select sum(age) from emp where workaddress='西安';

4.分组查询

语法：

select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];

where与having区别：

1.执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤；

2.判断条件不同：where不能对聚合函数进行判断，而having可以；

#1.根据性别分组，统计男性员工和女性员工的数量
select gendar,count(*) from emp group by gendar ;

 

#2.根据性别分组，统计男性员工和女性员工的平均年龄
select gendar,avg(age) from emp group by gendar;

#3.查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址
select workaddress,count(*) from emp where age<45 group by workaddress having count(*)>=3;
#或
select workaddress,count(*) address_count from emp where age<45 group by workaddress having address_count>=3;

5.排序查询

语法：

select 字段列表 from 表名 order by 字段1 排序方式1，字段2 排序方式2;

排序方式：

• asc：升序（默认值）；
• desc：降序；

注意：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序；

#1.根据年龄对公司的员工进行升序排序
select * from emp order by age asc;
#2.根据入职时间，对员工进行降序排序
select * from emp order by entrydate desc;
#3.根据年龄对公司的员工进行升序排序，年龄相同，在按入职时间进行降序排序；
select * from emp order by age asc, entrydate desc;

6.分页查询

语法：

select 字段列表 from 表名 limit 起始索引,查询记录数;

 注意：

1.其实索引从0开始，起始索引=（查询页码-1）×每页显示记录数；

2.分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是limit；

3.如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10；

-- 分页查询
#1.查询第一页员工数据，每页展示10条记录
select * from emp limit 0,10;
select * from emp limit 10;
#2.查询第二页员工数据，每页展示10条记录
select * from emp limit 10,10;

7.练习

#1.查询年龄为20,21,22,23的女性员工信息
select * from emp where gendar='女' and age in (20,21,22,23);

#2.查询性别为男，并且年龄在20-40岁（含）以内的姓名为3个字的员工
select * from emp where gendar='男' and (age between 20 and 40) and name like '___';#模糊查询

#3.统计员工表中，年龄小于60岁的，男性员工和女性员工的人数
select gendar,count(*) from emp where age<60 group by gendar;

#4.查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同按照入职时间降序排序
select name,age from emp where age<=35 order by age asc ,entrydate desc ;

#5.查询性别为男，且年龄在20-40岁（含）以内的前5个员工信息，对查询的结果按照年龄升序排序，年龄相同按照入职时间升序排序；
select * from emp where gendar='男' and age between 20 and 40 order by age asc ,entrydate desc limit 0,5;

七.DCL

Data Control Language（数据控制语言），用来管理数据库用户、控制数据库的访问权限。

1.管理用户

#1.查询用户
use mysql;
select * from user;

#2.创建用户
create user '用户名'@'主机名' identified by '密码';

#3.修改用户密码
alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';

#4.删除用户
drop user '用户名'@'主机名';

例子：

#1.创建用户itcast，只能在当前主机localhost访问，密码123456；
create user 'itcast'@'localhost' identified by '123456';

#2.创建用户heima，可以在任意主机访问该数据库，密码123456；
create user 'heima'@'%' identified by '123456';

#3.修改用户heima的访问密码为1234；
alter user 'heima'@'%' identified with mysql_native_password by'1234';

#4.删除itcast@localhost用户
drop user 'itcast'@'localhost' ;

 注意：

1.主机名可以使用%通配。

2.这类SQL开发人员操作的比较少，主要是DBA（database administrator数据库管理员）使用。

2.权限控制

权限	说明
all，all privileges	所有权限
select	查询数据
insert	插入数据
update	修改数据
delete	删除数据
alter	修改表
drop	删除数据库/表/视图
create	创建数据库/表

#1.查询权限
show grants for '用户名'@'主机名';

#2.授予权限
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

#3.撤销权限
revoke 权限列表 数据库名.表名 from '用户名'@'主机名';

例子：

#1.查询权限
show grants for 'heima'@'%';

#2.授予权限
grant all on destiny.* to 'heima'@'%';

#3.撤销权限
revoke all on destiny.* from 'heima'@'%';