SQL分类：

• DDL(Data Definition Language) 数据定义语言

  • 用来定义数据库对象：数据库，表，列等
    • 关键字：create，frop，alter等

• DML(Data Manipulation Language) 数据操作语言

  • 用来对数据库中表的数据进行增删改
    • 关键字：insert，delete，update等

• DQL(Data Query Language) 数据查询语言

  • 用来查询数据库中表的记录(数据)
    • 关键字：select，where等

• DCL(Data Control Language) 数据控制语言（了解）

  • 用来定义数据库的访问权限和安全级别，以及创建用户
    • 关键字：GRANT，REVOKE等

1.DDL（操作数据库、表）

操作数据库：CRUD

1.C(Create)：创建

• 创建数据库：
  • create database 数据库名称;
• 创建数据库，判断如果不存在，再创建：
  • create database if not exists 数据库名称;
• 创建数据库，并指定字符集：
  • create database 数据库名称 character set 字符集名;
• 案例：创建数据库db1，判断如果不存在则创建，并指定字符集为utf-8：
  • create database if not exists db1 character set utf8;

2.R(Retrieve)：查询

• 查询所有数据库的名称：
  • show databases;
• 查询某个数据库的字符集：查询某个数据库的创建语句
  • show create database 数据库名称;

3.U(Update)：修改

• 修改数据库的字符集：
  • alter database 数据库名称 character set 字符集名称;

4.D(Delete)：删除

• 删除数据库：
  • drop database 数据库名称;
• 判断数据库是否存在，存在再删除：
  • drop database if exists 数据库名称;

5.使用数据库

• 查询当前正在使用的数据库名称：
  • select database();
• 使用数据库：
  • use 数据库名称;

操作表

1.C(Create)：创建

• 语法：

  create table 表名(
      列名1 数据类型,
      列名2 数据类型,
      ......
      列名n 数据类型
  );
  -- 注意：最后一个列名不需要加逗号
  

• 常见数据类型

  • int ：整数类型
    • age int,
  • varvhar ：字符串
    • name varchar(20)：姓名最大20个字符长度
      • zhangsan 8个字符 ； 张三 2个字符
  • double ：小数类型
    • score double(5,2)：该小数最大位数为5位，小数点后保留两位，即最大数为999.99
  • date ：日期，只包含年月日，yyyy-MM-dd
  • datetime ：日期，包含年月日时分秒，yyyy-MM-dd HH:mm:ss
  • timestamp ：时间错类型，yyyy-MM-dd HH:mm:ss
    • 如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间，来自动赋值
  • text ：长文本型
    • address text：地址最大字符数量:2的31次方 - 1个字符

• 复制表

  • create table if exists db2 like db1; 复制db1表的结构到db2表

2.R(Retrieve)：查询

• 查询某个数据库中所有的表名称
  • show tables;
• 查询表结构
  • desc 表名;

3.U(Update)：修改

• 修改表名
  • alter table 表名 rename to 新表名;
• 修改表的字符集
  • 查看表的字符集
    • show create table 表名;
  • 修改表的字符集
    • 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 表名;

2.DML(对表中数据进行增删改)

1.添加数据

• 语法：
  • insert into 表名(列名1,列名2,...,列名n) values(数据1,数据2,...,数据n);
• 注意：
  • 添加的列名个数和数据个数要一一对应
  • 如果表名后不定义具体列名，则默认给所有列添加数据

2.删除数据

• 语法：
  • delete from 表名 [where 条件];
• 注意：
  • 如果不加表后的条件，则会删除该表中的所有数据
  • 如果需要删除所有记录，有两种方式：
    • delete from 表名; -- 不推荐使用
    • truncate table 表名; -- 先删除表，然后再创建一张一模一样的表

3.修改数据

• 语法：
  • update 表名 set 列名1 = 数据1, 列名2 = 数据2, ... [where 条件];
• 注意：
  • 如果不加任何条件，则会将表中所有行(记录)全部进行修改

3.DQL(对表中数据进行查询)

查询数据

基础查询

• 多个字段的查询
  • select 字段名1,字段名2,... from 表名;
  • 注意：如果查询所有字段，则可以使用 * 来替代字段列表
• 去除重复
  • distinct 关键字
• 计算列
  • 一般可以使用四则运算计算一些列的值。（一般只会进行数值型的计算）
  • ifnull(表达式1,表达式2)
    • 表达式1：哪个字段需要判断是否为null
    • 表达式2：如果该字段为null后替换的值
• 起别名
  • as 关键字 -- as也可以省略

