定义数据表MySQLppt课件

第3章 数据库和表,3.1 数据库的创建和使用,1,复习,1、 创建数据库 CREATE DATABASE | SCHEMA IF NOT EXISTS db_name create_specification , create_specification . 2. 修改数据库 ALTER DATABASE | SCHEMA db_name alter_specification , alter_specification . 3. 删除数据库 DROP DATABASE IF EXISTS db _ name 4、选择数据库 USE db_name; 5. 查看数据库 SHOW DATABASES; 6. 查看当前使用数据库 select database()查看当前使用数据库,创建数据库test1,2,3.2 创建表,1、创建表 创建表使用CREATE TABLE命令。 语法格式： CREATE TEMPORARY TABLE IF NOT EXISTS tbl_name ( column_definition , . | index_definition ) table_option select_statement; 说明： TEMPORARY：该关键字表示用CREATE命令新建的表为临时表。不加该关键字创建的表通常称为持久表，在数据库中持久表一旦创建将一直存在，多个用户或者多个应用程序可以同时使用持久表。有时候需要临时存放数据，例如，临时存储复杂的SELECT语句的结果。此后，可能要重复地使用这个结果，但这个结果又不需要永久保存。这时，可以使用临时表。用户可以像操作持久表一样操作临时表。只不过临时表的生命周期较短，而且只能对创建它的用户可见，当断开与该数据库的连接时，MySQL会自动删除它们。 IF NOT EXISTS：在建表前加上一个判断，只有该表目前尚不存在时才执行CREATE TABLE操作。用此选项可以避免出现表已经存在无法再新建的错误。 table_name：要创建的表的表名。该表名必须符合标志符规则，如果有MySQL保留字必须用单引号括起来。,3,3.2 创建表,4,3.2 创建表,1、创建表 创建表使用CREATE TABLE命令。 语法格式： CREATE TEMPORARY TABLE IF NOT EXISTS tbl_name ( column_definition , . | index_definition ) table_option ; 说明： column_definition：列定义，包括列名、数据类型，可能还有一个空值声明和一个完整性约束。 index_definition：表索引项定义，主要定义表的索引、主键、外键 table_option：用于描述表的选项。,5,3.2 创建表,列定义column_definition格式如下： col_name type NOT NULL | NULL DEFAULT default_value AUTO_INCREMENT UNIQUE KEY | PRIMARY KEY COMMENT string reference_definition 说明： col_name：表中列的名字。列名必须符合标志符规则，长度不能超过64个字符，而且在表中要唯一。如果有MySQL保留字必须用单引号括起来。 type：列的数据类型，有的数据类型需要指明长度n，并用括号括起 NOT NULL | NULL：指定该列是否允许为空。如果不指定，则默认为NULL。 AUTO_INCREMENT：设置自增属性，只有整型列才能设置此属性。当插入NULL值或0到一个AUTO_INCREMENT列中时，列被设置为value+1，在这里value是此前表中该列的最大值。AUTO_INCREMENT顺序从1开始。每个表只能有一个AUTO_INCREMENT列，并且它必须被索引。,6,3.2 创建表,列定义column_definition格式如下： col_name type NOT NULL | NULL DEFAULT default_value AUTO_INCREMENT UNIQUE KEY | PRIMARY KEY COMMENT string reference_definition 说明： DEFAULT default_value：为列指定默认值，默认值必须为一个常数。其中，BLOB和TEXT列不能被赋予默认值。如果没有为列指定默认值，MySQL自动地分配一个。如果列可以取NULL值，默认值就是NULL。如果列被声明为NOT NULL，默认值取决于列类型： （1）对于没有声明AUTO_INCREMENT属性的数字类型，默认值是0。对于一个AUTO_INCREMENT列，默认值是在顺序中的下一个值。 （2）对于除TIMESTAMP以外的日期和时间类型，默认值是该类型适当的“零”值。对于表中第一个TIMESTAMP列，默认值是当前的日期和时间。 （3）对于除ENUM的字符串类型，默认值是空字符串。对于ENUM，默认值是第一个枚举值。 UNIQUE KEY | PRIMARY KEY：PRIMARY KEY和UNIQUE KEY都表示字段中的值是唯一的。PRIMARY KEY表示设置为主键，一个表只能定义一个主键，主键一定要为NOT NULL。 COMMENT string：对于列的描述，string是描述的内容。 reference_definition：指定参照的表和列，具体定义在第8章参照完整性中介绍。,7,3.2 创建表,表选项table_option定义如下 ENGINE | TYPE = engine_name /*存储引擎*/ | AUTO_INCREMENT = value /*初始值*/ | AVG_ROW_LENGTH = value /*表的平均行长度*/ | DEFAULT CHARACTER SET charset_name COLLATE collation_name /*默认字符集和校对*/ | CHECKSUM = 0 | 1 /*设置为1表示求校验和*/ | COMMENT = string /*注释*/ | CONNECTION = connect_string /*连接字符串*/ | MAX_ROWS = value /*行的最大数*/ | MIN_ROWS = value /*列的最小数*/ | PACK_KEYS = 0 | 1 | DEFAULT | PASSWORD = string /*对.frm文件加密*/ | DELAY_KEY_WRITE = 0 | 1 /*对关键字的更新*/ | ROW_FORMAT = DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT /*定义各行应如何储存*/ | UNION = (tbl_name,tbl_name.) /*表示哪个表应该合并*/ | INSERT_METHOD = NO | FIRST | LAST /*是否执行INSERT语句*/ | DATA DIRECTORY = absolute path to directory /*数据文件的路径*/ | INDEX DIRECTORY = absolute path to directory /*索引的路径*/,8,3.2.1 数据类型,MySQL支持多种数据类型，数值类型、日期/时间类型和字符串(字符)类型不区分大小写。 （1） 数值数据类型 MySQL支持所有标准SQL数值数据类型。 MySQL支持 5 个主要整数类型是 TINYINT，SMALLINT，MEDIUMINT，INT 和 BIGINT。这些类型在很大程度上是相同的，只有它们存储的值的大小是不相同的。 MySQL 支持3个浮点类型是 FLOAT、DOUBLE 和 DECIMAL 类型。FLOAT 数值类型用于表示单精度浮点数值，而 DOUBLE 数值类型用于表示双精度浮点数值。 关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。,9,3.2.1 数据类型,（1） 数值数据类型,10,3.2.1 数据类型,（2） 字符串数据类型,11,3.2.1 数据类型,（2） 字符串数据类型 TEXT 和 BLOB 类型 对于字段长度要求超过 255 个的情况下，MySQL 提供了 TEXT 和 BLOB 两种类型。根据存储数据的大小，它们都有不同的子类型。这些大型的数据用于存储文本块或图像、声音文件等二进制数据类型。,12,3.2.1 数据类型,（3）日期和时间数据类型,13,3.2.1 数据类型,（4）复合类型 MySQL 还支持两种复合数据类型 ENUM 和 SET，它们扩展了 SQL 规范。虽然这些类型在技术上是字符串类型，但是可以被视为不同的数据类型。一个 ENUM 类型只允许从一个集合中取得一个值；而 SET 类型允许从一个集合中取得任意多个值。,14,3.2.2 NULL值,空值NULL通常表示未知、不可用或将在以后添加的数据。若一个列允许为空值，则向表中输入记录值时可不为该列给出具体值；而一个列若不允许为空值，则在输入时必须给出该列的具体值。 注意：表的关键字不允许为空值。空值不能与数值数据0或字符类型的空字符混为一谈。任意两个空值都不相等。,15,3.2.1 创建表-例,【例3】创建学生成绩数据库（xscj）中，并在其中创建一个学生情况表，表名xs。 输入以下命令： CREATE DATABSE xscj; use xscj create table xs ( 学号 char(6) not null primary key, 姓名 char(8) not null, 专业名 char(10) null, 性别 tinyint(1) not null default 1, 出生日期 date not null, 总学分 tinyint(1) null, 照片 blob null, 备注 text null ),16,用show tables命令显示xscj数据库中产生了学生（xs）表，用describe xs/desc xs/show columns from xs命令可以显示xs表的结构。如图所示。,3.2.1 创建表-例,17,练习,1、建立db_school数据库 2、在数据库中建立一张学生表tb_student 3、按照P36表3.1建立学生表，其中字符型用varchar, 前三列非空，籍贯默认为汉。学号为主键。 4、在mysql内查看表结构,18,3.2 创建表,2. 查看表 SHOW TABLES用于查看表，显示表的名称。 SHOW COLUMNS FROM tbl_name 或 DESC tbl_name 显示表的结构 Show Create table 可以查看建立表时的语句以及引擎、字符集,19,3.2 创建表,3. 复制表 语法格式： CREATE TEMPORARY TABLE IF NOT EXISTS tbl_name ( ) LIKE old_tbl_name | AS (select_statement); 说明： 使用LIKE关键字创建一个与old_table_name表相同结构的新表，列名、数据类型、空指定和索引也将复制，但是表的内容不会复制，因此创建的新表是一个空表。使用AS关键字可以复制表的内容，但索引和完整性约束是不会复制的。select_statement表示一个表达式，例如，可以是一条SELECT语句。,20,3.2 创建表,练习：复制tb_student为tb_stu，并查看,21,3.2 创建表,4. 修改表 ALTER TABLE用于更改原有表的结构。例如，可以增加或删减列，创建或取消索引，更改原有列的类型，重新命名列或表，还可以更改表的评注和表的类型。 语法格式： ALTER IGNORE TABLE tbl_name alter_specification , alter_specification .,22,3.2 创建表,4. 修改表 alter_specification: ADD COLUMN column_definition FIRST | AFTER col_name /*添加列*/ | ALTER COLUMN col_name SET DEFAULT literal | DROP DEFAULT /*修改默认值*/ | CHANGE COLUMN old_col_name column_definition /*对列重命名*/ FIRST|AFTER col_name | MODIFY COLUMN column_definition FIRST | AFTER col_name /*修改列类型*/ | DROP COLUMN col_name /*删除列*/ | RENAME TO new_tbl_name /*重命名该表*/ | ORDER BY col_name /*排序*/ | CONVERT TO CHARACTER SET charset_name COLLATE collation_name /*将字符集转换为二进制*/ | DEFAULT CHARACTER SET charset_name COLLATE collation_name /*修改默认字符集*/ | table_options | 列或表中索引项的增、删、改,23,3.2 创建表,练习：在tb_student中增加一列StudentID，varchar(20), 非空。并查看,24, ADDCOLUMN子句：向表中增加新列。指定位置： FIRST/AFTER. 例如，在表t1中增加新的一列a在b列之后： ALTER TABLE t1 ADD COLUMN a TINYINT NULL AFTER b;,3.2 创建表,练习：在tb_student中增加一列年级grade，varchar(4), 非空，默认值2017，放在出生日日期列之后。并查看,25,说明：修改字段 FIRST | AFTER col_name：表示在某列的前或后添加，不指定则添加到最后。 ALTER COLUMN子句：修改表中指定列的默认值。 CHANGE COLUMN子句：修改列的名称和数据类型。 重命名时，需给定旧的和新的列名称和列当前的类型，old_col_name表示旧的列名。column_definition中定义新的列名和当前数据类型。 例如，要把一个INTEGER列的名称从a变更到b： ALTER TABLE t1 CHANGE a b INTEGER; MODIFY COLUMN子句：修改指定列的类型。 例如，要把一个列的数据类型改为BIGINT： ALTER TABLE t1 MODIFY b BIGINT NOT NULL;,3.2 创建表,26,CHANGE COLUMN子句：修改列的名称和数据类型。 (注意必须为完整列定义，即不论是否修改数据类型都必 须加上列名和数据类型说明) 例如，将Birthday重命名为Age，改为int类型，并添加默认值18. ALTER TABLE,3.2 创建表,请自行练习,27,MODIFY COLUMN子句：修改指定列的类型。 例如，将StudentID的数据类型改为char(20),并移动到StudentName之后。,3.2 创建表,请自行练习,28,ALTER COLUMN子句：修改表中指定列的默认值: SET DEFAULT/DROP DEFAULT 例如，将Grade的默认值改为2017,3.2 创建表,请自行练习,例如，删除Grade的默认值,请自行练习,29,删除列：DROP COLUMN子句：删除指定列 例如，删除StudentID列,3.2 创建表,请自行练习,30,3.2 创建表,修改表名 方法1 RENAME子句：修改该表的表名，new_tbl_name是新表名。 例如，将表a改名为b： ALTER TABLE a RENAME TO b ; 方法2 RENAME TABLE tbl_name TO new_tbl_name 说明： tbl_name：修改之前的表名。 new_tbl_name：修改之后的表名。,例如：将tb_stu重命名为tb_stu1, 再用另外一种方法重命名回来。,请自行练习,31,3.2 创建表,删除表 需要删除一个表时可以使用DROP TABLE语句。 语法格式： DROP TEMPORARY TABLE IF EXISTS tbl_name , tbl_name . 其中， tb1_name：要被删除的表名。 IF EXISTS：避免要删除的表不存在时出现错误信息。 这个命令将表的描述、表的完整性约束、索引及和表相关的权限等都全部删除。 例：删除表tb_stu,请自行练习,32,作业,1、复制建立tb_stu表（或根据表3.1重建） 2、增加一列系部Department，数据类型varchar(10), 不可为空，放到出生日期之后。 3、将Department重命名为Dept,设置默认值为机械系 4、修改Dept的默认值为“计算机系” 5、修改所属班级的数据类型为varchar（10），并移动到Dept之后。 5、删除民族列。 6、重命名此表为new_stu.,33,