数据库基础03——数据表操作

数据库基础03——数据表操作

1.建表之前需要思考的基本问题

• 表里会包含什么类型的数据？
• 表的名称是什么？
• 哪个（或哪些） 列组成主键？
• 列（字段） 的名称是什么？
• 每一列的数据类型是什么？
• 每一列的长度是多少？
• 表里哪些列可以是NULL？
• 遵循每个DBMS的命名规则

1.1数据库的三范式

Codd博士定义了6个范式来规范化数据库，范式由小到大来约束，范式越高冗余越小，但表的个数也越多。实验证明，三范式是性价比最高的。

1.1.1 第一范式：确保每列原子性

第一范式确保每个字段不可再分

1.1.2 第二范式：非键字段必须依赖于键字段

一个表只能描述一件事

1.1.3 第三范式：消除传递依赖

在所有的非键字段中，不能有传递依赖

下列设计是否满足第三范式？

不满足，因为语文和数学确定了，总分就确定了。

思考：上面的设计不满足第三范式，但是高考分数表就是这样设计的，为什么？

答：高考分数峰值访问量非常大，这时候就是性能更重要。当性能和规范化冲突的时候，我们首选性能。这就是“反三范式”。

2.显示所有表

语法：

show tables

3.创建表

3.1 常用建表sql

语法：

create table [if not exists] 表名(
    字段名 数据类型 [null|not null] [auto_increment] [primary key] [comment],
    字段名 数据类型 [default]…
)engine=存储引擎

单词

null | not null   	空|非空
default	          	默认值
auto_increment    	自动增长
primary key       	主键
comment           	备注
engine           	引擎   innodb  myisam  memory  引擎是决定数据存储的方式

创建简单的表

mysql> create database itcast;
Query OK, 1 row affected (0.00 sec)

mysql> use itcast;
Database changed
mysql> show tables;
Empty set (0.05 sec)

# 创建表
mysql> create table stu(
    -> id int,
    -> name varchar(30)
    -> );
Query OK, 0 rows affected (0.13 sec)
# 查看创建的表
mysql> show tables;
+------------------+
| Tables_in_itcast |
+------------------+
| stu              |
+------------------+

创建复杂的表，有主键、注释、约束、默认值等

mysql> set names gbk;   # 设置字符编码
Query OK, 0 rows affected (0.05 sec)

mysql> create table if not exists teacher(
    -> id int auto_increment primary key comment '主键',
    -> name varchar(20) not null comment '姓名',
    -> phone varchar(20) comment '电话号码',
    -> `add` varchar(100) default '地址不详' comment '地址'
    -> )engine=innodb;
Query OK, 0 rows affected (0.09 sec)

多学一招：create table 数据库名.表名，用于给指定的数据库创建表

mysql> create table data.stu(  #给data数据库中创建stu表
    -> id int,
    -> name varchar(10));
Query OK, 0 rows affected (0.00 sec)

**命名规范：表和列的名称， 应该让名称反应出所保存的数据 **

3.2创建自增长的列

在MySQL中创建序列，可以在列上设置AUTO_INCREMENT属性，这通常是主键列。

使用AUTO_INCREMENT属性时，将应用以下规则：

• 每个表只有一个AUTO_INCREMENT列，其数据类型通常为整数。
• 必须对AUTO_INCREMENT列进行索引，它可以是PRIMARY KEY或UNIQUE)索引。
• AUTO_INCREMENT列必须具有NOT NULL约束。当您为列设置AUTO_INCREMENT属性时，MySQL会自动将NOT NULL约束隐式添加到列中。

CREATE TABLE USER (
	uid INT auto_increment PRIMARY KEY,
	uname VARCHAR (20)
);

Microsoft SQL Server中可以使用 IDENTITY类型：

