Mysql学习笔记

Posted on | Edited on | In

数据库的操作

数据库的概念

1.数据库是用于存放数据和数据库对象的容器。数据库对象包括表、索引、存储过程、视图、触发器、用户、角色、类型、函数等。

2.每个数据库都有唯一的名称，并且数据库的名字都是有实际意义的。

数据库的操作命令

创建数据库 create

查看数据库 show

选择数据库 use

修改数据库 alter

删除数据库 drop

创建数据库

1
2
3
4
CREATE DATABASE database_name  character  set  character_name;
创建数据库是在系统磁盘上划分一块区域用于数据的存储和管理。
database_name:表示所要创建的数据库的名称；不区分大小写。
character_name：表示数据库的字符集。存放中文，最好使用gbk或utf8
1
2
举例：创建数据库cms。
CREATE DATABASE cms  character  set  gbk;

注意：MySQL不允许两个数据库使用相同的名字。
每条SQL语句都以“;”作为结束标志。

查看数据库

show databases;

成功创建数据库后，可用show命令查看MySQL服务器中的所有数据库信息。

1
2
举例：查看MySQL服务器中的所有数据库。
show  databases;

选择数据库

成功创建数据库后，该数据库不会自动成为当前数据库，需要用use语句来指定当前数据库。

1
2
举例：选择名称为cms的数据库。
use  cms;

修改数据库

alter DATABASE database_name character set character_name;

database_name:表示所要修改的数据库名；

character_name:是修改诉字符集的名称。

1
2
举例：修改数据库cms。
alter DATABASE cms character set gb2312 ;

删除数据库

DROP DATABASE database_name;

database_name:表示所要删除的数据库名；
删除数据的操作应谨慎使用，一旦执行该操作，数据库的所有结构和数据都会被删除，没有恢复的可能，除非数据库有备份。

1
2
举例：删除数据库cms。
DROP DATABASE cms ;

表的操作

表的概念

1.表是包含数据库中所有数据的数据库对象。按行和列格式组织，每一行代表一条唯一的记录，每一列代表记录中的一个字段

2.表中的数据库对象包含列、索引和触发器。
列：属性列，创建表时，必须指定列的名字和数据类型。
索引：根据指定数据表的列建立起来的顺序，提供快速访问数据的途径且可监督表的数据，使其索引所指向的列中的数据不重复。
触发器：指用户定义的事务命令的集合，当对一个表中的数据进行插入、更新或删除时，这组命令就会自动执行。

创建表

CREATE TABLE table_name( 属性名 数据类型, 属性名 数据类型, … 属性名 数据类型 )

表的具体内容定义在括号之中，各列之间用逗号分隔。
属性名：字段的名称；数据类型：字段的数据类型。
表名：不能与已经存在的表对象重名。

1
2
3
4
5
6
7
8
9
10
11
举例：创建数据库company，在数据库中创建表t_dept。
(1)CREATE DATABASE company;
(2)Use company;
(3)Create table t_dept(
    deptno int,
    dname varchar(20),
    loc varchar(40)
);

注意：最后一个属性后没有逗号。圆括号外有分号。
创建表之前，需要选择数据库。No database selected.

查看表结构

语法格式：DESCRIBE 表名

表名：查看表对象定义信息的名字

1
2
3
举例：查看数据库company中创建名为t_dept表时的定义信息。
(1)选择数据库：USE company;
(2)DESCRIBE t_dept;

DESCRIBE语句查看表定义

你想输入的替代文字

SHOW CREATE TABLE语句查看表详细定义

语法格式：SHOW CREATE TABLE 表名

表名：查看表对象定义的名字

1
2
3
4
5
6
举例：查看数据库company中创建名为t_dept表时的详细信息。
(1)选择数据库：USE company;
(2)SHOW CREATE  table  t_dept\G

注意：在显示表详细定义信息时，可以使用； \g   \G符号来结束。
\G可使显示结果更加美观，且便于用户查看。

DESCRIBE语句查看表定义演示

SHOW CREATE table t_dept\G 执行结果

你想输入的替代文字

SHOW CREATE table t_dept\g 执行结果

SHOW CREATE table t_dept; 执行结果

你想输入的替代文字

DESCRIBE语句和SHOW CREATE TABLE语句的区别

DESCRIBE 表名：表结构定义信息，字段名，字段类型，是否为空，是否主键，默认值等；

SHOW CREATE TABLE 表名：除上述信息外，还有存储引擎和默认字符集。

删除表

删除数据库中已存在的表，会删除表中所保存的所有数据。

语法格式：DROP TABLE 表名

表名：表示所要删除的表名字，表必须是数据库中已经存在的表。

1
2
3
4
5
举例：删除数据库company中名为t_dept的表。
(1)选择数据库：USE company;
(2)Drop  table t_dept;
(3)检验数据库company中是否还存在名为t_dept的表
DESCRIBE t_dept;

你想输入的替代文字

修改表

增加字段

如果表已建好，数据已录入，但是需要增加一列或几列，怎么办？

(1)ALTER TABLE 表名 ADD 属性名 属性类型
增加到所有字段的最后一个位置。

(2)ALTER TABLE 表名 ADD 属性名 属性类型 FIRST
FIRST关键字：字段将增加到所有字段的第一个位置。

(3) ALTER TABLE 表名 ADD 属性名 属性类型 AFTER 属性名
在表的指定字段之后增加字段

表名：表示所要修改的表名字，“属性名”:要增加字段的名称，“属性类型”:数据类型。

1
2
3
4
5
6
举例1：为数据库company中表t_dept表增加一个名为descri，类型为VARCHAR的字段，所增加字段在表中所有字段的最后一个位置。

Use company
Desc t_dept;
Alter table t_dept add descri varchar(20);
Desc t_dept;

你想输入的替代文字

1
2
3
4
5
6
举例2：为数据库company中表t_dept表增加一个名为no1，类型为VARCHAR的字段，所增加字段在表中所有字段的第一个位置。

Use company
Desc t_dept;
Alter table t_dept add no1 varchar(20)  first;
Desc t_dept;

你想输入的替代文字

1
2
3
4
5
6
举例3：为数据库company中表t_dept表增加一个名为no2，类型为VARCHAR的字段，所增加字段在deptno字段之后的位置。

