SQL 語句

SQL

MySQL内置功能

連接配接資料庫

-u

-p

-S

-h

-P

-e

<

示例：

mysql -u root -p -S /tmp/mysql.sock

mysql -u root -p -h 10.0.0.51 -P3306

-e 免互動執行 sql 語句

[root@db01 ~]# mysql -uroot -p -e "show databases;"

< 導入資料

[root@db01 ~]# mysql -uroot -p123 /root/world.sql

内置指令

help 幫助

\c ctrl+c 結束上個指令運作

\q quit exit ctrl+d 退出

\G 豎行顯示

source 恢複備份檔案

SQL 基礎應用

介紹

結構化的查詢語言

關系型資料庫通用的指令

遵循SQL92标準（SQL_MODE）

常用種類

DDL 資料定義語言

DCL 資料控制語言

DML 資料操作語言

DQL 資料查詢語言

資料庫的邏輯結構

庫

庫名

庫屬性(字元集，排序規則)

表

表名

表屬性(存儲引擎類型，字元集，排序規則)

列名

列屬性(資料類型，限制，其他屬性)

資料行

字元集(charset)

檢視支援字元集 show charset

utf8 三個字元

utfmb4 四個字元(支援emoji)

排序規則(collation)

檢視排序規則 show collation

英文字元串的大小寫不敏感

utf8mb4_general_ci 大小寫不敏感

utf8mb4_bin 大小寫敏感(存拼音,日文)

資料類型

數字：tinyint int

字元串:

char(100)

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

varchar(100)

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

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

如何選擇這兩個資料類型?

少于255個字元串長度,定長的列值,選擇char

多于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

庫的定義

建立資料庫

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;

庫定義規範

庫名使用小寫字元

庫名不能以數字開頭

不能為資料庫内部關鍵字

必須設定字元集

表的定義

建表

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

列屬性

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;

建表規範

表名小寫字母,不能數字開頭

不能是保留字元,使用和業務有關的表名

選擇合适的資料類型及長度

每個列設定 NOT NULL + DEFAULT ;對于資料0填充,對于字元使用有效字元串填充

每個列設定注釋

表必須設定存儲引擎和字元集

主鍵列盡量是無關列數字列,最好是自增長

enum類型不要儲存數字,隻能是字元串類型

查詢表資訊

SHOW TABLES;

SHOW CREATE TABLE stu;

DESC stu;

建立一個表結構一樣的表

CREATE TABLE test LIKE stu;

删表(不代表生産操作)

DROP TABLE test;

修改

在stu表中添加qq列

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;

ALTER TABLE stu DROP qq;

ALTER TABLE stu DROP wechat;

修改sname資料類型的屬性

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';

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

DQL介紹

SELECT

SHOW

SELECT 語句的應用

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();

SELECT通用文法表(單表)

select 列

from 表

where 條件

group by 條件

having 條件

order by 條件

limit

學習環境說明

world 資料庫

city 城市表

country 國家表

countrylanguage 語言表

city表結構

ID 城市序号

name 城市名代号

countrycode 國家

district 區域

population 人口

SELECT 配合 FROM 子句使用

SELECT 列 from 表

查詢表中所有的資訊(生産中幾乎是沒有這種需求的)

USE world ;

SELECT id,NAME ,countrycode ,district,population FROM city;

或者

SELECT * FROM city;

查詢表中 name 和population的值

SELECT NAME ,population FROM city;

SELECT 配合 WHERE 子句使用

select 列 from 表 where 過濾條件

示例:

等值條件查詢

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

SELECT NAME,population FROM city

WHERE countrycode='CHN';

比較判斷查詢

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

SELECT NAME,population FROM city

WHERE population<100;

邏輯連接配接符

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

WHERE countrycode='CHN' AND population>8000000;

查詢中國或美國的城市名和人口數

WHERE countrycode='CHN' OR countrycode='USA';

查詢人口數量在500w到600w之間的城市名和人口數

WHERE population>5000000 AND population<6000000;

WHERE population BETWEEN 5000000 AND 6000000;

模糊查詢

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

SELECT * FROM city

WHERE countrycode LIKE 'CH%';

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

in 語句

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

WHERE countrycode IN ('CHN' ,'USA');

GROUP BY

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

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

SELECT countrycode ,COUNT(id) FROM city

GROUP BY countrycode;

統計每個國家的總人口數.

SELECT countrycode,SUM(population) FROM city

統計每個 國家 省 的個數

SELECT countrycode,COUNT(DISTINCT district) FROM city

統計中國 每個省的總人口數

SELECT district, SUM(population) FROM city

