零基础学MySQL

1. 零基础学MySQL

1.1 数据库简介

1.1.1 数据库三层结构

1. 所谓安装Mysql数据库，就是在主机安装一个数据库管理系统(DBMS)，这个管理程序可以管理多个数据库。DBMS(database manage system)
2. 一个数据库中可以创建多个表,以保存数据(信息)。
3. 数据库管理系统(DBMS)、数据库和表的关系如图所示：

1.1.2 数据在数据库中的存储方式

1.2 SQL语句分类

DDL: 数据定义语句 [create表，库...]
DML: 数据操作语句 [增加insert，修改update，删除delete]
DQL: 数据查询语句[select ]
DCL: 数据控制语句[管理数据库: 比如用户权限grant revoke ]

1.3 创建数据库

1. CHARACTER SET:指定数据库采用的字符集，如果不指定字符集，默认utf8
2. COLLATE:指定数据库字符集的校对规则(常用的utf8 bin[区分大小引]、utf8 _general_ci[不区分大小引]注意默认是utf8_general_ci)

案例练习：
1. 创建一个名称为zak_db01的数据库。
2. 创建一个使用utf8字符集的zak db02数据库
3. 创建一个使用utf8字符集，并带校对规则的zak db03数据库

#演示数据库的操作
#创建一个名称为zak_db01的数据库。#使用指令创建数据库CREATE DATABASE zak_db01;#删除数据库指令DROP DATABASE zak_db01#创建一个使用utf8字符集的hsp_db02数据库CREATE DATABASE zak_db02 CHARACTER SET utf8#创建一个使用utf8字符集，并带校对规则的hsp_db03数据库CREATE DATABASE zak_db03 CHARACTER SET utf8COLLATEutf8_bin#校对规则utf8_bin区分大小默认utf8_general_ci不区分大小写#下面是一条查询的sql,select查询*表示所有字段FROM从哪个
#WHERE从哪个字段NAME='tom'查询名字是tomSELECT *FROM t1WHERE NAME = 'tom'

1.4 查看、删除数据库

#显示数据库语句:SHOW DATABASES
#显示数据库创建语句:SHOW CREATE DATABASE db_name
#数据库删除语句[一定要慎用]:DROP DATABASE [IF EXISTS] db_name

案例练习:
1. 查看当前数据库服务器中的所有数据库
2. 查看前面创建的zak_db01数据库的定义信息
3. 删除前面创建的zak_db01数据库

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

1.5 备份恢复数据库

备份数据库(注意:在DOS执行)命令行
mysqldump -u用户名 -p -B 数据库1 数据库2 数据库n >文件名.sqI
恢复数据库(注意: 进入Mysql命令行再执行)
Source文件名.sql
案例练习: database03.sql 备份 zak_db02 和 zak_db03库中的数据，并恢复

#备份, 要在Dos下执行mysqldump指令其实在mysql安装目录\bin#这个备份的文件，就是对应的sql语句mysqldump -u root -p -B zak_db02 zak_db03 > d:\\bak.sqlDROP DATABASE ecshop;#恢复数据库(注意：进入Mysql命令行再执行)source d:\\bak.sql#第二个恢复方法， 直接将 bak.sql 的内容放到查询编辑器中，执行

1.6 备份恢复数据库的表

#备份库的表
mysqldump -u 用户名 -p密码 数据库 表1 表2 表n > d:\\文件名.sql

1.7 安装 Ecshop 数据库

案例练习：

#这是一个ecshop 的数据库，包括 ecshop 所有的表，请导入到mysql数据库中[备份]#进入到mysql命令行:sourceecshop 备份文件路径
#再将ecshop 整个数据库备份到你的 d:\\ecshop.sql到dos 下 :mysqldump -u root -p -B ecshop > d:\\ecshop.sql
#将mysql 的ecshop 数据库删除, 并通过备份的d:\\ecshop.sql恢复
#进入mysql命令行source d:\\ecshop.sql

1.8 创建表(按课程大纲顺序)

案例：

 #指令创建表
#注意：zak_db02 创建表时，要根据需保存的数据创建相应的列，并根据数据的类型定义相应的列类型。例：user表#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

1.9 Mysql 常用数据类型(列类型)

1.9.1 数值型(整数)的基本使用

1. 说明：使用规范: 在能够满足需求的情况下，尽量选择占用空间小的

案例：

#演示整型的是一个
#使用 tinyint 来演示范围 有符号 -128~127 如果没有符号 0-255#说明：表的字符集，校验规则,存储引擎，使用默认
#1. 如果没有指定 unsinged, 则 TINYINT 就是有符号
#2. 如果指定 unsinged, 则 TINYINT 就是无符号 0-255CREATE TABLE t3(id TINYINT);CREATE TABLE t4(id TINYINT UNSIGNED);INSERT INTO t3 VALUES(127); #这是非常简单的添加语句SELECT * FROM t3INSERT INTO t4 VALUES(255);SELECT * FROM t4;

1.9.2 如何定义一个无符号的整数

create table t10 (id tinyint ); #默认是有符号的
create table t11 (id tinyint unsigned ); #无符号的

1.9.3 数值型(bit)的使用

1. 基本使用
        mysql> create table t05 (num bit(8));
        mysql>insert into t05 (1,3);
        mysql> insert into t05 values(2,65);

#演示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(255);SELECT * FROM t05;SELECT * FROM t05 WHERE num = 1;

1.9.4 数值型(小数)的基本使用

1. FLOAT/DOUBLE [UNSIGNED]     Float单精度精度，Double双精度．
2. DECIMAL [M,D] [UNSIGNED]
        可以支持更加精确的小数位。M是小数位数(精度)的总数，D是小数点(标度)后面的位数。
        如果D是0，则值没有小数点或分数部分。M最大65。D最大是30。如果D被省略,默认是0。如果M被省略，默认是10。
        建议: 如果希望小数的精度高，推荐使用decimal
案例：

#演示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)INSERT INTO t08 VALUES(8999999933338388388383838838383009338388383838383838383);SELECT * FROM t08;

1.9.5 字符串的基本使用

#演示字符串类型使用char varchar
--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=32766CREATE TABLE t09 (`name` CHAR(255));
CREATE TABLE t10 (`name` VARCHAR(32766)) CHARSET gbk;DROP TABLE t10;

1.9.6 字符串使用细节

1. 细节1
char(4) //这个4表示字符数(最大255)，不是字节数,不管是中文还是字母都是放四个,按字符计算.
varchar(4) //这个4表示字符数，不管是字母还是中文都以定义好的表的编码来存放数据
不管是中文还是英文字母，都是最多存放4个，是按照字符来存放的.
2. 细节2
char(4)是定长(固定的大小)，就是说，即使你插入'aa'，也会占用分配的4个字符的空间.
varchar(4)是变长(变化的大小)，就是说，如果你插入了'aa',实际占用空间大小并不是4个字符，而是按照实际占用空间来分配(说明: varchar本身还需要占用1-3个字节来记录存放内容长度)
        L(实际数据大小) + (1-3)字节
3. 细节3
        什么时候使用char,什么时候使用varchar
        1. 如果数据是定长，推荐使用char, 比如md5的密码,邮编，手机号,身份证号码等. char(32)
        2. 如果一个字段的长度是不确定,我们使用varchar,比如留言,文章
查询速度: char > varchar
4. 细节4
        在存放文本时，也可以使用Text 数据类型.可以将TEXT列视为VARCHAR列,注意Text不能有默认值.大小 0-2^16 字节
        如果希望存放更多字符，可以选择 MEDIUMTEXT 0 - 2^24 或者 LONGTEXT 0 ~ 2^32

#演示字符串类型的使用细节
#char(4)和varchar(4)这个4表示的是字符，而不是字节,不区分字符是汉字还是字母
CREATE TABLE t11(`name`CHAR(4));INSERT INTO t11 VALUES('zak');SELECT * FROM t11;CREATE TABLE t12(`name` VARCHAR(4));INSERT INTO t12 VALUES('zak');INSERT INTO t12 VALUES('ab北京');SELECT * FROM t12;
#如果varchar不够用，可以考试使用mediumtext或者longtext,
#如果想简单点，可以使用直接使用textCREATE TABLE t13(content TEXT,content2 MEDIUMTEXT,content3 LONGTEXT);INSERT INTO t13 VALUES('zak', 'zak123', 'zak123456');SELECT * FROM t13;

