MySQL 8.0

查看當(dāng)前所有存在的數(shù)據(jù)庫

SHOW DATABASES;

創(chuàng)建測試數(shù)據(jù)庫test_db

CREATE DATABASE test_db;

查看創(chuàng)建好的數(shù)據(jù)庫test_db

SHOW CREATE DATABASE test_db;

刪除數(shù)據(jù)庫test_db

DROP DATABASE test_db;

查看數(shù)據(jù)庫版本號

SELECT version();

查看系統(tǒng)表類型8.0開始犀农，系統(tǒng)表全部換成事務(wù)型的InnoDB表

SELECT DISTINCT(ENGINE) FROM information_schema.tables;

create database zoo;

選擇當(dāng)前數(shù)據(jù)庫

use zoo;
drop database zoo;
show databases;

create database test_db;
use test_db;
drop table tb_emp1;
create table tb_emp1(
id int(11),
name varchar(25),
deptId int(11),
salary float
);

查看數(shù)據(jù)表

show tables;

主鍵是唯一的洞渤，且不允許為空

方法一

create table tb_emp2(
id int(11) primary key,
name varchar(25),
deptId int(11),
salary float
);

方法二

定義完所有列之后指定主鍵

create table tb_emp3(
id int(11),
name varchar(25),
deptId int(11),
salary float,
primary key(id)
);

多字段聯(lián)合主鍵

create table tb_emp4(
name varchar(25),
deptId int(11),
salary float,
primary key(name, deptId)
);

使用外鍵約束

子表的外鍵必須關(guān)聯(lián)父表的主鍵，且關(guān)聯(lián)字段的數(shù)據(jù)類型必須匹配

create table tb_dept1(
id int(11) primary key,
name varchar(22) not null,
location varchar(50)
);
create table tb_em5(
id int(11) primary key,
name varchar(25),
deptId int(11),
salary float,
constraint fk_emp_dept1 foreign key(deptId) references tb_dept1(id)
);

使用非空約束

create table tb_emp6(
id int(11) primary key,
name varchar(25) not null,
deptId int(11),
salary float
);

唯一性約束膳算，一個表中可以有多個字段聲明為unique

方法一

create table tb_dept2(
id int(11) primary key,
name varchar(22) unique,
location varchar(50)
);

方法二

create table tb_dept3(
id int(11) primary key,
name varchar(22),
location varchar(50),
constraint STH unique(name)
);

使用默認(rèn)約束

create table tb_emp7(
id int(11) primary key,
name varchar(25) not null,
deptId int(11) default 1111,
salary float
);

設(shè)置表的屬性值自動增加

create table tb_emp8(
id int(11) primary key auto_increment,
name varchar(25) not null,
deptId int(11),
salary float
);
show tables;
insert into tb_emp8(name, salary)
value('Lucy', 1000),
('Lura', 1200),
('Kevin', 1500);
select * from tb_emp8;

查看數(shù)據(jù)表結(jié)構(gòu)

describe tb_emp8;
desc tb_emp8;

查看表詳細(xì)結(jié)構(gòu)語句

show create table tb_emp1;

修改表結(jié)構(gòu)

修改表名

alter table tb_dept3 rename tb_deptment3;
show tables;

修改字段的數(shù)據(jù)類型

alter table tb_dept1 modify name varchar(30);
desc tb_dept1;

修改字段名坷剧，必須要寫數(shù)據(jù)類型

alter table tb_dept1 change location loc varchar(50);
alter table tb_dept1 change loc location varchar(60);

4.3.4 添加字段

alter table tb_dept1 add managerId int(10);
alter table tb_dept1 add column1 varchar(12) not null;
alter table tb_dept1 add column2 int(11) first;
alter table tb_dept1 add column3 int(11) after name;

4.3.5 刪除字段

alter table tb_dept1 drop column2;

4.3.6修改字段的排列位置

alter table tb_dept1 modify column1 varchar(12) first;
alter table tb_dept1 modify column1 varchar(12) after location;

4.3.7 更改表的存儲引擎

