MySql

创建，删除，查询数据库与表等基础语句(指令)

创建数据库

#演示数据库的操作

#启动和关闭数据库

net START mysql

net STOP mysql

#创建一个名为lby_db01的数据库。

#使用指令创建数据库

CREATE DATABASE lby_db01

#删除数据库

DROP DATABASE lby_db01

#创建一个使用utf8字符集的lby_db02数据库

CREATE DATABASE lby_db02 CHARACTER SET utf8

#创建一个使用utf8字符集，并带校对规则的lby_db03数据库

CREATE DATABASE lby_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'

查询数据库

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

SHOW DATABASES

#查看前面创建的lby_db01数据库的定义信息

SHOW CREATE DATABASE lby_db01

#在创建数据库，表的时候，为了规避关键字，可以使用反引号解决

CREATE DATABASE `create`

#删除上面创建的create数据库

DROP DATABASE `create`

备份恢复数据库

#备份，要在Dos下执行mysqldump指令其实在mysql安装目录下\bin
#这个备份的文件，就是对应的sql语句

mysqldump -u root -p -B lby_db02 lby_db03 > C:\\bak.sql

DROP DATABASE lby_db03;

#恢复数据库(注意：进入Mysql命令行执行)

source C:\\bak.sql

#第二个恢复方法，直接将bak.sql的内容放到查询编辑器中，执行就可以了

#备份数据库的表

mysqldump -u root -p lby_db02 t1 t2 >C:\\bak02.sql

创建表

#指令创建表

CREATE TABLE `user` (
	id INT,
	`name` VARCHAR(255),
	`password` VARCHAR(255),
	birthday DATE)
	CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;

简单说明：utf8表示这个表的字符集，utf8_bin表示这个表的校对规则，innodb表示这个表的引擎

character set：指定表的字符集，若没有指定，则默认为所在数据库的字符集

collate：指定表的校对规则，若没有指定，则默认为所在数据库的校对规则

engine：指定表的引擎

练习：

CREATE TABLE t15 (
	id INT,
	`name` CHAR(32),
	sex CHAR(32),
	birthday DATE,
	entry_date DATETIME,
	job CHAR(32),
	Salary DOUBLE,
	RESUME TEXT) CHARSET utf8 COLLATE utf8_bin ENGINE INNODB;
SELECT * FROM t15;

– 添加一条

INSERT INTO t15 VALUES(100,'李白','2000-11-18','2018-11-18 11:11:11',
'写诗的',10000,'皇上叫我写诗','');

修改表

#添加列
– ALTER TABLE tablename
– ADD (column datatype [DEFAULT exper]
– [, column datatype]…);
– 员工表t15的基础上增加一个image列，char类型(要求加在resume后面)

ALTER TABLE t15
ADD	image CHAR(32) NOT NULL DEFAULT '' AFTER RESUME;

DESC employee

– 显示表结构，可以查看表的所有列

#修改列
– 修改job列，使其长度为60

ALTER TABLE t15
	MODIFY job VARCHAR(60) NOT NULL DEFAULT ' '

– 删除sec列

ALTER TABLE t15
	DROP sex

– 表名改为employee

RENAME TABLE t15 TO employee

– 修改表的字符集为utf8

ALTER TABLE employee CHARACTER SET utf8

– 列名name修改为user_name

ALTER TABLE employee CHANGE NAME user_name VARCHAR(32) NOT NULL DEFAULT ' '

SELECT * FROM employee;

列类型

常用数据类型

整型

#演示整数
#说明：表的字符集，校验规则，存储引擎，使用默认
#1.如果没有指定unsigned，则TINYINT就是有符号的(-128~127)，如果没有符号(0-255)
#2.如果指定unsigned，则TINYINT就是无符号0-255

CREATE TABLE t3 (
	id TINYINT);
CREATE TABLE t4 (
	id TINYINT UNSIGNED);

INSERT INTO t3 VALUES(128);#这是非常简单的添加语句，添加失败，超过tinyint范围，因为这是带符号的

SELECT * FROM t3
INSERT INTO t4 VALUES(255);#添加成功
SELECT * FROM t4

bit

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

CREATE TABLE t5 (num BIT(8));
INSERT INTO t5 VALUES(255);
SELECT * FROM t5
SELECT * FROM t5 WHERE num = 1;

小数型

#演示decimal类型，float，double使用（一般使用double或者decimal，因为其精度更高）
#创建表

CREATE TABLE t6 (
	num1 FLOAT,
	num2 DOUBLE,
	num3 DECIMAL(30,20));

#decimal(m,n)如果没有给m，n赋值，那么会默认m为10，n为0
#添加数据

INSERT INTO t6 VALUES(88.12345678912345,88.12345678912345,88.12345678912345);

SELECT * FROM t6;

#decimal可以存放很大的数

CREATE TABLE t7 (
	num DECIMAL(65));
INSERT INTO t7 VALUES(899999999999999999999999999999999999999999999999999999999999999);
SELECT * FROM t7;

CREATE TABLE t8 (
	num BIGINT UNSIGNED);
INSERT INTO t8 VALUES(899999999999999999999999999999999999999999999999999999999999999);
SELECT * FROM t8;

字符串

#演示字符串类型使用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 t9 (
	`name` CHAR(255));
CREATE TABLE t10 (
	`name` VARCHAR(32766) CHARACTER SET gbk);

#演示字符串类型的使用细节
#1.char(4) 和 varchar(4) 这个4表示的是字符，而不是字节，不区分字符是汉字还是字母
#2.char(4)是定长(固定的大小)，就是说，即使你插入’aa’，也会占用分配的4个字节的空间
#3.varchar(4)是变长(变化的大小)，就是说，如果你插入了’aa’，实际占用空间大小并不是4个字符
– 而是按照实际占用空间来分配
– varchar本身还需要1-3个字节来记录存放内容长度
– L(实际数据大小)+(1-3)字节

CREATE TABLE t11 (
	`name` CHAR(4));
INSERT INTO t11 VALUES('abcd');

INSERT INTO t11 VALUES('白居易号');

SELECT * FROM t11;

CREATE TABLE t12 (
	`name` VARCHAR(4));
INSERT INTO t12 VALUES('abcd');

INSERT INTO t12 VALUES('白居易号');

INSERT INTO t12 VALUES('ab李白');

SELECT * FROM t12;

#4.什么时候使用char，什么时候使用varchar
– 如果数据定长，推荐使用char，比如md5的密码，邮编，手机号，身份证号码等.char(32)
– 如果一个字段的长度是不确定，我们使用varchar，比如留言，文章
– 查询速度：char>varchar

