爱华网create table department
(
dept_id varchar(8) not null,
dept_name varchar(20),
dept_head_id varchar(8) not null
)
create table employee
(
emp_id varchar(8) not null primary key ,
emp_fname varchar(20),
emp_lname varchar(20),
sex char(2),
dept_id varchar(8),
city varchar(100),
zip_code int,
phone int,
ss_number int,
salary int not null,
birth_date datetime not null
)
alter table department
add constraint pk_department_dept_head_id
primary key (dept_head_id)
alter table department
add constraint fk
foreign key
(dept_head_id)
references
employee(emp_id)
--------------------简单的SQL语法-------------------------------
-- (一) 简单的sql 查询
--(1)不限制列查询
--显示员工表中所有列的信息
select * from employee
-- (2)限制列查询
--查询所有员工的员工号与员工姓。
--用SELECT子句来指定查询所需的列，多个列之间用逗号分开。
select emp_id,emp_lname from employee
-- (3)查询全体员工的员工号、姓名。
--（姓名要求用一个字段显示出来）
select emp_id,emp_lname+emp_fname from employee
-- (4)在查询结果中添加列
--为上题的查询结果添加一个别名”姓名”
--查询结果中产生的列，别名用AS命名。
select emp_id,emp_lname+emp_fname as ‘姓名’ fromemployee
-- (5) 查询结果添加说明文字
-- 查询所有员工的姓名，电话号码。（电话号码列在结果中显示为：“XXX的电话号码是：”）
Select emp_lname + emp_fname +’的电话号码是:’+phone fromemployee
-- (6)对查询结果进行排序
-- 查询所有员工名单，并将工资降序排序。
-- 说明：Order By 有一个附加参数：ASC | DESC表明是升序排序还是降序排序。缺省ASC。
Select emp_id,emp_lname+emp_fname ‘姓名’,salary
from employee
Order by salary desc
-- (7)去冗余查询
--查询员工来自的城市（不出现重复的城市名称）。
--说明：使用DISTINCT消除重复数据。
Select distinct city from employee
-- (8)选取前几行数据
--查询员工表中前三名员工的姓名与工号。
--说明：在SELECT语句中使用TOP n或 TOP n PERCENT，
--来选取查询结果的前n行或前百分之n的数据。
Select top 3 emp_id,
emp_lname+emp_fname ‘姓名’
from employee
--------------------建立数据库---------------------------------------
create database web -- 创建数据库 名为 web
on
(
name=stu, -- 主数据文件名 stu
filename="d:haimastu.mdf", --主数据文件名的存放路径
size=1mb, --文件大小
maxsize=2mb, --文件最大
filegrowth=1mb --文件增长
)
,
(
name=stu1, --辅助数据文件名 stu1
filename="d:haimastu1.ndf", --文件存放的地址
size=1mb,
maxsize=2mb,
filegrowth=1mb
)
log on
( --日志文件 stu2
name=stu2,
filename="d:haimastu2.ldf",
size=1mb,
maxsize=2mb,
filegrowth=25%
)
-- 删除 数据库 web
drop database web
--添加数据 文件组 名字为 soft
alter database web
add filegroup soft
-- 给文件组 soft 添加 文件stu3
alter database web
add file
(
name=stu3,
filename="d:haimastu3.ndf"
) to filegroup soft
-- 数据库属性
sp_helpdb web
-- 重命名数据库 名称 web 改为 web1
sp_renamedb web,web1
sp_renamedb web1,web
--1、用SQL语句创建学生表，表中字段及其数据类型如下所示：（20分）
--（1）．学生（学号varchar(10)，姓名varchar(8)，性别char(2)，政治面貌varchar(20)），其中学号字段设置为主键字段。
create table student
(
stu_id varchar(10) not null primary key,
stu_name varchar(10),
sex char(2),
visage varchar(20)
)
-- 查看表约束
sp_helpconstraint student
-- 删除表
drop table student
--第二种加主键的方法
create table student
(
stu_id varchar(10) not null,
stu_name varchar(10),
sex char(2),
visage varchar(20)
constraint pk_stu_id primary key(stu_id)
)
-- 第三种加主键方法
create table student
(
stu_id varchar(10) not null,
stu_name varchar(10),
sex char(2),
visage varchar(20)
)
alter table student
add constraint pk_stuid primary key(stu_id)
--（2）.用SQL语句将学生表的政治面貌字段数据类型改为varchar(26)。
alter table student
alter column visage varchar(26)
sp_help student
--（3）.用SQL语句为学生表增加一个新的字段：班级char(20)。
alter table student
add class char(20)
--（4）.用SQL语句删除学生表的班级字段。
alter table student
drop column class
--（5）.用SQL语句为学生表添加如下记录：（’0001'，’张三’,’男’,’党员’）。
insert into student values('001','张三','男','党员')
select * from student
--（6）.用SQL语句将学生表中所有政治面貌为‘党员’的记录改为‘共产党员’。
update student set visage='共产党员' where visage='党员'
--（7）.用SQL语句删除学生表中的学号为‘0003’的记录。
insert into student values('003','李四','男','党员')
select * from student
delete from student where stu_id='003'
--（8）.用SQL语句查出所有男同学的学号、姓名、政治面貌。
insert into student values('004','李四','男','党员')
insert into student values('005','李四1','女','党员')
insert into student values('006','李四2','男','党员')
select * from student where sex='男'
--（9）.用SQL语句查出政治面貌为‘共产党员’或者‘团员’的学生信息
insert into student values('007','李四1','女','共产党员')
insert into student values('008','李四2','男','共产党员')
insert into student values('009','李四2','男','团员')
select * from student where visage='共产党员' orvisage='团员'
--（10）.用SQL语句查出每种政治面貌的学生人数。
select count(*) as 人数,visage from student
group by visage
select * from student
-- 外键
-- 先建一张学生表
drop table Stu_grade
-- 第一种
create table Stu_grade
(
id int not null primary key,
stu_id varchar(10) foreign key(stu_id) references student(stu_id),
grade varchar(20),
)
sp_help Stu_grade
-- 第二种
create table Stu_grade
(
id int not null primary key,
stu_id varchar(10) ,
grade varchar(20),
constraint fk_stuid foreign key(stu_id) referencesstudent(stu_id)
)
-- 第三种
create table Stu_grade
(
id int not null primary key,
stu_id varchar(10),
grade varchar(20)
)
alter table Stu_grade
add constraint fk_stuid foreign key(stu_id) referencesstudent(stu_id)
--- 惟一性约束
--(1) 第二种
create table student
(
stu_id varchar(10) not null,
stu_name varchar(10) unique,
sex char(2),
age int,
visage varchar(20)
)
--(2)第二种
create table student
(
stu_id varchar(10) not null,
stu_name varchar(10),
sex char(2),
age int,
visage varchar(20)
constraint un_name unique(stu_name)
)
--(3) 第三种
alter table student
add constraint un_name unique(stu_name)
-- 检查约束
--(1)
create table student
(
stu_id varchar(10) not null,
stu_name varchar(10),
sex char(2),
age int,
visage varchar(20)
constraint ch_sex check(sex in ('男','女'))
)
-- (2) alter table student
add constraint ch_sex check(age>=18 and age<=40)
-- 缺省约束
--(1)
create table student
(
stu_id varchar(10) not null,
stu_name varchar(10),
sex char(2) default '男',
age int,
visage varchar(20)
)
(2)
create table student
(
stu_id varchar(10) not null,
stu_name varchar(10),
sex char(2) default '男',
age int,
visage varchar(20)
constraint de_sex default '男'
)
--(3)
alter table student
add constraint de_sex default '男'
----------------------条件限制查询------------------------------------
--(1)比较条件查询
Select emp_id,emp_name
from employee
Where salary>50000
And sex=‘男’
--(2) 模糊查询
Select emp_id,emp_name
from employee
Where emp_name like ‘%P%’
--(3)范围条件查询
--查询出工资介于20000元和30000元之间的员工姓名。
Select emp_id,emp_name
from employee
Where salary between 20000 and 30000
--(4)使用统计函数查询
--查询员工表中共有多少名员工。
Select count(*) from employee
Select count(emp_id) from employee
--(5)分组查询
--查询各个部门的总人数各是多少
Select count(*)
from employee
Group by dept_id
--(6)带条件分组查询
-- 显示部门人数多于20人的部门编号与人数。
Select dept_id,count(*)
from employee
Group by dept_id
Having count(*)>20
-------------------------增删改查-----------------------------------
insert into users
values (10000,'haha');
-- 一个表向另一个表复制行
--insert合select组合使用,介绍oracle的merge来复制行
insert into users
(select userid,name
from users
where userid=10004)
添加
insert into employeevalues('1','张三','男','1','西安','1336666','0295444545','54545','101','2006-5-6')
insert into employeevalues('2','李四','男','1','西安','1336666','0295444545','54545','101','2006-5-6')
insert into departmentvalues('1','网络部','1')
-- 修改
update users
set name='heihei'
where userid=10003;
-- 删除
delete from users
where userid=10001;
------------------------------连接查询------------------------------】
-- 连接查询
--(1)等值连接查询
--查询所有员工所在的部门名称。
Select emp_id,e.dept_id,dept_name
from employee e, department d
Where e.dept_id=d.dept_id
--(3) 非等值连接查询
-- 在非等值查询的连接条件中不使用等号，而使用其它比较运算符。
--比较运算符有：>、>=、<、<=、!=,<>
--查询所有非领导的员工的所有信息
Select emp_id,e.dept_id,dept_name
from employee e,department d
Where e.dept_id=d.dept_id
and e.emp_id<>d.dept_head_id
-- (二)高级查询
-- 在一个SELECT语句的WHERE子句或HAVING子句中嵌套另一个SELECT语句的查询称为嵌套查询，又称子查询。
-- (1) 查询出工资最高的员工的个人信息。
select *from employee
where salary =
(select max(salary)
from employee)
-- (2)查询各个部门中工资最高的人的个人信息。
select * from employee
where salary in
(select max(salary)
from employee
Group by dept_id)
-- (3)查询出工资超过90000元的员工的部门名称，查询结果按部门编号排序。
--(使用谓词IN连接子查询)
select dept_id,dept_name
from department
where dept_id in (
select dept_id
from employee
where salary>90000)
order by dept_id desc
-- ××××××××××虚拟表查询×××××
--(5)各个部门中按性别分组的平均工资各是多少。
-- 1、先将各部门女员工的平均工资计算出来。
--2、再将各部门男员工的平均工资计算出来。
--3、以上面的1、2为两张虚拟表，进行连接查询。
select f.dept_id,fsalary,msalary
from
(select dept_id,avg(salary) fsalary
from employee
group by dept_id,sex
having sex='f') f, ------各部门女员工的平均工资。
(select dept_id,avg(salary) msalary
from employee
group by dept_id,sex
having sex='m') m -----各部门男员工的平均工资
where f.dept_id=m.dept_id
-----------------------------存储过程-----------------------------------
-(1)无参数的存储过程
-- 创建一个存储过程，存储各部门的总工资与平均工资及部门编号。
create proc deptsalary
as
select avg(salary) avgsalary, sum(salary) sumsalary,dept_id
from employee
group by dept_id
-- 调用
exec deptsalary
--(2) 带有输入参数的存储过程
-- 创建一个存储过程,以员工姓名为参数,输入姓名后显示某人信息(模糊查询).
create proc empname
@name varchar(20)
as
select *
from employee
where emp_lname+emp_fname
like '%'+@name+'%'
-- 调用
exec empname 'A'
-- (3)带有输出参数的存储过程
-- 创建一个存储过程,能够输出工资最高员工的工资。
create proc empsalary
@empsal decimal(20,3) output
as
select @empsal=max(salary)
from employee
--调用带输出参数的存储过程:
declare @empsal decimal(20,3)
exec empsalary @empsal output
print @empsal
--(4)查看 存储过程
-- sp_helptext 存储过程名
sp_helptext deptsalary
sp_helptext empname
sp_helptext empsalary
--(5)sp_rename 旧存储过程名， 新存储过程名
--(6)drop proc 存储过程名
