程序员福利-MySql全套笔记

最新推荐文章于 2024-09-01 10:32:33 发布

lv_yao_wen

最新推荐文章于 2024-09-01 10:32:33 发布

阅读量320

点赞数 5

分类专栏： MySql 程序员编程文章标签： mysql 数据库

本文链接：https://blog.csdn.net/lvy_a_o_w_e_n/article/details/109314297

版权

MySql 同时被 3 个专栏收录

1 篇文章 0 订阅

订阅专栏

程序员

1 篇文章 0 订阅

订阅专栏

编程

1 篇文章 0 订阅

订阅专栏

关系型数据库：Oracle MySQL sqlserver
非关系型数据库：redis(内存数据库)

SQL(Structured Query Language)

结构化查询语言
SQL分类
1. DDL(Data Definition Language)：数据定义语言。用来操作数据库、表、列、等。
2. DML(Data Manipulation Language)：数据操作语言。用来对数据库中表的数据进行增删改。
3. DQL(Data Query Language)：数据查询语言。用于查询数据库中表的记录。
4. DCL(Data Control Language)：数据控制语言。用来定义数据库的访问权限和安全级别，及创建用户。

-- 启动数据库
servlce mysqld start
-- 登录数据库
mysql -u root -p

Day01 DDL、DML、DQL、约束

一、DDL操作数据库和数据表

1. DDL查询和创建数据库

-- 查询数据库	
show databases;
-- 查询数据库的创建语句   
show create database 数据库名称;
-- 创建数据库   
create database 数据库名称;
  -- 判断，如果不存在则创建
     create database if not exists 数据库名称;
  -- 指定字符集
     create database 数据库名称 character set 字符集名称;

2. DDL修改、删除、使用数据库

-- 修改数据库字符集
alter database 数据库名称 character set 字符集名称;
-- 删除数据库   
drop database 数据库名称;
  -- 判断，存在则删除
     drop database if exists 数据库名称;
-- 使用数据库   
use 数据库名称;
-- 查看当前使用数据库   
select database();

3.DDL查询数据表

-- 查询所有的数据表   
show tables;
-- 查询表结构   
desc 表名;
-- 查询字符集   
show table status from 数据库名 like '表名';

4. DDL创建数据表

-- 创建数据表
create table 表名{
	列名 数据类型 约束,
	...
	列名 数据类型 约束,
}

-- 数据类型
int	整数
date	yyyy-MM-dd
datetime	yyyy-MM-dd HH:mm:ss
timestamp	yyyy-MM-dd HH:mm:ss  系统时间自动赋值
varchar	字符串
/*	
1. 创建数据库user并进入
2. 创建表teacher包含编号，姓名，学科，生日，工资字段，并设置表的字符集为utf8；
3. 查看表结构
*/
CREATE DATABASE IF NOT EXISTS `user` CHARACTER SET utf8;
USE user;
SHOW TABLES;
DROP TABLE teacher;
CREATE TABLE teacher(	
	-- 编号
	id INT, 
	-- 姓名
	`name` VARCHAR(20),
	-- 学科
	`subject` VARCHAR(45),
	-- 生日
	birthday DATE,
	-- 工资
	salary DOUBLE(8,2)
)CHARSET = utf8;
SHOW CREATE TABLE teacher;

/*复制表*/
CREATE TABLE teacher1 LIKE teacher;

5. DDL修改数据表

-- 修改表名
alter table 表名 rename to 新表名;
-- 修改字符集
alter table 表名 character set 字符集名称;
-- 单独添加一列
alter table 表名 add 列名 数据类型;
-- 修改某一列的数据类型
alter table 表名 modify 列名 新数据类型
-- 修改列名和数据类型
alter table 表名 change 列名 新列名 新数据类型;
-- 删除某一列
alter table 表名 drop 列名;
/*同时操作多列的数据,新增一列，删除一列*/
ALTER TABLE teacher2 ADD age INT,DROP age;

6. DLL删除数据表

-- 删除数据表
drop table 表名;
-- 判断，存在就删除
drop table if exists 表名;

二、 DML表数据的增删改

1. DML新增表数据

-- 给指定列添加数据
insert into 表名(列名1,列名2,...) values (值1,值2,...);
-- 给全部列添加数据
insert into 表名 values (值1,值2,...);
-- 批量添加数据
insert into 表名(列名1,列名2,...) values (值1,值2,...),(值1,值2,...),...;
insert into 表名 values (值1,值2,...),(值1,值2,...),...;
-- 列名和值得数量以及数据类型都要对应。

2. 修改和删除表数据

-- 修改表中的数据
update 表名 set 列名1=值1,列名2=值2,...[where 条件];
-- 删除表中的数据
delete from 表名 [where 条件];

三、 DQL表数据的查询

1. 语法

select 
	字段列表
	-- distinct	去重	
	-- as	别名
	-- 函数(列名)
from
	表名列表
where
	条件列表
	--  <>或!=  -->  不等于 
    -- between...and...		在某个范围之内
    -- in(...)		多选
    -- like 占位符		模糊查询，_单个任意字符，%多个任意字符
    -- is null		是null
    -- is not null 	不是null
    -- and 或 &&		并且
    -- or 或 || 		或者
    -- not 或 ！		非，不是
group by
	分组列表
having
	分组后的过滤条件
order by
	排序
limit	
	分页
-- 从上到下，顺序不能改变

2. 条件查询

-- 进入数据库user
USE `user`;
-- 创建数据表
CREATE TABLE product(
	id INT,			-- 商品编号
	NAME VARCHAR(20),	-- 商品名称
	price DOUBLE,		-- 商品价格
	brand VARCHAR(10),	-- 商品品牌
	stock INT,		-- 商品库存
	insert_time DATE        -- 添加时间
);
-- 添加数据
INSERT INTO product VALUES 
(1,'华为手机',3999,'华为',23,'2088-03-10'),
(2,'小米手机',2999,'小米',30,'2088-05-15'),
(3,'苹果手机',5999,'苹果',18,'2088-08-20'),
(4,'华为电脑',6999,'华为',14,'2088-06-16'),
(5,'小米电脑',4999,'小米',26,'2088-07-08'),
(6,'苹果电脑',8999,'苹果',15,'2088-10-25'),
(7,'联想电脑',7999,'联想',NULL,'2088-11-11');

-- 查询表中的所有数据
SELECT * FROM product;
-- 查询指定的列
SELECT name,price,brand FROM product;

-- 查询品牌，去重  DISTINCT  -->  去重
SELECT DISTINCT brand FROM product;