1.9.7 日期类型的基本使用

日期类型的细节说明
TimeStamp 在 Insert 和 update时，自动更新

#演示时间相关的类型
#创建一张表,date,datetime,timestampCREATE TABLE t14(birthday DATE,--生日job_time DATE TIME,--记录年月日时分秒login_time TIMESTAMPNOTNULL DEFAULT CURRENT_TIMESTAMPONUPDATE CURRENT_TIMESTAMP); --登录时间,如果希望login_time列自动更新,需要配置SELECT * FROM t14;
INSERT INTO t14 (birthday,job_time)VALUES ('2022-11-11','2022-11-1110:10:10');--如果我们更新 t14 表的某条记录，login_time 列会自动的以当前时间进行更新

1.10 创建表练习

创建一个员工表 emp 选用适当的数据类型

CREATE TABLE `emp`(id INT,`name` VARCHAR(32),sex CHAR(1),brithday DATE,entry_date DATETIME,job VARCHAR(32),salary DOUBLE,`resume` TEXT) CHARSET utf8 COLLATE utf8_bin ENGINE INNODB;
-- 添加一条
INSERTINTO `emp`VALUES(100, '小妖怪', '男', '2000-11-11', '2010-11-10 11:11:11', '巡山的', 3000, '大王叫我来巡山');SELECT * FROM `emp`;

1.11 修改表-基本介绍

#修改表的操作练习
--员工表 emp 的上增加一个 image 列，varchar 类型(要求在resume后面)。ALTER TABLE empADD image VARCHAR(32) NOT NULLDEFAULT ''AFTER RESUMEDESC employee --显示表结构，可以查看表的所有列
--修改 job 列，使其长度为60。ALTER TABLE empMODIFY job VARCHAR(60) NOT NULL DEFAULT''
--删除sex列。ALTER TABLE empDROP sex
--表名改为 employee。RENAME TABLE emp TO employee 
--修改表的字符集为utf8ALTER TABLE employee CHARACTER SET utf8
--列名name修改为user_nameALTER TABLE employeeCHANGE `name` `user_name` VARCHAR(64) NOT NULL DEFAULT''DESC employee

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

1.13 Insert语句

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

#练习insert语句
--创建一张商品表goods(id int,goods_name varchar(10),price double);
--添加2条记录
CREATE TABLE `goods`(id INT,goods_name VARCHAR(10), --长度10price DOUBLE NOT NULL DEFAULT100);
--添加数据
INSERT INTO `goods`(id,goods_name,price)VALUES (10, '华为手机',2000);
INSERT INTO `goods`(id,goods_name,price)VALUES (20, '苹果手机',3000);SELECT * FROM goods;CREATE TABLE `goods2`(id INT,goods_name VARCHAR (10), --长度10price DOUBLE NOT NULL DEFAULT 100);

1.14 update语句

1.14.1 使用update语句修改表中数据

1.14.2 基本使用

案例：:在上面创建的employee表中修改表中的纪录
        1. 将所有员工薪水修改为5000元。
        2. 将姓名为小妖怪的员工薪水修改为3000元。
        3. 将老妖怪的薪水在原有基础上增加1000元。

--演示update语句
--要求: 在上面创建的employee表中修改表中的纪录
--1.将所有员工薪水修改为5000元。[如果没有带where条件，会修改所有的记录，因此要小心]UPDATE employee SET salary = 5000
--2.将姓名为小妖怪的员工薪水修改为3000元。UPDATE employeeSET salary = 3000WHERE user_name = '小妖怪'
--3.将老妖怪的薪水在原有基础上增加1000元INSERT INTO employeeVALUES (200, '老妖怪', '1990-11-11', '2000-11-1110:10:10', '捶背的',5000, '给大王捶背', 'd:\\a.jpg');UPDATE employeeSET salary = salary + 1000WHERE user_name = '老妖怪'
-- 可以修改多个列的值UPDATE employeeSET salary = salary + 1000 , job = '出主意的'WHERE user_name = '老妖怪'SELECT * FROM employee;

1.14.3 使用细节:

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

1.15 delete 语句

1.15.1 使用 delete 语句删除表中数据

delete from tbl_name[WHERE where_definition]

案例： 删除表中名称为’老妖怪’的记录。删除表中所有记录。

-- delete 语句演示
-- 删除表中名称为’老妖怪’的记录。DELETE FROM employeeWHERE user_name = '老妖怪';
-- 删除表中所有记录, 提醒，一定要小心DELETE FROM employee;
-- Delete 语句不能删除某一列的值（可使用update 设为 null 或者 "）UPDATE employee SET job = ' WHERE user_name = '老妖怪';SELECT * FROM employee
-- 要删除这个表DROP TABLE employee;

1.15.2 使用细节

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

1.16 select 语句

1.16.1 基本语法

SELECT [DISTINCT] * | {column1, column2. column3. .}FROM table name;

1.16.2 注意事项

1. Select指定查询哪些列的数据。
2. column指定列名。
3. *号代表查询所有列。
4. From指定查询哪张表。
5. DISTINCT 可选,指显示结果时，是否去掉重复数据
案例练习：
        1. 查询表中所有学生的信息。
        2. 查询表中所有学生的姓名和对应的英语成绩。
        3. 过滤表中重复数据distinct 。
        4. 要查询的记录,每个字段都相同,才会去重

--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,'zak',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;

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

SELECT * | { column1 | expression, column2 | expression，..}FROM tablename;

1.16.4 在select语句中可使用as语句

SELECT column_name as 别名 from 表名;

案例： 1. 统计每个学生的总分 2. 在所有学生总分加10分的情况 3. 使用别名表示学生分数。

-- 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_scoreFROM student;

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

1.16.6 使用where子句，进行过滤查询

案例：

-- select语句
-- 查询姓名为赵云的学生成绩SELECT * FROM studentWHERE `name` = '赵云'--查询英语成绩大于90分的同学SELECT * FROM studentWHERE english > 90-- 查询总分大于200分的所有同学SELECT  *FROM studentWHERE(chinese + english + math) > 200-- 查询math大于60 并且(and)id大于4的学生成绩SELECT * FROM studentWHERE math > 60 AND id > 4-- 查询英语成绩大于语文成绩的同学SELECT * FROM studentWHERE english > chinese-- 查询总分大于200分 并且 数学成绩小于语文成绩,的姓赵的学生.
-- 赵% 表示 名字以韩开头的就可以SELECT * FROM studentWHERE(chinese + english + math) > 200 ANDmath < chinese AND `name` LIKE '赵%'-- 查询英语分数在 80－90之间的同学。SELECT * FROM studentWHERE english >= 80 AND english <= 90;SELECT * FROM studentWHERE english BETWEEN 80 AND 90;
-- between .. and .. 是 闭区间-- 查询数学分数为 89,90,91的同学。SELECT * FROM studentWHERE math = 89 OR math = 90 OR math = 91;SELECT  *FROM studentWHERE math IN (89, 90, 91);-- 查询所有姓李的学生成绩。SELECT * FROM studentWHERE `name` LIKE '李%'

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

SELECT column1 , column2 . column3. .FROM table;order by column asc | desc, ...

1. Order by 指定排序的列，排序的列既可以是表中的列名，也可以是select语句后指定的列名。
2. Asc升序[默认]、Desc降序
3. ORDER BY子句应位于SELECT语句的结尾。

-- 演示order by 使用
-- 对数学成绩排序后输出【升序】。SELECT * FROM studentORDER BY math;-- 对总分按从高到低的顺序输出 [降序]
-- 使用别名排序SELECT `name` , (chinese + english + math) AS total_score FROM studentORDER BY total_score DESC;-- 对姓李的学生成绩[总分]排序输出(升序) where+orderbySELECT `name`, (chinese + english + math)AS total_score FROM studentWHERE `name` LIKE '李%'ORDER BY total_score;

1.17 合计/统计函数

1.17.1 count

-- Count返回行的总数Select count(*) | count(列名) from table_name[WHERE where_definition]