Use company
Desc t_dept;
Alter table t_dept add no2 varchar(20)  after deptno;
Desc t_dept;

你想输入的替代文字

删除字段

删除字段：删除已经在表中定义好的某个字段。

ALTER TABLE 表名 DROP 属性名

表名：表示所要修改的表名字，“属性名”:要删除字段的名称。

1
2
3
4
5
6
举例1：为数据库company中表t_dept删除名为no2的字段。

Use company
Desc t_dept;
Alter table t_dept drop no2 ;
Desc t_dept;

你想输入的替代文字

修改字段

1、修改字段的数据类型

1
2
3
ALTER TABLE 表名  MODIFY 属性名 数据类型 

表名：表示所要修改的表名字，“属性名”:要修改字段的字段名，“数据类型”为修改后的数据类型。

2、修改字段的名字

1
ALTER TABLE 表名  CHANGE 旧属性名 新属性名  旧数据类型

3、同时修改字段的名字和属性

1
ALTER TABLE 表名  CHANGE 旧属性名 新属性名  新数据类型

4、修改字段的顺序

1
2
3
ALTER TABLE 表名  CHANGE 属性名1 数据类型  FIRST|AFTER  属性名2

表名：表示所要修改的表名字，“属性名1”:所要调整顺序的字段名，“first”表示将字段调整到的第一个位置，“after属性名2”表示将字段调整到属性名2字段位置之后。  属性名1和属性名2必须是表中已存在的字段名。

修改字段数据类型

1
2
3
4
5
6
举例1：在数据库company中表t_dept中，将deptno字段的数据类型由原来的int(11)类型修改为varchar(20)类型。

Use company
Desc t_dept;
Alter table t_dept modify deptno varchar(20) ;
Desc t_dept;

你想输入的替代文字

修改字段的名字

1
2
3
4
5
6
举例2：在数据库company中表t_dept中，将loc字段的字段名改为location。

Use company
Desc t_dept;
Alter table t_dept change loc location varchar(40) ;
Desc t_dept;

你想输入的替代文字

同时修改字段的名字和属性

1
2
3
4
5
6
举例3：在数据库company中表t_dept中，将location字段的字段名改为loc,数据类型由原来的varchar(40)类型修改为varchar(20)类型。

Use company
Desc t_dept;
Alter table t_dept change location  loc varchar(20) ;
Desc t_dept;

你想输入的替代文字

修改字段的顺序

1
2
3
4
5
6
举例4-1：为数据库company中表t_dept中，将loc字段调整到表的第一个位置，把字段deptno调整到字段的dname字段之后。

Use company
Desc t_dept;
Alter table t_dept modify loc varchar(20) first; 
Desc t_dept;

你想输入的替代文字

1
2
3
4
5
6
7
举例4-2：为数据库company中表t_dept中，将loc字段调整到表的第一个位置，把字段deptno调整到字段的dname字段之后。

Use company
Desc t_dept;
Alter table t_dept modify loc varchar(20) first; 
Desc t_dept;
Alter table t_dept modify deptno varchar(20) after dname;

你想输入的替代文字

修改表名

表名可以在一个数据库中唯一地确定一张表。数据库系统通过表名来区分不同的表。

1
2
3
ALTER TABLE 表名  rename   [as]  new_tablename 

表名：表示所要修改的表名字，“new_tablename”:表的新名称。
1
2
3
rename  table  tablename  to   new_tablename 

tablename ：修改之前的表名，“new_tablename”:修改之后的表名。
1
2
3
4
5
6
7
8
举例：将表名tb_user改为tb_user2。

ALTER TABLE tb_user  rename  tb_user2; 


举例：将表名tb_user2改为tb_user3。

rename  table tb_user2  to tb_user3

操作表的约束

完整性约束

完整性：指数据的准确性和一致性。

完整性检查：指检查数据的准确性和一致性。

完整性约束：检查数据库表中的数据是否满足规定的条件，以保证数据库表中数据的准确性和一致性。

































完整性约束关键字 含义
NOT NULL 约束字段的值不能为空
DEFAULT 设置字段的默认值
UNIQUE KEY（UK） 约束字段的值是唯一
PRIMARY KEY （PK） 约束字段为表的主键，可为作为该表记录的唯一标记
AUTO_INCREMENT 约束字段的值为自动增加
FOREIGN（FK） 约束字段为表的外键

备注：不支持check约束，使用check约束没有任何效果；单列约束和多列约束。（一个约束只约束一列车或多列）

设置非空约束 NOT NULL,NK

当数据库表中的某个字段上的内容不希望设置为NULL时，则可以使用NK约束进行设置。
约束设置方法：在创建数据库表时为某些字段加上“NOT NULL”，保证所有记录中该字段都有值。若该字段为空值，则会报错。

1
2
3
4
Create table table_name(
    属性名  数据类型 NOT NULL,
    ……
);
设置字段的默认值 DEFAULT

在数据库表中插入一条新记录时，若没有为某个字段赋值，则自动为这个字段插入默认值。
约束设置方法： DEFAULT 

1
2
3
4
Create table table_name(
    属性名  数据类型 DEFAULT 默认值,
    ……
);
设置唯一约束 UNIQUE，UK

当数据库表中的某个字段上的内容不允许重复时，用UK约束进行设置。
约束设置方法：在创建数据库表时为某些字段加上“UNIQUE”，保证所有记录中该字段上的值不重复。若该字段的值有重复，则会报错。

1
2
3
4
Create table table_name(
    属性名  数据类型 UNIQUE,
    ……
);
设置主键约束 PRIMARY ,PK

当用数据库表中的某个字段来唯一标识所有记录时，则可以使用PK约束进行设置。
约束设置方法：在创建数据库表时为某些字段加上“PRIMARY KEY”，则该字段可以唯一地标示所有记录。
设置主键约束时，必须要满足主键字段的值是唯一、非空的。
主键可以是单一字段、也可为多个字段，因此分为单字段主键和多字段主键。

1
2
3
4
5
单字段主键：
Create table table_name(
   属性名  数据类型 PRIMARY KEY,
    ……
);
1
2
3
4
5
6
多字段主键：
Create table table_name(
    属性名  数据类型,
    ……
[CONSTRAINT 约束值]PRIMARY KEY(属性名,属性名……)
);
设置字段值自动增加AUTO_INCREMENT

