MySQL基本命令

一、连接MySQL

格式： mysql -h主机地址 -u用户名 －p用户密码

1.连接到本机上的MYSQL。

C:\Users\pc>mysql -uroot -p
Enter password: ****

2.连接到远程主机上的MYSQL。

假设远程主机的IP为：192.168.50.46，用户名为root,密码为root。则键入以下命令：

C:\Users\pc>mysql -h192.168.50.46 -uroot -p
Enter password: ****

3.退出MYSQL命令： exit （回车）

mysql> exit
Bye

二、MySQL用户操作

1.创建用户

方法一：CREATE USER语句创建

--格式
CREATE USER "用户名"@"IP地址" IDENTIFIED BY "密码";

--举例
create user "test"@"localhost" identified by "123456";
create user "test4"@"%" identified by "123456";
create user "test5"@"192.168.50.%" identified by "123456";

这种方法创建的用户没有授权，不能对数据库进行任何操作。所以还需要额外给用户授权

grant all privileges on *.* to "test"@"localhost";
flush privileges;

方法二：GRANT语句创建

--格式
GRANT SELECT ON *.* TO 用户名@’IP地址’ IDENTIFIED BY ‘密码’;
FLUSH PRIVILEGES;

--举例
grant all privileges on *.* to "test2"@"localhost" identified by "123456";
grant all privileges on *.* to "test3"@"192.168.50.206" identified by "123456";
flush privileges;

2.查看用户

select user,host from mysql.user;

3.远程连接

上一步我们查看到所有的用户,想要别的客户端能远程连接上本地MySQL,那么要求所使用的用户的host必须能匹配到远程客户端的IP。
我们的远程客户端的IP是192.168.50.206,所以我们可以使用test3,test4,test5用户都能连接到mysql

C:\Users\pc>mysql -h192.168.50.46 -utest5 -p
Enter password: ******
Welcome to the MySQL monitor.

但是不能使用test,test2 因为他们的host都是localhost,只有本机能连上。如果想让他们也能连接上test,test2。只需要修改两个用户的host即可。

mysql> update mysql.user set host="%" where user="test" or user="test2";
mysql> flush privileges; --别忘记刷新权限

--测试test远程连接
mysql -h192.168.50.46 -utest -p
Enter password: ******
Welcome to the MySQL monitor.

--测试test2远程连接
C:\Users\pc>mysql -h192.168.50.46 -utest2 -p
Enter password: ******
Welcome to the MySQL monitor.

4.删除用户

方法一：DROP USER语句删除

--格式
DROP USER "用户名"@"IP地址";

--举例
drop user "test"@"%";

方法二：DELETE语句删除

--格式
DELETE FROM mysql.user WHERE user='用户名' AND host='IP地址';
FLUSH PRIVILEGES;

--举例
delete from mysql.user where user="test2" and host="%";
flush privileges;

5.修改密码

方法一: ALTER命令修改

alter user 'root'@'localhost' identified by 'root';

方法二：UPDATE语句修改

--格式
UPDATE mysql.user SET authentication_string=password(‘new_password’)
       WHERE user='用户名' AND host=’host’;
FLUSH PRIVILEGES;

--举例
update mysql.user set authentication_string=password("456789") 
	where user="test4" and host="%";
flush privileges;

这里需要注意5.7以前版本是password字段，5.7以后改成authentication_string字段存储了。

方法三：SET命令修改

--格式
SET PASSWORD=password(“new_password”);  --修改登录用户的密码
SET PASSWORD for '用户名'@'IP地址' =password(“new_password”); --修改指定用户的密码
FLUSH PRIVILEGES;

--举例
set password for "test3"@"192.168.50.206" =password("123456")
set password=password("123456") --可以不添加

方法四：GRANT命令修改

--格式
GRANT ALL PRIVILEGES ON 库名.表名 to '用户名'@'IP地址' indentified by 'newPassword'
FLUSH PRIVILEGES

--举例
grant all privileges on *.* to "test3"@"192.168.50.206" identified by "new123"
flush privileges;

三、MySQL权限管理

1.授权操作

--格式
grant 权限列表 on 库名.表名 to '用户名'@'客户端主机' [identified by '密码'  with参数];
FLUSH PRIVILEGES

