二鸠窗、常用基本SQL語句/語法

SQL語句基礎(chǔ)理論

SQL       是操作和檢索關(guān)系型數(shù)據(jù)庫的標(biāo)準(zhǔn)語言妓羊，標(biāo)準(zhǔn)SQL語句可用于操作然后關(guān)系型數(shù)據(jù)庫。
標(biāo)準(zhǔn)的SQL語句通常劃分為以下類型：
查詢語句:  主要由于select關(guān)鍵字完成稍计，查詢語句是SQL語句中最復(fù)雜躁绸，功能最豐富的語句。
DML  （Data Munipulation Language臣嚣，數(shù)據(jù)操作語言）語句净刮，這組DML語句修改后數(shù)據(jù)將保持較好的
一致性；操作表的語句硅则，如插入淹父、修改、刪除等怎虫；
DDL  （Data Definition Language暑认，數(shù)據(jù)定義語言）語句，操作數(shù)據(jù)對(duì)象的語言大审，有create蘸际、alter、
drop饥努。
DCL  （Data Control Language捡鱼，數(shù)據(jù)控制語言）語句，主要有g(shù)rant酷愧、revoke語句驾诈。
事務(wù)控制語句：  主要有commit、rollback和savepoint三個(gè)關(guān)鍵字完成
DDL語句是操作數(shù)據(jù)庫對(duì)象的語句溶浴，包括創(chuàng)建create乍迄、刪除drop、修改alter數(shù)據(jù)庫對(duì)象士败。

常見數(shù)據(jù)庫對(duì)象

上面的對(duì)象都可以通過用create假勿、alter借嗽、drop完成相關(guān)的創(chuàng)建、修改转培、刪除操作恶导。

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

常用查詢

MySQL結(jié)束符是“财喳；”結(jié)束察迟。
1、 顯示所有數(shù)據(jù)庫
show databases; 
2耳高、 刪除數(shù)據(jù)庫
drop database dbName;
3扎瓶、 創(chuàng)建數(shù)據(jù)庫
create database [if not exists] dbName;
中括號(hào)部分可選的，判斷該數(shù)據(jù)不存在就創(chuàng)建
4泌枪、 切換概荷、使用指定數(shù)據(jù)庫
use dbName;
5、 顯示當(dāng)前使用數(shù)據(jù)庫所有的表對(duì)象
show tables碌燕； 
6误证、 顯示表結(jié)構(gòu)describe（desc）
desc tableName;

7、 創(chuàng)建一張表
create table user (
--int 整型
uId int,
--小數(shù)
uPrice decimal,
--普通長(zhǎng)度文本修壕，default設(shè)置默認(rèn)值
uName varchar(255) default ‘zhangsan’,
--超長(zhǎng)文本
uRemark text,
--圖片
uPhoto blob,
--日期
uBirthday datetime
);
8愈捅、 子查詢建表方法
部分列名匹配模式：
create table userInfo (
   name varchar(20),
   sex char
)
as 
select name, sex from user;
上面的列名和子查詢的列名以及類型要對(duì)應(yīng)
全部列名模式：
create table userInfo
as
select * from user;
直接將整個(gè)表的類型和數(shù)據(jù)備份到新表userInfo中

9、 添加表字段
添加單列
alter table user add tel varchar(11) default ‘02012345678’;
 
添加多列
alter table user 
add (
photo blob,
birthday date
);
上面就同時(shí)增加了多列字段

10慈鸠、 修改表字段
修改tel列
alter table user modify tel varchar(15) default ‘02087654321’;
修改tel列的位置蓝谨，在第一列顯示
alter table user modify tel varchar(15) default '02087654321' first;
修改tel列的位置，在指定列之后顯示
alter table user modify tel varchar(15) default '02087654321' after age;
注意：alter modify不支持一次修改多個(gè)列青团，但是Oracle支持多列修改
但是MySQL可以通過多個(gè)modify的方式完成：
alter table user 
modify tel varchar(15) default '02087654321' first,
modify name varchar(20) after tel;

11譬巫、 刪除指定字段
alter table user drop photo;
 
12、 重命名表數(shù)據(jù)
表重命名
alter table user rename to users;
 
字段重命名
alter table users change name u_name varchar(10);
alter table users change sex u_sex varchar(10) after u_name;
如果需要改變列名建議使用change壶冒，如果需要改變數(shù)據(jù)類型和顯示位置可以使用modify

