sql筆記

SQL語句可以單行或者多行書寫,以分號表示結尾

SQL不區分大小寫,關鍵字推薦大寫

註釋

單行註釋: -- 空格 註釋內容 或者 # 註釋內容 (mysql特有)(#號可以沒有空格)

多行註釋: /* 註釋 */

sql中語言的分類:

1. DDL資料定義語言,用來定義資料庫物件,資料庫,表,列等
2. DML資料操作語言,用來對資料庫中表的資料進行增刪改
3. DQL資料查詢語言,用來查詢資料庫中表的記錄(資料)
4. DCL資料控制語言,用來定義資料庫的訪問許可權和安全級別,及建立使用者

登入 mysql -uroot -p密碼

建立資料庫 create database 庫名

顯示有哪些資料庫 show databases

刪除資料庫 drop database 庫名

也可以寫作 drop database if exists 庫名

create database if not exists 庫名

select database() 檢視當前使用的資料庫

use 資料庫名稱 (使用資料庫)

show tables 查詢當前資料庫下所有表的名稱

desc 表名稱 查詢表的結構

建立表

create table 表名 (

欄位名1 資料型別,

欄位名2 資料型別,

欄位名3 資料型別,

..........

欄位名n 資料型別

);

sql中用varchar(n)表示字串,n是規定的最大長度

sql中資料的型別

分類	資料型別	大小	描述
數值型別	tinyint	1 byte	小整數值
數值型別	smallint	2 bytes	大整數值
數值型別	mediumint	3 bytes	大整數值
數值型別	int 或者 integer	4 bytes	大整數值
數值型別	bigint	8 bytes	極大整數值
數值型別	float	4 bytes	單精度浮點數值
數值型別	double	8 bytes	雙精度浮點數值
數值型別	decimal		小數值
日期和時間型別	date	3	日期值
日期和時間型別	time	3	時間值或持續時間
日期和時間型別	year	1	年份值
日期和時間型別	datetime	8	混合日期和時間值
日期和時間型別	timestamp	4	混合日期和時間值,時間數
字串型別	char	0-255 bytes	定長字串
字串型別	varchar	0-65535 bytes	變長字串
字串型別	tinyblob	0-255 bytes	不超過255個字元的二進位制字串
字串型別	tinytext	0-255 bytes	短文字字串
字串型別	blob	0-65535 bytes	二進位制形式的長文字資料
字串型別	text	0-65535 bytes	長文字資料
字串型別	mediumblob	0-16777215 bytes	二進位制形式的中等長度文字資料
字串型別	mediumtext	0-16777215 bytes	中等長度文字資料
字串型別	longblob	0-4294967295 bytes	二進位制形式的極大文字資料
字串型別	longtext	0-4294967295 bytes	極大文字資料

double(a,b) a是指定整數位長度,b是指定小數位長度

刪除表 drop table 表名

drop table if exists 表名

DDL操作表

1. 修改表名

   alter table 表名 rename to 新的表名

2. 新增一列

   alter table 表名 add 列名 資料型別

3. 修改資料型別

   alter table 表名 modify 列名 新資料型別

4. 同時修改列名和資料型別

   alter table 表名 change 列名 新列名 新資料型別

5. 刪除列

   alter table 表名 drop 列名

DML

新增資料

1. 給指定的列新增資料 insert into 表名(列名1,列名2,...) values(值1,值2,...)

2. 給全部列新增資料 insert into 表名 values(值1,值2,...)

3. 批次新增資料 insert into 表名(列名1,列名2) values(值1,值2,...),(值1,值2,...),(值1,值2,...)...

   insert into 表名 values (值1,值2,...),(值1,值2,...),(值1,值2,...)...

給所有列新增資料,列名可以省略

修改資料

1. 修改表資料

   update 表名 set 列名1=值1 ,列名2=值2,... [where 條件]

   UPDATE student set address="beijing" where name = "阿灰";

   如果不加where條件,則表中所有資料都修改

2. 刪除表資料

   delete from 表名 where條件

   DELETE FROM student WHERE id=2;

   如果不加條件會刪除表中所有的記錄

查詢資料

分為基礎查詢 條件查詢(where) 分組查詢(group by) 排序查詢(order by) 分頁查詢(limit)

基礎語法

1. select 欄位列表
2. from 表名列表
3. where 條件列表
4. group by 分組列表'
5. having 分組後條件
6. order by 排序欄位
7. limit 分頁限定

基礎查詢

查詢多個欄位

select 欄位列表 from 表名

select * from 表名 查詢所有資料

去除重複記錄

select distinct 欄位列表 from 表名

