MYSQL學習

安裝：sudo apt install mysql-server

https://blog.csdn.net/qq_38505969/article/details/109957055

mysql -h 主機名 -p端口 -u 用戶名 -p密碼（注意p后面無空格）

或mysql -u 用戶名 -p密碼

或mysql -u 用戶名 -p

-u：用戶名，-p：密碼

退出：exit、quit、ctrl+d

查看mysql進程：ps -aux | grep mysql

查看mysql服務狀態：sudo service mysql status

停止mysql服務：sudo service mysql stop

啟動mysql服務：sudo service mysql start

重啟mysql服務：sudo service mysql restart

mysql配置文件路徑：/etc/mysql/mysql.conf.d/mysqld.cnf

查看當前使用的數據庫：select database();

刪除數據庫：drop database 庫名;

查看數據庫創建信息：show create database 庫名;

修改數據庫字符集：alter database 庫名 charset=utf8;

簡單創建數據表：create table user(id int,name char(7),age tinyint);

創建標準的數據表：create table person(id int(5) unsigned zerofill primary key auto_increment,name char(5) not null,height float(5,2) default 0.0,gender enum(‘男’,‘女’) default ‘男’);

查看表結構：desc 表名;

查看創建表的信息：show creat table 表名;

為表添加字段：alter table 表名 add weight float(5,2) not null;

為表修改類型：alter table 表名 modify weight int(5);

修改表的字段名和約束：alter table 表名 change weight tizhong float(5,3) default 99.99;

當字段類型發生變化時，如果原字段中有數據，那么數字字符串和數字之間可以轉換

可同時修改多個字段和約束，中間用逗號分隔。

刪除表字段 alter table 表名 drop tizhong;

刪除表：drop table 表名;

查詢：

? select * from person;

插入：

? insert into 表名 values(1,‘tom’,default,male)，(2,‘Jack’,178,male); 如果不想提供數據，也要使用default占位

? insert into 表名 (name) values(‘Lucy’),(‘Tom’) ;

? insert into 表名 (id,name,gender) values(5,‘Bulke’,1);

更新數據：

? update 表名 set 字段名=值;

? update 表名 set 字段名=值 where 條件;

刪除數據：

? delete from 表名;

? delete from 表名 where 條件;

別名：

? select id as 序號,name as 姓名，gender as 性別 from person;(as 可以省略)

數據去重：

? select distinct name from person;

模糊查詢：like，%(0或多個字符)，_(一個字符)，

? select * from person where name like ‘孫%’;

? select * from person where name like ‘孫_’;

? select * from person where name like ‘%孫%’;

范圍查詢：between…and…(連續范圍內)，in(非連續的范圍)

? select * from person where height between 170 and 180;

? select * from person where id in (1,7,9);

空判斷：is null，is not null

? select * from person where height is null;

? 注意：where height is not null;和where not height is null;結果一樣但是執行邏輯不一樣，后者是先把空數據取出來再反選。

排序：order by

? select * from 表名 order by 列名 asc：升序排列，不寫asc默認升序

? select * from 表名 order by 列名 desc：降序排列

? select * from 表名 order by 列1 desc,列2 asc：當列一中出現相同值情況無法排序時使用列二排序

? where 條件 要寫在order by 的前面

分頁查詢：limit，start——索引起始位置，count——條數

? select * from 表名 limit start,count 從start+1開始讀取count條數據，start不寫默認為0，count不夠時有多少就顯示多少

? select * from student limit (n-1)*m,m 獲取第n頁數據

? select * from person where gender=‘女’ order by gender desc limit 5; 查詢性別為女的年齡最大的五個人

? where 條件——order by——limit

聚合函數：用來對數據進行統計和計算

? count(col)：求指定列的總行數(不統計空值的行)、max(col)、min(col)、sum(col)、avg(col)：求指定列的平均值

? round()，指定保留小數點的位數，如round(avg(height),2)

? select count(height),max(height),min(height),sum(height),avg(height) from person;