#5.在存放文本时，也可以使用Text数据类型
– 可以将Text列视为VARCHAR列，注意Text不能有默认值，大小0-2^16字节
– 如果希望存放更多字符，可以选择
– MEDIUMTEXT 0-2^24 或者 LONGTEXT 0~2^32

CREATE TABLE t13 (content TEXT, content2 MEDIUMTEXT, content3 LONGTEXT);
INSERT INTO t13 VALUES('李白是诗神','白居易是诗人','杜甫是散人~~');
SELECT * FROM t13;

日期类

#演示时间相关的类型
#创建一张表，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-18','2022-11-18 20:41:10');

– 如果我们更新了t14表的某条记录，login_time列会自动的以当前时间进行更新

CRUD语句

insert

#练习insert语句
– 创建一张商品表goods (id int , goods_name , varchar(10) , price double);
– 添加两条记录

CREATE TABLE `goods` (
	id INT,
	goods_name VARCHAR(10),
	price DOUBLE);

– 添加数据

INSERT INTO `goods` (id,goods_name,price)
	VALUES(10,'华为手机',5000);
INSERT INTO `goods` (id,goods_name,price)
	VALUES(20,'苹果手机',9000);
SELECT * FROM `goods`;

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

#追加练习
– 使用insert语句向表employee中插入2个员工的信息

INSERT INTO employee (id,user_name,birthday,entry_date,job,Salary,`resume`)
	VALUES(101,'杜甫','1998-11-21','1998-11-28 21:00:00','写诗的',6000,'为百姓写诗');
INSERT INTO employee (id,user_name,birthday,entry_date,job,Salary,`resume`)
	VALUES(102,'白居易','2016-11-21','2016-11-28 21:00:00','写诗的',6000,'为历史写诗');
SELECT * FROM employee;

#说明insert语句的细节
– 1.插入的数据应与字段的数据类型相同
– 比如把’abc’添加到int类型会错误

INSERT INTO `goods` (id,goods_name,price)**
	VALUES('abc','小米手机',4000);#Incorrect integer value
INSERT INTO `goods` (id,goods_name,price)**
	VALUES('30','小米手机',4000);#success,底层会尝试转换

– 2.数据的长度应在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中

INSERT INTO `goods` (id,goods_name,price)
	VALUES(40,'vivovivovivovivovivovivo手机',3000);#Data too long for column

– 3.在values中列出的数据位置必须与被加入的列的排序位置相对应

INSERT INTO `goods` (id,goods_name,price)
	VALUES('vivo手机',40,3000);#Incorrect integer value
INSERT INTO `goods` (goods_name,id,price)
	VALUES('vivo手机',40,3000);#success

– 4.字符和日期型数据应包含在单引号中

INSERT INTO `goods` (id,goods_name,price)
	VALUES(40,vivo手机,3000);#Unknown column 应该'vivo手机'

– 5.**列可以插入空值[前提是该字段允许为空]**，insert into table_name values(null)

INSERT INTO `goods` (id,goods_name,price)
	VALUES(50,'oppo手机',NULL);#success 因为我们创建表的时候没设置不为空 即(not null)

– 6.insert into table_name (列名…) values (),(),() 形式添加多条记录

INSERT INTO `goods` (id,goods_name,price)
	VALUES(60,'三星手机',6000),(60,'海尔手机',7000);#success

– 7.如果是给表中的所有字段添加数据，可以不写前面的字段名称

INSERT INTO `goods` 
	VALUES(70,'IBM手机',5000);#success

– 8.默认值的使用，当不给某个字段值时，如果有默认值就会添加默认值，否则报错
– 如果某个列，没有指定not null，那么添加数据时，没有给定值，则会默认给null

INSERT INTO `goods2` (id,goods_name)
	VALUES(80,'格力手机');#success,并且自动给price添加默认值100
SELECT * FROM `goods2`;

update

#演示update语句
– 要求：在上面创建的employee表中修改表中的记录
– 1.将所有员工薪水修改为5000元.如果没有带where这个条件，会修改所有的记录，因此使用要小心

UPDATE employee SET Salary = 5000

– 2.将姓名为李白的员工薪水修改为8000元

UPDATE employee 
	SET salary = 3000 
	WHERE user_name = '李白';

– 3.将姓名为杜甫的员工在原有薪水基础上增加2000元

UPDATE employee
	SET salary = salary + 2000
	WHERE user_name = '杜甫';
	
SELECT * FROM employee;

#update语句使用细节
– 1.update语法可以用新值更新原有表行中的各列
– 2.set子句指示要修改哪些列和要给予哪些值
– 3.where子句指定应更新哪些行。如没有where子句，则更新所有的行(记录)，因此使用时要小心
– 4.如果需要修改多个字段，可以通过set 字段1=值1，字段2=值2…..

delete

#delete语句演示
– 删除表中名称为’杜甫’的记录

DELETE FROM employee
	WHERE user_name = '杜甫';

– 删除表中所有记录
– 如不需要where子句，将删除表中所有数据

DELETE FROM employee;

#delete语句使用细节
– delete语句不能删除某一列的值(可使用update 设为null或者’’)

UPDATE employee SET job = '' WHERE user_name='杜甫';

– 使用delete语句仅删除记录，不删除表本身。如要删除表，使用drop table语句。
– drop table 表名;

DROP TABLE employee;
SELECT * FROM employee;

select

1.0

– select 语句【重点 难点】
– 格式:
– SELECT [distinct] * {column1,column2,column3..}
– FROM table_name;

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 
	VALUES(1,'李白',100,90,80),(2,'杜甫',100,90,90),(3,'白居易',95,80,80),(4,'王维',95,80,80),
	(5,'赵云',82,84,67),(6,'关羽',88,98,90),(7,'张飞',67,98,56),(8,'李世民',100,90,95);
	
SELECT * FROM student;

– 查询表中所有学生的信息

SELECT * FROM student;

– 查询表中所有学生的姓名和对应的英语成绩

SELECT `name`,english FROM student;

– 过滤表中重复数据 distinct

SELECT DISTINCT english FROM student;

– 要查询的记录，每个字段都相同，才会去重

SELECT DISTINCT `name`,english FROM student;

2.0

#select语句的使用
– 使用表达式对查询的列进行运算
– SELECT * |{column1|expression,column2|expression…}
– FROM table_name;

– 在select语句中可使用as语句
– SELECT column_name as 别名 from 表名;

– 统计每个学生的总分

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;

3.0

#select语句
– 查询姓名为赵云的学生成绩

SELECT * FROM student WHERE `name`='赵云';