show engines;
show create table tb_dept1;
alter table tb_dept1 engine=InnoDB;

4.3.8 刪除表的外鍵約束

create table tb_dept9(
id int(11) primary key,
name varchar(25),
deptId int(11),
salary float,
constraint fk_emp_dept foreign key (deptId) references tb_dept1(id)
);
show create table tb_dept9;
alter table tb_dept9 drop foreign key fk_emp_dept;

4.4 刪除數(shù)據(jù)表

drop table if exists tb_dept2;
show tables;
create table tb_dept2(
id int(11) primary key,
name varchar(22),
location varchar(50)
);
create table tb_emp(
id int(11) primary key,
name varchar(25),
deptId int(11),
salary float,
constraint fk_emp_dept foreign key (deptId) references tb_dept2(id)
);
drop table tb_dept2;
alter table tb_emp drop foreign key fk_emp_dept;

4.5 查看默認(rèn)字符集

show variables like 'character_set_database';

4.7 綜合案例

create database company;
use company;
create table offices(
officeCode int(10) primary key not null unique,
city varchar(50) not null,
address varchar(50),
country varchar(50) not null,
postalCode varchar(15) unique
);

create table employees(
employeeNumber int(11) primary key not null unique auto_increment,
lastName varchar(50) not null,
firstName varchar(50) not null,
mobile varchar(25) unique,
officeCode int(10) not null,
jobTitle varchar(50) not null,
birth datetime not null,
note varchar(255),
sex varchar(5),
constraint office_fk foreign key(officeCode) references offices(officeCode)
);
show tables;
alter table employees modify mobile varchar(255) after officeCode;
alter table employees change birth employee_birth datetime;
alter table employees modify sex char(1) not null;
alter table employees drop note;
alter table employees add favoriate_activity varchar(100);
alter table employees drop foreign key office_fk;
drop table offices;
alter table employees engine=MyISAM;
alter table employees rename employees_info;

4.9 經(jīng)典習(xí)題

create database Market;
use Market;
create table customers(
c_num int(11) primary key not null unique auto_increment,
c_name varchar(50),
c_contact varchar(50),
c_city varchar(50),
c_birth datetime not null
);
desc customers;
alter table customers modify c_contact varchar(50) after c_birth;
alter table customers modify c_name varchar(70);
alter table customers change c_contact c_phone varchar(50);
alter table customers add c_gender char(1);
alter table customers rename customers_info;
show tables;
alter table customers_info drop c_city;
alter table customers_info engine=InnoDB;
create table orders(
o_num int(11) primary key not null unique auto_increment,
o_date date ,
c_id int(11),
constraint orders_fk foreign key (c_id) references customers_info(c_num)
);
show tables;
desc orders;
alter table orders drop foreign key orders_fk;
drop table orders;

7.1 基本查詢語句

create table fruits(
f_id char(10) not null,
s_id int not null,
f_name char(255) not null,
f_price decimal(8,2) not null,
primary key(f_id)
);
insert into fruits
(f_id, s_id, f_name, f_price)
values
('a1', 101, 'apple', 5.2),
('b1', 101, 'blackberry', 10.2),
('bs1', 102, 'orange', 11.2),
('bs2', 105, 'melon', 8.2),
('t1', 102, 'banana', 10.3),
('t2', 102, 'grape', 5.3),
('o2', 103, 'coconut', 3.2),
('c0', 101, 'cherry', 3.2),
('a2', 103, 'apricot', 2.3),
('l2', 104, 'lemon', 6.4),
('b2', 104, 'berry', 7.6),
('m1', 106, 'mango', 15.7),
('m2', 105, 'xbabay', 2.6),
('t4', 107, 'xbababa', 3.6),
('m3', 105, 'xxtt',11.6),
('b5', 107, 'xxxx', 3.6);
select f_id, f_name from fruits;

7.2 單表查詢

select * from fruits;
select f_id, s_id, f_name, f_price from fruits;
select f_name from fruits;

7.2.3 查詢指定記錄

