Sql Server 03:DDL DML

--数据库表的简单操作
--创建数据库
CREATE DATABASE STUDENT;
--修改数据库名字
SP_RENAMEDB STUDENT,STU;
ALTER DATABASE STU MODIFY NAME=STUDENT;
--删除数据库
DROP DATABASE STUDENT;
--创建数据库
CREATE DATABASE STUDENT;
--使用数据库
 USE STUDENT;
--创建表
CREATE TABLE FRIEND (
ID NVARCHAR(10),
NAME CHAR(5) NOT NULL ,
SEX CHAR(2) ,
HEIGHT FLOAT,
BIRTHDAY DATE,
MAJOR VARCHAR(8) DEFAULT '计算机',
CONSTRAINT ID_PK PRIMARY KEY (ID),
CONSTRAINT SE_CH CHECK (SEX IN('男','女'))
);
--修改表名
EXEC SP_RENAME FRIEND,FRI;
EXEC  SP_RENAME @objname='FRI',@newname='FRIEND';
--添加列
ALTER TABLE FRIEND ADD COURSE VARCHAR(8);
--增加约束
ALTER TABLE FRIEND ADD CONSTRAINT un_co unique(course) ;--not null default不能用行级约束
--修改数据类型
ALTER TABLE FRIEND ALTER COLUMN COURSE VARCHAR(10);
--删除约束
ALTER TABLE FRIEND  DROP CONSTRAINT UN_CO;
--删除列
ALTER TABLE FRIEND DROP COLUMN COURSE;
--修改列名
EXEC SP_RENAME 'FRIEND.HEIGHT','HHEIGHT','COLUMN';
EXEC SP_RENAME 'FRIEND.HHEIGHT','HEIGHT','COLUMN';
--插入数据
INSERT INTO FRIEND VALUES('18210240','小金','男','172.8','1999-05-24','英语');
INSERT INTO FRIEND (ID,NAME,SEX,BIRTHDAY)VALUES('18210248','黄敏','女','1999-06-28');
--查询数据
 SELECT * FROM FRIEND;
 SELECT ID,NAME,SEX,MAJOR,BIRTHDAY FROM FRIEND;
 SELECT * FROM FRIEND ORDER BY ID DESC;
--更新数据
UPDATE FRIEND SET NAME='江玉' WHERE height IS NULL;
UPDATE FRIEND SET HEIGHT ='165.0'WHERE ID='18210248';
--删除行
DELETE  FRIEND WHERE BIRTHDAY='1999-05-24';
--删除表
DROP TABLE FRIEND;

--数据库表的基本查询两张表关联查询
CREATE DATABASE 公司;
USE 公司;
--部门表
CREATE TABLE department
(
id INT identity(1,1),--'部门主键'
dept_name VARCHAR(10) UNIQUE ,--部门名称
dept_desc VARCHAR(10),--部门描述
dept_date  VARCHAR(15),--部门成立时间
CONSTRAINT ID_PK PRIMARY KEY (ID),
);
CREATE TABLE staff
(
id INT PRIMARY KEY identity(1,1),--员工主键
NAME VARCHAR(10),--员工
sex VARCHAR(2),--性别
age INT,--年龄
salary INT ,--薪资
dept_id INT--所属部门的主键
);
--插入数据
INSERT INTO department(dept_name,dept_desc,dept_date) VALUES('研发部','开发软件','2014-12-14');
INSERT INTO department(dept_name,dept_desc,dept_date) VALUES('财务部','发工资','2014-10-14');
INSERT INTO department(dept_name,dept_desc,dept_date) VALUES('市场部','销售软件','2015-11-02');
INSERT INTO staff (NAME,sex,age,salary,dept_id)VALUES('王五','男',25,3000,1);
INSERT INTO staff (NAME,sex,age,salary,dept_id)VALUES('李明','男',23,2500,1);
INSERT INTO staff (NAME,sex,age,salary,dept_id)VALUES('王二小','男',23,2356,2);
INSERT INTO staff (NAME,sex,age,salary,dept_id)VALUES('陈发','男',22,3600,2);
INSERT INTO staff (NAME,sex,age,salary,dept_id)VALUES('小明','男',21,3100,3);
INSERT INTO staff (NAME,sex,age,salary,dept_id)VALUES('苏奇','男',24,2800,3);
INSERT INTO staff (NAME,sex,age,salary,dept_id)VALUES('王丽','女',19,1800,1);
INSERT INTO staff (NAME,sex,age,salary,dept_id)VALUES('李芳','女',18,1900,3);
--基本查询
--1.查询所有员工信息，信息内容包括:员工主键，用户名，性别，年龄，工资，所属部门
SELECT 
st.id 员工主键,name as 用户名,sex 性别,age 年龄,salary 工资,dept_id 所属部门,dept_name 部门名称
from staff st,department de
where st.dept_id=de.id;
SELECT 
st.id 员工主键,name as 用户名,sex 性别,age 年龄,salary 工资,dept_id 所属部门,dept_name 部门名称
from staff st
inner join department de
on st.dept_id=de.id;
--2.查询出工资最高的员工，并显示员工信息
select st.* ,de.dept_name ,de.dept_desc,de.dept_date
 from staff st,department de
 where st.salary=(select max(salary) from staff)and st.dept_id=de.id ;
