MySQL学习笔记

林凯瑞

于 2023-08-14 00:06:58 发布

阅读量76

点赞数

分类专栏：计算机学习笔记文章标签： mysql 学习笔记

本文链接：https://blog.csdn.net/weixin_46629928/article/details/132267483

版权

计算机学习笔记专栏收录该内容

3 篇文章 0 订阅

订阅专栏

基础语法

创建数据库

语法：CREATE DATABASE 数据库名字

删除数据库

语法：DROP DATABASE 数据库名字

创建一个使用utf-8字符集的数据库
CREATE DATABASE `roy_db01` CHARACTER SET utf8

创建一个使用utf-8字符集,并带校对规则的数据库
-- 校对规则: utf8_bin区分大小写,utf8_general_ci不区分大小写(相同字母无论大小写都会输出)
CREATE DATABASE `roy_db01` CHARACTER SET utf8 COLLATE utf8_bin

查看当前数据库服务器中的所有数据库
SHOW DATABASES

表添加列

语法: ALTER TABLE tablename ADD(字段1 字段1属性,字段2 字段属性2,字段n);

表修改列

语法: ALTER TABLE tablename MODIFY(字段字段属性);

表删除列

语法: ALTER TABLE tablename DROP(column);

修改表名

语法: RENAME table 表名 to 新表名

修改字符集

