MySQL数据库表约束、索引、表操作及部分常见条件运算符

一.表的约束：避免输入错误的数据，保证数据完整性

1.主键约束：主键默认是not null unique

单列主键：主键在当前列的设置后面添加primary key（只有一个，非空），例如：id int primary key。

多列复合主键：一般都是在建表语句最后面添加primary key(列名1,列名2,...)。

例如：

use firstdb;
DROP TABLE if EXISTS users;
CREATE TABLE users(
id INT,
name VARCHAR(20),
sex enum('男','女') NOT NULL,

-- 创建复合主键
primary key(id,name)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

注意：数据库中每张表只能有一个主键约束（只有一个主键约束不代表受主键约束的列只有一列，可以有多列，如复合主键），主键列默认是not null unique。

2.非空约束：not null

3.外键约束：foreign key

4.唯一约束：unique（可以有多个，不一定非空，当前约束列不能出现相同数据）

5.默认约束：default

例如：

use firstdb;

DROP TABLE if EXISTS users;

CREATE TABLE users(

--设置单列主键，自动增长

id INT PRIMARY KEY auto_increment,

name VARCHAR(20) NOT NULL,

sex enum('男','女') NOT NULL,

age int default 18

)ENGINE=INNODB DEFAULT CHARSET=utf8; 

6.设置自动增长：auto_increment

二.索引：提高数据的查询和排序效率

1.创建索引

语法：

create table 表名

(

列名 数据类型 [约束]，

… …

列名 数据类型[约束],

[unique|fulltext|spatial] index|key [别名](列名[asc|desc])

)

unique：唯一索引，要求索引列的值必须唯一。

fulltext：全文索引，只能创建在使用char，varchar或text的列上。

spatial：空间索引，只能使用在非空的并且使用空间数据类型的列上。

index|key：都一样，二选一。

别名：自定义的索引名

asc|dsc：表示升序（asc）和降序（desc）

如：

普通索引：

use firstdb;
DROP TABLE if EXISTS users;
CREATE TABLE users

(

id INT PRIMARY KEY auto_increment,

name VARCHAR(20) NOT NULL,

sex enum('男','女') NOT NULL,

age int default 18,

--创建普通索引

index(id)

)ENGINE=INNODB DEFAULT CHARSET=utf8;

唯一索引：

use firstdb;

DROP TABLE if EXISTS users;

CREATE TABLE users

(

id INT PRIMARY KEY auto_increment,

name VARCHAR(20) NOT NULL,

sex enum('男','女') NOT NULL,

age int default 18,

--创建唯一索引

unique key unique_id(id asc)

)ENGINE=INNODB DEFAULT CHARSET=utf8;

索引和约束的区别：索引是为了提高查询和排序效率，约束是为了避免输入错误的数据，保证数据完整性。但是有特殊的地方：唯一约束和唯一索引共通，创建唯一索引时，唯一约束也同时创建，创建唯一约束时，唯一索引也同时创建。

另：使用create index给已经存在的表上添加索引：

create [unique|fulltext|spatial] index [别名] on 表名(列名 [asc|desc]);

2.删除索引

alter table 表名 drop index 索引名;

或者

drop index 索引名 on 表名;

注意：索引会消耗磁盘空间，需要及时删除不使用的索引。

三.表操作：

1.添加数据：

insert into 表名(列名1,列名2,...) values(值1,值2,...);或者insert into 表名set列名1=值1,列名2=值2,…

如果没有显式的指明列名，那么就按照创建表的列顺序添加数据。

向有自动增长列的表中插入数据：加入第一列为id自动增长：

INSERT INTO users VALUES(0或NULL或default,值2,...);

同时添加多条数据：insert into表名([列名列表]) values(值列表1),(值列表2),…;

2.更新数据：

update 表名 set 列名1=值1，列名2= 值2,...[where 条件表达式];

后面不跟着where条件表示修改所有数据。

3.删除数据：

delete from 表名[where 条件表达式];

后面不跟着where条件表示删除整个表的所有数据；

4.单表查询：

select [distinct] *|列名1，列名2,...from 表名

[where 条件表达式]

[group by 列名[having条件表达式]] 分组

[order by 列名 [asc|desc]]排序

[特殊函数，limit] 相当于sql server的top函数

注：distinct：去掉重复，如果多个同时去重复，那么必须多列全部一致才有效。

另：使用like进行模糊查询：

select * from 表名 where 列名[not] like '匹配字符串';

常用通配符：%匹配任意长度的字符串，_(下划线)匹配单个字符。

如：select * from users where name like '张%';

四.条件运算符：

关系运算符：> < = !=(<>不等于)

between...and：表示在某区间，如[1,3]包括1和3

and：表示并且

or：表示或者

in：在集合中

not int：表示否（不在集合中，取反）

is NULL：表示空值