--3.查询出研发部工资最高的员工信息
select top 1 * from staff st,department de 
where  st.dept_id=de.id and de.dept_name='研发部'
order by salary desc ;--没有考虑两行
select * from staff st,department de
where st.salary=
(select  max(st.salary) from staff st,department de 
where  st.dept_id=de.id GROUP BY de.dept_name having de.dept_name='研发部')
and st.dept_id=de.id;
select st.*,de.dept_date,de.dept_desc,de.dept_name from staff st,department de 
 where  salary= (select max(salary) from staff st ,department de
 where dept_id=(select id from department where dept_name='研发部')
 and st.dept_id=de.id)and st.dept_id=de.id  ;
--4.查询所有男性员工和女性员工工资总和
select count(*) 人数 , sum(salary) from staff where sex='男';
select count(*) 人数 ,sum(salary) from staff group by sex having sex='男';
select sex 性别,count(sex) 人数 ,sum(salary) 工资 from staff group by sex ;
--5.查询工资大于平均工资的所有员工信息
select * from staff st,department de where st.salary> (select avg(salary) 工资 from staff)and st.dept_id=de.id ;
select * from staff st join department de on st.salary> 
(select avg(salary) 工资 from staff)where  st.dept_id=de.id ;
select * from staff st join department de on st.salary> 
(select avg(salary) 工资 from staff)and  st.dept_id=de.id ;
--6.查询所有男性员工和女性员工的平均工资
select sex 性别,count(*) 人数 ,avg(salary) from staff group by sex;
--7.查询年龄在18~22岁（包括18岁和22岁）之间的员工的姓名和年龄
select name 姓名,age 年龄 from staff where age between 18 and 22;
select name 姓名,age 年龄 from staff where age in(18,19,20,21,22);
select name 姓名,age 年龄 from staff where age >=18 and age<=22;
--8.查询年龄不在18~22岁（包括18岁和12岁）之间的员工的姓名和年龄
select name 姓名,age 年龄 from staff where age not  between 18 and 22;
select name 姓名,age 年龄 from staff where age not  in(18,19,20,21,22);
select name 姓名,age 年龄 from staff where age <18 or age>22;
--9.查询年龄在22以下的员工的姓名
select name 姓名 from staff where age <22;
--10.查询全体员工的姓名、年龄，要求按照年龄降序
select name 姓名,age 年龄 from staff  order by age desc;
--11.查询年龄最大的前3个员工的姓名和年龄
select top 3 name 姓名,age 年龄 from staff  order by age desc;
--12.查询所有姓王员工的姓名和性别
select name 姓名,sex 性别 from staff ;
--13.查询工资最高的前3个员工信息
SELECT top 3
st.id 员工主键,name as 用户名,sex 性别,age 年龄,salary 工资,dept_id 所属部门,dept_name 部门名称
from staff st,department de
where st.dept_id=de.id order by salary desc;
--14.统计每个部门的工资总和,显示信息：部门名称，工资总和
select de.dept_name 部门名称,sum(salary) from staff st , department de where st.dept_id=de.id  group by de.dept_name ;
--15.统计每个部门的总人数,显示信息：部门名称，部门人数
select de.dept_name 部门名称  ,count(*) 总人数   from staff st , department de where st.dept_id=de.id  group by de.dept_name;