Mysql

1.一个问题

2.解决之道

1.解决之道-文件、数据库

3.数据库三层结构

数据库-表本质是文件

4. 数据在数据库中存储方式

5.SQL 语句分类

6.创建数据库

# 演示数据库的操作
#创建一个名称为 hsp_db01 的数据库。[图形化和指令 演示]
CREATE DATABASE hsp_db01

#使用指令创建数据库 CREATE DATABASE hsp_db01;
#删除数据库指令
DROP DATABASE hsp_db01
#创建一个使用 utf8 字符集的 hsp_db02 数据库
CREATE DATABASE hsp_db02 CHARACTER SET utf8
#创建一个使用 utf8 字符集，并带校对规则的 hsp_db03 数据库
CREATE DATABASE hsp_db03 CHARACTER SET utf8 COLLATE utf8_bin
#校对规则 utf8_bin  区分大小 默认 utf8_general_ci  不区分大小写


#下面是一条查询的 sql , select 查询 *  表示所有字段 FROM  从哪个表
#WHERE 从哪个字段 NAME = 'tom' 查询名字是 tom
SELECT  * FROM T1  WHERE NAME = 'tom'

utf8-bin:区分大小写

utf8_general_ci:默认校对规则，不区分大小写

7.查看、删除数据库

#演示删除和查询数据库
#查看当前数据库服务器中的所有数据库SHOW DATABASES
#查看前面创建的 hsp_db01 数据库的定义信息
SHOW CREATE DATABASE `hsp_db01`
#说明 在创建数据库,表的时候，为了规避关键字，可以使用反引号解决

#删除前面创建的 hsp_db01 数据库
DROP DATABASE hsp_db01

8.备份恢复数据库

#练习 : database03.sql  备份 hsp_db01 和 hsp_db03  库中的数据，并恢复


#备份, 要在 Dos 下执行 mysqldump 指令其实在 mysql 安装目录\bin
#这个备份的文件，就是对应的 sql 语句
mysqldump -u root -p -B hsp_db02 hsp_db03 > d:\\bak.sql


DROP DATABASE ecshop;


#恢复数据库(注意：进入 Mysql 命令行再执行) source d:\\bak.sql
#第二个恢复方法， 直接将 bak.sql 的内容放到查询编辑器中，执行

9.创建表

#指令创建表
#注意：hsp_db02 创建表时，要根据需保存的数据创建相应的列，并根据数据的类型定义相应的列类型。例：user表 (快速入门案例 create_tab01.sql )
#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;

10.Mysql 常用数据类型(列类型)

1.数值型(整数)的基本使用

#演示整型的是一个
#使用 tinyint 来演示范围 有符号 -128 ~ 127	如果没有符号 0-255
#说明： 表的字符集，校验规则, 存储引擎，使用默认
#1. 如果没有指定 unsinged ,  则 TINYINT 就是有符号
#2. 如果指定 unsinged , 则 TINYINT 就是无符号 0-255 
CREATE TABLE t3 (id TINYINT);
 
CREATE TABLE t4 (id TINYINT UNSIGNED);

INSERT INTO t3 VALUES(127); #这是非常简单的添加语句SELECT * FROM t3

INSERT INTO t4 VALUES(255);

SELECT * FROM t4;

2.整型如何定义一个无符号的整数

3.数值型(bit)的使用

#演示 bit 类型使用
#说明
#1. bit(m) m 在 1-64
#2. 添加数据 范围 按照你给的位数来确定，比如 m = 8  表示一个字节 0~255
#3. 显示按照 bit
#4. 查询时，仍然可以按照数来查询
CREATE TABLE t05 (num BIT(8));

#超出一个字节范围，报错
INSERT INTO t05 VALUES(256); 

SELECT * FROM t05;

#查询时，仍然可以按照数来查询
SELECT * FROM t05 WHERE num = 1;

4.数值型(小数)的基本使用

#演示 decimal 类型、float、double 使用


#创建表
CREATE TABLE t06 ( num1 FLOAT, num2 DOUBLE,
num3 DECIMAL(30,20));
#添加数据
INSERT INTO t06 VALUES(88.12345678912345, 88.12345678912345,88.12345678912345); 
SELECT * FROM t06;

