零基础学MySQL

最新推荐文章于 2024-07-23 11:45:00 发布

XZY-SUNSHINE

最新推荐文章于 2024-07-23 11:45:00 发布

阅读量700

点赞数 1

分类专栏： MySQL 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/qq_52117201/article/details/127740970

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

MySQL三层结构

在这里插入图片描述

安装Mysql数据库，就是在主机安装数据库管理系统(DBMS)，这个管理程序可以管理多个数据库。DBMS(database manage system)
一个数据库中可以创建多个表，以保存数据(信息)。
普通表的本质是文件。

SQL语句分类

DDL:数据定义语句
DML:数据操作语句[增加,修改，删除]DQL:数据查询语句[select ]
DCL:数据控制语句[管理数据库]

数据库

创建数据库

语法

在这里插入图片描述

CHARACTER SET:指定数据库采用的字符集，如果不指定字符集，默认utf-8
COLLATE:指定数据库字符集的校对规则(常用的utf8_bin(区分带小写) 、utf8_general_ci(不区分大小写).注意默认是 utf8_general_ci)

代码练习

#创建一个名称为hello01的数据库。
CREATE DATABASE hello01
#创建一个使用utf8字符集的hello02数据库
CREATE DATABASE hello02 CHARACTER SET utf8
#创建一个使用utf8字符集，并带校对规则的hello03数据库
CREATE DATABASE hello03 CHARACTER SET utf8 COLLATE utf8_bin

查看、删除数据库

在这里插入图片描述

代码练习

#查看当前数据库服务器中的所有数据库
SHOW DATABASES
#查看前面创建的hello01数据库的定义信息
SHOW CREATE DATABASE hello01
#删除前面创建的hello01数据库
DROP DATABASE hello01
#在创建数据库,表的时候，为了规避关键字，
#可以使用反引号(tab键上面那个)解决

备份、恢复数据库

在这里插入图片描述

表

创建表

在这里插入图片描述

修改表

在这里插入图片描述

代码练习

#用指令在hello02数据库创建表,要根据需保存的数据创建相应的列
#id 		整形
#name		字符串
#password	字符串
#birthday	日期
CREATE TABLE `user`(
	id INT,
	`name` VARCHAR(255),
	`password` VARCHAR(255),
	birthday DATE
)CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB
CREATE TABLE emp(
	`resume` INT,
	job  VARCHAR(32),
	sex CHAR(1),
	`name` CHAR(4)
)CHARACTER SET gbk ENGINE INNODB
#修改表练习
#员工表emp的上增加一个image列，varchar类型(要求在resume后面)。
ALTER TABLE emp
	ADD image VARCHAR(32) NOT NULL DEFAULT ''
	AFTER `resume`;
#修改job列，使其长度为60。
ALTER TABLE emp
	MODIFY job VARCHAR(60);
#删除sex列。
ALTER TABLE emp
	DROP COLUMN sex;
#表名改为employee。修改表的字符集为utf8
RENAME TABLE emp 
	TO employee ;
ALTER TABLE employee 
	CHARACTER SET utf8;
#列名name修改为user_name
ALTER TABLE employee
	CHANGE `name` user_name VARCHAR(32) NOT NULL DEFAULT '';

MySQL常用的数据类型

在这里插入图片描述

数值型使用

在这里插入图片描述

说明

DECIMAL[M,D] [UNSIGNED]：可以支持更加精确的小数位。M是小数位数(精度)的总数，D是小数点(标度)后面的位数。如果D是0，则值没有小数点或分数部分。M最大65。D最大是30.如果D被省略,默认是0。如果M被省略，默认是10。
bit(m) ：m的范围是1-64

使用规范

在能够满足需求的情况下,尽量选择占用空间小的类型。

代码练习

#整型练习
CREATE TABLE t1(
	#id tinyint unsigned#此处是无符号数,范围是0-255
	id TINYINT #此处是有符号数,范围是-128-127
);
#insert into t1 values(128)#报错
#INSERT INTO t1 VALUES(-129)#报错
INSERT INTO t1 VALUES(127);
SELECT * FROM t1;
#bit位练习
CREATE TABLE t2(
	id BIT(8)#bit(m) m在1-64
)
#insert into t2 values(256);#报错
INSERT INTO t2 VALUES(255);
SELECT * FROM t2;#显示的时候二进制的方式显示

