【数据库原理及应用】第四章 —— 关系型数据库标准语言SQL_使用模式能防止来自不同模式对象名称的冲突-CSDN博客

本文链接：https://blog.csdn.net/liushi21/article/details/128085384

4.1、SQL概述

结构化查询语言，是关系型数据库管理系统的标准语言，不同数据库软件可能还有自己独特的方言

4.1.1、SQL特点

综合统一
高度非过程化
面向集合的操作方式
以同一种语法结构提供两种使用方式：独立和嵌入。独立使用方式又称为交互式，是指在DBMS提供的查询界面输入SQL语句并执行，DBMS向用户反馈SQL查询结果。嵌入是指在类似C#、Java等编程语言中使用SQL语句

4.1.2、SQL基本概念

4.1.2.1、DBMS

DBMS是一种操纵和管理数据库的大型软件，常见的DBMS产品：SQL Server、MySQL、Oracle

4.1.2.2、方言

SQL方言是不同数据库管理系统在SQL基础上进行的拓展，也是不同管理系统sql语句的主要区别

4.1.2.3、SQL功能组件

标准的有三大功能组件：DML(数据操纵)、DDL(数据定义)、DCL(数据控制)

4.1.3、SQL基本数据类型

数据类型	实例	占用字节数	说明或范围
精确型	money	8	范围是-922337203685477.5808 ~ -922337203685477.5807，精确到所代表的的货币单位的万分之一
（money和smallmoney也是货币类型）	smallmoney	4	存储范围为-214748.3648 ~ 214748.3647
	tinyint	1	0~255
	bigint	8	-2^63 ~ 2^63-1
	int`(常用)`	4	-2^31 ~ 2^31-1
	bit	1	取值: 1、
	decimal（numeric）	由精度确定	压缩严格的定点数。`decimal(p,s)`：其中p是精度（1~38，默认18），表示最多可存储的十进制数字的总位数；s是小数位数）（十进制）
近似型	real	4	-3.4010^38 ~ 1.1810^{(-38)、0、1.1810}(-38)~3.4010^38
	float	取决于n值	`float(n)`
普通字符串类型	text（会删除）		服务器代码页中长度可变的非Unicode数据，字符串最大长度为2^31-1字节
	char	n决定	`char(n)`, 固定长度，n∈[1, 8000]
	varchar	决定	`varchar(n)`, 可变长度， n∈[1, 8000]
Unicode字符类型	nchar（会删除）	n决定	`nchar(n)`, n∈[1, 4000]，，存储固定大小的Unicode字符集，存储大小为2n
	nvarchar	n决定	存储可变大小的Unicode字符集，n∈[1, 4000]
	varchar		长度可变的Unicode数据，字符串最大长度为2^30-1字节，存储大小是所输入字符串长度的两倍
日期时间类型	date	3	0001-01-01~9999-12-31
	time	5	00:00:00.0000000 ~ 23:59:59:9999999
	datetime	8	1753年1月1日 ~ 9999年12月31日
	smalldatetime	4	1900-01-01~2079-06-06
二进制类型	binary	n	`binary(n)`: 存储n字节固定长度的二进制数据，n∈[1, 8000]
	varbinary	n	`varbinary(n)`,可变长度二进制数据，n∈[1, 8000]
	image（会删除）		长度可变的二进制数据

4.2、数据定义

4.2.1、模式的定义与删除

模数据库模式是一种逻辑分组对象，数据库模式是数据库对象的集合，包括了各种对象，比如表、视图、存储过程、索引等。

数据库模式可作为一个命名空间，防止来自不同模式的对象名称冲突

数据库的定义和删除

/*创建数据库*/
create database DBname

/*删除数据库*/
drop database DBname

创建模式语法

create schema 模式名 authorization 用户名

/*说明：创建一个模式，并将这个模式授权给指定的用户进行管理*/
/*模式创建成功后，即可在该模式下创建对象：表、视图等*/

删除模式

drop schema 模式名

实例 - 一个数据库的创建、建表到删库

create database testDB/*创建数据库*/
use testDB            /*进入该数据库*/
go
create login Rory with passwords='test'   /*先创建一个数据库用户*/
go
create user Rory for login Rory          
go
create schema mod_1 authorization Rory   /*再定义模式，并将其授权给创建的数据库用户管理*/

/*自此模式创建成功，下面在该模式下创建表，插入表并查找表*/
create table mod_1.testTable(
    Cno char(12) not null,
    Vname varchar(10) not null
) 
go
insert into mod_1.testTable values('202131066666', 'Doc')
go
select * from mod_1.testTable

/*下面是删库*/
drop table mod_1.testTable  /*先删表*/
go
drop schema mod_1     /*再删模式*/
go
drop database testDB   /*最后删库*/

4.2.2、基本表的定义、删除、修改

4.2.2.1、定义表

/*语法*/
create table<tableName>(
    <列名> <数据类型> [列级完整性约束定义]
        {, <列名> <数据类型> [数据完整性定义]
        ……}
    	[, 表级完整性约束定义]
    ）
)

/*eg1.*/
create table student(
    sno char(7) primary key, /*主码*/
    sname char(10) not null,
    ssex char(2) check (ssex = '男' or ssex = '女')，
    sage tinyint check (sage >= 15 and sage <= 20),
    
    /**
    *primary key(sno)   也可以在最后，这样定义主码
    *primary key(sno, sname)   或者定义一个有多个属性的主码
    **/
)

4.2.2.2、删除表

drop table tableName

