《数据库原理与应用》PPT1第4章 MySQL数据库和表.ppt_163文库

资源描述

2、schem：保存关于MySQL服务器所维护的所有其他数据库的信息。如数据库名、数据库的表、表栏的数据类型与访问权限等。performance_schema：主要用于收集数据库服务器性能参数。sys：该数据库里面包含了一系列的存储过程、自定义函数以及视图，存储了许多系统的元数据信息。4.1 MySQL数据库简介数据库简介数据库原理与应用（基于MySQL）34.2 定义数据库定义数据库4.2.1创建数据库创建数据库创建数据库使用CREATE DATABASE语句。语法格式：语法格式：CREATE DATABASE|SCHEMA IF NOT EXISTS db_nameDEFAULT CHARAC

3、TER SET charset_nameDEFAULT COLLATE collation_name;说明：说明：语句中“”为可选语法项，“”为必选语法项，“|”分隔括号或大括号中的语法项，只能选择其中一项。db_name：数据库名称。IF NOT EXISTS：在创建数据库前进行判断，只有该数据库目前尚不存在时才执行CREATE DATABASE操作。数据库原理与应用（基于MySQL）4 CHARACTER SET：指定数据库字符集。COLLATE：指定字符集的校对规则。DEFAULT：指定默认值。【例4.2】创建名称为stusys的学生信息数据库，该数据库是本书的重要数据库。在MySQL命

5、择数据库选择数据库选择数据库使用USE语句。语法格式：语法格式：USE db_name;4.2 定义数据库定义数据库4.2.3 修改数据库修改数据库修改数据库使用ALTER DATABASE语句。语法格式：语法格式：ALTER DATABASE|SCHEMA db_name DEFAULT CHARACTER SET charset_nameDEFAULT COLLATE collation_name;【例4.3】选择stusys为当前数据库。mysql USE stusys;执行结果：执行结果：Database changed数据库原理与应用（基于MySQL）7 说明：说明：数据库名称可以省

6、略，表示修改当前(默认)数据库。选项CHARACTER SET和COLLATE与创建数据库语句相同。【例4.4】修改数据库stusys的默认字符集和校对规则。mysql ALTER DATABASE stusys -DEFAULT CHARACTER SET gb2312 -DEFAULT COLLATE gb2312_chinese_ci;执行结果：执行结果：Query OK,1 row affected(0.31 sec)4.2 定义数据库定义数据库数据库原理与应用（基于MySQL）84.2.4 删除数据库删除数据库删除数据库使用DROP DATABASE语句。语法格式：语法格式：DRO

7、P DATABASE|SCHEMA IF EXISTS db_name 说明：说明：db_name：指定要删除的数据库名称。DROP DATABASE 或 DROP SCHEMA：该命令会删除指定的整个数据库，数据库中所有的表和所有数据也将被永久删除，并不给出任何提示确认的信息。因此，删除数据库要特别小心。IF EXISTS：使用该子句，可避免删除不存在的数据库时出现MySQL错误信息。4.2 定义数据库定义数据库数据库原理与应用（基于MySQL）9【例4.5】删除数据库stusys。mysql DROP DATABASE stusys;执行结果：执行结果：Query OK,0 rows af

9、。表4.1 学生表(student)数据库原理与应用（基于MySQL）学号姓名性别出生日期专业总学分191001刘清泉男1998-06-21计算机52191002张慧玲女1999-11-07计算机50191003冯涛男1999-08-12计算机52196001董明霞女1999-05-02通信48196002李茜女1998-07-25通信52196004周俊文男1998-03-08通信50114.3 表的基本概念表的基本概念表包含以下基本概念：（1）表表是数据库中存储数据的数据库对象，每个数据库包含了若干个表，表由行和列组成。例如，表4.1由6行6列组成。（2）表结构每个表具有一定的结构，