字符串的使用

说明

CHAR(size)固定长度字符串最大255字符
VARCHAR(size) 0~65535可变长度字符串最大65532字节【utf8编码最大21844字符1-3个字节用于记录大小】
char(4)和 varchar(4)这个4表示的是字符，而不是字节，不区分字符是汉字还是字母
char(4)是定长(固定的大小)，就是说，即使你插入’aa’，也会占用分配的4个字符的空间.varchar(4)是变长,就是说,如果你插入了’aa’,实际占用空间大小并不是4个字符,而是按照实际占用空间来分配(说明:varchar本身还需要占用1-3个字节来记录存放内容长度)
查询速度:char > varchar

什么时候使用char，什么时候使用varchar
1.如果数据是定长，推荐使用char,比如md5的密码,邮编，手机号，身份证号码等. char(32)
2.如果一个字段的长度是不确定,，我们使用varchar ,比如留言,文章
在存放文本时，也可以使用TEXT如数据类型.可以将TEXT列视为VARCHAR列,注意Text不能有默认值.大小O-2^{16字节如果希望存放更多字符，可以选择MEDIUMTEXT0-2}24 或者LONGTEXT0~2432

日期使用

说明

如果想要timestamp自动更新需要配置以下

NOT NULLDEEAULT CURRENT TIMESTAMPON UPDATECURRENTTIMESTAMP

CRUD语句

INSERT插入语句

基本语法

在这里插入图片描述

细节说明

插入的数据应与字段的数据类型相同。比如把’abc’添加到int类型会错误
数据的长度应在列的规定范围内，例如:不能将一个长度为80的字符串加入到长度为40的列中。
在values中列出的数据位置必须与被加入的列的排列位置相对应。
字符和日期型数据应包含在单引号中。
列可以插入空值[前提是该字段允许为空],insert into table value(null)
insert into tab_name(列名…)values 0.0).0形式添加多条记录
如果是给表中的所有字段添加数据，可以不写前面的字段名称
默认值的使用,当不给某个字段值时，如果有默认值就会添加,否则报错

代码练习

#练习insert语句
#创建一张商品表goods (id int , goods name varchar (10), price double );
#添加2条记录
CREATE TABLE`goods`(
	id INT ,
	goods_name VARCHAR(10),
	price DOUBLE
);
#添加数据
INSERT INTO `goods` (id, goods_name, price)
	VALUES(10,'华为于机',2000);
INSERT INTO `goods`(id, goods_name, price)
	VALUES(20,'苹果于机', 3000);

update语句

基本语法

在这里插入图片描述

使用细节

UPDATE语法可以用新值更新原有表行中的各列。
SET子句指示要修改哪些列和要给予哪些值。
WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行。
如果需要修改多个字段，可以通过set字段1=值1,字段2=值2

代码练习


#要求:在上面创建的employee表中修改表中的纪录
#1.将所有员工薪水修改为5000元。
UPDATE employee
	SET salary=5000;
#2.将姓名为小妖怪的员工薪水修改为3000元。
UPDATE employee
	SET salary=3000;
	WHERE `name`='小妖怪';
#3.将老妖怪的薪水在原有基础上增加1000元。
UPDATE employee
	SET salary=salary+1000;
	WHERE `name`='老妖怪';

delete语句

基本语法

在这里插入图片描述

使用细节

如果不使用where子句，将删除表中所有数据。
Delete语句不能删除某一列的值(可使用update 设为null或者"")
使用delete语句仅删除记录，不删除表本身。如要删除表，使用drop table语句。drop table表名;

代码练习


#删除表中名称为'老妖怪'的记录。
DELETE FROM employee
	WHERE	user_name ='老妖怪';
#删除表中所有记录，老师提醒,一定要小心
DELETE	FROM employee;
SELECT*FROM employee;

select语句

示意图

在这里插入图片描述

单表查询

基本语法

在这里插入图片描述

说明：

Select指定查询哪些列的数据
column指定列名。
*号代表查询所有列。
From指定查询哪张表。
DISTINCT可选,指显示结果时,是否去掉重复数据

使用表达式对查询的列进行计算

在这里插入图片描述

使用as关键字

在这里插入图片描述

where子句中用到的运算符