起別名

as: as也可以忽略

SELECT name,math AS 數學成績,english AS 英語成績 from student;

模糊查詢: like關鍵字加萬用字元

萬用字元:

_代表單個任意字元

%代表任意個數字符

排序查詢

select 欄位列表 from 表名 order by 排序欄位名1 [排序方式1],排序欄位名2 [排序方式2]...;

排序方式:

asc:升序排列(預設值)

desc:降序排列

注意如果有多個排序條件,當前邊的條件值一樣時,才會根據第二條件進行排序

分組查詢:

聚合函式:

將一列資料作為一個整體進行縱向計算

聚合函式分類:

count(列名) 統計數量(一般選用不為null的列)(注意不是求和,是統計有幾行資料)

取值: 1. 主鍵(非空) 2. *(都統計)

max(列名) 最大值

min(列名) 最小值

sum(列名) 求和

avg(列名) 平均值

聚合函式語法:

select 聚合函式名(列名) from 表名

null 值不參與所有聚合函式的運算

select COUNT(id) from student;

分組查詢:

select 欄位列表 from [where 分組前條件限定] group by 分組欄位名 [having 分組後條件過濾]

SELECT sex , AVG(math) from student GROUP BY sex;

注意: 分組之後,查詢的欄位為聚合函式和分組欄位,查詢其他欄位無任何意義

select sex , AVG(math),COUNT(*) from student where math >70 GROUP BY sex;

select sex,avg(math),count() from student where math > 70 GROUP BY sex having count() > 2

where 和 having 的區別 :

執行時機不一樣,where 是分組之前進行限定的,不滿足where條件,不參與分組,而having是分組之後對結果進行過濾.

可判斷的條件不一樣,where不能對聚合函式進行判斷,having可以

執行順序 where 早於 聚合函式 早於 having

分頁查詢

分頁查詢語法:

select 欄位列表 from 表名 limit 起始索引 , 查詢條目數;

索引是從0開始的

計算公式 : 起始索引 = (當前頁碼數 - 1) \(\times\)每頁顯示的條數

注意點:

分頁查詢 limit 是 mysql資料庫的方言

oracle 分頁使用rownumber

sql server分頁查詢使用top

約束

約束的概念

約束是作用於表中列上的規則,用於限制加入表中的資料

約束的存在保證了資料庫中資料的正確性,有效性和完整性

約束名稱	描述	關鍵字
非空約束	保證列中所有資料不能有null值	not null
唯一約束	保證列中所有資料各不相同	unique
主鍵約束	主鍵是一行資料的唯一標識,要求非空且唯一	primary key
檢查約束	保證列中的值滿足某一條件	check
預設約束	儲存資料時,未指定值則採用預設值	default
外來鍵約束	外來鍵用來讓兩個表的資料之間建立連結,保證資料的一致性和完整性	foreign key
自增長約束	保證在不新增資料時,預設在前一行該資料的基礎上加一	auto_increment

mysql不支援檢查約束

drop table if EXISTS emp;
-- 員工表
create table emp (
id INT PRIMARY KEY, -- 員工id,主鍵且自增長
ename VARCHAR(50) not null UNIQUE, -- 員工姓名,非空且唯一
joindate DATE not null, -- 入職日期,非空
salary DOUBLE(7,2) not null, -- 工資,非空
bonus DOUBLE(7,2) DEFAULT 0 -- 獎金,如果沒有獎金預設為0
);
INSERT INTO emp(id,ename,joindate,salary,bonus) VALUES (1,"張三","1999-11-11",8800,5000);
select * from emp;

預設欄位只有不新增資訊時才能生效,新增null是不能生效的

新增約束可以在建立表的時候,也可以在表建立好之後進行修改

修改或者在建表後補充約束條件

alter table 表名 modify 欄位名 資料型別 約束條件

刪除約束條件

alter table 表名 modify 欄位名 資料型別 (不填約束條件,意味著刪除約束條件)

外來鍵約束

概念

外來鍵用來讓兩個表的資料之間建立連結,保證資料的一致性和完整性

新增約束(建立表時)

create table 表名(

列名 資料型別,

......

[constraint] [外來鍵名稱] foreign key(外來鍵列名) references 主表(主表列名)

);

新增約束(修改或建立表後補充)

alter table 表名 add constraint 外來鍵名稱 foreign key (外來鍵欄位名稱) references 主表名稱(主表列名稱)

刪除約束

alter table 表名 drop foreign key 外來鍵名稱

資料庫設計

表關係之多對多:

如訂單和商品 一個商品對應多個訂單,一個訂單對應多個商品

