MySQL-基礎語法

MySQL客戶端連接,系統自帶的命令列工具執行指令: mysql [-h 127.0.0.1] [-P 3306] -u root -p

SQL

SQL通用語法

• SQL陳述句可以單行或多行書寫，以分號結尾
• SQL陳述句可以使用空格/縮進來增強陳述句的可讀性
• MySQL資料庫的SQL陳述句不區分大小寫，關鍵字建議使用大寫
• 注釋：
  • 單行注釋：-- 注釋內容 或 # 注釋內容(MySQL特有)
  • 多行注釋： /* 注釋內容 */

DDL

create table 表名(
    欄位名1 欄位1型別[COMMENT 欄位1注釋],
    欄位名2 欄位2型別[COMMENT 欄位2注釋],
    ……
    欄位名n 欄位n型別[COMMENT 欄位n注釋]
)[COMMENT 表注釋];

MySQL中的資料型別有很多，主要分為三類：數值型別、字串型別、日期時間型別

DML

DML英文全稱是Data Manipulation Language(資料操作語言)，用來對資料庫中表的資料記錄進行增刪改操作

• 添加資料（INSERT）
• 修改資料（UPDATE）
• 洗掉資料（DELETE）

DQL

DQL英文全稱是Data Query Language(資料查詢語言)，資料查詢語言，用來查詢資料庫中表的記錄
語法:select 欄位串列 from 表名 where 條件串列;

select 欄位串列
from 表名串列
where 條件串列
group by 分組欄位串列
having 分組后條件串列
order by 排序欄位串列
limit 分頁引數

DQL執行順序:from → where → group by → select → order by → limit

DQL聚合查詢:將一列資料作為一個整體，進行縱向計算.語法:select 聚合函式(欄位串列) from 表名;
注意:null值不參與所有聚合函式運算

分組查詢: select 欄位串列 表名 [where 條件] group by 分組欄位名 [having 分組后的過濾條件];

執行順序:

1. where是分組之前進行過濾,不滿足where條件,不參與分組
2. 執行聚合函式
3. having是分組之后對結果進行過濾

注意:where不能對聚合函式進行判斷,而having可以;分組之后查詢的欄位一般為聚合函式和分組欄位,查詢其他欄位無任何意義

-- 查詢年齡小于45的員工,并根據作業地址分組,獲取員工數量大于等于3的作業地址
select workaddress,count(*) from emp where age<45 group by workaddress having count(*)>=3;
select workaddress,count(*) address from emp where age<45 group by workaddress having address>=3;

排序查詢: select 欄位串列 from 表名 order by 欄位名1 排序方式,欄位名2 排序方式;

排序方式有兩種:升序ASC(默認值);降序desc

注意:如果是多欄位排序,當第一個欄位值相同時,才會根據第二個欄位進行排序

分頁查詢: select 欄位串列 from 表名 limit 起始索引,查詢記錄數;

-- 查詢第一頁的員工資料,每頁展示10條記錄
select * from emp limit 0,10;
select * from emp limit 10;
-- 查詢第二頁的員工資料,每頁展示10條記錄
select * from emp limit 10,10;

注意

• 起始索引從0開始，起始索引 = （查詢頁碼 - 1）* 每頁顯示記錄數
• 如果查詢的是第一頁資料，起始索引可以省略，直接簡寫為 limit 10
• 分頁查詢是資料庫的方言，不同的資料庫有不同的實作，MySQL中是LIMIT

DCL

DCL英文全稱是Data ControlLanguage(資料控制語言),用來管理資料庫用戶,控制資料庫的訪問權限

主機名可以使用 % 通配

-- 創建用戶zhangsan,只能在當前主機localhost訪問,密碼為123456
create user 'zhangsan'@'localhost' identified by '123456';
-- 創建用戶lisi,可以在任意主機訪問該資料庫,密碼為123456
create user 'lisi'@'%' identified by '123456';
-- 修改用戶lisi的訪問密碼為1234
alter user 'lisi'@'%' identified with mysql_native_password by '123456';
-- 洗掉zhangsan@localhost用戶
drop user 'zhangsan'@'localhost';

