当前位置:   article > 正文

四、数据表的基本操作_数据准备阶段用于两表之间的操作有哪些

数据准备阶段用于两表之间的操作有哪些

4.1 创建数据表

在创建完数据库之后,接下来的工作就是创建数据表。所谓创建数据 表,指的是在已经创建好的数据库中建立新表。创建数据表的过程是规定数 据列的属性的过程,同时也是实施数据完整性(包括实体完整性、引用完整 性和域完整性等)约束的过程。 数据表属于数据库,在创建数据表之前,应该使用语句“USE <数据库 名>”指定操作是在哪个数据库中进行,如果没有选择数据库,就会抛出“No database selected”的错误。

创建数据表的语句为CREATE TABLE,语法规则如下:

  1. CREATE TABLE TAB_NAME(
  2. 字段1 类型 约束 默认值,
  3. 字段2 类型 约束 默认值,
  4. 字段n 类型 约束 默认值
  5. )

使用CREATE TABLE创建表时,必须指定以下信息: (1) 要创建的表的名称,不区分大小写,不能使用SQL语言中的关键 字,如DROP、ALTER、INSERT 等。 (2) 数据表中每一列(字段)的名称和数据类型,如果创建多列,就要用逗号隔开。 案例:创建员工表:employee

字段名数据类型备注
emp_idint员工编号
emp_namevarchar(50)员工名字
emp_sexchar(2)员工性别
emp_salaryfloat员工工资
emp_phonevarchar(50)员工电话
emp_birthdate员工生日
dept_idint所在部门

创建表的sql语句如下:

  1. CREATE TABLE employee(
  2. emp_id INT,
  3. emp_name VARCHAR(50),
  4. emp_sex CHAR(2),
  5. emp_salary FLOAT,
  6. emp_phone VARCHAR(50),
  7. emp_birth DATE,
  8. dept_id INT
  9. );

 

此时可以通过以下命令查看该库下的表

SHOW TABLES;

 

通过上图可以看到表中还有一些默认值,主键,非空等。这些都是属于表的约束。

4.2 表约束

1.数据完整性

数据的完整性是指数据的可靠性和准确性 1.实体完整性:实体的完整性强制表的标识符列或主键的完整性(通过索引,唯一约束,主键约束或标识列属性). 2.域完整性:限制类型(数据类型),格式(通过检查约束和规则),可能值范围(通过外键约束,检查约束,默认值定义,非空约束和规则). 3.引用完整性:在删除和输入记录时,引用完整性保持表之间已定义的关系.引用完整性确保键值在所有表中一致.这样的一致辞性要求不能引用不存在的值.如果一个键值更改了,那么在整个数据库中,对该键值的引用要进行一致的更改. 4.自定义完整性:用户自己定义的业务规则. 四种完整性约束:

实体完整性:唯一约束、主键约束、标识列

域完完整性:限制数据类型、外键约束、默认值、非空约束

引用完整性:外键 自定义完整性:过程,触发器等

2.约束 约束是在表上强制执行的一些数据校验规则,被插入、修改或删除的数据必须符合在相关字段上设置的这些约束条件。 五类完整性约束: NOT NULL 非空 UNIQUE 唯一 PRIMARY KEY 主键 FOREIGN KEY 外键 CHECKED 检查(mysql不支持的)

4.2.1 主键约束

主键,又称主码,是表中一列或多列的组合。主键约束(Primary Key Constraint)要求主键列的数据唯一,并且不允许为空。主键能够唯一地标 识表中的一条记录,可以结合外键来定义不同数据表之间的关系,并且可以 加快数据库查询的速度。主键和记录之间的关系如同身份证和人之间的关 系,它们之间是一一对应的。主键分为两种类型:单字段主键和多字段联合主键。

1.单子段主键

主键由一个字段组成,SQL语句格式分为以下两种情况。 1)定义列的同时指定主键,语法如下

字段名 字段类型 primary key

如:创建部门表 dept,表结构如下

字段名数据类型备注
deptidint部门编号 主键
dept_namevarchar(50)部门名称
dept_descvarchar(200)部门描述
dept_numint部门人数

sql语句如下:

  1. CREATE TABLE dept(
  2. deptid INT PRIMARY KEY,
  3. dept_name VARCHAR(50),
  4. dept_desc VARCHAR(200),
  5. dept_num INT
  6. );

通过上图,我们还看到有个自增,自增是什么? 在数据库应用中,经常希望在每次插入新记录时,系统自动生成字段的 主键值。可以通过为表主键添加 AUTO_INCREMENT关键字来实现。默认 的,在MySQL中AUTO_INCREMENT的初始值是1,每新增一条 记录,字段 值自动加1。一个表只能有一个字段使用AUTO_INCREMENT约束,且该字 段必须为主键的一部分。AUTO_INCREMENT约束的字段可以是任何整数 类型(TINYINT、SMALLIN、INT、BIGINT 等)。

  1. CREATE TABLE dept(
  2. deptid INT PRIMARY KEY AUTO_INCREMENT,
  3. dept_name VARCHAR(50),
  4. dept_desc VARCHAR(200),
  5. dept_num INT
  6. );

 

