Mysql查询
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、算数运算符
/*算术运算符：
加：+
减：-
乘：*
除：/ 或 div
div只保留整数部分
模：% 或 mod
*/
#查询员工的姓名和薪资
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、逻辑运算符
/*逻辑运算符
与：&& 或 and
或：|| 或 or
非：! 或 not
*/
#查询薪资高于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、区间范围和集合范围运算符
/*区间范围和集合运算符
区间范围：
between xx and yy：[xx,yy]
not between xx and yy： 小于xx 或 大于yy
集合范围：
in (值列表)
not in(值列表)
*/
#查询薪资大于等于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、模糊查询运算符
/*模糊查询:
like "xx"
xx可以用占位符：
_：代表确定的一个字符
%：代表是任意个数的字符，0~n个
*/
#查询名字中，第二个字是“冰”
SELECT * FROM t_employee
WHERE ename LIKE "_冰%";
6、位运算符：<<,>>,&,|,^,~,>>>
三、关联查询
/*关联查询：
1、内连接：inner join
2、外连接
（1）左外连接：left join
（2）右外连接：right join
（3）全外连接：full join mysql不支持
关联查询的结果一共有七种：
关联查询必须有两张或两张以上，以下用两张来示例：
（1）A ∩ B
用内连接
（2）A
（3）A - A ∩ B
（2）和（3）用左连接
（4）B
（5）B - A ∩ B
（4）（5）用右连接
（6）A ∪ B
（7）A ∪ B - A ∩ B
（6）（7）过去应该用全外连接，现在用union
（6）（2）的结果 union （4）B
（7）（3）的结果 union （5）的结果
*/
关联查询图解
（1）A∩B
（2）A
(3)A - A∩B
（4）B
(5)B-A∩B
（6）A∪B
（7）A∪B - A∩B
1、内连接
/*内连接
（1）形式一:
select 字段列表
from A表 inner join B表
on 关联条件
【where 其他筛选条件】
说明：
如果不写关联条件，会出现一种现象：笛卡尔积
关联条件的个数 = n - 1，n是几张表关联
on只能和join一起用
（2） 形式二
select 字段列表
from A表 , B表
where 关联条件 【and 其他筛选条件】
*/
#查询所有的员工的姓名和他所在部门的编号和部门的名称，不包括那些没有安排部门的员工
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、左连接
/*左连接：
第一种结果：A
select 字段列表
from A表 left join B表
on 关联条件
第二种结果：A - A∩B
select 字段列表
from A表 left join B表
on 关联条件
where 从表的关联字段 is null
左连接和右连接的区别：
（1）left换成right
（2）以左边的表为主还是以右边的表为主
右连接
第一种结果：B
select 字段列表
from A表 right join B表
on 关联条件
第二种结果：B - A∩B
select 字段列表
from A表 right join B表
on 关联条件
where 从表的关联字段 is null
*/
#查询所有员工和他的部门编号，部门名称，包括那些没有部门的员工
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、全连接
/*使用union实现全连接的效果
（1）A ∪ B
select 字段列表
from A表 left join B表
on 关联条件
union
select 字段列表
from A表 right join B表
on 关联条件
（2）A ∪ B - A ∩ B
select 字段列表
from A表 left join B表
on 关联条件
where 从表的关联字段 is null
union
select 字段列表
from A表 right join B表
on 关联条件
where 从表的关联字段 is null
*/
#查询所有员工和部门信息，包括那些没有部门的员工和没有员工的部门
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;
#查询那些没有部门的员工和没有员工的部门
/*
where xxx is null
xxx 看从表
员工表和部门表来说，员工表是从表。
因为主表的字段，例如did是不可能为null
*/
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`; #员工的领导编号 = 领导的员工编号
附、数据库导入脚本
/*SQLyog Ultimate v11.25 (64 bit)
MySQL - 5.5.27 : Database - mysqldb
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=""*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE="NO_AUTO_VALUE_ON_ZERO" */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`test` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `test`;
/*Table structure for table `t_department` */
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;
/*Data for the table `t_department` */
insert into `t_department`(`did`,`dname`,`description`) values (1,"教学部","负责教学工作"),(2,"咨询部","负责咨询工作"),(3,"运营部","负责运营工作"),(4,"财务部","负责财务工作"),(5,"后勤部","负责后勤工作");
/*Table structure for table `t_employee` */
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;
/*Data for the table `t_employee` */
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);
/*Table structure for table `t_job` */
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;
/*Data for the table `t_job` */
insert into `t_job`(`job_id`,`job_name`,`description`) values (1,"教学总监","负责教学管理工作"),(2,"讲师","负责教学工作"),(3,"助教","负责辅导工作"),(4,"咨询主管","负责咨询管理工作"),(5,"咨询师","负责咨询工作"),(6,"财务主管","负责财务工作"),(7,"出纳","负责出纳工作"),(8,"后勤主管","负责后勤管理工作"),(9,"网络管理员","负责网络管理"),(10,"运营主管","负责运营管理"),(11,"运营工程师","负责运营推广");
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
以上是 Mysql查询 的全部内容， 来源链接： utcz.com/z/533014.html
