技術文章 » MySQL：一、MySQL基礎，數據庫基礎操作

MySQL：一、MySQL基礎，數據庫基礎操作

2020-08-14 19:09:36

一、數據庫簡介

  • 數據庫：DataBase【DB】，指的是長期儲存到計算機上的數據，按照一定順序組織，可以被各種使用者或者應用共用的數據集合。
  • 用於儲存數據的地方，可以視爲儲存數據的容器。

1.1 數據庫管理系統

  • DataBase Management System【DBMS】,能夠管理和操作數據庫的大型的軟體 。
  • 數據庫是CS模式:Client/Server
  • 作用：用於建立、使用和維護數據庫，對數據庫進行統一的管理和控制，爲了保證數據庫的安全性和完整性，使用者可以通過數據庫管理系統存取數據庫中的數據。

1.2 數據庫的應用

  • 涉及到大量的數據需要長期儲存，就可以使用數據庫。
  • 持久化： 數據持久化, 一般存在硬碟， MySQL
  • 快取： 臨時儲存， 一般可以存在記憶體, Redis

1.3 常見數據庫管理系統

  1. Oracle（甲骨文）: 目前比較成功的關係型數據庫管理系統，執行穩定，功能齊全，效能超羣，技術領先，主要應用在大型的企業數據庫領域, 收費。
  2. DB2: IBM（國際商業機器公司）的產品，伸縮性比較強。
  3. SQL Server: Microsoft的產品，軟體介面友好，易學易用，在操作性和互動性方面獨樹一幟。
  4. MySQL: 免費的數據庫系統，被廣泛應用於中小型應用系統，體積小，速度快，總體擁有成本低，開放原始碼，2008年被SUN收購，2009年SUN被Oracle收購 。

1.4 數據庫的安裝

  • 安裝步驟：
    如附件：https://download.csdn.net/download/weixin_44180787/12702303

  • 啓動和停止mysql服務：

    windows 開啓mysql: net start mysql57
    windows 關閉mysql: net stop mysql57

  • 進入MySQL：mysql -u root -p

  • 退出：exit、quit

二、SQL概述

  • Structure Query Language，結構化查詢語言。

2.1 數據庫伺服器、數據庫和表之間的關係

  • 表：爲了儲存應用實體中的數據，一般會給數據庫中建立表，一個數據庫可以同時管理多個表。

2.2 SQL的分類

  • DDL【Data Definition Language】,數據定義語言，使用者建立、修改、刪除表結構。
  • DML【Data Manipulation Language】,數據操作語言，用於對錶數據進行增刪改的操作。（重點）
  • DQL【Data Query Language】,數據查詢語言，用於負責表數據的查詢工作。（重點）
  • DCL【Data Control Language】：數據控制語言，用來定義存取許可權和安全級別。、

三、數據庫操作

3.1 DDL

  • 使用關鍵字：create alter drop
  • 注意：一般情況下，mysql關鍵字是大寫的，但是爲了方便，一般小寫

3.2 數據庫基礎操作

  1. 檢視所有數據庫：show databases;

  2. 建立數據庫：create database 數據庫名 charset=utf8;

  3. 進入數據庫：use 數據庫名;

  4. 檢視當前使用的數據庫名：select database();

  5. 刪除數據庫：drop database 數據庫名;

  6. 檢視所有表：show tables;

  7. 建立表(標準)：create table 表名(列欄位1,列欄位2……)
    create table user(
    id int primary key auto_increment,
    name varchar(20),
    age int
    );

    說明：
    1、primary key：主鍵，確保該欄位的數據不能重複。一個表可有多個主鍵，建議一個表一個主鍵。
    2、auto_increment: 自動增長(+1)，用於整數。只增不減，把前面生成的刪除後新增加的還是從後遞增。
    3、varchar(M): 可變字串型別。M取值：0~65535，varchar最大實際長度由最長行的大小決定。
    4、char(M)：固定長度的字串。必須指定長度，若輸入字串長度不夠，會在右邊用空格填充。

  8. 檢視錶結構：desc 表名;

  9. 查詢表數據：詳細如後面查詢部分；

  10. 刪除表：drop table 表名;

