数据库学习（《Head FIrst SQL》）

CREATE DATABASE

USE DATABASE

CREATE TABLE

DROP TABLE

NULL NOT  NULL DEFAULT 

 

SELECT * 

= <> < > <= >=

BETWEEN LIKE

AND OR

NOT

 

 

DELETE

 

 

UPDATE：

UPDATE your_table

SET first_column = 'newvalue',

second_colume = 'newvalue'

WHERE colume_name = somevalue

 

UPDATE语句能运用在表的多条记录上。它可以和基础数字运算符一起使用，可以操作数值数据。

 

 

 

 

原子性：1、具有原子性数据的列中不会有多个类型相同的值;

    2、具有原子性数据的表中不会有多个存储同类数据的列。

原子性有利于确保表内容的准确性，原子性数据也使得查询更有效率，因为查询会因原子性而更容易设计，而且运行所需时间也更短，因此在面对大量数据时有加分效果。

 

 

让数据具有原子性是创建一个规范化表的第一步。规范化表的优点：

1、规范化表中没有重复的数据，可以减少数据库的大小;

2、因为查找的数据较少，你的查询会更为快速。

 

 

1NF：

1、每个数据行必须包含具有原子性的值

2、每个数据行必须有独一无二的识别项，人称主键。

 

（主键： 不可以为NULL、插入新纪录时必须指定主键值，主键必须简洁，主键值不可以被修改。

 

SHOW命令可以查看重建表所需的语句、表的所有列及其数据类型信息等，例如：

SHOW COLUMNS FROM tablename

SHOW CREATE TABLE tablename

 

ALTER指令可用来给表添加新列等，ALTER命令几乎能让你改变表里的一切，而且不需要重新插入数据。但也要小心，如果改变列的类型，你可能就会有遗失数据的风险。ALTER经常和下面这几个保留字一起使用，CHANGE MODIFY ADD DROP RENAME TO 

CHANGE:可以同时改变现有列的名称和数据类型

MODIFY:修改现有列的数据类型或位置

ADD 在当前表中添加一列，可自选类型

DROP 从表中删除某列

 

 

SELECT还可以和一些字符串处理函数搭配使用，要求所选的列必须是CHAR或者VARCHAR等文本类型

RIGHT（location，index）

SUBSTRING_INDEX(location,',',1)查找指定字符或字符串前的所有内容

UPPER(your_string)

LOWER(your_string)

REVERSE(your_string)翻转字符串里的字符排序

LTRIM(your_string) RTRIM(your_string)返回清除多余空格后的字符串，分别清除字符左侧和右侧的多余空格

LENGTH（your_string）返回字符串中的字符数量

 

 

CASE表达式可以搭配UPDATE以外，SELECT、INSERT、DELETE等

 

 

ORDER BY排序，支持多列排序，默认ASC，可设置唯DEC
SELECT title，purchased

FROM table

ORDER BY title ASC, purchased DESC;

 

ORDER BY  根据指定的列，按字母顺序排列查询结果

GROUP BY 根据共用列，把记录分成多个组

COUNT 

DISTINCT 不同的值只会返回一次，返回的结果中没有重复值

SUM 

LIMIT 可以明确指定返回记录的数量，以及从哪一条记录开始返回

AVG

MAX

MIN

 

外键：

外键是表中的某一列，它引用到另一个表的主键

 

外键可以与引用的主键名称不相同

外键能用于确认一张表中的行与另一张表中的行相对应

外键的值可以是NULL，即使主键值不可为NULL

外键值不需唯一──事实上，外键通常都没有唯一性。

 

数据模式：

一对一

一对多

多对多（通常通过junction table连接表来将多对多关系转变成两个一对多关系。）

 

函数依赖的表示方式：

T.x -> T.y 表示 在关系表T中，y列函数依赖于x列。

 

部分函数依赖：

非主键的列依赖于组合主键的某一部分（担不是完全依赖于组合主键）

传递函数依赖：

任何非键列与另一个非键列有关联，如果改变任何非键列可能造成其它列的改变

 

第二范式：

重点就是表的主键如何与其他数据产生关系

定义：1、先符合1NF，2、没有部分函数依赖性

只要所有列都是主键的一部分，或者表中有唯一主键列符合1NF的表也会符合2NF。

如何你的表有人工主键且没有组合主键，则符合2NF

 

第三范式：

定义：1、符合2NF，2、没有传递函数依赖性

 

AS关键字

即可用于将查询的结果填入新表中，有可以用于给列或者表起个别名。如下面的用法：

CREATE TRABLE profession 

(

id INT（11） NOT NULL AUTO_INCREMENT PRIMARY KEY,

mc_prof vatchar(20)

) AS
SELECT profession AS mc_prof FROM my_contacts

GROUP BY mc_prof

ORDER BY mc_prof;

 

 交叉联接（CROSS JOIN）: 返回两张表的每一行相乘的结果

SELECT t.toy, b.boy

FROM toys AS t

CROSS JOIN

boys AS b;

 

