一、SQL
Structure Query Language(结构化查询语言)简称 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
二、存储引擎
ENGINE=INNODB DEFAULT CHARSET=utf8
常见的存储引擎
- innodb
innodb 会产生文件有:独占表空间 .idb文件 , 共享表空间文件(.ibdata1)
每个表对应的数据、索引和插入缓冲 存放在 => 独占表空间 .idb文件
每个表对应的撤销(undo)信息,系统事务信息 等信息 => 共享表空间文件(.ibdata1) - MyISAM
MyISAM 会产生三个文件 索引文件 :MYI,数据文件:MYD, 表结构定义文件 :frm 文件
区别:
- InnoDB支持事务,MyISAM不支持,对于InnoDB每一条SQL语言都默认封装成事务,自动提交,这样会影响速度,所以最好把多条SQL语言放在begin和commit之间,组成一个事务
- InnoDB支持外键,而MyISAM不支持
- InnoDB是聚集索引 而 MyISAM是非聚集索引
- InnoDB不保存表的具体行数,执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数,执行上述语句时只需要读出该变量即可,速度很快(注意不能加有任何WHERE条件);
- InnoDB支持表、行(默认)级锁,而MyISAM支持表级锁
如何选择
- 是否要支持事务,如果要请选择innodb,如果不需要可以考虑MyISAM;
- 如果表中绝大多数都只是读查询,可以考虑MyISAM,如果既有读也有写,请使用InnoDB。
- 系统崩溃后,MyISAM恢复起来更困难,能否接受;
- MySQL5.5版本开始Innodb已经成为Mysql的默认引擎
查看存储引擎:show engines;
三、三大范式
-
第一范式(1NF)确保每列的原子性。
-
满足第二范式(2NF)必须先满足第一范式(1NF)
- 第二范式要求表中的每列都和主键相关
- 完全依赖:主键可能由多个属性构成,完全依赖要求不允许存在非主属性依赖于主键中的某一部分属性
-
3NF满足第三范式必须先满足第二范式。
- 要求:每列都和主键列直接相关,而不是间接相关
四、数据类型
类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
---|---|---|---|---|
TINYINT | 1 byte | (-128,127) | (0,255) | 小整数值 |
SMALLINT | 2 bytes | (-32 768,32 767) | (0,65 535) | 大整数值 |
MEDIUMINT | 3 bytes | (-8 388 608,8 388 607) | (0,16 777 215) | 大整数值 |
INT或INTEGER | 4 bytes | (-2 147 483 648,2 147 483 647) | (0,4 294 967 295) | 大整数值 |
BIGINT | 8 bytes | (-9,223,372,036,854,775,808,9 223 372 036 854 775 807) | (0,18 446 744 073 709 551 615) | 极大整数值 |
FLOAT | 4 bytes | (-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) | 0,(1.175 494 351 E-38,3.402 823 466 E+38) | 单精度 浮点数值 |
DOUBLE | 8 bytes | (-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 双精度 浮点数值 |
DECIMAL | 对DECIMAL(M,D) ,如果M>D,为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
日期类型
类型 | 大小 ( bytes) | 范围 | 格式 | 用途 |
---|---|---|---|---|
DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
TIME | 3 | ‘-838:59:59’/‘838:59:59’ | HH:MM:SS | 时间值或持续时间 |
YEAR | 1 | 1901/2155 | YYYY | 年份值 |
DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
TIMESTAMP | 4 | 1970-01-01 00:00:00/2038结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYYMMDD HHMMSS | 混合日期和时间值,时间戳 |
字符串类型
类型 | 大小 | 用途 |
---|---|---|
CHAR | 0-255 bytes | 定长字符串 |
VARCHAR | 0-65535 bytes | 变长字符串 |
TINYBLOB | 0-255 bytes | 不超过 255 个字符的二进制字符串 |
TINYTEXT | 0-255 bytes | 短文本字符串 |
BLOB | 0-65 535 bytes | 二进制形式的长文本数据 |
TEXT | 0-65 535 bytes | 长文本数据 |
MEDIUMBLOB | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 |
MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 |
LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 |
LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |
五、数据库操作
-
创建数据库
create database 数据库名称
-
创建一个叫db1的数据库
show create database db1
-
查看该数据库基本信息
show create database db1
-
删除数据库
drop database db1
-
查询出MySQL中所有的数据库MySQL命令:
show databases
-
将数据库的字符集修改为gbk MySQL命令:
alter database db1 character set gbk
-
切换数据库 MySQL命令:
use db1
-
查看当前使用的数据库 MySQL命令:
select database()
六、数据表操作
1 创建数据表
create table student(
id int,
name varchar(20),
gender varchar(10),
birthday date
);
2 查看数据表
-
查看当前数据库中所有表
show tables;
-
查表的基本信息
show create table student;
-
查看表的字段信息
desc student;
3 修改数据表
-
修改表名
alter table student rename to stu
-
改字段名
alter table stu change name sname varchar(10);
-
修改字段数据类型
alter table stu modify sname int;
-
增加字段
alter table stu add address varchar(50);
-
删除字段
alter table stu drop address;
4 删除数据表
drop table 表名;
七、筛选
where
操作符 | 描述 |
---|---|
= | 等号,检测两个值是否相等,如果相等返回true |
<>, != | 不等于,检测两个值是否相等,如果不相等返回true |
> | 大于号,检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true |
< | 小于号,检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true |
>= | 大于等于号,检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true |
<= | 小于等于号,检测左边的值是否小于或等于右边的值, 如果左边的值小于或等于右边的值返回true |
or,
and
not
is null
is not null
in
not in
修改数据
xxUPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]
删除数据
DELETE FROM table_name [WHERE Clause]
查询数据
SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]
like
_
%
DISTINCT
select DISTINCT cname from class;--distinct去重复的值
ORDER BY
排序 desc
asc :默认
GROUP BY
分组
where....group by having......
null
select * from class where beizhu is null; --查询null值 使用的是is 关键字
select * from class where beizhu is not null;
limit
limit 起始值 ,每页记录
select * from person limit 3,3
多表查询
语法1
select 字段1,字段2... from 表1,表2... [where 条件]
笛卡尔积: A表中数据条数 * B表中数据条数 = 笛卡尔乘积.
语法2
SELECT 字段列表
``FROM 表1 INNER|LEFT|RIGHT JOIN 表2
ON 表1.字段 = 表2.字段;
判断查询 IF关键字,CASE 关键字
语法: IF(条件表达式,"结果为true",'结果为false');
SELECT account.* ,IF(money>18000,'高','低') AS jibie FROM account
语法 case:
SELECT
CASE WHEN STATE = '1' THEN '成功'
WHEN STATE = '2' THEN '失败'
ELSE '其他' END
FROM 表;
子查询
概念: 把一个查询嵌套在另一个查询当中
分类
- 标量子查询:返回单一值的标量,最简单的形式。
- 列子查询:返回的结果集是 N 行一列。
- 行子查询:返回的结果集是一行 N 列。
- 表子查询:返回的结果集是 N 行 N 列。
SELECT * FROM account WHERE (account,money) IN (SELECT email from s1)
SELECT * FROM account WHERE (account,money) IN (SELECT email,NAME FROM s1)
优化
-
使用连接(JOIN)来代替子查询
SELECT * FROM t1 WHERE t1.a1 NOT ``in` `(SELECT a2 FROM t2 ) 优化后: SELECT * FROM t1 LEFT JOIN t2 ON t1.a1=t2.a2 WHERE t2.a2 IS NULL
函数
聚合函数
-
count
-
sum
-
avg
-
max
-
min
八、视图 view
概念:
视图是虚拟表,本身不存储数据,而是按照指定的方式进行查询
语法
CREATE VIEW 视图名 AS 查询语句
CREATE OR REPLACE VIEW 视图名 AS SELECT [...] FROM [...];
使用
通过视图查询表中的数据
通过视图修改表中的数据(注意:可以通过视图插入数据,但是只能基于一个基础表进行插入)
create view ptem as select * from person where id >6
CREATE view person_tab as select person.id,person.name,person.age,person.sex,person.salary,person.hire_date,dept.dname from person INNER JOIN dept on person.dept_id=dept.did
--查询视图
select * from person_tab;
--删除视图中的数据
delete from ptab where name ='wupeiqi'
--往视图添加数据
insert into ptab(name,age) values('feifei',40)
--修改视图的数据
update person_tab set name ='kk' where id = 1;
九、事务(transaction)
特性:ACID
A: 原子性 : 事务是最小单位,不可再分
C: 一致性 : 事务要求所有的DML语句操作的时候,必须保证同时成功或者同时失败
I: 隔离性: 事务A和事务B之间具有隔离性
D: 持久性 : 数据一旦被修改不能回到之前的状态
语法
start transaction
DML语句
rollback/ commit
rollback : 回滚
commit: 提交
使用
start TRANSACTION;
update person set salary= salary-2000 where id = 1;
update person set salary1 = salary+2000 where id =12;
ROLLBACK;
隔离性有隔离级别
查看当前事务隔离级别: select @@tx_isolation; select @@global.tx_isolation;
-
读未提交:read uncommitted
-
事物A和事物B,事物A未提交的数据,事物B可以读取到
-
这里读取到的数据叫做“脏数据”
-
这种隔离级别最低,这种级别一般是在理论上存在,数据库隔离级别一般都高于该级别
-
-
读已提交:read committed
- 事物A和事物B,事物A提交的数据,事物B才能读取到
- 这种隔离级别高于读未提交 - 换句话说,对方事物提交之后的数据,我当前事物才能读取到
- 这种级别可以避免“脏数据” - 这种隔离级别会导致“不可重复读取”
-
可重复读:repeatable read
- 事务A和事务B,事务A提交之后的数据,事务B读取不到
- 事务B是可重复读取数据
- 这种隔离级别高于读已提交
- 换句话说,对方提交之后的数据,我还是读取不到
- 这种隔离级别可以避免“不可重复读取”,达到可重复读取
- 比如1点和2点读到数据是同一个
- MySQL默认级别
- 虽然可以达到可重复读取,但是会导致“幻像读”
-
串行化:serializable
- 事务A和事务B,事务A在操作数据库时,事务B只能排队等待
- 这种隔离级别很少使用,吞吐量太低,用户体验差 - 这种级别可以避免“幻像读”,每一次读取的都是数据库中真实存在数据,事务A与事务B串行,而不并发
不同的隔离级别会产生不同的现象,4种事务隔离级别分别表现的现象如下表
隔离级别 | 脏读 | 非重复读 | 幻像读 |
---|---|---|---|
read uncommitted | 允许 | 允许 | 允许 |
read committed | 不允许 | 允许 | 允许 |
repeatable read | 不允许 | 不允许 | 允许 |
serializable | 不允许 | 不允许 | 不允许 |
名词解释:
-
脏读(dirty read):一个事务可以读取另一个尚未提交事务的修改数据
-
非重复读(nonrepeatable read):在同一个事务中,如果数据被其他事务修改,不能重复读取该记录原始值。
-
幻想读(phantom read):在同一事务中,同一查询多次进行时候,由于其他插入操作(insert)的事务提交,导致每次返回不同的结果集。
事务的隔离级别的范围
- SESSION 只对当前的会话有效
- GLOBAL 全局,不包含当前连接,之后新获取的连接都会生效
设置隔离级别的语法
SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL <isolation-level>
注意:在InnoDB的事务中,对于UPDATE、DELETE和INSERT语句,InnoDB会自动给涉及数据行加排他锁,锁只有在执行COMMIT或者ROLLBACK时才会释放
十、锁
1 作用
避免资源挣用的机制
资源征用:多个任务使用同一个资源,对资源产生征用
在数据库中,数据就是资源,不同的客户端对记录的CRUD操作就是任务
解决方式:在某个任务使用某个资源的时候,进行标记,其他任务不能同时使用,等待或者放弃
2 流程
加锁 -->成功–>使用资源–>释放锁
加锁–>失败–>进操作队列–>重复加锁
3 锁类型
- 共享锁,读锁,S-lock,都能读取表中的记录 但是不能写
- 排他锁,独占锁,写锁,X-lock:
4 锁粒度
不同的锁导致并发操作结果不一样
-
表级:table-level,锁定整张表
-
行级: row-level ,锁定表中的某些行的记录
Innodb ,实现了表级和行级锁
MyISAM 实现表级锁
5 语法
-
表锁(Innodb 和 MyISAM 引擎都支持)
加锁:lock tables 表名1,表名2 read|write
释放锁:unlock tables
-
行锁(Innodb 支持,在InnoDB事务中,行锁是在需要的时候才加上的,但并不是不需要了就立刻释放,等到事务结束时才释放)
共享级: select * from 表 where id=1 lock in share mode;
排他级: select * from 表 条件 for update
十一、索引
1 作用
帮助mysql提高查询效率
索引存储在磁盘中
索引就是空间换时间
2 分类
-
普通索引
-
唯一索引: unique
-
主键索引: primary key
-
联合索引:
primary key (id,name) 联合主键索引
unique(id,name) 联合唯一索引
index(id,name) 联合普通索引 -
全文索引(用于搜索很长一片文章)
3 语法
-
创建表时
CREATE TABLE 表名 ( 字段名1 数据类型 [完整性约束条件…], 字段名2 数据类型 [完整性约束条件…], [UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY [索引名] (字段名[(长度)] [ASC |DESC]) );
-
在已存在的表上创建
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 (字段名[(长度)] [ASC |DESC]) ;
-
删除索引
DROP INDEX 索引名 ON 表名字;
使用
创建表时
CREATE TABLE s1(
id INT ,
NAME CHAR(20),
age INT,
email VARCHAR(30),
PRIMARY KEY(id,age)
);
inforamtion varchar(10000) ,
INDEX(email)
在创建表后添加索引
create index name on s1(name); #添加普通索引
create unique age on s1(age);添加唯一索引
alter table s1 add primary key(id); #添加住建索引,也就是给id字段增加一个主键约束
create index name on s1(id,name); #添加普通联合索引
删除索引
DROP INDEX NAME ON s1; #删除普通索引
DROP INDEX email ON s1; #删除唯一索引,就和普通索引一样,不用在index前加unique来删,直接就可以删了
alter table s1 drop primary key; #删除主键(因为它添加的时候是按照alter来增加的,那么我们也用alter来删)
ALTER TABLE s1 ADD FULLTEXT (`information`)
CREATE FULLTEXT INDEX information ON `s1` (`information`)
案例
DELIMITER $$ #声明存储过程的结束符号为$$
CREATE PROCEDURE auto_insert1()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE(i<3000000)DO
INSERT INTO s1 VALUES(i,CONCAT('egon',i),i,CONCAT('egon',i,'@oldboy'));
SET i=i+1;
END WHILE;
END$$ #$$结束
DELIMITER ; #重新声明分号为结束符号
CALL auto_insert1()
select * from s1 where id=333;
## 使用索引之后
全文索引
关键字不是字段本身而是从字段中提取的关键词,作为关键字。对于普通索引 会将整体内容作为关键字来使用
全文索引是针对大段文章
语法:fulltext index (information)
insert into(information) s1 values('河南的水舞很飘亮 被华春莹所赞美')
explain详解
id
SELECT识别符。这是SELECT的查询序列号
select_type
-
PRIMARY :子查询中最外层查询
-
SUBQUERY : 子查询内层第一个SELECT,结果不依赖于外部查询
-
DEPENDENT SUBQUERY:子查询内层第一个SELECT,依赖于外部查询
-
UNION :UNION语句中第二个SELECT开始后面所有SELECT,
-
SIMPLE:简单的 select 查询,不使用 union 及子查询
-
UNION :UNION 中的第二个或随后的 select 查询,不依赖于外部查询的结果集
Table
显示这一步所访问数据库中表名称
Type
表访问方式
-
ALL:完整的表扫描 通常不好
-
index: 这种连接类型只是另外一种形式的全表扫描,只不过它的扫描顺序是按照索引的顺序
-
range: 指的是有范围的索引扫描
-
ref: 查找条件列使用了索引而且不为主键和unique
-
ref_eq: 使用了主键或者唯一性索引进行查找的情况
-
const: 如果将一个主键放置到where后面作为条件查询
-
null: 不用访问表或者索引就直接能到结果.
效率从高到低: system > const >ref_eq> ref > range > index > all
Possible_keys
该查询可以利用的索引,如果没有任何索引显示 null
Key
Mysql 从 Possible_keys 所选择使用索引
Rows
估算出结果集行数
十二、优化
1 硬件
2 软件
-
设计之初考虑优化问题
-
业务逻辑
-
核心方向
-
应用层
- 应用层加缓存(redis)
- 减少不必要的数据提取(可以惰性加载,延迟加载)
-
数据库层
-
配置参数
-
sql语句优化
-
sql语句都大写
-
sql中不要使用*关键字进行查询
-
使用内连接 尽量代替 子表查询
-
添加索引
-
索引失效的情况
-
使用了or( 要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引 )
-
like 以%号开头
-
如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引
-
查询条件使用函数在索引列上,或者 对索引列进行运算, 运算包括(+,-,*,/,! 等)
-
使用了in,not in,not exist
-
使用了is null 或者 is not null(索引不存储null值)
-
主键索引尽量使用自增(int类型)
查看sql语句执行时的情况
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-J8h3T939-1626104269511)(D:\前端备课\笔记\数据库-课程\explain.png)]
-
-
.应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描
-
应尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描
-
in 和 not in 也要慎用,否则会导致全表扫描( 对于连续的数值,能用 between 就不要用 in 了)
-
应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描
-
-
存储过程( 因为数据库管理系统是在创建存储过程时对SQL代码进行分析和优化,并在第一次执行时进行语法检查和编译,将编译好的可执行代码存储在内存的一个专门缓冲区中,以后再执行此存储过程时,只需直接执行内存中的可执行代码即可 )
-
-
-