– 查询英语成绩大于90分的同学

SELECT * FROM student WHERE english>90;

– 查询总分大于200分的所有同学

SELECT * FROM student WHERE (chinese+english+math)>200;

– 查询math大于60并且(and)id大于4的学生成绩

SELECT * FROM student WHERE math>60 AND id>4;

– 查询英语成绩大于语文成绩的同学

SELECT * FROM student WHERE english>chinese;

– 查询总分大于200分并且数学成绩小于语文成绩的姓李的学生
– 李%表示名字以李开头的就可以

SELECT * FROM student WHERE
	(chinese+math+english)>200 AND math<chinese
	 AND `name` LIKE '李%';

– 查询英语成绩在80-90之间的同学

SELECT * FROM student WHERE english>=80 AND english<=90;

– between..and…是一个闭区间

SELECT * FROM student WHERE english BETWEEN 80 AND 90;

– 查询数学分数为89，90，91的同学

SELECT * FROM student WHERE math BETWEEN 89 AND 91;
SELECT * FROM student WHERE math=89 OR math=90 OR math=91;
SELECT * FROM student WHERE math IN(89,90,91);

– 查询所有姓李的学生成绩(%表示任意多个字符)

SELECT * FROM student WHERE `name` LIKE '李%';

– 查询数学分>80，语文成绩>80的同学

SELECT * FROM student WHERE math>80 AND chinese>80;

函数

统计函数

#演示mysql统计函数的使用
– count
– 统计一个班级****共有多少学生

SELECT COUNT(*) FROM student;

– 统计数学成绩大于90的有多少个

SELECT COUNT(*) FROM student WHERE math>90;

– 统计总分大于250的人数有多少

SELECT COUNT(*) FROM student WHERE (math+chinese+english)>250;

– count(*)和count(列)的区别
– 解释：count(*)返回满足条件的记录的行数
– count(列)：统计满足条件的某列有多少个，但是会排除为null

CREATE TABLE t15 (
	`name` VARCHAR(20));
INSERT INTO t15 VALUES('tom');
INSERT INTO t15 VALUES('jack');
INSERT INTO t15 VALUES('mary');
INSERT INTO t15 VALUES(NULL);

SELECT * FROM t15;
SELECT COUNT(*) FROM t15;-- 4
SELECT COUNT(`name`) FROM t15;-- 3

– sum
– 统计一个班级数学总成绩

SELECT SUM(math) FROM student;

– 统计一个班级语文，英语，数学各科的总成绩

SELECT SUM(math),SUM(english),SUM(chinese) FROM student;

– 统计一个班级语文，英语，数学的成绩总和

SELECT SUM(math+english+chinese) FROM student;

– 统计一个班级语文成绩平均分

SELECT SUM(chinese)/COUNT(*) FROM student;

– avg
– 求一个班级数学平均分

SELECT AVG(math) FROM student;

– 求一个班级总分平均分

SELECT AVG(math+english+chinese) FROM student;

– Max/min
– 求班级最高分和最低分

SELECT MAX(math+chinese+english) FROM student;
SELECT MIN(math+chinese+english) FROM student;

分组函数

#演示group by的使用

CREATE TABLE dept(/*部门表*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
dname VARCHAR(20)NOT NULL DEFAULT '' '',
1oc VARCHAR(13)NOT NULL DEFAULT '' '');
INSERT INTO dept VALUES(10,'ACCOUNTING','NEW YORK'),(20,'RESEARCH','DALLAS'),(30,'SALES','CHICAGO'),(40,'OPERATIONS','BOSTON');
SELECT * FROM dept;

#创建表EMP雇员

DROP TABLE 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,/*上级编号*/
hiredate DATE NOT NULL,/*入职时间*/
sa1 DECIMAL(7,2) NOT NULL,/*薪水*/
comm DECIMAL(7,2),/*红利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0/*部门编号*/);

– 添加测试数据

INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,'1990-12-17',800.00,NULL,20),
(7499,'ALLEN','SALESMAN',7698,'1991-2-20',1600.00,300.00,30),
(7521,'WARD','SALESMAN',7698,'1991-2-22',1250.00,500.00,30),
(7566,'J0NES','MANAGER',7839,'1991-4-2',2975.00,NULL,20),
(7654,'MARTIN','SALESMAN',7698,'1991-9-28',1250.00,1400.00,30),
(7698,'BLAKE','MANAGER',7839,'1991-5-1',2850.00,NULL,30),
(7782,'CLARK','MANAGER',7839,'1991-6-9',2450.00,NULL,10),
(7788,'SC0TT','ANALYST',7566,'1997-4-19',3000.00,NULL,20),
(7839,'KING','PRESIDENT',NULL,'1991-11-17',5000.00,NULL,10),
(7844,'TURNER','SALESMAN',7698,'1991-9-8',1500.00,NULL,30),
(7900,'JAMES','CLERK',7698,'1991-12-3',950.00,NULL,30),
(7902,'FORD','ANALYST',7566,'1991-12-3',3000.00,NULL,20),
(7934,'MILLER','CLERK',7782,'1992-1-23',1300.00,NULL,10);

SELECT * FROM emp;

#工资级别表

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);

SELECT * FROM salgrade;

– 演示group by + having
– having子句用于限制分组显示结果

– 如何显示每个部门的平均工资和最高工资
– avg(sa1) max(sa1)
– 按照部门来分组查询

SELECT AVG(sa1),MAX(sa1),deptno 
	FROM emp GROUP BY deptno;

– 显示每个部门的每种岗位的平均工资和最低工资
– avg(sa1) min(sa1)
– 按照部门来分组查询

SELECT AVG(sa1),MIN(sa1),deptno,job
	FROM emp GROUP BY deptno,job;

– 显示平均工资低于2000的部门号和它的平均工资
– 化繁为简，各个击破
– 1.显示各个部门的平均工资和部门号
– 2.在1的结果基础上，进行过滤，保留平均工资小于2000的

SELECT AVG(sa1),deptno 
		FROM emp GROUP BY deptno
		HAVING AVG(sa1)<2000;

– 使用别名

SELECT AVG(sa1) AS avg_sal,deptno 
		FROM emp GROUP BY deptno
		HAVING avg_sal<2000;

字符串函数

#字符串函数使用演示 使用emp表来演示
– CHARSET(str) 返回字符串字符集

SELECT CHARSET(ename) FROM emp;

– CONCAT (string2 […..]) 连接字符串,将多个列拼接成一列

SELECT CONCAT(ename,' job is ',job) FROM emp;

– INSTR (string,substring) 返回substring在string中出现的位置，没有返回0
– dual亚元表，系统表 可以作为测试表使用