#decimal 可以存放很大的数
CREATE TABLE t07 ( num DECIMAL(65));
INSERT INTO t07 VALUES(8999999933338388388383838838383009338388383838383838383);
SELECT * FROM t07; 

CREATE TABLE t08(num BIGINT UNSIGNED)
#超出范围0~2^16-1无法插入
INSERT INTO t08 VALUES(8999999933338388388383838838383009338388383838383838383); 
SELECT * FROM t08;

5.字符串的基本使用

#演示字符串类型使用 char varchar
#注释的快捷键 shift+ctrl+c ,  注销注释 shift+ctrl+r
-- CHAR(size)
-- 固定长度字符串 最大 255  字符
-- VARCHAR(size)	0~65535 字节
-- 可变长度字符串 最大 65532 字节	【utf8 编码最大 21844 字符 1-3 个字节用于记录大小】
-- 如果表的编码是 utf8 varchar(size) size = (65535-3) / 3 = 21844
-- 如果表的编码是 gbk varchar(size) size = (65535-3) / 2 = 32766

CREATE TABLE t09 (
`name` CHAR(255));

CREATE TABLE t10 (
`name` VARCHAR(32766)) CHARSET gbk;


DROP TABLE t10;

6.字符串使用细节

varchar 大小也是0-2^16，但是varchar本身还需要占用1-3个字节来记录存放内容长度

如果是存放长度过大的文本信息建议使用text

#演示字符串类型的使用细节
#char(4) 和 varchar(4) 这个 4 表示的是字符，而不是字节,  不区分字符是汉字还是字母
CREATE TABLE t11(
`name` CHAR(4));
INSERT INTO t11 VALUES('琪琪真好');


SELECT * FROM t11;


CREATE TABLE t12(
`name` VARCHAR(4));
INSERT INTO t12 VALUES('琪琪你好'); 
#超出最大长度限制Data too long for column 'name' at row 1
INSERT INTO t12 VALUES('ab 北京'); 
SELECT * FROM t12;

#如果 varchar 不够用，可以考试使用 mediumtext  或者 longtext,
#如果想简单点，可以使用直接使用 text
CREATE TABLE t13( content TEXT, content2 MEDIUMTEXT , content3 LONGTEXT); 
INSERT INTO t13 VALUES('琪琪你真好', '琪琪你真好 520', '琪琪你真好 5200~~');
SELECT * FROM t13;

7.日期类型的基本使用

#演示时间相关的类型
#创建一张表, date , datetime , timestamp 
CREATE TABLE t14 (
birthday DATE , -- 生日
job_time DATETIME, -- 记录年月日 时分秒
login_time TIMESTAMP
NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP); -- 登录时间, 如果希望 login_time 列自动更新, 需要配置


SELECT * FROM t14;
INSERT INTO t14(birthday, job_time) VALUES('2022-11-11','2022-11-11 10:10:10');
-- 如果我们更新 t14 表的某条记录，login_time 列会自动的以当前时间进行更新
UPDATE t14 SET job_time = '2022-11-11 10:10:11'

11.修改表

应用实例

#修改表的操作练习
--	员工表 emp 的上增加一个 image 列，varchar 类型(要求在 resume 后面)。
ALTER TABLE emp
ADD image VARCHAR(32) NOT NULL DEFAULT '' AFTER RESUME
DESC employee -- 显示表结构，可以查看表的所有列

--	修改 job 列，使其长度为 60。
ALTER TABLE emp
MODIFY job VARCHAR(60) NOT NULL DEFAULT ''

--	删除 sex 列。
ALTER TABLE emp
DROP sex

--	表名改为 employee。
RENAME TABLE emp TO employee

--	修改表的字符集为 utf8
ALTER TABLE employee CHARACTER SET utf8

--	列名 name 修改为 user_name 
ALTER TABLE employee
CHANGE `name` `user_name` VARCHAR(64) NOT NULL DEFAULT ''

--	显示表结构，可以查看表的所有列
DESC employee

12.数据库C[create]R[read]U[update]D[delete]语句

13.insert语句

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

