SQL應(yīng)用
1.client?
help:mysql的內(nèi)置命令?
? 打印幫助
\c? 結(jié)束上一條命令的運行?
\G 讓每行的數(shù)據(jù)以列的形式顯示 ，針對列很多的數(shù)據(jù)
exit 退出?
/q 退出?
ctl d 退出
source 導(dǎo)入sql腳本，類似于 <?
例子 ：source /root/a.sql?
system:可以調(diào)用linux的命令?
2.server?
Linux中一切皆命令，Linux中一切皆文件。
2.1DDL? 數(shù)據(jù)定義語言
(1)庫定義 ：?
創(chuàng)建數(shù)據(jù)庫：
create database oldguo charset utf8mb4;
規(guī)范：1.庫名，小寫，業(yè)務(wù)有關(guān)，不要數(shù)字開頭，不要太長，不能用保留關(guān)鍵字
查看數(shù)據(jù)庫：show databases;
查看建庫語句:show create database 庫名;
修改數(shù)據(jù)庫：alter database 庫名 charset utf8mb4;一般修改字符集是往大了改。不然不兼容.
刪除數(shù)據(jù)庫：不要操作。drop database 庫名.
(2)表定義 ：
創(chuàng)建表?
create table user ( id int not null auto_increment comment '用戶序號', name varchar(64) not null comment '用戶名', age tinyint unsigned not null default 18 comment '年齡', gender char(1) not null default 'F' comment '性別', cometime datetime not null comment '注冊時間', shengfen enum('成都','南充','廣安','小香港','重慶') default '成都' not null comment '省份', primary key (id)) engine=innodb charset=utf8mb4;
建表規(guī)范:
1.表名:
小寫字母 原因：windows操作系統(tǒng)不區(qū)分大小寫
不能數(shù)字開頭，
表名和業(yè)務(wù)有關(guān)，
不能使用關(guān)鍵字，
名字不要太長，不超過15個字符.
2.必須設(shè)置存儲引擎和字符集
3.數(shù)據(jù)類型：合適，簡短，足夠
4.必須要有主鍵
5.每個列盡量設(shè)置not null,不知道怎么填，設(shè)定默認(rèn)值
6.每個列要有注釋
7.列名也不要太長?
查詢表
show tables;
desc user;
show create table user;
修改表
例子：
添加字段:alter table user add column phone_no bigint? not null? unique key? comment '手機號';
修改列屬性:alter table user modify? phone_no char(11)? not null? unique key? comment '手機號';
刪除列:alter table user drop phone_no;
刪除表:drop table user;
清空表:truncate table? user ;
2.2DCL 數(shù)據(jù)庫控制語言
grant? 賦權(quán)
revoke? 回收權(quán)限
2.3DML 數(shù)據(jù)庫操作語言
insert?
insert into user (字段1,字段2,...) values (值1，值2,...);
插入多行:
insert into user (字段1,字段2,...) values (值1，值2,...),(值1,值2,....);
update
update user set? name='喬碧蘿' where id=3;
delete?
delete from user where? name ='蒼井空';
擴展:
1.偽刪除
--修改表結(jié)構(gòu)，添加一個state狀態(tài)列
alter table user add column state tinyint not null default 1 comment '狀態(tài)';
update user set state = 0? where name ='張飛';
select * from user where state = 1 ;
2.區(qū)別?
delete? dml語言，邏輯上，逐行刪除，數(shù)據(jù)多，操作慢，
并沒有真正刪除，只是在存儲層面打標(biāo)記，磁盤空間不會立即釋放，HMW高水位線不會降低，
會產(chǎn)生大量的碎片。
drop? ddl語言,將表結(jié)構(gòu)(元數(shù)據(jù))和數(shù)據(jù)行物理層次刪除。
truncate 物理層次刪除表中所有數(shù)據(jù)，磁盤空間立即釋放，HMW高水位線立即降低.
2.4DQL 數(shù)據(jù)庫查詢語言
select :實際屬于dml語言，這里抽離出來。體現(xiàn)重要性
功能:獲取表中的數(shù)據(jù)行
1)select 單獨使用(mysql獨家)
select 配合函數(shù)使用。
\mysql> select now() ;
| now()? ? ? ? ? ? ? |
| 2020-03-17 21:30:08 |
mysql> select concat('good');
| concat('good') |
| good? ? ? ? ? |
select concat(user,'@',host) from mysql.user;
2)計算?
select 100/3 ;
3)查詢數(shù)據(jù)庫的參數(shù)?
select @@sql_mode;select @@datadir;select @@socket;
4)select 標(biāo)準(zhǔn)用法 （配合其他子句使用）
--單表
前提默認(rèn)執(zhí)行順序
select?
from? 表1，表2
where? 條件1，條件2
group by? 條1，條2
select_list 列名列表
having? 過濾條件1，過濾條件2
order by? 條件列1 ，條件列2?
limit? 限制??
mysql示例數(shù)據(jù)庫地址：
shttp://dev.mysql.com/doc/index-other.html
導(dǎo)入示例庫 :
mysql- uluobiao -p? < wolrd.sql?
模糊查詢: like like只適用于 字符串
select * from city where `CountryCode` like 'CHN%';
下面這種查詢方式不會走索引，需要注意:
select * from city where `CountryCode` like '%CHN%'
where 配合邏輯連接符? and? ?or
bwetween and? 類似<=? and? >=??
group by? 配合聚合函數(shù)使用
max()? ：最大值
min() 最小值
avg() 平均值?
count(0) 統(tǒng)計個數(shù)
sum() 求和?
group_concat()? 列轉(zhuǎn)行?
求出中國省份的人數(shù)，和城市數(shù)量，以及城市列表
select countrycode,District,count(0),sum(Population) ,group_concat(name,'@') from city
where? countrycode='CHN'
group? by? District
擴展：delete drop truncate? 操作，怎么恢復(fù)數(shù)據(jù)
1.通過備份+日志，恢復(fù)數(shù)據(jù)。三種。
2.delete 可以通過翻轉(zhuǎn)日志（binlog恢復(fù)），只針對delete
3.也可以通過 延時從庫進行恢復(fù)，三種
having? 只顯示總?cè)丝跀?shù)500萬的省
select countrycode,District,sum(Population) from city
where? countrycode='CHN'
group? by? District
having sum(Population) >= 5000000 ;
order by? 只顯示總?cè)丝跀?shù)500萬的省,且降序排序
select countrycode,District,sum(Population) from city?
where? countrycode='CHN'
group? by? District
having sum(Population) >= 5000000?
order by?sum(Population)? desc ;
order by? 只顯示總?cè)丝跀?shù)500萬的省,且降序排序,只看前5名
select countrycode,District,sum(Population) from city
where? countrycode='CHN'
group? by? District
having sum(Population) >= 5000000
order by sum(Population)? desc
limit 5? ;
limit 3 offset 2? 等價于 limit 2,3 ;
意思是顯示3行，跳過2行。
---多表連接
1.內(nèi)連接??select t.* ,c.* from teacher t , course c where t.`tno` = c.`tno`
select t.* ,c.* from teacher t inner join course c on t.`tno` = c.`tno` (標(biāo)準(zhǔn)寫法);
2.外連接:
left join? ?on? ?right join on? ?實際上是重新生成一張表? 在做where? group by 等操作
例子：張三學(xué)習(xí)了那些課程
select t.`sno`,t.sname,group_concat(c.cname) from student t
join sc s
? on t.`sno` = s.sno and t.sname='zhang3'
? join course c on s.`cno` = c.`cno`
? group by? t.`sno`,t.sname;
驅(qū)動表的概念:
next_loop 循環(huán)?
內(nèi)連接的驅(qū)動表是由優(yōu)化器決定。
外連接，是連接的那一邊的表作為驅(qū)動表(左連接是左表，右連接是右表)。驅(qū)動表是作為外層循環(huán)去連接內(nèi)層循環(huán)。
所以建議：使用小表作為驅(qū)動表.降低next_loop的次數(shù).
列別名: as ‘別名’ 或者 '別名':
作用：1.方便查看 2.可以在having order by? limit? 以及group by? 后面的子句使用別名。
表別名:全局使用
distinct(字段名) 去重復(fù)字段函數(shù).
例子：select distinct(countrycode) from city ;
union 與union all的區(qū)別? ?
union 會去除重復(fù)的數(shù)據(jù)而union all 不會。
show?語句
show? databases; 查詢所有的庫
show tables;查詢use到庫下的所有的表?
show tables from? 庫名;查詢某個庫下的表
show processlist; 查詢所有的用戶連接。
show full processlist; 會顯示所有詳細(xì)信息.
show charset; 查看字符集
show collation;查看校對規(guī)則
show engines; 查看支持的引擎信息
show privileges; 查看支持的權(quán)限信息
show grants for 查看某用戶的權(quán)限
show create database 庫名 查看建庫語句
show create table 查看建表語句??
show index from? 索引名稱? 查看表的索引信息
show engine innodb status? 查詢innodb引擎狀態(tài)
show status 查看數(shù)據(jù)庫狀態(tài)信息
show status like '%狀態(tài)名%' 模糊查詢數(shù)據(jù)庫的狀態(tài)
show variables 查看數(shù)據(jù)庫的參數(shù)
show variables like '%%' 模糊查詢部分參數(shù)
show binary logs? 查詢所有二進制日志文件信息
show binlog events in? 查詢二進制日志事件
show master status 查詢二進制日志的位置點信息
show slave status 查詢從庫的狀態(tài)信息
show relaylog events in? 查詢中繼日志事件?
show variables? like '%mode' \G
元數(shù)據(jù)?use information_schema 庫
每次數(shù)據(jù)庫啟動，會自動在內(nèi)存中生成i_s,生成查詢mysql部分元素?fù)?jù)信息視圖
i_s中的視圖就是保存的元信息
is.tables?
作用保存了所有表的數(shù)據(jù)字典信息?
字段信息：
table_schema 表所在的庫
table_name 表名
engine 表引擎
table_rows 表的數(shù)據(jù)行（不是實時更新）
avg_row_length 平均行長度
data_length? 表的使用存儲空間大?。ú皇菍崟r更新）
index_length 表中索引占用空間的大小
data_free 表中是否有碎片
i_s.tables 企業(yè)應(yīng)用案例
--例子1:數(shù)據(jù)資產(chǎn)統(tǒng)計--數(shù)據(jù)庫資產(chǎn)統(tǒng)計-統(tǒng)計每個庫，所有的表個數(shù)和表名
select table_schema,count(0),group_concat(table_name) from tables group by table_schema;
--例子2:統(tǒng)計所有的庫占用的存儲空間大小 (默認(rèn)單位bytes)
方法一.select table_schema,sum(data_length + index_length) from tables group by table_schema;
方法二(推薦).select table_schema,sum(table_rows * avg_row_length + index_length) from tables group by table_schema;
例子3：查詢業(yè)務(wù)庫中，非innodb表的
select table_schema,table_name from tables where engine != 'InnoDB' and table_schema not in
('sys','mysql','information_schema','performance_schema')
例子4，將所有非innodb的業(yè)務(wù)表轉(zhuǎn)換為innodbi 表?
select concat('alter table ',table_schema,'.',table_name,' engine = innodb;') from tables where engine != 'InnoDB' and table_schema not in?
('sys','mysql','information_schema','performance_schema') into outfile? '/tmp/test.sql';
將上面的sql輸出到本地
1.需要將輸出的目錄作為安全目錄保存到mysql配置文件,
vi /etc/my.cnf? 寫入服務(wù)器端 :?secure-file-priv=/tmp ，并重啟數(shù)據(jù)庫
執(zhí)行導(dǎo)入命令: mysql -uroot -p </tmp/test.sql;