SELECT INSTR('libai','li') FROM DUAL;

– UCASE (string2) 转换为大写

SELECT UCASE(ename) FROM emp;

– LCASE (string2) 转换为小写

SELECT LCASE(ename) FROM emp;

– LEFT (string2,length) 从string2中的左边起取length个字符

SELECT LEFT(ename,2) FROM emp;

– RIGHT (string2,length) 从string2中的右边起取length个字符

SELECT RIGHT(ename,2) FROM emp;

– LENGTH (string) string长度[按照字节返回] 比如’李白’会返回6（utf8字符集）

SELECT LENGTH(ename) FROM emp;

– REPLACE (str,search_str,replace_str)
– 在str中用replace_str替换search_str
– 如果是manager就替换成经理

SELECT ename ,REPLACE(job,'MANAGER','经理') FROM emp;

– STRCMP (string1,string2) 逐字符比较两字符串大小

SELECT STRCMP('hsp','jsp') FROM DUAL;

– SUBSTRING (str，position，[length])
– 从str的position开始【从1开始计算】，取length个字符

SELECT SUBSTRING(ename ,1,2) FROM emp;

– LTRIM (string2) RTRIM (string2) 去除前端空格或者后端空格
– TRIM(string) 去除前后端空格

SELECT LTRIM('      李白是诗人') FROM DUAL;
SELECT LTRIM('李白是诗人      ') FROM DUAL;
SELECT LTRIM('      李白是诗人       ') FROM DUAL;

– 以首字母小写的方式显示所有员工的姓名
– 方式1

SELECT CONCAT( LCASE(SUBSTRING(ename,1,1)) ,SUBSTRING(ename,2))
	FROM emp;

– 方式2

SELECT REPLACE(ename,SUBSTRING(ename,1,1),LCASE(SUBSTRING(ename,1,1)))
	FROM emp;

– 方式3

SELECT CONCAT( LCASE(LEFT(ename,1)) ,SUBSTRING(ename,2))
	FROM emp;

数学函数

#演示数学相关函数

– ABS (num) 绝对值

SELECT ABS(-10) FROM DUAL;

– BIN (decimal_number) 十进制转二进制

SELECT BIN(10) FROM DUAL;

– CEILING (num2) 向上取整，得到比num2大的最小整数

SELECT CEILING(-1.1) FROM DUAL;

– CONV(num2,from_base,to_base) 进制转换
– 下面的含义是 8是十进制的8，转成二进制输出

SELECT CONV(8,10,2) FROM DUAL;

– 下面的含义是 8是十六进制的8，转成二进制输出

SELECT CONV(8,16,2) FROM DUAL;

– FLOOR (num2) 向下取整，得到比num2小的最大整数

SELECT FLOOR(-1.1) FROM DUAL;

– FORMAT (num,decimal_places) 保留小数位数(四舍五入)

SELECT FORMAT(78.1254582,2) FROM DUAL;

– HEX (DecimalNumber) 转十六进制

– LEAST (num,num2,…) 求最小值

SELECT LEAST(0,1,-10,4) FROM DUAL;

– MOD (numerator,denominator) 求余

SELECT MOD(10,3) FROM DUAL;

– RAND ([seed]) RAND([seed]) 返回随机数 其范围为0<=v<=1.0
– 1.如果使用RAND()每次返回不同的随机数，在0<=v<=1.0之间
– 2.如果使用RAND(seed) 返回随机数，范围0<=v<=1.0，如果seed不变，该随机数也不变了

SELECT RAND(6) FROM DUAL;

日期函数

#日期时间相关函数

– CURRENT_DATE() 当前日期

SELECT CURRENT_DATE() FROM DUAL;

– CURRENT_TIME() 当前时间

SELECT CURRENT_TIME() FROM DUAL;

– CURRENT_TIMESTAMP() 当前时间戳

SELECT CURRENT_TIMESTAMP() FROM DUAL;

– 创建测试表 信息表

CREATE TABLE mes(
	id INT,
	content VARCHAR(32),
	send_time DATETIME);

– 添加一条记录

INSERT INTO mes VALUES(1,'北京新闻',CURRENT_TIMESTAMP());
INSERT INTO mes VALUES(2,'上海新闻',NOW());
INSERT INTO mes VALUES(3,'广州新闻',NOW());

SELECT * FROM mes;
SELECT NOW() FROM DUAL;

– 应用实例
– 显示所有新闻信息，发布日期只显示日期，不显示时间

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语句求出你活了多少天？【练习】 2003-09-11 出生

SELECT DATEDIFF(NOW(),'2003-09-11') FROM DUAL;

– 如果你能活80岁，求出你还能活多少天? 【练习】 2003-09-11 出生
– 先求出活80岁时，是什么日期
– 然后在使用datediff(x,now());
– INTERVAL 80 YEAR：YEAR可以是替换成年月日，时分秒

SELECT DATEDIFF(DATE_ADD('2003-09-11',INTERVAL 80 YEAR),NOW()) FROM DUAL;

– YEAR|MONTH|DAY| DATE (datetimee)：(即返回年|月|日)

SELECT YEAR(NOW()) FROM DUAL;
SELECT MONTH(NOW()) FROM DUAL; 
SELECT DAY(NOW()) FROM DUAL;
SELECT MONTH('2003-11-10') FROM DUAL;

– unix_timestamp()：返回的是1970-01-01 到现在的秒数

SELECT UNIX_TIMESTAMP() FROM DUAL;

– FROM_UNIXTIME()：可以把一个unix_timestamp秒数，转成指定格式的日期

SELECT FROM_UNIXTIME(1669349758,'%Y-%m-%d') FROM DUAL;
SELECT FROM_UNIXTIME(1669349758,'%Y-%m-%d %H:%i:%s') FROM DUAL;

加密函数

#演示加密函数和系统函数
– USER() 查询用户
– 可以查看登录到mysql的有哪些用户，以及登录的IP

SELECT USER() FROM DUAL; -- 用户@IP地址

– DATABASE() 查询当前使用数据库名称

SELECT DATABASE();

– MD5(str) 为字符串算出一个MD5 32位的字符串，常用(用户密码)加密
– root 密码是lby->加密md5->在数据库中存放的是加密后的密码

SELECT MD5('lby') FROM DUAL;
SELECT LENGTH(MD5('lby')) FROM DUAL;

– 演示用户表，存放密码时，是md5

CREATE TABLE lby_user
	(id INT,
	`name` VARCHAR(32) NOT NULL DEFAULT ' ',
	pwd CHAR(32) NOT NULL DEFAULT ' ');

