SQL基础教程：从创建到查询的全面解析-优快云博客

本文链接：https://blog.youkuaiyun.com/stupid_dernier/article/details/115439072

SQL基础语法

什么是SQL

什么是SQL
- Structured Query Language：结构化查询语言
- 其实就是定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方，称为“方言”

SQL通用语法

SQL语句可以单行或多行书写，以分号结尾。
可使用空格和缩进来增强语句的可读性。
MySQL数据库的SQL语句不区分大小写，关键字建议使用大写
```
	SELECT * FROM student;
```

3种注释

	-- 单行注释
	# 单行注释
	 /*多行注释 */

SQL分类

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

DDL：操作数据库、表

操作数据库：CRUD
- C：Create：创建
  - 创建数据库：
```
Create  database 数据库名称；
```
  - 创建数据库，判断不存在，再创建：
```
Create database if not exists 数据库名称；
```
  - 创建数据库，并制定字符集
```
Create database 数据库名称 character set 字符集名；
```
  - 练习：
    - 创建db4数据库，判断是否存在，并制定字符集为gbk
```
Create database if not exists db4 character set gbk;
```
- R：Retrieve：查询
  - 查询所有数据库的名称；
    - Show databases;
  - 查询某个数据库的字符集：查询某个数据库的创建语句
    - Show create database 数据库名称
- U：Update：修改
  - 修改数据库的字符集
    - Alter database 数据库名称 character set 字符集名称；
- D：Delete：删除
  - 删除数据库
    - Drop database 数据库名称；
  - 判断数据库存在，存在再删除
    - Drop database if exists db3;
  - 查询数据库
    - 查询当前正在使用的数据库名称
      - Select database();
  - 使用数据库
    - Use 数据库名称；
操作表：CRUD
- C（Create）：创建
  - 语法
```
Create table 表名(
		列名1 数据类型1，列名2 数据类型2，….，列名n 数据类型n
);
# 注意：最后一列，不需要加逗号（,）
```
  - 数据库类型
    - int：整数类型
    - double：小数类型
    - 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个字符张三两个字符
  - 创建表
```
create table student(
	id int,
	name varchar(32),
	age int,
	score double(4,1),
	birthday date,
	insert_time timestamp
);
```
  - 复制表
    - Create table 表名 like 被复制的表名;
- R（Retrieve）：查询
  - 查询某个数据库中所有的表名称
    - Show tables；
  - 查询表结构
    - Desc 表名
- U（Update）：修改
  - 修改表名
    - Alter table 表名 rename to 新的表名；
  - 修改表的字符集
    - Alter table 表名 character set 字符集名称
  - 添加一列
    - Alter table 表名 add 列名数据类型
  - 修改列的名称类型
    - Alter table 表名 change 列名新列名新数据类型;
    - Alter table 表名 modify 列名新数据类型;
  - 删除列
    - Alter table 表名 drop 列名;
- D（Delete）：删除
  - 删除表
    - Drop table 表名；
      - Drop table if exists 表名；
客户端图形化工具：SQLYog

DML：增删改表中数据

添加数据
- 语法
  - Insert into 表名(列名1，列名2，….，列名n) values (值1，值2，值3…);
- 注意：
  - 列名和值要一一对应
  - 如果表名后，不定义列名，则默认给所有列添加值（不能少列）
    ® Insert into 表名 values(值1,值2,…值n);
删除数据
- 语法
  - Delete from 表名 [where 条件]
- 注意：
  - 如果不加条件，则删除表中所有数据
  - （即使有这样的需求，也不建议这样做，而推荐使用turncate）
    - 因为delete from 表名；效率很低，有多少条记录就会执行多少次删除操作。
    - Truncate table 表名; 效率更高，先删除表，然后再创建一张一样的表。
修改数据
- 语法
  - Update 表名 set 列名1 = 值1, 列名2 = 值2, … where 条件;
- 注意
  - 如果不加条件，则会将表中所有记录全部修改。

DQL：查询表中的记录

Select * from 表名;

语法

