天天看點
傳回

SQL篇SQL

SQL

MySQL内置功能

  1. 連接配接資料庫
    • -u
    • -p
    • -S
    • -h
    • -P
    • -e
    • <
    示例：
    1. mysql -u root -p -S /tmp/mysql.sock
    2. mysql -u root -p -h 10.0.0.51 -P3306
    3. -e 免互動執行 sql 語句

      [root@db01 ~]# mysql -uroot -p -e "show databases;"
    4. < 導入資料

      [root@db01 ~]# mysql -uroot -p123 /root/world.sql
  2. 内置指令
    • help 幫助
    • \c ctrl+c 結束上個指令運作
    • \q quit exit ctrl+d 退出
    • \G 豎行顯示
    • source 恢複備份檔案

SQL 基礎應用

  1. 介紹

    結構化的查詢語言

    關系型資料庫通用的指令

    遵循SQL92标準（SQL_MODE）

  2. 常用種類

    DDL 資料定義語言

    DCL 資料控制語言

    DML 資料操作語言

    DQL 資料查詢語言

  3. 資料庫的邏輯結構
    • 庫
      • 庫名
      • 庫屬性(字元集，排序規則)
    • 表
      • 表名
      • 表屬性(存儲引擎類型，字元集，排序規則)
      • 列名
      • 列屬性(資料類型，限制，其他屬性)
      • 資料行

  4. 字元集(charset)

    檢視支援字元集

    show charset
    • utf8 三個字元
    • utfmb4 四個字元(支援emoji)

  5. 排序規則(collation)

    檢視排序規則

    show collation

     英文字元串的大小寫不敏感
    • utf8mb4_general_ci 大小寫不敏感
    • utf8mb4_bin 大小寫敏感(存拼音,日文)
  6. 資料類型
    • 數字：tinyint int
    • 字元串:

      • char(100)

        定長字元串類型,不管字元串長度多長,都立即配置設定100個字元長度的存儲空間,未占滿的空間使用"空格"填充

      • varchar(100)

        變長字元串類型,每次存儲資料之前,都要先判斷一下長度,按需配置設定此盤空間.

        會單獨申請一個字元長度的空間存儲字元長度(少于255,如果超過255以上,會占用兩個存儲空間)

      如何選擇這兩個資料類型?
      1. 少于255個字元串長度,定長的列值,選擇char
      2. 多于255字元長度,變長的字元串,可以選擇varchar
    • 枚舉

      • address enum('sz','sh','bj'.....)

        可能會影響到索引的性能

    • 時間

      • datetime

        範圍為從 1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999

      • timestamp

        範圍為從 1970-01-01 00:00:00.000000 至 2038-01-19 03:14:07.999999

    • 二進制

DDL

  1. 庫的定義

    • 建立資料庫

      CREATE DATABASE zabbix CHARSET utf8mb4 COLLATE utf8mb4_bin;

    • 檢視庫情況

      SHOW DATABASES;

      SHOW CREATE DATABASE zabbix;

    • 删除資料庫(不代表生産操作)

      DROP DATABASE oldguo;

    • 修改資料庫字元集

      注意: 一定是從小往大了改,比如utf8--->utf8mb4.

      目标字元集一定是源字元集的嚴格超級.

      CREATE DATABASE oldguo;

      SHOW CREATE DATABASE oldguo;

      ALTER DATABASE oldguo CHARSET utf8mb4;

  2. 庫定義規範
    • 庫名使用小寫字元
    • 庫名不能以數字開頭
    • 不能為資料庫内部關鍵字
    • 必須設定字元集
  3. 表的定義

    • 建表

      表名, 列名, 列屬性, 表屬性

    • 列屬性
      • PRIMARY KEY : 主鍵限制,表中隻能有一個,非空且唯一.
      • NOT NULL : 非空限制,不允許空值
      • UNIQUE KEY : 唯一鍵限制,不允許重複值
      • DEFAULT : 一般配合 NOT NULL 一起使用.
      • UNSIGNED : 無符号,一般是配合數字列,非負數
      • COMMENT : 注釋
      • AUTO_INCREMENT : 自增長的列 
    CREATE TABLE stu (
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '學号',
sname VARCHAR(255) NOT NULL  COMMENT '姓名',
age TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年齡',
gender ENUM('m','f','n') NOT NULL DEFAULT 'n' COMMENT '性别',
intime DATETIME NOT NULL DEFAULT NOW() COMMENT '入學時間'
)ENGINE INNODB CHARSET utf8mb4;
  4. 建表規範
    1. 表名小寫字母,不能數字開頭
    2. 不能是保留字元,使用和業務有關的表名
    3. 選擇合适的資料類型及長度
    4. 每個列設定 NOT NULL + DEFAULT ;對于資料0填充,對于字元使用有效字元串填充
    5. 每個列設定注釋
    6. 表必須設定存儲引擎和字元集
    7. 主鍵列盡量是無關列數字列,最好是自增長
    8. enum類型不要儲存數字,隻能是字元串類型

  5. 查詢表資訊

    SHOW TABLES;

    SHOW CREATE TABLE stu;

    DESC stu;

  6. 建立一個表結構一樣的表

    CREATE TABLE test LIKE stu;

  7. 删表(不代表生産操作)

    DROP TABLE test;

  8. 修改
    • 在stu表中添加qq列 
      DESC stu;