13缕题、 刪除表
drop table users;
drop刪除表會(huì)刪除表結(jié)構(gòu)，表對(duì)象將不存在數(shù)據(jù)中胖腾；數(shù)據(jù)也不會(huì)存在烟零；表內(nèi)的對(duì)象也不存在瘪松，如：索引、
視圖锨阿、約束宵睦；
 
truncate刪除表
truncate都被當(dāng)成DDL出來，truncate的作用就是刪除該表里的全部數(shù)據(jù)墅诡，保留表結(jié)構(gòu)壳嚎。相當(dāng)于DDL中
的delete語句，但是truncate比delete語句的速度要快得多末早。但是truncate不能帶條件刪除指定數(shù)據(jù)烟馅，
只會(huì)刪除所有的數(shù)據(jù)。如果刪除的表有外鍵然磷，刪除的速度類似于delete郑趁。但新版本的MySQL中truncate的
速度比delete速度快。

約束

MySQL中約束保存在information_schema數(shù)據(jù)庫的table_constraints中姿搜，可以通過該表查詢約束信息寡润；

約束主要完成對(duì)數(shù)據(jù)的檢驗(yàn)，保證數(shù)據(jù)庫數(shù)據(jù)的完整性舅柜；如果有相互依賴數(shù)據(jù)梭纹，保證該數(shù)據(jù)不被刪除。

常用五類約束：

not null：非空約束致份，指定某列不為空
unique： 唯一約束变抽，指定某列和幾列組合的數(shù)據(jù)不能重復(fù)
primary key：主鍵約束，指定某列的數(shù)據(jù)不能重復(fù)知举、唯一
foreign key：外鍵瞬沦，指定該列記錄屬于主表中的一條記錄，參照另一條數(shù)據(jù)
check：檢查雇锡，指定一個(gè)表達(dá)式逛钻，用于檢驗(yàn)指定數(shù)據(jù)
MySQL不支持check約束，但可以使用check約束锰提，而沒有任何效果曙痘；

根據(jù)約束數(shù)據(jù)列限制，約束可分為：

單列約束：每個(gè)約束只約束一列
多列約束：每個(gè)約束約束多列數(shù)據(jù)

MySQL中約束保存在information_schema數(shù)據(jù)庫的table_constraints中立肘，可以通過該表查詢約束信息边坤；

1、 not null約束

非空約束用于確保當(dāng)前列的值不為空值谅年，非空約束只能出現(xiàn)在表對(duì)象的列上茧痒。
Null類型特征：
所有的類型的值都可以是null，包括int融蹂、float等數(shù)據(jù)類型
空字符串“”是不等于null旺订，0也不等于null
create table temp(
id int not null,
name varchar(255) not null default ‘a(chǎn)bc’,
sex char null
)
上面的table加上了非空約束弄企，也可以用alter來修改或增加非空約束
增加非空約束
alter table temp
modify sex varchar(2) not null;
 
取消非空約束
alter table temp modify sex varchar(2) null;
 
取消非空約束，增加默認(rèn)值
alter table temp modify sex varchar(2) default ‘a(chǎn)bc’ null;

2区拳、 unique

唯一約束是指定table的列或列組合不能重復(fù)拘领，保證數(shù)據(jù)的唯一性。雖然唯一約束不允許出現(xiàn)重復(fù)的值樱调，但是可以為多個(gè)null

同一個(gè)表可以有多個(gè)唯一約束约素，多個(gè)列組合的約束。在創(chuàng)建唯一約束的時(shí)候笆凌，如果不給唯一約束名稱圣猎，就默認(rèn)和列名相同。

唯一約束不僅可以在一個(gè)表內(nèi)創(chuàng)建菩颖，而且可以同時(shí)多表創(chuàng)建組合唯一約束样漆。

MySQL會(huì)給唯一約束的列上默認(rèn)創(chuàng)建一個(gè)唯一索引；

create table temp (
id int not null,
name varchar(25),
password varchar(16),
--使用表級(jí)約束語法晦闰，
constraint uk_name_pwd unique(name, password)
);
表示用戶名和密碼組合不能重復(fù)
添加唯一約束
alter table temp add unique(name, password);
alter table temp modify name varchar(25) unique;
刪除約束
alter table temp drop index name;

3、 primary key

主鍵約束相當(dāng)于唯一約束+非空約束的組合鳍怨，主鍵約束列不允許重復(fù)呻右，也不允許出現(xiàn)空值；如果的多列組合的主鍵約束鞋喇，

那么這些列都不允許為空值声滥，并且組合的值不允許重復(fù)。