2)定义表之后指定主键

ALTER TABLE 表名 ADD CONSTRAINT主键名 PRIMARY KEY(字段);
ALTER TABLE employee ADD CONSTRAINT pk_empid PRIMARY KEY(emp_id);

2.联合主键

主键由多个字段联合组成,语法规则如下:

primary key(字段1,字段2,...字段n)

表关系详细讲解。 主键选取原则:

  1. 主键应当是对用户没有意义的。如果用户看到了一个表示多对多关系的连接表中的数据,并抱怨它没有什么用处,那就证明它的主键设计地很好。

  2. 主键应该是单列的,以便提高连接和筛选操作的效率。

  3. 永远也不要更新主键。实际上,因为主键除了惟一地标识一行之外,再没有其他的用途了,所以也就没有理由去对它更新。如果主键需要更新,则说明主键应对用户无意义的原则被违反了。

  4. 主键不应包含动态变化的数据,如时间戳、创建时间列、修改时间列等。

  5. 主键应当有计算机自动生成 我们在建立数据库的时候,需要为每张表指定一个主键,所谓主键就是能够唯一标识表中某一行的属性或属性组,一个表只能有一个主键,但可以有多个候选索引。因为主键可以唯一标识某一行记录,所以可以确保执行数据更新、删除的时候不会出现张冠李戴的错误。当然,其它字段可以辅助我们在执行这些操作时消除共享冲突,不过就不在这里讨论了。主键除了上述作用外,常常与外键构成参照完整性约束,防止出现数据不一致。所以数据库在设计时,主键起到了很重要的作用。

4.2.2 非空约束

非空约束(Not Null Constraint)指字段的值不能为空。对于使用了非空 约束的字段,如果用户在添加数据时没有指定值,数据库系统会报错。

 字段名 数据类型 not null

4.2.3 唯一约束

唯一性约束(Unique Constraint)要求该列唯一,允许为空,但只能出 现一个空值。唯一约束可以确保一列或者几列不出现重复值。 唯一性约束的语法规则如下

字段名 数据类型 UNIQUE

如果定义完表后也可以给某个字段添加唯一约束,语法如下:

ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE (约束字段);
ALTER TABLE employee ADD CONSTRAINT uk_phone UNIQUE (emp_phone);

 

删除唯一索引的语法:

ALTER TABLE 表名 DROP INDEX 约束名;
ALTER TABLE employee DROP INDEX uk_phone;

4.2.4 默认值约束

默认约束(Default Constraint)指定某列的默认值。如男性同学较多, 性别就可以默认为‘男’。如果插入一条新的记录时没有为这个字段赋值,那么系统会自动为这个字段赋值为‘男’。 默认约束的语法规则如下:

字段名 字段类型 default 默认值
  1. CREATE TABLE employee(
  2. emp_id INT,
  3. emp_name VARCHAR(50) NOT NULL ,
  4. emp_sex CHAR(2) DEFAULT '男',
  5. emp_salary FLOAT,
  6. emp_phone VARCHAR(50) UNIQUE ,
  7. emp_birth DATE,
  8. dept_id INT
  9. );

4.2.5 外键约束

外键用来在两个表的数据之间建立连接,可以是一列或者多列。一个表 可以有一个或多个外键。外键对应的是参照完整性,一个表的外键可以为空 值,若不为空值,则每一个外键值必须等于另一个表中主键的某个值。 外键:首先它是表中的一个字段,虽可以不是本表的主键,但要对应另 外一个表的主键。外键的主要作用是保证数据引用的完整性,定义外键后, 不允许删除在另一个表中具有关联关系的行。外键的作用是保持数据的一致 性、完整性。例如,部门表tb_dept的主键是id,在员工表tb_emp5中有一个键deptId与这个id关联。 主表(父表):对于两个具有关联关系的表而言,相关联字段中主键所 在的那个表即是主表。

从表(子表):对于两个具有关联关系的表而言,相关联字段中外键所 在的那个表即是从表。 语法:

ALTER TABLE 子表 ADD CONSTRAINT 约束名 FOREIGN KEY (外键) REFERENCES 主表(主键);
  1. ALTER TABLE employee ADD CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id)
  2. REFERENCES dept(deptid);

以上案例就是一个一对多的表关系。一个部门下有多个员工,一个员工从属一个部门。 常用的表关系【重点】有三种:一对一,一对多【自关联】,多对多

