数据库学习笔记

L的定义：

SQL 是用于访问和处理数据库的标准的计算机语言。

• SQL 指结构化查询语言，全称是 Structured Query Language。
• SQL 让您可以访问和处理数据库。
• SQL 是一种 ANSI（American National Standards Institute 美国国家标准化组织）标准的计算机语言。

2.RDBMS

• RDBMS 指关系型数据库管理系统，全称 Relational Database Management System。
• RDBMS 是 SQL 的基础，同样也是所有现代数据库系统的基础，比如 MS SQL Server、IBM DB2、Oracle、MySQL 以及 Microsoft Access。
• RDBMS 中的数据存储在被称为表的数据库对象中。
• 表是相关的数据项的集合，它由列和行组成。

3.SQL语法

• use 数据库名称; 命令用于选择数据库。
• set names utf8; 命令用于设置使用的字符集。
• SELECT * FROM Websites; 读取数据表的信息。
• SQL 对大小写不敏感：SELECT 与 select 是相同的。
• select 语句

SELECT name,country FROM Websites;

SELECT DISTINCT country FROM Websites;//SELECT DISTINCT 语句用于返回唯一不同的值。

SELECT * FROM Websites WHERE country='CN';

• and & or 运算符

SELECT * FROM Websites
WHERE country='CN'
AND alexa > 50;

SELECT * FROM Websites
WHERE country='USA'
OR country='CN';

• order by语法

SELECT * FROM Websites
ORDER BY alexa;//默认就是升序排列，即SELECT * FROM Websites ORDER BY alexa ASC

SELECT * FROM Websites
ORDER BY alexa DESC;

• insert into 语法

第一种形式无需指定要插入数据的列名，只需提供被插入的值即可：

INSERT INTO table_name
VALUES (value1,value2,value3,...);

第二种形式需要指定列名及被插入的值：

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

• update语法

UPDATE Websites 
SET alexa='5000', country='USA' 
WHERE name='菜鸟教程';

在更新记录时要格外小心！在上面的实例中，如果我们省略了 WHERE 子句，所有数据中的alexa 和 country 都会被更改

• delete语法

DELETE FROM Websites
WHERE name='百度' AND country='CN';

• select top , limit , rownum语法

  并不是所有的数据库系统都支持select top 语法，下面是SQL,MS Access数据库系统

SELECT TOP number column_name(s)
FROM table_name;

SELECT TOP 50 PERCENT * FROM Websites;

　　MY SQL数据库系统

SELECT column_name(s)
FROM table_name
LIMIT number;

　　ORACLE数据库系统

SELECT *
FROM Persons
WHERE ROWNUM <=5;

 　　LIKE 操作符

SELECT * FROM Websites
WHERE name LIKE 'G%';

SELECT * FROM Websites
WHERE name NOT LIKE '%oo%';

通配符	描述
%	替代一个或多个字符
_	仅替代一个字符
[charlist]	字符列中的任何单一字符
[^charlist] 或者 [!charlist]	不在字符列中的任何单一

 

SELECT * FROM Websites
WHERE name REGEXP '^[A-H]';//^在[]外面的就表示以A-H开头

SELECT * FROM Websites
WHERE name REGEXP '^[^A-H]';//如果^在[]里面面表示不以A-H开头 

• in 操作符

IN 操作符允许您在 WHERE 子句中规定多个值。

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);


SELECT column_name(s)
FROM table_name 
WHERE coumn_name IN (SELECT column_name FROM table_name WHERE ...)

• between操作符

BETWEEN 操作符选取介于两个值之间的数据范围内的值。这些值可以是数值、文本或者日期。

SELECT * FROM Websites
WHERE alexa BETWEEN 1 AND 20;

SELECT * FROM Websites
WHERE alexa NOT BETWEEN 1 AND 20;

SELECT * FROM Websites
WHERE (alexa BETWEEN 1 AND 20)
AND NOT country IN ('USA', 'IND');

SELECT * FROM Websites
WHERE name NOT BETWEEN 'A' AND 'H';// SQL 语句选取 name 不介于 'A' 和 'H' 之间字母开始的所有网站

