SQL结构化查询语言

一、什么是SQL？

全称 Structured Query Language ，结构化查询语言。操作关系型数据库的编程语言，定义了  

一套操作关系型数据库统一 标准 。

二、通用语法：

1.SQL 语句可以单行或多行书写，以分号结尾。

2. MySQL 数据库的 SQL 语句不区分大小写，关键字可使用大写。

3. 注释：

单行注释：-- 注释内容 或 # 注释内容

多行注释： /* 注释内容 */

三、分类：

根据SQL的功能，可分为四类：

1.DDL 数据定义语言，用来定义数据库对象（数据库，表，字段）。

2.DML 数据操作语言 用来对数据库中表的数据记录进行更新。

3.DQL 数据查询语言 用来查询数据库中表的记录。

4.DCL 数据控制语言 用来管理数据库用户、控制数据库的访问权限。

四、SQL语言：

（一）DDL：

1.数据库操作：

--查询所有数据库

show databases;

--查询当前数据库

select databases;

--创建数据库

create database [ if not exists ] 数据库名 [ default charset 字符集 ] [ collate 排序规则 ] ;

--删除数据库

drop database [ if exists ] 数据库名 ;

--切换数据库

use 数据库名 ;

2.表操作

--查询当前数据库所有表

show tables;

例如：查看gaga数据库中的所有表

use gaga;

show tables;

--查看指定表结构

desc 表名;

（可以查看到指定表的字段，字段的类型、是否可以为NULL，是否存在默认值等信息。）

--创建表结构

CREATE TABLE 表名(

字段1 数据类型 [ COMMENT 字段1注释 ],

字段2 数据类型 [COMMENT 字段2注释 ],

字段3 数据类型 [COMMENT 字段3注释 ],

......

字段n 字段n类型 [COMMENT 字段n注释 ]

) [ COMMENT 表注释 ] ;

例如：

create table tb_user(

id int comment '编号',

name varchar(50) comment '姓名',

age int comment '年龄',

gender varchar(1) comment '性别'

) comment '用户表';

3.数据类型

--数值类型

TINYINT         1byte (-128，127) (0，255) 小整数值

SMALLINT      2bytes (-32768，32767) (0，65535) 大整数值

MEDIUMINT     3bytes (-8388608，8388607) (0，16777215)大整数值

INT/INTEGER         4bytes 大整数值

BIGINT         8bytes 极大整数值

FLOAT         4bytes 单精度浮点数值

DOUBLE         8bytes 双精度浮点数值

DECIMAL=DOUBLE

--字符串类型

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         极大文本数据

--日期类型

DATE

TIME

YEAR

DATATIME

TIMESTAMP

4. 表操作——修改

--添加字段

ALTER TABLE 表名 ADD 字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];

例如：

为emp表增加一个新的字段”昵称”为nickname，类型为varchar(20)

ALTER TABLE emp ADD nickname varchar(20) COMMENT '昵称';

--修改数据类型

ALTER TABLE 表名 MODIFY 字段名 新数据类型 (长度);

--修改字段名和字段类型

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];

例如：将emp表的nickname字段修改为username，类型为varchar(30)

ALTER TABLE emp CHANGE nickname username varchar(30) COMMENT '昵称';

--删除字段

ALTER TABLE 表名 DROP 字段名;

--修改表名

ALTER TABLE 表名 RENAME TO 新表名;

```

5.表操作——删除

--删除表

DROP TABLE [ IF EXISTS ] 表名;

--删除指定表, 并重新创建表 

TRUNCATE TABLE 表名;

（二）DML：

1.添加数据

--给指定字段添加数据

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

例如：给gaga表的id,name,city字段添加数据 ；

insert into gaga(id,name,city) values(1,'Tom','北京',);

--给所有字段添加数据

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

例如：给gaga表的所有段添加数据

insert into gaga values(1,'Tom','male','20','1996-12-23','北京',83.5);

--批量添加数据

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

例如：

insert into gaga values(1,'Jerry','male','20','1996-12-23','上海',83.5),(1,'Bob','male','20','1996-12-23','长沙',83.5),(1,'Jack','male','20','1996-12-23','武汉',83.5),(1,'Lisa','female','20','1996-12-23','广州',83.5),(1,'Black','male','20','1996-12-23','北京',83.5),(1,'Scott','male','20','1996-12-23','北京',83.5);

```