当数据库表中插入新记录时，字段上的值会自动生成唯一的ID。
约束设置方法：在创建数据库表时为某些字段加上“AUTO_INCREMENT”。
一个数据库表中只能有一个字段使用该约束，该字段的数据类型必须是整数类型。

1
2
3
4
Create table table_name(
    属性名  数据类型 AUTO_INCREMENT,
    ……
);
设置外键约束FOREIGN，FK

外键约束保证多个表（通常为两个表）之间的参照完整性，构建于两个表的两个字段之间的参照关系。
设置外键约束的两个表之间具有父子关系，即子表中某个字段的取值范围由父表决定。
约束设置方法：在创建数据库表时为某些字段加上“FOREIGN KEY”。

1
2
3
4
5
6
7
8
Create table table_name(
    属性名  数据类型,
    属性名  数据类型,
    ……
   CONSTRAINT 外键约束名 FOREIGN KEY（属性名1）
    REFERENCES 表名（属性名2）
);
“外键约束名”用来标识约束名，“属性名1”是子表中设置外键的字段名，“属性名2”是父表中设置主键约束的字段名。

设置外键约束的两个表之间具有父子关系，即子表中某个字段的取值范围由父表决定。
例如：部门表和雇员表，雇员表中有一个表示部门编号的字段deptno，其依赖于部门表的主键，字段deptno就是雇员表的外键，通过该字段部门表和雇员表建立了关系。
设置外键约束的字段必须依赖于数据库中已经存在的父表的主键，同时外键可以为NULL。

1
2
3
4
5
6
7
8
Create table table_name(
    属性名  数据类型,
    属性名  数据类型,
    ……
   CONSTRAINT 外键约束名 FOREIGN KEY（属性名1）
    REFERENCES 表名（属性名2）
);
“外键约束名”用来标识约束名，“属性名1”是子表中设置外键的字段名，“属性名2”是父表中设置主键约束的字段名

数据的操作

数据库是存储数据的仓库。数据库基本对象—-表，用来存储数据。

1
2
3
4
5
6
7
8
9
10
数据的操作包括


插入数据记录   create

查询数据记录   Select

更新数据记录   update

删除数据记录   delete

插入数据记录

插入完整数据记录

1
2
3
4
5
Insert into 数据表名(field1,field2,……fieldn)
Values(value1,value2,……valuen)


Fieldn表示表中的字段名字，valuen表示所要插入的数值， Fieldn 与valuen 要一一对应。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
例一:

举例样例表person，创建语句如下：



CREATE TABLE person
(
id     INT   UNSIGNED   NOT NULL AUTO_INCREMENT,
name   CHAR(40)  NOT NULL  DEFAULT '',
age    INT NOT NULL  DEFAULT  0,
info   CHAR(50)  NULL,
PRIMARY KEY (id)
);


在person表中，插入一条新记录，id值为1，name值为Green，age值为21，info值为Lawyer，SQL语句如下：
执行插入操作之前，使用SELECT语句查看表中的数据：
 SELECT * FROM person;
Empty set (0.00 sec)
结果显示当前表为空，没有数据

执行插入操作：
INSERT INTO person (id ,name, age , info)
      VALUES (1,'Green', 21, 'Lawyer');

语句执行完毕，查看执行结果：
 SELECT * FROM person;
 
 
 
 提示：
在插入数据时，指定了person表的所有字段，因此将为每一个字段插入新的值。
INSERT语句后面的列名称顺序可以不是person表定义时的顺序。
即插入数据时，不需要按照表定义的顺序插入，只要保证值的顺序与列字段的顺序相同就可以。
1
2
3
4
5
6
7
8
例2:

在person表中，插入一条新记录，id值为2，name值为Suse，age值为22，info值为dancer，SQL语句如下：

INSERT INTO person (age ,name, id , info)
      VALUES (22, 'Suse', 2, 'dancer');

SELECT * FROM person;
1
2
3
4
5
6
7
8
9
10
11
例3:

在person表中，插入一条新记录，id值为3，name值为Mary，age值为24，info值为Musician，SQL语句如下：
INSERT INTO person 
     VALUES (3,'Mary', 24, 'Musician');

SELECT * FROM person;


提示：
使用INSERT插入数据时，允许列名称列表column_list为空，此时，值列表中需要为表的每一个字段指定值，并且值的顺序必须和数据表中字段定义时的顺序相同。
1
2
3
4
5
6
7
例4:

在person表中，插入一条新记录，name值为Willam，age值为20，info值为sports man，SQL语句如下：
INSERT INTO person (name, age,info)
     VALUES('Willam', 20, 'sports man');

SELECT * FROM person;
1
2
3
4
5
6
例5:

在person表中，插入一条新记录，name值为laura，age值为25，SQL语句如下：

INSERT INTO person (name, age ) VALUES ('Laura', 25);
SELECT * FROM person;

同时插入多条记录

1
2
3
4
5
6
7
8
9
10
11
12
13
插入时指定多个值列表，每个值列表之间用逗号分隔开

INSERT INTO 数据表名
 (field1,field2,……fieldn) 
 VALUES 
 (value11,value22,……valuen1), 
 (value12,value22,……valuen2),
                         ...,      
 (value1m,value2m,……valuenm)
     
     
提示：
记录中数值与字段参数field相对应即可，即字段参数field顺序可以与表的字段顺序不一致。
1
2
3
4
5
6
7
8
9
10
11
12
插入时指定多个值列表，每个值列表之间用逗号分隔开
INSERT INTO 数据表名
  VALUES 
 (value11,value22,……valuen1), 
 (value12,value22,……valuen2),
                         ...,      
   (value1m,value2m,……valuenm)



提示：
格式中没有字段参数field，即每条数据记录中的数值必须与表中字段的顺序一致，也可以正确插入多条完整数据记录。

插入完整数据记录

1
2
3
4
5
6
7
8
9
10
11
12
例6:


在person表中，在name、age和info字段指定插入值，同时插入3条新记录，SQL语句如下：

INSERT INTO person(name, age, info)
VALUES 
('Evans',27, 'secretary'),
('Dale',22, 'cook'),
('Edison',28, 'singer');

SELECT * FROM person;
1
2
3
4
5
6
7
8
9
10
11
例7:



