1: 视图
什么是视图
视图是一个虚拟表, 它的内容来源于查询的实表, 本身没有真正的数据;
视图的作用
对于复杂的查询时,每次查询时都需要编写一些重复的查询代码让编写sql的效率低下, 为了解决这个问题,就是提供视图
创建视图
语法: create view view_name as select .....
例: create view v_stu as select stu_no, stu_name, stu_sex, stu_age from student;
查询视图
例: select * from v_stu;
例: select * from (select stu_no, stu_name, stu_sex, stu_age from student) as v_stu;
修改视图
方式一:
语法: create or replace view view_name as select......
例: create or replace view v_stu as select stu_no, stu_name, stu_sex from student; -- or replace 就是替换的意思
方式二:
语法: alter view view_name(column_list) as select column_list from;
例: alter view v_stu(stu_no, stu_name) as select stu_no, stu_name from student;
查看视图
方式一: show table status; --查询当前数据库下的表的状态
show table status where comment = 'view'; --查看视图
方式二: select table_schema, table_name, view_definition from `information_schema`.views;
删除视图
语法: drop view [if exists] view_name
例: drop view if exists v_stu;
例: insert into v_stu values(11, '吕布', '男', '18');
例: update v_stu set stu_name = '小布' where stu_name = '吕布';
例: delete from v_stu where stu_name = '小布';
ps: 对视图的数据操作一样也会影响到实表, 虽然视图支持增,删,改的操作,但是视图主要还是为了查询
*************************************************************************************************************************************
2: 事务
drop table if exists student;
create table student(
stu_name varchar(30) comment '学生姓名',
stu_money decimal(10,2) comment '学生money'
);
insert into student values('小明', 10000);
drop table if exists teacher;
create table teacher(
teacher_name varchar(30) comment '教师姓名',
teacher_money decimal(10,2) comment '教师money'
);
insert into teacher values('张飞', 5000);
需求: 小明同学交出3000元给张飞老师;
起点
第一步: 小明同学的金钱减去3000元
update student set stu_money = stu_money - 3000 where stu_name = '小明';
第二步: 张飞老师的金钱加上3000元
update teacher set teacher_money = teacher_money + 3000 where teacher_name = '张飞';
结束
一组sql就是一个操作单元, 里边的sql则是要完成一个业务
如果整个单元没有错误,则这个操作是成功的
如果里边有任何一个sql操作失败, 则整个操作都是没有用的，这个时候我们应该要把数据回到操作前的状态,这样的特性,就是事务
SQL执行分为两个阶段
1: 执行阶段
2: 把执行的结果提交数据库(默认是自提交的, 就是执行完后会把执行的结果自动的提交);
例: show variables like 'autocommit'; 查看mysql自动提交的状态
例: set autocommit = 0; 关闭自动提交
例: set autocommit = 1; 开启自动提交
例: rollback; 数据回滚
例: commit; 提交
例: start transaction; --关闭自动提交, 如果事务结束了,不管是成功还是失败,那都会把自动提交回到开始的状态
事务的特点
1: 原子性
事务是一个不可分割工作单元，要事务里的操作要么是全部发生，要么是全部不发生
2: 一致性
事务前后数据完整性要保持一致(在开始和结束之间,只受这一个事务的影响)
在第一个窗口执行
set autocommit = 0;
update student set stu_money = stu_money - 3000 where stu_name = '小明';
在第二个窗口执行
update student set stu_money = stu_money - 3000 where stu_name = '小明';
3: 隔离性
多个用户在并发访问数据时, 一个用户的事务不能被其的事务所有干扰, 多个事务间的数据要相互隔离
在第一个窗口执行
start transaction;
update student set stu_money = stu_money - 3000 where stu_name = '小明';
select * from student;
select * from teacher;
在第二个窗口执行
start transaction;
update teacher set teacher_money = teacher_money + 3000 where teacher_name = '张飞';
select * from student;
select * from teacher;
4: 持久性
事务一旦提交,那么它对数据的修改就是永久的
1: 用户权限管理
登陆语法: mysql -h(ip) -u(user) -p(password) -P(端口号)
例: mysql -hlocalhost -uroot -p123 -P3306
例: mysql -hlocalhost -uroot -p -P3306
mysql其实是一个多用户的数据库系统; 按权限可以把用户分为:
root用户: 超级管理员
普通用户: 由root用户创建的用户
在mysql这个默认的数据库里的user表存储着用户的信息;
select host, user, password from user;
创建用户
语法: create user 用户名@'host' identified by 'password';
例: create user mys1@'172.16.70.101' identified by '456';
例: create user mys1@'%' identified by '456';
用户权限可以为两个部分
1: 是否有连接的权限
2: 是否有操作的权限如: create, drop, select, delete, update, insert等等
用户授权
语法: grant 权限1, 权限2... on 数据库名.表名 to 用户名@ip地址 identified by 'password'; --这条语句同时也可以用来创建用户或者修改用户
例: grant create on *.* to mys1@'%' identified by '456';
例: grant select on test.student to mys1@'%' identified by '456';
例: flush privileges;
例: grant all privileges on *.* to mys1@'%' identified by '123';
删除用户权限
语法: revoke 权限1, 权限2... on 数据库名.表名 from 用户名@'ip地址';
例: revoke create on *.* from mys1@'%';
查看用户权限
use mysql;
select user, host, create_priv, select_priv from user; --查看用户是否有创建和查询的权限
删除用户
语法: drop user 用户名@'ip';
drop user mys1@'172.16.70.101';
2: 索引
索引是什么？
索引是一种把数据库中单列或者多列的值进行排列的结构,是帮助MySQL高效查询数据的数据结构
索引作用
提高数据的查询速度
索引特点
一个数据库的对象在数据库中用来加速对表的查询, 可以使用快速的路径访问数据, 这会减少磁盘IO
与表独立存放,但是又不能独立存在,它必须属于某个表,当删除表时,表上的索引也会自动删除。由数据库自动维护
1: 普通索引
语法: create index 索引名 on 表名(字段1,字段2...);
alter table 表名 add/modify index 索引名(字段1, 字段2...);
例: create index teacher_name_index on teacher(teacher_name);
2: 唯一性索引
语法: create unique index 索引 on 表名(字段);
例: create unique index teacher_money_index on teacher(teacher_money);
3: 主键索引
主键索引就是唯一索引的特殊的形式,创建主健的同时也是加了一个主键索引
4: 全文索引(需要MyISAM引擎)
语法: create fulltext index 索引名 on 表名(字段);
alter table 表名 add fulltext 索引名(字段);
查看索引
语法: show index from 表名
例: show index from teacher;
例: show keys from teacher;
例: show indexes from teacher;
删除索引
语法: drop index 索引名 on 表名;
alter table 表名 drop index 索引名
例: drop index teacher_money_index on teacher;
3: 存储引擎
存储引擎也叫表类型, 指的是数据表的存储机制,索引方案等相关功能,
不同的引擎处理的方式也不同,就会带来不同的功能和相对的优化, 需要我们根据实际的业务需求来选择一个合理的存储引擎
例: show engines; --查看mysql里的存储存储
比较常用的存储引擎有: InnoDB引擎, MyISAM引擎, memory引擎
InnoDB存储引擎
1: 外键约束
在mysql里, 只有InnoDB引擎支持外键约束
2: 事务
InnoDB存储引擎是支持事务的标准的mysql存储引擎
3: 支持自动增长列
4: 支持自动灾难恢复等
ps: 如果需要支持事务,则可以选择InnoDB
MyISAM存储引擎
drop table if exists t_1;
create table t_1(
t_id int primary key,
t_name varchar(30)
)engine=MyISAM default charset = utf8;
1: MyISAM引擎不支持事务处理
2: 查询速度快
tb_name.frm: 存储表定义
tb_name.MYD: 存储的数据
tb_name.MYI: 存储索引
Memory存储引擎(相当于内存数据库)
特点就是速度很快, 因为存储介质是系统内存, 但容易造成数据的丢失
drop table if exists t_2;
create table t_2(
t_id int primary key,
t_name varchar(30)
)engine=memory default charset = utf8;
insert into t_2 values(01, '小明');
select * from t_2;
ps: 当数据量少且访问频繁,并且数据是临时的,就算数据丢失也不会有实质性的负面影响, 可以考虑选择Memory存储引擎