MySQL 多表查询、备份、账户权限

多表查询

子查询：只能得到1个表的数据，另一个表只是作为一个限制条件
多表查询：多个表中的数据
在设计数据表时，那么将现实中的一个实体设计为一个表，但在查询数据时，很多时候会将具有关系的不同实体表中的数据一同取出来。
语法：
select * from 表A,表B 【where 子句】
说明：
从多个表中获取where子句的匹配条件进行获取数据
如果省略where子句，那么得到的结果是一个笛卡尔积

联合查询

语法：
select语句A
union【union选项】
select 语句B
说明：
【union选项】
all 显示所有(包含重复记录)
distinct 去复(默认)
作用：
1、对同一个表的不同的部分进行不同的操作。
2、一般用于对比较大的表进行分表存储，联合查询

连接查询

交差连接

语法：
select * from 表A cross join表B 【where子句】
说明：
如果省略where子句，将是一个迪卡尔积
如果使用where子句，则是在迪卡尔积的基础上筛选满足条件的记录

内连接

语法：
select * from 表A inner join 表B【where子句】

外连接

左外连接
语法：
select * from 表L left join 表R on 连接条件
右外连接
语法：
select * from 表L right join 表R on 连接条件
说明：
1、外连接，必须使用on关键字，指定连接条件
2、from关键字后如果是左外连接第1个表，咱称之为主表，第2个表称之为从表
3、from关键字后如果是右外连接第1个表，咱称之为从表，第2个表称之为主表

using关键字

当进行外连接时，如果两个表的连接字段同名，可以使用
：using(进行连接的同名字段)

自然连接

所谓的自然就是自已找连接条件，找到的依据是同名字段
自然左外连接
语法：
select * from 表A natural left join 表B
自然右外连接
语法：
select * from 表A natural right join 表B
自然左外连接就是左外连接
自然右外连接就是右外连接
注意：
如果想使用自然连接代替外连接，那么作为连接的字段名子相同名，且不作为连接的字段的名子不能相同
对于自然连接会只保留一个同名的字段，且放在第1列
其实using关键字及自然连接就是简化left join或right join的语法

理解：
MySQL提供很多种连接方式，不同的连接方式的区别：
共同点：都是从多个表中进行记录的横向拼接
不同点：拼接的方法不同

子查询：一个select包含另一个select
联合查询：selectA union select B

多表查询：from 表A,表B
交差连接：from 表A cross join表B
内连接：from 表A inner join表B
左外连接：from 表L left join 表R on
右外连接：from 表L right join 表R on

外连接与其他连接
1、其他连接与外连接，当匹配条件成立时结果完成一样
2、外连接会包含，匹配不成功的记录。
3、匹配不成功时，究竟包含什么样的不成功记录，要看使用的left join还是right join
如果是left join保留的是左表的没有匹配成功的记录
如果是right join保留的是右表的没有匹配成功的记录

备份

物理备份（冷备）

备份表或数据库的物理文件

对于myisam存储引擎，需要备份 .frm .myd myi
对于innodb 存储引擎，需要备份 .frm data/ibdata1 或 .ibd

数据备份

只备份数据表中的数据，并没有表结构

备份语法：
select * from 表名 into outfile ‘备份的文件的文件路径’【fields选项】【lines选项】
说明：
fields选项是以fields开头的：
terminated by字符设置字段的结束符默认是\t
enclosed by字符设置字段的包含符默认是’’
escaped by 字符设置字符null值使用什么字符代替默认\N

lines选项是以lines开头的：
terminated by字符设置行的结束符默认是\t
starting by 字符设置行的开始符

mysql> select * from stu into outfile '/Users/uiste/www/back.sql';
Query OK, 9 rows affected (0.01 sec)

备份文件内容如下：
1 it001 zhangsan  1 20  1
2 it002 lisi  1 26  2
3 it003 wangwu  1 23  2
4 it004 zhaoliu 1 30  3
5 it005 tianqi  0 28  1
6 it006 wangwang  1 20  1
7 it007 songjiu 1 18  2
8 it008 张三  女 19  2
9 it009 zhener  1 23  1

增加参数的示例

mysql> select * from stu into outfile '/Users/uiste/www/back2.sql'
    -> fields
    -> terminated by ''
    -> enclosed by '#'
    -> lines
    -> starting by 'BEGIN';
Query OK, 9 rows affected (0.02 sec)

备份文件内容如下：
BEGIN#1##it001##zhangsan##1##20##1#
BEGIN#2##it002##lisi##1##26##2#
BEGIN#3##it003##wangwu##1##23##2#
BEGIN#4##it004##zhaoliu##1##30##3#
BEGIN#5##it005##tianqi##0##28##1#
BEGIN#6##it006##wangwang##1##20##1#
BEGIN#7##it007##songjiu##1##18##2#
BEGIN#8##it008##张三##女##19##2#
BEGIN#9##it009##zhener##1##23##1#