MySQL中定義了很多種權限，但是常用的就以下幾種：

注意：

• 多個權限之間,使用逗號分隔
• 授權時,資料庫名和表名可以使用 * 進行通配,代表所有

-- 查詢權限
show grants for 'lisi'@'%';
-- 授予權限
grant all on *.* to 'lisi'@'%';
-- 撤銷權限
revoke all on test.* from 'lisi'@'%';

函式

函式是指一段可以直接被另一段程式呼叫的程式或代碼

使用函式: select 函式(引數);

-- 生成一個六位數的隨機驗證碼
select lpad(round(rand()*1000000,0),6,'0');

select curdate();
select curtime();
select now();
select year(now());
select month(now());
select day(now());
select date_add(now(),interval 70 day);
select datediff('2022-12-01','2022-11-20');

select if(true,'success','error'); -- success
select if(false,'success','error'); -- error
select ifnull('str','default'); -- str
select ifnull('','default'); -- 空
select ifnull(null,'default'); -- default


-- case when then else end
-- 需求:查詢emp表的員工姓名和作業地址(北京/上海——一線城市，其他——二線城市)
select
       name,
       ( case workaddress when '北京' then '一線城市' when '上海' then '一線城市' else '二線城市' end) as '作業地址'
from emp;


select
       id,
       name,
       (case when math >= 85 then '優秀' when math >= 60 then '及格' else '不及格' end)       '數學',
       (case when chinese >= 85 then '優秀' when chinese >= 60 then '及格' else '不及格' end) '語文',
       (case when english >= 85 then '優秀' when english >= 60 then '及格' else '不及格' end) '英語'
from score;

約束

約束是作用于表中欄位上的規則,用于限制存盤在表中的資料;從而保證資料庫中資料的正確、有效性和完整性

create table user(
    id int primary key auto_increment comment '主鍵且自動增長',
    name varchar(10) not null unique comment '姓名',
    age int check ( age > 0 && age <= 120 ) comment '年齡',
    status char(1) default '1' comment '狀態',
    gender char(1) comment '性別'
) comment '用戶表';

外鍵約束語法

-- 添加外鍵:創建表的同時添加外鍵
create table 表名(
    欄位名 資料型別,
    …
    [constraint] [外鍵名稱] foreign key (外鍵欄位名) references 主表(主表列名)
);

-- 添加外鍵
alter table 表名 add constraint 外鍵名稱 foreign key (外鍵欄位名) references 主表(主表列名);

-- 添加外鍵的同時指定洗掉或更新的行為
alter table 表名 add constraint 外鍵名稱 foreign key(外鍵欄位) references 主表名(主表欄位名) on update cascade on delete cascade;

-- 洗掉外鍵
alter table 表名 drop foreign key 外鍵名稱;

多表查詢

多表關系

• 一對多(多對一)
  • 案例: 部門與員工的關系;一個部門對應多個員工，一個員工對應一個部門
  • 實作: 在多的一方建立外鍵，指向一的一方的主鍵
• 多對多
  • 案例: 學生與課程的關系;一個學生可以選修多門課程，一門課程也可以供多個學生選擇
  • 實作: 建立第三張中間表，中間表至少包含兩個外鍵，分別關聯兩方主鍵
• 一對一
  • 案例: 用戶與用戶詳情的關系;一對一關系，多用于單表拆分，將一張表的基礎欄位放在一張表中，其他詳情欄位放在另一張表中，以提升操作效率
  • 實作: 在任意一方加入外鍵，關聯另外一方的主鍵，并且設定外鍵為唯一的(UNIQUE)

笛卡爾積:笛卡爾乘積是指在數學中，兩個集合A集合和B集合的所有組合情況，(在多表查詢時，需要消除無效的笛卡爾積)

連接分類:

• 內連接：相當于查詢A、B交集部分資料
• 外連接
  • 左外連接：查詢左表所有資料，以及兩張表交集部分資料
  • 右外連接：查詢右表所有資料，以及兩張表交集部分資料
