myslq基本语法(3)
一.多表连接
当需要的查询的数据不是来自一张表，二十来自多张表时，就需要用到多表连接查询
select t1.*,t2.teacher from it_student t1, it_subject t2 where t1.subject = t2.name;
mysql> select t1.*,t2.teacher from it_student t1, it_subject t2 where t1.subject = t2.name; +----+----------+------+-------+----------+---------+------------+ | id | name | age | sex | salary | subject | teacher | +----+----------+------+-------+----------+---------+------------+ | 1 | xianqian | 23 | woman | 19000.00 | test | wanglaoshi | | 2 | xiaoming | 26 | man | 15000.30 | python | lilaoshi | | 3 | laowang | 29 | man | 16000.50 | python | lilaoshi | | 4 | xiaolong | 27 | man | 28000.00 | python | lilaoshi | | 5 | laowen | 30 | man | 12000.00 | test | wanglaoshi | +----+----------+------+-------+----------+---------+------------+ 5 rows in set (0.00 sec)
注意：
1.设置数据库别名，与列类似，
t1,t2 分别为it_student，it_subject表的别名
2.笛卡儿积：表1的行数 * 表2的行数
3.内连接
缺点：只能显示匹配的行
表1 inner join 表2 on 条件
select t1.*,t2.teacher from it_student t1 inner join it_subject t2 on t1.subject = t2.name;
mysql> select t1.*,t2.teacher from it_student t1 inner join it_subject t2 on t1.subject = t2.name; +----+----------+------+-------+----------+---------+------------+ | id | name | age | sex | salary | subject | teacher | +----+----------+------+-------+----------+---------+------------+ | 1 | xianqian | 23 | woman | 19000.00 | test | wanglaoshi | | 2 | xiaoming | 26 | man | 15000.30 | python | lilaoshi | | 3 | laowang | 29 | man | 16000.50 | python | lilaoshi | | 4 | xiaolong | 27 | man | 28000.00 | python | lilaoshi | | 5 | laowen | 30 | man | 12000.00 | test | wanglaoshi | +----+----------+------+-------+----------+---------+------------+ 5 rows in set (0.00 sec)
4.外连接
左外连接(left join)：左表中的数据能够全部显示
select t1.*,t2.teacher from it_student t1 left join it_subject t2 on t1.subject = t2.name;
mysql> select t1.*,t2.teacher from it_student t1 left join it_subject t2 on t1.subject = t2.name; +----+----------+------+-------+----------+---------+------------+ | id | name | age | sex | salary | subject | teacher | +----+----------+------+-------+----------+---------+------------+ | 2 | xiaoming | 26 | man | 15000.30 | python | lilaoshi | | 3 | laowang | 29 | man | 16000.50 | python | lilaoshi | | 4 | xiaolong | 27 | man | 28000.00 | python | lilaoshi | | 1 | xianqian | 23 | woman | 19000.00 | test | wanglaoshi | | 5 | laowen | 30 | man | 12000.00 | test | wanglaoshi | | 6 | laotian | 27 | man | NULL | NULL | NULL | +----+----------+------+-------+----------+---------+------------+ 6 rows in set (0.00 sec)
右外连接(right join)：右表中的数据能够全部显示
select t1.*,t2.teacher from it_student t1 right join it_subject t2 on t1.subject = t2.name;
mysql> select t1.*,t2.teacher from it_student t1 right join it_subject t2 on t1.subject = t2.name; +------+----------+------+-------+----------+---------+------------+ | id | name | age | sex | salary | subject | teacher | +------+----------+------+-------+----------+---------+------------+ | 1 | xianqian | 23 | woman | 19000.00 | test | wanglaoshi | | 2 | xiaoming | 26 | man | 15000.30 | python | lilaoshi | | 3 | laowang | 29 | man | 16000.50 | python | lilaoshi | | 4 | xiaolong | 27 | man | 28000.00 | python | lilaoshi | | 5 | laowen | 30 | man | 12000.00 | test | wanglaoshi | | NULL | NULL | NULL | NULL | NULL | NULL | houlaoshi | +------+----------+------+-------+----------+---------+------------+ 6 rows in set (0.00 sec)
二.子查询
就是查询语句中嵌套右查询语句
示例：
select * from it_student where salary=(select max(salary) from it_student);
mysql> select * from it_student where salary=(select max(salary) from it_student); +----+----------+------+------+----------+---------+ | id | name | age | sex | salary | subject | +----+----------+------+------+----------+---------+ | 4 | xiaolong | 27 | man | 28000.00 | python | +----+----------+------+------+----------+---------+ 1 row in set (0.00 sec)
select * from it_student where subject in (select name from it_subject where teacher = 'lilaoshi');
mysql> select * from it_student where subject in (select name from it_subject where teacher = 'lilaoshi'); +----+----------+------+------+----------+---------+ | id | name | age | sex | salary | subject | +----+----------+------+------+----------+---------+ | 2 | xiaoming | 26 | man | 15000.30 | python | | 3 | laowang | 29 | man | 16000.50 | python | | 4 | xiaolong | 27 | man | 28000.00 | python | +----+----------+------+------+----------+---------+ 3 rows in set (0.00 sec)
三.事务
1.什么是事务
就是一系列将要执行或正在执行的操作(sql语句)
2.事务安全，一种保护数据的安全机制，就是保证数据的完整性，一致性，事务执行前和执行后，数据是完整的
3.事务的特性(ACID)
(1)原子性(atomicity)
就是把事务中的一系列操作当作一个整体，要么全部执行，要么都不执行
(2)一致性(consistency)
数据从一个状态转到另一个状态，保证每一个状态数据的完整性
(3)隔离性(isolation)
当有多个事务同时进行时，能够保证各个事务之间不会相互影响
(4)持久性(durability)
当事务提交后，就会永久保存在数据库中
4.事务安全机制只在innodb引擎有效，myisam引擎注重查询快
5.事务处理的步骤
(1)事务的启动
start transaction;
(2)执行一系列SQL语句
update it_student set ...
update it_student set ...
(3)commit/rollback
提交或回滚，
commit后所有操作都会永久保存到数据库中
rollback后(2)步骤中执行所有SQL语句都不会写入数据库中
事务处理示例：
mysql> select * from it_student where name='xianqian'; +----+----------+------+-------+----------+---------+ | id | name | age | sex | salary | subject | +----+----------+------+-------+----------+---------+ | 1 | xianqian | 23 | woman | 19000.00 | test | +----+----------+------+-------+----------+---------+ 1 row in set (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> update it_student set salary=salary+5000 where name='xianqian'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from it_student where name='xianqian'; +----+----------+------+-------+----------+---------+ | id | name | age | sex | salary | subject | +----+----------+------+-------+----------+---------+ | 1 | xianqian | 23 | woman | 24000.00 | test | +----+----------+------+-------+----------+---------+ 1 row in set (0.00 sec) mysql> rollback; Query OK, 0 rows affected (0.01 sec) mysql> select * from it_student where name='xianqian'; +----+----------+------+-------+----------+---------+ | id | name | age | sex | salary | subject | +----+----------+------+-------+----------+---------+ | 1 | xianqian | 23 | woman | 19000.00 | test | +----+----------+------+-------+----------+---------+ 1 row in set (0.00 sec)
四.用户管理
1.用户创建
create user '用户名'@'主机信息' identified by '123'; # 本机可以使用localhost或127.0.0.1
create user 'tom'@'localhost' identified by '123';
mysql> create user 'tom'@'localhost' identified by '123'; Query OK, 0 rows affected (0.00 sec)
create user 'tom'@'192.168.211.103' identified by '123';
mysql> create user 'tom'@'192.168.211.103' identified by '123'; Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from mysql.user; +------+------------------+ | user | host | +------+------------------+ | root | 127.0.0.1 | | root | 152.136.89.241 | | tom | 192.168.211.103 | | root | 192.168.39.22 | | root | 36.112.0.68 | | root | ::1 | | jack | localhost | | root | localhost | | tom | localhost | | root | vm\_0\_2\_centos | +------+------------------+ 10 rows in set (0.00 sec)
用户的主机表现形式
'user'@'localhost' 表示user只能在本地通过socket登录数据库
'user'@'192.168.0.1' 表示user用户只能在192.168.0.1登录数据库
'user'@'192.168.0.0/24' 表示user用户可以在该网络任意的主机登录数据库
'user'@'%' 表示user用户可以在所有的机器上登录数据库
2.用户赋权
(1)grant赋权
grant 权限1 [,权限2...] on 数据库名.数据表名 to '用户名'@'主机信息';
grant 权限(field1,field2...) on 数据库名.数据表名 to '用户名'@'主机信息';
grant select,update(age) on db_itheima.it_student to 'tom'@'localhost';
grant all on *.* to 'tom'@'192.168.211.103';
mysql> grant all on *.* to 'tom'@'192.168.211.103'; Query OK, 0 rows affected (0.00 sec)
赋权完成后建议刷新授权表:flush privileges;
查看权限
show grants; # 查看当前用户的权限
show grants for 'tom'@'localhost';
mysql> show grants for 'tom'@'192.168.211.103'; +---------------------------------------------------------------------------------------------------------------------------+ | Grants for tom@192.168.211.103 | +---------------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'tom'@'192.168.211.103' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' | +---------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
注意：
①. *.* 代表所有数据库的所有表
②. all 代表所有权限;usage 代表没有权限，只有登录数据库,只可以使用test或test_*数据库
with grant option 可以让被赋权的用户有权限赋权给其他用户，但是不能超过它本身具有的最大权限
示例：
grant all select,update,delete on db_itheima.* to 'tom'@'localhost';
mysql -u tom -p登录mysql
grant select ,update on db_itheima.* to 'jack'@'loclahost'; # jack的最大权限为select,update,delete on db_itheima.*
(2)创建用户的同时赋权
grant select, update on db_itheima.* to 'harry'@'localhost' identified by '1234';
mysql> grant select, update on db_itheima.* to 'harry'@'localhost' identified by '1234'; Query OK, 0 rows affected (0.00 sec) mysql> select user,host from mysql.user ; +-------+------------------+ | user | host | +-------+------------------+ | root | 127.0.0.1 | | root | 152.136.89.241 | | tom | 192.168.211.103 | | root | 192.168.39.22 | | root | 36.112.0.68 | | root | ::1 | | harry | localhost | | jack | localhost | | root | localhost | | tom | localhost | | root | vm\_0\_2\_centos | +-------+------------------+ 11 rows in set (0.00 sec)
(3)权限保存位置
mysql.user 所有mysql用户的账号和密码，以及用户对全库全表权限（*.*）
mysql.db 非mysql库的授权都保存在此(db.*)
mysql.table_priv 某库某表的授权(db.table)
mysql.columns_priv 某库某表某列的授权(db.table.col1)
mysql.procs_priv 某库存储过程的授权
3.收回权限
用户有什么权限就可以回收什么权限
revoke select on db_itheima.* from 'harry'@'localhost';
mysql> show grants for 'harry'@'localhost' -> ; +--------------------------------------------------------------------------------------------------------------+ | Grants for harry@localhost | +--------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'harry'@'localhost' IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' | | GRANT SELECT, UPDATE ON `db_itheima`.* TO 'harry'@'localhost' | +--------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> revoke select on db_itheima.* from 'harry'@'localhost'; Query OK, 0 rows affected (0.01 sec) mysql> show grants for 'harry'@'localhost'; +--------------------------------------------------------------------------------------------------------------+ | Grants for harry@localhost | +--------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'harry'@'localhost' IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' | | GRANT UPDATE ON `db_itheima`.* TO 'harry'@'localhost' | +--------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
4.删除用户
drop user '用户名'@'主机信息';
drop user 'tom'@'localhost';
delete from mysql.user where user='' and host='localhost' # 删除本地匿名用户
mysql> drop user 'tom'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql> select user,host from mysql.user where user='tom'; +------+-----------------+ | user | host | +------+-----------------+ | tom | 192.168.211.103 | +------+-----------------+ 1 row in set (0.00 sec)
