07.sql语句-索引+外键+视图+字段约束+清除表中记录+主键

字段约束

字段约束符也称字段修饰符。

NULL

创建表时，默认NULL。

create table test(id int(5), name varchar(20));

MariaDB [books]> create table test(id int(5), name varchar(20));

Query OK, 0 rows affected (0.01 sec)

desc test;

MariaDB [books]> desc test;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| id | int(5) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

create table name(id int(5) null, name varchar(20) null);

MariaDB [books]> create table name(id int(5) null, name varchar(20) null);

Query OK, 0 rows affected (0.00 sec)

desc name;

MariaDB [books]> desc name;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| id | int(5) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

NOT NULL

定义字段不可以为NULL，但可以为空（' ’）

语法：create table 表名(字段名1 字段类型 not null,字段名2 字段类型 not null,……);

create table jobs(id int(3) not null, name varchar(8) not null, jobs varchar(20) not null);

MariaDB [books]> create table jobs(id int(3) not null, name varchar(8) not null, jobs varchar(20) not null);

Query OK, 0 rows affected (0.00 sec)

desc jobs;

MariaDB [books]> desc jobs;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| id | int(3) | NO | | NULL | |

+-------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

空和NULL的对比

空值在数据库中不占用空间；

NULL在数据库中占用空间；

NOT NULL比NULL效率高

NULL其实并不是空值，而是占用空间的。在mysql进行比较时，NULL会参与字段的比较，所以会影响一部分效率。

而且索引时不会存储NULL值，所以索引的字段为NULL时，索引效率会下降很多。

所以NOT NULL要比NULL效率高。

判断字段不为空（NULL和’ ’）

create table dream(colors varchar(18) not null, name varchar(18) null)ENGINE=MyISAM;

MariaDB [books]> create table dream(colors varchar(18) not null, name varchar(18) null)ENGINE=MyISAM;

Query OK, 0 rows affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

insert into dream values('1','2');

MariaDB [books]> insert into dream values('1','2');

Query OK, 1 row affected (0.00 sec)

insert into dream values('','1');

MariaDB [books]> insert into dream values('','1');

Query OK, 1 row affected (0.00 sec)

select * from dream;

MariaDB [books]> select * from dream;

+--------+------+

| colors | name |

+--------+------+

| | NULL |

| 1 | 2 |

| | 1 |

+--------+------+

3 rows in set (0.00 sec)

select * from dream where colors is not null;

MariaDB [books]> select * from dream where colors is not null;

+--------+------+

| colors | name |

+--------+------+

| | NULL |

| 1 | 2 |

| | 1 |

+--------+------+

3 rows in set (0.00 sec)

select * from dream where colors <> '';

MariaDB [books]> select * from dream where colors <> '';

+--------+------+

| colors | name |

+--------+------+

| 1 | 2 |

+--------+------+

1 row in set (0.00 sec)

DEFAULT

为字段指定默认值。

create table userinfo(id int(5), name varchar(18) not null, gender varchar(12) default 'nan');

MariaDB [books]> create table userinfo(id int(5), name varchar(18) not null, gender varchar(12) default 'nan');

Query OK, 0 rows affected (0.00 sec)

desc userinfo;

MariaDB [books]> desc userinfo;

+--------+-------------+------+-----+---------+-------+

+--------+-------------+------+-----+---------+-------+

| id | int(5) | YES | | NULL | |

| gender | varchar(12) | YES | | nan | |

+--------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

insert into userinfo (id,name) values(1,'wangbin');字段使用默认值。

MariaDB [books]> insert into userinfo (id,name) values(1,'wangbin');

Query OK, 1 row affected (0.08 sec)

select * from userinfo;

MariaDB [books]> select * from userinfo;

+------+---------+--------+

| id | name | gender |

+------+---------+--------+

| 1 | wangbin | nan |

+------+---------+--------+

1 row in set (0.00 sec)

insert into userinfo values(1,'changmengka','nv');字段不适用默认值。

MariaDB [books]> insert into userinfo values(1,'changmengka','nv');

Query OK, 1 row affected (0.00 sec)

select * from userinfo;

MariaDB [books]> select * from userinfo;

+------+-------------+--------+

| id | name | gender |

+------+-------------+--------+

| 1 | wangbin | nan |

| 1 | changmengka | nv |

+------+-------------+--------+

2 rows in set (0.00 sec)

DEFAULT总结

如果字段没有设定default，mysql依据这个字段时null还是not null，如果是null则为null；如果是not null则报错。

如果时间字段，插入0时，为当前默认时间。

如果是enum类型，默认为第一个元素。enum表示：限定值的取值范围。

AUTO_INCREMENT

自动增加、增长。只能修改时int字段。

表名mysql应该自动为该字段生成一个唯一没有用过的数值（每次在最大ID值得基础上加1。）对于主键，这是非常有用的，可以为每条记录创建一个唯一的标识符。

create table auto(id int(5) not null auto_increment primary key, name varchar(20) not null, age int(5) not null);

MariaDB [books]> create table auto(id int(5) not null auto_increment primary key, name varchar(20) not null, age int(5) not null);

