用MySQL创建数据库和数据库表.doc

用MySQL创建数据库和数据库表正式版

用MySQL创建数据库和数据表：
步骤：
1、 使用show语句找出在服务器上当前存在什么数据库：
mysql>show databases;

2、 创建一个数据库test：
mysql>create database test;

3、 选择你所创建的数据库：
mysql>use test;

4创建一个数据表：
首先查看刚才创建的数据库中存在什么表：
mysql>show tables;

（说明刚才创建的数据库中还没有数据库表）
接着我们创建一个关于students的数据表：包括学生的学号(id)，姓名(name)，性别(sex)，年龄(age)。
mysql>create table students(id int unsigned not null auto_increment primary key,name char(8) not null,sex char(4) not null,age tinyint unsigned not null,);

解释：以 "id int unsigned not null auto_increment primary key" 行进行介绍:
"id" 为列的名称;
"int" 指定该列的类型为 int(取值范围为 -8388608到8388607), 在后面我们又用 "unsigned" 加以修饰, 表示该类型为无符号型, 此时该列的取值范围为 0到16777215;
"not null" 说明该列的值不能为空, 必须要填, 如果不指定该属性, 默认可为空;
"auto_increment" 需在整数列中使用, 其作用是在插入数据时若该列为 NULL, MySQL将自动产生一个比现存值更大的唯一标识符值。在每张表中仅能有一个这样的值且所在列必须为索引列。
"primary key" 表示该列是表的主键, 本列的值必须唯一, MySQL将自动索引该列。
下面的 char(8) 表示存储的字符长度为8, tinyint的取值范围为 -127到128, default 属性指定当该列值为空时的默认值。

创建一个表后，用show tables显示数据库中有哪些表：
mysql>show tables；

5、 显示表结构：
mysql>describe students;

6、 在表中添加记录：
首先用select命令来查看表中的数据：
mysql>select*from students;

（说明刚才创建的数据库表中还没有任何记录）
接着加入一条新纪录：
mysql>insert into students value(‘01’,’Tom’，’F’，’18’)；

再用select命令来查看表中的数据的变化：
mysql>select*from students;

7、 用文本方式将数据装入一个数据库表：
创建一个文本文件“student.sql”，每行包括一个记录，用TAB键把值分开，并且以在createtable语句中列出的次序，例如：
02TonyF18
03AmyM18
04LisaM18
将文本文件“student.sql”装载到students表中：
mysql>load data local infile”e:\\student.sql”into table students;

再使用select命令来查看表中的数据的变化：
mysql>select*from students;