CREATE TABLE goods(
id INT,
goods_name VARCHAR(10),
price DOUBLE NOT NULL DEFAULT 100);

INSERT INTO goods (id,goods_name,price) VALUES(1,'华为手机',2000);
INSERT INTO goods (id,goods_name,price) VALUES(2,'苹果手机',3000);

SELECT * FROM goods;

2.使用细节

#说明 insert  语句的细节
-- 1.插入的数据应与字段的数据类型相同。
--	比如 把 'abc' 添加到 int 类型会错误
-- 成功
INSERT INTO `goods` (id, goods_name, price)
VALUES('21', '小米手机', 2000);

-- 失败 会尝试去转换成功对应的类型，如果不能转换则抛出异常Incorrect integer value: 'llp' for column 'id' at row 1
INSERT INTO `goods` (id, goods_name, price)
VALUES('llp', '小米手机', 2000);

-- 2.  数据的长度应在列的规定范围内，例如：不能将一个长度为 80 的字符串加入到长度为 40 的列中。
INSERT INTO `goods` (id, goods_name, price)
VALUES(40, 'vovo 手机 vovo 手机 vovo 手机 vovo 手机 vovo 手机', 3000);

-- 3.  在 values 中列出的数据位置必须与被加入的列的排列位置相对应。
INSERT INTO `goods` (id, goods_name, price)	-- 不对
VALUES('vovo 手机',40, 2000);

-- 4. 字符和日期型数据应包含在单引号中。
INSERT INTO `goods` (id, goods_name, price)
VALUES(40, vovo 手机, 3000); -- 错误的 vovo 手机 应该 'vovo 手机'

-- 5. 列可以插入空值[前提是该字段允许为空]，
INSERT INTO TABLE VALUE(NULL) 
INSERT INTO `goods` (id, goods_name, price)
VALUES(40, 'vovo 手机', NULL);

-- 6. insert into tab_name (列名..)	values (),(),()	形式添加多条记录
INSERT INTO `goods` (id, goods_name, price) VALUES(50, '三星手机', 2300),(60, '海尔手机', 1800);

-- 7.  如果是给表中的所有字段添加数据，可以不写前面的字段名称
INSERT INTO `goods` VALUES(70, 'IBM 手机', 5000);

-- 8.  默认值的使用，当不给某个字段值时，如果有默认值就会添加默认值，否则报错
-- 如果某个列 没有指定 not null ,那么当添加数据时，没有给定值，则会默认给 null
-- 如果我们希望指定某个列的默认值，可以在创建表时指定
INSERT INTO `goods` (id, goods_name) VALUES(80, '格力手机');

SELECT * FROM goods;

14.update 语句

1.使用 update 语句修改表中数据

2.基本使用 update.sql

-- 演示 update 语句
-- 要求:  在上面创建的 employee 表中修改表中的纪录
-- 1. 将所有员工薪水修改为 5000 元。[如果没有带 where 条件，会修改所有的记录，因此要小心] 
UPDATE employee SET salary = 5000

-- 2. 将姓名为 小妖怪 的员工薪水修改为 3000 元。
UPDATE employee SET salary = 3000
WHERE user_name = '小妖怪'

-- 3. 将 老妖怪 的薪水在原有基础上增加 1000 元
INSERT INTO employee
VALUES(200, '老妖怪', '1990-11-11', '2000-11-11 10:10:10', '捶背的', 5000, '给大王捶背', 'd:\\a.jpg');

UPDATE employee
SET salary = salary + 1000 WHERE user_name = '老妖怪'

-- 可以修改多个列的值
UPDATE employee
SET salary = salary + 1000 , job = '出主意的' WHERE user_name = '老妖怪'

SELECT * FROM employee;

3.使用细节

15.delete语句

1.使用 delete 语句删除表中数据

-- delete 语句演示

--	删除表中名称为’老妖怪’的记录。
DELETE FROM employee WHERE user_name = '老妖怪';

--	删除表中所有记录
DELETE FROM employee;

-- Delete 语句不能删除某一列的值（可使用 update 设为 null 或者 ''） 
UPDATE employee SET job = '' WHERE user_name = '老妖怪';

SELECT * FROM employee