Select 
	□  字段列表 
from 
	□ 表名列表 
where 
	□ 条件列表 
group by 
	□ 分组字段
having 
	□ 分组之后的条件 
order by
	□ 排序
Limit
	□ 分页限定

基础查询
- 多个字段的查询
  - Select 字段名1,字段名2…from 表名;
  - 注意：
    - 如果查询所有字段，则可以使用*来替代字段列表。
- 去除重复
  - distinct
- 计算列
  - 一般可以使用四则运算计算一些列的值。（一般只会进行数值型的计算）
  - Ifnull(表达式1,表达式2): null 参与的运算，计算结果都为null
    - 表达式1：哪个字段需要判断是否为null
    - 表达式2：如果该字段为null后的替换值
- 起别名
  - As （as也可以省略）
条件查询
- Where 子句后跟条件
- 运算符
  - > < <= >= = <>（不等于号，等同于!=）
  - Between…and
  - In(集合)
  - Like ：模糊查询
    - 占位符：
      - _：单个任意字符
      - %：任意个任意字符
  - Is null
  - And 或 &&
  - Or 或 ||
  - Not 或！

DQL：查询语句

排序查询
- 语法：
  - Order by 子句
    - Order by 排序字段1 排序方式1，排序字段2 排序方式2…
  - 排序方式
    - ASC：升序，默认的。
    - DESC：降序。
  - 注意：
    - 如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件。
聚合查询
- 解释
  - 将一列数据作为一个整体，进行纵向的计算。
  - Count: 计算个数
    - 一般选择非空的列：主键
    - Count(*):不推荐使用
  - Max:计算最小值
  - sum：计算和
  - avg：计算平均值
- 注意
  - 聚合函数的计算，会排除null值
    - 解决方案
      - 选择不包含空值的列
      - ifnull函数
分组查询：
- 语法：group by 分组字段;
- 注意：
  - 分组之后查询的字段
    - 分组字段、聚合函数
  - where和having的区别?
    - Where 在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来。
    - Where 后不可以跟聚合函数，having可以进行聚合函数的判断
分页查询
- 语法：
  - Limit 开始的索引，每页查询的条数
- 公式：
  - 开始的索引=（当前的页码-1）*每页显示的条数
- limit操作是mysql的一个“方言”（只能在mysql中使用）

约束

概念

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

分类

主键约束：primary key
- 注意
  - 含义：非空且唯一
  - 一张表只能有一个字段为主键
  - 主键就是表中记录的唯一标识
- 在创建表时，添加主键约束
- 删除主键
  - Alter table stu drop primary key;
- 创建表完后，添加主键
  - Alter table stu modify id int primary key;
- 自动增长：
  - 概念：如果某一列是数值类型的，使用auto_increment可以来完成值的自动增长
  - 在创建表时，添加主键约束，并且完成主键自增长
    - Create table stu(id int primary key auto_increment,name varchar(20));
  - 删除自动增长
    - Alter table stu modify id int;
  - 添加自动增长
    - Alter table stu modify id int auto_increment;
非空约束：not null
- 创建表时添加约束
  - Create table stu( id int,name varchar(20) not nul);
- 创建表完后，添加非空约束
  - Alter table stu modify name varchar(20) not null;
- 删除name的非空约束
  - Alter table stu modify name varchar(20);
唯一约束：unique：某一列的值不能重复
- 创建表时添加唯一约束
  CREATE TABLE stu(
  id INT,
  phone_number VARCHAR(20) UNIQUE
  );
- 删除唯一约束
  - ALTER TABLE stu DROP INDEX phone_number;
- 创建表完后，添加唯一约束
  - ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;
- 注意：
  - 唯一约束可以有null值，但是只能有一条记录为null
外键约束：foreign key ，让表与表产生关系，从而保证数据的正确性
- 在创建表时，可以添加外键
  - 语法：
    - Create table 表名(
    - …
    - 外键列,
    - Constraint 外键名称 foreign key (外键列名称) references 主表名称（主表列名称）
    - );