一对一:人与身份证

  1. /*
  2. 一对一
  3. 人 身份证
  4. 1 1
  5. */
  6. CREATE TABLE person(
  7. pid INT PRIMARY KEY AUTO_INCREMENT,
  8. pname VARCHAR(50),
  9. pphone VARCHAR(50)
  10. );
  11. CREATE TABLE idcard(
  12. cid INT PRIMARY KEY,
  13. cnum VARCHAR(50),
  14. cstart DATE,
  15. cend DATE,
  16. cpublish VARCHAR(200)
  17. );
  18. ALTER TABLE idcard ADD CONSTRAINT fk_card_person FOREIGN KEY(cid) REFERENCES person(pid);

一对多:部门与员工

  1. /*
  2. 1.一对多
  3. 部门 员工
  4. 1 N
  5. 1 1
  6. */
  7. CREATE TABLE department(
  8. departid INT PRIMARY KEY AUTO_INCREMENT,
  9. departname VARCHAR(50),
  10. departnum INT DEFAULT 0,
  11. departdesc VARCHAR(200)
  12. );
  13. CREATE TABLE employee(
  14. empid INT PRIMARY KEY AUTO_INCREMENT,
  15. empname VARCHAR(50),
  16. empsex CHAR(2) DEFAULT '男',
  17. empbirth DATE,
  18. empphone VARCHAR(50),
  19. deptid INT -- FK
  20. );
  21. /*
  22. alter table tabname add constratin 约束名 foreign key(字段) references 主表
  23. (主键);
  24. */
  25. ALTER TABLE employee ADD CONSTRAINT fk_emp_dept FOREIGN KEY(deptid) REFERENCES department(departid);

多对多:用户与角色【中间表多列主键】

  1. /* 多对多的关系
  2. 用户 角色
  3. 1 N
  4. N 1
  5. N N
  6. */
  7. CREATE TABLE users(
  8. uid INT PRIMARY KEY AUTO_INCREMENT,
  9. uname VARCHAR(50) NOT NULL UNIQUE,
  10. upwd VARCHAR(50) NOT NULL,
  11. ustatus INT
  12. );
  13. CREATE TABLE roles(
  14. rid INT PRIMARY KEY AUTO_INCREMENT,
  15. rname VARCHAR(50) NOT NULL
  16. );
  17. CREATE TABLE userroles(
  18. uid INT ,
  19. rid INT,
  20. PRIMARY KEY(uid,rid)
  21. );
  22. ALTER TABLE userroles ADD CONSTRAINT fk_ur_user FOREIGN KEY(uid) REFERENCES users(uid);
  23. ALTER TABLE userroles ADD CONSTRAINT fk_ur_role FOREIGN KEY(rid) REFERENCES roles(rid);

自关联:省市

  1. CREATE TABLE cities(
  2. cid INT PRIMARY KEY AUTO_INCREMENT,
  3. cname VARCHAR(50),
  4. pid INT ,
  5. CONSTRAINT fk_pro_ci FOREIGN KEY(pid) REFERENCES cities(cid)
  6. )

4.2.6 查看表结构

DESCRIBE/DESC语句可以查看表的字段信息,其中包括字段名、字段 数据类型、是否为主键、是否有默认值等。语法规则如下:

DESCRIBE 表名;

或者简写为

DESC 表名;

 

其中,各个字段的含义分别解释如下 NULL:表示该列是否可以存储NULL值。 Key:表示该列是否已编制索引。PRI表示该列是表主键的一部分; UNI表示该列是UNIQUE索引的一部分;MUL表示在列中某个给定 值允许出现多次。 Default:表示该列是否有默认值,有的话指定值是多少。 Extra:表示可以获取的与给定列有关的附加信息,例如 AUTO_INCREMENT等。 查看表详细结构语句: SHOW CREATE TABLE语句可以用来显示创建表时的CREATE TABLE 语句,语法格式如下:

SHOW CREATE TABLE employee;

 

  1. CREATE TABLE `employee` (
  2. `emp_id` int(11) NOT NULL AUTO_INCREMENT,
  3. `emp_name` varchar(50) NOT NULL,
  4. `emp_sex` char(2) DEFAULT '男',
  5. `emp_salary` float DEFAULT NULL,
  6. `emp_phone` varchar(50) DEFAULT NULL,
  7. `emp_birth` date DEFAULT NULL,
  8. `dept_id` int(11) DEFAULT NULL,
  9. PRIMARY KEY (`emp_id`),
  10. UNIQUE KEY `emp_phone` (`emp_phone`),
  11. KEY `fk_emp_dept` (`dept_id`),
  12. CONSTRAINT `fk_emp_dept` FOREIGN KEY (`dept_id`) REFERENCES `dept`
  13. (`deptid`)
  14. ) ENGINE=InnoDB DEFAULT CHARSET=utf8

