SQL server基础知识(表操作、数据约束、多表链接查询)

时间:2023-03-08 16:06:01
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