- 删除外键
  - Alter table 表名 drop foreign key 外键名;
- 创建表之后，创建外键
  - Alter table 表名 add constraint 外键名 foreign key(外键列) references 主表名（主表列名称）
- 级联操作
  - 添加外键，并设置级联更新
    - Alter table 表名 add constraint 外键名 foreign key(外键列) references 主表名（主表列名称）on update cascade;
  - 添加外键，并设置级联删除
    - Alter table 表名 add constraint 外键名 foreign key(外键列) references 主表名（主表列名称）on delete cascade;
  - 级联分类
    - 级联更新 on update cascade;
    - 级联删除 on delete cascade
  - 注意
    - 正式开发中，级联操作有风险，需要谨慎使用

数据库的设计

多表之间的关系

一对一：
- 如：人和身份证
- 分析：一个人只有一个身份证，一个身份证只能对应一个人
- 注意：
  - 一对一关系实现，可以在任意一方添加外键指向另一方的主键。
一对多（多对一）：
- 如：部门和员工
- 分析：一个部门有多个员工，一个员工只能对应一个部门
多对多：
- 如：学生和课程
- 分析：一个学生可以选择很多门课程，一个课程也可以被很多学生选择
- 注意
  - 多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键。
多表关系-案例
- 创建复合主键
  - Primary key(键名1，键名2，…)
  - Foreign key(键名1) references 主表1(主表列),
  - Foreign key(键名2) references 主表2(主表列）
  - );

数据库设计的范式

概念：设计数据库时，需要遵循的一些规范。
- 设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。
- 目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式（4NF）和第五范式（5NF，又称完美范式）。
  § 分类：
- 第一范式（1NF）：每一列都是不可分割的原子数据项。
- 第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）
  - 函数依赖：
    - A–>B，如果通过A属性（属性组）的值，可以确定唯一B属性的值。则称B依赖于A。
      } 例如：学号–>姓名（学号，课程名称）–>分数
  - 完全函数依赖：
    - A–>B ,如果A是一个属性组，则B属性值的确定需要依赖于A属性组中所有的属性值。
      } 例如：（学号，课程名称）–>分数
  - 部分函数依赖：
    - A–>B，如果A是一个属性组，则B属性值的确定只需要依赖于A属性组中某一些值即可。
      } 例如：（学号，课程名称）–> 姓名
  - 传递函数依赖：A–>B，B–>C. 如果通过A属性（属性组）的值，可以确定唯一B属性的值，在通过B 属性（属性组）的值可以确定唯一C属性的值，则称C传递依赖于A
    - 例如：学号–>系名，系名–>系主任
  - 码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性（属性值）为该表的码
    - 例如：该码中码为：（学号，课程名称）
    - 主属性：码属性组中的所有属性
    - 非主属性：除码属性组外的所有属性
- 第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其他非主属性（在2NF基础上消除传递依赖）

数据库的备份和还原

命令行：
- 语法：
  - 备份：mysqldump -u 用户名 -p 密码数据库名 > 保存的路径
  - 还原：
    - 登录数据库
    - 创建数据库
    - 使用数据库
    - 执行文件。 Source 文件路径
图形化工具

多表查询的分类

内连接查询

隐式内连接：
- 使用where条件消除无用数据
显式内连接：
- 语法：select 字段列名 from 表名1 [inner] join 表名2 on 条件;
注意
- 从哪些表中查询数据
- 条件是什么
- 查询哪些字段

外连接查询

左外连接：
- 语法：select 字段列表 from 表1 left [outer] join 表2 on 条件;
- 查询的是左表所有数据以及其与右表的交集部分
右外连接
- 语法：select 字段列表 from 表1 right [outer] join 表2 on 条件;
- 查询的是右表所有数据以及其与左表的交集部分

子查询

概念：查询中嵌套查询，称嵌套查询为子查询
- 查询工资最高的员工信息
  - 查询最高的工资是多少 9000
    - Select max（salary）from emp;
  - 查询员工信息，并且工资等于9000的
    - Select * from emp where emp.salary = 9000;
  - 一条sql就完成这个操作。子查询
    - Select * from emp where emp.salary = (select max(salary) from emp);
