-----------------建立表-----------------------------------

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 ‘姓名’  from employee

-- (5) 查询结果添加说明文字
--  查询所有员工的姓名，电话号码。（电话号码列在结果中显示为：“XXX的电话号码是：”）
    Select  emp_lname + emp_fname +’的电话号码是:’+phone from employee

-- (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:\haima\stu.mdf", --主数据文件名的存放路径
   size=1mb,                    --文件大小
   maxsize=2mb,                 --文件最大
   filegrowth=1mb               --文件增长
)
,
(
   name=stu1,                    --辅助数据文件名 stu1
   filename="d:\haima\stu1.ndf", --文件存放的地址
   size=1mb,
   maxsize=2mb,
   filegrowth=1mb
)
   log on
(                                --日志文件     stu2
   name=stu2,
   filename="d:\haima\stu2.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:\haima\stu3.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='共产党员' or visage='团员'

 

--（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) references student(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) references student(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 employee values('1','张三','男','1','西安','1336666','0295444545','54545','101','2006-5-6')
insert into employee values('2','李四','男','1','西安','1336666','0295444545','54545','101','2006-5-6')

insert into department values('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 存储过程名