在person表中，不指定插入列表，同时插入2条新记录，SQL语句如下：

INSERT INTO person 
VALUES (9,'Harry',21, 'magician'), 
(NULL,'Harriet',19, 'pianist');

SELECT * FROM person;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
例8：

创建person_old的数据表，与person结构相同：

CREATE TABLE person_old
(
id     INT  UNSIGNED  NOT  NULL AUTO_INCREMENT,
name   CHAR(40)  NOT  NULL  DEFAULT '',
age    INT NOT  NULL  DEFAULT  0,
info   CHAR(50) NULL,
PRIMARY KEY (id)
);



从person_old表中查询所有的记录，并将其插入到person表中。
向person_old表中添加两条记录：

 INSERT INTO person_old
     VALUES (11,'Harry',20, 'student'), (12,'Beckham',31, 'police');

SELECT * FROM person_old;

将查询结果插入到表中

1
2
3
4
INSERT语句和SELECT语句组成的组合语句即可快速地从一个或多个表中向一个表中插入多个行。

INSERT INTO  数据表名1  (field1,field2,……fieldn) SELECT (field1,field2,……fieldn) FROM 数据表名2       
    WHERE (条件)
1
2
3
4
5
6
7
8
9
例9:



将person_old表中所有的记录插入person表中，SQL语句如下：
INSERT INTO person(id, name, age, info)
SELECT id, name, age, info FROM person_old;
上面是一条语句，注意只有一个分号。
SELECT * FROM person;

更新数据记录

1
2
3
4
5
6
7
8
9
10
MySQL中使用UPDATE语句更新表中的记录，可以更新特定的行或者同时更新所有的行。
UPDATE 数据表名 
     SET field1=value1,
            field2=value2,
            field3=value3,
WHERE (condition);



注意：field表示表中所要更新数值的字段名字，参数value表示更新后的数值，condition指定更新满足条件的特定数据记录。
1
2
3
4
5
6
7
8
9
例10:

更新前：SELECT * FROM person WHERE id=11;

更新：在person表中，更新id值为11的记录，将age字段值改为15，将name字段值改为LiMing，SQL语句如下：
UPDATE person SET age = 15, name='LiMing'   WHERE   id = 11;

更新后：
SELECT * FROM person WHERE id=11;
1
2
3
4
5
6
7
8
9
10
11
例10:


更新前：
SELECT * FROM person WHERE age BETWEEN 19 AND 22;

在person表中，更新age值为19~22的记录，将info字段值都改为student，SQL语句如下：
UPDATE person SET info='student'  WHERE id  BETWEEN 19 AND 22;

更新后：查看数据
SELECT * FROM person WHERE age BETWEEN 19 AND 22;

删除数据记录

从数据表中删除数据使用DELETE语句，DELETE语句允许WHERE子句指定删除条件。

DELETE FROM 数据表名[WHERE condition>] 

1
2
3
4
5
6
7
8
9
10
11
例12:


在person表中，删除id等于11的记录

使用SELECT语句查看当前id=11的记录：
 SELECT * FROM person WHERE id=11;

DELETE FROM person WHERE id = 11;

SELECT * FROM person WHERE id=11;
1
2
3
4
5
6
7
8
9
10
例13:


使用DELETE语句同时删除多条记录
删除前：
SELECT * FROM person WHERE age BETWEEN 19 AND 22;
删除：
DELETE FROM person WHERE age BETWEEN 19 AND 22;
删除后：
 SELECT * FROM person WHERE age BETWEEN 19 AND 22;

TRUNCATE TABLE语句

TRUNCATE将直接删除原来的表并重新创建一个表，其语法结构为：
TRUNCATE TABLE table_name
TRUNCATE直接删除表而不是删除记录，因此执行速度比DELETE快。

综合案例——记录的插入、更新和删除

对数据进行更新和删除时，MySQL中对数据操作没有任何提示，因此在更新和删除数据时，一要谨慎小心，查询条件一定要准确，避免造成数据的丢失。本章的综合案例包含了对数据表中数据的基本操作，包括记录的插入、更新和删除。

创建数据表books，并定义各个字段

1
2
3
4
5
6
7
8
9
10
CREATE TABLE books
(
id      	INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name   	VARCHAR(40) NOT NULL,
authors  VARCHAR(200) NOT NULL,
price    	INT(11) NOT NULL,
pubdate 	YEAR NOT NULL,
note    	VARCHAR(255) NULL,
num    	INT NOT NULL DEFAULT  0
);

插入数据到books表中

1
2
3
4
5
6
7
8
9
10
看看：SELECT * FROM books;
（1）指定所有字段名称插入记录，SQL语句如下。
 INSERT INTO books
      (id, name, authors, price, pubdate,note,num)
      VALUES(1, 'Tale of AAA', 'Dickes', 23, '1995', 'novel',11);
（2）不指定字段名称插入记录，SQL语句如下。
 INSERT INTO books
      VALUES (2,'EmmaT','Jane lura',35,'1993', 'joke',22);
使用SELECT语句查看当前表中的数据：
 SELECT * FROM books;

同时插入多条记录

1
2
3
4
5
6
INSERT INTO books
     VALUES(3, 'Story of Jane', 'Jane Tim', 40, '2001', 'novel', 0),
     (4, 'Lovey Day', 'George Byron', 20, '2005', 'novel', 30),
     (5, 'Old Land', 'Honore Blade', 30, '2010', 'law',0),
     (6,'The Battle','Upton Sara',33,'1999', 'medicine',40),
     (7,'Rose Hood','Richard Kale',28,'2008', 'cartoon',28);

批量更新数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
一、
先看数据：SELECT * FROM books;
将小说类型（novel）的书的价格都增加5。
UPDATE books SET price = price + 5 WHERE note = ‘novel’;

SELECT id, name, price, note FROM books WHERE note = 'novel';


二、
将名称为EmmaT的书的价格改为40，并将说明改为drama。
看：
 SELECT name, price, note FROM books WHERE name='EmmaT';
改：UPDATE books SET price=40,note= 'drama 'WHERE name= 'EmmaT ';
看：SELECT name, price, note FROM books WHERE name='EmmaT';


三、
看：SELECT * FROM books WHERE num=0;

删：DELETE FROM books WHERE num=0;

看：SELECT * FROM books WHERE num=0;

