【SQL】Sql Server SQL语句学习

墨蓝 2022-02-26 19:38 638阅读 0赞

### 文章目录 ###

*   *   *  1. 什么是SQL
         *  2. 关系模型
         *   *  2.1 主键
             *  2.2 外键
         *  2.3 索引
         *  3. DDL操作
         *   *  3.1 对库操作
             *  3.2 对表操作
             *   *  3.2.1 创建和删除表
                 *  3.2.2 添加约束
                 *  3.2.3 对列进行操作
                 *  3.2.4 分页查询
                 *  3.2.5 连接查询

### 1. 什么是SQL ###

什么是SQL(Structured Query Language)？  
SQL是结构化查询语言的缩写，用来访问和操作数据库系统。SQL语句既可以查询数据库中的数据，也可以添加、更新和删除数据库中的数据，还可以对数据库进行管理和维护操作。不同的数据库，都支持SQL，这样，我们通过学习SQL这一种语言，就可以操作各种不同的数据库。

虽然SQL已经被ANSI组织定义为标准，不幸地是，各个不同的数据库对标准的SQL支持不太一致。并且，大部分数据库都在标准的SQL上做了扩展。也就是说，如果只使用标准SQL，理论上所有数据库都可以支持，但如果使用某个特定数据库的扩展SQL，换一个数据库就不能执行了。例如，Oracle把自己扩展的SQL称为PL/SQL，Microsoft把自己扩展的SQL称为T-SQL。

现实情况是，如果我们只使用标准SQL的核心功能，那么所有数据库通常都可以执行。不常用的SQL功能，不同的数据库支持的程度都不一样。而各个数据库支持的各自扩展的功能，通常我们把它们称之为“方言”。

总的来说，SQL语言定义了这么几种操作数据库的能力：

**DDL：Data Definition Language**

DDL允许用户定义数据，也就是创建表、删除表、修改表结构这些操作。通常，DDL由数据库管理员执行。

**DML：Data Manipulation Language**

DML为用户提供添加、删除、更新数据的能力，这些是应用程序对数据库的日常操作。

**DQL：Data Query Language**

DQL允许用户查询数据，这也是通常最频繁的数据库日常操作。

### 2. 关系模型 ###

#### 2.1 主键 ####

主键大家都懂，还有一个东西叫做联合主键，允许通过多个字段唯一标识记录，即两个或更多的字段都设置为主键，这种主键被称为联合主键。

对于联合主键，允许一列有重复，只要不是所有主键列都重复即可：  
例如我们可以把student类中的id和name都设置为主键，语法如下：

ALERT TABLE student
    ADD CONSTRAINT PK_ID PK_NAME PRIMARY KEY (id,name)

注意：SQL Server这个表中之前如果已经有主键了，是不能够执行上面的语句的，只有将之前的主键取消并且保存之后才可以成功执行。

没有必要的情况下，我们尽量不使用联合主键，因为它给关系表带来了复杂度的上升。

#### 2.2 外键 ####

就是如果一个 student 表中存在一个字段 class\_id , 是与 class 表中的 class\_id 字段含义相同并且关联起来的，那么通过 class\_id 的字段，可以把数据与另一张表关联起来，这种列称为外键 。  
外键并不是通过列名实现的，而是通过定义外键约束实现的：

ALTER TABLE students
    ADD CONSTRAINT fk_class_id
    FOREIGN KEY (class_id)
    REFERENCES classes (id);

外键约束的名称fk\_class\_id可以任意，FOREIGN KEY (class\_id)指定了class\_id作为外键，REFERENCES classes (id)指定了这个外键将关联到classes表的id列（即classes表的主键）,引用的列必须是主键约束或者是唯一约束。  
通过定义外键约束，关系数据库可以保证无法插入无效的数据。即如果classes表不存在id=99的记录，students表就无法插入class\_id=99的记录。

由于外键约束会降低数据库的性能，大部分互联网应用程序为了追求速度，并不设置外键约束，而是仅靠应用程序自身来保证逻辑的正确性。这种情况下，class\_id仅仅是一个普通的列，只是它起到了外键的作用而已。

要删除一个外键约束，也是通过ALTER TABLE实现的：

ALTER TABLE students
    DROP FOREIGN KEY fk_class_id;

注意：删除外键约束并没有删除外键这一列。删除列是通过`DROP COLUMN ...`实现的。

### 2.3 索引 ###

在关系数据库中，如果有上万甚至上亿条记录，在查找记录的时候，想要获得非常快的速度，就需要使用索引。

索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度。

为列创造索引：

ALTER TABLE table_name
    ADD INDEX index_name(column_name1,column_name2);

索引的效率取决于索引列的值是否散列，即该列的值如果越互不相同，那么索引效率越高。反过来，如果记录的列存在大量相同的值，例如gender列，大约一半的记录值是M，另一半是F，因此，对该列创建索引就没有意义。