在这里插入图片描述

使用order by子句对查询结果进行排序

在这里插入图片描述

代码练习

#基本语法练习
#查询表中所有学生的信息。
SELECT *FROM student;
#查询表中所有学生的姓名和对应的英语成绩。
SELECT	`name`,english FROM student;
#过滤表中重复数据distinct .
SELECT DISTINCT english FROM student;
#要查询的记录，每个字段都相同,才会去重
SELECT DISTINCT `name`, english FROM student;
#使用表达式对查询的列进行计算练习
#统计所有学生的总分
SELECT	`name`,(chinese+english+math)FROM student;
#在所有学总分加10分的情况
SELECT `name`, (chinese + english + math + 10) FROM student
#使用别名表示学生分数。
SELECT `name`, (chinese + english + math + 10) AS total_score FROM student;
#where子句使用运算符
#查询姓名为赵云的学生成绩
SELECT *FROM student
	WHERE`name` ='赵云'
#查询英语成绩大于90分的同学
SELECT *FROM student
	WHERE	english >90
#查询总分大于200分的所有同学
SELECT *FROM student
	WHERE(chinese + english + math) > 200
	#查询总分大于200分并且数学成绩小于语文成绩的姓赵的学生.
#赵%表示名字以韩开头的就可以
SELECT *FROM student
	WHERE(chinese + english + math) > 200 AND
		math <chinese AND `name`LIKE '赵%';
		#演示order by使用
#对数学成绩排序后输出【升序】。
SELECT * FROM student
	ORDER BY math;
#对总分按从高到低的顺序输出[降序]--使用别名排序
SELECT `name`, (chinese + english + math) AS total_score FROM student
	ORDER BY total score DESC;
#对姓韩的学生成绩[总分]排序输出(升序) where + order by
SELECT `name`，(chinese + english + math) AS total_score FROM student
	WHERE `name` LIKE '韩%';

查询增强

代码练习

-- 查询加强
-- 使用where子句
--	?如何查找1992.1.1后入职的员工
-- 老师说明:在mysql中，日期类型可以直接比较，需要注意格式
SELECT * FROM emp
	WHERE hiredate >'1992-01-01'
-- 如何使用like操作符(模糊)
     -- %:表示o到多个任意字符  
     -- _:表示单个任意字符
    -- ?如何显示首字符为s的员工姓名和工资
SELECT ename,sal FROM emp
	WHERE ename LIKE 's%';
-- ?如何显示第三个字符为大写o的所有员工的姓名和工资
SELECT ename,sal FROM emp
	WHERE ename LIKE'__O%';
-- 如何显示没有上级的雇员的情况
SELECT * FROM emp
	WHERE mgr IS NULL;
-- 使用order by子句
-- ?如何按照工资的从低到高的顺序[升序]，显示雇员的信息
SELECT * FROM emp
	ORDER BY sal
-- ?按照部门号升序而雇员的工资降序排列，显示雇员信息
SELECT * FROM emp
	ORDER BY deptno ASC,sal DESC;

分页查询

基本语法

select … limit start, rows

代码练习

-- 第1页
SELECT * FROM emp
	ORDER BY empno 
	LIMIT 0,3;
-- 第2页
SELECT * FROM emp
	ORDER BY empno 
	LIMIT 3,3;

分组查询

代码练习

-- (1)显示每种岗位的雇员总数、平均工资。
SELECT COUNT(*) ,AVG (sal),job
	FROM emp
	GROUP BY job;
-- (2).显示雇员总数，以及获得补助的雇员数。
SELECT COUNT(*),COUNT(comm)
	FROM emp;
-- (1)显示每种岗位的雇员总数、平均工资。
SELECT COUNT(*) ,AVG (sal),job
	FROM emp
	GROUP BY job;
-- (2).显示雇员总数，以及获得补助的雇员数。
SELECT COUNT(*),COUNT(comm)
	FROM emp;
-- (3)显示管理者的总人数。
SELECT COUNT(DISTINCT mgr)
	FROM emp ;
-- 显示雇员工资的最大差额。
SELECT MAX(sal)-MIN(sal)
	FROM emp ;

多表查询

条件

多表查询的条件不能小于表的个数-1

自连接

自连接是指在同一张表的连接查询。

代码练习