--举例 
--限制用户5每小时的查询数量
grant all privileges on *.* to "test5"@"192.168.50.%" with max_queries_per_hour 5;
flush privileges;

--只开放部分权限
grant select on *.* to "test5"@"192.168.50.%";
flush privileges;

2.查看权限

--格式
show grants for '用户'@'IP地址'

--举例
show grants for 'test4'@'%';

3.回收权限

--格式
REVOKE 权限列表 ON 库名.表名 FROM '用户名'@'客户端主机'
FLUSH PRIVILEGES

--举例

revoke insert,delete,update on *.* from "test4"@"%";--回收增删改的权限
revoke all privileges on *.* from "test4"@"%";--回收所有权限
flush privileges;

要记住回收权限和添加权限根据是库名.表名，如果之前给所有的*.*添加了权限，你回收权限的时候也是以*.*为基准,
意思就是你可以回收*.*的部分权限，例如select,insert,update等等all privileges中的权限，但是不能只回收*.*中部分数据库的权限例如test.*,如下这样写revoke语句是没有作用的。

--给所有库授权
grant all privileges on *.* to "test4"@"%" 

--不能只回收test库的权限,这样写是没有作用的
revoke all privileges on test.* from "test4"@"%"; 

--这样写有效
revoke insert,update privileges on *.* from "test4"@"%" 

4.刷新权限

flush privileges 命令本质上的作用是将当前user和privilige表中的用户信息/权限设置从mysql库(MySQL数据库的内置库)中提取到内存里。
MySQL用户数据和权限有修改后，搜索希望在"不重启MySQL服务"的情况下直接生效，那么就需要执行这个命令

四、数据库操作

create database db_name;  --创建数据库
show databases;           --显示所有的数据库
drop database db_name;    --删除数据库
use db_name;              --选择数据库

五、数据表操作

1.创建表

--语法
CREATE TABLE tab_name(
			 field1 type[完整性约束条件] [comment] [comment注释],
			 field2 type,
			 ...
			 fieldn type,
			 [UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY [别名](属性名1 [(长度)] [ASC|DESC]) 
);
		)[character set xxx]	

--举例
create table employee(
		id int primary key auto_increment,
		name varchar(20),
		gender bit default 1 comment '0:女性 1:男性', --comment后可以加注释,bit位数据类型
		birthday date,
		department varchar(20),
		--double(8,2)表示保留小数2位，最大显示宽度为8;unsigned表示非负数,可以增加数据的长度
		salary double(8,2) unsigned,
		unique key idx_name(name), --创建表的同时添加索引
		key idx_birthday(birthday),
		key idx_dep_sal(department,salary)
)

2.查看表信息

--语法
show tables;   --显示当前数据库中所有表
desc tab_name --显示表结构
show create table tab_name --显示创建表的语句
show table status --显示所有表的属性(引擎类型)

--举例
use test;
show tables;
+----------------+
| Tables_in_test |
+----------------+
| employee       |
+----------------+

--举例
desc employee;
+------------+----------------------+------+-----+---------+----------------+
| Field      | Type                 | Null | Key | Default | Extra          |
+------------+----------------------+------+-----+---------+----------------+
| id         | int(11)              | NO   | PRI | NULL    | auto_increment |
| name       | varchar(20)          | YES  |     | NULL    |                |
| gender     | bit(1)               | YES  |     | b'1'    |                |
| birthday   | date                 | YES  |     | NULL    |                |
| department | varchar(20)          | YES  |     | NULL    |                |
| salary     | double(8,2) unsigned | YES  |     | NULL    |                |
+------------+----------------------+------+-----+---------+----------------+