-- 演示mysql 的统计函数的使用
-- 统计一个班级共有多少学生？SELECT COUNT(*) FROM student;-- 统计数学成绩大于90的学生有多少个？SELECT COUNT(*) FROM studentWHERE math>90-- 统计总分大于250的人数有多少？SELE CTCOUNT(*) FROM studentWHERE (math + english + chinese) > 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; -- 4SELECT COUNT(`name`) FROM t15; -- 3-- 演示sum函数的使用
-- 统计一个班级数学总成绩？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(chinese)/COUNT(*) FROM student;SELECT SUM(`name`)FROM student;--演示avg的使用
--练习：
--求一个班级数学平均分？SELECT AVG (math)FROM student;--求一个班级总分平均分SELECT AVG(math + english + chinese) FROM student;--演示max和min的使用--求班级最高分和最低分（数值范围在统计中特别有用）SELECT MAX(math + english + chinese), MIN(math + english + chinese)FROM student;--求出班级数学最高分和最低分SELECT MAX(math) AS math_high_socre, MIN(math) AS math_low_socreFROM student;

1.17.2 sum

--Sum函数返回满足where条件的行的和 -一般使用在数值列select sum(列名) { , sum(列名)...} from tablename[WHERE where_definition]

--演示sum函数的使用
--统计一个班级数学总成绩？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(chinese)/COUNT(*) FROM student;SELECT SUM(`name`) FROM student;

1.17.3 avg

-- AVG函数返回满足where条件的一列的平均值Select avg(列名) {,avg(列名)...} from tablename[WHERE where_definition]

-- 演示avg的使用
-- 练习：
-- 求一个班级数学平均分？SELECT AVG(math) FROM student;-- 求一个班级总分平均分SELECT AVG(math + english + chinese) FROM student;

1.17.4 max/min

-- Max/min函数返回满足where条件的一列的最大/最小值Select max（列名)from tablename[WHERE where_definition]

