常用SQL语句

来源：互联网发布：河北省人工智能学会编辑：程序博客网时间：2024/05/22 04:37

创建数据库
create database MyDb
on
(name='MyDb_Data',FileName='E:\Temp\MyDb.mdf')
log on
(name='MyDb_Log',FileName='E:\Temp\MyDb.mdf')

删除数据库
drop database MyDb

创建表
use MyDb //通过use命令先连接到数据库
create table Department
(
    DepartmentId int primary key,
    DepartmentName nvarchar(20) not null //not null指示不能为空
)
create table Employee
(
    EmployeeId nchar(10) primary key,
    EmployeeName nvarchar(20) not null,
    DateOfBirth datetime,
    DepartmentId int,
    Salary float,
    Sex nchar(1)
)

添加数据
insert into Department (DepartmentId,Departmentname)
values (1,'开发科')
insert into Department (DepartmentId,Departmentname)
values (2,'销售科')
insert into Department (DepartmentId,Departmentname)
values (3,'财务科')

insert into Employee(EmployeeId,Employeename,DateOfBirth,DepartmentId,
Salary,Sex)
values('E001','火狐','1989-10-6',1,9000,'男')
insert into Employee(EmployeeId,Employeename,DateOfBirth,DepartmentId,
Salary,Sex)
values('E002','路人','1985-10-6',2,6000,'女')

更新数据
update Employee
set Salary=Salary+500,EmployeeName='张四' //可以修改多个
where EmployeeId='E003'

删除数据
delete from Department //如果不写条件则全部删除
where DepartmentId=10

查询数据
select * from Employee //查询Employee表中的所有信息
where Salary>=6000 //查询Employee表中Salary>=6000 的信息

select top 2 * from Employee //查询Employee表中的所有信息的前两条

聚合函数
AVG 返回一组数据的平均值，NULL将被忽略
COUNT 返回一组数据的项数，始终返回int型值
MAX 返回一组数据中的最大值，NULL将被忽略
MIN 返回一组数据中的最小值，NULL将被忽略
SUM 返回一组数据中的数据的和，NULL将被忽略

select avg(Salary) from Employee //查询所有的平均工资
select avg(Salary) from Employee whereDepartmentId=1 //只查询开发科的平均工资

select count(*) from Employee where sex='男' //查询男性的数量

select max(salary) as 最高工资, min(salary) as 最低工资
from Employee

select sum(salary) from Employee

未完待续！

0 0