3.3 表的操作

3.3.1 增加數據：insert into

  • 語法：insert into 表名(列名1, 列名2, …) values(值1, 值2, ……)

  • 說明：

  1. 列名和列值的型別、個數以及順序一一對應
  2. 可以把列名當做Python中的形參，把列值當做實參
  3. 值不能超出列定義的長度
  4. 如果插入的是空值，寫Null/null
  5. 插入的是日期，和字串一樣，使用引號括起來 「1988-11-22 10:11:12」
  • 每次插入一行：
    insert into 表名(name, age) values(「馬雲」, 50);
  • 每次插入多行：
    insert into 表名(name, age) values(「馬化騰」, 40),(「馬明哲」,60);
  • 如果給每個欄位都賦值，就可以省略掉欄位的書寫：
    insert into 表名 values(100, 「馬冬梅」, 30)(101, 「馬冬梅」, 30),…;
3.3.2 查詢
1、基礎查詢：select 列名 from 表名
  • 讓數據庫發送查詢結果到用戶端查詢返回的結果其實是一張虛擬表。
  • 查詢所有列：select * from 表名;
  • 查詢指定列：select 列名 from 表名;
2、條件查詢
  • 主要結合where的使用
    between…and: 介於…和…之間
    and：邏輯與
    or：邏輯或
    in / not in：類似於Python中的成員運算子
    is / is not: 類似於Python中的身份運算子 , 常用語判斷null值， 如：name is null
  • 語法：select * from 表名 where 查詢條件;
    如：select * from student where id in(‘1’,‘2’,‘3’);
3、模糊查詢
  • where 子句中=表示精準查詢
  • like：一般情況下結合where子句使用
  • 萬用字元：
    _: 匹配任意一個字元
    %：匹配0~n個字元【n大於等於1】
  • 語法：select * from 表名 where 查詢列名
  • like ‘__c’;（查詢姓名由3個字元組成的記錄，並且最後一個字母爲c的記錄）
1.查詢姓名由3個字元組成的記錄，並且最後一個字母爲c的記錄：
select * from student where name like '__c';
2.查詢以a開頭的記錄：
select * from student where name like 'a%';
3.查詢姓名中包含b的記錄：
select * from student where name like '%b%';
4、欄位控制查詢
  • distinct: 去除重複查詢：
select distinct name from user;
  • as: 起別名
  • 用法 ：select 欄位 as 別名，as可省略。
select name as 姓名, age as 年齡 from user;
select name  姓名, age 年齡 from user;
3.3.3 更新數據：update
  • 語法：update 表名 set 欄位=「修改後」 where 修改物件;
1.將tom的薪水改爲3000元：
update worker set salary=3000 where name='tom';
2.在原數據基礎上增加：如工資加1000：
update worker set salary=salary+1000 where name='rose';
3.將所有員工的薪水修改爲5000：
update worker set salary=5000;
  • 注意：
    a.完全可以更新一個欄位或者多個欄位
    b.where相當於Python中的if語句
    c.可以指定任何條件到where子句中
    d.如果沒有where子句，則預設所有的行都被同時更新爲指定的操作（慎用！一般要結合where使用）。
3.3.4 刪除數據：
  • 注意：
  1. delete：刪除表中的指定數據，表結構還在，刪除之後的數據可以找回，對自動增加的欄位無影響。
  2. truncate：清空表中的數據，刪除的數據是不能找回的，執行速度比delete快，自動增加的欄位會重新計數。
  3. drop: 刪除表，數據和表結構都刪除。

delete、truncate是用來刪除表中數據的，不是刪除表；drop是刪除整個表！

1、delete
  • 語法：delete from 表名 where 物件;
  • 注意：
    a.如果where子句沒有指定，則預設將表中的數據全部刪除【慎用！】
    b.可以指定任何條件在where子句中。
1.刪除id爲102的數據：
delete from user where id=102;
2.刪除表中的所有數據：
mysql> delete from worker;

不帶where是刪除整個表的數據，但是不會刪除表，也不會清空auto_increment欄位計數，重新插入還是會從原計數開始遞增。

2、truncate
  • 語法：truncate table 表名;
