Day41 数据库基础3
一，记录操作
1.增
mysql> create table t1 (id int,name varchar(20)); # Query OK, 0 rows affected (0.19 sec) insert into t1 values (1,'alex'),(2,'yuan'),(3,'nezha'); # Query OK, 3 rows affected (0.03 sec) # Records: 3 Duplicates: 0 Warnings: 0 # mysql> select * from t1; # +------+-------+ # | id | name | # +------+-------+ # | 1 | alex | # | 2 | yuan | # | 3 | nezha | # +------+-------+ mysql> create table t2 (id int primary key auto_increment,sname varchar(20)); # Query OK, 0 rows affected (0.17 sec) insert into t2 (sname) select name from t1; select * from t2; # +----+-------+ # | id | sname | # +----+-------+ # | 1 | alex | # | 2 | yuan | # | 3 | nezha | # +----+-------+ mysql> insert into t2 (sname) select name from t1 where id < 3; # Query OK, 2 rows affected (0.03 sec) # Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from t2; # +----+-------+ # | id | sname | # +----+-------+ # | 1 | alex | # | 2 | yuan | # | 3 | nezha | # | 4 | alex | # | 5 | yuan |
2.改
mysql> update t2 set sname = 'boss_jin' where id = 4; # Query OK, 1 row affected (0.03 sec) # Rows matched: 1 Changed: 1 Warnings: 0 # mysql> select * from t2; # +----+----------+ # | id | sname | # +----+----------+ # | 1 | alex | # | 2 | yuan | # | 3 | nezha | # | 4 | boss_jin | # | 5 | yuan | # +----+----------+ # 5 rows in set (0.00 sec) # mysql> select user,host,password from mysql.user; # +-------+--------------+-------------------------------------------+ # | user | host | password | # +-------+--------------+-------------------------------------------+ # | root | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | # | root | 127.0.0.1 | | # | root | ::1 | | # | | localhost | | # | eva | % | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | # | egon2 | 192.168.16.% | | # | egon1 | 192.168.16.* | | # +-------+--------------+-------------------------------------------+ # 7 rows in set (0.00 sec) # mysql> update mysql.user set password = password('123') where user = 'egon1'; # Query OK, 1 row affected (0.00 sec) # Rows matched: 1 Changed: 1 Warnings: 0 # mysql> select user,host,password from mysql.user; # +-------+--------------+-------------------------------------------+ # | user | host | password | # +-------+--------------+-------------------------------------------+ # | root | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | # | root | 127.0.0.1 | | # | root | ::1 | | # | | localhost | | # | eva | % | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | # | egon2 | 192.168.16.% | | # | egon1 | 192.168.16.* | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | # +-------+--------------+-------------------------------------------+ # 7 rows in set (0.00 sec) # mysql> update mysql.user set password = '123' where user = 'egon2'; # Query OK, 1 row affected (0.00 sec) # Rows matched: 1 Changed: 1 Warnings: 0 # mysql> select user,host,password from mysql.user; # +-------+--------------+-------------------------------------------+ # | user | host | password | # +-------+--------------+-------------------------------------------+ # | root | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | # | root | 127.0.0.1 | | # | root | ::1 | | # | | localhost | | # | eva | % | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | # | egon2 | 192.168.16.% | 123 | # | egon1 | 192.168.16.* | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | # +-------+--------------+-------------------------------------------+ # 7 rows in set (0.00 sec)
3.删
mysql> delete from t2 where id=5; # Query OK, 1 row affected (0.03 sec) # mysql> select * from t2; # +----+----------+ # | id | sname | # +----+----------+ # | 1 | alex | # | 2 | yuan | # | 3 | nezha | # | 4 | boss_jin | # +----+----------+ # 4 rows in set (0.00 sec) # mysql> delete from mysql.user where user = 'egon2'; # Query OK, 1 row affected (0.00 sec) # mysql> select user,host,password from mysql.user; # +-------+--------------+-------------------------------------------+ # | user | host | password | # +-------+--------------+-------------------------------------------+ # | root | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | # | root | 127.0.0.1 | | # | root | ::1 | | # | | localhost | | # | eva | % | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | # | egon1 | 192.168.16.* | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | # +-------+--------------+-------------------------------------------+ # 6 rows in set (0.00 sec)
4.查
单表查询
mysql> select * from employee; # +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ # | id | emp_name | sex | age | hire_date | post | post_comment | salary | office | depart_id | # +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ # | 1 | egon | male | 18 | 2017-03-01 | 外交大使 | NULL | 7300.33 | 401 | 1 | # | 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | # | 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | # | 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | # | 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | # | 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | # | 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | # | 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | # | 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | # | 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 | # | 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 | # | 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 | # | 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 | # | 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | # | 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 | # | 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 | # | 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 | # | 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 | # +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ # 查询其中的几个字段 mysql> select emp_name,salary from employee; # +------------+------------+ # | emp_name | salary | # +------------+------------+ # | egon | 7300.33 | # | alex | 1000000.31 | # | wupeiqi | 8300.00 | # | yuanhao | 3500.00 | # | liwenzhou | 2100.00 | # | jingliyang | 9000.00 | # | jinxin | 30000.00 | # | 成龙 | 10000.00 | # | 歪歪 | 3000.13 | # | 丫丫 | 2000.35 | # | 丁丁 | 1000.37 | # | 星星 | 3000.29 | # | 格格 | 4000.33 | # | 张野 | 10000.13 | # | 程咬金 | 20000.00 | # | 程咬银 | 19000.00 | # | 程咬铜 | 18000.00 | # | 程咬铁 | 17000.00 | # 去重 distinct mysql> select distinct post from employee; # +-----------------------------------------+ # | post | # +-----------------------------------------+ # | 外交大使 | # | teacher | # | sale | # | operation | # +-----------------------------------------+ # 4 rows in set (0.00 sec) # mysql> select distinct sex from employee; # +--------+ # | sex | # +--------+ # | male | # | female | # +--------+ # 2 rows in set (0.00 sec) # 四则运算和重命名 mysql> select emp_name,salary*12 from employee; # +------------+-------------+ # | emp_name | salary*12 | # +------------+-------------+ # | egon | 87603.96 | # | alex | 12000003.72 | # | wupeiqi | 99600.00 | # | yuanhao | 42000.00 | # | liwenzhou | 25200.00 | # | jingliyang | 108000.00 | # | jinxin | 360000.00 | # | 成龙 | 120000.00 | # | 歪歪 | 36001.56 | # | 丫丫 | 24004.20 | # | 丁丁 | 12004.44 | # | 星星 | 36003.48 | # | 格格 | 48003.96 | # | 张野 | 120001.56 | # | 程咬金 | 240000.00 | # | 程咬银 | 228000.00 | # | 程咬铜 | 216000.00 | # | 程咬铁 | 204000.00 | # +------------+-------------+ # 18 rows in set (0.01 sec) # mysql> select emp_name,salary*12 as ysalary from employee; # +------------+-------------+ # | emp_name | ysalary | # +------------+-------------+ # | egon | 87603.96 | # | alex | 12000003.72 | # | wupeiqi | 99600.00 | # | yuanhao | 42000.00 | # | liwenzhou | 25200.00 | # | jingliyang | 108000.00 | # | jinxin | 360000.00 | # | 成龙 | 120000.00 | # | 歪歪 | 36001.56 | # | 丫丫 | 24004.20 | # | 丁丁 | 12004.44 | # | 星星 | 36003.48 | # | 格格 | 48003.96 | # | 张野 | 120001.56 | # | 程咬金 | 240000.00 | # | 程咬银 | 228000.00 | # | 程咬铜 | 216000.00 | # | 程咬铁 | 204000.00 | # +------------+-------------+ # 18 rows in set (0.00 sec) # mysql> select emp_name,salary*12 ysalary from employee; # +------------+-------------+ # | emp_name | ysalary | # +------------+-------------+ # | egon | 87603.96 | # | alex | 12000003.72 | # | wupeiqi | 99600.00 | # | yuanhao | 42000.00 | # | liwenzhou | 25200.00 | # | jingliyang | 108000.00 | # | jinxin | 360000.00 | # | 成龙 | 120000.00 | # | 歪歪 | 36001.56 | # | 丫丫 | 24004.20 | # | 丁丁 | 12004.44 | # | 星星 | 36003.48 | # | 格格 | 48003.96 | # | 张野 | 120001.56 | # | 程咬金 | 240000.00 | # | 程咬银 | 228000.00 | # | 程咬铜 | 216000.00 | # | 程咬铁 | 204000.00 | # +------------+-------------+ # concat和concat_ws select concat('员工 :',emp_name,'年薪 :',salary *12) from employee; # +---------------------------------------------------+ # | concat('员工 :',emp_name,'年薪 :',salary *12) | # +---------------------------------------------------+ # | 员工 :egon年薪 :87603.96 | # | 员工 :alex年薪 :12000003.72 | # | 员工 :wupeiqi年薪 :99600.00 | # | 员工 :yuanhao年薪 :42000.00 | # | 员工 :liwenzhou年薪 :25200.00 | # | 员工 :jingliyang年薪 :108000.00 | # | 员工 :jinxin年薪 :360000.00 | # | 员工 :成龙年薪 :120000.00 | # | 员工 :歪歪年薪 :36001.56 | # | 员工 :丫丫年薪 :24004.20 | # | 员工 :丁丁年薪 :12004.44 | # | 员工 :星星年薪 :36003.48 | # | 员工 :格格年薪 :48003.96 | # | 员工 :张野年薪 :120001.56 | # | 员工 :程咬金年薪 :240000.00 | # | 员工 :程咬银年薪 :228000.00 | # | 员工 :程咬铜年薪 :216000.00 | # | 员工 :程咬铁年薪 :204000.00 | # +---------------------------------------------------+ # 18 rows in set (0.00 sec) mysql> select concat_ws(':',emp_name,salary *12,sex,age) from employee; # +--------------------------------------------+ # | concat_ws(':',emp_name,salary *12,sex,age) | # +--------------------------------------------+ # | egon:87603.96:male:18 | # | alex:12000003.72:male:78 | # | wupeiqi:99600.00:male:81 | # | yuanhao:42000.00:male:73 | # | liwenzhou:25200.00:male:28 | # | jingliyang:108000.00:female:18 | # | jinxin:360000.00:male:18 | # | 成龙:120000.00:male:48 | # | 歪歪:36001.56:female:48 | # | 丫丫:24004.20:female:38 | # | 丁丁:12004.44:female:18 | # | 星星:36003.48:female:18 | # | 格格:48003.96:female:28 | # | 张野:120001.56:male:28 | # | 程咬金:240000.00:male:18 | # | 程咬银:228000.00:female:18 | # | 程咬铜:216000.00:male:18 | # | 程咬铁:204000.00:female:18 | # +--------------------------------------------+ # 18 rows in set (0.00 sec) # case语句 mysql> select ( -> case -> when emp_name = 'jingliyang' -> then emp_name -> when emp_name = 'alex' -> then concat(emp_name,'_bigsb') -> else -> concat(emp_name,'sb') -> end) as new_name -> from employee; # +-------------+ # | new_name | # +-------------+ # | egonsb | # | alex_bigsb | # | wupeiqisb | # | yuanhaosb | # | liwenzhousb | # | jingliyang | # | jinxinsb | # | 成龙sb | # | 歪歪sb | # | 丫丫sb | # | 丁丁sb | # | 星星sb | # | 格格sb | # | 张野sb | # | 程咬金sb | # | 程咬银sb | # | 程咬铜sb | # | 程咬铁sb | # +-------------+ # 18 rows in set (0.00 sec)
多表查询
# 直接查询 # 表与表之间产生了一个暴力全集 —— 笛卡儿积 # [a,b,c],[1,2,3] # a1,b1,c1,a2,b2,c2,a3,b3,c3 # 内连接 inner join # select * from 表1 inner join 表2 on 表1.外键字段 = 表2.字段 # 只有两个表中互相匹配的项才会被显示在新的表中 # employee id name sex age dep_id # department id name # # mysql> select * from employee t1 inner join department t2 on t1.dep_id = t2.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 t1.id,t1.name,t1.sex,t2.name from employee t1 inner join department t2 on t1.dep_id = t2.id; # +----+-----------+--------+--------------+ # | id | name | sex | name | # +----+-----------+--------+--------------+ # | 1 | egon | male | 技术 | # | 2 | alex | female | 人力资源 | # | 3 | wupeiqi | male | 人力资源 | # | 4 | yuanhao | female | 销售 | # | 5 | liwenzhou | male | 技术 | # +----+-----------+--------+--------------+ # 5 rows in set (0.00 sec) # 左连接和右连接 # mysql> select * from employee t1 left join department t2 on t1.dep_id = t2.id; # mysql> select * from employee t1 left join department t2 on t1.dep_id = t2.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 employee t1 right join department t2 on t1.dep_id = t2.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) # 全外连接 # select * from employee t1 left join department t2 on t1.dep_id = t2.id # union # select * from employee t1 right join department t2 on t1.dep_id = t2.id;
子查询：
# 查询平均年龄在25岁以上的部门名 # 首先查询平均年龄在25岁以上的部门id # select dep_id from employee group by dep_id having avg(age)>25; # 部门id是201,202的部门信息 # select * from department where id in (201,202); # 拼sql # select * from department where id in ( # select dep_id from employee group by dep_id having avg(age)>25 # ); # 查看技术部员工姓名 # select name from employee where dep_id = ( # select id from department where name = '技术' # ); # 查看不足1人的部门名(子查询得到的是有人的部门id) # select * from department where id not in (select dep_id from employee group by dep_id); # 每个部门入职最晚的时间 # select post,max(hire_date) from employee2 group by post; # 将时间和人对上 # select employee2.name,employee2.hire_date,t2.post # from employee2 # inner join ( # select post,max(hire_date) max_hire from employee2 group by post # ) t2 # on employee2.post = t2.post and employee2.hire_date = t2.max_hire; # select ( # select t2.name from emp as t2 where t2.post=t1.post order by hire_date desc limit 1 # ) # from emp as t1 group by post; # mysql> select t3.name,t3.post,t3.hire_date from emp as t3 where id in ( # select ( # select id from emp as t2 where t2.post=t1.post order by hire_date desc limit 1) # from emp as t1 group by post);
浙公网安备 33010602011771号