数据还原

load data local infile ‘备份的文件的路径’ into table ‘表名’ [files选项][lines选项]

mysql> delete from stu;
Query OK, 9 rows affected (0.01 sec)

mysql> select * from stu;
Empty set (0.01 sec)

mysql> load data local infile '/Users/uiste/www/back.sql' into table stu;
Query OK, 9 rows affected (0.02 sec)
Records: 9  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from stu;
+----+-------+----------+------+------+------+
| id | s_num | s_name   | sex  | age  | c_id |
+----+-------+----------+------+------+------+
|  1 | it001 | zhangsan | 1    |   20 |    1 |
|  2 | it002 | lisi     | 1    |   26 |    2 |
|  3 | it003 | wangwu   | 1    |   23 |    2 |
|  4 | it004 | zhaoliu  | 1    |   30 |    3 |
|  5 | it005 | tianqi   | 0    |   28 |    1 |
|  6 | it006 | wangwang | 1    |   20 |    1 |
|  7 | it007 | songjiu  | 1    |   18 |    2 |
|  8 | it008 | 张三     | 女   |   19 |    2 |
|  9 | it009 | zhener   | 1    |   23 |    1 |
+----+-------+----------+------+------+------+
9 rows in set (0.01 sec)

第二种方法根据备份内容将限定选项一起加入即可

SQL备份

备份的是sql(包含数据)
语法：
需要在mysql之外(cmd命令行使用)mysqldump工具
mysqldump –uroot –p 数据库名【数据表名】 > 位置

还原：
语法1：在mysql之外
mysql –uroot –p 数据库名 <位置

语法2：在mysql中
source 需要还原的文件

不加引号

权限管理

创建用户：
语法：
create user ‘用户名’@’主机’ identified by ‘密码’;
说明：
主机表示用户所能登陆的位置
取值：
IP 192.168.10.1 只允许该用户此ip地址上登陆
域名 localhost 只允许该用户在本机上登陆
% 对该用户的登陆位置没有任何限制
密码必须使用引号

查看MYSQL的用户
1、进入到MySQL数据库

mysql> create user 'xiaoqiang'@'localhost' identified by '123';
Query OK, 0 rows affected (0.01 sec)

mysql> use mysql;
Database changed
mysql> select host,user,password from user;
+-----------+-----------+-------------------------------------------+
| host      | user      | password                                  |
+-----------+-----------+-------------------------------------------+
| localhost | root      | *7C31BA29248E9330208F65C47A673D863961B220 |
| linux     | root      |                                           |
| localhost |           |                                           |
| linux     |           |                                           |
| localhost | pma       |                                           |
| localhost | xiaoqiang | *7C31BA29248E9330208F65C47A673D863961B220 |
+-----------+-----------+-------------------------------------------+
6 rows in set (0.01 sec)

2、MySQL用户被保存在user表中

授权
语法：
grant 权限|all privileges on 数据库名.数据表名 to ‘user’@‘主机名’

1 2	mysql> grant all privileges on uiste.* to 'xiaoqiang'@'localhost'; Query OK, 0 rows affected (0.01 sec)

查看权限：
语法：
show grants

mysql> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*7C31BA29248E9330208F65C47A673D863961B220' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)

收权限
语法：
revoke 权限|all privileges on 数据库名.数据表名 from ‘user’@‘主机名’

1 2	mysql> revoke all privileges on uiste.* from 'xiaoqiang'@'localhost'; Query OK, 0 rows affected (0.01 sec)

更改用户密码：
语法：
set password for ‘user’@’host’=password(‘密码’);

1 2	mysql> set password for 'xiaoqiang'@'localhost'=password(''); Query OK, 0 rows affected (0.01 sec)

重置管理员密码
1、先停止mysql服务

2、跳过授权表来，启动mysql
语法：
mysqld –skip-grant-tables
说明：
当执行此语句后，窗口会处于挂起状态，不接收用户的任何输入(但不要关闭此窗口)

3、重新开启一个cmd窗口，直接使用mysql登陆(不需要加任何参数)
示例：

4、更改mysql数据库的user数据表中的root用户的密码

5、关闭第2步中的窗口，并在任何管理器中结束mysqld服务

6、正常启动mysql，使用新用户密码登陆

–if判断
drop procedure if exists proc;
create procedure proc(in num int)
begin
if num=1 then
select ‘金牌会员’ as ‘等级’ from dual;
elseif num=2 then
select ‘普通会员’ as ‘等级’ from dual;
elseif num=3 then
select ‘游客’ as ‘等级’ from dual;
else
select ‘输入有误’ from dual;
end if;
end //