INSERT INTO lby_user
	VALUES(100,'李博扬',MD5('lby'));

SELECT * FROM lby_user;
SELECT * 
	FROM lby_user
	WHERE `name`='李博扬' AND pwd=MD5('lby');

– PASSWORD(str) –加密函数,在MYSQL数据库的用户密码就是PASSWORD加密的

SELECT PASSWORD('lby') FROM DUAL;-- *2DE7B6C45E31629453E39499BDA8A5950CA05900

– select * from mysql.user \G 从原文密码str计算并返回密码字符串
– 通常用于对mysql数据库的用户密码加密
– mysql.user 表示数据库.表

SELECT * FROM mysql.user;

流程控制函数

#演示流程控制语句
– IF(expr1,expr2,expr3) 如果expr1为True，则返回expr2，否则返回expr3

SELECT IF(TRUE,'李白','杜甫') FROM DUAL;

– IFNULL(expr1,expr2) 如果expr1不为NULL，则返回expr1，否则返回expr2

SELECT IFNULL(NULL,'李白') FROM DUAL;

– SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END;[类似多重分支]
– 如果expr1为TRUE，则返回expr2，如果expr3为TRUE，返回expr4，否则返回expr5

SELECT CASE
	WHEN TRUE THEN 'jack' -- jack
	WHEN FALSE THEN 'tom'
	ELSE 'mary' END

– 1.查询emp表，如果comm是null，则显示0.0
– 判断是否为null，要使用is null，判断不为空，使用is not null

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  job END) job
	FROM emp;

查询增强

#查询加强
– 使用where子句
– 如何查找1992.1.1后入职的员工
– 在mysql中，日期类型可以直接比较，需要注意格式

SELECT * FROM emp
	WHERE hiredate > '1992-1-1';

– 如何使用like操作符
– %表示0到多个字符 _表示单个字符
– 如何显示首字符为S的员工姓名和工资

SELECT ename,sa1
	FROM emp
	WHERE ename LIKE 'S%';

– 如何显示第三个字符为大写O的所有员工的姓名和工资

SELECT ename,sa1 FROM emp
	WHERE ename LIKE '__0%';

– 如何显示没有上级的雇员的情况

SELECT * FROM emp
	WHERE mgr IS NULL;

– 查询表结构

DESC emp;

– 使用order by子句
– 如何按照工资从低到高的顺序[升序]，显示雇员的信息

SELECT * FROM emp
	ORDER BY sa1;

– 按照部门号升序而雇员的工资降序排列，显示雇员信息

SELECT * FROM emp
	ORDER BY deptno ASC,sa1 DESC;

分页查询

#分页查询
– 按雇员的id号升序取出，每页显示3条记录，请分别显示第1页，第2页，第2页
– 第1页

SELECT * FROM emp
	ORDER BY empno
	LIMIT 0,3;

– 第2页

SELECT * FROM emp
	ORDER BY empno
	LIMIT 3,3;

– 第3页

SELECT * FROM emp
	ORDER BY empno
	LIMIT 6,3;

– 推导一个公式

SELECT * FROM emp
	ORDER BY empno

​ LIMIT 每页显示记录数 * (第几页—1)，每页显示记录数
– 练习

SELECT * FROM emp
	ORDER BY empno DESC
	LIMIT 20,5;

分组增强

#增强group by的使用

SELECT * FROM emp;

– 1.显示每种岗位的雇员总数，平均工资

SELECT COUNT(*),job,AVG(sa1) FROM emp
	GROUP BY job;

– 2.显示雇员总数，以及获得补助的雇员数
– 思路：获得补助的雇员数就是comm列为非null，就是count(),如果该列的值为null，是不会统计的

SELECT COUNT(*),COUNT(comm) 
	FROM emp;

– 扩展要求 统计没有获得补助的雇员数

SELECT COUNT(*),COUNT(IF(comm IS NULL,1,NULL))
	FROM emp;
SELECT COUNT(*),COUNT(*)-COUNT(comm)
	FROM emp;

– 3.显示管理者的总人数

SELECT COUNT(DISTINCT mgr)
	FROM emp;

– 4.显示雇员工资的最大差额

SELECT MAX(sa1)-MIN(sa1)
	FROM emp;

多表查询

笛卡儿集

#多表查询
– 显示雇员名，雇员工资及所在部门的名字[笛卡儿集]
/*
分析
1.雇员名，雇员工资来自emp表
2.部门的名字来自dept表
3.需求对emp和dept查询
*/

SELECT * FROM emp,dept;

– 在默认情况下，当两个表查询时，规则为
– 1.从第一张表中，取出一行和第二张表的每一行进行组合
– 2.返回结果，一共返回的记录数为第一张表的行数*第二张表的行数
– 3.这样多表查询默认处理返回的结果，称为笛卡尔表
– 4.解决这个多表的关键就是要写出正确的过滤条件where，需求程序员进行分析
– 5.当我们需要指定显示某个表的列是，需要 表.列

SELECT ename,sa1,dname FROM emp,dept
	WHERE emp.deptno=dept.deptno;

SELECT ename,sa1,emp.deptno FROM emp,dept
	WHERE emp.deptno=dept.deptno;

#注意事项：多表查询的条件不能少于表的个数-1，否则会出现笛卡儿集
– 如何显示部门号为10的部门名，员工名和工资

SELECT ename,sa1,dname,emp.deptno
	from emp,dept
	where emp.deptno=dept.deptno and emp.deptno=10;

– 显示各个员工的姓名，工资，及其工资的级别
– 思路：姓名，工资来自emp
– 工资级别来自salgrade

select ename,sa1,grade
	from emp,salgrade
	where sa1 between losal and hisal ;

自连接

#多表查询的自连接
– 思考：显示公司员工名字和他的上级的名字
– 分析：员工名字在emp，上级的名字在emp
– 员工与上级是通过emp表的mgr列关联

SELECT worker.ename,boss.ename
	FROM emp worker,emp boss
	WHERE worker.mgr=boss.empno;

– 自连接的特点
– 1.把同一张表当作两张表来使用
– 2.需要给表取别名
– 格式：表名 表别名
– 3.列名不明确，可以指定列的别名

多行子查询

#子查询的演示
– 请思考：如何显示与SMITH同一部门的所有员工？
/*
1.先查询到SMITH的部门号
2.把上面的select语句当作一个子查询来使用
*/

SELECT deptno
	FROM emp
	WHERE ename='SMITH'

– 单行子查询

SELECT *
	FROM emp
	WHERE deptno=(
		SELECT deptno
		FROM emp
		WHERE ename='SMITH'
		)

