#############本地連接MySQL的語法：快捷鍵 command+R CMD ###########
# mysql -uroot -p 回車可以隱藏密碼 u為用戶名，P為密碼；
# ?Enter password: ******
############# 加IP地址連接MySQL的語法：##########
# mysql -h ip地址 -P 3306 -uroot -p 123456 (本地IP地址表達(dá)方式： localhost=127.0.0.1 );
############# 退出MySQL########
#mysql>exist/quit;

######## DDL #################
————————————————創(chuàng)建數(shù)據(jù)庫—
create database if not exists db_lianjing charset utf8;# create database [其他] 數(shù)據(jù)庫名 [設(shè)置編碼格式];
# 查看有哪些數(shù)據(jù)庫
show databases;
# 查看數(shù)據(jù)庫的創(chuàng)建語句
show create database db_lianjing;#show create database 數(shù)據(jù)庫名;
#當(dāng)需要切換及使用另一個數(shù)據(jù)庫時,務(wù)必切換至所需數(shù)據(jù)庫；
use db_lianjing;#use 數(shù)據(jù)庫名;
#刪除數(shù)據(jù)庫的語法
drop database db_lianjing;#drop database 數(shù)據(jù)庫名稱;
#查看當(dāng)前正在使用的數(shù)據(jù)庫名
select database();
#————————————————————創(chuàng)建數(shù)據(jù)表—————————————————
#創(chuàng)建數(shù)據(jù)表：create table 表名(
? #字段名1 數(shù)據(jù)類型(長度) [約束],
? #字段名2 數(shù)據(jù)類型(長度) [約束],
# ?字段名3 數(shù)據(jù)類型(長度) [約束],
# ?...
# );
use db_lianjing;
create table if not exists tb_lianjing(
? ?id int primary key auto_increment, #主鍵自動增長，當(dāng)給字段添加自動增長后，可以不給自動增長列添加值，且會設(shè)定自動整數(shù)值。
? ?name varchar(20),
? ?gender varchar(10)
) engine =InnoDB default charset utf8;
#查看所有數(shù)據(jù)表
show tables ;#查看所有數(shù)據(jù)表；
# 查看數(shù)據(jù)表的創(chuàng)建語句
show create table tb_lianjing;#show create table 表名;
# 查看表結(jié)構(gòu)
desc tb_lianjing;# #desc 表名;
#刪除數(shù)據(jù)表語法：
drop table tb_lianjing;#drop table 表名;
#修改數(shù)據(jù)表 alter table 表名 add/modify/change/drop 字段名 類型(長度);rename 舊表名 to 新表名;
#添加一列 alter table 表名 add 字段名 類型(長度) [first|after 其他字段名稱];
alter table ?tb_lianjing add hobby varchar(20);
alter table ?tb_lianjing add strange varchar(20) first;
alter table ?tb_lianjing add age int after name;
#修改列類型 alter table 表名 modify 要修改的字段名 類型(長度) [約束]; ? # 不為空
alter table ?tb_lianjing modify strange char(10);
#修改列名 alter table 表名 change 舊列名 新列名 類型(長度) [約束];當(dāng)舊列名被修改后，則無法使用舊列名來操作數(shù)據(jù)。
alter table tb_lianjing change strange competiter varchar(20);
#刪除一列 alter table 表名 drop 列名;
alter table tb_lianjing drop competiter;
#修改表名 rename table 表名 to 新表名;
rename table tb_lianjing to tb_newlianjing;
#alter tbale tb_lianjing rename to tb_newlianjing;可運(yùn)行，但不推薦；

############## DML ################
use db_lianjing;
#插入(insert)數(shù)據(jù)，常見語法：#insert into 表名(字段名1,字段名2,字段名3,...) values(值1,值2,值3,...);
insert into tb_newlianjing values (5,'ass','male','love',10); #如所有字段都賦值，字段名可省略；可一次性插入多條數(shù)據(jù)之間使用,（逗號）分隔。
insert into tb_newlianjing (id, name, gender) VALUES (1,'lianjing','female');#1）當(dāng)要給字符串類型的字段插入值時，要使用單引號把值引起來，否則顯示出錯；
insert into tb_newlianjing (name, gender) values ('duli','male');##表名后字段個數(shù)與類型等，要與values后的值對應(yīng)，字段名順序可調(diào)節(jié)。
#修改(update)數(shù)據(jù)，常見語法：#update 表名 set 字段名1=值,字段名2=值,字段名3=值,... where 條件;
update tb_newlianjing set name= 'zhuzuh',gender ='male' where id=4;#如不指定where條件，則該字段下所有數(shù)據(jù)均被修改；
#刪除(delete)數(shù)據(jù)，常見語法：#delete from 表名 where 條件;
delete from tb_newlianjing where id=4; #如不添加where 條件語句，則整張表的數(shù)據(jù)均被清除。（表頭字段的DDL語句，不會受到影響）；
#truncate清空數(shù)據(jù)；truncate ?[table] 表名;不可以帶條件刪除，適用于刪除銷毀大量無效數(shù)據(jù)，原理是刪除表格再創(chuàng)建一張新的空白表格，自增序列會清零。
truncate tb_newlianjing;
################## 設(shè)定引擎 ##########################
# MySQL引擎常用的為INnoDB（事務(wù)型，大數(shù)據(jù)開發(fā)常用，行鎖），MyISAM（注重性能，不能處理事務(wù)型命令，表鎖）
# create table 表名(
# ? ? 字段名 數(shù)據(jù)類型(長度) [約束],
# ? ? ...
# # ) engine = 引擎名 default charset utf8;
###################### SQL約束 #############################

