mysql-基础-sql语句

通用语法及分类

SQL通用语法：

SQL语句可以单行或多行书写,以分号结尾。
SQL语句可以使用空格缩进来增强语句的可读性。
MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
注释：
- 单行注烃：–注释内容或 # 注释内容（MySQL特有）
- 多行注释：严注释内容

SQL分类：

分类	全称	说明
DDL	Data Definitson Language	数据定义语言，用来定义数据库对象（数据库，表，字段）
DML	Data Manipulation Language	数据操作语言，用来对数据库表中的数据讲行增删改
DQL	Data Query Language	数据查询语言，来查询数据库中表的记录
DCL	Data Control Language	数据控制语言，用来创建数据库用户、控制数据库的访问权限

SQL-DDL（定义）

1. DDL-数据库操作

查询

查询所有数据库
SHOW DATABASES;
查询当前数据库
SELECT DATABASE();

创建

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

删除

DROP DATABASE [IF EXISTS] 数据库名;

使用

USE 数据库名;

2. DDL-表操作

a. 查询

i. 查洵当前数据库所有表
SHOW TABLES;
ii. 查询表结构
DESC 表名;
iii. 查洵指定表的建表语句
SHOW CREATE TBALE 表名;

b. 创建

CREATE TABLE 表名（
  字段1 字段1类型 [COMMENT 字段1注释],
  字段2 字段2类型 [COMMENT 字段2注释],
  字段3 字段3类型 [COMMENT 字段3注释],
  ……
  字段n 字段n类型 [COMMENT 字段n注释]
) [COMMENT 表注释];

注意：
[ … ]为可选参数，最后一个字段后面没有逗号

c. 修改

i. 添加字段:
ALTER TABLE 表名 ADD 字段名类型（长度）[COMMENT 注释] [约束];

案例：
为employee表增加仃个新的字段”昵称“为nickname,类型为varchar(20)
alter table employee add nickname varchar(20) ;

ii. 修改数据类型:
ALTER TABLE 表名 MODIFY 字段名新数据类型（长度）;

iii. 修改字段名和字段类型:
ALTER TABLE 表名 CHANGE 旧字段名新字段名类型（长度）[COMMENT 注释] [约束];

案例：
将employee表的nickname字段修改为username,类型为varchar(30)
alter table employee change nickname username varchar(30) ;

iv. 修改表名:
ALTER TABLE 表名 RENAME TO 新表名;

案例：
将employee表的表名修改为emp
alter table employee rename emp;

d. 删除

i. 删除字段：
ALTER TABLE 表名 DROP 字段名;

案例：
将employee 表的字段username删除
alter table employee drop username ;

ii. 删除表:
DROP TABLE [IF EXISTS] 表名;

iii. 删除指定表，并重新创建该表:
TRUNCATE TABLE 表名;

注意：
在删除表时，表中的全部数据也会被删除。

3. DDL-数据类型及案例

a. 数据类型：

MYSQL中的数据类型有很多，主要分为三类：

数值&字符串&日期数据类型表
- 数值类型

类型	大小	有符号(signed)范围	无符号(unsigned)范围	描述
tinyint	1 byte	(-128,127)	(0,255)	小整数值
smallint	2 bytes	(-32768,32767)	(0,65535)	大整数值
mediumint	3 bytes	(-8388608,83886007)	(0,16777215)	大整数值
int或integer	4 bytes	(-2147483648,214783648)	(0,4294967295)	大整数值
bigint	8 bytes	(-2^63,2^63-1)	(0,2^64-1)	极大整数值
float	4 bytes	(-3.402823466 E+308,3.402823351 E+308)	0和（1.175494351 E-38，3.402823466 E+38）	单精度浮点数值
double	8 bytes	(-1.79693134862357 E+308,1.796931348623557 E+308)	0和(2.2250738585072014 E-38，1.7976931348623157 E+38)	双精度浮点数值
decimal		依赖于M(精度)和D（标度）的值	依赖于M(精度)和D（标度）的值	小数值（精确定点数）

字符串类

类型	大小	有符号(signed)范围	描述
char	0-255 bytes	例子：性别插入（1）	定长字串符
varchar	0-65535 bytes	例子：用户名（20）	变长字串符
tiwyblob	0-255 bytes		不超过255个字符的二进制数据
tiwyblob	0-255 bytes		短文本数据
blob	0-65 535 bytes		二进制形式的长文本数据
text	0-65 535 bytes		长文本数据
mediumbob	0-16 777 215 bytes		二进制形式的中等长度文本数据
mediumtext	0-16 777 215 bytes		中等长度文本数据
lomgblob	0-4 294 967 295 bytes		二进制形式的极大文本数据
lomgtetx	0-4 294 967 295 bytes		极大文本数据

日期类型

类型	大小	范围	格式	描述
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	混合日期和时间，时间戳

数值类型，字符串类型、日期时间类

