mysql单表查询某班人数_MySQL单表查询

MySQL之单表查询

创建表

#创建表

mysql>create table company.employee5(

id int primary key AUTO_INCREMENTnotnull,

name varchar(30) notnull,

sex enum('male','female') default 'male' notnull,

hire_date datenotnull,

post varchar(50) notnull,

job_description varchar(100),

salary double(15,2) notnull,

office int,

dep_id int

);#插入数据

mysql>insert into company.employee5(name,sex,hire_date,post,job_description,salary,office,dep_id) values

('jack','male','20180202','instructor','teach',5000,501,100),

('tom','male','20180203','instructor','teach',5500,501,100),

('robin','male','20180202','instructor','teach',8000,501,100),

('alice','female','20180202','instructor','teach',7200,501,100),

('tianyun','male','20180202','hr','hrcc',600,502,101),

('harry','male','20180202','hr',NULL,6000,502,101),

('emma','female','20180206','sale','salecc',20000,503,102),

('christine','female','20180205','sale','salecc',2200,503,102),

('zhuzhu','male','20180205','sale',NULL,2200,503,102),

('gougou','male','20180205','sale','',2200,503,102);#查看表结构

mysql>desc employee5;+-----------------+-----------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-----------------+-----------------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| name | varchar(30) | NO | | NULL | |

| sex | enum('male','female') | NO | | male | |

| hire_date | date | NO | | NULL | |

| post | varchar(50) | NO | | NULL | |

| job_description | varchar(100) | YES | | NULL | |

| salary | double(15,2) | NO | | NULL | |

| office | int(11) | YES | | NULL | |

| dep_id | int(11) | YES | | NULL | |

+-----------------+-----------------------+------+-----+---------+----------------+

查询语法

SELECT 字段1,字段2... FROM 表名

WHERE 条件

GROUP BY field

HAVING 筛选

ORDER BY field

LIMIT 限制条数；

查看表中所有数据

mysql> select * fromemployee5;+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+

| id | name | sex | hire_date | post | job_description | salary | office | dep_id |

+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+

| 1 | jack | male | 2018-02-02 | instructor | teach | 5000.00 | 501 | 100 |

| 2 | tom | male | 2018-02-03 | instructor | teach | 5500.00 | 501 | 100 |

| 3 | robin | male | 2018-02-02 | instructor | teach | 8000.00 | 501 | 100 |

| 4 | alice | female | 2018-02-02 | instructor | teach | 7200.00 | 501 | 100 |

| 5 | tianyun | male | 2018-02-02 | hr | hrcc | 600.00 | 502 | 101 |

| 6 | harry | male | 2018-02-02 | hr | NULL | 6000.00 | 502 | 101 |

| 7 | emma | female | 2018-02-06 | sale | salecc | 20000.00 | 503 | 102 |

| 8 | christine | female | 2018-02-05 | sale | salecc | 2200.00 | 503 | 102 |

| 9 | zhuzhu | male | 2018-02-05 | sale | NULL | 2200.00 | 503 | 102 |

| 10 | gougou | male | 2018-02-05 | sale | | 2200.00 | 503 | 102 |

+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+

10 rows in set (0.00 sec)

简单查询

简单查询

mysql> SELECT *FROM employee5;

mysql> SELECT name, salary, dep_id FROM employee5;

去重DISTINCT

mysql>SELECT post FROM employee5;

mysql>SELECT DISTINCT post FROM employee5;

注：不能部分使用DISTINCT，通常仅用于某一字段。

通过四则运算查询

mysql> SELECT name, salary, salary*14FROM employee5;

mysql> SELECT name, salary, salary*14AS Annual_salary FROM employee5;

mysql> SELECT name, salary, salary*14 Annual_salary FROM employee5;

定义显示格式

CONCAT() 函数用于连接字符串

mysql> SELECT CONCAT(name, 'annual salary:', salary*14) AS Annual_salary FROM employee5;

条件查询

a、语法

select* from表名 where 条件

b、比较运算符

大于 小于 大于等于 小于等于 不等于> < >= <= !=或<>c、逻辑运算符

并且 或者 非and or notd、模糊查询

like%表示任意多个任意字符

_ 表示一个任意字符

e、范围查询in表示在一个非连续的范围内

between...and... 表示在一个连续的范围内

f、空判断

