MySQL基础
数据库
- 表的一行称之为一条记录
- 普通表的本质就是一个文件
- DDL:数据定义语义(create)
- DML:数据操作语言(增加insert,修改update,删除delete)
- DQL:数据查询语言(select sort )
- DCL:数据控制语言(管理数据库, grand revoke)
数据库操作
- utf8mb4 – 真正的UTF-8 | utf8 – 是多为3个字节的UTF-8,不是真正意义上的UTF-8
- utf8mb4_0900_as_cs
- 0900 表示 校对规则 的 Unicode
- ai 表示 不区分 口音, 相反 as 表示 区分 口音
- ci 表示 不区分 大小写, 相反 cs 表示 区分 大小写
-- 数据库的操作
-- 1. 创建一个名称为wxy_db01的数据库
create database wxy_db01
-- 2. 创建一个使用utf-8字符集的wxy_db02
create database wxy_db02 character set utf8
-- 3. 创建一个使用utf-8字符集,并带校对规则的wxy_db03
-- 校对规则 utf8_bin 不区分大小写 默认校对规则是 utf8_general_ci 不区分大小写
create database wxy_db03 character set utf8 collate utf8_bin
-- 4. 删除数据库
DROP DATABASE wxy_db03
-- 5. 查看当前数据库服务器中的所有数据库
show Databases
-- 6. 查看前面创建的wxy_db01数据库的定义信息
show CREATE DATABASE wxy_db01
-- 7. 删除前面创建的wxy_db01数据库
DROP DATABASE wxy_db01 [IF EXISTS]
-- 为了规避关键字,可以使用反引号解决``
- 备份数据库 Dos执行
mysqldump -u 用户名 -p -B 数据库1 数据库2 数据库n > 路径\文件名.sql
- 恢复数据库 进入mysql命令行再执行
source 文件名.sql
表
创建表
create table emp (
id int unsigned AUTO_INCREMENT primary key,
name varchar(20),
sex char(1),
brithday date,
entry_day date,
job varchar(20),
Salay float,
resume text
);
删除表
修改表
-- 修改表
select * from emp
select * from employee
desc emp -- 查看表的列
-- 添加列
-- alter table table_name add
-- colume datatype [default expr] ...
ALTER TABLE emp add
image varchar(32)
-- 修改列
-- alter table table_name modify
-- colume datatype [default expr] ...
alter table emp modify
job varchar(60)
-- 删除列
-- alter table table_name drop
-- colume ...
alter table emp drop
image
-- 修改表名 rename table 表名 to 新表名
rename table emp to employee
-- 修改表的字符集 alter table 表名 charset 字符集 (character set)
alter table emp character set utf8mb4
-- 修改列名和信息
alter table employee
change name user_name varchar(20) not null default ' '
desc employee
MySQL数据类型△
数值类型
- unsigned 表示无符号
字符串类型
-
varchar(size) size最大为 65535-3 / utf8或utf8mb4
-
char(size) 其中size表示字符数,不管是中文还是英文都是放size个
varchar(size) 其中size表示字符数,不管是中文还是英文都以定义好的表的编码来存放数据
-
char(4) 定长的,当插入“aa” 也会占用四个字符的空间
Varchar(4) 变长的,当插入“aa” 也不会占用四个字符的空间,而是按实际来分配占用空间,而且varchar需要1-3个字节来存放内容长度
-
当数据是定长时,如身份证号、邮编号、md5密码、手机号等,使用char
当数据是变长时,直接采用varchar
查询速度:char > varchar
-
当存放大量字符时,可以用text代替varchar,若更大可采用longtext、mediumtext
日期和时间类型
- timestamp 需要自动更新时,需要设置为 not null default current_timestamp on update current_timestamp
bit
- bit(m) m表示位数 1-64
- 可存数据为 0 - 2^m -1
- 查询时,仍然可以使用数值型进行查询
- 当所需位数小于8时,可以采用bit
CRUD△
insert
-- insert into table_name (colume ...) values (value ...)
create table goods (
id int primary key,
goods_name varchar(30),
price double
);
insert into goods values (1, '油麦菜', 12.35);
insert into goods values (2, '黄瓜', 2.12);
select * from goods;
- 插入的数据与表中字段的数据类型一致
- 数据的长度应与列的规定范围中
- 在values中列出的数据位置必须与被加入的列的排列位置相应
- 字符和日期型数据应包含在单引号中
- 列可以插入空值[前提是该字段运行为空 null]
- insert into tablename (columu) values (), (), ()… 添加多条记录
- 若给表中所有字段添加信息,可以省略字段的书写
- 默认值的使用,当不给某字段值时,若有默认值则会自动添加,否则报错,若没有指定not null,则会默认填充null
update
-- update table_name set columun_name = data1, columu_name = data2...
-- where id = ...
-- 没有where时,会对所有记录进行修改
update employee set salay = 5000;
-- where条件修改
update employee
set job = '测开'
where id = 2;
-- 多字段修改
update employee
set salay = 19912, sex = 0
where user_name = 'wxy';
delete
-- delete from table_name where ... 删除某个记录
-- where 条件删除
delete from employee where id = 1;
-- 无where的全部删除
delete from employee;
select
-- select [distinct(去重)] * | {columu1, columu2 ...}
-- from table_name
-- where ...
select * from student;
select name, english from student;
select distinct english from student;
-- 每个字段都相同才会去重
select distinct * from student;
-- 1. select culumu_name as 别名 from table_name
select name ,chinese as '语文' from student;
select name, chinese, math, english, chinese+english+math as score
from student;
select id, name, chinese, math, english, chinese + english + math + 10 as score
from student
order by id;
-- 2. where 比较运算的使用
select name, math, chinese from student
where math > 60 and chinese > 90;
select * from student where chinese > english;
select name, chinese, english, math, chinese + english + math as score from student
where chinese + english + math > 200
and math < chinese
and name like '诸%'; -- 以诸开头,后面任意 _表示任意一个字符
-- 3. or between and
select * from student
-- where english >= 80 and english <=90;
where english between 80 and 90 -- 闭区间
select * from student
where math = 12 or math = 11 or math = 90;
select * from student where name like '张%';
select * from student
where math > 80 and chinese > 80;
-- 4. order by
select * from student
order by math;
select * from student
order by english + chinese + math desc;
select * from student
where name like '刘%'
order by math;
函数△
统计函数
-- 1. select count(*) | count(columu_name) from table_name
-- where ...
-- count(*) 返回满足条件的记录的行数
-- count(列名) 统计满足条件的某列有多少个,但会排除null
select count(*) from student;
select count(*) from student where math > 60;
select count(*) from student where (chinese + math + english) > 150;
select count(distinct mgr) from emp; -- 去重
-- 2. select sum(columu_name1) {,sum(columu_name2),...} from table_name
-- where ...
select sum(math) from student;
select sum(math), sum(english), sum(chinese) from student;
select sum(math + english + chinese) from student;
select sum(chinese) / count(*) from student;
-- 3. select avg(columu_name1) {,avg(columu_name2),...} from table_name
-- where ...
select avg(math) from student;
select avg(math + english + chinese) from student;
-- 4. select max[min](columu_name1) from table_name
-- where ...
select max(math + english + chinese), min(math + english + chinese)
from student;
select max(math) as math_high, min(math) as math_littel from student;
分组方法
-- select column1, column2 from table_name
-- group by column
-- having ...
-- 1. 每个部门的平均工资和最高工资
select avg(sal), max(sal), deptno
from emp
group by deptno
order by deptno;
-- 2. 每个部门的每种岗位的平均工资和最低工资
select deptno, job, avg(sal), min(sal)
from emp
group by deptno, job
order by deptno;
-- 3. 平均工资低于2000的部门号和它的平均工资
select deptno, avg(sal) as avg_sal
from emp
group by deptno
having avg_sal < 2500
ORDER BY deptno;
字符串函数
-- 1. charset(str) 返回字符串字符集
select charset(ename) from emp; -- utf8mb4
-- 2. concat(string2 [,....]) 连接字符串 将多列拼接成一列
select concat(ename, ' job is ', job) from emp;
-- 3. instr(string, substring) 返回substring在string中出现的位置,没有返回0
select instr('wangxinyu', 'wang') from dual; -- dual mysql中亚元表,可用于测试
-- 4. UCASE(string2) 转化成大写
select UCASE(ename) from emp;
-- 4. LCASE(string2) 转化成小写
select LCASE(ename) from emp;
-- 5. left(string2, length) 从string2中的坐标起取length个字符
select left(ename, 2) from emp;
select right(ename, 2) from emp;
-- 6. length(string) string长度
select job, length(job) from emp;
-- 7. replace(str, search_str, replace_str) 在str中用replace_str替换search_str
select replace(job, 'manager', '经理') from emp;
select replace(job, '经理', 'manager') from emp;
-- 8. strcmp(str1, str2) 逐字符比较两字符串大小, 不区分大小写 0 表示相同, -1表示不同
select strcmp('wxy', 'wxy') from dual;
-- 9. substring(str, pos [, len]) 从str的pos开始
select substring(ename, 1, 2) from emp;
-- 10. ltrim(string2) rtrim(string2) trim 去除前端空格或后端空格,左右都去
select ltrim(" wxy ") from dual;
select rtrim(" wxy ") from dual;
select trim(" wxy ") from dual;
-- 练习:以首字母小写的方式显示所有员工emp中的姓名
select empno, concat(UCASE(substring(ename, 1, 1)), substring(ename,2)) from emp;
数学函数
-- 1. abs(num) 绝对值
select ABS(-20) from dual;
-- 2. bin(decimal_number) 十进制转二进制
select bin(10) from dual;
-- 3. ceiling(num) 向上取整
select ceiling(2.5) from dual;
-- 4. conv(number2, from_base, to_base) 进制转换
select conv(10, 10, 2) from dual;
-- 5. floor(num) 向下取整
select floor(2.5) from dual;
-- 6. format(number, decimal_places) 保留小数位数
select format(12.12345, 3) from dual; -- 四舍五入
select format(avg(sal), 2) from emp group by deptno;
-- 7. hex(decimalNumber) 转十六进制
select hex(17) from dual;
-- 8. least(number1, number2 [,....]) 求最小值
select least(10, 20, 30) from dual;
-- 9. mod(numerator, denominator) 求余
select mod(10, 3) from dual;
-- 10. rand([send]) 范围为 0 ≤ v ≤ 1.0 若加入种子后,若种子不变,返回的随机数也不会改变
select format(rand() * 9 + 1 , 0) from dual;
日期函数
-- 1. CURRENT_DATE() 返回当前日期
select current_date from dual;
-- 2. CURRENT_TIME 返回当前时间
select current_time from dual;
-- 3. CURRENT_TIMESTAMP() 返回当前日期和时间
select current_timestamp from dual;
insert into emp values
(0001, 'wxyyy', 'salesman', 0 ,current_date, 9999.00, 9999, 40);
-- 4. DATE() 从日期或日期时间表达式中提取日期值
select id, content, date(send_time) from news;
-- 5. DATE_ADD(d,INTERVAL expr type) 计算起始日期 d 加上一个时间段后的日期
-- type 值可以是:MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR
select id, content, send_time from news
where date_add(send_time, interval 10 minute) >= now();
-- 6. DATE_SUB(date,INTERVAL expr type) 函数从日期减去指定的时间间隔。
select id, content, send_time from news
where date_sub(NOW(), interval 10 minute) <= send_time;
-- 7. DATEDIFF(d1,d2) 计算日期 d1->d2 之间相隔的天数
select datediff('2021-10-23', '2021-10-2') from dual;
select datediff(NOW(), '1998-12-02') / 365 from dual;
select datediff(date_add('1998-12-02', interval 80 year), '1998-12-02') from dual;
-- 8. now() 返回当前时间
select now() from dual;
-- 9. DAY(d) 返回日期值 d 的日期部分
select day(now()) from dual;
select month(now()) from dual;
select year(now()) from dual;
-- 10. unix_timestamp() : 返回的是1970-1-1到现在的毫秒数
select UNIX_TIMESTAMP() from dual;
-- 11. from_unixtime() 可以把unix_timestamp 秒数,转换成指定格式的日期
select from_unixtime(UNIX_TIMESTAMP(), '%Y-%m-%d %H:%i:%s') from dual;
select from_unixtime(1628483484, '%Y-%m-%d') from dual;
加密和系统函数
-- 1. user() 查询用户 查看登录到mysql的所有用户和用户ID
select user() from dual;
-- 2. database() 数据库名称
select database();
-- 3. MD5(str) 为字符串算出一个MD5 32位的字符串,(用户密码)加密
select MD5('wxywxy123') from dual;
select length(MD5('wxywxy123')) from dual;
-- 4. Password(str) mysql数据库的用户密码用password函数进行加密
select * from mysql.USER;
select password('wxy') from dual;
流程控制函数
-- 1. if(expr1, expr2, expr3) 如果expr1 为true,则返回expr2 否则返回expr3
-- (condition, value_if_true, value_if_false
select if(true, '北京', '上海') from dual;
select if(false, '北京', '上海') from dual;
select ename, if(comm is null, 0.0, comm) from emp;
-- 2. IFNULL(expression, alt_value)
-- IFNULL函数接受两个参数,如果不是NULL,则返回第一个参数。否则,IFNULL函数返回第二个参数。
select ifnull('沈阳' , '大连');
select ifnull(null , '大连');
-- 3. CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END
select case '12'
when '11' then 11
when '12' then 12 end;
select case '12' when '11' then '11' else '12' end;
单表增强
-- 1. 查找1992.1.1 后入职的员工
-- 在mysql中 date数据可以直接比较
select * from emp
where hiredate > '2000-01-01';
-- 2. like 模糊查询
-- % 表示0到多个任意字符
-- _ 表示单个任意字符
select ename, sal from emp
where ename like 's%';
select ename, sal from emp
where ename like '__o%';
select * from emp
where mgr is null;
desc emp;
-- 3. order by
select * from emp order by sal;
select * from emp order by deptno asc, sal desc;
select * from emp order by sal desc, deptno asc; -- 两者不同
-- 4. 分页查询 select ... limit start, rows
-- 表示从 start+1 行 开始取, 取出rows行,start从0开始计算
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;
-- 5. 增强group by
select count(*), format(avg(sal), 2) from emp
group by job;
select count(*), count(comm) from emp -- count(列) 若该列是null,不进行统计
select count(*), count(if(comm is null, 1, null)) from emp;
select count(*), count(*) - count(comm) from emp;
select count(distinct mgr) from emp; -- 去重
select max(sal) - min(sal) from emp;
-- 6. 多字句查询 group by > having > order by > limit
select avg(sal) as avg_sal from emp
group by deptno
having avg_sal > 1000
order by avg_sal desc
limit 0, 2;
多表查询
查询
-- 1. 查询员工名,员工工资及所在部门名字
-- 当不适用where条件时,就是两个表或多个表之间的笛卡尔积
-- 笛卡尔积就是 每条记录之间两两组合
select ename, sal, dname, emp.deptno from emp, dept
where emp.deptno = dept.deptno;
-- 2. 查询部门号为10 的 员工名,员工工资及所在部门名字
select ename, sal, dname, emp.deptno from emp, dept
where emp.deptno = dept.deptno
and dept.deptno = 10;
-- 3. 查询各个员工的姓名,工资,及其工资的级别
select ename, sal, grade
from emp, salgrade
where emp.sal between losal and hisal;
自连接
-- 自连接
-- 1. 查询工资员工姓名和他的上级的名字
select worker.ename as worker, boss.ename as boss
from emp worker, emp boss
where worker.mgr = boss.empno;
-- 自连接的特点:
-- 1. 把同一张表当成两张表使用
-- 2. 需要给表取别名, 表别名
-- 3. 列名不明确,可以指定列的别名
子查询
-- 1. 子查询是指嵌入在其他sql语句中的select语句,也叫嵌套查询
-- 2. 单行子查询 是指只返回一行数据的子查询语句
-- 3. 多行子查询 是指返回多行数据的子查询,使用关键字in
-- 子查询
-- 1. 查询与simith同一部门的所有员工
select * from emp
where deptno = (
select deptno from emp
where ename like 'smith');
-- 2. 查询和部门10的工作相同的员工的
-- 名字、岗位、工资、部门号,但不含10自己的
select ename, job, sal, deptno from emp
where deptno <> 10
and job in (
select distinct job from emp
where deptno = 10
);
-- 3. 查询ecshop中各个类别,价格最高的商品
select goods_id, cat_id, goods_name, shop_price
from ecs_goods;
select cat_id, max(shop_price)
from ecs_goods
GROUP BY cat_id;
-- 把子查询当做一张临时表
select goods_id, ecs_goods.cat_id, goods_name, ecs_goods.shop_price
from ecs_goods, (
select cat_id, max(shop_price) as max_price
from ecs_goods
GROUP BY cat_id
) temp
where temp.cat_id = ecs_goods.cat_id
and temp.max_price = ecs_goods.shop_price;
-- all any
-- 4. 查询工资比部门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
);
-- 5. 查询工资比部门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
);
-- 6. 多列子查询
-- 6.1 查询与allen的部门和岗位完全相同的所有员工
select * from emp
where (deptno, job) = (
select deptno, job from emp
where ename = 'allen'
);
-- 6.2 查询与allen的部门和岗位完全相同的所有员工,但不包含allen本人
select * from emp
where (deptno, job) in (
select deptno, job from emp
where ename = 'allen'
) and ename <> 'allen';
表的复制与去重
-- 1. 表的复制
create table emp01 (
empno int not null default 0,
ename varchar(32),
sal double,
job varchar(32),
deptno int
);
desc emp01;
select * from emp01;
-- 1.1 复制emp表给emp01
insert into emp01 (empno, ename, sal, job, deptno)
select empno, ename, sal, job, deptno from emp;
-- 1.2 emp01自复制
insert into emp01
select * from emp01;
select count(*) from emp01;
-- 2. 表的去重
-- 思路:
-- 2.1 创建一个临时表 temp ,此表与emp01相似
create table temp like emp01;
-- 2.2 通过关键字distinc,将emp01中的信息复制个temp表
-- 这个时候 也可 直接删除emp01 然后 修改temp的名字为emp01
insert into temp select distinct * from emp01;
-- 2.3 清空emp01表
delete from emp01;
-- 2.4 将temp表中的信息复制给emp01
insert into emp01 select * from temp;
-- 2.5 将temp表删除
drop table temp;
select * from emp01;
合并查询
-- 1. 合并查询
-- 1.1 union all 直接 合并, 不会去重
select * from emp where sal > 2500 union all
select * from emp where job = 'manager';
-- 1.2 union 合并 自动去重
select * from emp where sal > 2500 union
select * from emp where job = 'manager';
外连接△
-- 外连接
-- 例:显示部门名称、员工名称,部门编号,工作,同时显示没有员工的部门
select dname, ename, dept.deptno,job
from emp, dept
where emp.deptno = dept.deptno
order by dname;
select * from emp;
-- 1. 外连接分为 左外连接和右外连接
create table stu (id int, sname varchar(10));
insert into stu values (1, 'jack'),(2, 'tom'),(3, 'nono'),(4, 'wxy');
select * from stu;
create table exam (id int, grade int);
insert into exam values(1, 80),(2,90),(11,60);
select * from exam;
-- 2. 左外连接:左侧的表完全显示 即使没有匹配
-- 2.1 通过左外连接,显示所有人的成绩,若没有成绩,也显示此人的id和sname,成绩为空
select stu.id, sname, grade
from stu left join exam
on stu.id = exam.id;
-- 3. 右外连接:右侧的表完全显示 即使没有匹配
-- 3.1 通过右外连接,显示所有成绩,若没有人,则名字为空
select exam.id, sname, grade
from stu right join exam
on stu.id = exam.id;
-- 练习:查询部门名称和这些员工信息,同时列出那些没有员工的部门名
select dname, ename, job, dept.deptno
from emp right join dept
on emp.deptno = dept.deptno;
select dname, ename, job, dept.deptno
from dept left join emp
on emp.deptno = dept.deptno;
约束
主键 primary key
- 字段非空,且唯一
- 一张表只能有一个主键,但可以是复合主键
- primary key (id,
name
)
唯一 unique
- 该字段不能重复
- 若无 not null 约束时, null值可以添加多个
- 一张表可以多个unique约束
外键 foreign key
- 外键约束要定义在从表上,主表则必须具有主键约束,或是unique约束,当定义外键约束后,要求外键列数据必须在主表的主键上存在或是为null
- Foreign key (本表字段名) references 主表名(主键名或unique字段名)
- 表的类型必须是innodb,这样的表才支持外键
- 外键字段的类型要和主键字段的类型一致【长度可以不一致】
- 外键字段的值,必须在主键字段中出现过,或者为null【前提是外键字段允许为空】
- 一旦建立外键,数据不能随意删除
校验 check
-
Mysql5.7 可以使用,支持语法,但不会生效 。 oracle 和 sql server支持
-
强制要求字段满足某种条件
-
sex varchar(5) check (sex in ('man', 'woman'))
sal double check (sal > 1000 and sal < 90000)
自增长
id int primary key auto_increment
- 从1开始,自动添加
- 搭配 primary key 和 unique 使用
- 修饰整型
- 若给自增长的值,指定了一个值,以指定值为准
修改约束
-
alter table 表名 modify column 字段名 字段类型 新约束
-
alter table student modify column age int null;
-
alter table 表名 add [constraint 约束名] 约束类型(字段名) [外键的引用]
-
alter table student drop primary key;
create table goods (
goods_id int primary key auto_increment,
goods_name varchar(32) not null default '',
unitprice double not null default 0 check(unitprice >= 1.0 and unitprice <= 9999.99),
category varchar(32),
privider varchar(32) not null default ''
);
create table customer (
customer_id int primary key,
`name` varchar(32) not null default '',
address varchar(32) not null default '',
email varchar(32) unique,
sex varchar(8) not null check (sex in ('男', '女')),
-- sex ENUM('男', '女') not null,
card_id varchar(18) unique
);
create table purchase (
order_id int unsigned primary key,
customer_id int not null default 0,
goods_id int not null default 0,
nums int not null default 0,
foreign key (customer_id) references customer(customer_id),
foreign key (goods_id) references goods(goods_id)
);
desc goods;
desc customer;
desc purchase;
索引△
create index index_name on table_name(column_name)
- 创建索引,会增加表文件的大小,但会优化查询时间 【索引占空间】
- 创建索引只对创建索引的字段有效
索引机制
- 不使用索引时,是对全表进行扫描
- 使用索引,就是创建其他的查找数据结构 例如二叉树,提高查找效率
- 会产生磁盘占用
- 会对insert update delete 产生影响,因为每次修改都需要修改索引,也就是修改数据结构的信息
使用索引
-
主键索引:主键自动的为主索引(类型primary key)
-
唯一索引:unique
-
普通索引:index
-
全文索引:fulltext 适用于MyISAM
建议使用:Solr 和 ElasticSearch ES
一般不适用mysql自带的全文索引
-- 索引使用
create table table01 (
id int,
`name` varchar(32)
);
show indexes from table01;
-- 1. 添加索引
-- 1.1 添加唯一索引
create unique index id_index on table01(id);
-- 1.2 添加普通索引
-- 若某字段值,不会出现重复,则优先使用unique index 因为快
create index id_index on table01(id);
-- 1.3 添加方式2
alter table table01 add index id_index(id);
-- 1.4 添加主键索引
alter table table01 add primary key (id);
-- 2. 删除索引
drop index id_index on table01;
-- 2.1 删除主键索引
alter table table01 drop primary key;
-- 3. 修改索引 就是 先删除再添加
-- 4. 查询索引
show indexes from table01;
show index from table01;
show keys from table01;
创建索引规则
- 较频繁作为查询条件的字段应该创建索引
- 唯一性太差的字段不适合创建索引,即使作为查询条件
- 更新非常频繁的字段不适合创建索引
- 不会出现在where自己中的字段不该创建索引
事务
-
事务用于保证数据的一致性,它由一组相关的dml语句组成,该组的dml语句要么全部成功,要么全部失败。
-
事物和锁:
当执行事务操作时(dml语句),mysql会在表上加锁,防止其他用户改表的数据。
dml (update insert delete)
事务操作
-- mysql 数据库控制台事物的重要操作
-- 1. start transaction; 开始一个事务
-- 2. savepoint 保存点名; 设置保存点
-- 3. rollback to 保存点名; 回退事务
-- 4. rollback; 退回全部事务
-- 5. commit; 提交事务,所有的操作生效,不能回退
create table table03 (
id int,
name varchar(32)
);
select * from table03;
-- 1. 开启事务
start transaction;
-- 2. 设置保存点
savepoint a;
-- 3. dml操作
insert into table03 values(100, 'tom');
-- 4. 再设置一个保存点
savepoint b;
-- 5. dml
insert into table03 values(200,'join');
-- 6. 回退到b
-- 用于取消部分事务,当结束事务时,会自动的删除该事务所定义的所有保存点。
-- 当执行回退事务时,通过制定保存点可以回退到指定的点
-- 当回退到a后,不能再回退到b,先后顺序存在
rollback to b;
rollback to a;
rollback;
-- 7. commit 提交事务 回退失效
-- 使用commit语句可以提交事务,当执行了commit语句后,会确认事务的变化、
-- 结束事务、删除保存点、释放锁、数据生效。
-- 当使用commit语句结束事务后,其它会话将可用查看到事务变化后的新数据
commit;
事务注意事项
- – 注意事项
- – 1. 若不开始事务,默认情况下,dml操作是自动提交,不能回滚
- – 2. 若开始一个事务,若没有创建保存点,可以执行rollback,
- – 默认就是退回到事务开始的状态
- – 3. 可以在这个事务中,创建多个保存点
- – 4. 可以在事务没有提交前,回退到任意保存点
- – 5. mysql的事务机制需要innobb存储引擎支持,myISAM不好使
- – 6. 开始一个事务 用 start transaction;
- – set autocommit = off;
隔离
隔离级别介绍
- 多个连接开启各自事务操作数据库中数据时,数据库系统要负责隔离操作
以保证各个连接在获取数据时的准确性。 - 如果不考虑隔离性,可能会造成:脏读、不可重复读、幻读
2.1 脏读 dirty read : 当一个事务读取另一个事务尚未提交的修改时,产生脏读
2.2 不可重复度 nonrepeatable read:同一查询在同一事务中多次进行,由于其他
提交事务所做的修改或删除,每次返回不同的结果集,此时发生不可重复度
在A事务中对同一数据两次查询不一致,可能原因是在A事务提交之前B事务对该数据进行了操作
2.3 幻读 phantom read : 同一查询在同一事务中多次进行,由于其他提交事务
所做的插入操作,每次返回不同的结果接,此时产生幻读
类似于不可重复读,都是在一个事务周期内读的数据不一致,
区别在于幻读是侧重于插入操作带来的影响,而不可重复读是编辑或者删除带来的影响
隔离级别 | 脏读 | 不可重复读 | 幻读 | 锁 |
---|---|---|---|---|
READ UNCOMMITTED | 可能 | 可能 | 可能 | 不加锁 |
READ COMMITTED | 不可能 | 可能 | 可能 | 不加锁 |
REPEATABLE READ | 不可能 | 不可能 | 可能 | 不加锁 |
SERIALIZABLE | 不可能 | 不可能 | 不可能 | 加锁 |
隔离级别操作
-- 1.1 查看当前mysql的隔离级别
select @@tx_isolation; -- 当前
select @@global.tx_isolation; -- 系统当前
-- 1.2 设置隔离界别
set session transaction isolation level read uncommitted;
-- 系统隔离级别
set global transaction isolation level read uncommitted;
set session transaction isolation level read committed;
set session transaction isolation level repeatable read; -- 系统默认级别
set session transaction isolation level serializable;
-- 1.3 开启事务 start transaction;
-- 1.4 可以通过修改mysql配置文件,设置系统默认隔离级别
事务的acid特性
-
原子性 Atomicity
原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生
-
一致性 Consistency
事务必须使数据库从一个一致性状态变换到另一个一致性状态 -
隔离性 Isolation
事务的隔离性是多个用户并发访问数据库时,数据库为每个用户开启的事务,
不能被其他事务的操作数据所干扰,多个并发事务之间要相互隔离 -
持久性 Durability
持久性是指一个事务一旦被提交,它对数据库中的数据的改变就是永久性的,
接下来即使数据库发生故障也不应该对其有任何影响
存储引擎
基本介绍
- mysql的表类型由存储引擎(Storage Engines) 决定,主要包括MyISAM、innoDB、Memory等
- Mysql数据表主要支持六种类型,分别是:CSV、Memory、ARCHIVE、MRG_MYISAM、MYISAM、InnoDB。
- 这六种又分为两类,一类是 “事务安全型”, 比如 innoDB;
其余都属于第二类,称为“非事务安全型”
-- 1. 显示当前数据库支持的存储引擎
show engines;
-- 2. 修改存储引擎
alter table table_name engine = engines_name;
视图
基本概念
- 视图是一个虚拟表,其内容由查询定义。同真实的表一样,视图包含列,其数据来自对应的真实表(基表)
- 视图和基表关系
通过视图可以修改基表的数据
基表的改变,也会影响视图的数据 - 创建视图后,到数据库中,对应视图只有一个视图结构文件
基本使用
-- 视图的基本使用
-- 1. create view 视图名 as select语句
-- 2. alter view 视图名 as select语句
-- 3. show create view 视图名
-- 4. drop view 视图名1, 视图名2
-- 1. 创建视图
create view emp_view01 as
select empno, ename, job, deptno from emp;
-- 2. 查看视图
desc emp_view01;
-- 3. 查询
select * from emp_view01;
select empno, job from emp01 where empno = 1;
-- 4. 修改视图
alter view emp_view01 as
select empno, ename, job, deptno from emp;
-- 5. 查看创建视图的指令
show create view emp_view01;
-- 6. 删除视图
drop view emp_view01;
-- 修改视图
update emp_view01 set job = 'manager' where empno = 7369;
update emp_view01 set job = 'salesman' where empno = 7369;
select * from emp;
select * from emp_view01;
-- 利用视图创建视图
create view emo_view02 as
select empno,deptno from emp_view01;
create view three_table as
select empno, ename, dept.deptno, dname, emp.sal, salgrade.grade
from emp, dept, salgrade
where emp.deptno = dept.deptno
and emp.sal between losal and hisal;
select * from three_table;
视图最佳实践
- 安全:一些数据表有着重要的信息,有些字段是保密的,不能让用户直接看到。这时就可以创建一个视图,在这张视图中只保留一部分字段,这样用户就可以查询自己需要的字段,不能查看保密的字段
- 性能:关系数据库的数据常常会分表查询,使用外键建立这些表的之间关系。这时,数据库查询通常会用到连接,这样做不但麻烦,效率相对也比较低。如果建立一个视图,将相关的表和字段组合在一起就可以避免使用join查询数据
- 灵活:如果系统中有一张旧的表,这张表由于设计的问题,即将被废弃,然而,很多应用都是基于这张表,不易修改。这时就可以建立一张视图,视图中的数据直接映射到新建的表。这样就可以少做很多改动,也达到了升级数据表的目的
管理
用户管理
- user表的重要字段说明:
- host:允许登录的“位置”,localhost表示该用户只允许本机登录,也可以指定ip地址
- user:用户名
- authentication_string:密码,是通过mysql的password()函数加密之后的密码
-- 1. 创建用户
-- create user '用户名'@'允许登录位置' identified by '密码'
-- 说明:创建用户,同时指定密码
create user 'wxy_study'@'localhost' identified by '123456';
-- 2. 删除用户
-- drop user 'username' @ 'location';
drop user 'wxy_study'@'localhost';
-- 3. 查询用户表
select * from mysql.user;
select `host`, `user`, `authentication_string` from mysql.user;
select password('123456');
-- 4. 登录(控制台或软件登录)
-- 不同的数据库用户,操作的库和表不相同
-- 不同的数据库用户,登录到DBMS后,根据相应的权限,可以操作的数据库和数据对象(表、视图、触发器等)都不一样
-- 5. 修改自己的密码:
-- set password = password('密码');
-- 6. 修改他人的密码:(需要有修改用户密码的权限)
-- set password for '用户名'@'登录位置' = password('密码');
权限管理
权限 | 权限级别 | 权限说明 |
---|---|---|
CREATE | 数据库、表或索引 | 创建数据库、表或索引权限 |
DROP | 数据库或表 | 删除数据库或表权限 |
GRANT OPTION | 数据库、表或保存的程序 | 赋予权限选项 |
REFERENCES | 数据库或表 | |
ALTER | 表 | 更改表,比如添加字段、索引等 |
DELETE | 表 | 删除数据权限 |
INDEX | 表 | 索引权限 |
INSERT | 表 | 插入权限 |
SELECT | 表 | 查询权限 |
UPDATE | 表 | 更新权限 |
CREATE VIEW | 视图 | 创建视图权限 |
SHOW VIEW | 视图 | 查看视图权限 |
ALTER ROUTINE | 存储过程 | 更改存储过程权限 |
CREATE ROUTINE | 存储过程 | 创建存储过程权限 |
EXECUTE | 存储过程 | 执行存储过程权限 |
FILE | 服务器主机上的文件访问 | 文件访问权限 |
CREATE TEMPORARY TABLES | 服务器管理 | 创建临时表权限 |
LOCK TABLES | 服务器管理 | 锁表权限 |
CREATE USER | 服务器管理 | 创建用户权限 |
PROCESS | 服务器管理 | 查看进程权限 |
RELOAD | 服务器管理 | 执行flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, reload等命令的权限 |
REPLICATION CLIENT | 服务器管理 | 复制权限 |
REPLICATION SLAVE | 服务器管理 | 复制权限 |
SHOW DATABASES | 服务器管理 | 查看数据库权限 |
SHUTDOWN | 服务器管理 | 关闭数据库权限 |
SUPER | 服务器管理 | 执行kill线程权限 |
-- 给用户授权
-- 基本语法:
-- 授权用户
-- 1. grant 权限列表 on 库.对象名 to 'username'@'localtion' [identified by 'password']
-- 2. *.* 代表本系统中的所有数据库的所有对象(表,视图,存储过程)
-- 3. 库.* 表示某个数据库中的所有数据对象(表,视图,存储过程等)
-- 4. identified by 可以省略,也可写出
-- 4.1 若用户存在,就修改该用户的密码
-- 4.2 若用户不存在,就创建该用户
-- 回收用户授权
-- 基本语法: revoke 权限列表 on 库.对象名 from 'username'@'localtion';
-- 权限生成指令
-- 基本语法: flush privileges;
create database testdb;
create table news (
id int,
content varchar(32)
);
insert into news values (1, '北京新闻'),(2, '上海新闻'),(3, '广州新闻');
select * from testdb.news;
-- 给 study 分配 查看 news 和添加news 的权限
create user 'study'@'localhost' identified by '123456';
grant select, insert on testdb.news to 'study'@'localhost';
-- 修改密码
set password for 'study'@'localhost' = PASSWORD('abc');
-- 回收权限
revoke select on testdb.news from 'study'@'localhost';
revoke all on testdb.news from 'study'@'localhost';
-- 删除用户
drop user 'study'@'localhost'
补充
- 在创建用户时,若不指定host,则为%,%表示所有IP都有连接权限
create user xxx
create user 'xxx'@'192.168.11.%' 表示xxx用户在192.168.11.*的ip可以登录到mysql
- 在删除用户的时候,若host不是%,需要明确指定’username’@‘localtion’