2. 修改数据

UPDATE 表名 SET 字段名1 = 值1 , 字段名2 = 值2 , .... [ WHERE 条件 ] ；

例如：

将name为Jerry的员工的sid修改为2

update gaga set sid = 2 where name = 'Jerry';

将所有的员工的birth修改为2008-01-01

update gaga set birth = '2008-01-01';

3.删除数据

DELETE FROM 表名 [ WHERE 条件 ] ;

例如：

删除性别为女的员工

delete from gaga where gender = 'female';

删除所有员工

delete from gaga;

（三）DQL

1.基础查询

--查询多个字段

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

SELECT * FROM 表名 ;

例如：

查询emp表中指定字段并返回：

select name,workno,age from emp;

查询emp表中所有字段：

select * from emp;

--设置字段别名

SELECT 字段1 [ AS 别名1 ] , 字段2 [ AS 别名2 ] ... FROM 表名;

SELECT 字段1 [ 别名1 ] , 字段2 [ 别名2 ] ... FROM 表名;

例如：查询所有员工的workaddress 取别名为'工作地址'

select workaddress as '工作地址' from emp;

--去除重复记录

SELECT DISTINCT 字段列表 FROM 表名;

2.条件查询 WHERE

SELECT 字段列表 FROM 表名 WHERE 条件列表 ;

例如：

查询age在15 ~ 20之间且性别为女的员工信息

select * from emp where age >= 15 && age <= 20 and gender = '女';

select * from emp where age >= 15 and age <= 20 and gender = '女';

select * from emp where age between 15 and 20 && gender = '女';

```

3.聚合函数 count max min arg sum

count 统计数量

max 最大值

min 最小值

avg 平均值

sum 求和

注意 : NULL值是不参与所有聚合函数运算的。

SELECT 聚合函数(字段列表) FROM 表名 ;

例如：

统计员工总数量

select count(*) from emp; -- 统计的是员工总数

select count(idcard) from emp; --统计的是idcard不为NULL的员工数

统计所有员工的平均年龄--这里的'所有'不包括NULL

select avg(age) from emp;

最大年龄

select max(age) from emp;

4.分组查询 group by

SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组

后过滤条件 ];

例如：

根据性别分组 , 统计男性员工 和 女性员工的数量

select gender, count(*) from emp group by gender ;

根据性别分组 , 统计男性员工 和 女性员工的平均年龄

select gender, avg(age) from emp group by gender ;

查询年龄小于45的员工 , 并根据工作地址分组 , 获取员工数量大于等于3的工作地址

select workaddress, count(*) address_count from emp where age < 45 group by workaddress having address_count >= 3;

 --where与having区别：

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

之后对结果进行过滤。

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

5. 排序查询 order only

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2 ;

例如：

根据年龄对公司的员工进行升序排序 , 年龄相同 , 再按照入职时间进行降序排序

select * from emp order by age asc , entrydate desc;

排序方式：

ASC : 升序(默认值)

DESC: 降序

注意事项：

如果是升序, 可以不指定排序方式ASC ;

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

6.分页查询 limit

SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数 ;

例如:

查询第1页员工数据, 每页展示10条记录

select * from emp limit 0,10;

查询第2页员工数据, 每页展示10条记录

select * from emp limit 10,10;

7.DQL语法的编写顺序和执行顺序

执行顺序       编写顺序：

FROM                SELECT

WHERE               FROM

GROUP BY            WHERE

HAVING              GROUP BY

SELECT              HAVING

ORDER BY            ORDER BY

LIMIT               LIMIT

（四）DCL

1.管理用户

--查询用户

select * from mysql.user;

--创建用户

CREATE USER 'gaga'@'%' IDENTIFIED BY '123456';

--修改用户密码

ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码' ;

--删除用户

DROP USER '用户名'@'主机名' ;

DROP USER 'gaga'@'%' ;

2. 权限控制

-- 查询权限

SHOW GRANTS FOR 'gaga'@'%' ;

-- 授予权限

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

GRANT all ON *.* TO 'gaga'@'%';

-- 撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';