Mysql總結

mysql資料型別

1、整數型別（單位：位元組）

2、小數型別

定點型：decimal(m,d)，m是數字長度（整數位+小數位），d是小數位的長度
3、日期的時間型別

4、字串型別

5、二進制型別

管理資料庫結構

#1、創建資料庫
# create database people default character set utf8 collate utf8_general_ci;
#2、洗掉資料庫
# drop database people;
#3、洗掉表
# drop table user;
#4、查看所有資料庫
# show databases;
#5、選擇資料庫
# use people;
#6、創建表
# use people;
# create table user(
#     id int,
#     name varchar(11),
#     score float
# )
#7、洗掉表
# drop table people.user;
#8、查看所有的資料表
# use people;
# show tables ;
#9、查看表結構
# desc people.user;
#10、修改表結構
#11、修改表結構
# （1）新增一個欄位
# alter table user add title varchar(11);
# (2)修改一個欄位
# alter table user change title age int;
# (3)洗掉一個欄位
# alter table user drop age;
# desc user;

常用SQL函式

# 一、日期函式
#1、獲取當前日期
# select current_date;
#2、獲取當前時間
# select current_time;
#3、獲取當前日期+時間
# select now();
#4、計算d1與d2之間相隔的天數(老日期在前，回傳天數為負；新日期在前，回傳天數為正)
# select datediff('2021:02:08','2020:03:08');
#5、獲取d日期100天之后的新日期
# select adddate('2021:02:08',100);
#6、獲取d日期100之前的舊日期
# select subdate('2021:02:08',9200);

# 二、聚合函式
#1、根據某個欄位統計總記錄數，若欄位值設定為null不計算
# select count(score) from user;
# 2、計算某個欄位的總和
# select sum(score) from user;
# 3、計算某個欄位值的平均值
# select avg(score) from user;
# 4、最大值
# select max(score) from user;
# 5、最小值
# select min(score) FROM user;

# 三、分組查詢
# 1、根據名稱、平均分分組
# select name,avg(score) from user group by name;
# 2、根據名稱、平均分分組，平均分降序排序（desc降序、asc升序）
# select name,avg(score) from user group by name  order by avg(score) desc ;
# 3、根據名稱、平均分分組，平均分降序排序，且平均大于70
# select name,avg(score) from user group by name having avg(score)> 70 order by avg(score) desc ;

#四、模糊查詢
# 1、查詢name中包含“大”字的資料
# %大%：包含所有大  %大：以大結尾  大%：以大開頭
# select * from user where name like '%大%'
# 2、"_":限定一個長度去查詢
# __:兩個字查詢 __大：長度為3，以大結尾
# select * from user where name like '__大';

主外鍵

1、主鍵
用來唯一標識某條資料，便于查詢和管理，建議int型別，保證主鍵值不沖突
語法：

create table user(
    # 設定主鍵
    id int primary key auto_increment,
    name varchar(11)
);

2、外鍵
為了構建表與表之間的關系，主外鍵之間的約束關系
3、標語表之間的關系
（1）一對一
例如人類作為一張表，身份證id作為一張表
（2）一對多
學生作為一張表，班級多為一張表
例如：

#班級表，主表
create table class(
    id int primary key auto_increment,
    name varchar(11)
);
#學生表，從表（有外鍵的表）
create table student(
    id int primary key auto_increment,
    name varchar(11),
    cid int,
#     設定外鍵連接
    foreign key (cid) references class(id)
);

（3）多對多
大學選課，學生作為一張表，課程作為一張表
例如：

#用戶表
create table user(
    # 設定主鍵
    id int primary key auto_increment,
    name varchar(11)
);
#課程表
create table course(
    id int primary key auto_increment,
    name varchar(11)
);
# 從表，中間表
create table user_course(
    id int primary key auto_increment,
    uid int,
    cid int,
    foreign key (uid) references user(id),
    foreign key (cid) references course(id)
);

多表關聯查詢

1、嵌套查詢
查詢張三所在班級

select * from class where id = (select cid from student where student.name = '張三');

2、連接查詢
（1）內連接
笛卡爾積
查詢一班中的所有學生

#查詢所有學生
select student.name from student;

#笛卡爾積
select student.name from student inner join class;

#篩選出一班與所有學生做笛卡爾積
select student.name from student inner join class where class.name = '一班';