-- 显示公司员工名字和他的上级的名字
SELECT worker.ename AS '职员名',boss.ename AS '上级名'
	FROM emp worker, emp boss
	WHERE worker.mgr =boss.empno;

子查询

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

单行子查询是指只返回一行数据的子查询语句
多行子查询指返回多行数据的子查询使用关键字in

代码练习

-- 如何显示与SMITH同一部门的所有员工?
SELECT	*
	FROM emp
	WHERE deptno = (SELECT deptno
			FROM emp
			WHERE ename = 'SMITH'
			);
-- 课堂练习:如何查询和部门10的工作相同的雇员的
-- 名字、岗位、工资、部门号，但是不含10号部门自己的雇员
SELECT ename, job, sal, deptno
		FROM emp
		WHERE job IN(SELECT DISTINCT job
					FROM emp
					WHERE deptno = 10
				)AND deptno != 1ol

子查询临时表

代码练习

-- 查询ecshop中各个类别中，价格最高的商品
SELECT goods_id, cat_id, goods_name,shop_price
			FROM (SELECT cat_id , MAX(shop_price)AS max_price
					FROM ecs_goods
					GROUP BY cat_id
				) temp , ecs _goods
			WHERE temp.cat_id = ecs_goods.cat_id 
			AND temp.max_price = ecs_goods.shop_price

all、any关键字

代码练习

-- 显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
SELECT ename, sal, deptno
	FROM emp
	WHERE sal >ALL (SELECT sal
				FROM emp
				WHERE deptno = 30);
-- 如何显示工资比部门30的其中一个员工的工资高的员工的姓名、工资和部门号
SELECT ename, sal,deptno
	FROM emp
	WHERE sal>ANY(SELECT sal
			FROM emp
			WHERE deptno = 30);
-- 请思考如何查询与smith的部门和岗位完全相同的所有雇员(并且不含smith本人)
SELECT *
	FROM emp
	WHERE(deptno , job) = (SELECT deptno,job
					FROM emp
					WHERE ename = 'SMITH')
	AND ename != 'SMITH

多列子查询

多列子查序则是指查询返回多个列数据的子查询语句

合并查询

union all

该操作符用于取得两个结果集的并集。当使用该操作符时，不会取消重复行。

在这里插入图片描述

union

该操作赋与union all相似，但是会自动去掉结果集中重复行

在这里插入图片描述

函数

统计函数

count函数

基本语法

在这里插入图片描述

代码练习

#统计一个班级共有多少学生?
SELECT COUNT(*)FROM student;
#统计数学成绩大于90的学生有多少个?
SELECT COUNT(*)FROM student
	WHERE math>90;
#统计总分大于250的人数有多少?
SELECT	COUNT(*)FROM student
	WHERE(math + english + chinese) >250
#count(*)和count(列)的区别
#解释: count(*)返回满足条件的记录的行数
-- count(列):统计满足条件的某列有多少个，但是会排除为null

合计函数

基本语法

求和-sum

在这里插入图片描述

求平均值-avg

在这里插入图片描述

求最大值，最小值

在这里插入图片描述

代码练习

#统计一个班级数学总成绩?
SELECT SUM(math)FROM student;
#统计一个班级语文、英语、数学各科的总成绩
SELECT SUM(math) AS math total_score , SUM(english) , SUM(chinese) FROM student;
#统计一个班级语文、英语、数学的成绩总和
SELECT SUM(math + english + chinese) FROM student;
#统计一个班级语文成绩平均分
SELECT	SUM(math)/COUNT(*)FROM student;
#求一个班级数学平均分?
SEL,ECT AVG (math)FROM student;
#求一个班级总分平均分
SELECT AVG (math + english + chinese) FROM student;
#求班级最高分和最低分（数值范围在统计中特别有用)
SELECT MAX(math + english + chinese),MIN(math + english + chinese)
	FROM student;

分组统计

基本语法

在这里插入图片描述

代码练习

#显示平均工资低于2000的部门号和它的平均工资
SELECT AVG(sal)AS AVG sal,deptno
	FROM emp GROUP BY deptno
		HAVING AVG sal <2000;

字符串函数

示意图

在这里插入图片描述

代码练习

#以首字母小写的方式显示所有员工emp表的姓名
#方式一
SELECT CONCAT(LCASE(SUBSTRING(ename,1,1)),SUBSTRING(ename,2)) AS new_name
	FROM emp;
