MySQL單表查詢實例詳解

create table student (
 id int unsigned primary key auto_increment,
 name char(12) not null,
 gender enum("male","famale") default "male",
 age tinyint unsigned not null,
 hoc_group char(12) not null,
 html tinyint unsigned not null,
 css tinyint unsigned not null,
 js tinyint unsigned not null,
 sanction enum("大處分","小處分","無")
);

insert into student(name,gender,age,hoc_group,html,css,js,sanction) values
 ("Yunya","male",18,"first",88,93,76,"無"),
 ("Jack","male",17,"second",92,81,88,"無"),
 ("Bella","famale",17,"first",72,68,91,"小處分"),
 ("Dairis","famale",18,"third",89,54,43,"大處分"),
 ("Kyle","famale",19,"fifth",31,24,60,"大處分"),
 ("Alice","famale",16,"second",49,23,58,"無"),
 ("Ken","male",16,"third",33,62,17,"大處分"),
 ("Jason","male",21,"fourth",91,92,90,"無"),
 ("Tom","male",20,"fifth",88,72,91,"無"),
 ("Fiona","famale",19,"fourth",60,71,45,"無");

SELECT DISTINCT(字段名1,字段名2...) FROM 表名
  WHERE 條件
  GROUP BY 字段名
  HAVING 篩選
  ORDER BY 字段名 asc/desc
  LIMIT 限制條數(shù);

 select * from student;
 
+----+--------+--------+-----+-----------+------+-----+----+-----------+
| id | name | gender | age | hoc_group | html | css | js | sanction |
+----+--------+--------+-----+-----------+------+-----+----+-----------+
| 1 | Yunya | male | 18 | first | 88 | 93 | 76 | 無 |
| 2 | Jack | male | 17 | second | 92 | 81 | 88 | 無 |
| 3 | Bella | famale | 17 | first | 72 | 68 | 91 | 小處分 |
| 4 | Dairis | famale | 18 | third | 89 | 54 | 43 | 大處分 |
| 5 | Kyle | famale | 19 | fifth | 31 | 24 | 60 | 大處分 |
| 6 | Alice | famale | 16 | second | 49 | 23 | 58 | 無 |
| 7 | Ken | male | 16 | third | 33 | 62 | 17 | 大處分 |
| 8 | Jason | male | 21 | fourth | 91 | 92 | 90 | 無 |
| 9 | Tom | male | 20 | fifth | 88 | 72 | 91 | 無 |
| 10 | Fiona | famale | 19 | fourth | 60 | 71 | 45 | 無 |
+----+--------+--------+-----+-----------+------+-----+----+-----------+

select name,html,css,js from student;

+--------+------+-----+----+
| name | html | css | js |
+--------+------+-----+----+
| Yunya | 88 | 93 | 76 |
| Jack | 92 | 81 | 88 |
| Bella | 72 | 68 | 91 |
| Dairis | 89 | 54 | 43 |
| Kyle | 31 | 24 | 60 |
| Alice | 49 | 23 | 58 |
| Ken | 33 | 62 | 17 |
| Jason | 91 | 92 | 90 |
| Tom | 88 | 72 | 91 |
| Fiona | 60 | 71 | 45 |
+--------+------+-----+----+

select name as "姓名", gender as "性別", age as "年齡" from student;

+--------+--------+--------+
| 姓名 | 性別 | 年齡 |
+--------+--------+--------+
| Yunya | male | 18 |
| Jack | male | 17 |
| Bella | famale | 17 |
| Dairis | famale | 18 |
| Kyle | famale | 19 |
| Alice | famale | 16 |
| Ken | male | 16 |
| Jason | male | 21 |
| Tom | male | 20 |
| Fiona | famale | 19 |
+--------+--------+--------+

select distinct(hoc_group) from student;

+-----------+
| hoc_group |
+-----------+
| first |
| second |
| third |
| fifth |
| fourth |
+-----------+