每個(gè)表最多只允許一個(gè)主鍵侦香，建立主鍵約束可以在列級(jí)別創(chuàng)建落塑，也可以在表級(jí)別上創(chuàng)建。MySQL的主鍵名總是PRIMARY罐韩，

當(dāng)創(chuàng)建主鍵約束時(shí)憾赁，系統(tǒng)默認(rèn)會(huì)在所在的列和列組合上建立對(duì)應(yīng)的唯一索引。

列模式：
create table temp(
/*主鍵約束*/
id int primary key,
name varchar(25)
);
 
create table temp2(
id int not null,
name varchar(25),
pwd varchar(15),
constraint pk_temp_id primary key(id)
);
 
組合模式：
create table temp2(
id int not null,
name varchar(25),
pwd varchar(15),
constraint pk_temp_id primary key(name, pwd)
);
 
alter刪除主鍵約束
alter table temp drop primary key;
 
alter添加主鍵
alter table temp add primary key(name, pwd);
 
alter修改列為主鍵
alter table temp modify id int primary key;
 
設(shè)置主鍵自增
create table temp(
id int auto_increment primary key,
name varchar(20),
pwd varchar(16)
);
auto_increment自增模式散吵，設(shè)置自增后在插入數(shù)據(jù)的時(shí)候就不需要給該列插入值了龙考。

4、 foreign key 約束

外鍵約束是保證一個(gè)或兩個(gè)表之間的參照完整性矾睦，外鍵是構(gòu)建于一個(gè)表的兩個(gè)字段或是兩個(gè)表的兩個(gè)字段之間的參照關(guān)系晦款。

也就是說從表的外鍵值必須在主表中能找到或者為空。

當(dāng)主表的記錄被從表參照時(shí)枚冗，主表的記錄將不允許刪除缓溅，如果要?jiǎng)h除數(shù)據(jù)，需要先刪除從表中依賴該記錄的數(shù)據(jù)赁温，

然后才可以刪除主表的數(shù)據(jù)坛怪。還有一種就是級(jí)聯(lián)刪除子表數(shù)據(jù)州藕。

注意：外鍵約束的參照列，在主表中引用的只能是主鍵或唯一鍵約束的列酝陈，假定引用的主表列不是唯一的記錄床玻，

那么從表引用的數(shù)據(jù)就不確定記錄的位置。同一個(gè)表可以有多個(gè)外鍵約束沉帮。

創(chuàng)建外鍵約束：

主表
create table classes(
id int auto_increment primary key,
name varchar(20)
);
從表
create table student(
id int auto_increment,
name varchar(22),
constraint pk_id primary key(id),
classes_id int references classes(id)
);
 
通常先建主表锈死，然后再建從表，這樣從表的參照引用的表才存在穆壕。
表級(jí)別創(chuàng)建外鍵約束：
create table student(
id int auto_increment primary key,
name varchar(25),
classes_id int,
foreign key(classes_id) references classes(id)
);
上面的創(chuàng)建外鍵的方法沒有指定約束名稱待牵，系統(tǒng)會(huì)默認(rèn)給外鍵約束分配外鍵約束名稱，命名為student_ibfk_n喇勋，
其中student是表名缨该，n是當(dāng)前約束從1開始的整數(shù)。

指定約束名稱：

create table student(
id int auto_increment primary key,
name varchar(25),
classes_id int,
/*指定約束名稱*/
constraint fk_classes_id foreign key(classes_id) references classes(id)
);
 
多列外鍵組合川背，必須用表級(jí)別約束語法：
create table classes(
id int,
name varchar(20),
number int,
primary key(name, number)
);
create table student(
id int auto_increment primary key,
name varchar(20),
classes_name varchar(20),
classes_number int,
/*表級(jí)別聯(lián)合外鍵*/
foreign key(classes_name, classes_number) references classes(name, number)
);

刪除外鍵約束：
alter table student drop foreign key student_ibfk_1;
alter table student drop foreign key fk_student_id;
 
增加外鍵約束
alter table student add foreign key(classes_name, classes_number) references classes(name, number);
 
自引用贰拿、自關(guān)聯(lián)（遞歸表、樹狀表）
create table tree(
id int auto_increment primary key,
name varchar(50),
parent_id int,
foreign key(parent_id) references tree(id)
);

級(jí)聯(lián)刪除：刪除主表的數(shù)據(jù)時(shí)熄云，關(guān)聯(lián)的從表數(shù)據(jù)也刪除膨更，則需要在建立外鍵約束的后面增加on delete
 cascade或on delete set null，前者是級(jí)聯(lián)刪除缴允，后者是將從表的關(guān)聯(lián)列的值設(shè)置為null荚守。