-- 要删除这个表
DROP TABLE employee;

2.使用细节

16.select 语句

1.基本语法

2.注意事项

3.练习

-- select 语句【重点 难点】
CREATE TABLE student(
id INT NOT NULL DEFAULT 1,
NAME VARCHAR(20) NOT NULL DEFAULT '',
 chinese FLOAT NOT NULL DEFAULT 0.0, 
english FLOAT NOT NULL DEFAULT 0.0,
math FLOAT NOT NULL DEFAULT 0.0
);

INSERT INTO student(id,NAME,chinese,english,math) VALUES(1,'韩顺平',89,78,90); 
INSERT INTO student(id,NAME,chinese,english,math) VALUES(2,'张飞',67,98,56); 
INSERT INTO student(id,NAME,chinese,english,math) VALUES(3,'宋江',87,78,77); 
INSERT INTO student(id,NAME,chinese,english,math) VALUES(4,'关羽',88,98,90); 
INSERT INTO student(id,NAME,chinese,english,math) VALUES(5,'赵云',82,84,67); 
INSERT INTO student(id,NAME,chinese,english,math) VALUES(6,'欧阳锋',55,85,45); 
INSERT INTO student(id,NAME,chinese,english,math) VALUES(7,'黄蓉',75,65,30); 
INSERT INTO student(id,NAME,chinese,english,math) VALUES(8,'韩信',45,65,99);

SELECT * FROM student;

-- 查询表中所有学生的信息。
SELECT * FROM student;

-- 查询表中所有学生的姓名和对应的英语成绩。
SELECT `name`,english FROM student;

-- 过滤表中重复数据 distinct  。
SELECT DISTINCT english FROM student;

-- 要查询的记录，每个字段都相同，才会去重
SELECT DISTINCT `name`, english FROM student;

4.使用表达式对查询的列进行运算

5.在select 语句中可使用 as 语句

-- select 语句的使用

-- 统计每个学生的总分
SELECT `name`, (chinese+english+math) FROM student;
-- 在所有学生总分加 10 分的情况
SELECT `name`, (chinese + english + math + 10) FROM student;
-- 使用别名表示学生分数。
SELECT `name` AS '名字', (chinese + english + math + 10) AS total_score FROM student;

6.在where 子句中经常使用的运算符

7.使用order by 子句排序查询结果

-- 演示 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 '韩%'
ORDER BY total_score;

17.子查询

1.在多行子查询中使用all、any 操作符

-- all  和 any 的使用

-- 请思考:显示工资比部门 30 的所有员工的工资高的员工的姓名、工资和部门号
SELECT ename, sal, deptno FROM emp WHERE sal > ALL(SELECT sal FROM emp WHERE deptno = 30)
-- 可以这样写
SELECT ename, sal, deptno FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30)


-- 请思考:如何显示工资比部门 30 的其中一个员工的工资高的员工的姓名、工资和部门号
SELECT ename, sal, deptno FROM emp WHERE sal > ANY( SELECT sal FROM emp WHERE deptno = 30)


SELECT ename, sal, deptno FROM emp WHERE sal > (SELECT MIN(sal) FROM emp WHERE deptno = 30)

-- 查询 ecshop 中各个类别中，价格最高的商品
-- 查询 商品表
-- 先得到 各个类别中，价格最高的商品 max + group by cat_id,  当做临时表
-- 把子查询当做一张临时表可以解决很多很多复杂的查询

SELECT cat_id , MAX(shop_price) FROM ecs_goods GROUP BY cat_id


-- 这个最后答案
SELECT goods_id, ecs_goods.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

18.表复制

1.自我复制数据(蠕虫复制)

-- 表的复制
-- 为了对某个 sql 语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据


CREATE TABLE my_tab01 ( id INT,
`name` VARCHAR(32), sal DOUBLE,
job VARCHAR(32),
deptno INT); 

INSERT INTO my_tab01 (id,`name`,sal,job,deptno) VALUES(1,'孙悟空',12000,'java',1); 

DESC my_tab01
SELECT * FROM my_tab01;


