/*
SQLyog Ultimate v11.25 (64 bit)
MySQL - 5.7.28-log : Database - test
*********************************************************************
*//*!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 */;CREATEDATABASE/*!32312 IF NOT EXISTS*/`test`/*!40100 DEFAULT CHARACTER SET utf8 */;USE`test`;/*Table structure for table `depts` */DROPTABLEIFEXISTS`depts`;CREATETABLE`depts`(`id`int(11)NOTNULLAUTO_INCREMENT,`name`varchar(100)DEFAULTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=9DEFAULTCHARSET=utf8;/*Data for the table `depts` */insertinto`depts`(`id`,`name`)values(1,'教学部'),(2,'教务部'),(3,'运营部'),(4,'咨询部'),(5,'销售部'),(6,'新媒体'),(7,'物流部'),(8,'研发部');/*Table structure for table `employees` */DROPTABLEIFEXISTS`employees`;CREATETABLE`employees`(`id`int(11)NOTNULLAUTO_INCREMENT,`last_name`varchar(100)NOTNULL,`email`varchar(100)DEFAULTNULL,`salary`double(11,2)DEFAULTNULL,`deptId`int(11)DEFAULTNULL,PRIMARYKEY(`id`),KEY`deptId`(`deptId`),CONSTRAINT`employees_ibfk_1`FOREIGNKEY(`deptId`)REFERENCES`depts`(`id`))ENGINE=InnoDBAUTO_INCREMENT=11DEFAULTCHARSET=utf8;/*Data for the table `employees` */insertinto`employees`(`id`,`last_name`,`email`,`salary`,`deptId`)values(1,'韩总','hybing@atguigu.com',30000.00,1),(2,'马云','mayun@alibaba.com',20000.00,2),(3,'马化腾','mahuateng@tencent',20000.00,3),(4,'李彦宏','liyanhong@baidu.com',10000.00,4),(5,'雷军','leijun@xiaomi.com',10000.00,8),(6,'王健林','wangjianlin@wanda.com',9000.00,5),(7,'许家印','xujiayin@hengda.com',8000.00,5),(8,'张一鸣','zhangyiming@toutiao.com',7000.00,6),(9,'刘强东','liuqiangdong@jd.com',10000.00,7),(10,'王卫','wangwei@shunfeng.com',6000.00,7);/*!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的单行注释-- 这也是MySQL的单行注释(注意:两个减号后需要有空格)/*
这是MySQL的多行注释,和Java中的一样
*/#MySQL中的运算符/*
1.算术运算符
加 +
减 -
乘 *
除 / div(只保留整数部分)
模 % mod
*/SELECT10 MOD 3;/*
2.比较运算符
大于 >
小于 <
大于等于 >=
小于等于 <=
等于 = ,不能用于null值的判断
不等于 != 或者 <>
安全等于 <=> ,用于null值的判断
*/SELECT*FROM employees WHERE salary !=20000.00;SELECT*FROM employees WHERE email <=>NULL;/*
3.逻辑运算符(建议使用英文单词)
与(且) && 或者使用英文单词 and
或 || 或者使用英文单词 or
非 ! 或者使用 not
异或 ^ 或者使用 xor
*/#查询工资是10000元并且部门id是7的员工SELECT*FROM employees WHERE salary =10000.00&& deptId =7;SELECT*FROM employees WHERE salary =10000.00AND deptId =7;#查询工资是10000元或者部门id是7的员工SELECT*FROM employees WHERE salary =10000.00|| deptId =7;SELECT*FROM employees WHERE salary =10000.00OR deptId =7;#查询工资不是10000元的员工SELECT*FROM employees WHERE salary !=10000.00#异或的演示SELECTTRUE^TRUE;# 0SELECTFALSE^FALSE;# 0SELECTTRUE^FALSE;# 1SELECTFALSE^TRUE;# 1/*
4.范围
区间范围(在某两个值之间,包含这两个值)
between 值1 and 值2
not between 值1 and 值2
集合范围
in(值1,值2,值3)
not in(值1,值2,值3)
*/#查询员工的工资在20000到30000之间的员工SELECT*FROM employees WHERE salary BETWEEN20000.00AND30000.00;#查询员工的工资在20000到30000之间的员工SELECT*FROM employees WHERE salary NOTBETWEEN20000.00AND30000.00;#查询工资是7000、8000、9000的员工SELECT*FROM employees WHERE salary IN(7000.00,8000.00,9000.00);#查询工资不是7000、8000、9000的员工SELECT*FROM employees WHERE salary NOTIN(7000.00,8000.00,9000.00);/*
5.模糊查询
like '_xxx'
_ 代表1个字符
like '%xxx'
% 代表 0-n个字符
*/#查询employees表中姓名以马开头,并且名字是两个字的员工SELECT*FROM employees WHERE last_name LIKE'马_';#查询employees表中姓名以马开头的所有的员工SELECT*FROM employees WHERE last_name LIKE'马%';#查询employees表中姓名中包含马的员工SELECT*FROM employees WHERE last_name LIKE'%马%';/*
6.对应null值的操作
1)判断
is null 或者 <=> null
等于null
is not null
不等于null
2)计算
ifnull(xxx,代替值)
当xxx为null时使用代替值
*/#查询employees表中邮箱为null的员工SELECT*FROM employees WHERE email ISNULL;SELECT*FROM employees WHERE email <=>NULL;#查询employees表中邮箱不为null的员工SELECT*FROM employees WHERE email ISNOTNULL;#当员工的邮箱是null时显示这个员工没有邮箱SELECT IFNULL(email,'这个员工没有邮箱')FROM employees;