SELECT * FROM t_employee;

#查询孙红雷的姓名和薪资
SELECT ename,salary FROM t_employee WHERE ename="孙红雷"

#查询孙红雷的姓名和领导编号
SELECT ename,`mid` FROM t_employee WHERE ename="孙红雷"

#给查询结果取别名
SELECT ename AS "姓名", salary AS "薪资" FROM t_employee;

二、运算符

1、算数运算符

#查询员工的姓名和薪资
SELECT ename,salary FROM t_employee;

#查询员工的姓名和原来的薪资和涨薪1000元后的薪资
SELECT ename,salary,salary + 1000 FROM t_employee;

#查询9/4的结果
mysql> SELECT 9/4;
+-------+
| 9 / 4 |
+-------+
| 2.25  |
+-------+
#查询9/4的结果
mysql> SELECT 9 DIV 4;
+---------+
| 9 DIV 4 |
+---------+
|       2 |
+---------+

#查询员工的姓名，和每天的薪资，假设每个月的工作日是22天
SELECT ename AS "姓名", salary / 22 AS "日薪" FROM t_employee

#查询9%4的结果
mysql> select 9%4, 9 MOD 4;
+-----+---------+
| 9%4 | 9 MOD 4 |
+-----+---------+
|   1 |       1 |
+-----+---------+
1 row in set

2、比较运算符

#查询薪资大于20000的员工
SELECT * FROM t_employee WHERE salary > 20000;

#查询薪资等于9000
SELECT * FROM t_employee WHERE salary = 9000;

#查询部门编号不是1的员工
SELECT * FROM t_employee WHERE did != 1;
SELECT * FROM t_employee WHERE did <> 1;

#查询奖金比例是NULL的员工
SELECT * FROM t_employee WHERE commission_pct = NULL;#错误的
SELECT * FROM t_employee WHERE commission_pct <=> NULL;
SELECT * FROM t_employee WHERE commission_pct IS NULL;

3、逻辑运算符

#查询薪资高于10000 并且低于15000的女员工
SELECT * FROM t_employee
WHERE salary > 10000 && salary <15000 AND gender = "女"

#查询薪资高于20000  或者  籍贯是 浙江
SELECT * FROM t_employee
WHERE salary > 20000 || native_place = "浙江";

#查询非浙江籍的男生
SELECT * FROM t_employee
WHERE NOT native_place = "浙江" AND gender = "男";

#查询奖金比例非空的员工
SELECT * FROM t_employee
WHERE commission_pct IS NOT NULL;

4、区间范围和集合范围运算符

#查询薪资大于等于10000 并且小于等于15000的员工
SELECT * FROM t_employee
WHERE salary BETWEEN 10000 AND 15000;

#查询籍贯是 “浙江”、“北京”、“上海”、“黑龙江”的员工
SELECT * FROM t_employee
WHERE native_place IN ("浙江","上海","北京","黑龙江");

#查询籍贯不是 “浙江”、“北京”、“上海”、“黑龙江”的员工
SELECT * FROM t_employee
WHERE native_place NOT IN ("浙江","上海","北京","黑龙江");

5、模糊查询运算符

#查询名字中，第二个字是“冰”
SELECT * FROM t_employee
WHERE ename LIKE "_冰%";

6、位运算符：<<,>>,&,|,^,~,>>>

三、关联查询

关联查询图解

（1）A∩B

（2）A

(3)A - A∩B

（4）B

(5)B-A∩B

（6）A∪B

（7）A∪B - A∩B

1、内连接

#查询所有的员工的姓名和他所在部门的编号和部门的名称，不包括那些没有安排部门的员工
SELECT ename,did,dname FROM t_employee INNER JOIN t_department  #错误
#1052 - Column "did" in field list is ambiguous，因为did没有说明是哪个表的

SELECT ename,t_employee.did,dname
FROM t_employee INNER JOIN t_department
ON t_employee.did = t_department.did;

SELECT ename,t_employee.did,dname
FROM t_employee , t_department
WHERE t_employee.did = t_department.did;

#查询所有的女员工的姓名和他所在部门的编号和部门的名称，不包括那些没有安排部门的员工
SELECT ename,t_employee.did,dname
FROM t_employee INNER JOIN t_department
ON t_employee.did = t_department.did
WHERE gender = "女";

SELECT ename,t_employee.did,dname
FROM t_employee , t_department
WHERE t_employee.did = t_department.did AND gender = "女";