– 如何查询和部门号为10的工作相同的雇员的名字，岗位，工资，部门号。
– 但是不包含部门号为10的雇员
/*
1.先查询到10号部门有哪些工作
2.把上面查询的结果当作子查询使用
*/

SELECT DISTINCT job
	FROM emp
	WHERE deptno=10;

SELECT ename,job,sa1,deptno
	FROM emp
	WHERE job IN(
		SELECT DISTINCT job
		FROM emp
		WHERE deptno=10
		) AND deptno!=10;

多列子查询

#多列子查询
– 请思考如何查询与smith的部门和岗位完全相同的所有雇员(并且不包含smith本人)
– (字段1，字段2…..) = (select 字段1，字段2 from……)

– 分析：
– 1.得到smith的部门号和岗位

SELECT deptno,job
	FROM emp
	WHERE ename='SMITH';

– 2.把上面查询结果当作子查询来使用，并且使用多列子查询的语法进行匹配

SELECT*
	FROM emp
	WHERE(deptno,job)=(
			SELECT deptno,job
			FROM emp
			WHERE ename='allen'
		) AND ename<>'allen';

– <>表示不等于

#子查询练习
– 查找每个部门工资高于本部门平均工资的人的资料

SELECT *
	FROM emp,(
		SELECT deptno,AVG(sa1) avg_sa1
		FROM emp
		GROUP BY deptno
	)temp
	WHERE emp.deptno=temp.deptno AND emp.sa1>temp.avg_sa1

– 查找每个部门工资最高的人的详细资料

SELECT *
	FROM emp,(
		SELECT deptno,MAX(sa1) max_sa1
		FROM emp
		GROUP BY deptno
	)temp
	WHERE emp.deptno=temp.deptno AND emp.sa1=temp.max_sa1

– 查询每个部门的信息(包括：部门号，编号，地址)和人员数量
– 分析：
– 1.部门名,编号,地址来自dept表
– 2.各个部门的人员数量-》构建一个临时表

SELECT COUNT(*),deptno
	FROM emp
	GROUP BY deptno;	

SELECT dname,dept.deptno,1oc ,per_num
	FROM dept,(
		SELECT COUNT(*) per_num,deptno
		FROM emp
		GROUP BY deptno
	)temp
	WHERE temp.deptno=dept.deptno

– 还有一种写法,表.*表示将该表所有列都显示出来，可以简化sql语句
– 在多表查询中，当多个表的列不重复时，才可以直接写列名

SELECT temp.*,dname,1oc
	FROM dept,(
		SELECT COUNT(*) per_num,deptno
		FROM emp
		GROUP BY deptno
	)temp
	WHERE temp.deptno=dept.deptno

表复制和去重

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

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

– 演示如何自我复制
– 1.先把emp表的记录复制到my_tb01

INSERT INTO my_tb01
	(id,`name`,sal,job,deptno)
	SELECT empno,ename,sa1,job,deptno FROM emp;

– 2.自我复制

INSERT INTO my_tb01
	SELECT * FROM my_tb01;

– 如何删除掉一张表重复记录
– 1.先创建一张表my_tb02
– 2.让my_tb02有重复的记录

CREATE TABLE my_tb02 LIKE emp;

– 把emp表的结构(列)，复制到my_tb02

INSERT INTO my_tb02
	SELECT * FROM emp;

– 3.考虑去重
– 分析
/*
1.先创建一张临时表my_tb，该表的结构与my_tb02一致
2.把my_tb02的记录通过distinct关键字处理后，把记录复制到my_tb
3.清除掉my_tb02记录
4.把my_tb表的记录复制到my_tb02
5.drop掉临时表my_tb
*/

CREATE TABLE my_tb LIKE my_tb02;

INSERT INTO my_tb
	SELECT DISTINCT * FROM my_tb02;

DELETE FROM my_tb02;

INSERT INTO my_tb02
	SELECT * FROM my_tb;
	
DROP TABLE my_tb;

合并查询

#演示合并查询

SELECT ename,sa1,job FROM emp WHERE sa1>2500;

SELECT ename,sa1,job FROM emp WHERE job='manager'

– union all 就是将两个查询结果合并，不会去重

SELECT ename,sa1,job FROM emp WHERE sa1>2500
UNION ALL
SELECT ename,sa1,job FROM emp WHERE job='manager'

– union 该操作赋与 union all相似，但是会自动去掉结果中重复的记录

SELECT ename,sa1,job FROM emp WHERE sa1>2500
UNION
SELECT ename,sa1,job FROM emp WHERE job='manager'

左外连接与右外连接

#演示外连接

– 比如：列出部门名称和这些部门的员工名称和工作，同时要求显示出那些没有员工的部门

– 使用我们学习过的多表查询的sql，看看效果如何

SELECT dname,ename,job
	FROM emp,dept
	WHERE emp.deptno=dept.deptno
	ORDER BY dname;

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

– 创建stu

CREATE TABLE stu (
	id INT,
	`name` VARCHAR(32));
INSERT INTO stu VALUES(1,'jack'),(2,'tom'),(3,'kity'),(4,'noo');

– 创建表exam

CREATE TABLE exam (
	id INT,
	grade INT);
INSERT INTO exam VALUES(1,56),(2,76),(11,8);

– 使用左连接(显示所有人的成绩，如果没有成绩，也要显示该人的姓名和id号，成绩显示为空)
/*
格式：select…from 表1 left join 表2 on 条件
*/

SELECT `name`,stu.id,grade
	FROM stu,exam
	WHERE stu.id=exam.id

– 改成左连接

SELECT `name`,stu.id ,grade
	FROM stu LEFT JOIN exam
	ON stu.id=exam.id

– 右外连接(显示所有成绩，如果没有名字匹配，显示空)

SELECT `name`,grade,exam.id
	FROM stu RIGHT JOIN exam
	ON stu.id=exam.id

SELECT dept.deptno,`ename`,job
	FROM emp RIGHT JOIN dept
	ON emp.deptno=dept.deptno

SELECT dept.deptno,`ename`,job
	FROM dept LEFT JOIN emp
	ON emp.deptno=dept.deptno

MySql约束

主键

#演示主键使用
– id name email

CREATE TABLE t17 (
	id INT PRIMARY KEY, -- 表示id列是主键
	`name` VARCHAR(32),
	email VARCHAR(32));

– 主键列的值是不可以重复的

INSERT INTO t17
	VALUES(1,'jack','jack@sohu.com'),(2,'tom','tom@sohu.com');

INSERT INTO t17 VALUES(1,'lvy','lvy@sohu.com'); 添加失败，主键重复

