数据库学习笔记

数据库的设计三大范式：

1.第一范式：[列的原子性]

   数据库的第一范式的合理性主要遵循实际需求进行设计，如果每个字段都都不可拆分的原子，则可以说明该表满足了第一范式。
   
2.第二范式：[每列都和主键相关]

   在满足第一范式的基础上，数据库中的每个字段和主键相关，并且非主属性与主属性有完全依赖性。
   
3.第三范式；[主表与外表]
   
   满足第三范式之前先要满足第二范式。第三范式要求一个数据表中不包含已在其他数据表中已包含的非主键信息，即属性不依赖于其他非主属性。
   
     建立一个数据库library，主要用于书籍借出管理，限定每人拥有一张借书卡，每人每种书只能借一本；库存册数随借书、还书而改变。主要字段：USERNAME 姓名，CLASS 班级， BNO 书号，BNAME 书名,AUTHOR 作者，QUANTITY 库存册数， CNO 借书卡号，PRICE 价格 ， RDATE 还书日期, DEPT 系,  系地址 DEPTADDR                              

     第一步：根据前端设计和应用程序逻辑结合系统需求，进行分析，提取出数据库的字段。                            

     第二步：根据字段设计表结构。
     
    "1 找字段相关性，并整合到一个表上。
    
    借书卡号 - 姓名 - 班级 - 系 - 系地址
    （书号， 借书卡号） - 书名 - 作者 - 库存数 - 价格 - 还书日期"    
    

    "2 查看字段是否能再分割。根据系统需求的逻辑，该字段是否需要拆分。
列的原子性。根据业务设计，设计者决定是否可以再分。（1NF）
"                            

              借书卡信息表：                            
借书卡号    姓名    班级    系    系地址            
C001    张三，18，男    16级2班    计算机系    南校区光明路100号1栋            
                                            系所在校区    系详细地址        
                                            南校区      光明路100号1栋        
                            
     3 整合成两张表，分析其中问题。    
     
               借书卡信息表：                            
借书卡号    姓名    班级    系         系所在校区      系详细地址        
C001      张三       16级2班    计算机系    南校区    光明路100号1栋        
C002      李四       16级1班    体育系        北校区    胜利路200号2栋        
               图书信息表:                            
书号    借书卡号    书名       作者       库存数    价格    借书日期    还书日期
B001    C001    演员的自我修养    周星驰    100      20    2016/3/1    2016/4/1
B002    C001    sql server从入门到放弃    小王    200    30    2016/3/2    2016/4/5
B001    C002    演员的自我修养    周星驰    100    20    2016/4/1    2016/5/1
B001    C003    演员的自我修养    周星驰                
                            
"问题：
a 当新增一条书号为B001的记录时，书名，作者等信息重复增加，造成数据冗余。
b 操作异常。更新异常、插入异常、删除异常。"                            
4 解决上述问题：把第二张表进行拆分。分析主属性是能确定其他每一非主属性，重点分析非主属性是否完全依赖主属性，而不是部分依赖或不依赖。（2NF）    
                        
借书卡信息表：                            
借书卡号    姓名    班级    系    系所在校区    系详细地址        
C001    张三    16级2班    计算机系    南校区    光明路100号1栋        
C002    李四    16级1班    体育系    北校区    胜利路200号2栋        
C003    王五    16级1班    计算机系    南校区    光明路100号1栋        
                            
借书记录表:                            
借书流水号    书号    借书卡号    借书日期    还书日期            
1000001    B001    C001    2016/3/1    2016/4/1            
1000002    B002    C001    2016/3/2    2016/4/5            
1000003    B001    C002    2016/4/1    2016/5/1            
1000004    B001    C003                    
                            
图书信息表:                            
书号    书名    作者    库存数    价格            
B001    演员的自我修养    周星驰    100    20            
B002    sql server从入门到放弃    小王    200    30            
B001    演员的自我修养    周星驰    100    20            
B001    演员的自我修养    周星驰            
    
5 发现以下问题还未完全解决。消除传递依赖。（3NF）
                            
"问题：（还未完全解决）
a 当新增一条书号为B001的记录时，书名，作者等信息重复增加，造成数据冗余。
b 操作异常。更新异常、插入异常、删除异常。"                            
                            
"分析：
（借书卡号） - （系） - （系所在校区和详细地址）
这是一种传递依赖，并不是直接依赖。
每一个字段设计之初，不仅要设计好列名，还要设计其存放的数据和实际意义。"    
                        
学生卡信息表：                            
借书卡号    姓名    班级    系                
C001    张三    16级2班    计算机系                
C002    李四    16级1班    体育系                
C003    王五    16级1班    计算机系                
                            
系信息表：                            
系    系所在校区    系详细地址                    
计算机系    南校区    光明路100号1栋                    
体育系    北校区    胜利路200号2栋                    
                            