CREATE TABLE TEST_INCREMENT(
	ID		INT  IDENTITY(1,1) NOT NULL,
    TEST_NAME	VARCHAR(20);

Oracle 没有提供直接的方法来创建自动增加的列。 但却可以使用 SEQUENCE 对象和一个触发器来实现类似的效果。

3.3从现有表新建另一个表

语法如下：

create table  新表名 as       
select [*|column1,column2...]	--可以是部分字段也可以是所有字段
from  表名
[where]							--可以添加条件

MySQL和Oracle都支持使用CREATE TABLE AS SELECT方法， 在一个表的基础上创建另一个表。 但是
Microsoft SQL Server却不一样， 它使用SELECT…INTO方法来实现相同的效果。

SQLserver如下：

select [*|column1,column2]
into  新表名
form 旧表名
[where]

3.4 显示创建表的语句

语法：

show create table 表名

显示创建teacher表的语句

mysql> show create table teacher;
+---------+--------------------------------------------------------------------------
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
-------+
| Table   | Create Table


       |
+---------+--------------------------------------------------------------------------
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
-------+
| teacher | CREATE TABLE `teacher` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(20) NOT NULL COMMENT '姓名',
  `phone` varchar(20) DEFAULT NULL COMMENT '电话号码',
  `add` varchar(100) DEFAULT '地址不详' COMMENT '地址',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8                         |

将两个字段竖着排列 show create table 表名\G

mysql> show create table teacher\G;
*************************** 1. row ***************************
       Table: teacher
Create Table: CREATE TABLE `teacher` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(20) NOT NULL COMMENT '姓名',
  `phone` varchar(20) DEFAULT NULL COMMENT '电话号码',
  `add` varchar(100) DEFAULT '地址不详' COMMENT '地址',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

4.查看表结构

语法：

desc[ribe] 表名

查看teacher表的结构

mysql> describe teacher;
+-------+--------------+------+-----+----------+----------------+
| Field | Type         | Null | Key | Default  | Extra          |
+-------+--------------+------+-----+----------+----------------+
| id    | int(11)      | NO   | PRI | NULL     | auto_increment |
| name  | varchar(20)  | NO   |     | NULL     |                |
| phone | varchar(20)  | YES  |     | NULL     |                |
| add   | varchar(100) | YES  |     | 地址不详        |                |
+-------+--------------+------+-----+----------+----------------+
4 rows in set (0.08 sec)

mysql> desc teacher;
+-------+--------------+------+-----+----------+----------------+
| Field | Type         | Null | Key | Default  | Extra          |
+-------+--------------+------+-----+----------+----------------+
| id    | int(11)      | NO   | PRI | NULL     | auto_increment |
| name  | varchar(20)  | NO   |     | NULL     |                |
| phone | varchar(20)  | YES  |     | NULL     |                |
| add   | varchar(100) | YES  |     | 地址不详        |                |
+-------+--------------+------+-----+----------+----------------+
4 rows in set (0.01 sec)

5.删除表

语法：

drop table [if exists] 表1，表2,… 

删除表

mysql> drop table stu;
Query OK, 0 rows affected (0.08 sec)

如果删除一个不存在的表就会报错，删除的时候可以判断一下，存在就删除。

mysql> drop table stu;
ERROR 1051 (42S02): Unknown table 'stu'

mysql> drop table if exists stu;
Query OK, 0 rows affected, 1 warning (0.00 sec)

可以一次删除多个表

mysql> drop table a1,a2;
Query OK, 0 rows affected (0.00 sec)

6.修改表

表创建以后，我们可以使用alter table命令添加列、 删除列、 修改列定义、 添加和去除约束， 在某些实现中还可以修改表STORAGE值。

首先说明下mysql的 alter 语句；

alter table 表名   add 列名  类型（长度） [约束]；--修改表添加列
alter table 表名	 modify 列名 类型（长度） [约束]；--修改表修改列的类型长度及约束
alter table 表名	change 旧列名	新列名	类型(长度) 约束；  --修改表修改列名
alter table 表名	drop  列名;						--修改表删除列
rename table 表名  to  新表名;						--修改表名
alter  table 表名	character set 字符集;				--修改表的字符集

6.1只修改数据类型

要改变表中列的数据类型，请使用下面的语法：

**SQL Server / MS Access：**修改表修改列的类型长度及约束

ALTER TABLE 表名
ALTER COLUMN 列名 类型

My SQL修改表修改列的类型长度及约束

ALTER TABLE 表名 MODIFY  列名 类型等属性

6.2 添加字段（列）

如果表已经包含数据， 这时添加的列就不能定义为NOT NULL ， 强行向表添加一列的方法如下：
1． 添加一列， 把它定义为NULL（这一行不一定要包含数据） ；
2． 给这个新列在每条记录里都插入数据；
3． 把列的定义修改为NOT NULL。

常规添加语句：

alter table 表名 add [column] 字段名 数据类型 [位置]

alter table teacher add age int;

在第一个位置上添加字段： first

alter table teacher add email varchar(30) first;

在指定的字段后添加字段：after

alter table teacher add sex varchar(2) after name;

6.3 删除字段

如需删除表中的列，请使用下面的语法（请注意，某些数据库系统不允许这种在数据库表中删除列的方式）：

alter table 表名 drop [column] 字段名
--至少mysql  sqlserver  oracle是允许的

6.4 修改字段名和类型

SQL server 中修改字段名：

execute sp_rename '表名.旧字段名','新字段名'

MySQL修改字段名：

alter table 表 change [column] 原字段名 新字段名 数据类型 …  --修改表修改列名

Oracle修改字段名：

alter table tableName rename column oldCName to newCName; -- 修改字段名

6.5 修改表名

alter table 表名 rename to 新表名
---------------------------------
mysql> alter table teacher rename to stu;
Query OK, 0 rows affected (0.00 sec)

--或者
rename table 表名  to  新表名;						--修改表名

6.6 修改引擎

alter table 表名 engine=引擎名

mysql> alter table teacher engine=myisam;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

7、完整性约束

• 主键约束
• 唯一性约束
• 外键约束
• NOT NULL 约束
• 检查约束

(1)主键约束：

主键是表里一个或多个用于实现记录唯一性的字段。（可以一个，也可以多个） ，即一个表只能有一个主键，但是主键可以是一个或多个字段组成

主键的作用：

• 保证数据完整性

• 加快查询速度

要求被修饰的字段 唯一、非空

--创建表时指明主键
CREATE TABLE EMPLOYEE_TBL(
	EMP_ID		CHAR(9)   NOT NULL PRIMARY KEY,-- 这里的主键是个隐含约束
	EMP_NAME	VARCHAR(40) NOT NULL
);
--创建时还可以明确指定主键
CREATE TABLE EMPLOYEE_TBL(
	EMP_ID		CHAR(9)   NOT NULL,
	EMP_NAME	VARCHAR(40) NOT NULL，
    PRIMARY KEY(EMP_ID)
);
/*如果多个字段也可以用以下两种方式之一来定义*/--以下在oracle数据库是可行的
CREATE TABLE PRODUCT_TST(
	PROD_ID		VARCHAR2(10) NOT NULL,
    VEND_ID		VARCHAR2(30) NOT NULL,
    PRODUCT		VARCHAR2(10) NOT NULL,
    PRIMARY KEY(PROD_ID,VEND_ID)
)
--修改表时指明主键
--或者
ALTER TABLE PRODUCTS_TST
ADD CONSTRAINT	 PRIMARY KEY (prod_id,VEND_ID)

(2)唯一性约束

唯一性约束要求表里某个字段的值在每条记录里都是唯一的 。主键必须唯一，唯一不一定是主键。

CREATE TABLE EMPLOYEE_TBL(
	EMP_ID		CHAR(9)   NOT NULL PRIMARY KEY,
	EMP_NAME	VARCHAR(40) NOT NULL  UNIQUE-- 举个例子，直接加 UNIQUE字句就行
);
--或者
CREATE TABLE EMPLOYEE_TBL(
	EMP_ID		CHAR(9)   NOT NULL ,
	EMP_NAME	VARCHAR(40) NOT NULL,
    PRIMARY KEY(EMP_ID),
    UNIQUE(EMP_NAME)
);
--或者 在alter 的时候添加 比如
ALTER TABLE EMPLOYEE_TBL add unique(EMP_NAME);

--一次性添加多个唯一键
alter table 表名 add unique(字段1),add unique(字段2);
-- 添加组合的唯一键
alter  table 表名 add unique(字段1，字段2);
-- 添加唯一键的时候可以取名
alter  table 表名 add unique 唯一键的名字(字段1)

(3)外键约束

外键是子表里的一个字段， 引用父表里的主键。 外键约束是确保表与表之间引用完整性的主要机制。
一个被定义为外键的字段用于引用另一个表里的主键。 举个例子：

CRATE TABLE EMPLOYEE_PAY_TST(
	EMP_ID		CHAR(9)		NOT NULL,
    POSITION	VARCHAR(15)	NOT NULL,
    DATE_HIRE	DATE		NULL,
    PAY_RATE	NUMBER(4,2)	NOT NUL,
    CONSTRAINT	EMP_ID_FK	FOREIGN KEY(EMP_ID) REFERENCES EMPLOYEE_TBL(EMP_ID)
    --这里 是把此表的 EMP_ID作为EMPLOYEE_TBL的外键，于EMPLOYEE_TBL 的EMP_ID一一对应
);

注意：子表里的EMP_ID字段引用父表里的EMP_ID字段。 为了在子表里插入一个EMP_ID的值， 它首先要存在于父表的EMP_ID里。 类似地， 父表里删除一个EMP_ID的值， 子表里相应的EMP_ID值必须全部被删除。 这就是引用完整性的概念

还可以利用ALTER TABLE 命令向表里添加外键

alter table employee_pay_tbl add constraint id_fk foreign key(emp_id)
references employee_tbl(emp_id)
--语法
alter table 从表名  add  constraint 外键名(自己起) foreign key(外键)
references 主表名(主键)

在mysql中注意：要创建外键必须是innodb引擎，myisam不支持外键约束

(4)NOT NULL 约束：

例子中已经展示，not null就行

(5)检查约束

检查（CHK） 约束用于检查输入到特定字段的数据的有效性， 可以提供后端的数据库编辑。 检查约束为数据提供了另一层保护。

CRATE TABLE EMPLOYEE_PAY_TST(
	EMP_ID		CHAR(9)		NOT NULL,
    POSITION	VARCHAR(15)	NOT NULL,
    DATE_HIRE	DATE		NULL,
    PAY_RATE	NUMBER(4,2)	NOT NUL,
    EMP_ZIP		NUMBER(5)	NOT NULL,
    CONSTRAINT CHK_EMP_ZIP	CHECK(EMP_ZIP='12345')
    --这个检查约束就是保证输入到这个表的数据的emp_zip都是‘12345’，只是个例子，知道这么用就行
    --在检查约束里可以使用几乎任何条件，就像在SQL查询里一样。 check(条件)
);

(6)去除约束

利用alter table命令的drop constraint选项可以去除已经定义的约束。

根据约束名去除约束：

--根据主键名删除主键
ALTER TABLE EMPLOYEES DROP CONSTRAINT EMPLOYEES_PK;--这里就是去掉了empoyees表中的employees_pk的主键约束。
--直接删除主键
alter table  表名 drop  primary key;

如果要删除外键约束：

--mysql 删除
ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名;--所以添加约束的时候最好起起个约束名，不然不好删

在mysql中查看约束

show  create table 表名\G