cgb2108-day02

一,作業

–1,創建表

C:\Users\WangYiBo>mysql -uroot -proot
mysql> show databases;
mysql> use cgb210801;
mysql> show tables;
mysql> create table dept(
    -> deptno int,
    -> dname varchar(20),
    -> loc varchar(13)
    -> );
Query OK, 0 rows affected (0.01 sec)
mysql> create table emp(
    -> empno int,
    -> ename varchar(10),
    -> job varchar(10),
    -> mgr int,
    -> hiredate datetime,
    -> sal double,
    -> comm numeric(8,2),
    -> deptno int
    -> );
Query OK, 0 rows affected (0.01 sec)

–2,插入資料

mysql> set names gbk; #防止中文亂碼
mysql> INSERT INTO dept VALUES(10,'accounting','一區');
mysql> INSERT INTO dept VALUES(20,'research','二區');
mysql> INSERT INTO dept VALUES(10,'operations','二區');
mysql> INSERT INTO emp VALUES(100,'jack','副總',NULL,'2002-05-1',90000,NULL,1);
mysql> INSERT INTO emp VALUES(200,'tony','總監',100,'2015-02-02',10000,2000,2);
mysql> INSERT INTO emp VALUES(300,'hana','經理',200,'2017-02-02',8000,1000,2);
mysql> INSERT INTO emp VALUES(400,'leo','員工',300,'2019-02-22',3000,200.12,2);
mysql> INSERT INTO emp VALUES(500,'liu','員工',300,'2019-03-19',3500,200.58,2);

二,使用Sqlyog工具

–1,創建庫

右鍵 – 創建庫 – 輸入資料庫的名字/選字符集utf8 – 創建

–2,創建表

選中Tables – 右鍵 – 新建表 – 填寫欄位名稱/欄位型別/欄位長度 – 創建 – 輸入表名 – 確定

–3,創建記錄

可以自己寫SQL陳述句來執行,也可以直接利用工具在表中雙擊錄入資料并保存

三,欄位約束

–1,主鍵約束

哪個欄位添加了主鍵約束,哪個欄位就是一張表里的唯一的主鍵.
約束了欄位的值,必須唯一且不能為空
主鍵自動遞增策略: 主鍵的值交給了資料庫去管理,資料庫會查到當前記錄中的最大值+1.

CREATE TABLE a(
  id INT PRIMARY KEY 
)
#1.主鍵約束,給欄位添加PRIMARY KEY 
#特點是:欄位值必須唯一 + 值不能為null
INSERT INTO a VALUES(10)#成功
INSERT INTO a VALUES(10)#不成功,因為10已經存在
INSERT INTO a VALUES(NULL)#不成功,不能插入null
#2.主鍵自增策略:是指主鍵的值不需要程式員數,
#交給資料庫自增,給主鍵添加 AUTO_INCREMENT
CREATE TABLE b(
  id INT PRIMARY KEY AUTO_INCREMENT
)

–2,非空約束

哪個欄位添加了非空約束,哪個欄位的值不能為null

#3.非空約束,給欄位添加 not null
#特點是:欄位的值必須不能為null
CREATE TABLE c(
  id INT PRIMARY KEY AUTO_INCREMENT,
  pwd VARCHAR(10) NOT NULL
)
#第一個null是id的值,id是主鍵自動遞增就不用程式員賦值了,
#資料庫會自動+1
INSERT INTO c VALUES(NULL,'123456')#成功,pwd有值
INSERT INTO c VALUES(NULL,NULL)#不成功,pwd不能是null

–3,唯一約束

哪個欄位添加了唯一約束,哪個欄位的值就必須唯一

#4.唯一約束,給欄位添加unique
#特點是:值必須唯一
CREATE TABLE d(
 id INT PRIMARY KEY AUTO_INCREMENT,
 nick VARCHAR(100) UNIQUE
)

四,基礎函式

–1,概述

類似于java中的方法,也有()作為標記,主要也是用來提高SQL的效率
可能有些函式的使用需要傳遞引數
包括: ???

–2,測驗