-- 查询商品名称和库存，库存数量在原有的基础上加10.进行null判断，起别名
/*
AS 名称   -->  给新列起别名
IFNULL(列名称,值)		-->  进行null判断
*/
SELECT name,IFNULL(stock,0) + 10 AS getnum FROM product;
-- 查询库存大于20的商品信息
SELECT * FROM product WHERE stock < 20;
-- 查询品牌为华为的商品信息
SELECT * FROM product WHERE brand = '华为';
-- 查询品牌为华为，并且库存大于20的商品信息
SELECT * FROM product WHERE brand = '华为' AND price > 20;
-- 查询金额在4000 - 6000之间的商品信息
SELECT * FROM product WHERE price >= 4000 AND price < 6000;
SELECT * FROM product WHERE price BETWEEN 4000 AND 6000;
-- 查询库存为14,30,23的商品信息
SELECT * FROM product WHERE stock = 14 OR stock = 30 OR stock = 23;
SELECT * FROM product WHERE stock IN (14,23,30);
-- 查询库存为null的商品信息
SELECT * FROM product WHERE stock IS NULL;
-- 查询库存不为null的商品信息
SELECT * FROM product WHERE stock IS NOT NULL;
-- 查询名称以小米为开头的商品信息
SELECT * FROM product WHERE name LIKE '小米%';
-- 查询名称第二个字是为的商品信息
SELECT * FROM product WHERE name LIKE '_为%';
-- 查询名称为四个字符的商品信息
SELECT * FROM product WHERE name LIKE '____';
-- 查询名称中包含电脑的的商品信息
SELECT * FROM product WHERE name LIKE '%电脑%';
-- 起别名(给表取)，两种方式
SELECT * FROM product AS p WHERE p.brand = '华为';
SELECT p.`NAME`,p.brand,p.price FROM product p WHERE p.brand = '华为';

3. 聚合函数

/*
count(列名) 	统计数量
max(列名)		最大值
min(列名)		最小值
sum(列名)		求和
avg(列名)		平均值
*/

-- 聚合函数 标准语法：	SELECT 函数名(列名) FROM 表名 [WHERE 条件];
-- 计算product表中总记录条数
SELECT COUNT(*) FROM product;
-- 获取最高价格
SELECT MAX(price) FROM product;
-- 获取最低库存
SELECT MIN(stock) FROM product;
-- 获取最高和最低价格差异
SELECT MAX(price) - MIN(stock) FROM product;
-- 获取总库存数量
SELECT SUM(stock) FROM product;
-- 获取品牌为苹果的总库存数量
SELECT SUM(stock) FROM product WHERE brand='苹果';
-- 获取品牌为小米的平均商品价格
SELECT AVG(price) FROM product WHERE brand='小米';
-- 对品牌进行分组,显示价格总和，并显示出分组是由那几个品牌组成的
SELECT brand,SUM(price),GROUP_CONCAT(name)  FROM product GROUP BY brand;

4. 排序查询

-- 排序查询 标准语法：SELECT 列名 FROM 表名 [WHERE 条件] ORDER BY 列名1 排序方式1,列名2 排序方式2;
-- 按照库存升序排序
SELECT * FROM product ORDER BY stock ASC;
-- 查询名称中包含手机的商品信息。按照金额降序排序
SELECT * FROM product WHERE NAME LIKE '%手机%' ORDER BY price DESC;
-- 按照金额升序排序，如果金额相同，按照库存降序排列
SELECT * FROM product ORDER BY price ASC,stock DESC;

5. 分组查询

-- 分组查询 标准语法：SELECT 列名 FROM 表名 [WHERE 条件] GROUP BY 分组列名 [HAVING 分组后条件过滤] [ORDER BY 排序列名 排序方式];

-- 按照品牌分组，获取每组商品的总金额
SELECT brand,SUM(price) FROM product GROUP BY brand;
-- 对金额大于4000元的商品，按照品牌分组,获取每组商品的总金额
SELECT brand,SUM(price) FROM product WHERE price > 4000 GROUP BY brand;
-- 对金额大于4000元的商品，按照品牌分组，获取每组商品的总金额，只显示总金额大于7000元的
SELECT brand,SUM(price) getSum FROM product WHERE price > 4000 GROUP BY brand HAVING getSum > 7000;
-- 对金额大于4000元的商品，按照品牌分组，获取每组商品的总金额，只显示总金额大于7000元的、并按照总金额的降序排列
SELECT brand,SUM(price) getSum FROM product 
WHERE price > 4000 
GROUP BY brand 
HAVING getSum > 7000 
ORDER BY getSum DESC;

6. 分页查询

/*	分页查询 标准语法：
		SELECT 列名 FROM 表名 
		[WHERE 条件] 
		[GROUP BY 分组列名]
		[HAVING 分组后条件过滤] 
		[ORDER BY 排序列名 排序方式] 
		LIMIT 当前页数,每页显示的条数;	
	LIMIT 当前页数,每页显示的条数;
	公式：当前页数 = (当前页数-1) * 每页显示的条数
*/
-- 每页显示3条数据
-- 第1页  当前页数=(1-1) * 3
SELECT * FROM product LIMIT 0,3;
-- 第2页  当前页数=(2-1) * 3
SELECT * FROM product LIMIT 3,3;
-- 第3页  当前页数=(3-1) * 3
SELECT * FROM product LIMIT 6,3;
-- 只展示前n行数据
SELECT * FROM product LIMIT n;

四、约束

00–分类

约束	作用
PRIMARY KEY	主键约束
PRIMARY KEY AUTO_INCREMENT	主键自增
UNIQUE	唯一约束
NOT NULL	非空约束
FOREIGN KEY	外键约束
FOREIGN KEY ON UPDATE CASSADE	外键级联更新
FOREIGN KEY ON DELETE CASCADE	外键级联删除

1. 主键约束

-- 创建学生表(编号、姓名、年龄)  编号设为主键
CREATE TABLE student(
	id INT PRIMARY KEY,
	NAME VARCHAR(30),
	age INT
);

-- 查询学生表的详细信息
DESC student;
-- 添加数据
INSERT INTO student VALUES (1,'张三',23);
INSERT INTO student VALUES (2,'李四',24);
-- 删除主键
ALTER TABLE student DROP PRIMARY KEY;
-- 建表后单独添加主键约束
ALTER TABLE student MODIFY id INT PRIMARY KEY;

2. 主键自增约束

-- 创建学生表(编号、姓名、年龄)  编号设为主键自增
CREATE TABLE student(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(30),
	age INT
);
-- 查询学生表的详细信息
DESC student;
-- 添加数据
INSERT INTO student VALUES (NULL,'张三',23),(NULL,'李四',24);
-- 删除自增约束
ALTER TABLE student MODIFY id INT;
INSERT INTO student VALUES (NULL,'张三',23);
-- 建表后单独添加自增约束
ALTER TABLE student MODIFY id INT AUTO_INCREMENT;
-- 建表时自定义自增起始值
CREATE TABLE employee(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(30),
	age INT
)AUTO_INCREMENT=100;