判断空：isnull

判断非空：is notnull

g、优先级

小括号,not比较运算符， 逻辑运算符

and比or优先级高，如果同时出现并希望先选or，需要结合()来使用

单条件查询

mysql> SELECT name,post FROM employee5 WHERE post='hr';

多条件查询

mysql> SELECT name,salary FROM employee5 WHERE post='hr' AND salary>5000;

关键字 BETWEEN AND查询

mysql> SELECT name,salary FROM employee5 WHERE salary BETWEEN 5000 AND 15000;

mysql> SELECT name,salary FROM employee5 WHERE salary NOT BETWEEN 5000 AND 15000;

关键字 IS NULL 查询

mysql>SELECT name,job_description FROM employee5 WHERE job_description IS NULL;

mysql>SELECT name,job_description FROM employee5 WHERE job_description IS NOT NULL;

mysql> SELECT name,job_description FROM employee5 WHERE job_description='';

关键字IN集合查询

mysql> SELECT name, salary FROM employee5 WHERE salary=4000 OR salary=5000 OR salary=6000 OR salary=9000;

mysql> SELECT name, salary FROM employee5 WHERE salary IN (4000,5000,6000,9000) ;

mysql> SELECT name, salary FROM employee5 WHERE salary NOT IN (4000,5000,6000,9000) ;

关键字LIKE模糊查询

通配符’%’

mysql> SELECT * FROM employee5 WHERE name LIKE 'al%';

通配符’_’

mysql> SELECT * FROM employee5 WHERE name LIKE 'al___';

查询排序

按单列排序

mysql> SELECT *FROM employee5 ORDER BY salary;

mysql>SELECT name, salary FROM employee5 ORDER BY salary ASC;

mysql> SELECT name, salary FROM employee5 ORDER BY salary DESC;

按多列排序

mysql> SELECT *FROM employee5 ORDER BY hire_date DESC,salary ASC;#先按入职时间，再按薪水排序

mysql> SELECT *FROM employee5 ORDER BY hire_date DESC, salary DESC;#先按职位，再按薪水排序

mysql> SELECT * FROM employee5 ORDER BY post, salary DESC;

分页查询 limit

mysql> SELECT * FROM employee5 ORDER BY salary DESC LIMIT 5; //默认初始位置为0

mysql> SELECT * FROM employee5 ORDER BY salary DESC LIMIT 0,5;

mysql> SELECT * FROM employee5 ORDER BY salary DESC LIMIT 3,5; //从第4条开始，共显示5条

聚合函数查询

a、count(*) 表示计算总行数，括号中可以写*和列名

b、max(列) 表示求此列的最大值

c、min(列) 表示求此列的最小值

d、sun(列) 表示求此列的和

e、avg(列) 表示求此列的平均值

mysql> SELECT COUNT(*) FROM employee5;

mysql> SELECT COUNT(*) FROM employee5 WHERE dep_id=101;

mysql>SELECT MAX(salary) FROM employee5;

mysql>SELECT MIN(salary) FROM employee5;

mysql>SELECT AVG(salary) FROM employee5;

mysql>SELECT SUM(salary) FROM employee5;

mysql> SELECT SUM(salary) FROM employee5 WHERE dep_id=101;

分组查询

单独使用GROUP BY关键字分组

mysql>SELECT post FROM employee5 GROUP BY post;

注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数

GROUP BY关键字和group_concat()函数一起使用

#按照id分组，并查看组内成员

mysql>SELECT dep_id,GROUP_CONCAT(name) FROM employee5 GROUP BY dep_id;

mysql> SELECT dep_id,GROUP_CONCAT(name) as emp_members FROM employee5 GROUP BY dep_id;

GROUP BY和集合函数一起使用

#按照dep_id 分组， 并计算组内成员工资总和

mysql>SELECT dep_id,SUM(salary) FROM employee5 GROUP BY dep_id;#按照dep_id分组，并计算组内成员工资平均值

mysql> SELECT dep_id,AVG(salary) FROM employee5 GROUP BY dep_id;

正则表达式查询

mysql> SELECT * FROM employee5 WHERE name REGEXP '^j';

mysql> SELECT * FROM employee5 WHERE salary REGEXP '[5]+.*';

mysql> SELECT * FROM employee5 WHERE salary REGEXP '[5]{2}.*';