增删改
egon上课笔记
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
| sb1 |
| sb2 |
| sb3 |
| test |
+--------------------+
8 rows in set (0.00 sec)
mysql> create database sb4;
Query OK, 1 row affected (0.00 sec)
mysql> use sb4;
Database changed
mysql> desc mysql.user;
+------------------------+-----------------------------------+------+-----+-----
------------------+-------+
| Field | Type | Null | Key | Defa
ult | Extra |
+------------------------+-----------------------------------+------+-----+-----
------------------+-------+
| Host | char(60) | NO | PRI |
| |
| User | char(16) | NO | PRI |
| |
| Password | char(41) | NO | |
| |
| Select_priv | enum('N','Y') | NO | | N
| |
| Insert_priv | enum('N','Y') | NO | | N
| |
| Update_priv | enum('N','Y') | NO | | N
| |
| Delete_priv | enum('N','Y') | NO | | N
| |
| Create_priv | enum('N','Y') | NO | | N
| |
| Drop_priv | enum('N','Y') | NO | | N
| |
| Reload_priv | enum('N','Y') | NO | | N
| |
| Shutdown_priv | enum('N','Y') | NO | | N
| |
| Process_priv | enum('N','Y') | NO | | N
| |
| File_priv | enum('N','Y') | NO | | N
| |
| Grant_priv | enum('N','Y') | NO | | N
| |
| References_priv | enum('N','Y') | NO | | N
| |
| Index_priv | enum('N','Y') | NO | | N
| |
| Alter_priv | enum('N','Y') | NO | | N
| |
| Show_db_priv | enum('N','Y') | NO | | N
| |
| Super_priv | enum('N','Y') | NO | | N
| |
| Create_tmp_table_priv | enum('N','Y') | NO | | N
| |
| Lock_tables_priv | enum('N','Y') | NO | | N
| |
| Execute_priv | enum('N','Y') | NO | | N
| |
| Repl_slave_priv | enum('N','Y') | NO | | N
| |
| Repl_client_priv | enum('N','Y') | NO | | N
| |
| Create_view_priv | enum('N','Y') | NO | | N
| |
| Show_view_priv | enum('N','Y') | NO | | N
| |
| Create_routine_priv | enum('N','Y') | NO | | N
| |
| Alter_routine_priv | enum('N','Y') | NO | | N
| |
| Create_user_priv | enum('N','Y') | NO | | N
| |
| Event_priv | enum('N','Y') | NO | | N
| |
| Trigger_priv | enum('N','Y') | NO | | N
| |
| Create_tablespace_priv | enum('N','Y') | NO | | N
| |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | |
| |
| ssl_cipher | blob | NO | | NULL
| |
| x509_issuer | blob | NO | | NULL
| |
| x509_subject | blob | NO | | NULL
| |
| max_questions | int(11) unsigned | NO | | 0
| |
| max_updates | int(11) unsigned | NO | | 0
| |
| max_connections | int(11) unsigned | NO | | 0
| |
| max_user_connections | int(11) unsigned | NO | | 0
| |
| plugin | char(64) | YES | | mysq
l_native_password | |
| authentication_string | text | YES | | NULL
| |
| password_expired | enum('N','Y') | NO | | N
| |
+------------------------+-----------------------------------+------+-----+-----
------------------+-------+
43 rows in set (0.01 sec)
mysql> create table user(host char(60),user char(16),password char(41));
Query OK, 0 rows affected (0.02 sec)
mysql> desc user;
+----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| host | char(60) | YES | | NULL | |
| user | char(16) | YES | | NULL | |
| password | char(41) | YES | | NULL | |
+----------+----------+------+-----+---------+-------+
3 rows in set (0.04 sec)
mysql> select host,user,password from mysql.user;
+-----------+------+----------+
| host | user | password |
+-----------+------+----------+
| localhost | root | |
| 127.0.0.1 | root | |
| ::1 | root | |
| localhost | | |
+-----------+------+----------+
4 rows in set (0.00 sec)
mysql> insert into user select host,user,password from mysql.user;
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select*from user;
+-----------+------+----------+
| host | user | password |
+-----------+------+----------+
| localhost | root | |
| 127.0.0.1 | root | |
| ::1 | root | |
| localhost | | |
+-----------+------+----------+
4 rows in set (0.00 sec)
mysql> select*from user;
+-----------+------+----------+
| host | user | password |
+-----------+------+----------+
| localhost | root | |
| 127.0.0.1 | root | |
| ::1 | root | |
| localhost | | |
+-----------+------+----------+
4 rows in set (0.00 sec)
mysql> update user set user="ROOT" where host="localhost";
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select*from user;
+-----------+------+----------+
| host | user | password |
+-----------+------+----------+
| localhost | ROOT | |
| 127.0.0.1 | root | |
| ::1 | root | |
| localhost | ROOT | |
+-----------+------+----------+
4 rows in set (0.00 sec)
mysql> delete from user where user="ROOT";
Query OK, 4 rows affected (0.00 sec)
mysql> select*from user;
Empty set (0.00 sec)
mysql> create table t5(id int);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t5 values(1),(2),(3),(4);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select*from t5;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)
mysql> delete from t5 where id>2;
Query OK, 2 rows affected (0.00 sec)
mysql> delete from t5 where id>2;
Query OK, 0 rows affected (0.00 sec)
mysql> select*from t5;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
column name 代表字段的意思
# 1、跟权限有关的几张表
user->db->table_priv->columns_priv
# 2、只创建账号
#create user tom@"客户端的ip" identified by "123";
create user tom@"192.168.15.%" identified by "123";
create user tom@"%" identified by "123";
#客户端：192.168.15.13 服务端：192.168.15.90
create user tom@"192.168.15.13 " identified by "123";
mysql -utom -p"123" -h 192.168.15.90 -P 3306
# 3、创建账号并且授权（只有root账号才能为其他账号授权grant）
# *.* ===> mysql.user
grant all on *.* to "tom"@"192.168.15.90" identified by "123";#all代表除了grant以外的所有权限
# db1.* ===> mysql.db
grant all on db1.* to "jack"@"192.168.15.90" identified by "123";#all代表除了grant以外的所有权限
# db1.t1 ===> mysql.tables_priv
grant all on db1.t1 to "rose"@"192.168.15.90" identified by "123";#all代表除了grant以外的所有权限
# db1.t1(id) ===> mysql.columns_priv
grant select(id),update(name) on db1.t1 to "lili"@"192.168.15.90" identified by "123";
# 修改完权限一定要
flush privileges;
drop user "tom"@"192.168.15.90";
drop user "jack"@"192.168.15.90";
drop user "rose"@"192.168.15.90";
drop user "lili"@"192.168.15.90";
flush privileges;
如何创建账号,以及具体步骤
增删改 视频有32分钟到结束
=============================================================================================================
03 单表查询的语法与关键字的执行优先级
#1、完整语法(语法级别关键字的排列顺序如下)
select distinct 字段1,字段2,字段3,... from 库名.表名 #distinct去重的意思
where 约束条件
group by 分组依据
having 过滤条件
order by 排序的字段
limit 限制显示的条数
;
# 必须要有的关键字如下：
select * from t1;
# 关键字执行的优先级
from
where
group by
having
distinct
order by
limit
def from():
pass
def where():
pass
def group():
pass
def having():
pass
def distinct():
pass
def order():
pass
def limit():
pass
def select():
f=from()
res1=where(f)
res2=group(res1)
res3=having(res2)
res4=distinct(res3)
res5=order(res4)
limit(res5)
where关键字
2、where
select * from emp where id >= 10 and id <=15; # 等同于select * from emp where id between 10 and 15;
select * from emp where id = 6 or id = 9 or id = 12; # 等同于select * from emp where id in (6,9,12);
_代表任意单个字符
%代表任意无穷个字符
select * from emp where name like "__";
select * from emp where name like "jin%";
select * from emp where id not in (6,9,12);
select * from emp where id not between 10 and 15;
egon上机笔记
----------+------------+ | id | name | sex | age | hire_date | post | pos t_comment | salary | +----+------------+--------+-----+------------+----------------------------+---- ----------+------------+ | 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NUL L | 7300.33 | | 2 | alex | male | 78 | 2015-03-02 | teacher | NUL L | 1000000.31 | | 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NUL L | 8300.00 | | 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NUL L | 3500.00 | | 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NUL L | 2100.00 | | 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NUL L | 9000.00 | | 7 | jinxin | male | 18 | 1900-03-01 | teacher | NUL L | 30000.00 | | 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NUL L | 10000.00 | | 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NUL L | 3000.13 | | 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NUL L | 2000.35 | | 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NUL L | 1000.37 | | 12 | 星星 | female | 18 | 2016-05-13 | sale | NUL L | 3000.29 | | 13 | 格格 | female | 28 | 2017-01-27 | sale | NUL L | 4000.33 | | 14 | 张野 | male | 28 | 2016-03-11 | operation | NUL L | 10000.13 | | 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NUL L | 20000.00 | | 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NUL L | 19000.00 | | 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NUL L | 18000.00 | | 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NUL L | 17000.00 | +----+------------+--------+-----+------------+----------------------------+---- ----------+------------+ 18 rows in set (0.01 sec) mysql> select*from emp where id>10 and id<15; +----+------+--------+-----+------------+-----------+--------------+----------+ | id | name | sex | age | hire_date | post | post_comment | salary | +----+------+--------+-----+------------+-----------+--------------+----------+ | 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | | 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | | 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | | 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | +----+------+--------+-----+------------+-----------+--------------+----------+ 4 rows in set (0.00 sec) mysql> select*from emp where id=10 and id=15; Empty set (0.00 sec) mysql> select*from emp where id between 10 and 15; +----+--------+--------+-----+------------+-----------+--------------+---------- + | id | name | sex | age | hire_date | post | post_comment | salary | +----+--------+--------+-----+------------+-----------+--------------+---------- + | 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | | 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | | 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | | 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | | 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | | 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | +----+--------+--------+-----+------------+-----------+--------------+---------- + 6 rows in set (0.00 sec) mysql> select*from emp; +----+------------+--------+-----+------------+----------------------------+---- ----------+------------+ | id | name | sex | age | hire_date | post | pos t_comment | salary | +----+------------+--------+-----+------------+----------------------------+---- ----------+------------+ | 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NUL L | 7300.33 | | 2 | alex | male | 78 | 2015-03-02 | teacher | NUL L | 1000000.31 | | 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NUL L | 8300.00 | | 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NUL L | 3500.00 | | 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NUL L | 2100.00 | | 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NUL L | 9000.00 | | 7 | jinxin | male | 18 | 1900-03-01 | teacher | NUL L | 30000.00 | | 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NUL L | 10000.00 | | 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NUL L | 3000.13 | | 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NUL L | 2000.35 | | 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NUL L | 1000.37 | | 12 | 星星 | female | 18 | 2016-05-13 | sale | NUL L | 3000.29 | | 13 | 格格 | female | 28 | 2017-01-27 | sale | NUL L | 4000.33 | | 14 | 张野 | male | 28 | 2016-03-11 | operation | NUL L | 10000.13 | | 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NUL L | 20000.00 | | 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NUL L | 19000.00 | | 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NUL L | 18000.00 | | 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NUL L | 17000.00 | +----+------------+--------+-----+------------+----------------------------+---- ----------+------------+ 18 rows in set (0.00 sec) mysql> select*from emp where id>10; +----+--------+--------+-----+------------+-----------+--------------+---------- + | id | name | sex | age | hire_date | post | post_comment | salary | +----+--------+--------+-----+------------+-----------+--------------+---------- + | 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | | 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | | 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | | 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | | 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | | 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | | 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | | 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | +----+--------+--------+-----+------------+-----------+--------------+---------- + 8 rows in set (0.00 sec) mysql> select*from emp where id=6 or id=9 or id=12; +----+------------+--------+-----+------------+---------+--------------+-------- -+ | id | name | sex | age | hire_date | post | post_comment | salary | +----+------------+--------+-----+------------+---------+--------------+-------- -+ | 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | | 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | | 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | +----+------------+--------+-----+------------+---------+--------------+-------- -+ 3 rows in set (0.00 sec) mysql> select*from emp; +----+------------+--------+-----+------------+----------------------------+---- ----------+------------+ | id | name | sex | age | hire_date | post | pos t_comment | salary | +----+------------+--------+-----+------------+----------------------------+---- ----------+------------+ | 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NUL L | 7300.33 | | 2 | alex | male | 78 | 2015-03-02 | teacher | NUL L | 1000000.31 | | 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NUL L | 8300.00 | | 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NUL L | 3500.00 | | 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NUL L | 2100.00 | | 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NUL L | 9000.00 | | 7 | jinxin | male | 18 | 1900-03-01 | teacher | NUL L | 30000.00 | | 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NUL L | 10000.00 | | 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NUL L | 3000.13 | | 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NUL L | 2000.35 | | 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NUL L | 1000.37 | | 12 | 星星 | female | 18 | 2016-05-13 | sale | NUL L | 3000.29 | | 13 | 格格 | female | 28 | 2017-01-27 | sale | NUL L | 4000.33 | | 14 | 张野 | male | 28 | 2016-03-11 | operation | NUL L | 10000.13 | | 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NUL L | 20000.00 | | 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NUL L | 19000.00 | | 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NUL L | 18000.00 | | 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NUL L | 17000.00 | +----+------------+--------+-----+------------+----------------------------+---- ----------+------------+ 18 rows in set (0.00 sec) mysql> select*from emp where name like "__"; +----+------+--------+-----+------------+-----------+--------------+----------+ | id | name | sex | age | hire_date | post | post_comment | salary | +----+------+--------+-----+------------+-----------+--------------+----------+ | 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | | 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | | 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | | 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | | 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | | 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | | 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | +----+------+--------+-----+------------+-----------+--------------+----------+ 7 rows in set (0.00 sec) mysql> select*from emp where name like "jin%"; +----+------------+--------+-----+------------+---------+--------------+-------- --+ | id | name | sex | age | hire_date | post | post_comment | salary | +----+------------+--------+-----+------------+---------+--------------+-------- --+ | 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.0 0 | | 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.0 0 | +----+------------+--------+-----+------------+---------+--------------+-------- --+ 2 rows in set (0.00 sec) mysql> select*from emp where id not between 10 and 15; +----+------------+--------+-----+------------+----------------------------+---- ----------+------------+ | id | name | sex | age | hire_date | post | pos t_comment | salary | +----+------------+--------+-----+------------+----------------------------+---- ----------+------------+ | 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NUL L | 7300.33 | | 2 | alex | male | 78 | 2015-03-02 | teacher | NUL L | 1000000.31 | | 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NUL L | 8300.00 | | 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NUL L | 3500.00 | | 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NUL L | 2100.00 | | 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NUL L | 9000.00 | | 7 | jinxin | male | 18 | 1900-03-01 | teacher | NUL L | 30000.00 | | 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NUL L | 10000.00 | | 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NUL L | 3000.13 | | 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NUL L | 19000.00 | | 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NUL L | 18000.00 | | 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NUL L | 17000.00 | +----+------------+--------+-----+------------+----------------------------+---- ----------+------------+ 12 rows in set (0.00 sec)
3、group by分组
什么分组：按照所有记录相同的部分进行归类，一定区分度低的字段
为何要分组：当我们要以组为单位进行统计时就必须分组，分组的目的是为了以组为单位进行统计的，再去考虑单条记录毫无意义
set global sql_mode="strict_trans_tables,only_full_group_by";
注意:分组之后，只能查到分组的字段以及组内多条记录聚合的成果
select * from emp group by post;
聚合函数
max
min
avg
sum
count
select post,count(id) from emp group by post;
select post,max(salary) from emp group by post;
select post,avg(salary) from emp group by post;
select sex,count(sex) from emp group by sex;
统计出每个部门年龄30以上的员工的平均薪资
select post,avg(salary) from emp where age >= 30 group by post;
注意：分组是在where之后发生的
mysql> select * from emp where max(salary) > 3000;
ERROR 1111 (HY000): Invalid use of group function
group_concat
egon上机笔记
mysql> select*from emp group by post;
+----+------+--------+-----+------------+----------------------------+----------
----+------------+
| id | name | sex | age | hire_date | post | post_comm
ent | salary |
+----+------+--------+-----+------------+----------------------------+----------
----+------------+
| 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL
| 10000.13 |
| 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL
| 3000.13 |
| 2 | alex | male | 78 | 2015-03-02 | teacher | NULL
| 1000000.31 |
| 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL
| 7300.33 |
+----+------+--------+-----+------------+----------------------------+----------
----+------------+
4 rows in set (0.00 sec)
mysql> select*from emp;
+----+------------+--------+-----+------------+----------------------------+----
----------+------------+
| id | name | sex | age | hire_date | post | pos
t_comment | salary |
+----+------------+--------+-----+------------+----------------------------+----
----------+------------+
| 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NUL
L | 7300.33 |
| 2 | alex | male | 78 | 2015-03-02 | teacher | NUL
L | 1000000.31 |
| 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NUL
L | 8300.00 |
| 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NUL
L | 3500.00 |
| 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NUL
L | 2100.00 |
| 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NUL
L | 9000.00 |
| 7 | jinxin | male | 18 | 1900-03-01 | teacher | NUL
L | 30000.00 |
| 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NUL
L | 10000.00 |
| 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NUL
L | 3000.13 |
| 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NUL
L | 2000.35 |
| 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NUL
L | 1000.37 |
| 12 | 星星 | female | 18 | 2016-05-13 | sale | NUL
L | 3000.29 |
| 13 | 格格 | female | 28 | 2017-01-27 | sale | NUL
L | 4000.33 |
| 14 | 张野 | male | 28 | 2016-03-11 | operation | NUL
L | 10000.13 |
| 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NUL
L | 20000.00 |
| 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NUL
L | 19000.00 |
| 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NUL
L | 18000.00 |
| 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NUL
L | 17000.00 |
+----+------------+--------+-----+------------+----------------------------+----
----------+------------+
18 rows in set (0.00 sec)
mysql> select @@sql_mode;
+------------------------+
| @@sql_mode |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)
写sql语句的时候只能按照严格模式
mysql> set global sql_mode="strict_trans_tables,only_full_group_by";
Query OK, 0 rows affected (0.00 sec) #只能取分组的字段和聚合的结果
mysql> use sb5;
Database changed
mysql> select*from emp group by post;
ERROR 1055 (42000): 'sb5.emp.id' isn't in GROUP BY
mysql> select post from emp group by post;
+----------------------------+
| post |
+----------------------------+
| operation |
| sale |
| teacher |
| 老男孩驻沙河办事处外交大使 |
+----------------------------+
4 rows in set (0.00 sec)
# 聚合函数(切记分组以后才能用聚合函数)
max
min
avg
sum
count
mysql> select post from emp group by post;
+----------------------------+
| post |
+----------------------------+
| operation |
| sale |
| teacher |
| 老男孩驻沙河办事处外交大使 |
+----------------------------+
4 rows in set (0.00 sec)
mysql> select post,count(id) from emp group by post;
+----------------------------+-----------+
| post | count(id) |
+----------------------------+-----------+
| operation | 5 |
| sale | 5 |
| teacher | 7 |
| 老男孩驻沙河办事处外交大使 | 1 |
+----------------------------+-----------+
4 rows in set (0.00 sec)
mysql> select post,max(salary) from emp group by post;
+----------------------------+-------------+
| post | max(salary) |
+----------------------------+-------------+
| operation | 20000.00 |
| sale | 4000.33 |
| teacher | 1000000.31 |
| 老男孩驻沙河办事处外交大使 | 7300.33 |
+----------------------------+-------------+
4 rows in set (0.00 sec)
mysql> select post,avg(salary) from emp group by post;
+----------------------------+---------------+
| post | avg(salary) |
+----------------------------+---------------+
| operation | 16800.026000 |
| sale | 2600.294000 |
| teacher | 151842.901429 |
| 老男孩驻沙河办事处外交大使 | 7300.330000 |
+----------------------------+---------------+
4 rows in set (0.00 sec)
mysql> select post,sum(salary) from emp group by post;
+----------------------------+-------------+
| post | sum(salary) |
+----------------------------+-------------+
| operation | 84000.13 |
| sale | 13001.47 |
| teacher | 1062900.31 |
| 老男孩驻沙河办事处外交大使 | 7300.33 |
+----------------------------+-------------+
4 rows in set (0.00 sec)
mysql> select post,avg(salary) from emp group by post;
+----------------------------+---------------+
| post | avg(salary) |
+----------------------------+---------------+
| operation | 16800.026000 |
| sale | 2600.294000 |
| teacher | 151842.901429 |
| 老男孩驻沙河办事处外交大使 | 7300.330000 |
+----------------------------+---------------+
4 rows in set (0.00 sec)
mysql> select post,sum(salary) from emp group by post;
+----------------------------+-------------+
| post | sum(salary) |
+----------------------------+-------------+
| operation | 84000.13 |
| sale | 13001.47 |
| teacher | 1062900.31 |
| 老男孩驻沙河办事处外交大使 | 7300.33 |
+----------------------------+-------------+
4 rows in set (0.00 sec)
mysql> select post,count(id) from emp group by post;
+----------------------------+-----------+
| post | count(id) |
+----------------------------+-----------+
| operation | 5 |
| sale | 5 |
| teacher | 7 |
| 老男孩驻沙河办事处外交大使 | 1 |
+----------------------------+-----------+
4 rows in set (0.00 sec)
mysql> select post,count(sex) from emp group by post;
+----------------------------+------------+
| post | count(sex) |
+----------------------------+------------+
| operation | 5 |
| sale | 5 |
| teacher | 7 |
| 老男孩驻沙河办事处外交大使 | 1 |
+----------------------------+------------+
4 rows in set (0.00 sec)
为什么用id呢?
innodb表达id字段，primary本身就是一个索引用他排序速度很快
统计出每个部门年龄30以上的员工的平均薪资
mysql> select*from emp where max(salary)>3000;
ERROR 1111 (HY000): Invalid use of group function
mysql> select max(salary) from emp;
+-------------+
| max(salary) |
+-------------+
| 1000000.31 |
+-------------+
1 row in set (0.00 sec)
mysql> select*from emp where max(salary)>3000;
ERROR 1111 (HY000): Invalid use of group function
mysql> select max(salary) from emp;
+-------------+
| max(salary) |
+-------------+
| 1000000.31 |
+-------------+
1 row in set (0.00 sec)
concat字符拼接
mysql> select post from emp group by post;
+----------------------------+
| post |
+----------------------------+
| operation |
| sale |
| teacher |
| 老男孩驻沙河办事处外交大使 |
+----------------------------+
4 rows in set (0.00 sec)
mysql> select post,group_concat(name) from emp group by post;
+----------------------------+--------------------------------------------------
-----+
| post | group_concat(name)
|
+----------------------------+--------------------------------------------------
-----+
| operation | 程咬铁,程咬铜,程咬银,程咬金,张野
|
| sale | 格格,星星,丁丁,丫丫,歪歪
|
| teacher | 成龙,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,
alex |
| 老男孩驻沙河办事处外交大使 | egon
|
+----------------------------+--------------------------------------------------
-----+
4 rows in set (0.00 sec)
mysql> select post,group_concat_ws(":",name,age,sex) from emp group by post;
ERROR 1305 (42000): FUNCTION sb5.group_concat_ws does not exist
mysql> select post,group_concat(name,age) from emp group by post;
+----------------------------+--------------------------------------------------
-------------------+
| post | group_concat(name,age)
|
+----------------------------+--------------------------------------------------
-------------------+
| operation | 程咬铁18,程咬铜18,程咬银18,程咬金18,张野28
|
| sale | 格格28,星星18,丁丁18,丫丫38,歪歪48
|
| teacher | 成龙48,jinxin18,jingliyang18,liwenzhou28,yuanhao7
3,wupeiqi81,alex78 |
| 老男孩驻沙河办事处外交大使 | egon18
|
+----------------------------+--------------------------------------------------
-------------------+
4 rows in set (0.00 sec)
mysql> select post,group_concat(name,';',age) from emp group by post;
+----------------------------+--------------------------------------------------
--------------------------+
| post | group_concat(name,';',age)
|
+----------------------------+--------------------------------------------------
--------------------------+
| operation | 程咬铁;18,程咬铜;18,程咬银;18,程咬金;18,张野;28
|
| sale | 格格;28,星星;18,丁丁;18,丫丫;38,歪歪;48
|
| teacher | 成龙;48,jinxin;18,jingliyang;18,liwenzhou;28,yuan
hao;73,wupeiqi;81,alex;78 |
| 老男孩驻沙河办事处外交大使 | egon;18
|
+----------------------------+--------------------------------------------------
--------------------------+
4 rows in set (0.00 sec)
#4、having 过滤条件
# where是在分组之前的过滤，即在分组之前做了一次整体性的筛选
# having是在分组之后的过滤，即在分组之后专门针对聚合的结果进行进一步的筛选
select post,avg(salary) from emp group by post having avg(salary) > 10000;
select post,avg(salary) from emp group by post ;
mysql> select post,avg(salary) from emp group by post having avg(salary)>10000; +-----------+---------------+ | post | avg(salary) | +-----------+---------------+ | operation | 16800.026000 | | teacher | 151842.901429 | +-----------+---------------+ 2 rows in set (0.00 sec) mysql> select post,avg(salary) from emp group by post ; +----------------------------+---------------+ | post | avg(salary) | +----------------------------+---------------+ | operation | 16800.026000 | | sale | 2600.294000 | | teacher | 151842.901429 | | 老男孩驻沙河办事处外交大使 | 7300.330000 | +----------------------------+---------------+ 4 rows in set (0.00 sec)
小练习:
相关小练习:
1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
3. 查询各岗位平均薪资大于10000的岗位名、平均工资
4. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
6、limit 限制显示的条件
select * from emp limit 3;
#薪资最高那个人的详细信息
select * from emp order by salary desc limit 1;
分页显示
select * from emp limit 0,5; # 从0开始往后取5条
select * from emp limit 5,5; #从5开始往后取5条
egon上机笔记
mysql> select*from emp; +----+------------+--------+-----+------------+----------------------------+--------------+------------+ | id | name | sex | age | hire_date | post | post_comment | salary | +----+------------+--------+-----+------------+----------------------------+--------------+------------+ | 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | | 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | | 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | | 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | | 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | | 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | | 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | | 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | | 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | | 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | | 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | | 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | | 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | | 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | | 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | | 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | | 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | | 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | +----+------------+--------+-----+------------+----------------------------+--------------+------------+ 18 rows in set (0.00 sec) mysql> select*from emp limit 0,5; +----+-----------+------+-----+------------+----------------------------+--------------+------------+ | id | name | sex | age | hire_date | post | post_comment | salary | +----+-----------+------+-----+------------+----------------------------+--------------+------------+ | 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | | 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | | 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | | 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | | 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | +----+-----------+------+-----+------------+----------------------------+--------------+------------+ 5 rows in set (0.00 sec) mysql> select*from emp limit 5,10; +----+------------+--------+-----+------------+-----------+--------------+----------+ | id | name | sex | age | hire_date | post | post_comment | salary | +----+------------+--------+-----+------------+-----------+--------------+----------+ | 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | | 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | | 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | | 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | | 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | | 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | | 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | | 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | | 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | | 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | +----+------------+--------+-----+------------+-----------+--------------+----------+ 10 rows in set (0.00 sec) mysql> select*from emp limit 10,5; +----+--------+--------+-----+------------+-----------+--------------+----------+ | id | name | sex | age | hire_date | post | post_comment | salary | +----+--------+--------+-----+------------+-----------+--------------+----------+ | 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | | 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | | 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | | 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | | 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | +----+--------+--------+-----+------------+-----------+--------------+----------+ 5 rows in set (0.00 sec) mysql> select*from emp limit 15,5; +----+--------+--------+-----+------------+-----------+--------------+----------+ | id | name | sex | age | hire_date | post | post_comment | salary | +----+--------+--------+-----+------------+-----------+--------------+----------+ | 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | | 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | | 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | +----+--------+--------+-----+------------+-----------+--------------+----------+ 3 rows in set (0.00 sec)
#正则表达式
select * from emp where name regexp "^jin.*(g|n)$";
#1、笛卡儿积
select * from emp,dep; #左表和右表全部建立对应关系
select * from emp,dep where emp.dep_id = dep.id;
select * from emp,dep where emp.dep_id = dep.id and dep.name = "技术";
#2、内连接：只取两张表有对应关系的记录 #where专门干筛选的活
select * from emp inner join dep on emp.dep_id = dep.id;
select * from emp inner join dep on emp.dep_id = dep.id
where dep.name = "技术";
#3、左连接: 在内连接的基础上保留左表没有对应关系的记录
select * from emp left join dep on emp.dep_id = dep.id;
#4、右连接: 在内连接的基础上保留右表没有对应关系的记录
select * from emp right join dep on emp.dep_id = dep.id;
#5、全连接：在内连接的基础上保留左、右面表没有对应关系的的记录
select * from emp left join dep on emp.dep_id = dep.id
union
select * from emp right join dep on emp.dep_id = dep.id;
egon上机笔记
mysql> create database sb5 charset gbk;
Query OK, 1 row affected (0.00 sec)
mysql> use sb5;
Database changed
mysql> create table department(
-> id int,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> create table employee(
-> id int primary key auto_increment,
-> name varchar(20),
-> sex enum('male','female') not null default 'male',
-> age int,
-> dep_id int
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> desc department;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> desc employee;
+--------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| sex | enum('male','female') | NO | | male | |
| age | int(11) | YES | | NULL | |
| dep_id | int(11) | YES | | NULL | |
+--------+-----------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
mysql> insert into department values
-> (200,'技术'),
-> (201,'人力资源'),
-> (202,'销售'),
-> (203,'运营');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into employee(name,sex,age,dep_id) values
-> ('egon','male',18,200),
-> ('alex','female',48,201),
-> ('wupeiqi','male',38,201),
-> ('yuanhao','female',28,202),
-> ('liwenzhou','male',18,200),
-> ('jingliyang','female',18,204)
-> ;
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> alter table department rename dep;
Query OK, 0 rows affected (0.01 sec)
mysql> select*from employee;
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------------+--------+------+--------+
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | liwenzhou | male | 18 | 200 |
| 6 | jingliyang | female | 18 | 204 |
+----+------------+--------+------+--------+
6 rows in set (0.00 sec)
mysql> alter table employee rename empp;
Query OK, 0 rows affected (0.01 sec)
mysql> select*from dep;
+------+----------+
| id | name |
+------+----------+
| 200 | 技术 |
| 201 | 人力资源 |
| 202 | 销售 |
| 203 | 运营 |
+------+----------+
4 rows in set (0.00 sec)
mysql> select*from empp,dep;
+----+------------+--------+------+--------+------+----------+
| id | name | sex | age | dep_id | id | name |
+----+------------+--------+------+--------+------+----------+
| 1 | egon | male | 18 | 200 | 200 | 技术 |
| 1 | egon | male | 18 | 200 | 201 | 人力资源 |
| 1 | egon | male | 18 | 200 | 202 | 销售 |
| 1 | egon | male | 18 | 200 | 203 | 运营 |
| 2 | alex | female | 48 | 201 | 200 | 技术 |
| 2 | alex | female | 48 | 201 | 201 | 人力资源 |
| 2 | alex | female | 48 | 201 | 202 | 销售 |
| 2 | alex | female | 48 | 201 | 203 | 运营 |
| 3 | wupeiqi | male | 38 | 201 | 200 | 技术 |
| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |
| 3 | wupeiqi | male | 38 | 201 | 202 | 销售 |
| 3 | wupeiqi | male | 38 | 201 | 203 | 运营 |
| 4 | yuanhao | female | 28 | 202 | 200 | 技术 |
| 4 | yuanhao | female | 28 | 202 | 201 | 人力资源 |
| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |
| 4 | yuanhao | female | 28 | 202 | 203 | 运营 |
| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |
| 5 | liwenzhou | male | 18 | 200 | 201 | 人力资源 |
| 5 | liwenzhou | male | 18 | 200 | 202 | 销售 |
| 5 | liwenzhou | male | 18 | 200 | 203 | 运营 |
| 6 | jingliyang | female | 18 | 204 | 200 | 技术 |
| 6 | jingliyang | female | 18 | 204 | 201 | 人力资源 |
| 6 | jingliyang | female | 18 | 204 | 202 | 销售 |
| 6 | jingliyang | female | 18 | 204 | 203 | 运营 |
+----+------------+--------+------+--------+------+----------+
24 rows in set (0.01 sec)
连成一张大的表然后单表去查就可以了
mysql> select*from empp,dep where empp.dep_id = dep.id;
+----+-----------+--------+------+--------+------+----------+
| id | name | sex | age | dep_id | id | name |
+----+-----------+--------+------+--------+------+----------+
| 1 | egon | male | 18 | 200 | 200 | 技术 |
| 2 | alex | female | 48 | 201 | 201 | 人力资源 |
| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |
| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |
| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |
+----+-----------+--------+------+--------+------+----------+
5 rows in set (0.00 sec)
mysql> select*from empp,dep where empp.dep_id=dep.id and dep.name="技术";
+----+-----------+------+------+--------+------+------+
| id | name | sex | age | dep_id | id | name |
+----+-----------+------+------+--------+------+------+
| 1 | egon | male | 18 | 200 | 200 | 技术 |
| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |
+----+-----------+------+------+--------+------+------+
2 rows in set (0.00 sec)
mysql> select * from empp inner join dep on empp.dep_id = dep.id
-> where dep.name = "技术";
+----+-----------+------+------+--------+------+------+
| id | name | sex | age | dep_id | id | name |
+----+-----------+------+------+--------+------+------+
| 1 | egon | male | 18 | 200 | 200 | 技术 |
| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |
+----+-----------+------+------+--------+------+------+
2 rows in set (0.00 sec)
mysql> select * from empp left join dep on empp.dep_id = dep.id;
+----+------------+--------+------+--------+------+----------+
| id | name | sex | age | dep_id | id | name |
+----+------------+--------+------+--------+------+----------+
| 1 | egon | male | 18 | 200 | 200 | 技术 |
| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |
| 2 | alex | female | 48 | 201 | 201 | 人力资源 |
| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |
| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |
| 6 | jingliyang | female | 18 | 204 | NULL | NULL |
+----+------------+--------+------+--------+------+----------+
6 rows in set (0.00 sec)
mysql> select * from empp right join dep on empp.dep_id = dep.id;
+------+-----------+--------+------+--------+------+----------+
| id | name | sex | age | dep_id | id | name |
+------+-----------+--------+------+--------+------+----------+
| 1 | egon | male | 18 | 200 | 200 | 技术 |
| 2 | alex | female | 48 | 201 | 201 | 人力资源 |
| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |
| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |
| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |
| NULL | NULL | NULL | NULL | NULL | 203 | 运营 |
+------+-----------+--------+------+--------+------+----------+
6 rows in set (0.00 sec)
mysql> select * from empp left join dep on empp.dep_id = dep.id
-> union
-> select * from empp right join dep on empp.dep_id = dep.id;
+------+------------+--------+------+--------+------+----------+
| id | name | sex | age | dep_id | id | name |
+------+------------+--------+------+--------+------+----------+
| 1 | egon | male | 18 | 200 | 200 | 技术 |
| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |
| 2 | alex | female | 48 | 201 | 201 | 人力资源 |
| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |
| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |
| 6 | jingliyang | female | 18 | 204 | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | 203 | 运营 |
+------+------------+--------+------+--------+------+----------+
7 rows in set (0.01 sec)