4.2.7 修改表结构

修改表指的是修改数据库中已经存在的数据表的结构。MySQL使用 ALTER TABLE语句修改表。常用的修改表的操作有修改表名、修改字段数 据类型或字段名、增加和删除字段、修改字段的排列位置、更改表的存储引擎、删除表的外键约束等。本节将对和修改表有关的操作进行讲解。

  1. 修改表名

    ALTER TABLE <旧表名> RENAME [TO] <新表名>;
    ALTER TABLE employee RENAME TO emp;
  2. 修改字段的数据类型

    ALTER TABLE <表名> MODIFY <字段名> <数据类型>
    ALTER TABLE emp MODIFY emp_name VARCHAR(30);
  3. 修改字段名

    ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>;
    ALTER TABLE emp CHANGE dept_id deptid INT;
  4. 添加字段

    ALTER TABLE <表名> ADD <新字段名> <新字段类型> <约束条件> [FIRST|AFTER 已存在字段名];
    1. ALTER TABLE emp ADD empaddr VARCHAR(20);
    2. ALTER TABLE emp ADD empaddr VARCHAR(20) AFTER empsex;
    3. ALTER TABLE emp ADD empnum VARCHAR(20) FIRST;

    比如条件null

    ALTER TABLE tabname ADD column1 VARCHAR(12) not null;
  5. 删除字段

    ALTER TABLE <表名> DROP <字段名> ;
    ALTER TABLE emp DROP empno;
  6. 修改字段的排序位置

    ALTER TABLE <表名> MODIFY <字段1> <数据类型> FIRST|AFTER <字段2>;
    ALTER TABLE emp MODIFY deptid INT AFTER empphone;
  7. 删除表的外键约束

    ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>
    ALTER TABLE emp DROP FOREIGN KEY fk_emp_dept;
  8. 更改表的存储引擎

    ALTER TABLE <表名> ENGINE=<更改后的存储引擎名>;
    ALTER TABLE tb_deptment3 ENGINE=MyISAM;
  9. 删除数据表

DROP TABLE [IF EXISTS]表1, 表2,…表n;

建表的时候,如果选择数据类型

  1. 整数和浮点数 如果不需要小数部分,就使用整数来保存数据;如果需要表示小数部分,就使用浮点数类型。对于浮点数据列,存入的数值会对该列定义的小数位进行四舍五入。例如,假设列的值的范围为1~99999,若使用整数,则 MEDIUMINT UNSIGNED是 好的类型;若需要存储小数,则使用FLOAT 类型 浮点类型包括FLOAT和DOUBLE类型。DOUBLE类型精度比FLOAT类型高,因此要求存储精度较高时应选择DOUBLE类型

  2. 浮点数和定点数浮点数

    FLOAT、DOUBLE相对于定点数DECIMAL的优势是:在长度一定的情况下,浮点数能表示更大的数据范围。由于浮点数容易产生误差,因 此对精确度要求比较高时,建议使用DECIMAL来存储。DECIMAL在MySQL中是以字符串存储的,用于定义货币等对精确度要求较高的数据。 在数据迁移中,float(M,D)是非标准SQL定义,数据库迁移可能会出现问题, 好不要这样使用。另外,两个浮点数进行减法和比较运算时也容易出 问题,因此在进行计算的时候,一定要小心。进行数值比较时, 好使用DECIMAL类型

  3. 日期与时间类型 MySQL对于不同种类的日期和时间有很多数据类型,比如YEAR和TIME。如果只需要记录年份,则使用YEAR类型即可;如果只记录时间,则使用TIME类型。 如果同时需要记录日期和时间,则可以使用TIMESTAMP或者DATETIME类型。由于 TIMESTAMP列的取值范围小于DATETIME的取值范围,因此存储范围较大的日期 好使用DATETIME。 TIMESTAMP也有一个DATETIME不具备的属性。默认的情况下,当插入一条记录但并没有指定TIMESTAMP这个列值时,MySQL会把TIMESTAMP列设为当前的时间。因此当需要插入记录的同时插入当前时间时,使用TIMESTAMP是方便的。另外,TIMESTAMP在空间上比 DATETIME更有效。

  4. CHARVARCHAR之间的特点与选择 CHAR和VARCHAR的区别如下 CHAR是固定长度字符,VARCHAR是可变长度字符。 CHAR会自动补空格,VARCHAR不自动补。 CHAR是固定长度,所以它的处理速度比VARCHAR的速度要快,但是它的缺点是浪费存储空间,所以对存储不大但在速度上有要求的可以使用CHAR类型,反之可以使用 VARCHAR类型来实现。

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/很楠不爱3/article/detail/671606
推荐阅读
相关标签
  

闽ICP备14008679号