-- 演示如何自我复制
-- 1. 先把 emp 表的记录复制到 my_tab01 
INSERT INTO my_tab01
(id, `name`, sal, job,deptno)
SELECT empno, ename, sal, job, deptno FROM emp;

-- 2. 自我复制
INSERT INTO my_tab01 SELECT * FROM my_tab01;
SELECT COUNT(*) FROM my_tab01;

-- 如何删除掉一张表重复记录
-- 1.  先创建一张表 my_tab02,
-- 2.  让 my_tab02 有重复的记录
CREATE TABLE my_tab02 LIKE emp; -- 这个语句 把 emp 表的结构(列)，复制到 my_tab02


DESC my_tab02;


INSERT INTO my_tab02 SELECT * FROM emp;
SELECT * FROM my_tab02;
-- 3.  考虑去重 my_tab02 的记录
/*
思路
(1)先创建一张临时表 my_tmp ,  该表的结构和 my_tab02 一样
(2)把 my_tmp  的记录 通过 distinct  关键字 处理后 把记录复制到 my_tmp
(3)清除掉 my_tab02 记录
(4)把 my_tmp  表的记录复制到 my_tab02
(5)drop 掉 临时表 my_tmp
*/
-- (1) 先创建一张临时表 my_tmp ,  该表的结构和 my_tab02 一样
CREATE TABLE my_tmp LIKE my_tab02

-- (2) 把 my_tmp 的记录 通过 distinct 关键字 处理后 把记录复制到 my_tmp 
INSERT INTO my_tmp SELECT DISTINCT * FROM my_tab02;

-- (3) 清除掉 my_tab02 记录
DELETE FROM my_tab02;

-- (4) 把 my_tmp 表的记录复制到 my_tab02 
INSERT INTO my_tab02 SELECT * FROM my_tmp;

-- (5) drop 掉 临时表 my_tmp drop table my_tmp;
SELECT * FROM my_tab02;

19.mysql约束

1.foreign key外键

-- 外键演示


-- 创 建 主 表 my_class
 CREATE TABLE my_class (
id INT PRIMARY KEY , -- 班级编号
`name` VARCHAR(32) NOT NULL DEFAULT '');

SELECT * FROM my_class;

-- 创 建 从 表 my_stu 
CREATE TABLE my_stu (
id INT PRIMARY KEY , -- 学生编号
`name` VARCHAR(32) NOT NULL DEFAULT '',
class_id INT , -- 学生所在班级的编号
-- 下面指定外键关系
FOREIGN KEY (class_id) REFERENCES my_class(id));

SELECT * FROM my_stu;


-- 测试数据
INSERT INTO my_class VALUES(100, 'java'), (200, 'web');
INSERT INTO my_class VALUES(300, 'php');

SELECT * FROM my_class; 

INSERT INTO my_stu VALUES(1, 'tom', 100);
INSERT INTO my_stu VALUES(2, 'jack', 200);
INSERT INTO my_stu VALUES(3, 'hsp', 300);

SELECT * FROM my_stu;

INSERT INTO my_stu VALUES(4, 'mary', 400); -- 这里会失败...因为 400 班级不存在


INSERT INTO my_stu VALUES(5, 'king', NULL); -- 可以, 外键 没有写 not null SELECT * FROM my_class;

-- 一旦建立主外键的关系，数据不能随意删除了
DELETE FROM my_class WHERE id = 100;

2.check

-- 演示 check 的使用
-- mysql5.7 目前还不支持 check ,只做语法校验，但不会生效
-- 了解
-- 学习 oracle, sql server, 这两个数据库是真的生效.

-- 测试
CREATE TABLE t23 (
id INT PRIMARY KEY,
`name` VARCHAR(32) ,
sex VARCHAR(6) CHECK (sex IN('man','woman')), sal DOUBLE CHECK ( sal > 1000 AND sal < 2000)
);


-- 添加数据
INSERT INTO  t23 VALUES(1, 'jack', 'mid', 1);
SELECT * FROM t23;

20.mysql索引

1.索引快速入门

创建海量数据的sql脚本

CREATE TABLE dept( /*部门表*/
deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0,
dname VARCHAR(20)  NOT NULL  DEFAULT "",
loc VARCHAR(13) NOT NULL DEFAULT ""
) ;

