1.列约束
mysql可以对要插入的数据进行特定的验证,只有满足条件才允许插入到数据表中,否则被认为非法的插入。
例如:一个人的性别只能是男或者女,一个人的年龄0~100
(1).主键约束——PRIMARY KEY
声明了主键约束的列上不能出现重复的值,表中查询的记录会按照主键从小到大排序—加快查询速度,通常主键加在编号列。
注意:一个表中只能出现一个主键,主键列上不允许插入NULL,(NULL表示空,插入数据时,无法确定要保存的值,例如无法确定员工的工资、姓名、声明....)
2019年4月16日星期二
(2).非空约束——NOT NULL
声明了非空约束的列上不能插入NULL值
(3).唯一约束——UNIQUE
声明了唯一约束(UNIQUE)的列上不能插入重复的值,允许插入NULL,而且允许插入多个NULL。一个表中可以有多个UNIQUE约束
说明:NULL这个值比较特殊,它和任何值都不等,甚至和自身都不等 |
(4).检查约束——CHECK
检查约束可以对插入的数据进行自定义验证
CREATE TABLE student(
score TINYINT CHECK(score>=0 AND score<=100) );
MySQL不支持检查约束,会降低数据插入速度
(5).默认值约束——DEFAULT
可以使用DEFAULT关键字声明默认值,有两种方式可以使用默认值,
INSERT INTO family VALUES(50, '华硕', DEFAULT);
INSERT INTO family(fid,fname) VALUES(60,'荣耀');
(6).外键约束——FOREIGN KEY
声明了外键约束的列,取值必须在另外一个表的主键列上出现过,两者的列类型要保持一致,允许使用NULL或者多个NULL
FROMIGN KEY(外键列) REFERENCES(引用) 数据表(主键列)
familyId INT,
FOREIGN KEY(familyId) REFERENCES laptop_family(fid)
#familyId外键引入laptop_family (did)
2.mysql中的自增列
AUTO_INCREMENT:自动增长,假如一个列声明了自增列,无需手动赋值,直接赋值为NULL,会获取当前的最大值,然后+1。
注意: 只适用于整型的列上,自增允许手动赋值 |
练习:创建脚本文件01_tedu.sql,创建数据库tedu;进入该数据库 创建部门表dept,包含(did,dname) 10 研发部 20 市场部 30 运营部 40 测试部 创建员工表emp,包含(eid,ename,sex,birthday,salary,deptId 所属部门编号),使用恰当的列约束和自增 插入15条记录
| #设置服务器编码先丢弃,再创建数据库tedu,进入数据库 SET NAMES UTF8; DROP DATABASE IF EXISTS tedu; CREATE DATABASE tedu CHARSET=UTF8; USE tedu; #创建部门表dept,包含did,dname CREATE TABLE dept( did SMALLINT PRIMARY KEY AUTO_INCREMENT, #主键 dname VARCHAR(10) UNIQUE #唯一约束 部门名不允许重复出现 ); INSERT INTO dept VALUES(10,'研发部'); INSERT INTO dept VALUES(20,'市场部'); INSERT INTO dept VALUES(30,'运营部'); INSERT INTO dept VALUES(40,'测试部'); CREATE TABLE emp( eid INT PRIMARY KEY AUTO_INCREMENT, #主键 自增 ename VARCHAR(10), sex BOOL DEFAULT 0, birthday DATE, salary DECIMAL(8,2), deptld SMALLINT, FOREIGN KEY(deptld) REFERENCES dept(did) #deptid外键引入dept(did) ); INSERT INTO emp VALUES(1,'A',1,'2018-01-01',5000,10); INSERT INTO emp VALUES(2,'B',0,'2018-02-02',5500,20); INSERT INTO emp VALUES(16,'R',0,'2019-08-08',8500,40); |
3.简单查询
3.1查询特定的列
示例:查询所有员工的姓名、生日
SELECT ename,birthdayFROM emp;
练习:查询所有员工的编号,姓名,性别,工资
SELECT eid,ename,sex,salary FROM emp;
3.2查询所有的列
查询所有的列
SELECT * FROM emp; or
SELECT eid,ename,sex,birthday,salary,deptId FROM emp;
3.3给列起别名
AS AS关键字可以省略,保留空格
示例:查询所有员工的姓名和工资,使用中文别名
SELECT ename AS 姓名,salary AS 工资 FROM emp;
练习:查询所有员工的编号,姓名,性别,生日,使用中文别名
SELECT eid AS 编号,ename AS 姓名,sex AS 性别,birthday AS 生日 FROM emp;
练习:查询所有员工的编号和姓名,使用一个之母作为别名
SELECT eid AS a,ename AS b FROM emp;
3.4显示不同的记录/合并相同的记录
DISTINCT 合并相同的记录
示例:查询出员工都在哪些部门
SELECT DISTINCT deptId FROM emp;
练习:查询出都有哪些性别的员工
SELECT DISTINCT sex FROM emp;
3.5在查询时执行计算
示例:计算2+3*5+7.4*3.5
SELECT 2+3*5+7.4*3.5;
练习:查询所有员工的姓名及其年薪
SELECT ename,salary*12 FROM emp;
练习:假设每个员工工资增加500,年终奖5000,查询所有员工的姓名及其年薪,给列别起中文别名。
SELECT ename 姓名,(salary+500)*12+5000 加工资后年薪 FROM emp;
3.6查询结果集排序
ORDER BY ASC/DESC 按照..排序 ASC升序, DESC降序
示例:查询所有的部门,结果集按照部门编号升序排列
SELECT * FROM dept ORDER BY did ASC;
示例:查询所有的部门,结果集按照部门编号降序排列
SELECT * FROM dept ORDER BY did DESC;
练习:查询员工所有列,结果集按照工资的降序排列
SELECT * FROM emp ORDER BY salary DESC;
练习:查询员工所有列,结果集按照年龄从小到大排列
SELECT * FROM emp ORDER BY birthday DESC;
练习:查询员工所有列,结果集按照姓名升序排列
SELECT * FROM emp ORDER BY ename; 默认升序排列 ASC
练习:查询员工所有列,结果集按照工资降序排列,如果工资相同,按照姓名排序
SELECT * FROM emp ORDER BY salary DESC,ename;
练习:查询员工所有列,结果集按照性别升序排列,如果性别相同,按照生日降序排列。
SELECT * FROM emp ORDER BY sex ASC,birthday ASC;
ORDER BY 可以按照数值、字符串、日期时间排序、默认是按照升序排列(ASC) |
3.7 条件查询
WHERE
比较运算符:> < = != >= <=
关键字: IS NULL/IS NOT NULL AND/OR BETWEEN ... AND../NOT BETWEEN...AND... IN(a,b )/NOT IN (a,b ) |
示例:查询出编号为5的员工所有列
SELECT * FROM emp WHERE eid=5;
练习:查询出姓名叫King的员工的编号,工资,姓名,生日
SELECT eid,salary,ename,birthday FROM emp WHERE ename='King';
练习:查询出20号部门下员工所有的列
SELECT * FROM emp WHERE deptId=20;
练习:查询出工资为6000以上的员工所有列
SELECT * FROM emp WHERE salary>=6000;
练习:查询出1993-1-1后出生的员工所有列
SELECT * FROM emp WHERE birthday>='1993-1-1';
练习:查询出不在10号部门的员工所有列
SELECT * FROM emp WHERE deptId!=10;
练习:查询出没有明确部门的员工所有列
SELECT * FROM emp WHERE deptId IS NULL;
练习:查询出有明确部门的员工所有列
SELECT * FROM emp WHERE deptId IS NOT NULL;
练习:查询出工资在6000以上男员工所有列
SELECT * FROM emp WHERE salary>=6000 AND sex=1;
练习:查询出工资在7000~10000之间员工所有列
SELECT * FROM emp WHERE salary >=7000 AND salary<=10000;
SELECT * FROM emp WHERE salary BETWEEN 7000 AND 10000;
练习:查询出工资不在7000~ 10000之间的员工所有列
SELECT * FROM emp WHERE salary<7000 OR salary>10000;
SELECT * FROM emp WHERE salary NOT BETWEEN 7000 AND 10000;
练习:查询出1990年之前和1995之后出生的员工所有列
SELECT * FROM emp WHERE birthday<'1990-1-1' OR birthday>'1995-12-31';
SELECT * FROM emp WHERE birthday NOT BETWEEN '1990-1-1' AND '1995-12-31';
练习:查询出1993年出生的员工所有列
SELECT * FROM emp WHERE birthday BETWEEN '1993-1-1' AND '1993-12-31';
SELECT * FROM emp WHERE birthday > '1993-1-1' AND birthday < '1993-12-31';
练习:查询出20号部门和30号部门的员工所有列
SELECT * FROM emp WHERE deptId=30 OR deptId=20;
SELECT * FROM emp WHERE deptId IN(20,30);
练习:查询出不在20号部门和30号部门员工所有列
SELECT * FROM emp WHERE deptId!=30 AND deptId!=20;
SELECT * FROM emp WHERE deptId NOT IN(20,30);
3.8分页查询
假如查询的结果集中有太多的数据,一次显示不完,可以分页显示。
需要有两个条件:当前的页码、每页的数据量
每页的开始=(当前的页码-1)*每页的数量
分页查询语法:
SELECT * FROM emp LIMIT start,count;
start: 是一个数字,表示从哪一条开始读取;
start=(当前的页码-1)*每页的数据量 |
count:是一个数字,表示每页的数据量
注意:start和count的值必须是整数,不能是字符串形式。
假设每页显示5条记录
第1页:SELECT * FROM emp LIMIT 0,5;
第2页:SELECT * FROM emp LIMIT 5,5;
第3页:SELECT * FROM emp LIMIT 10,5;
第4页:SELECT * FROM emp LIMIT 15,5;
第5页:SELECT * FROM emp LIMIT 20,5;
练习:假设每页显示6条记录,查询3页
SELECT * FROM emp LIMIT 0,6;
SELECT * FROM emp LIMIT 6,6;
SELECT * FROM emp LIMIT 12,6;
3.9 模糊条件查询
LIKE %
% 可以匹配任意多个字符 >=0 _ 可以匹配任意1个字符 =1 以上两个匹配必须使用LIKE关键字 |
示例:查询姓名中含有字母e的员工所有列
SELECT * FROM emp WHERE LIKE '%e%';
练习:查询姓名中以e结尾的员工所有列
SELECT * FROM emp WHERE LIKE '%e';
练习:查询姓名中倒数第2个字符为e的员工所有列
SELECT * FROM emp WHERE LIKE '%e_';
2.复杂查询
(1) 聚合查询/分组查询
示例:查询出所有员工的数量
SELECT COUNT(eid) FROM emp;
SELECT COUNT(*) FROM emp; #推荐写法
练习:使用员工的姓名来查询数量
SELECT COUNT(ename) FROM emp;
练习:使用员工的部门编号来查询数量
SELECT COUNT(deptId) FROM emp;
练习:查询所有男员工的数量
SELECT COUNT(*) FROM emp WHERE sex=1;
聚合函数:COUNT()/SUM()/AVG()/MAX()/MIN() 函数就是一个功能体,需要提供若干个数据,产出某个结果。 COUNT()---总数量 SUM()---总和 AVG()---平均值 MAX()---最大值 MIN()---最小值 |
练习:查询所有员工的工资总和
SELECT SUM(salary) FROM emp;
练习:查询所有员工的平均工资
SELECT AVG(salary) FROM emp;
SELECT SUM(salary)/COUNT(*) FROM emp;
练习:查询出男员工中工资最高的
SELECT MAX(salary) FROM emp WHERE sex=1;
练习:查询出年龄最大的员工
SELECT MIN(birthday) FROM emp;
分组查询:只能查询分组条件和聚合函数 |
练习:查询出男、女员工的平均工资,最高工资
SELECT sex,AVG(salary),MAX(salary) FROM emp GROUP BY sex;
练习:查询出每个部门的员工数量,最高工资,最低工资
SELECT deptId 部门,Count( *),MAX(salary),MIN(salary) FROM emp GROUP BY deptId;
YEAR(..) 获取日期中的年份 MONTH(..) 获取日期中的月份 |
练习:查询出1991年出生的员工所有列
SELECT * FROM emp WHERE TEAR(birthday)=1993;
练习:查询5月份出生的员工所有列
SELECT * FROM emp WHERE MONTH(birthday)=5;
3.子查询
子查询:把多个SQL语句的查询结果作为另外一个SQL语句的查询条件 |
示例:查询出研发部员工的所有列
步骤一:查询出研发部的部门编号
SELECT did FROM dept WHERE dname='研发部';
步骤二:根据研发部部门编号查询员工
SELECT * FROM emp WHERE deptId=10;
综合:
SELECT * FROM emp WHERE deptId=( SELECT did FROM dept WHERE dname='研发部');
示例:查询比tom工资高的员工有哪些
步骤一:查询出tom的工资---6000
SELECT salary FROM emp WHERE ename='tom';
步骤二:查询出比tom高的 高于6000
SELECT * FROM emp WHERE salary>6000;
综合:
SELECT * FROM emp WHERE salary>(SELECT salary FROM emp WHERE ename='tom');
练习:查询出和tom同一年出生的员工
SELECT * FROM emp WHERE YEAR(birthday)=(SELECT YEAR(birthday) FROM emp WHERE ename='tom');
4.多表查询
示例:查询所有的员工及其部门名称
SELECT ename,dname FROM emp,dept; 错误:会出现 笛卡尔积
多表查询如何避免笛卡尔积:添加查询条件
SELECT ename,dname FROM emp,dept WHERE deptId=did; ---无法查询出没有部门的员工和没有员工的部门
上述的多表查询语法是SQL-92中的,无法查询出没有部门的员工和没有员工的部门 SQL-99中提到了新的多表查询语法 |
1.内连接——b1 INNER JION b2 ON ... 结果和SQL-92一致
SELECT ename,dname FROM emp INNER JOIN dept ON deptId=did;
2.左外连接——b1 LEFT OUTER JOIN b2 ON ..
SELECT ename,dname FROM emp LEFT OUTER JOIN dept ON deptId=did;
查询结果是左侧所有的记录都有显示,OUTER可以省略
3.右外连接——b1 RIGHT OUTER JOIN b2 ON..
SELECT ename,dname FROM emp RIGHT OUTER JOIN dept ON deptId=did;
查询的结果是右侧表中所有的记录都有显示,OUTER可以省略。
4.全连接 FULL JOIN
显示左侧和右侧所有的记录——MySQL不支持
UNION 合并相同的项,
UNION ALL 不合并相同的项
(SELECT ename,dname FROM emp LEFT OUTER JOIN dept ON deptId=did) UNION
(SELECT ename,dname FROM emp RIGHT OUTER JOIN dept ON deptId=did);