介绍各种约束

一.字段约束

1.概述

常见的约束:主键约束,非空约束,唯一约束

2.主键约束

表设计的原则是,表里必须有主键,作为每条记录的唯一标识

当表里的字段加了主键约束时,效果是,字段的值必须唯一且不能为null

通常会使用自增策略,是指主键的值从此不必我们设计,交给了数据库来管理

#主键约束:给表里的某个字段设计约束,
#特点是字段值必须唯一且不能null
CREATE TABLE a(id INT PRIMARY KEY)
#主键自增策略:主键的值交给数据库维护管理(从1开始,++)
CREATE TABLE b(id INT PRIMARY KEY AUTO_INCREMENT)

3.非空约束

#唯一约束:值不能相同
CREATE TABLE e(
id INT PRIMARY KEY AUTO_INCREMENT,#主键约束
NAME VARCHAR(20) UNIQUE NOT NULL #唯一约束,值不能重复
)

二.基本函数

1.概述

Mysql数据库提供了丰富的函数,

常见的:lower upper length concat substr replace ifnull tound/ceil/floor

2.测试

#只查指定列的值
#UPPER全转大写 LOWER全转小写
SELECT ename,UPPER(ename),LOWER(ename) FROM emp
`dept`#length求长度(一个字母算一个字符,一个汉字算三个字符)
SELECT LENGTH(ename),ename,LENGTH(job),job FROM emp
#concat(1,2,3)拼接字符串-1是字段名2和3都是要拼的内容
SELECT ename,CONCAT(ename,'hello',100) FROM emp

#查部门名称
#substr(1,2,3)截取字符串-1是字段名2是开始位置3是总长度
SELECT dname,SUBSTR(dname,2),SUBSTR(dname,2,3) FROM dept
#replace(1,2,3)替换-1是字段名2是要被替换的3是新的数据
SELECT dname,REPLACE(dname,'o','666') FROM dept
#ifnull(1,2)判断是否为null,如果是null就替换成0-1是字段名2是要替换的值
SELECT comm,IFNULL(comm,0) FROM emp
SELECT *,sal+IFNULL(comm,0) FROM emp

#round四舍五入/ceil向上取整/floor向下取整
SELECT comm,ROUND(comm),CEIL(comm),FLOOR(comm) FROM emp 
#now/CURDATE/CURTIME
SELECT NOW() #年月日 时分秒
SELECT CURDATE()#年月日
SELECT CURTIME()#时分秒
#year/month/day/hour/minute/second
SELECT YEAR('2000-07-27')
SELECT YEAR(NOW()),MONTH(NOW()),DAY(NOW())
SELECT HOUR(NOW()),MINUTE(NOW()),SECOND(NOW())

三,条件查询

1.where like

#查询部门地址
SELECT loc FROM dept
#DISTINCT用来给结果去重distinct
SELECT DISTINCT loc FROM dept

#where满足条件的才查出来
#查询deptno等于1的部门记录
SELECT * FROM dept WHERE deptno=1
#查询地址在一区的部门记录
SELECT * FROM dept WHERE loc='一区'
#查询地址在二区的部门名称
SELECT dname FROM dept WHERE loc='二区'
#根据名称查询部门记录
SELECT * FROM dept WHERE dname='research'
#根据名称和地址 查询部门记录
SELECT * FROM dept 
#WHERE loc='二区' and dname='research2'#并且关系
WHERE loc='二区' OR dname='research2'#或者关系

SELECT * FROM dept 
WHERE 1=1

#查询工资>8000的员工信息
SELECT * FROM emp WHERE sal>8000
#查询工资是8000的或者工资是3000的员工信息
SELECT * FROM emp WHERE
# sal=8000 or sal=3000
sal IN (3000,8000) #作用意义同上

SELECT * FROM emp WHERE
empno IN (100,300,500)

#like 像,模糊查询,通常配合%作为占位符
#查询名字里包含a的员工信息
SELECT * FROM emp WHERE
#ename like '%a%' #包含a,动态匹配0~n个字符
ename LIKE 'l%' #以l开头,动态匹配0~n个字符

2.null limit order by等

#查询工资>8000的员工信息
SELECT * FROM emp WHERE sal>8000
#查询工资是8000的或者工资是3000的员工信息
SELECT * FROM emp WHERE
# sal=8000 or sal=3000
sal IN (3000,8000) #作用意义同上

SELECT * FROM emp WHERE
empno IN (100,300,500)

#like 像,模糊查询,通常配合%作为占位符
#查询名字里包含a的员工信息
SELECT * FROM emp WHERE
#ename like '%a%' #包含a,动态匹配0~n个字符
ename LIKE 'l%' #以l开头,动态匹配0~n个字符

#查询mgr是null的员工信息
SELECT * FROM emp 
WHERE mgr IS NULL  #是null
#WHERE mgr IS not NULL #不是null

#查询sal在3000到10000内的员工信息
SELECT * FROM emp WHERE 
#sal > 3000 and sal< 10000
sal BETWEEN 3000 AND 10000 #包含[3000,10000]

#查询2017年到2019年入职的员工信息
SELECT * FROM emp WHERE 
YEAR(hiredate) BETWEEN 2017 AND 2019 #包含

#limit分页
SELECT * FROM emp LIMIT 3 #只查前三条
#第1个数字是开始位置(从n+1条数据开始)第2个是总条数 
SELECT * FROM emp LIMIT 0,3 
SELECT * FROM emp LIMIT 2,2 

#order by 排序
SELECT * FROM emp ORDER BY sal ASC #默认ASC,升序
SELECT * FROM emp ORDER BY sal DESC #倒序
SELECT * FROM emp ORDER BY hiredate #按照日期的数字升序
SELECT * FROM emp ORDER BY ename #a-z升序
SELECT * FROM emp ORDER BY job 
#拿着每个汉字对应的数字(查u8) ,升序排序

四.统计案例

#统计2019年入职的员工
SELECT * FROM emp WHERE 
#year(hiredate)=2019
hiredate>'2019-1-1' AND hiredate<'2019-12-12'
#统计2017年以前入职的员工
SELECT * FROM emp WHERE 
YEAR(hiredate)<2017
#统计2015~2017年入职的员工
SELECT * FROM emp WHERE 
YEAR(hiredate) BETWEEN 2015 AND 2017
#统计员工入职了几年
SELECT *,YEAR(NOW())-YEAR(hiredate) FROM emp