#创建表EMP雇员
CREATE TABLE emp
(empno  MEDIUMINT UNSIGNED  NOT NULL  DEFAULT 0, /*编号*/
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/
hiredate DATE NOT NULL,/*入职时间*/
sal DECIMAL(7,2)  NOT NULL,/*薪水*/
comm DECIMAL(7,2) NOT NULL,/*红利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
) ;

#工资级别表
CREATE TABLE salgrade
(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
losal DECIMAL(17,2)  NOT NULL,
hisal DECIMAL(17,2)  NOT NULL
);

#测试数据
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);

delimiter $$

#创建一个函数，名字 rand_string，可以随机返回我指定的个数字符串
create function rand_string(n INT)
returns varchar(255) #该函数会返回一个字符串
begin
#定义了一个变量 chars_str， 类型  varchar(100)
#默认给 chars_str 初始值   'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'
 declare chars_str varchar(100) default
   'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'; 
 declare return_str varchar(255) default '';
 declare i int default 0; 
 while i < n do
    # concat 函数 : 连接函数mysql函数
   set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
   set i = i + 1;
   end while;
  return return_str;
  end $$


 #这里我们又自定了一个函数,返回一个随机的部门号
create function rand_num( )
returns int(5)
begin
declare i int default 0;
set i = floor(10+rand()*500);
return i;
end $$

 #创建一个存储过程， 可以添加雇员
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0;
#set autocommit =0 把autocommit设置成0
 #autocommit = 0 含义: 不要自动提交
 set autocommit = 0; #默认不提交sql语句
 repeat
 set i = i + 1;
 #通过前面写的函数随机产生字符串和部门编号，然后加入到emp表
 insert into emp values ((start+i) ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
  until i = max_num
 end repeat;
 #commit整体提交所有sql语句，提高效率
   commit;
 end $$

 #添加8000000数据
call insert_emp(100001,8000000)$$

#命令结束符，再重新设置为;
delimiter ;

创建索引

SELECT COUNT(*) FROM emp;


-- 在没有创建索引时，我们的查询一条记录
SELECT * FROM emp WHERE empno = 1234567
-- 使用索引来优化一下， 体验索引的牛
-- 在没有创建索引前 , emp.ibd  文件大小 是 524m
-- 创建索引后 emp.ibd  文件大小 是 655m [索引本身也会占用空间.]
-- 创建 ename 列索引,emp.ibd  文件大小 是 827m

-- empno_index 索引名称
-- ON emp (empno) :  表示在 emp 表的 empno 列创建索引
CREATE INDEX empno_index ON emp (empno)

-- 创建索引后， 查询的速度如何
SELECT * FROM emp WHERE empno = 1234578 -- 0.003s  原来是 4.5s

-- 创建索引后，只对创建了索引的列有效
SELECT * FROM emp WHERE ename = 'PjDlwy' -- 没有在 ename 创建索引时，时间 4.7s

CREATE INDEX ename_index ON emp (ename) -- 在 ename 上创建索引

2.索引的原理

3.索引的类型

4.索引使用

-- 演示 mysql 的索引的使用
-- 创建索引
CREATE TABLE t25 (
id INT ,
`name` VARCHAR(32));


-- 查询表是否有索引
SHOW INDEXES FROM t25;
-- 添加索引
-- 添加唯一索引
CREATE UNIQUE INDEX id_index ON t25 (id);

-- 添加普通索引方式 1
CREATE INDEX id_index ON t25 (id);
-- 如何选择
-- 1.  如果某列的值，是不会重复的，则优先考虑使用 unique 索引,  否则使用普通索引
-- 添加普通索引方式 2
ALTER TABLE t25 ADD INDEX id_index (id)


-- 添加主键索引
CREATE TABLE t26 (
id INT ,
`name` VARCHAR(32));
ALTER TABLE t26 ADD PRIMARY KEY (id)


SHOW INDEX FROM t25


-- 删除索引
DROP INDEX id_index ON t25
-- 删除主键索引
ALTER TABLE t26 DROP PRIMARY KEY



-- 修改索引 ， 先删除，在添加新的索引


-- 查询索引
-- 1. 方式
SHOW INDEX FROM t25
-- 2. 方式
SHOW INDEXES FROM t25
-- 3. 方式
SHOW KEYS FROM t25
-- 4 方式
DESC t25

5.哪些列上适合使用索引

21.mysql 事务

1.什么是事务

2.事务和锁

-- 事务的一个重要的概念和具体操作
-- 看一个图[看示意图]
-- 演示
-- 1. 创建一张测试表
CREATE TABLE t27 ( id INT,
`name` VARCHAR(32));
-- 2. 开始事务
START TRANSACTION
-- 3. 设置保存点
SAVEPOINT a
-- 执行 dml  操作
INSERT INTO t27 VALUES(100, 'tom'); SELECT * FROM t27;

SAVEPOINT b
-- 执行 dml 操作
INSERT INTO t27 VALUES(200, 'jack');

-- 回 退 到 b 
ROLLBACK TO b
-- 继 续 回 退 a 
ROLLBACK TO a
-- 如果这样,  表示直接回退到事务开始的状态. ROLLBACK
COMMIT

SELECT * FROM t27;

3.回退事务

4.提交事务

5.事务细节讨论

-- 讨论 事务细节
-- 1.  如果不开始事务，默认情况下，dml 操作是自动提交的，不能回滚
INSERT INTO t27 VALUES(300, 'milan'); -- 自动提交 commit


SELECT * FROM t27


-- 2.  如果开始一个事务，你没有创建保存点.  你可以执行 rollback，
-- 默认就是回退到你事务开始的状态
START TRANSACTION
INSERT INTO t27 VALUES(400, 'king'); INSERT INTO t27 VALUES(500, 'scott');
ROLLBACK -- 表示直接回退到事务开始的的状态
COMMIT;


-- 3. 你也可以在这个事务中(还没有提交时),  创建多个保存点.比如: savepoint	aaa;
-- 执行 dml , savepoint	bbb


-- 4. 你可以在事务没有提交前，选择回退到哪个保存点
-- 5. InnoDB 存储引擎支持事务 , MyISAM  不支持
-- 6. 开始一个事务 start	transaction,	set autocommit=off;

22.mysql 事务隔离级别

1.事务隔离级别介绍

2.查看事务隔离级别

3.事务隔离级别

4.mysql 的事务隔离级--案例

5.设置事务隔离级别

-- 演示 mysql 的事务隔离级别
-- 1.  开了两个 mysql 的控制台
-- 2.  查看当前 mysql 的隔离级别
SELECT @@tx_isolation;

-- mysql> SELECT @@tx_isolation;
-- +-----------------+
-- | @@tx_isolation	|
-- +-----------------+
-- | REPEATABLE-READ |
-- +-----------------+

-- 3.把其中一个控制台的隔离级别设置 Read uncommitted
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- 4. 创建表
CREATE TABLE `account`( id INT,
`name` VARCHAR(32), money INT);

-- 查看当前会话隔离级别
SELECT @@tx_isolation
-- 查看系统当前隔离级别
SELECT @@global.tx_isolation
-- 设置当前会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- 设置系统当前隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL [你设置的级别]

23.mysql 事务 ACID

1. 事务的acid 特性

24.mysql 表类型和存储引擎

1.基本介绍

2.主要的存储引擎/表类型特点

3.细节说明

比较常用的： MyISAM、InnoDB、MEMORY

4.三种存储引擎表使用案例

-- 表类型和存储引擎


-- 查看所有的存储引擎
SHOW ENGINES
-- innodb 存储引擎，是前面使用过.
-- 1. 支持事务 2.  支持外键 3.  支持行级锁


-- myisam 存储引擎
CREATE TABLE t28 (
id INT,
`name` VARCHAR(32)) ENGINE MYISAM
-- 1. 添加速度快 2.  不支持外键和事务 3.  支持表级锁

START TRANSACTION; SAVEPOINT t1
INSERT INTO t28 VALUES(1, 'jack'); SELECT * FROM t28;
ROLLBACK TO t1


-- memory  存储引擎
-- 1.  数据存储在内存中[关闭了 Mysql 服务，数据丢失,  但是表结构还在]
-- 2.  执行速度很快(没有 IO 读写) 3.  默认支持索引(hash 表)


CREATE TABLE t29 (
id INT,
`name` VARCHAR(32)) ENGINE MEMORY DESC t29
INSERT INTO t29
VALUES(1,'tom'), (2,'jack'), (3, 'hsp'); SELECT * FROM t29

-- 指令修改存储引擎
ALTER TABLE `t29` ENGINE = INNODB

5.如何选择表的存储引擎

6.修改存储引擎

25.视图(view)

1.看一个需求

2.基本概念

3.视图的基本使用

4.完成前面提出的需求

-- 视图的使用
-- 创建一个视图 emp_view01，只能查询 emp 表的(empno、ename, job 和 deptno ) 信息

-- 创建视图
CREATE VIEW emp_view01 AS SELECT empno, ename, job, deptno FROM emp;


-- 查看视图
DESC emp_view01


SELECT * FROM emp_view01;
SELECT empno, job FROM emp_view01;


-- 查看创建视图的指令
SHOW CREATE VIEW emp_view01
-- 删除视图
DROP VIEW emp_view01;



-- 视图的细节

-- 1.  创建视图后，到数据库去看，对应视图只有一个视图结构文件(形式:  视图名.frm)
-- 2.  视图的数据变化会影响到基表，基表的数据变化也会影响到视图[insert update delete ]


-- 修改视图 会影响到基表

UPDATE  emp_view01 SET job = 'MANAGER' WHERE empno = 7369


SELECT * FROM emp; -- 查询基表

SELECT * FROM emp_view01


-- 修改基本表， 会影响到视图
UPDATE emp SET job = 'SALESMAN' WHERE empno = 7369


-- 3.  视图中可以再使用视图 ,  比如从 emp_view01 视图中，选出 empno,和 ename 做出新视图
DESC emp_view01

CREATE VIEW emp_view02 AS SELECT empno, ename FROM emp_view01

SELECT * FROM emp_view02

5视图细节讨论

6.视图最佳实践

26.mysql管理

1.mysql用户

2.创建用户

3.删除用户

4.用户修改密码

5.mysql 中的权限

6.给用户授权

7.回收用户授权

8.权限生效指令

9.练习

-- 演示 用户权限的管理


-- 创建用户 shunping	密码 123 , 从本地登录
CREATE USER 'shunping'@'localhost' IDENTIFIED BY '123'


-- 使用 root 用户创建 testdb	, 表 news 
CREATE DATABASE testdb
CREATE TABLE news (
id INT ,
content VARCHAR(32));
-- 添加一条测试数据
INSERT INTO news VALUES(100, '北京新闻');
SELECT * FROM news;


-- 给 shunping  分配查看 news 表和 添加 news 的权限
GRANT SELECT , INSERT
ON testdb.news
TO 'shunping'@'localhost'


-- 可以增加 update 权限
GRANT UPDATE
ON testdb.news
TO 'shunping'@'localhost'



-- 修改 shunping 的密码为 abc
SET PASSWORD FOR 'shunping'@'localhost' = PASSWORD('abc');


-- 回收 shunping  用户在 testdb.news  表的所有权限
REVOKE SELECT , UPDATE, INSERT ON testdb.news FROM 'shunping'@'localhost' REVOKE ALL ON testdb.news FROM 'shunping'@'localhost'

-- 删除 shunping
DROP USER 'shunping'@'localhost'

10.细节说明

-- 说明 用户管理的细节
-- 在创建用户的时候，如果不指定 Host,  则为% , %表示表示所有 IP 都有连接权限
-- create user	xxx;

CREATE USER jack

SELECT `host`, `user` FROM mysql.user

-- 你也可以这样指定
-- create user	'xxx'@'192.168.1.%'	表示 xxx 用户在 192.168.1.*的 ip 可以登录 mysql
CREATE USER 'smith'@'192.168.1.%'

-- 在删除用户的时候，如果 host  不是 %,  需要明确指定	'用户'@'host 值'
DROP USER jack -- 默认就是 DROP USER 'jack'@'%'

DROP USER 'smith'@'192.168.1.%'