在某些数据库中，BETWEEN 选取介于两个值之间但不包括两个测试值的字段。
在某些数据库中，BETWEEN 选取介于两个值之间且包括两个测试值的字段。
在某些数据库中，BETWEEN 选取介于两个值之间且包括第一个测试值但不包括最后一个测试值的字段。

• SQL别名

SELECT column_name AS alias_name
FROM table_name;

SELECT column_name(s)
FROM table_name AS alias_name;

SELECT name, CONCAT(url, ', ', alexa, ', ', country) AS site_info
FROM Websites;//把三个列（url、alexa 和 country）结合在一起，并创建一个名为 "site_info" 的别名

• SQL JOIN

1. INNER JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。

最常见的 JOIN 类型：SQL INNER JOIN（简单的 JOIN）。 SQL INNER JOIN 从多个表中返回满足 JOIN 条件的所有行。

SELECT Websites.id, Websites.name, access_log.count, access_log.date
FROM Websites
INNER JOIN access_log
ON Websites.id=access_log.site_id;

2. LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;

例如：

SELECT Websites.name, access_log.count, access_log.date
FROM Websites
LEFT JOIN access_log
ON Websites.id=access_log.site_id
ORDER BY access_log.count DESC;

3. RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;

4.

FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行.

FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

•  union操作符

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。

SELECT country FROM Websites
UNION
SELECT country FROM apps
ORDER BY country;

与union差不多的还有intersection(交）,difference（差），使用的方法和union是一致的

但需要注意的一点是，这三个关系代数查询的前提是两个查询的结构一致，还有一点就是最后的结果是自动去掉重复的，所以如果需要展示所有的，就需要使用union all

SELECT country FROM Websites
UNION ALL
SELECT country FROM apps
ORDER BY country;

• select into 语句

通过 SQL，您可以从一个表复制信息到另一个表。

SELECT INTO 语句从一个表复制数据，然后把数据插入到另一个新表中。（

MySQL 数据库不支持 SELECT ... INTO 语句，但支持 INSERT INTO ... SELECT 。

当然你可以使用以下语句来拷贝表结构及数据：CREATE TABLE 新表 SELECT * FROM 旧表）

SELECT *
INTO WebsitesBackup2016
FROM Websites;//创建备份

SELECT name, url
INTO WebsitesBackup2016
FROM Websites;//只备份部分列到新的表中

SELECT *
INTO WebsitesBackup2016
FROM Websites
WHERE country='CN';//只备份国家为中国的数据到新的表中

SELECT Websites.name, access_log.count, access_log.date
INTO WebsitesBackup2016
FROM Websites
LEFT JOIN access_log
ON Websites.id=access_log.site_id;//复制多个表中的数据到新的表中

• insert into select语句

通过 SQL，您可以从一个表复制信息到另一个表。

INSERT INTO SELECT 语句从一个表复制数据，然后把数据插入到一个已存在的表中。

INSERT INTO table2
SELECT * FROM table1;

INSERT INTO Websites (name, country)
SELECT app_name, country FROM apps;

INSERT INTO Websites (name, country)
SELECT app_name, country FROM apps
WHERE id=1;

• SQL UNIQUE 约束

UNIQUE 约束唯一标识数据库表中的每条记录。

UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。

PRIMARY KEY 约束拥有自动定义的 UNIQUE 约束。

需要注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

CREATE TABLE Persons
(
P_Id int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)//为某一列添加约束unique

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)//可以添加constraint name
)//添加多个列为unique

ALTER TABLE Persons
ADD UNIQUE (P_Id)//alter时为某一列的unique约束

ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)//alter时添加某几列的unique约束

ALTER TABLE Persons
DROP INDEX uc_PersonID//MY Sql数据库系统中，撤销或删除某一列的unique约束

ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID//SQL Server / Oracle / MS Access数据库系统中，撤销或删除某一列的；unique约束

• primary key约束

平时创建表时直接在列后面添加primary key即可

ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)//alter时添加一个primary key

ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)//alter时添加几个primary key

ALTER TABLE Persons

DROP PRIMARY KEY//MY SQL数据库系统

ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID//SQL MS ACCESS 数据库系统

foreign key 约束

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)//可以添加多个foreign key

ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)//alter时修改单个列的foreign key


ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)//alter时修改多个列的foreign key

ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders//MySQL

ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders//SQL Server / Oracle / MS Access