mysql数据库
数据库sql语句
SQL语句分类
DDL:数据定义语句[create表，库...]
DML:数据操作语句[增加insert,修改update，删除delete]DQL:数据查询语句[select ]
DCL:数据控制语句[管理数据库:比如用户权限grant revoke ]
创建数据库
CREATE DATABASE [IF NOT EXISTS] db_name
[create_specification [, create_specification] ...]
create_specification:
[DEFAULT] CHARACTER SET charset_name[DEFAULT] COLLATE collation_name
1.CHARACTER SET:指定数据库采用的字符集，如果不指定字符集，默认utf8
2.COLLATE: 指定数据库字符集的校对规则(常用的utf8 bin[区分大小引]、
utf8_general_ci[不区分大小写引]注意默认是utf8_general_ci)[举例说明database.sql文件]
#使用指令创建数据库
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'
查看，删除数据库
显示数据库语句:
SHOW DATABASES
显示数据库创建语句:
SHOW CREATE DATABASE db_ name
数据库删除语句[一定要慎用]:
DROP DATABASE [IF EXISTS]db_ name
查看表结构
desc 表名
#查看当前数据库服务器中的所有数据库
SHOW DATABASES
#查看前面创建的 hsp_db01 数据库的定义信息
SHOW CREATE DATABASE `hsp_db01`
#老师说明 在创建数据库,表的时候，为了规避关键字，可以使用反引号解决
#删除前面创建的 hsp_db01 数据库
DROP DATABASE hsp_db01
备份恢复数据库
在dos命令行运行
备份数据库(注意:在DOS执行)命令行
mysqldump -u用户名-p-B数据库1数据库2数据库n >文件名.sql
恢复数据库(注意:进入Mysql命令行再执行)
Source文件名.sql
#练习 : database03.sql 备份 hsp_db02 和 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 的内容放到查询编辑器中，执行
备份恢复数据库的表
备份库的表
mysqldump -u 用户名-p密码 数据库 表1表2表n > d:\\文件名.sql
创建表
CREATE TABLE table_name
(field1 datatype,
field2 datatype，
field3 datatype
)character set字符集collate校对规则engine存储引擎
field:指定列名datatype:指定列类型(字段类型)
character set :如不指定则为所在数据库字符集
collate:如不指定则为所在数据库校对规则
engine:引擎(这个涉及内容较多，后面单独讲解)
#指令创建表
#注意：hsp_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;
mysql常用的数据类型
#演示时间相关的类型
#创建一张表, 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 列会自动的以当前时间进行更新
创建表练习
#创建表的课堂练习
-- 字段属性
-- Id 整形
-- name字符型
-- sex 字符型
-- brithday 日期型（date）
-- entry_date 日期型 (date)
-- job 字符型
-- Salary 小数型
-- resume 文本型
-- 自己一定要练习一把
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;
-- 添加一条
INSERT INTO `emp`
VALUES(100, '小妖怪', '男', '2000-11-11',
'2010-11-10 11:11:11', '巡山的', 3000, '大王叫我来巡山');
SELECT * FROM `emp`;
修改表
#修改表的操作练习
-- 员工表 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
数据库crud语句
insert语句
INSERT INTO table_name [ (column [, column.. .])]
VALUES
(value [ , value. . .]);
#练习 insert 语句
-- 创建一张商品表 goods (id int , goods_name varchar(10), price double );
-- 添加 2 条记录
CREATE TABLE `goods` (
id INT ,
goods_name VARCHAR(10), -- 长度 10
price DOUBLE NOT NULL DEFAULT 100 );
-- 添加数据
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), -- 长度 10
price DOUBLE NOT NULL DEFAULT 100 );
insert细节
#说明 insert 语句的细节
-- 1.插入的数据应与字段的数据类型相同。
-- 比如 把 'abc' 添加到 int 类型会错误
INSERT INTO `goods` (id, goods_name, price)
VALUES('韩顺平', '小米手机', 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;
INSERT INTO `goods2` (id, goods_name)
VALUES(10, '顺平手机');
SELECT * FROM goods2;
update语句
UPDATE tbl _name
SET col_name1=expr1 [col_name2=expr2 ... ]
[[WHERE where_definition]
-- 演示 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;
细节：
1.UPDATE语法可以用新值更新原有表行中的各列。
2.SET子句指示要修改哪些列和要给予哪些值。
3.WHERE子句指定应更新哪些行。如没有WHERE子句,则更新所有的行(记录)，因此老师提醒一定小心。
4.如果需要修改多个字段，可以通过set字段1=值1,字段2=值2.….
delete语句
使用delete语句删除表中的数据
还有 truncate table table_name; //摧毁表结构，然后重构
-- 删除表中名称为’老妖怪’的记录。
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;
细节：
1.如果不使用where子句，将删除表中所有数据。
2.Delete语句不能删除某一列的值(可使用update设为null或者")
3.使用delete语句仅删除记录,不删除表本身。如要删除表，使用drop
table语句。drop table表名;
select语句
SELECT [DISTINCT]*l{column1,column2. column3..}
FROM table name;
- Select 指定查询哪些列的数据。
column指定列名。
*号代表查询所有列。From指定查询哪张表。 - DISTINCT可选，指显示结果时，是否去掉重复数据
-- 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;
使用表达式对查询的列进行运算
SELECT * l {column1l expression,column2 / expression,..}
FROM tablename;
在select语句中可使用as语句
SELECT column_ name as 别名from表名;
-- 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;
在where子句中经常使用到的运算符
使用order by子句排序查询结果
SELECTcolzmn1 , column2 . column3 . .
FROM table;
order by column asc l desc, ...
- Order by 指定排序的列，排序的列既可以是表中的列名，也可以是
select
语句后指定的列名。
2.Asc升序[默认]、Desc降序
3.ORDER BY 子句应位于SELECT语句的结尾。
-- 演示 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;
合计/统计函数
Count返回行的总数
select count(*) l count(列名)from table_name
[WHERE where_definition]
-- 演示 mysql 的统计函数的使用
-- 统计一个班级共有多少学生？
SELECT COUNT(*) FROM student;
-- 统计数学成绩大于 90 的学生有多少个？
SELECT COUNT(*) FROM student
WHERE math > 90
-- 统计总分大于 250 的人数有多少？
SELECT COUNT(*) FROM student
WHERE (math + english + chinese) > 250
-- count(*) 和 count(列) 的区别
-- 解释 :count(*) 返回满足条件的记录的行数
-- count(列): 统计满足条件的某列有多少个，但是会排除 为 null 的情况
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)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 的使用
-- 练习：
-- 求一个班级数学平均分？
SELECTAVG(math) FROM student;
-- 求一个班级总分平均分
SELECTAVG(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_socre
FROM student;
Sum函数返回满足where条件的行的和–一般使用在数值列
Select sum(列名){ ,sum(列名)...} from tablename
[WHERE where definition]
AVG函数返回满足where条件的一列的平均值
select avg(列名){,avg(列名)...} from tablename
[WHERE where_definition]
Max/min函数返回满足where条件的一列的最大/最小值
Select max(列名)from tablename
[WHERE where_definition]
使用group by子句对列进行分组
SELECT column1, column2. column3.. FROM table
group by column
使用having子句分组后结果进行过滤
SELECT column1 , column2 . column3 . .
FROM table
group by column having ...
group by用于对查询的结果分组统计,(示意图)having子句用于限制分组显示结果.
CREATE TABLE dept( /*部门表*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
dname VARCHAR(20) NOT NULL 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 NOT NULL DEFAULT 0, /*编号*/
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
mgr MEDIUMINT UNSIGNED ,/*上级编号*/
hiredate DATE NOT NULL,/*入职时间*/
sal 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, '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;
-- 工资级别
#工资级别表
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;
SELECT * FROM dept;
SELECT * FROM emp;
# 演示 group by + having
GROUP by 用于对查询的结果分组统计, (示意图)
-- having 子句用于限制分组显示结果.
-- ?如何显示每个部门的平均工资和最高工资
-- 分析: avg(sal) max(sal)
-- 按照部分来分组查询
SELECTAVG(sal), MAX(sal) , deptno
FROM emp GROUP BY deptno;
-- 使用数学方法，对小数点进行处理
SELECT FORMAT(AVG(sal),2), MAX(sal) , deptno
FROM emp GROUP BY deptno;
-- ?显示每个部门的每种岗位的平均工资和最低工资
-- 老师分析 1. 显示每个部门的平均工资和最低工资
-- 2. 显示每个部门的每种岗位的平均工资和最低工资
SELECTAVG(sal), MIN(sal) , deptno, job
FROM emp GROUP BY deptno, job;
-- ?显示平均工资低于 2000 的部门号和它的平均工资 // 别名
-- 老师分析 [写 sql 语句的思路是化繁为简,各个击破]
-- 1. 显示各个部门的平均工资和部门号
-- 2. 在 1 的结果基础上，进行过滤，保留 AVG(sal) < 2000
-- 3. 使用别名进行过滤
SELECTAVG(sal), deptno
FROM emp GROUP BY deptno
HAVINGAVG(sal) < 2000;
-- 使用别名
SELECTAVG(sal)AS avg_sal, deptno
FROM emp GROUP BY deptno
HAVING avg_sal < 2000;
字符串函数
-- CHARSET(str) 返回字串字符集
SELECT CHARSET(ename) FROM emp;
-- CONCAT (string2 [,... ]) 连接字串, 将多个列拼接成一列
SELECT CONCAT(ename, ' 工作是 ', job) FROM emp;
-- INSTR (string ,substring ) 返回 substring 在 string 中出现的位置,没有返回 0
-- dual 亚元表, 系统表 可以作为测试表使用
SELECT INSTR('hanshunping', 'ping') 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('hsp', 'hsp') 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)
-- 去除前端空格或后端空格
SELECT LTRIM(' 韩顺平教育') FROM DUAL;
SELECT RTRIM('韩顺平教育 ') FROM DUAL;
SELECT TRIM(' 韩顺平教育 ') FROM DUAL;
-- 练习: 以首字母小写的方式显示所有员工 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;
数学相关函数
-- 演示数学相关函数
--ABS(num) 绝对值
SELECTABS(-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 FORMAT(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;
时间日期相关函数
上面函数的细节说明:
- DATE ADD()中的interval后面可以是 year minute second day等
- DATE SUB()中的 interval后面可以是 year minute second hour day等
- DATEDIFF(date1,date2)得到的是天数，而且是date1-date2的天数，因此可以取负数
- 这四个函数的日期类型可以是date, datetime或者 timestamp
在实际开发中，我们也经常使用int来保存一个unix时间戳，然后使用from_unixtime()进行转换,还是非常有实用价值的
dual表是MySQL数据库自带的一个特殊表，它只有一列和一行，且该列没有名称，该表的作用是为了在SQL语句中提供一个可以查询的虚拟表，以便执行一些不需要表格数据的操作。
-- 日期时间相关函数
-- 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);
-- 添加一条记录
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 语句求出你活了多少天? [练习] 1986-11-11 出生
SELECT DATEDIFF(NOW(), '1986-11-11') FROM DUAL;
-- 如果你能活 80 岁，求出你还能活多少天.[练习] 1986-11-11 出生
-- 先求出活 80 岁 时, 是什么日期 X
-- 然后在使用 datediff(x, now()); 1986-11-11->datetime
-- INTERVAL 80 YEAR ： YEAR 可以是 年月日，时分秒
-- '1986-11-11' 可以 date,datetime timestamp
SELECT 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 )
SELECTYEAR(NOW()) FROM DUAL;
SELECT MONTH(NOW()) FROM DUAL;
SELECT DAY(NOW()) FROM DUAL;
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;
加密和系统函数
-- 演示加密函数和系统函数
-- USER() 查询用户
-- 可以查看登录到 mysql 的有哪些用户，以及登录的 IP
SELECT USER() FROM DUAL; -- 用户@IP 地址
-- DATABASE()查询当前使用数据库名称
SELECT DATABASE();
-- MD5(str) 为字符串算出一个 MD5 32 的字符串，常用(用户密码)加密
-- root 密码是 hsp -> 加密 md5 -> 在数据库中存放的是加密后的密码
SELECT MD5('hsp') FROM DUAL;
SELECT LENGTH(MD5('hsp')) FROM DUAL;
-- 演示用户表，存放密码时，是 md5
CREATE TABLE hsp_user
(id INT ,
`name` VARCHAR(32) NOT NULL DEFAULT '',
pwd CHAR(32) NOT NULL DEFAULT '');
INSERT INTO hsp_user
VALUES(100, '韩顺平', MD5('hsp'));
SELECT * FROM hsp_user; -- csdn
SELECT * FROM hsp_user -- SQL 注入问题
WHERE `name`='韩顺平'AND pwd = MD5('hsp')
-- PASSWORD(str) -- 加密函数, MySQL 数据库的用户密码就是 PASSWORD 函数加密
SELECT PASSWORD('hsp') FROM DUAL; -- 数据库的 *81220D972A52D4C51BB1C37518A2613706220DAC
-- select * from mysql.user 从原文密码 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,如果 expr2 为 t, 返回 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
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)AS 'job'
FROM emp;
SELECT * FROM emp;
SELECT * FROM dept;
SELECT * FROM salgrade;
mysql表查询--加强
在前面我们讲过mysql表的基本查询，但是都是对一张表进行的查询，这在实际的软件开发中，还远远的不够。
下面我们讲解的过程中，将使用前面创建三张表(emp,dept,salgrade)为大家演示如何进行多表查询使用where子句
?如何查找1992.1.1后入职的员工
如何使用like操作符
%:表示0到多个字符
_:表示单个字符
如何显示首字符为S的员工姓名和工资
如何显示第三个字符为大写O的所有员工的姓名和工资如何显示没有上级的雇员的情况
查询表结构selectinc.sql
-- 查询加强
-- ■ 使用 where 子句
-- ?如何查找 1992.1.1 后入职的员工
-- 老师说明： 在 mysql 中,日期类型可以直接比较, 需要注意格式
SELECT * FROM emp
WHERE hiredate > '1992-01-01'
-- ■ 如何使用 like 操作符(模糊)
-- %: 表示 0 到多个任意字符 _: 表示单个任意字符
-- 如何显示首字符为 S 的员工姓名和工资
SELECT ename, sal FROM emp
WHERE ename LIKE 'S%'
-- ?如何显示第三个字符为大写 O 的所有员工的姓名和工资
SELECT ename, sal FROM emp
WHERE ename LIKE '__O%'
-- ■ 如何显示没有上级的雇员的情况
SELECT * FROM emp
WHERE mgr IS NULL;
-- ■ 查询表结构
DESC emp
-- 使用 order by 子句
-- ?如何按照工资的从低到高的顺序[升序]，显示雇员的信息
SELECT * FROM emp
ORDER BY sal
-- ?按照部门号升序而雇员的工资降序排列 , 显示雇员信息
SELECT * FROM emp
ORDER BY deptno ASC , sal DESC;
分页查询
1．按雇员的id号升序取出，每页显示3条记录，请分别显示第1页，第2页 第3页
2.基本语法:select...limit start, rows
表示从start+1行开始取,取出rows行, start 从0开始计算page.sql
--推导一个公式
3.公式:
SELECT *FROM emp
ORDER BY empno
LIMIT每页显示记录数*(第几页-1)，每页显示记录数
-- 分页查询
-- 按雇员的 id 号升序取出， 每页显示 3 条记录，请分别显示 第 1 页，第 2 页，第 3 页
-- 第 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 job, COUNT(*) FROM emp GROUP BY job;
-- 显示雇员总数，以及获得补助的雇员数
SELECT COUNT(*) FROM emp WHERE mgr IS NOT NULL;
SELECT MAX(sal) - MIN(sal) FROM emp;
使用分组函数和分组子句group by
(1)显示每种岗位的雇员总数、平均工资。
(2)显示雇员总数，以及获得补助的雇员数。
(3)显示管理者的总人数。
(4)显示雇员工资的最大差额。
-- 增强 group by 的使用
-- (1) 显示每种岗位的雇员总数、平均工资。
SELECT COUNT(*), AVG(sal), job
FROM emp
GROUP BY job;
-- (2) 显示雇员总数，以及获得补助的雇员数。
-- 思路: 获得补助的雇员数 就是 comm 列为非 null, 就是 count(列)，如果该列的值为 null, 是
-- 不会统计 , SQL 非常灵活，需要我们动脑筋.
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) 显示雇员工资的最大差额。
-- 思路： max(sal) - min(sal)
SELECT MAX(sal) - MIN(sal)
FROM emp;
SELECT *
FROM emp;
select * from dept;
-- 应用案例：请统计各个部门 group by 的平均工资 avg，
-- 并且是大于 1000 的 having，并且按照平均工资从高到低排序， order by
-- 取出前两行记录 limit 0, 2
SELECT deptno, AVG(sal)AS avg_sal
FROM emp
GROUP BY deptno
HAVING avg_sal > 1000
ORDER BY avg_sal DESC
LIMIT 0,2
数据分组的总结
如果select语句同时包含有group by ,having ,limit，order by那么他们的顺序是group by , having , order by,limit
格式：
SELECT column1 , column2 . column3 . .
FROM table
join table_name on join_condition
where serach_condition
group by column
having condition
order by column
limit start, rows;
mysql的多表查询
多表查询是指基于两个和两个以上的表查询.在实际应用中,查询单个表可能不能满足你的需求,需要使用到(dept表和emp表)
-- 多表查询
-- ?显示雇员名,雇员工资及所在部门的名字 【笛卡尔集】
/*
分析
1. 雇员名,雇员工资 来自 emp 表
2. 部门的名字 来自 dept 表
3. 需求对 emp 和 dept 查询 ename,sal,dname,deptno
4. 当我们需要指定显示某个表的列时，需要 表.列表
*/
SELECT ename,sal,dname,emp.deptno
FROM emp, dept
WHERE emp.deptno = dept.deptno
SELECT * FROM emp;
SELECT * FROM dept;
SELECT * FROM salgrade;
-- 小技巧：多表查询的条件不能少于 表的个数-1, 否则会出现笛卡尔集
-- ?如何显示部门号为 10 的部门名、员工名和工资
SELECT ename,sal,dname,emp.deptno
FROM emp, dept
WHERE emp.deptno = dept.deptno AND emp.deptno = 10
-- ?显示各个员工的姓名，工资，及其工资的级别
-- 思路 姓名，工资 来自 emp 13
-- 工资级别 salgrade 5
-- 写 sql , 先写一个简单，然后加入过滤条件...
select ename, sal, grade
from emp , salgrade
where sal between losal and hisal;
自连接
自连接是指在同一张表的连接查询[将同一张表看做两张表].
-- 多表查询的 自连接
-- 思考题: 显示公司员工名字和他的上级的名字
-- 老韩分析： 员工名字 在 emp, 上级的名字的名字 emp
-- 员工和上级是通过 emp 表的 mgr 列关联
-- 这里老师小结：
-- 自连接的特点 1. 把同一张表当做两张表使用
-- 2. 需要给表取别名 表名 表别名
-- 3. 列名不明确，可以指定列的别名 列名 as 列的别名
SELECT worker.ename AS '职员名' , boss.ename AS '上级名'
FROM emp worker, emp boss
WHERE worker.mgr = boss.empno;
SELECT * FROM emp;
mysql表-子查询
子查询是指嵌入在其它sql语句中的select语句,也叫嵌套查询
单行子查询
单行子查询是指只返回一行数据的子查询语句
多行子查询
多行子查询指返回多行数据的子查询 使用关键字 in
-- 子查询的演示
-- 请思考：如何显示与 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, sal, deptno
from emp
where job in (
SELECT DISTINCT job
FROM emp
WHERE deptno = 10
) and deptno <> 10
子查询当作临时表来使用
在多行子查询中使用 all 操作符
-- 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中各个类别中，价格最高的商品.
-- 老韩提示，可以将子查询当做一张临时表使用
-- 查询 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
在多行子查询中使用 any 操作符
多列子查询
-- 多列子查询
-- 请思考如何查询与 allen 的部门和岗位完全相同的所有雇员(并且不含 allen 本人)
-- (字段 1， 字段 2 ...) = (select 字段 1，字段 2 from 。。。。)
-- 分析: 1. 得到 smith 的部门和岗位
SELECT deptno , job
FROM emp
WHERE ename = 'ALLEN'
-- 分析: 2 把上面的查询当做子查询来使用，并且使用多列子查询的语法进行匹配
SELECT *
FROM emp
WHERE (deptno , job) = (
SELECT deptno , job
FROM emp
WHERE ename = 'ALLEN'
)AND ename != 'ALLEN'
-- 请查询 和宋江数学，英语，语文
-- 成绩 完全相同的学生
SELECT *
FROM student
WHERE (math, english, chinese) = (
SELECT math, english, chinese
FROM student
WHERE `name` = '宋江'
)
SELECT * FROM student;
在 from 子句中使用子查询
-- 子查询练习
-- 请思考：查找每个部门工资高于本部门平均工资的人的资料
-- 这里要用到数据查询的小技巧，把一个子查询当作一个临时表使用
-- 1. 先得到每个部门的 部门号和 对应的平均工资
SELECT deptno, AVG(sal)AS avg_sal
FROM emp GROUP BY deptno
-- 2. 把上面的结果当做子查询, 和 emp 进行多表查询
--
SELECT ename, sal, temp.avg_sal, emp.deptno
FROM emp, (
SELECT deptno, AVG(sal)AS avg_sal
FROM emp
GROUP BY deptno
) temp
WHERE emp.deptno = temp.deptnoAND emp.sal > temp.avg_sal
-- 查找每个部门工资最高的人的详细资料
SELECT ename, sal, temp.max_sal, emp.deptno
FROM emp, (
SELECT deptno, MAX(sal) AS max_sal
FROM emp
GROUP BY deptno
) temp
WHERE emp.deptno = temp.deptnoAND emp.sal = temp.max_sal
-- 查询每个部门的信息(包括：部门名,编号,地址)和人员数量,我们一起完成。
-- 1. 部门名,编号,地址 来自 dept 表
-- 2. 各个部门的人员数量 -》 构建一个临时表
SELECT COUNT(*), deptno
FROM emp
GROUP BY deptno;
SELECT dname, dept.deptno, loc , tmp.per_num AS '人数'
FROM dept, (
SELECT COUNT(*) AS per_num, deptno
FROM emp
GROUP BY deptno
) tmp
WHERE tmp.deptno = dept.deptno
-- 还有一种写法 表.* 表示将该表所有列都显示出来, 可以简化 sql 语句
-- 在多表查询中，当多个表的列不重复时，才可以直接写列名
SELECT tmp.* , dname, loc
FROM dept, (
SELECT COUNT(*) AS per_num, deptno
FROM emp
GROUP BY deptno
) tmp
WHERE tmp.deptno = dept.deptno
表复制
自我复制数据(蠕虫复制)
有时，为了对某个sql语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据。coyptab.sql
-- 表的复制
-- 为了对某个 sql 语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据
CREATE TABLE my_tab01
( id INT,
`name` VARCHAR(32),
sal DOUBLE,
job VARCHAR(32),
deptno INT);
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;
合并查询
有时在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号union , union all
1.union all
该操作符用于取得两个结果集的并集。当使用该操作符时，不会取消重复行。
select ename,sal,job from emp where sal>2500 union
select ename,sal,job from emp where job='MANAGER';
2.union
该操作赋与union all相似，但是会自动去掉结果集中重复行select ename,sal,job from emp where sal>2500
union all select ename,sal,job from emp wherejob='manager";
-- 合并查询
SELECT ename,sal,job FROM emp WHERE sal>2500 -- 5
SELECT ename,sal,job FROM emp WHERE job='MANAGER' -- 3
-- union all 就是将两个查询结果合并，不会去重
SELECT ename,sal,job FROM emp WHERE sal>2500 -- 5
UNION ALL
SELECT ename,sal,job FROM emp WHERE job='MANAGER' -- 3
-- union 就是将两个查询结果合并，会去重
SELECT ename,sal,job FROM emp WHERE sal>2500 -- 5
UNION
SELECT ename,sal,job FROM emp WHERE job='MANAGER' -- 3
mysql表外连接
1．前面我们学习的查询，是利用where子句对两张表或者多张表，形成的笛
卡尔积进行筛选，根据关联条件，显示所有匹配的记录，匹配不上的，不显示
2.比如:列出部门名称和这些部门的员工名称和工作，同时要求显示出那些没有员工的部门。
3．使用我们学习过的多表查询的SQL，看看效果如何?:outer.sql ->外连
接
-- 外连接
-- 比如：列出部门名称和这些部门的员工名称和工作，
-- 同时要求 显示出那些没有员工的部门。
-- 使用我们学习过的多表查询的 SQL， 看看效果如何?
SELECT dname, ename, job
FROM emp, dept
WHERE emp.deptno = dept.deptno
ORDER BY dname
SELECT * FROM dept;
SELECT * FROM emp;
-- 创建 stu
/*
id name
1 Jack
2 Tom
3 Kity
4 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 grade
1 56
2 76
11 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, grade
FROM stu, exam
WHERE stu.id = exam.id;
-- 改成左外连接
SELECT `name`, stu.id, grade
FROM stu LEFT JOIN exam
ON stu.id = exam.id;
-- 使用右外连接（显示所有成绩，如果没有名字匹配，显示空)
-- 即：右边的表(exam) 和左表没有匹配的记录，也会把右表的记录显示出来
SELECT `name`, stu.id, grade
FROM stu RIGHT JOIN exam
ON stu.id = exam.id;
-- 列出部门名称和这些部门的员工信息(名字和工作)，
-- 同时列出那些没有员工的部门名。5min
-- 使用左外连接实现
SELECT dname, ename, job
FROM dept LEFT JOIN emp
ON dept.deptno = emp.deptno
-- 使用右外连接实现
SELECT dname, ename, job
FROM emp RIGHT JOIN dept
ON dept.deptno = emp.deptno
--
mysql约束
约束用于确保数据库的数据满足特定的商业规则。在mysql中，约束包括: not null、unique,primary key, foreign key,和check五种.
primary key(主键)-细节说明
primary key不能重复而且不能为null。
一张表最多只能有一个主键,但可以是复合主键
主键的指定方式有两种
直接在字段名后指定:字段名 primakry key
在表定义最后写primary key(列名);
使用desc表名，可以看到primary key的情况.
老师提醒:在实际开发中，每个表往往都会设计个主键.
-- 主键使用
-- id name email
CREATE TABLE t17
(id INT PRIMARYKEY, -- 表示 id 列是主键
`name` VARCHAR(32),
email VARCHAR(32));
-- 主键列的值是不可以重复
INSERT INTO t17
VALUES(1, 'jack', 'jack@sohu.com');
INSERT INTO t17
VALUES(2, 'tom', 'tom@sohu.com');
INSERT INTO t17
VALUES(1, 'hsp', 'hsp@sohu.com');
SELECT * FROM t17;
-- 主键使用的细节讨论
-- primary key 不能重复而且不能为 null。
INSERT INTO t17
VALUES(NULL, 'hsp', 'hsp@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),
PRIMARYKEY (id, `name`) -- 这里就是复合主键
);
INSERT INTO t18
VALUES(1, 'tom', 'tom@sohu.com');
INSERT INTO t18
VALUES(1, 'jack', 'jack@sohu.com');
INSERT INTO t18
VALUES(1, 'tom', 'xx@sohu.com'); -- 这里就违反了复合主键
SELECT * FROM t18;
-- 主键的指定方式 有两种
-- 1. 直接在字段名后指定：字段名 primakry key
-- 2. 在表定义最后写 primary key(列名);
CREATE TABLE t19
(id INT ,
`name` VARCHAR(32) PRIMARYKEY,
email VARCHAR(32)
);
CREATE TABLE t20
(id INT ,
`name` VARCHAR(32) ,
email VARCHAR(32),
PRIMARYKEY(`name`) -- 在表定义最后写 primary key(列名)
);
-- 使用 desc 表名，可以看到 primary key 的情况
DESC t20 -- 查看 t20 表的结果，显示约束的情况
DESC t18
not null 如果在列上定义了not null,那么当插入数据时，必须为列提供数据。
unique 当定义了唯一约束后，该列值是不能重复的.。
如果没有指定not null，则unique字段可以有多个null一张表可以有多个unique字段
-- unique 的使用
CREATE TABLE t21
(id INT UNIQUE , -- 表示 id 列是不可以重复的.
`name` VARCHAR(32) ,
email VARCHAR(32)
);
INSERT INTO t21
VALUES(1, 'jack', 'jack@sohu.com');
INSERT INTO t21
VALUES(1, 'tom', 'tom@sohu.com');
-- unqiue 使用细节
-- 1. 如果没有指定 not null , 则 unique 字段可以有多个 null
-- 如果一个列(字段)， 是 unique not null 使用效果类似 primary key
INSERT INTO t21
VALUES(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
foreign key 外键
用于定义主表和从表之间的关系:外键约束要定义在从表上，主表则必须具有主键约束或是unique约束.当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null(学生/班级图示)
FOREIGN KEY(本表字段名)REFERENCES主表名(主键名或unique字段名)
外键细节
外键指向的表的字段，要求是primary key或者是unique
表的类型是innodb,这样的表才支持外键
外键字段的类型要和主键字段的类型一致(长度可以不同)外键字段的值,必须在主键字段中出现过，或者为null[前提是外键字段允许为null]
一旦建立主外键的关系，数据不能随意删除了.
-- 外键演示
-- 创建 主表 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');
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);
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;
check
用于强制行数据必须满足的条件，假定在sal列上定义了check约束,并要求sal列值在1000-2000之间如果不再1000-2000之间就会提示出错。
基本语法:
列名 类型 check (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;
约束练习
-- 使用约束的课堂练习
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
-- 商品 goods
CREATE TABLE goods (
goods_id INT PRIMARY KEY,
goods_name VARCHAR(64) NOT NULL DEFAULT '',
unitprice DECIMAL(10,2) NOT NULL DEFAULT 0
CHECK (unitprice >= 1.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) PRIMARYKEY, -- 程序员自己决定
`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;
自增长
语法：字段名 整型 primary key auto_increment
添加自增长的字段方式
insert into xxx(字段1，字段2…….) values(null,"值'...);
insert into xxx(字段2..….) values('值1，"值2'..…);
insert into xxx values(null, "值1'......)
细节：
一般来说自增长是和primary key 配合使用的
自增长也可以单独使用[但是需要配合一个unique]
自增长修饰的字段为整数型的(虽然小数也可以但是非常非常少这样使用)
自增长默认从1开始,你也可以通过如下命令修改altertable表名auto_increment=新的开始值;
如果你添加数据时，给自增长字段(列)指定的有值，则以指定的值为准，如果指定了自增长，一般来说，就按照自增长的规则来添加数据。
-- auto_increment
-- 演示自增长的使用
-- 创建表
CREATE TABLE t24
(id INT PRIMARYKEY AUTO_INCREMENT,
email VARCHAR(32)NOT NULL DEFAULT '',
`name` VARCHAR(32)NOT NULL DEFAULT '');
DESC t24
-- 测试自增长的使用
INSERT INTO t24
VALUES(NULL, 'tom@qq.com', 'tom');
INSERT INTO t24
(email, `name`) VALUES('hsp@sohu.com', 'hsp');
SELECT * FROM t24;
-- 修改默认的自增长开始值
ALTER TABLE t25AUTO_INCREMENT = 100
CREATE TABLE t25
(id INT PRIMARYKEY AUTO_INCREMENT,
email VARCHAR(32)NOT NULL DEFAULT '',
`name` VARCHAR(32)NOT NULL DEFAULT '');
INSERT INTO t25
VALUES(NULL, 'mary@qq.com', 'mary');
INSERT INTO t25
VALUES(666, 'hsp@qq.com', 'hsp');
SELECT * FROM t25;
CREATE DATABASE tmp;
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);
mysql索引
说起提高数据库性能,索引是最物美价廉的东西了。不用加内存，不用改程序,不用调sql，查询速度就可能提高百倍干倍。
-- 创建海量数据
-- 创建测试数据库 tmp
CREATE DATABASE tmp;
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 上创建索引
索引的原理
没有索引为什么会慢?因为全表扫描.
使用索引为什么会快?形成一个索引的数据结构,，比如二叉树索引的代价
磁盘占用
对dml(update delete insert)语句的效率影响
索引的类型
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．添加索引(建小表测试id , name ) index use.sql
create [UNIQUE] index index name on tbl_name (col name [(length)1[ASC |DESC],.….….);
alter table table name ADD INDEX [index_name] (index col name,….)
2.添加主键(索引)ALTER TABLE表名ADD PRIMARY KEY(列名.…);
3.删除索引
DROP INDEX index_name ON tbl_name,
alter table table_name drop index index_name;
4．删除主键索引比较特别: alter table t_b drop primary key;
5.查询索引(三种方式)
show index(es) from table_name;
show keys from table_name;
desc table_Name;
-- 演示 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 t25ADD INDEX id_index (id)
-- 添加主键索引
CREATE TABLE t26 (
id INT ,
`name` VARCHAR(32));
ALTER TABLE t26ADD PRIMARYKEY (id)
SHOW INDEX FROM t25
-- 删除索引
DROP INDEX id_index ON t25
-- 删除主键索引
ALTER TABLE t26 DROP PRIMARYKEY
-- 修改索引 ， 先删除，在添加新的索引
-- 查询索引
-- 1. 方式
SHOW INDEX FROM t25
-- 2. 方式
SHOW INDEXES FROM t25
-- 3. 方式
SHOW KEYS FROM t25
-- 4 方式
DESC t25
总结：
1．较频繁的作为查询条件字段应该创建索引select* from emp where empno =1
2．唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件
select * from emp where sex =‘男‘
3.更新非常频繁的字段不适合创建索引
select * from emp where logincount = 14。不会出现在WHERE子句中字段不该创建索引
mysql事务
事务用于保证数据的一致性,它由一组相关的dml语句组成,该组的dml语句要么全部成功,要么全部失败。如:转账就要用事务来处理,用以保证数据的一致性。
事务和锁
当执行事务操作时(dml语句),mysql会在表上加锁,防止其它用户改表的数据.这对用户来讲是非常重要的
mysql数据库控制台事务的几个重要操作
start transaction -- 开始一个事务
savepoint保存点名--设置保存点
rollback to保存点名--回退事务
rollback --回退全部事务
commit -- 提交事务,所有的操作生效,不能回退
细节:
1.没有设置保存点
2多个保存点
3存储引擎
4.开始事务方式
-- 事务的一个重要的概念和具体操作
-- 看一个图[看示意图]
-- 演示
-- 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-- 事务的一个重要的概念和具体操作
-- 看一个图[看示意图]
-- 演示
-- 1. 创建一张测试表
CREATE TABLE t27
( id INT,
`name` VARCHAR(32));
-- 2. 开始事务
STARTTRANSACTION
-- 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
回退事务
在介绍回退事务前，先介绍一下保存点(savepoint).保存点是事务中的点.用于取消部分事务,当结束事务时(commit),会自动的删除该事务所定义的所有保存点.当执行回退事务时，通过指定保存点可以回退到指定的点,这里我们作图说明
提交事务
使用commit语句可以提交事务.当执行了commit语句子后,会确认事务的变化、结束事务、删除保存点、释放锁，数据生效。当使用commit语句结束事务之后，其它会话[其他连接]将可以查看到事务变化后的新数据[所有数据就正式生效]
事务细节
如果不开始事务，默认情况下，dml操作是自动提交的,不能回滚
如果开始一个事务，你没有创建保存点.你可以执行rollback,默认就是回退到你事务开始的状态.
你也可以在这个事务中(还没有提交时),创建多个保存点.比如:
savepointaaa;执行dml , savepoint bbb;
你可以在事务没有提交前，选择回退到哪个保存点.
mysql的事务机制需要innodb的存储引擎才可以使用,myisam不好使.开始一个事务start transaction, set autocommit=off;
-- 讨论 事务细节
-- 1. 如果不开始事务，默认情况下，dml 操作是自动提交的，不能回滚
INSERT INTO t27 VALUES(300, 'milan'); -- 自动提交 commit
SELECT * FROM t27
-- 2. 如果开始一个事务，你没有创建保存点. 你可以执行 rollback，
-- 默认就是回退到你事务开始的状态
STARTTRANSACTION
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;
事务隔离级别
事务隔离级别介绍：
1.多个连接开启各自事务操作数据库中数据时，数据库系统要负
责隔离操作，以保证各个连接在获取数据时的准确性。(通俗解释）
2．如果不考虑隔离性,可能会引发如下问题:
1.脏读
2.不可重复读
3.幻读
查看事务隔离级别
脏读(dirty read):当一个事务读取另一个事务尚未提交的改变(update,insert.delete)时,产生脏读
不可重复读(nonrepeatable read):同一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除,每次返回不同的结果集，此时发生不可重复读。
幻读(phantom read):同一查询在同一事务中多次进行，由于其他提交事务所做的插入操作，每次返回不同的结果集,此时发生幻读。
事务隔离级别
Mysql隔离级别定义了事务与事务之间的隔离程度。
说明：√是可能出现，×是不会出现
设置事务的隔离级别
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事务acid
事务的特性：
原子性(Atomicity)
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性(Consistency)
事务必须使数据库从一个一致性状态变换到另外一个一致性状态
隔离性(lsolation)
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
持久性(Durability)
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响
mysql的表类型和存储引擎
MySQL的表类型由存储引擎(Storage Engines）决定，主要包括MylSAM,innoDB、Memory等。
MySQL 数据表主要支持六种类型，分别是:CSV、Memory、ARCHIVE，MRG_MYISAM、MYISAM、InnoBDB。
这六种又分为两类,一类是”事务安全型”(transaction-safe)，比如:InnoDB;
其余都属于第二类，称为”非事务安全型”(non-transaction-safe)[myisam和memory]
显示当前数据库支持的存储引擎： show engines;
细节：
MyISAM不支持事务、也不支持外键，但其访问速度快，对事务完整性没有要求
InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MyISAM存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。
MEMORY存储引擎使用存在内存中的内容来创建表。每个MEMORY表只实际对应上个磁盘文件。MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH索引。但是一旦MySQL服务关闭，表中的数据就会丢失掉,表的结构还在。
-- 表类型和存储引擎
-- 查看所有的存储引擎
SHOW ENGINES
-- innodb 存储引擎，是前面使用过.
-- 1. 支持事务 2. 支持外键 3. 支持行级锁
-- myisam 存储引擎
CREATE TABLE t28 (
id INT,
`name` VARCHAR(32)) ENGINE MYISAM
-- 1. 添加速度快 2. 不支持外键和事务 3. 支持表级锁
STARTTRANSACTION;
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
如何选择表的存储引擎
1．如果你的应用不需要事务，处理的只是基本的CRUD操作，那么MylSAM
是不二选择,速度快
2.如果需要支持事务，选择lnnoDB。
3.Memory存储引擎就是将数据存储在内存中，由于没有磁盘I./O的等待,
速度极快。但由于是内存存储引擎，所做的任何修改在服务器重启后都将消失。(经典用法用户的在线状态().)
修改存储引擎
ALTER TABLE 表名`ENGINE =储存引擎;
视图
emp表的列信息很多，有些信息是个人重要信息(比如sal, comm,mgr, hiredate)，如果我们希望某个用户只能查询emp表的(empno,.ename, job和deptno)信息，有什么办法?=>视图
视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含列,其数据来自对应的真实表(基表)视图和基表关系的示意图
对视图的总结
1.视图是根据基表(可以是多个基表)来创建的视图是虚拟的表
2.视图也有列，数据来自基表
3.通过视图可以修改基表的数据
4.基表的改变，也会影响到视图的数据
- create view视图名as select语句
- alter view视图名as select语句--更新成新的视图
- SHoW CREATE VIEW 视图名
- drop view视图名1,视图名2
-- 视图的使用
-- 创建一个视图 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
-- 删除视图
DROPVIEW 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
视图细节：
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．安全。一些数据表有着重要的信息。有些字段是保密的，不能让用户直接看到。这
时就可以创建一个视图，在这张视图中只保留一部分字段。这样，用户就可以查询自己需要的字段,不能查看保密的字段。
2.性能。关系数据库的数据常常会分表存储，使用外键建立这些表的之间关系。这时,
数据库查询通常会用到连接（JOIN）。这样做不但麻烦，效率相对也比较低。如果建立一个视图，将相关的表和字段组合在一起，就可以避免使用JOIN查询数据。
3.灵活。如果系统中有一张旧的表，这张表由于设计的问题，即将被废弃。然而，很
多应用都是基于这张表，不易修改。这时就可以建立一张视图,视图中的数据直接映射到新建的表。这样，就可以少做很多改动，也达到了升级数据表的目的。
练习：
-- 视图的课堂练习
-- 针对 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
(sal BETWEEN losal AND hisal)
DESC emp_view03
SELECT * FROM emp_view03
mysql管理
mysql用户
其中user表的重要字段说明:
- host: 允许登录的“位置”，localhost表示该用户只允许本机登录，也可以指定ip地址，比如:192.168.1.100
- user:用户名;
- authentication string:密码，是通过mysql的password()函数加密之后
的密码。
创建用户
create user‘用户名’ @ ’ 允许登录位置 ’ identified by “密码' 说明:创建用户，同时指定密码
删除用户：
drop user‘用户名’@’允许登录位置’;
用户修改密码
修改自己的密码:
set password = password('密码"');
修改他人的密码(需要有修改用户密码权限):
set password for'用户名'@′登录位置'=password('密码');
mysql中的权限
给用户授权
基本语法:
grant 权限列表 on 库.对象名 to‘用户名’@’登录位置’【identified by‘密码’】
说明:
1，权限列表，多个权限用逗号分开
grant select on......
grant select,delete,create on .....-
grant all [privileges]on .....
//表示赋予该用户在该对象上的所有权限
2特别说明
*.*:代表本系统中的所有数据库的所有对象(表，视图，存储过程)
库.*:表示某个数据库中的所有数据对象(表，视图，存储过程等)
3,identified by可以省略，也可以写出.
(1)如果用户存在,就是修改该用户的密码。(2)如果该用户不存在，就是创建该用户!
回收用户权限
基本语法:
revoke 权限列表 on 库.对象名 from '用户名"@"登录位置';
权限生效指令：
如果权限没有生效，可以执行下面命令.基本语法:
FLUSH PRIVILEGES;
-- 演示 用户权限的管理
-- 创建用户 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'
REVOKEALL ON testdb.news FROM 'shunping'@'localhost'
-- 删除 shunping
DROP USER 'shunping'@'localhost'
细节：
-- 说明 用户管理的细节
-- 在创建用户的时候，如果不指定 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.%'
数据库编程
数据库储存过程：https://www.runoob.com/w3cnote/mysql-stored-procedure.html
浙公网安备 33010602011771号