-- 連接認(rèn)證
mysql.exe -h localhost -P 3306 -u root -p
mysql -u root -p
-h 找到電腦里的主機(jī)地址
-P 大P-默認(rèn)端口3306
-p 小p-密碼
-u user名字

show databases; -- 查看所有數(shù)據(jù)庫(kù)

-- 退出命令
exit,quit,\q

-- 創(chuàng)建數(shù)據(jù)庫(kù)
create database mydatabase charset utf8;

-- 創(chuàng)建關(guān)鍵字?jǐn)?shù)據(jù)庫(kù)
create database database charset utf8;

-- 告訴服務(wù)器當(dāng)前中文的字符集是什么
set names gbk;

-- 創(chuàng)建中文數(shù)據(jù)庫(kù)
create database 中國(guó) charset utf8;

-- 創(chuàng)建數(shù)據(jù)庫(kù)
create database informationtest charset utf8;

-- 查看以information_開(kāi)始的數(shù)據(jù)庫(kù) (匹配需要被轉(zhuǎn)義)
show databases like 'information_%';
show databases like 'information%'; --相當(dāng)于information%

-- 查看數(shù)據(jù)庫(kù)的創(chuàng)建語(yǔ)句
show create database mydatabase;
show create database 'database';

-- 修改數(shù)據(jù)庫(kù)informationtest的字符集
alter database informationtest charset GBK;

-- 刪除數(shù)據(jù)庫(kù) -- 數(shù)據(jù)庫(kù)名字
drop database informationtest;

-- 創(chuàng)建表
create table if not exists mydatabase.student (
-- 顯示的將student表放到mydatabase數(shù)據(jù)庫(kù)下面
name varchar(10),
gender varchar (10),
number varchar (10),
age int
)charset utf8;

-- 創(chuàng)建數(shù)據(jù)表 --常用的
-- 進(jìn)入數(shù)據(jù)庫(kù)
use mydatabase;
--創(chuàng)建表
create table class (
name varchar (10),
room varchar (10)
) charset utf8;

-- 查看所有表
show tables;

-- 查看以s結(jié)尾的表
show tables like '%s';

-- 查看表的創(chuàng)建語(yǔ)句：
show create table student;
show create table student\g -- \g 相當(dāng)于 ;
show create table student\G -- \G 將查到的結(jié)構(gòu)旋轉(zhuǎn)90度變成縱向---------"方便觀察"

-- 查看表結(jié)構(gòu)
desc class;
describe class;
show columns from class;

-- 重命名表（student表 -> tal_student）
rename table student to my_student;

-- 修改表選項(xiàng)：字符集
alter table my_student charset = GBK;

-- 查看創(chuàng)建表的語(yǔ)句
show create table my_student;

-- 查看表結(jié)構(gòu)
desc my_student;

-- 給學(xué)生表增加ID罐柳， first---放到第一個(gè)位置 column 列字段的意思
alter table my_student add column id int first;

-- 將學(xué)生表中的number學(xué)號(hào)字段變成固定長(zhǎng)度，且放到第二位（ID之后）
alter table my_student modify number char(10) after id;

-- 修改學(xué)生表中的gender字段為sex
alter table my_student change gender sex varchar (10);

-- 刪除學(xué)生表中的年齡字段
alter table my_student drop age;

-- 刪除數(shù)據(jù)表
drop table class;

-- 插入數(shù)據(jù)
insert into my_student values
(1,'bc20190001','jim','male'),
(2,'bc20190002','Lily','female');

-- 插入數(shù)據(jù)：指定字段列表
insert into my_student (number,sex,name,id) values
('ba20190003','male','Tom',3),
('ba20190004','female','LUcy',4);

-- 查看所有數(shù)據(jù)
select * from my_student;

-- 查看指定字段狰住、指定條件的數(shù)據(jù)
-- 查看滿足ID為1的學(xué)生信息
select id,number,sex,name from my_student where id=1;

-- 更新數(shù)據(jù) where條件 先開(kāi)始的表名
update my_student set sex='female' where name='jim';

-- 刪除數(shù)據(jù)
delete from my_student where sex='male';

**************************** 第二天的 *******************************

-- 創(chuàng)建整型表
create table my_int(
int_1 tinyint,
int_2 smallint,
int_3 int,
int_4 bigint
) charset utf8;

-- 插入數(shù)據(jù)
insert into my_int
values(100,100,100,100); --有效數(shù)據(jù)
insert into my_int
values('a','b','199','f'); --無(wú)效數(shù)據(jù)张吉，類(lèi)型限定
insert into my_int
values(255,10000,100000,1000000); --錯(cuò)誤的寫(xiě)法，超出范圍

-- 給表增加一個(gè)無(wú)符號(hào)的類(lèi)型
alter table my_int add int_5
tinyint unsigned; -- 無(wú)符號(hào)類(lèi)型
-- 插入數(shù)據(jù)
insert into my_int
values (127,10000,100000,100000,255);

-- 指定顯示寬度為1
alter table my_int add int_6
tinyint(1) unsigned;
-- 插入數(shù)據(jù)
insert into my_int
values (127,0,0,0,255,255);

-- 顯示寬度為2催植，0填充
alter table my_int add int_7
tinyint(2) zerofill; -- zerofill; 自動(dòng)變成 unsigned無(wú)符號(hào)
-- 插入數(shù)據(jù)
insert into my_int
values (1,1,1,1,1,1,1);
insert into my_int
values (100,100,100,100,100,100,100);

-- 浮點(diǎn)數(shù)表
create table my_float(
f1 float,
f2 float(10,2), -- 10位在精度范圍之外
f3 float(6,2) -- 6位在精度范圍之內(nèi)
);