--举例
mysql> show create table employee;
+----------+----------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                                                              |
+----------+----------------------------------------------------------------------+
| employee | CREATE TABLE `employee` (
			  `id` int(11) NOT NULL AUTO_INCREMENT,
			  `name` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
			  `gender` bit(1) DEFAULT b'1' COMMENT '0:女性 1:男性',
			  `birthday` date DEFAULT NULL,
			  `department` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
			  `salary` double(8,2) unsigned DEFAULT NULL,
			  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+----------+----------------------------------------------------------------------+
1 row in set (0.00 sec)

--举例
mysql> show table status;
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name     | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| employee | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |            0 |         0 |              1 | 2021-09-24 14:39:34 | NULL        | NULL       | utf8_unicode_ci |     NULL |                |         |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+

3.删除表

--格式
drop table tab_name;

4.修改表中结构

(1)添加表中字段(ADD)

--格式
alter table tab_name add [column] 列名 类型［完整性约束条件］［first｜after 字段名］;

--举例
--默认自动添加到数据表字段的末尾
alter table employee add job varchar(20) comment '职位';


--指定新增字段的位置
--FIRST (设定为第一列)
alter table employee add job varchar(20) comment '职位' first;
--AFTER（设定位于某个字段之后）
alter table employee add job varchar(20) comment '职位' after gender;

--添加多个字段
alter table employee add job varchar(20) comment '职位' after gender,add mgr int after department;

(2)删除表中字段(DROP)

--格式
alter table tab_name drop [column] 列名;

--举例
alter table employee drop job   --删除一列
alter table employee drop job,drop department --删除多列

(3)修改表中字段类型及名称(MODIFY|CHANGE)

--格式
--修改字段的类型
alter table tab_name modify 列名 类型 [完整性约束条件]［first｜after 字段名］
--修改字段的名字
alter table tab_name change [column] 列名 新列名 类型 [完整性约束条件]［first｜after 字段名］;

--举例
--修改字段的类型
alter table employee modify name char(10)
--修改类型还能设置约束条件
alter table employee modify mgr int default '0'
--修改列名必须要指定新列名的类型,不然语法不通过
alter table employee change name ename varchar(20)

(4)修改表名(RENAME)

--格式
alter table tab_name rename to new_tab_name;
--举例
alter table employee rename to employee2;

(5)修改数据表属性

--查看表的属性
show table status;
--修改表的属性
alter table employee Engine=MYISAM;

(6)添加索引

--格式
--添加普通索引
alter table 表名 add index index_name(column_name);
--添加主键索引
alter table 表名 add primary key(column_name);
--添加唯一索引
alter table 表名 add unique index_name(column_name);
--添加全文索引
alter table 表名 add fulltext(column_name)
--添加联合索引
alter table table_name add index index_name(column1,column2,column3,..)

--举例
alter table employee add index idx_name(name)
alter table employee add primary key(id)
alter table employee add unique idx_unique_mgr(mgr)
alter table employee add fulltext idx_full_job(job)
alter table employee add index idx_bir_sal(birthday,salary)

(7)删除索引

--格式
alter table table_name drop index index_name;
--举例
alter table employee drop index idx_name

(8)添加外键|删除外键

--格式
alter table table_name add constraint foreign_key_name foreign key(column1) references table_name2(column2)
alter table table_name drop foreign key foreign_key_name

--举例
alter table employee add constraint fk_emp_dept foreign key(deptno) references department(deptno)
alter table employee drop foreign key fk_emp_dept

外键策略
外键是指表中某个字段的值依赖于另一张表中某个字段的值，而被依赖的字段必须具有主键约束或者唯一约束。被依赖的表我们通常称之为父表或者主表，设置外键约束的表称为子表或者从表。
(1)策略1：no action 不允许操作
(2)策略2：cascade 级联操作：操作主表的时候影响从表的外键信息：
(3)策略3：set null 置空操作：

-- 默认为no action，不允许更新和删除外键对应的主表字段。
alter table employee add constraint fk_emp_dept foreign key(deptno) references 
department(deptno)

-- cascade,更新的时候从表自动更新，删除的时候，从表相关的记录也一起删除
alter table employee add constraint fk_emp_dept foreign key(deptno) references 
department(deptno) on update cascade on delete cascade 

-- set null,外键对应的主表字段发生变化时,从表相关记录的外键设置为null。可以跟cascade一起使用
alter table employee add constraint fk_emp_dept foreign key(deptno) references 
department(deptno) on update cascade on delete set null

六、表记录操作

1.增加表记录

--格式
--插入一条记录
insert ［into］ tab_name (field1,filed2,.......) values (value1,value2,.......);
--插入多条记录
insert ［into］ tab_name (field1,filed2,.......) values (value1,value2,.......),
　　　　　　　　　　　　　　　　　　　　　　　　　　　　         (value1,value2,.......), 
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　		...                               

--举例
insert into employee(name,gender,department) values("SMITH",1,"CLERK")
insert into employee(name,gender,department) values("ClAIR",0,"CLERK"),("MIKE",1,"SALE")

2.修改表记录

--格式
update tab_name set field1=value1,field2=value2,......[where 语句]

--举例
update employee set name="JONES" where name="SMITH";

3.删除表记录

方法一: delete

--格式
delete from tab_name [where ....]

--举例
delete from employee where id=1;

-- 注意auto_increment没有被重置
alter table employee auto_increment=1;

方法二: truncate会删除表中所有数据，但是不能删除表结构。

--格式
truncate table tab_name_new;
--举例
truncate table employee

七、查询操作

1.单表查询

employee表

--语法
SELECT *|field1,filed2 ...   FROM tab_name
                  WHERE 条件
                  GROUP BY field
                  HAVING 筛选
                  ORDER BY field
                  LIMIT 限制条数

重点：sql语句的执行顺序

1.from 
2.join 
3.on 
4.where 
5.group by(开始使用select中的别名，后面的语句中都可以使用)
6.avg,sum....一些聚合函数
7.having 
8.select的字段
9.distinct 
10.order by
11.limit 

(1)找到表:from
(2)如果有join，找到联表
(3)找到关联的字段on
(4)拿着where指定的约束条件，去文件/表中取出一条条记录
(5)将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
(6)如果有聚合函数，则将组进行聚合
(7)将6的结果过滤：having
(8)查出结果：select
(9)去重distinct
(10)将8的结果按条件排序：order by
(11)将9的结果限制显示条数limit

1)where约束

