2019/04/08 09:01

SQL介紹

==結(jié)構(gòu)化查詢語言(Structured Query Language)==

簡(jiǎn)稱SQL(發(fā)音：/?es kju? ?el/ "S-Q-L")，是一種特殊目的的編程語言，是一種數(shù)據(jù)庫(kù)查詢和程序設(shè)計(jì)語言，用于存取數(shù)據(jù)以及查詢、更新和管理關(guān)系數(shù)據(jù)庫(kù)系統(tǒng)；同時(shí)也是數(shù)據(jù)庫(kù)腳本文件的擴(kuò)展名。

MySQL-5.7版本后符合SQL-92標(biāo)準(zhǔn)的嚴(yán)格模式。
數(shù)字不用加雙引號(hào)，字符串要加

數(shù)據(jù)類型、表屬性、字符集

數(shù)據(jù)類型

作用：保證數(shù)據(jù)的準(zhǔn)確性和標(biāo)準(zhǔn)性。

種類

數(shù)值類型

TINYINT  ：-128~127

INT      ：-2^31~2^31-1

說明：手機(jī)號(hào)是無法存儲(chǔ)到INT的,一般是使用char類型來存儲(chǔ)收集號(hào)。

數(shù)值類型

字符類型

#char
固定長(zhǎng)度字符串，存儲(chǔ)效率高，不需要判斷字符長(zhǎng)度，直接分配空間，浪費(fèi)不必要的資源。

#varchar
相比前者，存儲(chǔ)效率低，需要判斷字符長(zhǎng)度，按需分配空間。

如何選擇？
#大量插入（insert）操作時(shí)，推薦使用char去代替varchar。
#業(yè)務(wù)中，大量是查詢類操作，數(shù)據(jù)量級(jí)又比較大的情況下，變長(zhǎng)長(zhǎng)度數(shù)據(jù)類型，可以考慮采用varchar，一方面節(jié)省空間，可以有效的減少索引樹的高度，從而提高索引的優(yōu)化查詢的效果。

字符類型

時(shí)間類型

#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.
TIMESTAMP會(huì)受到時(shí)區(qū)的影響。

時(shí)間類型

二進(jìn)制類型

二進(jìn)制類型

表屬性

列屬性

primary key   主鍵
#非空且唯一  

not null      非空   
#可以設(shè)置默認(rèn)值

unique key    唯一鍵  
#列值不能重復(fù)  

unsigned      無符號(hào)  
#針對(duì)數(shù)字列（非負(fù)數(shù)等等）

其他屬性

key            索引  
#建立索引，優(yōu)化查詢  

default        默認(rèn)值  
#列中，無錄入值時(shí)，默認(rèn)使用default值  

auto_increment 自增長(zhǎng) 
#針對(duì)數(shù)字列（默認(rèn)從1開始，可以設(shè)定起始點(diǎn)與偏移量）

comment        注釋

字符集校對(duì)規(guī)則

字符集

utf-8
utf8mb4
utf8mb4是utf8的超集，專門用來兼容四字節(jié)的unicode（emoji表情等等）

校對(duì)規(guī)則

大小寫是否敏感

SQL分類

DDL：數(shù)據(jù)定義語言
DCL：數(shù)據(jù)控制語言
DML：數(shù)據(jù)操作語言
DQL：數(shù)據(jù)查詢語言  #MySQL查詢語句類型

DDL語句

庫(kù)定義

創(chuàng)建

#創(chuàng)建數(shù)據(jù)庫(kù)
>create database school;   

#查看創(chuàng)建庫(kù)的命令內(nèi)容
>show create database school;

規(guī)范

庫(kù)名大小寫統(tǒng)一（Windows不區(qū)分大小寫，Linux區(qū)分大小寫）

建庫(kù)時(shí)指定字符集

>create database db charset utf8mb4;

庫(kù)名不能以數(shù)字開頭

見名知其意

刪除(禁止刪庫(kù))

>drop database db;

修改

#修改字符集為utf8（默認(rèn)為latin1）
>alter database school charset utf8;

#查看字符集
>show charset;

#查看校對(duì)集
> show collation;

表定義

表規(guī)范

表名小寫
不能是數(shù)字開頭
注意字符集和存儲(chǔ)引擎
表名和業(yè)務(wù)有關(guān)
選擇合適的數(shù)據(jù)類型
每列要有注釋
每列設(shè)置為非空，否則設(shè)置默認(rèn)值（0）

創(chuàng)建表

create table  table_name (

列1  屬性(數(shù)據(jù)類型、約束、其他屬性) ，

列2  屬性，

列3  屬性

)  

