mysql 查询及子句

select 基本使用

快速入门案例

mysql> create table student(
    -> id int unsigned not null default 1,
    -> name varchar(10) not null default '',
    -> chinese float not null default 0.0,
    -> english float not null default 0.0,
    -> math float not null default 0.0
    -> )charset=utf8 engine=myisam;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into student values(1,'宋江',88,85,95);
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(2,'李逵',81,55,65);
Query OK, 1 row affected (0.02 sec)

mysql> insert into student values(3,'林冲',89,85,95);
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(4,'鲁智深',99,85,95);
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(5,'杨志',97,85,96);
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(6,'晁盖',88,75,95);
Query OK, 1 row affected (0.00 sec)

查询表中所有学生信息

mysql> select * from student;
+----+--------+---------+---------+------+
| id | name   | chinese | english | math |
+----+--------+---------+---------+------+
|  1 | 宋江   |      88 |      85 |   95 |
|  2 | 李逵   |      81 |      55 |   65 |
|  3 | 林冲   |      89 |      85 |   95 |
|  4 | 鲁智深 |      99 |      85 |   95 |
|  5 | 杨志   |      97 |      85 |   96 |
|  6 | 晁盖   |      88 |      75 |   95 |
+----+--------+---------+---------+------+
6 rows in set (0.00 sec)

查询表中所有学生姓名和对应的英语成绩

mysql> select name,english from student;
+--------+---------+
| name   | english |
+--------+---------+
| 宋江   |      85 |
| 李逵   |      55 |
| 林冲   |      85 |
| 鲁智深 |      85 |
| 杨志   |      85 |
| 晁盖   |      75 |
+--------+---------+
6 rows in set (0.00 sec)

使用 distinct 过滤表中重复数据

//查询到的数据完全一样才会过滤
mysql> select distinct * from student;
+----+--------+---------+---------+------+
| id | name   | chinese | english | math |
+----+--------+---------+---------+------+
|  1 | 宋江   |      88 |      85 |   95 |
|  2 | 李逵   |      81 |      55 |   65 |
|  3 | 林冲   |      89 |      85 |   95 |
|  4 | 鲁智深 |      99 |      85 |   95 |
|  5 | 杨志   |      97 |      85 |   96 |
|  6 | 晁盖   |      88 |      75 |   95 |
+----+--------+---------+---------+------+
6 rows in set (0.00 sec)


mysql> select distinct english from student;
+---------+
| english |
+---------+
|      85 |
|      55 |
|      75 |
+---------+
3 rows in set (0.00 sec)

对列进行运算和别名操作

基本语法

• 在select语句中可使用表达式对查询的列进行运算
• 再select语句中可使用as语句

查询每个学生的总分

mysql> select name,chinese+english+math from student;
+--------+----------------------+
| name   | chinese+english+math |
+--------+----------------------+
| 宋江   |                  268 |
| 李逵   |                  201 |
| 林冲   |                  269 |
| 鲁智深 |                  279 |
| 杨志   |                  278 |
| 晁盖   |                  258 |
+--------+----------------------+
6 rows in set (0.00 sec)

使用别名表示学生的分数

mysql> select name,chinese+english+math as '总分' from student;
+--------+------+
| name   | 总分 |
+--------+------+
| 宋江   |  268 |
| 李逵   |  201 |
| 林冲   |  269 |
| 鲁智深 |  279 |
| 杨志   |  278 |
| 晁盖   |  258 |
+--------+------+
6 r**ows in set (0.00 sec)
```**
**查询在姓杨的总分上增加60%**

```go
// like '杨%'，只要是以杨开头的就能匹配到
mysql> select name,(chinese+english+math)*1.6 as '新总分' from student where name like '杨%';
+------+--------+
| name | 新总分 |
+------+--------+
| 杨志 |  444.8 |
+------+--------+
1 row in set (0.00 sec)

where子句的使用

使用WHERE语句来设定查询条件，返回值为true或false

常用运算符
>，<，>=，<=，=，!=	大于、小于、大于等于、小于等于、不等于
between…and…	显示在某一区间的值
in(set)	显示在in列表中的值，例：in(100,200)
like，not like	模糊查询,%任意n个字符，_任意一个字符
is null，is not null	判断是否为空
and，or，not	与，或，非

快速入门案例

