cgb2106-day02

一,欄位約束

–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