统一的标准语言SQL

英文：Structured Query Language，简称 SQL，结构化查询语言
操作关系型数据库的编程语言
定义操作所有关系型数据库的统一标准，可以使用SQL操作所有的关系型数据库管理系统，以后工作中如果使用到了其他的数据库管理系统，也同样的使用SQL来操作
对于同一个需求，每一种数据库操作的方式可能会存在一些不一样的地方，我们称为“方言”

SQL通用语法

SQL 语句可以单行或多行书写，以分号结尾。
可使用空格和缩进来增强语句的可读性。 //库名、表名后空格起别名
MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写。
3 种注释
- 单行注释: -- 注释内容或 # 注释内容(mysql 特有) Tip：--（空格），空格不能省略
- 多行注释: /* 注释 */

SQL数据类型

分为数值类型、日期和时间类型、字符串类型三大类。

int：整数类型
- age int
double:小数类型
- score double(5,2)，表示最多五位数字，其中最多两位小数位
date:日期，只包含年月日，yyyy-MM-dd
datetime:日期，包含年月日时分秒 yyyy-MM-dd HH:mm:ss
timestamp:时间戳类型包含年月日时分秒 yyyy-MM-dd HH:mm:ss【空间能表示的日期有限】
- 如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间，来自动赋值
varchar：可变字符串；存储性能低节约空间
- name varchar(20):姓名最大20个字符
- 则输入zhangsan ——8个字符；张三 ——2个字符

SQL的分类

Tip

注意：以后我们最常操作的是 DML 和 DQL ，因为我们开发中最常操作的就是数据。其实数据库的操作和方法都是CRUD围绕增删改查

DDL(Data Definition Language)数据定义语言
用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等
DML(Data Manipulation Language)数据操作语言
用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等
DQL(Data Query Language)数据查询语言
用来查询数据库中表的记录(数据)。关键字：select, where 等
DCL(Data Control Language)数据控制语言(了解)
用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE 等

DDL:操作数据库、表

操作数据库

1. C(Create):创建

创建数据库：

create database 数据库名称;

创建数据库，判断不存在，再创建：

create database if not exists 数据库名称;

创建数据库，并指定字符集

create database 数据库名称 character set 字符集名;

练习：创建db4数据库，判断是否存在，并制定字符集为gbk

create database if not exists db4 character set gbk;

2. R(Retrieve)：查询

查询所有数据库的名称:

show databases;

查询某个数据库的字符集:查询某个数据库的创建语句

show create database 数据库名称;

3. U(Update):修改

修改数据库的字符集

 alter database 数据库名称 character set 字符集名称;

4. D(Delete):删除

删除数据库

drop database 数据库名称;

判断数据库存在，存在再删除v

drop database if exists 数据库名称;

5. 使用数据库

查询当前正在使用的数据库名称

select database();

使用数据库

use 数据库名称;

操作表

1. C(Create):创建

1. 语法：
			create table 表名(
				列名1 数据类型1,
				列名2 数据类型2,
				....
				列名n 数据类型n
			);
			* 注意：最后一列，不需要加逗号（,）


2. 示例：
	- 创建表
			create table student(
				id int,
				name varchar(32),
				age int ,
				score double(4,1),
				birthday date,
				insert_time timestamp
			);
	- 复制表：
			* create table 表名 like 被复制的表名;

2. R(Retrieve)：查询

查询某个数据库中所有的表名称

show tables;

查询表结构

desc 表名;                              //命令（command）中用于表名前的DESC是describe的缩写,描述

3. U(Update):修改

修改表名

alter table 表名 rename to 新的表名;

修改表的字符集

alter table 表名 character set 字符集名称;

添加一列

alter table 表名 add 列名 数据类型;

修改列名称类型

alter table 表名 change 列名 新列别 新数据类型;
alter table 表名 modify 列名 新数据类型;

删除列

alter table 表名 drop 列名;

4. D(Delete):删除

drop table 表名;
drop table  if exists 表名 ;

DML：增删改表中数据

添加数据

给指定列添加数据

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

给全部列添加数据

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

批量添加数据

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

