操作資料庫
陳述句分類
-
DDL 定義庫、表
- create 、drop、alter
-
DML 更新資料
- insert 、update、delete
-
DQL 查詢
- select
-
DCL 控制
- grant、deny、revoke
-
TCL 事務
- commit、rollback
DDL示例
創建資料庫 : create database [if not exists] 資料庫名;
洗掉資料庫 : drop database [if exists] 資料庫名;
查看資料庫 : show databases;
使用資料庫 : use 資料庫名;
創建表
create table [if not exists] `表名`(
'欄位名1' 列型別 [屬性][索引][注釋],
'欄位名2' 列型別 [屬性][索引][注釋],
#...
'欄位名n' 列型別 [屬性][索引][注釋]
)[表型別][表字符集][注釋];
說明 : 反引號用于區別MySQL保留字與普通字符而引入的 (鍵盤esc下面的鍵)
DML示例
通過DML陳述句管理資料庫資料
添加資料
INSERT INTO 表名[(欄位1,欄位2,欄位3,...)] VALUES('值1','值2','值3',...)
注意 :
- 欄位或值之間用英文逗號隔開
- ' 欄位1,欄位2...' 該部分可省略 , 但添加的值務必與表結構,資料列,順序相對應,且數量一致 .
- 可同時插入多條資料 , values 后用英文逗號隔開 .
修改資料
UPDATE 表名 SET column_name=value [,column_name2=value2,...] [WHERE condition];
注意 :
- column_name 為要更改的資料列
- value 為修改后的資料 , 可以為變數 , 具體指 , 運算式或者嵌套的SELECT結果
- condition 為篩選條件 , 如不指定則修改該表的所有列資料
洗掉資料
DELETE FROM 表名 [WHERE condition];
以上方法不重置自增計數器
TRUNCATE [TABLE] 表名;
truncate截斷，相當于刪表重建，自增計數器重置，
DQL查詢
語法：
SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
[left | right | inner join table_name2] -- 聯合查詢
[WHERE ...] -- 指定結果需滿足的條件
[GROUP BY ...] -- 指定結果按照哪幾個欄位來分組
[HAVING] -- 過濾分組的記錄必須滿足的次要條件
[ORDER BY ...] -- 指定查詢記錄按一個或多個條件排序
[LIMIT {[offset,]row_count | row_countOFFSET offset}];
-- 指定查詢的記錄從哪條至哪條
說明:
-
distinct 去重
-
as 陳述句可作別名
- 用于資料列、表名、計算結果
指定查詢欄位
-- 查詢所有學生資訊
SELECT * FROM student;
-- 查詢指定列(學號 , 姓名)
SELECT studentno,studentname FROM student;
where條件陳述句
作用：用于檢索資料表中 符合條件 的記錄
搜索條件可由一個或多個邏輯運算式組成 , 結果一般為真或假.
-- 查詢考試成績在95-100之間的
SELECT Studentno,StudentResult
FROM result
WHERE StudentResult>=95 AND StudentResult<=100;
-- AND也可以寫成 &&
模糊查詢
包含between and、like、in、null的查詢
-- 查詢姓劉的同學的學號及姓名
SELECT studentno,studentname FROM student
WHERE studentname LIKE '劉%';
-- 查詢姓名中含有 嘉 字的
SELECT studentno,studentname FROM student
WHERE studentname LIKE '%嘉%';
-- 查詢學號為1000,1001,1002的學生姓名
SELECT studentno,studentname FROM student
WHERE studentno IN (1000,1001,1002);
-- 查詢出生日期沒有填寫的同學
-- 不能直接寫=NULL , 這是代表錯誤的 , 用 is null
SELECT studentname FROM student
WHERE BornDate IS NULL;
-- 注意空字串'' 和 null 不等價
連接查詢
| 運算子名稱 | 描述 |
|---|---|
| INNER JOIN | 如果表中有至少一個匹配，則回傳行 |
| LEFT JOIN | 即使右表中沒有匹配，也從左表中回傳所有的行 |
| RIGHT JOIN | 即使左表中沒有匹配，也從右表中回傳所有的行 |
左連接，保留左表所有資料；右連接，保留右表所有資料，
3模式擴展7模式
-- 內連接
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
-- 等值連接
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s , result r
WHERE r.studentno = s.studentno
-- 查一下缺考的同學(左連接應用場景)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno
WHERE StudentResult IS NULL
-- 左連接保留所有學生資訊，篩選成績為空，即為缺考的同學
-- 自連接，資料表與自身進行連接
INSERT INTO `category` (`categoryid`, `pid`, `categoryName`)
VALUES('2','1','資訊技術'),
('3','1','軟體開發'),
('4','3','資料庫'),
('5','1','美術設計'),
('6','3','web開發'),
('7','5','ps技術'),
('8','2','辦公資訊');
SELECT a.categoryName AS '父欄目',b.categoryName AS '子欄目'
FROM category AS a,category AS b
WHERE a.`categoryid`=b.`pid`
子查詢
套娃：在查詢陳述句中的WHERE條件子句中,又嵌套了另一個查詢陳述句，
子查詢回傳的結果一般都是集合,故而建議使用IN關鍵字;
-- 查詢課程為 高等數學-2 且分數不小于80分的學生的學號和姓名
SELECT studentno,studentname FROM student WHERE studentno IN(
SELECT studentno FROM result WHERE StudentResult>=80 AND subjectno=(
SELECT subjectno FROM `subject` WHERE subjectname = '高等數學-2'
)
)
最難的就是相關子查詢！現階段并未講解
排序分頁
查詢得到結果集后，還可以進行一些操作，
ORDER BY 陳述句
- 用于根據指定的列對結果集進行排序
- 默認按照ASC升序對記錄進行排序
- DESC 關鍵字降序
-- 查詢 資料庫結構-1 的所有考試結果(學號 學生姓名 科目名稱 成績)
-- 按成績降序排序
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='資料庫結構-1'
ORDER BY StudentResult DESC
LIMIT 陳述句
SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
-
用于強制 SELECT 陳述句回傳指定的記錄數
-
offset指定第一個回傳記錄行的偏移量，不輸入則默認為0
- LIMIT n 等價于 LIMIT 0,n，
-
rows 指定回傳記錄行的最大數目
-- 查詢 JAVA第一學年 課程成績前10名的學生資訊(學號,姓名,課程名,分數)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='JAVA第一學年'
ORDER BY StudentResult DESC
LIMIT 0,10 -- LIMIT 10
DCL實體
權限控制
授權
GRANT 權限串列 ON 表名 TO 用戶名
撤銷
REVOKE 權限串列 ON 表名 FROM 用戶名
用戶管理
/* 用戶和權限管理 */ ------------------
用戶資訊表：mysql.user
-- 重繪權限
FLUSH PRIVILEGES
-- 增加用戶 CREATE USER kuangshen IDENTIFIED BY '123456'
CREATE USER 用戶名 IDENTIFIED BY [PASSWORD] 密碼(字串)
- 必須擁有mysql資料庫的全域CREATE USER權限，或擁有INSERT權限，
- 只能創建用戶，不能賦予權限，
- 用戶名，注意引號：如 'user_name'@'192.168.1.1'
- 密碼也需引號，純數字密碼也要加引號
- 要在純文本中指定密碼，需忽略PASSWORD關鍵詞，要把密碼指定為由PASSWORD()函式回傳的
混編值，需包含關鍵字PASSWORD
-- 重命名用戶 RENAME USER kuangshen TO kuangshen2
RENAME USER old_user TO new_user
-- 設定密碼
SET PASSWORD = PASSWORD('密碼') -- 為當前用戶設定密碼
SET PASSWORD FOR 用戶名 = PASSWORD('密碼') -- 為指定用戶設定密碼
-- 洗掉用戶 DROP USER kuangshen2
DROP USER 用戶名
-- 分配權限/添加用戶
GRANT 權限串列 ON 表名 TO 用戶名 [IDENTIFIED BY [PASSWORD] 'password']
- all privileges 表示所有權限
- *.* 表示所有庫的所有表
- 庫名.表名 表示某庫下面的某表
-- 查看權限 SHOW GRANTS FOR root@localhost;
SHOW GRANTS FOR 用戶名
-- 查看當前用戶權限
SHOW GRANTS; 或 SHOW GRANTS FOR CURRENT_USER; 或 SHOW GRANTS FOR
CURRENT_USER();
-- 撤消權限
REVOKE 權限串列 ON 表名 FROM 用戶名
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用戶名 -- 撤銷所有權限
TCL 事務
什么是事務
- 事務就是將一組SQL陳述句放在同一批次內去執行
- 如果一個SQL陳述句出錯,則該批次內的所有SQL都將被取消執行
- MySQL事務處理只支持InnoDB和BDB資料表型別
遵循ACID原則
-
原子性(Atomic)
- 要么全部完成，要么全部不完成
-
一致性(Consist)
- 最終一致性，總量守恒
-
持久性(Durable)
- 未提交，回滾
- 已提交，保存到資料庫
- 一旦提交就不可逆！
-
隔離性(Isolated)
-
多個用戶并發訪問資料庫時，為每個用戶開啟事務，不能被其他事務干擾的事務之間要互相隔離
-
隔離級別
- 臟讀：一個事務讀取另一個事務未提交的資料
- 不可重復讀：一次事務內，同一個資料，多次讀取結果不一致
- 虛讀：在一個事務內，讀取到另個事務插入的資料
-
事務實作
-- 使用set陳述句來改變自動提交模式
SET autocommit = 0; /*關閉*/
SET autocommit = 1; /*開啟*/
-- 注意:
--- 1.MySQL中默認是自動提交
--- 2.使用事務時應先關閉自動提交
-- 開始一個事務,標記事務的起始點
START TRANSACTION
-- 提交一個事務給資料庫
COMMIT
-- 將事務回滾,資料回到本次事務的初始狀態
ROLLBACK
-- 還原MySQL資料庫的自動提交
SET autocommit =1;
-- 保存點
SAVEPOINT 保存點名稱 -- 設定一個事務保存點
ROLLBACK TO SAVEPOINT 保存點名稱 -- 回滾到保存點
RELEASE SAVEPOINT 保存點名稱 -- 洗掉保存點
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/19747.html
標籤：MySQL
上一篇：初識MySQL