子查询和正则表达式查询

本章有点杂乱,抓重点记一下,重在体会方法。

子查询

带ANY、SOME关键字的子查询

ANY和SOME关键字是同义词，表示满足其中任一条件，它们允许创建一个表达式对子查询的返回值列表进行比较，只要满足内层子查询中的任何一个比较条件，就返回一个结果作为外层查询的条件。

下面定义两个表tb1和tb2:

1
2
3
4
5
6
CREATE table tbl1 ( num1 INT NOT NULL);
CREATE table tbl2 ( num2 INT NOT NULL);
分别向两个表中插入数据：
INSERT INTO tbl1 values(1), (5), (13), (27);
INSERT INTO tbl2 values(6), (14), (11), (20);
ANY关键字接在一个比较操作符的后面，表示若与子查询返回的任何值比较为TRUE，则返回TRUE。

示例:
你想输入的替代文字

你想输入的替代文字

1
2
3
4
5
示例:

返回tbl2表的所有num2列，然后将tbl1中的num1的值与之进行比较，只要大于num2的任何1个值，即为符合查询条件的结果。

SELECT num1 FROM tbl1 WHERE num1 > ANY (SELECT num2 FROM tbl2);

你想输入的替代文字

带ALL关键字的子查询

ALL关键字与ANY和SOME不同,使用ALL时需要同时满足所有内层查询的条件。

1
2
3
4
5
示例:

返回tbl1表中比tbl2表num2 列所有值都大的值，SQL语句如下：

SELECT num1 FROM tbl1 WHERE num1 > ALL (SELECT num2 FROM tbl2);

你想输入的替代文字

带EXISTS关键字的子查询

EXISTS关键字后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行，如果至少返回一行，那么EXISTS的结果为true，此时外层查询语句将进行查询；如果子查询没有返回任何行，那么EXISTS返回的结果是false，此时外层语句将不进行查询。

1
2
3
4
查询suppliers表中是否存在s_id=87的供应商，如果存在，则查询fruits表中的记录。
SELECT * FROM fruits
     WHERE EXISTS
     (SELECT s_name FROM suppliers WHERE s_id = 87);

你想输入的替代文字

1
2
3
4
查询suppliers表中是否存在s_id=87的供应商，如果存在，则查询fruits表中的f_price大于8.20的记录。
SELECT * FROM fruits
     WHERE f_price>8.20 AND EXISTS
     (SELECT s_name FROM suppliers WHERE s_id = 87);

你想输入的替代文字

1
2
3
4
查询suppliers表中是否存在s_id=87的供应商，如果不存在则查询fruits表中的记录。
SELECT * FROM fruits
     WHERE NOT EXISTS
     (SELECT s_name FROM suppliers WHERE s_id = 87);

你想输入的替代文字

带IN关键字的子查询

IN关键字进行子查询时，内层查询语句仅仅返回一个数据列，这个数据列里的值将提供给外层查询语句进行比较操作。

在orderitems表中查询f_id为c0的订单号，并根据订单号查询具有订单号的客户c_id。
SELECT c_id FROM orders WHEREo_numIN (SELECTo_numFROM orderitems WHERE f_id = 'c0');

1
2
3
4
5
SELECT o_num  FROM orderitems WHERE f_id = 'c0';

假设，符合条件的o_num列的值有两个：30003和30005，然后执行外层查询，在orders表中查询订单号等于30003或30005的客户c_id。嵌套子查询语句还可以写为如下形式，实现相同的效果：

 SELECT c_id FROM orders WHERE o_num IN (30003, 30005);
1
2
3
4
5
在SELECT语句中使用NOT IN关键字。
SELECT c_id FROM orders WHERE o_num NOT IN
     (SELECT o_num  FROM orderitems WHERE f_id = 'c0');

 SELECT * FROM orders;

带比较运算符的子查询

在前面介绍的带ANY、ALL关键字的子查询时使用了>比较运算符，子查询时还可以使用其他的比较运算符，如<、<=、=、>=和!=等。

1
2
3
4
在suppliers表中查询s_city等于“Tianjin”的供应商s_id，然后在fruits表中查询所有该供应商提供的水果的种类。
SELECT s_id, f_name FROM fruits
     WHERE s_id =
     (SELECT s1.s_id FROM suppliers AS s1 WHERE s1.s_city = 'Tianjin');
1
2
3
4
在suppliers表中查询s_city等于“Tianjin”的供应商s_id，然后在fruits表中查询所有非该供应商提供的水果的种类。
SELECT s_id, f_name FROM fruits
     WHERE s_id <>
     (SELECT s1.s_id FROM suppliers AS s1 WHERE s1.s_city = 'Tianjin');

合并查询结果

利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。

1
2
3
4
5
6
7
8
9
10
查询所有价格小于9的水果的信息，查询s_id等于81和83所有的水果的信息，使用UNION ALL
![upload successful](\\images\pasted-195.png\)连接查询结果。

SELECT s_id, f_name, f_price 
FROM fruits
WHERE f_price < 9.0
UNION ALL
SELECT s_id, f_name, f_price 
FROM fruits
WHERE s_id IN(81,83);

为表和字段取别名

为表取别名

为了方便操作或者需要多次使用相同的表时，可以为表指定别名，用这个别名替代表原来的名称。
表名 [AS] 表别名

为字段取别名

MySQL可以指定列别名，替换字段或表达式。
列名 [AS] 列别名

使用正则表达式查询

例如：在文本文件中提取电话号码；
查找文章中重复的单词；
或替换用户输入的某些敏感词语；
正则表达式强大灵活，可以应用于非常复杂的查询；
REGEXP 正则表达式匹配模式；
可以使用多种不同的匹配字符；

查询以特定字符或字符串开头的记录

字符‘^’匹配以特定字符或者字符串开头的文本。

1
2
3
4
5
6
7
8
9
在fruits表中，查询f_name字段以字母’b’开头的记录

SELECT * FROM fruits WHERE f_name REGEXP '^b';



在fruits表中，查询f_name字段以“be”开头的记录

SELECT * FROM fruits WHERE f_name REGEXP '^be';

查询以特定字符或字符串结尾的记录

字符‘$’匹配以特定字符或者字符串结尾的文本。

1
2
3
4
5
6
7
8
9
在fruits表中，查询f_name字段以字母’y’结尾的记录