#查询员工编号，员工的姓名，部门编号，部门名称，职位编号，职位名称
#需要t_employee,t_department, t_job
SELECT t_employee.eid, t_employee.`ename`, t_department.did,t_department.`dname`, t_job.`job_id`,t_job.`job_name`
FROM t_employee INNER JOIN t_department INNER JOIN t_job
ON t_employee.did = t_department.did AND t_employee.`job_id` = t_job.`job_id`;


SELECT t_employee.eid, t_employee.`ename`, t_department.did,t_department.`dname`, t_job.`job_id`,t_job.`job_name`
FROM t_employee , t_department , t_job
WHERE t_employee.did = t_department.did AND t_employee.`job_id` = t_job.`job_id`;

2、左连接

#查询所有员工和他的部门编号，部门名称，包括那些没有部门的员工
SELECT * 
FROM t_employee LEFT JOIN t_department
ON t_employee.did = t_department.did;

#查询所有没有部门的员工
#不用关联查询也可以实现
SELECT * FROM t_employee WHERE did IS NULL;

#用关联查询
SELECT * 
FROM t_employee LEFT JOIN t_department
ON t_employee.did = t_department.did
WHERE t_employee.did IS NULL;

3、全连接

#查询所有员工和部门信息，包括那些没有部门的员工和没有员工的部门
SELECT *
FROM t_employee LEFT JOIN t_department
ON t_employee.did = t_department.did
UNION
SELECT *
FROM t_employee RIGHT JOIN t_department
ON t_employee.did = t_department.did;

#查询那些没有部门的员工和没有员工的部门

SELECT *
FROM t_employee LEFT JOIN t_department
ON t_employee.did = t_department.did
WHERE t_employee.did IS NULL
UNION
SELECT *
FROM t_employee RIGHT JOIN t_department
ON t_employee.did = t_department.did
WHERE t_employee.did IS NULL;

4、自连接

#查询员工的编号，员工的姓名，领导的编号，领导的姓名
#因为员工的信息和领导的信息都在t_employee表
SELECT emp.eid,emp.ename,emp.`mid`,mgr.ename
FROM t_employee AS emp INNER JOIN t_employee AS mgr  #emp代表员工表，mgr代表领导表
ON emp.`mid` = mgr.`eid`;  #员工的领导编号 = 领导的员工编号

附、数据库导入脚本

;

;

;
;
;
;
CREATE DATABASE `test` ;

USE `test`;



DROP TABLE IF EXISTS `t_department`;