-- 插入數(shù)據(jù)
insert into my_float
values(1000.10,1000.10,1000.10);
insert into my_float
values(1234567890,12345678.90,123.56);
insert into my_float
values(3e38,3.01e7,1234.56);
insert into my_float
values(9999999999,99999999.99,9999.99); -- 后兩個(gè)是最大值

-- 超出長(zhǎng)度插入數(shù)據(jù)
insert into my_float
values (123456,1234.12345678,123.9876543); -- 小數(shù)部分可以超出長(zhǎng)度
insert into my_float
values (132456,1324.12,123456.56); -- 最后一個(gè)整數(shù)部分超出

-- 創(chuàng)建定點(diǎn)數(shù)表
create table my_decimal(
f1 float (10,2),
d1 decimal (10,2)
);

-- 插入數(shù)據(jù)
insert into my_decimal
values (12345678.90,12346578.90); -- 有效數(shù)據(jù)
insert into my_decimal
values (1234.123456,1234.123456); -- 小數(shù)部分是可以超出的

-- 查看警告
show warnings;

-- 插入數(shù)據(jù)
insert into my_decimal
values (99999999.99,99999999.99); -- 沒(méi)有問(wèn)題
insert into my_decimal
values (99999999.99,99999999.999); -- 進(jìn)位超出范圍

-- 創(chuàng)建時(shí)間日期表
create table my_date(
d1 datetime,
d2 date,
d3 time,
d4 timestamp,
d5 year
);

-- 插入數(shù)據(jù)
insert into my_date
values ('2019-11-21 13:38:36',
'2019-11-21','13:38:36',
'2019-11-21 13:38:36','2019');
-- 時(shí)間使用負(fù)數(shù)
insert into my_date
values ('2019-11-21 13:38:36',
'2019-11-21','-13:38:36',
'2019-11-21 13:38:36','2019');
insert into my_date
values ('2019-11-21 13:38:36',
'2019-11-21','-213:38:36',
'2019-11-21 13:38:36','2019');
insert into my_date
values ('2019-11-21 13:38:36',
'2019-11-21','-2:38:36',
'2019-11-21 13:38:36','2019'); -- -2表示過(guò)去2天肮蛹，就是48小時(shí) -- year可以使用2位或者4位
insert into my_date
values ('2019-11-21 13:38:36',
'2019-11-21','13:38:36',
'2019-11-21 13:38:36','69');
insert into my_date
values ('2019-11-21 13:38:36',
'2019-11-21','13:38:36',
'2019-11-21 13:38:36','70');

-- timestamp；修改記錄
update my_date set d1=
'2019-11-21 13:46:45' where d5=2069;

-- 創(chuàng)建枚舉表
create table my_enum(
gender enum('男','女','保密')
);

-- 插入數(shù)據(jù)
insert into my_enum
values ('男'),('保密'); -- 有效數(shù)據(jù)
insert into my_enum
values ('male'); -- 錯(cuò)誤數(shù)據(jù)创南，沒(méi)有該元素

-- 將字段結(jié)果取出來(lái)進(jìn)行+0運(yùn)算
select gender + 0, gender
from my_enum;

-- 數(shù)值插入枚舉元素
insert into my_enum
values (1),(2);

-- 創(chuàng)建班級(jí)表
create table my_class(
name varchar (20) not null,
room varchar (20) null -- 代表允許為空伦忠，不寫(xiě)默認(rèn)就是允許為空
);

-- 創(chuàng)建表
create table my_teacher(
name varchar (20) not null comment
'姓名',
money decimal (10,2) not null
comment '工資'
);

-- 默認(rèn)值
create table my_default(
name varchar (20) not null,
age tinyint unsigned default 0,
gender enum('男','女','保密')
default '男'
);

-- 插入數(shù)據(jù)
insert into my_default (name)
values('臭蛋');
insert into my_default
values ('男閨蜜',18,default );

-- 增加主鍵
create table my_pri1(
name varchar (20) not null comment
'姓名',
number char (10) primary key
comment '學(xué)號(hào)：bc2019+0001，不能重復(fù)'
);

-- 復(fù)合主鍵
create table my_pri2(
number char (10) comment
'學(xué)號(hào): bc20190001',
course char (10) comment
'課程代碼：bc25890001',
score tinyint unsigned default 60
comment '成績(jī)',
-- 增加主鍵限制：學(xué)號(hào)和課程號(hào)應(yīng)該是對(duì)應(yīng)的稿辙，具有唯一性
primary key(number, course)
);

-- 追加主鍵
create table my_pri3(
course char (10) not null comment
'課程代碼：bc25890001',
name varchar (10) not null comment
'課程名字'
);
-- 第一種方式
alter table my_pri3 modify course
char(10) primary key comment
'課程代碼:bc25890001';
-- 把第一種用drop table my_pri3; 刪除然后在創(chuàng)建一個(gè)在用第二種方式
-- 刪除表
drop table my_pri3;
-- 第二種方式
alter table my_pri3 add primary
key(course);

alter table my_pri3 add primary
key(course); -- 也可以是復(fù)合主鍵

*************************** 第三天 *****************************

-- 向pri1昆码、2表插入數(shù)據(jù)
insert into my_pri1
values ('古天樂(lè)','bc20190001'),
('臭蛋','bc20190002');
insert into my_pri2
values('bc20190001','bc25890001',90),
('bc2019002','bc25890002',85),
('bc2019003','bc25890003',92);

-- 主鍵沖突（重復(fù)）
insert into my_pri1
values('劉濤','bc20190002'); -- 不可以；主鍵沖突
insert into my_pri2
values('bc20190001','bc25890001',100); -- 不可以邻储；主鍵沖突