#查詢部門表中的部門名稱
#select 部門名稱 from 表名 
SELECT * FROM dept
SELECT dname FROM dept #查詢時使用欄位名代替了*
SELECT dname,loc FROM dept #查詢多個欄位的值時用逗號隔開
#基礎函式
#upper(a)把a的值變大寫,lower(a)把a的值變小寫
SELECT dname,UPPER(dname),LOWER('ABC'),LOWER(dname) FROM dept
#length(a)把a的值求長度,一個字母/數字長度為1,一個漢字長度為3(utf8)
SELECT ename,LENGTH(ename),job,LENGTH(job) FROM emp
#substr(a,b,c)-a是欄位名b是截取的開始位置c是截取的長度
SELECT ename,SUBSTR(ename,2),SUBSTR(ename,2,3) FROM emp 
#concat(a,b,c)-a是欄位名b是想要拼接的內容c是想要拼接的內容
SELECT ename,CONCAT(ename,123,'abc') FROM emp
#replace(a,b,c)-a是欄位名b是要被替換的字符c是新資料
SELECT dname,REPLACE(dname,'o','666') FROM dept
#ifnull(a,b)-a是欄位名b是要把null替換成的值
SELECT comm,IFNULL(comm,1000) FROM emp
#對小數的處理:round四舍五入取整 ceil向上取整 floor向下取整
SELECT comm,ROUND(comm),CEIL(comm),FLOOR(comm) FROM emp
#對日期的處理:now獲取當前系統時間year獲取年month獲取月day獲取天
SELECT NOW(),YEAR(NOW()),MONTH(NOW()),DAY(NOW())
#對日期的處理:HOUR獲取時MINUTE獲取分SECOND獲取秒
SELECT NOW(),HOUR(NOW()),MINUTE(NOW()),SECOND(NOW())

五,條件查詢

–1,測驗

#常見的業務中:增刪改查CRUD,其中查詢需求最多
#條件查詢:
#1.distinct把資料去重
SELECT DISTINCT loc FROM dept
#2.where用來引導判斷條件
#select ? from ? where ?
#練習1:查詢部門編號=40的資料
SELECT * FROM dept WHERE deptno=40
SELECT * FROM dept WHERE deptno=40000
#練習2:查詢部門編號>10的資料
SELECT * FROM dept WHERE deptno>10
SELECT * FROM dept WHERE 1=1 #條件永遠成立
#練習3:查詢部門編號>10并且地址在二區的資料
SELECT * FROM dept WHERE deptno>10 AND loc='二區'
#練習4:查詢部門編號>10或者地址在二區的資料
SELECT * FROM dept WHERE deptno>10 OR loc='二區'
#練習5:查詢部門編號=10的或者=20的或者=30的資料
SELECT * FROM dept WHERE 
		deptno=10 OR deptno=20 OR deptno=30
#同上的需求,被簡化--in子句
SELECT * FROM dept WHERE deptno IN(10,30)#查deptno=10 or deptno=30的
SELECT * FROM dept WHERE deptno NOT IN(10,20,30)

#3.like模糊查詢 %是通配符,通配0~n個字符
#練習1:查詢按照員工名字以l開頭員工資訊
SELECT * FROM emp WHERE ename LIKE 'l%' #以l開頭
#練習2:查詢按照員工名字包含o 的員工資訊
SELECT * FROM emp WHERE ename LIKE '%o%' #中間包含o
#練習3:查詢按照員工名字以a結尾 的員工資訊
SELECT * FROM emp WHERE ename LIKE '%a' #以a結尾 

#4.null資料的處理
#練習1:查詢沒有獎金的員工資訊
SELECT * FROM emp WHERE comm IS NULL
#練習2:查詢有獎金的員工資訊
SELECT * FROM emp WHERE comm IS NOT NULL

#5.between...and在區間范圍內的資料
#練習1:查詢工資范圍在5000~10000內的員工資訊
SELECT * FROM emp WHERE sal>5000 AND sal<10000 #(5000,10000)
SELECT * FROM emp WHERE sal BETWEEN 5000 AND 10000 #[5000,10000]