-- 演示max 和 min的使用
-- 求班级最高分和最低分（数值范围在统计中特别有用）SELECT MAX (math + english + chinese), MIN(math + english + chineseFROMstudent;--求出班级数学最高分和最低分SELECT MAX(math) AS math_high_socre,MIN(math) AS math_low_socreFROM student;

1.17.5 使用groupby子句对列进行分组[先创建测试表]

SELECT column1, column2. column3.. FROM tablegroup by column

1.17.6 使用having子句对分组后的结果进行过滤

SELECT column1 , column2 . column3 ..FROM tablegroup by column having ...

group by 用于对查询的结果分组统计,
having 子句用于限制分组显示结果.
?如何显示每个部门的平均工资和最高工资
?显示每个部门的每种岗位的平均工资和最低工资
?显示平均工资低于2000的部门号和它的平均工资 //别名

CREATE TABLE dept(/*部门表*/deptno MEDIUMINT UNSIGNED NOTNULL DEFAULT0,dname VARCHAR(20) NOTNULL DEFAULT "",loc VARCHAR(13) NOT NULL DEFAULT "");INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEWYORK'),(20, 'RESEARCH', 'DALLAS'),(30, 'SALES', 'CHICAGO'),(40, 'OPERATIONS', 'BOSTON');SELECT * FROM dept;-- 员工表CREATE TABLE emp(empno MEDIUMINT UNSIGNED NOTNULL DEFAULT 0,/*编号*/ename VARCHAR(20) NOTNULL DEFAULT "", /*名字*/job VARCHAR(9) NOTNULL DEFAULT "",/*工作*/mgr MEDIUMINT UNSIGNED,/*上级编号*/hiredate DATE NOT NULL, /*入职时间*/sal DECIMAL(7,2) NOTNULL, /*薪水*/comm DECIMAL(7,2) ,/*红利 奖金*/deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0/*部门编号*/);-- 添加测试数据INSERTINTO empVALUES(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, 'JONES', '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, 'SCOTT','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;-- 工资级别
#工资级别表
CREATETABLE salgrade(grade MEDIUMINT UNSIGNED NOTNULL DEFAULT 0,/*工资级别*/losal DECIMAL(17,2) NOTNULL, /* 该级别的最低工资 */hisal DECIMAL(17,2) NOTNULL /* 该级别的最高工资*/);INSERTINTO salgrade VALUES (1,700,1200);INSERTINTO salgrade VALUES (2,1201,1400);INSERTINTO salgrade VALUES (3,1401,2000);INSERTINTO salgrade VALUES (4,2001,3000);INSERTINTO salgrade VALUES (5,3001,9999);SELECT * FROM salgrade;SELECT * FROM dept;SELECT * FROM emp;--演示groupby+having-- GROUPby用于对查询的结果分组统计,
-- having 子句用于限制分组显示结果.
-- ? 如何显示每个部门的平均工资和最高工资
-- 分析:avg(sal) max(sal)
-- 按照部分来分组查询SELECTAVG(sal), MAX(sal) , deptnoFROM emp GROUP BY deptno;
-- 使用数学方法，对小数点进行处理SELECT FORMAT(AVG(sal),2), MAX(sal) , deptnoFROM emp GROUP BY deptno;-- ?显示每个部门的每种岗位的平均工资和最低工资
-- 分析 1. 显示每个部门的平均工资和最低工资
--     2. 显示每个部门的每种岗位的平均工资和最低工资SELECT AVG(sal), MIN(sal) , deptno, jobFROM emp GROUP BY deptno,job;-- ?显示平均工资低于2000的部门号和它的平均工资 // 别名
-- 分析 [写sql语句的思路是化繁为简,各个击破]
-- 1. 显示各个部门的平均工资和部门号
-- 2. 在1的结果基础上，进行过滤，保留 AVG(sal)<2000
-- 3. 使用别名进行过SELECT AVG (sal), deptnoFROM emp GROUP BY deptnoHAVING AVG (sal) < 2000;
-- 使用别名SELECT AVG (sal) AS avg_sal, deptnoFROM emp GROUP BY deptnoHAVING avg_sal < 2000;

1.18 字符串相关函数

-- 演示字符串相关函数的使用 ， 使用emp表来演示
-- CHARSET(str) 返回字串字符集SELECT CHARSET (ename) FROM emp;
-- CONCAT(string2 [,... ]) 连接字串, 将多个列拼接成一列SELECT CONCAT(ename, ' 工作是 ', job) FROM emp;-- INSTR (string ,substring ) 返回 substring 在 string 中出现的位置,没有返回 0
-- dual 亚元表, 系统表 可以作为测试表使用SELECT INSTR('zak', 'ke') FROM DUAL;-- UCASE (string2 ) 转换成大写SELECT UCASE(ename) FROM emp;-- LCASE (string2 ) 转换成小写SELECT LCASE(ename) FROM emp;
-- LEFT (string2 ,length )从 string2 中的左边起取 length 个字符
-- RIGHT (string2 ,length ) 从 string2 中的右边起取 length 个字符SELECT LEFT(ename, 2) FROM emp;-- LENGTH (string )string 长度[按照字节]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('zak', 'zak') FROM DUAL;
-- SUBSTRING (str , position [,length ])
-- 从str 的position 开始【从1开始计算】,取length个字符
-- 从ename 列的第一个位置开始取出2个字符SELECT SUBSTRING(ename, 1, 2) FROM emp;-- LTRIM (string2 ) RTRIM (string2 ) TRIM(string)
-- 去除前端空格或后端空格SELECTLTRIM('  zak')FROMDUAL;SELECTRTRIM('zak  ')FROMDUAL;SELECTTRIM('  zak  ')FROMDUAL;-- 练习: 以首字母小写的方式显示所有员工emp表的姓名
-- 方法1
-- 思路先取出ename 的第一个字符，转成小写的
-- 把他和后面的字符串进行拼接输出即可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;

1.19 数学相关函数

rand(返回一个随机浮点值v.范围在0到1之间(即,其范围为0<= v <=1.0)。若已指定一个整数参数N, 则它被用作种子值,用来产生重复序列。

--演示数学相关函数
--ABS(num)绝对值SELECT ABS(-10) FROM DUAL;
--BIN(decimal_number)十进制转二进制SELECT BIN(10)FROM DUAL;
--CEILING(number2)向上取整,得到比num2大的最小整数SELECT CEILING (-1.1)FROM DUAL;--CONV(number2,from_base,to_base) 进制转换
--下面的含义是8是十进制的8,转成2进制输出SELECT CONV(8,10,2) FROM DUAL;
--下面的含义是8是16进制的8,转成2进制输出SELECT CONV(16,16,10) FROM DUAL;--FLOOR(number2)向下取整,得到比num2小的最大整数SELECT FLOOR(-1.1) FROM DUAL--FORMAT(number,decimal_places)    保留小数位数(四舍五入)SELECT FORM AT (78.125458,2) FROM DUAL;--HEX (DecimalNumber)转十六进制--LEAST(number,number2 [,..])求最小值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() FROM DUAL;SELECT CURRENT_TIMESTAMP() FROM DUAL;

1.20 时间日期相关函数

在实际开发中，我们也经常使用 int 来保存一个unix时间戳，然后使用from_unixtime()进行转换,，还是非常有实用价值的

--日期时间相关函数--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(30),send_time DATETIME );-- 添加一条记录INSERTINTO mesVALUES(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 mesWHERE DATE_ADD(send_time, INTERVAL 10 MINUTE) >= NOW()SELECT * FROMmesWHERE send_time >= DATE_SUB(NOW(), INTERVAL10 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 出生
-- 先求出活80岁 时, 是什么日期 X
-- 然后在使用 datediff(x, now()); 1986-11-11->datetime
-- INTERVAL80YEAR ： YEAR 可以是 年月日，时分秒
-- '1986-11-11' 可以 date,datetime timestampSELECT DATEDIFF (DATE_ADD('1986-11-11', INTERVAL 80 YEAR), NOW())FROM DUAL;SELECT TIMEDIFF('10:11:11', '06:10:10') FROM DUAL;-- YEAR|Month|DAY| DATE (datetime )SELECT YEAR(NOW()) FROM DUAL;SELECT MONTH(NOW()) FROMDUAL;SELECT DAY(NOW()) FROMDUAL;SELECT MONTH('2013-11-10') FROM DUAL;
-- unix_timestamp() : 返回的是 1970-1-1 到现在的秒数SELECT UNIX_TIMESTAMP() FROM DUAL;
-- FROM_UNIXTIME() : 可以把一个unix_timestamp 秒数[时间戳]，转成指定格式的日期
-- %Y-%m-%d 格式是规定好的，表示年月日
-- 意义：在开发中，可以存放一个整数，然后表示时间，通过FROM_UNIXTIME转换SELECT FROM_UNIXTIME(1618483484, '%Y-%m-%d') FROM DUAL;SELECT FROM_UNIXTIME(1618483100, '%Y-%m-%d%H:%i:%s') FROM DUAL;SELECT * FROM mysql.user\G

1.21 加密和系统函数

--演示加密函数和系统函数--USER()查询用户
--可以查看登录到mysql的有哪些用户，以及登录的IPSELECT USER() FROM DUAL; --用户@IP地址
--DATABASE()查询当前使用数据库名称SELECT DATA BASE();--MD5(str)为字符串算出一个MD532的字符串，常用(用户密码)加密
--root密码是hsp->加密md5->在数据库中存放的是加密后的密码SELECT MD5('zak')FROM DUAL;SELECT LENGTH (MD5('zak')) FROM DUAL;-- 演示用户表，存放密码时，是md5CREATE TABLE zak_user(id INT ,`name` VARCHAR(32) NOT NULL DEFAULT ",pwd CHAR(32) NOTNULL DEFAULT ");INSERT INTO zak_userVALUES(100, '知昂可', MD5('zak'));SELECT * FROM zak_user;  -- csdnSELECT * FROM zak_user  -- SQL注入问题WHERE`name`='知昂可' AND pwd = MD5('zak')-- PASSWORD(str) -- 加密函数, MySQL 数据库的用户密码就是 PASSWORD函数加密SELECT PASSWORD('zak') FROM DUAL; -- select * from mysql.user \G    从原文密码str 计算并返回密码字符串
-- 通常用于对mysql数据库的用户密码加密
-- mysql.user 表示 数据库.表SELECT * FROM mysql.user

1.22 流程控制函数

#演示流程控制语句#IF(expr1,expr2,expr3)如果expr1为True,则返回expr2否则返回expr3SELECT IF (TRUE, '北京', '上海') FROM DUAL;
#IFNULL(expr1,expr2)如果expr1不为空NULL,则返回expr1,否则返回expr2SELECT IFNULL( NULL, '知昂可')FROMDUAL;
# SELE CTCASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END; [类似多重分支.]
# 如果expr1 为TRUE,则返回expr2,如果expr2 为t, 返回 expr4, 否则返回 expr5SELECT CASEWHEN TRUE THEN'jack' -- jackWHEN FALSE THEN'tom'ELSE 'mary' END-- 1. 查询emp 表, 如果 comm 是null, 则显示0.0
-- 说明，判断是否为null 要使用 is null, 判断不为空 使用 is notSELECT 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 CASEWHEN job='CLERK'THEN'职员'WHEN job='MANAGER'THEN'经理'WHEN job='SALESMAN'THEN'销售人员'ELSE job END)AS 'job'FROM emp;SELECT * FROM emp;SELECT * FROM dept;SELECT * FROM salgrade;

1.23 mysql表查询--加强

1.23.1 介绍

        在前面我们讲过mysql表的基本查询，但是都是对一张表进行的查询，这在实际的软件开发中，还远远的不够。下面我们讲解的过程中，将使用前面创建三张表(emp,dept,salgrade)为大家演示如何进行多表查询
使用where子句
        ?如何查找1992.1.1后入职的员工
如何使用like操作符
        %:表示O到多个字符:表示单个字符
        ?如何显示首字符为S的员工姓名和工资
        ?如何显示第三个字符为大写O的所有员工的姓名和工资
如何显示没有上级的雇员的情况
使用order by子句
        ?如何按照工资的从低到高的顺序，显示雇员的信息
        ?按照部门号升序而雇员的工资降序排列，显示雇员信息

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

1.23.2 分页查询

--分页查询-
-按雇员的id号升序取出，每页显示3条记录，请分别显示第1页，第2页，第3页--第1页SELECT * FROM empORDER BY empnoLIMIT 0,3;
--第2页SELECT * FROM empORDER BY empnoLIMIT 3,3;
--第3页SELECT * FROM empORDER BY empnoLIMIT 6,3;
--推导一个公式SELECT * FROM empORDER BY empnoLIMIT  每页显示记录数 * (第几页-1),每页显示记录数
--测试SELECT job,COUNT(*)FROM emp GROUP BY job;
--显示雇员总数，以及获得补助的雇员数SELECT COUNT(*)FROM emp WHERE mgr IS NOT NULL;SELECT MAX(sal)-MIN(sal)FROM emp;

1.23.3 使用分组函数和分组子句groupby

--增强groupby的使用--(1)显示每种岗位的雇员总数、平均工资。SELECT COUNT(*),AVG(sal),jobFROM empGROUP BY job;
--(2)显示雇员总数，以及获得补助的雇员数。
--思路: 获得补助的雇员数 就是 comm 列为非null, 就是count(列)，如果该列的值为null, 是不会统计 ,SQL 非常灵活，需要我们动脑筋.SELECT COUNT(*), COUNT(comm)FROM emp-- 老师的扩展要求：统计没有获得补助的雇员数SELECT COUNT(*), COUNT(IF(comm IS NULL, 1, NULL))FROM empSELECT COUNT(*), COUNT(*)- COUNT(comm)FROM emp
-- (3) 显示管理者的总人数。小技巧:尝试写->修改->尝试[正确的]SELECT COUNT (DISTINCT mgr)FROM emp;-- (4) 显示雇员工资的最大差额。
-- 思路： max(sal)-min(sal)SELECT MAX(sal)- MIN(sal)FROM emp;SELECT * FROM emp;select * from dept;-- 应用案例：请统计各个部门groupby 的平均工资 avg，
-- 并且是大于1000的 having，并且按照平均工资从高到低排序， orderby
-- 取出前两行记录 limit0,2SELECT deptno,AVG(sal) AS avg_salFROM empGROUP BY deptnoHAVING avg_sal > 1000ORDERBY avg_sal DESCLIMIT 0,2

1.23.4 数据分组的总结

SELECT column1 , column2 . column3. . FROM  tablegroup by columnhaving conditionorder by columnlimit start, rows ;

1.24 mysql 多表查询

1.24.1 说明

多表查询是指基于两个和两个以上的表查询.在实际应用中,查询单个表可能不能满足你的需求,需要使用到(dept表和emp表)

1.24.2 多表查询练习

--多表查询
--?显示雇员名,雇员工资及所在部门的名字【笛卡尔集】
/*分析1.雇员名,雇员工资来自emp表2. 部门的名字 来自 dept表3. 需求对 emp 和 dept查询 ename,sal,dname,deptno4. 当我们需要指定显示某个表的列是，需要 表.列表
*/SELECT ename,sal,dname,emp.deptnoFROM emp,deptWHERE emp.deptno = dept.deptnoSELECT * FROM emp;SELECT * FROM dept;SELECT * FROM salgrade;
-- 小技巧：多表查询的条件不能少于 表的个数-1, 否则会出现笛卡尔集
-- ?如何显示部门号为10的部门名、员工名和工SELECT ename,sal,dname, emp.deptnoFROM emp, deptWHERE emp.deptno = dept.deptno AND emp.deptno = 10-- ?显示各个员工的姓名，工资，及其工资的级别-- 思路 姓名，工资 来自 emp13
-- 工资级别 salgrade5
-- 写sql , 先写一个简单，然后加入过滤条件...select ename, sal, gradefrom emp , salgradewhere sal between losal and hisal;

1.24.3 自连接

自连接是指在同一张表的连接查询 [将同一张表看做两张表]。

--多表查询的自连接--思考题:显示公司员工名字和他的上级的名字--分析：员工名字在emp,上级的名字的名字emp
--员工和上级是通过emp表的mgr列关联
--小结：
--自连接的特点1.把同一张表当做两张表使用
--   2.需要给表取别名表名表别名
-- 3.列名不明确，可以指定列的别名列名as列的别名SELECT worker.ename AS '职员名' , boss.enameAS'上级名'FROM emp worker,emp bossWHERE worker.mgr = boss.empno;SELECT * FROM emp;

1.25 mysql表子查询

1.25.1 什么是子查询

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

1.25.2 单行子查询

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

1.25.3 多行子查询

--子查询的演示
--请思考：如何显示与SMITH同一部门的所有员工?/*1.先查询到SMITH的部门号得到2.把上面的select语句当做一个子查询来使用
*/SELECT deptnoFROM empWHERE ename ='SMITH'--下面的答案.SELECT *FROM empWHERE deptno =(SELECT deptnoFROM empWHERE ename ='SMITH')
--课堂练习:如何查询和部门10的工作相同的雇员的
--名字、岗位、工资、部门号,但是不含10号部门自己的雇员./*1.查询到10号部门有哪些工作2.把上面查询的结果当做子查询使用
*/select distinct jobfrom empwhere deptno =10;--下面语句完整select ename,job,sal,deptnofrom empwhere job in(SELECT DISTINCT jobFROM empWHERE deptno = 10) and deptno <> 10

1.25.4 在多行子查询中使用all操作符

--all和any的使用--请思考:显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号SELECT ename,sal,deptnoFROM empWHERE sal > ALL(SELECT salFROM empWHERE deptno = 30)-- 可以这样写SELECT ename, sal, deptnoFROM empWHERE sal > (SELECT MAX(sal)FROM empWHERE deptno = 30)-- 请思考:如何显示工资比部门30的其中一个员工的工资高的员工的姓名、工资和部门号SELECT ename, sal, deptnoFROM empWHERE sal > any(SELECT salFROM empWHERE deptno = 30)SELECT ename, sal, deptnoFROM empWHERE sal > (SELECT min(sal)FROM empWHERE deptno = 30)-- 查询ecshop 中各个类别中，价格最高的商品-- 查询 商品表
-- 先得到 各个类别中，价格最高的商品 max + groupbycat_id, 当做临时表
-- 把子查询当做一张临时表可以解决很多很多复杂的查询select cat_id , max(shop_price)from ecs_goodsgroup by cat_id-- 这个最后答案select goods_id, ecs_goods.cat_id, goods_name, shop_pricefrom (SELECT cat_id , MAX(shop_price) as max_priceFROM ecs_goodsGROUP BY cat_id) temp , ecs_goodswhere temp.cat_id = ecs_goods.cat_idand temp.max_price = ecs_goods.shop_price

1.25.5 多列子查询

--多列子查询--请思考如何查询与allen的部门和岗位完全相同的所有雇员(并且不含allen本人)
--(字段1，字段2...)=(select字段1，字段2from。。。。)--分析:1.得到smith的部门和岗位SELECT deptno,jobFROM empWHERE ename = 'ALLEN'-- 分析:2 把上面的查询当做子查询来使用，并且使用多列子查询的语法进行匹配SELECT *FROM empWHERE(deptno , job) = (SELECT deptno , jobFROM empWHERE ename ='ALLEN') AND ename != 'ALLEN-- 请查询 和宋江数学，英语，语文
-- 成绩 完全相同的学生SELECT *FROM studentWHERE(math, english, chinese) = (SELECT math, english, chineseFROM studentWHERE`name` = '宋江')SELECT * FROM student;

1.25.6 在from子句中使用子查询

--子查询练习--请思考：查找每个部门工资高于本部门平均工资的人的资料
--这里要用到数据查询的小技巧，把一个子查询当作一个临时表使用--1.先得到每个部门的部门号和对应的平均工资SELECT deptno,AVG(sal) AS avg_salFROM emp GROUP BY deptno--2.把上面的结果当做子查询,和emp进行多表查询SELECT ename,sal,temp.avg_sal,emp.deptnoFROM emp,(SELECT deptno,AVG(sal) AS avg_salFROM empGROUP BY deptno) tempWHERE emp.deptno = temp.deptnoAND emp.sal > temp.avg_sal-- 查找每个部门工资最高的人的详细资料SELECT ename, sal, temp.max_sal, emp.deptnoFROM emp,(SELECT deptno, MAX(sal)AS max_salFROM empGROUP BY deptno) tempWHERE emp.deptno = temp.deptno AND emp.sal = temp.max_sal-- 查询每个部门的信息(包括：部门名,编号,地址)和人员数量,我们一起完成。- 1. 部门名,编号,地址 来自 dept表
-- 2. 各个部门的人员数量-》 构建一个临时表SELECT COUNT(*), deptnoFROM empGROUP BY deptno;SELECT dname,dept.deptno,loc,tmp.per_numAS'人数'FROM dept,(SELECT COUNT(*) AS per_num,deptnoFROM empGROUP BY deptno)tmpWHERE tmp.deptno = dept.deptno--还有一种写法表.*表示将该表所有列都显示出来,可以简化sql语句
--在多表查询中，当多个表的列不重复时，才可以直接写列名SELECT tmp.*, dname,locFROM dept,(SELECT COUNT(*) ASper_num,deptnoFROM empGROUP BY deptno) tmpWHERE tmp.deptno = dept.deptno

1.26 表复制

1.26.1 自我复制数据(蠕虫复制)

-- 表的复制
-- 为了对某个sql语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据CREATE TABLE my_tab01( id INT,`name` VARCHAR(32),sal DOUBLE,job VARCHAR(32),deptno INT);DESC my_tab01SELECT * FROM my_tab01;-- 演示如何自我复制
-- 1. 先把emp 表的记录复制到 my_tab01INSERT INTO my_tab01(id, `name`, sal, job,deptno)SELECT empno, ename, sal, job, deptno FROM emp;-- 2. 自我复制INSERT INTO my_tab01SELECT * FROM my_tab01;SELECT COUNT(*) FROM my_tab01;-- 如何删除掉一张表重复记录
-- 1. 先创建一张表 my_tab02,
-- 2. 让 my_tab02 有重复的记CREATE TABLE my_tab02 LIKE emp;  -- 这个语句 把emp表的结构(列)，复制到my_tab02DESCmy_tab02;INSERT INTO my_tab02SELECT * 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_tmpinsert into my_tmpselect distinct * from my_tab02;
-- (3) 清除掉 my_tab02 记录delete from my_tab02;
-- (4) 把 my_tmp 表的记录复制到 my_tab02insert into my_tab02select * from my_tmp;
-- (5) drop 掉 临时表my_tmpdrop table my_tmp;select * from my_tab02;

1.27 合并查询

1.27.1 介绍

-- 合并查询SELECT ename,sal,job FROM emp WHERE sal>2500  -- 5SELECT ename,sal,job FROM emp WHERE job='MANAGER'  -- 3-- union all 就是将两个查询结果合并，不会去重SELECT ename,sal,job FROM emp WHERE sal>2500  -- 5UNION ALLSELECT ename,sal,job FROM emp WHERE job='MANAGER' -- 3-- union 就是将两个查询结果合并，会去重SELECT ename,sal,job FROM emp WHERE sal>2500  -- 5UNIONSELECT ename,sal,job FROM emp WHERE job='MANAGER'  -- 3

1.28 mysql 表外连接

1.28.1 外连接

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

-- 外连接--比如：列出部门名称和这些部门的员工名称和工作，
--同时要求显示出那些没有员工的部门--使用我们学习过的多表查询的SQL，看看效果如何?SELECT dname,ename,jobFROM emp,deptWHERE emp.deptno = dept.deptnoORDER BY dnameSELECT * FROM dept;SELECT * FROM emp;-- 创建 stu/*id name1 Jack2 Tom3 Kity4 nono*/CREATE TABLE stu(id INT,`name` VARCHAR(32));INSERT INTO stu VALUES(1, 'jack'),(2,'tom'),(3, 'kity'),(4, 'nono');SELECT * FROM stu;
-- 创建 exam
/*id  grade1   562   7611   8
*/CREATE TABLE exam(id INT,grade INT);INSERT INTO exam VALUES(1, 56),(2,76),(11, 8);SELECT * FROM exam;-- 使用左连接
-- （显示所有人的成绩，如果没有成绩，也要显示该人的姓名和id号,成绩显示为空）SELECT `name`, stu.id, gradeFROM stu, examWHERE stu.id = exam.id;-- 改成左外连接
SELECT`name`, stu.id, gradeFROM stu LEFTJOIN examON stu.id = exam.id;-- 使用右外连接（显示所有成绩，如果没有名字匹配，显示空)
-- 即：右边的表(exam) 和左表没有匹配的记录，也会把右表的记录显示出来
SELECT`name`, stu.id, gradeFROM stu RIGHT JOIN examON stu.id = exam.id;-- 列出部门名称和这些部门的员工信息(名字和工作)，
-- 同时列出那些没有员工的部门名。5min
-- 使用左外连接实现
SELECT dname, ename, jobFROM dept LEFTJOIN empON dept.deptno = emp.deptno-- 使用右外连接实现
SELECT dname, ename, jobFROM emp RIGHT JOIN deptON dept.deptno = emp.deptno

1.29 mysql 约束

1.29.1 基本介绍

约束用于确保数据库的数据满足特定的商业规则。在mysql中，约束包括: not null、unique,primary key,foreign key,和check五种.

1.29.2 primarykey(主键)-基本使用

字段名字段类型 primary key
用于唯一的标示表行的数据,当定义主键约束后，该列不能重复

--主键使用--id name emailCREATE TABLE t17(id INT PRIMARY KEY,  --表示id列是主键`name` VARCHAR(32),email VARCHAR(32));-- 主键列的值是不可以重复INSERT INTO t17VALUES(1, 'jack', 'jack@sohu.com');INSERT INTO t17VALUES(2, 'tom', 'tom@sohu.com');INSER TINTO t17VALUES(1, 'hsp', 'zak@sohu.com');SELECT * FROM t17-- 主键使用的细节讨论
-- primary key 不能重复而且不能为 null。
INSERT INTO t17VALUES(NULL, 'zak', 'zak@sohu.com');
-- 一张表最多只能有一个主键, 但可以是复合主键(比如 id+name)CREATE TABLE t18(id INT PRIMARYKEY, -- 表示id列是主键`name` VARCHAR(32), PRIMARYKEY  -- 错误的email VARCHAR(32));
-- 演示复合主键 (id 和 name 做成复合主键)CREATE TABLE t18(id INT ,`name` VARCHAR(32),email VARCHAR(32),PRIMARY KEY(id, `name`) -- 这里就是复合主键);INSERT INTO t18VALUES(1, 'tom', 'tom@sohu.com');INSERT INTO t18VALUES(1, 'jack', 'jack@sohu.com');INSERT INTO t18VALUES(1, 'tom', 'xx@sohu.com'); -- 这里就违反了复合主键
SELECT * FROM t18;-- 主键的指定方式 有两种
-- 1. 直接在字段名后指定：字段名 primakry key
-- 2. 在表定义最后写 primary key(列名)
CREATE TABLE t19(id INT ,`name` VARCHAR(32) PRIMARY KEY,email VARCHAR(32));CREATE TABLE t20(id INT ,`name` VARCHAR(32) ,email VARCHAR(32),PRIMARY KEY(`name`)  -- 在表定义最后写 primary key(列名);--使用desc表名，可以看到primarykey的情况DESC t20 --查看t20表的结果，显示约束的情况
DESC t18

1.29.3 not null(非空)

如果在列上定义了not null,那么当插入数据时，必须为列提供数据。
字段名字段类型 not null

1.29.4 unique(唯一)

当定义了唯一约束后，该列值是不能重复的.。
字段名字段类型 unique
unique细节(注意):
1. 如果没有指定not null，则unique字段可以有多个null 2. 一张表可以有多个unique字段

--unique的使用CREATE TABLE t21(id INT UNIQUE, --表示id列是不可以重复的.`name` VARCHAR(32),email VARCHAR(32));INSERT INTO t21VALUES(1, 'jack', 'jack@sohu.com');INSERT INTO t21VALUES(1, 'tom', 'tom@sohu.com');--unqiue使用细节
--1.如果没有指定notnull,则unique字段可以有多个null
--如果一个列(字段)，是unique not null使用效果类似primary keyINSERT INTO t21VALUES (NULL, 'tom', 'tom@sohu.com');SELECT * FROM t21;
--2. 一张表可以有多个unique字段
CREATE TABLE t22(id INT UNIQUE, --表示id列是不可以重复的.`name` VARCHAR(32) UNIQUE, --表示name不可以重复email VARCHAR(32));DESC t22

1.29.5 foreignkey(外键)

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

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

-- 外键演示-- 创建 主表 my_classCREATE TABLE my_class (id INT PRIMARYKEY,  -- 班级编号`name` VARCHAR(32) NOT NULL DEFAULT ');-- 创建 从表 my_stuCREATE TABLE my_stu (id INT PRIMARY KEY, -- 学生编号`name` VARCHAR(32) NOTNULLDEFAULT ',class_id INT ,-- 学生所在班级的编号
-- 下面指定外键关系FOREIGN KEY(class_id) REFERENCES my_class(id)) 
-- 测试数据
INSERT INTO my_classVALUES(100, 'java'), (200, 'web');
INSERT INTO my_classVALUES(300, 'php');SELECT * FROM my_class;
INSERT INTO my_stuVALUES(1, 'tom', 100);
INSERT INTO my_stuVALUES(2, 'jack', 200);
INSERT INTO my_stuVALUES(3, 'zak', 300);
INSERT INTO my_stuVALUES(4, 'mary', 400); -- 这里会失败...因为 400 班级不存在INSERT INTO my_stuVALUES(5, 'king', NULL);  -- 可以, 外键 没有写 not null
SELECT * FROM my_class;-- 一旦建立主外键的关系，数据不能随意删除了
DELETE FROM my_classWHERE id = 100;

1.29.6 check

用于强制行数据必须满足的条件,假定在sal列上定义了check约束,并要求sal列值在1000 ~2000之间如果不再1000~2000之间就会提示出错。
提示: oracle和sql server均支持check ,但是mysql5.7目前还不支持check ,只做语法校验，但不会生效。

在mysql中实现 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> 1000AND sal <2000));-- 添加数据
INSERT INTO t23VALUES(1, 'jack', 'mid', 1);SELECT * FROM t23;

