MySQL
[TOC]
注釋
- 單行注釋： --
- 多行注釋 ：/* */
SQL分類
- DDL ：Data Definition Language 數據定義語言：create拌滋、drop虱咧、alert等
- DML：Data Manipulation language 數據操作語言： insert拭抬、update苍狰、delete等
- DQL：Data Query Language 數據查詢語言：select叽粹、where等
- DCL：Data Control Language 數據控制語言（定義數據庫訪問權限和安全級別以及創(chuàng)建用戶）：GRANT蛆楞、REVOKE等
操作數據：CRUD
C(Create):創(chuàng)建
R(Retrieve):查詢
U(Update):修改
-
D(Delete):刪除
/*-----------------------數據庫操作----------------------*/ /*創(chuàng)建數據庫*/ create database 數據庫名稱; /*創(chuàng)建數據庫也物，如果不存在的話*/ create database if is not exists 數據庫名稱; /*創(chuàng)建數據庫 并設置字符集*/ create database 數據庫名稱 character set GBK; /*查詢所有數據庫的名稱*/ show databases; /*查詢創(chuàng)建數據庫的語法*/ show create database mysql; /*查詢某個數據庫的字符集*/ show create database 數據庫名稱; /*修改數據庫的字符集*/ alter database 數據庫名稱 character set 字符集; /*刪除數據庫*/ drop database 數據庫名稱; drop database 數據庫名稱 if exists; /*查詢當前正在使用的數據庫*/ seletct database(); /*使用數據庫*/ use 數據庫名稱 /*-----------------------表操作----------------------*/ /*創(chuàng)建表*/ create table 表名(列名1 數據類型,列名2 數據類型... ); /*查詢數據庫中所有表的名稱*/ show tables; /*查詢表結構*/ desc 表名; /*復制表*/ create table 表名 like 被復制的表; /*修改表名*/ alter table 表名 rename to 新的表名; /*修改表的字符集*/ alter table 表名 character set 字符集; /*添加一列*/ alter table 表名 add 列名 數據類型; /*修改列名稱 類型*/ alter table 表名 change 列名 新列名; alter table 表名 modify 列名 新數據類型; alter table 表名 change 列名 新列名 新數據類型; /*刪除列*/ alter table 表名 drop 列名; /*刪除表*/ drop table 表名 if exists;
MySql支持的數據類型
基本增刪改查
/*-----------------------表中數據操作----------------------*/
/*添加 如不定義列名,則默認給所有列添加值*/
insert into 表名 (列名1,列名2....) values (值1,值2,...)
/*刪除 如無條件叛赚，則刪除全部*/
delete from 表名 where [where條件]
/*刪除表澡绩，再創(chuàng)建一個一樣的新表 */
truncate table 表名
/*修改數據*/
update 表名 set 列名1=值1,列名2=值2.... [where條件]
/*查詢排序 升序(默認)：asc 降序 desc*/
select * from 表名 order by 列名 排序方式
查詢：
-
排序
升序(默認)：asc-
降序：descselect * from 表名 order by 列名1 排序方式1,列名2 排序方式2 ... [where條件]
-
聚合函數
count ：個數max：最大值min：最小值sum：和-
avg：平均值select count(*) from 表名 --查詢條數，只要有一個值不為空就算一跳記錄 select count(列名) from 表名 --會排除null值 select count(列名 ifnull(列名,默認值)) from 表名 --不排除null值俺附，設置null時的默認值
-
分組
group bygroup by 分組字段having 分組后的限定條件select * from 表名 [where 條件] group by 分組條件 having [having 限定條件];where 和 having 的區(qū)別 ：
[where 條件] 分組前限定肥卡，并不可用聚合函數判斷
-
[having 限定條件] 分組后限定，可與聚合函數一起判斷
例: 查詢學生表中事镣，男女生數學成績的平局值步鉴，要求：參與分組分數不得小于70人，且人數不少于2人
select sex,avg(math),count(id) from student where math>70 group by sex having count(id)>2;
-
分頁
limitlimit 開始的索引蛮浑，每頁的條數// 每頁3條數據唠叛，查詢第一頁的數據 select * from student limit 0,3 -
去重
distinct//id去重 select distinct id from student //多個字段去重，只有多個字段完全一樣才會被去重 -
模糊查詢
select * from student where name like "a%"; //第二個字是a的 select * from student where name like "_a%"; //名字是三個字的 select * from student where name like "___"; //名字包含a的 select * from student where name like "%a%"; -
其他基礎
>沮稚、<艺沼、>=、<=蕴掏、=between..andin 集合likeis nulland 或者 &&or 或者 ||not 或者 !
約束
例子：
CREATE TABLE stu(
id INT PRIMARY KEY AUTO_INCREMENT, --主鍵
name VARCHAR(20) NOT NULL, --非空
phone VARCHAR(20) UNIQUE --唯一
);
-
主鍵:primary key (非空且唯一苍姜，一張表只能有一個字段為主鍵)
-- 添加主鍵 ALTER TABLE stu MODIFY id INT PRIMARY KEY; -- 主鍵 無法用一下語句刪除 ALTER TABLE stu MODIFY id INT; -- 主鍵刪除 ALTER TABLE stu DROP PRIMARY KEY; -- 刪除主鍵自增長 ALTER TABLE stu MODIFY id INT;
-
非空約束:not null (字段不能為空)
-- 添加非空約束 ALTER TABLE stu MODIFY name VARCHAR(20) NOT NULL; -- 刪除非空約束 ALTER TABLE stu MODIFY name VARCHAR(20) ;
-
唯一約束:unique (字段值唯一，注意MySQL中 null可有多個)
ALTER TABLE stu MODIFY phone VARCHAR(20) UNIQUE; -- 唯一約束 無法用一下語句刪除 ALTER TABLE stu MODIFY phone VARCHAR(20) ; -- 唯一約束正確的刪除方式： ALTER TABLE stu DROP INDEX phone; -
外鍵約束:foreign key
語法：
CONSTRAINT [外鍵名稱] FOREIGN KEY ([本表的字段]) REFERENCES 外鍵鏈接的表名([鏈接表名中的字段])CREATE TABLE A( id INT PRIMARY KEY AUTO_INCREMENT ); CREATE TABLE B( id INT PRIMARY KEY AUTO_INCREMENT, id_A INT, CONSTRAINT a_b_id FOREIGN KEY (id_A) REFERENCES A(id) -- 創(chuàng)建表時添加外鍵 ); -- 添加外鍵 ALTER TABLE B ADD CONSTRAINT a_b_id FOREIGN KEY (id_A) REFERENCES A(id) -- 刪除外鍵 ALTER TABLE B DROP FOREIGN KEY a_b_id; -- 級聯操作 -- 1.級聯更新 ON UPDATE CASCADE -- 2.級聯刪除 ON DELETE CASCADE ALTER TABLE B ADD CONSTRAINT a_b_id FOREIGN KEY (id_A) REFERENCES A(id) ON UPDATE CASCADE ON DELETE CASCADE;
數據庫設計的三大范式
第一范式（1NF）：數據庫表的每一列都是不可分割的原子數據項
第二范式（2NF）：在第一范式的基礎上撒强，實體的屬性完全依賴于主關鍵字倦零。所謂完全依賴是指不能存在僅依賴主關鍵字一部分的屬性
第三范式（3NF）：在2NF基礎上，任何非主屬性不依賴于其它非主屬性（在2NF基礎上消除傳遞依賴）
數據庫備份與還原
-
備份： mysqldump -u用戶名 -p密碼 > 保存路徑
備份指定數據庫： mysqldump -u用戶名 -p密碼 數據庫名稱 > 保存路徑
-
還原：
- 登陸數據庫
- 創(chuàng)建數據庫
- 使用數據庫
- 執(zhí)行備份文件即供，source 文件路徑
多表查詢
笛卡爾積：多表查詢定拟，清除無用數據
select * from table_a,table_b [where 條件]
-- 為表取別名
select * from table_a a,table_b b [where 條件]
-
內連接：查詢多表交集部分
-
隱式內連接：使用where 條件消除無用數據
語法：
select 字段列表 from table1,table2 where table1.id = table2.id -
顯示內連接 ：
語法：
select 字段列表 from table1 [inner] join table2 ON [條件](inner 可省略)
-
-
外鏈接
-
左外連接：查詢的是左表的全部信息，以及與右表其交集部分
語法：
select 字段列表 from table1 left [outer] join table2 on [條件] -
右外連接：查詢的是右表的全部信息逗嫡，以及與左表其交集部分
語法：
select 字段列表 from table1 right [outer] join table2 on [條件]
-
-
子查詢
單行多列：作為條件使用運算符 >青自、<株依、=、>=延窜、<=
多行單列：作為條件運算符 IN
-
多行多列：作為虛擬表進行多表聯查
select * from table1 t1,(select * from table2 [where]) t2 where table1.id = table2.id
事務
事務概念：如果一個包含多個步驟的業(yè)務操作恋腕，被事務管理，要么同時成功逆瑞，要么同事失敗荠藤。
-
事務操作方式：
- 開啟事務：start transaction;
- 回滾：rollback;
- 提交：commit;
-- 開啟事務 start transaction; -- 被事務管理的sql語句 ... -- 執(zhí)行通過 commit; -- 出現問題，回滾事務 刪除臨時數據获高，回滾數據到開啟事務之前 rollback;
關于數據提交需要注意的地方：
MySQL 中事務默認自動提交哈肖，Oracle 默認手動提交
自動提交：增刪改語句會默認自動提交，使數據持久化
手動提交：開啟事務的情況下谋减，必須commit 手動提交牡彻，才能讓數據持久化
修改事務的默認提交方式：
? 查詢默認提交方式：
select @@autocommit;-- 1 自動提交 2 手動提交? 修改默認提交方式：
set @@autocommit=0;? 修改完成后每一條sql都需要手動更新
-
事務的四大特征
- 原子性：不可分割的最小操作單位
- 持久性：事務一旦提交或者回滾，會持久化保存數據
- 隔離性：多個事務之間相互獨立
- 一致性：事務操作前后數據總量不變
-
事務的隔離級別
用來處理多個事務處理同一批數據引發(fā)的一些問題
存在問題：
- 臟讀：一個事務讀到另一個事務中沒有提交的數據
- 不可重復讀(虛度)：同一個事務中出爹，兩次獨到的數據不一樣
- 幻讀：一個事務操作(DML)數據表中所有的記錄庄吼，另一個事務添加了一條數據，則第一個事務無法查詢到自己的修改
隔離級別：
-
read uncommitted:讀未提交問題：臟讀严就、不可重復讀总寻、幻讀
-
read committed:讀已提交(Oracle 默認) (只有提交了數據另一個事務才能讀到)問題 : 不可重復讀、幻讀
-
repeatable read: 可重復讀(MySQL 默認)問題 : 幻讀
-
serializable：串行化 (鎖表)解決所有存在的問題
注意：隔離級別 安全性越來越高梢为，效率越來越低
關于數據庫隔離級別設置：
-- 查詢事務的隔離級別
select @@tx_isolation;
-- 設置隔離級別
set global transaction isolation level 級別字符串;
數據庫管理員
-
管理用戶
-
添加用戶
語法：
create user '用戶名'@'主機名' identified by '密碼'; -
刪除用戶
語法：
drop user '用戶名'@'主機名'; -
修改用戶密碼
語法：
update user set password = password('新密碼') where user ='用戶名'或者：
set password for '用戶名'@'主機名' = password('新密碼'); -
查詢用戶
-
切換到mysql數據庫
use mysql; -
查詢user 表
select * from user;
-
-
-
管理權限
查詢權限：
show grants for '用戶名'@'主機名';-
授予權限：
grant 權限列表 on 數據庫.表名 to '用戶名'@'主機名';授予所有權限：
grant ALL on *.* to '用戶名'@'主機名'; 撤銷權限：
revoke 權限列表 on 數據庫.表名 from '用戶名'@'主機名';