#建表舉例  
USE db_name;
CREATE TABLE student_info (
id INT PRIMARY KEY NOT NULL  AUTO_INCREMENT COMMENT '學(xué)生學(xué)號(hào)',
sname VARCHAR(64) NOT NULL COMMENT '學(xué)生姓名',
gender ENUM('m','f','n') NOT NULL DEFAULT 'n' COMMENT '學(xué)生性別',
phone  CHAR(11) UNIQUE KEY NOT NULL COMMENT '手機(jī)號(hào)',
age  TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '學(xué)生年齡',
time TIMESTAMP NOT NULL DEFAULT NOW() COMMENT '入學(xué)時(shí)間'
)ENGINE=INNODB CHARSET=utf8mb4;

刪除表（禁止使用）

drop table t1;

修改表屬性

#在student表的最后加上QQ列
ALTER TABLE school.student ADD qq VARCHAR ( 64 ) UNIQUE KEY NOT NULL COMMENT 'QQ';
DESC stutdent;

#在sname后加微信列
ALTER TABLE school.student ADD Wechat VARCHAR ( 64 ) UNIQUE KEY NOT NULL COMMENT '微信' AFTER sname;
DESC student;

#插入在第一列  
ALTER TABLE school.student ADD num INT FIRST;
DESC student;

#修改sname數(shù)據(jù)類型
ALTER TABLE student MODIFY sname VARCHAR ( 32 );
DESC student;

#修改sname列名改為sn,數(shù)據(jù)類型改為varchar(50)
ALTER TABLE student CHANGE sname sn VARCHAR ( 50 );
DESC student;

刪除列

#刪除num列
ALTER TABLE student DROP num;

表屬性查詢

#查看表內(nèi)容
show table_name;

#
desc table_name;  

#查看表所有內(nèi)容的前4行
select * from table_name where id<5; 

#復(fù)制source表結(jié)構(gòu)，并創(chuàng)建新的空表
create table source_table_name like new_table_name;

DCL語句

#授予權(quán)限
grant

#回收權(quán)限
revoke

DML語句

對(duì)表中數(shù)據(jù)進(jìn)行增、刪、改。

INSERT語句

#在學(xué)生表插入一行數(shù)據(jù)
DESC student;
INSERT INTO student(sn,gender,phone,age)
VALUES('zs','m',  '110'  ,18);
SELECT * FROM student;
#省事的插入方法
INSERT INTO student
VALUES(2,'ls','f','190',18,NOW());
SELECT * FROM student;

#一次性錄入多行數(shù)據(jù)
INSERT INTO student(sn,gender,phone,age)
VALUES('w5','f','120',19),('m6','m','119',20),('m66','f','1190',27);
SELECT * FROM student;
INSERT INTO student(sn,phone,age)
VALUES('w55','1200',17);

UPDATE語句

#更改第7行'sn'列為'zhao4'
UPDATE student SET sn='zhao4' WHERE id=7;
注意：update語句必須要加where。

DELETE語句 (刪除)

#刪除第7行信息
delete from student where id=7;

偽刪除：用update來替代delete，最終保證業(yè)務(wù)中查不到（select）即可。
#添加狀態(tài)列
alter table student add state tinyint not null default 1;
#使用update替代delete
update student set state=0 where id=6;
select * from xuesheng;
#業(yè)務(wù)查詢時(shí),加入狀態(tài)判斷
select * from student where state=1;

DQL語句

SELECT語句

MySQL獨(dú)有參數(shù)

#查看配置文件信息
select @@basedir;
select @@socket;

MySQL內(nèi)置函數(shù)

#查看系統(tǒng)當(dāng)前時(shí)間
select now();    

#查看MySQL當(dāng)前登陸用戶
select user();  

#查看當(dāng)前所在數(shù)據(jù)庫(kù)
select database();

from

#查看city表中某一列信息 
SELECT NAME,population FROM world.city;

#查看student表的所有信息
select * from  student;

where

#查詢中國(guó)所有城市
select * from city where countrycode='CHN';  

#查詢以'US'開頭的列（%不可以在最前方）
select * from city where countrycode like 'US%';

#查看中國(guó)和美國(guó)的城市信息
select * from city where countrycode='CHN' or countrycode='USA';

#同時(shí)模糊查找
select * from city where countrycode like 'US%' or countrycode like 'CH%';

group by + 常用聚合函數(shù)

作用：根據(jù)by后面的條件進(jìn)行分組，方便統(tǒng)計(jì)，by后面跟單列或者多列。