3. 唯一约束

-- 创建学生表(编号、姓名、年龄)  编号设为主键自增，年龄设为唯一
CREATE TABLE student(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(30),
	age INT UNIQUE
);
-- 查询学生表的详细信息
DESC student;
-- 添加数据
INSERT INTO student VALUES (NULL,'张三',23);
INSERT INTO student VALUES (NULL,'李四',23);
-- 删除唯一约束
ALTER TABLE student DROP INDEX age;
-- 建表后单独添加唯一约束
ALTER TABLE student MODIFY age INT UNIQUE;

4. 非空约束

-- 创建学生表(编号、姓名、年龄)  编号设为主键自增，姓名设为非空，年龄设为唯一
CREATE TABLE student(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(30) NOT NULL,
	age INT UNIQUE
);
-- 查询学生表的详细信息
DESC student;
-- 添加数据
INSERT INTO student VALUES (NULL,'张三',23);
-- 删除非空约束
ALTER TABLE student MODIFY NAME VARCHAR(30);
INSERT INTO student VALUES (NULL,NULL,25);
-- 建表后单独添加非空约束
ALTER TABLE student MODIFY NAME VARCHAR(30) NOT NULL;

5. 外键约束

-- 作用在两张表中。让表与表之间产生关联关系，从而保证数据的准确性。
uid INT UNIQUE,
CONSISTENT ou_fk1 FOREIGN KEY (uid) REFERENCES uuser(id)

Day02 多表关系、多表查询

一、多表关系

1. 一对一

在任意一个表建立外键，去关联另外一个表的主键。

-- 创建db3数据库
CREATE DATABASE db3;
-- 使用db3数据库
USE db3;
-- 创建person表
CREATE TABLE person(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 主键id
	NAME VARCHAR(20)                        -- 姓名
);
-- 添加数据
INSERT INTO person VALUES (NULL,'张三'),(NULL,'李四');

-- 创建card表
CREATE TABLE card(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 主键id
	number VARCHAR(20) UNIQUE NOT NULL,	-- 身份证号
	pid INT UNIQUE,                         -- 外键列
	CONSTRAINT cp_fk1 FOREIGN KEY (pid) REFERENCES person(id)
);
-- 添加数据
INSERT INTO card VALUES (NULL,'12345',1),(NULL,'56789',2);

2. 一对多

在多的一方，建立外键约束，来关联一的一方主键。

-- 创建user表
CREATE TABLE USER(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 主键id
	NAME VARCHAR(20)                        -- 姓名
);
-- 添加数据
INSERT INTO USER VALUES (NULL,'张三'),(NULL,'李四');

-- 创建orderlist表
CREATE TABLE orderlist(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 主键id
	number VARCHAR(20),                     -- 订单编号
	uid INT,				-- 外键列
	CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id)
);
-- 添加数据
INSERT INTO orderlist VALUES (NULL,'hm001',1),(NULL,'hm002',1),(NULL,'hm003',2),(NULL,'hm004',2);
-- 商品分类和商品
-- 创建category表
CREATE TABLE category(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 主键id
	NAME VARCHAR(10)                        -- 分类名称
);
-- 添加数据
INSERT INTO category VALUES (NULL,'手机数码'),(NULL,'电脑办公');

-- 创建product表
CREATE TABLE product(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 主键id
	NAME VARCHAR(30),			-- 商品名称
	cid INT,				-- 外键列
	CONSTRAINT pc_fk1 FOREIGN KEY (cid) REFERENCES category(id)
);
-- 添加数据
INSERT INTO product VALUES (NULL,'华为P30',1),(NULL,'小米note3',1),
(NULL,'联想电脑',2),(NULL,'苹果电脑',2);

3. 多对多

需要借助第三张中间表，中间表至少包含两个列。这两个列作为中间表的外键，分别关联两张表的主键

-- 创建student表
CREATE TABLE student(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 主键id
	NAME VARCHAR(20)			-- 学生姓名
);
-- 添加数据
INSERT INTO student VALUES (NULL,'张三'),(NULL,'李四');

-- 创建course表
CREATE TABLE course(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 主键id
	NAME VARCHAR(10)			-- 课程名称
);
-- 添加数据
INSERT INTO course VALUES (NULL,'语文'),(NULL,'数学');

-- 创建中间表
CREATE TABLE stu_course(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 主键id
	sid INT,  -- 用于和student表中的id进行外键关联
	cid INT,  -- 用于和course表中的id进行外键关联
	CONSTRAINT sc_fk1 FOREIGN KEY (sid) REFERENCES student(id), -- 添加外键约束
	CONSTRAINT sc_fk2 FOREIGN KEY (cid) REFERENCES course(id)   -- 添加外键约束
);
-- 添加数据
INSERT INTO stu_course VALUES (NULL,1,1),(NULL,1,2),(NULL,2,1),(NULL,2,2);

二、多表查询

00. 数据准备

-- 创建db4数据库
CREATE DATABASE db4;
-- 使用db4数据库
USE db4;

-- 创建user表
CREATE TABLE t_user(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 用户id
	uname VARCHAR(20),			-- 用户姓名
	age INT                                 -- 用户年龄
);
-- 添加数据
INSERT INTO USER VALUES (1,'张三',23);
INSERT INTO USER VALUES (2,'李四',24);
INSERT INTO USER VALUES (3,'王五',25);
INSERT INTO USER VALUES (4,'赵六',26);

-- 订单表
CREATE TABLE orderlist(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 订单id
	number VARCHAR(30),			-- 订单编号
	uid INT,    -- 外键字段
	CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id)
);
-- 添加数据
INSERT INTO orderlist VALUES (1,'hm001',1);
INSERT INTO orderlist VALUES (2,'hm002',1);
INSERT INTO orderlist VALUES (3,'hm003',2);
INSERT INTO orderlist VALUES (4,'hm004',2);
INSERT INTO orderlist VALUES (5,'hm005',3);
INSERT INTO orderlist VALUES (6,'hm006',3);
INSERT INTO orderlist VALUES (7,'hm007',NULL);

-- 商品分类表
CREATE TABLE category(
	id INT PRIMARY KEY AUTO_INCREMENT,  -- 商品分类id
	cname VARCHAR(10)                    -- 商品分类名称
);
-- 添加数据
INSERT INTO category VALUES (1,'手机数码');
INSERT INTO category VALUES (2,'电脑办公');
INSERT INTO category VALUES (3,'烟酒茶糖');
INSERT INTO category VALUES (4,'鞋靴箱包');

