快速复习mysql

SQL的分类

1、DDL(Data Definition Language) 数据定义语言，用来操作数据库、表、列等； 常用语句：CREATE、 ALTER、DROP
2、DML(Data Manipulation Language) 数据操作语言，用来操作数据库中表里的数据；常用语句：INSERT、 UPDATE、 DELETE
3、DCL(Data Control Language) 数据控制语言，用来操作访问权限和安全级别； 常用语句：GRANT、DENY
4、DQL(Data Query Language) 数据查询语言，用来查询数据 常用语句：SELECT

mysql常用命令

登录

mysql -u root -p 随后输入密码

退出

exit

查看mysql中有哪些数据库

show databases;

创建数据库

create database 数据库名称;

使用数据库

use 数据库名;

创建数据库后查看该数据库基本信息

show create database db1;

删除数据库MySQL命令：

drop database db1;

查看某个数据库下有哪些表

show tables;

导入sql文件

source 文件路径; 注意不要有中文路径

查看当前使用的数据库

select database();

数据表

数据表的创建

create table 表名( 字段1 字段类型, 字段2 字段类型, … 字段n 字段类型 );

查看数据表

show tables;

查表的基本信息

show create table 表名;

查看表的字段信息

desc 表名;

修改表名

alter table 原表名 rename to 表名;

修改字段名

alter table 表名 change 原字段名 字段名 数据类型;

修改字段数据类型

alter table 表名 modify 字段名 数据类型;

增加字段

alter table 表名 add 字段名 字段数据类型;

删除字段

alter table 表名 drop 字段名;

删除数据表

drop table 表名;

insert插数据

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

更新数据

UPDATE 表名 SET 字段名1=值1[,字段名2 =值2,…] [WHERE 条件表达式];

删除数据

DELETE FROM 表名 [WHERE 条件表达式];

约束

约束条件 说明
PRIMARY KEY 主键约束用于唯一标识对应的记录，被标识为主键的数据在表中是唯一的且其值不能为空。
FOREIGN KEY 外键约束，常用于多张表之间的约束
NOT NULL 非空约束，字段的值不能为空
UNIQUE 唯一性约束，表中字段的值不能重复出现
DEFAULT 默认值约束，用于设置字段的默认值

主键

每张表都必须有主键，被设为主键的字段为主键字段，主键字段的值叫主键值，主键值唯一且不能为空。

外键

添加外键约束的字段称为外键字段，外键字段的值叫外键值
当两张表有联系时，通常会有一个相同的字段作为联系，为了确保这个字段值不会出错，就可以给这个字段加外键约束，增强两个表的联系
添加外键约束的表是子表(从表)，另一个表则是父表(主表)

创建外键

在创建数据表时语法如下：
CONSTRAINT 外键名 FOREIGN KEY (子表外键字段) REFERENCES 父表 (对应字段)
或者直接
FOREIGN KEY (子表外键字段) REFERENCES 父表 (对应字段)
如果没有指定外键名称，MySQL 会自动生成一个
将创建数据表创好后添加外键约束语法如下：
ALTER TABLE 子表名 ADD CONSTRAINT 外键名 FOREIGN KEY (子表外键字段) REFERENCES 父表 (对应字段);

删除外键

alter table 从表名 drop foreign key 外键名；

删除表及数据的顺序
先子后父，没有父哪来的子(数据一致性)
创建表及插入数据的顺序
先父后子

关于外键约束需要注意的细节

1、从表里的外键通常为主表的主键
2、从表里外键的数据类型必须与主表中主键的数据类型一致
3、主表发生变化时应注意主表与从表的数据一致性问题

查询

查询所有字段

select * from 表名;

查询指定字段，也可以查常数，在SELECT查询语句中还可以使用加减乘除运算符。

select 字段1,字段2,常数 from 表名;

查询结果中过滤重复数据

在使用DISTINCT 时需要注意：
在SELECT查询语句中DISTINCT关键字只能用在第一个所查列名之前。
select distinct 字段 from 表名;

聚合函数

count()
max()
min()
sum()
avg()

条件查询

在查询语句中通过WHERE子句指定查询条件对查询结果进行过滤。

关系运算符

select * from student where age>=17;

使用IN关键字查询

IN关键字用于判断某个字段的值是否在指定集合中。如果字段的值恰好在指定的集合中，则将字段所在的记录将査询出来。
select * from student where sid in (‘S_1002’,‘S_1003’);
查询sid为S_1001以外的学生的信息 MySQL命令：
select * from student where sid not in (‘S_1001’);

