基礎(chǔ)命令

• 登錄數(shù)據(jù)庫

mysql -u root -p

• 顯示當(dāng)前時間

select now();

• 退出(登出)數(shù)據(jù)庫

exit();

• 查看所有數(shù)據(jù)

show databases;

• 創(chuàng)建數(shù)據(jù)庫(test為數(shù)據(jù)庫名)

create database test charset=utf8;

• 刪除數(shù)據(jù)庫(慎用)

drop database test;

• 使用(選擇)數(shù)據(jù)庫

use test;

• 查看當(dāng)前使用的數(shù)據(jù)庫

select database();

• 查看當(dāng)前數(shù)據(jù)庫中所有數(shù)據(jù)表

show tables;

表結(jié)構(gòu)操作命令

• 創(chuàng)建表(表名為studens,包含id、name、gender、height、age)

create table students(
id int unsigned not null primary key auto_increment,
name varchar(20) not null,
gender enum("男","女"),
height decimal(3,2),
age tinyint default 0
);

##### 常見數(shù)據(jù)類型:
整數(shù)：int，bit
小數(shù)：decimal
字符串：varchar,char
日期時間: date, time, datetime
枚舉類型:enum
##### 常見的約束:
主鍵 primary key: 物理上存儲的順序. MySQL 建議所有表的主鍵字段都叫 id, 類型為 int unsigned
非空 not null: 此字段不允許填寫空值.
惟一 unique: 此字段的值不允許重復(fù).
默認(rèn) default: 當(dāng)不填寫字段對應(yīng)的值會使用默認(rèn)值，如果填寫時以填寫為準(zhǔn).
外鍵 foreign key: 對關(guān)系字段進(jìn)行約束, 當(dāng)為關(guān)系字段填寫值時, 會到關(guān)聯(lián)的表中查詢此值是否存在, 如果存在則填寫成功, 如果不存在則填寫失敗并拋出異常.

• 刪除表

drop table students;

• 修改表-添加字段(添加birthday字段)

alter table students add birthday datatime;

• 修改表-僅修改字段類型

alter table students modify birthday data;

• 修改表-修改字段名和字段類型(修改birthday字段為birth)

alter table students change birthday birth datatime not null;

• 修改表-刪除字段(刪除birth字段)

alter table students drop birth;

• 查看表結(jié)構(gòu)

desc students;

數(shù)據(jù)庫表基本增、刪、改、查命令

• 查詢所有數(shù)據(jù)

select * from students;

• 查詢指定列數(shù)據(jù)

select name,age from students;

• 添加數(shù)據(jù)-全列插入

insert into students values(0,"小明","男",1.75,18);

• 添加數(shù)據(jù)-部分列插入

insert into students(name,gender,age) values("小紅","女","17");

• 添加數(shù)據(jù)-全列多行插入

insert into students values (0,"小張","男",1.75,18),(null,"小王","男",1.68,17);

• 添加數(shù)據(jù)-部分多行插入

insert into students(name,gender,age) values("小花","女","17"),("小黑","男","19");

• 修改數(shù)據(jù)

update students set name = "張三" where id = 2;

• 刪除數(shù)據(jù)

-- 永久刪除:
delete from students where id = 1;
-- 標(biāo)記式刪除:
alter table students add is_delete bit not null default 0;
update students set is_delete = 1 where id = 2;

條件查詢

• as 別名

select name as 姓名 from students;

• distinct 去重

select distinct name from students;

• desc降序 \ asc升序
• where條件查詢

--查詢年齡大于18的學(xué)生
select * from students where age>18;
--查詢id在2和4之間的學(xué)生(包含)
select * from students where id between 2 and 4;
--查詢id在（3，5，7）內(nèi)的學(xué)生
select * from students where id in (3,5,7);
--查詢姓名最后一個字為"紅"的學(xué)生
select * from students where name like '%紅';
--查詢名字是兩個字且以"小"開頭的學(xué)生
select * from students where name like '小_';
--查詢未刪除男生信息，按學(xué)號降序
 select * from students where is_delete = 0 and gender = '男' order by id desc;
--查詢前3行男生信息
 select * from students where gender = '男'  limit 3;

聚合函數(shù)查詢(聚合函數(shù)不統(tǒng)計null值)

