mysql資料庫操作語句整合

来源:https://www.cnblogs.com/kk328/archive/2018/10/08/9757311.html
-Advertisement-
Play Games

查看版本：select version();顯示當前時間：select now();註意：在語句結尾要使用分號;遠程連接一般在公司開發中，可能會將資料庫統一搭建在一臺伺服器上，所有開發人員共用一個資料庫，而不是在自己的電腦中配置一個資料庫運行命令mysql -hip地址 -uroot -p-h後面寫... ...


    查看版本：select version();
    顯示當前時間：select now();

    • 註意：在語句結尾要使用分號;

    遠程連接

    • 一般在公司開發中，可能會將資料庫統一搭建在一臺伺服器上，所有開發人員共用一個資料庫，而不是在自己的電腦中配置一個資料庫
    • 運行命令

    mysql -hip地址 -uroot -p

    • -h後面寫要連接的主機ip地址
    • -u後面寫連接的用戶名
    • -p回車後寫密碼

    資料庫操作

    • 創建資料庫

    create database 資料庫名 charset=utf8;

    • 刪除資料庫

    drop database 資料庫名;

    • 切換資料庫

    use 資料庫名;

    • 查看當前選擇的資料庫

    select database();

    表操作

    • 查看當前資料庫中所有表

    show tables;

    • 創建表
    • auto_increment表示自動增長
      create table 表名(列及類型);
      如：
      create table students(
      id int auto_increment primary key,
      sname varchar(10) not null
      );
    • 修改表

    alter table 表名 add|change|drop 列名 類型;
    如：
    alter table students add birthday datetime;

    • 刪除表

    drop table 表名;

    • 查看表結構

    desc 表名;

    • 更改表名稱

    rename table 原表名 to 新表名;

    • 查看表的創建語句

    show create table '表名';

    數據操作

    • 查詢

    select * from 表名

    • 增加

    全列插入：insert into 表名 values(...)
    預設插入：insert into 表名(列1,...) values(值1,...)
    同時插入多條數據：insert into 表名 values(...),(...)...;
    或insert into 表名(列1,...) values(值1,...),(值1,...)...;

    • 主鍵列是自動增長，但是在全列插入時需要占位，通常使用0，插入成功後以實際數據為準
    • 修改

    update 表名 set 列1=值1,... where 條件

    • 刪除

    delete from 表名 where 條件

    • 邏輯刪除，本質就是修改操作update

    alter table students add isdelete bit default 0;
    如果需要刪除則
    update students isdelete=1 where ...;

    備份與恢復

    數據備份

    • 進入超級管理員

    sudo -s

    • 進入mysql庫目錄

    cd /var/lib/mysql

    • 運行mysqldump命令

    mysqldump –uroot –p 資料庫名 > ~/Desktop/備份文件.sql;
    按提示輸入mysql的密碼

    數據恢復

    • 連接mysqk，創建資料庫
    • 退出連接，執行如下命令

    mysql -uroot –p 資料庫名 < ~/Desktop/備份文件.sql
    根據提示輸入mysql密碼

    查詢時消除重覆行

    • 在select後面列前使用distinct可以消除重覆的行

    select distinct gender from students;

    邏輯運算符

    • and
    • or
    • not
    • 查詢編號大於3的女同學

    select * from students where id>3 and gender=0;

    • 查詢編號小於4或沒被刪除的學生

    select * from students where id<4 or isdelete=0;

    模糊查詢

    • like
    • %表示任意多個任意字元
    • _表示一個任意字元
    • 查詢姓黃的學生

    select * from students where sname like '黃%';

    • 查詢姓黃並且名字是一個字的學生

    select * from students where sname like '黃_';

    • 查詢姓黃或叫靖的學生

    select * from students where sname like '黃%' or sname like '%靖%';

    範圍查詢

    • in表示在一個非連續的範圍內
    • 查詢編號是1或3或8的學生

    select * from students where id in(1,3,8);

    • between ... and ...表示在一個連續的範圍內
    • 查詢學生是3至8的學生

    select * from students where id between 3 and 8;

    • 查詢學生是3至8的男生

    select * from students where id between 3 and 8 and gender=1;

    空判斷

    • 註意：null與''是不同的
    • 判空is null
    • 查詢沒有填寫地址的學生

    select * from students where hometown is null;

    • 判非空is not null
    • 查詢填寫了地址的學生

    select * from students where hometown is not null;

    • 查詢填寫了地址的女生

    select * from students where hometown is not null and gender=0;

    優先順序

    • 小括弧，not，比較運算符，邏輯運算符
    • and比or先運算，如果同時出現並希望先算or，需要結合()使用

    分組

    • 按照欄位分組，表示此欄位相同的數據會被放到一個組中
    • 分組後，只能查詢出相同的數據列，對於有差異的數據列無法出現在結果集中
    • 可以對分組後的數據進行統計，做聚合運算
    • 語法：

    select 列1,列2,聚合... from 表名 group by 列1,列2,列3...

    • 查詢男女生總數

    select gender as 性別,count(*)
    from students
    group by gender;

    • 查詢各城市人數

    select hometown as 家鄉,count(*)
    from students
    group by hometown;

    分組後的數據篩選

    • 語法：

    select 列1,列2,聚合... from 表名
    group by 列1,列2,列3...
    having 列1,...聚合...

    • having後面的條件運算符與where的相同
    • 查詢男生總人數

    方案一
    select count(*)
    from students
    where gender=1;
    -----------------------------------
    方案二：
    select gender as 性別,count(*)
    from students
    group by gender
    having gender=1;

    對比where與having

    • where是對from後面指定的表進行數據篩選，屬於對原始數據的篩選
    • having是對group by的結果進行篩選

    聚合

    • 為了快速得到統計數據，提供了5個聚合函數
    • count(*)表示計算總行數，括弧中寫星與列名，結果是相同的
    • 查詢學生總數

    select count(*) from students;

    • max(列)表示求此列的最大值
    • 查詢女生的編號最大值

    select max(id) from students where gender=0;

    • min(列)表示求此列的最小值
    • 查詢未刪除的學生最小編號

    select min(id) from students where isdelete=0;

    • sum(列)表示求此列的和
    • 查詢男生的編號之後

    select sum(id) from students where gender=1;

    • avg(列)表示求此列的平均值
    • 查詢未刪除女生的編號平均值

    select avg(id) from students where isdelete=0 and gender=0;

    排序

    • 為了方便查看數據，可以對數據進行排序
    • 語法：

    select * from 表名
    order by 列1 asc|desc,列2 asc|desc,...

    • 將行數據按照列1進行排序，如果某些行列1的值相同時，則按照列2排序，以此類推
    • 預設按照列值從小到大排列
    • asc從小到大排列，即升序
    • desc從大到小排序，即降序
    • 查詢未刪除男生學生信息，按學號降序

    select * from students
    where gender=1 and isdelete=0
    order by id desc;

    • 查詢未刪除科目信息，按名稱升序

    select * from subject
    where isdelete=0
    order by stitle;

    獲取部分行

    • 當數據量過大時，在一頁中查看數據是一件非常麻煩的事情
    • 語法

    select * from 表名
    limit start,count

    • 從start開始，獲取count條數據
    • start索引從0開始

    示例：分頁

    • 已知：每頁顯示m條數據，當前顯示第n頁
    • 求總頁數：此段邏輯後面會在python中實現
      • 查詢總條數p1
      • 使用p1除以m得到p2
      • 如果整除則p2為總數頁
      • 如果不整除則p2+1為總頁數
    • 求第n頁的數據

    select * from students
    where isdelete=0
    limit (n-1)*m,m

    總結

    • 完整的select語句

    select distinct *
    from 表名
    where ....
    group by ... having ...
    order by ...
    limit star,count

    • 執行順序為：
      • from 表名
      • where ....
      • group by ...
      • select distinct *
      • having ...
      • order by ...
      • limit star,count
    • 實際使用中，只是語句中某些部分的組合，而不是全部

    多表操作之連接

    • 問：查詢每個學生每個科目的分數
    • 分析：學生姓名來源於students表，科目名稱來源於subjects，分數來源於scores表，怎麼將3個表放到一起查詢，並將結果顯示在同一個結果集中呢？
    • 答：當查詢結果來源於多張表時，需要使用連接查詢
    • 關鍵：找到表間的關係，當前的關係是
      • students表的id---scores表的stuid
      • subjects表的id---scores表的subid
    • 則上面問題的答案是：

    select students.sname,subjects.stitle,scores.score
    from scores
    inner join students on scores.stuid=students.id
    inner join subjects on scores.subid=subjects.id;

    • 結論：當需要對有關係的多張表進行查詢時，需要使用連接join

    連接查詢

    • 連接查詢分類如下：
      • 表A inner join 表B：表A與表B匹配的行會出現在結果中
      • 表A left join 表B：表A與表B匹配的行會出現在結果中，外加表A中獨有的數據，未對應的數據使用null填充
      • 表A right join 表B：表A與表B匹配的行會出現在結果中，外加表B中獨有的數據，未對應的數據使用null填充
    • 在查詢或條件中推薦使用“表名.列名”的語法
    • 如果多個表中列名不重覆可以省略“表名.”部分
    • 如果表的名稱太長，可以在表名後面使用' as 簡寫名'或' 簡寫名'，為表起個臨時的簡寫名稱

    練習

    • 查詢學生的姓名、平均分

    select students.sname,avg(scores.score)
    from scores
    inner join students on scores.stuid=students.id
    group by students.sname;

    • 查詢男生的姓名、總分

    select students.sname,avg(scores.score)
    from scores
    inner join students on scores.stuid=students.id
    where students.gender=1
    group by students.sname;

    • 查詢科目的名稱、平均分

    select subjects.stitle,avg(scores.score)
    from scores
    inner join subjects on scores.subid=subjects.id
    group by subjects.stitle;

    • 查詢未刪除科目的名稱、最高分、平均分

    select subjects.stitle,avg(scores.score),max(scores.score)
    from scores
    inner join subjects on scores.subid=subjects.id
    where subjects.isdelete=0
    group by subjects.stitle;

    子查詢

    • 查詢支持嵌套使用
    • 查詢各學生的語文、數學、英語的成績

    select sname,
    (select sco.score from scores sco inner join subjects sub on sco.subid=sub.id where sub.stitle='語文' and stuid=stu.id) as 語文,
    (select sco.score from scores sco inner join subjects sub on sco.subid=sub.id where sub.stitle='數學' and stuid=stu.id) as 數學,
    (select sco.score from scores sco inner join subjects sub on sco.subid=sub.id where sub.stitle='英語' and stuid=stu.id) as 英語
    from students stu;

    內置函數

    字元串函數

    • 查看字元的ascii碼值ascii(str)，str是空串時返回0

    select ascii('a');

    • 查看ascii碼值對應的字元char(數字)

    select char(97);

    • 拼接字元串concat(str1,str2...)

    select concat(12,34,'ab');

    • 包含字元個數length(str)

    select length('abc');

    • 截取字元串
      • left(str,len)返回字元串str的左端len個字元
      • right(str,len)返回字元串str的右端len個字元
      • substring(str,pos,len)返回字元串str的位置pos起len個字元

    select substring('abc123',2,3);

    • 去除空格
      • ltrim(str)返回刪除了左空格的字元串str
      • rtrim(str)返回刪除了右空格的字元串str
      • trim([方向 remstr from str)返回從某側刪除remstr後的字元串str，方向詞包括both、leading、trailing，表示兩側、左、右

    select trim(' bar ');
    select trim(leading 'x' FROM 'xxxbarxxx');
    select trim(both 'x' FROM 'xxxbarxxx');
    select trim(trailing 'x' FROM 'xxxbarxxx');

    • 返回由n個空格字元組成的一個字元串space(n)

    select space(10);

    • 替換字元串replace(str,from_str,to_str)

    select replace('abc123','123','def');

    • 大小寫轉換，函數如下
      • lower(str)
      • upper(str)

    select lower('aBcD');

    數學函數

    • 求絕對值abs(n)

    select abs(-32);

    • 求m除以n的餘數mod(m,n)，同運算符%

    select mod(10,3);
    select 10%3;

    • 地板floor(n)，表示不大於n的最大整數

    select floor(2.3);

    • 天花板ceiling(n)，表示不小於n的最大整數

    select ceiling(2.3);

    • 求四捨五入值round(n,d)，n表示原數，d表示小數位置，預設為0

    select round(1.6);

    • 求x的y次冪pow(x,y)

    select pow(2,3);

    • 獲取圓周率PI()

    select PI();

    • 隨機數rand()，值為0-1.0的浮點數

    select rand();

    • 還有其它很多三角函數，使用時可以查詢文檔

    日期時間函數

    • 獲取子值，語法如下
      • year(date)返回date的年份(範圍在1000到9999)
      • month(date)返回date中的月份數值
      • day(date)返回date中的日期數值
      • hour(time)返回time的小時數(範圍是0到23)
      • minute(time)返回time的分鐘數(範圍是0到59)
      • second(time)返回time的秒數(範圍是0到59)

    select year('2016-12-21');

    • 日期計算，使用+-運算符，數字後面的關鍵字為year、month、day、hour、minute、second

    select '2016-12-21'+interval 1 day;

    • 日期格式化date_format(date,format)，format參數可用的值如下
      • 獲取年%Y，返回4位的整數
        *　獲取年%y，返回2位的整數
        *　獲取月%m，值為1-12的整數
      • 獲取日%d，返回整數
        *　獲取時%H，值為0-23的整數
        *　獲取時%h，值為1-12的整數
        *　獲取分%i，值為0-59的整數
        *　獲取秒%s，值為0-59的整數

    select date_format('2016-12-21','%Y %m %d');

    • 當前日期current_date()

    select current_date();

    • 當前時間current_time()

    select current_time();

    • 當前日期時間now()

    select now();

    視圖

    • 對於複雜的查詢，在多次使用後，維護是一件非常麻煩的事情
    • 解決：定義視圖
    • 視圖本質就是對查詢的一個封裝
    • 定義視圖

    create view stuscore as
    select students.*,scores.score from scores
    inner join students on scores.stuid=students.id;

    • 視圖的用途就是查詢

    select * from stuscore;

    事務

    • 當一個業務邏輯需要多個sql完成時，如果其中某條sql語句出錯，則希望整個操作都退回
    • 使用事務可以完成退回的功能，保證業務邏輯的正確性
    • 事務四大特性(簡稱ACID)
      • 原子性(Atomicity)：事務中的全部操作在資料庫中是不可分割的，要麼全部完成，要麼均不執行
      • 一致性(Consistency)：幾個並行執行的事務，其執行結果必須與按某一順序串列執行的結果相一致
      • 隔離性(Isolation)：事務的執行不受其他事務的干擾，事務執行的中間結果對其他事務必須是透明的
      • 持久性(Durability)：對於任意已提交事務，系統必須保證該事務對資料庫的改變不被丟失，即使資料庫出現故障
    • 要求：表的類型必須是innodb或bdb類型，才可以對此表使用事務
    • 查看表的創建語句

    show create table students;

    • 修改表的類型

    alter table '表名' engine=innodb;

    • 事務語句

    開啟begin;
    提交commit;
    回滾rollback;

    示例1

    • 步驟1：打開兩個終端，連接mysql，使用同一個資料庫，操作同一張表

    終端1：
    select * from students;
    ------------------------
    終端2：
    begin;
    insert into students(sname) values('張飛');

    • 步驟2

    終端1：
    select * from students;

    • 步驟3

    終端2：
    commit;
    ------------------------
    終端1：
    select * from students;

    示例2

    • 步驟1：打開兩個終端，連接mysql，使用同一個資料庫，操作同一張表

    終端1：
    select * from students;
    ------------------------
    終端2：
    begin;
    insert into students(sname) values('張飛');

    • 步驟2

    終端1：
    select * from students;

    • 步驟3

    終端2：
    rollback;
    ------------------------
    終端1：
    select * from students;

    • 創建表的語句如下

    create table scores(
    id int primary key auto_increment,
    stuid int,
    subid int,
    score decimal(5,2)
    );

    外鍵

    • 思考：怎麼保證關係列數據的有效性呢？任何整數都可以嗎？
    • 答：必須是學生表中id列存在的數據，可以通過外鍵約束進行數據的有效性驗證
    • 為stuid添加外鍵約束

    alter table scores add constraint stu_sco foreign key(stuid) references students(id);

    • 此時插入或者修改數據時，如果stuid的值在students表中不存在則會報錯
    • 在創建表時可以直接創建約束

    create table scores(
    id int primary key auto_increment,
    stuid int,
    subid int,
    score decimal(5,2),
    foreign key(stuid) references students(id),
    foreign key(subid) references subjects(id)
    );

    外鍵的級聯操作

    • 在刪除students表的數據時，如果這個id值在scores中已經存在，則會拋異常
    • 推薦使用邏輯刪除，還可以解決這個問題
    • 可以創建表時指定級聯操作，也可以在創建表後再修改外鍵的級聯操作
    • 語法

    alter table scores add constraint stu_sco foreign key(stuid) references students(id) on delete cascade;

    • 級聯操作的類型包括：
      • restrict（限制）：預設值，拋異常
      • cascade（級聯）：如果主表的記錄刪掉，則從表中相關聯的記錄都將被刪除
      • set null：將外鍵設置為空
      • no action：什麼都不做

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • （二）補補你對操作系統的認識
    目錄（你將學到一下知識） 1.windows系統 2\. Mac OS系統 3.Linux系統 4.各系統的使用比例 1.windows系統 眾所周知，windows系統是目前最主流的系統，Microsoft Windows是美國微軟公司研發的一套操作系統，它問世於1985年，起初僅僅是Micros ...
  • gitlab相關
    1.gitlab的概述 1.gitlab是什麼 是一個用於倉庫管理系統的開源項目，使用Git作為代碼管理工具，併在此基礎上搭建起來的web服務。 基礎功能免費，高級功能收費 2.為什麼要使用gitlab 1.基礎功能開源，可自行搭建 2.可以進行許可權控制，使得代碼對部分人可見 3.gitlab使用方 ...
  • Linux中一些約定俗成的文件擴展名
    註：Linux中的所有內容均以文件的形式保存，但不依靠擴展名區分文件類型（根據許可權區分），約定俗成的文件擴展名是為了方便管理員對文件進行區分 壓縮包：“*.gz”、“*.bz2”、“*.tar.bz2”、“*.tgz”等，根據壓縮方式的不同設定不同的擴展名 二進位軟體包：“*.rpm” 網頁文件：“ ...
  • linux防火牆管理工具之iptables
    iptables策略與規則連 防火牆是由上而下的順序來讀取配置的策略規則，策略規則的設置有兩種：通（放行）、堵（阻止）。當預設策略設置為通時，就要設置拒絕規則，當預設策略為堵時，則要設置允許規則。 iptables服務把用於處理或過濾流量的策略條目稱為規則，多條規則組成一個規則鏈，規則連依據數據包處 ...
  • Cent os常見操作命令
    1.查看防火牆狀態：firewall-cmd –-state 2.關閉防火牆：systemctl stop firewalld.service 3.禁止防火牆開機啟動：systemctl disable firewalld.service 4.關閉selinux：vi /etc/selinux/co ...
  • macOS版TeamViewer 個人版被誤認為商業行為解決辦法
    問題描述： 這周突然發現自己 MacBook 上的 TeamViwer 出現 5 分鐘限制（即，只讓我遠程連接 5 分鐘，然而事實是 1 分鐘左右便會自動斷開，並且需要等待一段時間後才可以繼續連接），提示讓我購買序列號。原因為：自己個人使用過於頻繁，被懷疑為商業行為！！！ PS：下述的解決方案本人已 ...
  • 【http反向代理】多個功能變數名稱指向同一個ip的不同網站解決方法
    一個伺服器需要掛載多個項目【重點是都能通過功能變數名稱訪問】 實現原理： 1.當前市面上看到的一些伺服器，開放的埠一般都要求為 '80' 埠 所以80埠成了商用埠 2.功能變數名稱的綁定是綁定一個一般是綁定你的伺服器ip地址 3.使用伺服器的80埠攔截訪問的功能變數名稱是什麼跳轉至伺服器的其他 舉例 只有一臺服務 ...
  • shell基礎 -- 基本正則表達式
    正則表達式（Regular Expression，通常簡稱為 regex 或 RE）是一種表達方式，可以用它來查找匹配特定准則的文本。在許多編程語言中都有用到正則表達式，常用它來實現一些複雜的匹配。這裡簡單介紹一下 shell 中常用到的一些正則表達式。 一、什麼是正則表達式 正則表達式是對字元串進 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類