文章目录
常用的数据类型
数据类型 | 描述 |
---|---|
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