BETWEEN AND

用于判断某个字段的值是否在指定的范围之内。如果字段的值在指定范围内，则将所在的记录将查询出来
查询16到18岁的学生信息 MySQL命令：
select * from student where age between 16 and 18;
查询不是16到18岁的学生信息 MySQL命令：
select * from student where age not between 16 and 18;

使用空值查询

is null is not null

使用AND关键字查询

在MySQL中可使用AND关键字可以连接两个或者多个查询条件。
where 条件1 and 条件2;

使用OR关键字查询

只要记录满足其中任意一个条件就会被查询出来
where 条件1 or 条件2;

使用LIKE关键字查询

LIKE关键字可以判断两个字符串是否相匹配
可以用来精准查询，也可以使用通配符进行模糊查询
含有%通配的字符串
用于匹配任意长度的字符串。例如，字符串“a%”匹配以字符a开始任意长度的字符串
含有_通配的字符串
只匹配单个字符，如果要匹配多个字符，需要连续使用多个下划线通配符。例如，字符串“ab_”匹配以字符串“ab”开始长度为3的字符串，如abc、abp等等；字符串“a__d”匹配在字符“a”和“d”之间包含两个字符的字符串，如"abcd"、"atud"等等。

使用LIMIT限制查询结果的数量

完整用法
limit startIndex,length 起始下标从0开始
缺省用法
limit n; 取前n位
查询学生表中年纪最小的3位同学
select * from student order by age asc limit 3;

分页

每页显示pageSize条数据
第pageNo页：limit (pageNo-1)*pageSize , pageSize

OFFSET 跳过查询结果的前 N 行

完整语法
offset n rows
简化
offset n(注意mysql中没有rows用法，且offset不能单独使用，需要和limit结合使用)
通常与 LIMIT 或 FETCH 结合使用实现分页。
LIMIT 10 OFFSET 20;
相当于
LIMIT 20, 10;

union合并查询结果集

在两个查询语句中间使用可以合并结果，优点是效率高

select语句 union select语句;

使用GROUP BY进行分组查询

GROUP BY 子句可像切蛋糕一样将表中的数据进行分组，再进行查询等操作

统计各班级学生数 select count(*)as stunum(as后自己临时对查询项目取名，也可以不用) from stu group by classno;

ORDER BY

对查询结果排序
从表中査询出来的数据可能是无序的或者其排列顺序不是我们期望的。为此，我们可以使用ORDER BY对查询结果进行排序
SELECT 字段名1,字段名2,…
FROM 表名
ORDER BY 字段名1 [ASC 丨 DESC],字段名2 [ASC | DESC];
参数 ASC表示按照升序排序，DESC表示按照降序排序；默认情况下，按照ASC方式排序。
多字段排序的时候先排的优先

HAVING

HAVING 必须与 GROUP BY 一起使用（除非 SELECT 中只有聚合函数）
支持在 HAVING 中使用聚合函数
尽量先用 WHERE 过滤，减少分组的数据量
可以同时使用 WHERE 和 HAVING：
WHERE 过滤原始数据
HAVING 过滤分组结果

子查询

一个查询语句的所需的信息可以嵌套另一个查询语句，被嵌套的语句称为子查询
被嵌套的语句查询出来的结果可以作为这个查询所需的信息/条件
子查询可以嵌套在select后，也可以嵌套在from后，也可以嵌套在where后

select ...(select语句) from ...(select语句) where ...(select语句);

带EXISTS关键字的子查询

EXISTS关键字后面的参数可以是任意一个子查询， 它不产生任何数据只返回TRUE或FALSE。当返回值为TRUE时外层查询才会 执行

带ANY关键字的子查询

ANY关键字表示满足其中任意一个条件就返回一个结果作为外层查询条件。

带ALL关键字的子查询

ALL关键字与ANY有点类似，只不过带ALL关键字的子査询返回的结果需同时满足所有内层査询条件。

删除

delete from student where classid=(select cid from class where cname='Java');

内连接查询

SELECT 查询字段1,查询字段2, … FROM 表1 [INNER] JOIN 表2 ON (表1.关系字段和表2.关系字段的关系)
或者
select 表1.查询字段1,表2.查询字段2, … FROM 表1,表2,… where (表1.关系字段和表2.关系字段的关系)
内连接的特点就是两个表不分主次，地位相当，只会显示出两个表符合条件的那一部分信息