-- 刪除主鍵
alter table my_pri3 drop primary key;

-- 自增長(zhǎng)
create table my_auto(
id int primary key
auto_increment comment '自動(dòng)增長(zhǎng)',
name varchar(10) not null
)charset utf8;

-- 觸發(fā)自增長(zhǎng)
insert into my_auto(name)
values ('鄧麗君');
insert into my_auto
values (null,'成龍');
insert into my_auto
values (default ,'吳綺莉');

-- 指定數(shù)據(jù)
insert into my_auto values (6,
'黃曉明');
insert into my_auto values (null,
'楊穎');

-- 修改表選項(xiàng)的值
alter table my_auto
auto_increment=4; -- 向下修改（改懈逞省） 不生效
alter table my_auto
auto_increment=10; -- 向上修改（改大） 生效

-- 查看自增長(zhǎng)變量
show variables like
'auto_increment%';

-- 修改自增長(zhǎng)的步長(zhǎng)
set auto_increment_increment=5; -- 一次自增5

-- 插入記錄；使用自增長(zhǎng)
insert into my_auto values(null,
'楊紫');
insert into my_auto values(null,
'張一山');

-- 刪除自增長(zhǎng)
alter table my_auto modify id int
primary key; -- 錯(cuò)誤芥备；主鍵在理論上是單獨(dú)存在的（系統(tǒng)會(huì)認(rèn)為我們要在加一個(gè)主鍵）
alter table my_auto modify id int; -- 有主鍵的時(shí)候冬耿，千萬(wàn)不要在加主鍵

-- 創(chuàng)建一個(gè)唯一鍵
create table my_unique1(
number char (10) unique comment
'學(xué)號(hào)：唯一，允許為空',
name varchar (20) not null
)charset utf8;

create table my_unique2(
number char (10) not null comment
'學(xué)號(hào)',
name varchar (20) not null,
-- 增加唯一鍵
unique key(number)
)charset utf8;

create table my_unique3(
id int primary key auto_increment,
number char (10) not null,
name varchar (20) not null
)charset utf8;

-- 追加唯一鍵
alter table my_unique3 add unique
key(number);-- 給number增加唯一鍵

alter table my_unique3 add unique
key(name);-- 給name增加唯一鍵

-- 插入數(shù)據(jù)
insert into my_unique1
values (null, '大熊'),
('bc20190001','胖虎'),
(null,'靜香');

insert into my_unique1
values ('bc20190001','哆啦A夢(mèng)');

-- 刪除唯一鍵 -----刪除的是索引 index
alter table my_unique3 drop index number;

-- 自關(guān)聯(lián) 自關(guān)聯(lián)就是看房山區(qū)的父id是哪個(gè)萌壳，是1 北京就是父id
-- id name pid
-- 1 北京 null
-- 2 朝陽(yáng)區(qū) 1
-- 3 房山區(qū) 1
-- 4 吉林省 null
-- 5 長(zhǎng)春 4
-- 6 吉林市 4

-- 空間換時(shí)間
-- 商品表
-- id name pic
-- 商品樣式表
-- id pid style pic

**************************** 第四天 *************************

-- 給班級(jí)表增加主鍵
alter table my_class add primary
key(name);

-- 插入數(shù)據(jù)
insert into my_class values (
'python1907','B408');
insert into my_class values (
'python1907','B407'); -- 錯(cuò)誤；主鍵沖突

insert into my_class values (
'python1907','B407')
-- 沖突處理
on duplicate key update
-- 更新教室
room='B407';

insert into my_class values (
'python1903','B408');

-- 主鍵沖突日月；替換
replace into my_class values (
'python1903','B406');
replace into my_class values (
'python1910','B409');

-- 復(fù)制創(chuàng)建表,
create table my_copy like my_class;

-- 刪除主鍵
alter table my_copy drop primary key;
-- 在MySQL中去掉一個(gè)表的主鍵需要分2中的情況袱瓮；
-- 1；該列（column）不光設(shè)置為主鍵（primary key）爱咬，
-- 還有自增長(zhǎng)（auto_increment）尺借；
-- 句式：alter table +表名+ modify id int, drop primary key;
-- 2、如果沒(méi)有設(shè)置為自增長(zhǎng)（auto_increment）精拟，
-- 那么可以直接刪除主鍵（primary key ）;
-- 句式：alter table +表名+drop primary key;

-- 蠕蟲(chóng)復(fù)制-------用來(lái)測(cè)試表的壓力以及效率
insert into my_copy select * from my_class;
insert into my_copy select * from my_copy;

-- 更新部分B406變成A406
update my_copy set room='A408' where room='B408' limit 3;

-- 刪除數(shù)據(jù)燎斩；限制記錄數(shù)為5
delete from my_copy where room='B409' limit 5;

-- 給學(xué)生表增加主鍵
alter table my_student modify id int primary key auto_increment;

-- 清空表，重置自增長(zhǎng)
truncate my_student;

-- 以下三句有什么區(qū)別蜂绎？
delete from 表名; -- 只刪除數(shù)據(jù)
truncate 表名; -- 刪除數(shù)據(jù)栅表，重置自增長(zhǎng)
drop table 表名; -- 刪除數(shù)據(jù)表

-- select 選項(xiàng)
select * from my_copy;
select all * from my_copy;

-- 去重
select distinct * from my_copy;

-- 向?qū)W生表插入數(shù)據(jù)
insert into my_student values(null,'bc20190001','張三','男'),
(null,'bc20190002','李四','男'),
(null,'bc20190003','王五','女'),
(null,'bc20190004','趙柳','男'),
(null,'bc20190005','六七','男');

-- 字段別名
select id,
number as 學(xué)號(hào),
name as 姓名,
sex 性別 from my_student;

