SQL学习笔记

MySQL learning notes

运算符

xor One or the other (but not both)

round

• round(gdp/1000000000,2) 取到2位小数
• round(gdp/1000000000,-3) 取到nearst 1000

LEFT(name,1) 选出开头的几个字母

不等于 <>，！= ,<=>

• <> 或 ！= 只能判断基本数据类型，不包括null
• is 关键字只能判断null
• <=> 既能判断null 又能判断 基本数据类型

SELECT DISTINCT 返回唯一不同的值

SELECT DISTINCT column1, column2, ...
FROM table_name;

UNION 合并两个或多个 SELECT 语句的结果

• UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同
• 默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL

WHERE 中运算符

NOT 非

select * from emp where not sal > 1500;

is null 空值判断

Select * from emp where comm is null;

LIKE 粗略查找

• WHERE name LIKE 'B%' 来找出以B开头的名字
• WHERE name LIKE '%B' 来找出以B结尾的名字
• WHERE name LIKE '%B%' 来找出包括B的名字
• WHERE name LIKE '%B%B%' 来找出包括两个以上B的名字
• WHERE name LIKE '_n%' 以n为第二个字母,'_'为万用字母元位
• WHERE name LIKE '____' 找出名字为4个字母的
• 顯示所有國家名字,其首都是國家名字加上”City”
  SELECT name FROM world WHERE capital LIKE concat(name, ' City')

通配符

%

选取 url 包含模式 “oo” 的所有网站：

SELECT * FROM Websites
WHERE url LIKE '%oo%';

_

选取 name 以 “G” 开始，然后是一个任意字符，然后是 “o”，然后是一个任意字符，然后是 “le” 的所有网站

SELECT * FROM Websites
WHERE name LIKE 'G_o_le';

[ ]

选取 name 以 “G”、“F” 或 “s” 开始的所有网站

SELECT * FROM Websites
WHERE name REGEXP '^[GFs]'; --REGEXP 是正则表达式

选取 name 以 A 到 H 字母开头的网站：

SELECT * FROM Websites
WHERE name REGEXP '^[A-H]';

选取 name 不以 A 到 H 字母开头的网站：

SELECT * FROM Websites
WHERE name REGEXP '^[^A-H]';

BETWEEN

选取 date 介于 ‘2016-05-10’ 和 ‘2016-05-14’ 之间的所有访问记录：

SELECT * FROM access_log
WHERE date BETWEEN '2016-05-10' AND '2016-05-14';

• 需检查不同数据库中BETWEEN是开区间、闭区间还是半开半闭区间

修改表

各种JOIN

需要两个表中的共4列数据，将’Websites’ inner join 到‘access_log’ 中。以id/site_id为匹配列

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

• INNER JOIN 和 JOIN 是一样的

INSERT INTO 插入新记录

1. 不指定列名，只提供新的值

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

2. 指定列名，提供新列名和新的值

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

复制表： INSERT INTO SELECT 与SELECT INTO FROM

• INSERT INTO SELECT
  只复制指定的列插入到另一个已存在的表中：

INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;

• SELECT INTO FROM
  • 复制表结构及其数据：

create table table_name_new as select * from table_name_old

- 只复制表结构

create table table_name_new as select * from table_name_old where 1=2;

or

create table table_name_new like table_name_old

UPDATE 更新表的记录

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

DELETE 删除表中的行

DELETE FROM table_name
WHERE condition;

改名

改列名

SELECT column_name AS alias_name
FROM table_name;

把三个列（url、alexa 和 country）结合在一起，并创建一个名为 “site_info” 的别名：

SELECT name, CONCAT(url, ', ', alexa, ', ', country) AS site_info
FROM Websites;

使用 “Websites” 和 “access_log” 表，并分别为它们指定表别名 “w” 和 “a”（通过使用别名让 SQL 更简短）：

SELECT w.name, w.url, a.count, a.date
FROM Websites AS w, access_log AS a
WHERE a.site_id=w.id and w.name="菜鸟教程";

改表名

SELECT column_name(s)
FROM table_name AS alias_name;

创建数据库

CREATE DATABASE dbname;

创建表

CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);

• data_type 参数规定列的数据类型（例如 varchar、integer、decimal、date 等等）
• size 参数规定表中列的最大长度

设置返回格式

SELECT TOP 规定返回记录的数目

LIMIT

SELECT *
FROM Persons
LIMIT 5;

ROWNUM

SELECT *
FROM Persons
WHERE ROWNUM <=5;

SELECT TOP

选取前面百分之 50 的记录

SELECT TOP 50 PERCENT * FROM Websites;

返回前5行

select top 5 * from table

返回后5行

select top 5 * from table order by id desc  --desc 表示降序排列 asc 表示升序

ORDER BY 排序

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

• column1, column2, …：要排序的字段名称，可以为多个字段。
• ASC：表示按升序排序。
• DESC：表示按降序排序。

对表的约束

• 如果存在违反约束的数据行为，行为会被约束终止。
• 约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）

CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
);

NOT NULL

1. 创建表时加NOT NULL约束

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NOT NULL,
    Age int
);

2. 已创建表中加NOT NULL约束

ALTER TABLE Persons
MODIFY Age int NOT NULL;

3. 已创建表中删除NOT NULL约束

ALTER TABLE Persons
MODIFY Age int NULL;

UNIQUE

1. 创建表时加UNIQUE约束

CREATE TABLE Persons
(
P_Id int NOT NULL,
UNIQUE (P_Id)
)
);

或命名UNIQUE 约束，并定义多个列的 UNIQUE 约束

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
)

2. 已创建表中加UNIQUE约束

ALTER TABLE Persons
ADD UNIQUE (P_Id)
-- or
ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

3. 已创建表中删除UNIQUE约束

ALTER TABLE Persons
DROP INDEX uc_PersonID
-- or
ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID

PRIMARY KEY

• 唯一标识，可看作not null和unique的结合，约束该列中值必须唯一且不为空。
• 每个表都应该有一个主键，并且每个表只能有一个主键

1. 创建表时加PRIMARY KEY约束

CREATE TABLE Persons
(
P_Id int NOT NULL,
PRIMARY KEY (P_Id)
)

或命名PRIMARY KEY 约束，并定义多个列的 PRIMARY KEY 约束

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)
)

2. 已创建表中加PRIMARY KEY约束

ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)
-- or
ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

3. 已创建表中删除PRIMARY KEY约束

ALTER TABLE Persons
DROP PRIMARY KEY
-- or
ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID

FOREIGN KEY

一个表中的 FOREIGN KEY 指向另一个表中的 UNIQUE KEY(唯一约束的键)

CREATE TABLE Orders
(
O_Id int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)  -- 在Orders表中创建外键指向Persons表中的那一列
)

CHECK

用于限制列中的值的范围。
'CHECK (P_id>0'

DEFAULT

插入默认值

City varchar(255) DEFAULT 'Sandnes'

函数