001 sql基础

常用的数据类型

数据类型	描述
integer(size)int(size)smallint(size)tinyint(size)	仅容纳整数。在括号内规定数字的最大位数。
decimal(size,d)numeric(size,d)	容纳带有小数的数字。“size” 规定数字的最大位数。“d” 规定小数点右侧的最大位数。
char(size)	容纳固定长度的字符串（可容纳字母、数字以及特殊字符）。在括号中规定字符串的长度。
varchar(size)	容纳可变长度的字符串（可容纳字母、数字以及特殊的字符）。在括号中规定字符串的最大长度。
date(yyyymmdd)	容纳日期。

MySQL 数据类型

在 MySQL 中，有三种主要的类型：文本、数字和日期/时间类型。

Text 类型：

数据类型	描述
CHAR(size)	保存固定长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的长度。最多 255 个字符。
VARCHAR(size)	保存可变长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的最大长度。最多 255 个字符。注释：如果值的长度大于 255，则被转换为 TEXT 类型。
TINYTEXT	存放最大长度为 255 个字符的字符串。
TEXT	存放最大长度为 65,535 个字符的字符串。
BLOB	用于 BLOBs (Binary Large OBjects)。存放最多 65,535 字节的数据。
MEDIUMTEXT	存放最大长度为 16,777,215 个字符的字符串。
MEDIUMBLOB	用于 BLOBs (Binary Large OBjects)。存放最多 16,777,215 字节的数据。
LONGTEXT	存放最大长度为 4,294,967,295 个字符的字符串。
LONGBLOB	用于 BLOBs (Binary Large OBjects)。存放最多 4,294,967,295 字节的数据。
ENUM(x,y,z,etc.)	允许你输入可能值的列表。可以在 ENUM 列表中列出最大 65535 个值。如果列表中不存在插入的值，则插入空值。注释：这些值是按照你输入的顺序存储的。可以按照此格式输入可能的值：ENUM(‘X’,‘Y’,‘Z’)
SET	与 ENUM 类似，SET 最多只能包含 64 个列表项，不过 SET 可存储一个以上的值。

Number 类型：

数据类型	描述
TINYINT(size)	-128 到 127 常规。0 到 255 无符号*。在括号中规定最大位数。
SMALLINT(size)	-32768 到 32767 常规。0 到 65535 无符号*。在括号中规定最大位数。
MEDIUMINT(size)	-8388608 到 8388607 普通。0 to 16777215 无符号*。在括号中规定最大位数。
INT(size)	-2147483648 到 2147483647 常规。0 到 4294967295 无符号*。在括号中规定最大位数。
BIGINT(size)	-9223372036854775808 到 9223372036854775807 常规。0 到 18446744073709551615 无符号*。在括号中规定最大位数。
FLOAT(size,d)	带有浮动小数点的小数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。
DOUBLE(size,d)	带有浮动小数点的大数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。
DECIMAL(size,d)	作为字符串存储的 DOUBLE 类型，允许固定的小数点。

* 这些整数类型拥有额外的选项 UNSIGNED。通常，整数可以是负数或正数。如果添加 UNSIGNED 属性，那么范围将从 0 开始，而不是某个负数。

Date 类型：

数据类型	描述
DATE()	日期。格式：YYYY-MM-DD注释：支持的范围是从 ‘1000-01-01’ 到 ‘9999-12-31’
DATETIME()	*日期和时间的组合。格式：YYYY-MM-DD HH:MM:SS注释：支持的范围是从 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’
TIMESTAMP()	*时间戳。TIMESTAMP 值使用 Unix 纪元(‘1970-01-01 00:00:00’ UTC) 至今的描述来存储。格式：YYYY-MM-DD HH:MM:SS注释：支持的范围是从 ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-09 03:14:07’ UTC
TIME()	时间。格式：HH:MM:SS 注释：支持的范围是从 ‘-838:59:59’ 到 ‘838:59:59’
YEAR()	2 位或 4 位格式的年。注释：4 位格式所允许的值：1901 到 2155。2 位格式所允许的值：70 到 69，表示从 1970 到 2069。

* 即便 DATETIME 和 TIMESTAMP 返回相同的格式，它们的工作方式很不同。在 INSERT 或 UPDATE 查询中，TIMESTAMP 自动把自身设置为当前的日期和时间。TIMESTAMP 也接受不同的格式，比如 YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD 或 YYMMDD。

---

基本使用

SQL 约束 (Constraints)

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 约束

创建表的时候

单个约束

mysql写到最后面，SQL server 在字段后面加上 unique即可

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)
)

多个约束，顺便起个总名 (数据库通用)

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

创建表 之后

mysql sqlserver 通用

alter table person add unique (year);

# 重命名为uc_PersonID
ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)

删除 unique

mysql

ALTER TABLE Persons DROP INDEX year;

ALTER TABLE Persons DROP INDEX uc_PersonID

SQL Server / Oracle / MS Access:

ALTER TABLE Persons DROP CONSTRAINT uc_PersonID

PRIMARY KEY

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)
)

