python--12、数据库进阶

SQL语句关键词：

#再次不做过多介绍

使用INSERT实现数据的插入
UPDATE实现数据的更新
使用DELETE实现数据的删除
使用SELECT查询数据以及。

#示例中department为部门表，employee为员工表。#

多表连接查询

外链接语法

select 字段列 from 表1
inner|left|right join 表2 on 
表1.字段 = 表2.字段

交叉连接

不适用任何匹配条件，生成笛卡尔积
第一个表的每一列对应后面表的所有列

内连接 inner：只连接匹配的行

找两张表共有的部分，相当于利用笛卡尔积结果中筛选除了正确的结果。
若一个表有而另一个表没有，则不会被匹配到。

mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee inner join department on employee.dep_id=department.id; 
或者
mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee,department where employee.dep_id=department.id;

外链接之左连接 left：优先显示左表全部记录

在内连接的基础上增加左边有而右边没有的数据

mysql> select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id;

外链接之右链接 right：优先显示右表全部记录

在内连接的基础上增加右边有而左边没有的数据

mysql> select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id;

全外链接：显示左右两个表全部记录

不管两个表是左边没有还是右边没有，都显示，没有的数据用null填充。
MySQL不支持全外链接。但是可以使用左右连接间接实现

select * from employee left join department on employee.dep_id = department.id
union
select * from employee right join department on employee.dep_id = department.id
;

#注意！ union与union all的区别：union会去掉相同的纪录

符合条件连接查询

以内连接的方式查询，在对应查询语句后加 where，order by等操作。

内连接，并过滤
select employee.name,department.name from employee inner join department
    on employee.dep_id = department.id
    where age > 25;

#以上面为基础，增加排序
select employee.id,employee.name,employee.age,department.name from employee,department
    where employee.dep_id = department.id
    and age > 25
    order by age asc;

子查询

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

带in关键字的子查询

# 查平均年龄25岁以上部门名
select id,name from department where id in (select dep_id from employee group by dep_id having avg(age) > 25);

#查技术部员工名
select name from employee where dep_id in (select id from department where name='技术');

#查看哪些部门不超过一人
select name from department where id in (select dep_id from employee group by dep_id having count(id) <=1);

带有比较运算符的子查询

比较运算符：=、!=、>、>=、<、<=、<>

# 输出大于所有人平均年龄的人的人名和年龄
select name,age from emp where age > (select avg(age)) from emp);

# 查询大于部门内平均年龄的员工名和年龄。
select name,age from emp t1 
inner join (select dep_id,avg(age) avg_age from emp group by dep_id) t2
on t1.dep_id = t2.dep_id where t1.age > t2.avg_age;

带EXISTS关键字的子查询

过滤条件使用EXISTS时，内层查询语句不会反悔查询记录，只返回布尔值。true或false
返回true时外层语句才执行。
返回false时外层语句不执行。

#department表中存在dept_id=203，Ture
mysql> select * from employee
    ->     where exists
    ->         (select id from department where id=200);
+----+------------+--------+------+--------+


#department表中存在dept_id=205，False
mysql> select * from employee
    ->     where exists
    ->         (select id from department where id=204);
Empty set (0.00 sec)

查看MYSQL数据库中所有用户

mysql> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;

查看数据库中具体某个用户的权限

mysql> show grants for 'cactiuser'@'%';

mysql> select * from mysql.user where user='cactiuser' \G

查看user表结构　需要具体的项可结合表结构来查询

mysql> desc mysql.user;

grant语句

GRANT USAGE ON . TO 'jiudou'@'10.173.6.182' IDENTIFIED BY PASSWORD '9F4DCFD752EDB2DD095A080EEF9508444FF42894';
GRANT ALL PRIVILEGES ON stock_data. TO 'jiudou'@'10.173.6.182'

MySQL各种权限

1. usage

连接（登陆）权限，建立一个用户，就会自动授予其usage权限（默认授予）。

mysql> grant usage on . to ‘p1′@’localhost’ identified by ‘123′;

该权限只能用于数据库登陆，不能执行任何操作；且usage权限不能被回收，也即REVOKE用户并不能删除用户。

2. select

必须有select的权限，才可以使用select table

mysql> grant select on pyt.* to ‘p1′@’localhost’;

mysql> select * from shop;

3. create

必须有create的权限，才可以使用create table

mysql> grant create on pyt.* to ‘p1′@’localhost’;