? 一般都是使用count(*)，可以將空值也統計進去

ifnull(height,0)：如果height為空，則用0代替，可與聚合函數連用，如count(ifnull(height,0));

分組查詢：group by

? select gender from person group by gender; 通過什么分組就查詢什么——select A from 表名 group by A，此方法只能看到分組的類別，看不到各組的成員情況

? group by 與group_concat()連用可以查看各組成員情況：select gender ,group_concat(name) from person group by gender;

? having過濾分組數據（having只能用于group by）：select gender,group_concat(name) from person where age>50 group by gender having gender=‘女’; 顯示年齡大于50的女生成員姓名

? 與聚合函數連用：select gender,count(age),max(age),min(age),sum(age),avg(age) from person where age is not null group by gender;

? with rollup：select gender,count(age),max(age),min(age),sum(age),avg(age) from person where age is not null group by gender with rollup; 在最后記錄后面新增一行，顯示select查詢時聚合函數的統計和計算的結果

內連接、左連接、右連接、自連接查詢時where必須寫到連接語句后面

內連接：inner join on 根據on后面的條件取兩個表的“交集”，on可省略

? select * from student inner join class; 這樣的話查詢出來的條數是student的條數于class條數之積
? select * from student inner join class on student.class_id=class.id; on 后跟條件

? select s.id,s.name,c.class_name class from student as s inner join class c on s.class_id=c.id; as可以省略

左外連接：left join on 以左表為主，根據條件查詢右表數據，如果右表數據不存在則以null填充，on不可省略

? select 字段 from 左表 left join 右表 on 左表.字段1=右表.字段2;

右外連接：right join on 以右表為主，根據條件查詢左表數據，如果左表數據不存在則以null填充，on不可省略

? select 字段 from 左表 right join 右表 on 左表.字段1=右表.字段2;

? 將左表和右表互換一下，right和left替換一下，可實現左連接查詢的結果和右連接查詢的結果相同

自連接：左表和右表是同一張表，內連接、左連接、右連接都可以實現自連接

? select p.name 省名 c.name 市名 from areas p inner join areas c on p.id=c.pid; pid表示改地名的上級地名id

子查詢：子查詢是一條完整的select語句，嵌入到主查詢中

? select * from student where age > (select avg(age) from student); 查詢年齡大于平均值的學生

? select name from class where id in (select distinct class_id from student where class_id is not null); 查詢學生都歸屬于哪些班級

? select * from student where (age,height) = (select max(age),max(height) from student); 查找年齡最大且身高最高的學生

外鍵約束：foreign key(col) reference

? 對外鍵字段的值進行更新和插入時，會引用表中的字段進行數據驗證，如果數據不合法則會失敗，保證數據的有效性

? 一個表可以有多個外鍵

? 創建表時直接寫入：create table Test(id int(5) not null primary key auto_increment,字段1 char(5),foreign key(字段1) references 表2(地段2)); 字段1和字段2類型要一致，被參考的字段，即字段2一定是表2的主鍵

? 注意：定義外鍵后，要是想刪除被參考的那張表或者表中數據，需要先把定義外鍵的表或者表中數據刪除，就好比如果想解散一家公司，要先解散員工。或者先把外鍵約束刪除了。

? 刪除外鍵約束：

? 1.獲取外鍵名稱（系統自動生成）：show create table 表名; 查看外鍵名

? 2.刪除外鍵約束：alter table 表名 drop foreign key 外鍵名;

? 添加外鍵約束：如果字段1中存在無法與字段2匹配的數據，則添加外鍵會失敗。

? alter table 表名 add constraint fk_class_id foreign key(字段1) references 表2(字段2); fk_class_id為外鍵名，constraint fk_class_id可以省略，省略后系統自動生成外鍵名，外鍵名一般為fk_+字段名

將查詢結果作為數據插入到另一張表：

? insert into … select…; 不需要寫values()

使用連接更新表中數據：