-- 商品表
CREATE TABLE product(
	id INT PRIMARY KEY AUTO_INCREMENT,   -- 商品id
	pname VARCHAR(30),                    -- 商品名称
	cid INT, -- 外键字段
	CONSTRAINT cp_fk1 FOREIGN KEY (cid) REFERENCES category(id)
);
-- 添加数据
INSERT INTO product VALUES (1,'华为手机',1);
INSERT INTO product VALUES (2,'小米手机',1);
INSERT INTO product VALUES (3,'联想电脑',2);
INSERT INTO product VALUES (4,'苹果电脑',2);
INSERT INTO product VALUES (5,'中华香烟',3);
INSERT INTO product VALUES (6,'玉溪香烟',3);
INSERT INTO product VALUES (7,'计生用品',NULL);

-- 中间表
CREATE TABLE us_pro(
	upid INT PRIMARY KEY AUTO_INCREMENT,  -- 中间表id
	uid INT, -- 外键字段。需要和用户表的主键产生关联
	pid INT, -- 外键字段。需要和商品表的主键产生关联
	CONSTRAINT up_fk1 FOREIGN KEY (uid) REFERENCES USER(id),
	CONSTRAINT up_fk2 FOREIGN KEY (pid) REFERENCES product(id)
);
-- 添加数据
INSERT INTO us_pro VALUES (NULL,1,1);
INSERT INTO us_pro VALUES (NULL,1,2);
INSERT INTO us_pro VALUES (NULL,1,3);
INSERT INTO us_pro VALUES (NULL,1,4);
INSERT INTO us_pro VALUES (NULL,1,5);
INSERT INTO us_pro VALUES (NULL,1,6);
INSERT INTO us_pro VALUES (NULL,1,7);
INSERT INTO us_pro VALUES (NULL,2,1);
INSERT INTO us_pro VALUES (NULL,2,2);
INSERT INTO us_pro VALUES (NULL,2,3);
INSERT INTO us_pro VALUES (NULL,2,4);
INSERT INTO us_pro VALUES (NULL,2,5);
INSERT INTO us_pro VALUES (NULL,2,6);
INSERT INTO us_pro VALUES (NULL,2,7);
INSERT INTO us_pro VALUES (NULL,3,1);
INSERT INTO us_pro VALUES (NULL,3,2);
INSERT INTO us_pro VALUES (NULL,3,3);
INSERT INTO us_pro VALUES (NULL,3,4);
INSERT INTO us_pro VALUES (NULL,3,5);
INSERT INTO us_pro VALUES (NULL,3,6);
INSERT INTO us_pro VALUES (NULL,3,7);
INSERT INTO us_pro VALUES (NULL,4,1);
INSERT INTO us_pro VALUES (NULL,4,2);
INSERT INTO us_pro VALUES (NULL,4,3);
INSERT INTO us_pro VALUES (NULL,4,4);
INSERT INTO us_pro VALUES (NULL,4,5);
INSERT INTO us_pro VALUES (NULL,4,6);
INSERT INTO us_pro VALUES (NULL,4,7);

1. 内连接查询

(1) 显示内连接

-- t_user表和orderlist表
-- 显示内连接:SELECT 列名 FROM 表名1 [INNER] JOIN 表名2 ON 关联条件;
-- 查询用户信息和对应的订单信息
SELECT 
	u.*,o.*
FROM
	t_user u
inner join
	orderlist o
ON
	u.id = o.uid;

-- 查询用户信息和对应的订单信息，表名起别名
SELECT 
	u.*,o.*
FROM
	t_user u
INNER JOIN
	orderlist o
ON
	u.id = o.uid;

-- 查询用户姓名，年龄。和订单编号
SELECT 
	u.uname,u.age,o.number
FROM
	t_user u
INNER JOIN
	orderlist o
ON
	u.id = o.uid;

(2) 隐式内连接

-- 隐式内连接：SELECT 列名 FROM 表名1,表名2 WHERE 关联条件;
-- 查询用户姓名，年龄。和订单编号
SELECT 
	u.uname,u.age,o.number
FROM 
	t_user u,
	orderlist o
WHERE 
	u.id = o.uid;

2. 外连接查询

(1) 左外连接：SELECT 列名 FROM 表名1 LEFT [OUTER] JOIN 表名2 ON 条件;

可以查询出来left join 左面表的全部数据，右面表的部分信息

-- t_user表和orderlist表
-- 左外连接：SELECT 列名 FROM 表名1 LEFT [OUTER] JOIN 表名2 ON 条件;
-- 查询所有用户信息，以及用户对应的订单信息
SELECT 
	u.*,o.*
FROM
	user u
LEFT JOIN
	orderlist o
ON
	u.id = o.uid;

(2) 右外连接：SELECT 列名 FROM 表名1 RIGHT [OUTER] JOIN 表名2 ON 条件;

可以查询出来left join 右面表的全部数据，左面表的部分信息

-- 右外连接SELECT 列名 FROM 表名1 RIGHT [OUTER] JOIN 表名2 ON 条件;
-- 查询所有订单信息，以及订单所属的用户信息
SELECT
	o.*,u.*
FROM 
	user u
RIGHT JOIN
	orderlist o
ON 
	u.id = o.uid;

3. 子查询

-- 结果是单行单列的：可以将查询的结果作为另一条语句的查询条件，使用运算符判断，= > >= < <=
-- 语法 SELECT 列名 FROM 表名 WHERE 列名=(SELECT 列名 FROM 表名 [WHERE 条件]);
-- 查询年龄最高的用户姓名

SELECT * FROM `user` WHERE age=(SELECT MAX(age) FROM `user`);

-- 结果是多行单列的：可以作为条件，使用in或者not in进行判断。
-- 语法 ：SELECT 列名 FROM 表名 WHERE 列名 [NOT] IN (SELECT 列名 FROM 表名 [WHERE 条件]); 
-- 查询张三和李四的订单信息

SELECT id FROM user WHERE uname IN ('张三','李四');  -- 查询张三和李四的用户信息
SELECT * FROM orderlist WHERE uid IN (SELECT id FROM `user` WHERE uname IN ('张三','李四'));

-- 结果是多行多列的：查询的结果可以作为一张虚拟表参与查询
-- 语法：SELECT 列名 FROM 表名 [别名],(SELECT 列名 FROM 表名 [WHERE 条件]) [别名] [WHERE 条件];
-- 查询订单表中id大于4的订单信息和所属用户信息

SELECT * FROM orderlist WHERE id > 4;		-- 查询id大于4的订单信息
SELECT
	u.*,o.*
FROM 
	`user` u,
	(SELECT * FROM orderlist WHERE id > 4) o
WHERE u.id = o.uid;

4. 自关联查询