–case语句（一）
drop procedure if exists proc;
create procedure proc(in season int)
begin
case season
when 1 then select ‘春天’ as ‘季节’ from dual;
when 2 then select ‘夏天’ as ‘季节’ from dual;
when 3 then select ‘秋天’ as ‘季节’ from dual;
when 4 then select ‘冬天’ as ‘季节’ from dual;
else select ‘输入不正确’ as ‘季节’ from dual;
end case;
end //

–case语句（二）
select stuno,stuname,stusex,writtenexam,case
when writtenexam>=90 then ‘等级A’
when writtenexam>=80 then ‘等级B’
when writtenexam>=70 then ‘等级C’
when writtenexam>=60 then ‘等级D’
when writtenexam is null then ‘缺考’
else ‘等级E’
end as ‘等级’ from stuinfo natural left join stumarks;

–loop循环
drop procedure if exists proc;
create procedure proc(in num int)
begin
declare total int default 0;
declare i int default 1;
aa:loop
set total=total+i;
set i=i+1;
if(i>num) then
leave aa;
end if;
end loop;
select total as ‘结果’ from dual;
end //

–while循环
drop procedure if exists proc;
create procedure proc(in num int)
begin
declare total int default 0;
declare i int default 1;
while i<=num do
set total=total+i;
set i=i+1;
end while;
select total as ‘结果’ from dual;
end //

– repeat循环
drop procedure if exists proc;
create procedure proc(in num int)
begin
declare total int default 0;
declare i int default 1;
repeat
set total=total+i;
set i=i+1;
until i>num end repeat;
select total as ‘结果’ from dual;
end //

– leave、iterate
drop procedure if exists proc;
create procedure proc()
begin
declare i int default 0;
aa:while i<=5 do
set i=i+1;
if(i=3) then
leave aa; –类似于break;
iterate aa; –类似于continue
end if;
select i;
end while;
end //

–coalesce
select stuname,coalesce(writtenexam,’缺考’),coalesce(labexam,’缺考’) from stuinfo natural left join stumarks//

–获取当前时间
select year(now()) ‘年’,month(now()) ‘月’,day(now()) ‘日’,hour(now()) ‘小时’,minute(now()) ‘分钟’,second(now()) ‘秒’//

–

select ADDDATE(now(),INTERVAL 12 day) as ‘日期’

select datediff(now(),’2017-1-1’) as ‘还有几天过年’;

–函数
create function fun() returns varchar(20)
begin
return ‘锄禾日当午’;
end //

drop function if exists fun;
create function fun(num1 int,num2 int)returns int
begin
declare num int default 0;
set num=num1+num2;
return num;
end //

–insert触发器
create table t1(num int);
create table t2(num int);

create trigger trig1
after insert on t1 for each row
begin
insert into t2 set num=new.num*new.num;
end //

–insert触发器
drop table transinfo,bank;
create table bank(
cardid char(4) primary key,
name varchar(20) not null,
money int not null
);
create table transinfo(
id int auto_increment primary key,
cardid char(4) not null,
type char(2) not null,
money int not null,
transdate datetime
);
insert into bank values (‘1001’,’tom’,1000),(‘1002’,’berry’,1) //

create trigger trig_transinfo_insert
before insert on transinfo for each row
begin
declare mytype,mycardid char(4);
declare mymoney int;
set mytype=new.type;
set mycardid=new.cardid;
set mymoney=new.money;
if mytype=’支取’ then
update bank set money=money-mymoney where cardid=mycardid;
else
update bank set money=money+mymoney where cardid=mycardid;
end if;
end //

–delete触发器
create table temp like transinfo//
create trigger trig_transinfo_delete
after delete on transinfo for each row
begin
insert into temp values (null,old.cardid,old.type,old.money,old.transdate);
end //

–upate触发器
drop table if exists stuinfo1,stumarks1;
create table stuinfo1(
id int auto_increment primary key,
name varchar(20) not null,
sex char(1) not null,
grade varchar(10) not null
);
insert into stuinfo1 values (null,’tom’,’男’,’未知’);
create table stumarks1(
id int primary key,
ch int,
math int
);
insert into stumarks1 values (1,77,88) //

create trigger trig_stumarks1_update
after update on stumarks1 for each row
begin
declare ch,math int;
declare avgscore decimal(3,1);
declare sid int;
set ch=new.ch;
set math=new.math;
set sid=new.id;
set avgscore=(ch+math)/2;
update stuinfo1 set grade=case
when avgscore>=90 then ‘优秀’
when avgscore>=80 then ‘良好’
when avgscore>=70 then ‘一般’
when avgscore>=60 then ‘及格’
else ‘不及格’
end where id=sid;
end //