ALTER TABLE stu ADD qq VARCHAR(20) NOT NULL COMMENT 'qq号';
    • 在sname後加微信列 
      ALTER TABLE stu ADD wechat VARCHAR(64) NOT NULL UNIQUE COMMENT '微信号' AFTER sname;
    • 在id列前加一個新列num 
      ALTER TABLE stu ADD num INT NOT NULL UNIQUE COMMENT '身份證' FIRST ;
DESC  stu;
    • 把剛才添加的列都删掉(危險,不代表生産操作 
      ALTER TABLE stu DROP num;
DESC stu;
ALTER TABLE stu DROP qq;
ALTER TABLE stu DROP wechat;
    • 修改sname資料類型的屬性 
      DESC stu;
ALTER TABLE stu MODIFY sname VARCHAR(64) NOT NULL COMMENT '姓名';
    • 将gender 改為 sex 資料類型改為 CHAR 類型 
      ALTER TABLE stu  CHANGE gender sex CHAR(4) NOT NULL COMMENT '性别';

DCL

grant

revoke

DML

插入資料(insert)

  • 簡單 
    INSERT stu VALUES(1,'zs',18,'m',NOW());
SELECT * FROM stu;
  • 規範 
    NSERT INTO stu(id,sname,age,sex,intime)
VALUES (2,'ls',19,'f',NOW());
  • 錄入多行 
    INSERT INTO stu(sname,age,sex)
VALUES 
('aa',11,'m'),
('bb',12,'f'),
('cc',13,'m');
  • update (一定加where條件) 
    UPDATE stu SET sname='aaa';
SELECT * FROM stu;
UPDATE stu SET sname='bb' WHERE id=6;
  • delete (一定要加where條件) 
    DELETE FROM stu;
DELETE FROM stu WHERE id=9;
  • 生産中屏蔽delete功能，使用update替代delete 
    ALTER TABLE stu ADD is_del TINYINT DEFAULT 0 ;
UPDATE stu SET is_del=1 WHERE id=7;
SELECT * FROM stu WHERE is_del=0;

DQL

  1. DQL介紹
    • SELECT
    • SHOW
  2. SELECT 語句的應用

    1. SELECT單獨使用的情況

      mysql> select @@basedir;

      mysql> select @@port;

      mysql> select @@innodb_flush_log_at_trx_commit;

      mysql> show variables like 'innodb%';

      mysql> select database();

      mysql> select now();

    2. SELECT通用文法表(單表)

      select 列

      from 表

      where 條件

      group by 條件

      having 條件

      order by 條件

      limit

    3. 學習環境說明
      • world 資料庫
        • city 城市表
        • country 國家表
        • countrylanguage 語言表
      • city表結構
        • ID 城市序号
        • name 城市名代号
        • countrycode 國家
        • district 區域
        • population 人口
    4. SELECT 配合 FROM 子句使用

      SELECT 列 from 表
      • 查詢表中所有的資訊(生産中幾乎是沒有這種需求的) 
        USE world ;
SELECT  id,NAME ,countrycode ,district,population   FROM  city;

或者

SELECT  *   FROM city;
      • 查詢表中 name 和population的值 
        SELECT  NAME ,population   FROM  city;
    5. SELECT 配合 WHERE 子句使用

      select 列 from 表 where 過濾條件

       示例:

      • 等值條件查詢

        查詢中國所有的城市名和人口數

        SELECT  NAME,population FROM city 
WHERE countrycode='CHN';

      • 比較判斷查詢

        世界上小于100人的城市名和人口數

        SELECT NAME,population FROM city 
WHERE population<100;

      • 邏輯連接配接符

        查詢中國人口數量大于1000w的城市名和人口

        SELECT NAME,population FROM city 
WHERE countrycode='CHN' AND population>8000000;
        查詢中國或美國的城市名和人口數 
        SELECT NAME,population FROM city 
WHERE countrycode='CHN' OR countrycode='USA';
        查詢人口數量在500w到600w之間的城市名和人口數 
        SELECT NAME,population FROM city 
WHERE population>5000000 AND population<6000000;

或者

SELECT NAME,population FROM city 
WHERE population BETWEEN 5000000 AND 6000000;

      • 模糊查詢

        查詢一下contrycode中帶有CH開頭,城市資訊

        SELECT * FROM city  
WHERE countrycode  LIKE 'CH%';
        TIP:不要出現類似于 %CH%,前後都有百分号的語句,因為不走索引,性能極差。如果業務中有大量需求,我們用"ES"來替代。

      • in 語句

        查詢中國或美國的城市資訊

        SELECT NAME,population FROM city 
WHERE countrycode='CHN' OR countrycode='USA';

或者

SELECT NAME,population FROM city 
WHERE countrycode IN ('CHN' ,'USA');

    6. GROUP BY

      将某列中有共同條件的資料行,分成一組,然後在進行聚合函數操作

      統計每個國家,城市的個數

      SELECT  countrycode ,COUNT(id) FROM city
GROUP BY countrycode;
      統計每個國家的總人口數. 
      SELECT countrycode,SUM(population) FROM city 
GROUP BY countrycode;
      統計每個 國家 省 的個數 
      SELECT  countrycode,COUNT(DISTINCT district)     FROM city
GROUP BY countrycode;
      統計中國 每個省的總人口數 
      SELECT district, SUM(population)     FROM  city 
WHERE countrycode='CHN'
GROUP BY district ;
      統計中國 每個省城市的個數 
      SELECT district, COUNT(NAME)     FROM  city 
WHERE countrycode='CHN'
GROUP BY distric
      統計中國 每個省城市的名字清單GROUP_CONCAT() 
      SELECT district, GROUP_CONCAT(NAME)     FROM  city 
WHERE countrycode='CHN'
GROUP BY district ;
      anhui : hefei,huaian .... 
      SELECT CONCAT(district,":" ,GROUP_CONCAT(NAME))     FROM  city 
WHERE countrycode='CHN'
GROUP BY district ;

    7. ORDER BY

      統計所有國家的總人口數量,将總人口數大于5000w的過濾出來,并且按照從大到小順序排列

      SELECT countrycode,SUM(population) FROM city
GROUP BY countrycode
HAVING SUM(population)>50000000
ORDER BY SUM(population) DESC ;

    8. LIMIT

      統計所有國家的總人口數量,将總人口數大于5000w的過濾出來,并且按照從大到小順序排列,隻顯示前三名

      SELECT countrycode,SUM(population) FROM city
GROUP BY countrycode
HAVING SUM(population)>50000000
ORDER BY SUM(population) DESC 
LIMIT 3 OFFSET 0;

      LIMIT M,N :跳過M行,顯示一共N行

      LIMIT Y OFFSET X: 跳過X行,顯示一共Y行

    9. 小結 
      select disctrict , count(name) from    city  
where countrycode='CHN'
group by  district 
having  count(name) >10
order by  count(name) desc 
limit 3;

    10. union 和 union all

      多個結果集合并查詢的功能

      查詢中或者美國的城市資訊

      SELECT * FROM city WHERE countrycode='CHN' OR countrycode='USA';
      改寫 
      SELECT * FROM city WHERE countrycode='CHN'
UNION ALL 
SELECT * FROM city WHERE countrycode='USA';

      union 和 union all 的差別 ?

      union all 不做去重複

      union 會做去重操作

    11. 練習題
      1. 統計中國每個省的總人口數，隻列印總人口數小于100w的 
        SELECT   district ,SUM(population)  FROM  city 
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population)<1000000;
      2. 檢視中國所有的城市，并按人口數進行排序(從大到小) 
        SELECT * FROM city WHERE countrycode='CHN' 