条件查询

• where字句后跟条件

• 运算符

  > 、 < 、 >= 、 <= 、 = 、 <>
  BETWEEN...AND
  IN(集合)
  LIKE
  	占位符：
  		_ ：单个任意字符
  		% ：多个任意字符
  IS NULL
  and 或 &&
  or 或 ||
  not 或 !
  

排序查询

• 语法：
  • order by 字句
  • order by 排序字段1 排序方式1,排序字段2 排序方式2,...,排序字段n 排序方式n
• 排序方式
  • ASC ：升序--默认的
  • DESC ：降序
• 注意
  • 如果有多个排序条件，则当前边的条件值一样时，才会判断第二个条件

聚合函数

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

• count ：计算个数
• max ：计算最大值
• min ：计算最小值
• sum ：计算总和
• avg ：计算平均值

注意：聚合函数的计算，排除null值的计算

解决方案：

1.选择不包含空值的列进行计算

2.使用IFNULL函数将空值替换

分组查询

• 语法：
  • group by 字段 [having 分组后条件];
• 注意：
  • 分组之后查询字段的要求：
    • 被分组的字段
    • 聚合函数
  • where 和 having 的区别？
    • where 在分组之前进行条件限定，如果不满足条件，则不进行后续操作
    • having 在分组之后进行条件限定，如果不满足条件，则不会被查询出来
    • where 后不可以跟聚合函数判断， having 后可以跟聚合函数的判断

分页查询

• 语法：
  • limit 开始的索引,每页查询的条数;
• 公式
  • 开始的索引 = (当前的页码 - 1) * 每页显示的条数
• 案例：-- 每页显示3条记录
  • select * from student limit (1-1)*3,3; -- 第一页
  • select * from student limit (2-1)*3,3; -- 第二页
  • select * from student limit (3-1)*3,3; -- 第三页

4.约束

对表中的数据进行限定，保证数据的正确性、有效性和完整性

1.主键约束（primary key）

非空且唯一

一张表只能有一个字段为主键

主键就是表中记录的唯一标识

• 创建表时添加约束
  • id int primary key, -- id 为主键
• 创建表之后给某列添加主键约束
  • alter table student modify id int primary key;
  • 添加主键约束前表中的记录不能有空值和重复值，若有，则添加约束失败
• 删除主键
  • alter table student drop primary key;

》》》自动增长

如果某列是数值类型的，使用 auto_increment 可以来完成值的自动增长

• 创建表时添加自动增长
  • id int primary key auto_increment, -- id 为主键
• 创建表之后给某列添加自动增长
  • alter table student modify id int primary key auto_increment;
• 删除自动增长
  • alter table student modify id int primary key;

2.非空约束（not null）

• 创建表时添加约束
  • name varchar(20) not null, -- name 为非空
• 创建表之后给某列添加非空约束
  • alter table student modify name varchar(20) not null;
  • 添加非空约束前表中的记录不能有空值，若有，则添加约束失败
• 删除非空约束
  • alter table student modify name varchar(20);

3.唯一约束（unique）

某一列的值不能重复

• 创建表时添加约束
  • phone varchar(20) unique,
• 创建表之后给某列添加唯一约束
  • alter table student modify name varchar(20) unique;
  • 添加唯一约束前表中的记录不能有重复，若有，则添加约束失败
• 删除唯一约束
  • alter table student drop index phone;
• 注意：
  • 唯一约束可以有null值，但是只能有一个记录为null值

4.外键约束（foreign key）

• 在创建表时，可以添加外键

  • 语法：

    • create table 表名{
      	...
      	外键列
      	-- 外键约束
      	constraint 外键约束名称 foreign key (外键列名称) references 外表名称(外表列名称)
      };
      

• 创建表之后给某列添加外键约束

  • alter table student add constraint 外键约束名称 foreign key (外键列名称) references 外表名称(外表列名称);

• 删除外键约束

  • alter table student drop foreign key 外键约束名称;

》》》级联操作

• 创建表之后给某列添加外键约束并设置级联更新操作
  • alter table student add constraint 外键约束名称 foreign key (外键列名称) references 外表名称(外表列名称) on update cascade;
• 创建表之后给某列添加外键约束并设置级联删除操作
  • alter table student add constraint 外键约束名称 foreign key (外键列名称) references 外表名称(外表列名称) on delete cascade;
• 创建表之后给某列添加外键约束并设置级联更新操作以及级联删除操作
  • alter table student add constraint 外键约束名称 foreign key (外键列名称) references 外表名称(外表列名称) on update cascade on delete cascade;