1.29.7 商店售货系统表设计案例

-- 使用约束的练习CREATE DATABASE shop_db-- 现有一个商店的数据库shop_db，记录客户及其购物情况，由下面三个表组成：
-- 商品goods（商品号goods_id，商品名goods_name，单价unitprice，商品类别category，
-- 供应商provider);
-- 客户customer（客户号customer_id,姓名 name,住址address,电邮 email 性别 sex,身份证card_Id);
-- 购买purchase（购买订单号order_id，客户号customer_id,商品号goods_id,购买数量nums);
-- 1 建表，在定义中要求声明 [进行合理设计]：
-- (1)每个表的主外键；
-- (2)客户的姓名不能为空值；
-- (3)电邮不能够重复;
-- (4)客户的性别[男|女]check 枚举..
-- (5)单价 unitprice 在 1.0- 9999.99 之间 check-- 商品goodsCREATE TABLE goods (goods_id INT PRIMARY KEY,goods_name VARCHAR(64) NOT NULL DEFAULT ",unitprice DECIMAL(10,2) NOT NULL DEFAULT 0CHECK(unitprice >= 1.0AND unitprice <= 9999.99),category INT NOT NULL DEFAULT 0,provider VARCHAR(64) NOT NULL DEFAULT ");-- 客户customer（客户号customer_id,姓名 name,住址address,电邮 email 性别 sex,
--身份证card_Id);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, --这里使用的枚举类型,card_Id CHAR(18));--购买purchase（购买订单号order_id，客户号customer_id,商品号goods_id,
--购买数量nums);
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));DESC goods; DESC customer;DESC purchase;