select name, html+css+js as 總成績 from student;
+--------+-----------+
| name | 總成績 |
+--------+-----------+
| Yunya | 257 |
| Jack | 261 |
| Bella | 231 |
| Dairis | 186 |
| Kyle | 115 |
| Alice | 130 |
| Ken | 112 |
| Jason | 273 |
| Tom | 251 |
| Fiona | 176 |
+--------+-----------+

select concat("姓名->",name," ","性別->",gender) from student; # 合并成了一個字符串，注意用的空格分隔開的，不然會黏在一起

+--------------------------------------------------+
| concat("姓名->",name," ","性別->",gender) |
+--------------------------------------------------+
| 姓名->Yunya 性別->male  |
| 姓名->Jack 性別->male  |
| 姓名->Bella 性別->famale  |
| 姓名->Dairis 性別->famale  |
| 姓名->Kyle 性別->famale  |
| 姓名->Alice 性別->famale  |
| 姓名->Ken 性別->male  |
| 姓名->Jason 性別->male  |
| 姓名->Tom 性別->male  |
| 姓名->Fiona 性別->famale  |
+--------------------------------------------------+

select concat_ws("|||",name,gender,age) from student; # 使用|||為每個字段進行分割

+----------------------------------+
| concat_ws("|||",name,gender,age) |
+----------------------------------+
| Yunya|||male|||18 |
| Jack|||male|||17  |
| Bella|||famale|||17 |
| Dairis|||famale|||18 |
| Kyle|||famale|||19 |
| Alice|||famale|||16 |
| Ken|||male|||16  |
| Jason|||male|||21 |
| Tom|||male|||20  |
| Fiona|||famale|||19 |
+----------------------------------+

select name, js from student where js > 80;

+-------+----+
| name | js |
+-------+----+
| Jack | 88 |
| Bella | 91 |
| Jason | 90 |
| Tom | 91 |
+-------+----+

select name, html, css, js from student where html > 80 and css > 80 and js > 80;

+-------+------+-----+----+
| name | html | css | js |
+-------+------+-----+----+
| Jack | 92 | 81 | 88 |
| Jason | 91 | 92 | 90 |
+-------+------+-----+----+

select name, gender, age, hoc_group from student where hoc_group in ("first","second");

+-------+--------+-----+-----------+
| name | gender | age | hoc_group |
+-------+--------+-----+-----------+
| Yunya | male | 18 | first |
| Jack | male | 17 | second |
| Bella | famale | 17 | first |
| Alice | famale | 16 | second |
+-------+--------+-----+-----------+

select name, js from student where js between 60 and 80;

+-------+----+
| name | js |
+-------+----+
| Yunya | 76 |
| Kyle | 60 |
+-------+----+

select name from student where name like "k%";

+------+
| name |
+------+
| Kyle |
| Ken |
+------+

select name from student where name like "k__";

+------+
| name |
+------+
| Ken |
+------+

select name from student where name REGEXP "k+";

+------+
| name |
+------+
| Jack |
| Kyle |
| Ken |
+------+

select hoc_group from student group by hoc_group;

+-----------+
| hoc_group |
+-----------+
| fifth |
| first |
| fourth |
| second |
| third |
+-----------+

mysql> select hoc_group from student;
+-----------+
| hoc_group |
+-----------+
| first |
| second |
| first |
| third |
| fifth |
| second |
| third |
| fourth |
| fifth |
| fourth |
+-----------+

mysql> select gender,name from student where js > 80 group by gender;

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'school.student.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

select gender, group_concat(name) from student where js > 80 group by gender;

+--------+--------------------+
| gender | group_concat(name) |
+--------+--------------------+
| male | Jack,Jason,Tom |
| famale | Bella  |
+--------+--------------------+

#查看MySQL 5.7默認的sql_mode如下：
mysql> select @@global.sql_mode;
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

#設(shè)置sql_mole如下操作(我們可以去掉ONLY_FULL_GROUP_BY模式)：
mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';