ORDER BY population DESC;
      3. 統計中國各個省的總人口數量，按照總人口從大到小排序 
        SELECT   district ,SUM(population)  FROM  city 
WHERE countrycode='CHN'
GROUP BY district
ORDER BY SUM(population) DESC ;
      4. 統計中國,每個省的總人口,找出總人口大于500w的,并按總人口從大到小排序,隻顯示前三名 
        SELECT   district ,SUM(population)  FROM  city 
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population)>5000000
ORDER BY SUM(population) DESC 
LIMIT 3;
  3. 多表連接配接查詢(内連接配接)

    1. 作用

      單表資料不能滿足查詢需求時

      查詢世界上小于100人的城市,所在的國家名,國土面積,城市名,人口數

      SELECT  countrycode,NAME,population FROM city WHERE population<100;
PCN     Adamstown   42 

SELECT NAME ,SurfaceArea FROM country WHERE CODE='PCN';
    2. 多表連接配接基本文法 
      student ：學生表
===============
sno：    學号
sname：學生姓名
sage： 學生年齡
ssex： 學生性别

teacher ：教師表
================
tno：     教師編号
tname：教師名字

course ：課程表
===============
cno：  課程編号
cname：課程名字
tno：  教師編号

score  ：成績表
==============
sno：  學号
cno：  課程編号
score：成績
    3. 多表連接配接例子
      1. 統計zhang3,學習了幾門課 
        SELECT student.sname,COUNT(sc.cno)