删除数据

delete from 表名 [where 条件]

注意事项：
1. 如果不加条件，则删除表中所有记录。
2. 如果要删除所有记录
	- delete from 表名; -- 不推荐使用。有多少条记录就会执行多少次删除操作
	- TRUNCATE TABLE 表名; -- 推荐使用，效率更高 先删除表，然后再创建一张一样的表。

修改数据

update 表名 set 列名1 = 值1, 列名2 = 值2,... [where 条件];
注意：如果不加任何条件，则会将表中所有记录全部修改。

DQL：查询表中的记录【单表查询】

查询的完整语法：

SELECT 
    字段列表
FROM 
    表名列表 
WHERE 
    条件列表
GROUP BY
    分组字段
HAVING
    分组后条件
ORDER BY
    排序字段
LIMIT
    分页限定

基础查询

语法

查询多个字段

SELECT 字段列表 FROM 表名;
SELECT * FROM 表名; -- 查询所有数据

去除重复记录

SELECT DISTINCT 字段列表 FROM 表名;

起别名

AS: AS 也可以省略

练习

查询name、age两列

select name,age from stu;

查询所有列的数据，列名的列表可以使用`*替代

select * from stu;

我们写字段列表的话，可以添加注释对每一个字段进行说明这是公司的规范。以免养成不好的习惯不要写*,因为不方便我们阅读sql语句。【书写习惯：先在多行注释里写好分析，在快速用*代替字段写好模板，然后根据需求替换上具体要查询的字段】

查询地址信息

select address from stu;

去除重复记录

select distinct address from stu;

查询姓名、数学成绩、英语成绩。并通过as给math和english起别名（as关键字可以省略）

select name,math as 数学成绩,english as 英文成绩 from stu;  
select name,math 数学成绩,english 英文成绩 from stu;

条件查询【含模糊查询】

语法

SELECT 字段列表 FROM 表名 WHERE 条件列表;
条件列表可以使用以下运算符：

练习

查询年龄大于20岁的学员信息

select * from stu where age > 20;

查询年龄大于等于20岁的学员信息

select * from stu where age >= 20;

查询年龄大于等于20岁并且年龄小于等于 30岁的学员信息
```
select * from stu where age >= 20 &&  age <= 30;
select * from stu where age >= 20 and  age <= 30;
```
上面语句中 && 和 and 都表示并且的意思。建议使用 and 。

也可以使用 between ... and 来实现上面需求
```
select * from stu where age BETWEEN 20 and 30;
```

查询入学日期在'1998-09-01' 到 '1999-09-01' 之间的学员信息

select * from stu where hire_date BETWEEN '1998-09-01' and '1999-09-01';

查询年龄等于18岁的学员信息

select * from stu where age = 18;

查询年龄不等于18岁的学员信息

select * from stu where age != 18;
select * from stu where age <> 18;

查询年龄等于18岁或者年龄等于20岁或者年龄等于22岁的学员信息

select * from stu where age = 18 or age = 20 or age = 22;
select * from stu where age in (18,20 ,22);

查询英语成绩为 null的学员信息

null值的比较不能使用 = 或者 != 。需要使用 is 或者 is not

select * from stu where english = null; -- 这个语句是不行的
select * from stu where english is null;
select * from stu where english is not null;

模糊查询

模糊查询使用like关键字，可以使用通配符进行占位:

（1）_ : 代表单个任意字符

（2）% : 代表任意个数字符

查询姓'马'的学员信息

select * from stu where name like '马%';

查询第二个字是'花'的学员信息

select * from stu where name like '_花%';

查询名字中包含 '德' 的学员信息

select * from stu where name like '%德%';

排序查询

语法

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

上述语句中的排序方式有两种，分别是：

ASC ：升序排列 （默认值）
DESC ：降序排列

注意：如果有多个排序条件，当前边的条件值一样时，才会根据第二条件进行排序

练习

查询学生信息，按照年龄升序排列
```
select * from stu order by age ;
```

查询学生信息，按照数学成绩降序排列

select * from stu order by math desc ;

查询学生信息，按照数学成绩降序排列，如果数学成绩一样，再按照英语成绩升序排列
```
select * from stu order by math desc , english asc ;
```

分组查询

常用于报表

聚合函数

什么是聚合函数？
聚合函数：将一列数据作为一个整体，进行纵向计算。
聚合函数分类：

函数名	功能
count(列名)	统计数量（一般选用不为null的列）
max(列名)	最大值
min(列名)	最小值
sum(列名)	求和
avg(列名)	平均值

聚合函数语法：

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

注意：null 值不参与所有聚合函数运算【使用主键解决更nice】
		解决方案：
			1. 选择不包含非空的列进行计算（一般选择count(*)从所有列中统计最大值数量）
			2. IFNULL函数

语法

SELECT 字段列表 FROM 表名 [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤];

注意：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义

where 和 having 区别：

执行时机不一样：where 是分组之前进行限定，不满足where条件，则不参与分组，而having是分组之后对结果进行过滤。
可判断的条件不一样：where 不能对聚合函数进行判断，having 可以。

练习

查询男同学和女同学各自的数学平均分

select sex, avg(math) from stu group by sex;

注意：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义

select name, sex, avg(math) from stu group by sex;  -- 这里查询name字段就没有任何意义

查询男同学和女同学各自的数学平均分，以及各自人数
```
select sex, avg(math),count(*) from stu group by sex;
```
查询男同学和女同学各自的数学平均分，以及各自人数，要求：分数低于70分的不参与分组
```
select sex, avg(math),count(*) from stu where math > 70 group by sex;
```
查询男同学和女同学各自的数学平均分，以及各自人数，要求：分数低于70分的不参与分组，分组之后人数大于2个的
```
select sex, avg(math),count(*) from stu where math > 70 group by sex having count(*)  > 2;
```

分页查询

分页查询是将数据一页一页的展示给用户看，用户也可以通过点击查看下一页的数据。
不同的关系型数据库的分页查询语法不一致

分页查询limit是MySQL数据库的方言
oracle分真查询使用rownumber
sQL Server分页查询使用top

语法【MySQL方言】

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

公式：起始索引 = (当前页码 - 1) * 每页显示的条数

注意：上述语句中的起始索引是从0开始

练习

从0开始查询，查询3条数据
```
select * from stu limit 0 , 3;
```
每页显示3条数据，查询第1页数据
```
select * from stu limit 0 , 3;
```
每页显示3条数据，查询第2页数据
```
select * from stu limit 3 , 3;
```
每页显示3条数据，查询第3页数据
```
select * from stu limit 6 , 3;
```

应用场景

DQL小结

DCL：管理用户，授权【了解】

使用者大多为DBA：数据库管理员，全称DatabaseAdministrator

管理用户

添加用户：

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

删除用户：

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

修改用户密码：

	UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';
	UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi';

	SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');
	SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');

mysql中忘记了root用户的密码？

cmd -- > net stop mysql 停止mysql服务
* 需要管理员运行该cmd

使用无验证方式启动mysql服务： mysqld --skip-grant-tables

打开新的cmd窗口,直接输入mysql命令，敲回车。就可以登录成功

use mysql;

update user set password = password('你的新密码') where user = 'root';

关闭两个窗口

打开任务管理器，手动结束mysqld.exe 的进程

启动mysql服务

使用新密码登录。

查询用户：

	-- 1. 切换到mysql数据库
	USE myql;
	-- 2. 查询user表
	SELECT * FROM USER;

	* 通配符： % 表示可以在任意主机使用用户登录数据库

权限管理

查询权限：

	-- 查询权限
	SHOW GRANTS FOR '用户名'@'主机名';
	SHOW GRANTS FOR 'lisi'@'%';

授予权限：//通配符：权限ALL；库表*

	-- 授予权限
	grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
	-- 给张三用户授予所有权限，在任意数据库任意表上

	GRANT ALL ON *.* TO 'zhangsan'@'localhost';

撤销权限：

	-- 撤销权限：
	revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
	REVOKE UPDATE ON db3.account FROM 'lisi'@'%';