Query OK, 0 rows affected (0.08 sec)

MariaDB [books]> desc auto;

+-------+-------------+------+-----+---------+----------------+

+-------+-------------+------+-----+---------+----------------+

| id | int(5) | NO | PRI | NULL | auto_increment |

| age | int(5) | NO | | NULL | |

+-------+-------------+------+-----+---------+----------------+

3 rows in set (0.00 sec)

insert into auto (name,age) values('wangbin',27);自动增长

MariaDB [books]> insert into auto (name,age) values('wangbin',27);

Query OK, 1 row affected (0.00 sec)

select * from auto;

MariaDB [books]> select * from auto;

+----+---------+-----+

| id | name | age |

+----+---------+-----+

| 1 | wangbin | 27 |

+----+---------+-----+

1 row in set (0.00 sec)

insert into auto (name,age) values('changmengka',26);自动增长。

MariaDB [books]> insert into auto (name,age) values('changmengka',26);

Query OK, 1 row affected (0.00 sec)

select * from auto;

MariaDB [books]> select * from auto;

+----+-------------+-----+

| id | name | age |

+----+-------------+-----+

| 1 | wangbin | 27 |

| 2 | changmengka | 26 |

+----+-------------+-----+

2 rows in set (0.00 sec)

insert into auto values(10,'liuzhiwei',26);指定值

MariaDB [books]> insert into auto values(10,'liuzhiwei',26);

Query OK, 1 row affected (0.01 sec)

select * from auto;

MariaDB [books]> select * from auto;

+----+-------------+-----+

| id | name | age |

+----+-------------+-----+

| 1 | wangbin | 27 |

| 2 | changmengka | 26 |

| 10 | liuzhiwei | 26 |

+----+-------------+-----+

3 rows in set (0.00 sec)

insert into auto (name,age) values('wangting',25);自动增长

MariaDB [books]> insert into auto (name,age) values('wangting',25);

Query OK, 1 row affected (0.00 sec)

select * from auto;

MariaDB [books]> select * from auto;

+----+-------------+-----+

| id | name | age |

+----+-------------+-----+

| 1 | wangbin | 27 |

| 2 | changmengka | 26 |

| 10 | liuzhiwei | 26 |

| 11 | wangting | 25 |

+----+-------------+-----+

4 rows in set (0.01 sec)

清除表中记录

清空表中所有记录。

不清空auto_increment

delete不加where条件，情况所有表记录。但是delete不会清零auto_increment值。

delete from auto;清空记录

MariaDB [books]> delete from auto;

Query OK, 4 rows affected (0.00 sec)

insert into auto (name,age) values('weiyapeng',28);自动增加

MariaDB [books]> insert into auto (name,age) values('weiyapeng',28);

Query OK, 1 row affected (0.00 sec)

select * from auto;

MariaDB [books]> select * from auto;

+----+-----------+-----+

| id | name | age |

+----+-----------+-----+

| 12 | weiyapeng | 28 |

+----+-----------+-----+

1 row in set (0.00 sec)

清空auto_increment

删除表中所有记录，并清空auto_increment值。新插入的记录从1开始。

语法：truncate table 表名;

truncate table auto;

MariaDB [books]> truncate table auto;

Query OK, 0 rows affected (0.00 sec)

insert into auto (name,age) values('weifangchao',27);自动增长

MariaDB [books]> insert into auto (name,age) values('weifangchao',27);

Query OK, 1 row affected (0.00 sec)

select * from auto;

MariaDB [books]> select * from auto;

+----+-------------+-----+

| id | name | age |

+----+-------------+-----+

| 1 | weifangchao | 27 |

+----+-------------+-----+

1 row in set (0.00 sec)

索引

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

优点：为了加快搜索速度，减少查询时间。

缺点：

1、索引是以文件存储的。如果索引过多，占磁盘空间较大。而且他影响： insert ,update ,delete 执行时间。

2、索引中数据必须与数据表数据同步：如果索引过多，当表中数据更新的时候后，索引也要同步更新，这就降低了效率。

普通索引（MUL）

最基本的索引，不具备唯一性，但是可以加快查询速度。（一个表中可以创建多个索引）

