MySQL SQL介绍(3)
一、MySQL SQL介绍
1.1.1 select 多表连接查询
1.select 多表连接查询
什么时候用？
需要查询的数据是来自于多张表时。
例子
#人口数量小于100人的城市,所在国家的国土面积(城市名，国家名，国土面积)
mysql> select city.name,country.name ,country.surfacearea
-> from city,country
-> where city.countrycode = country.code
-> and city.population<100;
+-----------+----------+-------------+
| name | name | surfacearea |
+-----------+----------+-------------+
| Adamstown | Pitcairn | 49.00 |
+-----------+----------+-------------+
1 row in set (0.00 sec)
- 怎么去多表连接查询
1、传统的连接：基于where条件
* 找表之间的关系列
* 排列查询条件
2、内连接
join on
3、外连接
- 传统连接（from 后面接多个表，用","号隔开）#不推荐
#人口数量小于100人的城市,所在国家的国土面积(城市名，国家名，国土面积)
mysql> select city.name,country.name ,country.surfacearea
-> from city,country
-> where city.countrycode = country.code
-> and city.population<100;
+-----------+----------+-------------+
| name | name | surfacearea |
+-----------+----------+-------------+
| Adamstown | Pitcairn | 49.00 |
+-----------+----------+-------------+
1 row in set (0.00 sec)
- 内连接（join on）
A B
A.x B.y
1、找表之间的关系列
2、将两表放在join左右
3、将关联条件了放在on后面
4、将所有的查询条件进行罗列
select A.m,B.n
from
A join B
on A.x=B.y
where
group by
order by
limit
#查询人口数量小于100人的国家名，城市名，国土面积
mysql> SELECT country.name,city.name,country.surfacearea
-> FROM
-> city JOIN country
-> ON city.countrycode=country.code
-> WHERE city.population<100;
+----------+-----------+-------------+
| name | name | surfacearea |
+----------+-----------+-------------+
| Pitcairn | Adamstown | 49.00 |
+----------+-----------+-------------+
1 row in set (0.00 sec)
- 内连接（join on）例子
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| chenhj |
| mysql |
| performance_schema |
| school |
| sys |
| wordpress |
| world |
+--------------------+
8 rows in set (0.00 sec)
mysql> use school
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| course |
| score |
| student |
| teacher |
+------------------+
4 rows in set (0.00 sec)
#查询oldguo老师和他教课程名称
1、首先找两张表的关联点，两张表都有tno列
mysql> select * from course;
+------+--------+-----+
| cno | cname | tno |
+------+--------+-----+
| 1001 | linux | 101 |
| 1002 | python | 102 |
| 1003 | mysql | 103 |
+------+--------+-----+
3 rows in set (0.00 sec)
mysql> select * from teacher;
+-----+--------+
| tno | tname |
+-----+--------+
| 101 | oldboy |
| 102 | hesw |
| 103 | oldguo |
+-----+--------+
3 rows in set (0.00 sec)
mysql> select teacher.tno,teacher.tname,course.cname
-> from teacher join course
-> on teacher.tno=course.tno
-> where teacher.tname='oldguo';
+-----+--------+-------+
| tno | tname | cname |
+-----+--------+-------+
| 103 | oldguo | mysql |
+-----+--------+-------+
1 row in set (0.00 sec)
#统计一下每门课程的总成绩找表的关联点
mysql> select * from course;
+------+--------+-----+
| cno | cname | tno |
+------+--------+-----+
| 1001 | linux | 101 |
| 1002 | python | 102 |
| 1003 | mysql | 103 |
+------+--------+-----+
3 rows in set (0.00 sec)
mysql> select * from score;
+-----+------+-------+
| sno | cno | score |
+-----+------+-------+
| 1 | 1001 | 080 |
| 1 | 1002 | 059 |
| 2 | 1002 | 090 |
| 2 | 1003 | 100 |
| 3 | 1001 | 099 |
| 3 | 1003 | 040 |
| 4 | 1001 | 079 |
| 4 | 1002 | 061 |
| 4 | 1003 | 099 |
| 5 | 1003 | 040 |
| 6 | 1001 | 089 |
| 6 | 1003 | 077 |
| 7 | 1001 | 067 |
| 7 | 1003 | 082 |
| 8 | 1001 | 070 |
| 9 | 1003 | 080 |
| 10 | 1003 | 096 |
+-----+------+-------+
17 rows in set (0.00 sec)
mysql> select course.cno,course.cname,sum(score.score)
-> from course join score
-> on course.cno=score.cno
-> group by course.cname;
+------+--------+------------------+
| cno | cname | sum(score.score) |
+------+--------+------------------+
| 1001 | linux | 484 |
| 1003 | mysql | 614 |
| 1002 | python | 210 |
+------+--------+------------------+
3 rows in set (0.00 sec)
报错：ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'school.course.cno' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
原因：group by后面的条件不能代表唯一值
1. 在select后面出现的列，不是分组条件，并且没有在函数中出现。
2. 如果group by 后是主键列或者是唯一条件列，不会报出错误。
#查询oldguo老师教的学生姓名列表
mysql> select teacher.tname,course.cname,student.sname
-> from teacher join course
-> on teacher.tno=course.tno
-> join score on
-> course.cno=score.cno
-> join student on
-> score.sno=student.sno
-> where teacher.tname='oldguo';
+--------+-------+---------+
| tname | cname | sname |
+--------+-------+---------+
| oldguo | mysql | zhang4 |
| oldguo | mysql | li4 |
| oldguo | mysql | wang5 |
| oldguo | mysql | zh4 |
| oldguo | mysql | zhao4 |
| oldguo | mysql | ma6 |
| oldguo | mysql | oldgirl |
| oldguo | mysql | oldp |
+--------+-------+---------+
8 rows in set (0.00 sec)
mysql> select teacher.tname,group_concat(student.sname)
-> from teacher join course
-> on teacher.tno=course.tno
-> join score on
-> course.cno=score.cno
-> join student on
-> score.sno=student.sno
-> where teacher.tname='oldguo';
+--------+---------------------------------------------+
| tname | group_concat(student.sname) |
+--------+---------------------------------------------+
| oldguo | zhang4,li4,wang5,zh4,zhao4,ma6,oldgirl,oldp |
+--------+---------------------------------------------+
1 row in set (0.00 sec)
#查询所有老师教的学生姓名列表
mysql> select teacher.tname,group_concat(student.sname)
-> from teacher join course
-> on teacher.tno=course.tno
-> join score on
-> course.cno=score.cno
-> join student on
-> score.sno=student.sno
-> group by teacher.tno,teacher.tname;
+--------+---------------------------------------------+
| tname | group_concat(student.sname) |
+--------+---------------------------------------------+
| oldboy | li4,wang5,zhao4,ma6,oldboy,zhang3 |
| hesw | zhang4,wang5,zhang3 |
| oldguo | zhang4,li4,wang5,zh4,zhao4,ma6,oldgirl,oldp |
+--------+---------------------------------------------+
3 rows in set (0.00 sec)
#查询oldguo老师教的不及格学生的姓名
mysql> select teacher.tname,group_concat(student.sname)
-> from teacher join course
-> on teacher.tno=course.tno
-> join score
-> on course.cno=score.cno
-> join student
-> on score.sno=student.sno
-> where teacher.tname='oldguo' and score.score <60
-> group by teacher.tno;
+--------+-----------------------------+
| tname | group_concat(student.sname) |
+--------+-----------------------------+
| oldguo | li4,zh4 |
+--------+-----------------------------+
1 row in set (0.00 sec)
#查询所有师教的不及格学生的姓名
mysql> select teacher.tname,group_concat(student.sname)
-> from teacher join course
-> on teacher.tno=course.tno
-> join score
-> on course.cno=score.cno
-> join student
-> on score.sno=student.sno
-> where score.score <60
-> group by teacher.tno;
+--------+-----------------------------+
| tname | group_concat(student.sname) |
+--------+-----------------------------+
| hesw | zhang3 |
| oldguo | zh4,li4 |
+--------+-----------------------------+
2 rows in set (0.00 sec)
- 数据去重（distinct）
mysql> select distinct(cno) from score;
+------+
| cno |
+------+
| 1001 |
| 1002 |
| 1003 |
+------+
3 rows in set (0.00 sec)
mysql> select count(distinct(name)) from world.city;
+-----------------------+
| count(distinct(name)) |
+-----------------------+
| 3998 |
+-----------------------+
1 row in set (0.00 sec)
- 别名应用
1、表别名
mysql> select te.tname,group_concat(stu.sname) #多次调用表名长的表，使用别名方便些
-> from teacher as te join course as co
-> on te.tno=co.tno
-> join score as sc on
-> co.cno=sc.cno
-> join student as stu on
-> sc.sno=stu.sno
-> group by te.tno,te.tname;
+--------+---------------------------------------------+
| tname | group_concat(stu.sname) |
+--------+---------------------------------------------+
| oldboy | ma6,wang5,zhang3,oldboy,zhao4,li4 |
| hesw | zhang4,wang5,zhang3 |
| oldguo | oldgirl,zh4,zhang4,zhao4,wang5,li4,oldp,ma6 |
+--------+---------------------------------------------+
3 rows in set (0.00 sec)
2、列别名
mysql> select count(distinct(name)) as dis from world.city;
+------+
| dis |
+------+
| 3998 |
+------+
1 row in set (0.01 sec)
#注意列名是别名
8.外连接
1、左外连接（left join on）
mysql> select city.name,country.name,country.surfacearea
-> from
-> city left join country
-> on city.countrycode=country.code
-> and city.population<100 limit 10;
+----------------+------+-------------+
| name | name | surfacearea |
+----------------+------+-------------+
| Kabul | NULL | NULL |
| Qandahar | NULL | NULL |
| Herat | NULL | NULL |
| Mazar-e-Sharif | NULL | NULL |
| Amsterdam | NULL | NULL |
| Rotterdam | NULL | NULL |
| Haag | NULL | NULL |
| Utrecht | NULL | NULL |
| Eindhoven | NULL | NULL |
| Tilburg | NULL | NULL |
+----------------+------+-------------+
10 rows in set (0.00 sec)
#只显示左表的数据，右表的数据没有满足条件的null填充
2、由外连接 (right join on)
mysql> select city.name,country.name,country.surfacearea
-> from
-> city right join country
-> on city.countrycode=country.code
-> and city.population<100 limit 10;
+------+----------------------+-------------+
| name | name | surfacearea |
+------+----------------------+-------------+
| NULL | Aruba | 193.00 |
| NULL | Afghanistan | 652090.00 |
| NULL | Angola | 1246700.00 |
| NULL | Anguilla | 96.00 |
| NULL | Albania | 28748.00 |
| NULL | Andorra | 468.00 |
| NULL | Netherlands Antilles | 800.00 |
| NULL | United Arab Emirates | 83600.00 |
| NULL | Argentina | 2780400.00 |
| NULL | Armenia | 29800.00 |
+------+----------------------+-------------+
10 rows in set (0.00 sec)
#只显示右表的数据，左表的数据没有满足条件的null填充
#满足条件的
mysql> select city.name,country.name,country.surfacearea
-> from
-> city left join country
-> on city.countrycode=country.code
-> where city.population<100 limit 10;
+-----------+----------+-------------+
| name | name | surfacearea |
+-----------+----------+-------------+
| Adamstown | Pitcairn | 49.00 |
+-----------+----------+-------------+
1 row in set (0.00 sec)
#应用生产场景通常都使用左外连接（left join on where）
多用于多表连接查询优化。
- information_schema 是一个虚拟的库
元数据？
----> “基表”（无法直接查询和修改的）
----> DDL 进行元数据修改
----> show ,desc(show),information_schema(全局类的统计和查询)
#information_schema table视图的常用列属性
desc tables;
TABLE_SCHEMA 表所在的库
TABLE_NAME 表名
ENGINE 表的存储引擎
TABLE_ROWS 表的行数
AVG_ROW_LENGTH 平均行长度
INDEX_LENGTH 索引的长度
#查询整个数据库中所有的库对应的表名
mysql> select table_schema,table_name from information_schema.tables;
+--------------------+------------------------------------------------------+
| table_schema | table_name |
+--------------------+------------------------------------------------------+
| information_schema | CHARACTER_SETS |
| information_schema | COLLATIONS |
| information_schema | COLLATION_CHARACTER_SET_APPLICABILITY |
| information_schema | COLUMNS |
| information_schema | COLUMN_PRIVILEGES |
| information_schema | ENGINES |
#库表太多只截取一部分
#查询world和school库下的所有表名
mysql> select table_schema,table_name from information_schema.tables
-> where table_schema='world'
-> union all
-> select table_schema,table_name from information_schema.tables
-> where table_schema='school';
+--------------+-----------------+
| table_schema | table_name |
+--------------+-----------------+
| world | city |
| world | country |
| world | countrylanguage |
| school | course |
| school | score |
| school | student |
| school | teacher |
+--------------+-----------------+
7 rows in set (0.00 sec)
#一行排列，“，”分割的方式（group_concat）
mysql> select table_schema,group_concat(table_name) from information_schema.tables
-> where table_schema='world'
-> union all
-> select table_schema,group_concat(table_name) from information_schema.tables
-> where table_schema='school';
+--------------+------------------------------+
| table_schema | group_concat(table_name) |
+--------------+------------------------------+
| world | city,country,countrylanguage |
| school | course,score,student,teacher |
+--------------+------------------------------+
2 rows in set (0.00 sec)
#统计一下每个库下的表的个数
mysql> select table_schema,count(table_name) from
-> information_schema.tables
-> group by table_schema;
+--------------------+-------------------+
| table_schema | count(table_name) |
+--------------------+-------------------+
| chenhj | 3 |
| information_schema | 61 |
| mysql | 31 |
| performance_schema | 87 |
| school | 4 |
| sys | 101 |
| wordpress | 12 |
| world | 3 |
+--------------------+-------------------+
8 rows in set (0.00 sec)
- 生产常用
#计一下每个库下的表的个数以及的真实数据量 #重要
mysql> select table_schema,count(table_name),sum(avg_row_length*table_rows+index_length)/1024/1024 as tool_mb
-> from information_schema.tables
-> group by table_schema;
+--------------------+-------------------+------------+
| table_schema | count(table_name) | tool_mb |
+--------------------+-------------------+------------+
| chenhj | 3 | 0.09375000 |
| information_schema | 61 | NULL |
| mysql | 31 | 1.99886036 |
| performance_schema | 87 | 0.00000000 |
| school | 4 | 0.06248188 |
| sys | 101 | 0.01562500 |
| wordpress | 12 | 0.56247711 |
| world | 3 | 0.76149845 |
+--------------------+-------------------+------------+
8 rows in set, 48 warnings (0.02 sec)
#统计每张表得真实数据量
mysql> select table_name,sum(avg_row_length*table_rows+index_length)/1024/1024 from information_schema.tables group by table_name;
+------------------------------------------------------+-------------------------------------------------------+
| table_name | sum(avg_row_length*table_rows+index_length)/1024/1024 |
+------------------------------------------------------+-------------------------------------------------------+
| accounts | 0.00000000 |
| CHARACTER_SETS | NULL |
| chen | 0.06250000 |
| city | 0.51241684 |
#统计一下数据库的真实数据量
mysql> select sum(avg_row_length*table_rows+index_length)/1024/1024 as total_mb
-> from information_schema.tables; #重点
+------------+
| total_mb |
+------------+
| 3.49469280 |
+------------+
1 row in set, 48 warnings (0.02 sec)
- concat 命令拼接函数(可以自定义格式)
mysql> select user,host from mysql.user;
+---------------+------------+
| user | host |
+---------------+------------+
| kedao | % |
| wordpress | % |
| root | 10.0.0.% |
| zhihu | 10.0.0.% |
| oldboy | 172.16.1.% |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+------------+
8 rows in set (0.00 sec)
mysql> select concat(user,"@",host) from mysql.user; #concat 拼接命令 注意分割符是“,”。
+-------------------------+
| concat(user,"@",host) |
+-------------------------+
| kedao@% |
| wordpress@% |
| root@10.0.0.% |
| zhihu@10.0.0.% |
| oldboy@172.16.1.% |
| mysql.session@localhost |
| mysql.sys@localhost |
| root@localhost |
+-------------------------+
8 rows in set (0.00 sec)
mysql> select concat(user,"@","'",host,"'") from mysql.user;
+-------------------------------+
| concat(user,"@","'",host,"'") |
+-------------------------------+
| kedao@'%' |
| wordpress@'%' |
| root@'10.0.0.%' |
| zhihu@'10.0.0.%' |
| oldboy@'172.16.1.%' |
| mysql.session@'localhost' |
| mysql.sys@'localhost' |
| root@'localhost' |
+-------------------------------+
8 rows in set (0.00 sec)
12 . 利用information 和concat实现分表备份
#模仿以上命令，对整个数据库下的1000张表进行单独备份，
#排除sys，performance，information_schema
SELECT CONCAT("mysqldump -uroot -p123 ",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql")
FROM information_schema.tables
WHERE table_schema NOT IN('sys','performance','information_schema')
INTO OUTFILE '/tmp/bak.sh'; （重点）
mysql> select concat("mysqldump -uroot -p123456 ",table_schema," ",table_name, " >/tmp/",table_schema,"_",table_name,".sql")
-> from information_schema.tables
-> where table_schema not in ('sys','information_schema','performance')
-> into outfile '/tmp/bak.sh';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
#注意在配置文件添加此参数，不然会报错（安全问题）
vim /etc/my.cnf
secure-file-priv=/tmp （/tmp是生产时你指定备份的目录）
mysql> select concat("mysqldump -uroot -p123456 ",table_schema," ",table_name, " >/tmp/",table_schema,"_",table_name,".sql")
-> from information_schema.tables
-> where table_schema not in ('sys','information_schema','performance')
-> into outfile '/tmp/bak.sh';
Query OK, 140 rows affected (0.00 sec)
[root@db01 ~]# ll /tmp/bak.sh
-rw-rw-rw- 1 mysql mysql 15454 Jun 11 13:45 /tmp/bak.sh
[root@db01 ~]# cat /tmp/bak.sh #全是备份语句
mysqldump -uroot -p123456 chenhj chen >/tmp/chenhj_chen.sql
mysqldump -uroot -p123456 chenhj oldchen >/tmp/chenhj_oldchen.sql
mysqldump -uroot -p123456 chenhj stu >/tmp/chenhj_stu.sql
mysqldump -uroot -p123456 mysql columns_priv >/tmp/mysql_columns_priv.sql
mysqldump -uroot -p123456 mysql db >/tmp/mysql_db.sql
mysqldump -uroot -p123456 mysql engine_cost >/tmp/mysql_engine_cost.sql
mysqldump -uroot -p123456 mysql event >/tmp/mysql_event.sql
mysqldump -uroot -p123456 mysql func >/tmp/mysql_func.sql
mysqldump -uroot -p123456 mysql general_log >/tmp/mysql_general_log.sql
mysqldump -uroot -p123456 mysql gtid_executed >/tmp/mysql_gtid_executed.sql
mysqldump -uroot -p123456 mysql help_category >/tmp/mysql_help_category.sql
#执行上面脚本就可以备份完成在写一个打包压缩脚本打包就ok了
#例子：模仿以下语句，批量实现world下所有表的操作语句生成
mysql> select concat("alter table ",table_schema,".",table_name," discard tablespace;")
-> from information_schema.tables
-> where table_schema='world'
-> into outfile '/tmp/discard.sql';
Query OK, 3 rows affected (0.00 sec)
[root@db01 ~]# cat /tmp/discard.sql
alter table world.city discard tablespace;
alter table world.country discard tablespace;
alter table world.countrylanguage discard tablespace;
#alter table world.city discard tablespace;（丢弃表空间）
- 常用的show语句
show databases; 查看所有数据库名
show tables; 查看当前库下的表名
show tables from world; 查看world数据库下的表名
show create database 查看建库语句
show create table 查看建表语句
show grants for root@'localhost' 查看用户权限信息
show charset 查看所有的字符集
show collation 查看校对规则
show full processlist 查看数据库连接情况
show status 查看数据库的整体状态
show status like '%lock%' 模糊查看数据库的整体状态
show variables 查看数据库所有变量情况
show variables like '%innodb%' 查看数据库所有变量情况
show engines 查看所有支持存储引擎
show engine innodb status 查看所有innodb存储引擎状态情况
show binary logs 查看二进制日志情况
show binlog events in 查看二进制日志事件
show relaylog events in 查看relay日志事件
show slave status 查看从库状态
show master status 查看数据库binlog位置信息
show index from 查看表的索引情况