1.刪除表數據worker：
truncate table worker;
  • 注意：
    1、truncate table 用於刪除表中的所有行，而不記錄單個行刪除操作。truncate table 與沒有 where 子句的 delete 語句類似；但是，truncate table 速度更快，使用的系統資源和事務日誌資源更少。
    2、Truncate是一個能夠快速清空資料表內所有資料的SQL語法。並且能針對具有自動遞增值的欄位，做計數重置歸零重新計算的作用。
3、drop：刪除表
  • 語法：drop table 表名;
1.刪除表worker：
drop table worker;

3.4 where語句

  • 注意：where子句其實就是一個操作符，類似於Python中的 if 語句，可以做數據的篩選。

    操作符 說明
    = 相等
    <> / != 不相等
    < 小於
    <= 小於等於
    > 大於
    >= 大於等於
    IN(A，B) A 和 B 之間
    between A and B 位於兩值之間
    AND 連線多個表達式 並且的關係

四、排序、分組查詢、分頁查詢

4.1 排序：order by

  • order by：指定數據返回的順序
    asc：ascending，升序
    desc: descending，降序
  • 語法：select from 表 order by 條件；
1.查詢所有的記錄，按照年齡升序排序：
select * from student order by age asc;
2. 降序
select * from user order by age desc;
3.多欄位組合排序
select * from user order by name desc, age asc;
4.先篩選再排序
select * from user where id>1 order by age asc;
4.1.1 組合函數
  • 聚合函數主要用來做縱向運算 。
  • 查詢關鍵字的書寫順序：select 聚合函數 from where order by。
  • count()：統計指定列不爲null的記錄行數
select count(name) from user;
select count(*) from user;
select count(age) from user;
  • sum()：計算指定列的數值和
select sum(age) from student;
  • max()：指定列的最大值
select max(age) from user;
  • min()：指定列的最小值
select min(age) from user;
  • avg()：指定列的平均值
select avg(age) from user;

4.2 分組查詢：group by

  • group by：分組查詢
  • having：有…表示條件，類似於where的用法。

group by分組後將表數據分成幾部分，用count()這些聚合函數進行計算時，每個部分會單獨計算一次。

1.查詢各個部門的人數；
select deptno, count(*) from emp group by deptno;
2. 查詢每個部門的部門編號和每個部門的工資和；
select deptno, sum(sal) from emp group by deptno;
3. 查詢每個部門的部門編號和每個部門的人數；
select deptno, count(*) from emp group by deptno;
4.查詢每個部門的部門編號和每個部門工資大於1500的人數；
select deptno, count(*) from emp where sal>1500 group by deptno;
5.查詢工資總和大於7000的部門編號以及工資和；
select deptno,sum(sal) from emp group by deptno having sum(sal)>7000;
6.查詢員工工資大於1500，工資總和大於6000的部門編號和工資和；
select deptno, sum(sal) from emp where sal>1500 group by deptno having sum(sal)>6000;
  • 總結：
    having和where的區別：
    a.二者都表示對數據執行條件
    b.having是在分組之後對數據進行過濾
    where是在分組之前對數據進行過濾
    c.having後面可以使用聚合函數
    where後面不可以使用聚合函數

4.3 分頁查詢：limit

  • limit：用來限定查詢的起始行，以及總行數。

llimit a, b;
a：表示頁數，頁碼爲：a=（a-1）*b
b：表示每頁顯示的數量。

1.取前3行內容：0預設不寫
select * from emp limit 3;
2.跳過前3個，再取4行：
select * from emp limit 3,4;
select * from emp limit 6,3;

select deptno, sum(sal) from emp where sal>1500 group by deptno having sum(sal)>2000 order by sum(sal) asc limit 2;

五、總結

  • 書寫順序：select =》from =》where =》group by =》having =》order by =》limit

  • 執行順序：from =》where =》group by =》having =》order by =》limit =》select

  • where 和having的區別：

  1. 都是進行數據條件篩選；
  2. where是在進行分組之前對數據篩選，having是在分組之後再篩選；
  3. where不可以使用聚合函數，having後面可以使用聚合函數；
  4. having需要結合group by使用。