借书记录表:                            
借书流水号    书号    借书卡号    借书日期    还书日期            
1000001    B001    C001    2016/3/1    2016/4/1            
1000002    B002    C001    2016/3/2    2016/4/5            
1000003    B001    C002    2016/4/1    2016/5/1            
1000004    B001    C003                    
                            
图书信息表:                            
书号    书名    作者    库存数    价格            
B001    演员的自我修养    周星驰    100    20            
B002    sql server从入门到放弃    小王    200    30            
B001    演员的自我修养    周星驰    100    20            
B001    演员的自我修养    周星驰                    

constraint p_k primary key(nm);//表级约束。表级约束可以同时多个列建立约会。
create table test1(  id int, primary key,
 sno varchar(20),
 cno varchar(10),
 foreign key(sno,cno) references sc(ano,cno),
);


//数据查询：

--选择查询所有信息

SELECT * FROM table;

//查询SNO列

SELECT * FROM table WHERE SNO='s001';

//条件查询

SELECT * FROM table WHERE SCORE>60 AND SNO = 's001‘；

//模糊查询：

SELECT * FROM table WHERE CNAME LIKE '%java%';

SELECT * FROM table WHERE CNO IN('s001','s002');

SELECT * FROM table WHERE SBN = 's001' OR SBN = 's003';

SELECT * FROM table WHERE SBN BETWEEN 35 AND 80;

//列查询：

SELECT SNO 学号,SBN 得分 FROM SC WHERE BETWEEN 65 AND 80;

//多表查询：

SELECT * FROM SC,COURSE;

注：  

SELECT SC.SNO 编号,SC.SCORE  成绩 FROM SC,COURSE AS C WHERE C.CNAME LIKE '%Java%' AND C.CNO=C.NO;


//排序：

//升序排列：

//asc - 升序，desv - 降序

SELECT SC.SNO 编号,SC.SCORE  成绩 FROM SC,COURSE AS C WHERE C.CNAME LIKE '%Java%' AND C.CNO=C.NO ORDER BY SC.SNO ASC;

//筛选多少行：

SELECT TOP 3 SC.SNO 编号,SC.SCRORE 成绩 FROM sc,COURSE  AS C WHERE C.CNAME LIKE '%Java%' AND C.CNO=C.NO ORDER BY SC.SNO DESC;

//聚合函数：


SELECT COUNT(*) COURSE_NUM,SUM(SCORE) TOTALE_SCORE, AVG(SCORE) AS AVG_SCORE,MAX(SCORE),MIN(SCORE) MIN_SCORE FROM SC WHERE SNO='s002';

//分组查询：

SELECT TOP AVG(SCORE) FROM SC GROUP BY SC.SNO HAVING AVG(SCORE)>75 ORDER BY AVG_SCORE DESC;

CREATE DATABASE library
GO
USE library
CREATE TABLE student(
         id BIGINT PRIMARY KEY IDENTITY, 
         name VARCHAR(10) NOT NULL, 
         sex CHAR(1)NOT NULL,
         class VARCHAR(10)NOT NULL,
         );
CREATE TABLE bBook(
         bnd BIGINT PRIMARY KEY IDENTITY,
         author VARCHAR(20)NOT NULL,
         bName VARCHAR(30) NOT NULL,
         quantity int
         );

         
        
--ERROR：
--消息 544，级别 16，状态 1，第 2 行
--当 IDENTITY_INSERT 设置为 OFF 时，不能为表 'student' 中的标识列插入显式值。
--
--在表里面, 有一列数据类型是 IDENTITY 的，数据库自动递增列对于自动递增的列, 插入的时候, 不要把这一列加进去.


INSERT INTO bBook(author,bName,quantity)SELECT '威廉·莎士比亚','《哈姆雷特》',  20 UNION
                                        SELECT '但丁',         '《神曲》',      32 UNION
                                        SELECT '列夫·托尔斯泰','《战争与和平》',29 UNION
                                        SELECT '塞万提斯',     '《堂吉诃德》',  40;
                    
CREATE TABLE borrow(cno BIGINT,stu_id BIGINT,book_id BIGINT);

--添加一列 alter table tableName add variableName variableType

ALTER TABLE bBook ADD price VARCHAR(10);

--修改数据表中的数据：update tableName set variableName=variableValue where variableName2=variableValue2;

UPDATE bBook SET price='$30.3' WHERE bnd=2;

--数据表的删除drop table tableName;

DROP TABLE course;

--默认清空数据表中的数据。删除速度比 DELETE 快。

TRUNCATE TABLE major;

--使用 DELETE 同时删除多条数据。DELETE FROM tableName WHERE variableName IN(variableValue1,variableValue)

