合計函數和表約束實現
0 合計函數
avg()
max(),min()，當max()和min()函數位于日期類型時，分別取得最近日期和最早日期
案例：
求一個班級數學平均分。
select sum(math) / count(math) as 數學平均分
from student;
select avg(math) as 數學平均分
from student;
select avg(name) as XX平均分
from student;//0
求一個班級總分平均分。
select (sum(chinese)+sum(math)+sum(english)) / count(*)
from student;
select avg(chinese+math+english)
from student;
求班級語文最高分和最低分。
select max(name),min(name)
from student;
drop table if exists teacher;
create table teacher(
id int,
name varchar(20),
birthday date
);
insert into teacher(id,name,birthday) values(1,'jack','2011-1-1');
insert into teacher(id,name,birthday) values(2,'marry','2011-2-2');
insert into teacher(id,name,birthday) values(3,'sisi','2011-3-3');
select max(birthday),min(birthday)
from teacher;
對訂單表中商品歸類后，顯示每一類商品的總價
select product as 類別名,sum(price) as 商品類別總價
from orders
group by product;
查詢購買了幾類商品，并且每類總價大于100的商品
select product as 類別名,sum(price) as 商品類別總價
from orders
group by product
having sum(price) > 100;
where v.s. having區別：
where主要用于行過濾器
having主要用于類別過濾器，通常有having就一定出現group by,但有group by的地方，不一定出現having。
drop table if exists teacher;
create table teacher(
id int primary key auto_increment,
name varchar(20) not null unique,
birthday date
);
insert into teacher(name,birthday) values(NULL,'2011-1-1');
insert into teacher(name,birthday) values('marry','2011-2-2');
insert into teacher(id,name,birthday) values(3,'sisi','2011-3-3');
select max(birthday),min(birthday)
from teacher;
刪除主鍵，主鍵在表中只有一個，要么是一列，要么是多列
alter table teacher drop primary key;
一對一關系(方案一)：
drop table if exists card;
drop table if exists person;
create table person(
id int primary key auto_increment,
name varchar(20) not null
);
insert into person(name) values('jack');
insert into person(name) values('marry');
create table card(
id int primary key auto_increment,
location varchar(20) not null,
pid int,
constraint pid_FK foreign key(pid) references person(id)
);
insert into card(location,pid) values('BJ',1);
insert into card(location,pid) values('GZ',2);
insert into card(location,pid) values('CS',NULL);
insert into card(location,pid) values('NJ',3);//出錯
//刪除person表的某記錄
delete from person where name = 'jack';
一對一關系(方案二)：
drop table if exists card;
drop table if exists person;
create table person(
id int primary key auto_increment,
name varchar(20) not null
);
insert into person(name) values('jack');
insert into person(name) values('marry');
create table card(
id int primary key auto_increment,
location varchar(20) not null,
constraint id_FK foreign key(id) references person(id)
);
insert into card(location) values('BJ');
insert into card(location) values('GZ');
insert into card(location) values('CS');//出錯
insert into card(location) values(NULL);
一對多/多對一關系：
drop table if exists employee;
drop table if exists department;
create table department(
id int primary key auto_increment,
name varchar(20) not null
);
insert into department(name) values('軟件部');
insert into department(name) values('銷售部');
create table employee(
id int primary key auto_increment,
name varchar(20) not null,
did int,
constraint did_FK foreign key(did) references department(id)
);
insert into employee(name,did) values('jack',1);
insert into employee(name,did) values('marry',1);
問題?查詢"軟件部"的所有員工(組合式)
select d.name as 部門名,e.name as 員工名
from department as d,employee as e
where d.name = '軟件部';
思考：還有沒有其它方法?
分解：
(1)select id from department where name='軟件部';
(2)select name from employee where did = 1;
(總)嵌入式SQL
select name as 員工
from employee
where did = (
select id
from department
where name='軟件部'
);
多對多關系：
drop table if exists middle;
drop table if exists student;
drop table if exists teacher;
create table if not exists student(
id int primary key auto_increment,
name varchar(20) not null
);
insert into student(name) values('jack');
insert into student(name) values('marry');
create table if not exists teacher(
id int primary key auto_increment,
name varchar(20) not null
);
insert into teacher(name) values('趙');
insert into teacher(name) values('蔡');
create table if not exists middle(
sid int,
tid int,
constraint sid_FK foreign key(sid) references student(id),
constraint tid_FK foreign key(tid) references teacher(id),
primary key(sid,tid)
);
insert into middle(sid,tid) values(1,1);
insert into middle(sid,tid) values(1,2);
insert into middle(sid,tid) values(2,1);
insert into middle(sid,tid) values(2,2);
問題?查詢"趙"所教過的所有學員
select t.name as 老師, s.name as 學員
from teacher as t,student as s,middle as m
where t.name = '趙'and m.sid=s.id and m.tid=t.id;
模式：
select 列出需要顯示的字段
from 列出所涉及到的所有表，建議寫別名
where 業務條件 and 表關聯條件
使用MySQL特有函數：
到年底還有幾少天?
select datediff('2011-12-31',now());
截取字符串
select substring('mysql',1,2); //從1開始
保留小數點后2位(四舍五入)
select format(3.1415926535657989,3);
向下取整(截取)
select floor(3.14);
select floor(-3.14);
select floor(3.54);
select floor(-3.54);
取隨機值
select format(rand(),2);
取1-6之間的隨機整數值
select floor(rand()*6) + 1;
MySQL擴展知識：
查MySQL文檔，利用MySQL的函數：隨機產生'a'-'z'之間的隨機字符。
隨機產生'a'-'z'之間的隨機字符
(1)查詢'a'-'z'對應的Unicode值
select ascii('a');//97
select ascii('z');//122
(2)產生97-122之間的隨機整數
select floor(rand()*26)+97;
(3)產生97-122對應的字符
select char(floor(rand()*26)+97);
查MySQL文檔，利用MySQL的函數：對密碼'123456'進行MD5加密。
select md5('123456');
drop table user;
create table user(
id int primary key auto_increment,
name varchar(20),
gender varchar(6),
salary float
);
insert into user(name,gender,salary) values('jack','male',4000);
insert into user(name,gender,salary) values('marry','female',5000);
insert into user(name,gender,salary) values('jim','male',6000);
insert into user(name,gender,salary) values('tom','male',7000);
insert into user(name,gender,salary) values('soso','female',NULL);
insert into user(name,gender,salary) values('haha','female',3500);
insert into user(name,gender,salary) values('hehe','female',4500);
select * from user;
MySQL特有流程控制函數：
1) if(value,第一值,第二值);
value為真，取第一值，否則取第二值
將5000元(含)以上的員工標識為"高薪"，否則標識為"起薪"
類似于Java中的三目運算符
select if(salary>=5000,'高薪','起薪')
from user;
2) ifnull(value1,value2)
value1為NULL，用value2替代
將薪水為NULL的員工標識為"無薪"
select name as 員工,ifnull(salary,'無薪') as 薪水情況
from user;
3) case when [value] then [result1] else [result2] end;
當value表達式的值為true時，取result1的值，否則取result2的值(if...else...)
將5000元(含)以上的員工標識為"高薪"，否則標識為"起薪"
select
case when salary>=5000 then '高薪'
else '起薪' end
from user;
4) case [express] when [value1] then [result1] when [value2] then [result2] else [result3] end;
當express滿足value1時，取result1的值，滿足value2時，取result2的值，否則取result3的值(switch...case..)
將7000元的員工標識為"高薪"，6000元的員工標識為"中薪",5000元則標識為"起薪"，否則標識為"低薪"
select
case salary
when 7000 then '高薪'
when 6000 then '中薪'
when 5000 then '起薪'
else '低薪' end
from user;
1 表的完整性
(1)實體完整性：每條記錄有一個唯一標識符，通常用無任何業務含義的字段表示
(2)參照完整性：一張表的某個字段必須引用另一張表的某個字段值
(3)域完整性：域即單元數據，域中的數值必須符合一定的規則
2 鍵的概念
(1)主鍵：只有唯一字段
(2)組合主鍵：由多個字段組合起來，形成唯一字段
(3)外鍵：針對多張表之間的關聯
3 主鍵的特點
(1)主鍵不能重復
(2)主鍵不能為NULL
(3)auto_increment是MySQL特有的，默認從1開始，該ID值與表同生亡
(4)多人項目中，通常使用UUID來生成唯一的主鍵值，便于多個合并數據時依然保持實體完整性
4 唯一約束的特點
(1)非NULL值不能重復
(2)可以插入多個NULL值
(3)'NULL'空串和NULL是不同的概念
5 非空約束特點
(1)不能插入NULL值
(2)主鍵約束=非NULL約束+唯一約束
6 外健特點
(1)外鍵值必須來源于所引用別一個表主鍵值，或NULL
*7 關聯關系
(1)一對一(外健根業務有關)
(2)一對多或多對一(外鍵放置在多方)
(3)多對多(外健放置在關聯表中，即將一個多對多拆分成二個一對多關系)
看文倉www.kanwencang.com網友整理上傳,為您提供最全的知識大全,期待您的分享，轉載請注明出處。
歡迎轉載：http://www.kanwencang.com/bangong/20170216/102226.html
文章列表
留言列表
