mysql获取表名并查询表

一.单表查询

1.查询所有数据（ｓｅｌｅｃｔ * FROM tb_name;）

ｓｅｌｅｃｔ 后面跟上要查询的字段，* 号代表所有的字段，一般来说，查询所有字段是最耗时长的，所以今后查询数据尽可能按需索取。

mysql> ｓｅｌｅｃｔ * from `student`;+------+---------+------+------+----------+| id| name| age| sex| class_id |+------+---------+------+------+----------+| 1000 | yige| NULL |0 |1 || 1001 | liangge | NULL |0 |1 || 1002 | sange| NULL |0 |2 || 1003 | sige| NULL |0 |2 |+------+---------+------+------+----------+

2.查询部分字段数据（ｓｅｌｅｃｔ col_name1,col_name2 FROM tb_name;）

查询字段尽可能查询部分字段，不需要的字段就要不查询出来

mysql> ｓｅｌｅｃｔ `id`,`name` from `student`;+------+---------+| id| name|+------+---------+| 1000 | yige|| 1001 | liangge || 1002 | sange|| 1003 | sige|+------+---------+

3.带条件查询（ｓｅｌｅｃｔ col_name FROM tb_name WHERE 你的要求）

条件查询条件可以是大于等于不等于（>,=,）等等，也可以是更加复杂的判断都是可以的。

mysql> ｓｅｌｅｃｔ `id`,`name` from `student` where `id`=1000;+------+------+| id| name |+------+------+| 1000 | yige |+------+------+

4.取别名（ｓｅｌｅｃｔ col_name AS new_name FROM tab_name AS new_name2）

如果列名或者表名太长，可以给它们取一个别名,可以方便取使用

mysql> ｓｅｌｅｃｔ `s`.`name` FROM `student` AS `s`;+---------+| name|+---------+| yige|| liangge || sange|| sige|+---------+二.多表查询

1.内连接

对两个表格进行链接，并进行带条件的查询（同时查询两个表格中的个别元素）。

（1）对两个表格进行合并（join）

mysql> ｓｅｌｅｃｔ * FROM `student` join `class`;+------+---------+------+------+----------+----+-------+--------+| id| name| age| sex| class_id | id | name| slogan |+------+---------+------+------+----------+----+-------+--------+| 1000 | yige| NULL |0 |1 |1 | cls-1 | NULL|| 1000 | yige| NULL |0 |1 |2 | cls-2 | NULL|| 1001 | liangge | NULL |0 |1 |1 | cls-1 | NULL|| 1001 | liangge | NULL |0 |1 |2 | cls-2 | NULL|| 1002 | sange| NULL |0 |2 |1 | cls-1 | NULL|| 1002 | sange| NULL |0 |2 |2 | cls-2 | NULL|| 1003 | sige| NULL |0 |2 |1 | cls-1 | NULL|| 1003 | sige| NULL |0 |2 |2 | cls-2 | NULL|+------+---------+------+------+----------+----+-------+--------+

（2）对合并后的表格进行查询

mysql> ｓｅｌｅｃｔ `student`.`name`,`student`.`id`,`class`.`name` FROM `student`,`class`;+---------+------+-------+| name| id| name|+---------+------+-------+| yige| 1000 | cls-1 || yige| 1000 | cls-2 || liangge | 1001 | cls-1 || liangge | 1001 | cls-2 || sange| 1002 | cls-1 || sange| 1002 | cls-2 || sige| 1003 | cls-1 || sige| 1003 | cls-2 |+---------+------+-------+

（3）缩写（适合表格名长的）

mysql> ｓｅｌｅｃｔ `s`.`name`,`s`.`id`,`c`.`name` FROM `student` as `s` join `class` as `c`;+---------+------+-------+| name| id| name|+---------+------+-------+| yige| 1000 | cls-1 || yige| 1000 | cls-2 || liangge | 1001 | cls-1 || liangge | 1001 | cls-2 || sange| 1002 | cls-1 || sange| 1002 | cls-2 || sige| 1003 | cls-1 || sige| 1003 | cls-2 |+---------+------+-------+

（4）对合并后表格进行带条件查询（on）

mysql> ｓｅｌｅｃｔ `s`.`name`,`s`.`id`,`c`.`name` FROM `student` as `s` join `class` as `c` on `s`.`class_id`=`c`.`id`;+---------+------+-------+| name| id| name|+---------+------+-------+| yige| 1000 | cls-1 || liangge | 1001 | cls-1 || sange| 1002 | cls-2 || sige| 1003 | cls-2 |+---------+------+-------+

2.外连接（左连接）

（1）左连接

A LEFT JOIN B 会以左边的表为主，展式左边表的所有数据，展式右边表中符合ON子句中条件的数据，没有则为空。

mysql> ｓｅｌｅｃｔ * from `student` join `student_detail` on `student`.`id`=`student_detail`.`student_id`;+------+---------+------+------+----------+----+-------------+---------+------------+| id| name| age| sex| class_id | id | phone| address | student_id |+------+---------+------+------+----------+----+-------------+---------+------------+| 1000 | yige| NULL |0 |1 |1 | 12345678911 | NULL|1000 || 1001 | liangge | NULL |0 |1 |2 | 12345678912 | NULL|1001 || 1002 | sange| NULL |0 |2 |3 | 12345678913 | NULL|1002 || 1003 | sige| NULL |0 |2 |4 | 12345678914 | NULL|1003 |+------+---------+------+------+----------+----+-------------+---------+------------+

（2）右连接

与左连接相反