b. 案例：

根据需求创建表（设计合理的数据类型、长度）

设计一张员工信息表，要求如下：

编号（纯数字）

员工工号（字符串类型，长度不超过10位）

员工姓名（字符类型，长度不超过10位）

性别（男/女,存储一个汉字）

年龄（正常人年龄，不可能存储负数）

身份证号（二代身份证号均为18位，身份证中有x这样的字符）

入职时间（取值年月日即可）
1
2
3
4
5
6
7
8
9
create table employee (
    id int comment '编号',
    worknumber var(10) comment '工号',
    name var var(10) comment '姓名',
    gender char(1) comment '年龄',
    age tinyint unsigned comment '年龄',
    idcard char(18) comment '身份证号',
    onboarding_date date comment '入职时间'
    ) comment '员工表' ;

SQL-DML（操作）

DML-添加数据

给指定字段添加数据
INSERT INTO 表名(字段名1,字段名2, . . . ) VALUES (值1,值2, . . . );
给全部字段添加数据
INSERT INTO 表名 VALUES(值1,值2,. . . ) ;
批量添加数据

1
2
3

INSERT INTO 表名(字段名1,字段名2, ．. .)VALUES(值1, 值2, . . . ),(值1,值2, . . . ),(值1,值2, . . . );  

INSERT INTO 表名 VALUES(值1 ,值2, . . . ), (值1 ,值2, . . . ), (值1 ,值2, . . . );

注意：
插入数据时，指定的字段顺序需要与值的顺序是一一对应的。
字符串和日期型数据应该包含在引号中。
插入的数据大小，应该在字段的规定范围内
DML-修改数据：
UPDATE 表名 SET 字段名1=值1, 字段名2=值2,… [WHERE 条件];

注意：修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

案例：
- 修改id为1的数据，将name修改为itname
  update employee set name = 'itname' where id=1;
- 修改id为1的数据，将name修改为小昭，gender修改为女
  update employee set name = '小昭', gender = '女' where id=1;
- 將所有的员工人职日期修改为 2008-01-01
  update employee set entrydate = '2008-01-01';

DML-删除数据

DELETE FROM 表名 [WHERE 条件]

注意：

DELETE语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。

DELETE语句不能删除某一个字段的值（可以使用UPDATE）。

SQL-DQL（查询）

1. DQL-语法

编写顺序：

-- sql语句 --       --顺序--

SELECT                |1  
  字段列表  
FROM                  |2  
  表名列表  
WHERE                 |3  
  条件列表  
GROUP BY              |4  
  分组字段列表  
HAVING                |5  
  分组后条件列表  
ORDER BY              |6  
 排序字段列表  
LIMIT                 |7  
  分页参数

2. DQL-基本查询

查询多个字段

1
2
3

SELECT 字段1, 字段2，字段3 . . . FROM 表名 ;
或
SELECT * FROM 表名

案例：
- 查询指定字段 name, workno, age 并返回值
  select name, workno, age from employee ;
- 查询所有字段，并返回值
  select * from employee ;

设置别名
SELECT 字段1 [AS 别名1], 字段2 [AS 别名2] ;

#AS 可以省略

案例：
- 查询所有员工的工作地址，并起别名
  select workaddress as '工作地址' from employee ;

去除重复记录
SELECT DISTINCT 字段列表 FROM 表名 ;

案例：
- 查询公司员工的上班地址（去除重复的值）
  select distinct workaddress from employee ;

3. DQL-条件查询

语法:
SELECT 字段列表 FROM 表名 WHERE 条件列表 ;
条件:

比较运算符	功能
`>`	大于
`>=`	大于等于
`<`	小于
`<=`	小于等于
`=`	等于
`<>` 或 `!=`	不等于
`BETWEEN … AND…`	在某个范围之内（含最小、最大值）
`IN(…)`	在in之后的列表中的值，多选一
`LIKE`	占位符模糊匹配（’ _ ‘匹配单个字符，’％’匹配任意个字符）
`IS NUL`L	是NULL（空值）

逻辑运算符	功能
`AND 或 &&`	并且（多个条件同时成立）
`OR 或 \|\|`	或者（多个条件任意一个成立）
`NOT 或！`	非，不是

案例：
- 查询年龄等于88的员工
  select * from emplyoee where age = 88;
- 查询年龄小于20的员工信息
  selelct * from employee where age < 20;
- 查询年龄小于等于28的员工信息
  selelct * from employee where age <= 28;
- 查询没有身份证号的员工信息
  select * from employee where idcard is null;
- 查询有身份证号的员工信息
  select * from employee where iadcard is not null;
- 查询年龄不等于88的员工信息
  - select * from employee where age != 88;
  - select * from employee where age <> 88;
- 查询年龄在15岁〔包含〕到20岁〔包含〕之间的员工信息
  - select * from employee where age between 15 and 20;
  - select * from employee where age >= 15 and age <= 20;
  - select * from employee where age >= 15 && age <= 20;