ALTER TABLE Persons ADD PRIMARY KEY (Id_P);
ALTER TABLE Persons DROP PRIMARY KEY

FOREIGN 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)
)

ALTER TABLE Orders ADD FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
ALTER TABLE Orders DROP FOREIGN KEY Id_P

CHECK 约束

CHECK 约束用于限制列中的值的范围。

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

ALTER TABLE Persons ADD CHECK (Id_P>0)
ALTER TABLE Persons DROP CHECK chk_Person

DEFAULT

CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes',
addDate date DEFAULT GETDATE()
)

ALTER TABLE Persons ALTER City SET DEFAULT 'SANDNES'
ALTER TABLE Persons ALTER City DROP DEFAULT

建立测试表

create database testdb;
use testdb;
create table `person`
(
  `id`        int(11)     not null auto_increment,
  `firstname` varchar(20) not null,
  `lastname`  varchar(20) not null,
  `address`   varchar(80) not null,
  `city`      varchar(10) not null,
  `year` int(4) not null ,
  primary key (`id`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 10000
  DEFAULT CHARSET = utf8
  COMMENT ='用户信息';

# truncate person;

insert into person (firstname, lastname, address, city, year)
values ('Adams', 'John', 'Oxford Street', 'London',1960);

insert into person (firstname, lastname, address, city, year)
values ('Bush', 'George', 'Fifth Avenue', 'New York',1980);

insert into person (firstname, lastname, address, city, year)
values ('Carter', 'Thomas', 'Changan Street', 'Beijing',2001);

insert into person (firstname, lastname, address, city, year)
values ('bob', 'liu', 'Oxford Street', 'Beijing',1895);

select

SELECT COUNT(*) FROM Person;  # 返回记录数量

base

select * from person;
select lastname,address from person;
select distinct city from person;
select  distinct city,address from person;

where

select * from person where city='Beijing';
select * from person where year > 1900;

and or

select * from person where city = 'beijing' and address = 'Changan Street';

括号

select * from person where city = 'London' or (year >= 1980 and year <= 2000);

between

select * from person where city = 'London' or year between 1980 and 2000;

order by

默认升序，desc降序

select * from person order by year desc ;

update

update person set lastname='Liu',year=1895 where year=1896;

insert

insert into person (firstname, lastname, address, city, year)
values ('bob', 'liu', 'Oxford Street', 'Beijing',1895);

delete

删除某行

DELETE FROM Person WHERE city = 'London' 

删除全部数据

类似 truncate table_name

DELETE * FROM table_name

alter

# 添加列
ALTER TABLE Persons ADD Birthday date
# 改变列
ALTER TABLE Persons ALTER COLUMN Birthday year
# 删除列
ALTER TABLE Person DROP COLUMN Birthday

基础方法

top 限定数量

select * from person limit 2;

like 模糊查询

%是通配符，表示任意字符

select * from person where city like 'N%';
select * from person where city like '%N%';

select * from person where city not like 'N%';

通配符

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

in ( where )

select *
from person where city in ('London','New York');

between

select * from person where city = 'London' or year between 1980 and 2000;

index索引

加快速高效地查询数据。

有索引的表 更新时候 较慢。

# 索引
CREATE INDEX index_name ON table_name (column_name)
# 唯一索引
CREATE UNIQUE INDEX index_name ON table_name (column_name)

ALTER TABLE table_name DROP INDEX index_name

VIEW（视图）

用到再去看

Date 函数

最常用

函数	描述	对应的数据类型
NOW()	返回当前的日期和时间 2019-02-13 13:11:51	datetime
CURDATE()	返回当前的日期 2019-02-13	date
CURTIME()	返回当前的时间 13:11:17	time

CREATE TABLE Orders 
(
……
# 向表中插入行时，当前日期和时间自动插入列中。
#  2019-02-13 13:11:51 
OrderDate datetime NOT NULL DEFAULT NOW(),
……
)

使用时间部分 会很复杂

设我们有下面这个 “Orders” 表：

OrderId	ProductName	OrderDate
1	computer	2008-12-26
2	printer	2008-12-26
3	electrograph	2008-11-12
4	telephone	2008-10-19

现在，我们希望从上表中选取 OrderDate 为 “2008-12-26” 的记录。

我们使用如下 SELECT 语句：

SELECT * FROM Orders WHERE OrderDate='2008-12-26'

MySQL Date 函数

下面的表格列出了 MySQL 中最重要的内建日期函数：

函数	描述
NOW()	返回当前的日期和时间 2019-02-13 13:11:51 对应datetime类型
CURDATE()	返回当前的日期 2019-02-13 对应date类型
CURTIME()	返回当前的时间 13:11:17 对应time类型
DATE()	提取日期或日期/时间表达式的日期部分
EXTRACT()	返回日期/时间按的单独部分
DATE_ADD()	给日期添加指定的时间间隔
DATE_SUB()	从日期减去指定的时间间隔
DATEDIFF()	返回两个日期之间的天数
DATE_FORMAT()	用不同的格式显示日期/时间

null

SELECT LastName,FirstName,Address FROM Person
WHERE Address IS NULL

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