第二章 管理数据库和表
命名注意事项:标识符不能是所用RDBMS的保留字,不允许嵌入空格或其他特殊字符。
激活数据库:USE 数据库名;
删除数据库:DROP DATABASE 数据库名;
创建数据库:CREATE DATABASE 数据库名;
SQL数据类型
整数数据类型
TINYINT(大小:1个字节,极小整数值)
SMALLINT(大小:2个字节,小整数值)
MEDIUMINT(大小:3个字节,小整数值)
INT(大小:4个字节,整数值)
BIGINT(大小:8个字节,大整数值)
浮点数据类型
FLOAT(大小:4个字节,7位小数,单精度浮点数)
DOUBLE(大小:8个字节,15位小数,双精度浮点数)
DECIMAL(M,D)(大小:17个字节,30位小数,大浮点数)
NUMERIC(M,D)(大小:17个字节,30位小数,大浮点数)
注意:M为总位数,D为小数位数,M必须大于D。
字符串数据类型
CHAR(N) (大小N,范围0-255),固定大小
VARCHAR(N)(大小N,范围0-65535),可变大小
注意:N为自定义大小,表示字符串长度。
二进制数据类型
TINYBLOB(大小255,范围0-255)
BLOB(大小65K,范围0-65KB)
MEDIUMBLOB(大小16M,范围0-16M)
LONGBLOB(大小4G,范围0-4G)
逻辑数据类型
BOOLEAN(范围0-1)
注意:MYSQL没有BOOLEAN类型,但BOOLEAN类型可以创建,在MYSQL中BOOLEAN被自定义为TINYINT(1)。
日期数据类型
YEAR(大小1个字节,格式YYYY,范围1901-2155)
DATE(大小3个字节,格式YYYY-MM-DD,范围1000-01-01--9999-12-31)
TIMESTAMP(大小4个字节,范围1970-01-01 00:00:01-2038)
DATATIME(大小8个字节,范围1000-01-01 00:00:00-9999-12-31 23:59:59)
管理SQL表
创建表:CREATE TABLE<表名>(<列名><列的数据类型>[<列的约束>]);
其中表名在一个数据库中必须是唯一的,并且符合标识符规则。列名在一个表内必须是唯一的,并且符合标识符规则。列的数据类型决定了声明杨的数据可以存储在列中,而列的约束定义了创建列时的限制条件。
添加注释:-- 注释
DROP TABLE <表名>:删除表
注意:通过外键约束连接在一起的表不能被删除。在删除表之前必须先删除约束。在删除表时,我们必须是表的所有者或者对该表有管理权限。
复制表
CREATE TEBLE New_student SELECT * FROM student;:复制表的数据和结构(列、数据类型)。
复制表的同时表的约束并不能复制过来。
CREATE TEBLE New_student SELECT * FROM student WHERE 1=0;:只是复制表的结构,不复制数据,需要在WHERE子句中添加一个永远不等于true的条件即可。
DESC 表名;:查询指定表名的表的结构。
修改表
ALTER TABLE student ADD Address VARCHAR(50);:添加新列
ALTER TABLE student CHANGE Phone New_Phone VARCHAR(11);:更改列
ALTER TABLE student DROP COLUMN PhoneNo;:删除列
ALTER TABLE student RENAME new_student;:修改表名
ALTER TABLE student MODIFY 年龄 VARCHAR(4);:修改属性的数据类型
索引
索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。
索引的优点:
1>通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性;
2>可以大大加快数据的检索速度,这也是创建索引的最主要原因;
3>可以加速表与表之间的连接,特别是在实现数据的参考完整性方面特别有意义;
4>在使用分组和排序字句进行数据检索时,同样可以显著减少查询中分组和排序的时间;
5>通过使用索引,可以在查询的过程中,是由优化掩藏器,提高系统的性能。
索引的缺点:
1>创建索引和维护索引要消耗时间,这种事件随着数据量的增加而增加;
2>索引需要占用物理空间,除了数据表占用数据空间以外,每个索引还要占用一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大;
3>当对表中的数据进行增加、删除和修改时,索引也要动态维护,这样就降低了数据的维护速度。
********
索引的使用原则:
1>不要索引数据量不大的表,对于数据量小的表来说,表遍历的成本并不高。
2>不要创建过多的索引,在没有聚集索引的表中,最大可以创建249个非聚集索引,过多的索引首先会带来更大的磁盘空间,而且在数据发生修改时,对索引的维护是特别消耗性能的;
3>合理应用复合索引,有某些情况下可以考虑创建包含所有属性的覆盖索引;
4>对于经常使用范围查询的属性,可以考虑聚集索引;
5>避免对不常用的属性,大字段属性创建索引。
创建索引
CREATE INDEX 索引名 ON 表名(列名一,列名二....);
例:create index student_学号 on student(学号);
删除索引
ALTER TABLE student DROP INDEX student_学号;
命名注意事项:标识符不能是所用RDBMS的保留字,不允许嵌入空格或其他特殊字符。
激活数据库:USE 数据库名;
删除数据库:DROP DATABASE 数据库名;
创建数据库:CREATE DATABASE 数据库名;
SQL数据类型
整数数据类型
TINYINT(大小:1个字节,极小整数值)
SMALLINT(大小:2个字节,小整数值)
MEDIUMINT(大小:3个字节,小整数值)
INT(大小:4个字节,整数值)
BIGINT(大小:8个字节,大整数值)
浮点数据类型
FLOAT(大小:4个字节,7位小数,单精度浮点数)
DOUBLE(大小:8个字节,15位小数,双精度浮点数)
DECIMAL(M,D)(大小:17个字节,30位小数,大浮点数)
NUMERIC(M,D)(大小:17个字节,30位小数,大浮点数)
注意:M为总位数,D为小数位数,M必须大于D。
字符串数据类型
CHAR(N) (大小N,范围0-255),固定大小
VARCHAR(N)(大小N,范围0-65535),可变大小
注意:N为自定义大小,表示字符串长度。
二进制数据类型
TINYBLOB(大小255,范围0-255)
BLOB(大小65K,范围0-65KB)
MEDIUMBLOB(大小16M,范围0-16M)
LONGBLOB(大小4G,范围0-4G)
逻辑数据类型
BOOLEAN(范围0-1)
注意:MYSQL没有BOOLEAN类型,但BOOLEAN类型可以创建,在MYSQL中BOOLEAN被自定义为TINYINT(1)。
日期数据类型
YEAR(大小1个字节,格式YYYY,范围1901-2155)
DATE(大小3个字节,格式YYYY-MM-DD,范围1000-01-01--9999-12-31)
TIMESTAMP(大小4个字节,范围1970-01-01 00:00:01-2038)
DATATIME(大小8个字节,范围1000-01-01 00:00:00-9999-12-31 23:59:59)
管理SQL表
创建表:CREATE TABLE<表名>(<列名><列的数据类型>[<列的约束>]);
其中表名在一个数据库中必须是唯一的,并且符合标识符规则。列名在一个表内必须是唯一的,并且符合标识符规则。列的数据类型决定了声明杨的数据可以存储在列中,而列的约束定义了创建列时的限制条件。
添加注释:-- 注释
DROP TABLE <表名>:删除表
注意:通过外键约束连接在一起的表不能被删除。在删除表之前必须先删除约束。在删除表时,我们必须是表的所有者或者对该表有管理权限。
复制表
CREATE TEBLE New_student SELECT * FROM student;:复制表的数据和结构(列、数据类型)。
复制表的同时表的约束并不能复制过来。
CREATE TEBLE New_student SELECT * FROM student WHERE 1=0;:只是复制表的结构,不复制数据,需要在WHERE子句中添加一个永远不等于true的条件即可。
DESC 表名;:查询指定表名的表的结构。
修改表
ALTER TABLE student ADD Address VARCHAR(50);:添加新列
ALTER TABLE student CHANGE Phone New_Phone VARCHAR(11);:更改列
ALTER TABLE student DROP COLUMN PhoneNo;:删除列
ALTER TABLE student RENAME new_student;:修改表名
ALTER TABLE student MODIFY 年龄 VARCHAR(4);:修改属性的数据类型
索引
索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。
索引的优点:
1>通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性;
2>可以大大加快数据的检索速度,这也是创建索引的最主要原因;
3>可以加速表与表之间的连接,特别是在实现数据的参考完整性方面特别有意义;
4>在使用分组和排序字句进行数据检索时,同样可以显著减少查询中分组和排序的时间;
5>通过使用索引,可以在查询的过程中,是由优化掩藏器,提高系统的性能。
索引的缺点:
1>创建索引和维护索引要消耗时间,这种事件随着数据量的增加而增加;
2>索引需要占用物理空间,除了数据表占用数据空间以外,每个索引还要占用一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大;
3>当对表中的数据进行增加、删除和修改时,索引也要动态维护,这样就降低了数据的维护速度。
********
索引的使用原则:
1>不要索引数据量不大的表,对于数据量小的表来说,表遍历的成本并不高。
2>不要创建过多的索引,在没有聚集索引的表中,最大可以创建249个非聚集索引,过多的索引首先会带来更大的磁盘空间,而且在数据发生修改时,对索引的维护是特别消耗性能的;
3>合理应用复合索引,有某些情况下可以考虑创建包含所有属性的覆盖索引;
4>对于经常使用范围查询的属性,可以考虑聚集索引;
5>避免对不常用的属性,大字段属性创建索引。
创建索引
CREATE INDEX 索引名 ON 表名(列名一,列名二....);
例:create index student_学号 on student(学号);
删除索引
ALTER TABLE student DROP INDEX student_学号;