一字段约束

1概述

创建的约束：主键约束，非空约束，唯一约束

2主键约束

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

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

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

#主键自增策略
CREATE TABLE mm(id INT PRIMARY KEY AUTO_INCREMENT)#设置主键自增

3非空约束

#非空约束，字段不能是null
CREATE TABLE mm(
id INT PRIMARY KEY AUTO_INCREMENT
PASSWORD VARCHAR(20) NOT NULL)#主键约束
INSERT INTO mm VALUES(NULL,'12345')#设置主键自增的字段，插入数据的时候，可以写空，但是不能不写

4唯一约束

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

二基本函数

1.概述

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

常见的：lower upper length concat substr replace ifnull round/ceil/floor

#只查指定列的值
#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())

条件查询 模糊查询 分页查询 排序

1where 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个字符

2null like 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