-- 创建员工表
CREATE TABLE employee(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 员工编号
	ename VARCHAR(20),			-- 员工姓名
	mgr INT,				-- 上级编号
	salary DOUBLE				-- 员工工资
);
-- 添加数据
INSERT INTO employee VALUES (1001,'孙悟空',1005,9000.00),
(1002,'猪八戒',1005,8000.00),
(1003,'沙和尚',1005,8500.00),
(1004,'小白龙',1005,7900.00),
(1005,'唐僧',NULL,15000.00),
(1006,'武松',1009,7600.00),
(1007,'李逵',1009,7400.00),
(1008,'林冲',1009,8100.00),
(1009,'宋江',NULL,16000.00);
-- 查询所有员工的姓名及其直接上级的姓名，没有上级的员工也需要查询
/*
分析:员工信息 employee表
	条件：employee.mgr = employee.id
	查询左表的全部数据，和左右两张表有交集部分数据，左外连接
*/
SELECT 
	e.ename,leader.ename leaderName
FROM
	employee e			-- 员工表
LEFT JOIN
	employee leader		-- 领导表
ON
	e.mgr = leader.id;

5. 多表查询练习

-- 1.查询用户的编号、姓名、年龄。订单编号
/*	分析:用户的编号、姓名、年龄  user表      订单编号 orderlist表
	条件：user.id=orderlist.uid
*/
SELECT
	u.id,
	u.name,
	u.age,
	o.number
FROM
	USER u,
	orderlist o
WHERE
	u.id=o.uid;
-- 2.查询所有的用户。用户的编号、姓名、年龄。订单编号
/*	分析:用户的编号、姓名、年龄  user表    订单编号 orderlist表
	条件：user.id=orderlist.uid
	查询所有的用户，左外连接
*/
SELECT
	u.id,
	u.name,
	u.age,
	o.number
FROM
	USER u
LEFT OUTER JOIN
	orderlist o
ON
	u.id=o.uid;
-- 3.查询所有的订单。用户的编号、姓名、年龄。订单编号
/*	分析:用户的编号、姓名、年龄 user表    订单编号 orderlist表
	条件：user.id=orderlist.uid
	查询所有的订单，右外连接
*/
SELECT
	u.id,
	u.name,
	u.age,
	o.number
FROM
	USER u
RIGHT OUTER JOIN
	orderlist o
ON
	u.id=o.uid;
-- 4.查询用户年龄大于23岁的信息。显示用户的编号、姓名、年龄。订单编号
/*	分析:用户的编号、姓名、年龄 user表    订单编号 orderlist表
	条件：user.id=orderlist.uid AND user.age > 23
*/
SELECT
	u.id,
	u.name,
	u.age,
	o.number
FROM
	USER u,
	orderlist o
WHERE
	u.id=o.uid
	AND
	u.age > 23;
-- 5.查询张三和李四用户的信息。显示用户的编号、姓名、年龄。订单编号
/*	分析:用户的编号、姓名、年龄 user表   订单编号 orderlist表
	条件：user.id=orderlist.uid AND user.name IN ('张三','李四')
*/
SELECT
	u.id,
	u.name,
	u.age,
	o.number
FROM
	USER u,
	orderlist o
WHERE
	u.id=o.uid
	AND
	u.name IN ('张三','李四');
-- 6.查询商品分类的编号、分类名称。分类下的商品名称
/*	分析:商品分类的编号、分类名称 category表    商品名称 product表
	条件：category.id=product.cid
*/
SELECT
	c.id,
	c.name,
	p.name
FROM
	category c,
	product p
WHERE
	c.id=p.cid;
-- 7.查询所有的商品分类。商品分类的编号、分类名称。分类下的商品名称
/*	分析:商品分类的编号、分类名称 category表    商品名称 product表
	条件：category.id=product.cid
	查询所有的商品分类，左外连接
*/
SELECT
	c.id,
	c.name,
	p.name
FROM
	category c
LEFT OUTER JOIN
	product p
ON
	c.id=p.cid;
-- 8.查询所有的商品信息。商品分类的编号、分类名称。分类下的商品名称
/*	分析:商品分类的编号、分类名称  category表   商品名称 product表
	条件：category.id=product.cid
	查询所有的商品信息，右外连接
*/
SELECT
	c.id,
	c.name,
	p.name
FROM
	category c
RIGHT OUTER JOIN
	product p
ON
	c.id=p.cid;
-- 9.查询所有的用户和该用户能查看的所有的商品。显示用户的编号、姓名、年龄。商品名称
/*	分析:用户的编号、姓名、年龄 user表   商品名称 product表    中间表 us_pro
	条件：us_pro.uid=user.id AND us_pro.pid=product.id
*/
-- 内连接
SELECT
	u.id,
	u.name,
	u.age,
	p.name
FROM
	USER u,
	product p,
	us_pro up
WHERE
	up.uid=u.id
	AND
	up.pid=p.id;
-- 左外连接
SELECT 
	u.id,u.uname,u.age,p.pname
FROM
	`user` u
LEFT JOIN
	us_pro up
ON
	u.id = up.uid
LEFT JOIN
	product p
ON
	up.pid = p.id;
-- 10.查询张三和李四这两个用户可以看到的商品。显示用户的编号、姓名、年龄。商品名称
/*	分析:用户的编号、姓名、年龄 user表   商品名称 product表   中间表 us_pro
	条件：us_pro.uid=user.id AND us_pro.pid=product.id AND user.name IN ('张三','李四') 
*/
-- 左外链接
SELECT 
	u.id,u.uname,u.age,p.pname
FROM
	`user` u
LEFT JOIN
	us_pro up
ON
	u.id = up.uid
LEFT JOIN
	product p
ON
	up.pid = p.id
WHERE 
	u.uname IN ('张三','李四');
-- 内连接
SELECT
	u.id,
	u.name,
	u.age,
	p.name
FROM
	USER u,
	product p,
	us_pro up
WHERE
	up.uid=u.id
	AND
	up.pid=p.id
	AND
	u.name IN ('张三','李四');

Day03 存储过程和函数、触发器、事务

一、存储过程和函数

存储过程和函数：是事先经过编译并存储在数据库中的一段SQL语句的集合。
好处：(1)提高代码的复用性；(2)提高数据传输效率；(3)减少代码层面的业务处理；
区别：
- 存储函数必须要有返回值
- 存储过程可以无返回值

1. 存储过程

00 数据准备

-- 创建db6数据库
CREATE DATABASE db6;
-- 使用db6数据库
USE db6;
-- 创建学生表
CREATE TABLE student(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 学生id
	NAME VARCHAR(20),			-- 学生姓名
	age INT,				-- 学生年龄
	gender VARCHAR(5),			-- 学生性别
	score INT                               -- 学生成绩
);
-- 添加数据
INSERT INTO student VALUES (NULL,'张三',23,'男',95),(NULL,'李四',24,'男',98),
(NULL,'王五',25,'女',100),(NULL,'赵六',26,'女',90);

-- 按照性别进行分组，查询每组学生的总成绩。按照总成绩的升序排序
SELECT gender,SUM(score) getSum FROM student GROUP BY gender ORDER BY getSum ASC;