常用聚合函數(shù)

max()       #最大值
min()       #最小值
avg()       #平均值
sum()       #總和
count()     #個(gè)數(shù)

GROUP BY + 聚合函數(shù)口訣與實(shí)例

遇到統(tǒng)計(jì)想函數(shù)  
形容詞前 GROUP BY  
函數(shù)中央是名詞     
列名select后添加  

#統(tǒng)計(jì)世界上每個(gè)國(guó)家的總?cè)丝跀?shù).
USE world  
SELECT countrycode ,SUM(population)  FROM  city GROUP BY countrycode;  

#統(tǒng)計(jì)中國(guó)各個(gè)省的總?cè)丝跀?shù)量  
SELECT district,SUM(Population) FROM city WHERE countrycode='chn' GROUP BY district;    

#統(tǒng)計(jì)世界上每個(gè)國(guó)家的城市數(shù)量  
SELECT countrycode,COUNT(id)  FROM city GROUP BY countrycode;

having

where|group|having  
#統(tǒng)計(jì)中國(guó)每個(gè)省的總?cè)丝跀?shù)，將總?cè)丝跀?shù)小于100w  
SELECT district,SUM(Population)FROM cityWHERE countrycode='chn' GROUP BY districtHAVING SUM(Population) < 1000000 ;

order by + limit

#查看中國(guó)所有城市，并按人口數(shù)進(jìn)行排序（從大到?。?SELECT * FROM city WHERE countrycode='CHN' ORDER BY population DESC;


#統(tǒng)計(jì)中國(guó)各省總?cè)丝跀?shù)，按照總?cè)丝趶拇蟮叫∨判? 
select district as 省份,sum(population) as 總?cè)丝?from city
where countrycode='chn'
group by district 
order by 總?cè)丝?DESC;


#取第3名到第7名
select district as 省份,sum(population) as 總?cè)丝?from city
where countrycode='chn'
group by district 
order by 總?cè)丝?DESC
limit 2,5;

select district as 省份,sum(population) as 總?cè)丝?from city
where countrycode='chn'
group by district 
order by 總?cè)丝?DESC
limit 5 offset 2;

distinct （去重）

SELECT countrycode FROM city ;
SELECT DISTINCT(countrycode) FROM city  ;

join （多表連接查詢）

#查詢一下世界上人口數(shù)量小于100人的城市名和國(guó)家名  
SELECT b.name ,a.name ,a.populationFROM   city  AS aJOIN   country AS bON     b.code=a.countrycodeWHERE  a.Population<100  

#查詢城市shenyang，城市人口，所在國(guó)家名（name）及國(guó)土面積（SurfaceArea）
SELECT a.name,a.population,b.name ,b.SurfaceAreaFROM city  AS a JOIN country AS bON a.countrycode=b.codeWHERE a.name='shenyang';


#多表連接口訣  
數(shù)據(jù)來自多張表，優(yōu)先想到多表連接join ON  
關(guān)聯(lián)表寫join兩端  
on條件寫兩表的關(guān)聯(lián)列  
所有查詢條件select后，注意表名和別名
where過濾條件寫最后

union（all）

主要用來優(yōu)化 in 和 or 語句

SELECT * FROM city WHERE countrycode='CHN'
UNION ALL
SELECT * FROM city WHERE countrycode='USA'

子查詢

#查詢?nèi)丝谏儆?00人的城市所在的國(guó)家名字  
select name,population,countrycode from city where population <100；  
A，B，CAAA BBB CCCselect name from country where code in (select countrycode from city where population <100;)  

#優(yōu)化思路  
1.改寫為join  
2.利用臨時(shí)表

information_schema tables

DESC information_schema.TABLES

TABLE_SCHEMA    ---->庫(kù)名  
TABLE_NAME      ---->表名  
ENGINE          ---->引擎  
TABLE_ROWS      ---->表的行數(shù)  
AVG_ROW_LENGTH  ---->表中行的平均行（字節(jié)）  
INDEX_LENGTH    ---->索引的占用空間大?。ㄗ止?jié)）  


#查詢整個(gè)數(shù)據(jù)庫(kù)中所有庫(kù)和所對(duì)應(yīng)的表信息  
SELECT table_schema,GROUP_CONCAT(table_name) FROM information_schema.tablesGROUP BY table_schema;  

#統(tǒng)計(jì)所有庫(kù)下的表個(gè)數(shù)  
SELECT table_schema,COUNT(table_name)FROM information_schema.TABLESGROUP BY table_schema  