语法: ALTER TABLE 表名 character set 字符集;`

给数据库创建表

CREATE TABLE student (id INT NOT NULL, name VARCHAR(32) NOT NULL)

# 进入指定数据库
USE `roy_db01`

# 数据库数据类型
CREATE TABLE `student_one` (
`id` INT(4),	-- INT(size) -> 长度为4的整型
`name` VARCHAR(32) -- VARCHAR(size) -> 长度为32的字符串
)

# 创建表的练习
-- 字段 属性
-- Id 	整型
-- name 字符型
-- sex 	字符型
-- birthday 日期型
-- entry_date 日期型
-- job 	字符型
-- salary 小数型
-- resume 文本型
CREATE TABLE `employee`(
		id INT,
		`name` VARCHAR(32),
		sex CHAR(1),
		birthday DATE,
		entry_date DATETIME,
		job	VARCHAR(32),
		salary DOUBLE,
		`resume` text
) CHARSET utf8 COLLATE utf8_bin ENGINE INNODB;
# 给表添加一条数据
INSERT INTO `employee`
		-- VALUES(1,'张三','2000-11-1','后端',6000,'精通Java');
		-- VALUES(2,'李四','2000-11-1','前端',5000,'精通JavaScript'),
		-- VALUES(3,'王五','2000-11-1','后端',6000,'Python和Golang');
		VALUES(4,'赵六','2001-1-10','测试',5000,'Postman');
SELECT * from `employee`

# 创建学生表
CREATE TABLE `student`(
	id INT NOT NULL,
	`name` VARCHAR(32) NOT NULL,
	class VARCHAR(32) NOT null,
	gpa double NOT null
)
# 给表添加数据
INSERT INTO `student`
		-- VALUES(1,'张三','计算机学院','软件2010',3.75)
		-- VALUES(2,'李四','计算机学院','软件2010',3.40)
		-- VALUES(3,'王五','计算机学院','软件2009',3.65)
		VALUES(4,'赵六','计算机学院','软件2008',3.75)
SELECT * FROM student

# 应用实例
-- 表名修改为employee
rename TABLE emp TO employee
-- 员工表emp增加一个image列,vachar类型(要求在resume后面)
ALTER TABLE employee ADD image varchar(32) NOT null AFTER resume
-- 修改job列,长度为60
ALTER TABLE employee MODIFY job VARCHAR(60) NOT NULL DEFAULT ''
-- 删除sex列
ALTER TABLE employee DROP sex
-- 修改表的字符集为utf8
ALTER TABLE employee character set utf8
-- 列名name修改为user_name
ALTER TABLE employee CHANGE `name` user_name VARCHAR(32) NOT NULL DEFAULT '' 
-- 查看表的列
DESC employee

使用 INSERT 语句向表中插入数据

语法: INSERT INTO tablename [(column[column])] VALUES (values[,values]);

INSERT INTO `employee`
         VALUES(2,'李四','2000-11-1','2010-4-1 11:10:10','前端程序员',6000,'精通JavaScript');	
         -- 插入的数据要和字段的数据类型所对应

使用 Update 语句更新数据

UPDATE employee SET salary = 6000	-- 如果没有带where条件,会修改所有的记录,小心!
UPDATE employee SET salary = 8000 WHERE job = '后端'	-- 如果要修改多个字段可以使用 SET 字段1=值1,字段n=值n..
UPDATE employee SET resume = 'Java' where user_name = '张三'

使用 Delete 语句删除数据

DELETE FROM employee WHERE job = '前端'	-- 删除表中job为前端的数据
DELETE FROM employee -- 如果不使用where条件,则会删除表中所有的数据

使用 Select 语句查询数据

语法: SELECT * FROM TABLE WHERE NAME = 'data'
-- 其中 * 表示所有字段, FROM TABLE 表示从哪个表, WHERE 从哪个字段, NAME 查询的名字
SELECT * FROM student WHERE NAME = 'Tom'

-- 练习1: 查询job为后端的员工
SELECT * FROM employee where job = '后端'

-- 练习2: 查询job为后端并且简历为Java的员工
SELECT * FROM employee where job = '后端' AND	resume = 'Java'

函数

分组函数

# 查询每个学生的平均成绩
SELECT 姓名, AVG(成绩) FROM 学生信息表 GROUP BY 姓名;

# 查询每个年龄段的平均成绩
SELECT 年龄, AVG(成绩) FROM 学生信息表 GROUP BY 年龄;

# 查询男女生的平均成绩
SELECT 性别, AVG(成绩) FROM 学生信息表 GROUP BY 性别;

# 查询每个年龄段男女生的平均成绩
SELECT 年龄, 性别, AVG(成绩) FROM 学生信息表 GROUP BY 年龄, 性别;

字符串函数

# 1. CHARSET(str): 返回字串字符集
SELECT CHARSET(job) FROM employee;

# 2. CONCAT(str1,str2,...): 连接字串
SELECT CONCAT(job,resume) FROM employee;

# 3. INSTR(str,substr): 返回subtring在string中出现的位置,没有则返回0
SELECT INSTR('后端学Java','Java') FROM DUAL
-- DUAL是亚元表,可当作测试表

# 4. UCASE(str): 转换成大写
SELECT UCASE(resume) FROM employee;

# 5. LCASE(str): 转换成小写
SELECT LCASE(resume) FROM employee;

# 6. LEFT(str,len): 从str中的左边起取len个字符
SELECT LEFT('java python golang vue',4);

# 7. REPLACE(str,from_str,to_str): 在str中用to_str替换from_str
SELECT REPLACE(name,'张三','Peter Zhang') FROM employee;

# 8. STRCMP(expr1,expr2): 逐字符比较两字串大小
SELECT STRCMP('hsp','Hasp') FROM DUAL;

# 9. SUBSTRING(str FROM pos FOR len): 从str的pos开始[从1开始计算],取len个字符
-- 从 name 列的第一个位置开始取出 2个字符
SELECT SUBSTRING(name,1,2) FROM employee;

# 10. LENGTH(str): 返回字符串长度[字节返回]
SELECT LENGTH('hsp') FROM DUAL;
SELECT LENGTH('韩顺平') FROM DUAL;

# 11. LTRIM(str): 去除左边空格
SELECT LTRIM(' HSP') FROM DUAL;
# 12. RTRIM(str): 去除后边空格
SELECT RTRIM('HSP ') FROM DUAL;
# 13. TRIM([remstr FROM] str): 去除两边空格
SELECT TRIM(' HSP ') FROM DUAL;

-- 练习7: 以首字母小写的方式显示所有员工姓名
SELECT CONCAT(LCASE(SUBSTRING(name,1,1)), SUBSTRING(name,2)) AS newNames
			FROM employee;

日期相关函数

1. CURRENT_DATE() 当前日期
SELECT CURRENT_DATE() FROM DUAL;

2. CURRENT_TIME() 当前时间
SELECT CURRENT_TIME() FROM DUAL;

3. CURRENT_TIMESTAMP() 当前时间戳
SELECT CURRENT_TIMESTAMP() FROM DUAL;

4. NOW() 当前时间
5. DATEDIFF() 两个日期之差

-- 练习1: 显示所有员工和入职时间 DATE()只显示日期不显示分秒
SELECT `name`,DATE(jobTime) FROM employee;

-- 练习2: 请查询10分钟以内的入职员工
SELECT * FROM employee WHERE DATE_ADD(jobTime,INTERVAL 10 MINUTE) >= NOW();

-- 练习3: 请求出2011-11-11 和 1990-1-1 相差多少天
SELECT DATEDIFF('2011-11-11','1990-01-01') FROM dual;

数学相关函数

1. COUNT 统计函数
-- 练习2: 统计employee表里有多少员工
SELECT COUNT(*) FROM employee
-- 练习3: 统计employye表里工资大于6000的员工
SELECT COUNT(*) FROM employee WHERE salary > 6000

2. SUM 合计函数
-- 练习4: 统计employee表里工资总和
SELECT SUM(salary) FROM employee

3. AVG 平均数函数
-- 练习5: 统计employee表里平均工资
SELECT AVG(salary) FROM employee

4. MAX和MIN 最大值和最小值
-- 练习6: 统计employee表里工资的最大值和最小值
SELECT MAX(salary) AS 'MAX_SALARY', MIN(salary) AS 'MIN_SALARY' FROM employee;

5. ABS(X) 返回绝对值
SELECT ABS(salary) FROM employee;

6. FORMAT(X,D) 保留小数D位
SELECT FORMAT(AVG(salary),2) FROM employee;	-- 返回工资的平均数(保留2位小数)

7. LEAST(value1,value2,...) 求最小值
SELECT LEAST(2,-1,0) FROM DUAL;

流程控制

IF 语句

**语法：IF(expr1,expr2,expr3) -> 如果expr1为真则返回expr2,否则返回expr3 **

示例: SELECT IF(TRUE,‘北京’,‘上海’) FROM DUAL;

IFNULL 语句

语法：IFNULL(expr1,expr2) -> 如果expr1不为空则返回expr1,否则返回expr2

示例: SELECT IFNULL(‘上海’,‘北京’) FROM DUAL;

select case when … then… 语句

类似于Java中的If…else

语法：SELECT CASE WHEN expr1 THEN expr2 ELSE expr3 END; – 如果expr1为true则返回expr2,否则返回expr3

-- 练习1: 查询employee表,如果salary为null则显示0.0
SELECT name, IF(salary IS NULL,0.0,salary) FROM employee

-- 练习2: 如果employee表中的job是'后端'则显示'公司的后端主要采用Java语言',否则正常显示
SELECT `name`, (SELECT CASE
				WHEN job = '后端' THEN '公司的后端主要采用Java语言'
				ELSE job END ) AS 'job' FROM employee;

查询加强

使用 where 子句

-- 练习1: 如何查找在2016年后入职的员工
SELECT * FROM employee WHERE jobTime > '2016-01-01';

使用 like 操作符 ( 模糊查询 )

% : 表示0到多个字符
_ : 表示单个字符

-- 练习1: 如何显示名字开头为'李'的员工和他的工资
SELECT `name`,salary FROM employee WHERE `name` LIKE '李%';

-- 练习2: 如何显示第二个字为'四'的员工和他的工资
SELECT `name`,salary FROM employee WHERE `name` LIKE '_四%';

ORDER BY 子句

默认为升序, DESC降序

-- 练习1: 查询员工工资(降序排序)按照工资从低到高的顺序
SELECT * FROM employee ORDER BY salary DESC;

分页查询

**语法: SELECT … LIMIT START, ROWS; – START为从哪一行开始, ROWS为取出多少行 **

-- 练习1: 按员工的id升序取出,显示3条记录
SELECT id,`name`	FROM employee LIMIT 0,3;

-- 练习2: 按员工的id降序取出,显示5条记录,请分别显示第1页第2页..
-- 公式: SELECT * FROM tableName ORDER BY colName LIMIT 每页显示记录数 * (第几页-1), 每页显示记录数
SELECT id,`name` FROM employee 
			ORDER BY id DESC
			LIMIT 20,5;

增强 GROUP BY 的使用

-- (1) 显示每种岗位的员工总数和平均工资
SELECT COUNT(*), AVG(salary), job FROM employee GROUP BY job;
-- (2) 显示员工总数
SELECT COUNT(*) FROM employee;
-- (3) 显示job为后端的总人数
SELECT COUNT(DISTINCT job = '后端') FROM employee
-- (4) 显示job为后端和job为测试的员工
SELECT COUNT(job = '后端') AS '后端岗', COUNT(job = '测试') AS '测试岗' FROM employee;
-- (5) 显示员工工资的最大差额
SELECT MAX(salary) - MIN(salary) FROM employee;

GROUP BY 应用案例

-- 统计各个岗位group by的平均工资 avg
-- 并且是大于1000的 having, 并且按照平均工资从高到低排序 order by,  取出前两条记录
SELECT job, AVG(salary) AS avg_sal FROM employee 
			GROUP BY job 
			HAVING avg_sal > 1000
			ORDER BY avg_sal DESC
			LIMIT 0,2

多表查询

-- 练习1: 显示学生名、学生所在二级学院以及毕业去向(岗位明细)
-- 当我们需要指定显示某个表的列时,语法为 " 表名.列名 "
SELECT student.name, student.college, employee.department, employee.job
			FROM student, employee
			WHERE student.name = employee.name;

-- 练习2: 显示后端员工且在校期间GPA达3.6以上的
-- 写SQL -> 先写简单的,然后再加入过滤条件(这样思路就会比较清晰)
SELECT student.name, student.gpa, employee.name, employee.job
			FROM student, employee
			WHERE student.name = employee.name 
			AND employee.job = '后端' 
			AND student.gpa > 3.6

多表查询的自连接

在同一张表的连接查询 -> [即将同一张表看作为两张表]

-- 练习1: 列出student表中学生姓名以及他的导师姓名
SELECT stu.name AS '学生', teacher.name AS '导师'
			FROM student stu, student teacher
			WHERE stu.teacher = teacher.name

子查询

指嵌入在其它SQL语句中的select语句,也叫嵌套查询

单行子查询

只返回一行数据的子查询语句

练习1: 如何显示与张三同个班级的所有学生?
-- 分析: 先得到班级编号再将它作为where的条件
SELECT * FROM student
		WHERE college = (
				SELECT college
					FROM student
					WHERE name = '张三')

多行子查询

返回多行数据的子查询语句使用关键字IN

-- 练习1: 如何显示同个部门不同岗位的员工信息,但不显示后端岗位的信息
-- DISTINCT: 不显示重复的数据
SELECT * FROM employee
			WHERE job IN (
						SELECT DISTINCT job 
						FROM employee
						WHERE department = 'IT部'
			) AND job != '后端'
			
-- 练习2 : 查询每个部门中每个岗位的最高工资 max + group by
SELECT department, job, MAX(salary)
			FROM employee
			GROUP BY department, job;
			
-- 练习3: 将子查询当作临时表来使用
SELECT temp.name, temp.job, temp.max_salary
		FROM (
				SELECT name,department, job, MAX(salary) AS max_salary
					FROM employee
					GROUP BY department, job
		) temp
		WHERE temp.job = '后端'

all 和 any 的使用

-- 练习1: 显示工资比IT部门所有员工的工资都高的员工信息
SELECT `name`,salary,department
			FROM employee
			WHERE salary > ALL( SELECT salary
                              		FROM employee
                               		WHERE department = 'IT部' )
												
-- 练习2: 显示工资比IT部门其中一个员工的工资高的员工信息
SELECT `name`,salary,department
			FROM employee
			WHERE salary > ANY( SELECT salary
                               		FROM employee
                              		WHERE department = 'IT部' )

多列子查询

查询返回多列数据

-- 练习1: 查询与张三部门和岗位相同的所有雇员且不包含张三本人
SELECT `name`,department,job
			FROM employee
			WHERE (department,job) = (
                SELECT department,job
                FROM employee
                WHERE `name` = '张三' )
			AND `name` != '张三'

子查询应用案例

练习1 : 查找每个部门工资高于本部门平均工资的员工信息

-- 1. 先得到每个部门的名字和平均工资
SELECT department, AVG(salary)
		FROM employee
		GROUP BY department
-- 2. 把上面的结果当作子查询,和employee进行多表查询
SELECT `name`, salary, temp.avg_salary, employee.department
		FROM employee,( SELECT department, AVG(salary) AS avg_salary
                      	 	FROM employee
                       		GROUP BY department ) temp
		WHERE employee.department = temp.department AND employee.salary > temp.avg_salary

练习2 : 查找每个部门信息(包括:部门名、平均工资、最高工资)和人员数量

SELECT emp.department, AVG(salary), MAX(salary), temp.person_num
			FROM employee emp,( 
                SELECT department, COUNT(department) AS person_num
                FROM employee
                GROUP BY department ) temp			
			WHERE temp.department = emp.department
			GROUP BY department

表的复制

自我复制数据(蠕虫数据): 为了测试数据,即可以使用此法为表创建海量数据

如何自我复制数据?

-- 1. 先创建一张空表 my_table01
CREATE TABLE my_table01 (
		id INT,
		`name` VARCHAR(32),
		department VARCHAR(60),
		job VARCHAR(32),
		salary DOUBLE 
);
-- 2. 把 employee 表的数据复制到 my_table01
INSERT INTO my_table01
		(id, `name`, department, job, salary)
		SELECT id, `name`, department, job, salary FROM employee
-- 3. 自我复制: 将查询到的数据再次插入到表里取实现蠕虫复制
INSERT INTO my_table01
		SELECT * FROM my_table01

如何去重?

思路:
(1) 先创建一张临时表my_temp, 该表的结构和 my_table02 一致
(2) 把 my_table02 的记录, 通过 distinct 关键字处理后, 把记录复制到 my_temp
(3) 清除掉 my_table02 的记录
(4) 把 my_temp 表的记录复制到 my_table02
(5) drop掉临时表 my_temp

-- (1) 先创建一张临时表my_temp, 该表的结构和 my_table02 一致
CREATE TABLE my_temp LIKE my_table02
-- (2) 把 my_table02 的记录, 通过 distinct 关键字处理后, 把记录复制到 my_temp
INSERT INTO my_temp
		SELECT DISTINCT * FROM my_table02
-- (3) 清除掉 my_table02 的记录
DELETE FROM my_table02
-- (4) 把 my_temp 表的记录复制到 my_table02
INSERT INTO my_table02
		SELECT * FROM my_temp
-- (5) drop掉临时表 my_temp 
DROP TABLE my_temp

合并查询

将多个查询条件合并为一条查询语句

SELECT `name`, job, salary FROM employee WHERE salary > 8000 -- 这是单个查询语句

-- 1. union all: 将两个查询结果合并,结果不会去重
SELECT `name`, job, salary FROM employee WHERE salary > 8000
UNION ALL
SELECT `name`, job, salary FROM employee WHERE jobTime = '2015-10-10'

-- 2. union: 将两个查询结果合并,结果会去重
SELECT `name`, job, salary FROM employee WHERE salary > 8000
UNION
SELECT `name`, job, salary FROM employee WHERE jobTime = '2015-10-10'

外连接

前面学习到的查询是利用 where 子句对两张表或者多张表,形成笛卡尔积进行筛选,根据关联条件 ,显示所有匹配的记录,匹配不上的,不显示。[内连接,在实际开发中使用的多]

如果要显示匹配不上的,这个时候就要利用外连接。

语法: SELECT … FROM 表1 LEFT JOIN 表2 ON 条件

1. 右外连接: 如果右侧的表完全显示,即为右连接
2. 左外连接: 如果左侧的表完全显示,即为左连接

-- 练习1: 显示所有人的resume,也要显示所有人的学校
-- 未使用左外连接
SELECT emp.`name`, stu.university, emp.resume
		FROM student stu, employee emp
		WHERE stu.id = emp.id
-- 改成左外连接
SELECT emp.`name`, stu.university, emp.resume
		FROM employee emp LEFT JOIN student stu
		ON stu.id = emp.id

-- 练习2: 显示所有人的学校和resume,如果没有名字匹配则显示空 (使用右外连接)
SELECT emp.`name`, stu.university, emp.resume
		FROM student stu RIGHT JOIN employee emp
		ON stu.id = emp.id

约束

主键 PRIMARY KEY

主键的值不能重复而且不能为null, 一张表最多只能有一个主键,但可以是复合主键。

主键的指定方式有两种:

直接在字段后指定 -> 字段名 primary key
在表定义最后写 -> primary key(列名)

CREATE TABLE `my_table01` (
    id INT PRIMARY KEY,	-- 表示 id 列为主键
    `name` VARCHAR(32) 
);
		
-- 复合主键的演示
CREATE TABLE `my_table01` (
    id INT,
    `name` VARCHAR(32),
    PRIMARY KEY (id, `name`) 
);

唯一 UNIQUE

UNIQUE使用细节:

如果没有指定 not null, 则 UNIQUE 字段可以有多个null.
一张表可以有多个unique.

CREATE TABLE `my_table01` (
	id INT UNIQUE,	-- 表示 id 列不能重复
	`name` VARCHAR(32) UNIQUE -- 表示name不能重复
);

外键

用于定义主表与从表之间的关系

语法: FOREIGN KEY (本表字段名) REFERENCES 主表名(主键名或unique字段名)

外键约束需要定义在从表上, 主表则必须具有主键约束或是unique约束, 当定义外键约束后, 要求外键列数据必须在主表的主键列存在或是为null

-- 外键演示
-- 1. 创建主表 my_class01
CREATE TABLE my_class01(
	id INT PRIMARY KEY,
	`name` VARCHAR(32) NOT NULL	DEFAULT '' 
);
-- 2. 创建从表 my_stu01
CREATE TABLE my_stu01(
	id INT PRIMARY KEY,
	`name` VARCHAR(32) NOT NULL	default '' ,
	class_id INT,
	-- 下面指定外键关系
	FOREIGN KEY (class_id) REFERENCES my_class01 (id) 
);
-- 3. 测试数据
INSERT INTO	my_class01
	VALUES(100,'java'), (200,'web')
SELECT * FROM my_class01
INSERT INTO my_stu01
	VALUES(1,'Tom',100), (2,'Jack',200)
SELECT * FROM my_stu01

外键案例 (商店)

CREATE DATABASE `store_db`; -- 创建名为 store_db 的数据库

USE `store_db`; -- 进入指定的数据库

-- 商品goods
CREATE TABLE `goods`(
    goods_id INT PRIMARY KEY,	-- 商品号
    goods_name VARCHAR(64) NOT NULL DEFAULT '',	-- 商品名
    unitprice DECIMAL(10,2) NOT NULL DEFAULT 0 
    CHECK (unitprice >= 1.0 AND unitprice <= 9999.0), -- 单价
    category INT NOT NULL DEFAULT 0, -- 商品类别
    provider VARCHAR(32) NOT NULL DEFAULT ''  -- 供货商
);	
-- 客户customer
CREATE TABLE `customer`(
    customer_id CHAR(8) PRIMARY KEY, -- 客户号
    `name` VARCHAR(64) NOT NULL	DEFAULT '', -- 客户名
    address VARCHAR(64) NOT NULL DEFAULT '', -- 客户地址
    email VARCHAR(64) UNIQUE NOT NULL, -- 客户电邮
    sex ENUM('男','女') NOT NULL, -- 性别 ENUM为枚举类型
    card_id CHAR(18)
);
-- 购买订单purchase
CREATE TABLE `purchase`(
    order_id INT UNSIGNED PRIMARY KEY,
    customer_id CHAR(8) NOT NULL DEFAULT '',  -- 外键约束在后
    goods_id INT NOT NULL DEFAULT 0,	-- 外键约束在后
    nums INT NOT NULL DEFAULT 0,
    FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
    FOREIGN KEY (goods_id) REFERENCES goods(goods_id)
);

CHECK

用于强制行数据必须满足的条件

-- 假如在sal列上定义了check约束, 并要求sal列值在1000~2000之间, 则不在此范围的就会报错
CREATE TABLE my_table01(
	id INT PRIMARY KEY,
	`name` VARCHAR(32) NOT NULL DEFAULT '',
	salary INT CHECK (salary > 1000 AND salary < 8000) 
);

自增长

例如希望在添加记录的时候从1开始自动的增长

语法: 字段名整型 PRIMARY KEY auto_increment

自增长使用细节:

一般来说自增长是和primary key配合使用的
自增长也可以单独使用 [但是需要配合一个unique
自增长修饰的字段为整数型
自增长默认从 1 开始, 也可以通过: alter table 表名 auto_increment = xxx; 修改

-- 自增长的演示
CREATE TABLE `my_table03`(
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(32) NOT NULL DEFAULT '',
    `name` VARCHAR(32) NOT NULL DEFAULT ''
);
INSERT INTO `my_table03`
		VALUES(NULL, 'jack@qq.com', 'jack');

索引

目的为了提高查询的速度

**语法: empno_index 索引名称 ON emp (empno) 表示在emp表的empno列创建索引 **
示例: CREATE INDEX empno_index ON employee (department)

索引的类型:
1. 主键索引,主键自动为主键索引(类型Primary Key)
2. 唯一索引(UNIQUE)
3. 普通索引(INDEX)
4. 全文索引(FULLTEXT)

CREATE TABLE `index`(
id INT PRIMARY KEY, -- 主键,同时也是主键索引
)
CREATE TABLE `index`(
id INT unique, -- id是唯一的,同时也是unique索引
)  

-- 索引的演示
CREATE TABLE `index` (
	id INT,
	`name` VARCHAR(32)
);
-- 查询表是否有索引
SHOW INDEX FROM `index`;
SHOW INDEXES FROM `index`;
SHOW KEY FROM `index`;

-- >>> 添加索引 <<<
-- 1. 添加唯一索引 (某列的值不能重复)
CREATE UNIQUE INDEX id_index ON `index` (id);
-- 2. 添加普通索引 (某列的值可以重复)
CREATE INDEX id_index ON `index` (id);
-- 3. 添加主键索引 
ALTER TABLE `index` ADD PRIMARY KEY (id);

-- >>> 删除索引 <<<
DROP INDEX id_index ON `index`
-- 删除主键索引
DROP TABLE `index` DROP PRIMARY KEY

-- 修改索引: 先删除,再添加新的索引即可

创建索引

练习1 : 创建 (primary key主键) 索引

-- 创建一张订单表order(id号,商品名,订购人,数量).要求id号为主键
-- 第一种方式: 直接在字段名后直接加 primary key 完成主键
CREATE TABLE `order`(
	id INT NOT NULL PRIMARY KEY,
	goodsName VARCHAR(64) NOT NULL DEFAULT '',
	customer VARCHAR(64) NOT NULL DEFAULT '',
	number INT NOT NULL DEFAULT 0
);
-- 第二种方式: 在创建完表之后利用语句给指定字段添加主键
ALTER TABLE `index` ADD PRIMARY KEY (id);

练习2 : 创建 (unique唯一) 索引

-- 创建一张特价表menu(id号,菜谱名,厨师,点餐人身份证,价格).要求id号为主键,点餐人身份证为unique
-- 第一种方式: 直接在字段名后面加上unique完成唯一索引
CREATE TABLE `menu`(
	id INT NOT NULL PRIMARY KEY,
	`name` VARCHAR(64) NOT NULL DEFAULT '',
	cooker VARCHAR(64) NOT NULL DEFAULT '',
	customerID VARCHAR(64) NOT NULL DEFAULT '' UNIQUE,
	price INT NOT NULL DEFAULT 0
);
-- 第二种方式: 在创建完表后利用语句给字段添加索引
CREATE UNIQUE INDEX `customerID` ON `menu`;

练习3 : 创建 (普通索引)

-- 创建运动员表sportmen(id号,名字,特长).要求id号为主键,名字为普通索引
CREATE TABLE `sportment`(
	id INT NOT NULL PRIMARY KEY,
	`name` VARCHAR(32) NOT NULL DEFAULT '',
	skill VARCHAR(64) NOT NULL DEFAULT ''
);
CREATE INDEX `name` ON `sportment`; -- 给指定字段添加普通索引

哪些列上适合使用索引?

较频繁的作为查询条件的字段应该创建索引
SELECT * FROM emp WHERE name = ‘xx’
唯一性太差的字段不适合创建索引,即使频繁作为查询条件
ELECT * FROM emp WHERE sex = ‘男’
更新较频繁的字段不适合创建索引
不会出现在where查询子句的字段不该创建索引

事务

用于保存数据的一致性,它由一组相关的dml语句组成,该组的dml语句要么全部成功要么全部失败

-- >>> 事务演示 <<<
-- 1. 创建测试表
CREATE TABLE `table01`(
		id INT,
		`name` VARCHAR(32)
);
-- 2. 开始事务
START TRANSACTION
-- 3. 保存事物点
SAVEPOINT a
-- 4. 执行dml
INSERT INTO `table01`
		VALUES (1,'tom');
-- 5. 回退(回滚)到 a
ROLLBACK TO a
-- 6. 回退全部事物
ROLLBACK

回退事务:
保存点是保存事务的点,用于取消部分事务,
当结束事务时,会自动的删除该事务所定义的所有保存点.
当执行回退事务时,通过指定的保存点可以回退到指定的点.
提交事务:
使用commit语句就可以提交事务.
当执行了commit语句后会确认事务的变化,结束事务和删除保存点、释放锁、数据生效。
当使用commit语句结束事务之后,其它会话[其它连接]将可以看到事务变化后的新数据.
事务细节:
- 如果不开启事务,在默认的情况,dml是自动提交的,不能回滚.
- 如果开启一个事务,你没有创建保存点,你可以执行rollback,默认回退到事务开始的状态
- 在还没提交事务前,可以创建多个保存点,并且可以回退到任意保存点.

隔离级别

多个连接开启各自的事务操作数据库中的数据时,数据库系统要负责隔离操作,以保证各个连接获取数据时的准确性.

脏读: 当一个事务读取另一个事务尚未提交的修改 (update、insert、delete)时,产生脏读.
不可重复读: 同一查询在同一事务中多次进行,由于其它提交事务所做的修改或删除,每次放回不同的结果集,此时发生不可重复读.
幻读: 同一查询在同一事务中多次进行,由于其它提交的事务所作的插入操作,每次返回不同的结果集,此时发生幻读.

READ UNCOMMITTED: 读未提交 -> 会产生脏读、不可重复读、幻读
READ COMMITTED: 读已提交 -> 会产生不可重复、幻读,不会产生脏读
REPEATABLE read: 可重复读 -> 不会产生脏读、不可重复读、幻读

-- >>> 演示mysql事务的隔离级别 <<<

-- 查看当前mysql的隔离级别(控制台中查看)
SELECT @@tx_isolation

-- 查看系统当前的隔离级别
SELECT @@global.tx_ISOLATION;

-- 修改隔离级别(控制台中修改)
SET	SESSION	TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

视图

-- 1. 创建视图
CREATE VIEW `viewname` AS SELECT
-- 2. 修改视图
ALTER VIEW `viewname` AS SELECT
-- 3. 查看视图
SHOW CREATE VIEW `viewname`
-- 4. 删除视图
DROP VIEW `viewname1`, `viewname2`

视图总结:

视图是根据基表(可以是多个基表)来创建的,视图是虚拟的表
视图也有列,数据来自基表
通过视图也可以修改基表的数据
基表的改变,也会影响到视图的数据

-- 练习1: 创建视图emp_view01,只能查询employee的(departmen\id\name\job\salary)
CREATE VIEW `emp_view01`	-- 创建视图
			AS
			SELECT id,department,`name`,job,salary FROM employee;

视图应用案例

-- 针对 employee 和 student 两张表创建一个视图 emp_view02
-- 可以显示雇员编号(id),名字,部门名和学校(如无则为null)
CREATE VIEW	`emp_view02`
		AS
		SELECT emp.id, emp.`name`, emp.department, stu.university
		FROM employee emp, student stu
         WHERE emp.id = stu.id

林凯瑞

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL学习笔记

前面学习到的查询是利用 where 子句对两张表或者多张表,形成笛卡尔积进行筛选,根据关联条件 ,显示所有匹配的记录,匹配不上的,不显示。**语法：IF(expr1,expr2,expr3) -> 如果expr1为真则返回expr2,否则返回expr3 ****语法: empno_index 索引名称 ON emp (empno) 表示在emp表的empno列创建索引 **(2) 把 my_table02 的记录, 通过 distinct 关键字处理后, 把记录复制到 my_temp。
复制链接

扫一扫