where子句中可以使用
1.比较运算符：>、<、>=、<=、!=
2.between 80 and 100 ：值在80到100之间
3.in(80,90,100)值是80或90或100;
4.not in(20,30)值不是20,30
4.like 'xiaopat': pat可以是%或者_。%标识任意多字符，_表示一个字符 x%,%a% ,%t
5.逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not

select * from employee where salary between 1500 and 3000
select * from employee where age in(18,28,38)
select * from employee where depart_id not in(2,3)
select * from employee where name like "%q_"

重要:where使用正则表达式查询

--语法
where column_name regexp '正则表达式'

--匹配name中li开头u或者g结尾的
select * from employee where name regexp '^li.*[ug]$'

2)group by分组查询

1、首先明确一点：分组发生在where之后，即分组是基于where之后得到的记录而进行的
 
2、分组指的是：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等

3、为何要分组呢？
    取每个部门的最高工资
    取每个部门的员工数
    取男人数和女人数
 
小窍门："每"这个字后面的字段，就是我们分组的依据
 
4、大前提：
    可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，如果想查看组内信息，需要借助于聚合函数 

重点:由于sql_mode没有设置ONLY_FULL_GROUP_BY,于是也可以有结果，默认都是组内的第一条记录，但其实这是没有意义的

-- 能显示记录不报错，但是没有实际意义
select * from employee group by post;

--我们设置为严格模式(需要重新连接mysql生效)
show variables like "%mode%";
set global sql_mode="strict_trans_tables,only_full_group_by"

--设置为严格模式后我们只能查询分组的字段,查询其它字段就会报错
select name from employee group by post; --报错
select post from employee group by post; --不报错

--单独使用GROUP BY关键字分组
select post from employee group by post;

--GROUP BY关键字和group_concat()函数一起使用
select post,group_concat(name) from employee group by post;

--GROUP BY与聚合函数一起使用
select post,avg(age) from employee group by post;

group_concat(column1,column2,…)这个能把组内选中字段所有值用,号隔开聚合在一起。

3)聚合函数
先from找到表，再用where的条件约束去表中取出记录，然后进行分组group by，没有分组则默认一组，然后进行聚合

函数	描述
COUNT()	统计表中记录的数目
SUM()	计算指定字段值的总和
AVG()	计算指定字段值的平均值
MAX()	统计指定字段值的最大值
MIN()	统计指定字段值的最小值

--强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组
--每个部门有多少个员工
select post,count(id) from employee group by post;
--每个部门的平均薪水
select post,avg(salary) from employee group by post;
--每个部门的薪水之和
select post,sum(salary) from employee group by post;
--每个部门的最高薪水
select post,max(salary) from employee group by post;
--每个部门的最低薪水
select post,min(salary) from employee group by post;

4)having过滤
having和where语法上是一样的。不一样的地方在于以下几点！！！