子查询的不同情况
- 子查询的结果是单行单列的：
  - 子查询可以作为条件，使用运算符去判断。运算符： >、 >= 、<、 <=、 = 、<>
- 子查询的结果是多行单列的：
  - 子查询可以作为条件，使用运算符 in 来判断
- 子查询的结果是多行多列的：
  - 子查询可以作为一张虚拟表参与查询。
  - 这种情况可以替换使用普通内连接

事务

事务的基本介绍

概念：
- 如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。
操作：
- 开启事务：start transaction;
- 回滚：rollback;
- 提交：commit;
注意

事务提交的两种方式：

自动提交
- MySQL数据库中事务默认自动提交
  - 一条DML（增删改）语句会自动提交一次事务。
手动提交：
- Oracle 数据库中事务默认手动提交
  - 需要先开启事务，再提交
修改事务的默认提交方式
- 查看事务的默认提交方式
  - Select @@autocommit;
    - 1 代表自动提交
    - 0 代表手动提交
  - Set @@autocommit = 0/1;

事务的四大特征

原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败。
持久性：当事务提交或回滚后，数据库会持久化的保存数据。
隔离性：多个事务之间，相互独立。
一致性：事务操作前后，数据总量不变。
○ 事务的隔离级别（了解）
概念：多个事务之间是隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。
存在问题：
- 脏读：一个事务，读取到另一个事务中没有提交的数据
- 不可重复读（虚读）：在同一个事务中，两次读取到的数据不一样
- 幻读：一个事务操作（DML）数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。
隔离级别：
- Read uncommitted：读未提交
  - 产生的问题：脏读、不可重复读、幻读
- Read committed：读已提交（Oracle默认）
  - 产生的问题：不可重复读、幻读
- Repeatable read：可重复读（MySQL 默认）
  - 产生的问题：幻读
- serializable：串行化
  - 可以解决所有问题
- 注意：
  - 隔离级别从小到大安全性越来越高，但是效率越来越低
- 数据库查询隔离级别
  - Select @@tx_isolation;
- 数据库设置隔离级别
  - Set global transaction isolation level 级别字符串;

演示DCL

缩写分类
- DDL：操作数据库和表
- DML：增删改表中数据
- DQL：查询表中数据
- DCL：管理用户，授权
- DBA：数据库管理员

管理用户

添加用户
- 语法：create user ‘用户名’@‘主机名’ IDENTIFIED BY ‘密码’;
删除用户
- 语法：DROP USER ‘用户名’@‘主机名’;
修改用户密码
- UPDATE USER SET PASSWORD = PASSWORD(‘新密码’) WHERE USER =‘用户名’;
- SET PASSWORD FOR ‘用户名’@‘主机名’ = PASSWORD(‘新密码’);
- mysql中忘记了root用户的密码
  - Cmd --> net stop mysql 停止mysql服务
    } 需要管理员权限
  - 使用无验证方式启动mysql服务：mysqld --skip-grant-tables
  - 打开一个新的cmd窗口，直接输入mysql命令回车，就可以登陆成功
  - Use mysql;
  - Update user set password = password(‘你的新密码’) where user = ‘root’;
  - 关闭两个窗口
  - 打开任务管理器，手动结束mysqld.exe的进程
  - 启动mysql服务
  - 使用新密码登陆
查询用户：
- 切换到mysql数据库
  - Use mysql；
- 查询user表
  - Select * from user;

权限管理

查询权限：
- Show grants for ‘用户名’@‘主机名’;
授予权限
- Grant 权限列表 on 数据库名.表名 to ‘用户名’@‘主机名’;
- 权限列表：
  - SELECT
  - DELETE
  - UPDATE
  - ALL：所有权限
  - . ：所有表
撤销权限
- 语法：
  - Revoke 权限列表 on 数据库名.表名 from ‘用户名’@‘主机名’;