-- 多表數(shù)據(jù)源------數(shù)據(jù)源：?jiǎn)伪頂?shù)據(jù)源、多表數(shù)據(jù)源师枣、查詢(xún)語(yǔ)句怪瓶、子查詢(xún)
-- 只要返回的是個(gè)二維表那就是數(shù)據(jù)源
select * from my_student,my_class;

-- 子查詢(xún)
select * from (select * from my_student) as s;

-- 增加age年齡和height
alter table my_student add age tinyint unsigned;
alter table my_student add height tinyint unsigned;

-- 增加字段的值；rand取得一個(gè)0-1之間的隨機(jī)數(shù)践美，floor向下取整
update my_student set age=floor(
rand()20+20),height=floor(rand()20+170);

-- 找學(xué)生ID為1,3,5,的學(xué)生
select * from my_student where id=1 || id=3 || id=5; -- 邏輯判斷
select * from my_student where id in(1,3,5); -- in表示在集合中

-- 找出身高在180到190之間的學(xué)生
select * from my_student where height>=180 and height<=190;
select * from my_student where height between 180 and 190;
select * from my_student where height
between 190 and 180; -- 不成立洗贰，相當(dāng)于height>=190 and height<=180

select * from my_student where 1; -- 所有條件都滿足找岖；

-- 根據(jù)性別分組
select * from my_student group by sex;

-- 分組統(tǒng)計(jì)；身高高矮敛滋，平均年齡许布、總年齡
select sex,count(*),max(height),min(height),avg(age),
sum(age)from my_student group by sex;

-- 修改ID為4的記錄，把年齡置為null
update my_student set age=null where id=4;

select sex,count(*),max(height),min(height),avg(age),
sum(age)from my_student group by sex;

-- 修改ID為1 的記錄绎晃，把性別置為女
update my_student set sex='女' where id=1;

-- nan
-- nv

select sex,count(*),max(height),min(height),avg(age),
sum(age)from my_student group by sex desc;

-- 刪除班級(jí)表原主鍵
alter table my_class drop primary key;

-- 給班級(jí)表增加主鍵
alter table my_class add id int primary key auto_increment;

-- 給學(xué)生表增加班級(jí)ID
alter table my_student add c_id int;
update my_student set c_id=ceil(rand()*3);

-- 多字段分組蜜唾；先班組；后男女
select c_id,sex,count(*) from my_student group by
c_id, sex; -- 多字段排序

select c_id,sex,count(*),group _concat(name)
from my_student group by c_id, sex; -- 多字段排序

-- 統(tǒng)計(jì)
select c_id,count() from my_student group by c_id;
-- 回溯統(tǒng)計(jì)
select c_id,count() from my_student group by c_id with rollup;

-- 多字段分組回溯統(tǒng)計(jì)
select c_id,sex,count(),group _concat(name)
from my_student group byc_id, sex; -- 多字段排序
select c_id,sex,count(),group _concat(name)
from my_student group by c_id, sex with rollup;

-- 求出所有班級(jí)人數(shù)大于等于2的學(xué)生人數(shù)
select c_id,count() from my_student
group by c_id having count()>=2;

select c_id,count() from my_student
where count()>=2 group by c_id having by c_id; -- 錯(cuò)誤

-- having字句進(jìn)行條件查詢(xún)
-- 與where子句一樣箕昭，是進(jìn)行條件判斷的
-- having能夠使用字段別名
select name as 名字,number as 學(xué)號(hào) from my_student
having 名字 like '張%';

-- 排序
select * from my_student group by c_id; -- 分組 灵妨，為了進(jìn)行統(tǒng)計(jì)
select * from my_student order by c_id; -- 排序

-- 多字段排序；先班級(jí)排序落竹，后性別排序
select * from my_student order by c_id,sex desc;

-- 查詢(xún)學(xué)生泌霍；前兩個(gè)
select * from my_student limit 2;

select * from my_student limit 0,2; -- 記錄數(shù)是重0開(kāi)始編號(hào)
select * from my_student limit 2,2;
select * from my_student limit 4,2;

-- 更改ID為班級(jí)表的第一列
alter table my_class change id id int first;

-- 交叉連接
select * from my_student cross join my_class;
-- my_student cross join my_class 是數(shù)據(jù)源；

-- 內(nèi)連接
select * from my_student inner join my_class on c_id=my_class.id;

select * from my_student inner join my_class on c_id=id; --錯(cuò)誤
,因?yàn)閮蓮埍矶加蠭D字段

-- 字段和表別名
select s.*,c.name as c_name,c.room -- 字段別名
from my_student as s inner join
my_student as c on s.c_id=c.id;

-- 把學(xué)生表ID為5的記錄的c_ID設(shè)置為null
update my_student set c_id=null where id=5;

-- where 代替on
select s.*,c.name as c_name,c.room -- 字段別名
from my_student as s inner join
my_student as c where s.c_id=c.id;

-- 左連接
select s.*,c.name as c_name,c.room -- 字段別名
from my_student as s left join
my_student as c -- 左表為主表述召；最終記錄數(shù)至少不少于左表已有的記錄數(shù)
on s.c_id=c.id;

-- 右連接
select s.*,c.name as c_name,c.room -- 字段別名
from my_student as s right join
my_student as c -- 右表為主表朱转；最終記錄數(shù)至少不少于左表已有的記錄數(shù)
on s.c_id=c.id;

select s.*,c.name as c_name,c.room -- 字段別名
from my_student as c right join
my_student as s -- 左表為主表；最終記錄數(shù)至少不少于左表已有的記錄數(shù)
on s.c_id=c.id;

-- 自然內(nèi)連接
select * from my_student natural join my_class;