– 主键使用细节
– primary key不能重复而且不能为null

INSERT INTO t17 VALUES(NULL,'gsp','gsp@sohu.com'); -- Column 'id' cannot be null

– 一张表最多只能有一个主键，但可以是复合主键

CREATE TABLE t18 (
	id INT PRIMARY KEY, 
	`name` VARCHAR(32) PRIMARY KEY,
	email VARCHAR(32)); -- Multiple primary key defined

– 演示复合主键使用(id和name做成复合主键，即id和name做为一个复合主键，而不是id是主键1，name是主键2)

CREATE TABLE t18 (
	id INT , 
	`name` VARCHAR(32),
	email VARCHAR(32),
	PRIMARY KEY(id,`name`));

INSERT INTO t18 VALUES(1,'gsp','gsp@sohu.com');
INSERT INTO t18 VALUES(1,'dsa','dsa@sohu.com');
INSERT INTO t18 VALUES(2,'dsa','dsa@sohu.com');
INSERT INTO t18 VALUES(1,'gsp','gsp@sohu.com'); -- Duplicate entry '1-gsp' for key 'PRIMARY'

– 主键的指定方式有两种
– 1.直接在字段名后指定：字段名 primary key
– 2.在表定义最后写 primary key(列名);
– 参考上面创建表的方式
– 使用desc表名，可以看到primary key的情况

DESC t18;
DESC t17;

– 实际开发中，每个表往往都会设计一个主键

unique

#演示unique使用

CREATE TABLE t19 (
	id INT UNIQUE,-- 表示id列是不可重复的，但可为null
	`name` VARCHAR(32),
	email VARCHAR(32));
	
INSERT INTO t19 VALUES(1,'jack','jack@sohu.com');-- Duplicate entry '1' for key 'id'
INSERT INTO t19 VALUES(1,'tom','tom@sohu.com');

– unique使用细节
– 如果没有指定not null，则unique字段可以有多个null
– 如果一个列(字段)是unique not null，那么使用效果类似primary key

INSERT INTO t19 VALUES(NULL,'jack','jack@sohu.com');
SELECT * FROM t19;

– 一张表可以有多个unique字段

CREATE TABLE t20 (
	id INT UNIQUE,
	`name` VARCHAR(32) UNIQUE,
	email VARCHAR(32));

外键

#外键演示
– 创建主表 my_class

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

– 创建从表 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));

– 测试数据

INSERT INTO my_class VALUES(100,'java'),(200,'web');
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,'mary',300); -- 失败，因为300号班级不存在
INSERT INTO my_stu VALUES(4,'king',NULL); -- 可以，外键没有写not null

– 外键细节
– 1.外键指向的表的字段，要求是primary key或者是unique
– 2.表的存储引擎是innodb，这样的表才支持外键
– 3.外键字段的类型要和主键字段的类型一致(长度可以不同)
– 4.外键字段的值，必须在主键字段中出现过，或者为null(前提是外键字段允许为null)
– 5.一旦建立主外键的关系，数据不能随意删除了(需要先删除外键对应数据，才能删除主键对应数据)

check

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