- 查询性别为女且年龄小于25岁的员工信息
  select * from employee where age < 25, and gender = '女' ;
- 查询年龄等于18或20或40的员工信息
  - select * from employee where age in (18, 20, 40);
  - select * from employee where age = 18 or age = 20 or age = 40 ;
- 查询姓名为两个字的员工信息
  select * from employee where name like '__' ;
- 查询身份证号最后一位是x的员工信息
  select * from employee where idcard like '%x' ;

4. DQL-聚合函数

介绍：
将一列数据作为一个整体，进行纵向计算。
语法：
SELECT 聚合函数(字段列表) FROM 表名;

注意：
null值不参与所有的聚合函数运算
常见聚合函数：

函数	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

案例：

统计该企业的员工数量
- select count(*) from employee ;
- select count(idcard) from employee ;
统计改企业员工的平均年龄
select avg(age) from employee ;
统计该企业员工的最大年龄
select max(age) from employee ;
统计该企业员工的最小年龄
select min(age) from employee ;
统计西安地区员工的年龄之和
select count(age) from employee where workaddress = '西安';

5. DQL-分组查询

语法：
SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];

注意:
执行顺序：where > 聚合函数 > having。
分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。
where与having区别：
- 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
- 判断条件不同：where不能对聚合函数进行判断，而having可以。
案例：
- 根据性别分组，统计男性员工和女性员工的数量
  select gender, count(*) from employee group by gender;
- 根据性别分组，统计男性员工和女性员工的平均年龄
  select gender, avg(age) from employee group by gender;
- 查询年龄不小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址
  select workaddress, count(workaddress) from employee where age>=45 group by workaddress having count(*) >= 3;

6. DQL-排序查询:

语法:
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;
排序方式:
- ASC: 升序（默认值）
- DESC: 降序
注意：
如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。
案例：
- 根据年龄对公司的员工进行升序排序
  select * from employee oder by age asc;
- 根据入职时间对员工进行降序排序

select * from employee oder by entrydate desc;

根据年龄对公司的员工进行升序排序，若年龄相同，再按照入职时间进行降序排序
select * from employee oder by age asc, entrydate desc;

7. DQL-分页查洵

语法：
SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;
注意:
- 起始索引从0开始，起始索引=（查询页码-1)*每页显示记录数。
- 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
- 如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10。
案例：
- 查询第1页员工数据，每页展示10条记录
  - select * from employee limit 0,10 ;
  - select * from employee limit 10 ;
- 查询第2页员工数据，每页展示10条记录
  select * from employee limit 10,10 ;
  
  (起始索引=（查询页码-1)*每页显示记录数)
DQL-案例练习：
- 查询年龄为20，21，22，23岁的女性员工信息。

select * from employee where gender = '女' and age in (20, 21, 22, 23);

查询性别为男，并且年龄在20-40岁〔含〕以内的姓名为三个字的员工。
select * from employee where gender = '男' and age between 20 and 40 and name like '___' ;
统计员工表中，年龄小于60岁的，男性员工和女性员工的人数
select gender, count(*) from employee where age < 60 group by gender ;
查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同，按入职时间降序排序。
select age, name from employee where age <= 35 order by age asc, entrydate desc;
查询性别为男，且年龄在20-40岁，含以内的前五个员工信息，对查询的结果按照年龄进行升序排序，若年龄相同，按入职时间升序排序
select * from employee where gender = '男' and age between 20 and 40 oder by age asc, entrydate asc limit 0,5;

DQL-执行顺序：
  FROM 1
          表名列表
  WHERE 2
          条件列表
  GROUP BY 3
          分组字段列表
  HAVING 4
          分组后条件列表
  SELECT 5
          字段列表
  ORDER BY 6
          排序字段列表
  LIMIT 7
          分页参数

SQL-DCL（控制）

1. DCL-用户管理

查询用户

USE mysql;
SELECT * FROM user;

创建用户

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_name_password BY '新密码';
删除用户
DROP USER '用户名'@'主机名' ;

注意：
主机名可以使用%通配
这类sql开发人员操作的比较少，主要是DBA（database administrator 数据库管理员）使用

2. DCL-权限控制

MySQL中定义了很多种权限，但是常用的就以下如表

权限	说明
ALL,ALL	PRIVILEGES 所有权限
SELECT	查询数据
INSERT	插入数据
UPDATE	修改数据
DELETE	删除数据
ALTER	修改表
DROP	删除数据库/表/视图
CREATE	创建数据库/表

查询权限:
SHOW GRANTS FOR '用户名'@'主机名';

授予权限:
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

撤销权限:
REVOKE 权限列表 0N 数据库名.表名 FROM '用户名'@'主机名';

注意事项：
多个权限之间，使用逗号分隔
授权时，数据库名和表名可以使用* 进行通配，代表所有