Mysql数据库主要系统表说明
1. 获取所有表结构(TABLES)
SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA='数据库名';
TABLES表：提供了关于数据库中的表的信息（包括视图）。详细表述了某个表属于哪个schema，表类型，表引擎，创建时间等信息。各字段说明如下：
字段
含义
Table_catalog
数据表登记目录
Table_schema
数据表所属的数据库名
Table_name
表名称
Table_type
表类型[system view|base table]
Engine
使用的数据库引擎[MyISAM|CSV|InnoDB]
Version
版本，默认值10
Row_format
行格式[Compact|Dynamic|Fixed]
Table_rows
表里所存多少行数据
Avg_row_length
平均行长度
Data_length
数据长度
Max_data_length
最大数据长度
Index_length
索引长度
Data_free
自由数据？
Auto_increment
做自增主键的自动增量当前值
Create_time
表的创建时间
Update_time
表的更新时间
Check_time
表的检查时间
Table_collation
表的字符校验编码集
Checksum
校验和
Create_options
创建选项
Table_comment
表的注释、备注
2. 获取表字段(COLUMNS)
SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='数据库名' AND TABLE_NAME='表名'
COLUMNS表：提供了表中的列信息。详细表述了某张表的所有列以及每个列的信息。各字段的说明信息如下：
字段
含义
Table_catalog
数据表登记目录
Table_schema
数据表所属的数据库名
Table_name
所属的表名称
Column_name
列名称
Ordinal_position
字段在表中第几列
Column_default
列的默认数据
Is_nullable
字段是否可以为空
Data_type
数据类型
Character_maximum_length
字符最大长度
Character_octet_length
字节长度？
Numeric_precision
数据精度
Numeric_scale
数据规模
Character_set_name
字符集名称
Collation_name
字符集校验名称
Column_type
列类型
Column_key
关键列[NULL|MUL|PRI]
Extra
额外描述[NULL|on update CURRENT_TIMESTAMP|auto_increment]
Privileges
字段操作权限[select|select,insert,update,references]
Column_comment
字段注释、描述
3. 获取表键值
SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA='数据库名' AND TABLE_NAME='表名'
KEY_COLUMN_USAGE表：存取表的健值。各字段的说明信息如下：
字段
含义
Constraint_catalog
约束登记目录
Constraint_schema
约束所属的数据库名
Constraint_name
约束的名称
Table_catalog
数据表等级目录
Table_schema
键值所属表所属的数据库名（一般与Constraint_schema值相同）
Table_name
键值所属的表名
Column_name
键值所属的列名
Ordinal_position
键值所属的字段在表中第几列
Position_in_unique_constraint
键值所属的字段在唯一约束的位置（若为外键值为1）
Referenced_talble_schema
外键依赖的数据库名（一般与Constraint_schema值相同）
Referenced_talble_name
外键依赖的表名
Referenced_column_name
外键依赖的列名

4. 获取表Check约束
SELECT* FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA='数据库名' AND TABLE_NAME='表名'
TABLE_CONSTRAINTS表：存储主键约束、外键约束、唯一约束、check约束。各字段的说明信息如下：
字段
含义
Constraint_catalog
约束登记目录
Constraint_schema
约束所属的数据库名
Constraint_name
约束的名称
Table_schema
约束依赖表所属的数据库名（一般与Constraint_schema值相同）
Table_name
约束所属的表名
Constraint_type
约束类型[primary key|foreign key|unique|check]
5. 获取表索引
SELECT * FROM information_schema.STATISTICS WHERETABLE_SCHEMA='数据库名' AND TABLE_NAME='表名'
STATISTICS表：提供了关于表索引的信息。各字段的说明信息如下：
字段
含义
Table_catalog
数据表登记目录
Table_schema
索引所属表的数据库名
Table_name
索引所属的表名
Non_unique
字段不唯一的标识
Index_schema
索引所属的数据库名（一般与table_schema值相同）
Index_name
索引名称
Seq_in_index

Column_name
索引列的列名
Collation
校对，列值全显示为A
Cardinality
基数（一般与该表的数据行数相同）
Sub_part

Packed
是否包装过，默认为NULL
Nullable
是否为空[‘’|YES|NO]
Index_type
索引的类型，列值全显示为BTREE（平衡树索引）
Comment
索引注释、备注
6. mysql有关show的用法
SHOW DATABASES列出 MySQL Server上的数据库。
SHOW TABLES [FROM db_name]列出数据库中的表。
SHOW TABLE STATUS [FROM db_name]列出数据库的表信息，比较详细。
SHOW COLUMNS FROM tbl_name [FROM db_name]列出表的列信息，同 SHOW FIELDS FROM tbl_name [FROM db_name]，DESCRIBE tbl_name [col_name]。
SHOW FULL COLUMNS FROM tbl_name [FROM db_name]列出表的列信息，比较详细，同 SHOW FULL FIELDS FROM tbl_name [FROM db_name]。
SHOW INDEX FROM tbl_name [FROM db_name]列出表的索引信息。
SHOW STATUS列出 Server 的状态信息。
SHOW VARIABLES列出 MySQL 系参数值
SHOW PROCESSLIST查看当前mysql查询进程
SHOW GRANTS FOR user列出用户的授权命令