mysql> select * from student;
+----+--------+---------+---------+------+
| id | name   | chinese | english | math |
+----+--------+---------+---------+------+
|  1 | 宋江   |      88 |      85 |   95 |
|  2 | 李逵   |      81 |      55 |   65 |
|  3 | 林冲   |      89 |      85 |   95 |
|  4 | 鲁智深 |      99 |      85 |   95 |
|  5 | 杨志   |      97 |      85 |   96 |
|  6 | 晁盖   |      88 |      75 |   95 |
+----+--------+---------+---------+------+
6 rows in set (0.00 sec)

1.查询姓杨的学生成绩（模糊查询）

//模糊查询  记得%
mysql> select * from student where name like '杨%';
+----+------+---------+---------+------+
| id | name | chinese | english | math |
+----+------+---------+---------+------+
|  5 | 杨志 |      97 |      85 |   96 |
+----+------+---------+---------+------+
1 row in set (0.00 sec)

2.查询总分大于260分的同学（比较查询）

// 错误写法
mysql> select name,(chinese+english+math) as '总分' from student where '总分'>260;
Empty set, 1 warning (0.00 sec)
// 正确写法
mysql> select name,(chinese+english+math) as '总分' from student where (chinese+english+math)>260;
+--------+------+
| name   | 总分 |
+--------+------+
| 宋江   |  268 |
| 林冲   |  269 |
| 鲁智深 |  279 |
| 杨志   |  278 |
+--------+------+
4 rows in set (0.00 sec)

3.查询总分大于260分的同学，且数学成绩小于语文成绩的（and 与）

mysql> select name,(chinese+english+math) as '总分',chinese,math from student where (chinese+english+math)>260 and chinese>math;
+--------+------+---------+------+
| name   | 总分 | chinese | math |
+--------+------+---------+------+
| 鲁智深 |  279 |      99 |   95 |
| 杨志   |  278 |      97 |   96 |
+--------+------+---------+------+
2 rows in set (0.00 sec)

4.查询英语成绩在70-80之间的学生（between…and…）

//两种写法完全等价

mysql> select name,english from student where english>=70 and english<=80;
+------+---------+
| name | english |
+------+---------+
| 晁盖 |      75 |
+------+---------+
1 row in set (0.00 sec)

mysql>  select name,english from student where english between 70 and 80;
+------+---------+
| name | english |
+------+---------+
| 晁盖 |      75 |
+------+---------+
1 row in set (0.00 sec)

查询语文成绩为81，89，99的同学（ in ）

mysql> select name,chinese from student where chinese in(81,89,99);
+--------+---------+
| name   | chinese |
+--------+---------+
| 李逵   |      81 |
| 林冲   |      89 |
| 鲁智深 |      99 |
+--------+---------+
3 rows in set (0.00 sec)

//这种写法更好
mysql> select name,chinese from student where chinese=81 or chinese=89 or chinese=99;
+--------+---------+
| name   | chinese |
+--------+---------+
| 李逵   |      81 |
| 林冲   |      89 |
| 鲁智深 |      99 |
+--------+---------+
3 rows in set (0.46 sec)

order by子句的使用

基本用法

• order by 指定排序的列，排序的列既可以是表中的列名，也可以是select语句后指定的别名
• asc 升序（默认）、desc 降序
• order by 子句应位于 select 语句的结尾

快速入门案例

mysql> select * from student;
+----+--------+---------+---------+------+
| id | name   | chinese | english | math |
+----+--------+---------+---------+------+
|  1 | 宋江   |      88 |      85 |   95 |
|  2 | 李逵   |      81 |      55 |   65 |
|  3 | 林冲   |      89 |      85 |   95 |
|  4 | 鲁智深 |      99 |      85 |   95 |
|  5 | 杨志   |      97 |      85 |   96 |
|  6 | 晁盖   |      88 |      75 |   95 |
+----+--------+---------+---------+------+
6 rows in set (0.00 sec)

1.对语文成绩排序后输出（升序）

