数据库工程师下午试题2 ： SQL语句

#五大约束

　　1、主键约束（Primay Key Coustraint） 唯一性，非空性---------  primary key
　　2、唯一约束 （Unique Counstraint）唯一性，可以空，但只能有一个----------------------- unique 
　　3、检查约束 （Check Counstraint）对该列数据的范围、格式的限制（如：年龄、性别等） ---------------------check (salary >= 190)
　　4、默认约束 （Default Counstraint）该数据的默认值  
　　5、外键约束 （Foreign Key Counstraint）需要建立两表间的关系并引用主表的列  

主键：唯一表示，不能为空  eno char(4)  primary key

外键： cno char(4) references company(cno)

检查约束： salary int check(salary >= 150)

主键（在最后）：primary key (eno,cno)

外键（在最后）：foreign key (x1) references tb1(x1)

默认约束：sex char(4) default  “男”

 

#创建视图

可以使用 create view  语句来创建视图。

 CREATE VIEW <视图名> AS <SELECT语句>

1. 创建view 默认情况下，创建的视图和基本表的字段是一样的

create view stu_view as 
select * from Students

 

2.也可以通过指定视图字段的名称来创建视图。

create view stu_view(eno,cno,cname) as 
select eno,cno,cname  from  Students

 

在使用视图时，可能用户不需要了解基本表的结构，更接触不到实际表中的数据，从而保证了数据库的安全。

 

#创建触发器

触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性。

举个例子，比如你现在有两个表【用户表】和【日志表】，当一个用户被创建的时候，就需要在日志表中插入创建的log日志，如果在不使用触发器的情况下，你需要编写程序语言逻辑才能实现，但是如果你定义了一个触发器，触发器的作用就是当你在用户表中插入一条数据的之后帮你在日志表中插入一条日志信息。当然触发器并不是只能进行插入操作，还能执行修改，删除。

CREATE TRIGGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW trigger_stmt
trigger_name：触发器的名称
tirgger_time：触发时机，为BEFORE或者AFTER
trigger_event：触发事件，为INSERT、DELETE或者UPDATE
tb_name：表示建立触发器的表明，就是在哪张表上建立触发器
trigger_stmt：触发器的程序体，可以是一条SQL语句或者是用BEGIN和END包含的多条语句
所以可以说MySQL创建以下六种触发器：
BEFORE INSERT,BEFORE DELETE,BEFORE UPDATE
AFTER INSERT,AFTER DELETE,AFTER UPDATE

 

BEFORE和AFTER参数指定了触发执行的时间，在事件之前或是之后

FOR EACH ROW表示任何一条记录上的操作满足触发事件都会触发该触发器

前面的tb_name 定义在修改的文件表上， 后面需要用到另外一个表，即修改的表，因为前面的tb_name改变而改变的表：即

begin
    trigger_event houmian_table
    set x=y
    where  nrow.nn= houmian_table.nn;
end

 

//删除触发器
drop trigger triggername;

 

#创建存储过程

SQL语句需要先编译然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。简单来说 有点像 函数

 

create procedure product_name()
begin
    select avg(price) as price_avg
    from products;
end;
// 返回平均值
//接受参数，填写在括号里面。
//begin end 限定存储过程体

 

 

//调用上诉存储过程
call product_name();
//删除上诉存储过程
drop proceduer product_name;

 

平常的创建存储过程

create proceduer productprice(
out pl decimal(8,2),
out ph decimal(8,2),
out pa decimal(8.2)
)
begin
    select min(price) into pl from products;
    select max(price) into ph from products;
    select avg(price) into pa from products;
end;
//接受3个参数，调用时，必须制定3个变量名，这三个就是保存数据的3个变量
call productprice(@pricelow,@pricehigh,@priceavg)
//这样就把数据存入3个变量里面了，如果需要某个变量，select一下就好了
select @pricelow,@pricehigh;

 

