SQL語句大全

--登入資料庫
mysql -uroot -p  
解釋說明: -u表示使用者名, -p：密碼

--顯示目前時間
select now();

--登出(退出)資料庫
exit/quit/ctr+d

--檢視所有資料庫
show databases;

--建立資料庫
create database py40 charset=utf8;

--使用資料庫
use py40;
--檢視資料庫裡面的所有表
show tables;

--檢視目前使用的資料庫
select database();

--删除資料庫-慎重
drop database py40

--檢視目前資料庫中所有表
show tables;

--建立表
create table students(id int unsigned not null primary key auto_increment,name varchar(10) not null, age tinyint default 0, height decimal(3,2), gender enum('男', '女') default '女');


--修改表-添加birthday字段
alter table students add birthday date;

--修改表-修改字段類型
alter table students modify birthday datetime not null;

--修改表-修改字段名和字段類型
alter table students change birthday birth date;

--修改表-删除birthday字段
alter table students drop birth;


--檢視表結構
desc students

--檢視創表SQL語句
show create table students;

--檢視創庫SQL語句
show create database python40;

--删除表
drop table students;

--查詢所有列資料
select * from students;
--查詢指定列資料
select name,age from students;

--添加資料--全列插入
insert into students values(null, '黃蓉', 18, 1.55, '女')
提示: 主鍵自增列可以使用0或者null或者default

--添加資料--部分列插入
insert into students(name, age) values('黃老邪',60);  
--添加資料--全列多行插入
insert into students values(null, '黃蓉', 18, 1.55, '女'),(null, '郭靖', 18, 1.55, '女');

--添加資料--部分列多行插入
insert into students(name, age) values('黃老邪',60),('歐陽鋒',60);  

--修改資料
update students set height = 1.8, gender='男' where id = 8;

--删除資料
delete from students where id = 9;
--邏輯删除，添加表示字段，删除資料其實是對辨別字段的修改
alter table students add is_del int not null default 0;

update students set is_del = 1 where id = 10;

--as關鍵字
select name as n, age as a from students s;
提示: as可以省略表示也是設定别名

--distinct關鍵字,去除重複
select distinct height, gender from students;


--查詢編号大于3的學生
select * from students where id > 3;

--查詢編号不大于4的學生
select * from students where id <= 4;

--查詢姓名不是“黃蓉”的學生
select * from students where name != '黃蓉';
select * from students where name <> '黃蓉';

--查詢沒被删除的學生
select * from students where is_del = 0;

--查詢編号大于3的女同學
select * from students where id > 3 and gender = '女';

--查詢編号小于4或沒被删除的學生
select * from students where id < 4 or is_del = 0;

--查詢年齡不在10歲到15歲之間的學生
select * from students where not (age >= 10 and age <= 15);


--查詢姓黃的學生
select * from students where name like '黃%';

% 表示任意多個字元

--查詢姓黃并且“名”是一個字的學生
select * from students where name like '黃_';

_ 表示任意一個字元

--查詢姓黃或叫靖的學生
select * from students where name like '黃%' or name like '%靖';

--查詢編号為3至8的學生
select * froms students where id >= 3 and id <= 8;
select * from students where id between 3 and 8;

--查詢編号不是3至8的男生
select * from students where not (id between 3 and 8);

--查詢編号是3、5、7的學生
select * from students where id in (3, 5, 7);

--查詢編号不是3、5、7的學生
select * from students where id not in (3, 5, 7);

--查詢沒有填寫身高的學生
select * from students where height is null;
--查詢身高不為空的學生
select * from students where height is not null;

--查詢未删除男生資訊，按學号降序
select * from students where is_del = 0 and gender='男' order by id desc;

--顯示所有的學生資訊，先按照年齡從大-->小排序，當年齡相同時 按照身高從高-->矮排序
select * from students order by age desc, height desc;

--查詢前3行男生資訊，limit後面的參數，從第一個參數開始，往後查詢第二個參數個。
select * from students where gender = '男' limit 0, 3;
簡寫
select * from students where gender = '男' limit 3;

limit是分頁查詢的關鍵字，第一個參數表示開始行索引， 第二個參數是查詢的條數

--查詢學生表，擷取第n頁資料的SQL語句

提示： 求第n頁資料，其實先把開始行索引計算出來即可
select * from student limit (n-1)*10, 10      

-- 統計學生表裡面的總人數
select count(id) from students;
select count(height) from students;  注意點聚合函數不統計NULL值
提示: 如果要是必須指定某一個字段，那麼這個字段應該是主鍵
如果不想指定字段而且統計的資料不出現問題，可以使用*
select count(*) from students;
-- 擷取年齡最大的學生
select max(age) from students;
-- 查詢女生的編号最大值
select max(age) from students where gender = '女';
-- 查詢未删除的學生最小編号
select min(id) from students where is_del = 0;
-- 查詢男生的總身高
select sum(height) from students where gender = '男'
-- 求平均值
select sum(height)/count(*) from students where gender = '男'
-- 不統計null的平均值
select avg(height) from  students where gender='男';
-- 統計null的平均值
select avg(ifnull(height, 0)) from students where gender='男';
-- 根據gender字段來分組
select gender from students group by gender;