create table student(
id int auto_increment primary key,
name varchar(20),
classes_name varchar(20),
classes_number int,
/*表級(jí)別聯(lián)合外鍵*/
foreign key(classes_name, classes_number) references classes(name, number) on delete cascade
);

5、 check約束

MySQL可以使用check約束练般，但check約束對(duì)數(shù)據(jù)驗(yàn)證沒有任何作用矗漾。

create table temp(
id int auto_increment,
name varchar(20),
age int,
primary key(id),
/*check約束*/
check(age > 20)
);
上面check約束要求age必須大于0，但沒有任何作用薄料。但是創(chuàng)建table的時(shí)候沒有任何錯(cuò)誤或警告敞贡。

索引

索引是存放在模式（schema）中的一個(gè)數(shù)據(jù)庫對(duì)象，索引的作用就是提高對(duì)表的檢索查詢速度都办，

索引是通過快速訪問的方法來進(jìn)行快速定位數(shù)據(jù)嫡锌，從而減少了對(duì)磁盤的讀寫操作。

索引是數(shù)據(jù)庫的一個(gè)對(duì)象琳钉，它不能獨(dú)立存在势木，必須對(duì)某個(gè)表對(duì)象進(jìn)行依賴。

提示：索引保存在information_schema數(shù)據(jù)庫里的STATISTICS表中歌懒。

創(chuàng)建索引方式：
自動(dòng)：當(dāng)表上定義主鍵約束啦桌、唯一、外鍵約束時(shí)，該表會(huì)被系統(tǒng)自動(dòng)添加上索引甫男。
手動(dòng)：手動(dòng)在相關(guān)表或列上增加索引且改，提高查詢速度。

刪除索引方式：
自動(dòng)：當(dāng)表對(duì)象被刪除時(shí)板驳，該表上的索引自動(dòng)被刪除
手動(dòng)：手動(dòng)刪除指定表對(duì)象的相關(guān)列上的索引
索引類似于書籍的目錄又跛，可以快速定位到相關(guān)的數(shù)據(jù)，一個(gè)表可以有多個(gè)索引若治。

創(chuàng)建索引：
create index idx_temp_name on temp(name);

組合索引：
create index idx_temp_name$pwd on temp(name, pwd);

刪除索引：
drop index idx_temp_name on temp;

視圖

視圖就是一個(gè)表或多個(gè)表的查詢結(jié)果慨蓝，它是一張?zhí)摂M的表，因?yàn)樗⒉荒艽鎯?chǔ)數(shù)據(jù)端幼。

視圖的作用礼烈、優(yōu)點(diǎn)：

限制對(duì)數(shù)據(jù)的訪問

讓復(fù)雜查詢變得簡(jiǎn)單

提供數(shù)據(jù)的獨(dú)立性

可以完成對(duì)相同數(shù)據(jù)的不同顯示

創(chuàng)建、修改視圖
create or replace view view_temp
as
select name, age from temp;
通常我們并不對(duì)視圖的數(shù)據(jù)做修改操作婆跑，因?yàn)橐晥D是一張?zhí)摂M的表此熬，它并不存儲(chǔ)實(shí)際數(shù)據(jù)。如果想讓視圖
不被修改滑进，可以用with check option來完成限制犀忱。
create or replace view view_temp
as
select * from temp
with check option;
 
修改視圖：
alter view view_temp
as
select id, name from temp;
 
刪除視圖：
drop view view_temp;
 
顯示創(chuàng)建語法：
show create view v_temp;

DML語句

DML主要針對(duì)數(shù)據(jù)庫表對(duì)象的數(shù)據(jù)而言的，一般DML完成：

插入新數(shù)據(jù)

修改已添加的數(shù)據(jù)

刪除不需要的數(shù)據(jù)

1郊供、 insert into 插入語句

insert into temp values(null, ‘jack’, 25);
主鍵自增可以不插入峡碉，所以用null代替
 
指定列
insert into temp(name, age) values(‘jack’, 22);
在表面后面帶括號(hào)，括號(hào)中寫列名驮审，values中寫指定列名的值即可。當(dāng)省略列名就表示插入全部數(shù)據(jù)吉执，
注意插入值的順序和列的順序需要保持一致疯淫。
Set方式插入，也可以指定列
insert into temp set id = 7, name = 'jason';
 
MySQL中外鍵的table的外鍵引用列可以插入數(shù)據(jù)可以為null戳玫，不參照主表的數(shù)據(jù)熙掺。
 