• 自連接：當前表與自身的連接查詢，自連接必須使用表別名
  • 自連接查詢，可以是基本連接，還可以是內連接查詢或者外連接查詢
• 聯合查詢:就是把多次查詢的結果合并起來，形成一個新的查詢結果集
  • union all會將全部的資料直接合并在一起;union會對合并之后的資料去重
  • 對于聯合查詢的多張表的列數必須保持一致，欄位型別也需要保持一致
• 子查詢/嵌套查詢:SQL陳述句中嵌套SELECT陳述句，稱為嵌套查詢，又稱子查詢
  • 子查詢外部的陳述句可以是INSERT / UPDATE / DELETE / SELECT 的任何一個
  • 根據子查詢結果不同，分為
    • 標量子查詢(子查詢結果為單個值):常用的運算子有 = <> > >= < <=
    • 列子查詢(子查詢結果為一列):常用的運算子有 IN、NOT IN、ANY、SOME、ALL
    • 行子查詢(子查詢結果為一行):常用的運算子有 = 、<> 、IN 、NOT IN
    • 表子查詢(子查詢結果為多行多列):常用的運算子有 IN
  • 根據子查詢位置，分為：WHERE之后、FROM之后、SELECT之后

-- 標量子查詢
-- 需求1:查詢"銷售部"的所有員工資訊
select * from emp where dept_id = (select id from dept where name = '銷售部');
-- 需求2:查詢指定入職日期之后入職的員工資訊
select * from emp where entrydate > (select entrydate from emp where name='張三');

-- 列子查詢
-- 需求1:查詢銷售部和市場部的部門所有員工資訊
select * from emp where dept_id in (select id from dept where name='銷售部' or name='市場部');
-- 需求2:查詢比財務部所有人工資都高的員工資訊
select * from emp where salary > all (select salary from emp where dept_id = (select id from dept where name = '財務部'));
-- 需求3:查詢比研發部其中任意一人工資高的員工資訊
select * from emp where salary > any (select salary from emp where dept_id = (select id from dept where name = '研發部'));
select * from emp where salary > some (select salary from emp where dept_id = (select id from dept where name = '研發部'));

-- 行子查詢
-- 需求:查詢與'張三'的薪資及直屬領導相同的員工資訊
select * from emp where (salary,managerid) = (select salary,managerid from emp where name = '張三');

-- 表子查詢
-- 需求1:查詢與'張三','李四'的職位和薪資相同的員工資訊
select * from emp where (job,salary) in (select job,salary from emp where name = '張三' or name = '李四');
-- 需求2:查詢入職日期'2022-01-01' 之后的員工資訊及其部門資訊
select e.*,d.* from (select * from emp where entrydata > '2022-01-01' e left join dept d on e.dept_id = d.id);

事務

事務是一組操作的集合，它是一個不可分割的作業單位，事務會把所有的操作作為一個整體一起向系統提交或撤銷操作請求，即這些操作要么同時成功，要么同時失敗
默認MySQL的事務是自動提交的，也就是說，當執行一條DML陳述句，MySQL會立即隱式的提交事務

事務四大特性

事務四大特性

• 原子性（Atomicity）：事務是不可分割的最小操作單元，要么全部成功，要么全部失敗
• 一致性（Consistency）：事務完成時，必須使所有的資料都保持一致狀態
• 隔離性（Isolation）：資料庫系統提供的隔離機制，保證事務在不受外部并發操作影響的獨立環境下運行
• 持久性（Durability）：事務一旦提交或回滾，它對資料庫中的資料的改變就是永久的

注意：事務隔離級別越高，資料越安全，但是性能越低

-- 查看事務隔離級別
select @@transaction_isolation;

-- 設定事務隔離級別(session是當前會話有效;global是全部會話都有效)
set [session|global] transaction isolation level {read uncommitted | read committed | repeatable read | serializable};

本文來自博客園，作者：不二橘子醬，轉載請注明原文鏈接：https://www.cnblogs.com/marmaladeHY/p/15824658.html

標籤：其他

上一篇：MySQL-基礎語法

下一篇：將趨勢線添加到日期時間matplotlib折線圖