1、索引分類 1、普通索引 2、唯一索引 3、主鍵索引 4、外鍵索引2、普通索引(index) 1、使用規則 1、一個表中可以有多個index欄位 2、欄位的值可以有重覆,也可以為NULL值 3、經常把做查詢條件的欄位設置為index欄位 4、index欄位的key標誌為: MUL 2、創建 1、創 ...
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 "\n"
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 "\n";
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 "\n";
# 修改文件許可權 chmod 666 AID1709.csv
7、數據導出
1、作用
將資料庫表中的記錄保存到系統文件里
2、語法格式
select ... from 表名
into outfile "文件名"
fields terminated by "分隔符"
lines terminated by "\n";
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 "\n";
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