-- 修改班級(jí)表name字段名為c_name
alter table my_class change name
c_name varchar (20) not null;

-- 自然左外連接
select * from my_student natural
left join my_class;

-- 外連接模擬自然外連接：using
select * from my_student left
join my_class using (id);

*************************** 第五天 **************************

-- 創(chuàng)建外鍵
create table my_foreign1(
id int primary key auto_increment,
name varchar (20) not null comment
'學(xué)生姓名',
c_id int comment '班級(jí)id',
-- 增加外鍵
foreign key(c_id) references
my_class(id)
)charset utf8;

-- 創(chuàng)建表
create table my_foreign2(
id int primary key auto_increment,
name varchar (20) not null comment
'學(xué)生姓名',
c_id int comment '班級(jí)id' -- 普通字段
)charset utf8;

-- 增加外鍵
alter table my_foreign2 add
-- 指定外鍵的名字
constraint student_class_1 -- 可以指定多個(gè)外鍵 但是名字不能相同
-- 指定外鍵的字段
foreign key(c_id)
-- 引用父表主鍵
references my_class(id);

-- 刪除外鍵
alter table my_foreign1 drop
foreign key my_foreign1_ibfk_1;-- my_foreign1_ibfk_1 通過(guò)外鍵的名字來(lái)刪

-- 插入數(shù)據(jù)积暖；外鍵字段在父表不存在
insert into my_foreign2 values (
null,'郭富城',4); -- 沒(méi)有4號(hào)班級(jí)

insert into my_foreign2 values (
null,'項(xiàng)羽',1);
insert into my_foreign2 values (
null,'劉邦',2);
insert into my_foreign2 values (
null,'韓信',3);

-- 更新父表的記錄
update my_class set id=4 where id=1; -- 失斕傥；id=1記錄已經(jīng)被學(xué)生引用
update my_foreign2 set c_id=2 where id=4;-- 更新
update my_class set id=4 where id=3; -- 可以夺刑；沒(méi)有學(xué)生引用此班級(jí)

-- mysql中添加外鍵約束遇到一下情況：
-- cannot add foreign key constraint
-- 出現(xiàn)這個(gè)問(wèn)題的原因是缅疟，外鍵的使用：
-- 1. 外鍵字段不能為該表的主鍵；
-- 2. 外鍵字段參考字段必須為參考表的主鍵

-- 插入數(shù)據(jù)
insert into my_foreign1 values (
null,'馬超','3'
);

-- 增加外鍵
alter table my_foreign1 add
foreign key(c_id) references
my_class(id); -- 失敱樵浮存淫；因?yàn)闆](méi)有3號(hào)班了

-- 創(chuàng)建外鍵，指定模式沼填；刪除置空桅咆；更新級(jí)聯(lián)
create table my_foreign3(
id int primary key auto_increment,
name varchar (20) not null,
c_id int,
-- 增加外鍵
foreign key (c_id)
-- 引用表
references my_class(id)
-- 指定刪除模式
on delete set null
-- 指定更新模式
on update cascade
)charset utf8;

-- 插入數(shù)據(jù)
insert into my_foreign3 values (
null,'劉備',1),
(null,'曹操',1),
(null,'孫權(quán)',1),
(null,'祝賀量',2),
(null,'周瑜',2);

-- 解除My_foreign2表的外鍵
alter table my_foreign2 drop
foreign key student_class_1;

-- 更新父表主鍵
update my_class set id=3 where id=1;

-- 刪除父表主鍵
delete from my_class where id=2;

-- 聯(lián)合查詢(xún)
select * from my_class
union -- 默認(rèn)去重
select * from my_class;

select * from my_class
union all -- 不去重
select * from my_class;

select id,c_name,room from my_class
union all -- 不去重
select name,number,id from my_student;

-- 需求；男生升序坞笙；女生降序（年齡）
(select * from my_student
where sex='男'
order by age asc limit 9999999)
union
(select * from my_student
where sex='女'
order by age desc limit 9999999);

select * from my_student where
c_id=(
-- 標(biāo)量子查詢(xún)
select id from my_class where
c_name='python1903'); -- id一定只有一個(gè)值（一行一列）

insert into my_class values (1,
'python1907','B407');

-- 列子查詢(xún)
select * from my_student where
c_id in(select id from my_class);

-- any,some,all
select * from my_student where
c_id=any(select id from my_class);
select * from my_student where
c_id=some(select id from my_class);
select * from my_student where
c_id=all(select id from my_class);

select * from my_student where
c_id!=any(select id from my_class); -- 所有結(jié)果（null除外）
select * from my_student where
c_id!=some(select id from my_class); -- 所有結(jié)果（null除外）
select * from my_student where
c_id!=all(select id from my_class); -- 所有2號(hào)班級(jí)（null除外）

select * from my_student where
age=(select max(age) from
my_student)
and
height=(select max(height)) from
my_student);

-- 行子查詢(xún)
select * from my_student
-- (age,height)稱(chēng)之內(nèi)為行元素
where (age,height)=(select max(
age),max(height) from my_student);

update my_student set height=188
where name='王五';

select * from my_student order by
age desc,height desc limit 1;

select * from my_student order by
height desc;

-- 表子查詢(xún)
select * from my_student group by
c_id order by height desc; -- 每個(gè)班選出第一個(gè)學(xué)生再按身高排序

-- 表子查詢(xún)（每班身高最高的學(xué)生）
select * from (select * from
my_student order by height desc
limit 9999999)as student group --》student自己起的名字
by c_id;

select exists (select * from
my_student);

select exists (select * from
my_student where id=100);

-- exists子查詢(xún)
select * from my_student where
exists (select * from my_class
where id=1); -- 是否成立