4. create routine

必须具有create routine的权限，才可以使用{create |alter|drop} {procedure|function}

mysql> grant create routine on pyt.* to ‘p1′@’localhost’;

当授予create routine时，自动授予EXECUTE, ALTER ROUTINE权限给它的创建者：

mysql> show grants for ‘p1′@’localhost’;

+—————————————————————————+

Grants for p1@localhost

+————————————————————————–+

| GRANT USAGE ON . TO ‘p1′@’localhost’ IDENTIFIED BY PASSWORD ‘*23AE809DDACAF96AF0FD78ED04B6A265E05AA257′ |

| GRANT SELECT, CREATE, CREATE ROUTINE ON pyt.* TO ‘p1′@’localhost’|

| GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE pyt.pro_shop1 TO ‘p1′@’localhost’ |

+————————————————————————————-+

5. create temporary tables(注意这里是tables，不是table)

必须有create temporary tables的权限，才可以使用create temporary tables.

mysql> grant create temporary tables on pyt.* to ‘p1′@’localhost’;

[mysql@mydev ~]$ mysql -h localhost -u p1 -p pyt

mysql> create temporary table tt1(id int);

6. create view

必须有create view的权限，才可以使用create view

mysql> grant create view on pyt.* to ‘p1′@’localhost’;

mysql> create view v_shop as select price from shop;

7. create user

要使用CREATE USER，必须拥有mysql数据库的全局CREATE USER权限，或拥有INSERT权限。

mysql> grant create user on . to ‘p1′@’localhost’;

或：mysql> grant insert on . to p1@localhost;

8. insert

必须有insert的权限，才可以使用insert into ….. values….

9. alter

必须有alter的权限，才可以使用alter table

alter table shop modify dealer char(15);

10. alter routine

必须具有alter routine的权限，才可以使用{alter |drop} {procedure|function}

mysql>grant alter routine on pyt.* to ‘p1′@’ localhost ‘;

mysql> drop procedure pro_shop;

Query OK, 0 rows affected (0.00 sec)

mysql> revoke alter routine on pyt.* from ‘p1′@’localhost’;

[mysql@mydev ~]$ mysql -h localhost -u p1 -p pyt

mysql> drop procedure pro_shop;

ERROR 1370 (42000): alter routine command denied to user ‘p1′@’localhost’ for routine ‘pyt.pro_shop’

11. update

必须有update的权限，才可以使用update table

mysql> update shop set price=3.5 where article=0001 and dealer=’A';

12. delete

必须有delete的权限，才可以使用delete from ….where….(删除表中的记录)

13. drop

必须有drop的权限，才可以使用drop database db_name; drop table tab_name;

drop view vi_name; drop index in_name;

14. show database

通过show database只能看到你拥有的某些权限的数据库，除非你拥有全局SHOW DATABASES权限。

对于p1@localhost用户来说，没有对mysql数据库的权限，所以以此身份登陆查询时，无法看到mysql数据库：

mysql> show databases;

+——————–+

| Database |

+——————–+

| information_schema|

| pyt |

| test |

+——————–+

15. show view

必须拥有show view权限，才能执行show create view。

mysql> grant show view on pyt.* to p1@localhost;

mysql> show create view v_shop;

16. index

必须拥有index权限，才能执行[create |drop] index

mysql> grant index on pyt.* to p1@localhost;

mysql> create index ix_shop on shop(article);

mysql> drop index ix_shop on shop;

17. excute

执行存在的Functions,Procedures

mysql> call pro_shop1(0001,@a)；

+———+

| article |

+———+

| 0001 |

+———+

mysql> select @a;

+——+

| @a |

+——+

| 2 |

+——+

18. lock tables

必须拥有lock tables权限，才可以使用lock tables

mysql> grant lock tables on pyt.* to p1@localhost;

mysql> lock tables a1 read;

mysql> unlock tables;

19. references

有了REFERENCES权限，用户就可以将其它表的一个字段作为某一个表的外键约束。

20. reload

必须拥有reload权限，才可以执行flush [tables | logs | privileges]

mysql> grant reload on pyt.* to p1@localhost;

ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

mysql> grant reload on . to ‘p1′@’localhost’;

Query OK, 0 rows affected (0.00 sec)

mysql> flush tables;

21. replication client

拥有此权限可以查询master server、slave server状态。

mysql> show master status;

