目錄： 資料庫的基本操作 創建、刪除用戶及授權 資料庫字元校對集 創建、刪除資料庫和表 DML操作 DDL操作 索引 事務 一、資料庫的基本操作 二、創建、刪除用戶及授權 三、資料庫字元校對集 字元校對集，即排序規則，在某個字元集的情況下，字元集的排列順序應該是什麼，稱之為校對集。 四、創建、刪除數 ...
目錄：
一、資料庫的基本操作
-- 選擇要操作的資料庫 -- world:資料庫名 use world; -- 顯示已存在的資料庫列表 show databases; -- 顯示指定資料庫下的表的信息 show tables; -- 顯示指定表的列的信息 -- world.country:資料庫名.表名 show columns from world.country; -- 顯示指定表的索引信息 -- world.country:資料庫名.表名 show index from world.country; -- 顯示指定資料庫下的表的詳細信息 -- world:資料庫名 show table status from world; -- 顯示指定資料庫下的表名稱以字母'c'開頭的表的詳細信息 -- world:資料庫名 show table status from world like 'c%'; -- 顯示資料庫表的結構，如：欄位名，欄位類型等 -- world.country:資料庫名.表名 describe world.country; -- 查看當前用戶的資料庫許可權 show grants; -- 查看指定用戶的資料庫許可權 -- admin@localhost:用戶名@訪問主機 show grants for 'admin'@'localhost'; -- 查詢資料庫用戶信息 select * from mysql.user; -- 獲取伺服器版本信息 SELECT VERSION(); -- 獲取當前資料庫名 (或者返回空) SELECT DATABASE(); -- 獲取當前用戶名 SELECT USER(); -- 獲取伺服器狀態 SHOW STATUS; -- 獲取伺服器配置變數 SHOW VARIABLES; 例如： -- 查詢自增長值的步長，即每次增加多少，預設為1。 show variables like '%auto_increment%'; -- 設置自增長值每次增加的數值，會影響所有數據表。 set auto_increment_increment=3; -- 設置自增長值的起始值，會影響所有數據表。 set auto_increment_offset=100; -- 獲取最近一次向具有identity屬性(即自增列)的表插入數據時對應的自增列的值，@@identity是系統定義的全局變數。 select @@identity; -- LAST_INSERT_ID函數將返回當前連接自增列最新的 insert or update 操作生成的第一個記錄的ID。因為其基於Connection的，所以也是線程安全的。 select LAST_INSERT_ID();
二、創建、刪除用戶及授權
-- 創建一個新的用戶，並設置登錄密碼 -- test:用戶名;localhost:本地主機訪問(如果需要其他任意主機訪問，請使用通配符'%');123456:用戶密碼; create user 'test'@'localhost' identified by '123456'; -- 創建一個新的用戶，不指定登錄密碼，即不需要登錄密碼 create user 'test01'@'localhost'; -- 刪除指定的用戶 drop user 'test01'@'localhost'; -- 修改用戶名 -- test@localhost:要修改的用戶名和訪問主機 -- test@%:修改為的用戶名和訪問主機 rename user 'test'@'localhost' to 'test'@'%'; -- 修改用戶密碼 -- test@localhost:要修改的用戶名和訪問主機 -- 123456:新的用戶密碼 set password for 'test'@'localhost' = '123456'; -- 授予指定用戶'test'對於'world'資料庫下'country'表的查詢許可權 -- select:查詢許可權;world.country:資料庫名.表名;'test'@'localhost':用戶名@訪問主機; grant select on world.country to 'test'@'localhost'; -- 立即啟用修改(預設再次登錄才會生效) flush privileges; -- 撤銷指定用戶'test'對於'world'資料庫下'country'表的查詢許可權 -- select:查詢許可權;world.country:資料庫名.表名;'test'@'localhost':用戶名@訪問主機; revoke select on world.country from 'test'@'localhost'; -- 立即啟用修改(預設再次登錄才會生效) flush privileges; -- 授予指定用戶'test'對於'world'資料庫下所有表的查詢、新增、修改、刪除許可權 grant select,insert,update,delete on world.* to 'test'@'localhost'; -- 撤銷指定用戶'test'對於'world'資料庫下所有表的查詢、新增、修改、刪除許可權 revoke select,insert,update,delete on world.* from 'test'@'localhost'; -- 授予指定用戶'test'對於'world'資料庫下所有表的表結構進行創建、修改、刪除許可權 grant create,alter,drop on world.* to 'test'@'localhost'; -- 撤銷指定用戶'test'對於'world'資料庫下所有表的表結構進行創建、修改、刪除許可權 revoke create,alter,drop on world.* from 'test'@'localhost'; -- 授予指定用戶'test'對於'world'資料庫下所有存儲過程的執行許可權，並且該用戶有許可權轉授予其他用戶 grant execute on world.* to 'test'@'localhost' with grant option; -- 撤銷指定用戶'test'對於'world'資料庫下所有存儲過程的執行許可權，轉授予許可權一併撤銷 revoke execute on world.* from 'test'@'localhost';
三、資料庫字元校對集
字元校對集，即排序規則，在某個字元集的情況下，字元集的排列順序應該是什麼，稱之為校對集。
-- 查看所有的字元校對集 -- 尾碼為_bin:表示基於二進位編碼的直接比較 -- 尾碼為_ci:表示對大小寫不敏感的比較 -- 尾碼為_cs:表示對大小寫敏感的比較 show collation;
四、創建、刪除資料庫和表
-- 創建一個名為'test'的資料庫 create database test; -- 創建一個名為'test'的資料庫，如果該資料庫已存在則不創建，否則再創建 -- 並指定預設字元集為'utf8'，字元校對集為'utf8_general_ci' create database if not exists test default charset utf8 collate utf8_general_ci; -- 刪除名為'test'的資料庫 drop database test; -- 創建一個名為'Student'的數據表，如果該數據表已存在則不創建，否則再創建 -- engine:指定資料庫引擎為'InnoDB' -- auto_increment:指定自增列的起始值為1 create table if not exists Student ( ID int not null auto_increment, #自動增長列 StuNo varchar(32) not null, StuName varchar(8) not null, StuSex varchar(8) null, StuBirthday tinyint null, CreateTime datetime null, primary key (ID) #指定主鍵列 ) engine=InnoDB auto_increment=1 default charset=utf8 collate=utf8_general_ci; -- 刪除數據表 student，該操作會刪除所有數據包括表結構、視圖、索引、約束等。 drop table test.student; -- 刪除數據表中的所有數據，該操作會刪除表中所有的數據，但是會保留表結構、視圖、索引、約束等。 truncate table test.student; -- 創建一個臨時表，臨時表的創建與數據表的創建類似，只不過需要添加關鍵字 temporary。 -- 臨時表的作用域為當前會話，即當前連接可見，當斷開當前連接時會自動銷毀，當然也可以手動刪除，刪除方式與數據表一樣。 create temporary table Product ( ProName varchar(32) not null, Price decimal(10,3) not null default 0.000 ); -- 複製指定數據表的表結構到創建的新表。 create table test.StudentBak like test.student; -- 複製指定數據表的表結構及所有數據到創建的新表。 create table test.StudentBak select * from test.student;
五、DML操作
-- 向數據表中插入數據 insert into student(StuNo,StuName,StuSex,Stubirthday,CreateTime) select 'A001','小張','男',str_to_date('1988-06-09','%Y-%m-%d'),current_timestamp() union all select 'A002','小紅','女',str_to_date('1990-08-10','%Y-%m-%d'),current_timestamp() -- 在插入重覆的數據時，會直接跳過重覆數據的插入。在有自增列或主鍵的數據表中不起作用，因為自增列和主鍵都具有唯一性。 insert ignore into test.student(stuno,stuname,stusex,stubirthday,createtime) values ('A003','小魚','女','1991-07-07',current_timestamp()); -- MySQL的WHERE子句預設是不區分大小寫的，如果需要區分大小寫，就要在欄位前加上關鍵字 binary select * from student where stuno='a001'; #'1', 'A001', '小張', '男', '1988-06-09', '2018-01-12 12:17:00' select * from student where binary stuno='a001'; #null -- limit:用於設置返回的記錄數。 -- offset:用於設置select語句開始查詢的數據偏移量，預設為零。 -- 表示只取前10條數據 select * from world.city limit 10; -- 表示躍過5條，從第6條數據開始取10條數據。 select * from world.city limit 10 offset 5; -- 表示從第10條開始取5條數據。 select * from world.city limit 10,5; -- regexp:用於設置正則表達式匹配項，類似於模糊匹配like。 -- 表示查詢名稱以字元 'A'(不區分大小寫)開頭的記錄。 select * from world.city where Name regexp '^A'; -- 表示查詢名稱中包含字元串 'mer' 的記錄。 select * from world.city where Name regexp 'mer'; -- 表示查詢名稱以字元 'a' 或字元 'b' 開頭的記錄或者以字元 'r' 結尾的記錄。 select * from world.city where Name regexp '^[ab]|r$';
六、DDL操作
-- 向指定數據表添加一列，預設添加到數據表欄位的末尾。 alter table test.student add column1 varchar(10) null; -- first關鍵字用於把添加的列設置為第一列。 alter table test.student add column1 varchar(10) null first; -- after關鍵字用於把添加的列設置在指定列的後面，StuSex為指定列的列名。 alter table test.student add column1 varchar(10) null after StuSex; -- 刪除指定列名的列，當數據表僅剩一個欄位時，無法進行刪除。 alter table test.student drop column1; -- 修改指定列的數據類型，並設置該列位於指定列名的列之後。 alter table test.student modify column1 char(10) null after CreateTime; -- 修改指定列的列名和數據類型，並設置該列位於指定列名的列之後。 -- column1:為原列名 -- column2:為新的列名 alter table test.student change column1 column2 varchar(10) null after CreateTime; -- 修改指定列的預設值。 alter table test.student alter column2 set default '123'; -- 刪除指定列的預設值。 alter table test.student alter column2 drop default; -- 修改數據表的存儲引擎。 alter table test.student engine = myisam; alter table test.student engine = InnoDB; -- 修改數據表的自增長值的起始值。 alter table test.student auto_increment=10; -- 重建自增長列，當刪除數據過多，自增長列的值比較混亂時可以使用，但是重建時如果有新的數據插入，有可能會出現混亂。 alter table test.student drop ID; alter table test.student add ID int not null auto_increment first; alter table test.student add primary key(ID); -- 修改數據表的表名稱。 alter table test.student rename to test.StudentBak;
七、索引
-- 查看指定數據表的索引。 show index from test.student; -- 刪除指定的索引。 drop index index_name on test.student; -- 修改表結構的方式刪除索引。 alter table test.student drop index index_name; -- 創建普通索引。 create index index_name on test.student(StuNo); -- 修改表結構的方式添加索引，這種方式可以不指定索引名稱，不指定系統會自動預設一個索引名稱。 alter table test.student add index index_name(StuNo); -- 創建唯一索引，指定創建唯一索引的列的值必須是唯一的，不能重覆，但是可以為null。 create unique index index_name on test.student(StuNo); -- 修改表結構的方式添加唯一索引。 alter table test.student add unique index index_name(StuNo); -- 修改表結構的方式添加主鍵，必須保證添加主鍵的列的值不能為null，並且是唯一的，不可重覆。 alter table test.student add primary key PrimaryKey_Name(ID); -- 刪除指定數據表的主鍵，刪除主鍵時只需指定 primary key，刪除索引時必須指定索引名。 -- 註意：當主鍵列同時是自增長列時，不能直接刪除主鍵，需要先刪除自增長約束。 alter table test.student drop primary key; -- 添加全文索引。 alter table test.student add fulltext index_name(StuNo); -- 加上關鍵字ignore創建的唯一索引和主鍵，在插入重覆數據時，會直接過濾掉重覆數據，並且不會報錯，否則就會拋出錯誤。 alter ignore table test.student add primary key(ID); alter ignore table test.student add unique index index_name(StuNo);
八、事務
-- 關閉自動提交事務 set autocommit=0; -- 開啟自動提交事務，預設為開啟。 set autocommit=1; -- 顯式地開啟一個事務，有以下兩種方法。 start transaction; begin; -- commit用於提交事務，只有當自動提交事務被關閉時需要使用。 commit; -- rollback用於回滾事務，撤銷對於資料庫所做的未提交的操作。 rollback; -- 用於設置一個保存點，identifier是指保存點的名稱。 savepoint identifier; -- 用於刪除一個保存點，如果指定的保存點不存在，將會拋出一個異常。 release savepoint identifier; -- 把事務回滾到指定的保存點。 rollback to identifier; -- 設置事務隔離級別，只對下一個事務有效。 set transaction isolation level {事務隔離級別}; -- 設置事務隔離級別，對當前會話的事務有效。 set session transaction isolation level {事務隔離級別}; -- 設置事務隔離級別，對後面建立MySQL連接的事務有效。 set global transaction isolation level {事務隔離級別}; -- 事務的隔離級別 read uncommitted(讀取未提交): -- 該級別引發的問題是臟讀，會讀取到其他事務未提交的數據。 read committed(讀取已提交): -- 該級別引發的問題是不可重覆讀，即設置為該級別的事務只能讀取到其他事務已經提交的數據，未提交的數據不能讀取，會造成多次查詢的結果不一致。 repeatable read(可重覆讀): -- 該級別引發的問題是幻讀，即當用戶修改某一範圍內的數據行時，另一個事務又在該範圍內插入了新的行，當用戶再次讀取該範圍內的數據時，會發現有新的數據行沒有被修改。 -- 該級別是MySQL資料庫預設的事務隔離級別。註意：該級別不會對事務查詢到的行加行鎖，也就是該事務查詢到的行，其他事務依然能進行修改，但是能保證數據的一致性。 serializable(可串列化): -- 該級別是MySQL中事務隔離級別最高的，該級別會鎖住事務中操作的整張表，因此不會出現以上三個級別的問題。但是這種隔離級別併發性極地，開發中很少會用到。