FROM student JOIN sc
ON student.sno=sc.sno
WHERE student.sname='zhang3';
      2. 查詢zhang3,學習的課程名稱有哪些? 
        SELECT student.sname,GROUP_CONCAT(course.cname)
FROM student
JOIN sc
ON student.sno=sc.sno 
JOIN course
ON sc.cno=course.cno
WHERE student.sname='zhang3'
GROUP BY student.sname;
      3. 查詢oldguo老師教的學生名和個數. 
        SELECT teacher.tname,GROUP_CONCAT(student.sname),COUNT(student.sname)
FROM teacher 
JOIN course 
ON teacher.tno=course.tno
JOIN sc
ON course.cno=sc.cno
JOIN student
ON sc.sno=student.sno
WHERE teacher.tname='oldguo'
GROUP BY teacher.tname;
      4. 查詢oldguo所教課程的平均分數 
        SELECT teacher.tname,AVG(sc.score)
FROM teacher
JOIN course
ON teacher.tno=course.tno
JOIN sc
ON course.cno=sc.cno
WHERE teacher.tname='oldguo'
GROUP BY sc.cno;
      5. 每位老師所教課程的平均分,并按平均分排序 
        SELECT teacher.tname,course.cname,AVG(sc.score)
FROM teacher 
JOIN course
ON teacher.tno=course.tno
JOIN sc
ON course.cno=sc.cno 
GROUP BY teacher.tname,course.cname
ORDER BY AVG(sc.score)
      6. 查詢oldguo所教的不及格的學生姓名 
        SELECT teacher.tname,student.sname,sc.score
FROM teacher
JOIN course
ON teacher.tno=course.tno
JOIN sc
ON course.cno=sc.cno
JOIN student
ON sc.sno=student.sno
WHERE teacher.tname='oldguo' AND sc.score<60
      7. 查詢所有老師所教學生不及格的資訊(擴充) 
        SELECT teacher.tname,GROUP_CONCAT(CONCAT(student.sname,":",sc.score)) 
FROM teacher
JOIN course
ON teacher.tno=course.tno
JOIN sc
ON course.cno=sc.cno
JOIN student
ON sc.sno=student.sno
WHERE sc.score<60
GROUP BY teacher.tno

      8. 别名應用

        表别名 (全局調用)

        SELECT t.tname,GROUP_CONCAT(CONCAT(st.sname,":",sc.score)) 