4.2.2.3、修改表

语句	作用
`alter table tableName alter column 列名新数据类型`	修改列定义
`alter table tableName add column 列名数据类型属性`	添加新列
`alter table tableName drop column 列名`	删除列
`alter table tableName add primary key(列名[，...n])`	添加主码约束
`alter table tableName add foreign key 列名 references 表名(列名)`	添加外码约束
`alter table tableName drop column 字段名`	删除原有字段
`alter table tableName add 字段名数据类型`	添加新字段
`alter table tableName alter column 字段名数据类型(长度)`	修改数据类型或长度

4.2.2.4、数据库完整性

实体完整性、参照完整性、用户定义完整性、完整性约束命名子句

数据库完整性指数据的正确性和相容性

放置数据库中存在不正确的数据
防范对象：不合语义的

数据的安全性

维护数据库完整性，DBMS所做的：

提供定义完整性约束条件的机制
提供完整性检查的方法
违约处理：实体完整性中，检查主码是否唯一(全表扫描)，主码的各个属性是否非空

完整性控制是DBMS核心支持的功能，从而能够所有的用户和所有的应用提供一致的数据库完整性

4.2.2.4.1、列级完整性可定义的约束

约束	说明
not null	限制列取值非空
default k	设置列默认值为常量k
unique	限制列取值不重复
check	限制列的取值范围，格式：`check(约束表达式)`
primary key	指定本列为主码
foreign key	定义本列为引用其他表的外码，格式：`foreign key 列名 references 外表名/外表列名`

not null 和 default 不能在表级完整性约束处定义

4.3.0、准备

4.3.0.1、给表单取别名

/*格式一: 使用as*/
select * from stu as s1 where exists (select * from student as s2 where s1.major = s2.major and s2.name = 'Rory')

/*格式二: 空格*/
select * from stu s1 where exists (select * from student s2 where s1.major = s2.major and s2.name = 'Rory')

4.3.0.2、给列设置友好列标题

/*使用as, 得到的结果里，对应列名称会被改变为as之后的字段*/
select Sno as 学号, Sname as 姓名 from stu

4.3.0.3、逻辑运算符

逻辑运算符均写在where后面

逻辑运算符	说明
all	一组比较都为true，则结果为true
and	连接的两个布尔表达式都为true，则结果为true
any	其中的一个为true则结果为true
between…and…或between	操作数在某范围内则为true
exists	子查询包含符合要求的行，则为true
in	操作数等于表达式列表中的一个，则为true
like	操作数与一种模式相匹配则为true，注意，like需要配合通配符使用，进行模糊查询
not	对其他运算符取反，写在其他运算符之前
or	连接的两个运算符有一个为true则结果为true
some	一组比较中，有一些为true，则结果为true

4.3.0.3.1、通配符

通配符	描述	实例
`%`	包含任意个字符的字符串	`select * from stu where name like '%泽%'`
`_`	仅为任意单个字符	`select * from stu where name like '李__'`
`[]`	指定范围: `[a-f]`或者集合: `[asdc]`中的任意单个字符	`select * from stu where name like '_R[a-f]%'`
`[^]`	不属于指定范围中的任何单个字符`[^a-f]`、`[^asdasd]`	`select * from stu where name like 'j[^a]%'`

eg.

select * from stu where Sage not between 19 and 22
select * from stu where Sname like '李%'
select * from stu where indentify in ('班长','学委')

4.3.1、单表查询

select columnName1, columnName2, columnName2, ... from tableName where 限制条件
select columnName1, columnName2 group by columnName1 having 限制条件

/*columnName换为*则是全表查询*/
/*where是按行筛选，group by columnName having 限制条件 是按列筛选*/

语法	说明
`select * from tableQName where 限制条件`	根据限制条件查询全表满足条件的行的对应信息，不加where就是查询全表所有信息，相对的，having是按满足条件的列进行筛选，而having前面一定有group by
`select * from tableName order by columnName DESC/ASC`	查询信息并升序或降序排序，ASC是升序，DESC是降序，默认为ASC
`select * from tableName where columnName is NULL/is not NULL`	查询字段为空或不为空的数据段
`select Cno, count(Sno) from SC group by Cno order by count(Sno)`	分组查询示例
`select top n columnName from tableName`	查询前n行列名为columnName的数据

使用distinct关键字还可以去掉表中重复的行：select distinct Sno from SC

4.3.2、多表连接查询

语法	说明
`select table1., table2. from tableName1, tableName2 where table1.columnName = table2.columnName`	多表的等值连接查询
`select table1, table2 from table1, table2 where table1.xxx = table2.xxx and ......`	复合条件连接, where中含多个连接条件，条件用and连接

4.3.3、嵌套查询

语法	说明
`select columnName1 from table1 where columnName2 in (select columnName2 from table2 where columnName3等相关限制条件)`	带有`in`谓词的子查询，同样的也有`not in`，即将一个查询块嵌套在另一个查询块中，外层查询又叫父查询，内层查询又叫子查询
`select * from teacher where sex='男' and age > any(select age from teacher where sex = '女')`	使用`any`、`some`、`all`谓词的子查询，这些谓词是用来修饰引入子查询的比较运算符，其中all表示所有，any、some表示至少满足一个
`select * from stu where exists(select * from sc where Sno = stu.Sno and Cno = '2214')`	使用`exists`谓词的子查询，同样的也有`not exists`，相当于进行存在性测试，外部where子句测试子查询返回的行是否存在，存在返回`TRUE`，否则为`FALSE`