-- 根據name和gender字段進行分組
select gender,name from students group by gender, name;

-- 根據gender字段進行分組， 查詢gender字段和分組的name字段資訊
select gender, group_concat(name) from students group by gender;

-- 提示: 如果給表進行分組，那麼查詢的字段隻能是指定分組的字段， 其它字段要想查詢可以使用聚合函數
-- 統計不同性别的人的平均年齡
select gender, avg(age) from students group by gender;
-- 統計不同性别的人的個數
select gender, count(*) from students group by gender;
-- 根據gender字段進行分組，統計分組條數大于6的
select gender, count(*) from students group by gender having count(*) > 5;
-- 根據gender字段進行分組，彙總總人數
select gender, count(*) from students group by gender with rollup;

-- 根據gender字段進行分組，彙總所有人的年齡
select gender, group_concat(age) from students group by gender with rollup;

-- 使用内連接配接查詢學生表與班級表
select s.name, c.name from students s inner join classes c on s.c_id = c.id;

提示: 做連接配接查詢的時候可以給表設定别名，使用更加友善
‘
-- 使用左連接配接查詢學生表與班級表
select s.name, c.name from students s left join classes c on s.c_id = c.id;

左連接配接查詢以左表為主，查詢右表資料，右表不存在使用null
left關鍵字左邊的表是左表，left右邊的表使用右表

-- 使用右連接配接查詢學生表與班級表
select s.name, c.name from students s right join classes c on s.c_id = c.id;
右連接配接查詢以右表為主，查詢左表資料，左表不存在使用null
right關鍵字左邊的表是左表，right右邊的表使用右表

-- 使用自連接配接查詢省份和城市資訊
select c.id, c.title, c.pid, p.title from areas c inner join areas p on c.pid = p.id where p.title='山西省';

-- 查詢大于平均年齡的學生
select * from students where age > (select avg(age) from students);

子查詢其實就是一條完整的select查詢語句，先執行子查詢，然後在執行主查詢

-- 查詢學生在班的所有班級名字
select * from classes where id in (select c_id from students where c_id is not null);

-- 查找年齡最大,身高最高的學生

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

簡寫：
 select * from students where (age, height) = (select max(age), max(height) from students);
 
-- 為學生表的c_id字段添加外鍵限制（如果）
外鍵限制:對外鍵字段的值進行更新和插入時會和引用表中字段的資料進行驗證，資料如果不合法則更新和插入會失敗，保證資料的有效性
alter table students add foreign key(c_id) references classes(id);

-- 建立學校表
create table school( id int unsigned not null primary key auto_increment, name varchar(50) not null );


-- 建立老師表添加學校外鍵
create table teacher( id int unsigned not null primary key auto_increment, name varchar(30), sid int unsigned, foreign key(sid) references school(id) );
-- 删除外鍵
alter table teacher drop foreign key teacher_ibfk_1      

-- 查詢類型cate_name為 '超級本' 的商品名稱、價格
select name, price from goods where cate_name = '超級本';

-- 顯示商品的分類
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;select distinct cate_name from goods

-- 求所有電腦産品的平均價格,并且保留兩位小數
 select round(avg(price),2) from goods;

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

select cate_name, round(avg(price),1) from goods group by cate_name;

-- 查詢每種類型的商品中 最貴、最便宜、平均價、數量

select cate_name, max(price), min(price), avg(price), count(*) from goods group by cate_name;

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

-- 建立商品分類表
 create table goods_cates(
    id int unsigned not null primary key auto_increment,
    name varchar(50)
 );

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

-- 将查詢結果插入到good_cates表中
insert into goods_cates(name) select cate_name from goods group by cate_name;

-- 添加移動裝置分類資訊
insert into goods_cates(name) values('移動裝置');

-- 檢視goods表中的商品分類名稱對應的商品分類id
select g.cate_name, gc.id from goods g inner join goods_cates gc on g.cate_name = gc.name;


-- 将goods表中的分類名稱更改成商品分類表中對應的分類id，連接配接更新表中的某個字段

update  goods g inner join goods_cates gc on g.cate_name = gc.name set g.cate_name = gc.id;

update 表名 set 列名=列值

-- 查詢品牌資訊
select brand_name from goods group by brand_name;

-- 通過create table ...select來建立商品品牌表并且同時插入資料
create table goods_brands( id int unsigned not null primary key auto_increment, name varchar(30)) select brand_name as name from goods group by brand_name;

insert into goods_brands(name) select brand_name from goods group by brand_name;

-- 插入雙飛燕品牌
insert into goods_brands(name) values('雙飛燕');

-- 檢視goods表中的商品品牌對應的商品品牌id
select g.brand_name, gs.id from goods g inner join goods_brands gs on g.brand_name = gs.name

-- 将goods表中的品牌更改成品牌表中對應的品牌id，連接配接更新表中的某個字段
update goods g inner join goods_brands gs on g.brand_name = gs.name set g.brand_name = gs.id;

-- 通過alter table語句修改表結構,把cate_name改成cate_id，把brand_name改成brand_id
alter table goods change cate_name cate_id int not null, change brand_name brand_id int not null;      

多思考也是一種努力，做出正确的分析和選擇，因為我們的時間和精力都有限，是以把時間花在更有價值的地方。