一.单表查询
1.查询所有数据(SELECT * FROM tb_name;)
SELECT 后面跟上要查询的字段,* 号代表所有的字段,一般来说,查询所有字段是最耗时长的,所以今后查询数据尽可能按需索取。
mysql> select * 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.查询部分字段数据(SELECT col_name1,col_name2 FROM tb_name;)
查询字段尽可能查询部分字段,不需要的字段就要不查询出来
mysql> select `id`,`name` from `student`;
+------+---------+
| id | name |
+------+---------+
| 1000 | yige |
| 1001 | liangge |
| 1002 | sange |
| 1003 | sige |
+------+---------+
3.带条件查询(SELECT col_name FROM tb_name WHERE 你的要求)
条件查询条件可以是大于等于不等于(>,=,<>)等等,也可以是更加复杂的判断都是可以的。
mysql> select `id`,`name` from `student` where `id`=1000;
+------+------+
| id | name |
+------+------+
| 1000 | yige |
+------+------+
4.取别名(SELECT col_name AS new_name FROM tab_name AS new_name2)
如果列名或者表名太长,可以给它们取一个别名,可以方便取使用
mysql> SELECT `s`.`name` FROM `student` AS `s`;
+---------+
| name |
+---------+
| yige |
| liangge |
| sange |
| sige |
+---------+
二.多表查询
1.内连接
对两个表格进行链接,并进行带条件的查询(同时查询两个表格中的个别元素)。
(1)对两个表格进行合并(join)
mysql> SELECT * 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> SELECT `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> SELECT `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> SELECT `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> select * 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> select * 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 用于合并两个或多个 SELECT 语句的结果集,并消去表中任何重复行。
mysql> select `name` from `student` union select `name` from `course`;
+---------+
| name |
+---------+
| yige |
| liangge |
| sange |
| sige |
| wuge |
| liuge |
| qige |
| python |
| linux |
| mysql |
+---------+
3.子表查询
在一个SQL语句中出现两个SQL语句,就是子表查询,下面这个子表查询是在JOIN这个地方建立查询。(就是对两个不相干的表格进行查询,所有需要使用到第三个表格进行连接。)
mysql> select `s`.`name`,`e`.`name` from `student` as `s` left join (select `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> select * 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> select * 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> select * 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后面的字段进行分组,且必须是明确的字段,不能是*,因此SELECT后面也不能是*.其次可以使用 HAVING 可以对分组之后的结果进行筛选,注意:HAVING 后的字段必须是SELECT后出现过的
mysql> select `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> select `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> select max(`age`) from `student`;
+------------+
| max(`age`) |
+------------+
| 29 |
+------------+
四.SQL优化
模糊查询(lke)
mysql> select * 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;(撤销之前操作)