SELECT * FROM fruits WHERE f_name REGEXP 'y$';



在fruits表中，查询f_name字段以字符串“rry”结尾的记录

SELECT * FROM fruits WHERE f_name REGEXP 'rry$';

用符号”.”来替代字符串中的任意一个字符

字符‘.’匹配任意一个字符。

1
2
3
在fruits表中，查询f_name字段值包含字母’a’与’g’且两个字母之间只有一个字母的记录

SELECT * FROM fruits WHERE f_name REGEXP 'a.g';

使用”*”和”+”来匹配多个字符

1
2
3
4
星号‘*’匹配前面的字符任意多次，包括0次。
加号‘+’匹配前面的字符至少一次。

在fruits表中，查询f_name字段值以字母’b’开头，且’b’后面出现字母’a’的记录

你想输入的替代文字

星号’*’可以匹配任意多个字符，blackberry和berry中字母b后面并没有出现字母a，但是也满足匹配条件。

1
2
3
4
在fruits表中，查询f_name字段值以字母’b’开头，且’b’后面出现字母’a’至少一次的记录


SELECT * FROM fruits WHERE f_name REGEXP '^ba+';

你想输入的替代文字

‘a+’匹配字母’a’至少一次，只有banana满足匹配条件。

匹配指定字符串

正则表达式可以匹配指定字符串，只要这个字符串在查询文本中即可，如要匹配多个字符串，多个字符串之间使用分隔符‘|’隔开。

1
2
3
在fruits表中，查询f_name字段值包含字符串“on”的记录

SELECT * FROM fruits WHERE f_name REGEXP 'on';

你想输入的替代文字

1
2
3
在fruits表中，查询f_name字段值包含字符串“on”或者“ap”的记录

SELECT * FROM fruits WHERE f_name REGEXP 'on|ap';

你想输入的替代文字

与LIKE子句的对比

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT * FROM fruits WHERE f_name LIKE 'on';


查询结果为：
Empty set

如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的

'%a'     //以a结尾的数据
'a%'     //以a开头的数据
'%a%'    //含有a的数据
'_a_'    //三位且中间字母是a的
'_a'     //两位且结尾字母是a的
'a_'     //两位且开头字母是a的

匹配指定字符中的任意一个

方括号“[]”指定一个字符集合，只匹配其中任何一个字符，即为所查找的文本。

1
2
3
4
在fruits表中，查找f_name字段中包含字母’o’或者’t’的记录


SELECT * FROM fruits WHERE f_name REGEXP '[ot]';

你想输入的替代文字

1
2
3
例：在fruits表，查询s_id字段中数值中包含4、5或者6的记录

SELECT * FROM fruits WHERE s_id REGEXP '[456]';

你想输入的替代文字

注意：
查询结果中，s_id字段值中有3个数字中的1个即为匹配记录字段。
匹配集合“[456]”也可以写成“[4-6]”即指定集合区间。例如“[a-z]”表示集合区间为从a~z的字母，“[0-9]”表示集合区间为所有数字。

匹配指定字符以外的字符

“[^字符集合]”匹配不在指定集合中的任何字符。

1
2
3
4
在fruits表中，查询f_id字段包含字母a~e和数字1~2以外的字符的记录,即非a1 a2 b1 b2的字符。


SELECT * FROM fruits WHERE f_id REGEXP '[^a-e1-2]';

你想输入的替代文字

使用{M}或者{M,N}来指定字符串连续出现的次数

“字符串{n,}”表示至少匹配n次前面的字符。“字符串{n,m}”表示匹配前面的字符串不少于n次，不多于m次。

1
2
3
在fruits表中，查询f_name字段值出现字母’x’至少2次的记录

SELECT * FROM fruits WHERE f_name REGEXP 'x{2,}';

你想输入的替代文字

1
2
3
4
5
6
7
8
例：在fruits表中，查询f_name字段值出现字符串“ba”最少1次，最多3次的记录


SELECT * FROM fruits WHERE f_name REGEXP 'ba{1,3}';



![你想输入的替代文字](Mysql学习笔记/zhengze9.png)

综合案例——数据表查询操作

数据表查询操作

SQL语句可以分为两部分，一部分用来创建数据库对象，另一部分用来操作这些对象，本章详细介绍了操作数据库对象的数据表查询语句。通过本章的介绍，读者可以了解到SQL中的查询语言功能的强大，用户可以根据需要灵活使用。本章的综合案例将回顾这些查询语句。

创建数据表employee和dept。

1
2
3
4
5
6
CREATE TABLE dept
(
d_no         INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
d_name       VARCHAR(50),
d_location     VARCHAR(100)
);

employee表dept_no依赖于父表dept的主键d_no，因此需要先创建dept表，然后创建employee表。

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE employee
(
e_no        INT NOT NULL PRIMARY KEY,
e_name      VARCHAR(100) NOT NULL,
e_gender    CHAR(2) NOT NULL,
dept_no    INT NOT NULL,
e_job       VARCHAR(100) NOT NULL,
e_salary   SMALLINT NOT NULL,
hireDate   DATE,
CONSTRAINT dno_fk FOREIGN KEY(dept_no)
REFERENCES dept(d_no)
);

将指定记录分别插入两个表中。

1
2
3
4
5
6
向dept表中插入数据
INSERT INTO dept 
VALUES (10, 'ACCOUNTING', 'ShangHai'),
(20, 'RESEARCH ', 'BeiJing '),
(30, 'SALES ', 'ShenZhen '),
(40, 'OPERATIONS ', 'FuJian ');

向employee表中插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
INSERT INTO employee 
VALUES (1001, 'SMITH', 'm',20, 'CLERK',800,'2005-11-12'),
(1002, 'ALLEN', 'f',30, 'SALESMAN', 1600,'2003-05-12'),
(1003, 'WARD', 'f',30, 'SALESMAN', 1250,'2003-05-12'),
(1004, 'JONES', 'm',20, 'MANAGER', 2975,'1998-05-18'),
(1005, 'MARTIN', 'm',30, 'SALESMAN', 1250,'2001-06-12'), 
(1006, 'BLAKE', 'f',30, 'MANAGER', 2850,'1997-02-15'),
(1007, 'CLARK', 'm',10, 'MANAGER', 2450,'2002-09-12'),
(1008, 'SCOTT', 'm',20, 'ANALYST', 3000,'2003-05-12'),
(1009, 'KING', 'f',10, 'PRESIDENT', 5000,'1995-01-01'),
(1010, 'TURNER', 'f',30, 'SALESMAN', 1500,'1997-10-12'),
(1011, 'ADAMS', 'm',20, 'CLERK', 1100,'1999-10-05'),
(1012, 'JAMES', 'm',30, 'CLERK', 950,'2008-06-15');