#方式二
SELECT CONCAT (LCASE (LEFT (ename,1)),SUBSTRING (ename ,2)) AS new_name
	FROM emp;

数学函数

示意图

在这里插入图片描述

日期函数

示意图

在这里插入图片描述

使用细节

DATE_ADD()中的interval后面可以是year minute second day 等
DATE_SUB()中的interval后面可以是year minute second day等
DATEDIFF(date1,date2)得到的是天数，而且是date1-date2的天数，因此可以取负数
这四个函数的日期类型可以是date, datetime或者timestamp
代码练习

#显示所有新闻信息,发布日期只显示日期，不用显示时间.
SELECT id, content,DATE(send_time)
	FROM mes ;
#请查询在10分钟内发布的新闻思路一定要梳理一下.
SELECT * FROM mes;
	WHERE DATE_ADD(send_time，INTERVAL 10 MINUTE)>=NOW();
SELECT * FROM mes
	WHERE send_time>=DATE_SUB(NOW(),INTERVAL 10 MINUTE);
	#请在mysql 的sql语句中求出2011-11-11 和1990-1-1相差多少天
SELECT DATEDIFF('2011-11-11', '1990-01-01') FROM DUAL;
#请用mysql 的sql语句求出你活了多少天?[练习]1986-11-11
SELECT DATEDIFF(NOW(), '1986-11-11 ')FROM DUAL;
#如果你能活80岁，求出你还能活多少天.1986-11-11出生
SELECT DATEDIFF(DATE_ADD ( '1966-11-11'，INTERVAL 80 YEAR),NOW())
	FROM DUAL;
#在开发中，可以存放一个整数，然后表示时间，通过FROM_UNIXTIME转换
SELECT FROM_UNIXTIME (1618483100,'%Y-%m-%d %H:%i:%s') FROM DUAL;
-- unix timestamp():返回的是1970-1-1到现在的秒数
SELECT UNIX_TIMESTAMP()FROM DUAL;

加密函数

示意图

在这里插入图片描述

流程控制函数

示意图

在这里插入图片描述

代码练习

-- 1.查询emp表，如果comm是null ，则显示0.0
#判断是否为null要使用is null，判断不为空使用is not
SELECT ename,IF(comm IS NULL,0.0,comm)
	FROM emp;
SELECT ename,IFNULL(comm,0.0)
	FROM emp ;
-- 2.如果emp表的 job是CLERK则显示职员，如果是MANAGER则显示经理
#如果是SALESMAN则显示销售人员，其它正常显示
SELECT ename,(SELECT CASE
		WHEN job = 'CLERK' THEN '职员'
		WHEN job = 'MANAGER' THEN '经理'
		WHEN job = 'SALESMAN' THEN '销售人员'
		ELSE jobEND)
	FROM emp;

外连接

左外连接(如果左侧的表完全显示我们就说是左外连接)
右外连接(如果右侧的表完全显示我们就说是右外连接)

代码练习

-- 列出部门名称和这些部门的员工信息(名字和工作)，同时列出那些没有员工的部门名。
-- 使用左外连接实现
SELECT dname,ename,job
	FROM dept LEFT JOIN emp
	ON dept.deptno = emp.deptno
-- 使用右外连接实现
SELECT dname,ename,job
	FROM emp RIGHT JOIN dept
	ON dept.deptno = emp.deptno

MySQL约束

基本介绍

约束用于确保数据库数据满足特定的商业规则。

primary key(主键)

用于唯一的标示表行的数据,当定义主键约束后，该列不能重复

基本使用

在这里插入图片描述

细节说明

primary key不能重复而且不能为null。
一张表最多只能有一个主键,但可以是复合主键
主键的指定方式有两种
- 直接在字段名后指定:字段名primakry key
- 在表定义最后写primary key(列名);
使用desc表名，可以看到primary key的情况

not null(非空)

基本语法

在这里插入图片描述

unique(唯一)

基本语法

在这里插入图片描述

使用细节

都果没有指定not null，则unique字段可以有多个null
一张表可以有多个unique字段

foreign key(外键)

用于定义主表和从表之间的关系:外键约束要定义在从表上，主表则必须具有主键约束或是unique约束.,当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null