WHERE countrycode='CHN'

GROUP BY district ;

統計中國 每個省城市的個數

SELECT district, COUNT(NAME) FROM city

GROUP BY distric

統計中國 每個省城市的名字清單GROUP_CONCAT()

SELECT district, GROUP_CONCAT(NAME) FROM city

anhui : hefei,huaian ....

SELECT CONCAT(district,":" ,GROUP_CONCAT(NAME)) FROM city

ORDER BY

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

SELECT countrycode,SUM(population) FROM city

GROUP BY countrycode

HAVING SUM(population)>50000000

ORDER BY SUM(population) DESC ;

LIMIT

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

ORDER BY SUM(population) DESC

LIMIT 3 OFFSET 0;

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

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

小結

select disctrict , count(name) from city

where countrycode='CHN'

group by district

having count(name) >10

order by count(name) desc

limit 3;

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 會做去重操作

練習題

統計中國每個省的總人口數，隻列印總人口數小于100w的

SELECT district ,SUM(population) FROM city

GROUP BY district

HAVING SUM(population)<1000000;

檢視中國所有的城市，并按人口數進行排序(從大到小)

SELECT * FROM city WHERE countrycode='CHN'

ORDER BY population DESC;

統計中國各個省的總人口數量，按照總人口從大到小排序

ORDER BY SUM(population) DESC ;

統計中國,每個省的總人口,找出總人口大于500w的,并按總人口從大到小排序,隻顯示前三名

HAVING SUM(population)>5000000

ORDER BY SUM(population) DESC

LIMIT 3;

多表連接配接查詢(内連接配接)

作用

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

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

SELECT countrycode,NAME,population FROM city WHERE population<100;

PCN Adamstown 42

SELECT NAME ,SurfaceArea FROM country WHERE CODE='PCN';

多表連接配接基本文法

student ：學生表

sno： 學号

sname：學生姓名

sage： 學生年齡

ssex： 學生性别

teacher ：教師表

tno： 教師編号

tname：教師名字

course ：課程表

cno： 課程編号

cname：課程名字

tno： 教師編号

score ：成績表

sno： 學号

score：成績

多表連接配接例子

統計zhang3,學習了幾門課

SELECT student.sname,COUNT(sc.cno)

FROM student JOIN sc

ON student.sno=sc.sno

WHERE student.sname='zhang3';

查詢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;

查詢oldguo老師教的學生名和個數.

SELECT teacher.tname,GROUP_CONCAT(student.sname),COUNT(student.sname)

FROM teacher

JOIN course

ON teacher.tno=course.tno

ON course.cno=sc.cno

JOIN student

ON sc.sno=student.sno

WHERE teacher.tname='oldguo'

GROUP BY teacher.tname;

查詢oldguo所教課程的平均分數

SELECT teacher.tname,AVG(sc.score)

FROM teacher

GROUP BY sc.cno;

每位老師所教課程的平均分,并按平均分排序

SELECT teacher.tname,course.cname,AVG(sc.score)

ON course.cno=sc.cno

GROUP BY teacher.tname,course.cname

ORDER BY AVG(sc.score);

查詢oldguo所教的不及格的學生姓名

SELECT teacher.tname,student.sname,sc.score

WHERE teacher.tname='oldguo' AND sc.score<60;

查詢所有老師所教學生不及格的資訊(擴充)

SELECT teacher.tname,GROUP_CONCAT(CONCAT(student.sname,":",sc.score))

WHERE sc.score<60

GROUP BY teacher.tno;

别名應用

表别名 (全局調用)

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

GROUP BY t.tno;

列别名（having 和 order by 調用）

SELECT t.tname as 講師名 ,GROUP_CONCAT(CONCAT(st.sname,":",sc.score)) as 不及格的

WHERE sc.score<60;

擴充類内容-中繼資料擷取

中繼資料介紹及擷取

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

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

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

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

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

information_schema的基本應用

tables 視圖的應用

use information_schema;

mysql> desc tables;

TABLE_SCHEMA 表所在的庫名

TABLE_NAME 表名

ENGINE 存儲引擎

TABLE_ROWS 資料行

AVG_ROW_LENGTH 平均行長度

INDEX_LENGTH 索引長度

示例

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

WHERE table_schema='world';

統計每個庫的資料量大小，并按資料量從大到小排序

SELECT table_schema,SUM((AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH))/1024 AS total_KB

GROUP BY table_schema

ORDER BY total_KB DESC ;

配合concat()函數拼接語句或指令

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

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

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;")

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 '' 檢視資料庫整體狀态資訊