# 1.主鍵 ? （ primary key ） MySQL建議所有表的主鍵字段都叫id，類型為int。
# 2.非空 ? （ not null ） 此字段不允許填寫空值。
# 3.唯一 ? （ unique ?） 此字段的值不允許重復(fù)。
# 4.默認(rèn)值 ?（ default ） 當(dāng)不填寫字段對應(yīng)的值時，會使用默認(rèn)值；如果填寫時，則以填寫為準(zhǔn)。
# 5.外鍵 ? （ foreign key ） 涉及到多張表時才會使用。<br/>對表與表關(guān)系字段進(jìn)行約束，當(dāng)為表與表關(guān)系字段填寫值時，會到關(guān)聯(lián)的表中查詢此值是否存在。<br>如果存在，則填寫成功；如果不存在，則填寫失敗。
# create table 表名(
# ?字段名 數(shù)據(jù)類型(長度) primary key [其他約束],
# ?...
# ) engine = 引擎名 default charset utf8;
# 使用alter table關(guān)鍵字刪除主鍵
alter table tb_newlianjing drop primary key;#刪除主鍵約束后，該字段依舊有非空約束，不能為空。

################## ?DQL #############################select [*|字段名1,字段名2,...|函數(shù)(...)] from 表名 where 條件 另外的要求;
#簡單查詢 select [distinct] 字段名1,字段名2,... from 表名; select * from 表名：查看所有字段的數(shù)據(jù)。
select * from tb_newlianjing;
select name from tb_newlianjing;
select ?name ,gender from tb_newlianjing;
select distinct ?gender from tb_newlianjing;#distinct 去除重復(fù)數(shù)據(jù)；
# 取外號/別名 字段名 as 別名
SELECT name AS NA FROM tb_newlianjing;
SELECT name NA FROM tb_newlianjing; #as可省略；
#比較查詢 select [*|字段名1, 字段名2, ...] from 表名 where 條件; 比較運(yùn)算符有>,<,=,>=,<=;不等于：!= 或 <>;
select * from tb_newlianjing where ?id=3;
select name from tb_newlianjing where id >2;
#范圍查詢
# 1.in是用于非連續(xù)值的范圍查詢，語法：select * from 表名 where 字段名 in (范圍值1,范圍值2,...);
select * from tb_newlianjing where ?id in (1,2);
# 2. between and 是用于值在連續(xù)范圍的查詢，語法：select * from 表名 where 字段名 between 范圍值1 and 范圍值2; 閉區(qū)間，包含值1和值2；
select * from tb_newlianjing where id between 1 and 3;
#邏輯查詢 select [*|字段名1, 字段名2, ...] from 表名 where 條件; and 和/且， or 或，not 非；
select name from tb_newlianjing where id=3 or gender = 'female';
select ?name, id from tb_newlianjing where ?not (id =3); #增加括號可提升代碼閱讀性
#模糊查詢 select * from 表名 where 字段名 like '%某個字%'; 或 select * from 表名 where 字段名 like '某個字_';
select * from tb_newlianjing where name like '%a%';#%代表通配符，是替代任意多個字符。
select * from tb_newlianjing where gender like 'f_____';#_下劃線表示一個字符，位置和數(shù)量均會影響查詢結(jié)果。
#非空查詢 select * from 表名 where 字段名 [條件]; is null 判斷為空; is not null 判斷為非空;
select ?* from tb_newlianjing where id is null;
#排序查詢 select * from 表名 where 條件 order by 字段名 [asc|desc];(asc 可省略， 默認(rèn)升序。desc 降序）
select * from tb_newlianjing order by id desc;
select ?* from tb_newlianjing where gender='female' order by id;
#聚合查詢 select 函數(shù)(...) from 表名 [where 條件];注意：聚合函數(shù)會忽略空值進(jìn)行統(tǒng)計處理。
# ?count(col) | 用于統(tǒng)計指定列不為null的總數(shù)據(jù)條數(shù)。
# ?sum(col) ? | 用于計算指定列的數(shù)值和。
# ?max(col) ? | 用于計算指定列的最大值。
# ?min(col) ? | 用于計算指定列的最小值。
# ?avg(col) ? | 用于計算指定列的平均值。
select sum(id) from tb_newlianjing where gender='female';
select ?count(*) from tb_newlianjing;
# limit分頁查詢 #select 字段名1,字段名2,... from 表名 limit M, N;M為整數(shù)，表示從第幾條索引開始，默認(rèn)值是0；N為整數(shù)，表示查詢多少條數(shù)據(jù)。
select * from tb_newlianjing order by id desc limit 2;
select * from tb_newlianjing order by id asc limit 0, 2;
# 分組查詢 將查詢結(jié)果按照指定字段分組，其中，數(shù)值相等的可劃分為同一組。select 字段名1,字段名2,... from 表名 group by 分組字段名 [having 分組條件];
select ?gender from person group by ?gender;
#使用group by + 聚合函數(shù)進(jìn)行分組查詢 select 字段名1,字段名2,聚合函數(shù)(...) from 表名 group by 分組字段名;
select ?gender, avg (age) from person group by ?gender;
# select 字段名1,字段名2,... from 表名 group by 分組字段名 having 分組條件;
select gender,count(*) from person ?group by ?gender having gender='female';#having 優(yōu)先級最低，where 放在 group by 前面，優(yōu)先級高于group by。
select gender,count(*) from person ?group by ?gender having count(gender)>2;