语法：create table 表名(字段名1 字段类型,字段名2 字段类型,……,index 索引名 (字段名),index(索引名 (字段名));

注：可以使用key，也可以使用index 。两者用法相同，功能相同。

index 索引名称（字段），索引名称，可以加也可以不加，不加使用字段名作为索引名。

create table pu_tong(id int(5) not null auto_increment primary key, name varchar(20), age int(5), index (id),index (name));创建index普通索引并使用字段名为索引名。

MariaDB [books]> create table pu_tong(id int(5) not null auto_increment primary key, name varchar(20), age int(5), index (id),index (name));

Query OK, 0 rows affected (0.00 sec)

desc pu_tong;

MariaDB [books]> desc pu_tong;

+-------+-------------+------+-----+---------+----------------+

+-------+-------------+------+-----+---------+----------------+

| id | int(5) | NO | PRI | NULL | auto_increment |

| age | int(5) | YES | | NULL | |

+-------+-------------+------+-----+---------+----------------+

3 rows in set (0.00 sec)

create table indexs(id int(5) not null auto_increment primary key, name varchar(20), age int(5), index index_name (name), index index_age (age));创建index普通索引并创建索引名。

MariaDB [books]> create table indexs(id int(5) not null auto_increment primary key, name varchar(20), age int(5), index index_name (name), index index_age (age));

Query OK, 0 rows affected (0.00 sec)

desc indexs;

MariaDB [books]> desc indexs;

+-------+-------------+------+-----+---------+----------------+

+-------+-------------+------+-----+---------+----------------+

| id | int(5) | NO | PRI | NULL | auto_increment |

| age | int(5) | YES | MUL | NULL | |

+-------+-------------+------+-----+---------+----------------+

3 rows in set (0.00 sec)

create table keys_test(id int(4) not null auto_increment primary key, name varchar(12),age int(4), key (name), key (age)); 创建key普通索引并使用字段名为索引名。

MariaDB [books]> create table keys_test(id int(4) not null auto_increment primary key, name varchar(12),age int(4), key (name), key (age));

Query OK, 0 rows affected (0.01 sec)

show create table keys_test \G

MariaDB [books]> show create table keys_test \G

*************************** 1. row ***************************

Table: keys_test

Create Table: CREATE TABLE `keys_test` (

`id` int(4) NOT NULL AUTO_INCREMENT,

`name` varchar(12) DEFAULT NULL,

`age` int(4) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `name` (`name`),

KEY `age` (`age`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

create table keys_name(id int(5) not null auto_increment primary key, name varchar(12), age int(5), key key_name (name), key key_age (age)); 创建index普通索引并创建索引名。

MariaDB [books]> create table keys_name(id int(5) not null auto_increment primary key, name varchar(12), age int(5), key key_name (name), key key_age (age));

Query OK, 0 rows affected (0.00 sec)

show create table keys_name \G

MariaDB [books]> show create table keys_name \G

*************************** 1. row ***************************

Table: keys_name

Create Table: CREATE TABLE `keys_name` (

`id` int(5) NOT NULL AUTO_INCREMENT,

`name` varchar(12) DEFAULT NULL,

`age` int(5) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `key_name` (`name`),

KEY `key_age` (`age`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

唯一索引（UNI）

与普通索引几把相同。唯一区别：索引列的所有值只能出现一次，即必须唯一。常用来约束字段内容。其唯一性允许有null值。

语法：create table 表名(字段名1 字段类型,字段名2 字段类型,……,unique key 索引名 (字段名),unique key 索引名 (字段名));

注意：常用在值不能重复的字段上，比如说用户名，电话号码，身份证号。

只能使用unique key，而不能使用unique index。

create table cmk(id int(3) not null auto_increment primary key, name varchar(12), age int(5), unique key (name), unique key (age)); 创建unique key唯一索引并使用字段名为索引名。

MariaDB [books]> create table cmk(id int(3) not null auto_increment primary key, name varchar(12), age int(5), unique key (name), unique key (age));

Query OK, 0 rows affected (0.00 sec)

show create table cmk \G

MariaDB [books]> show create table cmk \G

*************************** 1. row ***************************

Table: cmk

Create Table: CREATE TABLE `cmk` (

`id` int(3) NOT NULL AUTO_INCREMENT,

`name` varchar(12) DEFAULT NULL,

`age` int(5) DEFAULT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `name` (`name`),

UNIQUE KEY `age` (`age`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

create table wang(id int(3) not null auto_increment primary key, name varchar(12), age int(5), unique key key_name (name), unique key key_age(age)); 创建unique key唯一索引并创建索引名。

MariaDB [books]> create table wang(id int(3) not null auto_increment primary key, name varchar(12), age int(5), unique key key_name (name), unique key key_age(age));

Query OK, 0 rows affected (0.00 sec)

show create table wang \G

MariaDB [books]> show create table wang \G

*************************** 1. row ***************************

Table: wang

Create Table: CREATE TABLE `wang` (

`id` int(3) NOT NULL AUTO_INCREMENT,

`name` varchar(12) DEFAULT NULL,

`age` int(5) DEFAULT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `key_name` (`name`),

UNIQUE KEY `key_age` (`age`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

主键索引（PRI）

查询数据库，按主键查询时最快的，每个表只能由一个主键列，可以有多个普通索引列。主键索引要求该列所有内容必须唯一，且不能为空！

语法：create table 表名(字段名1 字段类型,字段名2 字段类型,……,primary key 索引名 (字段名),primary key 索引名 (字段名));

总结：主键索引，唯一性索引区别：主键索引不能有NULL，唯一性索引可以有空值

create table dodo(id int(4) not null auto_increment,name varchar(20), age int(12) default null, primary key (id));

MariaDB [books]> create table dodo(id int(4) not null auto_increment,name varchar(20), age int(12) default null, primary key (id));

Query OK, 0 rows affected (0.00 sec)

show create table dodo \G

MariaDB [books]> show create table dodo \G

*************************** 1. row ***************************

Table: dodo

Create Table: CREATE TABLE `dodo` (

`id` int(4) NOT NULL AUTO_INCREMENT,

`name` varchar(20) DEFAULT NULL,

`age` int(12) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

desc dodo;

MariaDB [books]> desc dodo;

+-------+-------------+------+-----+---------+----------------+

+-------+-------------+------+-----+---------+----------------+

| id | int(4) | NO | PRI | NULL | auto_increment |

| age | int(12) | YES | | NULL | |

+-------+-------------+------+-----+---------+----------------+

3 rows in set (0.01 sec)

create table toto(id int(4) not null auto_increment, name varchar(20), age int(12), primary key id_key(id));

MariaDB [books]> create table toto(id int(4) not null auto_increment, name varchar(20), age int(12), primary key id_key(id));

Query OK, 0 rows affected (0.08 sec)

desc toto;

MariaDB [books]> desc toto;

+-------+-------------+------+-----+---------+----------------+

+-------+-------------+------+-----+---------+----------------+

| id | int(4) | NO | PRI | NULL | auto_increment |

| age | int(12) | YES | | NULL | |

+-------+-------------+------+-----+---------+----------------+

3 rows in set (0.00 sec)

show create table toto \G

MariaDB [books]> show create table toto \G

*************************** 1. row ***************************

Table: toto

Create Table: CREATE TABLE `toto` (

`id` int(4) NOT NULL AUTO_INCREMENT,

`name` varchar(20) DEFAULT NULL,

`age` int(12) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

复合索引（联合主键）

复合索引可以包含一个、两个或更多个列（字段）。两个或更多个列（字段）上的索引称作复合索引。复合索引页成联合主键！！！

语法：create table 表名(字段名1 字段类型,字段名2 字段类型,……,primary key 索引名 (字段名1,字段名2));

总结：

create table firewall(host varchar(15) not null, port varchar(5) not null, hostname varchar(12) not null, primary key(host,port));

MariaDB [books]> create table firewall(host varchar(15) not null, port varchar(5) not null, hostname varchar(12) not null, primary key(host,port));

Query OK, 0 rows affected (0.00 sec)

desc firewall;

MariaDB [books]> desc firewall;

+----------+-------------+------+-----+---------+-------+

+----------+-------------+------+-----+---------+-------+

+----------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

show create table firewall \G

MariaDB [books]> show create table firewall \G

*************************** 1. row ***************************

Table: firewall

Create Table: CREATE TABLE `firewall` (

`host` varchar(15) NOT NULL,

`port` varchar(5) NOT NULL,

`hostname` varchar(12) NOT NULL,

PRIMARY KEY (`host`,`port`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

create table fire(id int(5) not null, name varchar(15), gender enum('M','W'), primary key id_name_key(id,name));

MariaDB [books]> create table fire(id int(5) not null, name varchar(15), gender enum('M','W'), primary key id_name_key(id,name));

Query OK, 0 rows affected (0.01 sec)

desc fire;

MariaDB [books]> desc fire;

+--------+---------------+------+-----+---------+-------+

+--------+---------------+------+-----+---------+-------+

| id | int(5) | NO | PRI | NULL | |

| name | varchar(15) | NO | PRI | | |

+--------+---------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

show create table fire \G

MariaDB [books]> show create table fire \G

*************************** 1. row ***************************

Table: fire

Create Table: CREATE TABLE `fire` (

`id` int(5) NOT NULL,

`name` varchar(15) NOT NULL DEFAULT '',

`gender` enum('M','W') DEFAULT NULL,

PRIMARY KEY (`id`,`name`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

全文索引（全文检索）

全文索引也称为全文检索。是目前搜索引擎使用的一种关键技术。他能够利用分词技术等多种算法智能分析出文本文字中关键字的频率以及重要性，然后按照一定的算法锤子智能的删选出我们想要的搜索结果。

全文检索只能用于varchar text类型中。（

mysql中从3.2磅开始支持全文检索，但不支持中文检索；

从mysql5.7.6开始mysql中内置了ngram全文检索插件，用于支持中文检索。）

强烈注意：MySQL自带的全文索引只能用于数据库引擎为MyISAM的数据表，如果是其他数据引擎，则全文索引不会生效。

一般交给第三方软件进行全文索引

语法：create table 表名(字段名1 字段类型,字段名2 字段类型,……,primary key 索引名 (字段名),fulltext key 索引名 (字段名))ENGINE=MyISAM;

create table wall(id int(5) primary key not null auto_increment, name text(20), gender enum('W','M'), fulltext key (name))ENGINE=MyISAM;

MariaDB [books]> create table wall(id int(5) primary key not null auto_increment, name text(20), gender enum('W','M'), fulltext key (name))ENGINE=MyISAM;

Query OK, 0 rows affected (0.01 sec)

desc wall;

MariaDB [books]> desc wall;

+--------+---------------+------+-----+---------+----------------+

+--------+---------------+------+-----+---------+----------------+

| id | int(5) | NO | PRI | NULL | auto_increment |

+--------+---------------+------+-----+---------+----------------+

3 rows in set (0.00 sec)

show create table wall \G

MariaDB [books]> show create table wall \G

*************************** 1. row ***************************

Table: wall

Create Table: CREATE TABLE `wall` (

`id` int(5) NOT NULL AUTO_INCREMENT,

`name` tinytext,

`gender` enum('W','M') DEFAULT NULL,

PRIMARY KEY (`id`),

FULLTEXT KEY `name` (`name`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

create table wall_test(id int(5) primary key not null auto_increment, name text(20), gender enum('W','M'), fulltext key fulltest_key_name (name))ENGINE=MyISAM;

MariaDB [books]> create table wall_test(id int(5) primary key not null auto_increment, name text(20), gender enum('W','M'), fulltext key fulltest_key_name (name))ENGINE=MyISAM;

Query OK, 0 rows affected (0.01 sec)

desc wall_test;

MariaDB [books]> desc wall_test;

+--------+---------------+------+-----+---------+----------------+

+--------+---------------+------+-----+---------+----------------+

| id | int(5) | NO | PRI | NULL | auto_increment |

+--------+---------------+------+-----+---------+----------------+

3 rows in set (0.00 sec)

show create table wall_test \G

MariaDB [books]> show create table wall_test \G

*************************** 1. row ***************************

Table: wall_test

Create Table: CREATE TABLE `wall_test` (

`id` int(5) NOT NULL AUTO_INCREMENT,

`name` tinytext,

`gender` enum('W','M') DEFAULT NULL,

PRIMARY KEY (`id`),

FULLTEXT KEY `fulltest_key_name` (`name`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

修改索引

当表创建完成后，可以通过修改表索引的方式为表添加索引，或为表修改索引。

普通索引

语法：alter table 表名 add index/key 索引名 (字段名);

desc info;

MariaDB [books]> desc info;

+---------+----------+------+-----+---------+-------+

+---------+----------+------+-----+---------+-------+

| id | int(5) | YES | | NULL | |

| pace | int(3) | YES | | NULL | |

| lid | int(5) | YES | | NULL | |

+---------+----------+------+-----+---------+-------+

5 rows in set (0.00 sec)

alter table info add index iname_index (iname,pace);修改方式添加带有索引名的index普通索引

MariaDB [books]> alter table info add index iname_index (iname,pace);

Query OK, 0 rows affected (0.11 sec)

Records: 0 Duplicates: 0 Warnings: 0

desc info;

MariaDB [books]> desc info;

+---------+----------+------+-----+---------+-------+

+---------+----------+------+-----+---------+-------+

| id | int(5) | YES | | NULL | |

| pace | int(3) | YES | | NULL | |

| lid | int(5) | YES | | NULL | |

+---------+----------+------+-----+---------+-------+

5 rows in set (0.00 sec)

alter table info add key lid_key (lid); 修改方式添加key普通索引

MariaDB [books]> alter table info add key lid_key (lid);

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

Database changed

desc info;

MariaDB [books]> desc info;

+---------+----------+------+-----+---------+-------+

+---------+----------+------+-----+---------+-------+

| id | int(5) | YES | | NULL | |

| pace | int(3) | YES | | NULL | |

| lid | int(5) | YES | MUL | NULL | |

+---------+----------+------+-----+---------+-------+

5 rows in set (0.00 sec)

唯一索引

语法：alter table 表名 add unique key 索引名 (字段名);

desc user;

MariaDB [books]> desc user;

+---------+----------+------+-----+---------+-------+

+---------+----------+------+-----+---------+-------+

| uid | int(5) | YES | | NULL | |

| gender | int(2) | YES | | NULL | |

| age | int(10) | YES | | NULL | |

+---------+----------+------+-----+---------+-------+

5 rows in set (0.00 sec)

alter table user add unique key (uname,gender); 修改方式添加unique key唯一索引

MariaDB [books]> alter table user add unique key (uname,gender);

Query OK, 0 rows affected (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 0

desc user;

MariaDB [books]> desc user;

+---------+----------+------+-----+---------+-------+

+---------+----------+------+-----+---------+-------+

| uid | int(5) | YES | | NULL | |

| gender | int(2) | YES | | NULL | |

| age | int(10) | YES | | NULL | |

+---------+----------+------+-----+---------+-------+

5 rows in set (0.00 sec)

alter table user add unique key uname_key (uname,gender); 修改方式添加带有索引名的unique key唯一索引

MariaDB [books]> alter table user add unique key uname_key (uname,gender);

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

主键索引

不建议使用修改方式添加主键索引。

全文索引

show create table fulltext_test \G

MariaDB [books]> show create table fulltext_test \G

*************************** 1. row ***************************

Table: fulltext_test

Create Table: CREATE TABLE `fulltext_test` (

`id` int(2) DEFAULT NULL,

`name` varchar(20) DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

alter table fulltext_test add fulltext text_index (name);带索引名的全文索引

MariaDB [books]> alter table fulltext_test add fulltext text_index (name);

Query OK, 0 rows affected (0.00 sec)

Records: 0 Duplicates: 0 Warnings: 0

desc fulltext_test;

MariaDB [books]> desc fulltext_test;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| id | int(2) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

show create table fulltext_test \G

MariaDB [books]> show create table fulltext_test \G

*************************** 1. row ***************************

Table: fulltext_test

Create Table: CREATE TABLE `fulltext_test` (

`id` int(2) DEFAULT NULL,

`name` varchar(20) DEFAULT NULL,

FULLTEXT KEY `text_index` (`name`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

索引删除与取消

取消自增长auto_increment

语法：alter table 表名 change 原字段名新字段名新字段类型 not null;

desc toto;

MariaDB [books]> desc toto;

+-------+-------------+------+-----+---------+----------------+

+-------+-------------+------+-----+---------+----------------+

| id | int(4) | NO | PRI | NULL | auto_increment |

| age | int(12) | YES | | NULL | |

+-------+-------------+------+-----+---------+----------------+

3 rows in set (0.00 sec)

alter table toto change id id int(4) not null;

MariaDB [books]> alter table toto change id id int(4) not null;

Query OK, 0 rows affected (0.00 sec)

Records: 0 Duplicates: 0 Warnings: 0

desc toto;

MariaDB [books]> desc toto;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| id | int(4) | NO | PRI | NULL | |

| age | int(12) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

删除主键primary key

语法：alter table 表名 drop primary key;

desc toto;

MariaDB [books]> desc toto;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| id | int(4) | NO | PRI | NULL | |

| age | int(12) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

alter table toto drop primary key;

MariaDB [books]> alter table toto drop primary key;

Query OK, 0 rows affected (0.00 sec)

Records: 0 Duplicates: 0 Warnings: 0

desc toto;

MariaDB [books]> desc toto;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| id | int(4) | NO | | NULL | |

| age | int(12) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

索引设计原则

1、 索引并非越多越好

2、 数据量不大不需要建立索引

3、 列中的值变化不多不需要建立索引 row id

4、 经常排序（order by 字段）和分组（group by 字段）的列需要建立索引

select a.bTypeId,(select b.bTypeName from category b where a.bTypeId = b.bTypeId) bn,count(*) from books a group by bTypeId;

5、 唯一性约束对应使用唯一性索引

Table (id pri,use,name index,pass)

外键约束

外键约束：foreign key就是表与表之间的某种约定的关系。由于这种关系的存在，表与表之间的数据更加完整，它们关联性更强。

外键，在每次插入或更新时，都会检查数据的完整性。

注意：on update cascade是级联更新的意思，on delete cascade是级联删除的意思，意思就是说当你更新或删除主键表，那外键表也会跟随一起更新或删除。

注：创建成功，必须满足以下4个条件：

1、确保参照的表和字段存在。

2、组成外键的字段被索引。

3、必须使用ENGINE指定存储引擎为：innodb.

4、外键字段和关联字段，数据类型必须一致。

当表名与数据库关键字相同时、当表名中有特殊字符时，需要将表名用反引号括起来（` `）

创建外键约束

确保参照的表user中id字段存在。组成外键的字段u_id被索引。必须使用type指定存储引擎为：innodb。

外键字段和关联字段，数据类型必须一致。

主键表语法：

create table `user`(id int(5) not null auto_increment, uname varchar(20) not null default 'name', gender enum('W','M') not null default 'W', age int(5), primary key(id))ENGINE=innodb;

关联表语法：

create table `class`(c_id int(5) auto_increment, u_id int(5) default '0', classname varchar(50), classnum int(10), primary key(c_id), index (u_id), foreign key class_key(u_id) references user(id) on delete cascade on update cascade) ENGINE=innodb;

创建用户主键表

create table `user`(id int(5) not null auto_increment, uname varchar(20) not null default 'name', gender enum('W','M') not null default 'W', age int(5), primary key(id))ENGINE=innodb;

MariaDB [forkey]> create table `user`(id int(5) not null auto_increment, uname varchar(20) not null default 'name', gender enum('W','M') not null default 'W', age int(5), primary key(id))ENGINE=innodb;

Query OK, 0 rows affected (0.00 sec)

show create table user \G

MariaDB [forkey]> show create table user \G

*************************** 1. row ***************************

Table: user

Create Table: CREATE TABLE `user` (

`id` int(5) NOT NULL AUTO_INCREMENT,

`uname` varchar(20) NOT NULL DEFAULT 'name',

`gender` enum('W','M') NOT NULL DEFAULT 'W',

`age` int(5) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

desc user;

MariaDB [forkey]> desc user;

+--------+---------------+------+-----+---------+----------------+

+--------+---------------+------+-----+---------+----------------+

| id | int(5) | NO | PRI | NULL | auto_increment |

| gender | enum('W','M') | NO | | W | |

| age | int(5) | YES | | NULL | |

+--------+---------------+------+-----+---------+----------------+

4 rows in set (0.00 sec)

创建班级关联表

create table `class`(c_id int(5) auto_increment, u_id int(5) default '0', classname varchar(50), classnum int(10), primary key(c_id), index (u_id), foreign key class_key(u_id) references user(id) on delete cascade on update cascade) ENGINE=innodb;

MariaDB [forkey]> create table `class`(c_id int(5) auto_increment, u_id int(5) default '0', classname varchar(50), classnum int(10), primary key(c_id), index (u_id), foreign key class_key(u_id) references user(id) on delete cascade on update cascade) ENGINE=innodb;

Query OK, 0 rows affected (0.00 sec)

show create table class \G

MariaDB [forkey]> show create table class \G

*************************** 1. row ***************************

Table: class

Create Table: CREATE TABLE `class` (

`c_id` int(5) NOT NULL AUTO_INCREMENT,

`u_id` int(5) DEFAULT '0',

`classname` varchar(50) DEFAULT NULL,

`classnum` int(10) DEFAULT NULL,

PRIMARY KEY (`c_id`),

KEY `u_id` (`u_id`),

CONSTRAINT `class_ibfk_1` FOREIGN KEY (`u_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

desc class;

MariaDB [forkey]> desc class;

+-----------+-------------+------+-----+---------+----------------+

+-----------+-------------+------+-----+---------+----------------+

| c_id | int(5) | NO | PRI | NULL | auto_increment |

| u_id | int(5) | YES | MUL | 0 | |

| classnum | int(10) | YES | | NULL | |

+-----------+-------------+------+-----+---------+----------------+

4 rows in set (0.00 sec)

插入测试数据

insert into user (uname,age) values('wangbin',27);

MariaDB [forkey]> insert into user (uname,age) values('wangbin',27);

Query OK, 1 row affected (0.00 sec)

insert into class (u_id,classname,classnum) values(1,'python',22);

MariaDB [forkey]> insert into class (u_id,classname,classnum) values(1,'python',22);

Query OK, 1 row affected (0.00 sec)

级联删除

select * from user;

MariaDB [forkey]> select * from user;

+----+-------------+--------+------+

| id | uname | gender | age |

+----+-------------+--------+------+

| 1 | wangbin | W | 27 |

| 2 | changmengka | W | 27 |

| 3 | chang | W | 27 |

+----+-------------+--------+------+

3 rows in set (0.00 sec)

select * from class;

MariaDB [forkey]> select * from class;

+------+------+-----------+----------+

| c_id | u_id | classname | classnum |

+------+------+-----------+----------+

| 1 | 1 | python | 22 |

| 2 | 2 | linux | 23 |

| 3 | 3 | mysql | 24 |

+------+------+-----------+----------+

3 rows in set (0.01 sec)

delete from user where id=1;

Query OK, 1 row affected (0.00 sec)

select * from user;

MariaDB [forkey]> select * from user;

+----+-------------+--------+------+

| id | uname | gender | age |

+----+-------------+--------+------+

| 2 | changmengka | W | 27 |

| 3 | chang | W | 27 |

+----+-------------+--------+------+

2 rows in set (0.01 sec)

select * from class;

MariaDB [forkey]> select * from class;

+------+------+-----------+----------+

| c_id | u_id | classname | classnum |

+------+------+-----------+----------+

| 2 | 2 | linux | 23 |

| 3 | 3 | mysql | 24 |

+------+------+-----------+----------+

2 rows in set (0.00 sec)

级联更新

select * from user;

MariaDB [forkey]> select * from user;

+----+-------------+--------+------+

| id | uname | gender | age |

+----+-------------+--------+------+

| 2 | changmengka | W | 27 |

| 3 | chang | W | 27 |

+----+-------------+--------+------+

2 rows in set (0.01 sec)

select * from class;

MariaDB [forkey]> select * from class;

+------+------+-----------+----------+

| c_id | u_id | classname | classnum |

+------+------+-----------+----------+

| 2 | 2 | linux | 23 |

| 3 | 3 | mysql | 24 |

+------+------+-----------+----------+

2 rows in set (0.00 sec)

update user set id=6 where id=2;

MariaDB [forkey]> update user set id=6 where id=2;

Query OK, 1 row affected (0.08 sec)

Rows matched: 1 Changed: 1 Warnings: 0

select * from user;

MariaDB [forkey]> select * from user;

+----+-------------+--------+------+

| id | uname | gender | age |

+----+-------------+--------+------+

| 3 | chang | W | 27 |

| 6 | changmengka | W | 27 |

+----+-------------+--------+------+

2 rows in set (0.00 sec)

select * from class;

MariaDB [forkey]> select * from class;

+------+------+-----------+----------+

| c_id | u_id | classname | classnum |

+------+------+-----------+----------+

| 2 | 6 | linux | 23 |

| 3 | 3 | mysql | 24 |

+------+------+-----------+----------+

2 rows in set (0.00 sec)

添加外键、级联更新、级联删除

语法：

alter table 数据表名称 add

[constraint [约束名称] ] foreign key (外键字段,..) references 数据表(参照字段,...)

[on update cascade|set null|no action]

[on delete cascade|set null|no action]

)

删除外键

语法：alter table 数据表名称 drop foreign key 约束（外键）名称;

show create table class \G

MariaDB [forkey]> show create table class \G

*************************** 1. row ***************************

Table: class

Create Table: CREATE TABLE `class` (

`c_id` int(5) NOT NULL AUTO_INCREMENT,

`u_id` int(5) DEFAULT '0',

`classname` varchar(50) DEFAULT NULL,

`classnum` int(10) DEFAULT NULL,

PRIMARY KEY (`c_id`),

KEY `u_id` (`u_id`),

CONSTRAINT `class_ibfk_1` FOREIGN KEY (`u_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

alter table class drop foreign key class_ibfk_1;

MariaDB [forkey]> alter table class drop foreign key class_ibfk_1;

Query OK, 2 rows affected (0.08 sec)

Records: 2 Duplicates: 0 Warnings: 0

show create table class \G

MariaDB [forkey]> show create table class \G

*************************** 1. row ***************************

Table: class

Create Table: CREATE TABLE `class` (

`c_id` int(5) NOT NULL AUTO_INCREMENT,

`u_id` int(5) DEFAULT '0',

`classname` varchar(50) DEFAULT NULL,

`classnum` int(10) DEFAULT NULL,

PRIMARY KEY (`c_id`),

KEY `u_id` (`u_id`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

视图

视图就是一个存在于数据库中的虚拟表。

视图本身没有数据，只是通过执行相关的select语句完成获得相应的数据。

如果某个查询结果出现的非常频繁，也就是要经常拿这个查询结果做子查询的，可以作为视图。

视图作用

1. 视图能够简化用户的操作

视图机制用户可以将注意力集中在所关心的数据上。如果这些数据不是直接来自基本表，则可以通过定义视图，使数据库看起来结构简单、清晰，并且可以简化用户的数据查询操作

2. 视图是用户能以不同的角度看待同样的数据。

对于固定的一些基本表，我们可以给不同的用户建立不同的视图，这样不同的用户就可以看到自己需要的信息了。

3. 视图对重构数据库提供了一定程度的逻辑性。

比如原来的A表被分割成了B表和C表，我们仍然可以在B表和C表的基础上构建一个视图A，而使用该数据表的程序可以不变。

4. 视图能够对机密数据提供安全保护

比如说，每门课的成绩都构成了一个基本表，但是对于每个同学只可以查看自己这门课的成绩，因此可以为每个同学建立一个视图，隐藏其他同学的数据，只显示该同学自己的

5. 适当的利用视图可以更加清晰的表达查询数据。

有时用现有的视图进行查询可以极大的减小查询语句的复杂程度。

创建视图

语法：create view 视图名 as select (字段名1,字段名2,……) from 表名 ……;

视图表中的数据和原数据表中数据是同步的

desc info;

MariaDB [books]> desc info;

+---------+----------+------+-----+---------+-------+

+---------+----------+------+-----+---------+-------+

| id | int(5) | YES | | NULL | |

| pace | int(3) | YES | | NULL | |

| lid | int(5) | YES | MUL | NULL | |

+---------+----------+------+-----+---------+-------+

5 rows in set (0.00 sec)

create view info_view as select info.iname as '书名',info.pace as '价格' from info;针对info表创建视图。

MariaDB [books]> create view info_view as select info.iname as '书名',info.pace as '价格' from info;

Query OK, 0 rows affected (0.00 sec)

show create view info_view \G查看视图创建信息

MariaDB [books]> show create view info_view \G

*************************** 1. row ***************************

View: info_view

Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `info_view` AS select `info`.`iname` AS `书名`,`info`.`pace` AS `价格` from `info`

character_set_client: utf8

collation_connection: utf8_general_ci

1 row in set (0.00 sec)

select * from info_view;查看视图中数据

MariaDB [books]> select * from info_view;

+---------+--------+

| 书名 | 价格 |

+---------+--------+

| css | 30 |

| html | 30 |

| js | 30 |

| linux | 50 |

| mariadb | 40 |

| mongodb | 30 |

| mysql | 40 |

| pycharm | 75 |

| python | 75 |

| redis | 40 |

| RHCA | 60 |

| RHCE | 60 |

| RHCSE | 60 |

| shell | 40 |

| zabbex | 40 |

+---------+--------+

19 rows in set (0.00 sec)

修改视图

alter view 视图名称 as select (字段名1,字段名2,……) from 表名 ……;

alter view info_view as select info.iname as '书名' from info;

MariaDB [books]> alter view info_view as select info.iname as '书名' from info;

Query OK, 0 rows affected (0.00 sec)

show create view info_view \G

MariaDB [books]> show create view info_view \G

*************************** 1. row ***************************

View: info_view

Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `info_view` AS select `info`.`iname` AS `书名` from `info`

character_set_client: utf8

collation_connection: utf8_general_ci

1 row in set (0.01 sec)

更新视图

语法：update view 视图名称 set 更新字段名=更新字段值 where 定位字段名=字段值;

删除视图

drop view info_view;

MariaDB [books]> drop view info_view;

Query OK, 0 rows affected (0.00 sec)

posted @ 2020-12-31 12:51 AI非AI 阅读(151) 评论(0) 收藏举报

刷新页面返回顶部