1.30 自增长

在某张表中，存在一个id列(整数),我们希望在添加记录的时候,该列从1开始，自动的增长,怎么处理? 字段名整型 primary key auto_increment

1.30.1 自增长使用细节

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

--演示自增长的使用--创建表
CREATE TABLE t24(id INT PRIMARY KEY AUTO_INCREMENT,email VARCHAR(32) NOT NULL DEFAULT ",`name` VARCHAR(32) NOT NULL DEFAULT ");DESC t24
--测试自增长的使用
INSERT INTO t24VALUES(NULL, 'tom@qq.com', 'tom');INSERT INTO t24(email, `name`) VALUES ('zak@sohu.com', 'zak');SELECT * FROM t24;-- 修改默认的自增长开始值
ALTER TABLE t25 AUTO_INCREMENT = 100
CREATE TABLE t25(id INT PRIMARY KEY AUTO_INCREMENT,email VARCHAR(32) NOT NULLDEFAULT ",`name` VARCHAR(32) NOT NULL DEFAULT ");
INSERT INTO t25VALUES(NULL, 'mary@qq.com', 'mary');
INSERT INTO t25VALUES(666, 'zak@qq.com', 'zak');
SELECT * FROM t25;CREATE DATABASE tmp;CREATE TABLE dept( /*部门表*/deptno MEDIUMINT UNSIGNED NOTNULL DEFAULT0,dname VARCHAR(20) NOTNULL DEFAULT "",loc VARCHAR(13) NOT NULL DEFAULT "") ;#创建表EMP雇员
CREATE TABLE emp(empno MEDIUMINT UNSIGNED NOTNULL DEFAULT0, /*编号*/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);