CREATE TABLE `t_department` (
  `did` int(11) NOT NULL AUTO_INCREMENT,
  `dname` varchar(20) NOT NULL,
  `description` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`did`),
  UNIQUE KEY `dname` (`dname`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8;



insert  into `t_department`(`did`,`dname`,`description`) values (1,"教学部","负责教学工作"),(2,"咨询部","负责咨询工作"),(3,"运营部","负责运营工作"),(4,"财务部","负责财务工作"),(5,"后勤部","负责后勤工作");



DROP TABLE IF EXISTS `t_employee`;

CREATE TABLE `t_employee` (
  `eid` int(11) NOT NULL AUTO_INCREMENT,
  `ename` varchar(20) NOT NULL,
  `tel` char(11) NOT NULL,
  `gender` char(1) DEFAULT "男",
  `salary` double DEFAULT NULL,
  `commission_pct` double DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `job_id` int(11) DEFAULT NULL,
  `email` varchar(32) DEFAULT NULL,
  `mid` int(11) DEFAULT NULL,
  `address` varchar(150) DEFAULT NULL,
  `native_place` varchar(10) DEFAULT NULL,
  `did` int(11) DEFAULT NULL,
  PRIMARY KEY (`eid`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8;



insert  into `t_employee`(`eid`,`ename`,`tel`,`gender`,`salary`,`commission_pct`,`birthday`,`hiredate`,`job_id`,`email`,`mid`,`address`,`native_place`,`did`) values (1,"孙红雷","13789098765","男",8000.46,0.4,"1980-10-08","2011-07-28",2,"shl@atguigu.com",7,"白庙村西街","浙江",1),(2,"何炅","13456732145","男",7000.67,0.32,"1984-08-03","2015-07-03",2,"hj@atguigu.com",7,"半截塔存","河北",1),(3,"邓超","18678973456","男",8000,NULL,"1985-04-09","2014-07-01",2,"dc@atguigu.com",7,"宏福苑","陕西",1),(4,"黄晓明","13609876789","男",9456,0.12,"1986-09-07","2015-08-08",11,"hxm@atguigu.com",22,"白庙村东街","黑龙江",3),(5,"陈赫","13409876545","男",8567,0.23,"1978-08-02","2015-01-01",2,"ch@atguigu.com",7,"回龙观","吉林",1),(6,"韩庚","18945678986","男",12000,0.24,"1985-04-03","2015-02-02",2,"hg@atguigu.com",7,"龙泽","内蒙古",1),(7,"贾乃亮","15490876789","男",15700,0.14,"1982-08-02","2015-03-03",1,"jnl@atguigu.com",7,"霍营","新疆",1),(8,"李晨","13587689098","男",9000,0.15,"1983-03-02","2015-01-06",3,"lc@atguigu.com",7,"东三旗","西藏",1),(9,"李易峰","13467676789","男",7897,NULL,"1984-09-01","2015-04-01",2,"lyf@atguigu.com",7,"西山旗","天津",1),(10,"鹿晗","13689876789","男",8789,NULL,"1989-04-02","2014-09-03",2,"lh@atguigu.com",7,"天通苑一区","江苏",1),(11,"黄渤","13787876565","男",15678,NULL,"1983-05-07","2014-04-04",2,"hb@atguigu.com",7,"立水桥","安徽",1),(12,"范冰冰","13576234554","女",8909,NULL,"1986-04-02","2014-02-08",2,"fbb@atguigu.com",7,"立城苑","贵州",1),(13,"李冰冰","13790909887","女",18760,NULL,"1987-04-09","2015-06-07",2,"lbb@atguigu.com",7,"王府温馨公寓","四川",1),(14,"谢娜","13234543245","女",18978,NULL,"1990-01-01","2015-09-05",4,"xn@atguigu.com",14,"园中园","成都",2),(15,"董洁","13876544333","女",8978,NULL,"1987-05-05","2015-08-04",5,"dj@atguigu.com",14,"小辛庄","云南",2),(16,"汤唯","18264578930","女",9878,NULL,"1988-03-06","2015-03-06",5,"tw@atguigu.com",14,"西二旗","宁夏",2),(17,"李诗诗","18567899098","女",9000,NULL,"1990-08-09","2013-06-09",5,"lss@atguigu.com",14,"清河","河南",2),(18,"舒淇","18654565634","女",16788,NULL,"1978-09-04","2013-04-05",6,"sq@atguigu.com",18,"名流花园","福建",4),(19,"周迅","13589893434","女",7876,NULL,"1988-06-13","2014-04-07",7,"sq@atguigu.com",18,"小汤山","广西",4),(20,"章子怡","15634238979","女",15099,NULL,"1989-12-11","2015-08-04",8,"zzy@atguigu.com",20,"望都家园","广东",5),(21,"白百何","18909876789","女",9787,NULL,"1989-09-04","2014-06-05",9,"bbh@atguigu.com",20,"西湖新村","海南",5),(22,"刘烨","18890980989","男",130990,0.1,"1990-11-09","2016-08-09",10,"ly@atguigu.com",22,"多彩公寓","北京",3),(23,"陈坤","18712345632","男",130990,0.2,"1990-02-04","2016-05-09",2,"ck@atguigu.com",2,"天通苑二区","上海",1),(24,"迪丽热巴","17290876543","女",10289,0.4,"1990-04-01","2017-02-06",7,"dlrb@atguigu.com",18,"北苑","北京",5),(25,"姚笛","18709675645","女",9087,NULL,"1989-08-01","2017-09-01",7,"yd@atguigu.com",18,"望京","北京",5);



DROP TABLE IF EXISTS `t_job`;

CREATE TABLE `t_job` (
  `job_id` int(11) NOT NULL AUTO_INCREMENT,
  `job_name` varchar(20) DEFAULT NULL,
  `description` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`job_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;



insert  into `t_job`(`job_id`,`job_name`,`description`) values (1,"教学总监","负责教学管理工作"),(2,"讲师","负责教学工作"),(3,"助教","负责辅导工作"),(4,"咨询主管","负责咨询管理工作"),(5,"咨询师","负责咨询工作"),(6,"财务主管","负责财务工作"),(7,"出纳","负责出纳工作"),(8,"后勤主管","负责后勤管理工作"),(9,"网络管理员","负责网络管理"),(10,"运营主管","负责运营管理"),(11,"运营工程师","负责运营推广");

;
;
;
;