• 查詢學(xué)生個數(shù)

select count(*) from students;

• 查詢男生標(biāo)號的最值

-- 查詢男生的編號最大值
select max(id) from students where gender="男";
-- 查詢男生的編號最小值
select min(id) from students where gender="男";

• 求女生的平均身高

select avg(height) from students where gender="女";

分組查詢

• 按gender字段分組

select gender from students group by gender;

• 根據(jù)name和gender字段進(jìn)行分組

select gender,name from students group by gender,name;

• 根據(jù)gender字段進(jìn)行分組， 查詢每個分組的姓名信息

-- group_concat顯示每個分組的指定字段的信息
select gender,group_concat(name) from students group by gender;

• 統(tǒng)計不同性別的平均年齡,并保留兩位小數(shù)

select gender,round(avg(age),2) from students group by gender;

• 根據(jù)gender字段進(jìn)行分組，統(tǒng)計分組條數(shù)大于2的

select gender, count(*) from students group by gender having count(*) > 2;

• 根據(jù)gender字段進(jìn)行分組,行末匯總所有人的年齡(rollup 在行末顯示總匯總信息)

select gender, group_concat(age) from students group by gender with rollup;

多表連接查詢

inner/left/right on :連接查詢關(guān)鍵字
on:連接查詢條件
先創(chuàng)建班級表:

create table classes(id int unsigned not null primary key auto_increment,name varchar(20) not null);

插入3條班級數(shù)據(jù):

insert into classes values(0,"一班"),(null,"二班"),(null,"三班");

students 表增加c_id字段,并賦值

• 內(nèi)連接(以兩表指定字段的交集連接)

select * from students as s inner join classes as c on s.c_id=c.id

• 左連接(以左表為基準(zhǔn)數(shù)查詢右表數(shù)據(jù),右表數(shù)據(jù)不存在時顯示null)

select * from students as s right join classes as c on s.c_id = c.id

• 右連接(與左連接相反)

select * from students as s right join classes as c on s.c_id = c.id

• 自連接(左表右表是同一個數(shù)據(jù)源表,自連接必須對表取不同別名)

select c.id, c.title, c.pid, p.title from areas c inner join areas p on c.pid = p.id;

子查詢

在一個 select 語句中,嵌入了另外一個 select 語句, 那么被嵌入的 select 語句稱之為子查詢語句，外部那個select語句則稱為主查詢.
子查詢是可以獨(dú)立使用的查詢語句,輔助主查詢充當(dāng)數(shù)據(jù)源.

• 查詢大于平均年齡的學(xué)生

select * from students where age > (select avg(age) from students);

• 查找年齡最大,身高最高的學(xué)生

select * from students where (age,height) = (select max(age),max(height) from students);

添加/刪除外鍵約束

• 添加外鍵約束

1. 創(chuàng)建表時添加:

 create table school(
id int not null primary key auto_increment,
name varchar(30)
);

create table teacher(
id int not null primary key auto_increment,
name varchar(30), 
s_id int, 
foreign key (s_id) references school(id)
);

2. 為已存在的表添加:

alter table students add foreign key (c_id) references classes(id);

• 刪除外鍵

alter table drop foreign key teacher_ibfk_1;
(*外鍵名使用"show create table teacher"命令查找*)

Mysql高級使用 - 將查詢結(jié)果插入到其他表中

已存在商品表goods,包含字段:種類cate_name,商品名name,價格price

-- 先創(chuàng)建單獨(dú)的商品分類表,僅包含id和商品名name
create table goods_cate(
id int not null primary key auto_increment, name varchar(30) not null
);

-- 查詢goods表中商品的分類信息
select cate_name from goods group by cate_name;

-- 將查詢結(jié)果插入到good_cates表中
insert into goods_cate(name) select cate_name from goods group by cate_name;

-- 查看goods表中的商品分類名稱對應(yīng)的商品分類id
select * from goods g inner join goods_cate gc on g.cate_name=gc.name;

-- 將goods表中的分類名稱更改成商品分類表中對應(yīng)的分類id，連接更新表中的某個字段
updata goods inner join goods_cate on goods.cate_name=goods_cate.name set goods.cate_name=goods_cate.id;