SQL server基础知识(表操作、数据约束、多表链接查询)
SQL server基础知识
一、基础知识
(1)、存储结构:数据库->表->数据
(2)、管理数据库
增加:create database 数据库名称
删除:drop database 数据库名称
查询:select name from master..sysdatabases
修改:alter database 数据库名称(在 sql server 中修改与数据库关联的文件和文件组。在数据库中添加或删除文件和文件组、更改数据库或其文件和文件组的属性)
(3)、管理表
选择数据库:use 数据库
增加:create table 表名(字段1 字段类型,字段2 字段类型,......);
删除:drop table 表名
修改:
添加字段:alter table 表名 add 字段名 字段类型
删除字段:alter table 表名 drop column 字段名
修改字段类型:alter table 表名 alter column 字段名 字段类型
修改字段名称:exec sp_rename '表.原字段名称','新字段名称','column'
修改表的名称:exec sp_rename '原表名称','新表名称'
查询:select name from sysobjects where xtype = 'u'
(4)、管理数据
增加数据:insert into 表(字段1,字段2,...) values (值1,值2,...)
删除数据:delete from 表名 where 条件
修改数据:update 表名 set 字段1=值1,字段2=值2,... where 条件
查询数据:
所有字段:select * from 表
指定字段:select 字段1,字段2,... from 表
指定别名:select 字段1 as 别名1,字段2 as 别名1,... from 表
去重:select distinct 字段 from 表
条件查询:
逻辑条件:and(与)or(或)
select * from 表 where ... and/or ...
比较条件:>,<,>=,<=,=,!=,<>(不等于),between and
判空条件:
判断null:is null/is not null
判断空字符串:='',!='',<>''
模糊条件:like
'%':替换任意个字符
'_':替换一个字符
select * from 表 where 字段 like '%查询字符%'
分页查询:
@p_pagesize int,//页面数量
@p_currentPage int//当前页
declare @string varchar(3000)
if(@p_currentPage=1)//第一页
begin
set @string = 'Select top '+ cast(@p_pagesize as char(20)) + '* from 表名 where 条件
end
else//不是第一页
begin
set @string = 'Select top '+cast(@p_pagesize as varchar(20))+'* from 表名where 条件and 表.字段 not in (select top '+ cast(((@p_currentPage-1)*@p_pagesize) as varchar(20))+'字段 from 表名where 条件
end
exec(@string);
排序查询:order by asc/desc
asc:正序;顺序
desc:反序;倒序
分组查询:group by 字段
分组后筛选:having
二、数据约束
数据约束:对用户表操作进行约束
(1)默认值:
作用:当用户对使用默认值的字段不插入值的时候,就会使用默认值
注意:
(a)对于默认值字段插入null是可以的
(b)对于默认值字段可以插入非null
创建:
(a)建表:
create table temp_table
(id int,
name varchar(10),
address varchar(50) default '吉林长春')
(b)语句:
alert table 表名 add constraint 默认名字(任意) default('默认值') for 字段
(2)非空:
作用:限制字段,必须赋值
注意:
(a)非空字段必须赋值
(b)非空字段不能赋null
创建:
(a)建表:
create table temp_table
(id int not null,
name varchar(10),
address varchar(50) default '吉林长春')
(b)语句:
alert table 表 alert column 字段名称 字段类型 not null
(3)唯一:
唯一键分为主键(主键索引)和唯一索引(unique索引)
作用:对该字段的值不能重复
注意:
(a)唯一字段可以插入null
(b)唯一字段只能插入一个null
创建:
(a)建表:
create table student
(id int unique,
name varchar(10))
(b)语句
create unique clustered index 约束名 on 表名(字段名)
(4)主键:
作用:非空,唯一
注意:
(a)通常情况下,每张表都会设置一个主键字段。用于标记表中的每条记录的唯一性。
(b)建议不要选择表的包含业务含义的字段作为主键,建议给每张表独立设计一个非业务含义的id字段。
创建:
(a)建表:
create table student
(id int primary key,--主键
name varchar(10))
(b)语句:
第一:先设置字段为null: alter table 表名 alter column 字段名 字段类型 not null
第二:设置主键:alter table 表名 alter constrant 主键名(任意) promary key(主键字段)
(5)自增长
作用:自动递增
注意:一张表中不能有两个或者多个标识列
创建:
(a)建表:
create table student
(id int identity(1,1) primary key,--主键,自增长,从1开始,每次增加1
name varchar(10))
(b)语句:
alter table 表名 add 列名 int identity(1,1) NOT NULL--增加一列自增
(6)主键:
作用:约束两张表的数据
出现两种表的情况:解决数据冗余高问题, 独立出一张表。
注意:
(a)被约束的表称为副表,约束别人的表称为主表,外键设置在副表上的!!!
(b)主表的参考字段通用为主键!
(c)添加数据: 先添加主表,再添加副表
(d)修改数据: 先修改副表,再修改主表
(e)删除数据: 先删除副表,再删除主表
创建:
(a)建表:
-- 部门表(主表)
CREATE TABLE dept(
id INT PRIMARY KEY,
deptName VARCHAR(20)
)
-- 修改员工表
CREATE TABLE employee(
id INT PRIMARY KEY,
empName VARCHAR(20),
deptId INT,-- 把部门名称改为部门ID,
-- 声明一个外键约束
CONSTRAINT 外键名称 FOREIGN KEY(外键) REFERENCES 主键表(主键id)
)
(b)语句:
alter table 表 add foreign key(表外键) references主键表(主键id)
三、多表链接查询
(1)外链接
外连接分为:左链接、右链接、完全外链接
(a)左链接:left join 或 left outer join
用法:最少两张表,A表和B表
Select A.字段 from A left join B on A.id = B.id
左外连接包含left join左表所有行,如果左表中某行在右表没有匹配,则结果中对应行右表的部分全部为空(NULL).
(b)右链接 right join 或right outer join
用法:最少两张表,A表和B表
Select A.字段 from A right join B on A.id = B.id
右外连接包含right join右表所有行,如果左表中某行在右表没有匹配,则结果中对应左表的部分全部为空(NULL)。
(c)完全外连接 full join 或 full outer join
用法:最少两张表,A表和B表
Select A.字段 from A full join B on A.id = B.id
完全外连接包含full join左右两表中所有的行
如果右表中某行在左表中没有匹配,则结果中对应行右表的部分全部为空(NULL)
如果左表中某行在右表中没有匹配,则结果中对应行左表的部分全部为空(NULL)。
(2)内连接
内连接 join 或 inner join
用法:最少两张表,A表和B表
Select A.字段 from A full join B on A.id = B.id
inner join 是比较运算符,只返回符合条件的行。
此时相当于:select * from A,B where A.ID=B.ID