select f_name, f_price from fruits where f_price = 10.2;
select f_name, f_price from fruits where f_name='apple';
select f_name, f_price from fruits where f_price < 10;

7.2.4 in 和 not in 查詢

select s_id, f_name, f_price from fruits where s_id in (101, 102) order by f_name;
select s_id, f_name, f_price from fruits where s_id not in (101, 102) order by f_name;

7.2.5 between and

select f_name, f_price from fruits where f_price between 2.00 and 10.20;

7.2.6 like 查詢

select f_id, f_name from fruits where f_name like 'b%';
select f_id, f_name from fruits where f_name like '%g%';
select f_id, f_name from fruits where f_name like 'b%y';
select f_id, f_name from fruits where f_name like '____y';

7.2.7 查詢空值

create table customers(
c_id int not null auto_increment,
c_name char(50) not null,
c_address char (50) null,
c_city char(50) null,
c_zip char(10) null,
c_contact char(50) null,
c_email char(255) null,
primary key(c_id)
);
insert into customers
(c_id, c_name, c_address, c_city, c_zip, c_contact, c_email)
values
(10001, 'RedHook', '200 Street', 'Tianjin', '300000', 'LiMing', 'LiMing@163.com'),
(10002, 'Stars', '333 Fromage Lane', 'Dalian', '116000', 'Zhangbo', 'Jerry@hotmai.com'),
(10003, 'Netbhood', '1 Sunny Place', 'Qingdao', '266000', 'LuoCong', Null),
(10004, 'JOTO', '829 Riverside Drive', 'Haikou', '570000', 'YangShan', 'sam@hotmail.com');
select c_id, c_name, c_email from customers where c_email is null;
select c_id, c_name, c_email from customers where c_email is not null;

7.2.8 帶and 多條件查詢

select f_id, f_price, f_name from fruits where s_id = 101 and f_price >= 5;
select f_id, f_price, f_name from fruits where s_id in (101, 102) and f_price >=5 and f_name='apple';

7.2.9 帶or 的多條件查詢

select s_id, f_name, f_price from fruits where s_id = 101 or s_id = 102;

and 的優(yōu)先級比or的優(yōu)先級高

7.2.10 查詢結(jié)果不重復(fù)

select distinct s_id from fruits;

7.2.11 對查詢結(jié)果排序

select f_name from fruits order by f_name;
select f_name, f_price from fruits order by f_name, f_price;

升序 asc 降序 desc

select f_name, f_price from fruits order by f_price desc;
select f_price, f_name from fruits order by f_price desc, f_name;

7.2.12 分組查詢

select s_id, count(*) as Total from fruits group by s_id;

group_concat()

select s_id, group_concat(f_name) as Names from fruits group by s_id;

having 分組之后過濾

select s_id, group_concat(f_name) as Names from fruits group by s_id having count(f_name) > 1;

with rollup 不能與order by 同時使用

select s_id, coalesce(s_id, '總計'), count(*) as Total from fruits group by s_id with rollup;

多字段分組

select s_id, f_name from fruits group by s_id, f_name;

以下查詢會報錯

select * from fruits group by s_id, f_name;
select @@GLOBAL.sql_mode;
create table orderitems(
o_num int not null,
o_item int not null,
f_id char(10) not null,
quantity int not null,
item_price decimal(8,2) not null,
primary key (o_num, o_item)
);
insert into orderitems
(o_num, o_item, f_id, quantity, item_price)
values
(30001, 1, 'a1', 10, 5.2),
(30001, 2, 'b2', 3, 7.6),
(30001, 3, 'bs1', 5, 11.2),
(30001, 4, 'bs2', 15, 9.2),
(30002, 1, 'b3', 2, 20.0),
(30003, 1, 'c0', 100, 10),
(30004, 1, 'o2', 50, 2.50),
(30005, 1, 'c0', 5, 10),
(30005, 2, 'b1', 10, 8.99),
(30005, 3, 'a2', 10, 2.2),
(30005, 4, 'm1', 4, 14.99);
select o_num, SUM(quantity * item_price) AS orderTotal from orderitems group by o_num having SUM(quantity * item_price) >= 100;
select o_num, SUM(quantity * item_price) AS orderTotal from orderitems group by o_num having SUM(quantity * item_price) >= 100 order by orderTotal;

