SQL Server数据库管理系统中的数据库文件是由数据文件和日志文件组成的,数据文件以盘区为单位存储在存储器中。
一个数据库可以有一个或多个数据库文件,一个数据库文件只能属于一个数据库。 一个数据库只能有一个主数据库文件。 主数据文件是数据库的起点,指向数据库文件的其他部分,每个数据库都有一个主要数据文件,其扩展名为.mdf。 次数据文件包含除主数据文件外的所有数据文件,一个数据库可以没有次数据文件,也可能有多个次数据文件,扩展名为.ndf。
SQL Server 2016不强制使用.mdf、.ndf、.ldf作为文件的扩展名,但建议使用这些扩展名帮助标识文件的用途。SQL Server 2016中某个数据库中的所有文件的位置都记录在master数据库和该数制库的主数据文件中。
master是SQL Server 2016中最重要的数据库,是整个数据库服务器的核心。该数据库中包含下面一些内容:所有用户的登录信息、用户所在的组、所有系统的配置选项、服务器中本地数据库的名称和信息、SQL Server的初始化方式等。作为一个数据库管理员,应该定期备份master数据库。
model数据库是SQL Server 2016中创建数据库的模板,如果用户希望创建的数据库有相同的初始文件大小,则可以在model数据库中保存文件大小的信息;希望所有的数据库中都有一个相同的数据表,同样可以将数据表保存在model数据库中。因为将来创建的数据库以model数据库中的数据为模板,因此在修改model数据库之前要考虑到,任何对model数据库的数据的修改都将影响所有使用模板创建的数据库。
msdb提供运行SQL Server Agent工作的信息。SQL Server Agent是SQL Server中的一个Windows服务,该服务用来运行制定的计划任务。
tempdb是SQL Server中的一个临时数据库,用于存放临时对象或中间结果,SQL Server关闭后,该数据库中的内容被清空,每次重新启动服务器之后,tempdb数据库将被重置。
创建一个数据库sample_db,该数据库的主数据文件逻辑名为sample_db,物理文件名称为sample.mdf,初始大小为5MB,最大尺寸为30MB,增长速度为5%,数据库日志文件的逻辑名称为sample_log,保存日志的物理文件名称为sample.ldf,初始大小为1MB,最大尺寸为8MB,增长速度为128KB。具体操作步骤如下。
CREATE DATABASE [sample_db] ON PRIMARY ( NAME = 'sample_db', FILENAME = 'C:\SQL Server 2016\sample.mdf', SIZE = 5120KB, MAXSIZE = 30MB, FILEGROWTH = 5%, ) LOG ON ( NAME = 'sample_log', FILENAME = 'C:\SQL Server 2016\sample_log.ldf', SIZE = 1024KB, MAXSIZE = 8192KB, FILEGROWTH = 10%, ) GO
ALTER DATABASE sample_db MODIFY FILE ( NAME = sample_db, SIZE = 15MB ); GO
增加数据库容量
ALTER DATABASE sample_db MODIFY FILE ( NAME = sample_db, MAXSIZE = 50MB ); GO
缩减数据库容量
ALTER DATABASE sample_db MODIFY FILE ( NAME = sample_db, MAXSIZE = 25MB ); GO
SQL Server中可以使用多种方式查看数据库信息,例如使用目录视图、函数、存储过程等。
- 使用sys.filegroups查看有关数据库组的信息。
- 使用sys.master_files查看数据库文件的基本信息和状态信息。
- 使用sys.databases数据库和文件目录视图查看有关数据库的基本信息。
USE test GO SELECT DATABASEPROPERTYEX('test', 'Status') AS 'test数据库状态'
使用T-SQL语句修改数据库名称
ALTER DATABASE sample_db2 MODIFY NAME = sample_db; GO
使用T-SQL语句删除数据库
DROP DATABASE test;
数据库对象是数据库的组成部分,数据表、视图、索引、存储过程以及触发器等都是数据库对象。
一. 系统数据类型
- bigint,每个bigint存储在8字节中。
- int,每个int存储在4字节中,其中一个二进制位表示符号,其他31个二进制从头位表示长度和大小。
- smallint,每个smallint类型的数据占用了两个字节的存储空间,其中一个二进制位表示整数值的正负号,其他15个二进制位表示长度和大小,可以表示-2的15次方~2的15次-1范围内的所有整数。
- tinyint,每个tinyint类型的数据占用了一个字节的存储空间,可以表示0~255范围内的所有整数。
- real,占用4个字节的存储空间
- float[(n)],其中n为存储float数值尾数的位数(以科学记数法表示)。
- decimal[(p[,s])]和numeric[(p[,s])],带固定精度和小数位数的数值数据类型。p(精度)指定了最多可以存储的十进制数字的总位数,包括小数点左边和右边的位数。s(小数位数)指定小数点右边柳叶眉主存储的填制数字的最大位数。
- char(n),每个字符和符号占用一个字节的存储空间。n表示所有字符所占用的存储空间,n的取值为1~8000,默认为1。
- varchar(n|max),n为存储字符的最大长度,取值范围为1~8000,但可以根据实际存储的字符数改变存储空间
- nchar(n),n个字符的固定长度的Unicode字符数据。n值必须有1到4000之间(含)。
- date,数据格式为:YYYY-MM-DD
- time,数据格式为:hh:mm:ss[.nnnnnnn]
- datetime,默认值为1900-01-01 00:00:00
- datetime2,datetime类型的扩展,其数据范围更大,默认的小数精度更高。
- smalldatetime,与datetime类型相似,只是其取值的满园是从1900-01-01到2079-06-06
- datetimeoffset,用于定义一个采用24小时制与日期相结合并可识别时区的一日内时间。默认格式是:YYYY-MM-DD hh:mm:ss[.nnnnnnn][{+|-}hh:mm]
- text
- ntext
- image
- money
- smallmoney
位数据类型
bit称为位数据类型,只取0或1为值,长度1字节。
二进制数据类型
- binary(n),长度为n字节的固定长度二进制数据,其中n是1~8000值。存储大小为n字节。
- varbinary(n|max),可变长度二进制数据。
- rowversion,每个数据库都有一个计数器,当对数据库中包含rowversion列的表执行插入或更新操作时,该计数器值就会增加。
- timestamp,时间戳数据类型
- uniqueidentifier,16字节GUID(Globally Unique Identifier,全球唯一标识符),是SQL Server根据网络适配器地址和主机CPU时钟产生的唯一号码。
- cursor,游标数据类型
- sql_variant,用于存储除文本、图形数据和timestamp数据外的其他任何合法的SQL Server数据,可以方便SQL Server的开发工作。
- table,用于存储对表或视图处理后的结果集
- xml,存储xml数据的数据类型。
二. 自定义数据定义
SQL Server允许用户自定义数据类型,用户自定义数据类型是建立在SQL Server系统数据类型基础上的,自定义的数据类型使得数据库开发人员能够根据需要定义符合自己开发需求的数据类型。
CREATE TABLE authors ( auth_id int PRIMARY KEY, --数据表主键 auth_name VARCHAR(20) NOT NULL unique, --作者名称,不能为空 auth_gender tinyint NOT NULL DEFAULT(1) --作者性别:男(1),女(0) );
ALTER TABLE authors ADD auth_note VARCHAR(100) NULL
ALTER TABLE authors ALTER COLUMN auth_phone VARCHAR(15) GO
ALTER TALBE authors DROP COLUMN auth_phone
USE test GO DROP TABLE authors
可以将T-SQL语句分为4大类,分别为数据操作语句、数据定义语句、数据控制语句和一些附加的语言元素。
数据操作语句:
SELECT, INSERT, DELETE, UPDATE
数据定义语句:
CREATE TABLE, DROP TABLE, ALTER TABLE, CREATE VIEW, DROP VIEW, CREATE INDEX, DROP INDEX, CREATE PROCEDURE, ALTER PROCEDURE, CREATE TRIGGER, ALTER TRIGGER, DROP TRIGGER
数据控制语句:
GRANT, DENY, REVOKE
附加的语言元素:
BEGIN TRANSACTION/COMMIT, ROLLBACK, SET TRANSACTION, DECLARE OPEN, FETCH, CLOSE,EXECUTE
所有对数据库对象名的Transact-SQL引用将由4部分名称组成,格式如下:
server_name.[database_name].[schema_name].object_name
| database_name.[schema_name].[object_name]
| schema_name.object_name
| object_name
SQL Server的所有对象,包括服务器、数据库及数据对象,如表、视图、列、索引、触发器、存储过程、规则、默认值和约束等都可以有一个标识符,对绝大多数对象来说,标识符是必不可少的。 SQL Server一共定义了两种类型的标识符:规则标识符和界定标识符。
规则标识符:规则标识符严格遵守标识符有关的规定
界定标识符:界定标识符是那些使用了如[]和' '等界定符号来进行位置限定的标识符。
引用全局变量时,必须以标记符"@@"开头
局部变量被引用时要在其名称前加上标志”@“,而且必须先用DECLARE命令声明才可以使用。定义局部变量的语法形式如下: DECLARE {@local-variable data-type} [...n]
例如:
DECLARE @MyCount INT, @Name varchar(30);
如果想要设置局部变量的值,必须使用SELECT命令或SET命令。例如:
SELECT @MyCount = 100;
+ - * / %
= > < >= <= <> != !> !<
ALL AND ANY BETWEEN EXISTS IN LIKE NOT OR SOME
& | ^ ~
% 匹配任意长度的字符,甚至包括零字符
_ 匹配任意单个字符
[字符集合] 匹配字符集中的任何一个字符
[^]或[!] 匹配不在括号中的任何字符
CREATE DATABASE test_db ON PRIMARY ( NAME = test_db_data1, --数据库逻辑名称 FILENAME='C:\SQL Server 2016\test_db_data.mdf', --主数据文件存储位置 SIZE = 5120KB, --主数据文件大小 MAXSIZE = 20, --主数据文件最大增长空间为20MB FILEGROWTH = 1 --文件增长大小设置为1MB );
USE test_db CREATE TABLE tb_emp1 ( id INT PRIMARY KEY, name VARCHAR(25) NOT NULL, deptId VARCHAR(2) NOT NULL, salary SMALLMONEY NULL );
USE test_db GO DROP TABLE dbo.table_emp
DROP DATABASE test_db
ALTER DATABASE test_db MODIRY NAME = company
USE company GO ALTER TABLE tb_emp1 ADD birth DATE NOT NULL
删除字段列
USE company GO ALTER TABLE tb_emp1 DROP COLUMN birth
INSERT INTO teacher VALUES(1, '张三', '1978-02-14', '男', '0018611');
向teacher插入多条新记录
INSERT INTO teacher VALUES(2, '李四', '1978-11-21', '女', '0018624'), (3, '王五', '1976-12-05', '男', '0018678'), (4, '赵纤', '1980-6-5', '女', '0018699')
UPDATE teacher SET birthday = '1980-8-8', cellphon='0018600' WHERE id=1;
UPDATE teacher SET cellphone='01008611';
DELETE FROM teacher WHERE id=1;
DELETE FROM teacher;
SELECT * FROM stu_info;
SELECT s_name, s_score FROM stu_info;
SELECT TOP 3 * FROM stu_info;
SELECT * FROM stu_info WHERE s_sex='男';
SELECT * FROM stu_info WHERE s_sex='男' AND s_score > 80;
SELECT * FROM stu_info WHERE s_score>80 OR s_age >18;
SELECT * FROM stu_info s_name LIKE '马%'; SELECT * FROM stu_info s_name LIKE '[张王李]%';
SELECT * FROM stu_info WHERE s_score BETWEEN 50 AND 90;
SELECT * FROM stu_info ORDER BY s_score DESC;
GRANT UPDATE, DELETE, ON stu_info TO guest WITH GRANT OPTION
DENY UPDATE ON stu_info TO guest CASCADE;
5.3.3 收回权限操作——REVOKE
REVOKE DELETE ON stu_info FROM guest;
第6章 认识函数
首先,打开SSMS并连接到SQL Server服务器。单击SSMS窗口左上部分的[新建查询]按钮,或者选择[文件] | [新建] | [使用当前连接查询]命令,打开新的[查询]窗口。
SELECT * FROM fruits;
SELECT f_id, s_id, f_name, f_price FROM fruits;
SELECT f_name, f_price FROM fruits;
SELECT DISTINCT s_id FROM fruits;
SELECT TOP (3) * FROM frutis;
SELECT f_name AS '名称' , f_price AS '价格' FROM fruits;
SELECT '名称' = f_name, '价格' = f_price FROM fruits;
SELECT f_name, f_price 原价, f_price * 0.8 折扣价 FROM fruits;
操作符:
= <> < <= > >= BETWEEN AND
SELECT f_name, f_price FROM fruits WHERE f_price = 10.2; SELECT f_name, f_price FROM fruits WHERE f_name = 'apple'; SELECT f_name, f_price FROM fruits WHERE f_price < 10;
SELECT f_name, f_price FROM fruits WHERE f_price BETWEEN 2.00 AND 10.20; SELECT f_name, f_price FROM fruits NOT WHERE f_price BETWEEN 2.00 AND 10.20;
SELECT s_id, f_name, f_price FROM fruits WHERE s_id IN (101,102); SELECT s_id, f_name, f_price FROM fruits WHERE s_id NOT IN (101,102);
LIKE关键字中使用的通配符 % _ [] [^]
SELECT f_id, f_name FROM fruits WHERE f_name LIKE 'b%'; SELECT f_id, f_name FROM fruits WHERE f_name LIKE '%g%'; SELECT f_name FROM fruits WHERE f_name LIKE 'b%y';
SELECT f_id, f_name FROM fruits WHERE f_name LIKE '____y';
SELECT * FROM fruits WHERE f_name LIKE '[abe]%';
SELECT * FROM fruits WHERE f_name LIKE '[^abc]%';
SELECT c_id, c_name, c_email FROM customers WHERE c_email IS NULL; SELECT c_id, c_name, c_email FROM customers WHERE c_email IS NOT NULL;
EXISTS 关键字后面的参数是一个任意的子查询,系统对子查询进行运算以判断它是否返回行,如果至少返回一行,那么EXISTS的结果为TRUE,此时外层查询语句将进行查询;如果子查询没有返回任何行,那么EXISTS返回的结果是FALSE,此时外层语句将不进行查询。
SELECT * FROM fruits WHERE EXISTS (SELECT s_name FROM suppliers WHERE s_id =107); SELECT * FROM fruits f_price > 10.2 AND EXISTS (SELECT * s_name FROM suppliers WHERE s_id=107);
SELECT f_name FROM fruits ORDER BY f_name;
SELECT f_name, f_price FROM fruits ORDER BY f_name, f_price;
SELECT f_name, f_price FROM fruits ORDER BY f_price DESC;
SELECT s_id, COUNT(*) AS Total FROM fruits GROUP BY s_id;
SELECT s_id, f_name FROM fruits GROUP BY s_id, f_name;
GROUP BY 可以和 HAVING一起限定显示记录所需的条件,只有满足条件的分组才会被显示。
SELECT s_id, COUNT(*) AS Total fruits GROUP BY s_id HAVING COUNT(*) > 1;
利用 UNION 关键字,可以给出多条SELECT语句,并将它们的结果组合成单个结果集。合并时,两个表对应的列数和数据类型必须相同。各个SELECT语句之间用UNION 或UNION ALL 关键字分隔。UNION 不使用关键字ALL, 执行的时候删除重复的记录,所有返回的行都是唯一的;使用关键字ALL 的作用是不删除重复行也不对结果进行自动排序。
SELECT s_id, f_name, f_price FROM fruits WHERE f_price < 9.0 UNION ALL SELECT s_id, f_name, f_price FROM fruits WHERE s_id =101; SELECT s_id, f_name, f_price FROM fruits WHERE f_price < 9.0 UNION SELECT s_id, f_name, f_price FROM fruits WHERE s_id =101;
嵌套查询是指一个查询语句嵌套在另一个查询语句内部的查询。在SELECT子句中先计算子查询,子查询结果作为外层另一个查询的过滤条件,查询可以基于一个表或者多个表。
IN 关键字进行子查询时,内层查询语句仅仅返回一个数据列,这个数据列里的值将提供给外层查询语句进行比较。
SELECT s_name FROM suppliers WHERE s_id IN (SELECT s_id FROM fruits f_id='c0');
连接是关系数据库模型的主要特点。连接查询是关系数据库中最主要的查询,主要包括内连接、外连接等。通过连接运算符可以实现多个表查询。 SQL Server中的内连接有:等值连接和不等连接。
相等连接又叫等值连接:在连接条件中使用等于号(=)运算符比较被连接列的值,其查询结果中列出被连接表中的所有列,包括其中的重复列。
SELECT suppliers.s_id, s_name, f_name, f_price FROM fruits INNER JOIN suppliers ON fruits.s_id = suppliers.s_id;
不等连接:在连接条件中使用除等于运算符以外的其他比较运算符,比较被连接的列的列值。这些运算符包括>、>=、<=、<、!>、!<、<>。
SELECT suppliers.s_id, s_name, f_name, f_price FROM fruits INNER JOIN suppliers ON fruits.s_id <> suppliers.s_id;
SELECT fruits.s_id, suppliers.s_city FROM fruits INNER JOIN suppliers ON fruits.s_id = suppliers.s_id AND fruits.s_id = 101;
如果在一个连接查询中,涉及的两个表都是同一个表,这种查询称为自连接查询。自连接是一种特殊的内连接,它是指相互连接的表在物理上为同一张表,但可以在逻辑上分为两张表。
SELECT f1.f_id, f1.f_name FROM fruits AS f1, fruits AS f2 WHERE f1.s_id = f2.s_id AND f2.f_id = 'a1';
外连接分为左外连接和右外连接。
SELECT student.s_id, stu_detail.addr FROM student LEFT OUTER JOIN stu_detail ON student.s_id = stu.detail.s_id;
SELECT student.name, stu_detail.s_id FROM student RIGHT OUTER JOIN stu_detail ON student.s_id = stu_detail.s_id
全外连接又称为完全外连接,该连接查询方式返回两个连接中所有的记录数据。根据匹配条件,如果满足匹配条件时,则返回数据;如果不满足匹配条件时,同样返回数据,只不过在相应的列中填入空值,全外连接返回的结果集中包含了两个完全表的所有数据。全外连接使用关键字 FULL OUTER JOIN。
SELECT student.name, stu_detail.addr FROM student FULL OUTER JOIN stu_detail ON student.s_id = stu_detail.s_id;
INSERT INTO person (id, name, age, info) VALUES(1, 'Green', 21, 'Lawyer'); INSERT INTO person VALUES(3, 'Mary', 24, 'Musician');
INSERT INTO person(id, name, info) VALUES(4, 'Willam', 'sports man'); INSERT INTO person(id, name) VALUES(5, 'Laura');
INSERT INTO person(id, name, age, info) VALUES(6, 'Evans', 27, 'secretary'), (7, 'Dale', 22, 'cook'), (8, 'Edison', 28, 'singer') SELECT * FROM person;
INSERT还可以将SELECT语句查询的结果插入到表中。person_old表中现在有两条记录。接下来将person_old表中所有的记录插入到person表中,T-SQL语句如下:
INSERT INTO person(id, name, age, info) SELECT id, name, age, info FROM person_old; SELECT * FROM person;
UPDATE person SET age = 15, name= 'LiMing' WHERE id=10;
UPDATE person SET info='student' WHERE age BETWEEN 19 AND 22;
UPDATE person SET info='vip';
DELETE FROM person WHERE age = 22;
DELETE FROM person;
第9章 规则、默认和完整性约束
第10章 创建和使用索引
第11章 事务和锁
第12章 游标
第13章 存储过程和自定义函数
第14章 视图操作
第15章 触发器
第16章 SQL-SERVER的安全机制
第17章 数据库的备份与恢复
第18章 开发企业人事管理系统