目录
1.Mysql 三层结构
安装mysql数据库就是在主机安装一个数据库管理系统DBMS，这个管理程序可以管理多个数据库，一个数据库中可以创建多个表，已保存数据信息。
#注释的快捷键 shift+ctrl+c,注销注释shift+ctrl+r
2.数据定义语言DDL
2.1创建数据库
create database2.2查询数据库
SHOW DATABASES2.3创建表
create table table_name2.4修改表
2.4.1 修改表名
-- 方式一：只能修改一个表的名
ALTER TABLE `旧表名` RENAME TO `新表名`;
-- 方式二：rename同时修改多个表名
RENAME TABLE `旧表名` TO `新表名`,`旧表名` TO `新表名`;2.4.2 修改字段
--添加列
alter table `表名` add
-- 修改字段的数据类型
alter table `表名` modify `字段名` `该字段修改后的属性`;
-- 修改字段名
alter table `表名` change `旧字段名` `新字段名` `旧字段的属性`;
--添加列
alter table `表名` add
-- 删除列
alter table `表名` drop 2.5 删除表
drop table if exists `表名1`,表名2`, 表名n`;3.数据操作语句 DML
3.1插入数据
insert into 表名 values('值1'), ('值2', ('值3')
insert注意事项：
插入的数据应与字段的数据类型相同；
数据的长度应在列的规定范围内；
在values中列出的数据位置必须与被加入的列的排列位置相对应；
字符和日期型数据应该包含在单引号中；
列可以插入空值，前提是该字段允许为空，insert into table_name values (null);
insert into table_name (列名。。。)values （），（），（）形式添加多条记录；
如果是给表中的所有字段添加数据，可以不写前边的字段名称；
默认值的使用，当不给某个字段值时，如果有默认值就会添加。否则报错。如果某个列没有指定not null，那么当添加数据时，没有给定值，则会默认null。如果我们希望指定某个列的默认值，可以在创建表时指定。
3.2更新数据
update 表名 set 字段名=新值 where 筛选条件;
update使用细节：
update 语法可以用新值更新原有表行中的各列，SET子句指示要修改哪些列和要给予哪些值
WHERE子句指定应更新哪些行，如没有where子句，则更新所有行。
3.3删除数据
DELETE FROM 表名 [WHERE 子句]〔ORDER BY 子句][LIMIT 子句]
delete使用细节：
如果不使用where子句，将删除 表中所有的数据；
delete语句不能删除某一列的值（可使用update 语句设为null或者‘’）；
使用detele语句仅删除记录，不删除表本身。如果要删除表，使用drop table_name 语句
4.数据查询语句 DQL
4.1基本语法
select [distinct] `字段名` [ as 别名] from `表名`;
distinct 表示过滤表中重复数据。
4.2条件查询
举例： 查询数学分数为89,90,91的同学
SELECT * FROM student
WHERE math=89 OR math=90 OR math=91
SELECT * FROM student
WHERE math IN(89,90,91);
4.3模糊查询
举例： 查询总分大于200分并且数学成绩小于语文成绩的姓赵的学生，赵%表示名字以赵开头的就可以
SELECT * FROM student
WHERE (chinese+english+math) >200
AND
math<chinese AND `name`LIKE '赵%'like结合使用的通配符 :
% (代表匹配任意长度的字符 相当于…)
_ (代表匹配任意一个字符 相当于.)
4.4排序查询
SELECT 字段名 FROM 表名 ORDER BY 排序字段名 [ASC|DESC];
ASC | DESC：ASC表示字段按升序排序；DESC表示字段按降序排序。其中ASC为默认值
-- 对姓韩的学生成绩排序输出（升序）
SELECT `name` ,(chinese+english+math) AS total_score FROM student
WHERE `name` LIKE'韩%'
ORDER BY total_score; ORDER BY子句一般放到查询语句的最后面来做,并且允许使用函数, LIMIT字句除外
当排序的字段中存在空值时，ORDER BY会将该空值作为最小值来对待
ORDER BY指定多个字段进行排序时，MySQL会按照字段的顺序从左到右依次进行排序
4.5聚合查询
SELECT 聚合函数 FROM 表名 [WHERE条件]
count（*）和count（列）的区别：
count（*）返回满足条件的记录的行数；count(列)统计满足条件的某列有多少个，但是会排除为null
举例: 统计数学成绩大于90的学生有多少个？
SELECT COUNT(*)FROM student
WHERE math>90合计函数-sum的使用
-- 统计一个班级语文、英语、数学各科的总成绩
SELECT SUM(Chinese), SUM(english) ,SUM(math) FROM student;AVG 函数
-- 求一个班级总分平均分
SELECT AVG(chinese+english+math) FROM student;合计函数-max/min
-- 求班级最高分和最低分（数值范围在统计中特别有用）
SELECT MAX(chinese+math+english),MIN(chinese+math+english) FROM student;其中 COUNT函数可用于任何数据类型，而SUM、AVG函数都只能对数值类型做计算，MAX和MIN可用于数值、字符串或是日期时间数据类型。
4.6 分组查询
SELECT 分组字段/聚合函数 FROM 表名 [WHERE条件] GROUP BY 分组字段 [HAVING分组后条件];
举例： 显示平均工资低于2000 的部门号和它的平均工资//别名
SELECT AVG(sal) AS avg_sal , deptno
FROM emp GROUP BY deptno
HAVING avg_sal<2000; WHERE和HAVING区别:
WHERE：是在分组之前使用(可以没有GROUP BY)，不允许使用统计函数
HAVING：是在分组之后使用(必须结合GROUP BY)，允许使用统计函数
4.7分页查询
起始索引从0开始，起始索引=(查询页码-1)*每页显示记录数
基本语法:select...limit start,row 表示从start +1行开始取，取出rows行，start从0开始 计算
举例：按雇员的id号升序取出，每页显示3条记录，请分别显示 第1页，第2页，第3页
-- 第1页
SELECT * FROM emp
ORDER BY empto
LIMIT 0,3
-- 第2页
SELECT * FROM emp
ORDER BY empto
LIMIT 3,3
-- 第3页
SELECT * FROM emp
ORDER BY empto
LIMIT 6,3
5.函数
5.1数据函数
演示数学相关函数
-- 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) 进制转换
SELECT CONV(8,10,2)FROM DUAL;
-- FLOOR (number2) 向下取整，得到比num2小的 最大整数
SELECT FLOOR (2.2)FROM DUAL;
-- FORMAT(number,decimal_place) 保留小数位数
SELECT FORMAT(78.123456,2)FROM DUAL;
-- HEX （decimalnumber ）转十六进制
-- LEAST(number,number2 [,...])求最小值
SELECT LEAST (0,-1,-2,-3,4,5,7)FROM DUAL;
-- MOD（numerator,denominator） 求余
SELECT MOD(3,12)FROM DUAL;
-- RAND（[seed]）RAND([seed])返回随机数其范围为0≤v≤1.0
-- 如果使用rand()每次返回不同的随机数，在0≤v≤1.0
-- 如果使用rand(seed)返回随机数，范围在0≤v≤1.0，如果seed不变，该随机数也不变。
SELECT RAND()FROM DUAL;5.2字符串函数
#演示字符串相关函数的使用,使用emp表来演示
-- CHARSET(str) 返回字串字符集
SELECT CHARSET(ename)FROM emp
-- CONCAT(string2 [,...])连接字串，将多个列拼接成一列
SELECT CONCAT(ename,'job is',job )FROM emp;
-- INSTR (string ,substring)返回substring 在string中出现的位置，没有返回0
-- dual亚元表，系统表，可以作为测试表使用
SELECT INSTR ('hanshunping','ping')FROM DUAL;
-- UCASE (string2) 转换成大写
SELECT UCASE (ename)FROM emp;
-- LCASE (string2) 转换成小写
SELECT LCASE (ename)FROM emp;
-- LEFT (string,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
SELECT ename,REPLACE (job,'MANAGER','经理') FROM emp;
-- STRCMP(string1,string2) 逐字符比较两字串大小，
SELECT STRCMP ('zdd','zdd')FROM DUAL;
-- SUBSTRING（str,position[,length]）从str的position开始[从1开始计算]，取length个字符
SELECT SUBSTRING(ename, 1, 2) FROM emp;
-- LTRIM(string2) RTRIM(string2) TRIM(string) 去除前段空格或后端空格
SELECT LTRIM(' 小土豆真好吃')FROM DUAL;
SELECT RTRIM('小土豆真好吃 ')FROM DUAL;
SELECT TRIM(' 小土豆真好吃 ')FROM DUAL;
#练习：以首字母小写的方式显示所有员工emp表的姓名
SELECT CONCAT(LCASE( SUBSTRING(ename,1,1)),SUBSTRING(ename,2))AS new_name
FROM emp;5.3日期和时间函数
-- 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),sendtime DATETIME);
INSERT INTO mes
VALUES(1,'北京新闻',CURRENT_TIMESTAMP());
INSERT INTO mes
VALUES(2,'上海新闻',NOW());
INSERT INTO mes
VALUES(3,'广州新闻',NOW());
ALTER TABLE mes
CHANGE sendtime send_time DATETIME
SELECT*FROM mes
#练习
-- 显示所有留言信息，发布日期只显示日期，不用显示时间
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语句求出你活了多少天
SELECT DATEDIFF('2023-09-17','1996-11-20') FROM DUAL
-- 如果你能活到80岁，求出你还能活多少天
SELECT DATEDIFF (DATE_ADD('1996-11-20',INTERVAL 80 YEAR),NOW()) FROM DUAL;
-- YEAR |MONTH|DAY|DATE(DATETIME)
SELECT YEAR(NOW())FROM DUAL;
SELECT MONTH(NOW())FROM DUAL;
SELECT DAY(NOW())FROM DUAL;
SELECT DATE(NOW())FROM DUAL;
-- UNIX_TIMESTAMP():返回的是1970-1-1到现在的秒数
SELECT UNIX_TIMESTAMP()FROM DUAL
-- FROM_UNIXTIME() :可以把一个unix_timestamp 秒数，转成指定格式的日期
SELECT FROM_UNIXTIME(1694946396,'%Y-%m-%d %H:%i:%s')FROM DUAL;
5.4加密函数和系统函数
-- USER() 查询用户
SELECT USER() FROM DUAL;-- 用户@IP地址
-- DATABASE() 查询当前使用的数据库名称
SELECT DATABASE();
-- MD5（str） 为字符串算出一个MD5 32的字符串，（用户密码）加密
SELECT MD5('123456')FROM DUAL
SELECT LENGTH (MD5('123456'))FROM DUAL
-- 演示用户表，存放密码时，是MD5
CREATE TABLE users
(id INT,`name` VARCHAR(32)NOT NULL DEFAULT '',pwd CHAR(32)NOT NULL DEFAULT'');
INSERT INTO users
VALUES(100,'周周',MD5('zdd'));
SELECT *FROM users
WHERE`name`='周周'AND pwd='zdd'-- 这种查询方法查不到
SELECT *FROM users
WHERE`name`='周周'AND pwd = MD5('zdd')
-- PASSWORD(str)-- 加密函数 这个版本移除了password函数，所以报错
SELECT PASSWORD('') FROM DUAL;
-- select *from mysql.user\G 从原文密码str计算并返回密码字符串，通常用于对mysql数据库的用户密码加密
SELECT *FROM mysql.user5.5流程控制函数
#IF（expr1,expr2,expr3）如果expr1为True,则返回expr2否则返回expr3
SELECT IF (TRUE,'北京','上海')FROM DUAL;
SELECT IF (FALSE,'北京','上海')FROM DUAL;
#IFNULL (expr1,expr2) 如果expr1不为空null,则返回expr1,否则返回expr2
SELECT IFNULL (NULL,'周氏厨房')FROM DUAL;
SELECT IFNULL ('李氏饭店','周氏厨房')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 expr1 then epxr2 when expr3 then expr4 else expr5 end
SELECT CASE WHEN TRUE THEN 'emma' WHEN FALSE THEN 'tom' ELSE 'mary' END
-- 查询emp表，如果comm是null，则显示0.0
-- 说明：判断是否为null要使用is null，判断不为空 使用is not null
SELECT ename,IF (comm IS NULL,0.0,comm) FROM emp;
SELECT ename,IFNULL(comm,0.0)FROM emp;
-- 查询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;6.多表查询
6.1多表笛卡尔集
多表查询是指基于两个和两个以上的表查询。在实际应用中，查询单个表可能不能满足你的需求。
说明：在默认情况下，当两个表查询时，从第一张表中取出一行与第二张表中的每一行进行组合，返回结果
SELECT * FROM emp，dept 一共返回的记录数 第一张表行数*第二张表的行数
-- 应该用where子句过滤 当我们需要指定显示某个表的列时，需要 表.列名
-- 多表查询的条件不能少于 表的个数-1
#多表查询 练习
-- 显示雇员名、雇员工资和所在部门的名字(笛卡尔集)
SELECT ename,sal,dname
FROM emp,dept
WHERE emp.`deptno`=dept.`deptno`
-- 如何显示部门号为10的部门名、员工名和工资
SELECT dname,ename,sal ,emp.`deptno`
FROM emp,dept
WHERE emp.`deptno`=dept.`deptno` AND emp.`deptno`=10
-- 显示各个员工的姓名、工资、及其工资的级别
SELECT ename,sal,grade
FROM emp,salgrade
WHERE sal BETWEEN losal AND hisal6.2自连接
自连接是指在同一张表的连接查询，将同一张表看做两张表,需要给表取别名
-- 显示公司员工和他的上级的名字-- 分析员工和上级是通过emp表的mgr列关联
SELECT worker.ename AS '员工名',boss.ename AS'上级名'
FROM emp worker,emp boss
WHERE worker.mgr=boss.empto6.3合并查询
union有时在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号
union有时在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号
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'
-- union 该操作符与union all相似，但是会自动去掉结果集中重复行
SELECT ename,sal ,job FROM emp WHERE sal>2500
UNION
SELECT ename,sal,job FROM emp WHERE job='MANAGER'6.4内连接和外连接
#外连接需求
-- 列出部门名称和这些部门的员工名称和工作，同时要求显示出那些没有员工的部门
-- 使用学习过的多表查询的SQL
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`
-- 练习：列出部门名和这些部门的员工信息（名字和工作），同时列出那些没有员工的部门
-- 使用左外连接实现-- 注意两张表的顺序
SELECT dname,ename,job
FROM dept LEFT JOIN emp
ON emp.`deptno`=dept.`deptno`
-- 使用右外连接实现
SELECT dname,ename,job
FROM emp RIGHT JOIN dept
ON emp.`deptno`=dept.`deptno`7.子查询
7.1单行子查询
子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询
单行子查询 是指只返回一行数据的子查询语句
-- 练习：如何显示与SMITH同一部门的所有员工
SELECT *
FROM emp
WHERE deptno=(SELECT deptno
FROM emp
WHERE ename='SMITH')7.2多行子查询
多行子查询指返回多行数据的子查询
IN 判断是否在集合内
=ANY 判断是否在集合内(和IN一样)
>ANY 大于集合中值最小的那一个
<ANY 小于集合中值最大的那一个
<>ANY 与集合中任意一个不等
=ALL 与集合中每个值都相等
>ALL 比集合中最大的哪个值还大
<ALL 比集合中最小的那个值还小
<>ALL 与集合中每个都不相等的
-- 练习；如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号 ，但是不含 10自己的。
SELECT ename,job,sal ,deptno
FROM emp
WHERE job IN (SELECT DISTINCT job
FROM emp
WHERE deptno=10)AND deptno!=10;#在多行子查询中使用all操作符
-- 如何显示工资比部门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)
# 在多行子查询中使用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)
7.3多列子查询
多列子查询是指查询返回多个列数据的子查询语句
-- 如何查询与smith的部门和岗位完全相同的所有雇员(并且不包含smith本人)
SELECT *
FROM emp
WHERE (deptno,job)=( SELECT deptno,job
FROM emp
WHERE ename='SMITH')AND ename!='SMITH'
-- 练习：请查询和宋江数学、英语、语文完全相同的学生
SELECT*
FROM student
WHERE (math,english,chinese)=(
SELECT math,english,chinese
FROM student
WHERE `name`='宋江')
7.4表子查询
-- 查找每个部门工资高于本部门平均工资的人的资料（把子查询当做临时表
SELECT *
FROM emp,(
SELECT deptno,AVG(sal)AS avg_sal
FROM emp
GROUP BY deptno )temp
WHERE emp.`deptno`=temp.deptno AND emp.sal>temp.avg_sal
-- 查找每个部门工资最高的人的详细资料
SELECT *
FROM emp,(SELECT deptno,MAX(sal)AS max_sal
FROM emp
GROUP BY deptno)temp
WHERE emp.`deptno`=temp.deptno AND emp.sal=temp.max_sal
-- 查询每个部门的信息（包括：部门名，编号，地址）和人员数量
SELECT COUNT(*),deptno
FROM emp
GROUP BY deptno
SELECT dname,dept.deptno,loc,temp.num
FROM dept,(SELECT COUNT(*) AS num ,deptno
FROM emp
GROUP BY deptno)temp
WHERE dept.`deptno`=temp.deptno7.5表复制和去重
-- 为了对某个sql语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据
CREATE TABLE my_tab01
(id INT,`name`VARCHAR(32),sal DOUBLE,job VARCHAR(32),deptno INT);
SELECT *FROM my_tab01
-- 演示如何自我复制
-- 1.先把emp表的记录复制到my_tab01
INSERT INTO my_tab01
(id,`name`,sal,job,deptno)
SELECT empto,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_tmp,该表的结构和my_tab02一样
-- 把my_tmp的记录，通过distinct关键字处理后把记录复制到my_tmp
-- 清除掉my_tab02 记录
-- 把my_tmp的记录复制到my_tab02
-- drop掉临时表my_tmp
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_tab028.约束
Mysql约束 ：约束用于确保数据库的数据满足特定的商业规则
在mysql中，约束包括：not null.unique.primary key.foreign key和check五种
8.1主键约束 primary key
primary key 主键(基本使用) 用于唯一的标示表行的数据，当定义主键约束后，该列不能重复
在实际开发中，每个表往往都会设计一个主键。
CREATE TABLE t6
(id INT PRIMARY KEY ,-- 表示id列是主键
`name` VARCHAR(32),
email VARCHAR(32))
INSERT INTO t6
VALUES(1,'jack','jack@sohu.com')
INSERT INTO t6
VALUES(2,'tom','tom@sohu.com')
INSERT INTO t6
VALUES(1,'zdd','hsp@sohu.com')-- 会报错，1已经存在
-- 主键使用的细节讨论
--1. primary key 不能重复而且不能为null
INSERT INTO t6
VALUES(NULL,'jack','jack@sohu.com')-- 会报错，不能为null
-- 2.一张表最多只能有一个主键，但可以是复合主键
CREATE TABLE t7
(id INT PRIMARY KEY ,-- 表示id列是主键
`name` VARCHAR(32)PRIMARY KEY,
email VARCHAR(32))-- 不允许两个主键
-- 但是可以复合主键（id+name）
CREATE TABLE t8
(id INT ,
`name` VARCHAR(32),
email VARCHAR(32),
PRIMARY KEY(id,`name`)) -- 表示复合主键
INSERT INTO t8
VALUES(1,'jack','jack@sohu.com')
INSERT INTO t8
VALUES(1,'zdd','zdd@sohu.com') -- 这样就可以添加了，只有id和name都重复才不可以
-- 3.主键的指定方式有两种
-- 1.直接在字段后指定 ：字段名，primary key
CREATE TABLE t8
(id INT ,
`name` VARCHAR(32),PRIMARY KEY
email VARCHAR(32))
-- 2.在表定义最后写primary key(列名)
CREATE TABLE t8
(id INT ,
`name` VARCHAR(32),
email VARCHAR(32)
PRIMARY KEY(`name`) )
-- 使用desc 表名，可以看到primary key的情况
DESC t8-- 查看t8表的结果，显示约束的情况
8.2非空约束 not null
not null（非空）如果在列上定义了not null，那么当插入数据时，必须为列提供数据
-- 字段名 字段类型 not null
8.3唯一约束 unique
unique(唯一)当定义了唯一的约束后，该列值是不能重复的
-- 字段名 字段类型 unique
CREATE TABLE t9
(id INT UNIQUE ,-- 表示id这一列是不能重复的
`name` VARCHAR(32),
email VARCHAR(32))
INSERT INTO t9 VALUES (1,'jack','jack@sohu.com');
INSERT INTO t9 VALUES (1,'jack','jack@sohu.com');-- 会报错，id列不能重复
-- unique使用细节
-- 如果没有指定not null,则unique字段可以有多个null
-- 如果一个列（字段），是unique not null 使用效果类似primary key
INSERT INTO t9 VALUES (NULL,'jack','jack@sohu.com');-- 成功执行，因为id列没有指定not null
SELECT *FROM t9
-- 一张表可以有多个unique字段
CREATE TABLE t10
(id INT UNIQUE ,-- 表示id这一列是不能重复的
`name` VARCHAR(32) UNIQUE,-- 表示name这一列是不能重复的
email VARCHAR(32))
DESC t108.4外键约束 foreign key
foreign key(外键) 用于定义主表和从表之间的关系：外键约束定义在从表上，主表则必须具有主键约束或是unique约束。
当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为 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_stu VALUES (1,'tom',100);
INSERT INTO my_stu VALUES (2,'jack',200);
INSERT INTO my_stu VALUES (3,'zdd',300);-- 这条会报错，因为300班级不存在
-- 如果在主表增加300班级，从表的这条插入就会成功
INSERT INTO my_class VALUES (300,'ps');
INSERT INTO my_stu VALUES (3,'zdd',300);-- 这时就成功了
-- foreign key 细节说明
-- 外键指向的表的字段，要求是primary key或者是unique
-- 表的类型是innodb,这样的表才支持外键
-- 外键字段的类型要和主键字段的类型一致（长度可以不同）
-- 外键字段的值，必须在主键字段中出现过，或者为null(前提是外键字段允许为null)
-- 一旦建立主外键的关系，数据就不能随意删除了。
8.5检查约束 check
#-- check 用于强制执行数据必须满足的条件，假定在sal列上定义了check约束，
并要求sal列值在1000-2000之间 如果不在1000-2000之间就会提示出错。
CREATE TABLE t11(
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 t11 VALUES(1,'jack','mid',1) -- mysql8.0版本已经支持这个约束了
SELECT *FROM t11 8.6设置字段值自动增加 AUTO_INCREMENT
#自增长 在某张表中，存在一个id列（整数），希望在添加记录的时候，该列从1开始自动的增长，怎么处理？
-- 创建表
CREATE TABLE t13(
id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(32) NOT NULL DEFAULT'',
`name`VARCHAR(32) NOT NULL DEFAULT'');
DESC t13
-- 测试自增长的使用
INSERT INTO t13
VALUES(NULL,'jack@qq.com','jack')
INSERT INTO t13
VALUES(NULL,'tom@qq.com','tom')
INSERT INTO t13
(email,`name`)VALUES('zdd@qq.com','zdd') -- 这种写法在auto——increment下也是对的
SELECT *FROM t13
-- 自增长使用细节
-- 一般来说自增长是和primary key配合使用的
-- 自增长也可以单独使用（但是需要配合一个unique）
-- 自增长修饰的字段为整数型的（虽然小数也可以但非常少这样使用）
-- 自增长默认从1开始，你也可以通过如下命令修改alter table 表名auto_increment=xxx
ALTER TABLE t13 AUTO_INCREMENT=100
-- 添加数据时，给自增长字段指定的有值，则以指定的值为准，如果指定了自增长，一般来说，就按照自增长的规则来添加数据
9.索引
索引本质是数据结构，可以提高查询速度确保数据的唯一性
-- 在没有创建索引时，我们的查询一条记录 耗时长
SELECT*
FROM emp
WHERE empno=1234569
-- 使用索引优化一下,索引 本身也会占用空间
CREATE INDEX empno_index ON emp(empno) -- 在empno列上创建索引
-- 创建索引后，查询速度很快。只对创建了索引的列有效
#索引的机制 二叉树数据结构
-- 索引的代价：磁盘占用，对dml（update delete insert）语句的效率影响
#索引的类型
-- 主键索引，主键自动为主索引（类型primary key）
-- 唯一索引（unique）
-- 普通索引（index）
-- 全文索引（fulltext）适用于myISAM 开发中考虑使用：全文搜索Solr和ElasticSearch(ES)
9.1主键索引
-- 添加主键索引
CREATE TABLE t16(
id INT,
`name` VARCHAR(32))
ALTER TABLE t16 ADD PRIMARY KEY(id)
SHOW INDEXES FROM t16
-- 删除主键索引
ALTER TABLE t16 DROP PRIMARY KEY
SHOW INDEXES FROM t16
-- 建立索引（主键） 练习
CREATE TABLE order1(
id INT,
goods CHAR(8),
ename VARCHAR(20),
num INT )
ALTER TABLE order1 ADD PRIMARY KEY(id)
SHOW INDEX FROM order1
CREATE TABLE order2(
id INT PRIMARY KEY,
goods CHAR(8),
ename VARCHAR(20),
num INT )
SHOW INDEX FROM order29.2唯一索引
-- 添加唯一索引
CREATE UNIQUE INDEX id_index ON t15(id)
-- 建立唯一索引练习
CREATE TABLE menu1(
id INT PRIMARY KEY,
mname VARCHAR(32),
cooker CHAR(8),
eat_id VARCHAR(18) )
CREATE UNIQUE INDEX index_eat ON menu1(eat_id)
SHOW INDEX FROM menu1
CREATE TABLE menu2(
id INT PRIMARY KEY,
mname VARCHAR(32),
cooker CHAR(8),
eat_id VARCHAR(18) UNIQUE )
SHOW INDEX FROM menu29.3普通索引
-- 添加普通索引
CREATE INDEX id_index ON t15(id)
-- 如何选择索引类型，如果某列的值是不会重复的，则优先使用unique索引，否则使用普通索引
-- 添加普通索引方式2
ALTER TABLE t15 ADD INDEX id_index (id)
-- 删除索引
DROP INDEX id_index ON t15
-- 查询索引
SHOW INDEX FROM t15
SHOW INDEXES FROM t15
SHOW KEYS FROM t15
DESC t15
-- 建立普通索引
CREATE TABLE sportman1(
id INT PRIMARY KEY,
ename VARCHAR(32),
special CHAR(20))
CREATE INDEX index_ename ON sportman1(ename)
SHOW INDEX FROM sportman1
CREATE TABLE sportman2(
id INT PRIMARY KEY,
ename VARCHAR(32),
special CHAR(20))
ALTER TABLE sportman2 ADD INDEX index_ename (ename)
SHOW INDEX FROM sportman29.4全文索引
9.5 创建索引规则
-- 较频繁的作为查询条件字段应该创建索引
-- 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
-- 更新非常频繁的字段不适合创建索引
-- 不会出现在where子句中字段不该创建索引
10.事务
10.1事务概要和四大特性
事务用于保证数据的一致性，它由一组相关的dml语句组成，改组的dml语句要么全部成功，要么全部失败。
-- 如转账就要用事务来处理，用于保证数据的一致性
-- 事务和锁：当执行事务操作时（dml语句），mysql会在表上加锁，防止其它用户改表的数据，这对用户来讲是非常重要的
事务的acid特性
1.原子性（atomicity)
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。。
2.一致性（consistency）
事务必须使数据库从一个一致性状态变换到另外一个一致性状态
3.隔离性（isolation）
事务的隔离性是指多个用户并发访问数据库时，数据库为每一个用户开启的事务，
不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离
4.持久性（durability）
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，
接下来即使数据库发生故障也不应该对其有任何影响。
10.2事务的使用
-- mysql数据库控制台事务的几个重要操作（基本操作 transaction.sql）
-- 1.start transaction-- 开始一个事务
-- 2.savepoint 保存点名 -- 设置保存点
-- 3.rollback to 保存点名-- 回退事务
-- 4.rollback-- 回退全部事务
-- 5.commit-- 提交事务，所有的操作生效，不能回退
-- 创建一张测试表
CREATE TABLE t17
(id INT,
`name` VARCHAR(32));
-- 开始事务
START TRANSACTION
-- 设置保存点
SAVEPOINT a
-- 执行dml操作
INSERT INTO t17 VALUES (100,'tom')
SELECT *FROM t17
SAVEPOINT b
-- 执行dml操作
INSERT INTO t17 VALUES(200,'jack')
-- 回退到b
ROLLBACK TO b
-- 继续回退
ROLLBACK TO a
COMMIT
-- 回退事务
在介绍回退事务前，先介绍一下保存点（savepoint）保存点是事务中的点。
用于取消部分事务，当结束事务时（commit）,会自动删除该事务定义的所有保存点。
当执行回退事务时，通过指定保存点可以回退到指定的点，
-- 提交事务
使用commit语句可以提交事务，当执行了commit语句后，会确认事务的变化、结束事务、
删除保存点、释放锁，数据生效。当使用commit语句结束事务后，其他会话将可以查看到事务变化后的新数据
所有的数据正式生效。
#-- 事务注意事项
1.如果不开始事务，默认情况下，dml操作是自动提交的，不能回滚
INSERT INTO t17 VALUES (300,'mary')-- 自动提交commit
SELECT *FROM t17
ROLLBACK-- 不能回滚
2.如果开始一个事务，你没有创建保存点，你可以执行rollback，默认就是回到事务开始的状态
START TRANSACTION
INSERT INTO t17 VALUES (400,'King')
INSERT INTO t17 VALUES (500,'ming')
ROLLBACK-- 直接回退到事务开始的状态
3.也可以在这个事务中（还没有提交时），创建多个保存点。比如：savepoint aaa,执行dml,SAVEPOINT bbb
4.你可以在事务没有提交前，选择回退到哪个保存点
5.mysql的事务机制需要innodb的存储引擎还可以使用，myisam不好使
6.开始一个事务 START TRANSACTION,SET autocommit=off
10.3隔离级别
事务隔离级别
-- 1. 多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性
-- 2.如果不考虑隔离性，可能会引发如下问题：脏读，不可重复读，幻读
-- 脏读（dirty read）：当一个事务读取另一个事务尚未提交的修改时，产生脏读
-- 不可重复读(nonrepeatable read)：同一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，
-- 每次返回不同的结果集，此时发生不可重复读
-- 幻读（phantom read）:同一查询在同一事务中多次进行，由于其他提交事务所做的插入操作，每次返回不同的结果集，此时发生幻读
#-- 隔离级别演示
-- 查看当前mysql的隔离级别
SELECT @@transaction_isolation;
-- 把其中一个控制台的隔离级别设置成：read uncommitted
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED11.存储
11.1存储引擎
1.MySQL的表类型由存储引擎（storage engines）决定，主要包括MyISAM、innoDB、Memory等
2.MySQL数据表主要支持六种类型，分别是:CSV、Memory、ARCHIVE、MRG_MYISAM、MYISAM、InnoBDB
3.这六种又分为两类，一类是‘事务安全型‘，比如：InnoDB;其余都属于第二类，称为’非事务安全性‘（non-transaction-safe）[mysiam和memeory]
11.2查看、修改、删除存储过程
-- 查看所有的存储引擎
SHOW ENGINES
-- 细节说明
-- 1.MyISAM不支持事务、也不支持外键，但其访问速度快，对事务完整性没有要求。
-- 2.InnoDB 存储 引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MyISAM存储引擎，
-- InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引
-- 3.MEMORY 存储引擎使用存在内存中的内容来创建表，每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常得快，
-- 因为它的数据是放在内存中的，并且默认使用HASH索引。但是一旦服务关闭，表中的数据就会丢失掉，表的结构还在
-- innodb存储引擎 支持事务 支持外键 支持行级锁
-- myisam存储引擎 添加速度快 不支持外键和事务 支持表级锁
CREATE TABLE t18(
id INT,
`name` VARCHAR(32))ENGINE MYISAM
START TRANSACTION;
SAVEPOINT t1
INSERT INTO t18 VALUES (1,'jack');
SELECT *FROM t18
ROLLBACK TO t1-- 警告：回滚失败
-- memory 存储引擎 数据存储在内存中(关闭了mysql服务 数据丢失但是表结构还在)； 执行速度很快（没有IO读写）；默认支持索引（hash表）
CREATE TABLE t19(
id INT,
`name` VARCHAR(32))ENGINE MEMORY
INSERT INTO t19 VALUES(1,'tom'),(2,'jack'),(3,'hsp')
SELECT* FROM t19
DESC t19
-- 修改存储引擎
-- alter table 表名 engine=储存引擎；
ALTER TABLE t19 ENGINE=INNODB;12.视图 view
12.1视图概要
看一个需求:
emp表的列信息很多，有些信息是个人重要信息（比如sql,comm,mgr,hiredate）,如果我们希望某个用户只能查询emp表的（empno、ename,job和deptno）信息，有什么办法？
视图是一个虚拟表，其内容由查询定义。同真实的表一样视图包含其数据来自对应的真实表（基表）
-- 对视图的总结：
视图是根据基本来创建的，视图是虚拟的表
视图也有列，数据来自基表
通过视图可以修改基表的数据
基表的改变，也会影响到视图的数据
12.2视图的使用
-- 视图的基本使用
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
删除视图
DROP VIEW emp_view01
-- 视图细节讨论
1.创建视图后，到数据库去看，对应视图只有一个视图结构文件（形式：视图名.frm）
2.视图的数据变化会影响到基表，基表的数据变化也会影响到视图（insert UPDATE delete）
3.视图中可以再使用视图
UPDATE emp_view01
SET job='MANAGER'
WHERE empno=7369
SELECT *FROM emp_view01
UPDATE emp
SET job='SALESMAN'
WHERE empno=7369
视图（view）
-- 视图最佳实践
安全。 一些数据表有重要的信息。有些字段是保密的，不能让用户直接看到。这时就可以创建一个视图，
在这张视图中保留一部门字段。这样，用户就可以查询自己需要的字段，不能查看保密的字段
性能。 关系数据库的数据常常会分表存储，使用外键建立这些表之间的关系。这时，数据库查询通常会用到连接（join）.
这样做不但麻烦，效率相对也比较低。如果建立一个视图，将相关的表和字段组合在一起，就可以避免使用join查询数据
灵活。如果系统中有一张旧的表，这张表由于设计的问题，即将被废弃。然而，很多应用都是基于这张表，不易修改。
这时就可以建立一张视图，视图中的数据直接映射到新建的表。这样，就可以少做很多动作，也达到了升级数据表的目的
#视图练习
针对emp、dept和salgrade 三张表 ，创建一个视图emp_view03，可以显示雇员编号，雇员名，雇员部门名称和薪水级别[即使用三张表，构建一个视图]
CREATE VIEW emp_view03
AS
SELECT empto, ename,dname,grade
FROM emp,dept,salgrade
WHERE emp.`deptno`=dept.`deptno`AND sal BETWEEN losal AND hisal
DESC emp_view03
SELECT*FROM emp_view0313.用户管理
MySQL用户可以分为普通用户和root用户。root用户是超级管理员，拥有所有权限，包括创建用户、删除用户和修改用户的密码等管理权限;普通用户只拥有被授权的各种权限。
13.1创建、修改和删除
#mysql 用户管理
Mysql中的用户，都存储在系统数据库mysql中user表中。
其中user表的重要字段说明：
1.host: 允许登录的‘位置’，localhost表示该用户只允许本机登录，也可以指定ip地址，比如：192.168.1.100
2.user:用户名
3.authentication_string：密码，是通过mysql的password（）函数加密之后的密码
-- mysql用户的管理
原因：当我们做项目开发时，可以根据不同的开发人员，赋给他相应的mysql操作权限
所以mysql数据库管理人员（root），根据需要创建不同的用户，赋给相应的权限，供人员使用
-- 创建用户：‘hsp_edu'@'localhost’表示用户的完整信息‘hsp_edu’用户名 ‘localhost’登录的ip
123456 密码，但是注意存放到mysql.User表时，是password('123456')加密后的密码
CREATE USER 'hsp_edu'@'localhost' IDENTIFIED BY'123456'
SELECT 'host','user',authentication_string
FROM mysql.user
-- 删除用户
DROP USER ‘hsp_edu@localhost’
-- 登录
-- 修改自己的密码
SET PASSWORD = PASSWORD('abcdef')
-- 修改其他人的密码需要权限
SET PASSWORD FOR 'root'@'localhost'=PASSWORD('123456')
13.2权限管理
#-- 演示用户权限的管理
-- 创建一个用户（你的名字），密码123，并且只可以从本地登录，不让远程登录mysql
CREATE USER 'zdd'@'localhost' IDENTIFIED BY '123'
-- 创建库和表testdb下的news表，要求：使用root用户创建
CREATE DATABASE testdb
CREATE TABLE news(
id INT,
content VARCHAR(32))
INSERT INTO news VALUES(100,'北京新闻')
SELECT * FROM news
-- 给用户分配查看news表和添加数据的权限
GRANT SELECT,INSERT
ON testdb.news
TO 'zdd'@'localhost'
-- 测试看看用户是否只有这几个权限
-- 修改密码为abc,要求：使用root用户完成
SET PASSWORD FOR 'zdd'@'localhost'= PASSWORD('abc')
-- 重新登录 --上一条报错不知道为什么
-- 回收zdd用户 在testdb.news的所有权限
REVOKE SELECT,UPDATE ,INSERT ON testdb.`news`FROM 'zdd'@'localhost'
-- 使用root用户 删除你的用户
DROP USER 'zdd'@'localhost'
-- 管理细节
在创建用户的时候，如果不指定host,则为%，%表示所有ip都有连接权限
CREATE USER emma
SELECT 'host','user' FROM mysql.`user`
-- 也可以这样指定 create user ‘xxx’@'192.168.1.%' 表示xxx用户在192.168.1.*的ip可以登录mysql
CREATE USER 'emma'@'192.168.1.%'
再删除用户的时候，如果host不是%，需要明确指定‘用户’@‘host值’
DROP USER emma
DROP USER 'emma'@'192.168.1.%'
3万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