内联接： 通过查询中的条件移除了某些结果数据行后的交叉联接

SELECT somevolumns FROM table1

INNER JOIN

table2

ON someconditions;

 

内联接种类：

1、相等联接(EQUIJOIN), 测试相等性的内联接

SELECT boys.boy, tous.toy

FROM boys 

INNER JOIN

toys

ON boys.toy_id = toys.toy_id;

2、不等联接（NON──EQUIJOIN）测试不相等的内联接

SELECT boys.boy, tous.toy

 

FROM boys 

INNER JOIN

toys

ON boys.toy_id <> toys.toy_id

ORDER BY boys.boy;

3、自然联接（natural join），只有在联接表的列在两张表中的名称都相同时才会有用。

SELECT boys.boy, tous.toy

 

 

FROM boys 

INNER JOIN

toys

 

子查询：

在单一查询不够用的时候，请用子查询。

子查询不过是查询里的查询

外部的查询称为包含查询或外层查询，内部的查询就是内部查询，或称为子查询

 

SELECT last_name, first_name

FROM my_contracts

WHERE zip_code = (SELECT zip_code FROM zip_code WHERE city = "Memphis" AND state = "TN");

 

子查询分类：

非关联子查询：

如果子查询可以独立运行且不会引用外层查询的结果，即称为非关联子查询

关联子查询：

指内层查询的解析需要依赖外层查询的结果

 

外联接：

外联接返回某张表的所有行，并带有来自另一张表的条件相符的行。

 

和内联接的区别：

外联接一定会提供数据行，无论该行能否在另一个表中找到相匹配的行。

 

左外联接（LEFT OUTER JOIN）接收左表的所有行，并用这些行与右表中的行匹配。左外联接的结果集中的ＮＵＬＬ表示右表中没有找到与左表相符的记录。

 SELECT g.girl, t.toy

 FROM girls g 

 LEFT OUTER JOIN toys t

 ON g.toy_id = t.toy_id;

 

自联接

联合(UNION)

UNION联合规则：

1、每个SELECT语句中列的数量必须一致。不可以由第一条语句选取了两列，由其他语句却只选取一列;

2、每个SELECT语句包含的表达式与统计函数也必须相同

3、SELECT语句的顺序并不重要，不会改变结果

4、SQL默认会清除联合结果中的重复值

5、列的数据类型必须相同或者可以互相转换

6、如果出于某些原因而需要看到重复数据，可以使用UNION ALL运算符。这个运算符返回每个相符的记录，而不只是没有重复的记录。

 

交集（INTERSECT）、差集（EXCEPT）

 

 

创建试图：（CREATE VIEW）

基本上视图是一个只有在查浔中使用VIEW时才存在的表。它被称为虚拟表（virtual table）

视图把复杂查询简化为一个命令，带来更轻松的生活。

即使一直改变数据库结构，也不会破坏依赖表的应用程序。

创建试图可以隐藏读者无需看到的信息

 

CHECK OPTION 检查每个进行INSERT或DELETE的查询，它根据视图中的WHERE子句判断这些查询可否执行。

 

事务：

在事务过程中，如果所有步骤无法不受干扰地完成，则不该完成任何单一步骤

 

ACID检测：

ATOMICITY，原子性

事务里的每一个步骤都必须完成，否则只能都不完成。不能只执行部分事务。

CONSISTENCY，一致性

事物完成后，应该维持数据库的一致性，

ISOLATION，隔离性

表示每件事务都会看到具有一致性的数据库，无论其他事务有什么行动

DURABLILITY，持久性

事务完成后，数据库需要正确地存储数据并保护数据免受断电或其他威胁的伤害。

SQL管理事务

1、START TRANSACTION

2、COMMIT，COMMIT之前数据库都不会发生任何改变

 

两种支持事务的引擎：BDB，InnoDB

3、ROLLBACK

 

数据库用户管理、权限管理

 

创建新用户：

CREATE USER alise 

IDENTIFIED BY “fasd43”

 

分配权限

GRANT SELECT ON table TO alise WITH GRANT OPTION

 

收回权限

REVOKE SELCCT ON table FROM alise

 

收回权限的控制CASCADE、 RESTRICT

 

收回GRANT OPION权限分配权利，本来已经分配的权限不受影响，收回某个用户的权限，则该用户和该用户将这权限向下授权的用户都可以受牵连。可以用CASCADE、RESTRICT来控制。

 

为了使得给多个用户分配权限方便，创造了角色的概念，将用户定义为不同的角色，既而该用户就有了该角色能控制的权限。

例如：

CREATE ROLE data_entry

GRANT SELECT，INSERT ON some_table TO data_entry 

GRANT data_entry TO user WITH ADMIN OPTION;

 

 

删除角色

DROP ROLE data_entry

 

一旦撤销某个角色，该角色所在的用户组的相关权限都会消失

 

 WITH ADMIN OPTION允许用户将自己的权限授予他人

REVOKE（CASCADE、RESTRICT）同权限分配