select * from my_student where
exists (select * from my_class
where id=2); -- 是否成立

****************************** 第六天 **************************

-- 創(chuàng)建視圖岩饼；單表 + 多表
create view my_v1 as
select * from my_student;

create view my_v2 as
select * from my_class;

create view my_v3 as
select * from my_student as s
left join my_class as c on s.c_id=c.id; -- id重復(fù)

-- 多表視圖
create view my_v3 as
select s.*,c.c_name,c.room from
my_student as s
left join my_class as c
on s.c_id=c.id;

多表視圖更新數(shù)據(jù)
update my_v3 set c_id=3 where id=5;

-- 但是用內(nèi)連接生成的視圖可以更新數(shù)據(jù)
-- 左外連接后的結(jié)果是包括左表中的全部行，與右表中沒(méi)有對(duì)應(yīng)的數(shù)據(jù)填充null,內(nèi)連接只連接兩表中等值的部分
create view my_v13 as
select s.*,c.c_name,c.room
from my_student as s
join my_class as c
on s.c_id=c.id;
-- 查看建視圖語(yǔ)句
show create view my_v13\G
-- 查看視圖所有數(shù)據(jù)
select * from my_v3;
select * from my_v13;
-- 多表視圖更新數(shù)據(jù)
update my_v13 set c_id=3 where id=5;--成功

-- 查看視圖創(chuàng)建語(yǔ)句
show create view my_v3\G -- \G 橫向查看

-- 視圖使用
select * from my_v1;
select * from my_v2;
select * from my_v3;

-- 修改視圖
alter view my_v1 as
select id,name,sex,age,height,c_id
from my_student;

-- 創(chuàng)建視圖
create view my_v4 as select * from my_student;

-- 刪除視圖
drop view my_v4;

-- 多表視圖插入數(shù)據(jù)
insert into my_v3
values(null,'bc20190006','張三豐','男',
150,180,1,'python1907','B407');

-- 將學(xué)生表的學(xué)號(hào)字段設(shè)置成不允許為空
alter table my_student modify
number char(10) not null unique;

-- 單表視圖插入數(shù)據(jù)薛夜；視圖不包含所欲不允許為空的字段
insert into my_v1
values(null,'張三豐',150,'男',180,1); -- 插入失敿搿（學(xué)號(hào)不允許為空）

-- 單表視圖插入數(shù)據(jù)
insert into my_v2
values(2,'python1811','B410');

-- 多表視圖刪除數(shù)據(jù)
delete from my_v3 where id=1;

-- 單表視圖刪除數(shù)據(jù)
delete from my_v2 where id=4;

-- 多表視圖更新數(shù)據(jù)
update my_v3 set c_id=3 where id=5;

-- 視圖；age子彈限制更新
create view my_v4 as
select * from my_student where
age>30 with check option; -- 表示視圖的數(shù)據(jù)來(lái)源都是年齡大于30歲却邓，
是由where age>30 決定的
-- with check
-- option 決定通過(guò)視圖更新的時(shí)候,不能將已經(jīng)得到的數(shù)據(jù)age>30的改成<30的

-- 將視圖可以查到的數(shù)據(jù)該成年齡小于30
update my_v4 set age=29 where id=3;

-- 可以修改數(shù)據(jù)硕糊；可以改；但是視圖查不到
update my_v4 set age=32 where id=2;

-- 獲取所有班級(jí)中最高的一個(gè)學(xué)生
create view my_v5 as
select * from my_student order by
height desc ;

select * from my_v5 group by c_id;

select * from my_student group by c_id
order by height desc;

-- 指定算法為臨時(shí)表算法
create algorithm=temptable view
my_v6 as select * from my_student
order by height desc ;

select * from my_v6 group by c_id;

-- 查看MySQL的版本
select @@version;

-- 創(chuàng)建myisam表
create table my_myisam(
id int
) charset utf8 engine=myisam;

-- 向my_myisam表插入幾條記錄
insert into my_myisam values (1),(2),(3);

-- 單表的數(shù)據(jù)備份 --
-- ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
-- show variables like '%secure%';
-- 找到secure_file_priv | C:\ProgramData\MySQL\MySQL Server 5.7\my.ini
-- 打開(kāi)my文件，ctrl+f找到關(guān)鍵字secure,修改原路徑：
-- 把原路徑用#注釋掉简十，改為:
-- secure-file-priv=""
-- 需要重啟MySQL服務(wù)并重新登錄認(rèn)證

-- 單表的數(shù)據(jù)備份
select * into outfile
'D:/t/我的文檔/19071/web2/student.txt' from my_student;

-- 指定備份處理方式
select * into outfile
'D:/t/我的文檔/19071/web2/class1.txt'
-- 字段處理
fields
enclosed by '"' -- 數(shù)據(jù)使用雙引號(hào)包裹
terminated by '|' -- 使用豎線分割字段數(shù)據(jù)
-- 行處理
lines
starting by 'START:'
from my_class;

delete from my_class;

-- 還原數(shù)據(jù)
load data infile
-- 文件所在路徑
'D:/t/我的文檔/19071/web2/class1.txt'
into table my_class -- 表名[(字段列表)]
-- 字段處理
fields
enclosed by '"' -- 數(shù)據(jù)使用雙引號(hào)包裹
terminated by '|' -- 使用豎線分割字段數(shù)據(jù)
-- 行處理
lines
starting by 'START:';

-- SQL備份 不是SQL語(yǔ)句\q退出 -- 常用的 -- 不能換行
mysqldump -uroot -p123456 mydatabase my_student > D:/t/我的文檔/19071/web2/student.sql

-- 整庫(kù)備份
mysqldump -uroot -p123456 mydatabase > D:/t/我的文檔/19071/web2/mydatabase.sql