查询操作

在employee表中，查询所有记录的e_no、e_name和e_salary字段值
1
SELECT e_no, e_name, e_salary FROM employee;

你想输入的替代文字

在employee表中，查询dept_no等于10和20的所有记录
1
SELECT * FROM employee WHERE dept_no IN (10, 20);

你想输入的替代文字

在employee表中，查询工资范围在800~2500之间的员工信息。
1
SELECT * FROM employee WHERE e_salary BETWEEN 800 AND 2500;

你想输入的替代文字

在employee表中，查询部门编号为20的部门中的员工信息
1
SELECT * FROM employee WHERE dept_no = 20;

你想输入的替代文字

在employee表中，查询每个部门最高工资的员工信息。
1
SELECT dept_no, MAX(e_salary) FROM employee GROUP BY dept_no;

你想输入的替代文字

查询员工BLAKE所在部门和部门所在地。
1
2
3
SELECT d_name,d_no, d_location
      FROM dept WHERE d_no=
      (SELECT dept_no FROM employee WHERE e_name='BLAKE');

你想输入的替代文字

使用连接查询，查询所有员工的部门和部门信息。
1
2
SELECT e_no, e_name, dept_no, d_name,d_location
     FROM employee, dept WHERE dept.d_no=employee.dept_no;

你想输入的替代文字

在employee表中，计算每个部门各有多少名员工。

用到GROUP BY分组

1
SELECT dept_no, COUNT(*) FROM employee GROUP BY dept_no;

你想输入的替代文字

在employee表中，计算不同类型职工的总工资数。

用到GROUP BY分组

1
SELECT e_job, SUM(e_salary) FROM employee GROUP BY e_job;

你想输入的替代文字

在employee表中，计算不同部门的平均工资。

用到GROUP BY分组

1
SELECT dept_no, AVG(e_salary) FROM employee GROUP BY dept_no;

你想输入的替代文字

在employee表中，查询工资低于1500的员工信息。
1
SELECT * FROM employee WHERE e_salary < 1500;

你想输入的替代文字

在employee表中，将查询记录先按部门编号由高到低排列，再按员工工资由高到低排列。
1
2
SELECT e_name,dept_no, e_salary
     FROM employee ORDER BY dept_no DESC, e_salary DESC;

你想输入的替代文字

在employee表中，查询员工姓名以字母’A’或’S’开头的员工的信息。
1
SELECT * FROM employee WHERE e_name REGEXP '^[as]';

你想输入的替代文字

在employee表中，查询到目前为止，工龄大于等于10年的员工信息。
1
SELECT * FROM employee where YEAR(CURDATE()) -YEAR(hireDate) >= 10;

你想输入的替代文字

视图的操作

视图介绍

为什么使用视图

视图 VIEW：可实现查询简化，会提高安全性。

视图：一种虚拟表，不以存储数据值的形式存在，引用表中的数据，可提高数据库中数据的安全性。

Select语句的结果集构成视图所返回的虚拟表。
当数据表中的数据发生变化时，从视图中查询出来的数据也随之改变。

视图的作用

（1）将用户限定在数据表中的特定行上。如，只允许部门经理查看本部门的员工信息。

（2）将用户限定在特定列上。如可以让用户查看新闻的内容，但不允许用户查看新闻的发布者名字。

（3）将多个表中的列连接起来，使它们看起来像一个表，采用连接查询，用于报表的制作 。

（4）聚合信息而非提供详细信息。显示一个列的和，或者列的最大值和最小值。

视图的特点

（1）视图的列可来自于不同的表，是表的抽象和在逻辑意义上建立的新关系。

（2）视图由基本表（实表）产生的表（虚表）。

（3）视图的建立和删除不影响基本表。

（4）对视图内容的更新（添加、删除和修改）直接影响基本表。

（5）当视图来自多个基本表时，不允许添加和删除数据。

视图的优点

（1）数据保密。对不同的用户定义不同的视图，使用户只能看到与自己有关的数据。

（2）简化查询操作。为复杂的查询建立一个视图，用户不必输入复杂的查询语句，只需针对视图作简单的查询即可。

（3）保证数据的逻辑独立性。对于视图的操作，只依赖于视图的定义，当构成视图的数据表要修改时，只需修改视图定义中的子查询部分，而基于视图的查询不用改变。

视图的缺点

当更新视图中的数据时，是对数据表的数据进行更新。但，某些视图是不能更新数据的。这些视图有如下的特征：

（1）有UNION等集合操作符的视图。

（2）有GROUP BY子句的视图。

（3）有AVG、SUM或者MAX等函数的视图。

（4）使用DISTINCT短语的视图。

（5）连接表的视图。

视图的操作

创建视图

1
2
CREATE VIEW 视图名
     AS 查询语句

视图由视图名和视图定义两部分组成。
视图名不能和表名、其它视图名重名。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
例1：创建查看普通用户的视图。

用户表中有普通用户和管理员用户之分，为方便查询，先建立一个普通用户的视图。先建一个查询语句，将普通用户找出来，然后再建立视图。


查询语句：
Selecet  uid,username,email  From  tb_user Where  lever=‘普通用户’;

创建视图，视图名为tv_commuser；
Create view tv_commuser  as
Select  uid,username,email  from  tb_user  where  lever=‘普通用户’;

查看视图：
Select  *  from tv_commuser;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
例2：创建二级部门的视图，使用check option选项，并在使用该选项前后插入一条一级部门的记录，观察发生的情况。



查询语句：

Selecet  *  From  tb_dept   Where  deptlever=2;


创建视图，视图名为tv_dept_1；

Create view  tv_dept_1  as  Selecet  *  From  tb_dept   Where  deptlever=2;
 

查看视图：

Select  *  from   tv_dept_1;




（1）向该视图插入一条一级部门的记录。