7.2.13 limit

前4條數(shù)據(jù)

select * from fruits limit 4;

從第5條開始的3條數(shù)據(jù)

select * from fruits limit 4,3;
select * from fruits limit 3 offset 4;

7.3.1 COUNT()函數(shù)

select count(*) as cust_num from customers;
select count(c_email) as email_num from customers;
select * from orderitems ;
select o_num, count(f_id) from orderitems group by o_num;

7.3.2 SUM()函數(shù)

select sum(quantity) as items_total from orderitems where o_num = 30005;
select o_num, sum(quantity) as items_total from orderitems group by o_num;

7.3.3 AVG() 函數(shù)

select avg(f_price) as avg_price from fruits where s_id=103;
select s_id, avg(f_price) as avg_price from fruits group by s_id;

7.3.4 MAX() 函數(shù)

select MAX(f_price) as max_price from fruits;
select s_id, max(f_price) as max_price from fruits group by s_id;
select max(f_name) from fruits;

7.3.5 MIN() 函數(shù)

select min(f_price) as min_price from fruits;
select s_id, min(f_price) as min_price from fruits group by s_id;

7.4.1 內(nèi)連接查詢

create table suppliers(
s_id int not null auto_increment,
s_name char(50) not null,
s_city char(50) null,
s_zip char(10) null,
s_call char(50) not null,
primary key(s_id)
);
insert into suppliers
(s_id, s_name, s_city, s_zip, s_call)
values
(101, 'FastFruit Inc.', 'Tianjin', '300000', '48075'),
(102, 'LT Supplies', 'Chongqing', '400000', '44333'),
(103, 'ACME', 'Shanghai', '200000', '90046'),
(104, 'FNK Inc.', 'Zhongshan','528437','11111'),
(105, 'Good Set','Taiyuan','030000','2222'),
(106,'Just Eat Ours', 'Beijing','010', '45678'),
(107, 'DK Inc', 'Zhengzhou', '450000','33332');
desc fruits;
desc suppliers;
select suppliers.s_id, s_name, f_name, f_price from fruits, suppliers where fruits.s_id = suppliers.s_id;
select f.f_name, f.f_price, s.s_id, s.s_name from fruits f inner join suppliers s on f.s_id = s.s_id;

自連接查詢

不理解下面這句

select f1.f_id, f1.f_name from fruits f1, fruits f2 where f1.s_id = f2.s_id and f2.f_id = 'a1';

7.4.2 外連接查詢

create table orders(
o_num int not null auto_increment,
o_date datetime not null,
c_id int not null,
primary key(o_num)
);
insert into orders (o_num, o_date, c_id)
values
(30001, '2008-09-01', 10001),
(30002, '2018-09-12', 10003),
(30003, '2018-09-30', 10004),
(30004, '2018-10-03', 10005),
(30005, '2018-10-08', 10001);

外連接分為左外連接或左連接和右外連接或右連接

左連接： 包含左表中的所有記錄和右表中連接字段相等的記錄

select customers.c_id, orders.o_num from customers left join orders on customers.c_id = orders.c_id;

右連接： 包含右表中的所有記錄和左表中連接字段相等的記錄

select customers.c_id, orders.o_num from customers right join orders on customers.c_id = orders.c_id;

7.4.3 復(fù)合條件連接查詢

select customers.c_id, orders.o_num from customers inner join orders on customers.c_id = orders.c_id and customers.c_id=10001;
select s.s_id, s.s_name, f.f_name, f.f_price from fruits f inner join suppliers s on f.s_id = s.s_id order by s.s_id;

7.5 子查詢

create table tbl1 (num1 int not null);
create table tbl2 (num2 int not null);
insert into tbl1 values(1), (5), (13), (27);
insert into tbl2 values(6), (14), (11), (20);

