1、索引分類
1、普通索引
2、唯一索引
3、主鍵索引
4、外來鍵索引
2、普通索引(index)
1、使用規則
1、一個表中可以有多個index欄位
2、欄位的值可以有重複,也可以為NULL值
3、經常把做查詢條件的欄位設定為index欄位
4、index欄位的key標誌為: MUL
2、建立
1、建立表時建立index
create table t1(
... ...,
... ...,
index(id),
index(name));
2、在已有表中新增索引欄位
1、語法格式
create index 索引名 on 表名(欄位名);
# 索引名一般和欄位名一樣
3、檢視
1、desc 表名; ->檢視KEY標誌為 MUL
2、show index from 表名G;
4、刪除
drop index 索引名 on 表名;
注意：
刪除普通索引只能一個一個刪除
3、唯一索引(unique)
1、使用規則
1、一個表中可以有多個 unique 欄位
2、unique欄位的值不允許重複,可以為空值NULL
3、unique的KEY標誌是 UNI
2、建立(基本等同index建立)
1、建立表時建立
unique(欄位名),
unique(欄位名)
2、已有表中建立
create unique index 索引名 on 表名(欄位名);
3、檢視、刪除唯一索引
desc 表名;
show index from 表名;
drop index 索引名 on 表名;
4、主鍵索引(primary key) && 自增長屬性(auto_increment)
1、使用規則
1、一個表中只能有一個主鍵欄位
2、對應欄位的值不允許重複 且 不能為空值NULL
3、主鍵欄位的KEY標誌為 PRI
4、把表中能夠唯一標識一條記錄的欄位設定為主鍵,通常把表中記錄編號的欄位設定為主鍵
2、建立主鍵(PRI)
1、建立表時建立
1、欄位名 資料型別 primary key auto_increment,
2、
id int auto_increment,
... ..., # 設定自增長起始值
primary key(id))auto_increment=10000;
2、刪除主鍵
1、先刪除自增長屬性(modify)
alter table 表名 modify id int;
2、刪除主鍵
alter table 表名 drop primary key;
3、在已有表中新增主鍵
alter table 表名 add primary key(欄位名);
5、外來鍵
1、定義
讓當前表欄位的值在另一個表的範圍內選擇
2、語法格式
foreign key(參考欄位名)
references 被參考表名(被參考欄位名)
on delete 級聯動作
on update 級聯動作
3、案例
表1、繳費資訊表(財務)
學號 姓名 班級 繳費金額
1 唐伯虎 AID01 28000
2 點秋香 AID01 20000
表2、學生資訊表(班主任)
學號 姓名 繳費金額
1 唐伯虎 28000
2 點秋香 20000
1、建立繳費資訊表
create table jftab(
id int primary key,
name char(15),
class char(5),
money int
)default charset=utf8;
insert into jftab values
(1,"唐伯虎","AID01",28000),
(2,"點秋香","AID01",20000),
(3,"祝枝山","AID01",22000);
2、建立學生資訊表(從表)
create table bjtab(
stu_id int,
name char(15),
money int,
foreign key(stu_id) references jftab(id)
on delete cascade
on update cascade
);
4、級聯動作
1、cascade ：資料級聯更新
當主表刪除記錄 或者 更新被參考欄位的值時,從表會級聯更新
2、restrict 預設
1、當刪除主表記錄時,如果從表中有相關聯記錄則不允許主表刪除
2、更新同理
3、set null
1、當主表刪除記錄時,從表中相關聯記錄的參考欄位值自動設定為NULL
2、更新同理
4、no action
on delete no action on update no action
同 restrict,都是立即檢查外來鍵限制
5、刪除外來鍵
alter table 表名 drop foreign key 外來鍵名;
1、外來鍵名的檢視方式
show create table 表名;
6、已有表中新增外來鍵
## 會受到表中原有資料的限制
alter table 表名 add foreign key(參考欄位名)
references 被參考表名(被參考欄位名)
on delete 級聯動作
on update 級聯動作;
7、外來鍵使用規則
1、兩張表被參考欄位和參考欄位資料型別要一致
2、被參考欄位必須是 key 的一種,通常是 primary key
6、資料匯入
1、作用：把檔案系統的內容匯入到資料庫中
2、語法
load data infile "檔名"
into table 表名
fields terminated by "分隔符"
lines terminated by "
"
3、示例
把 /etc/passwd 檔案中的內容匯入到db2庫下的userinfo表
tarena : x : 1000 : 1000 :
使用者名稱 密碼 UID號 GID號
tarena,,, : /home/tarena : /bin/bash
使用者描述 使用者主目錄 登入許可權
/bin/false
/usr/sbin/nologin
4、操作步驟
1、在資料庫中建立對應的表
create table userinfo(
username char(20),
password char(1),
uid int,
gid int,
comment varchar(50),
homedir varchar(50),
shell varchar(50)
);
2、將要匯入的檔案拷貝到資料庫的預設搜尋路徑中
1、檢視資料庫的預設搜尋路徑
show variables like "secure_file_priv";
/var/lib/mysql-files
2、Linux命令列輸入：
sudo cp /etc/passwd /var/lib/mysql-files/
3、執行資料匯入語句
load data infile "/var/lib/mysql-files/passwd"
into table userinfo
fields terminated by ":"
lines terminated by "
";
5、練習：將AID1709.csv檔案匯入到資料庫中
# csv檔案單元格之間以 , 分隔
/var/lib/mysql-files/AID1709.csv
ls -l AID1709.csv
rw-------
chmod 666 AID1709.csv
1、在資料庫中建立對應的表
id 姓名 成績 手機號 班級
create table scoretab(
id int,
name varchar(20),
score float(5,2),
phone char(11),
class char(7)
)default charset=utf8;
2、把匯入的檔案複製到資料庫的預設搜尋路徑中
cp 原始檔 目標路徑
cp /home/tarena/AID1709.csv /var/lib/mysql-flies/
######## 用 TAB 鍵 補齊路徑 #######
3、執行資料匯入語句
load data infile "/var/lib/mysql-files/AID1709.csv"
into table scoretab
fields terminated by ","
lines terminated by "
";
# 修改檔案許可權 chmod 666 AID1709.csv
7、資料匯出
1、作用
將資料庫表中的記錄儲存到系統檔案裡
2、語法格式
select ... from 表名
into outfile "檔名"
fields terminated by "分隔符"
lines terminated by "
";
3、把userinfo表中的username、password和uid匯出到檔案user.txt
select username,password,uid from userinfo
into outfile "/var/lib/mysql-files/user.txt"
fields terminated by ","
lines terminated by "
";
1、sudo -i
2、cd /var/lib/mysql-files/
3、cat user.txt
4、注意
1、匯出的內容由SQL查詢語句決定
2、執行匯出命令時路徑必須指定對應的資料庫搜尋路徑
8、表的複製
1、語法格式
create table 表名 select 查詢命令;
2、示例
1、複製userinfo表中的全部記錄,userinfo2
create table userinfo2 select * from userinfo;
2、複製userinfo表中username,password,uid三個欄位的第2-10條記錄,userinfo3
create table userinfo3 select username,password,uid from userinfo limit 1,9;
3、複製表結構
create table 表名 select 查詢命令 where false;
4、注意
複製表的時候不會把原有表的 key 屬性複製過來
9、巢狀查詢(子查詢)
1、定義
把內層的查詢結果作為外層的查詢條件
2、示例
1、把uid的值小於 uid 平均值的使用者名稱和uid號顯示出來
select username,uid from userinfo
where uid < (select avg(uid) from userinfo);
10、連線查詢
1、內連線
1、定義
從表中刪除與其他被連線的表中沒有匹配到的行
2、語法格式
select 欄位名列表 from 表1
inner join 表2 on 條件 inner join 表3 on 條件;
3、示例
1、顯示省市的詳細資訊
select sheng.s_name,city.c_name from sheng
inner join city on sheng.s_id=city.cfather_id;
2、顯示省市縣詳細資訊
select sheng.s_name,city.c_name,xian.x_name from sheng
inner join city on sheng.s_id=city.cfather_id
inner join xian on city.c_id=xian.xfather_id;
2、外連線
1、左連線
1、定義
以左表為主顯示查詢結果
2、語法格式
select 欄位名列表 from 表1
left join 表2 on 條件;
3、示例
1、以省表為主顯示省市詳細資訊
select sheng.s_name,city.c_name from sheng
left join city on sheng.s_id=city.cfather_id;
2、顯示省市區詳細資訊,要求縣全部顯示
select sheng.s_name,city.c_name,xian.x_name from sheng left join city
on sheng.s_id=city.cfather_id
right join xian on city.c_id=xian.xfather_id;
3、顯示省市區詳細資訊,要求 市 全部顯示
select sheng.s_name,city.c_name,xian.x_name from sheng
right join city on sheng.s_id=city.cfather_id
left join xian on city.c_id=xian.xfather_id;
#### 結果集 ####
2、右連線
用法同左連線,以右表為主顯示查詢結果
11、多表查詢
1、select 欄位名列表 from 表名列表; # 笛卡爾積
2、select 欄位名列表 from 表名列表 where 條件;
等同於 內連線 inner join