############################################################################################
create database consumer;
use consumer;
# 創(chuàng)建數(shù)據(jù)表
create table if not exists person
(
? ?id ? ? int primary key auto_increment not null,
? ?name ? varchar(18),
? ?age ? ?int,
? ?gender varchar(10),
? ?height double
)engine = InnoDB default charset utf8;
# 插入數(shù)據(jù)
insert into person(name, age, gender, height) values ('洪七公', 76, 'Male', 1.6);
insert into person(name, age, gender, height) values ('歐陽峰', 68, 'Male', 1.65);
insert into person(name, age, gender, height) values ('郭靖', 43, 'Male', 1.7);
insert into person(name, age, gender, height) values ('黃蓉', 39, 'Female', 1.59);
insert into person(name, age, gender, height) values ('楊過', 26, 'Male', 1.8);
insert into person(name, age, gender, height) values ('小龍女', 33, 'Female', 1.65);
insert into person(name, age, gender, height) values('郭芙', 18, 'Female', 1.60);
insert into person(name, age, gender, height) values ('李莫愁', 35, 'Female', 1.65);
# 查詢數(shù)據(jù)
select * from person;
select ?gender from person group by ?gender;
select ?sum(height), avg (age) from person group by ?gender;
select gender,count(*) from person ?where ?gender='female' group by ?gender;
select gender,count(*) from person ?group by ?gender having count(gender)>2;

###################### 外鍵約束 ? #############################
# 兩個有關(guān)系的表，可以通過了某個字段來建立連接，這個字段在第一張表A中是主鍵primary key，在第二張表B中，就稱為外鍵約束foreign key。
# create table 表名(
# ?...
# ?字段名 數(shù)據(jù)類型(長度) [約束],
# ?foreign key (字段名) references 另外表名(主鍵列名)
# ) engine = InnoDB default charset utf8;
create table if not exists tb_product(
? ?pid int primary key auto_increment,
? ?age ?int ,
? ?name varchar(20),
? ?person_id int,
? ?foreign key (person_id) references person(id) #外鍵命名一般為 主鍵表名_id 字段類型需要與主鍵完全一致。需提前將兩個關(guān)聯(lián)表格創(chuàng)建好。
)engine =InnoDB default ?charset utf8;#設(shè)置引擎為InnoDB,否則可能無法成功設(shè)定外鍵約束。

# 通過外部添加外鍵 alter table 表A add constraint xxxfk foreign key (外鍵字段名) references 另外表名(主鍵字段名);
alter ?table tb_product add foreign key (person_id) references person (id);

# 當(dāng)數(shù)據(jù)表之間產(chǎn)生關(guān)聯(lián)后，需要注意：
# （1）不能給表B（外鍵）添加一個表A不存在的主鍵字段值；
# （2）不能刪除表B（外鍵）已使用表A存在的主鍵字段值。
######################### ?子查詢 #########################################
#子查詢指的是將一條select語句的結(jié)果，作為另一條select語句的一部分（如：查詢條件、查詢結(jié)果、表等），來進(jìn)行數(shù)據(jù)查詢。
# select *|字段名1,字段名2,.. from 表名 where 字段名 = (另外select語句的結(jié)果);
select * from category where cid=(select category_id from product where pname='格力');
# 注釋快捷鍵：ctrl+/