Haijd's Blog

数据库基本增删改查语句总结


创建 create

创建数据库

1
2
3
4
5
6
7
8

create database MyDatabase
on primary
(
    name='MyDatabase_data',
    filename='D:\working software\sql Sever\MSSQL11.MSSQLSERVER\MSSQL\DATA\MyDatabase0301_data.mdf',
    size=5MB,
    filegorwth=1MB
)

创建日志

1
2
3
4
5
6
7

log on
(
    name='MyDatabase_log',
    filename='D:\working software\sql Sever\MSSQL11.MSSQLSERVER\MSSQL\DATA\MyDatabase0301_data_log.ldf',
    size=1MB,
    filegorwth=10%
)

使用某个数据库

1

use MyDatabase

创建表

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

carate table Student
(
    StuId int identity(1,1) primary key, --主键，自增从1开始每次增加1
    StuName nvarchar(10),
    StuGender char(2),
    StuAge int,
    StuChinese int,
    StuMath int,
    StuEnglish int
)

增(三种方式) inster into

  • 第一种
1

inster into Student(StuName.StuGender,StuAge) values('Microsoft','gay',30,0,0,0)
  • 第二种
1

inster into Student values('Microsoft','gay',30,0,0,0)
  • 第三种
1
2
3
4
5

inster into Student(StuName,StuGender,StuAge)
select 'Microsoft','gay',30,0,0,0 union
select 'Google','gay',20,10,10,10 union
select 'FaceBook','gay',11,20,20,20 union
select 'Twitter','gay',13,30,30,30

删(三种方式)

  • 第一种，数据表一起删除掉
1

drop table Student
  • 第二种，只删除数据，主键不重置为0，可以加后缀(where子句)
1

delete from Student
  • 第三种，只删除数据，主键重置为0
1

truncate from Student

改 update

1

update Student set StuAge=20 where StuId=2

添加约束 alter

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

--主键约束
alter table Student add constraint PK_Student_StuId primary key(StuId)
--唯一约束
alter table Student add constraint UQ_Student_StuName unique (StuName)
--默认约束
alter table Student add constraint DF_Student_StuGender default 'gay' for StuGender
--检查约束
alter table Student add constraint CK_Student_StuAge check(StuAge>0 add StuAge<150)
--外键约束(前外键，后主键)
alter table Student add constraint FK_Student_StuClass foreign key (StuClass) references tClass(classId)
1
2
3
4
5
6

--手动删除一列
alter table Student drop column StuGender
--手动增加一列
alter table Student add StuIsGay nvarchar(2) not null
--手动修改一列数据类型
alter table Student alter column StuGender nchar(2)

查 select

  • 查询列表中所有的数据
1

select * from Student
  • 查询指定列
1

select StuName,StuGender,StuAge from Student
  • 给查询出的结果以别名显示(四种方式)
1
2
3
4

select StuName as '姓名',StuGender as '性别',StuAge as '年龄' from Student
select StuName as 姓名，StuGender as 性别,StuAge as 年龄 from Student
select StuName 姓名，StuGender 性别,StuAge 年龄 from Student
select 姓名=StuName,性别=StuGender,年龄=StuAge from Student
  • 前N条数据 top order by
1
2
3

select top 5 * from Student order by StuAge (asc)  --从小到大
select top 5 * from Student order by StuAge (desc)  --从大到小
select top 5 percent * from Student order by StuAge (desc)  --百分比
  • 去除重复
1

select distinct StuName from Student
  • 类型相同时连接
1

select StuName+StuAge+StuGender from Student
  • 聚合函数
1
2
3
4
5

select sum(StuChinese) as 语文总分 from Student
select avg(StuMath) as 数学平均分 from Student
select max(StuEnglish) as 英语最高分 from Student 
select min(StuEnglish) as 英语最低分 from Student
select count(*) from Student   --查询表中总共多少数据
  • between and双闭区间
1
2

select * from Student where StuAge between 20 and 30  --20岁到30岁之间的数据
select * from Student where StuAge>=20 and StuAge<=30
  • in 规定多个值
1

select * from Student where StuName in('Microsoft','Google')
  • 模糊查询
1
2
3
4
5

select * from Student where StuName like '东%' --%号替代一个或多个字符
select * from Student where StuName like '东_' --_符号代替一个字符
select * from Student where StuName like '东[a-z]' --限制范围
select * from Student where StuName like '东[^a-z]' --取非，排除该范围
select * from Student where StuName like '东[_%]'  --取消转义
  • 分组 group by
1

select StuId as 学生号,count(*) as 学生总数 from Student group by StuId
  • 分组后筛选 having
1

select StuID as 学生号,count(*) as 学生总数 from Student group by StuId having(count(*)>2)  --筛选人数大于2的数据
  • 时间函数
1
2
3
4

select GETDATE()   --获得当前时间
select DATEADD(dd,-90,GETDATE())  --可以在源日期值上追加指定时间间隔的日期数
select DATEDIFF(dd,'2015-03-25',GETDATE()) as 过了几天  --计算两个日期之间的间隔
select DATENAME(dw,GETDATE())   --获取日期的指定格式的字符串表现形式

  • 联合结果集

    • union去除重复

      1
2
3
4
5

      select StuId,StuName from Student
union
select BanGanBuId.BanGanBuName from BanGanBu
union
select XueShengHuiId,XueShengHuiName from XueShengHui

    • union all不去除重复

      1
2
3
4
5

      select StuId,StuName from Student
union all
select BanGanBuId.BanGanBuName from BanGanBu
union all
select XueShengHuiId,XueShengHuiName from XueShengHui

  • 字符串函数

    1
2
3
4

    select LEN('每天都要吃饭')   --获取字符串长度或字符个数
select DATALENGTH('今天天气真好')   --获取字符串字节数
select LOWER('MICROSOFT')   --大写转小写
select UPPER('microsoft')   --小写转大写

    • 去掉空格

      1
2
3

      select '我是'+LTRIM('  大侠  ')   --去掉左边空格
select '我是'+RTRIM('  小菜  ')   --去掉右边的空格
select '我是'+LTRIM(RTRIM('  我  '))   --去掉两边空格

    • 截取

      1
2
3

      select LEFT('我是好人啊',1)   --从字符串左边开始截取1个
select RIGHT('我是好人啊',1)   --从字符串右边开始截取1个
select SUBSTRING('我是好人啊',1,4)  --从第一个字符开始截取，截取4个

SQL语句的执行顺序

  1. from 表
  2. where 条件
  3. group by 列
  4. having 筛选条件
  5. select 5.1 选择列 5.2 distinct 5.3 top(应用top选项最后计算)
  6. order by 排序

select语句处理顺序

  1. FROM
  2. ON
  3. JOIN
  4. WHERE
  5. GROUP BY
  6. WITH CUBE 或 WITH ROLLUP
  7. HAVING
  8. SELECT
  9. DISTINCT
  10. RODER BY
  11. TOP

数据类型

  • char：汉字1，字母2，多余空间会用空格补全
  • varchar：汉字1，字母2
  • nchar：(n表示Unicode)汉字1，字母1，多余空间会用空格补全
  • nvarchar：(n表示Unicode)汉字1，字母1

null的特殊处理(null作为“不知道”处理)

1
2
3

select * from Student where StuMath is null  
select * from Student where StuMath is not null
select null+1   --返回null