資料庫基礎操作-part2
- 2022 年 9 月 7 日
- 筆記
- 資料庫的開發和優化
單表和多表查詢
單表查詢
記錄詳細操作:
增
insert into t1(欄位1, 欄位2, 欄位3) values
(值1, 值2, 值3),
(值1, 值2, 值3),
(值1, 值2, 值3);
改
update t1 set
欄位1 = 值1,
欄位2 = 值2,
where 條件;
刪除
delete from 表 where 條件; -- auto_increment自增記錄不會被刪除
truncate 表; -- 將auto_increment清空
show create table name; -> 查看創建表資訊
查
select distinct 欄位1, 欄位2, 欄位3 from 表
where 條件
group by 分組欄位
having 條件
order by 排序欄位
limit 限制條數
1. 簡單查詢
select * from employee;
select name, post from employee;
select distinct post from employee; -- 篩選掉重複的
select name as 名字, salary*12 as annual_salary from employee; -- 修改欄位名
-- 拼接字元串. 可用於表數據遷移. 將數據進行concat拼接後通過create table 新表名...複製到新表中
select concat("名字:", name) as new_name, concat("薪資:", salary) from employee;
select concat_ws(":", name, post, salary) from employee;
-- 表篩選查詢(了解即可)
select (
case
when name = 'egon' then
name
when name = "alex" then
concat(name, "_bigsb")
else
concat(name, 'sb')
end
) as new_name from employee;
2. where
select * from employee where id >= 10;
select * from employee where id >= 10 and id <= 13;
select * from employee where id between 10 and 13;
select * from employee where id = 10 or id = 13 or id = 15;
select * from employee where id in (10, 13, 15);
select * from employee where name like "Ma%" -- 模糊查詢
3. group by
聚合函數
max
min
sum
avg
count
select post, max(salary) from employee group by post;
select post, min(salary) from employee group by post;
select post, avg(salary) from employee group by post;
select post, sum(salary) from employee group by post;
select post, count(id) from employee group by post;
-- 取出每個性別的人數
select sex, count(id) from employee group by sex;
-- 案例1: 取出每個部門男員工的平均薪資
select post, avg(salary) from employee where sex = "male" group by post;
-- 案例2: 取出每個部門年齡大於20的員工的最大薪資
select post, max(salary) from employee where age <= 20 group by post;
4.having
-- 取出男員工平均薪資大於10000的部門
select post, avg(salary) from employee where sex = "male" group by post having avg(salary) > 10000;
select salary from employee where max(salary) > 1000; -- 該語句錯誤
select max(salary) from employee; -- 因為順序原因. 只有where不能使用聚合函數
5. order by 排序
select * from employee order by age asc; -- ascend -> 升
select * from employee order by age desc; -- descend -> 降
select * from employee order by age asc, id desc -- 出現相同的繼續排序
-- 取出男員工的平均薪資大於10000的部門並且按照平均薪資降序排序
select post, avg(salary) from employee where sex = "male" group by post having avg(salary) > 10000
order by avg(salary) desc;
6. limit
select * from employee limit 3;
-- 取年齡最大的員工的所有資訊
select * from employee order by age desc limit 1;
select * from employee limit 0, 5;
select * from employee limit 5, 5;
select * from employee limit 10, 5;
7. regexp -> 正則表達式
select * from employee where name regexp "^M.*u$"
鏈表查詢
# 創建表格
create table department(
id int primary key,
post varchar(16)
);
insert into department values
(200, "技術"),
(201, "人力資源"),
(202, "銷售"),
(203, "運營");
# 內鏈接(inner join): 只保留有對應關係的記錄
select * from employee, department where employee.dep_id = department.id;
Output:
+------+----------+----+----------+--------+--------+-----+--------+
| id | name | id | name | salary | sex | age | dep_id |
+------+----------+----+----------+--------+--------+-----+--------+
| 200 | 技術 | 1 | Mokeke | 100000 | female | 18 | 200 |
| 200 | 技術 | 3 | xiaoergu | 3000 | female | 27 | 200 |
| 201 | 人力資源 | 4 | longge | 8000 | male | 37 | 201 |
| 201 | 人力資源 | 6 | ocean | 10000 | male | 47 | 201 |
| 200 | 技術 | 7 | peiyao | 5000 | male | 78 | 200 |
+------+----------+----+----------+--------+--------+-----+--------+
select * from employee inner join department on employee.dep_id = department.id;
Output:
+----+----------+--------+--------+-----+--------+------+----------+
| id | name | salary | sex | age | dep_id | id | post |
+----+----------+--------+--------+-----+--------+------+----------+
| 1 | Mokeke | 100000 | female | 18 | 200 | 200 | 技術 |
| 3 | xiaoergu | 3000 | female | 27 | 200 | 200 | 技術 |
| 4 | longge | 8000 | male | 37 | 201 | 201 | 人力資源 |
| 6 | ocean | 10000 | male | 47 | 201 | 201 | 人力資源 |
| 7 | peiyao | 5000 | male | 78 | 200 | 200 | 技術 |
+----+----------+--------+--------+-----+--------+------+----------+
# 左鏈接(left join): 在內鏈接的基礎上保留左表的記錄
select * from employee left join department on employee.dep_id = department.id;
Output:
+----+----------+--------+--------+-----+--------+------+----------+
| id | name | salary | sex | age | dep_id | id | post |
+----+----------+--------+--------+-----+--------+------+----------+
| 1 | Mokeke | 100000 | female | 18 | 200 | 200 | 技術 |
| 3 | xiaoergu | 3000 | female | 27 | 200 | 200 | 技術 |
| 7 | peiyao | 5000 | male | 78 | 200 | 200 | 技術 |
| 4 | longge | 8000 | male | 37 | 201 | 201 | 人力資源 |
| 6 | ocean | 10000 | male | 47 | 201 | 201 | 人力資源 |
| 2 | Maxs_Hu | 100001 | male | 17 | 204 | NULL | NULL |
| 5 | egon | 3000 | female | 15 | 204 | NULL | NULL |
| 8 | qinjun | 41239 | male | 23 | 204 | NULL | NULL |
+----+----------+--------+--------+-----+--------+------+----------+
# 右鏈接(right join): 在內鏈接的基礎上保留右表的記錄
select * from employee right join department on employee.dep_id = department.id;
Output:
+------+----------+--------+--------+------+--------+------+----------+
| id | name | salary | sex | age | dep_id | id | post |
+------+----------+--------+--------+------+--------+------+----------+
| 1 | Mokeke | 100000 | female | 18 | 200 | 200 | 技術 |
| 3 | xiaoergu | 3000 | female | 27 | 200 | 200 | 技術 |
| 4 | longge | 8000 | male | 37 | 201 | 201 | 人力資源 |
| 6 | ocean | 10000 | male | 47 | 201 | 201 | 人力資源 |
| 7 | peiyao | 5000 | male | 78 | 200 | 200 | 技術 |
| NULL | NULL | NULL | NULL | NULL | NULL | 202 | 銷售 |
| NULL | NULL | NULL | NULL | NULL | NULL | 203 | 運營 |
+------+----------+--------+--------+------+--------+------+----------+
# 全鏈接(full join): 在內鏈接的基礎上. 左右兩邊沒有對應關係的記錄也留下來
select * from employee left join department on department.id = employee.dep_id
union
select * from employee right join department on department.id = employee.dep_id;
Output:
+------+----------+--------+--------+------+--------+------+----------+
| id | name | salary | sex | age | dep_id | id | post |
+------+----------+--------+--------+------+--------+------+----------+
| 1 | Mokeke | 100000 | female | 18 | 200 | 200 | 技術 |
| 3 | xiaoergu | 3000 | female | 27 | 200 | 200 | 技術 |
| 7 | peiyao | 5000 | male | 78 | 200 | 200 | 技術 |
| 4 | longge | 8000 | male | 37 | 201 | 201 | 人力資源 |
| 6 | ocean | 10000 | male | 47 | 201 | 201 | 人力資源 |
| 2 | Maxs_Hu | 100001 | male | 17 | 204 | NULL | NULL |
| 5 | egon | 3000 | female | 15 | 204 | NULL | NULL |
| 8 | qinjun | 41239 | male | 23 | 204 | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | NULL | 202 | 銷售 |
| NULL | NULL | NULL | NULL | NULL | NULL | 203 | 運營 |
+------+----------+--------+--------+------+--------+------+----------+
案例: 選出每個部門及其員工平均年齡
select department.post, avg(age) from employee inner join department on employee.dep_id = department.id
group by employee.post;
Output:
+----------+----------+
| post | avg(age) |
+----------+----------+
| 人力資源 | 42.0000 |
| 技術 | 41.0000 |
+----------+----------+
子查詢
-- 選出employee中技術部門的員工資訊
select * from employee where dep_id =
(select id from department where post = "技術");
Output:
+----+----------+--------+--------+-----+--------+
| id | name | salary | sex | age | dep_id |
+----+----------+--------+--------+-----+--------+
| 1 | Mokeke | 100000 | female | 18 | 200 |
| 3 | xiaoergu | 3000 | female | 27 | 200 |
| 7 | peiyao | 5000 | male | 78 | 200 |
+----+----------+--------+--------+-----+--------+
-- 選出employee中技術部門和銷售部門的員工資訊
select * from employee where dep_id in
(select id from department where post = "技術" or post = "銷售");
Output:
+----+----------+--------+--------+-----+--------+
| id | name | salary | sex | age | dep_id |
+----+----------+--------+--------+-----+--------+
| 1 | Mokeke | 100000 | female | 18 | 200 |
| 3 | xiaoergu | 3000 | female | 27 | 200 |
| 7 | peiyao | 5000 | male | 78 | 200 |
+----+----------+--------+--------+-----+--------+
子查詢運算符: all. any. exists
select * from department
where exists(
select * from employee where employee.dep_id = department.id
);
Output:
+------+----------+
| id | post |
+------+----------+
| 200 | 技術 |
| 201 | 人力資源 |
+------+----------+
單表和鏈表知識點總結
1. 多表查詢
鏈表查詢
select * from t1 inner join t2
on t1.欄位 = t2.欄位;
select * from t1 left join t2
on t1.欄位 = t2.欄位;
select * from t1 right join t2
on t1.欄位 = t2.欄位;
select * from t1 left join t2
on t1.欄位 = t2.欄位
union
select * from t1 right join t2
on t1.欄位 = t2.欄位;
完整的select查詢:
select distinct 欄位1, 欄位2 from t1 inner join t2
on t1.欄位 = t2.欄位
where 條件
group by 分組欄位
having 條件
order by 排序欄位
limit n;
子查詢
select * from t1 where id in (select id from t2);
select * from t1 where id = any(select id from t2);
select * from t1 where id > any(select id from t2);
select * from r1 where id > all(select id from t2);
select * from t2 where exists (...);
not in 不支援null
判斷為空可以是 dep_id is not null
資料庫其他
pymysql 和sql注入問題
# -*- encoding:utf-8 -*-
# @time: 2022/9/7 15:29
# @author: Maxs_hu
import pymysql
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
database='spader',
user='root',
password='th20190410'
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 可以使後面返回字典格式. 後面好提取
rows = cursor.execute("select * from employee") # 返回條數
cursor.scroll(3, mode='relative') # 相對當前位置移動
cursor.scroll(2, mode='absolute') # 絕對位置移動
res = cursor.fetchall() # 從迭代器中將數據取出
# res1 = cursor.fetchone() # 取一個
# res2 = cursor.fetchone()
# res3 = cursor.fetchone()
# res4 = cursor.fetchmany(3) # 取many個
print(res)
cursor.close()
conn.close()
# ----sql注入問題----
import pymysql
username = input("請輸入用戶名>>>").strip()
password = input("請輸入密碼>>>").strip()
conn = pymysql.connect(
host="127.0.0.1",
port=3306,
database="spader",
user="root",
password="th20190410"
)
cursor = conn.cursor()
# sql = 'select * from user_info where name = "%s" and password = "%s"' % (username, password)
# 會出現sql注入現象. 主要是就符號讓sql語句改變了意思.
# 1. Maxs_hu" --fjadklfja 後面的"會和前面形成閉合. -- 會將後面的都注釋
# 2. 還有更變態的 xxx" or 1=1 -- fjdskfjasd 可以登錄成功
rows = cursor.execute("select * from user_info where name = %s and password = %s", args=(username, password))
# print(rows)
if rows:
print("login successful")
else:
print('username or password error')
# ---- 增刪改查操作 ----
import pymysql
conn = pymysql.connect(
host="127.0.0.1",
port=3306,
database="spader",
user='root',
password="th20190410"
)
cursor = conn.cursor()
rows = cursor.execute("insert into user_info values ('xxxx', 'fasdklfasdjk')")
res = cursor.fetchall()
print(res)
# 屬於第三種: 自動開啟事務. 但是要手動提交
# 第一種: 自動開啟事務和提交
# 第二種: 手動開啟事務和提交
conn.commit()
cursor.close()
conn.close()
相關函數文檔
配合sql語句一起使用. 函數較多. 需要的時候可以對著文檔查
-- date_format()函數
select date_format(sub_time, "%Y-%m"), count(id) from blog group by date_format(sub_time, "%Y-%m");
一些函數文檔總結:
一.聚合函數
AVG 返回組中值的平均值。空值將被忽略
BINARY_CHECKSUM 返回對表中的行或表達式列表計算的二進位校驗值。BINARY_CHECKSUM 可用於檢測表中行的更改
CHECKSUM 返回在表的行上或在表達式列表上計算的校驗值。CHECKSUM 用於生成哈希索引
CHECKSUM_AGG 返回組中值的校驗值。空值將被忽略
COUNT 返回組中項目的數量
COUNT_BIG 返回組中項目的數量。COUNT_BIG 的使用與 COUNT 函數相似。它們之間的唯一差別是它們的返回值：COUNT_BIG 總是返回 bigint 數據類型值，而 COUNT 則總是返回 int 數據類型值
GROUPING "是一個聚合函數,它產生一個附加的列，當用 CUBE 或 ROLLUP 運算符添加行時，附加的列輸出值為1，當所添加的行不是由 CUBE 或 ROLLUP 產生時，附加列值為0。
僅在與包含 CUBE 或 ROLLUP 運算符的 GROUP BY 子句相聯繫的選擇列表中才允許分組"
MAX 返回表達式的最大值
MIN 返回表達式的最小值
SUM 返回表達式中所有值的和，或只返回 DISTINCT 值。SUM 只能用於數字列。空值將被忽略
STDEV 返回給定表達式中所有值的統計標準偏差
STDEVP 返回給定表達式中所有值的填充統計標準偏差
VAR 返回給定表達式中所有值的統計方差。
VARP 返回給定表達式中所有值的填充的統計方差。
二.數學函數
ABS 返回給定數字表達式的絕對值
ACOS 返回以弧度表示的角度值，該角度值的餘弦為給定的 float 表達式；本函數亦稱反餘弦。
ASIN 返回以弧度表示的角度值，該角度值的正弦為給定的 float 表達式；亦稱反正弦
ATAN 返回以弧度表示的角度值，該角度值的正切為給定的 float 表達式；亦稱反正切
ATN2 返回以弧度表示的角度值，該角度值的正切介於兩個給定的 float 表達式之間；亦稱反正切
CEILING 返回大於或等於所給數字表達式的最小整數
COS 一個數學函數，返回給定表達式中給定角度（以弧度為單位）的三角餘弦值
COT 一個數學函數，返回給定 float 表達式中指定角度（以弧度為單位）的三角餘切值
DEGREES 當給出以弧度為單位的角度時，返回相應的以度數為單位的角度
EXP 返回所給的 float 表達式的指數值
FLOOR 返回小於或等於所給數字表達式的最大整數
LOG 返回給定 float 表達式的自然對數
LOG10 返回給定 float 表達式的以 10 為底的對數
PI 返回 PI 的常量值
POWER 返回給定表達式乘指定次方的值
RADIANS 對於在數字表達式中輸入的度數值返回弧度值
RAND 返回 0 到1 之間的隨機float 值
ROUND 返回數字表達式並四捨五入為指定的長度或精度
SIGN 返回給定表達式的正 (+1)、零 (0) 或負 (-1) 號
SIN 以近似數字 (float) 表達式返回給定角度（以弧度為單位）的三角正弦值
SQUARE 返回給定表達式的平方
SQRT 返回給定表達式的平方根
TAN 返回輸入表達式的正切值
三.日期函數
DATEADD 在向指定日期加上一段時間的基礎上，返回新的 datetime 值。
DATEDIFF 返回跨兩個指定日期的日期和時間邊界數
DATENAME 返回代表指定日期的指定日期部分的字元串
DATEPART 返回代表指定日期的指定日期部分的整數
DAY 返回代表指定日期的天的日期部分的整數
GETDATE 按 datetime 值的 Microsoft® SQL Server™ 標準內部格式返回當前系統日期和時間
GETUTCDATE 返回表示當前 UTC 時間（世界時間坐標或格林尼治標準時間）的 datetime 值
MONTH 返回代表指定日期月份的整數
YEAR 返回表示指定日期中的年份的整數
四.系統函數
APP_NAME 返回當前會話的應用程式名稱（如果應用程式進行了設置）。
CASE 表達式 計算條件列表並返回多個可能結果表達式之一（詳見PPT資料）
CAST 和 CONVERT 將某種數據類型的表達式顯式轉換為另一種數據類型（詳見PPT資料）
COALESCE 返回其參數中第一個非空表達式
COLLATIONPROPERTY 返回給定排序規則的屬性
CURRENT_TIMESTAMP 返回當前的日期和時間。此函數等價於 GETDATE()
CURRENT_USER 返回當前的用戶。此函數等價於 USER_NAME()
DATALENGTH 返回任何表達式所佔用的位元組數
@@ERROR 返回最後執行的 Transact-SQL 語句的錯誤程式碼
fn_helpcollations 返回 Microsoft® SQL Server™ 2000 支援的所有排序規則的列表。
fn_servershareddrives 返回由群集伺服器使用的共享驅動器名稱
fn_virtualfilestats 返回對資料庫文件（包括日誌文件）的 I/O 統計
FORMATMESSAGE 從 sysmessages 現有的消息構造消息。FORMATMESSAGE 與 RAISERROR 語句的功能相似；但 RAISERROR 立即輸出消息而 FORMATMESSAGE 返回編輯後的資訊供進一步處理
GETANSINULL 返回會話的資料庫的默認為空性。
HOST_ID 返回工作站標識號。
HOST_NAME 返回工作站名稱。
IDENT_CURRENT 返回為任何會話和任何作用域中的指定表最後生成的標識值。
IDENT_INCR 返回增量值（返回形式為 numeric(@@MAXPRECISION,0)），該值是在帶有標識列的表或視圖中創建標識列時指定的
IDENT_SEED 返回種子值（返回形式為 numeric(@@MAXPRECISION,0)），該值是在帶有標識列的表或視圖中創建標識列時指定的
@@IDENTITY 返回最後插入的標識值
IDENTITY（函數） 只用在帶有 INTO table 子句的 SELECT 語句中，以將標識列插入到新表中。儘管類似，但是 IDENTITY 函數不是與 CREATE TABLE 和 ALTER TABLE 一起使用的 IDENTITY 屬性。（自動采番時可以起到作用）
ISDATE 確定輸入表達式是否為有效的日期
ISNULL 使用指定的替換值替換 NULL
ISNUMERIC 確定表達式是否為一個有效的數字類型
NEWID 創建 uniqueidentifier 類型的唯一值
NULLIF 如果兩個指定的表達式相等，則返回空值。
PARSENAME 返回對象名的指定部分。可以檢索的對象部分有對象名、所有者名稱、資料庫名稱和伺服器名稱
PERMISSIONS 返回一個包含點陣圖的值，表明當前用戶的語句、對象或列許可權。
@@ROWCOUNT 返回受上一語句影響的行數
ROWCOUNT_BIG 返回受執行的最後一個語句影響的行數。該函數的功能與 @@ROWCOUNT 一樣，除非 ROWCOUNT_BIG 的返回類型是 bigint。
SCOPE_IDENTITY 返回插入到同一作用域中的 IDENTITY 列內的最後一個 IDENTITY 值。
SERVERPROPERTY 返回有關伺服器實例的屬性資訊
SESSIONPROPERTY 返回會話的 SET 選項設置
SESSION_USER 是一個 niladic 函數，允許在未指定默認值時將系統為當前會話的用戶名提供的值插入到表中。還允許在查詢、錯誤資訊等中使用用戶名
STATS_DATE 返回最後一次更新指定索引統計的日期
SYSTEM_USER 返回當前系統用戶名
@@TRANCOUNT 返回當前連接的活動事務數
USER_NAME 返回給定標識號的用戶資料庫用戶名
五.字元函數
ASCII 返回字元表達式最左端字元的ASCII程式碼值。
CHAR 將intASCII程式碼轉換為字元的字元串函數。
CHARINDEX 返回字元串中指定表達式的起始位置
DIFFERENCE 以整數返回兩個字元表達式的SOUNDEX值之差。
LEFT 返回字元串中從左邊開始指定個數的字元。
LEN 返回給定字元串表達式的字元（而不是位元組）個數，其中不包含尾隨空格
LOWER 將大寫字元數據轉換為小寫字元數據後返回字元表達式。
LTRIM 刪除起始空格後返回字元表達式
NCHAR 根據Unicode標準所進行的定義，用給定整數程式碼返回Unicode字元
PATINDEX 返回指定表達式中某模式第一次出現的起始位置；如果在全部有效的文本和字元數據類型中沒有找到該模式，則返回零
REPLACE 用第三個表達式替換第一個字元串表達式中出現的所有第二個給定字元串表達式
QUOTENAME 返回帶有分隔符的Unicode字元串，分隔符的加入可使輸入的字元串成為有效的Microsoft®SQLServer™分隔標識符。
REPLICATE 以指定的次數重複字元表達式
REVERSE 返回字元表達式的反轉。
RIGHT 返回字元串中從右邊開始指定個數的字元
RTRIM 截斷所有尾隨空格後返回一個字元串
SOUNDEX 返回由四個字元組成的程式碼(SOUNDEX)以評估兩個字元串的相似性
SPACE 返回由重複的空格組成的字元串
STR 由數字數據轉換來的字元數據
STUFF 刪除指定長度的字元並在指定的起始點插入另一組字元
SUBSTRING 返回字元、binary、text或image表達式的一部分
UNICODE 按照Unicode標準的定義，返回輸入表達式的第一個字元的整數值
UPPER 返回將小寫字元數據轉換為大寫的字元表達式