-- 還原數(shù)據(jù)檬某；mysql客戶端還原
mysql -uroot -p123456 mydatabase < D:/t/我的文檔/19071/web2/student.sql

-- SQL指令還原SQL備份
source D:/t/我的文檔/19071/web2/student.sql;

**************************** 第七天 ***************************

-- 創(chuàng)建一個(gè)賬戶
create table my_account(
id int primary key auto_increment,
number char (16) not null unique
comment '賬戶',
name varchar (20) not null ,
money decimal (10,2) default 0.0
comment '賬戶余額'
)charset utf8;

-- 插入數(shù)據(jù)
insert into my_account values
(null,'1234567890000000','張三','1000'),
(null,'1234567890000001','李四','2000');

-- 張三轉(zhuǎn)賬1000元給李四
update my_account set money=money-1000 where id=1;

-- 事物安全
-- 開(kāi)啟事物
start transaction;
-- 事物的操作；1螟蝙，李四賬戶減少錢(qián)
update my_account set money=money-1000
where id=2;
-- 事物操作恢恼；2，張三賬戶增加
update my_account set money=money+1000
where id=1;
-- 提交事物
commit;

-- 回滾點(diǎn)操作
-- 開(kāi)啟事物加錢(qián)
start transaction ;

-- 事物處理1胰默；張三發(fā)工資了场斑，
update my_account set money=money+10000
where id=1;

-- 設(shè)置回滾點(diǎn)
savepoint sp1;

-- 銀行扣稅
update my_account set money=money-10000*0.05 where id=2; -- 錯(cuò)誤

-- 回滾到回滾點(diǎn)
rollback to sp1;

-- 繼續(xù)操作 銀行扣稅
update my_account set money=money-10000*0.05 where id=1;

-- 查看結(jié)果
select * from my_account;

-- 提交結(jié)果
commit;

-- 顯示系統(tǒng)變量autocommit（模糊查詢(xún)）
show variables like 'autocommit';

-- 關(guān)閉事物自動(dòng)提交
set autocommit=0; --off/0

-- 給李四發(fā)工資
update my_account set money=money+10000 where id=2;
commit;

-- 銀行扣稅
update my_account set money=money-10000*0.05 where id=2;

-- 事物的隔離性
start transaction;
-- 給張三返稅，返500塊錢(qián)
update my_account set money=money+500
where id=1;

    -- 另外窗口開(kāi)啟事物
    start transaction;
    -- 李四淘寶花了500
    update my_account set money=money-500 where id=2;
    select * from my_account;
    commit;
    select * from my_account;

-- 回到張三窗口牵署；事物回滾
rollback;
select * from my_account; -- 兩邊一致

-- 鎖機(jī)制
start transaction;
-- 使用非索引字段（name）漏隐，行鎖自動(dòng)上升為表鎖
update my_account set money=money+500
where name='張三';

    update my_account set money=money+1000 where id=2;

=================================================================

-- SQL演練
-- 查詢(xún)類(lèi)型cate_name為'超級(jí)本'的商品名稱(chēng)，價(jià)格
select * from goods where cate_name='超級(jí)本';

select name as 商品名稱(chēng),price as 商品價(jià)格
from goods where cate_name='超級(jí)本';

-- 顯示商品的種類(lèi)
select distinct cate_name from goods;

select cate_name from goods group by cate_name;

select cate_name,group_concat(name)
from goods group by cate_name;

-- 求所有電腦產(chǎn)品的平均價(jià)格,并且保留兩位小數(shù)
select round(avg(price),2) from goods;

-- 顯示每種商品的平均價(jià)格
select cate_name,avg(price) from goods
group by cate_name;

-- 查詢(xún)每種類(lèi)型的商品中 最貴奴迅、最便宜避归、平均價(jià)梢灭、數(shù)量
select cate_name,max(price),min(price),avg(price),
count(*) from goods group by cate_name;

-- 查詢(xún)所有價(jià)格大于平均價(jià)格的商品磨隘，并且按價(jià)格降序排序
select * from goods where price>
(select avg(price) from goods)
order by price desc;

-- 查詢(xún)每種類(lèi)型中最貴的電腦信息
select cate_name,max(price) from
goods group by cate_name;
select *

insert into goods values
(0,'東哥牌電腦','筆記本','老王','4999',default,default);

-- 查詢(xún)每種類(lèi)型中最貴的電腦信息
select * from goods
inner join
(
select
cate_name,
max(price) as max_price,
min(price) as min_price,
avg(price) as avg_price,
count(*) from goods group by cate_name
) as goods_new_info
on goods.cate_name=goods_new_info.cate_name and
goods.price=goods_new_info.max_price;

select g_new.cate_name,g.name,g.price
from (select cate_name,max(price) as
max_price from goods group by cate_name)
as g_new left
join goods as g
on g_new.cate_name=g.cate_name
and g_new.max_price = g.price order by g_new.cate_name;

-- 創(chuàng)建商品分類(lèi)表
create table if not exists goods_cates(
id int unsigned primary key auto_increment,
name varchar(40) not null
);

-- 查詢(xún)goods表中商品的種類(lèi)
select cate_name from goods group by cate_name;

-- 拆表
-- 向表中插入
-- 將分組結(jié)果寫(xiě)入到goods_cates數(shù)據(jù)表
insert into goods_cates (name)
select cate_name from goods
group by cate_name;

-- 通過(guò)goods_cates數(shù)據(jù)表來(lái)更新goods表
update goods as g inner join goods_cates
as c on g.cate_name=c.name
set g.cate_name=c.id;

-- 插入類(lèi)別
insert into goods_cates(name)
values ('路由器'),('交換機(jī)'),('網(wǎng)卡');