5.多表关系

1.多表之间的关系

》》》分类

• 一对一（了解）
  • 如：人和身份证
    • 分析：一个人只有一个身份证 ，一个身份证只能对应一个人
• 一对多（多对一）
  • 如：部门和员工
    • 分析：一个部门有多个员工，一个员工只能对应一个部门
• 多对多
  • 如：学生和课程
    • 分析：一名学生可以选择很多课程，一个课程也可以被很多名学生选择

2.数据库设计的范式

概念：设计数据库时，需要遵循的一些规范

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，

这些不同的规范要求被称为不同的范式，各种范式呈递次规范，

越高的范式，数据库冗余越小

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式（4NF）、第五范式（5NF，又称完美范式）

》》》第一范式（1NF）

每一列都是不可分割的原子数据项

》》》第二范式（2NF）

在1NF的基础上，非码属性必须完全依赖于候选码

（在1NF基础上，消除非主属性对主码的部分函数依赖）

》》》第三范式（3NF）

在2NF基础上，任何非主属性不依赖于其他非主属性

（在2NF基础上，消除传递依赖）

6.数据库的备份和还原

命令行方式

• 语法：
  • 备份
    • mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
  • 还原
    • 登录数据库
    • 创建数据库
    • 使用数据库
    • 执行文件（source 文件路径）

7.多表查询

》》》分类：

• 内连接查询
• 外连接查询
• 子查询

1.内连接查询

• 隐式内连接：使用where条件消除无用数据

  • select
    	t1.name, -- 员工表姓名
    	t1,gerder, -- 员工表性别
    	t2.name -- 部门表名称
    from
    	emp t1, -- 员工表
    	dept t2 -- 部门表
    where
    	t1.dept_id = t2.id;
    

• 显示内连接

  • select
    	t1.name, -- 员工表姓名
    	t1,gerder, -- 员工表性别
    	t2.name -- 部门表名称
    from
    	emp t1 -- 员工表
    inner join -- inner可以省略
    	dept t2 -- 部门表
    on
    	t1.dept_id = t2.id;
    

• 内连接查询思路：

  • 从哪些表中查询数据
  • 表之间的连接条件是什么
  • 查询哪些字段的数据

2.外连接查询

• 左外连接

  • 查询的是左表所有的数据以及其交集部分

  • -- 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称
    select
    	t1.*,
    	t2.name
    from
    	emp t1 -- 员工表
    left join
    	dept t2 -- 部门表
    on
    	t1.dept_id = t2.id;
    

• 右外连接

  • 查询的是右表所有的数据以及其交集部分

  • -- 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称
    select
    	t1.*,
    	t2.name
    from
    	dept t2 -- 部门表
    right join
    	emp t1 -- 员工表
    on
    	t1.dept_id = t2.id;
    

3.子查询

查询中嵌套查询，我们称被嵌套的查询为子查询

-- 1.查询最高工资是多少？ 9000
select
	max(salary)
from
	emp;
-- 2.查询员工信息，并且工资等于9000的
select
	*
from
	emp
where
	emp.salary = 9000;
-- 3.查询员工信息，并且工资是最高的
select
	*
from
	emp
where
	emo.salary = (
        select
        	max(salary)
        from
        	emp
    );

》》》子查询的不同情况：

• 1.子查询的结果是单行单列的：

  • 子查询可以作为条件，使用运算符去判断。运算符：> >= < <= <>

• 2.子查询的结果是多行单列的：

  • 子查询可以作为条件，使用运算符 in 来判断

• 3.子查询的结果是多行多列的：

  • 子查询可以作为一张虚拟表参与查询

  • -- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息
    select
    	*
    from
    	dept t1,
    	(
            select
            	*
            from
            	emp
            where
            	emp.join_date > '2011-11-11'
        ) t2
    where
    	t1.id = t2.dept_id;
    	
    -- 使用 普通内连接 实现
    select
    	*
    from
    	emp t1,
    	dept t2
    where
    	t1.dept_id = t2.id
    	and
    	t1.join_date > '2011-11-11';
    

8.事务

1.事务的基本介绍

如果一个包含多个步骤的业务操作，被事务管理，

那么这些操作要么同时成功，要么同时失败。

》》》操作：

1. 开启事务：start transaction;
2. 回滚：rollback;
3. 提交事务：commit;

MySQL数据库中事务是默认自动提交