#查詢所有innodb引擎的表及所在的庫(kù)   
SELECT table_schema,table_name,ENGINE FROM information_schema.`TABLES`WHERE ENGINE='innodb';  

#統(tǒng)計(jì)world數(shù)據(jù)庫(kù)下每張表的磁盤空間占用  
SELECT table_name,CONCAT((TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024," KB")  AS size_KBFROM information_schema.tables WHERE TABLE_SCHEMA='world';  

#統(tǒng)計(jì)所有數(shù)據(jù)庫(kù)的總的磁盤空間占用   
SELECTTABLE_SCHEMA,CONCAT(SUM(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024," KB") AS Total_KBFROM information_schema.tablesGROUP BY table_schema;  

mysql -uroot -p123 -e "SELECT TABLE_SCHEMA,CONCAT(SUM(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024,' KB') AS Total_KB FROM information_schema.tables GROUP BY table_schema;"  

#生成整個(gè)數(shù)據(jù)庫(kù)下的所有表的單獨(dú)備份語句模板語句  
mysqldump -uroot -p123 world city >/tmp/world_city.sql  
SELECT CONCAT("mysqldump -uroot -p123 ",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql" )  
FROM information_schema.tables  
WHERE table_schema NOT IN('information_schema','performance_schema','sys')  
INTO OUTFILE '/tmp/bak.sh' ;  
CONCAT("mysqldump -uroot -p123 ",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql" )  

#107張表，都需要執(zhí)行以下2條語句  
ALTER TABLE world.city DISCARD TABLESPACE;  
ALTER TABLE world.city IMPORT TABLESPACE;  
SELECT CONCAT("alter table ",table_schema,".",table_name," discard tablespace")  
FROM information_schema.tables  
WHERE table_schema='world'INTO OUTFILE '/tmp/dis.sql';

show 命令

#查看數(shù)據(jù)庫(kù)
show databases;

#查看表
show tables;

#查看某個(gè)數(shù)據(jù)庫(kù)里面的表 
show tables from db_name;

#查看建庫(kù)語句
show create db_name;

#查看建表語句  
show create table db_name.table_name;

#查看用戶授權(quán)信息   
show  grants for  root@'localhost';

#查看字符集
show  charset;

#查看校對(duì)集  
show collation;  

#查看數(shù)據(jù)庫(kù)連接情況  
show processlist; 

#查看表索引  
show index from table_name;

#查看所有配置信息  
show variables;

#查看數(shù)據(jù)庫(kù)狀態(tài)  
show status;  

#查看所有存儲(chǔ)引擎
show engines;  

#查看所有二進(jìn)制日志
show binary logs;

#查看部分配置信息
SHOW variables LIKE '%lock%';   

#查看InnoDB引擎相關(guān)的狀態(tài)信息  
show engine innodb status\G
  
#查看主庫(kù)狀態(tài)  
show master status

#查看從庫(kù)狀態(tài)
show slave status \G

#查看表的列定義信息
desc  (show colums from city)

show語句幫助
http://dev.mysql.com/doc/refman/5.7/en/show.html

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av

SQL應(yīng)用及元數(shù)據(jù)獲取

SQL應(yīng)用及元數(shù)據(jù)獲取

2019/04/08 09:01

SQL介紹

數(shù)據(jù)類型、表屬性、字符集

數(shù)據(jù)類型

種類

表屬性

列屬性

其他屬性

字符集校對(duì)規(guī)則

字符集

校對(duì)規(guī)則

SQL分類

DDL語句

庫(kù)定義

表定義

DCL語句

DML語句

INSERT語句

UPDATE語句

DELETE語句 (刪除)

DQL語句

SELECT語句

group by + 常用聚合函數(shù)

order by + limit

join （多表連接查詢）

union（all）

子查詢

information_schema tables

show 命令

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九 欧美,1769亚洲,黄色成人av

SQL應(yīng)用及元數(shù)據(jù)獲取

2019/04/08 09:01

SQL介紹

數(shù)據(jù)類型、表屬性、字符集

數(shù)據(jù)類型

種類

表屬性

列屬性

其他屬性

字符集校對(duì)規(guī)則

字符集

校對(duì)規(guī)則

SQL分類

DDL語句

庫(kù)定義

表定義

DCL語句

DML語句

INSERT語句

UPDATE語句

DELETE語句 (刪除)

DQL語句

SELECT語句

group by + 常用聚合函數(shù)

order by + limit

join （多表連接查詢）

union（all）

子查詢

information_schema tables

show 命令

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av

數(shù)據(jù)類型、表屬性、字符集