cgb2106-day02

梦里梦外; 2021-09-11 03:16 246阅读 0赞

### 文章目录 ###

*   *  一,字段约束
     *   *  \--1,概述
         *  \--2,主键约束
         *  \--3,非空约束
         *  \--4,唯一约束
     *  二,基本函数
     *   *  \--1，概述
         *  \--2，测试
     *  三,条件查询
     *   *  \--1,where like
         *  \--2,null limit order by等
     *  四,统计案例

## 一,字段约束 ##

### –1,概述 ###

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

### –2,主键约束 ###

表设计的原则是,表里必须有主键.作为每条记录的唯一标识.  
当表里的字段加了主键约束时,效果是,字段的值必须唯一且不能为null  
通常会使用自增策略,是指主键的值从此不必我们设计,交给了数据库来管理

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

### –3,非空约束 ###

#非空约束:字段的值不能是null
    CREATE TABLE c(
    id INT PRIMARY KEY AUTO_INCREMENT,#主键约束
    PASSWORD VARCHAR(20) NOT NULL #非空约束,值不能是null
    )
    #c表里有两个字段,那就必须赋两个值,
    #只是第一个是id的值不必维护
    INSERT INTO c VALUES(NULL,'123456')
    #INSERT INTO c VALUES(NULL,null),第二个必须不为null

### –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

### –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