where和having的区别                                                                                
     1.Where 是一个约束声明，使用Where约束来自数据库的数据，Where是在结果返回之前起作用的                                        
     （先找到表，按照where的约束条件，从表(文件)中取出数据），Where中不能使用聚合函数                                              
     
     2.Having是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作                                                     
     （先找到表，按照where的约束条件，从表(文件)中取出数据，然后group by分组，                                                
      如果没有group by则所有记录整体为一组，然后执行聚合函数，然后使用having对聚合的结果进行过滤），                                     
      在Having中可以使用聚合函数。      
                                                                          
     3.where的优先级比having的优先级高   
                                                                       
     4.having可以放到group by之后，而where只能放到group by 之前。

-- 验证不同之处
-- 查看员工的id>15的有多少个
select count(id) from employee where id>15; #正确，分析：where先执行，后执行聚合count(id),
select count(id) from employee having id>15; #报错，分析：先执行聚合count(id)，后执行having过滤，#无法对id进行id>15的过滤

-- 以上两条sql的顺序是
1:找到表employee->>>>>用where过滤->>>>>没有分组则默认一组执行聚合count(id)->>>>>select执行查看组内id数目
2:找到表employee->>>>>没有分组则默认一组执行聚合count(id)->>>>>having 基于上一步聚合的结果（此时只有count(id)字段了）
进行id>15的过滤，很明显，根本无法获取到id字段

--小练习
1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
select post,count(id),group_concat(name) from employee group by post having count(id)<2;

2. 查询各岗位平均薪资大于10000的岗位名、平均工资
select post,avg(salary) from employee group by post having avg(salary)>10000;

3. 查询各岗位平均薪资大于等于10000且小于等于20000的岗位名、平均工资
select post,avg(salary) from employee group by post having avg(salary) between 10000 and 20000;

5)order by查询排序

--单列排序
select * from employee order by age 	--默认升序
select * from employee order by age asc  	--升序 
select * from employee order by salary desc 	--降序

--多列排序
select * from employee order by age asc,salary desc  --age升序,salary降序

6)limit限制查询的记录数

select * from employee limit 3  --只显示记录的前3条
select * from employee limit 0,5 --从0也就是第1条开始,往后查5条
select * from employee limit 5,5 --从第6条开始往后查5条

2.多表查询

1.多表连接查询

--语法
SELECT 字段列表
    FROM 表1 CROSS|NATURAL JOIN 表2
    
SELECT 字段列表
    FROM 表1 INNER|LEFT|RIGHT JOIN 表2
    ON 表1.字段 = 表2.字段;

(1)先看第一种情况交叉连接：不适用任何匹配条件,生成笛卡尔积

SELECT * FROM emp corss join dept;   --99语法笛卡尔积
SELECT * FROM emp join dept; --cross可以省略
select * from emp,dept --92语法的笛卡尔积

(2)自然连接:自动匹配所有的同名列，同名列只展示一次

SELECT * FROM emp natural join dept;  

(3)内连接：只连接匹配的行

select * from emp inner join dept on emp.deptno=dept.deptno --99语法的内连接
select * from emp join dept on emp.deptno=dept.deptno --inner可以省略
select * from emp e,dept d where e.deptno=d.deptno --92语法,相当于99语法的内连接

(4)外链接之左连接：优先显示左表全部记录
本质就是：在内连接的基础上增加左边有，右边没有的结果

select * from emp left join dept on emp.deptno=dept.deptno

(5)外链接之右连接：优先显示右表全部记录
本质就是：在内连接的基础上增加右边有，左边没有的结果

select * from emp right join dept on emp.deptno=dept.deptno

(6)全外连接：显示左右两个表全部记录(了解)

select * from emp left join dept on emp.deptno=dept.deptno
union 
select * from emp right join dept on emp.deptno=dept.deptno;

3.子查询

子查询是将一个查询语句嵌套在另一个查询语句中，内层查询语句的查询结果，可以为外层查询语句提供查询条件。
子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字，还可以包含比较运算符：= 、!=、> 、<等
(1)不相关单行子查询

--查询工资高于平均工资的雇员名字和工资。
select ename,sal from emp where sal > (select avg(sal) from emp);

EXISTS关键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。而是返回一个真假值，True或False；
当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询。