CREATE TABLE t21 (
	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 t21 VALUES(1,'jack','mid',1)-- 添加失败(mid不在检查范围内并且sal小于1000)
SELECT * FROM t21;

自增长

#演示自增长的使用
– 创建表

CREATE TABLE t22 (
	id INT PRIMARY KEY AUTO_INCREMENT,
	email VARCHAR(32) NOT NULL DEFAULT '',
	`name` VARCHAR(32) NOT NULL DEFAULT '');
DESC t22;

– 测试自增长的使用

INSERT INTO t22 VALUES(NULL,'tom@sohu.com','tom');
INSERT INTO t22 (email,`name`) VALUES('lvy@sohu.com','lvy');
SELECT * FROM t22;

– 自增长使用细节
– 1.一般来说自增长是和primary key配合使用的
– 2.自增长也可以单独使用(但是需要配合一个unique)
– 3.自增长修饰的字段为整数型的(虽然小数也可以但是非常非常少这样使用)
– 4.自增长默认从1开始，你也可以通过如下命令修改
– alter table 表名 auto_increment=xxx;

ALTER TABLE t22 AUTO_INCREMENT=10;
INSERT INTO t22 VALUES(NULL,'jack@sohu.com','jack');

– 5.如果你添加数据时，给自增长字段(列)指定的有值，则以指定的值为准
– 6.一般来说，如果指定了自增长，就按照自增长的规则来添加数据，不要自己指定数值
/* 同时自增长会从指定的值开始,
比如之前自增长到10，而你添加数据时，
指定了100给自增长字段，
那么自增长会从10直接跳到100重新开始自增长
*/

INSERT INTO t22 VALUES(100,'brck@sohu.com','brck');

MySql索引

索引优化速度及索引原理

– 索引的原理
– 没用索引为什么查询海量数据会慢？因为是全表扫描
– 使用索引为什么查询海量数据会快？因为形成一个索引的数据结构，比如二叉树
– 使用索引的代价
– 1.磁盘占用空间变大
– 2.对dml(update,delete,insert)语句的执行效率产生影响，变慢

索引的类型

– 1.主键索引，主键自动的为主索引(类型Primary Key)
– 2.唯一索引(UNIQUE)
– 3.普通索引(INDEX)
– 4.全文索引(FULLTEXT)[适用于MylSAM]

mysql的索引的使用

– 创建索引

CREATE TABLE t23 (
	id INT,
	`name` VARCHAR(32));

– 查询表是否有索引

SHOW INDEXES FROM t23;

– 添加索引
– 添加唯一索引

CREATE UNIQUE INDEX id_index ON t23 (id);

– 添加普通索引方法1

CREATE INDEX id_index ON t23 (id);

– 添加普通索引方法2

ALTER TABLE t23 ADD INDEX id_index (id);

– 如何选择索引类型
– 如果某列的值，是不会重复的，则优先考虑使用unique索引，否则使用普通索引

– 添加主键索引

CREATE TABLE t24 (
	id INT,
	`name` VARCHAR(32));
ALTER TABLE t24 ADD PRIMARY KEY (id);
SHOW INDEX FROM t24;

– 删除索引

DROP INDEX id_index ON t23;

– 删除主键索引

ALTER TABLE t24 DROP PRIMARY KEY;

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

– 查询索引
– 方式1
SHOW INDEX FROM t23;
– 方式2
SHOW INDEXES FROM t23;
– 方式3
SHOW KEYS FROM t23;
– 方式4
DESC t23

创建索引规则

– 哪些列适合使用索引
– 1.较频繁的作为查询条件字段应该创建索引
– 2.唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
– 3.更新非常频繁的字段不适合创建索引
– 4.不会出现在where子句中字段不该创建索引

MySql事务

什么是事务及事务操作？

– 事务用于保证数据的一致性，它由一组相关的dml语句组成，该组的dml语句要么全部成功，要么全部失败
– 演示mysql数据库控制台事务的几个重要操作
– start transaction 开始一个事务
– savepoint 设置保存点
– rollback to 回退事务
– rollback 回退全部事务
– commit 提交事务，所有的操作生效，不能回退

– 1.创建一张表

CREATE TABLE t25 (
	id INT,
	`name` VARCHAR(32));

– 2.开始事务

START TRANSACTION

– 3.设置保存点

SAVEPOINT a

– 执行dml操作

INSERT INTO t25 VALUES(100,'tom');
SELECT * FROM t25;

– 4.设置保存点

SAVEPOINT b

– 执行dml操作

INSERT INTO t25 VALUES(200,'jack');

– 回退到b

ROLLBACK TO b

– 回退到a

ROLLBACK TO a

– 如果这样，表示直接回退到事务开始的状态

ROLLBACK

– 提交事务

COMMIT

事务注意细节

– 讨论 事务细节
– 1.如果不开始事务，在默认情况下，dml操作是自动提交的，不能回滚

INSERT INTO t25 VALUES(300,'mary');
SELECT * FROM t25;

– 2.如果开始一个事务，你没有创建保存点，你可以执行rollback
– 默认就是回退到事务开始的状态

START TRANSACTION
INSERT INTO t25 VALUES(400,'king');
INSERT INTO t25 VALUES(500,'scott');
ROLLBACK

– 3.你也可以在这个事务中(还没有提交时)，创建多个保存点
– 4.你可以在事务没有提交前，选择回退到哪个保存点
– 5.InnoDB存储引擎支持事务，MyISAM不支持
– 6.开始一个事务 start transaction 或者 set autocommit=off;

MySql事务隔离级别

4种隔离级别

设置隔离

#事务隔离级别介绍
– 1.多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作
– 以保证各个连接在获取数据时的准确性
– 2.如果不考虑隔离性，可能会引发如下问题：
– 脏读：当一个事务读取另一个事务尚未提交的修改时，产生脏读
– 不可重复读：同一查询在同一事务中多次进行，由于其他提交事务所做的
– 修改或删除，每次返回不同的结果集，此时发生不可重复读
– 幻读：同一查询在同一事务中多次进行，由于其他提交事务所做的插入操作
– 每次返回不同的结果集，此时发生幻读

#演示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 LEVE

MySql表类型与存储引擎

表类型和存储引擎

– 查看所有存储引擎

SHOW ENGINES

– innodb 存储引擎
– 1.支持事务 2.支持外键 3.支持行级锁

– myisam 存储引擎
– 1.添加数据快 2.不支持外键和事务 3.支持表级锁

CREATE TABLE t26 (
	id INT,
	`name` VARCHAR(32)) ENGINE MYISAM
START TRANSACTION
SAVEPOINT a
INSERT INTO t26 VALUES(1,'jack');
SELECT * FROM t26;
ROLLBACK TO a

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

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

– 指令修改存储引擎

ALTER TABLE `t27` ENGINE = INNODB;

如何选择表的存储引擎

视图

视图原理

– 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;
SELECT sa1 FROM emp_view01; -- Unknown column 'sa1' in 'field list'

– 查看创建视图的指令

SHOW CREATE VIEW emp_view01;

– 删除视图

DROP VIEW emp_view01;

– 视图细节讨论
– 1.创建视图后，到数据库去看，对应视图只有一个视图结构文件(形式：视图名.frm)，不会有数据文件(视图名.ibd)
– 2.视图的数据变化会影响到基表，基表的数据变化也会影响到视图(insert,update,delete)
– 修改视图

UPDATE emp_view01
	SET job='MANAGER'
	WHERE empno=7369;
SELECT * FROM emp_view01; -- 查询视图
SELECT * FROM emp; -- 查询基表

– 修改基表，也会影响到视图

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;

– 视图练习
– 针对emp，dept和salgrade三张表，创建一个视图emp_view03
– 可以显示雇员编号，雇员名，雇员部门名称和薪水级别[即使用三张表，创建一个视图]
/*
分析：使用三表联合查询，得到结果
将得到的结果，构建成视图
*/

CREATE VIEW emp_view03
	AS
SELECT empno,ename,dname,grade
	FROM emp,dept,salgrade
	WHERE emp.deptno=dept.deptno AND 
	(sa1 BETWEEN losal AND hisal)
SELECT * FROM emp_view03

MySql管理

MySql用户管理

– MySql用户的管理
– 原因：当我们做项目开发时，可以根据不同的开发人员，赋给他相应的MySql操作权限
– 所以，MySql数据库管理人员(root)，根据需要创建不同的用户，赋给相应的权限，供人员使用

– 1.创建新的用户
– 解读(1) ‘lby‘@’localhost’表示用户的完整信息，’lby’用户名，’localhost’登录的IP
– (2) 123456表示密码，但是注意，存放到mysql.user表时，是password(‘123456’)加密后的密码

CREATE USER 'lby'@'localhost' IDENTIFIED BY '123456'

– 2.删除用户

DROP USER 'lby'@'localhost'

– 3.修改自己的密码

SET PASSWORD=PASSWORD('abcdef')

– 4.修改其他人的密码，需要权限(root用户权限高，可以修改其他用户密码)

SET PASSWORD FOR 'lby'@'localhost'=PASSWORD('123456')

MySql权限管理

– 演示用户权限的管理
– 1.创建用户 libai 密码 123，从本地登录

CREATE USER 'libai'@'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

– 给libai分配查看news表和添加news的权限

GRANT SELECT,INSERT
	ON testdb.news
	TO 'libai'@'localhost'

– 给libai增加权限

GRANT UPDATE
	ON testdb.news
	TO 'libai'@'localhost'

– 修改libai的密码为abc

SET PASSWORD FOR 'libai'@'localhost'=PASSWORD('abc')

– 回收libai用户在testdb.news表的所有权限

REVOKE SELECT,UPDATE,INSERT ON testdb.news FROM 'libai'@'localhost'
REVOKE ALL ON testdb.news FROM 'libai'@'localhost'

– 如果权限没有生效，可以执行下面的指令

FLUSH PRIVILEGES

MySql用户管理细节

#说明用户管理的细节
– 在创建用户的时候，如果不指定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.%'