mysql> ｓｅｌｅｃｔ * from `student` right join `student_detail` on `student`.`id`=`student_detail`.`student_id`;+------+---------+------+------+----------+----+-------------+---------+------------+| id| name| age| sex| class_id | id | phone| address | student_id |+------+---------+------+------+----------+----+-------------+---------+------------+| 1000 | yige| NULL |0 |1 |1 | 12345678911 | NULL|1000 || 1001 | liangge | NULL |0 |1 |2 | 12345678912 | NULL|1001 || 1002 | sange| NULL |0 |2 |3 | 12345678913 | NULL|1002 || 1003 | sige| NULL |0 |2 |4 | 12345678914 | NULL|1003 |+------+---------+------+------+----------+----+-------------+---------+------------+

（3）全连接

UNION 用于合并两个或多个 ｓｅｌｅｃｔ 语句的结果集，并消去表中任何重复行。

mysql> ｓｅｌｅｃｔ `name` from `student`ｕｎｉｏｎｓｅｌｅｃｔ `name` from `course`;+---------+| name|+---------+| yige|| liangge || sange|| sige|| wuge|| liuge|| qige|| python|| linux|| mysql|+---------+

3.子表查询

在一个SQL语句中出现两个SQL语句，就是子表查询，下面这个子表查询是在JOIN这个地方建立查询。（就是对两个不相干的表格进行查询，所有需要使用到第三个表格进行连接。）

mysql> ｓｅｌｅｃｔ `s`.`name`,`e`.`name` from `student` as `s` left join (ｓｅｌｅｃｔ `c`.`name`,`cs`.`student_id` from `course` as `c` join `course_student` as `cs`on `c`.`id`=`cs`.`course_id`) as `e` on `s`.`id`=`e`.`student_id`;+---------+--------+| name| name|+---------+--------+| yige| python || yige| linux|| yige| mysql|| liangge | mysql|| sange| mysql|| sige| NULL|| wuge| NULL|| liuge| NULL|| qige| NULL|+---------+--------+

4.排序

对查询出来的结果进行排序，ASC升序(默认) DESC降序

（1）排序

mysql> ｓｅｌｅｃｔ * from `student` order by `class_id`;+------+---------+------+------+----------+| id| name| age| sex| class_id |+------+---------+------+------+----------+| 1000 | yige| NULL |0 |1 || 1001 | liangge | NULL |0 |1 || 1004 | wuge| NULL |0 |1 || 1002 | sange| NULL |0 |2 || 1003 | sige| NULL |0 |2 || 1005 | liuge| NULL |0 |2 || 1006 | qige| NULL |0 |2 |+------+---------+------+------+----------+

（2）降序

mysql> ｓｅｌｅｃｔ * from `student` order by `class_id` desc;+------+---------+------+------+----------+| id| name| age| sex| class_id |+------+---------+------+------+----------+| 1002 | sange| NULL |0 |2 || 1003 | sige| NULL |0 |2 || 1005 | liuge| NULL |0 |2 || 1006 | qige| NULL |0 |2 || 1000 | yige| NULL |0 |1 || 1001 | liangge | NULL |0 |1 || 1004 | wuge| NULL |0 |1 |+------+---------+------+------+----------+

5.限制行数

对查询出来的结果限制显示的行数

mysql> ｓｅｌｅｃｔ * from `student` order by `class_id` desc limit 3;+------+-------+------+------+----------+| id| name| age| sex| class_id |+------+-------+------+------+----------+| 1006 | qige| NULL |0 |2 || 1005 | liuge | NULL |0 |2 || 1002 | sange | NULL |0 |2 |+------+-------+------+------+----------+

6.分组

分组是个常见的操作，常用于分组统计，使用GROUP BY后，会按照GROUP BY后面的字段进行分组，且必须是明确的字段，不能是*，因此ｓｅｌｅｃｔ后面也不能是*.其次可以使用 HAVING 可以对分组之后的结果进行筛选，注意：HAVING 后的字段必须是ｓｅｌｅｃｔ后出现过的

mysql>ｓｅｌｅｃｔ `c`.`id`,`c`.`name`,count(*) from `class` `c` left join `student` `s` on `c`.`id`=`s`.`class_id` group by `c`.`id`,`c`.`name`;+----+-------+----------+| id | name| count(*) |+----+-------+----------+|1 | cls-1 |3 ||2 | cls-2 |4 |+----+-------+----------+

可将查询出的数据进行按条件查询（having）

mysql>ｓｅｌｅｃｔ `c`.`id`,`c`.`name`,count(*) from `class` `c` left join `student` `s` on `c`.`id`=`s`.`class_id` group by `c`.`id`,`c`.`name` having count(*)>3;+----+-------+----------+| id | name| count(*) |+----+-------+----------+|2 | cls-2 |4 |+----+-------+----------+三.MySQL函数

例如：ABS,MAX,MIN,ROUND,AVG,SUM如：最大值（max）

mysql> ｓｅｌｅｃｔ max(`age`) from `student`;+------------+| max(`age`) |+------------+| 29 |+------------+四.SQL优化

模糊查询（lke）

mysql> ｓｅｌｅｃｔ * from `student` where `name` like ‘%i%‘;+------+---------+------+------+----------+| id| name| age| sex| class_id |+------+---------+------+------+----------+| 1000 | yige| NULL |0 |1 || 1001 | liangge | NULL |0 |1 || 1003 | sige| NULL |0 |2 || 1005 | liuge| NULL |0 |2 || 1006 | qige| NULL |0 |2 || 1008 | jiuge|29 |0 |1 |+------+---------+------+------+----------+五.事务

一个连续不断的过程，如果中途被打断则整个程序都不会被执行。（例如银行取钱）开始事务：begin；结束事务：commit；回滚事务：rollback；（撤销之前操作）