#如果emp表中存在着empno=7369的记录,那么就将所有员工信息查询出来。否则不查询。
select * from emp where exists(select * from emp where empno=7369)

(2)不相关多行子查询

-- 查询【部门20中职务同部门30的雇员一样的】雇员信息。
select * from emp where deptno=30 and 
	job=any(select job from emp where deptno=20 group by job)
select * from emp where deptno=30 and 
	job in(select job from emp where deptno=20 group by job)

-- 查询工资比所有的“SALESMAN”都高的雇员的编号、名字和工资。
select e.empno,e.ename,e.sal from emp e where 
	sal>all(select sal from emp where job="salesman")

(3)相关子查询:一般使用在子查询作为表名使用的情况

--查询每个部门最高工资的员工信息
select e1.* from emp e1 
		inner join 
		(select deptno,max(sal) max_sal from emp group by deptno) e2 
		on e1.deptno=e2.deptno 
		where e1.sal=e2.max_sal order by e1.deptno
		
--查询大于部门内平均薪水的员工名、薪水
select e1.ename,e1.sal from emp e1 
		inner join 
		(select deptno,avg(sal) avg_sal from emp group by deptno) e2 
		on e1.deptno=e2.deptno 
		where e1.sal>e2.avg_sal;

八、视图操作

为什么使用视图
为了提高复杂SQL语句的复用性和表操作的安全性（例如：工资字段不想展示给所有能查看该查询结果的人），MySQL提供了视图特性。

视图本质上就是：一个查询语句，是一个虚拟的表，不存在的表，你查看视图，其实就是查看视图对应的sql语句

视图有如下特点;

1. 视图的列可以来自不同的表，是表的抽象和逻辑意义上建立的新关系。
2. 视图是由基本表（实表）产生的表（虚表）。
3. 视图的建立和删除不影响基本表。
4. 对视图内容的更新（添加、删除和修改）直接影响基本表。
5. 当视图来自多个基本表时，不允许添加和删除数据。

创建视图
MySQL中，创建视图是通过CREATE VIEW语句实现的。其语法如下：

CREATE [OR REPLACE] [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]
VIEW 视图名[(属性清单)]
AS SELECT语句
[WITH [CASCADED|LOCAL] CHECK OPTION];

参数说明：
（1）ALGORITHM：可选项，表示视图选择的算法。
（2）视图名：表示要创建的视图名称。
（3）属性清单：可选项，指定视图中各个属性的名词，默认情况下与SELECT语句中的查询的属性相同。
（4）SELECT语句：表示一个完整的查询语句，将查询记录导入视图中。
（5）WITH CHECK OPTION：可选项，表示更新视图时要保证在该视图的权限范围之内。

--创建两张表关联的视图
create view emp_dept_view 
as select emp.*,dept.dname,dept.loc from emp join dept on emp.deptno=dept.deptno

查看视图

#执行SHOW TABLES 语句时不仅可以显示表的名字，同时也是显示出视图的名字。
show tables;
#SHOW TABLE STATUS语句不仅会显示表的详细信息，同时也会显示视图的详细信息。
show table status;
#查看视图定义信息
show create view view_name;
#查看视图结构
desc view_name;

删除视图

DROP VIEW [IF EXISTS] view_name;

修改视图

--方法一:create or replace
create or replace view view_name as 查询语句

--方法二:alter
alter view view_name as 查询语句;

利用视图操作基本表
在MySQL中用视图检索数据，这是视图最基本的应用，除此之后还可以通过视图修改基本表中的数据。
检索（查询）数据：
通过视图查询数据，与通过表进行查询完全相同，只不过通过视图查询表更安全，更简单实用。只需要把表名换成视图名即可。

--查看视图
select * from emp_dept_view

利用视图操作基本表数据：
由于视图是“虚表”，所以对视图数据进行的更新操作，实际上是对其基本表数据进行的更新操作。在具体更新视图数据时，需要注意以下两点;

• 对视图数据进行添加、删除直接影响基本表。
• 视图来自于多个基本表时，不允许添加、删除数据，但是可以更新数据。

--给单张表的视图添加数据
insert into view_emp(empno,ename) values(8000,"zccheng")
--给多张表的视图添加数据
update emp_dept_view set ename='ss' where empno=7369