? update 表1 inner/left/right join 表2 on…set…;

建表時通過查詢加入數據：create table…select…

? create table 表名(id int primary key auto_increment,name char(10) not null) select name from 表名; 或者select 字段名 as name from 表名。id可以省略不寫，默認從第二個字段開始匹配，注意：select后跟的字段名必須和創建表時添加的字段名相同。

事務：

? 事務是用戶定義的一系列執行SQL語句的操作（增、刪、改），在一個事務中的所有操作要么完全執行，要么完全不執行（執行到一半意外退出則會發生回滾），它是一個不可分割的工作執行單元。

? 開啟事務后執行修改指令，變更的數據會保存到mysql服務端的緩存文件中，而不維護到物理表中，只有執行commit之后才會將本地緩存文件中的數據提交到原物理表中，完成數據的更新。

? 注意：如果沒有顯示的開啟一個事務，每條sql語句都會被當做一個事務，并且由于mysql默認采用自動提交模式(autocommit)，所以會自動執行提交事務的操作。

? set autocommit=0; 表示取消自動提交事務模式，需要手動commit完成事務的提交（當開啟一個事務后，會自動設置autocommit值為0）。

? 顯示的執行commit或rollback表示該事務的結束

? 四大特性：

? 原子性(Atomicity)：強調一個事務中的多個操作是一個整體

? 一致性(Consistency)：強調數據庫中保存一致的狀態，例如ATM取錢時你取多少錢，銀行卡中余額就要減少多少

? 隔離性(Isolation)：強調數據庫中的多個事務之間的操作互不可見

? 持久性(Durability)：強調數據庫能永久保存數據，一旦提交就不可撤銷

? 只有InnoDB引擎可使用事務，常用的表的存儲引擎是InnoDB和MyISAM，MyISAM不支持事務，優勢是訪問速度快。

? 修改表的存儲引擎：alter table 表名 engine=‘MyISAM’;

事務的操作：

? sql語句：

? 開啟事務begin;或start transaction;

? 提交事務commit;

? 回滾事務rollback;

? 提交或回滾事務后，當前事務會結束

pymysql：

? 連接數據庫對象.cursor() 創建游標對象，會自動隱式的開啟一個事務

? 提交事務：連接數據庫對象.commit()

? 回滾事務：連接數據庫對象.rollback()

索引：

? 在mysql中也叫做”鍵“，是一個特殊的文件，保存著數據表里所有記錄的位置信息，就像一本書的目錄，能加快數據庫的查詢速度。

? show index from 表名; 查看索引（主鍵列會自動創建索引）

? alter table test_index add index idx_title(title); 創建索引（非常耗費時間），非唯一性索引的名稱一般設置為idx_+字段名，不寫默認是字段名，添加索引后，使用where查詢數據時只需要對比一次。

? 在查詢語句前面加desc可以通過查看rows屬性值來查看對比次數，Extra屬性值查看查詢方法

? alter table test_index drop index idx_title; 刪除索引

? set profiling=1; 開啟時間監測

? show profiles; 查看時間監測的記錄數據

聯合索引（復合索引）：一個索引覆蓋表中兩個或者多個字段，一般用在多個字段一起查詢的時候

? alter table 表名 add index [索引名] (字段1,字段2,…);

? 最左原則：如果給(字段1,字段2,字段3)創建聯合索引，那么同時會給(字段1)、(字段1,字段2)創建索引

? 好處是減少了磁盤空間的開銷，缺點是耗費時間長

索引的使用原則：

? 1.對經常更新的表避免創建較多的索引，因為更新索引非常耗時，對經常查詢的字段應該創建索引

? 2.數據量小的表最好不要創建索引

? 3.在同一字段相同值比較多時不要建立索引，例如“性別”字段

? 4.索引并不是越多越好，要在查詢速度和建立索引所占用的時間和空間之間綜合權衡

?

?

?

?

總結

以上是生活随笔為你收集整理的MySQL知识点复习的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。