#最終結果
select student.name from student inner join class WHERE class.name='一班' and class.id=student.id;
#select s.name from student s ,class c where c.name='一班' and c.id=s.id; 

（2）外連接（左連接、右連接）

左連接：左表所有資料和右表滿足條件的資料的笛卡爾積

右連接：右邊所有資料和左表滿足條件的資料的笛卡爾積

3、一對多查詢
查詢王五所在的班級

select student.name,class.name 
from student,class 
where student.name='王五' and class.id=student.cid;

4、多對多查詢

查詢學習java的學生

select  u.name,c.name 
from user u ,course c ,user_course uc  
where u.id=uc.uid and c.id=uc.id and c.name = 'java' ;

查詢張三學習的課程

select  u.name,c.name 
from user u ,course c ,user_course uc  
where u.id=uc.uid and c.id=uc.cid and u.name = '張三' ;

5、去重

select  distinct u.name,c.name from user u ,course c ,user_course uc  where u.id=uc.uid and c.id=uc.cid and u.name = '張三' ;

6、分頁

select * from user limit 0,10;

資料庫索引

1、索引就是指向表中資料的指標，添加索引后能夠快速查詢某條記錄，所有欄位都可以添加索引，與主外鍵一樣，索引類似于查詢內容的目錄
索引的資料結構是B+tree，mysql用page來存盤資料的，將不同的資料存盤到不同的page中，
資料庫底層對于資料的存盤使用的是鏈表的結構，查詢效率低，在此基礎上通過樹狀的結構來達到快速檢索資料的目的，
2、索引分類：
（1）普通索引
（2）唯一索引
（3）全文索引
（4）單列索引
（5）多列索引
（6）空間索引
3、選擇索引
索引會消耗空間以及查詢時間
（1）在where陳述句后面的列添加索引
（2）為了效率，索引的值最好唯一
（3）不要添加過多的索引，這樣維護的成本高，降低資料庫性能
4、添加索引
（法1）alter table 表名 add index 索引名稱(欄位);
（法2）create index 索引名稱 on 表名(欄位);
5、洗掉索引
（法1）alter table 表名 drop index 索引名稱;
（法2）drop index 索引名稱 on 表名;

資料庫事務(transaction)

1、事物：將多條sql陳述句作為一個整體來執行，要么全部成功要么全部失敗，
2、事物的四要素
（1）原子性：多條sql陳述句是個整體不可分割
（2）一致性：sql陳述句執行前后，資料庫的值保持一資
（3）隔離性：事務之間的執行是相互獨立的
（4）永久性：事務一旦提交，資料庫中資料的改變是永久的
3、事務的操作
（1）開啟事務：start transaction;
（2）事務回滾（恢復成執行前的結果）：rollback;
（3）提交事務：commit;
注：mysql是默認自動提交的
show variables like ‘autocommit’; ：查詢是否自動提交
set autocommit 0/1 : 取消/自動

資料庫視圖

視圖是資料庫中虛擬的表，允許不同的用戶以不同的方式查看同一張表的資料
（1）創建視圖
create view 視圖名 as select 欄位 from 表名;
（2）使用視圖
select * from view;
（3）洗掉視圖
drop view 視圖名稱;

資料庫觸發器（trigger）

1、某些資料表中的資料需要進行資料同步的時候使用，觸發器是預先定義一系列的操作，可以在對指定表進行插入、更新、洗掉時自動完成這些操作
2、優點
（1）開發快，trigger存盤在資料庫中，在應用程式中不用手動呼叫觸發器，會自動執行
（2）易維護，訪問目標表會自動呼叫觸發器，實作資料同步
（3）業務全域實作，若修改業務只需要修改觸發器即可，不需要修改業務代碼
3、分類
（1）前觸發器：在更新、插入、洗掉前進行的操作
（2）后觸發器：在更新、插入、洗掉后進行的操作
4、觸發器使用
創建觸發器：
示例1

create trigger afterinsert
	 after insert on tab1 
	 for each row 
	 begin 
	 insert into tab2(tab2_id) 
values(new.tab1_id); 
	end;

示例2

create trigger afterdelete
	after delete on tab1 
	for each row 
	begin 
	delete from tab2 where tab2_id = old.tab1_id; 
end;

洗掉觸發器：

drop trigger afterinsert;