create database 數據庫名稱;
create database if not exists 數據庫名稱;
create database 數據庫名稱 default charset utf8mb4;
create database 數據庫名稱 collate 排序規則;
drop database 數據庫名稱;
drop database if not exists 數據庫名稱;
use 數據庫名稱;
select database ( ) ;
show tables ;
DESC 表名;
show create table 表名;
create table user_table( id int comment '編號' , name varchar ( 50 ) comment '姓名' , age int comment '年齡' , gender varchar ( 1 ) comment '性別'
) comment '用戶表' ;
分類 類型 大小 有符號(SIGNED)范圍 無符號(UNSIGNED)范圍 描述 數值類型 TINYINT 1 byte (-128, 127) (0, 255) 小整數值 SMALLINT 2 bytes (-32768, 32767) (0, 65535) 大整數值 MEDIUMINT 3 bytes (-8388608, 8388607) (0, 16777215) 大整數值 INT或INTEGER 4 bytes (-2147483648, 2147483647) (0, 4294967295) 大整數值 BIGINT 8 bytes (-2^63, 2^63?1) (0, 2^64?1) 極大整數值 FLOAT 4 bytes (-3.402823466E+38, 3.402823466E+38) 0 和 (1.175494351E?38, 3.402823466E+38) 單精度浮點數值 DOUBLE 8 bytes (-1.7976931348623157E+308, 1.7976931348623157E+308) 0 和 (2.2250738585072014E?308, 1.7976931348623157E+308) 雙精度浮點數值 DECIMAL 依賴于M(精度)和D(標度)的值 依賴于M(精度)和D(標度)的值 小數值（精確定點數）
分類 類型 大小 描述 字符串類型 CHAR 0–255 bytes 定長字符串 VARCHAR 0–65535 bytes 變長字符串 TINYBLOB 0–255 bytes 不超過255個字符的二進制數據 TINYTEXT 0–255 bytes 短文本字符串 BLOB 0–65535 bytes 二進制形式的長文本數據 TEXT 0–65535 bytes 長文本數據 MEDIUMBLOB 0–16777215 bytes 二進制形式的中等長度文本數據 MEDIUMTEXT 0–16777215 bytes 中等長度文本數據 LONGBLOB 0–4294967295 bytes 二進制形式的極大文本數據 LONGTEXT 0–4294967295 bytes 極大文本數據
分類 類型 大小 范圍 格式 描述 日期類型 DATE 3 1000-01-01 至 9999-12-31 YYYY-MM-DD 日期值 TIME 3 -838:59:59 至 838:59:59 HH:MM:SS 時間值或持續時間 YEAR 1 1901 至 2155 YYYY 年份值 DATETIME 8 1000-01-01 00:00:00 至 9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和時間值 TIMESTAMP 4 1970-01-01 00:00:01 至 2038-01-19 03:14:07 YYYY-MM-DD HH:MM:SS 混合日期和時間值，時間戳
示例：
age tinyint unsigned ;
score double ( 4 , 1 ) ;
name char ( 10 ) ;
name varchar ( 10 ) ;
alter 表名 add 字段名 類型;
alter table 表名 modify 字段名 新數據類型;
alter table 表名 change 舊字段名 新字段名 類型;
alter table 表名 drop 字段;
alter table 表名 rename to 新表名;
drop table 表名;
truncate table 表名;
insert into 表名( 字段名1 , 字段名2 , ……) values ( 值1 ，值2 , ……) ;
insert into 表名 values ( 值1 ，值2 , ……) ;
insert into 表名( 字段名1 , 字段名2 , ……) values ( 值1 ，值2 , ……) , ( 值1 ，值2 , ……) , ( 值1 ，值2 , ……) ;
insert into 表名 values ( 值1 ，值2 , ……) , ( 值1 ，值2 , ……) , ( 值1 ，值2 , ……) ;
注意；
插入數據時，指定的字段順序需要與值的順序是一一對應的。 字符串和日期型數據應該包含在引號中。 插入的數據大小，應該在字段的規定范圍內。 select 字段列表
from 表名列表
where 條件列表
group by 分組字段列表
having 分組后條件列表
order by 排序字段列表
limit 分頁參數
查詢多個字段 select * from 表名;
select 字段1 , 字段2 , …… from 表名;
設置別名 select 字段1 [ as 別名1 ] , 字段2 [ as 別名2 ] , …… from 表名;
去除重復記錄 select distinct 字段列表 from 表名;
語法 select 字段列表 from 表名 where 條件列表;
條件 運算符 功能 > 大于 >= 大于等于 < 小于 <= 小于等于 = 等于 <> 或 != 不等于 BETWEEN … AND … 在某個范圍內（含最小、最大值） IN(…) 在 in 之后的列表中的值，多選一 LIKE 占位符 模糊匹配（_ 匹配單個字符，% 匹配任意字符） IS NULL 是 NULL
邏輯運算符
運算符 功能 AND 或 && 并且（多個條件同時成立） OR 或 || 或者（多個條件任意一個成立） NOT 或 ! 非，不是
將一列數據視為整體，進行縱向統計 常見的聚合函數 函數 功能 count 統計數量 max 最大值 min 最小值 avg 平均值 sum 求和
使用方法 select 聚合函數( 字段列表) from 表名;
語法： select 字段列表 from 表名 group by 分組字段名
where與having區別 執行時機不同：where是分組之前進行過濾，不滿足where條件，不參與分組；而having是分組之后對結果進行過濾。 判斷條件不同：where不能對聚合函數進行判斷，而having可以。 注意 抗行順序：where>聚合函數>having 分組之后，查詢的字段一般為聚合函數和分組字段，查詢其他字段無任何意義。 語法 select 字段列表 order by 字段1 排序方式, 字段2 排序方式;
排序方式 語法 select 字段列表 from 表名 limit 起始索引, 查詢記錄數;
注意：
起始索引從0開始,起始索引 = (查詢頁碼 - 1) * 每頁顯示的記錄數 不同的sql軟件的分頁查詢語法不同,mysql的語法是limit 如果查詢的是第一頁數據,起始索引可以忽略 from -> where -> group by -> having -> select -> order by -> limit
use mysql;
select * from user ;
create user '用戶名' @'主機名' identified by '密碼' ;
alter user '用戶名' @'主機名' identified with mysql_native_password by '新密碼' ;
drop user '用戶名' @'主機名' ;
常用權限
權限 說明 ALL, ALL PRIVILEGES 所有權限 SELECT 查詢數據 INSERT 插入數據 UPDATE 修改數據 DELETE 刪除數據 ALTER 修改表 DROP 刪除數據庫/表/視圖 CREATE 創建數據庫/表
show grants for '用戶名' @'主機名' ;
grant 權限列表 on 數據庫名. 列表名 to '用戶名' @'主機名' ;
revoke 權限列表 on 數據庫名. 列表名 from '用戶名' @'主機名'
函數 功能 CONCAT(S1, S2, … Sn) 字符串拼接，將S1、S2、… Sn拼接成一個字符串 LOWER(str) 將字符串str全部轉為小寫 UPPER(str) 將字符串str全部轉為大寫 LPAD(str, n, pad) 左填充，用字符串pad對str的左邊進行填充，達到n個字符串長度 RPAD(str, n, pad) 右填充，用字符串pad對str的右邊進行填充，達到n個字符串長度 TRIM(str) 去掉字符串頭部和尾部的空格 SUBSTRING(str, start, len) 返回從字符串str從start位置起的len個長度的字符串
函數 功能 CEIL(x) 向上取整 FLOOR(x) 向下取整 MOD(x, y) 返回 x/y 的模 RAND() 返回 0~1 內的隨機數 ROUND(x, y) 求參數 x 的四舍五入的值，保留 y 位小數
函數 功能 CURDATE() 返回當前日期 CURTIME() 返回當前時間 NOW() 返回當前日期和時間 YEAR(date) 獲取指定 date 的年份 MONTH(date) 獲取指定 date 的月份 DAY(date) 獲取指定 date 的日期 DATE_ADD(date, INTERVAL expr type) 返回一個日期/時間值加上一個時間間隔 expr 后的時間值 DATEDIFF(date1, date2) 返回起始時間 date1 和 結束時間 date2 之間的天數
函數 功能 IF(value, t, f) 如果 value 為 true，則返回 t，否則返回 f IFNULL(value1, value2) 如果 value1 不為空，返回 value1，否則返回 value2 CASE WHEN [val1] THEN [res1] … ELSE [default] END 如果 val1 為 true，返回 res1，… 否則返回 default 默認值 CASE [expr] WHEN [val1] THEN [res1] … ELSE [default] END 如果 expr 的值等于 val1，返回 res1，… 否則返回 default 默認值
作用于表中字段上的規則,用于限制存儲在表中的數據 用于保證數據庫中的數據的正確性，有效性和完整性 約束分類 約束 描述 關鍵字 非空約束 限制該字段的數據不能為 null NOT NULL 唯一約束 保證該字段的所有數據都是唯一、不重復的 UNIQUE 主鍵約束 主鍵是一行數據的唯一標識，要求非空且唯一 PRIMARY KEY 默認約束 保存數據時，如果未指定該字段的值，則采用默認值 DEFAULT 檢查約束 保證字段值滿足某一個條件（8.0.16 版本之后） CHECK 外鍵約束 用來讓兩張表的數據之間建立連接，保證數據的一致性和完整性 FOREIGN KEY
用法 create table user ( id int primary key ) ;
外鍵用于使兩張表的數據之間建立連接，從而保證數據的一致性和完整性 建立外鍵約束語法
alter table 子表 add constraint 外鍵名稱 foreign key ( 外鍵字段名) references 主表( 主表列名) ;
CREATE TABLE 表名 ( 字段1 數據類型, 字段2 數據類型, . . . 外鍵字段 數據類型, CONSTRAINT 外鍵名稱 FOREIGN KEY ( 外鍵字段) REFERENCES 被引用表( 主鍵字段)
) ;
alter table 表名 drop foreign key 外鍵名稱;
刪除/更新行為 行為 說明 NO ACTION 當在父表中刪除/更新對應記錄時，首先檢查該記錄是否有對應外鍵，如果有則不允許刪除/更新。（與 RESTRICT 一致） RESTRICT 當在父表中刪除/更新對應記錄時，首先檢查該記錄是否有對應外鍵，如果有則不允許刪除/更新。（與 NO ACTION 一致） CASCADE 當在父表中刪除/更新對應記錄時，如果有，則也刪除/更新外鍵在子表中的記錄。 SET NULL 當在父表中刪除/更新對應記錄時，如果有則設置子表中該外鍵值為 null（這就要求該外鍵允許取 null）。 SET DEFAULT 父表有變更時，子表外鍵列設置成一個默認的值（InnoDB 不支持）
alter table 子表 add constraint 外鍵名稱 foreign key ( 外鍵字段名) references 主表( 主表列名) update 行為 delete 行為;
多對多: 含義：一個表中的多條記錄可以關聯到另一個表中的多條記錄，反之亦然。 建模方式：使用第三張中間表來表示這種關系，通常包含兩個外鍵，分別指向兩張主表。 一對多: 含義：一個表中的一條記錄可以關聯另一個表中的多條記錄，但反之不成立。 建模方式：在“多”的一方表中添加外鍵，指向“一”的一方的主鍵。 一對一: 含義：一個表中的一條記錄 只對應 另一個表中 唯一一條記錄，反之亦然。 建模方式：在任意一方加入外鍵，關聯另外一方的主鍵，并且設置外鍵為唯一的(UNIQUE)。 連接查詢 內連接：相當于查詢A、B交集部分數據 外連接： 左外連接：查詢左表所有數據，以及兩張表交集部分數據 右外連接：查詢右表所有數據，以及兩張表交集部分數據 自連接：當前表與自身的連接查詢，自連接必須使用表別名 子查詢:嵌套在其他 SQL 語句中的 SELECT 查詢，通常用于在主查詢中動態提供數據。
select 字段列表 from 表1 , 表2 where 條件;
select 字段列表 from 表1 join 表2 on 連接條件;
select 字段列表 from 表1 inner join 表2 on 連接條件;
select 字段列表 from 表1 left join 表2 on 連接條件;
select 字段列表 from 表1 left outer join 表2 on 連接條件;
select 字段列表 from 表1 right join 表2 on 連接條件;
select 字段列表 from 表1 right outer join 表2 on 連接條件;
select 字段列表 from 表A 別名A join 表A 別名B on 條件……;
對于union查詢，就是把多次查詢的結果合并起來，形成一個新的查詢結果集。 對于聯合查詢的多張表的列數必須保持一致，字段類型也需要保持一致。 union all會將全部的數據直接合并在一起，union會對合并之后的數據去重。
select 字段列表 from 表A ……
union all
select 字段列表 from 表B ……;
select 字段列表 from 表A ……
union
select 字段列表 from 表B ……;
概念：SQL語句中嵌套SELECT語句，稱為嵌套查詢，又稱子查詢 分類： 標量子查詢（子查詢結果為單個值） 列子查詢（子查詢結果為下列） 行子查詢（子查詢結果為一行） 表子查詢（子查詢結果為多行多列） 子查詢返回的結果是單個值（數字、字符串、日期等），最簡單的形式，這種子查詢成為標量子查詢 。 子查詢返回的結果是一列（可以是多行），這種子查詢稱為列子查詢。 常見操作符 操作符 描述（簡述） 示例說明 IN在指定集合中，多選一 SELECT * FROM emp WHERE dept_id IN (1, 2, 3);NOT IN不 在指定集合中SELECT * FROM emp WHERE dept_id NOT IN (1, 2);ANY子查詢返回值中，任意一個滿足即可 salary > ANY (SELECT salary FROM emp WHERE dept_id = 10)某一個人 的員工SOME與 ANY 相同，完全等價 使用不多，ANY 更常見 ALL子查詢返回值中，所有都必須滿足 salary > ALL (SELECT salary FROM emp WHERE dept_id = 10)所有人 的員工
create table salgrade(
grade int ,
losal int ,
hisal int
) comment '薪資等級表' ; insert into salgrade values ( 1 , 0 , 3000 ) ;
insert into salgrade values ( 2 , 3001 , 5000 ) ;
insert into salgrade values ( 3 , 5001 , 8000 ) ;
insert into salgrade values ( 4 , 8001 , 10000 ) ;
insert into salgrade values ( 5 , 10001 , 15000 ) ;
insert into salgrade values ( 6 , 15001 , 20000 ) ;
insert into salgrade values ( 7 , 20001 , 25000 ) ;
insert into salgrade values ( 8 , 25001 , 30000 ) ;
CREATE TABLE dept ( id INT PRIMARY KEY , name VARCHAR ( 50 )
) ; INSERT INTO dept ( id, name) VALUES
( 1 , '研發部' ) ,
( 2 , '市場部' ) ,
( 3 , '財務部' ) ,
( 4 , '銷售部' ) ,
( 5 , '總經辦' ) ,
( 6 , '人事部' ) ;
CREATE TABLE emp ( id INT PRIMARY KEY , name VARCHAR ( 50 ) , age INT , job VARCHAR ( 50 ) , salary DECIMAL ( 10 , 2 ) , entrydate DATE , managerid INT , dept_id INT
) ; INSERT INTO emp ( id, name, age, job, salary, entrydate, managerid, dept_id) VALUES
( 1 , '金庸' , 66 , '總裁' , 20000 , '2000-01-01' , NULL , 5 ) ,
( 2 , '張無忌' , 20 , '項目經理' , 12500 , '2005-12-05' , 1 , 1 ) ,
( 3 , '楊逍' , 33 , '開發' , 8400 , '2000-11-03' , 2 , 1 ) ,
( 4 , '韋一笑' , 48 , '開發' , 11000 , '2002-02-05' , 2 , 1 ) ,
( 5 , '常遇春' , 43 , '開發' , 10500 , '2004-09-07' , 3 , 1 ) ,
( 6 , '小昭' , 19 , '程序員鼓勵師' , 6600 , '2004-10-12' , 2 , 1 ) ,
( 7 , '滅絕' , 60 , '財務總監' , 8500 , '2002-09-12' , 1 , 3 ) ,
( 8 , '周芷若' , 19 , '會計' , 48000 , '2006-06-02' , 7 , 3 ) ,
( 9 , '丁敏君' , 23 , '出納' , 5250 , '2009-05-13' , 7 , 3 ) ,
( 10 , '趙敏' , 20 , '市場部總監' , 12500 , '2004-10-12' , 1 , 2 ) ,
( 11 , '鹿杖客' , 56 , '職員' , 3750 , '2006-10-03' , 10 , 2 ) ,
( 12 , '鶴筆翁' , 19 , '職員' , 3750 , '2007-05-09' , 10 , 2 ) ,
( 13 , '方東白' , 19 , '職員' , 5500 , '2009-02-12' , 10 , 2 ) ,
( 14 , '張三豐' , 88 , '銷售總監' , 14000 , '2004-10-12' , 1 , 4 ) ,
( 15 , '俞蓮舟' , 38 , '銷售' , 4600 , '2004-10-12' , 14 , 4 ) ,
( 16 , '宋遠橋' , 40 , '銷售' , 4600 , '2004-10-12' , 14 , 4 ) ,
( 17 , '陳友諒' , 42 , NULL , 2000 , '2011-10-12' , 1 , NULL ) ;
select e. name, e. age, e. job, d. name from dept d, emp e where e. dept_id = d. id;
select e. name, e. age, e. job, d. name from emp e left join dept d on e. dept_id = d. id;
select e. name, e. age, e. job, d. name from dept d, emp e where ( e. dept_id = d. id and age < 30 ) ;
select e. name, e. age, e. job, d. name from emp e join dept d where ( e. dept_id = d. id and age < 30 ) ;
select e. name, e. age, e. job, d. name from emp e join dept d on e. dept_id = d. id where age < 30 ;
select distinct d. id, d. name from emp e, dept d where e. dept_id = d. id order by d. id;
select e. name, d. name from emp e left join dept d on e. dept_id = d. id where e. age > 40 ;
select e. name, s. grade from emp e join salgrade s on e. salary between losal and hisal;
select e. name, s. grade from emp e join salgrade s on ( s. losal <= e. salary and e. salary <= s. hisal) ;
select e. * , s. grade from emp e
join dept d on e. dept_id = d. id
join salgrade s on e. salary between losal and hisal
where d. name = '研發部' ; select e. * , s. grade
from emp e, dept d, salgrade s
where e. dept_id = d. id and e. salary between losal and hisal and d. name = '研發部' ;
select avg ( e. salary) from emp e
join dept d on e. dept_id = d. id
where d. name = '研發部' ;
select * from emp where salary > ( select salary from emp where name = '滅絕' ) ;
select * from emp where salary > ( select avg ( salary) from emp) ;
select e1. * from emp e1 where e1. salary < ( select avg ( salary) from emp e2 where e2. dept_id = e1. dept_id) ;
select d. id, d. name, count ( e. id) as emp_count
from dept d
left join emp e on d. id = e. dept_id
group by d. id, d. name; select d1. * , ( select count ( * ) from emp e where e. dept_id = d1. id) as emp_count
from dept d1;
+ 事務是一組操作的集合，它是一個不可分割的工作單位，事務會把所有的操作作為一個整體一起向系統提交或撤銷操作請求，即這些操作* * 要么同時成功，要么同時失敗* * 。
+ 默認MySQL的事務是自動提交的，也就是說，當執行一條DML語句，MySQL會立即隱式的提交事務。
` ` ` sql
select @@autocommit
set @@autocommit
commit ;
rollback ;
start transaction ;
begin ;
原子性(Atomicity):事務是不可分割的最小操作單元，要么全部成功，要么全部失敗。 一致性(Consistency):事務完成時，必須使所有的數據都保持一致狀態。 隔離性(Isolation):數據庫系統提供的隔離機制，保證事務在不受外部并發操作影響的獨立環境下運行。 持久性(Durability)：事務一旦提交或回滾，它對數據庫中的數據的改變就是永久的。 問題 描述 臟讀 一個事務讀取到了另一個事務尚未提交的數據 。如果那個事務隨后回滾了，那么你讀取到的數據就根本不應該存在，叫做“臟”數據。 不可重復讀 一個事務兩次讀取同一條記錄 ，但讀取的內容不同（中間有其他事務修改了數據并提交），稱為不可重復讀。 幻讀 一個事務按照條件查詢數據時，第一次查沒有；另一個事務插入了滿足條件的新記錄并提交 ；當前事務再次查詢時又看到了新數據，好像產生了“幻影”。
隔離級別（Isolation Level） 臟讀（Dirty Read） 不可重復讀（Non-repeatable Read） 幻讀（Phantom Read） Read Uncommitted ? 可能出現 ? 可能出現 ? 可能出現 Read Committed ? 避免了 ? 可能出現 ? 可能出現 Repeatable Read（默認） ? 避免了 ? 避免了 ? 可能出現（??） Serializable ? 避免了 ? 避免了 ? 避免了
select @@transaction_isolation ;
set [ session | global ] transaction level 隔離級別;
)