ERROR 1227 (42000): Access denied; you need the SUPER,REPLICATION CLIENT privilege for this operation

mysql> grant Replication client on . to p1@localhost;

或：mysql> grant super on . to p1@localhost;

mysql> show master status;

+——————+———-+————–+——————+

+——————+———-+————–+——————+

| mysql-bin.000006 | 2111 | | |

+——————+———-+————–+——————+

mysql> show slave status;

22. replication slave

拥有此权限可以查看从服务器，从主服务器读取二进制日志。

mysql> show slave hosts;

ERROR 1227 (42000): Access denied; you need the REPLICATION SLAVE privilege for this operation

mysql> show binlog events;

ERROR 1227 (42000): Access denied; you need the REPLICATION SLAVE privilege for this operation

mysql> grant replication slave on . to p1@localhost;

mysql> show slave hosts;

Empty set (0.00 sec)

mysql>show binlog events;

+—————+——-+—————-+———–+————-+————–+

| mysql-bin.000005 | 4 | Format_desc | 1 | 98 | Server ver: 5.0.77-log, Binlog ver: 4 | |mysql-bin.000005|98|Query|1|197|use mysql; create table a1(i int)engine=myisam|

……………………………………

23. Shutdown

关闭MySQL：

[mysql@mydev ~]$ mysqladmin shutdown

重新连接：

[mysql@mydev ~]$ mysql

ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)

[mysql@mydev ~]$ cd /u01/mysql/bin

[mysql@mydev bin]$ ./mysqld_safe &

[mysql@mydev bin]$ mysql

24. grant option

拥有grant option，就可以将自己拥有的权限授予其他用户（仅限于自己已经拥有的权限）

mysql> grant Grant option on pyt.* to p1@localhost;

mysql> grant select on pyt.* to p2@localhost;

25. file

拥有file权限才可以执行 select ..into outfile和load data infile…操作，但是不要把file, process, super权限授予管理员以外的账号，这样存在严重的安全隐患。

mysql> grant file on . to p1@localhost;

mysql> load data infile ‘/home/mysql/pet.txt’ into table pet;

26. super

这个权限允许用户终止任何查询；修改全局变量的SET语句；使用CHANGE MASTER，PURGE MASTER LOGS。

mysql> grant super on . to p1@localhost;

mysql> purge master logs before ‘mysql-bin.000006′;

27. process

通过这个权限，用户可以执行SHOW PROCESSLIST和KILL命令。默认情况下，每个用户都可以执行SHOW PROCESSLIST命令，但是只能查询本用户的进程。

mysql> show processlist;

+—-+——+———–+——+———+——+——-+——————+

+—-+——+———–+——+———+——+——-+——————+

| 12 | p1 | localhost | pyt | Query | 0 | NULL | show processlist |

+—-+——+———–+——+———+——+——-+——————+

另外，管理权限（如 super， process， file等）不能够指定某个数据库，on后面必须跟.

mysql> grant super on pyt.* to p1@localhost;

ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

mysql> grant super on . to p1@localhost;

Query OK, 0 rows affected (0.01 sec)

posted @ 2017-12-09 11:45 jinyudong 阅读(319) 评论(0) 编辑收藏举报

刷新页面返回顶部

墨

python--12、数据库进阶

SQL语句关键词：

多表连接查询

外链接语法

交叉连接

内连接 inner：只连接匹配的行

外链接之左连接 left：优先显示左表全部记录

外链接之右链接 right：优先显示右表全部记录

全外链接：显示左右两个表全部记录

符合条件连接查询

子查询

带in关键字的子查询

带有比较运算符的子查询

带EXISTS关键字的子查询

查看MYSQL数据库中所有用户

查看数据库中具体某个用户的权限

查看user表结构 需要具体的项可结合表结构来查询

grant语句

MySQL各种权限

1. usage

2. select

3. create

4. create routine

5. create temporary tables(注意这里是tables，不是table)

6. create view

7. create user

8. insert

9. alter

10. alter routine

11. update

12. delete

13. drop

14. show database

15. show view

16. index

17. excute

18. lock tables

19. references

20. reload

21. replication client

22. replication slave

23. Shutdown

24. grant option

25. file

26. super

27. process

另外，管理权限（如 super， process， file等）不能够指定某个数据库，on后面必须跟*.*

公告

查看user表结构　需要具体的项可结合表结构来查询

另外，管理权限（如 super， process， file等）不能够指定某个数据库，on后面必须跟.