SQL

【persons表】

persons.jpg

【info表】

info.jpg

创建一个名为 "my_db" 的数据库。

CREATE DATABASE my_db;

image.png

创建名为 "PersON" 的表。该表包含 5 个列，列名分别是：
"Id_P"、"LastName"、"FirstName"、"Address" 以及 "City"

CREATE TABLE Persons
(
Id_P int,
LAStName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

插入新的行

INSERT INTO 
Persons 
VALUES ('1','Ming', 'Yao', '1 road', 'Shanghai');

在指定的列中插入数据

INSERT INTO 
persons (LastName,FirstName,Address,City) 
VALUE ('carter','vince','air road','U.S.A');

更新某一行中的一个列

UPDATE 
persons 
SET Id_P='2' WHERE LastName='carter';

更新某一行中的若干列

UPDATE 
persons 
SET id_p='3',FirstName='yao',Address='pudong' 
WHERE LastName='ming';

删除某行

DELETE 
FROM persons 
WHERE LastName='ming';

删除所有行
可以在不删除表的情况下删除所有的行。这意味着表的结构、属性和索引都是完整的

DELETE FROM 
persons;

DELETE * 
FROM persons;

TOP 子句用于规定要返回的记录的数目。
对于拥有数千条记录的大型表来说，TOP 子句是非常有用的。
注释：并非所有的数据库系统都支持 TOP 子句。

SELECT * 
FROM persons LIMIT 2;

LIKE 操作符
LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。

1、从上面的 "Persons" 表中选取居住在以 "U" 开始的城市里的人

SELECT * 
FROM persons 
WHERE city LIKE 'U%';

2、从上面的 "Persons" 表中选取居住在以 "g" 结尾的城市里的人

SELECT * 
FROM persons 
WHERE city LIKE '%g';

3、从 "Persons" 表中选取居住在包含 "ei" 的城市里的人

SELECT * 
FROM persons 
WHERE city LIKE '%ei%';

4、从 "Persons" 表中选取居住在不包含 "ei" 的城市里的人

SELECT * 
FROM persons 
WHERE city NOT LIKE '%ei%';

5、从 "Persons" 表中选取的这条记录的姓氏以 "C" 开头，
   然后是一个任意字符，然后是 "r"，然后是任意字符，然后是 "er"

SELECT * 
FROM Persons 
WHERE LastName LIKE 'c_r_er';

IN 操作符允许我们在 WHERE 子句中规定多个值
从上表中选取FirstName为 vince 和 bill 的人

SELECT * 
FROM persons 
WHERE FirstName IN ('vince','bill');

SELECT * 
FROM persons 
WHERE FirstName NOT IN ('vince','bill');

操作符 BETWEEN ... AND 会选取介于两个值之间的数据范围。
这些值可以是数值、文本或者日期。
显示介于 "1"和 "3"之间的人

SELECT * 
FROM persons 
WHERE Id_P BETWEEN '1' AND '3';

SELECT * 
FROM persons 
WHERE Id_P NOT BETWEEN '1' AND '3';

AliAS 为列名称和表名称指定别名

假设我们有两个表分别是："Persons" 和 "Product_Orders"。
我们分别为它们指定别名 "p" 和 "po"。
现在，我们希望列出 "John Adams" 的所有定单

SELECT po.OrderID, p.LastName, p.FirstName
FROM Persons AS p, Product_Orders AS po
WHERE p.LastName='Adams' AND p.FirstName='John'

使用一个列名别名
SELECT LastName AS Family, FirstName AS Name FROM Persons;

结果
Family  Name
Ming    Yao
Carter  Vince
Wade    Dwyane
Rose    Derrick
Nowitzki    Dirk
Lin       Jeremy
GINóbili    Manu

INNER JOIN 获取两个表中字段匹配关系的记录

SELECT persons.LastName,persons.FirstName,persons.City,
info.Height,info.Weight,info.Position 
FROM persons
JOIN info 
ON persons.Id_P=info.Id_P;

SELECT p.LastName,p.FirstName,p.City,i.Height,i.Weight,i.PositiON 
FROM persons AS p
JOIN info AS i
ON p.Id_P=i.Id_P;

LEFT JOIN 与 JOIN 有所不同。 MySQL LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据

SELECT p.LastName,p.FirstName,p.City,i.Height,i.Weight,i.PositiON 
FROM persons AS p
LEFT JOIN info AS i
ON p.LastName=i.LastName;

RIGHT JOIN 会读取右边数据表的全部数据，即便左边边表无对应数据

SELECT p.LastName,p.FirstName,p.City,i.Height,i.Weight,i.PositiON 
FROM persons AS p
RIGHT JOIN info AS i
ON p.LastName=i.LastName;

UNION 操作符用于合并两个或多个 SELECT 语句的结果集
默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL

SELECT LastName
FROM persons
UNION
SELECT LastName
FROM info;

NOT NULL 约束
NOT NULL 约束强制列不接受 NULL 值。
NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录

CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

UNIQUE 约束
UNIQUE 约束唯一标识数据库表中的每条记录。
UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。
PRIMARY KEY 拥有自动定义的 UNIQUE 约束。
请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束

CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (Id_P)
);

PRIMARY KEY 约束
PRIMARY KEY 约束唯一标识数据库表中的每条记录。
主键必须包含唯一的值。
主键列不能包含 NULL 值。
每个表都应该有一个主键，并且每个表只能有一个主键

CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (Id_P)
);

FOREIGN KEY 约束
一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY

CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (Id_O),
FOREIGN KEY (Id_P) REFERENCES PersONs(Id_P)
);

DROP TABLE语句用于删除表（表的结构、属性以及索引也会被删除）

DROP TABLE info;

TRUNCATE TABLE 语句仅仅需要除去表内的数据，但并不删除表本身

TRUNCATE TABLE info;

ALTER TABLE 语句用于在已有的表中添加、修改或删除列

ALTER TABLE info 
ADD Id_P int;

ALTER TABLE info 
DROP COLUMN Id_P;

VIEW（视图）
什么是视图？
在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。
视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。
我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，我们也可以提交数据，就像这些来自于某个单一的表。
注释：数据库的设计和结构不会受到视图中的函数、where 或 join 语句的影响。

CREATE VIEW 语法

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

IS NULL选取在 "Address" 列中带有 NULL 值的记录

SELECT LastName,FirstName,Address 
FROM Persons
WHERE Address IS NULL;

NOT NULL选取在 "Address" 列中不带有 NULL 值的记录

SELECT LastName,FirstName,Address 
FROM Persons
WHERE Address IS NOT NULL;

AVG() 语法 AVG 函数返回数值列的平均值。NULL 值不包括在计算中

选择info表里面大于平均身高的球员

SELECT FirstName,LastName 
FROM info 
WHERE Height>(SELECT AVG(Height) FROM info);

COUNT(column_name) 函数返回指定列的值的数目（NULL 不计入）
计算位置为SG的数量

SELECT COUNT(POSITION) FROM info WHERE POSITION='SG';

ORDER BY 排序
升序

SELECT * 
FROM players_info
ORDER BY Weight ASC;

image.png
降序

SELECT * 
FROM players_info
ORDER BY Weight DESC;

image.png
GROUP BY 数据分组
统计球衣号码相同的数量

SELECT Number AS 球衣号码,COUNT(*) AS 合计
FROM players_info
GROUP BY Number;

image.png