- 1【Graphormer阅读笔记、实验与刷榜】《Do Transformers Really Perform Bad for Graph Representation?》
- 2git常用命令及冲突解决_git解决冲突命令
- 3Pixel4 安卓源码及内核修改编译教程 | 基于Android12 AOSP【更新：231128】_pixel4 编译aosp
- 4华为ensp防火墙ipsec_ensp ipsec
- 5【FPGA】优化设计指南（二）：性能指标
- 6Django配置静态文件_django 配置静态文件
- 7PX4Flow使用操作
- 8墨子web3实时周报
- 9Jenkins在Linux环境下的安装与配置，包含遇到的问题以及解决方法_linux安装jenkins
- 10AndroidStudio中Attatch debugger to Android Ptocess时 Choose Process后OK是灰色的_android studio choose process
MySQL_mysql 5.7.44
赞
踩
MySQL基础
如果安装MySQL过程中出错了，重来
sc delete mysql【删除已经安装好的musql服务，慎重！！！】
MySQL 5.7.44安装
-
下载 MySQL5.7.44
-
解压设置环境变量（\bin目录）
-
在根目录添加 my.ini 文件
[client] port=3306 default-character-set=utf8 [mysqld] # 设置为自己MYSQL的安装目录 basedir=E:\MySQL\mysql-5.7.44-winx64\ # 设置为MYSQL的数据自录 datadir=E:\MySQL\mysql-5.7.44-winx64\data\ port=3306 character_set_server=utf8 # 跳过安全检查 skip-grant-tables- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
-
管理员身份运行cmd
- 转到\bin目录
mysqld -installmysqld --initialize-insecure --user=mysql产生data目录
-
启动 mysql 服务
net start mysql -
停止 mysql 服务
net stop mysql
-
进入 mysql 管理终端
mysql -u root -p -
修改 root 密码
use mysql; update user set authentication_string=password('root') where user='root' and Host='localhost'; # 修改 root 密码为 root flush privileges; # 刷新权限 quit # 退出- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
修改之后注销掉 my.ini 的
skip-grant-tables
-
连接到Mysql服务（Mysql数据库）的指令
mysql -h 主机IP -P 端口 -u 用户名 -p密码- 注意：-p密码不要有空格
- -p后面没有写密码，回车会要求输入密码
- 如果没有写 -h 主机，默认本机
- 如果没有些 -P 端口，默认3306
- 实际工作中端口一般会更改
MySQL的三层结构
数据库-普通表的本质：仍然是文件
表的一行称之为一条记录
在java程序中，一行记录往往使用对象表示
SQL语句分类
DDL：数据定义语句[create表，库…]
DML：数据操作语句[增加insert，修改update，删除delete]
DQL：数据查询语句[select】
DCL：数据控制语句【管理数据库：比如用户权限grant，revoke】
数据库基本操作
创建数据库
CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [create_specification...]]
- 1
create_specification:
[DEFAULT] CHARACTER SET charset_name
[DEFAULT] COLLATE collation_name
- CHARACTER SET：指定数据库采用的字符集，如果不指定字符集，默认utf8
- COLLATE：指定数据库字符集的校对规则（常用的utf8bin[区分大小写]、utf8generalci[不区分大小写]，注意默认是utf8_generalci）
在创建数据库，表的时候，为了规避关键字，可以使用反引号
反引号是~键解决
查看删除数据库
# 显示数据库：
SHOW DATABASES
# 显示数据库创建语句
SHOW CREATE DATABASE db_name
# 数据库删除语句【慎用】
DROP DATABASE [IF EXISTS] db_name
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
备份恢复数据库
-
备份数据库（注意：命令行DOS执行）
mysqldump -u 用户名 -p -B 数据库1 数据库2 数据库n > 文件名.sql生成的
文件名.sql文件，就是对应的sql语句 -
恢复数据库（注意：进入MySQL命令行再执行）
Source 文件名.sql -
备份恢复库的表
``mysqldump -u 用户名 -p密码 数据库 表1 表2 表n > 文件名.sql`
MySQL常用数据类型（列类型）
日期类型：
CREATE TABLE t4 ( birthday DATE, job_time DATETIME, login_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ; SELECT * FROM t4; INSERT INTO t4(birthday, job_time) VALUES('2023-12-07', '2023-12-07 10:00:00');
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP：默认填入当前时间，更新时也是当前时间
数据表的管理
创建表
CREATE TABLE table_name
(
field1 datatype,
field2 datatype,
field3 datatype,
)character set 字符集 collate 校对规则 engin 存储引擎
CREATE TABLE `user` (
id INT,
`name` VARCHAR(255),
`password` VARCHAR(255),
`birthday` DATE
) CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
field:指定列名
datatype：指定列类型（字段类型）
character set：如不指定则为所在数据库字符集
collate：如不指定则为所在数据库校对规则
engine:：引擎（这个涉及内容较多，后面单独讲解）
修改表
-
添加列
ALTER TABLE tablename ADD (column datatype [DEFAULT expr] [ , column datatype]...);- 1
- 2
-
修改列
ALTER TABLE tablename MODIFY (column datatype [DEFAULT expr] [ , column datatype]...);- 1
- 2
-
修改列名：
ALTER TABLE tablename CHANGE `原列名` `新列名` datatype;- 1
- 2
-
修改表名：
RENAME TABLE 表名 to 新表名- 1
-
修改表字符集
ALTER TABLE 表名 CHARACTER SET 字符集;- 1
-
删除列
ALTER TABLE tablename DROP (column); 查看表的结构：desc 表名; # 可以查看表的列- 1
- 2
- 3
删除表
DROP TABLE tablename;
- 1
CRUD增删改查
create，read，update，dalete
Insert 语句
使用Insert语句向表中插入数据
INSERT INTO table_name [(column [ , column...])]
VALUES (value [ , vallue...])
- 1
- 2
insert细节：
- 插入的数据应与字段的数据类型相同。比如把’abc’添加到int类型会错误
- 数据的长度应在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中。
- 在values中列出的数据位置必须与被加入的列的排列位置相对应。
- 字符和日期型数据应包含在单引号中。
- 列可以插入空值[前提是该字段允许为空]，insert into table value（null）
- insert into tab_name（列名…）values (),(),() 形式添加多条记录
- 如果是给表中的所有字段添加数据，可以不写前面的字段名称
- 默认值的使用，当不给某个字段值时，如果有默认值就会添加，否则报错
update 语句
使用update 语句修改表中数据
UPDATE tbl_name
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]
- 1
- 2
- 3
update细节：
- UPDATE语法可以用新值更新原有表行中的各列。
- SET子句指示要修改哪些列和要给予哪些值。
- WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行（记录），因此提醒一定小心。
- 如果需要修改多个字段，可以通过set字段1=值1，字段2=值2…
delete 语句
使用delete 语句删除表中数据
DELETE FROM tb_name
[WHERE where_defination]
- 1
- 2
delete 细节：
- 如果不使用where子句，将删除表中所有数据。
- Delete语句不能删除某一列的值（可使用update设为null或者）使用delete语句仅删除记录，不删除表本身。
- 如要删除表，使用drop table语句。drop table表名；
select 语句
单表查询
SELECT [DISTINCT] *|{column1, column2, column3...}
FROM tablename;
- 1
- 2
select 细节
- Select指定查询哪些列的数据。
- column指定列名。
- *号代表查询所有列。
- From指定查询哪张表。
- DISTINCT可选，指显示结果时，是否去掉重复数据。完全相同才去重。
-
使用表达式对查询的列进行运算
SELECT [DISTINCT] *|{column1 | expression, column2 | expression, ..} FROM tablename;- 1
- 2
-
在select语句中可使用as语句取别名
SELECT column_name as 别名 FROM 表名;- 1
-
在where子句中经常使用同的运算符
-
between and是闭区间
-
实例：
-- 查询总分大于200分并且数学成绩小于语文成绩的姓张的学生 SELECT * FROM student WHERE (chinese + english + math) > 200 AND math < chinese AND `name` LIKE '张%';- 1
- 2
- 3
'张%'：表示所有以张开始的名字
-
-
使用order by子句排序查询结果
SELECT column1, column2, column3.. FROM table ORDER BY column asc|desc, ...- 1
- 2
- 3
- asc(默认)：升序
- desc：降序
- Orderby指定排序的列，排序的列既可以是表中的列名，也可以是select语句后指定的列名。A
- ORDER BY子句应位于SELECT语句的结尾
-
使用group by 子句对列进行分组
SELECT column1, column2, column3..FROM table GROUP BY column- 1
- 2
-
使用 having 子句对分组后的结果进行过滤
SELECT column1, column2, column3..FROM table GROUP BY column having...- 1
- 2
查询加强
-- 查询加强 SELECT * FROM emp; -- ■ 使用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
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
-
分页查询
基本语法：
SELECT ... LIMIT start, rows- 1
表示从 start+1 行开始取，取出 rows 行，start 从0开始计算。
-- 分页查询 -- 按雇员的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) , 每页显示记录数
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
-
分组函数加强（group by）
-- 增强group by 的使用 -- (1) 显示每种岗位的雇员总数、平均工资。 SELECT COUNT(*), job, AVG(sal) 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; -- (3) 显示管理者的总人数。小技巧:尝试写->修改->尝试[正确的] SELECT COUNT(DISTINCT mgr) FROM emp; -- DISTINCT 去重 -- (4) 显示雇员工资的最大差额。 -- 思路： max(sal) - min(sal) SELECT MAX(sal) - MIN(sal) FROM emp; -- 应用案例：请统计各个部门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; SELECT deptno, AVG(sal) AS avg_sal FROM emp GROUP BY deptno HAVING avg_sal > 1000 ORDER BY avg_sal DESC LIMIT 0, 2- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
多表查询
多表查询是指基于两个和两个以上的表查询.在实际应用中，查询单个表可能不能满足你的需求。
默认情况下，当两个表查询时，规则如下：
- 从第一张表中，取出一行和第二张表的每一行进行组合，返回结果【含有两张表的所有列】
- 一共返回的记录数：第一张表行数 * 第二张表的行数
- 这样的多表查询默认处理方式返回的结果称为
笛卡尔积- 解决多表的关键就是要写出正确的过滤条件
where注意：
- 当我们需要指定显示某个表的列是，需要
表.列表- 多表查询的条件不能少于
表的个数-1, 否则会出现笛卡尔积
-- 多表查询 -- ?显示雇员名,雇员工资及所在部门的名字 【笛卡尔集】 /* 1. 雇员名,雇员工资 来自 emp表 2. 部门的名字 来自 dept表 3. 需求对 emp 和 dept查询 ename,sal,dname,deptno 4. 当我们需要指定显示某个表的列是，需要 表.列表 */ SELECT ename, sal, dname FROM emp, dept -- 默认返回笛卡尔积 SELECT ename, sal, dname FROM emp, dept WHERE emp.`deptno` = dept.`deptno` -- 小技巧：多表查询的条件不能少于 表的个数-1, 否则会出现笛卡尔集 -- ?如何显示部门号为10的部门名、员工名和工资 SELECT dname, ename, sal FROM emp, dept WHERE emp.`deptno` = dept.`deptno` AND emp.`deptno` = 10 SELECT dname, ename, sal, emp.`deptno` FROM emp, dept WHERE emp.`deptno` = dept.`deptno` HAVING emp.`deptno` = 10 -- ?显示各个员工的姓名，工资，及其工资的级别 -- 思路 姓名，工资 来自 emp 13 -- 工资级别 salgrade 5 -- 写sql , 先写一个简单，然后加入过滤条件... SELECT ename, sal, grade FROM emp, salgrade WHERE emp.`sal` BETWEEN salgrade.`losal` AND salgrade.`hisal`
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
自连接
自连接是指在同一张表的连接查询。即将同一张表看作两张表
-- 多表查询的 自连接
-- 思考题: 显示公司员工名字和他的上级的名字
-- 老韩分析： 员工名字 在emp, 上级的名字的名字 emp
-- 员工和上级是通过 emp表的 mgr 列关联
SELECT worker.`ename` AS '职员名', boss.`ename` AS '上级名' FROM emp AS worker, emp AS boss
WHERE worker.`mgr` = boss.`empno`
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
自连接的特点
- 把同一张表当做两张表使用
- 需要给表取别名：表名 表别名 （也可以加 AS）
- 列名不明确，可以指定列的别名：列名 as 列的别名
子查询
子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询
-
单行子查询
单行子查询是指只返回一行数据的子查询语句
-- 单行子查询：如何显示与SMITH同一部门的所有员工? /* 1. 先查询到 SMITH的部门号得到 2. 把上面的select 语句当做一个子查询来使用 */ SELECT deptno FROM emp WHERE ename = 'SMITH'; -- 下面的答案. SELECT * FROM emp WHERE deptno = ( SELECT deptno FROM emp WHERE ename = 'SMITH' );- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
-
多行子查询
多行子查询是指返回多行数据的子查询，使用关键字
in-- 多行子查询:如何查询和部门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; -- 或者不等<>- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
-
在多行子查询中使用 all 和 any 操作符
-- 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 );- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
-
子查询当作临时表使用
-- 查询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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
-
多列子查询
多列子查询是指查询返回多个列数据的子查询语句
-- 多列子查询 -- 请思考如何查询与ALLEN的部门和岗位完全相同的所有雇员(并且不含ALLEN本人) -- (字段1， 字段2 ...) = (select 字段 1，字段2 from 。。。。) -- 分析: 1. 得到ALLEN的部门和岗位 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` = '宋佳' );- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
表复制
-
自我复制数据（蠕虫复制）
有时，为了对某个sgl语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据
-- 表的复制 -- 为了对某个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;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
-
删除一条表中的重复记录（重复）
-- 如何删除掉一张表重复记录 -- 1. 先创建一张表 my_tab02, -- 2. 让 my_tab02 有重复的记录 CREATE TABLE my_tab02 LIKE emp; DESC my_tab02; INSERT INTO my_tab02 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;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
合并查询
又是在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号union，union all
union all：该操作符用于取得两个结果集的并集。当使用该操作符时，不会取消重复行。
union ：就是将两个查询结果合并，会去重；
-- 合并查询
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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
mysql表外连接
-
左外连接（如果左侧的表完全显示就是左外连接）
基本语法：
SELECT ... FROM tab1 LEFT JOIN tab2 on 条件，其中，tab1就是左表，tab2就是右表。-- 使用左连接 -- （显示所有人的成绩，如果没有成绩，也要显示该人的姓名和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;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
-
右外连接（如果右侧的表完全显示就是右外连接）
基本语法：
SELECT ... FROM tab1 RIGHT JOIN tab2 on 条件，其中，tab1就是左表，tab2就是右表。-- 使用右外连接（显示所有成绩，如果没有名字匹配，显示空) -- 即：右边的表(exam) 和左表没有匹配的记录，也会把右表的记录显示出来 SELECT `name`, stu.id, grade FROM stu RIGHT JOIN exam ON stu.id = exam.id;- 1
- 2
- 3
- 4
- 5
函数
合计/统计函数
count
Count 返回行的总数
SELECT COUNT(*) | COUNT(列名) FROM table_name
[WHERE where_defination]
- 1
- 2
count(*)和count(列)的区别：
- count(*)：返回满足条件的记录的行数
- count(列名)：统计满足条件的某列有多少个，但是会排除为null的情况
sum
Sum函数返回满足 where 条件的行的和——一般用在数值列
SELECT SUM(列名) {, SUM(列名)...} FROM tablename
[where where_defination]
- 1
- 2
注意：
sum进队数值起作用；
对多列求和，“,”不能少
avg
AVG函数返回满足where条件的一列的平均值
SELECT AVG(列名) {, AVG(列名)...} FROM tablename
[WHERE where_defination]
- 1
- 2
max/min
max/min函数返回满足where条件的一列的最大/最小值
SELECT MAX(列名) FROM tablename
[WHERE where_defination]
- 1
- 2
字符串相关函数
dual 亚元表, 系统表 可以作为测试表使用
索引从1开始
数学函数
RAND([seed]) RAND([seed]) 返回随机数 其范围为 0 ≤ v ≤ 1.0
- 如果使用 rand() 每次返回不同的随机数 ，在 0 ≤ v ≤ 1.0
- 如果使用 rand(seed) 返回随机数, 范围 0 ≤ v ≤ 1.0, 如果seed不变，该随机数也不变了
时间日期相关函数
DATE_ADD()中的interval后面可以是year minute second day等
DATE_SUB()中的interval后面可以是year minute second day等
DATEDIFF(date1，date2)得到的是天数，而且是date1-date2的天数，因此可以取负数
这四个函数的日期类型可以是date，datetime或者timestamp
unix_timestamp() : 返回的是1970-1-1 到现在的秒数
FROM_UNIXTIME() : 可以把一个unix_timestamp 秒数[时间戳]，转成指定格式的日期
意义：在开发中，可以存放一个整数，然后表示时间，通过FROM_UNIXTIME转换
SELECT FROM_UNIXTIME(1618483484, '%Y-%m-%d') FROM DUAL; SELECT FROM_UNIXTIME(1618483100, '%Y-%m-%d %H:%i:%s') FROM DUAL;
- 1
- 2
加密函数
-- 演示加密函数和系统函数 -- USER() 查询用户 -- 可以查看登录到mysql的有哪些用户，以及登录的IP SELECT USER() FROM DUAL; -- 返回 用户@IP地址 -- DATABASE() 查询当前使用数据库名称 SELECT DATABASE() FROM DUAL; -- MD5(str) 为字符串算出一个 MD5 32的字符串，常用(用户密码)加密 -- root 密码是 honvin -> 加密md5 -> 在数据库中存放的是加密后的密码 SELECT MD5('honvin') FROM DUAL; -- d7faa0cda0a6690fc378b2ee740e58a5 SELECT LENGTH(MD5('honvin')) FROM DUAL; -- 演示用户表，存放密码时，是md5 CREATE TABLE timerring_user (id INT , `name` VARCHAR(32) NOT NULL DEFAULT '', pwd CHAR(32) NOT NULL DEFAULT ''); INSERT INTO timerring_user VALUES(100, 'honvin', MD5('honvin')); SELECT * FROM timerring_user; SELECT * FROM timerring_user WHERE `name` = 'honvin' AND pwd = MD5('honvin'); -- PASSWORD(str) -- 加密函数, MySQL数据库的用户密码就是 PASSWORD函数加密 SELECT PASSWORD('honvin') FROM DUAL; -- *8EF49CD623ACBFFA57D630ECD695A3DC3EEECDBC -- select * from mysql.user \G 从原文密码str 计算并返回密码字符串 -- 通常用于对mysql数据库的用户密码加密 -- mysql.user 表示 数据库.表 SELECT * FROM mysql.`user`;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
流程控制函数
# 演示流程控制语句 # IF(expr1,expr2,expr3) 如果expr1为True ,则返回 expr2 否则返回 expr3 SELECT IF(FALSE, 'Beijing', 'Shanghai') FROM DUAL ; -- Shanghai # IFNULL(expr1,expr2) 如果expr1不为空NULL,则返回expr1,否则返回expr2 SELECT IFNULL('Beijing', 'Shanghai') FROM DUAL; -- Beijing SELECT IFNULL(NULL, 'Shanghai') FROM DUAL; -- Shanghai # 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' WHEN FALSE THEN 'tom' ELSE 'jerry' END ; -- jack -- 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 ;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
MySQL 约束
基本介绍：约束用于确保数据库的数据满足特定的商业规则。在mysql中，约束包括：not null、unique，primary key，foreignkey和check五种。
主键（primary key）
- 基本使用：
字段名 字段类型 primary key - 用于唯一的标识表行的数据，当定义主键约束后，该列不能重复
注意细节：
primary key不能重复而且不能为 null。
一张表最多只能有一个主键, 但可以是复合主键(比如 id+name，id和name不能同时相同)
-- 演示复合主键 (id 和 name 做成复合主键) CREATE TABLE t6 (id INT , `name` VARCHAR(32), email VARCHAR(32), PRIMARY KEY (id, `name`) -- 这里就是复合主键 ); INSERT INTO t6 VALUES(1, 'tom', 'tom@sohu.com'); INSERT INTO t6 VALUES(1, 'jack', 'jack@sohu.com'); INSERT INTO t6 VALUES(1, 'tom', 'xx@sohu.com'); -- 这里就违反了复合主键 SELECT * FROM t6;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
主键的指定方式 有两种
- 直接在字段名后指定：
字段名 primakry key- 在表定义最后写：
primary key(列名);CREATE TABLE t19 (id INT , `name` VARCHAR(32) PRIMARY KEY, email VARCHAR(32) ); CREATE TABLE t20 (id INT , `name` VARCHAR(32) , email VARCHAR(32), PRIMARY KEY(`name`) -- 在表定义最后写 primary key(列名) );
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
使用
desc 表名，可以看到primary key的情况
实际开发中，每个表往往都对设计一个主键
非空（not null）
如果在列上定义了notnull，那么当插入数据时，必须为列提供数据。
字段名 字段类型 not null
唯一（unique）
当定义了唯一约束后，该列值是不能重复的。
字段名 字段类型 unique
注意细节：
- 如果没有指定 not null ，则 unique 字段可以右多个null
- 如果一个列（字段）是 unique not null ， 使用效果类似 primary key
- 一张表可以右多个 unique 字段
外键（foreign key）
用于定义主表和从表之间的关系：外键约束要定义在从表上，主表则必须具有主键约束或是unique约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为nul。
学生表的class_id为300的，在班级表中并不存在，所以就会添加失败，这叫外键约束另外，如果学生表的jack和班级表已建立联系再删除班级表的id就会失败，得先删除学生表的jack才行，也叫外键约束。
- 语法：
FOREIGNKEY(本表字段名) REFERENCES 主表名(主键名或unique字段名) - 先创建主表，再创建从表
细节说明：
- 外键指向的表的字段，要求是primarykey或者是unique
- 表的类型是innodb，这样的表才支持外键
- 外键字段的类型要和主键字段的类型一致（长度可以不同）
- 外键字段的值，必须在主键字段中出现过，或者为null[前提是外键字段允许为null]
- 一旦建立主外键的关系，数据不能随意删除了
检查（check）
用于强制行数据必须满足的条件，假定在sql列上定义了check约束，并要求sql列值在1000～2000之间如果不在1000～2000之间就会提示出错。
提示：oracle和sqlserver均支持check，但是mysql5.7目前还不支持check，只做语法校验，但不会生效
-- 演示check的使用
-- mysql5.7目前还不支持check ,只做语法校验，但不会生效
-- 了解
-- 学习 oracle, sql server, 这两个数据库是真的生效.
-- 测试
CREATE TABLE t7 (
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 t7 VALUES(1, 'jack', 'mid', 1); -- MySQL 5.7能添加进去
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
自增长
某列从1开始自动的增长
字段名 整形 PRIMARY KEY AUTO_INCREMENT
添加自增长的字段的方式
-- 1
insert into xxx (字段1, 字段2,...) values(null, '值'...)
-- 2
insert into xxx (字段2,...) values('值'...)
-- 3
insert into xxx values(null, '值'...)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
使用细节：
- 一般来说自增长是和primarykey配合使用的
- 自增长也可以单独使用[但是需要配合一个unique]
- 自增长修饰的字段为整数型的（虽然小数也可以但是非常非常少这样使用）
- 自增长默认从1开始，你也可以通过如下命令修改
alter table 表名 auto_increment=xxx;- 如果添加数据是，给自增长字段（列）指定的具体值，则以指定的值为准
- 如果制定了自增长，一般来说，就按照自增长的规则来添加数据
索引
提高数据库的性能，索引是最物美价廉的东西了，不用加内存，不用该程序，不用调sql，查询速度就可能提高百倍千倍。
CREATE INDEX 索引名称 ON 表名(字段名/列名)
CREATE INDEX empno_index ON emp (empno)
-- empno_index 索引名称
-- ON emp (empno) : 表示在 emp表的 empno列创建索引
- 1
- 2
- 3
注意：
- 索引占用磁盘空间；
- 创建一个列的索引之后，如果查询换成另一列，则索引不起作用，需要重建索引。
索引的原理
- 没有索引时，查询进行全表扫描（即使找到了也会继续往下查询，知道查完全表），查询速度慢
- 使用索引，会形成 一个索引的数据结构，比如二叉树
- 索引的代价
- 磁盘占用
- 如果对表进行dml（修改update，删除delete，添加insert操作）语句，会对索引进行维护，对速度有影响
- 在实际项目中，select操作占比90%
索引的类型
-
主键索引，主键自动的为主索引（类型 Primary key）
create table t1 ( id int primary key, -- 主键，同时也是索引，称为主键索引 `name` varchar(32));- 1
- 2
- 3
-
唯一索引（UNIQUE）
create table t2 ( id int unique, -- id是唯一的，同时也是索引，称为unique索引 `name` varchar(32));- 1
- 2
- 3
-
普通索引（INDEX）
-
全文索引（FULLTEXT）[适用于MyISAM]
开发中一般不适用mysql自带的全文索引，而是使用：全文搜索Solr和ElasticSearch（ES）
创建索引
-
唯一索引
create table t2 ( id int unique, -- id是唯一的，同时也是索引，称为unique索引 `name` varchar(32)); -- 或者 create table t2 ( id int, `name` varchar(32)); create unique index id_index on t2(id);- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
-
普通索引
create index id_index on table_name(id); -- 或者 alter table table_name add index id_index (id)- 1
- 2
- 3
- 4
- 5
如何选择：
如果某列的值是不会重复的，则优先考虑使用unique索引，否则使用普通索引
-
主键索引
create table t1 ( id int primary key, -- 主键，同时也是索引，称为主键索引 `name` varchar(32)); -- 或者 create table t1 ( id int, `name` varchar(32)); ALTER TABLE t1 ADD PRIMARY KEY (id);- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
删除索引
-
首先查询索引名称
SHOW INDEX FROM table_name -
删除索引
DROP INDEX 索引名 ON table_name
删除主键索引比较特别：
ALTER TABLE table_name DROP PRIMARY KEY
修改索引
先删除，再添加新的索引
查询索引
4种方式
-- 1
SHOW INDEX FROM tablename
-- 2
SHOW INDEXES FROM tablename
-- 3
SHOW KEYS FROM tablename
-- 4 不如上面的详细
DESC tablename
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
总结
在哪些列上适合使用索引？
-
较频繁的作为查询条件字段应该创建索引
select * from emp where empno =1
-
唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
select * from emp where sex=‘男’
-
更新非常频繁的字段不适合创建索引
select * from emp where logincount =1
-
不会出现在WHERE子句中字段不该创建索引
事务
事务原理
-
什么是事务：
事务用于保证数据的一致性，它由一组相关的dml语句组成，该组的dml语句要么全部成功，要么全部失败。如：转账就要用事务来处理，用以保证数据的一致性。
-
事务和锁：
当执行事务操作时（dml语句），mysql会在表上加锁，防止其它用户改表的数据。这对用户来讲是非常重要的。
-
mysql数据库控制台事务的几个重要操作（基本操作）
- start transaction–开始一个事务
- savepoint保存点名–设置保存点
- rollback to保存点名–回退事务
- rollback–回退全部事务
- commit–提交事务，所有的操作生效，不能回退。同时将设置的保存点删除
-- 事务的一个重要的概念和具体操作 -- 演示 -- 1. 创建一张测试表 CREATE TABLE t9 ( id INT, `name` VARCHAR(32)); -- 2. 开始事务 START TRANSACTION -- 3. 设置保存点 SAVEPOINT a -- 执行dml 操作 INSERT INTO t9 VALUES(100, 'tom'); SELECT * FROM t9; SAVEPOINT b -- 执行dml操作 INSERT INTO t9 VALUES(200, 'jack'); -- 回退到 b ROLLBACK TO b; -- 继续回退 a ROLLBACK TO a; -- 如果这样, 表示直接回退到事务开始的状态. ROLLBACK- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
-
回退事务：在介绍回退事务前，先介绍一下保存点（savepoint)。保存点是事务中的点。用于取消部分事务，当结束事务时（commit），会自动的删除该事务所定义的所有保存点当执行回退事务时，通过指定保存点可以回退到指定的点。
-
提交事务：使用commit语句可以提交事务。当执行了commit语句子后，会确认事务的变化、结束事务、删除保存点、释放锁，数据生效。当使用commit语句结束事务子后，其它会话[其他连接]将可以查看到事务变化后的新数据[所有数据就正式生效]。
-
事务细节：
-
如果不开始事务，默认情况下，dml操作是自动提交的，不能回滚
-
如果开始一个事务，你没有创建保存点。你可以执行rollback，默认就是回退到你事务开始的状态
-
你也可以在这个事务中（还没有提交时），创建多个保存点。
比如：
save point aaa;
执行dml；
save point bbb;
-
你可以在事务没有提交前，选择回退到哪个保存点
-
mysql的事务机制需要innodb的存储引擎才可以使用，myisam不好使
-
开始一个事务
start transaction或者set autocommit = off;
-
隔离级别
-
事务隔离级别介绍：
-
多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性。
-
如果不考虑隔离性，可能会引发如下问题：
- 脏读（dirty read）：当一个事务读取另一个事务尚未提交的修改[insert，update， delete]时，产生脏读。
- 不可重复读（nonrepeatable read）：一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，每次返回不同的结果集，此时发生不可重复读。
- 幻读（phantom read）：查询在同一事务中多次进行，由于其他提交事务所做的插入操作，每次返回不同的结果集，此时发生幻读。
-
事务隔离级别
概念：Mysql隔离级别定义了事务与事务之间的隔离程度。
- 注意可串行化加锁，会一直等待，只有其他控制台事务提交之后才能执行成功。
-
查看当前mysql的隔离级别
SELECT @@tx_isolation;+-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set, 1 warning (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
-
设置隔离级别 READ UNCOMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED+------------------+ | @@tx_isolation | +------------------+ | READ-UNCOMMITTED | +------------------+ 1 row in set, 1 warning (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
-
-
隔离级别指令
-
查看当前会话隔离级别
SELECT @@tx_isolation; -
查看系统当前隔离级别
SELECT @@global.tx_isolation; -
设置当前会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED -
设置系统当前隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED -
mysql默认的事务隔离级别是repeatable read，一般情况下，没有特殊要求，没有必要修改（因为该级别可以满足绝大部分项目需求）
-
事务的ACID特性
-
原子性（Atomicity）
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
-
一致性（Consistency）
事务必须使数据库从一个一致性状态变换到另外一个一致性状态
-
隔离性（lsolation）
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
-
持久性（Durability）
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响
存储引擎
-
基本介绍
- MySQL的表类型由存储引l擎（Storage Engines）决定，主要包括MyISAM、innoDB、Memory等。
- MySQL数据表主要支持六种类型，分别是：CSV、Memory、ARCHIVE、MRG_MYISAM、MYISAM、InnoBDB。
- 这六种又分为两类，一类是”事务安全型”（transaction-safe），比如：InnoDB；其余都属于第二类，称为”非事务安全型”（non-transaction-safe)[mysiam和 memory]
-
查看所有的存储引擎
SHOW ENGINES;
-
主要存储引擎/表类型特点
-
细节
- MyISAM不支持事务、也不支持外键，但其访问速度快，对事务完整性没有要求
- InnoDB存储引l擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MyISAM存储引I擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引1。
- MEMORY存储引I擎使用存在内存中的内容来创建表。
- 每个MEMORY表只实际对应一个磁盘文件。
- MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH索引。
- 但是一旦服务关闭，表中的数据就会丢失掉，表的结构还在。
-
如何选择存储引擎
- 如果你的应用不需要事务，处理的只是基本的CRUD操作，那么MyISAM是不二选择，速度快
- 如果需要支持事务，选择InnoDB
- Memory存储引l擎就是将数据存储在内存中，由于没有磁盘I/O的等待速度极快。但由于是内存存储引擎，所做的任何修改在服务器重启后都将消失。（经典用法：用户的在线状态()）
-
修改存储引擎
ALTER TABLE tablename ENGINE = 引擎名;
视图
-
看一个需求
-
基本概念
视图是一个虚拟表，其内容由查询定义，同真实的表一样，视图包含列，其数据来自对应的真实表（基表）
-
视图和对应真实表（基本）的关系
-
总结：
- 视图时根据基表（可以是多个基表）来创建的，视图是虚拟的表。
- 视图也有列，数据来自基表
- 通过视图可以修改基表的数据
- 基表的改变也会影响到视图的数据
-
视图的基本使用
create view 视图名 as select 语句alter view 视图名 asselect 语句——更新成新的视图SHOW CREATEVIEW 视图名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; -- 查看创建视图的指令 SHOW CREATE VIEW emp_view01; -- 删除视图 DROP VIEW emp_view01;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
-
视图细节
- 创建视图后，到数据库去看，对应视图只有一个视图结构文件(形式: 视图名.frm)
- 视图的数据变化会影响到基表，基表的数据变化也会影响到视图[insert update delete]
- 视图中可以再使用视图，数据仍然来自基表， 比如从emp_view01 视图中，选出empno和ename做出新视图
-- 视图的细节 -- 1. 创建视图后，到数据库去看，对应视图只有一个视图结构文件(形式: 视图名.frm) -- 2. 视图的数据变化会影响到基表，基表的数据变化也会影响到视图[insert update delete ] -- 修改视图 会影响到基表 UPDATE emp_view01 SET job = 'MANAGER' WHERE empno = 7369; -- 修改基本表， 会影响到视图 UPDATE emp SET job = 'CLERK' WHERE empno = 7369; -- 3. 视图中可以再使用视图 , 比如从emp_view01 视图中，选出empno,和ename做出新视图 CREATE VIEW emp_view02 AS SELECT empno, ename FROM emp_view01;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
-
视图的最佳实践
-
安全。
一些数据表有着重要的信息。有些字段是保密的，不能让用户直接看到。这时就可以创建一个视图，在这张视图中只保留一部分字段。这样，用户就可以查询自己需要的字段，不能查看保密的字段。
-
性能。
关系数据库的数据常常会分表存储，使用外键建立这些表的之间关系。这时，数据库查询通常会用到连接（JOIN）。这样做不但麻烦，效率相对也比较低。如果建立一个视图，将相关的表和字段组合在一起，就可以避免使用JOIN查询数据。
-
灵活。
如果系统中有一张旧的表，这张表由于设计的问题，即将被废弃。然而，很多应用都是基于这张表，不易修改。这时就可以建立一张视图，视图中的数据直接映射到新建的表。这样，就可以少做很多改动，也达到了升级数据表的目的。
-
-
基表是多张表的情况
-- 视图的课堂练习 -- 针对 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); SELECT * FROM emp_view03; DESC emp_view03;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
MySQL管理
MySQL用户
我们做项目开发时，可以根据不同的开发人员，赋给他相应的Mysql操作权限，所以，Mysql数据库管理人员(root), 根据需要创建不同的用户，赋给相应的权限，供人员使用。
- mysql中的用户，都存储在系统数据库mysgl中user表中
-
重要字段说明：
- host：允许登录的“位置”，localhost表示该用户只允许本机登录。也可以指定ip地址，比如：192.168.1.100
- user：用户名
- authentication_string：密码，通过mysql的passwor()函数加密之后的密码。
-
创建用户
create user '用户名'@'允许登录的位置' identified by '密码'创建用户，同时指定密码
-
删除用户
drop user '用户名'@'允许登录的位置'-- Mysql用户的管理 -- 原因：当我们做项目开发时，可以根据不同的开发人员，赋给他相应的Mysql操作权限 -- 所以，Mysql数据库管理人员(root), 根据需要创建不同的用户，赋给相应的权限，供人员使用 -- 1. 创建新的用户 -- 解读 (1) 'zhw'@'localhost' 表示用户的完整信息 'zhw' 用户名 'localhost' 登录的IP -- (2) 123456 密码, 但是注意 存放到 mysql.user表时，是password('123456') 加密后的密码 -- *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 CREATE USER 'zhw'@'localhost' IDENTIFIED BY '123456'; SELECT `host`, `user`, `authentication_string` FROM mysql.`user`; -- 2. 删除用户 DROP USER 'zhw'@'localhost';- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
-
不同的数据库用户，操作的库和表不相同
-
用户修改密码
-
修改自己的密码
set password = password('密码'); -
修改他人的密码（需要有修改用户密码权限）
set password for '用户名'@'允许登录的位置' = password('密码');-- 修改自己的密码, 没问题 SET PASSWORD = PASSWORD('abcdef') -- 修改其他人的密码， 需要权限 SET PASSWORD FOR 'root'@'localhost' = PASSWORD('root'); -- Access denied for user 'zhw'@'localhost' to database 'mysql'- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
-
-
MySQL权限
-
给用户授权
grant 权限列表 on 库.对象名 to ‘用户名’@'登录位置' [identified by‘密码']说明
-
回收用户授权
revoke 权限列表 on 库.对象名 from ‘用户名'@'登录位置'； -
权限生效指令
如果权限没有生效，可以执行下面命令
FLUSH PRIVILEGES;-- 演示 用户权限的管理 -- 创建用户 honvin 密码 123 , 从本地登录 CREATE USER 'honvin'@'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; -- 给 honvin 分配查看 news 表和 添加news的权限 GRANT SELECT, INSERT ON testdb.`news` TO 'honvin'@'localhost'; -- 可以增加update权限 GRANT UPDATE ON testdb.`news` TO 'honvin'@'localhost'; -- 修改 honvin 的密码 abc SET PASSWORD FOR 'honvin'@'localhost' = PASSWORD('abc'); -- 回收 honvin 用户在 testdb.news 表的所有权限 REVOKE ALL ON testdb.`news` FROM 'honvin'@'localhost'; -- 删除 honvin DROP USER 'honvin'@'localhost';- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
细节
-
在创建用户的时候，如果不指定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`, `authentication_string` 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.%';
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
`
MySQL权限
[外链图片转存中…(img-ESU6ivnK-1702983094581)]
-
给用户授权
grant 权限列表 on 库.对象名 to ‘用户名’@'登录位置' [identified by‘密码']说明
[外链图片转存中…(img-rFITIXCR-1702983094582)]
-
回收用户授权
revoke 权限列表 on 库.对象名 from ‘用户名'@'登录位置'； -
权限生效指令
如果权限没有生效，可以执行下面命令
FLUSH PRIVILEGES;-- 演示 用户权限的管理 -- 创建用户 honvin 密码 123 , 从本地登录 CREATE USER 'honvin'@'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; -- 给 honvin 分配查看 news 表和 添加news的权限 GRANT SELECT, INSERT ON testdb.`news` TO 'honvin'@'localhost'; -- 可以增加update权限 GRANT UPDATE ON testdb.`news` TO 'honvin'@'localhost'; -- 修改 honvin 的密码 abc SET PASSWORD FOR 'honvin'@'localhost' = PASSWORD('abc'); -- 回收 honvin 用户在 testdb.news 表的所有权限 REVOKE ALL ON testdb.`news` FROM 'honvin'@'localhost'; -- 删除 honvin DROP USER 'honvin'@'localhost';- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
细节
-
在创建用户的时候，如果不指定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`, `authentication_string` 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.%';
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