基本语法

在这里插入图片描述

细节说明

外键指向的表的字段,要求是primary key 或者是unique
表的类型是innodb,这样的表才支持外键
外键字段的类型要和主键字段的类型一致(长度可以不同)
外键字段的值，必须在主键字段中出现过，或者为null[前提是外键字段允许为null]
一旦建立主外键的关系，数据不能随意删除了.

check约束

用于强制行数据必须满足的条件,假定在sal列上定义了check约束,并要求sal列值在1000_{2000之间如果不再1000}2000之间就会提示出错。用于强制行数据必须满足的条件，假定在列上定义了Check约束，并要求Sal列值在1000_{2000之间如果不再1000}2000之间就会提示出错。

基本语法

在这里插入图片描述

代码练习

-- 现有一个商店的数据库shop_db，记录客户及其购物情况，
CREATE DATABASE shop_db;
USE shop_db;
-- 由下面三个表组成:
-- 	商品goods(商品号goods_id，商品名goods_name，
-- 		单价unitprice，商品类别category，供应商provider);
CREATE TABLE goods(
	goods_id INT PRIMARY KEY,
	goods_name VARCHAR(32) NOT NULL ,
	untiprice DOUBLE CHECK (untiprice>1 AND untiprice<9999.99),
	category VARCHAR(32),
	provider VARCHAR(32)
	
)
-- 客户customer（客户号customer_id,姓名name,住址address,电邮Email,
-- 		性别SeX，身份证card_ld);
CREATE TABLE customer(
	customer_id INT PRIMARY KEY,
	`name` VARCHAR(32) NOT NULL,
	address VARCHAR(64),
	email VARCHAR(32) UNIQUE,
	sex CHAR(1) CHECK(sex IN('男','女')),
	card_id VARCHAR(32)
)
-- 购买purchase（购买订单号order_id，客户号customer_id,商品号goods_ld,
-- 		购头数量nums);
CREATE TABLE purchase(
	order_id INT PRIMARY KEY,
	customer_id INT,
	goods_id INT,
	nums INT,
	FOREIGN KEY(customer_id) REFERENCES customer(customer_id),
	FOREIGN KEY(goods_id) REFERENCES goods(goods_id)
)
-- 建表，在定义中要求声明[进行合理设计]:
-- (1)每个表的主外键;
-- (2)客户的姓名不能为空值;
-- (3)电邮不能够重复;
-- (4)客户的性别[男女]check枚举..
-- (5)单价unitprice在1.0 - 9999.99之间check

自增长

基本语法

在这里插入图片描述

使用示意图

在这里插入图片描述

使用细节

一般来说自增长是和primary key 配合使用的
自增长也可以单独使用[但是需要配合一个unique]
自增长修饰的字段为整数型的(虽然小数也可以但是非常非常少这样使用)
自增长默认从1开始,你也可以通过如下命令修改altertable表名auto increment = XXX;
如果你添加数据时，给自增长字段(列)指定的有值，则以指定的值为准

MySQL索引

索引的代价

磁盘占用
对dml(update delete insert)语句的效率影响

索引类型

主键索引,主键自动的为主索引(类型Primary)唯一索引(UNIQUE)
普通索引 (INDEX)
全文索引(FULLTEXT)[适用于MyISAM]

开发中考虑使用:全文搜索 Solr和 ElasticSearch(ES)

索引的使用

添加唯一索引

CREATE UNIQUE INDEX id ON t25(id);

添加普通索引

CREATE INDEx id INDEX ON t25(id);
ALTER TABLE t25 ADD INDEX id_index (id)

添加主键索引

ALTER TABLE t26 ADD PRIMARY KEY (id);

删除索引

DROP INDEX id INDEX ON t25

删除主键索引

ALTER TABLE t26 DROP PRIMARY KEY

创建索引的规则

较频繁的作为查询条件字段应该创建索引
唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件，比如性别
更新非常频繁的字段不适合创建索引
不会出现在WHERE子句中字段不该创建索引

代码练习

