db3数据库数据表创建管理

1、复习MySql的安装实验注意问题数据库设计步骤三范式 创建数据库和数据表SQL语句的书写规范在数据库系统中，SQL语句不区分大小写(建议用大写) 。但字符串常量区分大小写。SQL语句可单行或多行书写，以“;”结尾。关键词不能跨多行或简写。用空格和缩进来提高语句的可读性。子句通常位于独立行，便于编辑，提高可读性。SELECT * FROM tb_table注释：SQL标准：/*/。多行注释 “-” 单行注释MySql注释：“#”创建数据库创建数据库建立数据库操作：建立数据库操作：语法：create database 数据库名数据库名叙述：创建一个具有指定名称的数据库。如果要创建的数据库已经存在，

2、或者没有创建它的适当权限，则此语句失败。例：建立一个student库。 mysql create database student;查看数据库命令：show databases;列出当前数据库服务器实例下所有的数据库切换数据库： use 数据库的名字，例：use students;SHOW DATABASES语法：SHOW DATABASESLIKE wild；如果使用LIKE wild不符，wild字符串可以是一个使用SQL的”%”和”_”通配符的字符串。功能：列出在MySql服务器主机上的数据库。MySql自带数据库：Information_schema:主要存储了系统中的一些数据库对象信

3、息：如用户表信息、列信息、权限信息、字符集信息、分区信息等。(数据字典表)Mysql:存储了系统的用户权限信息及帮助信息。Test:系统自动创建的测试数据库，任何用户都可以使用。使用USE选用数据库语法：USE 数据库名;功能：把指定数据库作为默认(当前)数据库使用，用于后续语句。修改数据库查看数据库信息: status;命令语句：alter database 数据库名字 character set gb2312 修改字符集Default collate gb2312_Chinese_ci; 修改排序规则 数据库对象数据库对象的命名规则必须以字母开头可包括数字和三个特殊字符（# _ $）不要使

4、用MySQL的保留字同一Schema下的对象不能同名建立表操作建立表操作：语法：create table 表名（表名（列名列名1 列类型列类型 ,列名列名2 列类型列类型 , . . );叙 述：在当前数据库下新创建一个数据表。列类型：表示该列的数据类型。例：建立一个表建立一个表school,其由两列组成，第一列属性为非空，并做为主键其由两列组成，第一列属性为非空，并做为主键,并自增并自增create table school( school_id int(10) not null auto_increment primary key, school_name varchar(20) )；创建

5、表常见完整性约束：常见完整性约束：PRIMARY KEY 主码约束(主键)UNIQUE唯一性约束NOT NULL非空值约束AUTO_INCREMENT 用于整数列默认自增1UNSIGNED 无符号整数DEFAULT default_value默认值约束DEFAULT cur_timestamp 创建新记录时默认保存当前时间（仅适用timestamp数据列）ON UPDATE cur_timestamp 修改记录时默认保存当前时间（仅适用timestamp数据列）CHARACTER SET name 指定字符集（仅适用字符串）数据表类型在创建一个新的MySQL数据表时，可以为它设置一个类型，其中

6、最重要的3种类型是MyISAM：成熟、稳定和易于管理InnoDB：加入事务、数据行级锁定机制、外键约束条件、崩溃恢复等新功能HEAP：只存在于内存中，可做临时表create table tmp()ENGINE=MyISAM列类型MySql支持多种列类型：数值类型日期/时间类型字符串(字符)类型数值数据类型-整数数值数据类型-浮点数日期/时间类型字符串类型思考q 电话号码一般使用什么数据类型存储?q 性别一般使用什么数据类型存储?q 年龄信息一般使用什么数据类型存储?q 照片信息一般使用什么数据类型存储?q 薪水一般使用什么数据类型存储?关于识别符建表示例建表示例SHOW语法：SHOW TABL

7、ESFROM 数据库名LIKE wild;功能：显示当前数据库中已有的数据表的信息显示表结构操作显示表结构操作语法：describe 表名；表名； desc 表名；表名；叙述：用于显示表的创建结构。删除数据库或表操作删除数据库或表操作删除表操作语法：drop table if exists tab_name ,tab_name.叙述：从数据库中删除给定的表。如果给出if exists 子句，则删除不存在的表不会出错。删除数据库操作语法：drop database if exists db_name叙述：删除给定的数据库。在删除一个数据库后，它就永远没有了，因此要特别小心。如果给出if exis

8、ts 子句，则删除不存在的数据库不会出错。删除命令要慎用用ALTER TABLE语句修改表的结构修改列类型ALTER TABLE 表名 MODIFY 列名 列类型;增加列ALTER TABLE 表名 ADD 列名 列类型;删除列ALTER TABLE 表名 DROP 列名;列改名ALTER TABLE 表名 CHANGE 旧列名 新列名 列类型；更改表名ALTER TABLE 表名 RENAME 新表名；RENAME TABLE 表名 TO 新表名；在表中增加字段使用ADD 子句增加字段，新的字段只能被加到整个表的最后ALTER TABLE employees ADD (gender CHAR

9、(1);修改字段可修改列的数据类型,大小和默认值ALTER TABLE employeesMODIFY(age number(1);不是任何情况都可以修改的，当字段只包含空值时，类型、大小都可以修改，否则修改可能不能成功修改默认值，只会对以后的插入有作用，对以前已经存在的数据不会有影响删除字段可以从表中删除列：ALTER TABLE employeesDROP COLUMN sex;从每行中删除掉字段占据的长度和数据,释放在数据块中占用的空间.删除大表中的字段将需要比较长的时间，因为还需要回收空间删除表表中所有数据将被删除没有完成的事务被提交所有相关的索引被删除不能回滚表结构被删除，表对象不再

10、存在DROP TABLE table_name;更改表名rename table . to .支持一次重命名多个表。RENAME TABLE old_table1 TO tmp_table, new_table TO old_table,tmp_table TO new_table; 完成表名交换。MYSQL数据库中的表用户表:被用户创建和维护的一些表包括了用户自己的信息数据字典表(视图):被数据库系统创建和维护的一些表包括了数据库的信息MYSQL数据库字典由information_schema数据库负责维护tables存放数据库里所有的数据表、以及每个表所在数据库。schema存放数据库里所

11、有的数据库信息views存放数据库里所有的视图信息。columns存放数据库里所有的列信息。triggers存放数据库里所有的触发器。routines存放数据库里所有存储过程和函数。key_column_usage存放数据库所有的主外键table_constraints存放数据库全部约束。statistics存放了数据表的索引。什么是约束约束是在表上强制执行的数据校验规则.约束主要用于保证数据库的完整性。当表中数据有相互依赖性时,可以保护相关的数据不被删除.大部分数据库支持下面五类完整性约束:NOT NULL非空UNIQUE Key唯一键PRIMARY KEY主键FOREIGN KEY外键CH

12、ECK检查约束概述约束作为数据库对象，存放在系统表中，也有自己的名字创建约束的时机在建表的同时创建建表后创建（修改表）可定义列级或表级约束有单列约束和多列约束定义约束的语法非空约束(NOT NULL)列级约束，只能使用列级约束语法定义。确保字段值不允许为空只能在字段级定义NULL值所有数据类型的值都可以是NULL。空字符串不等于NULL。0也不等于NULL。非空约束(NOT NULL)CREATE TABLE tb_student(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(18) NOT NULL)唯一约束唯一性约束条件确保所在的字段或者字段

13、组合不出现重复值唯一性约束条件的字段允许出现多个NULL同一张表内可建多个唯一约束唯一约束可由多列组合而成建唯一约束时MySQL会为之建立对应的索引。如果不给唯一约束起名，该唯一约束默认与列名相同。唯一约束CREATE TABLE tb_student(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(18) UNIQUE NOT NULL)主键约束主键从功能上看相当于非空且唯一一个表中只允许一个主键主键是表中唯一确定一行数据的字段主键字段可以是单字段或者是多字段的组合当建立主键约束时，MySQL为主键创建对应的索引主键约束名总为PRIMARY。外键

14、约束外键是构建于一个表的两个字段或者两个表的两个字段之间的关系外键确保了相关的两个字段的两个关系：子(从)表外键列的值必须在主表参照列值的范围内，或者为空（也可以加非空约束，强制不允许为空）。当主表的记录被子表参照时，主表记录不允许被删除。外键参照的只能是主表主键或者唯一键，保证子表记录可以准确定位到被参照的记录。格式FOREIGN KEY (外键列名)REFERENCES 主表(参照列)主键与外键数据表之间的关联/引用关系是依靠具体的主键（primary key）和外键（foreign key）建立起来的。主键：帮助MySQL以最快的速度把一条特点的数据记录的位置确定下来。主键必须是唯一的主

15、键应该是紧凑的，因此整数类型比较适合外键：引用另外一个数据表的某条记录。外键列类型尽可能与主键列类型保持一致外键列应该加上NOT NULL主键create table student(sid int not null auto_increment,name varchar(20) not null,primary key(sid);外键（自动检查外键是否匹配，仅适用自动检查外键是否匹配，仅适用InnoDB）create table score(cid int not null auto_increment primary key,score int,sid int,foreign key(si

16、d) references student(sid);主表和从表1、当主表中没有对应的记录时，不能将记录添加到子表成绩表中不能出现在学员信息表中不存在的学号；2、不能更改主表中的值而导致子表中的记录孤立把学员信息表中的学号改变了，学员成绩表中的学号也应当随之改变；3、子表存在与主表对应的记录，不能从主表中删除该行不能把有成绩的学员删除了4、删除主表前，先删子表先删学员成绩表、后删除学员信息表选择主键的原则q最少性q尽量选择单个键作为主键q稳定性q尽量选择数值更新少的列作为主键主键约束CREATE TABLE tb_student(id INT PRIMARY KEY AUTO_INCREMEN

17、T,NAME VARCHAR(18)外键约束CREATE TABLE tb_dept(dept_id INT PRIMARY KEY,NAME VARCHAR(18),description VARCHAR(255);CREATE TABLE tb_employee(employee_id INT PRIMARY KEY,NAME VARCHAR(18),gender VARCHAR(10),dept_id INT REFERENCES tb_dept(dept_id),address VARCHAR(255);外键约束CREATE TABLE tb_dept(dept_id INT PRIM

18、ARY KEY,NAME VARCHAR(18),description VARCHAR(255);CREATE TABLE tb_employee(employee_id INT PRIMARY KEY,NAME VARCHAR(18),gender VARCHAR(10),dept_id INT,address VARCHAR(255),CONSTRAINT FOREIGN KEY tb_employee_fk (dept_id) REFERENCES tb_dept(dept_id);Check 约束既可作为列级约束，也可作为表级约束定义在字段上的每一记录都要满足的条件在check中定义

19、检查的条件表达式，数据需要符合设置的条件条件表达式不允许使用参照其他记录的值CREATE TABLE tb_student(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(18),age INT CHECK(age 18 AND age 60)建立表之间的关系示例列级约束与表级约束列级约束直接跟在列后定义，不再需要指定列名，与列定义之间用空格分开表级约束通常放在所有的列定义之后定义，要显式指定对哪些列建立列级约束。与列定义之间采用英语逗号,隔开。如果是对多列建联合约束，只能使用表级约束语法。增加约束可增加或删除约束，但不能直接修改可使约束启用和禁用非空约束必须使用MODIFY子句增加只要是可以使用列级约束语法来定义的约束，都可以通过modify来增加该约束。ALTER TABLE tableADD CONSTRAINT constraint type (column);增加约束加FOREIGN KEY 约束到EMP表ALTER TABLE tb_employeeADD CONSTRAINT tb_employee_