create proceduer ordertotal(
in onumber int,
out ototal decimal(8,2)
)
begin
    select sum(price*quantity) from orderitems 
    where order_num= onumber 
    into ototal;
end;

//onumber 定义为In，需要传入订单号，out ototal，就是需要反馈出来的

call ordertotal(@200005,@total);

select total;
就得到了订单为200005 的价格

#alter用法

//alter是对表进行更新
//给表添加1列
alter table vendors
add vend_phone char(20);


//给表删除1列
alter table vendors
drop column vend_phone;

//给表定义外检
alter table orderitems
add constraint fk_order
foreign key (order_num) references orders(order_num);

//删除表
drop table fk_order

//重新命名表
rename table customers to cusm,
                    fk_order to forder;

 

 

 

 

 

#insert用法

//插入完整的行
insert into custmers(custname,
custid,
custcity,
custsex
)
values('zhangming',
1000,
null,
'female'
);

//一下插入多行
insert into custmers(custname,
custid,
custcity,
custsex
)
values('zhangming',
1000,
null,
'female'
),    // 用逗号间隔开来，只需要一个values
('zhangpeng',
1200,
'nanjing',
'male'
)

//插入搜索出来的数据
insert into custmers(custname,
custid,
custcity,
custsex
)  // 直接跟上select
select custname,custid,custcity,custsex 
from custnew;

 

#update  delete 用法

//使用update 一定要注意加上where ，否则可能出错

update customers
set cust_email = ' www.xx.com'， //只需要1个set即可
     cust_name= ' zhangxiaoming'
where cust_id = 1005;


//删除数据

delete from customers
where cust_id = 10005;

#case when 用法

select cust_id,
    case 
        when 教育水平 > 20 THEN ' 研究生'
        when 教育水平 < 20 and 教育水平>16  THEN ' 本科生‘
        else '其他'
    end
from 员工，部门
where 员工.x=部门.x

 

 

 

#常用语法

ALL运算符是一个逻辑运算符，它将单个值与子查询返回的单列值集进行比较。
ALL运算符必须以比较运算符开头，例如：>，>=，<，<=，<>，=，后跟子查询。
如下，对于ALL运算符的条件和描述说明：

条件	描述
c > ALL(…)	c列中的值必须大于要评估为true的集合中的最大值。
c >= ALL(…)	c列中的值必须大于或等于要评估为true的集合中的最大值。
c < ALL(…)	c列中的值必须小于要评估为true的集合中的最小值。
c <= ALL(…)	c列中的值必须小于或等于要评估为true的集合中的最小值。
c <> ALL(…)	c列中的值不得等于要评估为true的集合中的任何值。
c = ALL(…)	c列中的值必须等于要评估为true的集合中的任何值。

 

ANY运算符是一个逻辑运算符，它将值与子查询返回的一组值进行比较。 ANY运算符必须以比较运算符：>，>=，<，<=，=，<>开头，后跟子查询。
如下，对于ANY运算符的条件和描述说明：

 

条件	描述
x = ANY (…)	c列中的值必须与集合中的一个或多个值匹配，以评估为true。
x != ANY (…)	c列中的值不能与集合中的一个或多个值匹配以评估为true。
x > ANY (…)	c列中的值必须大于要评估为true的集合中的最小值。
x < ANY (…)	c列中的值必须小于要评估为true的集合中的最大值。
x >= ANY (…)	c列中的值必须大于或等于要评估为true的集合中的最小值。
x <= ANY (…)	c列中的值必须小于或等于要评估为true的集合中的最大值。

 

 

sum(xx)  求总和   

avg(xx)  求平均

count(*) 求个数

select x1,x2,x3 from y1,y2 where y1.no=y2.no and y2.sex='nan' group by department having count(*) >= all (select count(*) from y2 group by department)

select x1,x2,x3
 from y1,y2 
where y1.no=y2.no and y2.sex='nan' 
group by department 
having count(*) >= all (
                                    select count(*) from y2 
                                    group by department)

 

in 在list里面

not in 

 like