FROM teacher as t
JOIN course as c
ON t.tno=c.tno
JOIN sc 
ON c.cno=sc.cno
JOIN student as st
ON sc.sno=st.sno
WHERE sc.score<60
GROUP BY t.tno
        列别名（having 和 order by 調用） 
        SELECT t.tname as 講師名 ,GROUP_CONCAT(CONCAT(st.sname,":",sc.score))  as 不及格的 
FROM teacher as t
JOIN course as c
ON t.tno=c.tno
JOIN sc 
ON c.cno=sc.cno
JOIN student as st
ON sc.sno=st.sno
WHERE sc.score<60
  4. 擴充類内容-中繼資料擷取

    1. 中繼資料介紹及擷取

      中繼資料是存儲在"基表"中。

      通過專用的DDL語句，DCL語句進行修改

      通過專用視圖和指令進行中繼資料的查詢

      information_schema中儲存了大量中繼資料查詢的試圖

      show 指令是封裝好功能，提供中繼資料查詢基礎功能

    2. information_schema的基本應用

      tables 視圖的應用

      use information_schema;
mysql> desc tables;

      TABLE_SCHEMA 表所在的庫名

      TABLE_NAME 表名

      ENGINE 存儲引擎

      TABLE_ROWS 資料行

      AVG_ROW_LENGTH 平均行長度

      INDEX_LENGTH 索引長度

    3. 示例 
      USE information_schema;
DESC TABLES;
      • 顯示所有的庫和表的資訊 
        SELECT table_schema,table_name FROM information_schema.tables;

      • 以以下模式 顯示所有的庫和表的資訊

        world city,country,countrylanguage

        SELECT table_schema,GROUP_CONCAT(table_name) 
FROM information_schema.tables
GROUP BY table_schema;
      • 查詢所有innodb引擎的表 
        SELECT table_schema,table_name ,ENGINE
FROM information_schema.tables 
WHERE ENGINE='innodb';

      • 統計world下的city表占用空間大小

        表的資料量=平均行長度*行數+索引長度

        AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH
SELECT table_name,(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024
FROM information_schema.TABLES
WHERE table_schema='world' AND table_name='city';
      • 統計world庫資料量總大小 
        SELECT table_schema,SUM((AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH))/1024
FROM information_schema.TABLES
WHERE table_schema='world';
      • 統計每個庫的資料量大小，并按資料量從大到小排序 
        SELECT table_schema,SUM((AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH))/1024 AS total_KB
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY total_KB DESC ;
    4. 配合concat()函數拼接語句或指令

      • 模仿以下語句,進行資料庫的分庫分表備份。

        mysqldump -uroot -p123 world city >/bak/world_city.sql

        SELECT
CONCAT("mysqldump -uroot -p123 ",table_schema," ",table_name
," >/bak/",table_schema,"_",table_name,".sql")
FROM information_schema.tables;

      • 模仿以下語句,進行批量生成對world庫下所有表進行操作

        ALTER TABLE world.city DISCARD TABLESPACE;

        SELECT 
CONCAT("ALTER TABLE ",table_schema,".",table_name," DISCARD TABLESPACE;")
FROM information_schema.tables
WHERE table_schema='world';

    5. show介紹

      show databases; 檢視資料庫名

      show tables; 檢視表名

      show create database xx; 檢視建庫語句

      show create table xx; 檢視建表語句

      show processlist; 檢視所有使用者連接配接情況

      show charset; 檢視支援的字元集

      show collation; 檢視所有支援的校對規則

      show grants for xx; 檢視使用者的權限資訊

      show variables like '%xx%' 檢視參數資訊

      show engines; 檢視所有支援的存儲引擎類型

      show index from xxx 檢視表的索引資訊

      show engine innodb status\G 檢視innoDB引擎詳細狀态資訊

      show binary logs 檢視二進制日志的清單資訊

      show binlog events in '' 檢視二進制日志的事件資訊

      show master status ; 檢視mysql目前使用二進制日志資訊

      show slave status\G 檢視從庫狀态資訊

      show relaylog events in '' 檢視中繼日志的事件資訊

      show status like '' 檢視資料庫整體狀态資訊

sql 存儲 監控 關系型資料庫 mysql shell 資料庫 索引 SQL問題 sql小數點 sql評估期 sql重新 不用sql
上一篇: Quick BI産品核心功能大圖（三）電子表格： 新手亦可表格自由
下一篇: 資料庫-索引索引執行計劃分析

繼續閱讀