文章目录
一、SQL语句分类
| 语句名 | 语句含义 |
|---|---|
| DDL | 数据定义语言（创建、删除、修改 库或表结构） |
| DML | 数据操作语言（增删改 表数据） |
| DQL | 数据查询语言 |
| DCL | 数据控制语言（定义访问权限及安全级别） |
备忘
蠕虫复制
truncate和delete的区别
去重复 distinct
模糊查询的 % 和 _
where 和having 的区别：
where分组前按条件过滤，having分组后按条件过滤
is null 为空 is not null 不为空
in 在…范围 not in 不在…范围内
字段长度 length(字段名)
between a and b; 左开右闭。 a< * <=b
1.DDL
定义数据库
1.1创建数据库
**create database 数据库名；**
1.2删除数据库
drop database 数据库名；
1.3修改数据库
ALTER DATABASE [数据库名] ->给修改数据库的权限
{ [ DEFAULT ] CHARACTER SET <字符集名> |
[ DEFAULT ] COLLATE <校对规则名>}
#修改数据库的编码格式
alter database 数据库名 default character set utf8；
1.4查看正在使用的数据库
select database();
1.5查看创建数据库
show create database 数据库名；
1.6注意
数据库的名字不能直接改，要重新新建数据库再把数据放进去
定义表
//前提是要使用某个数据库
use 数据库名;
1.创建表
1.1创建表
create table 表名（
字段名1 数据类型（长度），
字段名2 数据类型（长度）
）;
1.2创建表结构相同的表
create table 新表名 like 旧表名；
1.3 SQL支持的数据类型
**tinyint**：很小的整数
smaillint：小的整数
mediumint：中等大的整数
int：普通大小的整数
float：单精度浮点型
double：双精度浮点型
decimal（m,d）：压缩严格的定点数
year：YYYY 1901-2155
time：HH:MM:SS
date：YYYY-MM-DD
datetime：YYYY-MM-DD HH:MM:SS
1000-01-01 00:00:00~ 9999-12-31 23:59:59
timestamp：YYYY-MM-DD HH:MM:SS
1970~01~01 00:00:01 UTC~2038-01-1903:14:07UTC
CHAR（M）:M为0-255的整数
VARCHAR（M）：M为0-255的整数
2.删除表
drop table 表名；
3.修改表结构
3.1 添加列
alter table 表名 add 列名 类型；
ALTER TABLE student ADD remark VARCHAR(20);
3.2 删除列
alter table 表名 drop 列名；
3.3 修改列名和类型
alter table 表名 CHANGE 旧名 新名 类型；
3.4 修改列类型
alter table 表名 MODIFY 列名 新类型；
3.5 修改表名
RENAME TABLE 表名 to 新表名；
3.6 修改表字符集
alter table 表名 character set utf8；
4.查看表
4.1 查看某个数据库中的所有表
SHOW TABLES;
4.2 查看表结构
DESC 表名;
4.3 查看创建表的SQL语句
SHOW CREATE TABLE 表名;
2.DML
插入表记录
insert into 表名（字段1，字段2） values（字段1值，字段2值）；
insert into 表名 values（*，*，*）；->values里要表的所有字段的对应值
蠕虫复制
//在已有数据的基础上，复制原来的数据到新表
//把student2的所有数据放进student，前提是两表结构相同
INSERT INTO student SELECT * FROM student2;
更新表记录
1. 不带条件修改数据
UPDATE 表名 SET 字段名=值;
2. 带条件修改数据
UPDATE 表名 SET 字段名=值 WHERE 字段名=值;
删除表记录
1. 不带条件删除数据 DELETE FROM 表名;
2. 带条件删除数据 DELETE FROM 表名 WHERE 字段名=值;
3. truncate删除表记录 TRUNCATE TABLE 表名;
truncate和delete的区别
【注意：】 truncate和delete的区别：
delete是将表中的数据一条一条删除
truncate是将整个表摧毁，重新创建一个新的表,新的表结构 和原来表结构一模一样
3.DQL(*重要)
字段长度查询，如：
找到名字长度为6个字符的员工信息。
SELECT * FROM emp WHERE LENGTH(ename) = 6
1. 查询所有列
SELECT * FROM 表名;
2. 查询指定列
SELECT 字段1，字段2，字段3 FROM 表名;
3. 别名查询
select 字段1 as 别名，字段2 as 别名 from 表名 as 表别名；
- 清除重复值（查询指定列且不出现重复数据）DISTINCT
SELECT DISTINCT 字段名 FROM 表名;
5. 查询结果运算
SELECT math + english FROM student;//算数据英语总成绩
6. 条件查询
| BETWEEN …AND… | 查询某一区间的值 between 50 and 100 >=50 <=100 |
|---|---|
| IN(set) | 满足集合中任意条件 in (1,2,3,4) |
| LIKE ‘张pattern’ | 模糊查询 % 任意字符 ‘%张%’ _ 一个字符 '张_' |
| IS NULL | 判断是否为空 |
| and | 多个条件同时满足 |
| or | 满足任意一个条件即可 |
| not | 除了 |
6.1 比较运算符：> < = >= <= !=
SELECT * FROM student3 WHERE math>80;
6.2 逻辑运算符: and or not
SELECT 字段名 FROM 表名 WHERE 字段 not in (数据1, 数据2...);
//in里面的每个数据都会作为一次条件，只要满足条件的就会显示
6.3 范围:
SELECT * FROM 表名 WHERE 字段名 BETWEEN 值1 and 值2
6.4 模糊查询 like:
SELECT * FROM 表名 WHERE 字段名 like '通配符字符串';
【
%: 表示0个或多个字符(任意个字符)
_: 表示一个字符
】
SELECT * FROM student3 WHERE NAME LIKE '马%';
6.5 排序 order by:
6.5.1 单列排序
SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 [ASC|DESC];
6.5.2 组合排序
SELECT 字段名 FROM 表名 WHERE 字段=值
ORDER BY 字段名1 [ASC|DESC], 字段名2 [ASC|DESC];
//先按字段1排序的基础上，按字段2排序
6.6 聚合函数 sum avg count:
五个聚合函数：
| 聚合函数名 | 说明 |
|---|---|
| COUNT | 统计指定列记录数，记录为null的不计 |
| SUM | 计算指定列的和，如果不是数值类型，计算结果为0 |
| MAX | 指定列的最大值 |
| MIN | 指定列的最小值 |
| AVG | 计算指定列的平均值，如果不是数值类型，那么计算结果为0 |
聚合函数的使用：
SELECT 聚合字段名... FROM 表名;
SELECT COUNT(age) FROM 表名;
6.7 分组 group by;
****分组：按条件进行分组，然后每组只显示一条数据；
SELECT 字段1,字段2... FROM 表名 GROUP BY 分组字段 [HAVING 条件];
【分组举例：
1.查询年龄大于25岁的人,按性别分组,统计每组的人数
1.先过滤掉年龄小于25岁的人。2.再分组。3.最后统计每组的人数
SELECT sex, COUNT() FROM student3 WHERE age > 25 GROUP BY sex;
2.查询年龄大于25岁的人,按性别分组,统计每组的人数,并只显示性别人数大于2的数据
注意: 并只显示性别人数>2的数据属于分组后的条件,对于分组后的条件需要使用Having语句
SELECT sex, COUNT() FROM student3 WHERE age > 25 GROUP BY sex HAVING COUNT(*) >2; 】
6.8 limit：（方言，只能在MySQL中使用）
select * from 表名 limit offset length
offset：偏移量（越过多少数据，如果从第一条开始可省略）
length: 查询长度
注意：书写顺序和执行顺序
书写顺序(重点)：
SELECT 字段 FROM 表名 WHERE 条件 GROUP BY 字段 HAVING 条件 ORDER BY 字段;
其中，SELECT字段，FROM表名为 必写，其余均可根据情况而定
执行顺序：（了解）
-
From 表名
-
Where 条件
-
Group by 字段 字段值相同的数据会划分成一组
-
Having 条件 对每一组分别进行一次运算
-
Select 字段 把每组中第一条数据取出来。合并成一张新伪表展示这个新伪表上的部分字段
-
order by 字段 对新伪表进行最后排序
【查询顺序】：
先查询–》分组–》排序–》聚合
select–》group by --》Oder by --》 SUM，AVG
select 列名，聚合函数（* 不能含有其他列）
from 表名
[where 条件] ->分组前筛选
group by 列名 ->分组
[having 条件:一般为聚合函数] ->分组后筛选
[order by 列名或聚合函数ASC DESC]
where 在分组前进行过滤， having在分组后进行过滤
4.DCL
- 一个项目创建一个用户！一个项目对应的数据库只有一个！
- 这个用户只能对这个数据库有权限，其他数据库你就操作不了了！
1. 创建用户
CREATE USER '用户名'@'IP地址' IDENTIFIED BY '密码';
> 用户只能在指定的IP地址上登录
CREATE USER '用户名'@'%' IDENTIFIED BY '密码';
>用户可以在任意IP地址上登录
2. 给用户授权
GRANT 权限1，...，权限n ON 数据库.表名 TO 用户名@IP地址;
> 权限、用户、数据库
> 给用户分派在指定的数据库上的指定的权限
GRANT ALL ON 数据库.* TO 用户名@IP地址;
> 给用户分派指定数据库上的所有权限
3. 撤销权限
REVOKE 权限1，...，权限n ON 数据库.* FROM 用户名@IP地址;
> 撤销指定用户在指定数据库上的指定权限
4. 查看权限
SHOW GRANTS FOR 用户名@IP地址
> 查看指定用户的权限
5. 删除用户
DROP USER 用户名@IP地址
二、数据库备份
备份：mysqldump -u用户名 -p密码 数据库 > 文件的路径（注：进入cmd就可以备份）
还原：SOURCE 导入文件的路径;（注：需要选择数据库才可以还原）
三、数据库约束
对表中的数据进行进一步的限制，从而保证数据的正确性、有效性、完整性
注意：每个表只能有一个主键约束，但其他约束可以有多个
- 约束种类:
| 约束名 | 含义 | 特点 |
|---|---|---|
| primary key | 主键 | 唯一不为空，每个表只能有一个主键约束 |
| unique | 唯一 | 唯一，不能重复值，可以多个空（NULL） |
| not null | 非空 | 不为空 |
| default | 默认 | 默认值，没有给定值时的值，给定值了会覆盖 |
| foreign key | 外键 |
-
1.主键（唯一且不为空）
1.1添加： 在创建表的时候添加：字段名 字段类型 PRIMARY KEY 1.2删除： ALTER TABLE 表名 DROP PRIMARY KEY; -
2.唯一
在创建表的时候添加：字段名 字段类型 UNIQUE特点：唯一，允许为空，不对null做校验（可以允许多个null）
-
3.非空
在创建表的时候添加：字段名 字段类型 NOT NULL -
4.默认值
在创建表的时候添加：字段名 字段类型 DEFAULT 默认值 -
5.外键
5.1什么是外键约束？ 一张表中的某个字段引用另一个表的主键 主表： 约束别人 副表/从表： 使用别人的数据，被别人约束 5.2创建外键 新建表时添加外键**（约束是独立添加的，不在某个字段后写）** [CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名) 5.3外键的级联 级联操作：在修改和删除主表的主键时，同时更新或删除副表的外键值，称为级联操作 ON UPDATE CASCADE -- 级联更新，主键发生更新时，外键也会更新 ON DELETE CASCADE -- 级联删除，主键发生删除时，外键也会删除
【注：在添加外键约束时，同时添加级联操作，就可以同步修改数据， 否则不能随便修改被引用的（主表的）主键】
四、表关系
-
1.一对多
在 “多” 表中创建字段（外键） 引用 “一” 的主键； “一”的是主表，“多”的是附表，“多”的添加字段引用“一”的主键为外键
举例： 部门和员工。在部门表中添加字段，作为外键引用员工表的主键；
-
2.多对多
可以看成多个“一对多”，然后把每个“一”都当成新字段放进 中间表 里，且设置对应的主键为外键 注意：中间表最少有两个字段
举例： 学生选择课程，在中间表中，有学生和课程两个字段，分别最为外键指向学生主键和课程主键
-
3.一对一
很少用，因为一对一可以写成一个表 两种建表方法： 1.外键唯一：主表的主键和从表的外键（唯一），形成主外键关系 2.外键是主键：主表的主键和从表的主键，形成主外键关系
五、三范式
-
主键（主属性）是非业务字段；业务字段不能当成主键。
-
1.第一范式
每一列不能再拆分； 个人理解：所有字段都是独立的不能再拆分的。 -
2.第二范式
一张表只描述一件事 表中每一个字段都依赖与主键 -
3.第三范式
在第二范式的基础上，任何非主键都不依赖与其他非主键。 要求一个数据库表中不包含已在其它表中已包含的非[主关键字]信息 （使用主外键的方式关联，而不是使用非主属性关联） 个人理解：外键要引用其他表的主键不能是业务字段
六、多表查询*
条件查询，多表查询（一对一，一对多，多对多，笛卡尔积，内外链接，子查询，），聚合函数，分组，limit，数据库备份
-
1.多表查询顺序：
1确定表 --》 2确定关联关系 --》 3需要查询那些字段
***什么时候用内连接？什么时候用外连接？？、
-
2.内连接
用左边表的记录去匹配右边表的记录，如果符合条件的则显示
隐式内连接： SELECT * FROM 表1 , 表2，WHERE 条件； 显示内连接： SELECT * FROM 表1 INNER JOIN 表2 ON 条件； -
3.外连接
3.1左外连接 用左边表的记录去匹配右边表的记录，如果符合条件的则显示；否则，显示NULL SELECT * FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件； （OUTER可省略）
可以理解为：在内连接的基础上保证左表的数据全部显示 左表数据全部显示的基础上，显示条件匹配的
3.2右外连接
用右边表的记录去连接匹配左边表的记录，如果符合条件就显示，否则显示null
SELECT * FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件；
可以理解为：在内连接基础上保证由标的数据全部显示
-
4.子链接
4.1 子查询结果是一个值 肯定在`WHERE`后面作为`条件` SELECT 查询字段 FROM 表 WHERE 字段=（子查询）; 4.2 子查询结果是单例多行的时候 肯定在`WHERE`后面作为`条件`，结果集类似于一个数组，父查询使用`IN`运算符 SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）; 4.4 子查询结果只要是`多行多列` 肯定在`FROM`后面作为`表` SELECT 查询字段 FROM （子查询） 表别名 WHERE 条件;
子查询总结：
结果为单列，作为条件； 单行单列：= ,多行单列 IN
结果为多列，作为表；多行多列 FROM
- 多表查询总结
不管我们查询几张表，表连接查询会产出笛卡尔积，我们需要消除笛卡尔积，拿到正确的数据。我们需要找到表与表之间通过哪个字段关联起来的（通常是
外键=主键）
消除笛卡尔积的规律
消除笛卡尔积规律：2张表需要1个条件，3张表需要2个条件，4张表需要3个条件。（条件数量=表的数量-1），每张表都要参与进来
*多表连接查询步骤：
1. 确定要查询哪些表
2. 确定表连接条件
3. 确定查询字段
七、 事务
理解：事务中的sql语句，要么不执行，要么全部执行；
BEGIN 开启事务
执行过程没有出现问题，commit 提交
有问题，rollback 回滚
操作事务
自动提交事务(默认)
每执行一条sql，sql会执行成功，对结果产生影响
手动提交事务
BEGIN 或 START TRANSACTION 显式地开启一个事务；
-
开启事务：
start transaction,BEGIN
不管autocommit 是1还是0
START TRANSACTION 后，只有当commit数据才会生效，ROLLBACK后就会回滚。 -
回滚事务：rollback 执行失败时回滚,回滚到事务开启的地方
-
提交事务：commit 执行成功时提交
注意：
1、不管autocommit 是1还是0
START TRANSACTION 后，只有当commit数据才会生效，ROLLBACK后就会回滚。
2、当autocommit 为 0 时
不管有没有START TRANSACTION。
只有当commit数据才会生效，ROLLBACK后就会回滚。
3、如果autocommit 为1 ，并且没有START TRANSACTION 。
调用ROLLBACK是没有用的。即便设置了SAVEPOINT。
MYSQL 事务处理主要有两种方法：
1、用 BEGIN, ROLLBACK, COMMIT来实现
BEGIN开始一个事务 （开启单次事务）ROLLBACK事务回滚COMMIT事务确认
2、直接用 SET 来改变 MySQL 的自动提交模式:
SET AUTOCOMMIT=0禁止自动提交SET AUTOCOMMIT=1开启自动提交
事务原理
概述：事务开启之后, 所有的操作都会临时保存到事务日志, 事务日志只有在得到commit命令后才会同步到数据表中，其他任何情况都会清空事务日志(rollback，断开连接)
回滚点
概述：在某些成功的操作完成之后，后续的操作有可能成功有可能失败，但是不管成功还是失败，前面操作都已经成功， 可以在当前成功的位置设置一个回滚点。可以供后续失败操作返回到该位置，而不是返回所有操作，这个点称之为 回滚点。
-
设置回滚点：savepoint 回滚点名字
-
回滚到指定位置：rollback to 回滚点名字
事务的四大特性（ACID）
| 事务特性 | 含义 |
|---|---|
| 原子性（Atomicity） | 事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。（转账业务） |
| 一致性（Consistency） | 事务前后数据的完整性必须保持一致 |
| 隔离性（Isolation） | 是指多个用户并发访问数据库时，一个用户的事务不能被其它用户的事务所干扰，多个 并发事务之间数据要相互隔离，不能相互影响。隔离级别(课下扩展) |
| 持久性（Durability） | 指一个事务一旦被提交（commit），它对数据库中数据的改变就是永久性的，接下来即使数据库发 生故障也不应该对其有任何影响 |
事务隔离级别
数据库事务的隔离级别有4种，
由低到高分别为Read uncommitted（读未提交） 、Read committed （读提交）、Repeatable read （重复读）、Serializable（序列化） 。
数据库出现的问题：
脏读、不可重复读、幻读
不同隔离级别解决的问题：
扫一扫
9145
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
