基礎(chǔ)命令
- 登錄數(shù)據(jù)庫
mysql -u root -p
- 顯示當前時間
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;
- 查看當前使用的數(shù)據(jù)庫
select database();
- 查看當前數(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ù).
默認 default: 當不填寫字段對應(yīng)的值會使用默認值缩幸，如果填寫時以填寫為準.
外鍵 foreign key: 對關(guān)系字段進行約束, 當為關(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;
-- 標記式刪除:
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;
- 查詢男生標號的最值
-- 查詢男生的編號最大值
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字段進行分組
select gender,name from students group by gender,name;
- 根據(jù)gender字段進行分組检痰， 查詢每個分組的姓名信息
-- 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字段進行分組，統(tǒng)計分組條數(shù)大于2的
select gender, count(*) from students group by gender having count(*) > 2;
- 根據(jù)gender字段進行分組,行末匯總所有人的年齡(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
- 左連接(以左表為基準數(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語句則稱為主查詢.
子查詢是可以獨立使用的查詢語句,輔助主查詢充當數(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);
添加/刪除外鍵約束
- 添加外鍵約束
- 創(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)
);
- 為已存在的表添加:
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)建單獨的商品分類表,僅包含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;