--给多张表视图添加数据报下图的错误
delete from emp_dept_view where empno=7369

视图中的添加数据操作、删除数据操作、更新数据操作的语法同表完全相同。只是将表名换成视图名即可。

九、存储过程

1.存储过程是什么？
其实存储过程很简单，无非就是按照其特定的语法，创建存储过程，然后在执行程序的时候，调用就行了。
怎么调用呢？下面是一个创建存储过程的实例

#创建一个模糊匹配的存储过程
create procedure myproc(name varchar(20))
begin
	if name is null or name= "" then 
		select * from emp;
	else 
		select * from emp where ename like concat('%',name,'%');
	end if;  --end if后一定要加分号,不然报错
end

在mysql数据库中函数一栏，可以看到存储过程函数

然后把原本写的sql语句的地方直接换成CALL myproc，就可以执行了，跟执行select * from emp where ename like '%mi%'是一样的结果

call myproc('mi')

2.存储过程和普通sql语句有什么不同？
储存过程就是把sql语句放在数据库创建，然后直接编译，然后程序就可以重复直接调用了。

3.存储过程有什么优缺点？

优点

(1)存储过程在创建的时候直接编译，而sql语句每次使用都要编译，提高执行效率

(2)一个存储过程可以被重复使用。（其实sql语句也可以，没什么卵用）

(3)一条sql语句，可能需要访问几张表，对数据库连接好几次，存储过程只会连接一次

(4)存储的程序是安全的。数据库管理员可以向访问数据库中存储过程的应用程序授予适当的权限，而不向基础数据库表提供任何权限。（大概这就是存储过程存在的原因吧）

缺点

(1)可移植性太差了

(2)对于简单的sql语句，毫无意义

(3)对于只有一类用户的系统安全性毫无意义

(4)团队开发，标准不定好的话，后期维护很麻烦

(5)对于开发和调试都很不方便。

(6)复杂的业务逻辑，用存储过程还是很吃力的

基本上存储过程的优点就是性能更快，但服务器也不会因为这一点性能而变得很慢，所以基本用存储过程的很少，一般情况下，不建议使用，比较麻烦，除非有特定需求。

4.查看存储过程

#通过下面语句可以看到该数据库下所有的存储过程名称
show procedure status
#查看存储过程详细
show create procedure proc_name

5.删除存储过程

drop procedure [if exists] proc_name

6.存储过程参数
基本语法

create procedure 名称([IN|OUT|INOUT] 参数名 参数数据类型 )
begin
.........
end

存储过程的参数类型有：IN,OUT,INOUT，下面分别介绍这个三种类型：

(1)存储过程的传入参数IN

1.传入参数：类型为in,表示该参数的值必须在调用存储过程事指定，如果不显示指定为in,
那么默认就是in类型。
2.IN类型参数一般只用于传入，在调用过程中一般不作为修改和返回
3.如果调用存储过程中需要修改和返回值，可以使用OUT类型参数

create procedure myproc_in(in salary double)
begin
	declare name varchar(20); --声明变量
	select group_concat(ename) into name from emp where sal>salary group by deptno having deptno=20; 
	select name;
end

(2)存储过程的传出参数out

create procedure myproc_out(in id int,out name varchar(20))
begin
	select ename into name from emp where empno=id;
end
-- 传出参数必须传入一个变量,变量是@开头
call myproc_out(7369,@username)
select @username as username

概括：
  1、传出参数：在调用存储过程中，可以改变其值，并可返回；
  2、out是传出参数，不能用于传入参数值；
  3、调用存储过程时，out参数也需要指定，但必须是变量，不能是常量；
  4、如果既需要传入，同时又需要传出，则可以使用INOUT类型参数

(3)存储过程的可变参数INOUT
调用存储过程时，传入id和name,既是传入参数，也是传出参数。

create procedure myproc_inout(inout id int,inout name varchar(20))
begin
	select empno,ename into id,name from emp where empno=id;
end

--调用的时候，我们先给要传入id的变量@eid赋值,再调用
set @eid=7499
call myproc_inout(@eid,@ename)
select @eid,@ename

概括：
  1、可变变量INOUT:调用时可传入值，在调用过程中，可修改其值，同时也可返回值；
  2、INOUT参数集合了IN和OUT类型的参数功能；
  3、INOUT调用时传入的是变量，而不是常量；