10、表结构包含一组固定的列，列由数据类型、长度、允许Null值、键、默认值等组成。（3）记录每个表包含若干行数据，表中一行称为一个记录(Record)。表4.1有6个记录。（4）字段表中每列称为字段(Field)，每个记录由若干个数据项（列）构成，构成记录的每个数据项就称为字段。表4.1有6个字段。数据库原理与应用（基于MySQL）124.3 表的基本概念表的基本概念（5）空值空值(Null)通常表示未知、不可用或将在以后添加的数据。（6）关键字关键字用于唯一标识记录，如果表中记录的某一字段或字段组合能唯一标识记录，则该字段或字段组合称为候选键。如果一个表有多个候选键，则选定其中的一个为主键(

11、Primary Key)。表4.1的主键为“学号”。（7）默认值默认值指在插入数据时，当没有明确给出某列的值，系统为此列指定一个值。在MySQL中，默认值即关键字DEFAULT。数据库原理与应用（基于MySQL）134.3 表的基本概念表的基本概念4.3.2 表结构设计表结构设计在数据库设计过程中，最重要的是表结构设计，好的表结构设计，对应着较高的效率和安全性，而差的表设计，对应着差的效率和安全性。创建表的核心是定义表结构及设置表和列的属性，创建表以前，首先要确定表名和表的属性，表所包含的列名、列的数据类型、长度、是否为空、键、默认值等，这些属性构成表结构。在基本数据库stusys中的学生表

12、student、课程表course、成绩表score、教师表teacher、讲课表lecture的表结构，参见页“附录B 基本数据库stusys的表结构和样本数据”。其中，学生表student的表结构介绍如下：学生表student包含sno,sname,ssex,sbirthday,speciality,tc等列。数据库原理与应用（基于MySQL）144.3 表的基本概念表的基本概念（1）sno列是学生的学号，例如191001中19表示2019年入学，01表示学生的序号，sno列的数据类型选字符型char(n)，n的值为6，不允许空，无默认值，在student表中，只有sno列能唯一标识一个

13、学生，所以将sno列设为主键；（2）sname列是学生的姓名，姓名一般不超过4个中文字符，所以选字符型char(n)，n的值为8，不允许空，无默认值；（3）ssex列是学生的性别，选字符型char(n)，n的值为2，不允许空，默认值为”男”；（4）sbirthday列是学生的出生日期，选date数据类型，不允许空，无默认值；（5）speciality列是学生的专业，选字符型char(n)，n的值为12，允许空，无默认值；数据库原理与应用（基于MySQL）154.3 表的基本概念表的基本概念（6）tc列是学生的总学分，选tinyint数据类型，允许空，无默认值。student的表结构设计如表4.

14、2所示。表4.2 student的表结构数据库原理与应用（基于MySQL）列名数据类型允许null值键默认值说明SnoChar(6)主键无学号SnameChar(8)无姓名SsexChar(2)男性别SbirthdayDate无出生日期SpecialityChar(12)无专业tctinyint无总学分164.4 数据类型数据类型MySQL的数据类型包括数值类型、字符串类型、日期和时间类型、二进制数据类型、其它类型等。下面分别介绍MySQL的数据类型。4.4.1 数值类型数值类型1.整数类型整数类型整数类型包括tinyint、smallint、mediumint、int、bigint等类型，

15、integer是int的同义词，其字节数和取值范围如表4.3所示。表4.3 数值型数据库原理与应用（基于MySQL）数据类型字节数无符号数取值范围有符号数取值范围tinyint1 0255-128127smallint2 065535-3276832767mediumint3 016777215-83886088388607int integer4 04294967295-21474836482147483647bigint8 01.8410199.221018174.4 数据类型数据类型2.定点数类型定点数类型定点数类型用于存储定点数，保存必须为确切精度的值。在MySQL中，decimal

16、(m,d)和numeric(m,d)视为相同的定点数类型，m是小数总位数，d是小数点后面的位数。m的取值范围为165，取0时会被设为默认值，超出范围会报错。d的取值范围为030，而且必须d=m，超出范围会报错。m的默认取值为10，d默认取值为0。dec是 decimal的同义词。3.浮点数类型浮点数类型浮点数类型包括单精度浮点数float类型和双精度浮点数double类型。MySQL中的浮点数类型有float(m,d)，double(m,d)，m是小数位数的总数，d是小数点后面的位数。数据库原理与应用（基于MySQL）184.4 数据类型数据类型（1）float占4字节，其中，1位为符号位，8