01 创建和调用存储过程

/*	创建存储过程	
	-- 修改分隔符为$
	DELIMITER $
	-- 标准语法
	CREATE PROCEDURE 存储过程名称(参数列表)
	BEGIN
		SQL 语句列表;
	END$
	-- 修改分隔符为分号
	DELIMITER ;
*/
-- 创建stu_group()存储过程，封装 分组查询总成绩，并按照总成绩升序排序的功能
DELIMITER $

CREATE PROCEDURE stu_group()
BEGIN
	SELECT gender,SUM(score) getSum FROM student GROUP BY gender ORDER BY getSum ASC;
END$

DELIMITER ;

-- 调用存储过程：CALL 存储过程名称(实际参数);
CALL stu_group();

02 查看和删除存储过程

/*	查询数据库中所有的存储过程
	SELECT * FROM mysql.proc WHERE db='数据库名称';
*/
-- 查看db6数据库中所有的存储过程
SELECT * FROM mysql.proc WHERE db='db6';
/*
	删除存储过程
	DROP PROCEDURE [IF EXISTS] 存储过程名称;
*/
DROP PROCEDURE IF EXISTS stu_group;

03 变量

/*	定义变量
	DECLARE 变量名 数据类型 [DEFAULT 默认值];
*/
-- 定义一个int类型变量，并赋默认值为10
DELIMITER $

CREATE PROCEDURE pro_test1()
BEGIN
	-- 定义变量
	DECLARE num INT DEFAULT 10;
	-- 使用变量
	SELECT num;
END$

DELIMITER ;

-- 调用pro_test1存储过程
CALL pro_test1();

/*	变量赋值-方式一
	SET 变量名 = 变量值;
*/
-- 定义一个varchar类型变量并赋值
DELIMITER $

CREATE PROCEDURE pro_test2()
BEGIN
	-- 定义变量
	DECLARE NAME VARCHAR(10);
	-- 为变量赋值
	SET NAME = '存储过程';
	-- 使用变量
	SELECT NAME;
END$

DELIMITER ;

-- 调用pro_test2存储过程
CALL pro_test2();

/*	变量赋值-方式二
	SELECT 列名 INTO 变量名 FROM 表名 [WHERE 条件];
*/
-- 定义两个int变量，用于存储男女同学的总分数
DELIMITER $

CREATE PROCEDURE pro_test3()
BEGIN
	-- 定义两个变量
	DECLARE men,women INT;
	-- 查询男同学的总分数，为men赋值
	SELECT SUM(score) INTO men FROM student WHERE gender='男';
	-- 查询女同学的总分数，为women赋值
	SELECT SUM(score) INTO women FROM student WHERE gender='女';
	-- 使用变量
	SELECT men,women;
END$

DELIMITER ;

-- 调用pro_test3存储过程
CALL pro_test3();

04 if语句

/*	if语句
	IF 判断条件1 THEN 执行的sql语句1;
	[ELSEIF 判断条件2 THEN 执行的sql语句2;]
	...
	[ELSE 执行的sql语句n;]
	END IF;
	
	定义一个int变量，用于存储班级总成绩
	定义一个varchar变量，用于存储分数描述
	根据总成绩判断：
		380分及以上   学习优秀
		320 ~ 380     学习不错
		320以下       学习一般
*/
-- 第一种：if
DELIMITER $
CREATE PROCEDURE pro_test4()
BEGIN
	-- 定义变量
	DECLARE total INT;
	DECLARE info VARCHAR(10);
	-- 查询总成绩，为total赋值
	SELECT SUM(score) INTO total FROM student;
	-- 对总成绩判断
	IF total > 380 THEN
		SET info = '学习优秀';
	ELSEIF total >= 320 AND total <= 380 THEN
		SET info = '学习不错';
	ELSE
		SET info = '学习一般';
	END IF;
	-- 查询总成绩和描述信息
	SELECT total,info;
END$
DELIMITER ;
-- 调用pro_test4存储过程
CALL pro_test4();

-- 第二种case
DELIMITER $
CREATE PROCEDURE stu_if2()
BEGIN
	-- 定义变量
	DECLARE total INT DEFAULT 0;
	DECLARE info VARCHAR(20);
	-- 查询总成绩，为total赋值
	SELECT SUM(score) INTO total FROM student;
	-- 对成绩进行判断
	CASE 
		WHEN  total > 380 THEN
			SET info = '学习优秀';
		WHEN  total <= 380 AND total >= 320 THEN
			SET info = '学习不错';
		ELSE
			SET info = '学习一般';
	END CASE;
	-- 查询结果
	SELECT total,info;
END$
DELIMITER ;
-- 调用stu_if2存储过程
CALL stu_if2();

05 参数传递

/*	参数传递
	CREATE PROCEDURE 存储过程名称([IN|OUT|INOUT] 参数名 数据类型)
	BEGIN
		SQL 语句列表;
	END$
*/
/*	输入总成绩变量，代表学生总成绩
	输出分数描述变量，代表学生总成绩的描述信息
	根据总成绩判断：
		380分及以上  学习优秀
		320 ~ 380    学习不错
		320以下      学习一般
*/
DELIMITER $
CREATE PROCEDURE pro_test5(IN total INT,OUT info VARCHAR(10))
BEGIN
	-- 对总成绩判断
	IF total > 380 THEN
		SET info = '学习优秀';
	ELSEIF total >= 320 AND total <= 380 THEN
		SET info = '学习不错';
	ELSE
		SET info = '学习一般';
	END IF;
END$
DELIMITER ;

-- 调用pro_test5存储过程
CALL pro_test5(350,@info);
CALL pro_test5((SELECT SUM(score) FROM student),@info);
SELECT @info;

06 while循环

/*	while循环
	初始化语句;
	WHILE 条件判断语句 DO
		循环体语句;
		条件控制语句;
	END WHILE;
*/
-- 计算1~100之间的偶数和
DELIMITER $
CREATE PROCEDURE pro_test6()
BEGIN
	-- 定义求和变量
	DECLARE result INT DEFAULT 0;
	-- 定义初始化变量
	DECLARE num INT DEFAULT 1;
	-- while循环
	WHILE num <= 100 DO
		IF num % 2 = 0 THEN
			SET result = result + num;
		END IF;		
		SET num = num + 1;
	END WHILE;	
	-- 查询求和结果
	SELECT result;
END$
DELIMITER ;
-- 调用pro_test6存储过程
CALL pro_test6();

2. 存储函数