-- 创建一张订单表order (id号，商品名,订购人，数量).
-- 要求id号为主键，请使用2种方式来创建主键.(提示:为练习方便，可以是order1,order2)
CREATE TABLE order1(
	id INT PRIMARY KEY
);
CREATE TABLE order2(
	id INT
)
ALTER TABLE order2 ADD PRIMARY KEY(id);
-- 创建一张特价菜谱表menu (id号，菜谱名,厨师，点餐人身份证，价格).
-- 要求id号为主键，点餐人身份证是unique请使用两种方式来创建unique.
CREATE TABLE menu1(
	id INT UNIQUE
)
CREATE TABLE menu2(
	id INT
)
CREATE UNIQUE INDEX id ON menu2(id) 
-- 创建一张运动员表sportman (id号，名字，特长).
-- 要求id号为主键，名字为普通索引，请使用三种方式来创建索引
CREATE TABLE sportman1(
	id  INT PRIMARY KEY,
	`name` VARCHAR(32),
	INDEX name_index(`name`)
);
CREATE TABLE sportman2(
	id  INT PRIMARY KEY,
	`name` VARCHAR(32)
);
CREATE TABLE sportman3(
	id  INT PRIMARY KEY,
	`name` VARCHAR(32)
);
CREATE INDEX `name` ON sportman2(`name`);
ALTER TABLE sportman3 ADD INDEX name_index(`name`);

MySQL事务

什么是事务

事务用于保证数据的一致性,它由一组相关的dml语句组成,该组的dml语句要么全部成功，要么全部失败。如:转账就要用事务来处理，用以保证数据的一致性。

事务和锁

当执行事务操作时(dml语句),mysql会在表上加锁,防止其它用户改表的数据.这对用户来讲是非常重要的

基本操作

start transaction --开始一个事务
savepoint保存点名–设置保存点
rollback to保存点名–回退事务
rollback --回退全部事务
commit–提交事务,所有的操作生效,不能回退

说明

回退事务

在介绍回退事务前，先介绍一下保存点(savepoint).保存点是事务中的点.用于取消部分事务，当结束事务时，会自动的删除该事务所定义的所有保存点.当执行回退事务时，通过指定保存点可以回退到指定的点,

提交事务

使用commit语句可以提交事务.当执行了commit语句子后,会确认事务的变化、结束事务、删除保存点、释放锁，数据生效。当使用commit语句结束事务子后，其它会话将可以查看到事务变化后的新数据

事务的细节

如果不开始事务，默认情况下，dml操作是自动提交的，不能回滚
如果开始一个事务，你没有创建保存点.你可以执行rollback，默认就是回退到你事务开始的状态.
你也可以在这个事务中(还没有提交时),创建多个保存点.比如: savepointaaa;执行dml , savepoint bbb;
你可以在事务没有提交前，选择回退到哪个保存点.
mysql的事务机制需要innodb的存储引擎还可以使用,myisam不好使.
开始一个事务start transaction, set autocommit=off;

事务隔离级别

基本介绍

多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性。
查看当前隔离级别：SELECT @@transaction_isolation
设置隔离级别：SET SESSION TRANSACTION ISOLATION LEVEL+隔离级别
如果不考虑隔离性，可能会引发如下问题:
- 脏读：当一个事务读取另一个事务尚未提交的修改时，产生脏读
- 不可重复读：同一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，每次返回不同的结果集。
- 幻读：同一查询在同一事务中多次进行,由于其他提交事务所做的插入操作,每次返回不同的结果集。

示意图

在这里插入图片描述

ACID特性

原子性(Atomicity)

原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

一致性(Consistency)

事务必须使数据库从一个一致性状态变换到另外一个一致性状态

隔离性(lsolation)

事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

持久性(Durability)

持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的,接下来即使数据库发生故障也不应该对其有任何影响

存储引擎

基本介绍

MySQL的表类型由存储引擎(Storage Engines)决定，主要包括MylSAM、innoDB、 Memory等。
MySQL 数据表主要支持六种类型,分别是:CSV、Memory、ARCHIVE、MRG_MYISAM、MYISAM、 InnoBDB。
这六种又分为两类,一类是”事务安全型”(transaction-safe),比如:InnoDB;其余都属于第二类,称为”非事务安全型”

示意图

在这里插入图片描述

细节说明

MyISAM不支持事务、也不支持外键，但其访问速度快，对事务完整性没有要求
InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MylSAM存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。
MEMGRY存储引擎使用存在内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH索引。但是一旦服务关闭，表中的数据就会丢失掉,表的结构还在。