41定义数据表.pptx

第3章 数据库和表 3.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 _ name4、选择数据库、选择数据库USE db_name;5.查看数据库查看数据库SHOW DATABASES;6.查看当前使用数据库查看当前使用数据库select database()查看当前使用数据库创建数据库test13.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保留字必须用单引号括起来。例如：在数据库test1，创建学生情况表XS，将学号定义为主键USE test1;CREATE TABLE XS(StudengNo char(10)NOT NULL PRIMARY KEY,StudentName char(20)NOT NULL,Sex char(2)NOT NULL DEFAULT 1，Birthdaydate,NativeVarchar(20),NationVarchar(10)ClassNo char(6)ENGINE=InnoDB;在上面的例子里，每个字段都包含附加约束或修饰符，这些可以用来增加对所输入数据的约束。“PRIMARY KEY”表示将“学号”字段定义为主键。“DEFAULT 1”表示“性别”的默认值为1。“ENGINE=InnoDB”表示采用的存储引擎是InnoDB，InnoDB是MySQL在Windows平台默认的存储引擎，所以所以“ENGINE=InnoDB”可以省略可以省略。3.2 3.2 创建表创建表3.2 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：用于描述表的选项。3.2 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列，并且它必须被索引。3.2 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章参照完整性中介绍。3.2 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/*索引的路径*/3.2.1 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的同义词。3.2.1 3.2.1 数据类型数据类型（1）数值数据类型数值数据类型3.2.1 3.2.1 数据类型数据类型（2）字符串数据类型字符串数据类型3.2.1 3.2.1 数据类型数据类型（2）字符串数据类型字符串数据类型TEXT 和和 BLOB 类型类型对于字段长度要求超过 255 个的情况下，MySQL 提供了 TEXT 和 BLOB 两种类型。根据存储数据的大小，它们都有不同的子类型。这些大型的数据用于存储文本块或图像、声音文件等二进制数据类型。3.2.1 3.2.1 数据类型数据类型（3）日期和时间数据类型）日期和时间数据类型3.2.1 3.2.1 数据类型数据类型（4）复合类型）复合类型MySQL 还支持两种复合数据类型 ENUM 和 SET，它们扩展了 SQL 规范。虽然这些类型在技术上是字符串类型，但是可以被视为不同的数据类型。一个 ENUM 类型只允许从一个集合中取得一个值；而 SET 类型允许从一个集合中取得任意多个值。3.2.2 NULL3.2.2 NULL值值空值NULL通常表示未知、不可用或将在以后添加的数据。若一个列允许为空值，则向表中输入记录值时可不为该列给出具体值；而一个列若不允许为空值，则在输入时必须给出该列的具体值。注意：表的关键字不允许为空值。空值不能与数值数据0或字符类型的空字符混为一谈。任意两个空值都不相等。3.2.1 创建表-例【例3】创建学生成绩数据库（xscj）中，并在其中创建一个学生情况表，表名xs。输入以下命令：CREATE DATABSExscj;USE xscjCREATE 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)用show tables命令显示xscj数据库中产生了学生（xs）表，用describe xs/desc xs/show columns from xs命令可以显示xs表的结构。如图所示。3.2.1 创建表-例练习1、建立db_school数据库2、在数据库中建立一张学生表tb_student3、按照P36表3.1建立学生表，其中字符型用varchar,前三列非空，籍贯默认为汉。学号为主键。4、在mysql内查看表结构3.2 3.2 创建表创建表2.查看表查看表SHOW TABLES用于查看表，显示表的名称。SHOW COLUMNS FROM tbl_name 或 DESC tbl_name 显示表的结构Show Create table 可以查看建立表时的语句以及引擎、字符集 3.2 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语句。3.复制表复制表【例3.4】假设数据库test1中有一个表xs，创建xs表的一个名为xs_copy1的拷贝。USE TEST1；CREATE TABLE xs_copy1 LIKE xs;3.2 3.2 创建表创建表练习：复制tb_student为tb_stu，并查看3.2 3.2 创建表创建表4.修改表修改表ALTER TABLE用于更改原有表的结构。例如，可以增加或删减列，创建或取消索引，更改原有列的类型，重新命名列或表，还可以更改表的评注和表的类型。语法格式：ALTER IGNORE TABLE tbl_name alter_specification,alter_specification.3.2 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|列或表中索引项的增、删、改说明：tb1_name：表名。col_name：指定的列名。column_definition：定义列的数据类型和属性，具体内容在CREATE TABLE的语法中已做说明。ADDCOLUMN子句：向表中增加新列。例如，在表t1中增加新的一列a：ALTER TABLE t1 ADD COLUMN a TINYINT NULL;3.2 3.2 创建表创建表练习：在tb_student中增加一列StudentID，varchar(20),非空。并查看 ADDCOLUMN子句：向表中增加新列。指定位置：FIRST/AFTER.例如，在表t1中增加新的一列a在b列之后：ALTER TABLE t1 ADD COLUMN a TINYINT NULL AFTER b;3.2 3.2 创建表创建表练习：在tb_student中增加一列年级grade，varchar(4),非空，默认值2017，放在出生日日期列之后。并查看说明：修改字段 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 3.2 创建表创建表CHANGE COLUMN子句：修改列的名称列的名称和数据类型。(注意必须为完整列定义，即不论是否修改数据类型都必 须加上列名和数据类型说明)例如，将Birthday重命名为Age，改为int类型，并添加默认值18.ALTER TABLE 3.2 3.2 创建表创建表请自行练习MODIFY COLUMN子句：修改指定列的类型列的类型。例如，将StudentID的数据类型改为char(20),并移动到StudentName之后。3.2 3.2 创建表创建表请自行练习ALTER COLUMN子句：修改表中指定列的默认值默认值:SET DEFAULT/DROP DEFAULT例如，将Grade的默认值改为20173.2 3.2 创建表创建表请自行练习例如，删除Grade的默认值请自行练习删除列：DROP COLUMN子句：删除指定列例如，删除StudentID列3.2 3.2 创建表创建表请自行练习3.2 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,再用另外一种方法重命名回来。请自行练习3.2 3.2 创建表创建表删除表删除表需要删除一个表时可以使用DROP TABLE语句。语法格式：DROP TEMPORARY TABLE IF EXISTS tbl_name,tbl_name.其中，tb1_name：要被删除的表名。IF EXISTS：避免要删除的表不存在时出现错误信息。这个命令将表的描述、表的完整性约束、索引及和表相关的权限等都全部删除。例：删除表tb_stu请自行练习作业1、复制建立tb_stu表（或根据表3.1重建）2、增加一列系部Department，数据类型varchar(10),不可为空，放到出生日期之后。3、将Department重命名为Dept,设置默认值为机械系4、修改Dept的默认值为“计算机系”5、修改所属班级的数据类型为varchar（10），并移动到Dept之后。5、删除民族列。6、重命名此表为new_stu.