外连接

SELECT 查询字段1,查询字段2, … FROM 表1 [left/right] JOIN 表2 ON (表1.关系字段和表2.关系字段的关系)
其中left和right用来区分谁是主表，left表示join左边的表是主表，right同理
外连接的特点就是不管符不符合条件，都会将主表的要查询的字段值信息全部显示出来，符合条件的会一一对应，不符合的在从表那里会对应为null

update基本语法

UPDATE 表名 SET 字段名1=值1[,字段名2 =值2,…] [WHERE 条件表达式];

delete基本语法

DELETE FROM 表名 [WHERE 条件表达式];

日期的varchar与date类型的转换

varchar转date

str_to_date(‘字符串日期’,‘日期格式’) 一般用于insert将字符串转换为date插入
日期格式
%Y 年
%m 月
%d 日
%h 时
%i 分
%s 秒
insert into stu(no,birth) values(1,str_to_date(‘10-01-2005’,‘%m-%d-%Y’));
但是当你的字符串日期格式为%Y-%m-%d时不使用这个函数就能自动转换
insert into stu(no,birth) values(2,‘2005-10-02’);

date转字符串

date_format(日期类型字段名,‘所需日期格式’) 一般用于select将date转换成特定格式的字符串展示
select no,date_format(‘birth’,‘%Y/%m/%d’)
from stu
就能将日期输出为2005/10/01

书写顺序：S-F-W-G-H-O-L

SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT

执行顺序：F-W-G-H-S-D-O-L

FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT

执行顺序理解

SELECT department, AVG(salary) – 5. 选择显示的列
FROM employees – 1. 从表获取数据
WHERE hire_date > ‘2020-01-01’ – 2. 筛选行
GROUP BY department – 3. 分组
HAVING AVG(salary) > 5000 – 4. 筛选分组
ORDER BY AVG(salary) DESC; – 6. 排序

事务transaction

什么是事务

一个事务是一个完整的业务逻辑
和事务相关的是DML语句，也就是insert，update，delete
事务就是多条DML语句同时成功或同时失败

事务的特点

事务怎么做到多条语句同时成功和失败的
事务执行过程中，每一条DML语句操作都会提交到事务活动的日志文件
事务执行过程中，可以提交事务也可以回滚事务
提交事务标志事务的成功结束
回滚事务标志事务的失败结束

提交事务commit

mysql默认会自动提交事务，每执行一条语句，提交一下

关闭自动提交

start transaction;

回滚事务roolback

回滚只能回滚到上一次提交的节点

事务的四个特性

A原子性
C一致性
I隔离性
T持久性

事务的隔离性

事物的四个隔离级别

• 读未提交 read uncommited 最低
  事务A能读取到事务B未提交的数据
  可能导致脏读，大多数数据库都会从第二档开始
• 读已提交 read commited
  事务A只能读到事务B已提交的数据
  解决了脏读，但是会出现不可重复读，重复读到的数据可能不一样
• 可重复读 repeatable read
  事务A开启之后，不管多久，每次在事务A中读取到的数据都是一样的，即使事务B已经修改数据，事务A读到的还是之前读到的数据
  解决了不可重复读，会出现幻影读，读到的数据不真实
  mysql默认的隔离级别
• 序列化/串行化 serializable 最高
  效率最低，事务排队，不能并发，每一次读到的数据都是真实的

索引

索引在字段上添加，可以提高查询效率
添加索引后，索引对象会自动排序(表不会变)
数据库中主键和unique约束的字段会自动添加索引对象
不建议随便添加索引，添加太多会导致系统性能降低

索引的创建与删除

create index 索引名 on 表名(字段名); drop index 索引名 on 表名;

索引的失效

索引是从开头进行查询的，所以如果查的数据是%开始的模糊查询，索引就无从下手
索引列使用函数会导致索引的失效

视图

站在不同角度去看同一份数据

表复制

create table 新表名 as select* from 表名;

视图的创建与删除

create view 视图名 as DQL语句;
drop view 视图名;

视图的作用

对视图对象的操作会影响原表
当一个DQL语句需要重复使用的时候就可以使用视图将这段语句“复制”下来，到时候要用这段DQL语句的时候直接使用视图即可，可以大大提升效率