關(guān)鍵字ANY 或者SOME

select num1 from tbl1 where num1 > ANY (select num2 from tbl2 );
select num1 from tbl1 where num1 > SOME (select num2 from tbl2);

關(guān)鍵字 ALL

select num1 from tbl1 where num1 > ALL (select num2 from tbl2);

關(guān)鍵字 EXISTS

select * from fruits where exists (select s_name from suppliers where s_id = 107);
select * from fruits where f_price > 10.20 and exists (select s_name from suppliers where s_id = 107);
select * from fruits where not exists (select s_name from suppliers where s_id = 107);

關(guān)鍵字 IN

select c_id from orders where o_num in (select o_num from orderitems where f_id = 'c0');
select c_id from orders where o_num not in (select o_num from orderitems where f_id = 'c0');

7.5 帶比較運算符的子查詢

select s_id, f_name from fruits where s_id = (select s_id from suppliers where s_city = 'Tianjin');
select s_id, f_name from fruits where s_id <> (select s_id from suppliers where s_city = 'Tianjin');

7.6 合并查詢結(jié)果（union）

刪除重復(fù)的數(shù)據(jù)

select * from fruits where f_price < 9 union select * from fruits where s_id in (101, 103) ;

不刪除重復(fù)的數(shù)據(jù)

select * from fruits where f_price < 9 union all select * from fruits where s_id in (101, 103) ;

7.7 為表和字段取別名 AS

7.7.1 為表取別名

select * from orders o where o.o_num = 30001;

7.7.2 為字段取別名

select f_name as fruit_name , f_price as fruit_price from fruits as f1 where f_price < 8;
select concat(TRIM(s_name), '(', TRIM(s_city), ')') as suppliers_title from suppliers order by s_name;

7.8 使用正則表達(dá)式查詢

select * from fruits where f_name REGEXP '^b';
select * from fruits where f_name REGEXP '^be';
select * from fruits where f_name REGEXP 'y'; select * from fruits where f_name REGEXP 'rry';
select * from fruits where f_name REGEXP 'a.g';
select * from fruits where f_name REGEXP '^ba*';
select * from fruits where f_name REGEXP '^ba+';
select * from fruits where f_name REGEXP 'on';
select * from fruits where f_name REGEXP 'on|ap';

like 如果字符串在文本中間出現(xiàn)匹配不到

select * from fruits where f_name like 'on';
select * from fruits where f_name REGEXP '[ot]';
select * from fruits where f_id REGEXP '[456]';
select * from fruits where f_id REGEXP '[^a-e1-2]';
select * from fruits where f_name REGEXP 'x{2,}';
select * from fruits where f_name REGEXP 'ba{1,3}';

MySQL 8.0 GROUP BY 不在隱式排序，必須加ORDER BY