使用子查詢插入數(shù)據(jù)
insert into temp(name) select name from classes;
 
多行插入
insert into temp values(null, ‘jack’, 22), (null, ‘jackson’ 23);

2、 update 修改語句

update主要完成對(duì)數(shù)據(jù)的修改操作咕宿，可以修改一條或多條數(shù)據(jù)币绩。修改多條或指定條件的數(shù)據(jù)，需要用where條件來完成府阀。

修改所有數(shù)據(jù)
update temp set name = ‘jack2’;
所有的數(shù)據(jù)的name會(huì)被修改缆镣，如果修改多列用“,”分開
update temp set name = ‘jack’, age = 22;
修改指定條件的記錄需要用where
update temp set name = ‘jack’ where age > 22;

3、 delete 刪除語句

刪除table中的數(shù)據(jù)试浙，可以刪除所有董瞻，帶條件可以刪除指定的記錄。

刪除所有數(shù)據(jù)
delete from temp;
刪除指定條件數(shù)據(jù)
delete from temp where age > 20;

select 查詢、function 函數(shù)

select查詢語句用得最廣泛钠糊、功能也最豐富挟秤。可以完成單條記錄抄伍、多條記錄艘刚、單表、多表截珍、子查詢等攀甚。

1、 查詢某張表所有數(shù)據(jù)
select * from temp;
*代表所有列笛臣，temp代表表名云稚，不帶條件就查詢所有數(shù)據(jù)
 
2、 查詢指定列和條件的數(shù)據(jù)
select name, age from temp where age = 22;
查詢name和age這兩列沈堡，age 等于22的數(shù)據(jù)静陈。
 
3、 對(duì)查詢的數(shù)據(jù)進(jìn)行運(yùn)算操作
select age + 2, age / 2, age – 2, age * 2 from temp where age – 2 > 22;
 
4诞丽、 concat函數(shù)鲸拥，字符串連接
select concat(name, ‘-eco’) from temp;
concat和null進(jìn)行連接，會(huì)導(dǎo)致連接后的數(shù)據(jù)成為null
 
5僧免、 as 對(duì)列重命名
select name as ‘名稱’ from temp;
as也可以省略不寫刑赶，效果一樣
如果重命名的列名出現(xiàn)特殊字符，如“‘”單引號(hào)懂衩，那就需要用雙引號(hào)引在外面
select name as “名’稱” from temp;

6撞叨、 也可以給table去別名
select t.name Name from temp as t;
 
7、 查詢常量
類似于SQL Server
select 5 + 2;
select concat('a', 'bbb');
 
8浊洞、 distinct 去掉重復(fù)數(shù)據(jù)
select distinct id from temp;
多列將是組合的重復(fù)數(shù)據(jù)
select distinct id, age from temp;
 
9牵敷、 where 條件查詢
大于>、大于等于>=法希、小于<枷餐、小于等于<=、等于=苫亦、不等于<>
都可以出現(xiàn)在where語句中
select * from t where a > 2 or a >= 3 or a < 5 or a <= 6 or a = 7 or a <> 0;
 
10毛肋、 and 并且
select * from temp where age > 20 and name = ‘jack’;
查詢名稱等于jack并且年齡大于20的

11、 or 或者
滿足一個(gè)即可
select * from tmep where name = ‘jack’ or name = ‘jackson’;
 
12屋剑、 between v and v2
大于等于v且小于等于v2
select * form temp where age between 20 and 25;
 
13润匙、 in 查詢
可以多個(gè)條件 類似于or
select * from temp where id in (1, 2, 3);
查詢id在括號(hào)中出現(xiàn)的數(shù)據(jù)
 
14、 like 模糊查詢
查詢name以j開頭的
select * from temp where name like ‘j%’;
 
查詢name包含k的
select * from temp where name like ‘%k%’;
 
escape轉(zhuǎn)義
select * from temp where name like ‘\_%’ escape ‘\’;
指定\為轉(zhuǎn)義字符饼丘，上面的就可以查詢name中包含“_”的數(shù)據(jù)
 
15趁桃、 is null、is not null
查詢?yōu)閚ull的數(shù)據(jù)
select * from temp where name is null;
查詢不為null的數(shù)據(jù)
select * from temp where name is not null;

16、 not
select * from temp where not (age > 20);
取小于等于20的數(shù)據(jù)
select * from temp where id not in(1, 2);
 
17卫病、 order by
排序油啤，有desc、asc升序蟀苛、降序
select * from temp order by id;
默認(rèn)desc排序
select * from temp order by id asc;
多列組合
select * from temp order by id, age;