可以对一张表创建多个索引。索引的优点是提高了查询效率，缺点是在插入、更新和删除记录时，需要同时修改索引，因此，索引越多，插入、更新和删除记录的速度就越慢。

对于主键，关系数据库会自动对其创建主键索引。使用主键索引的效率是最高的，因为主键会保证绝对唯一。

**唯一索引（唯一约束）**

有些列根据业务要求，具有唯一性约束：例如不能出现两条记录存储了同一个身份证号。这个时候，就可以给该列添加一个唯一索引。例如，我们假设students表的name不能重复：

ALTER TABLE students
    ADD UNIQUE INDEX uni_name (name);

通过UNIQUE关键字我们就添加了一个唯一索引。

也可以只对某一列添加一个唯一约束而不创建唯一索引：

ALTER TABLE students
    ADD CONSTRAINT uni_name UNIQUE (name);

这种情况下，name列没有索引，但仍然具有唯一性保证。

无论是否创建索引，对于用户和应用程序来说，使用关系数据库不会有任何区别。这里的意思是说，当我们在数据库中查询时，如果有相应的索引可用，数据库系统就会自动使用索引来提高查询效率，如果没有索引，查询也能正常执行，只是速度会变慢。因此，索引可以在使用数据库的过程中逐步优化。

### 3. DDL操作 ###

#### 3.1 对库操作 ####

添加数据库：

CREATE DATABASE kaishui

删除数据库：

DROP DATABASE kaishui

备份SQLserver

--创建备份数据库的device
    USE master
    EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'
    --- 开始 备份
    BACKUP DATABASE pubs TO testBack

#### 3.2 对表操作 ####

##### 3.2.1 创建和删除表 #####

创建新表

CREATE TABLE student(
    -- IDENTITY(1,1)表示从1开始，每次自增1
    id INT NOT NULL IDENTITY(1,1)  PRIMARY KEY,
    student_name VARCHAR(20) DEFAULT NULL,
    Linux INT DEFAULT 0,
    MYSql INT DEFAULT 0,
    Java INT DEFAULT 0,
    class_name VARCHAR(20) DEFAULT NULL
    );

往表中添加数据

INSERT INTO student (student_name,Linux,MYSql,Java,class_name) VALUES ('陈二',70,90,70,'17')

删除一个表

DROP TABLE student1

##### 3.2.2 添加约束 #####

为表里的一列添加主键约束和唯一约束在上面已经写过，这里写为一列添加检查约束。  
例如我的 student 表中有 age 字段，我添加的学生中的年龄都不能超过30，那么语句如下：

ALERT TABLE student
    ADD CONSTRAINT Check_age CHECK (age<30)

为表里的一列添加默认约束，例如我的 student 表中 gender 字段默认为男

ALERT TABLE student
    ADD CONSTRAINT Default_sex DEFAULT '男' FOR gender

##### 3.2.3 对列进行操作 #####

添加一列：

ALTER TABLE student
    ADD score varchar(20)

修改列的数据类型

ALTER TABLE student
    ALTER COLUMN name VARCHAR(20)

删除列

ALTER TABLE student
    DROP COLUMN name

修改列名

EXEC SP_RENAME '表名.列名','age1'

修改表名

EXEC SP_RENAME 'student1','stu'

##### 3.2.4 分页查询 #####

使用SELECT查询时，如果结果集数据量很大，比如几万行数据，放在一个页面显示的话数据量太大，不如分页显示，每次显示100条。  
例如现在，我们把结果集分页，每页5条记录。要获取第1页的记录，可以使用：

SELECT TOP 5 * FROM (SELECT ROW_NUMBER() OVER ( ORDER BY  id ) AS RowNumber,
    * from ( select*  from student) as x )  as z  WHERE z.RowNumber >=1 and z.RowNumber<=5

如果要第二页第三页，就在后面的RowNumber 修改就可。

##### 3.2.5 连接查询 #####

内连接：

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
    FROM students s
    INNER JOIN classes c
    ON s.class_id = c.id;

外连接：

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
    FROM students s
    RIGHT OUTER JOIN classes c
    ON s.class_id = c.id;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0NjU5Nzc3_size_16_color_FFFFFF_t_70]

最后总结一下：  
如果你对此文有任何疑问，如果你也需要接口项目实战，如果你对软件测试、接口测试、自动化测试、面试经验交流感兴趣欢迎加入：软件测试技术群：**593462778**，群里的免费资料都是笔者十多年测试生涯的精华。还有同行大神一起交流技术哦。

作者：暗潮汹涌  
原创不易，欢迎转载，但未经作者同意请保留此段声明，并在文章页面明显位置给出原文链接。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0NjU5Nzc3_size_16_color_FFFFFF_t_70]: /images/20220226/7a682b5768324d3895a521d0ec6324f6.png