/*	创建存储函数
	CREATE FUNCTION 函数名称([参数 数据类型])
	RETURNS 返回值类型
	BEGIN
		执行的sql语句;
		RETURN 结果;
	END$
*/
-- 定义存储函数，获取学生表中成绩大于95分的学生数量
DELIMITER $
CREATE FUNCTION fun_test1()
RETURNS INT
BEGIN
	-- 定义变量
	DECLARE s_count INT;
	-- 查询成绩大于95分的数量，为s_count赋值
	SELECT COUNT(*) INTO s_count FROM student WHERE score > 95;
	-- 返回统计结果
	RETURN s_count;
END$  
DELIMITER ;
-- 调用函数：SELECT 函数名称(实际参数);
SELECT fun_test1();
-- 删除函数：DROP FUNCTION 函数名称;
DROP FUNCTION fun_test1;

二、触发器

00. 数据准备

-- 创建db7数据库
CREATE DATABASE db7;
-- 使用db7数据库
USE db7;
-- 创建账户表account
CREATE TABLE account(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 账户id
	NAME VARCHAR(20),			-- 姓名
	money DOUBLE				-- 余额
);
-- 添加数据
INSERT INTO account VALUES (NULL,'张三',1000),(NULL,'李四',1000);
-- 创建日志表account_log
CREATE TABLE account_log(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 日志id
	operation VARCHAR(20),			-- 操作类型 (insert update delete)
	operation_time DATETIME,		-- 操作时间
	operation_id INT,			-- 操作表的id
	operation_params VARCHAR(200)       	-- 操作参数
);

1. INSERT型触发器

/*	创建触发器
	DELIMITER $
	CREATE TRIGGER 触发器名称
	BEFORE|AFTER INSERT|UPDATE|DELETE
	ON 表名
	FOR EACH ROW
	BEGIN
		触发器要执行的功能;
	END$
	DELIMITER ;
*/
-- 创建INSERT型触发器。用于对account表新增数据进行日志的记录
DELIMITER $
CREATE TRIGGER account_insert
AFTER INSERT
ON account
FOR EACH ROW
BEGIN
	INSERT INTO account_log VALUES (NULL,'INSERT',NOW(),new.id,CONCAT('插入后{id=',new.id,',name=',new.name,',money=',new.money,'}'));
END$
DELIMITER ;
-- 向account表添加一条记录
INSERT INTO account VALUES (NULL,'王五',2000);
-- 查询account表
SELECT * FROM account;
-- 查询account_log表
SELECT * FROM account_log;

2. UPDATE型触发器

/*	创建触发器
	DELIMITER $
	CREATE TRIGGER 触发器名称
	BEFORE|AFTER INSERT|UPDATE|DELETE
	ON 表名
	FOR EACH ROW
	BEGIN
		触发器要执行的功能;
	END$
	DELIMITER ;
*/
-- 创建UPDATE型触发器。用于对account表修改数据进行日志的记录
DELIMITER $
CREATE TRIGGER account_update
AFTER UPDATE
ON account
FOR EACH ROW
BEGIN
	INSERT INTO account_log VALUES (NULL,'UPDATE',NOW(),new.id,CONCAT('更新前{id=',old.id,',name=',old.name,',money=',old.money,'}','更新后{id=',new.id,',name=',new.name,',money=',new.money,'}'));
END$
DELIMITER ;
-- 修改account表中李四的金额为2000
UPDATE account SET money=2000 WHERE id=2;
-- 查询account表
SELECT * FROM account;
-- 查询account_log表
SELECT * FROM account_log;

3. DELETE型触发器

/*	创建触发器
	DELIMITER $
	CREATE TRIGGER 触发器名称
	BEFORE|AFTER INSERT|UPDATE|DELETE
	ON 表名
	FOR EACH ROW
	BEGIN
		触发器要执行的功能;
	END$
	DELIMITER ;
*/
-- 创建DELETE型触发器。用于对account表删除数据进行日志的记录
DELIMITER $
CREATE TRIGGER account_delete
AFTER DELETE
ON account
FOR EACH ROW
BEGIN
	INSERT INTO account_log VALUES (NULL,'DELETE',NOW(),old.id,CONCAT('删除前{id=',old.id,',name=',old.name,',money=',old.money,'}'));
END$
DELIMITER ;
-- 删除account表中王五
DELETE FROM account WHERE id=3;
-- 查询account表
SELECT * FROM account;
-- 查询account_log表
SELECT * FROM account_log;

4. 触发器的查看和删除

/*	查看触发器
	SHOW TRIGGERS;
*/
-- 查看触发器
SHOW TRIGGERS;
/*	删除触发器
	DROP TRIGGER 触发器名称;
*/
-- 删除account_delete触发器
DROP TRIGGER account_delete;

三、事务

事务指逻辑上的一组操作，组成这组操作的各个单元，要么全部成功，要么全部失败。
一条或多条SQL语句组成一个执行单元，其特点是这个单元要么同时成功要么同时失败。

1. 事务的四大特征

原子性
- 原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响
一致性
- 一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态
隔离性
- 隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离
持久性
- 持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作

2. 事务的隔离级别

1	读未提交	read uncommitted	脏读、不可重复读、幻读
2	读已提交	read committed	不可重复读、幻读
3	可重复读	repeatable read	幻读
4	串行化	serializable	无

3. 现象解释

问题	现象
脏读	是指在一个事务处理过程中读取了另一个事务中未提交的的数据 , 导致两次查询结果不一致
不可重复读	是指在一个事务处理过程中读取了另一个事务中修改并已提交的数据, 导致两次查询结果不一致
幻读	一个事务执行select 查询某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入。或者查询记录不存在时执行delete删除，却发现删除成功

Day04 存储引擎、索引、锁

一、存储引擎

用来决定表中的数据在计算机中的存储方式

-- 查询数据库支持的存储引擎
SHOW ENGINES;

-- 查询某个数据库中所有数据表的存储引擎
-- SHOW TABLE STATUS FROM 数据库名称;
SHOW TABLE STATUS FROM db4;

-- 查询某个数据库中某个表的存储引擎
-- SHOW TABLE STATUS FROM 数据库名称 WHERE NAME = '数据表名称';
SHOW TABLE STATUS FROM db4 WHERE NAME = 'category';

/*
	创建数据表指定存储引擎
	CREATE TABLE 表名(
	      列名,数据类型,
	      ...
	)ENGINE = 引擎名称;
*/
CREATE TABLE engine_test(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10)
)ENGINE = MYISAM;

SHOW TABLE STATUS FROM db4;

-- 修改数据表的存储引擎
-- ALTER TABLE 表名 ENGINE = 引擎名称;
ALTER TABLE engine_test ENGINE = INNODB;

二、索引

是一种特殊的数据结构，由数据表的一列或者多列组合而成，可以用来快速查询数据表中某一特定值的记录。

1. 索引操作

00–数据准备