-- 插入商品
insert into goods (name,cate_name,brand_name,price)
values('LaserJet Pro P1606dn 黑白激光打印機(jī)', 12, 4,'1849');

-- 修改表結(jié)構(gòu)
alter table goods
change cate_name cate_id int
unsigned not null;

delete from goods where id=23;

-- 添加外鍵
alter table goods
add foreign key (cate_id)
references goods_cates(id);

-- 插入商品
insert into goods (name,cate_name,brand_name,price)
values('LaserJet Pro P1606dn 黑白激光打印機(jī)', 12, 4,'1849'); -- 失敗

-- 作業(yè)油挥；拆品牌表
create table goods_brands (
id int unsigned primary key auto_increment,
name varchar(40) not null) select
brand_name as name from goods group by brand_name;

update goods as g inner join goods_brands as
b on g.brand_name=b.name set g.brand_name=b.id;

-- 創(chuàng)建測(cè)試表
create table test_index(title varchar(10));

-- 開(kāi)啟運(yùn)行時(shí)間監(jiān)測(cè)： 統(tǒng)計(jì)操作時(shí)間
set profiling=1;

-- 查找第10萬(wàn)條數(shù)據(jù)ha-99999
select * from test_index where title='ha-99999';

-- 查看執(zhí)行的時(shí)間：
show profiles;

-- 為表title_index的title列創(chuàng)建索引：
create index title_index on test_index(title(10));

-- 執(zhí)行查詢(xún)語(yǔ)句：
select * from test_index where title='ha-99999';

-- 再次查看執(zhí)行的時(shí)間
show profiles;

-- 查看索引
show index from test_index;
show index from goods;

-- 權(quán)限
use mysql;
show tables;
select user,host from user;
-- authentication_string是密碼
select host,user,authentication_string from user;
-- 創(chuàng)建賬戶并授予所有權(quán)限
grant select on jd.* to 'laoweng'@'localhost' identified by '123456';
-- 使用老翁賬戶登錄
mysql -ulaoweng -p123456
use jd;
select * from goods;
select * from goods_cates;
-- 沒(méi)有權(quán)限；無(wú)法修改（也無(wú)法刪除）
update goods_cates set name="abc" where id=20;

-- 創(chuàng)建一個(gè)laoli的賬號(hào)暇检，密碼為12345678产阱，可以任意電腦進(jìn)行鏈接訪問(wèn), 并且對(duì)jing_dong數(shù)據(jù)庫(kù)中的所有表?yè)碛兴袡?quán)限
grant all privileges on jd.* to
"laoli"@"%" identified by "12345678";
use mysql;
select user,host from suer;

mysql -ulaoli -p12345678
show databases;
use jd;
show tables;
select * from goods; -- 查Ok
update goods set name="豪哥盤(pán)電腦" where id=22; -- 改OK

-- 修改密碼
-- 使用root登錄，修改mysql數(shù)據(jù)庫(kù)的user表

-- 使用password()函數(shù)進(jìn)行密碼加密

update user set authentication_string=password('新密碼') where user='用戶名';
-- 例：
update user set authentication_string=password('123') where user='laoweng';
-- 注意修改完成后需要刷新權(quán)限

-- 刷新權(quán)限：
flush privileges

-- 備份
mysqldump -uroot -p123456 jd > jd.sql

-- 主從
-- 在主服務(wù)器上進(jìn)行備份
mysqldump -uroot -p123456

作業(yè):修改和刪除分類(lèi)
增刪改品牌

-- 設(shè)置連接到master主服務(wù)器
change master to master_host='10.211.55.5', master_user='slave', master_password='slave',master_log_file='mysql-bin.000006', master_log_pos=590;

-- 創(chuàng)建購(gòu)物車(chē)表
create table carts(
id int unsigned primary key auto_increment,
customer_id int unsigned not null
)charset utf8;

-- 創(chuàng)建購(gòu)物項(xiàng)表
create table cart_detail(
id int unsigned primary key auto_increment,
cart_id int unsigned not null comment'購(gòu)物車(chē)id',
good_id int unsigned not null comment '商品id',
quantity int unsigned not null comment '數(shù)量',
price decimal (10,2) not null comment '價(jià)格'
)charset utf8;

insert into carts values(0, 3);

insert into cart_detail values(0, 1, 22, 1, 4999.000);

select * from carts as c,cart_detail as d where c.customer_id=3 and d.good_id=22;

-- 給商品表增加圖片字段
alter table goods add img varchar (200);

-- 給商品表的圖片字段插入數(shù)據(jù)
update goods set img ="img/1.jpg" where id=1;
update goods set img ="img/2.jpg" where id=2;
update goods set img ="img/3.jpg" where id=3;
update goods set img ="img/4.jpg" where id=4;
update goods set img ="img/5.jpg" where id=5;
update goods set img ="img/6.jpg" where id=6;
update goods set img ="img/7.jpg" where id=7;
update goods set img ="img/8.jpg" where id=8;
update goods set img ="img/9.jpg" where id=9;
update goods set img ="img/10.jpg" where id=10;

-- 修改商品表的圖片字段不允許為空
alter table goods modify img varchar(200) not null;

-- 查詢(xún)用戶編號(hào)=3的購(gòu)物車(chē)信息
select g.img,g.name,d.price,d.quantity
from carts as c,cart_detail as d,goods as g
where customer_id=3 and c.id=d.cart_id and d.good_id=g.id;

-- 給用戶增加郵箱字段
alter table customers add email varchar(100);

-- 修改用戶表的地址電話字段允許為空
alter table customers address varchar(150) default null;
-- alter table customers address varchar(150) default null;