實現方式: 建立第三個中間表,中間表至少包含兩個外來鍵,分別關聯兩方主鍵

表關係之一對一:

如使用者和使用者詳情

一對一關係用於表拆分,將一個實體中經常使用的欄位放一張表,不經常使用的欄位放另外一張表,用於提升查詢效能

實現方式:

在任意一方加入外來鍵,關聯另外一方的主鍵,並且設定外來鍵為唯一(unique)

表關係之一對多:

在多的一方新增外來鍵來關聯一的一方的主鍵

多表查詢:

笛卡爾積: 取遍A,B集合所有組合情況

多表查詢:從多張表查詢資料

1. 連線查詢:
   1. 內連線:相當於查詢A,B交集的資料
   2. 外連線:
      1. 左外連線:相當於查詢A表所有資料和交集部分資料
      2. 右外連線:相當於查詢B表所有資料和交集部分資料
2. 子查詢

內連線查詢語法:

隱式內連線

select 欄位列表 from 表1,表2... where 條件;

例如

select * from emp , dept where emp.dep_id = dept.did (sql中使用 表名.列名 來在多表關係中指定某個表的某個列)

select emp.name , emp.gender , dept.dname from emp , dept where emp.dep_id = dept.did

為了簡化程式碼可以給每個表起別名(as 只是為了規範,實際書寫可以省略)

select t1.name,t1.gender,t2.dname from emp t1,dept t2 where t1.dep_id = t2.did;

顯式內連線

select 欄位列表 from 表1 [inner] join 表2 on 條件;

select * from emp inner join dept on emp.dep_id = dept.did

(inner 是可以省略的)

select 欄位列表 from 表1 lift [outer] join 表2 on 條件

selext * from emp left join dept on emp.dep_id = dept.did (outer可以省略)

select 欄位列表 from 表1 right [outer] join 表2 on 條件

select * from emp right join dept on emp.dep_id = dept.did

注意點;這裡的left和right其實是相對的,從哪個表查,哪個表就認為是左,另外一個是右

select * from emp 因為是from emp所以emp表是左,後面接的是left就意味著查左邊,也就是emp表的所有資料和交集的所有資料,後面接right就意味著是查對方的所有資料和交集部分的資料,emp表的left就相當於dept表的right,反之同理

子查詢

子查詢概念:

查詢中巢狀查詢,稱巢狀查詢為子查詢

子查詢根據查詢結果不同,作用不同:

• 單行單列
• 多行單列
• 多行多列

select * from emp where salary > (select salary from emp where name = '員工1')

select * from emp where dep_id in (select did from dept where dname = "1" or dname = "2")

select * from (select * from emp where join date > "2011-11-11") t1 ,dept where t1.dep_id = dept.did

(t1相當於是別名)

在一個查詢語句裡巢狀另外一個語句的查詢結果,就是一種子查詢

子查詢根據查詢結果的不同,作用也是不同的:

1. 單行單列的查詢結果可以作為條件值,使用 = != > < 進行判斷

​ select 欄位列表 from 表名 where 欄位名 = (子查詢)

2. 多行單列:作為條件值,使用in等關鍵字進行條件判斷

select 欄位列表 from 表名 where 欄位名 in (欄位名)

3. 多行多列:作為虛擬表

   select 欄位列表 from (子查詢) where 條件

事務

簡介:

資料庫的事務是一種機制,一個操作序列,包含了一組資料庫操作的命令

事務把所有的命令作為一個整體一起向系統提交或者撤銷操作請求,這一組資料庫命令要麼同時成功,要麼同時失敗

事務是一個不可分割的工作邏輯單元

開啟事務,回滾事務,提交事務

事務開啟後,在沒有提交事務前所有的修改不會影響到資料庫的資料,可以預防命令中有異常報錯,導致只執行異常前的命令,後續命令不執行,導致資料異常

用begin開啟事務 或者也可以用 start transaction

rollback回滾事務

事務的所有命令成功執行後,可以檢查資料修改是否正確,如果發現有問題,可以用rollback回到事務執行前的狀態

commit 提交事務,提交後就不能恢復了

事務四大特徵:

原子性:事務是不可分割的最小單位,要麼同時成功,要麼同時失敗

一致性:事務完成時必須使所有資料都保持一致的狀態

隔離性:多個事務之間,操作的可見性

永續性:事務一旦提交或者回滾,他對資料庫中的資料的改變就是永久的

mysql的事務是預設自動提交的,也就是在沒有手動begin一個事務時,不需要進行commit操作

oracle事務預設手動提交,不管有沒有手動開事務,都要commit,寫的命令才能生效