-- 创建db9数据库
CREATE DATABASE db9;
-- 使用db9数据库
USE db9;
-- 创建student表
CREATE TABLE student(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10),
	age INT,
	score INT
);
-- 添加数据
INSERT INTO student VALUES (NULL,'张三',23,98),(NULL,'李四',24,95),
(NULL,'王五',25,96),(NULL,'赵六',26,94),(NULL,'周七',27,99);

01–创建和查询索引

-- 创建索引
-- CREATE [UNIQUE|FULLTEXT] INDEX 索引名称 [USING 索引类型]  -- 默认是BTREE ON 表名(列名...);
-- 为student表中的name列创建一个普通索引
CREATE INDEX idx_name ON student(NAME); 
-- 为student表中的age列创建一个唯一索引
CREATE UNIQUE INDEX idx_age ON student(age);
-- 查询索引 SHOW INDEX FROM 表名;
SHOW INDEX FROM student;
-- 查询db4数据库中的product表 (外键列自带外键索引)
SHOW INDEX FROM product;

02–添加和删除索引

/*	ALTER添加索引
	-- 普通索引
	ALTER TABLE 表名 ADD INDEX 索引名称(列名);
	-- 组合索引
	ALTER TABLE 表名 ADD INDEX 索引名称(列名1,列名2,...);
	-- 主键索引
	ALTER TABLE 表名 ADD PRIMARY KEY(主键列名); 
	-- 外键索引(添加外键约束，就是外键索引)
	ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);
	-- 唯一索引
	ALTER TABLE 表名 ADD UNIQUE 索引名称(列名);
	-- 全文索引
	ALTER TABLE 表名 ADD FULLTEXT 索引名称(列名);
*/
-- 为student表中score列添加唯一索引
ALTER TABLE student ADD UNIQUE idx_score(score);
-- 查询student表的索引
SHOW INDEX FROM student;

-- 删除索引	DROP INDEX 索引名称 ON 表名;
DROP INDEX idx_score ON student;

03–BTree数据结构及原理

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-O3MWnfZg-1603787915435)(E:\黑马培训\39期就业班\TyporaImages\BTree原理-1600683370758.jpg)]

04–B+Tree数据结构及原理

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CCrc1Uot-1603787915443)(E:\黑马培训\39期就业班\TyporaImages\B+Tree原理.jpg)]

三、锁

为了保证数据的一致性和安全性。

1. 锁的分类

01–按操作分类

共享锁：也叫读锁。针对同一份数据，多个事务读取操作可以同时加锁而不互相影响，但是不能修改数据记录。
排他锁：也叫写锁。当前的操作没有完成前,会阻断其他操作的读取和写入

02–按粒度分类

表级锁：操作时，会锁定整个表。开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低。偏向于MyISAM存储引擎
行级锁：操作时，会锁定当前操作行。开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高。偏向于InnoDB存储引擎

03–按使用方式分类

悲观锁：每次查询数据时都认为别人会修改，很悲观，所以查询时加锁。
乐观锁：每次查询数据时都认为别人不会修改，很乐观，但是更新时会判断一下在此期间别人有没有去更新这个数据

2. InNoDB的共享锁和排他锁

01–共享锁

当一个事务在索引列中的一行加入了共享锁，那么InnoDB将锁定该行。另一个事务只能查询，而不能修改这一行数据。
当一个事务在非索引列的一行加入了共享锁，那么InnoDB将锁定全表，另一个事务只能查询，而不能修改全表数据。

02–排他锁

当一个事务在索引列中的一行加入了排他锁，那么InnoDB将锁定该行。另一个事务只能做普通查询，不能加锁查询；另外不能该修改这一行数据。
当一个事务在非索引列中的一行加入了排他锁，那么InnoDB将锁定全表。另一个事务只能做普通查询，不能加锁查询；另外也不能修改全表。

HOW INDEX FROM student;
– 查询db4数据库中的product表 (外键列自带外键索引)
SHOW INDEX FROM product;


##### 02--添加和删除索引

```mysql
/*	ALTER添加索引
	-- 普通索引
	ALTER TABLE 表名 ADD INDEX 索引名称(列名);
	-- 组合索引
	ALTER TABLE 表名 ADD INDEX 索引名称(列名1,列名2,...);
	-- 主键索引
	ALTER TABLE 表名 ADD PRIMARY KEY(主键列名); 
	-- 外键索引(添加外键约束，就是外键索引)
	ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);
	-- 唯一索引
	ALTER TABLE 表名 ADD UNIQUE 索引名称(列名);
	-- 全文索引
	ALTER TABLE 表名 ADD FULLTEXT 索引名称(列名);
*/
-- 为student表中score列添加唯一索引
ALTER TABLE student ADD UNIQUE idx_score(score);
-- 查询student表的索引
SHOW INDEX FROM student;

-- 删除索引	DROP INDEX 索引名称 ON 表名;
DROP INDEX idx_score ON student;

三、锁

为了保证数据的一致性和安全性。

1. 锁的分类

01–按操作分类

共享锁：也叫读锁。针对同一份数据，多个事务读取操作可以同时加锁而不互相影响，但是不能修改数据记录。
排他锁：也叫写锁。当前的操作没有完成前,会阻断其他操作的读取和写入

02–按粒度分类

表级锁：操作时，会锁定整个表。开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低。偏向于MyISAM存储引擎
行级锁：操作时，会锁定当前操作行。开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高。偏向于InnoDB存储引擎

03–按使用方式分类

悲观锁：每次查询数据时都认为别人会修改，很悲观，所以查询时加锁。
乐观锁：每次查询数据时都认为别人不会修改，很乐观，但是更新时会判断一下在此期间别人有没有去更新这个数据

2. InNoDB的共享锁和排他锁

01–共享锁

当一个事务在索引列中的一行加入了共享锁，那么InnoDB将锁定该行。另一个事务只能查询，而不能修改这一行数据。
当一个事务在非索引列的一行加入了共享锁，那么InnoDB将锁定全表，另一个事务只能查询，而不能修改全表数据。

02–排他锁

当一个事务在索引列中的一行加入了排他锁，那么InnoDB将锁定该行。另一个事务只能做普通查询，不能加锁查询；另外不能该修改这一行数据。
当一个事务在非索引列中的一行加入了排他锁，那么InnoDB将锁定全表。另一个事务只能做普通查询，不能加锁查询；另外也不能修改全表。

lv_yao_wen

关注

5
点赞
踩
5

收藏

觉得还不错? 一键收藏
2
评论
程序员福利-MySql全套笔记

关系型数据库：Oracle MySQL sqlserver非关系型数据库：redis(内存数据库)SQL(Structured Query Language)结构化查询语言SQL分类DDL(Data Definition Language)：数据定义语言。用来操作数据库、表、列、等。DML(Data Manipulation Language)：数据操作语言。用来对数据库中表的数据进行增删改。DQL(Data Query Language)：数据查询语言。用于查询数据库中表的记录。D.
复制链接

扫一扫