1.31 mysql索引

1.31.1 索引快速入门

说起提高数据库性能，索引是最物美价廉的东西了。不用加内存，不用改程序,不用调sql，查询速度就可能提高百倍干倍。

        是不是建立一个索引就能解决所有的问题?
        ename上没有建立索引会怎样?
        select * from emp where ename = 'axJxC' ;

--创建测试数据库 tmpCREATE DATABASE tmp;CREATE TABLE dept( /*部门表*/deptno MEDIUMINT UNSIGNED NOTNULL DEFAULT 0,dname VARCHAR(20) NOT NULL DEFAULT "",loc VARCHAR(13) NOT NULL DEFAULT "");#创建表EMP雇员
CREATE TABLE emp(empno MEDIUMINT UNSIGNED NOTNULL DEFAULT0,/*编号*/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 <nDO# 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)BEGINDECLARE i INT DEFAULT 0;SET i = FLOOR(10+RAND()*500);RETURN i;END $$#创建一个存储过程， 可以添加雇员
CREATE PROCEDURE insert_emp(IN STARTINT(10),IN max_num INT(10))BEGINDECLARE i INT DEFAULT 0;#set autocommit =0 把 autocommit 设置成 0#autocommit = 0 含义: 不要自动提交
SET autocommit = 0; #默认不提交sql 语句
REPEATSET i =i +1;#通过前面写的函数随机产生字符串和部门编号，然后加入到emp表
INSERT INTO emp VALUES ((START+i) ,rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());UNTIL i = max_numEND REPEAT;#commit 整体提交所有sql语句，提高效率
COMMIT;END $$#添加8000000 数据
CALL insert_emp(100001,8000000)$$#命令结束符，再重新设置为;DELIMITER ;SELECT COUNT(*) FROM emp;-- 在没有创建索引时，我们的查询一条记录SELECT *FROM empWHERE 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 empWHERE empno = 1234578  -- 0.003s 原来是4.5s-- 创建索引后，只对创建了索引的列有效
SELECT *FROM empWHERE ename ='PjDlwy' -- 没有在ename创建索引时，时间4.7sCREATE INDEX ename_index ON emp (ename)  -- 在 ename 上创建索引

1.31.2 索引的类型

1. 主键索引，主键自动的为主索引(类型Primary key)
2. 唯一索引(UNIQUE)
3. 普通索引(INDEX)
4. 全文索引(FULLTEXT)[适用于MyISAM] 一般开发,不使用mysql自带的全文索引，而是使用:全文搜索Solr和 ElasticSearch (ES)

create table t1 (
id int primary key,--主键,同时也是索引，称为主键索引.name varchar(32));
create table t2(
id int unique, -- id是唯一的，同时也是索引，称为unique索引.

1.31.3 索引使用

--演示mysql的索引的使用--创建索引
CREATE TABLE t25(id INT,`name` VARCHAR(32));--查询表是否有索引
SHOW INDEXES FROM t25;--添加索引
--添加唯一索引
CREATE UNIQUE INDEXid_index ON t25(id);--添加普通索引方式1CREATE INDEX id_index ON t25(id);
--如何选择
-- 1. 如果某列的值，是不会重复的，则优先考虑使用unique索引, 否则使用普通索引
-- 添加普通索引方式2ALTER TABLE t25 ADD INDEXid_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

1.31.4 小结: 哪些列上适合使用索引

1.32 mysql 事务

1.32.1 什么是事务

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

1.32.2 事务和锁

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

--事务的一个重要的概念和具体操作
--看一个图[看示意图]
--演示
--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');-- 回退到 bROLLBACK TO b
-- 继续回退 aROLLBACK TO a
-- 如果这样, 表示直接回退到事务开始的状态.ROLLBACKCOMMIT

1.32.3 回退事务

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

1.32.4 提交事务

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

1.32.5 事务细节讨论

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

-- 讨论 事务细节--1.如果不开始事务，默认情况下，dml操作是自动提交的，不能回滚
INSERT INTO t27 VALUES(300, 'milan');  --自动提交commitSELECT * FROM t27--2.如果开始一个事务，你没有创建保存点.你可以执行rollback，
--默认就是回退到你事务开始的状态START TRANSACTIONINSERT 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;

1.33 mysql事务隔离级别

1.33.1 事务隔离级别介绍

1. 多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作,以保证各个连接在获取数据时的准确性。(通俗解释)
2. 如果不考虑隔离性,可能会引发如下问题: 脏读不可重复读幻读

1.33.2 查看事务隔离级别

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

1.33.3 事务隔离级别

1.33.4 设置事务隔离级别

1. 查看当前会话隔离级别 select @ @ tx isolation;
2. 查看系统当前隔离级别 select @ @global.tx_isolation;
3. 设置当前会话隔离级别
set session transaction isolation level repeatable read;
4. 设置系统当前隔离级别
set global transaction isolation level repeatable read;
5. mysql 默认的事务隔离级别是repeatable read，一般情况下，没有特殊要求,没有必要修改(因为该级别可以满足绝大部分项目需求)

-- 演示mysql 的事务隔离级别-- 1. 开了两个mysql的控制台
-- 2. 查看当前mysql的隔离级别
SELECT @@tx_isolation;-- mysql> SELECT @@tx_isolation;
-- +-----------------+
-- | @@tx_isolation |
-- +-----------------+
-- | REPEATABLE-READ |
-- +------------------- 3.把其中一个控制台的隔离级别设置 Read uncommitted
SET SESSION TRANSACTION ISOLATION LEVELREAD UNCOMMITTED-- 4. 创建表
CREATE TABLE `account`(id INT,`name` VARCHAR(32),money INT);-- 查看当前会话隔离级别
SELECT @@tx_isolation
-- 查看系统当前隔离级别
SELECT @@ global.tx_isolation
-- 设置当前会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVELREADUNCOMMITTED
-- 设置系统当前隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL[你设置的级别]

1.34 mysql 事务 ACID

1.34.1 事务的acid 特性

1. 原子性(Atomicity)
        原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
2. 一致性(Consistency)
        事务必须使数据库从一个一致性状态变换到另外一个一致性状态
3. 隔离性(lsolation)
        事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
4. 持久性(Durability)
        持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

1.35 mysql 表类型和存储引擎

1.35.1 基本介绍

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

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

1.35.3 细节说明

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

1.35.4 三种存储引擎表使用案例

--表类型和存储引擎--查看所有的存储引擎
SHOW ENGINES
--innodb存储引擎，是前面使用过.
--1.支持事务    2.支持外键    3.支持行级锁--myisam存储引擎
CREATE TABLE t28(id INT,`name` VARCHAR(32)) ENGINE MYISAM
-- 1.添加速度快    2.不支持外键和事务    3.支持表级锁START TRANSACTION;SAVEPOINT t1INSERT 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 MEMORYDESC t29INSERTINTO t29VALUES(1,'tom'), (2,'jack'), (3, 'zak');SELECT * FROM t29-- 指令修改存储引擎
ALTER TABLE `t29` ENGINE = INNODB

1.35.5 如何选择表的存储引擎

1. 如果你的应用不需要事务，处理的只是基本的CRUD操作，那么MylSAM 是不二选择,速度快
2. 如果需要支持事务，选择lnnoDB。
3. Memory存储引擎就是将数据存储在内存中，由于没有磁盘I./O的等待，速度极快。但由于是内存存储引擎，所做的任何修改在服务器重启后都将消失。(经典用法: 用户的在线状态().)

1.35.6 修改存储引擎

ALTER TABLE '表名' ENGINE = 储存引擎;

1.36 视图(view)

1.36.1 基本概念

1. 视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含列,其数据来自对应的真实表(基表)
2. 视图和基表关系的示意图

1.36.2 视图的基本使用

1. create view视图名as select语句
2. alter view视图名as select语句 --更新成新的视图
3. SHoW CREATE VIEW 视图名
4. drop view视图名1,视图名2

--视图的使用
--创建一个视图emp_view01，只能查询emp表的(empno、ename,job和deptno)信息--创建视图
CREATE VIEW emp_view01ASSELECTempno, ename, job, deptno FROM emp;-- 查看视图
DESC emp_view01SELECT * 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_view01SET job = 'MANAGER'WHERE empno = 7369SELECT * FROM emp; -- 查询基表SELECT  *FROM emp_view01
--修改基本表，会影响到视图UPDATE empSET job ='SALESMAN'WHERE empno = 7369--3.视图中可以再使用视图,比如从emp_view01视图中，选出empno,和ename做出新视图
DESC emp_view01CREATE VIEW emp_view02ASSELECT empno,ename FROM emp_view01SELECT * FROM emp_view02

1.36.3 视图细节讨论

1. 创建视图后，到数据库去看，对应视图只有一个视图结构文件(形式:视图名.frm)
2. 视图的数据变化会影响到基表，基表的数据变化也会影响到视图[insert update delete ]
---准针对前面的雇员管理系统-----
mysql> create view myview as select empno ,ename , job, comm from emp;
mysql> select * from myview;
mysql> update myview set comm=200 where empno=7369;//修改视图,对基表都有变化
mysql> update emp set comm=100 where empno=7369;//修改基表，对视频也有变化
3. 视图中可以再使用视图，数据仍然来自基表..

1.36.4 视图最佳实践

1. 安全。一些数据表有着重要的信息。有些字段是保密的，不能让用户直接看到。这时就可以创建一个视图，在这张视图中只保留一部分字段。这样，用户就可以查询自己需要的字段,不能查看保密的字段。
2. 性能。关系数据库的数据常常会分表存储，使用外键建立这些表的之间关系。这时,数据库查询通常会用到连接(JOIN)。这样做不但麻烦，效率相对也比较低。如果建立一个视图，将相关的表和字段组合在一起，就可以避免使用JOIN查询数据。
3. 灵活。如果系统中有一张旧的表，这张表由于设计的问题，即将被废弃。然而，很多应用都是基于这张表，不易修改。这时就可以建立一张视图，视图中的数据直接映射到新建的表。这样，就可以少做很多改动，也达到了升级数据表的目的。

-- 视图的课堂练习
-- 针对 emp ，dept, 和 salgrade 张三表.创建一个视图 emp_view03，
-- 可以显示雇员编号，雇员名，雇员部门名称和 薪水级别[即使用三张表，构建一个视图]/*分析: 使用三表联合查询，得到结果将得到的结果，构建成视图*/
CREATE VIEW emp_view03ASSELECT empno, ename, dname, gradeFROM emp,dept, salgradeWHERE emp.deptno = dept.deptno AND(sal BETWEEN losalAND hisal)DESC emp_view03SELECT * FROM emp_view03

1.37 Mysql 管理

1.37.1 Mysql 用户

mysql 中的用户，都存储在系统数据库mysql中user表中

其中user表的重要字段说明:
1. host: 允许登录的“位置”，localhost表示该用户只允许本机登录，也可以指定ip地址，
比如:192.168.1.100
2. user: 用户名;
3. authentication_string: 密码，是通过mysql的password()函数加密之后的密码。

1.37.2 创建用户

create user ‘用户名’ @’ 允许登录位置’ identified by ‘密码’
说明: 创建用户，同时指定密码

1.37.3 删除用户

drop user ‘用户名’ @ ’允许登录位置’;

1.37.4 用户修改密码

修改自己的密码:
set password = password(密码');
修改他人的密码(需要有修改用户密码权限):
set password for '用户名'@'登录位置' = password('密码');

1.37.5 mysql 中的权限

1.37.6 给用户授权

1.37.7 回收用户授权

基本语法:
revoke 权限列表 on 库.对象名 from '用户名" @ "登录位置';

1.37.8 权限生效指令

如果权限没有生效，可以执行下面命令.
基本语法: FLUSH PRIVILEGES;

1.37.9 练习

-- 演示 用户权限的管理-- 创建用户 zak 密码 123, 从本地登录
CREATE USER 'zak' @ 'localhost' IDENTIFIED BY '123'-- 使用root 用户创建 testdb ,表 newsCREATE DATABASE testdbCREATE TABLE news(id INT ,content VARCHAR(32));-- 添加一条测试数据
INSERT INTO news VALUES(100, '北京新闻');SELECT * FROM news;-- 给 zak 分配查看 news 表和 添加news的权限
GRANT SELECT,INSERTON testdb.newsTO 'zak' @ 'localhost'-- 可以增加update权限
GRANT UPDATEON testdb.newsTO 'zak' @ 'localhost'-- 修改 zak 的密码为 abc
SET PASSWORD FOR 'zak' @ 'localhost' = PASSWORD('abc');--回收 zak 用户在testdb.news表的所有权限
REVOKE SELECT,UPDATE,INSERT ON testdb.news FROM 'zak' @ 'localhost'REVOKE ALL ON testdb.news FROM 'zak' @ 'localhost'--删除 zak DROP USER 'zak' @ 'localhost'

1.37.10 细节说明

1. 在创建用户的时候，如果不指定Host,则为%，%表示表示所有IP都有连接权限create user xXx;
2. 你也可以这样指定
create user 'xxx' @ '192.168.1.%’ 表示Xxx用户在192.168.1.* 的 ip 可以登录mysql
3. 在删除用户的时候，如果 host 不是%,需要明确指定‘用户 '@' host 值'

--说明用户管理的细节
--在创建用户的时候，如果不指定Host,则为%,%表示表示所有IP都有连接权限
--create user xxx;CREATE USER jackSELECT `host` , `user` FROM mysql.user--你也可以这样指定
--create user 'xxx'@'192.168.1.%'  表示xxx用户在192.168.1.* 的ip可以登录mysqlCREATE USER 'smith' @ '192.168.1.%'
-- 在删除用户的时候，如果 host 不是 %, 需要明确指定 '用户'@'host值'DROP USER jack
-- 默认就是 DROP USER 'jack' @ '%'DROP USER 'smith' @ '192.168.1.%'