17、位表示指数，23位为尾数。在float(m,d)中，m=6时，数字通常是准确的，即float只保证6位有效数字的准确性。（2）double占8字节，其中，1位为符号位，11位表示指数，52位为尾数。在double(m,d)中，m=16时，数字通常是准确的，即double只保证16位有效数字的准确性。说明：说明：数字类型的选择应遵循如下原则：（1）选择最小的可用类型，如果该字段的值不超过127，则使用tinyint比int效果好。（2）对于完全都是数字的，即无小数点时，可以选择整数类型，比如年龄。（3）浮点类型用于可能具有的小数部分的数，比如学生成绩。（4）在需要表示金额等货币类型时优先选择de

18、cimal数据类型。数据库原理与应用（基于MySQL）194.4 数据类型数据类型4.4.2 字符串类型字符串类型常用的字符串类型有char(n)、varchar(n)、tinytext、text等，如表4.4所示。表4.4 字符串类型数据库原理与应用（基于MySQL）数据类型取值范围说明char(n)0255个字符固定长度字符串varchar(n)065535个字符可变长度字符串tinytext0255个字符可变长度短文本text065535个字符可变长度长文本204.4 数据类型数据类型说明：说明：（1）char(n)和varchar(n)中括号中n代表字符的个数，并不代表字节个数，所以

19、当使用了中文的时候(UTF8)意味着可以插入n个中文，但是实际会占用n*3个字节。（2）char和varchar最大的区别就在于char不管实际值都会占用n个字符的空间，而varchar只会占用实际字符应该占用的空间+1，并且实际空间+1=n。（3）实际超过char和varchar的n设置后，字符串后面超过部分会被截断。（4）char的上限为255字节，varchar的上限65535字节，text的上限为65535。（5）char在存储的时候会截断尾部的空格，varchar和text不会。数据库原理与应用（基于MySQL）214.4 数据类型数据类型4.4.3 日期和时间类型日期和时间类型My

20、SQL主要支持5种日期和时间类型：date、time、datetime、timestamp、year，取值范围和格式如表4.5所示。表4.5 日期和时间类型数据库原理与应用（基于MySQL）数据类型取值范围格式说明date 1000-01-019999-12-03YYYY-MM-DD日期 time-838:58:59835:59:59 HH:MM:SS时间 datetime 1000-01-01 00:00:009999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS日期和时间 timestamp 1970-01-01 00:00:00 2037年YYYY-MM-DD

21、HH:MM:SS时间标签year 1901-2155 YY或YY YY年份224.4 数据类型数据类型4.4.4 二进制数据类型二进制数据类型二进制串类型包含binary和blob类。1.binarybinary和varbinary类型类似于char和varchar类型，但是不同的是，它们存储的不是字符字符串，而是二进制串。所以它们没有字符集，并且排序和比较需要基于列字节的数值。当保存binary值时，在它们右边填充0 x00值以达到指定长度。取值时不删除尾部的字节。比较时注意空格和0 x00是不同的（0 x00 USE stusys;Database changedmysql CREATE

22、TABLE student -(-sno char(6)NOT NULL PRIMARY KEY,-sname char(8)NOT NULL,-ssex char(2)NOT NULL DEFAULT 男,-sbirthday date NOT NULL,-speciality char(12)NULL,-tc tinyint NULL -);执行结果：执行结果：Query OK,0 rows affected(0.26 sec)数据库原理与应用（基于MySQL）284.5 定义表定义表2.复制已有表使用直接复制数据库中已有表的结构和数据来创建一个表，更加方便和快捷。语法格式：语法格式：CR

23、EATE TEMPORARY TABLE IF NOT EXISTS table_name ()LIKE old_table_name|AS(SELECT_statement);说明：说明：LIKE old_table_name：使用LIKE关键字创建一个与”源表名”相同结构的新表，但是表的内容不会复制。AS(SELECT_statement)：使用AS关键字可以复制表的内容，但索引和完整性约束不会复制。数据库原理与应用（基于MySQL）294.5 定义表定义表【例4.7】在stusys数据库中，使用复制方式创建student1表，表结构取自student表。mysql USE stusys;

