Mysql2

1.排序 

1)單列排序

-- 查詢所有數據,使用年齡降序排序 
select * from student order by age desc; 

2)組合排序 

-- 查詢所有數據,在年齡降序排序的基礎上，如果年齡相同再以數學成績升序排序 
select * from student order by age desc, math asc; 

2.聚合函數 

-- 查詢學生總數 
select count(id) as 總人數 from student;  
select count(*) as 總人數 from student;

-- IFNULL()函數，如果記錄為 NULL，給個預設值，這樣統計的數據就不會遺漏 
select count(ifnull(id,0)) from student; 

-- 查詢年齡大於20的總數 
select count(*) from student where age>20; 
 
-- 查詢數學成績總分 
select sum(math) 總分 from student; 
 
-- 查詢數學成績平均分 
select avg(math) 平均分 from student; 
 
-- 查詢數學成績最高分 
select max(math) 最高分 from student; 
 
-- 查詢數學成績最低分 
select min(math) 最低分 from student; 

3.分組 

-- 按性別進行分組，求男生和女生數學的平均分 
select sex, avg(math) from student3 group by sex;

-- 查詢男女各多少人 
select sex, count(*) from student3 group by sex; 

-- 查詢年齡大於 25 歲的人,按性別分組,統計每組的人數 
select sex, count(*) from student3 where age > 25 group by sex ; 

-- 查詢年齡大於 25 歲的人，按性別分組，統計每組的人數，並只顯示性別人數大於 2 的數據 
-- 對分組查詢的結果再進行過濾 
SELECT sex, COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex having COUNT(*) >2; 

4.limit語句 

--公式：開始的索引 = （當前的頁碼 - 1） * 每頁顯示的條數
-- 查詢學生表中數據，從第3條開始顯示，顯示6條 
select * from student3 limit 2,6; 

-- 如果第一個參數是0可以省略寫
select * from student3 limit 5; 
 
-- 最後如果不夠5條，有多少顯示多少 
select * from student3 limit 10,5; 

5.資料庫備份和還原 

-- 備份day21資料庫中的數據到d:\day21.sql文件中 
mysqldump -uroot -proot day21 > d:/day21.sql 

--還原 （刪除 day21 資料庫中的所有表) 
use day21; 
source d:/day21.sql; 

6.主鍵約束 

-- 創建表學生表st5, 包含欄位(id, name, age)將id做為主鍵 
create table st5 (  
  id int primary key,  -- id為主鍵  
  name varchar(20),  
  age int
 ) 

-- 刪除st5表的主鍵 
alter table st5 drop primary key;

-- 添加主鍵 
alter table st5 add primary key(id); 

7.主鍵自增 

-- 指定起始值為1000 
create table st4 (   
   id int primary key auto_increment, 
   name varchar(20) 
 ) auto_increment = 1000; 
 
insert into st4 values (null, '孔明'); 
select * from st4; 

-- 創建好以後修改起始值 
alter table st4 auto_increment = 2000; 

8.唯一約束 

-- 創建學生表st7, 包含欄位(id, name),name這一列設置唯一約束,不能出現同名的學生 create table st7 (  
  id int, 
  name varchar(20) unique
 )

9.非空約束 

-- 創建表學生表st8, 包含欄位(id,name,gender)其中name不能為NULL 
create table st8 (
  id int,
  name varchar(20) not null,
  gender char(1) 
) 

10.預設值 

-- 創建一個學生表 st9，包含欄位(id,name,address)， 地址預設值是廣州 
create table st9 (
   id int,
   name varchar(20),
   address varchar(20)  default '廣州' 
) 
 

11.外鍵約束 

-- 創建部門表(id,dep_name,dep_location) 
-- 一方，主表 
create table department( 
 id int primary key auto_increment,
  dep_name varchar(20),
  dep_location varchar(20)
 ); 
 
-- 創建員工表(id,name,age,dep_id)
-- 多方，從表 
create table employee(
  id int primary key auto_increment,
  name varchar(20),
  age int,
  dep_id int  -- 外鍵對應主表的主鍵
 )

-- 1) 刪除副表/從表 
employee drop table employee; 
-- 2) 創建從表 employee 並添加外鍵約束emp_depid_fk 
-- 多方，從表
create table employee(
  id int primary key auto_increment,
  name varchar(20),
  age int,
  dep_id int,  -- 外鍵對應主表的主鍵
  -- 創建外鍵約束  
constraint emp_depid_fk foreign key (dep_id) references department(id)
 ) 

-- 刪除employee表的emp_depid_fk外鍵
alter table employee drop foreign key emp_depid_fk; 

-- 在employee表存在的情況下添加外鍵 
alter table employee add constraint emp_depid_fk  foreign key (dep_id) references department(id); 

12.外鍵的級聯 

-- 刪除employee表，重新創建employee表，添加級聯更新和級聯刪除  
drop table employee;    
create table employee( 
  id int primary key auto_increment,
  name varchar(20),
  age int,
  dep_id int,  -- 外鍵對應主表的主鍵
  -- 創建外鍵約束  constraint emp_depid_fk foreign key (dep_id) references     department(id) on update cascade on delete cascade
)