文章目录
1 数据库
1.1 数据简单原理图
1.2 使用命令行窗口连接MYSQL数据库
mysql (-h 主机名 -P 端口) -u 用户名 -p密码；
细节：-p密码 之间没有空格
1.3 Mysql三层结构
- 所谓安装MySQL数据库，就是在主机安装一个数据库管理系统（DBMS），这个管理程序可以管理多个数据库。DBMS（datebsase manage system）；
- 一个数据库中可以创建多个表，以保存数据（信息）；
- 数据库管理系统（DBMS）、数据库和表的关系如图：
1.4 SQL语句分类
1.5 创建、查看、删除和备份恢复数据库√
# 创建
create database database_name;
# 删除
drop database database_name;
# 查询
show database_name;
# 备份数据库
mysqldump -u [username] -p[password] [database_name] > [backupfile.sql]
-- eg：
mysqldump -u root -ppassword mydatabase > /path/to/backup/mydatabase_backup.sql
-- /path/to/backup/mydatabase_backup.sql 表示文件的路径
# 恢复数据库
mysql -u [username] -p[password] [database_name] < [backupfile.sql]
-- eg：
mysql -u root -ppassword mydatabase < /path/to/backup/mydatabase_backup.sql
2 表
2.1 创建、修改、删除表
# 创建
create table table_name (......);
# 删除
drop table table_name;
# 修改表名
rename table old_table_name to new_table_name;
# 查看表结构
desc table_name;
# 查看表内容
select * from table_name;
2.2 查询增强-总结
-- 应用案例：请统计各个部门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
2.3 多表查询
2.3.1 多行子查询-（子查询：嵌套查询）-> in
简单理解：单行返回一行；多行返回多行
SELECT deptno-- 子查询
FROM emp
WHERE ename = 'SMITH'
#单行子查询，deptno=20的行都是相同的
SELECT * FROM emp
WHERE deptno = (
SELECT deptno
FROM emp
WHERE ename = 'SMITH'
)-- 括号可以当做20处理，虽然也可以得到结果，但是不能那么写
SELECT DISTINCT job-- 子查询
FROM emp
WHERE deptno = 20;
#多行子查询，job的行不同且多行
SELECT ename, job, sal, deptno
FROM emp
WHERE job IN (
SELECT DISTINCT job
FROM emp
WHERE deptno = 20
) AND deptno <> 10 -- or deptno != 10 -- 不等于
2.3.2 子查询临时表-（select…）当做一个表
SELECT dname, dept.deptno, loc, temp.total_person
FROM dept, (
SELECT COUNT(*) AS total_person, deptno
FROM emp
GROUP BY deptno
) temp
WHERE dept.deptno = temp.deptno
另一种写法：SELECT dept.*, temp.total_person
2.3.3 all、any操作符-最大值和最小值的区别
#all，显示工资比30号部门都高
SELECT ename, sal, deptno
FROM emp
WHERE sal > ALL(
SELECT sal
FROM emp
WHERE deptno = 30
)
-- or
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
)
-- or
SELECT ename, sal, deptno
FROM emp
WHERE sal > (
SELECT MIN(sal)
FROM emp
WHERE deptno = 30
)
2.3.4 多列子查询-简单理解：多个字段查询
SELECT * FROM emp
WHERE (deptno, job) = (
SELECT deptno, job
FROM emp
WHERE ename = 'ALLEN'
) AND ename != 'ALLEN'
2.3 Alter table 各种语句-修改表结构，添加、删除或修改列，更换引擎，添加或删除索引
# 修改表结构，添加、删除或修改列
-- 添加列
alter table table_name add col_name;
-- 删除列
alter table table_name drop column col_name;
-- 修改列的数据类型
alter table table_name modify column col_name new_datatype; -- modify：修改
-- 修改列名和数据类型
alter table table_name change old_col_name new_col_name new_datatype;
# 以下是一些示例
-- 添加主键
ALTER TABLE table_name ADD PRIMARY KEY (column_name);
-- 删除主键
ALTER TABLE table_name DROP PRIMARY KEY;
-- 添加索引
ALTER TABLE table_name ADD INDEX index_name (column_name);
-- 删除索引
ALTER TABLE table_name DROP INDEX index_name;
-- 更换表引擎
ALTER TABLE table_name ENGINE = new_engine;
2.4 表复制和去重
2.4.1 自我复制数据（蠕虫复制）
-- 表的复制
-- 为了对某个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;
2.4.2 去重
-- 如何删除掉一张表重复记录
-- 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;
2.5 小结
- 只有子查询临时表写在from下，其他都在where下;
- select的本质是查询到一个值返回！
- select table_name.* 查询的是该表的所以字段
3 Mysql数据类型🚩
3.0 Mysql常用的数据类型
https://pan.quark.cn/g/ba5a8aa24f
3.1 数值型-整数、bit、小数
3.1.1 数值型-整数
#演示整型
#老韩使用tinyint 来演示范围 有符号 -128 ~ 127 如果没有符号 0-255
#说明： 表的字符集，校验规则, 存储引擎，老师使用默认
#1. 如果没有指定 unsinged , 则TINYINT就是有符号
#2. 如果指定 unsinged , 则TINYINT就是无符号 0-255
CREATE TABLE t3 (
id TINYINT);
CREATE TABLE t4 (
id TINYINT UNSIGNED);
INSERT INTO t3 VALUES(127); #这是非常简单的添加语句
SELECT * FROM t3
INSERT INTO t4 VALUES(255);
SELECT * FROM t4;
unsinged 无符号-> 可以表示更大的正数!
3.1.2 数值型-bit
#演示 bit 类型使用
#说明
#1. bit(m) m 在 1-64
#2. 添加数据 范围 按照你给的位数来确定，比如 m = 8 表示一个字节 0~255
#3. 显示按照 bit
#4. 查询时，仍然可以按照数来查询
CREATE TABLE t05 (num BIT(8));
INSERT INTO t05 VALUES(255);
SELECT * FROM t05;
SELECT * FROM t05 WHERE num = 1;
3.2.3 数值型-小数
#演示 decimal 类型、float、double 使用
#创建表
CREATE TABLE t06 (
num1 FLOAT, num2 DOUBLE, num3 DECIMAL(30,20));
#添加数据
INSERT INTO t06 VALUES(88.12345678912345, 88.12345678912345,88.12345678912345);
SELECT * FROM t06;
#decimal 可以存放很大的数
CREATE TABLE t07 (
num DECIMAL(65));
INSERT INTO t07 VALUES(8999999933338388388383838838383009338388383838383838383);
SELECT * FROM t07;
CREATE TABLE t08(
num BIGINT UNSIGNED)
INSERT INTO t08 VALUES(8999999933338388388383838838383009338388383838383838383);
SELECT * FROM t08;
3.2字符串(文本)类型-char 固长，varchar 变长
#演示字符串类型使用 char varchar
#注释的快捷键 shift+ctrl+c , 注销注释 shift+ctrl+r
-- CHAR(size)
-- 固定长度字符串 最大 255 字符
-- VARCHAR(size) 0~65535 字节
-- 可变长度字符串 最大 65532 字节 【utf8 编码最大 21844 字符 1-3 个字节用于记录大小】
-- 如果表的编码是 utf8 varchar(size) size = (65535-3) / 3 = 21844
-- 如果表的编码是 gbk varchar(size) size = (65535-3) / 2 = 32766
CREATE TABLE t09 (
`name` CHAR(255));
CREATE TABLE t10 (
`name` VARCHAR(32766)) CHARSET gbk;
DROP TABLE t10;
3.3 日期类型
#演示时间相关的类型
#创建一张表, 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 列会自动的以当前时间进行更新
3.4 创建表练习
#创建表的课堂练习
-- 字段 属性
-- 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`;
4 CRUD🚩
4.1 insert 语句
insert into table_name() values();
INSERT INTO `goods` (id, goods_name, price) VALUES(10, '华为手机', 2000);
#细节：插入要保持和数据类型一致
4.2 update 语句
update table_name set col where ...
#细节：如果没有where 语句，修改的是所有的行
4.3 delete 语句
delete from table_name where ...
#细节：如果没有where 会删除表中所有数据!
4.4 select 语句🚩
select * from ...
1. group by
2. having
3. order by
4. limit
where子语句常用运算符
5 函数🚩
5.1 合计/统计函数-count、sum、avg、max/min、group by 和 having子句
# 计算表中的记录数或某列的非 NULL 值数
SELECT COUNT(*) FROM table_name; -- 计算所有记录数 SELECT COUNT(column_name) FROM table_name; -- 计算某列的非 NULL 值数
# 计算某列的总和
SELECT SUM(column_name) FROM table_name;
# 计算某列的平均值
SELECT AVG(column_name) FROM table_name;
# 求某列的最大值
SELECT MAX(column_name) FROM table_name;
5.2 字符串函数
# 连接两个或多个字符串
SELECT CONCAT('Hello', ' ', 'World'); -- 返回 'Hello World'
# 将字符串转换为大写 - upper、ucase
SELECT UPPER('hello'); -- 返回 'HELLO'
# 将字符串转换为小写 - lower、lcase
# 返回字符串的长度。LENGTH() 对于多字节字符集可能会返回字节数，而 CHAR_LENGTH() 返回字符数
SELECT LENGTH('Hello'); -- 返回 5
# 在字符串中替换指定的子串
SELECT REPLACE('Hello World', 'World', 'Universe'); -- 返回 'Hello Universe'
# 提取字符串的子串,SUBSTRING() 或 SUBSTR()
SELECT SUBSTRING('Hello World', 1, 5); -- 返回 'Hello'
-- 练习: 以首字母小写的方式显示所有员工 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;
5.3 数学函数
# ABS(x) - 返回 x 的绝对值
SELECT ABS(-5); -- 返回 5
#CEIL(x) 和 CEILING(x) - 返回大于或等于 x 的最小整数
SELECT CEIL(5.6); -- 返回 6
#FLOOR(x) - 返回小于或等于 x 的最大整数
SELECT FLOOR(5.6); -- 返回 5
#FORMAT(X, D) - X 是要格式化的数字。
-- D 是可选参数，表示小数点后要显示的位数。如果省略 D，则默认值为 0，即不显示小数部分。
SELECT FORMAT(1234567.89, 2); -- 返回 '1,234,567.89'
SELECT FORMAT(1234567.89); -- 返回 '1,234,568'（因为默认不显示小数部分，并进行了四舍五入）
SELECT FORMAT(1234567); -- 返回 '1,234,567'
# RAND() - 返回一个 0 到 1 之间的随机小数
SELECT RAND(); -- 返回一个随机小数
5.4 日期函数
# CURDATE() 或 CURRENT_DATE - 返回当前日期
SELECT CURDATE();
# CURTIME() 或 CURRENT_TIME - 返回当前时间
SELECT CURTIME();
# date(datetime) - 返回年
select date('2024-3-18'); -- 返回2024
# DATE_ADD() 和 DATE_SUB() - 在日期上添加或减去指定的时间间隔
SELECT DATE_ADD(CURDATE(), INTERVAL 1 DAY);
SELECT DATE_SUB(CURDATE(), INTERVAL 1 MONTH);
#DATEDIFF() - 返回两个日期之间的天数差
SELECT DATEDIFF('2023-10-23', '2023-10-01'); -- 22
# NOW() - 返回当前的日期和时间
SELECT NOW();
5.5 加密函数
-- 演示加密函数和系统函数
-- 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 \G 从原文密码 str 计算并返回密码字符串
-- 通常用于对 mysql 数据库的用户密码加密
-- mysql.user 表示 数据库.表
SELECT * FROM mysql.user
5.6 流程控制函数
# IF - 根据条件返回两个不同的值
IF(condition, value_if_true, value_if_false);
-- eg：
SELECT
product_name,
price,
IF(price < 25, 'Low', 'High') AS price_level
FROM products;
# IFNULL
IFNULL(expression1, expression2);
# CASE 函数
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE result
END
-- eg：
SELECT
product_name,
price,
CASE
WHEN price < 10 THEN 'Cheap'
WHEN price BETWEEN 10 AND 50 THEN 'Affordable'
ELSE 'Expensive'
END AS price_category
FROM products;
6 内连接 和自连接大致相同
#自连接-同一张表
SELECT e1.employee_name AS employee, e2.employee_name AS manager
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.employee_id;
#内连接-不同表
SELECT orders.order_id, customers.customer_name
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id;
总结：自连接是连接同一张表，而内连接是连接不同表。
7 外连接🚩
7.1 左外连接和右外连接
简单理解：左外连接 显示完左边的表（可能出现NULL的情况）。右连接同理
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
-- left -> right
-- or 换一下表的位置
8 约束
8.1 primary key - not null - unique - foreign key - check - primary key auto_increment - default
主键、非空、唯一、外键、自增长
# 主键 - not null 和 不能重复。
-- 只能有一个主键，如果name+id 设置为主键，意思是name+id的这 一个（一个！）主键
(1)eg：id primary key;
(2) alter table table_name add primary key (column_name);
# 注意：判断是否为null 要用 is not null（不能!! == null）
# unique 不能重复，但是能为 NULL
-- unique + not null 差不多就是primary key
# foreign key - 用于在两个表之间建立关联，确保引用完整性
-- 一个表中的外键值必须引用另一个表的主键或唯一键的值
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
# check - 确保某列中的所有值都满足指定的条件，MySQL 8.0.16及更高版本支持CHECK约束。
CREATE TABLE users (
id INT NOT NULL,
age INT CHECK (age >= 0 AND age <= 150),
PRIMARY KEY (id)
);
# 自增长
-- 定义可用null（一般为id）
-- 可以重置初始值
alter table table_name auto_increment = statr_value;
9 索引🚩
9.1 索引原理
9.2 索引的类型
9.3 索引使用
-- 演示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 t25 ADD INDEX id_index (id)
-- 添加主键索引
CREATE TABLE t26 (
id INT ,
`name` VARCHAR(32));
ALTER TABLE t26 ADD PRIMARY KEY (id)
SHOW INDEX FROM t25
-- 删除索引
DROP INDEX id_index ON t25
-- 删除主键索引
ALTER TABLE t26 DROP PRIMARY KEY
-- 修改索引 ， 先删除，在添加新的索引
-- 查询索引
-- 1. 方式
SHOW INDEX FROM t25
-- 2. 方式
SHOW INDEXES FROM t25
-- 3. 方式
SHOW KEYS FROM t25
-- 4 方式
DESC t25
9.4 小结：哪些列上适合使用索引
10 事务
10.1 什么是事务
10.2 事务和锁
10.3 回退事务
10.4 提交事务
10.5 事务细节讨论
-- 开启事务
START TRANSACTION
-- 设置保存点 a
SAVEPOINT a
-- 进行dml 操作
INSERT INTO t27 VALUES(100, 'tom');
SELECT * FROM t27;
-- 设置保存点 b
SAVEPOINT b
INSERT INTO t27 VALUES(200, 'jack');
SELECT * FROM t27
-- 回退到 b
ROLLBACK TO b
-- 回退到 a
ROLLBACK TO a
-- rollback表示退回到开始事务的状态
ROLLBACK -- 回滚
COMMIT -- 提交事务，之前的全部删除
10.6 事务隔离级别
10.6.1 事务隔离级别介绍
10.6.2 查看事务隔离级别
10.6.3 事务隔离级别
简单理解：多用户开启事务的操作，互不干扰（数据是操作时间戳的数据）查询可能发生以下情况
- 未提交-脏读
- 已提交
- 修改/删除-不可重复读
- 插入-幻读
10.6.4 设置事务隔离级别
-- 演示mysql的事务隔离级别
-- 1. 开了两个mysql的控制台
-- 2. 查看当前mysql的隔离级别
SELECT @@tx_isolation;
-- mysql> SELECT @@tx_isolation;
-- +-----------------+
-- | @@tx_isolation |
-- +-----------------+
-- | REPEATABLE-READ |
-- +-----------------+
-- 3.把其中一个控制台的隔离级别设置 Read uncommitted
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- 4. 创建表
CREATE TABLE `account`(
id INT,
`name` VARCHAR(32),
money INT);
-- 查看当前会话隔离级别
SELECT @@tx_isolation
-- 查看系统当前隔离级别
SELECT @@global.tx_isolation
-- 设置当前会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- 设置系统当前隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL [你设置的级别]
10.6.5 事务acid 特性
11 引擎
11.1 基本介绍
11.2 主要的存储引擎/表特点
11.3 细节说明
11.4 三种存储引擎表使用案例
简单理解：
- 事务需求 -> InnoDB
- 读/写操作
- 读多 -> MyISAM，MyISAM可能提供更好的性能
- 写多 -> InnoDB，InnoDB的行级锁定可能更适合
- 内存使用 -> MEMORY，应用需要快速访问且数据量不大
- 分布式需求 -> NDB Cluster
11.5 修改存储引擎
-- 表类型和存储引擎
-- 查看所有的存储引擎
SHOW ENGINES
-- innodb 存储引擎，是前面使用过.
-- 1. 支持事务 2. 支持外键 3. 支持行级锁
-- myisam 存储引擎
CREATE TABLE t28 (
id INT,
`name` VARCHAR(32)) ENGINE MYISAM
-- 1. 添加速度快 2. 不支持外键和事务 3. 支持表级锁
START TRANSACTION;
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
12 视图
12.1 基本概念
视图（view）：视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含列，其数据来自对应的真实表（基表，可以多个基表）-源数据不会改变！
视图特点：简化复杂性、安全性、抽象数据
12.2 视图和基表关系示意图
12.3 基本使用
# 创建视图，as-select
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
# 删除视图
DROP VIEW view_name;
12.4 细节讨论
-- 视图的使用
-- 创建一个视图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 ]
-- 修改视图 会影响到基表
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
12.5 视图最佳实践
12.6 视图练习
-- 视图的课堂练习
-- 针对 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
13 Mysql 管理
13.1 Mysql用户
13.2 创建/删除用户
-- 'chase_stu'@'localhost' 是一个整体，'123456' 是密码，都可以动态变化
#1.创建用户
CREATE USER 'chase_stu'@'localhost' IDENTIFIED BY '123456';
SELECT `host`, `user`, authentication_string
FROM mysql.user;
#2.删除用户
DROP USER 'chase_stu'@'localhost';
#3.登录
-- root 用户修改 hsp_edu@localhost 密码, 是可以成功.
SET PASSWORD FOR 'chase_stu'@'localhost' = PASSWORD('123456')
13.3 不同数据用户，操作的库和表不相同
13.4 修改密码
-- 修改自己的密码, 没问题
SET PASSWORD = PASSWORD('abcdef')
-- 修改其他人的密码， 需要权限
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456')
13.5 mysql中的权限
13.6 给用户授权
GRANT SELECT, INSERT
ON testdb.news
TO 'yao'@'localhost';
13.7 回收用户授权
REVOKE SELECT, INSERT ON testdb.news FROM 'yao'@'localhost'; -- 回收权限
13.8 权限生效指令
对以上内容的sql语句
# 创建用户，'username'@'hostname' 是一个整体 *
CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';
-- eg：CREATE USER 'chase_stu'@'localhost' IDENTIFIED BY '123456';
# 删除用户，一般由root删除
DROP USER 'username'@'hostname';
# 修改密码
# 修改自己的密码
SET PASSWORD = PASSWORD('abcdef');
# 修改用户密码
SET PASSWORD FOR 'username'@'hostname' = PASSWORD('newpassword');
-- 或者在MySQL 5.7及更高版本中：
ALTER USER 'username'@'hostname' IDENTIFIED BY 'newpassword';
# 权限*
# 赋予权限
GRANT SELECT, INSERT, UPDATE ON database_name.table_name TO 'username'@'hostname';
# 刷新权限
FLUSH PRIVILEGES;
13.9 用户管理练习
-- 演示 用户权限的管理
-- 创建用户 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'
REVOKE ALL ON testdb.news FROM 'shunping'@'localhost'
-- 删除 shunping
DROP USER 'shunping'@'localhost'
13.10 细节
-- 说明 用户管理的细节
-- 在创建用户的时候，如果不指定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.%'
1755
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