24、Database changedmysql CREATE TABLE student1 like student;执行结果：Query OK,0 rows affected(0.23 sec)数据库原理与应用（基于MySQL）304.5 定义表定义表4.5.2 查看表查看表1.查看表的名称可以使用SHOW TABLES语句查看表的名称。语法格式：语法格式：SHOW TABLES FROM|IN db_name;其中，使用选项 FROM|IN db_name可以显示非当前数据库中的表名。数据库原理与应用（基于MySQL）314.5 定义表定义表【例4.8】查看数据库stusys中所有表名。mys

25、ql USE stusys;Database changedmysql SHOW TABLES;执行结果：执行结果：+-+|Tables_in_stusys|+-+|student|student1|+-+2 rows in set(0.18 sec)数据库原理与应用（基于MySQL）324.5 定义表定义表2.查看表的基本结构查看表的基本结构使用SHOW COLUMNS语句或DESCRIBE/DESC语句可以查看表的基本结构，包括列名、列的数据类型、长度、是否为空、是否为主键、是否有默认值等。（1）使用SHOW COLUMNS语句查看表的基本结构语法格式：语法格式：SHOW COLUMNS

28、mysql SHOW CREATE TABLE studentG执行结果：执行结果：*1.row*数据库原理与应用（基于MySQL）354.5 定义表定义表Table:studentCreate Table:CREATE TABLE student(sno char(6)NOT NULL,sname char(8)NOT NULL,ssex char(2)NOT NULL DEFAULT 男,sbirthday date NOT NULL,speciality char(12)DEFAULT NULL,tc tinyint(4)DEFAULT NULL,PRIMARY KEY(sno)ENGI

29、NE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set(0.00 sec)数据库原理与应用（基于MySQL）364.5 定义表定义表4.5.3 修改表修改表修改表使用ALTER TABLE语句。语法格式：语法格式：ALTER IGNORE TABLE tbl_name alter_specification,alter_specification.alter_specification:数据库原理与应用（基于MySQL）374.5 定义表定义表ADD COLUMN column_definition FI

30、RST|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 B

31、Y col_name /*排序*/|CONVERT TO CHARACTER SET charset_name COLLATE collation_name /*将字符集转换为二进制*/|DEFAULT CHARACTER SET charset_name COLLATE collation_name /*修改默认字符集*/数据库原理与应用（基于MySQL）384.5 定义表定义表1.添加列添加列在ALTER TABLE语句中，可使用ADD COLUMN子句添加列：增加无完整性约束条件的列，增加有完整性约束条件的列，在表的第一个位置增加列，在表的指定位置之后增加列。【例4.11】在数据库stu

32、sys的student表中增加一列sid，添加到表的第1列，不为空，取值唯一并自动增加。mysql ALTER TABLE stusys.student -ADD COLUMN sid int NOT NULL UNIQUE AUTO_INCREMENT FIRST;执行结果：执行结果：Query OK,0 rows affected(0.35 sec)Records:0 Duplicates:0 Warnings:0使用DESC语句查看表student。mysql DESC stusys.student1;数据库原理与应用（基于MySQL）394.5 定义表定义表显示结果：显示结果：+-+-

33、+-+-+-+-+|Field|Type|Null|Key|Default|Extra|+-+-+-+-+-+-+|sid|int(11)|NO|UNI|NULL|auto_increment|sno|char(6)|NO|PRI|NULL|sname|char(8)|NO|NULL|ssex|char(2)|NO|男|sbirthday|date|NO|NULL|speciality|char(12)|YES|NULL|tc|tinyint(4)|YES|NULL|+-+-+-+-+-+-+7 rows in set(0.01 sec)数据库原理与应用（基于MySQL）404.5 定义表定义

34、表2.修改列修改列ALTER TABLE语句有3个修改列的子句：ALTER COLUMN 子句：该子句用于修改或删除表中指定列的默认值。CHANGE COLUMN 子句：该子句可同时修改表中指定列的名称和数据类型。MODIFY COLUMN 子句：该子句只可修改表中指定列的名称，还可修改指定列在表中的位置。数据库原理与应用（基于MySQL）414.5 定义表定义表【例4.12】将stusys数据库的student1表的列sbirthday修改为sage，将数据类型改为tinyint，可空，默认值为18。mysql ALTER TABLE stusys.student1 -CHANGE COLU

36、NO|NULL|ssex|char(2)|NO|男|sage|tinyint(4)|YES|18|speciality|char(12)|YES|NULL|tc|tinyint(4)|YES|NULL|+-+-+-+-+-+-+6 rows in set(0.07 sec)数据库原理与应用（基于MySQL）434.5 定义表定义表3.删除列删除列在ALTER TABLE语句中，可通过DROP COLUMN子句完成删除列的功能。【例4.13】删除数据库stusys的表student中的列sid。mysql ALTER TABLE stusys.student -DROP COLUMN sid;执

37、行结果：执行结果：Query OK,0 rows affected(0.28 sec)Records:0 Duplicates:0 Warnings:0 数据库原理与应用（基于MySQL）444.5 定义表定义表4.重命名表重命名表可以使用ALTER TABLE语句中的RENAME TO子句重命名表，也可使用RENAME TABLE语句重命名表。（1）RENAME TO子句【例4.14】在stusys数据库中，将 student1表重命名为student2表。mysql ALTER TABLE stusys.student1 -RENAME TO stusys.student2;执行结果：执行

38、结果：Query OK,0 rows affected(0.10 sec)数据库原理与应用（基于MySQL）454.5 定义表定义表（2）RENAME TABLE语句RENAME TABLE语句的语法格式如下：RENAME TABLE old_table_name TO new_table_name,old_table_name TO new_table_name.【例4.15】在stusys数据库中，将 student2表重命名为student3表。mysql RENAME TABLE stusys.student2 TO stusys.student3;执行结果：执行结果：Query OK

39、,0 rows affected(0.23 sec)数据库原理与应用（基于MySQL）464.5 定义表定义表4.5.4 删除表删除表删除表使用DROP TABLE语句。语法格式：语法格式：DROP TEMPORARY TABLE IF NOT EXISTS table_name,table_name.【例4.16】删除stusys数据库中的student3表。mysql DROP TABLE stusys.student3;执行结果：执行结果：Query OK,0 rows affected(0.14 sec)数据库原理与应用（基于MySQL）数据库原理与应用（基于MySQL）474.6 存

40、储引擎存储引擎使用SHOW ENGINES命令查看存储引擎。【例4.17】查看存储引擎。mysql SHOW ENGINES;执行结果：执行结果：+-+-+-+-+-+-+|Engine|Support|Comment|Transactions|XA|Savepoints|+-+-+-+-+-+-+|MEMORY|YES|Hash based,stored in memory,useful for temporary tables|NO|NO|NO|MRG_MYISAM|YES|Collection of identical MyISAM tables|NO|NO|NO|CSV|YES|CSV

41、 storage engine|NO|NO|NO|FEDERATED|NO|Federated MySQL storage engine|NULL|NULL|NULL|PERFORMANCE_SCHEMA|YES|Performance Schema|NO|NO|NO|MyISAM|YES|MyISAM storage engine|NO|NO|NO|InnoDB|DEFAULT|Supports transactions,row-level locking,and foreign keys|YES|YES|YES|BLACKHOLE|YES|/dev/null storage engine(anything you write to it disappears)|NO|NO|NO|ARCHIVE|YES|Archive storage engine|NO|NO|NO|+-+-+-+-+-+-+9 rows in set(0.28 sec)数据库原理与应用（基于MySQL）48由显示结果可看出，MySQL 8.0支持的存储引擎有：MEMORY、MRG_MYISAM、CSV、FEDERATED、PERFORMANCE_SCHEMA、MyISAM、InnoDB、BLACKHOLE、ARCHIVE 等9种，默认的存储引擎是InnoDB。4.6 存储引擎存储引擎

展开阅读全文