目录
一. 字符串相关的函数
-- 演示字符串相关函数的使用
-- charset(str) 返回字符串集合
SELECT CHARSET (ename) FROM emp;
-- concat（string2[,....]) 连接字符串 将多个列拼接成一列
SELECT CONCAT(ename,' 工作是 ',job) FROM emp;
-- instr (string,substring) 返回substring在 string中出出现的位置，没有返回0
SELECT INSTR('hanshunping','ping') FROM DUAL;
-- DUAL 亚元表，系统表 可以作为一个测试表使用
-- ucase (string2) 转为大写或者小写
SELECT UCASE(ename) FROM emp; -- 转大写 ucase
SELECT LCASE(ename)FROM emp; -- 转小写 lcase
-- LEFT (string2,length) 从string2 的最左边起取length个字符
SELECT LEFT(ename,2) FROM emp; -- 右边边取 right
-- length（string） 统计长度 按照字节返回 utf8 一个汉字占三个比比特位
SELECT LENGTH(ename) FROM emp;
-- replace （str,search_str,replace_str)
-- 在str 中replace——str 替换search_str
SELECT ename,REPLACE(job,'MANAGER','经理') FROM emp;
-- strcmp （string1,string2) 逐字符比较两字符串大小
SELECT STRCMP('hsp','hsp') FROM DUAL;
-- substring（str,position [,length])
-- 从str的position 开始 从1开始计算，取length 个字符
SELECT SUBSTRING(ename,1,2) FROM emp; -- 从ename 列的第一个位置开始取出两个字符
-- ltrim(string2) rtrim (string2) trim trim(string)
-- 去除前端空格 去除端空格 -- 去除前后空格
SELECT LTRIM(' han') FROM DUAL;
SELECT RTRIM('han ') FROM DUAL;
SELECT TRIM(' han ')FROM DUAL;
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) 绝对值
SELECT ABS(-1) FROM DUAL;
-- BIN (decimal_number) 十进制转二进制
SELECT BIN(23) FROM DUAL;
-- CEILING (number2) 向上取整 得到比number2 大的最小整数
SELECT CEILING (33.3) FROM DUAL;
-- CONV(number2,from_base,to_base) 进制转换
SELECT CONV(123,10,2) FROM DUAL;
-- FLOOR (number2) 向下取整 得到比num2 小的最大整数
SELECT FLOOR (12.5) FROM DUAL;
-- FORMAT (number,decimal_places) 保留小数位 四舍五入
SELECT FORMAT(12.32,3) FROM DUAL;
-- HEX (DecimalNUmber) 转换为十六进制
SELECT HEX(123) FROM DUAL;
-- LEAST （num1,num2 ..... num)
SELECT LEAST(12,14,12,14,12,11) FROM DUAL;
-- mod (numerator,denominator) 求余
SELECT MOD(10,3) FROM DUAL;
-- rand ([seed]) rand ([seed)] 返回其范围为0<= v <= 1.0
-- 如果seed 不变，该随机数也不变
SELECT RAND(3) FROM DUAL;
三.时间日期相关函数 date.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());
SELECT * FROM MES;
SELECT id ,content, DATE(send_time)
FROM mes;
-- 请查询在十分钟内发布的新闻
SELECT * FROM mes
WHERE DATE_ADD(send_time,INTERVAL 10 MINUTE) >= NOW();
SELECT * FROM mes
WHERE DATE_SUB(NOW(),INTERVAL 10 MINUTE) <= send_time;
-- 请在MySQL语句的sql 语句中求出 2011-11-11 和 1990 - 1- 1 相差多少天
SELECT DATEDIFF('2011-11-11','1990-1-1')/365
FROM DUAL;
-- 用mysql 的sql 求出你活了多少天
SELECT DATEDIFF (NOW(),'1999-11-11') AS new_date FROM DUAL;
-- 如果你能活80岁，求出你能活多少天
SELECT DATEDIFF(DATE_ADD('1986-11-11',INTERVAL 80 YEAR),NOW()) AS new_date FROM DUAL;
细节说明：
1.DATE_ADD() 中的 interval 后面可以是 year minute second day 等
2.DATE_SUB() 中的 interval 后面可以是 year minute second hiur day 等
3.DATEDIFF(date1,date2) 得到的是天数，而且是 date1- date2 的天数 因此可以取负数
4.这四个函数的日期类型可以是date datetime 或者 timestamp
-- YEAR|Month|DAY| DATE(datetime);
SELECT NOW() FROM DUAL;
SELECT MONTH(NOW())FROM DUAL;
SELECT DAY(NOW())FROM DUAL;
SELECT YEAR('2013-10-10') FROM DUAL;
-- unix_timestamp(); 返回的是1970—1-1 到 现在的毫秒数
SELECT UNIX_TIMESTAMP()/(365*24*3600) FROM DUAL;
-- FROM_UNIXTIME() 时间戳 可以把一个unix 的秒数转换为一个指定格式的日期
-- 意义 在开发中 可以存放一个整数 然后表示 时间 通过 FROM_UNIXTIME() 来转换
--
SELECT FROM_UNIXTIME(1618483484,'%Y-%m-%d %H:%i:%s') FROM DUAL;四. 加密和系统函数 pwd.sql
-- 演示加密函数和系统函数
-- user() 查询用户
-- 可以查看登录到mysql 的有那些用户，以及登录的ip
SELECT USER() FROM DUAL; -- 用户 @IP 地址
-- DATABASE() 数据库名称
SELECT DATABASE() FROM DUAL;
SELECT DATABASE();
-- MD5(str) 为字符串算出一个 MD5 32 的字符串，常用（用户密码）加密
-- root 密码是 tian - > 加密md5 ——> 在数据库中存放的是加密后的密码
SELECT MD5('tian') FROM DUAL;
SELECT LENGTH(MD5('tian')) FROM DUAL;
-- 演示用户表 存放密码的时，是MD5
CREATE TABLE users1
(id INT,
`name` VARCHAR(32) NOT NULL DEFAULT '',
pwd CHAR(32) NOT NULL DEFAULT '');
INSERT INTO users1
VALUES( 100,'tian',MD5('tian'));
SELECT * FROM users1;
SELECT *
FROM users1
WHERE `name` = 'tian' AND pwd = MD5('tian');
-- pASSWORD(str) -- 加密 在MySQL 数据库 的用户密码是用 password 函数加密
SELECT PASSWORD('tian') FROM DUAL;
-- select * from mysql.user \G 从原文密码str 计算并返回密码字符串
-- 通常用于对 mysql 数据库的用户密码加密
-- mysql.user 表示 数据库.表的含义
SELECT * FROM mysql.user;四.流程控制函数 centrol.sql
-- 演示流程控制语句
-- IF(expr1,expr2,expr3) 如果expr1为true 则返回 expr2 否则返回 expr3
SELECT IF(TRUE,'北京','上海') FROM DUAL;
-- IFNULL(expr1,expr2) 如果expr1 不为空nuLL 则返回expr1 否则返回expr2
SELECT IFNULL(NULL,'hsp') FROM DUAL;
SELECT IFNULL ('jack','hsp') FROM DUAL;
-- select case when expr1 then expr2
-- when expr3 then expr4 else expr5 end; [类似多重分支]
-- 如果expr1 为true 则返回 expr2 如果 expr2 为 true 则返回expr4 否则返回 expr5
SELECT CASE
WHEN TRUE THEN 'jack'
WHEN FALSE THEN 'tom'
ELSE 'mary' END;
-- 查询 emp 表 如果 comm 是 null 则显示 0.0
-- 判断是否为空用 is null is not null
SELECT ename ,IF(comm IS NULL,0.0,comm)
FROM emp;
SELECT ename,IFNULL(comm,0.0)
FROM emp;
-- 如果 emp 表的job 是 CLERK 则显示 职员 如果是 MANAGER 则显示经理
-- 如果是 selesman 则显示 销售人员 其他正常显示
SELECT ename,(SELECT CASE
WHEN job = 'CLERK' THEN '职员'
WHEN job = 'MANAGER' THEN '经理'
ELSE job END)
FROM emp;五.MySQL 表查询—— 加强
- 使用where字句可以直接查询日期 比如 1999.1.1 后入职的员工
- 使用 link 操作符 % 表示0到多个逐字符 _ 表示单个字符
- 查询表结构 DESC
- order by 字句
-- 查询加强
-- 使用 where 字句 查询1992.1.1 后入职的员工
-- 在MySQL中日期类型可以直接比较
SELECT * FROM emp
WHERE hiredate > '1992-1-1';
-- 模糊查询 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 子句
-- 如何按照工资的从低到高的顺序，显示雇员的信息 ASC升序
SELECT * FROM emp
ORDER BY sal ASC;
-- 按照部门号升序而员工的工资降序排列 显示雇员信息
SELECT * FROM emp
ORDER BY deptno ASC, sal DESC;
分页查询
- 按雇员的id号升序取出，每页显示三条记录，请分别显示 第一页 第二页 第三页
- 基本语法： select ... limit start，rows 表示从 start + 1 行开始取 取出 rows 行，start 从0开始算起
-- 分页 查询
-- 按照雇员的id号升序取出，每页显示三条记录，请分别显示 第一页 第二页 第三页
-- 第一页
SELECT * FROM emp
ORDER BY empno
LIMIT 0,3;
SELECT * FROM emp
ORDER BY empno
LIMIT 3,3;
SELECT * FROM emp
ORDER BY empno
LIMIT 6,3;
SELECT * FROM emp
ORDER BY empno
LIMIT 9,3;
-- 推到一个公式
SELECT * FROM emp
ORDER BY empno
LIMIT 每页显示记录数* （第几页 -1） ,每页显示记录数
SELECT * FROM emp
ORDER BY empno DESC
LIMIT 10,5;
SELECT * FROM emp
ORDER BY empno DESC
LIMIT 20,5;使用分组函数和分组字句 group by
-- 演示 group by 分组字句
-- 显示每种岗位的雇员总数，平均工资
SELECT COUNT(*),AVG(sal),job
FROM emp
GROUP BY job;
-- 显示雇员总数，以及获得补助的雇员数
-- 获得补助的雇员数 就是 comm 列为非空的 null
-- count 统计的时候 如果该列的值为null 是不会统计的
SELECT COUNT(*),COUNT(comm)
FROM emp;
-- 统计经理有多少人
SELECT COUNT(*),COUNT(IF(comm IS NULL,NULL))
FROM emp;
-- 统计没有获得补助的雇员数
SELECT COUNT(*),COUNT(IF(comm IS NULL,1,NULL))
FROM emp;
SELECT COUNT(*),COUNT(*) - COUNT(comm)
FROM emp;
-- 显示管理者的总人数
SELECT COUNT(mgr) FROM emp;
SELECT COUNT(DISTINCT mgr) FROM emp;
-- 显示雇员工资的最大差值
-- max() - min()
SELECT MAX(sal) - MIN(sal) FROM emp;
SELECT * FROM emp;
数据分组的总结
如果 select 语句同时包含有 group by， having by ，limit， order by 那么他们的顺序是 group by , having,order by ,limit
SELECT cloumn1,cloumn2,cloumn3... FROM TABLE
GROUP BY cloumn 分组
HAVING CONDITION 过滤
ORDER BY COLUMN 排序
LIMIT START,ROWS 分页
-- 案例 请统计各个group by 部门的平均工资，
-- 并且是大于1000的 并且按照平均工资从高到底排序 order by
-- 取出前两行记录 limit
SELECT deptno, AVG(sal) AS avg_sal
FROM emp
GROUP BY deptno
HAVING avg_sal > 1000
ORDER BY avg_sal DESC
LIMIT 0,2;多表查询
所谓多表查询是指基于两个和两个以上得表查询。在实际开应用中，查询单个表可能不能满足你得需求（如下面得课程练习） 需要使用到（dept表和emp表）
在默认情况下：当两表查询时，规则
- 从第一张表中，取出每一行和第二张表的每一行进行一个组合，返回这个结果【含有两张表的所有列】
- 一共返回的记录数 第一张表的行数 * 第二章表表的行数
- 这样的多表查询默认处理返回的结果，称为笛卡尔积
- 解决这个多个表的关键就是要写出正确的过滤条件 where
- 多表查询的条件不能少于 表的个数 -1，否则会出现笛卡尔积 比如4 张表 条件要不少于三个
-- 多表查询
-- 显示雇员名看，雇员工资以及所在部门的名字【笛卡尔积]
-- 1.雇员名，雇员工资 来自 emp 表
-- 2.部门的名称 来自 dept表
-- 3.需要对 emp 和 dept 表进行查询
-- 4.当我们需要指定显示某个表的列是，需要 表.列名
SELECT ename,sal,dname
FROM emp,dept
WHERE emp.deptno = dept.deptno;
-- 如和显示部门号为10的部门名，员工名，和工资
SELECT ename,dname,sal
FROM emp,dept
WHERE emp.deptno = dept.deptno AND emp.deptno = 10;
-- 显示各个员工的姓名，工资，及其工资级别
-- 思路 姓名 工资 及其 工资级别 来自 emp
-- 工资级别 salgrade
-- 写sql 先写一个简单的 然后加上固定条件
SELECT ename, sal,grade
FROM emp , salgrade
WHERE sal BETWEEN losal AND hisal;自连接
自连接是指在同一张表的连接查询【将同一张表看作两张表】self.sql
-- 多表查询的 自连接
-- 显示公司员工和他的上级的名字
-- 员工的名字在emp 表 上级的名字 也在emp 表
-- 员工和上级是通过 emp 表的 mgr 列 关联
-- 自连接 特点
-- 把同一张表当作两张表使用
-- 需要给表取别名 表名 表的别名 不需要AS AS 是给列区取别名的时候使用
-- 如果列明名不明确 可以指定列的别名 列名 AS 列的别名
SELECT worker.ename AS '职员',boss.ename AS'上级'
FROM emp worker,emp boss -- 169 13*13
WHERE worker.mgr = boss.empno;
子查询 subquery.sql
子查询是指嵌入在其他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. 十号部门有那些工作岗位
-- 2. 把上面查询的结果当作子查询使用
SELECT DISTINCT job -- distinct 去重
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
-- 在 MySQL 中 判断两个指是否相等 使用 =
-- != <> 这两个都是不等
-- 查询ecshop 中各个类别中，价格最高的商品
-- 查询 商品表
-- 先得到 各个类别中，价格最高的商品 max + group by cat_id
SELECT cat_id,MAX(shop_price)
FROM ecs_goods
GROUP BY cat_id;
SELECT goods_id,cat_id,goods_name,shop_price
FROM ecs_goods;
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;
-- 把子查询当作一张零时表可以解决很多复杂的查询在多行子查询中使用all 操作符 allany.sql
-- 如何显示工资比部门30多的所有员工的工资高的员工的姓名 工资 部门号
-- all 和 any 的使用
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
)在多行子查询中使用any 操作符
-- 如何显示工资比部门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);多列子查询 manycolumn.sql
多列子查序则是指查询返回多个列数据的子查询语句
（字段1，字段2 ...) = (select 字段1，字段2 from ...)
-- 如何查询与smith的部门和岗位完全相同的所有雇员（并且不含smith本人）
-- 多列 子查询
-- 分析 1.要得到 smith 部门和岗位
SELECT deptno,job
FROM emp
WHERE ename = 'SMITH';
-- 2.把上面的查询当作一个子查询来使用并且使用多列子查询的语法进行匹配
SELECT *
FROM emp
WHERE (deptno,job) = (
SELECT deptno,job
FROM emp
WHERE ename = 'SMITH'
) AND ename != 'SMITH';
-- 如何查询与allen 部门和岗位完全相同的所有雇员
SELECT *
FROM emp
WHERE(deptno,job) = (
SELECT deptno,job
FROM emp
WHERE ename = 'ALLEN'
) AND ename != 'ALLEN';
SELECT * FROM student;
-- 查询与松江数学 英语 语文 成绩相同的学生
SELECT *
FROM student
WHERE (chinese,english,math） = (
SELECT chinese,english,math
FROM student
WHERE `name` = '张飞'
);
五.表的复制
自我复制数据（蠕虫复制）
有时，为类对某个sql 语句进行效率测试，我们需要海量数据时，可以使用此方法为表创建海量数据。copytab.sql
-- 表的复制
CREATE TABLE my_tab01
( id INT,
`name` VARCHAR(32),
sal DOUBLE,
job VARCHAR(32),
deptno INT);
DROP TABLE my_tab01;
DESC my_tab01;
-- 如何自我复制
-- 1.先把emp 表的记录复制到 my_tab01
INSERT INTO my_tab01
(id,`name`,sal,job,deptno)
SELECT empno,ename,sal,job,deptno
FROM emp;
SELECT * FROM my_tab01;
-- 2.自我复制
INSERT INTO my_tab01
SELECT * FROM my_tab01;
-- 如何删除一张表中重复的记录
-- 1.先创建一张表
-- 2.有重复的记录
CREATE TABLE my_tab02 LIKE emp; -- 把emp表的结构列 复制到my_tab02
DESC my_tab02;
INSERT INTO my_tab02
SELECT * FROM emp;
-- 考虑去掉重复的
-- 思路： 1.先创建一张零时表 my_tmp 该表的结构和my_tab02 相同
-- 2. 把my_tab 的记录 通过distinct 关键字处理后，把记录复制到my_tmp
-- 3. 清除掉 my_tab02 记录
-- 4.把 my_tmp 表的记录复制到 my_tab02
-- 5. drop 掉 my_tmp
SELECT * FROM my_tab02;
CREATE TABLE my_tmp LIKE my_tab02;
INSERT INTO my_tmp
SELECT DISTINCT * FROM my_tab02;
DELETE FROM my_tab02;
INSERT INTO my_tab02
SELECT * FROM my_tmp;
DROP TABLE my_tmp;
SELECT * FROM my_tmp;六.合并查询
有时在实际运用中，为了合并多个select语句的结果，可以使用集合操作符号 union, union all
1. union all
该操作符用于取得两结果相同的并集。当使用该操作符时，不会取消重复行
2.union
该操作符 与union all 相似 但是会自动去掉集中重复行
-- 合并查询
select ename,sal,job
from emp
where sal > 2500; -- 五条
select ename,sal,job
from emp
where job = 'MANAGER'; -- 三条
-- union all 就是将两个查询结果合并 不会去重
SELECT ename,sal,job
FROM emp
WHERE sal > 2500 -- 五条
union all
SELECT ename,sal,job
FROM emp
WHERE job = 'MANAGER'; -- 三条
SELECT ename,sal,job
FROM emp
WHERE sal > 2500 -- 五条
UNION
SELECT ename,sal,job
FROM emp
WHERE job = 'MANAGER';七.表的外连接
1.前面的查询，时利用where子句对两张表或者多张表，形成的笛卡尔积进行筛选，根据相关条件，显示所有匹配的记录，匹配不上则不显示
2.左外连接 （如果左侧表的表完全显示我们就说是左外连接）
基本语法 select ... from 表1 left join 表2 on 条件
3.右外连接 （如果右侧的表完全显示我们就说是右外连接）
基本语法 select ... from 表1 right join 表2 on 条件
-- 外连接
-- 列出部门名称和员工名称和工作，同时要求 显示那些没有员工的部门
SELECT dname,ename,job
FROM emp,dept
WHERE emp.deptno = dept.deptno
ORDER BY dname;
-- 创建 stu
CREATE TABLE stu
( id INT,
`name` VARCHAR(32)
);
INSERT INTO stu
VALUES( 1,'jack'),(2,'tom'),(3,'kity'),(4,'nono');
SELECT * FROM stu;
-- 创建 exam
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;
-- 右外连接 显示所有的成绩 如果没有名字匹配显示为空
SELECT `name`,stu.id,grade
FROM stu RIGHT JOIN exam
ON stu.id = exam.id;
小结： 在实际开发中绝大多数情况下 使用的是内连接
八.MySQL 约束
约束用于确保数据库的数据满足特定的商业规划
在MySQL中，约束包括：not null, unique,primary key,foreign key 和check 五种
primary key(主键） - 基本使用 字段名 字段名称 primary key 用于位于的表行的数据，当定义主键约束后，该列不能重复
主键的细节:
- primary key 不能重复而且不能为null
- 一张表最多只能有一个主键，但可以是复合主键
- 主键的指定方式 有两种
- 直接写在字段名后指定 ： 字段名 primakry key
- 在表定义最后写 primary key (列名）
- 使用 desc 表名，可以看到 primary key 的情况
- 在实际开发中，每个表往往都会设计一个主键
-- 主键的使用
-- id 那么 email
CREATE TABLE t17
(id INT PRIMARY KEY, -- 主键 id 添加不可重复
`name` VARCHAR(32),
email VARCHAR(32));
INSERT INTO t17
VALUES(1,'jack','kkkkk.com'),
(2,'tom','kkkkk.com');
SELECT * FROM t17;
INSERT INTO t17
VALUES(1,'hsp','kkkkk.com'); -- id 为主键 报错
-- 主键使用的细节讨论
-- primary key 不能重复而且不能为null
-- 一张表最多只能有一个主键，但可以是复合主键
-- 将id 和 name 合并成一个 复合主键
CREATE TABLE t19
(id INT, -- 主键 id 添加不可重复
`name` VARCHAR(32),
email VARCHAR(32),
PRIMARY KEY (id,`name`) -- 复合主键 只有这两个都不相同的时候添加不了
);
-- 主键的指定方式 有两种
-- 直接写在字段名后指定 ： 字段名 primakry key
-- 在表定义最后写 primary key (列名）
-- (在列后面直接指定，列写完后 统一指定，primary key
-- 使用 desc 表名，可以看到 primary key 的情况
DESC t20 -- 查看t20 的结果 显示约束情况
not null(非空)
如果在列上定义了 not null 那么当插入数据时，必须为列提供数据
字段名 字段类型 not null
unique (唯一）
当定义了唯一约束后，该列值时不能重复的
字段名 字段类型 unique
unique 使用细节
1.如果没有指定not null 则 unique 字段可以有多个 null,如果一个字段（列） 是 unique not null 使用效果 类似 primary key
2. 一张表可以有多个unique 字段
foreign key (外键）
用于定义主表和从表之间的关系：外加按约束要定义在从从表上，主表则必须具有之间的约束或是unique 约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或者是为 null(学生/班级）
FOREIGN KEY (本表字段名) REFERENCES
主表名 （主键名 或 unique 字段名）
-- 外键演示
-- 建主表 班级表 myclass
CREATE TABLE myclass
( 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 myclass(id));
-- 测试数据
INSERT INTO myclass
VALUES(100,'java'),(200,'web');
SELECT * FROM myclass;
INSERT INTO my_stu
VALUES(1,'tom',100);
INSERT INTO my_stu
VALUES(2,'hsp',300) -- 这里会失败 300 号班级不存在foreign key (外键） 细节说明
- 外键指向的表的字段，要求是 primary key 或者是unique
- 表的类型是 innodb 这样的表才支持外键
- 外键字段的类型要和主键字段的类型一致（长度可以不同）
- 外加按字段的值，必须在主键字段中出现过，或者为null [前提是外键字段允许为null]
- 一旦建立主键的关系，数据不能随意删除了
check
用于 强制行数据必须满足的条件，假设在sal 列上定义了check 约束，并要求sal列在1000~2000 之间就会显示错误
oracle 和SQL server 均支持check，但是mysql5.7目前还不支持check，只做语法校验，但不会生效。
-- check 的使用
-- 5.7 目前不支持check 只做语法校验，但不会生效
-- Oracle ,sql server 这两个数据库生效
-- 测试
CREATE TABLE t23(
id INT PRIMARY KEY,
`nane` 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;九.自增长
在某张表中，存在一个id列（整数） 我们希望在添加记录 的时候，该列从1开始，自动增长 increment.sql
字段名 整形 primary key aut_increment
-- 自增长的使用
-- 创建表
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 t24
values (null,'jack@qq.com','jack');
insert into t24
values (null,'tom@qq.com','tom');
insert into t24
(email,`name`) values ('hsp@qq.com','hsp');
select * from t24;自增长的细节
- 一般来说自增长是和primary key 配合使用的
- 自增长也可以单独使用（但是需要配合一个unique）
- 自增长修饰的字段为整数的（虽然小数也可以但是非常非常少这样使用）
- 自增长默认从1开始，也可以通过alter table 表名 auto_increment == xxx
- 如果你添加数据时，给自增长（列） 指定的值，则以指定的值为准 如果指定了自增长 就按照自增长的规则来添加数据
十.mysql索引
为了提高数据库性能，索引是物美价廉的东西，不用加内存，不用该程序，不用调sql,查询速度就可能提高百倍千倍。CREATE INDEX empno_index ON emp (empno) // 创建索引
索引的原理
索引的原理是通过建立索引来提高检索效率的一种方法。在信息检索领域，索引是指按照一定规则对信息进行分类和排序，以便于用户快速定位所需信息的一种数据结构。
MySQL中有三种类型的索引：B-Tree索引、哈希索引和全文索引。其中，B-Tree索引是最常用的一种，也是MySQL中默认的索引类型。B-Tree索引是基于B-Tree算法实现的，它将数据表中的每个值都存储在一个B-Tree中，并通过指针将它们链接起来。当我们执行查询时，MySQL会使用这些指针来快速定位到所需的数据。
索引的代价
- 磁盘占用
- 对dml(update delete insert)语句的效率影响
索引类型
- 主键索引 ，主键自动的为主索引（类型 primary key)
- 唯一缩影（unique)
- 普通索引（index)
- 全文索引（fulltext) [适用于myIsam] 一般开发不适应MySQL自带的全文索引 开发中考虑使用：全文索引solr 和 elasticsearch（ES)
-- 演示MySQL的索引使用
CREATE TABLE t26(
id INT,
`name` VARCHAR(32));
-- 查询表是否有索引
SHOW INDEXES FROM t26;
-- 添加索引
-- 添加唯一索引
CREATE UNIQUE INDEX id_index ON t26 (id);
-- 添加普通索引
CREATE INDEX id_index ON t26 (id);
-- 如和选择索引
-- 如果某列的值不重复 则优先使用unique 速度快 否则使用普通索引
-- 添加 普通索引方式2
CREATE TABLE t26 ADD INDEX id_index (id);
-- 添加组件索引 创建表的时候直接添加主键 primary key 或者
ALTER TABLE t26 ADD PRIMARY KEY (id);
-- 删除索引
DROP INDEX id_index ON t26;
SHOW INDEX FROM t26;
-- 删除主键索引
ALTER TABLE t26 DROP PRIMARY KEY;
-- 修改索引 先删除在添加新的索引
-- 查询索引 三种方式
-- 1
SHOW INDEX FROM t25;
-- 第二中方式
SHOW INDEXES FROM t25;
-- 方式三
SHOW KEYS FROM t25;
-- 方式 4
DESC t25; -- 信息没有前面三种方式详细小结： 那些列上适合使用索引
- 较频繁的作为查询条件字段应该创建索引 select*from emp where empno = 1
- 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件 select*from emp where sex= '男'
- 更新非常频繁的字段不适合创建索引 select * from emp where logincount = 1
- 不会出现在where 字句中字段不该创建索引
十一.MySQL 事务
什么时事务
事务用于保证数据的一致性，它是由一组相关的dml语句组成，该组的dml 语句要么全部成功，要么全部失败。如：转账就要用事务来处理，用以保证数据的一致性
事务和锁
当执行事务操作时（dml)语句，MySQL会在表上加锁，防止其他用户该表的数据，这是对用户来讲时非常重要的
- MySQL 数据库控制台事务的几个重要操作（基本操作 transaction.sqsl)
- start transaction -- 开始一个事务
- savepoint 保存点名 -- 设置保存点
- rollback to 保存点名 --退回事务
- rollback -- 退回全部事务
- commit -- 提交事务，所有的操作生效，不能回退
回退事务
保存点（savepoint） 保存点是事务中的点，用于取消部分事务，当事务结束时（commit)，会自动的删除该事务所有定义的节点。当执行回退事务时，通过指定保存的点
提交事务
使用commit语句可以提交事务，当执行了commit语句后，会确认事务的变换，结束事务，删除保存点，释放锁，数据生效。当使用commit语句结束了事务之后，其他会话将可以查看到事务变话【其他连接】后的新数据【所有的数据都正式生效】
MySQL 事务细节讨论 transcation_detail.sql
- 如果不开始事务，默认情况下，dml操作是自动提交的，不能回滚
- 如果开始一个事务，你没有创建保存点，你可以执行rollback，默认就是回退到你开始的状态
- 你也可以在这个事务中（还没有提交时）创建的个保存点 比如：savepoint aaa; 执行 dml,savepoint bbb;
- 你可以在事务没有提交前，退回到那个保存点
- MySQL的事务机制需要innodb 的存储引擎还可以使用，myisam不好使
- 开始一个事务start transaction, set autocommit = off;
-- 事务几个重要的概念和操作
-- 1.创建一张测试表
CREATE TABLE t27 (
id INT,
`name` VARCHAR(32));
-- 开始一个事务
START TRANSACTION;
-- 设置一个保存点
SAVEPOINT a;
-- 执行dml操作
INSERT INTO t27
VALUES (100,'tom');
SAVEPOINT b;
-- 执行 dml 操作
INSERT INTO t27
VALUES(200,'jack');
-- 回退到 b
ROLLBACK TO b;
-- 回退到 a
ROLLBACK TO a;
SELECT * FROM t27;
-- 如果这样,表示直接回退事务开始的状态
ROLLBACK,
-- commit 提交事务 一旦执行了commit 在rollback 无法回退
COMMIT;十二.mysql 的事务隔离级别
事务隔离级别介绍
- 多个连接开启各自事务操作数据库中的数据时，数据库系统要复制隔离操作，以保证各个连接在获取数据时的准确性。
- 如果不考虑隔离性，可能会引发如下问题： 脏读，不可重复读，幻读
- 脏读：当一个事务读取另外一个事务尚未提交修改时，产生脏读
- 不可重复读：同一查询在同一事务中出现多次运行，由于其他事务所作的修改或删除，每次返回不同的结果集，此时发生不可重复读
- 幻读 同一查询在同一事务中多次进行，由于其他提交事务所做的插入操作，每次返回不同的结果集，此时发生幻读
事务隔离级别 概念：mysql 隔离级别定义可事务与事务之间的隔离程度
MySQL事务隔离级别
- 查询当前会话隔离级别 select @@tx_isolation;
- 查询系统当前隔离级别 select @@global.tx_isolation;
- 设置当前会话隔离级别 set seccion transaction isolation level repeatable read;
- 设置系统当前隔离级别 set global transaction isolation level repeatable read;
- mysql 默认的事务隔离级别是 repeatable read, 一般情况下，没有特水要求，没有必要修改（因为该级别可以满足绝大部分项目的要求）
MySQL事务ACID
事务的acid特性
- 原子性 原子性是指事务时一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生
- 一致性 事务必须使数据库从一个一致性状态转变到另外一个一致性状态
- 隔离性 事务的隔离性是多个 用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据干扰，多个并发事务之间要相互隔离
- 持久性 持久性是指一个事务一旦被提交，他对数据库中的改变就是永久性的，接下来即使数据库发生故障也不应该对其由任何影响
十二.MySQL 表类型和存储引擎
- MySQL的表的类型由存储引擎（Storage Engines) 决定，主要包括 MyISAM，innodb,Menmory等
- MySQL 数据表主要支持六种类型 分别是 CSV Memory ARCHIVE，MEG_MYISAM,MYISAM,innoBDB
- 这六种又分为两类，一类是“事务安全型” 比如：innodb 其余两种都属于第二类，称为“非事务安全型”（non_transaction-safe) 【MYSIAM和memory]
细节说明：
这里介绍三种：MyISAM,innoDB，MEMORY
- MyISAM 不支持事务，也不支持外键，但其访问速度快，对事物完整性没有要求
- innodb 存储引擎提高了具有提交，会滚和崩溃恢复能力的事物安全。但是比起MyISAM存储引擎，InnoDB写的处理效率差一些并且会占有更多的磁盘空间以保留数据和索引
- MEMORY 存储引擎使用在内存的内容来创建表。每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常快，应为他的数据是放在内存中的，并且默认使用HASH索引。但是一旦服务关闭，表中的数据就丢失，表的结构还在。
-- 表类型和存储引擎
-- 查看说有的存储引擎
SHOW ENGINES;
-- innodb 存储引擎前面使用过了 支持事物和外键 支持行级锁
-- myisam
CREATE TABLE t28 (
id INT,
`name` VARCHAR(32))ENGINE MYISAM;
-- 添加速度快 不支持外键和索引 支持表级锁
START TRANSACTION;
SAVEPOINT t1;
INSERT INTO t28 VALUES(1,'jack');
ROLLBACK TO t1; -- 不支持回滚
SELECT * FROM t28;
-- memory 存储引擎 数据是存储在内存当作的执行速度非常快（在内存中没有 io 读写） 默认支持索引（hash表）
CREATE TABLE t29(
id INT,
`name` VARCHAR(32)) ENGINE MEMORY;
INSERT INTO t29
VALUES(1,'tom'),(2,'jack'),(3,'hsp');
-- 如果 关闭MySQL 服务，数据丢失，但是表结构还在
SELECT * FROM t29;如何选择表的存储引擎
- 如果你的应用不需要事物，处理的指示基本的CRUD操作，那么MyISAM是不二选择
- 如果需要支持事物，选择InnoDB
- Memory 存储引擎就是将数据存储在内存中，由于没有磁盘 I/O 的等待，速度极快。但是由于内存存储引擎，所作的任何修改在服务器重启后都将消失（经典用法 用户的在线状态().)
修改存储引擎
ALTER TABLE `表名` ENGINE = 存储引擎；
十二.MySQL视图 view
视图，比如说看emp表的列信息很多，有些信息是个人重要信息（比如：sal,comm,mgr,hiredate) 如果我们希望某个用户只能查询emp表的（empno，ename,job和deptno)信息 可以使用视图
视图的基本概念：视图是一个虚拟表，其内容由查询定义，同真实的表一样，视图包含列，其数据来自对应得真实表（基表）
视图的总结：
- 视图时根据基表来创建的（可以是多个基表）来创建的 是虚拟的表
- 视图也有列数据来自于基表
- 通过视图可以修改基表的数据
- 基表的改变也会影响到视图的数据
视图的基本使用：
- create view 视图名 as select 语句 创建视图
- alter view 视图名 as select 语句 修改视图 更新为新的视图
- SHOW CREATE VIEW 视图名 显示当时用什么指令创建视图的
- drop view 视图1，视图2 删除视图
视图的细节讨论
- 创建视图后，到数据库去看，对应视图只有一个视图结构文件（形式：视图名.frm)
- 视图的数据变换会形象到基表，基表的数据变化也会影响到视图[insert update delete]
- 视图中可以再使用视图
-- 视图的使用
-- 创建一个视图emp_view01 只能查询emp 表的 empno ename job deptno 信息
CREATE VIEW emp_view01
AS
SELECT empno,ename,job,deptno FROM emp;
DESC emp_view01;
SELECT empno,job FROM emp_view01;
-- 查看创建视图的指令
SHOW CREATE VIEW emp_view01;
-- 删除视图
DROP VIEW emp_view01;
-- 视图的细节
-- 创建视图后，到数据库去看，对应视图只有一个视图结构文件（形式：视图名.frm)
-- 视图的数据变换会形象到基表，基表的数据变化也会影响到视图[insert update delete]
-- 修改视图
UPDATE emp_view01
SET job = 'MANAGER'
WHERE empno = 7369;
SELECT * FROM emp; -- 查询基表
-- 修改视图会影响到基表
SELECT * FROM emp_view01;
-- 修改基表也会影响到基表
UPDATE emp
SET job = 'SALESAN'
WHERE empno = 7369;
-- 视图中可以再使用视图
DESC emp_view01;
-- 从emp_view01 当中选出两列 no 和 ename 做出新视图
CREATE VIEW emp_view02
AS
SELECT empno,ename
FROM emp_view01;
SELECT * FROM emp_view02;
视图最佳实践
- 安全，一些数据表有着重要的信息。有些字段是保密的，不让用户直接看到。这时就可以创建一个视图，在这张视图中只保留一部分字段。这样，用户就可以查询自己所需要的字段，不能查看保密的字段。
- 性能。 关系数据库的数据常常会分表存储，使用外键建立这些表的之间关系。这时，数据库查询通常会用到连接（JOIN）这样做不但麻烦，效率相对也较低。如果建立一个视图，将相关的表和字段组合在一起，就可以避免使用JOIN查询数据
- 灵活。如果系统中有一张旧的表，这张表由于设计的问题，即将被抛弃。然而，和多应用都是基于这张表，不易修改。这是就可以建立一张视图，视图中的数据直接映射到新建的表。这样，就可以少很多改动，也达到了升级数据表的目的。
十三.MySQL管理
MySQL用户
MySQL中的用户，存储在系统数据库mysql中user 表中
其中user表的重要字段说明：
- host: 允许登录的“位置”，localhost 表示该用户只允许本机登录，也可以指定ip地址，比如：192.168.1.100
- user: 用户名;
- authentication_string: 密码,时通过MySQL的password() 函数加密之后的密码
创建用户
create user ‘用户名’ @ ‘允许登录的位置’ inentified by '密码'
说明：创建用户，同时指定密码
删除用户
drop user '用户名' @ 允许登录的位置；
用户修改密码
修改自己的密码： set password = password('密码'）；
修改他人的密码： （需要修改权限）：
set password for '用户名' @ '登录位置' = password('密码');
MySQL中的权限
给用户授权
基本语法：
grant 权限列表 on 库.对象名 to '用户名' @ '登录位置' 【identified by '密码'】
说明：
- 权限列表，多个权限用逗号分开 grant select on ...... grant select,delect,create on...... grant all [privilleges] on ...... // 表示赋予该用户在对象上的所有权限
- 特别说明 *.*代表本系统中的所有数据库的所有对象（表，视图，存储过程）库.*:表示某个数据库中的所有数据对象（表，视图，存储过程等）
- identified by 可以省略，也可以写出 如果用户存在，就是修改该用户的密码。 如果该用户不存在，就是创建该用户！
回收用户权限
基本语法： revoke 权限列表 on 库.对象名 from '用户名' @ ''登录位置''；
权限生效指令
如果权限没有生成，可以执行下面指令。
基本语法：
FLUSH PRIVILEGES;
-- 演示用户权限的管理
-- 创建一个用户（你的你的名字 tian 密码 123
CREATE USER 'diandian' @'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;
-- 给diandain 用户分配查看news表 和 添加 news 的权限
GRANT SELECT,INSERT
ON testdb.news
TO 'diandian' @'localhost';
-- 增加权限
GRANT UPDATE
ON testdb.news
TO 'diandian' @'localhost';
-- 修改密码为123
SET PASSWORD FOR 'diandian'@'localhost' = PASSWORD('abc');
-- 回收 diandian 用户 testdb.news 表的所有权限
REVOKE SELECT ,UPDATE,INSERT ON testdb.news FROM 'diandian'@'localhost';
-- 或者
REVOKE ALL ON testdb.news FROM 'diandian'@'localhost'
-- 刷新指令
falush PRIVILEGES;
-- 删除 点点这个用户
DROP USER 'diandian'@'localhost'细节说明：
- 在创建用户的时候，如果不指定Host,则为%,% 表示所有ip都可以连接权限，create user XXX;
- 你也可以这样指定 create user 'XXX' @'192.168.1.%' 表示xxx 用户在192.168.1* 的ip可以登录MySQL
- 在删除用户的时候，如果host 不是%,需要明确指定’用户‘@'host值'
-- 说明 用户管理的细节
-- 在创建用户的时候，如果不指定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 'tiantian'@'192.168.1.%'
-- 在删除用户的时候，如果host 不是%,需要明确指定’用户‘@'host值'
DROP USER jack -- 默认就是 drop user 'jack'@'%'
DROP USER tiantian -- 失败
DROP USER 'tiantian'@'192.168.1.%'
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