MySQL 8.0 CTE 通用表表達(dá)式

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 人面猴
    序言：七十年代末，一起剝皮案震驚了整個濱河市笋除，隨后出現(xiàn)的幾起案子，更是在濱河造成了極大的恐慌炸裆，老刑警劉巖垃它，帶你破解...
    沈念sama閱讀 216,692評論 6贊 501
  • 死咒
    序言：濱河連續(xù)發(fā)生了三起死亡事件，死亡現(xiàn)場離奇詭異烹看，居然都是意外死亡国拇，警方通過查閱死者的電腦和手機，發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 92,482評論 3贊 392
  • 救了他兩次的神仙讓他今天三更去死
    文/潘曉璐 我一進(jìn)店門惯殊，熙熙樓的掌柜王于貴愁眉苦臉地迎上來酱吝，“玉大人，你說我怎么就攤上這事土思∥袢龋” “怎么了忆嗜？”我有些...
    開封第一講書人閱讀 162,995評論 0贊 353
  • 道士緝兇錄：失蹤的賣姜人
    文/不壞的土叔 我叫張陵，是天一觀的道長崎岂。 經(jīng)常有香客問我捆毫，道長，這世上最難降的妖魔是什么冲甘？ 我笑而不...
    開封第一講書人閱讀 58,223評論 1贊 292
  • ?港島之戀（遺憾婚禮）
    正文 為了忘掉前任绩卤，我火速辦了婚禮，結(jié)果婚禮上江醇，老公的妹妹穿的比我還像新娘濒憋。我一直安慰自己，他們只是感情好陶夜，可當(dāng)我...
    茶點故事閱讀 67,245評論 6贊 388
  • 惡毒庶女頂嫁案：這布局不是一般人想出來的
    文/花漫 我一把揭開白布凛驮。 她就那樣靜靜地躺著，像睡著了一般条辟。 火紅的嫁衣襯著肌膚如雪辐烂。 梳的紋絲不亂的頭發(fā)上，一...
    開封第一講書人閱讀 51,208評論 1贊 299
  • 城市分裂傳說
    那天捂贿，我揣著相機與錄音纠修，去河邊找鬼。 笑死厂僧，一個胖子當(dāng)著我的面吹牛扣草，可吹牛的內(nèi)容都是我干的。 我是一名探鬼主播颜屠，決...
    沈念sama閱讀 40,091評論 3贊 418
  • 雙鴛鴦連環(huán)套：你想象不到人心有多黑
    文/蒼蘭香墨 我猛地睜開眼辰妙，長吁一口氣：“原來是場噩夢啊……” “哼！你這毒婦竟也來了甫窟？” 一聲冷哼從身側(cè)響起密浑，我...
    開封第一講書人閱讀 38,929評論 0贊 274
  • 萬榮殺人案實錄
    序言：老撾萬榮一對情侶失蹤，失蹤者是張志新（化名）和其女友劉穎粗井，沒想到半個月后尔破，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體，經(jīng)...
    沈念sama閱讀 45,346評論 1贊 311
  • ?護(hù)林員之死
    正文 獨居荒郊野嶺守林人離奇死亡浇衬，尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 37,570評論 2贊 333
  • ?白月光啟示錄
    正文 我和宋清朗相戀三年懒构，在試婚紗的時候發(fā)現(xiàn)自己被綠了。 大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片耘擂。...
    茶點故事閱讀 39,739評論 1贊 348
  • 活死人
    序言：一個原本活蹦亂跳的男人離奇死亡胆剧，死狀恐怖，靈堂內(nèi)的尸體忽然破棺而出醉冤，到底是詐尸還是另有隱情秩霍，我是刑警寧澤篙悯，帶...
    沈念sama閱讀 35,437評論 5贊 344
  • ?日本核電站爆炸內(nèi)幕
    正文 年R本政府宣布，位于F島的核電站铃绒，受9級特大地震影響鸽照，放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜匿垄，卻給世界環(huán)境...
    茶點故事閱讀 41,037評論 3贊 326
  • 男人毒藥：我在死后第九天來索命
    文/蒙蒙 一、第九天 我趴在偏房一處隱蔽的房頂上張望归粉。 院中可真熱鬧椿疗，春花似錦、人聲如沸糠悼。這莊子的主人今日做“春日...
    開封第一講書人閱讀 31,677評論 0贊 22
  • 一樁弒父案，背后竟有這般陰謀
    文/蒼蘭香墨 我抬頭看了看天上的太陽倔喂。三九已至铝条，卻和暖如春，著一層夾襖步出監(jiān)牢的瞬間席噩，已是汗流浹背班缰。 一陣腳步聲響...
    開封第一講書人閱讀 32,833評論 1贊 269
  • 情欲美人皮
    我被黑心中介騙來泰國打工， 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留悼枢，地道東北人埠忘。 一個月前我還...
    沈念sama閱讀 47,760評論 2贊 369
  • 代替公主和親
    正文 我出身青樓，卻偏偏與公主長得像馒索，于是被迫代替她去往敵國和親莹妒。 傳聞我的和親對象是個殘疾皇子，可洞房花燭夜當(dāng)晚...
    茶點故事閱讀 44,647評論 2贊 354