1．写出创建如下三张表的SQL语句，要求在定义表的同时定义数据的完整性约束：
（1）“图书表”结构如下：
书号：统一字符编码定长类型，长度为6，主键；
书名：统一字符编码可变长类型，长度为30，非空；
第一 普通编码定长字符类型，长度为10，非空；
出版日期：小日期时间型；
价格：定点小数，小数部分1位，整数部分3位。
（2）“书店表”结构如下：
书店编号：统一字符编码定长类型，长度为6，主键；
店名：统一字符编码可变长类型，长度为30，非空；
：普通编码定长字符类型，8位长，每一位的取值均是0～9的数字；
地址：普通编码可变长字符类型，40位长。
邮政编码：普通编码定长字符类型，6位长。
（3）“图书销售表”结构如下：
书号：统一字符编码定长类型，长度为6，非空；
书店编号：统一字符编码定长类型，长度为6，非空；
销售日期：小日期时间型，非空；
销售数量：小整型，大于等于1。
主键为（书号，书店编号，销售日期）；
其中“书号”为引用“图书表”的“书号”的外键；
“书店编号”为引用“书店表”的“书店编号”的外键。
答：
CREATE TABLE 图书表 (
书号 nchar(6) primary key,
书名 nvarchar(30) not null,
第一作者 char(10) not null,
出版日期 smalldatetime,
价格 numeric(4,1))
CREATE TABLE 书店表 (
书店编号 nchar(6) primary key,
店名 nvarchar(30) not null,
char(8) check ( like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
地址 varchar(40),
邮政编码 char(6))
CREATE TABLE 图书销售表 (
书号 nchar(6) not null,
书店编号 nchar(6) not null,
销售日期 smalldatetime not null,
销售数量 smallint check(销售数量>=1),
primary key(书号,书店编号,销售日期),
foreign key(书号) references 图书表(书号),
foreign key(书店编号) references 书店表(书店编号))
1.1为图书表添加“印刷数量”列，类型为整数，同时添加约束，要求此列的取值要大于等于1000。
答：ALTER TABLE 图书表
ADD 印刷数量 int check (印刷数量>=1000)
1.2删除“书店表”中的“邮政编码”列。
答：ALTER TABLE 书店表
DROP COLUMN 邮政编码
1.3将“图书销售表”中的“销售数量”列的数据类型改为整型。
答：ALTER TABLE 图书销售表
ALTER COLUMN 销售数量 int

2．设某商业集团数据库中有三个实体集：
商店：商店编号、商店名、地址
商品：商品编号、商品名、规格、单价
职工：职工编号、姓名、性别、业绩
每个商店可销售多种商品，每种商品也可放在多个商店销售，每个商店销售一种商品时有月销售量；每个商店有许多职工，每个职工只能在一个商店工作，商店聘用职工有聘期和月薪。
（1）试画出E-R图，要求在图上注明属性及联系的类型；
（2）将E-R图转换成关系模型，并注明主码；
（3）根据实际情况，使用SQL创建表，包括各种约束；
（4）用SQL语句查找大于平均业绩的职工姓名；
（5）用SQL语句创建一个业绩大于100的所有男职工信息的视图。

（1）（5分）

（2）这个E-R图可转换为4个关系模式：（8分）
商店（商店编号，商店名，地址） （2分）
职工（职工编号，姓名，性别，业绩，商店编号，聘期，月薪） (2分)
商品（商品编号，商品名，规格，单价） （2分）
销售（商店编号，商品编号，月销售量） （2分）
（3）create shop(Sid char(3) primary key, Sname char(10), Sadd char(50)); （2分）
create employee(Eid char(3) primary key, Ename char(5), Esex char(1), Each real, Sid char(3),Ere date, Esa int, foreign key (Sid) references (shop)); （2分）
create commodity(Cid char(3) primary key, Cname char(10), Csp char(10), Cpr real); （2分）
create vendition(Sid char(3), Cid char(3), Vse int, primary key (Sid,Cid),foreign key (Sid) references (shop), foreign key (Cid) references (commodity)); （2分）
（4） （4）select Ename from employee x where Each>=(select avg(Each) from employee y where y.Each=x.Each);
（5）create view Eman(Eid, Ename, Esex, Each, Sid, Ere, Esa)
As select Eid, Ename, Esex, Each, Sid, Ere, Es from employee where Each>100 and Esex=’男’;（2分）

3. （共10分）设学校数据库中有两个实体集：
学生表：学号、姓名、班级
课程表：课程号、课程名称、教师
某学校有若干学生，每个学生可以选修多门课程，学校有若干课程供学生选修，每门课程可以供多个学生选修，要建立该学校学生选修课程的数据库，请设计：
（1）试画出E-R图，要求在图上注明属性及联系的类型；
（2）将E-R图转换成关系模型，并注明主码；
（2）这个E-R图可转换为4个关系模式：（12分）
商店（商店编号，商店名，地址） （3分）
职工（职工编号，姓名，性别，业绩，商店编号，聘期，月薪） (3分)
商品（商品编号，商品名，规格，单价） （3分）
销售（商店编号，商品编号，月销售量） （3分）
（1）（4分）

（2）这个E-R图可转换为3个关系模式：（6分）
课程表（学号，姓名，班级）
选修（学号，课程号）
课程表（课程号，课程名称，教师）

4.一个图书借阅管理数据库要求提供下述服务：
1）可随时查询书库中现有书籍的品种，数量与存放位置。所有各类书籍均可由书号唯一标识。
2）可随时查询书籍借还情况。包括借书人单位、姓名、借书证号、借书日期和还书日期。约定任何人可借多种书，任何一种书可为多个人所借，借书证号具有唯一性。
3）当需要时，可通过数据库中保存的出版社的电报编号、 、 及地址等信息向有关书籍的出版社增购有关书籍。约定一个出版社可以出版多种书籍，同一本书仅为一个出版社出版，出版社名具有唯一性。
根据以上情况，试构造满足需求的E-R图，并转换成等价的关系模型结构。
答案：
图略。
转换为等价的关系模型结构如下：
借书人（借书证号，姓名，单位）
图书（书号，书名，数量，位置，出版社名）
出版社（出版社名，电报， ， ，地址）
借阅（借书证号，书号，借书日期，还书日期）

实验一 创建数据库和表
1、目的和要求
（1）了解SQL Server数据库的逻辑结构和物理结构。
（2）了解表的结构特点
（3）了解SQL Server的基本数据类型。
2、实验内容
（1）实验题目
①创建一个新的数据库。创建用于企业管理的员工数据库，数据库名称为YGGL。
② 在创建好的数据库YGGL中创建数据表。考虑到数据库YGGL中包含员工的信息、部分信息以及员工的薪水信息，所以数据库YGGL应该包含三个表：Employees（员工自然信息）表、Departments（部门信息）表和Salary（员工薪水情况）表。
3、实验步骤
（1）在对象资源管理器中创建数据库YGGL。

（2）删除数据库YGGL。

①界面操作删除数据库
删除数据库YGGL时，右键单击数据库，弹出菜单点击“删除”，弹出“删除对象”窗口，确认删除。

② T-SQL语句删除数据库

在代码空白处右击鼠标，弹出菜单选择“执行(X)”或者键盘“F5”运行代码，在下方会显示运行成功。

此时在左侧“对象资管理器”中右键点击“数据库”选择刷新数据库，会发现数据库“PX”已经删除。
注意：当执行过一遍代码时，也就是执行了T-SQL语句删除数据库后再次执行代码会出现这样的警告。原因是由于执行过一遍T-SQL语句，进行操作的数据库已经被删除，不存在。

（3）使用T-SQL语句创建数据库YGGL。

新建查询后，在窗口中输入上面代码，右键代码空白处执行。然后在“对象资源管理器”中查看。如果“数据库”列表中并未列出YGGL数据库，则单击右键“数据库”，选择“刷新”选项，“数据库”列表中就会出现创建的YGGL数据库。
（4）在对象资源管理器中创建表。
① 创建表。Employees（员工自然信息）表、Departments（部门信息）表和Salary（员工薪水情况）表。
在对象资源管理器中选择创建的数据库“YGGL”，展开数据库YGGL，选择“表”，右键单击“表”在弹出的菜单中选择“新建表”。将表保存并命名为“Employees”。下面是相同方法创建的Employees（员工自然信息）表、Departments（部门信息）表和Salary（员工薪水情况）表。

Employees（员工自然信息）表

Departments（部门信息）表

Salary（员工薪水情况）表

② 删除表。右击表弹出的快捷菜单中“删除”项，打开“删除对象”窗口。
在YGGL数据库中选择“表”，展开找到Employees（员工自然信息）表右键单击在弹出菜单内选择删除，在“对象删除”窗口中选择确定删除。
（5）使用T-SQL语句创建表。
①利用T-SQL语句创建Employees（员工自然信息）

注：Employees红色下划线，由于已经运行了代码，数据库中已经存在Employees表，所以将鼠标放在上面才会出现下面这样的错误。

② 利用T-SQL语句创建Departments（部门信息）表。

在代码空白处单击右键选择“执行”，会在下方“消息”对话框中看到执行成功。

在左侧对象资源管理器中YGGL数据库的子菜单表中右键选择刷新，就会显示创建的表。
③ 利用T-SQL语句创建Salary（员工薪水情况）表。

同样执行后，在下方“消息”中会看到“命令已成功完成”，在左边对象资源管理器中YGGL数据库的表中单击右键选择刷新，会出现新建的Salary（员工薪水情况）表。
4、思考与练习
（1）在YGGL数据库存在的情况下，使用CREATE DATABASE语句新建数据库YGGL，查看错误信息。

当左侧资源管理器的数据库中已经存在数据库YGGL时，再次用CREATE DATABASE语句新建数据库YGGL时，在下方“消息”中会出现以下错误，是由于已经存在数据库YGGL。

（2）创建数据库YGGL1，使用界面方式或者ALTER DATABASE语句尝试修改YGGL1数据库逻辑文件的初始大小。

在左侧对象资源管理器中刷新数据库，在数据库YGGL1中右键单击查看属性，逻辑文件大小已经由初始值修改为20MB。

（3）在YGGL1中创建表Salary1（参照表Salary的结构），表Salary1比Salary多一列计算列，列名为“ActIncome”，由InCome-OutCome得到。

在下方“消息”中显示“命令执行完成”。
在数据库YGGL1的Salary1表单击右键“设计”中会发现比起Salary表已经多了ActInCome一项。

（4）在YGGL1数据库中创建Employees1（结构与Employees相同），分别使用命令行方式和界面方式将表Employees1中的Address列删除，并将Sex列的默认值修改为0。
利用命令行形式在数据库YGGL1中创建表Employees1.

利用界面操作的形式将数据库YGGL1中表Address列删除，并且将Sex的默认值修改为0。
在数据库YGGL1中打开表Employees1，在列中找到Sex这一项，单击右键修改，在列属性中将Sex的默认值或绑定由原来的1修改为0。

同样在表Employees1中找到Address找到，在Address属性上单击右键，选择删除 ，在弹出的删除对象窗口中选择确定，进行删除。

（5）什么是临时表？怎样创建临时表？
可以创建本地和全局临时表。本地临时表仅在当前会话中可见；全局临时表在所有会话中都可见。
本地临时表的名称前面有一个编号符 (#table_name)，而全局临时表的名称前面有两个编号符 (##table_name)。
方法一：
create table #临时表名(字段1 约束条件,
字段2 约束条件,
.....)
create table ##临时表名(字段1 约束条件,
字段2 约束条件,
.....)
方法二：
select * into #临时表名 from 你的表;
select * into ##临时表名 from 你的表;
注：以上的#代表局部临时表，##代表全局临时表
下面是我创建的临时表Salary1