• 事务提交的两种方式：

  • 自动提交：
    • mysql就是自动提交的
    • 一条DML(增删改)语句会自动提交一次事务
  • 手动提交：
    • Oracle 数据库默认是手动提交事务的
    • 需要先开启事务，然后提交事务

• 修改事务的默认提交方式：

  • 查看事务的默认提交方式：

    • select @@autocommit;
      

      • 1 代表自动提交事务
      • 0 代表手动提交事务

  • 修改事务的默认提交方式：

    • set @@autocommit = 0;
      

2.事务的四大特征

1. 原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败
2. 持久性：当事务提交或回滚后，数据库会持久化的保存数据
3. 隔离性：多个事务之间，相互独立
4. 一致性：事务操作前后，数据总量不变

3.事务的隔离级别（了解）

多个事务之间是隔离的，相互独立的。

但是如果多个事务操作同一批数据，则会引发一些问题，

设置不同的隔离级别就可以解决这些问题。

》》》存在的问题：

1. 脏读：一个事务，读取到另一个事务中没有提交的数据
2. 不可重复读(虚读)：在同一个事务中，两次读取到的数据不一致
3. 幻读：一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改

》》》隔离级别：（从小到大）

• 1.read uncommitted：读未提交
  • 产生的问题：脏读、不可重复读、幻读
• 2.read committed：读已提交 （Oracle 默认）
  • 产生的问题：不可重复读、幻读
• 3.repeatable read：可重复读 （MySQL 默认）
  • 产生的问题：幻读
• 4.serializable：串行化
  • 可以解决所有的问题

注意：隔离级别从小到大安全性越来越高，但效率越来越低

（所有我们要选择合适的隔离级别，既保证安全性，又保证效率性）

》》》操作：

• 数据库查询隔离级别：

  • select @@tx_isolation;
    

• 数据库设置隔离级别：

  • set global transaction isolation level 级别字符串;
    
    -- 例如
    set global transaction isolation level serializable;
    

9.DCL（定义数据库的访问权限和安全级别，以及创建用户）

DBA数据库管理员

1.添加用户

• 创建用户

  • create user '用户名'@'主机名' identified by '密码';
    -- 例如：创建一个账户为zhangsan密码为123只能在本机登录数据库的用户
    create user 'zhangsan'@'localhost' identified by '123';
    -- 例如：创建一个账户为lisi密码为123可以在任意主机登录的用户
    create user 'lisi'@'%' identified by '123';
    

2.删除用户

• 删除语法

  • drop user '用户名'@'主机名';
    -- 例如：删除刚刚创建的zhangsan用户
    drop user 'zhangsan'@'localhost';
    

3.修改用户密码

• 修改用户密码语法

  • -- 方式一：
    update user set password=password('新密码') where user='用户名';
    -- 例如：修改刚刚创建的lisi用户的密码为abc
    update user set password=password('abc') where user='lisi';
    
    -- 方式二：
    set password for '用户名'@'主机名' = password('新密码');
    -- 例如：修改刚刚创建的lisi用户的密码为abc
    set password for 'lisi'@'%' = password('abc');
    

》》》MySQL中忘记root用户密码？如下找回

• 1.以管理员的身份运行cmd输入net stop mysql 停止mysql服务
• 2.使用无验证方式启动mysql服务：mysqld --skip-grant-tables
• 3.打开新的cmd(管理员身份运行)窗口，直接输入mysql命令，敲回车。就可以登录成功了
• 4.修改root用户密码
  • use mysql;
  • update user set password=password('新密码') where user='root';
• 5.关闭两个cmd窗口
• 6.打开任务管理器，手动结束名为 mysqld.exe 的进程
• 7.启动mysql服务：net start mysql
• 8.操作完成，即可使用新密码登录成功

4.查询用户

• 切换到mysql数据库

  • use mysql;
    

• 查询user表

  • select * from user;
    

  • 通配符(%)：表示可以在任意主机上使用该用户登录数据库

5.权限管理

• 查询权限

  • show grants for '用户名'@'主机名';
    
    -- 例如：查询root用户的权限
    show grants for 'root'@'%';
    

• 授予权限

  • grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
    
    -- 例如：授予用户lisi对于数据库db1中t1表的查询和修改权限
    grant select,update on db1.t1 to 'lisi'@'%';
    -- 例如：授予用户zhangsan对于所有数据库中的所有表的所有权限
    grant all on *.* to 'zhangsan'@'localhost';
    

• 撤销权限

  • revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
    
    -- 例如：将用户lisi对于数据库db1中t1表的查询权限撤销掉
    revoke select on db1.t1 from 'lisi'@'%';