表的基本概念
表是包含数据库中所有数据的数据库对象。其中每一行代表一条唯一的记录,每一列代表记录中的一个字段。
表中的数据库对象包含列、索引和触发器。
列(Columns):也称属性列,在具体创建表时,必须制定列的名字和数据类型。
索引(Indexes):是指根据制定的数据库表列建立起来的顺序,提供了快速访问数据的途径。且可监督表的数据,使其索引指向的列的数据不重复;
触发器(triggers):是指用户定义的命令的集合。当对一个表中的数据进行插入、更新和删除时这组命令就会自动执行。
1.创建表
创建表的语法:
CREATE TABLE table_name(
属性名 数据类型,
属性名 数据类型,
属性名 数据类型,
.
.
.
);
创建一个表,示例:
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| DATA_MINING |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> USE DATA_MINING;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SHOW TABLES;
+-----------------------+
| Tables_in_DATA_MINING |
+-----------------------+
| AAA |
| f_test |
+-----------------------+
2 rows in set (0.00 sec)
**mysql> CREATE TABLE T_DEPT(
-> ID BIGINT,
-> NAME VARCHAR(32),
-> LOC VARCHAR(32)
-> );**
Query OK, 0 rows affected (0.09 sec)
mysql> SHOW TABLES;
+-----------------------+
| Tables_in_DATA_MINING |
+-----------------------+
| AAA |
| T_DEPT |
| f_test |
+-----------------------+
3 rows in set (0.00 sec)
2.查看表
查看表的定义信息:DESCRIBE T_DEPT;
mysql> DESCRIBE T_DEPT;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID | bigint(20) | YES | | NULL | |
| NAME | varchar(32) | YES | | NULL | |
| LOC | varchar(32) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.19 sec)
mysql> DESCRIBE T_DEPT \g
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID | bigint(20) | YES | | NULL | |
| NAME | varchar(32) | YES | | NULL | |
| LOC | varchar(32) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> DESCRIBE T_DEPT \G
*************************** 1. row ***************************
Field: ID
Type: bigint(20)
Null: YES
Key:
Default: NULL
Extra:
*************************** 2. row ***************************
Field: NAME
Type: varchar(32)
Null: YES
Key:
Default: NULL
Extra:
*************************** 3. row ***************************
Field: LOC
Type: varchar(32)
Null: YES
Key:
Default: NULL
Extra:
3 rows in set (0.00 sec)
mysql>
查看表结构的详细信息可以通过:SHOW CREATE TABLE TABLE_NAME;
mysql> SHOW CREATE TABLE T_DEPT \G
*************************** 1. row ***************************
Table: T_DEPT
Create Table: CREATE TABLE `T_DEPT` (
`ID` bigint(20) DEFAULT NULL,
`NAME` varchar(32) DEFAULT NULL,
`LOC` varchar(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql>
3.删除表
删除一张表时,会直接删除表中所保存的所有数据,因此在删除表时应非常小心。
删除表的命令:DROP TABLE TABLE_NAME;
mysql> SHOW TABLES;
+-----------------------+
| Tables_in_DATA_MINING |
+-----------------------+
| AAA |
| T_DEPT |
| f_test |
+-----------------------+
3 rows in set (0.00 sec)
mysql> DROP table f_test
-> ;
Query OK, 0 rows affected (0.11 sec)
mysql> show tables;
+-----------------------+
| Tables_in_DATA_MINING |
+-----------------------+
| AAA |
| T_DEPT |
+-----------------------+
2 rows in set (0.00 sec)
mysql>
3.修改表
mysql数据库提供”ALTER TABLE“语句来实现修改表结构。
修改表名:ALTER TABLE OLD_TABLE_NAME RENAME NEW_TABLE_NAME;
mysql> SHOW TABLES;
+-----------------------+
| Tables_in_DATA_MINING |
+-----------------------+
| AAA |
| T_DEPT |
+-----------------------+
2 rows in set (0.01 sec)
mysql> ALTER TABLE T_DEPT RENAME t_dept;
Query OK, 0 rows affected (0.04 sec)
mysql> show tables;
+-----------------------+
| Tables_in_DATA_MINING |
+-----------------------+
| AAA |
| t_dept |
+-----------------------+
2 rows in set (0.00 sec)
mysql> describe t_dept;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID | bigint(20) | YES | | NULL | |
| NAME | varchar(32) | YES | | NULL | |
| LOC | varchar(32) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
增加字段(经常将列称为字段):
在表的最后一个位置增加字段:
ALTER TABLE TABLE_NAME ADD 属性名 属性类型
mysql> DESC t_dept;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID | bigint(20) | YES | | NULL | |
| NAME | varchar(32) | YES | | NULL | |
| LOC | varchar(32) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> ALTER TABLE t_dept ADD DESCRI VARCHAR(20);
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESCRIBE t_dept;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| ID | bigint(20) | YES | | NULL | |
| NAME | varchar(32) | YES | | NULL | |
| LOC | varchar(32) | YES | | NULL | |
| DESCRI | varchar(20) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
在表的第一个位置增加字段:
ALTER TABLE TABLE_NAME ADD COLUMN_NAME COLUMN_ATTR FIRST;
mysql> DESC t_dept;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| ID | bigint(20) | YES | | NULL | |
| NAME | varchar(32) | YES | | NULL | |
| LOC | varchar(32) | YES | | NULL | |
| DESCRI | varchar(20) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql> ALTER TABLE t_dept ADD ATTR VARCHAR(32) FIRST;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC t_dept;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| ATTR | varchar(32) | YES | | NULL | |
| ID | bigint(20) | YES | | NULL | |
| NAME | varchar(32) | YES | | NULL | |
| LOC | varchar(32) | YES | | NULL | |
| DESCRI | varchar(20) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
在表的指定字段之后增加字段
ALTER TABLE TABLE_NAME ADD column_name column_attr AFTER column_name;
mysql> DESC t_dept;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| ATTR | varchar(32) | YES | | NULL | |
| ID | bigint(20) | YES | | NULL | |
| NAME | varchar(32) | YES | | NULL | |
| LOC | varchar(32) | YES | | NULL | |
| DESCRI | varchar(20) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
mysql> ALTER TABLE t_dept ADD ATTR1 VARCHAR(32) AFTER
-> ID;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC TABLE t_dept;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TABLE t_dept' at line 1
mysql> DESC t_dept;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| ATTR | varchar(32) | YES | | NULL | |
| ID | bigint(20) | YES | | NULL | |
| ATTR1 | varchar(32) | YES | | NULL | |
| NAME | varchar(32) | YES | | NULL | |
| LOC | varchar(32) | YES | | NULL | |
| DESCRI | varchar(20) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
删除字段
ALTER TABLE TABLE_NAME
DROP column_name;
mysql> DESC t_dept;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| ATTR | varchar(32) | YES | | NULL | |
| ID | bigint(20) | YES | | NULL | |
| ATTR1 | varchar(32) | YES | | NULL | |
| NAME | varchar(32) | YES | | NULL | |
| LOC | varchar(32) | YES | | NULL | |
| DESCRI | varchar(20) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> ALTER TABLE t_dept DROP ATTR;
Query OK, 0 rows affected (0.21 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC t_dept;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| ID | bigint(20) | YES | | NULL | |
| ATTR1 | varchar(32) | YES | | NULL | |
| NAME | varchar(32) | YES | | NULL | |
| LOC | varchar(32) | YES | | NULL | |
| DESCRI | varchar(20) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
修改字段
修改字段的数据类型
ALTER TABLE TABLE_NAME
MODIFY column_name 数据类型
mysql> DESC T_DEPT;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| ID | bigint(20) | YES | | NULL | |
| ATTR1 | varchar(32) | YES | | NULL | |
| NAME | varchar(32) | YES | | NULL | |
| LOC | varchar(32) | YES | | NULL | |
| DESCRI | varchar(20) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
mysql> ALTER TABLE MODIFY ATTR1 VARCHAR(20);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ATTR1 VARCHAR(20)' at line 1
mysql> ALTER TABLE T_DEPT MODIFY ATTR1 VARCHAR(20);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC T_DEPT;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| ID | bigint(20) | YES | | NULL | |
| ATTR1 | varchar(20) | YES | | NULL | |
| NAME | varchar(32) | YES | | NULL | |
| LOC | varchar(32) | YES | | NULL | |
| DESCRI | varchar(20) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
修改字段的名称和数据类型:
ALTER TABLE TABLE_NAME
CHANGE 旧属性名 新属性名 数据类型
mysql> DESC T_DEPT;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| ID | bigint(20) | YES | | NULL | |
| ATTR1 | varchar(20) | YES | | NULL | |
| NAME | varchar(32) | YES | | NULL | |
| LOC | varchar(32) | YES | | NULL | |
| DESCRI | varchar(20) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> ALTER TABLE T_DEPT ATTR1 ATTR VARCHAR(32);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ATTR1 ATTR VARCHAR(32)' at line 1
mysql> ALTER TABLE T_DEPT CHANGE ATTR1 ATTR VARCHAR(32);
Query OK, 0 rows affected (0.25 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC T_DEPT;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| ID | bigint(20) | YES | | NULL | |
| ATTR | varchar(32) | YES | | NULL | |
| NAME | varchar(32) | YES | | NULL | |
| LOC | varchar(32) | YES | | NULL | |
| DESCRI | varchar(20) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
修改字段的顺序
ALTER TABLE TABLE_NAME
MODIFY 属性名1 数据类型 FIRST|AFTER 属性名2
4.操作表的约束
对于已经创建好的表,虽然字段的数据类型决定了所能存储的数据类型,但表中存储的数据是否合法并没有进行检查。在具体使用mysql时,如果想针对表中的数据做一些完整性的检查操作,可以通过约束来完成。
常用五类约束:
not null:非空约束,指定某列不为空
unique: 唯一约束,指定某列和几列组合的数据不能重复
primary key:主键约束,指定某列的数据不能重复、唯一
foreign key:外键,指定该列记录属于主表中的一条记录,参照另一条数据
check:检查,指定一个表达式,用于检验指定数据
AUTO_INCREMENT:约束字段的值为自动增加。
注意: MySQL不支持check约束,但可以使用check约束,而没有任何效果;
根据约束数据列限制,约束可分为:
单列约束:每个约束只约束一列
多列约束:每个约束约束多列数据
mysql 中不支持check约束,即可以使用check约束但是却没有任何效果。
设置非空约束:
非空约束用于确保当前列的值不为空值,非空约束只能出现在表对象的列上。
Null类型特征: 所有的类型的值都可以是null,包括int、float等数据类型 空字符串””是不等于null,0也不等于null
create table temp(
id int not null,
name varchar(255) not null default ‘abc’,
sex char null
)
上面的table加上了非空约束,也可以用alter来修改或增加非空约束
增加非空约束
alter table temp modify sex varchar(2) not null;
取消非空约束
alter table temp modify sex varchar(2) null;
取消非空约束,增加默认值
alter table temp modify sex varchar(2) default ‘abc’;
设置字段的默认值:
设置唯一约束(UNIQUE,UK):
唯一约束是指定table的列或列组合不能重复,保证数据的唯一性。虽然唯一约束不允许出现重复的值,但是可以为多个null,同一个表可以有多个唯一约束,多个列组合的约束。在创建唯一约束的时候,如果不给唯一约束名称,就默认和列名相同。 MySQL会给唯一约束的列上默认创建一个唯一索引;
create table temp (
id int not null,
name varchar(25),
password varchar(16),
constraint uk_name_pwd unique(name, password)
);
表示用户名和密码组合不能重复
添加唯一约束
alter table temp add unique(name, password);
修改唯一性约束
alter table temp modify name varchar(25) unique;
删除约束
alter table temp drop index name;
建表时设置唯一约束(UNIQUE,UK)
mysql> CREATE TABLE D_DEPT(
-> ID BIGINT,
-> NAME VARCHAR(32),
-> ATTR VARCHAR(32)
-> ,
-> CONSTRAINT UK_NAME UNIQUE(NAME)
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> DESC D_DEPT;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID | bigint(20) | YES | | NULL | |
| NAME | varchar(32) | YES | UNI | NULL | |
| ATTR | varchar(32) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
设置主键约束
mysql> CREATE TABLE AAB( DEPTNO INT PRIMARY KEY, DNAME VARCHAR(20) );
Query OK, 0 rows affected (1.01 sec)
mysql>
设置字段值自增列
mysql> CREATE TABLE AAD( DEPTNO INT PRIMARY KEY AUTO_INCREMENT, DNAME VARCHAR(20) );
Query OK, 0 rows affected (2.82 sec)
mysql> DESC AAD;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| DEPTNO | int(11) | NO | PRI | NULL | auto_increment |
| DNAME | varchar(20) | YES | | NULL | |
+--------+-------------+------+-----+---------+----------------+
2 rows in set (0.02 sec)
设置外键约束(FOREIGN KEY,FK)
外键约束构建多个表两个字段之间的参照关系。设置外键约束的两个表之间会有父子关系,即子表中某个字段的取值范围由父表所决定。
例如:
表示一种部门和雇员之间的关系。
部门表
部门编号 部门名 主要业务
1 数据库 111
2 总线 222
3 采集 333
雇员表:
雇员编号 名字 所属部门
1 liyang 1
2 zcw 1
3 wy 1
4 wrl 2
部门表的部门编号字段就是雇员表的外键。
创建语法请参见mysq数据库从入门到精通
关键语法:
CONSTRAINT FK_DEPTNO FOREIGN KEY(列名)
REFERENCES 表名(列名)
索引
索引的操作
数据库对象表时存储和操作数据的逻辑结构,而索引则是一种有效组合数据的方式,通过索引对象可以快速查询到数据库对象表中的特定记录,是一种提高性能的常用方法。
一个索引会包含表中按照一定顺序排列的一列或多列字段。索引的操作包括创建索引、修改索引和删除索引。
数据库对象的索引其实与书的目录非常相似,主要是为了提高从表中检索数据的速度。由于数据存储在数据库表中,所以索引是创建在数据库表对象上的,由表中的一个字段或多个字段生成的键组成。这些键存储在数据结构(B树或哈希表)中,根据索引的存储类型可以将索引分为B型树索引和哈希索引。
InnoDB和MyISAM存储引擎支持BTREE类型索引,MEMORY存储引擎支持HASH类型索引,默认为前者索引。
mysql支持六种索引:普通索引、唯一索引、全文索引、单列索引、多列索引和空间索引。
创建索引可以提高查询效率,但过多的创建索引则会占据许多的磁盘空间。
以下情况下适合创建索引:
- 经常被查询的字段,即在WHERE子句中出现的字段;
- 在分组的字段,即在GROUP BY子句中出现的字段;
- 存在依赖关系的子表和父表之间的联合查询,即主键或外键字段;
- 设置唯一完整性约束的字段;
- 以下的情况不适合创建索引:
- 在查询中很少被使用的字段;
- 拥有许多重复值的字段。
创建索引的命令:
CREATE TABLE T_DEPT(
DEPT INT,
DNAME VARCHAR(20),
INDEX index_deptno(DEPTNO)//INDEX 【索引名】(属性名 【长度】 【ASC|DESC】)
);
查看创建的索引:SHOW CREATE TABLE T_DEPT \G
检验索引是否被使用:
EXPLAIN
SELECT * FROM T_DEPT WHERE DEPTNO=1 \G;
在已经存在的表上创建普通索引:
CREATE INDEX 索引名
ON 表名(属性名 【(长度)】 【ASC|DESC】)
通过语句创建索引
ALTER TABLES TABLE_NAME
ADD INDEX | KEY 索引名(属性名 【(长度)】 【ASC|DESC】)
创建唯一性索引就是在INDEX关键字之前加上UNIQUE
全文索引主要关联在数据类型为CHAR和VARCHAR和TEXT的字段上。
删除索引
DROP INDEX index_name
ON table_name
路漫漫其修远兮,吾将上下而…