//升序
mysql> select * from student order by chinese;
+----+--------+---------+---------+------+
| id | name   | chinese | english | math |
+----+--------+---------+---------+------+
|  2 | 李逵   |      81 |      55 |   65 |
|  1 | 宋江   |      88 |      85 |   95 |
|  6 | 晁盖   |      88 |      75 |   95 |
|  3 | 林冲   |      89 |      85 |   95 |
|  5 | 杨志   |      97 |      85 |   96 |
|  4 | 鲁智深 |      99 |      85 |   95 |
+----+--------+---------+---------+------+
6 rows in set (0.00 sec)
//降序
mysql> select * from student order by chinese desc;
+----+--------+---------+---------+------+
| id | name   | chinese | english | math |
+----+--------+---------+---------+------+
|  4 | 鲁智深 |      99 |      85 |   95 |
|  5 | 杨志   |      97 |      85 |   96 |
|  3 | 林冲   |      89 |      85 |   95 |
|  1 | 宋江   |      88 |      85 |   95 |
|  6 | 晁盖   |      88 |      75 |   95 |
|  2 | 李逵   |      81 |      55 |   65 |
+----+--------+---------+---------+------+
6 rows in set (0.00 sec)

2.对总成绩排序后输出（降序）

//如果对别名排序  不要带【引号】
mysql>  select id,name,(chinese+english+math) as '总分' from student order by 总分;
+----+--------+------+
| id | name   | 总分 |
+----+--------+------+
|  2 | 李逵   |  201 |
|  6 | 晁盖   |  258 |
|  1 | 宋江   |  268 |
|  3 | 林冲   |  269 |
|  5 | 杨志   |  278 |
|  4 | 鲁智深 |  279 |
+----+--------+------+
6 rows in set (0.00 sec)

3.按照部门号升序和雇员工资降序排列（emp表）

//我的第一想法使用 分组+排序  实际是错的  （分组分为几组就只能查到几条数据）
mysql> select * from emp order by deptno,sal desc;
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7839 | king   | president | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7782 | clark  | manager   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7934 | miller | clerk     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
|  7902 | ford   | analyst   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7788 | scott  | analyst   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7566 | jones  | manager   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7369 | smith  | clerk     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7698 | blake  | manager   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7499 | allen  | salesman  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7844 | iurner | salesman  | 7698 | 1981-09-08 | 1500.00 |    NULL |     30 |
|  7521 | ward   | salesman  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7654 | martin | salesman  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7900 | james  | clerk     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
+-------+--------+-----------+------+------------+---------+---------+--------+
13 rows in set (0.00 sec)

group by 子句的使用 和 having 子句

having 一般和 group by 配合使用，对结果进行刷选（代替where）

创建部门表

//创建部门表
mysql> create table dept( /*部门表*/
    -> deptno mediumint unsigned not null default 0,
    -> danme varchar(20) not null default '',
    -> loc varchar(13) not null default ''
    -> ) engine=myisam default charset=utf8;
Query OK, 0 rows affected (0.04 sec)

mysql> insert into dept values(10, 'accounting', 'new york'), (20, 'research', 'dallas'), (30, 'sales', 'chicago'), (40,'operations', 'bosion');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from dept;
+--------+------------+----------+
| deptno | danme      | loc      |
+--------+------------+----------+
|     10 | accounting | new york |
|     20 | research   | dallas   |
|     30 | sales      | chicago  |
|     40 | operations | bosion   |
+--------+------------+----------+
4 rows in set (0.00 sec)

创建雇员表

mysql> create table emp(
    -> empno mediumint unsigned not null default 0 comment '编号',
    -> ename varchar(20) not null default '' comment '姓名',
    -> job varchar(9) not null default '' comment '岗位',
    -> mgr mediumint unsigned comment '上级编号',
    -> hiredate date not null comment '入职日期',
    -> sal decimal(7,2) not null comment '工资',
    -> comm decimal(7,2) comment '津贴',
    -> deptno mediumint unsigned not null default 0 comment '部门编号'
    -> ) engine=myisam default charset=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into emp values(7369, 'smith', 'clerk', 7902, '1980-12-17', 800.00, null, 20),
    -> (7499, 'allen', 'salesman', 7698, '1981-02-20', 1600.00, 300.00, 30),
    -> (7521, 'ward', 'salesman', 7698, '1981-02-22', 1250.00, 500.00, 30),
    -> (7566, 'jones', 'manager', 7839, '1981-04-02', 2975.00, null, 20),
    -> (7654, 'martin', 'salesman', 7698, '1981-09-28', 1250.00, 1400.00, 30),
    -> (7698, 'blake', 'manager', 7839, '1981-05-01', 2850.00, null, 30),
    -> (7782, 'clark', 'manager', 7839, '1981-06-09', 2450.00, null, 10),
    -> (7788, 'scott', 'analyst', 7566, '1987-04-19', 3000.00, null, 20),
    -> (7839, 'king', 'president', null, '1981-11-17', 5000.00, null, 10),
    -> (7844, 'iurner', 'salesman', 7698, '1981-09-08', 1500.00, null, 30),
    -> (7900, 'james', 'clerk', 7698, '1981-12-03', 950.00, null, 30),
    -> (7902, 'ford', 'analyst', 7566, '1981-12-03', 3000.00, null, 20),
    -> (7934, 'miller', 'clerk', 7782, '1982-01-23', 1300.00, null, 10);