Insert  into tv_dept_1 values(‘d6001’,’信息部’,’显示’,’d6001’,1);


（2）使用check  option选项来创建视图

Create view  tv_dept_1  as  Selecet  *  From  tb_dept   Where  deptlever=2  with  check  option;
 

（3）再向该视图插入一条一级部门的记录。

Insert  into tv_dept_1 values(‘d6001’,’信息部’,’显示’,’d6001’,1);

会出现：ERROR 1369（HY000）：CHECK OPTION FAILED ‘cms.tv_dept_1’

加上check option选项以后，不能向该视图插入非二级部门的记录。

查看视图

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
1.显示表的名字和视图的名字

SHOW TABLES      



2.显示表和视图的详细信息

SHOW TABLE STATUS [FROM 数据库名][LIKE ‘pattern’]



3.查看视图定义信息  

SHOW CREATE VIEW 视图名

DESCRIBE|DESC 视图名
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
1.显示表的名字和视图的名字

SHOW TABLES    



2.显示表和视图的详细信息

SHOW TABLE STATUS [FROM 数据库名][LIKE ‘pattern’]


例：查看视图名字以”tv_d”开头的视图基本情况。

Show  table  status  like  ‘tv_d%’;




3.查看视图定义

SHOW CREATE VIEW 视图名

例：查看已经建立的视图情况

Select *  from  information_schema.tables  where  table_type=‘view’;

例：查看视图tv_dept_1的定义

Show  create  view  tv_dept_1;
1
2
3
4
5
6
7
8

4.查看视图结构定义

DESCRIBE|DESC 视图名

例：查看视图tv_dept_1的结构定义

Describe  tv_dept_1

修改视图

方法一：

（1）先删除视图

DROP VIEW 视图名；

（2）新建视图

CREATE VIEW 视图名 AS  SELECT 字段名 FROM 表名

方法二：

（1）ALTER语句修改视图

ALTER VIEW 视图名 AS 查询

（2）查看视图

SELECT  * FROM  视图名 ；

1
2
3
4
5
6
7
8
9
10
修改视图：ALTER VIEW 视图名 AS 查询
 
 
例：创建一个视图，列出二级部门的情况，然后使用alter语句修改为一级部门。

创建视图：
create view  tv_test  as  select  *  from tb_dept  where  deptlever1=2;

修改视图：
Alter  view  tv_test as  select  *  from tb_dept  where  deptlever=1;

视图的更名

视图的更名：

视图创建之后，可以对其重新命名。在mysql中，视图被当作表看待，对视图的重命名就像对表的重命名一样。

Rename table 原视图名 to 新视图名

例：将视图tv_test重命名为tv_dep_1

Rename table tv_test to tv_dep_1;

删除视图

语法格式：DROP VIEW 视图名 [,视图名]…

举例：删除视图tv_test

Drop view tv_test;

利用视图操作基本表

通过视图检索基本表数据；

通过视图修改基本表中的数据；

检索（查询）数据

通过视图查询数据，比通过表进行查询更安全、更简单、实用。只需将表名换为视图名。

举例：SELECT * FROM 视图名

利用视图操作基本表数据

1、添加数据操作

INSERT INTO 视图名（字段名1，字段名2，…，字段n）VALUES（值1，值2，…，值n）

2、删除数据操作

DELETE FROM 视图名 WHERE 条件)

3、更新数据操作

UPDATE 视图名 SET 表达式 WHERE 条件

索引的操作

索引简介

索引的含义和特点

索引是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据表里所有记录的引用指针。

索引的优点。

索引的缺点。

索引的好处是为了增加查询速度 建的不好的话会导致查询非常的慢

建立索引的好处

创建索引可以大大提高数据库系统的查询性能。

a) 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

b) 可以大大加快数据的检索速度，这也是创建索 引的最主要的原因。

c) 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

d) 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

e) 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

建立索引需要付出的代价

建立索引的目的是加快对表中记录的查找或排序。

a) 索引需要占物理空间 数据表占数据空间，每一个索引要占一定的物理空间，聚簇索引，需要的空间更大。

b) 创建索引和维护索引要耗费时间 这种时间随着数据量的增加而增加。

c)降低维护速度，降低了效率 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维 护，这样就降低了数据的维护速度

索引的分类

普通索引和唯一索引

单列索引和组合索引

全文索引

空间索引

索引的设计原则

索引并非越多越好。

避免对经常更新的表进行过多的索引，并且索引中的列尽可能少。

数据量小的表最好不要使用索引。

在条件表达式中经常用到的不同值较多的列上建立检索，在不同值少的列上不要建立索引。

当唯一性是某种数据本身的特征时，指定唯一索引

在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引

创建索引

创建表的时候创建索引

1
2
3
4
5
创建表时创建索引的基本语法格式如下：
CREATE  TABLE  
     table_name [col_name data_type]    [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY]     
    [index_name] (col_name [length])
    [ASC | DESC]

在已经存在的表上创建索引

1．使用ALTER TABLE语句创建索引

2．使用CREATE INDEX创建索引

建立数据库

1
2
Create database  test_db;
Use test_db;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
例1：在book表中的year_publication字段上建立普通索引。（建表）


CREATE TABLE book
(
bookid            	INT NOT NULL,
bookname    VARCHAR(255) NOT NULL,
authors     	VARCHAR(255) NOT NULL,
info               VARCHAR(255)   NULL,
comment     VARCHAR(255)   NULL,
year_publication   YEAR  NOT  NULL,
INDEX(year_publication)     );





使用SHOW CREATE TABLE查看表结构：
 SHOW CREATE table book;

KEY `year_publication` (`year_publication`)
book1表的year_publication字段上成功建立索引，其索引名称year_publication为MySQL自动添加。




使用EXPLAIN语句查看索引是否正在使用：
 explain select * from book where year_publication=1990 \G

你想输入的替代文字

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
例2：创建一个表t1，在表中的id字段上使用UNIQUE关键字创建唯一索引



CREATE TABLE t1
(
id    INT NOT NULL,
name CHAR(30) NOT NULL,
UNIQUE INDEX UniqIdx(id)
);





使用SHOW CREATE TABLE查看表结构：
SHOW CREATE table t1\G

id字段上已经成功建立了一个名为UniqIdx的唯一索引。

你想输入的替代文字