sql語言
1.1sql概述
簡答題：sql 的概念及分類？
Sql 是專門操作資料庫的增刪改查的一種語言，
結構化查詢語言(Structured Query Language)簡稱 SQL，是一種特殊目的的編程語言，是一種資料庫查詢和程式設計語言，用于存取資料以及查詢、更新和管理關系資料庫系統，
Sql 的分類：
1、資料定義語言(DDL Data Definition Language)：針對資料庫或表做創建、 修改和洗掉操作，關鍵字：CREATE,ALTER,DROP 和 SHOW，
2、資料查詢語言(DQL Data Query Language):針對表中資料做查詢操作，關鍵 字：SELECT，
3、資料操作語言(DML Data Manipulation Language):針對表中資料做添加、修改和洗掉操作，關鍵字：INSERT、UPDATE 和 DELETE，
4、資料控制語言(DCL Data Control language)：通過 GRANT 或 REVOKE 關鍵字實作權限控制，
5、事務控制語言（TCL Transcation Cnontrol language）：通過 COMMIT、 SAVEPOINT、ROLLBACK 命令確保被 DML 陳述句影響的表的所有行及時得以更新，
1.2DDL語言
DDL資料定義語言：主要是針對資料庫和資料庫表進行增刪改查的操作，
Create創建 alter修改 drop洗掉 show 查詢
1）資料庫常用命令：
#mysql注釋：
#單行注釋：# –
#多行注釋：/**/
#1、展示所有資料庫
SHOW DATABASES;
#2、創建資料庫:一個連接下面資料庫名不能重復
CREATE DATABASE mysql0131;
#3、查看當前使用的資料庫版本
SELECT VERSION();
#4、切換資料庫和使用資料庫
USE mysql0131;
#5、查看當前資料庫中的所有表
SHOW TABLES;
#6、查看其他資料庫中的表
SHOW TABLES FROM xiaomi1229;
#7、查看當前正在使用的資料庫
SELECT DATABASE();
#8、查看創建資料庫的創建陳述句
#CREATE DATABASE mysql0131 /*!40100 DEFAULT CHARACTER SET utf8 */
SHOW CREATE DATABASE mysql0131;
#9、創建資料庫時指定資料庫的編碼集
CREATE DATABASE mysql0121 DEFAULT CHARACTER SET utf8;
#10、洗掉資料庫
DROP DATABASE mysql0121;
2）資料庫表常用資料型別
整數型別:tinyint、int
浮點型別:float 、double
布爾型別:（MySQL 是沒有 booblean 型別的）
在 MYSQL 中一般布爾型別都用 int 去代替了，1 代表 true，0 代表 false
字串型別:
varchar：（相當java里面的string型別）可變長度型別，無論創建表時指定多大長度，存盤資料的時候會按照資料
的實際長度進行空間占用，設定的最大值目的就是為設定最大范圍，
char：固定長度型別，當存盤資料的長度小于創建表時指定的長度時，則用空格填充，
而不會自動調整，
日期型別：
Date 年月日
Time 時分秒
Datetime（標準日期型別） 年月日時分秒
Timestamp（時間戳）年月日時分秒
Datetime Timestamp 區別：
Datetime ：最大值是 9999 年 12 月 31 23:59:59、默認值 null
Timestamp ：最大值是 2037 年 12 月 31 23:59:59、默認值當前系統時間
3）mysql資料庫表約束：
對資料庫表欄位的一種規范要求
常用的約束：
主鍵：PRIMARY KEY 欄位非空并且唯一
非空：NOT NULL 欄位不能為空
唯一：UNIQUE 欄位值在一個表里面不能重復
默認：DEFAULT 欄位設定的默認值
自增長：AUTO_INCREMENT 整形資料每次自增加1，從1開始
外鍵：forgin key 兩表關聯的關系設定
4）資料庫表的DDL陳述句
/*
mysql資料庫表創建的時候需要設定表中列對應的資料型別
通常我們將表的列稱之為欄位
/
/
創建一個學生表：姓名 年齡 性別 生日
CREATE TABLE 表名(
欄位名稱 欄位資料型別(默認長度) ,
欄位名稱 欄位資料型別(默認長度) ,
…
);
/
#1、創建表的陳述句：
CREATE TABLE students(
sid INT ,
sname VARCHAR(30),
age INT ,
gender INT ,
birthday DATETIME
);
/
mysql資料庫表約束：對資料庫表欄位的一種規范要求
常用的約束：
主鍵：PRIMARY KEY 欄位非空并且唯一
非空：NOT NULL 欄位不能為空
唯一：UNIQUE 欄位值在一個表里面不能重復
默認：DEFAULT 欄位設定的默認值
自增長：AUTO_INCREMENT 整形資料每次自增加1，從1開始
外鍵：forgin key 兩表關聯的關系設定
/
/
創建一個商品表：
編號 主鍵
名稱 不能重復 非空
顏色 不能為空
價格 默認為0
*/
CREATE TABLE products(
pid INT PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(60) NOT NULL UNIQUE,
color VARCHAR(20) NOT NULL,
price DOUBLE DEFAULT 0
);
#2、查看表結構:表欄位名 型別 約束 …資訊
DESC students;
#3、查看創建表的陳述句
/*
CREATE TABLE products (
pid int(11) NOT NULL AUTO_INCREMENT,
pname varchar(60) NOT NULL,
color varchar(20) NOT NULL,
price double DEFAULT ‘0’,
PRIMARY KEY (pid),
UNIQUE KEY pname (pname)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
*/
SHOW CREATE TABLE products;
#4、洗掉表
DROP TABLE students;
#6、給表添加一個欄位
ALTER TABLE products ADD size VARCHAR(20);
#7、修改表欄位的型別及約束
ALTER TABLE products MODIFY size INT;
DESC products;
#8、修改表中的欄位名
ALTER TABLE products CHANGE size model VARCHAR(50);
#9、洗掉表的某個欄位
ALTER TABLE products DROP model;
#10、修改表名稱
ALTER TABLE products RENAME TO prods;
二、DML語言
DML資料庫的操作語言：對資料庫表中的資料進行增加 修改 洗掉的操作，
2.1增加資料：insert
增加的語法：
Insert [into] 表名 (欄位1，欄位2…)value/values(欄位名的值…)
使用insert語法可以對資料庫表增加單潭訓者多條資料，
代碼示例：
準備作業
/*
創建一個員工表：
編號 姓名 崗位 薪資 入職日期 績效獎金 部門編號
*/
CREATE TABLE emp(
eid INT PRIMARY KEY AUTO_INCREMENT,#編號
ename VARCHAR(30) NOT NULL,#姓名
job VARCHAR(30) NOT NULL ,#崗位
salay DOUBLE ,#基本工資
hiredate DATE NOT NULL,#入職日期
money DOUBLE ,#績效獎金
dept_no INT #部門編號
);
增加練習
#增加一條員工資料:完整的語法結構
INSERT INTO emp
(eid,ename,job,salay,hiredate,money,dept_no)
VALUES
(1,“張三”,“銷售員”,12300,“2020-01-01”,2000,1);
#增加員工：姓名 和崗位(沒有非空約束的欄位會自動設定null值)
INSERT INTO emp (ename,job,hiredate)
VALUES(“張三豐”,“經理”,“2019-12-11”);
#增加一條員工資訊：增加所有欄位，欄位名可以省略
#此時所有的欄位都必須按照表結構全部賦值
#主鍵欄位設定為null即可實作自增加
INSERT INTO emp
VALUES(NULL,“趙四”,“文員”,8000,“2018-02-05”,500,2);
#into 可以省略，但是通常不建議省略
INSERT emp
VALUES(NULL,“王百萬”,“老板”,80000,“2015-02-05”,5000,NULL);
#一次增加多條資料
INSERT INTO
emp (ename,job)
VALUE
(“李思琪”,“文員”),
(“王小二”,“銷售員”),
(“光熙”,“保潔”);
#values可以使用value替換
INSERT INTO
emp (ename,job)
VALUES
(“李師師”,“經理”),
(“小二黑”,“銷售員”),
(“許家”,“經理”);
2.2修改資料：update
語法：
Update 表名
set 欄位名1=“欄位值1”，欄位名2=欄位值2…
[where 欄位名=欄位值 and 欄位名=欄位值]
其中：Update修改 set設定 where表示條件 and多個條件的連接
代碼示例：
/*
2、修改員工資料
1)修改表中所有行資料：不需要where 條件
2）修改表中多個欄位資料：多個欄位之間使用，隔開
3）修改資料的時候根據多個條件：多個條件使用and關鍵字連接
*/
#將員工表中所有人的績效設定為2000
UPDATE emp SET money=2000;
#將員工表中所有人的基本工資設定為20000績效為5000
UPDATE emp SET salay=20000,money=5000;
#將4號員工的基本工資修改為50000績效修改為null
UPDATE emp SET salay=50000,money=NULL WHERE eid=4;
#將2號部門的入職日期是2018-02-05的員工的基本工資修改為15000
UPDATE emp SET salay=15000
WHERE dept_no=2 AND hiredate=“2018-02-05”;
2.3洗掉資料：delete
語法：
Delete from 表名 [where 欄位=欄位值 and 欄位名=值]
當沒有where關鍵字的時候是洗掉表中所有資料，where條件表示根據指定條件洗掉資料，
Truncate洗掉：洗掉表中所有的資料
Delete和truncate區別：
1、delete和truncate都可以實作表資料的洗掉
2、Delete可以洗掉單條資料，truncate不可以
3、Delete洗掉表不會洗掉索引，truncate洗掉表資料同時洗掉索引，
注意事項：
在進行資料庫洗掉操作的時候，一定要確認洗掉，謹慎操作！
代碼示例：
/*
3、洗掉員工資料
*/
#洗掉小二黑員工資料
DELETE FROM emp WHERE ename=“小二黑”;
#洗掉基本工資15000績效是5000資料
DELETE FROM emp WHERE salay=15000 AND money=5000;
#洗掉所有資料:洗掉資料但是不會洗掉索引
DELETE FROM emp;
#洗掉所有資料：將索引一起洗掉重建索引
TRUNCATE TABLE emp;
三、DQL語言
DQL資料查詢語言：select
查詢的分類：
1、不帶條件查詢：select 欄位1，欄位2 …from 表名
2、帶條件查詢：select 欄位1，欄位2…from 表名 where 欄位名=值 and 欄位名=值
示例代碼：
/*
1、不帶條件查詢員工表資料
*/
#1、創建emp資料庫表
SHOW CREATE TABLE emp;
CREATE TABLE emp (
eid INT(11) NOT NULL AUTO_INCREMENT,
ename VARCHAR(30) NOT NULL,
job VARCHAR(30) NOT NULL,
salay DOUBLE DEFAULT NULL,
hiredate DATE DEFAULT NULL,
money DOUBLE DEFAULT NULL,
dept_no INT(11) DEFAULT NULL,
PRIMARY KEY (eid)
) ENGINE=INNODB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8
#2、查詢所有員工的姓名
SELECT ename FROM emp;
#3、查詢所有員工的姓名和基本工資
SELECT ename,salay FROM emp;
#4、查詢員工的所有資訊
SELECT eid,ename,job,salay,hiredate,money,dept_no FROM emp;
#當查詢表中所有欄位的時候可以使用替換所有欄位*
#通常在實際開發的時候不建議使用*查詢：查詢速率慢 浪費服務器資源
SELECT * FROM emp;
#5、查詢員工的姓名和工資，展示的時候使用“姓名”“工資”
#使用as給欄位起別名,As可以省略
SELECT ename AS “姓名” ,salay AS “工資” FROM emp;
SELECT ename AS 姓名 ,salay AS 工資 FROM emp;
SELECT ename 姓名 ,salay 工資 FROM emp;
#as可以給欄位或者表起別名
SELECT e.ename ,e.salay FROM emp AS e;
#6、查詢所有員工的部門編號
SELECT dept_no FROM emp;
#7、查詢所有員工的不重復的部門編號
#distinct表示查詢的時候除去欄位重復的值
SELECT DISTINCT dept_no FROM emp;
#8、查詢所有員工的姓名和績效，如果績效為空則用0表示
#ifnull (欄位,替換的值)
SELECT ename 姓名 ,IFNULL(money,0) 績效 FROM emp;
#9、查詢員工姓名及其崗位： 如我是張三，我的崗位是銷售員
SELECT ename,job FROM emp;
#字串拼接concat(str1,str2,str3…)
SELECT CONCAT(“我是：”,ename,",我的崗位是：",job) 個人簡介 FROM emp;
#10、今年業績不錯，每個員工工資sal漲個1000，把漲薪前后的結果顯示出來
#欄位可以直接參與運算操作
SELECT ename 姓名, salay 漲薪前,salay+1000 漲薪后 FROM emp;
#11、查詢每個員工的薪資（基本工資+績效）
SELECT ename 姓名,salay+IFNULL(money ,0) 薪資 FROM emp;
/*
2、帶條件查詢
比較運算子：> < 不等于<> not(3>0)
between 3 and 5 相當于>= <=
邏輯運算： 和and 或or
模糊查詢：like _表示一個字符 %一個或者多個字符
*/
#12、查詢出基本工資在10000~20000之間的員工資訊
SELECT * FROM emp WHERE salay >=10000 AND salay <=20000;
SELECT * FROM emp WHERE salay BETWEEN 10000 AND 20000;
#13、查詢部門編號為1或者2的員工資訊
SELECT * FROM emp WHERE dept_no =1 OR dept_no =2;
SELECT * FROM emp WHERE dept_no IN (1,2);
#14、查詢出部門編號不是2部門的員工資訊
SELECT * FROM emp WHERE dept_no <>2;
SELECT * FROM emp WHERE NOT(dept_no=2);
#15、查詢出績效為null的員工資訊
SELECT * FROM emp WHERE money IS NULL;
#16、查詢出績效不為null的員工資訊
SELECT * FROM emp WHERE money IS NOT NULL;
#17、查詢出名字以王開頭，并且兩個字的員工資訊
SELECT * FROM emp WHERE ename LIKE “王_”;
#18、查詢出名字是三個字的員工資訊
SELECT * FROM emp WHERE ename LIKE “___”;
#19、查詢出名字以王開頭的員工資訊
SELECT * FROM emp WHERE ename LIKE “王%”;
#20、找到職位最后是以"員"結尾的員工資訊
SELECT * FROM emp WHERE job LIKE “%員”;
#21、查詢出名字里面帶小的員工資訊
SELECT * FROM emp WHERE ename LIKE “%小%”;
四、高級查詢
1、排序查詢
/*
1、排序查詢：
語法： select 查詢欄位 from 表名 order by 欄位 asc /desc
asc表示升序：從小到大排序,默認值asc可以省略
desc表示降序：從大到小排序
注意事項：
當排序的欄位是多個欄位的時候：
先根據第一個欄位進行排序，當第一個欄位值相等的時候，
再按照第二個欄位排序，當第二個欄位值相等的時候再按照
第三個欄位排序
以此類推
/
#按斬訓本工資降序顯示員工資訊
SELECT * FROM emp ORDER BY salay DESC;
#按照部門編號升序顯示員工資訊
SELECT * FROM emp ORDER BY dept_no ;
#按照部門編號升序，再按照入職日期降序，最后按照薪資降序排列，顯示員工資訊
SELECT * FROM emp ORDER BY dept_no ASC,hiredate DESC,salay DESC;
2、聚合查詢
/
2、聚合查詢
mysql常用的聚合函式：
max()查詢最大值
min()查詢最小值
sum()查詢欄位值的總和
avg()查詢欄位的值平均值
count()查詢表資料總條數
*/
#求出員工的最高薪資
SELECT MAX(salay) FROM emp;
#求最低薪資
SELECT MIN(salay) FROM emp;
#求最高薪資的員工資訊:聚合查詢的結果可以作為一個變數值使用
SELECT * FROM emp WHERE salay =(SELECT MAX(salay) FROM emp);
#求員工的平均薪資
SELECT AVG(salay) FROM emp;
#查詢大于平均薪資的員工資訊
SELECT * FROM emp WHERE salay >(SELECT AVG(salay) FROM emp);
#求員工的薪資總和
SELECT SUM(salay) FROM emp;
#求員工的數量:資料的總行數
SELECT COUNT(eid) FROM emp;
SELECT COUNT() FROM emp;
SELECT COUNT(1) FROM emp;
3、分組查詢
/
3、分組查詢：
select 欄位 ，聚合查詢 from 表名 group by 欄位 [having 聚合查詢] ;
having和wehere類似表示分組查詢的條件
分組查詢使用場景：涉及 到每個 每組 每日
/
#統計每個部門下的員工數量
SELECT dept_no, COUNT() FROM emp GROUP BY dept_no ;
#統計每個崗位下的員工平均薪資
SELECT job,AVG(salay) FROM emp GROUP BY job;
#統計每個部門的最高薪資
SELECT dept_no,MAX(salay) FROM emp GROUP BY dept_no;
#查詢每個部門中薪資大于10000的員工人數
SELECT dept_no,COUNT(*) FROM emp
WHERE salay >10000 GROUP BY dept_no;
#查詢出部門人數大于2的部門及其人數，并按照人數降序排列
SELECT dept_no,COUNT() FROM emp
GROUP BY dept_no HAVING COUNT()>2
ORDER BY COUNT(*) DESC;
#查詢每個部門下最高薪資大于10000且名字中含有小的員工資訊
SELECT * FROM emp
WHERE ename LIKE “%小%”
GROUP BY dept_no HAVING MAX(salay)>10000;
4、分頁查詢
/*
4、分頁查詢
select 欄位 from 表名 limit index,size;
引數說明：
index 查詢表資料的起始索引值，索引從0開始
size表示分頁查詢的資料條數
注意：
其中index可以省略，此時表示查詢第一頁資料，也就是從索引為0開始查詢，
*/
#將我們的員工表資料進行分頁查詢，設定每頁顯示三條
#查詢一頁資料每頁顯示三條
SELECT * FROM emp LIMIT 0,3;
#查詢第二頁資料
SELECT * FROM emp LIMIT 3,3;
#查詢第三頁資料
SELECT * FROM emp LIMIT 6,3;
#limit 的引數可以省略前面的第一個引數
#查詢薪資的前三名員工資訊
SELECT * FROM emp ORDER BY salay DESC LIMIT 3;
#查詢公司入職最早的員工資訊
#如果沒有重復資料可以使用limit求最值
SELECT * FROM emp ORDER BY hiredate LIMIT 1;
#如果表中有重復資料使用聚合查詢實作
SELECT * FROM emp WHERE hiredate=(SELECT MIN(hiredate) FROM emp);
5、子查詢
/*
5、子查詢
將查詢的結果作為一個條件使用的操作
涉及的關鍵字：
all 所有
any 任意
*/
#1、查詢薪資大于平均薪資的員工資訊
SELECT * FROM emp WHERE salay >(SELECT AVG(salay) FROM emp);
#2、查詢大于0號部門所有員工的薪資的員工資訊
#第一種方式
SELECT * FROM emp WHERE salay >
(SELECT MAX(salay) FROM emp WHERE dept_no =0);
#第二種方式
SELECT * FROM emp WHERE salay >
ALL(SELECT salay FROM emp WHERE dept_no =0);
#3、查詢大于任意一位經理工資的員工資訊
SELECT * FROM emp WHERE salay >
(SELECT MIN(salay) FROM emp WHERE job=“經理”);
SELECT * FROM emp WHERE salay >
ANY (SELECT salay FROM emp WHERE job=“經理”);
#4、查詢與王百萬崗位和薪資一樣的員工資訊
SELECT * FROM emp
WHERE job=(SELECT job FROM emp WHERE ename=“王百萬”)
AND salay =(SELECT salay FROM emp WHERE ename=“王百萬”)
AND ename <> “王百萬”;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/267107.html
標籤：其他
上一篇：Mybatis的基礎學習