Query OK, 13 rows affected (0.00 sec)
Records: 13  Duplicates: 0  Warnings: 0

mysql> desc emp;
+----------+-----------------------+------+-----+---------+-------+
| Field    | Type                  | Null | Key | Default | Extra |
+----------+-----------------------+------+-----+---------+-------+
| empno    | mediumint(8) unsigned | NO   |     | 0       |       |
| ename    | varchar(20)           | NO   |     |         |       |
| job      | varchar(9)            | NO   |     |         |       |
| mgr      | mediumint(8) unsigned | YES  |     | NULL    |       |
| hiredate | date                  | NO   |     | NULL    |       |
| sal      | decimal(7,2)          | NO   |     | NULL    |       |
| comm     | decimal(7,2)          | YES  |     | NULL    |       |
| deptno   | mediumint(8) unsigned | NO   |     | 0       |       |
+----------+-----------------------+------+-----+---------+-------+
8 rows in set (0.01 sec)

mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | smith  | clerk     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | allen  | salesman  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | ward   | salesman  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | jones  | manager   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | martin | salesman  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | blake  | manager   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | clark  | manager   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | scott  | analyst   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | king   | president | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | iurner | salesman  | 7698 | 1981-09-08 | 1500.00 |    NULL |     30 |
|  7900 | james  | clerk     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | ford   | analyst   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | miller | clerk     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
13 rows in set (0.00 sec)

快速入门案例

1.统计各部门的平均工资和最高工资

mysql> select avg(sal),max(sal),deptno from emp group by deptno;
+-------------+----------+--------+
| avg(sal)    | max(sal) | deptno |
+-------------+----------+--------+
| 2916.666667 |  5000.00 |     10 |
| 2443.750000 |  3000.00 |     20 |
| 1566.666667 |  2850.00 |     30 |
+-------------+----------+--------+
3 rows in set (0.00 sec)

2.显示每个部门的每种岗位的平均工资和最低工资

示意图：先根据deptno分，再根据job分

mysql> select avg(sal),min(sal),deptno,job from emp group by deptno,job;
+-------------+----------+--------+-----------+
| avg(sal)    | min(sal) | deptno | job       |
+-------------+----------+--------+-----------+
| 1300.000000 |  1300.00 |     10 | clerk     |
| 2450.000000 |  2450.00 |     10 | manager   |
| 5000.000000 |  5000.00 |     10 | president |
| 3000.000000 |  3000.00 |     20 | analyst   |
|  800.000000 |   800.00 |     20 | clerk     |
| 2975.000000 |  2975.00 |     20 | manager   |
|  950.000000 |   950.00 |     30 | clerk     |
| 2850.000000 |  2850.00 |     30 | manager   |
| 1400.000000 |  1250.00 |     30 | salesman  |
+-------------+----------+--------+-----------+
9 rows in set (0.00 sec)

3.显示平均工资低于2000的部门号和它的平均工资

//where不能和group by配合使用，应该用having代替where
mysql> select avg(sal),deptno from emp group by deptno where avg(sal)<2000;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where avg(sal)<2000' at line 1

// having 一般和 group by 配合使用，对结果进行刷选（代替where）
mysql> select avg(sal),deptno from emp group by deptno having avg(sal)<2000;
+-------------+--------+
| avg(sal)    | deptno |
+-------------+--------+
| 1566.666667 |     30 |
+-------------+--------+
1 row in set (0.00 sec)

//使用别名更好看一些
mysql> select avg(sal) as '平均工资',deptno from emp group by deptno having 平均工资<2000;
+-------------+--------+
| 平均工资    | deptno |
+-------------+--------+
| 1566.666667 |     30 |
+-------------+--------+
1 row in set (0.00 sec)