DELETE FROM major WHERE code IN(557,567);

--修改major表中的字段名classteacher为class_teacher.

EXEC sp_rename 'major.[classteacher]', 'class_teacher', 'column' ;

--查找major数据表中的code=557的数据条。

SELECT * from major where code=557;

--查询多条语句。

SELECT * from major where code IN(557,558);

--SELECT 多条件查询。

SELECT * from major where collegeId>=3 AND code=558;

SELECT * from major where collegeId BETWEEN 3 AND 8  AND code IN(557,558);

--使用 DISTINCT 关键字排除指定字段的重复项来查询。

SELECT DISTINCT collegeId, code, mname, intro, classreacher  FROM major;



关于 DELETE TRUNCATE DROP:


     DROP 删除的速度最快最暴力删除整个数据表并释放空间，数据表无法回退。属于DDL。
     
     TRUNCATE 删除速度比 DELETE 速度快清除数据表中的数据并释放空间，清除后表内中自增的索引值也会被清除，而 DELETE 不能清楚索引，删除数据也无法回退，属于DDL。不能清除行数据。
     
     DELETE 可以清楚整个数据表中的数据，也可以安条件进行清除数据行。属于DML，如果有 ROLLBACK 指令时 DELETE 将会被回退撤销。
     
     
     
关于 模糊查询：

    % 包含零个或更多字符的任意字符串。
    
    _ 任何单个字符。
    
    [ ] 指定范围（例如 [a-f]）或集合（例如 [abcdef]）内的任何单个字符。 
    
    [^] 不在指定范围（例如 [^a - f]）或集合（例如 [^abcdef]）内的任何单个字符。 

    请将通配符和字符串用单引号引起来，例如： 
    
    LIKE 'Mc%' 将搜索以字母 Mc 开头的所有字符串（如 McBadden）。 

    LIKE '%inger' 将搜索以字母 inger 结尾的所有字符串（如 Ringer、Stringer）。 

    LIKE '%en%' 将搜索在任何位置包含字母 en 的所有字符串（如 Bennet、Green、McBadden）。 

    LIKE '_heryl' 将搜索以字母 heryl 结尾的所有六个字母的名称（如 Cheryl、Sheryl）。 

    LIKE '[CK]ars[eo]n' 将搜索下列字符串：Carsen、Karsen、Carson 和 Karson（如 Carson）。 

    LIKE '[M-Z]inger' 将搜索以字符串 inger 结尾、以从 M 到 Z 的任何单个字母开头的所有名称（如 Ringer）。

    LIKE 'M[^c]%' 将搜索以字母 M 开头，并且第二个字母不是 c 的所有名称（如 MacFeather）。
     

关于SQL Server中常见的一些问题：


1.char、varchar、nchar、nvarchar之间的区别：

char：存储ANSI字符，固定长度，不足的以英文半角空格填充，存储定长字符很方便，char字段上的索引效率级高；
varchar：存储ANSI字符，可变长度，存储空间随着实际存储量变化，存储效率没有char高；

nchar：存储Unicode字符，固定长度，不足的以英文半角空格填充；
nvarchar：存储Unicode字符，可变长度，存储空间是实际存储字符数*2+2(结束字符)；

从存储方式上，nvarchar是按字符存储的，而varchar是按字节来存储的。
从存储量上来讲，nvarchar是按双字节来存储的，而varchar是按单字节来存储的。
在使用上，具体要看需求上是否含有Unicode字符来决定。

2.varchar(n)与nvarchar(n)的区别：

varchar(n)：长度为n个字节的可变长度且非Unicode的字符，n必须是介于1~8000之间的整数。但存储空间是实际的存储长度；
nvarchar(n)：包含了n个字符可变长度的Unicode字符。n是1~4000之间的整数，但存储空间是实际长度的存储长度的2倍+2。
当ncarchar>8000时，存储机制和text一样。

添加约束：

--在表birds中给id字段设置为主键[前提：birds表中的id字段具有非空属性]。

ALTER TABLE birds ADD CONSTRAINT constraint_name PRIMARY KEY(id);

--修改birds表中的id字段属性为非空。

ALTER TABLE birds ALTER COLUMN id INT NOT NULL;

--删除birds表中的d_id约束[d_id为表中定义的主键名，而不是主键的字段名];

ALTER TABLE birds DROP CONSTRAINT d_id;


好像有个问题： 只有当相关表之间的外键关系删除之后方可修改主表中的主键属性。

--将birds表中的id设置为唯一约束。

ALTER TABLE birds ADD CONSTRAINT unique_id UNIQUE(id);

--在birds表中添加检查属性；

ALTER TABLE birds ADD CONSTRAINT con_nbame CHECK(name='[F,M]');

 

转载于:https://www.cnblogs.com/feiruo/p/5437719.html