数据库上机实验一：操作命令

程序员小虫

已于 2023-04-13 21:22:41 修改

阅读量994

点赞数 18

文章标签：数据库 mysql sql

于 2023-04-13 11:31:33 首次发布

本文链接：https://blog.csdn.net/m0_59798711/article/details/130125923

版权

1、操作数据库：

C(Create)：创建
- 创建数据库

create database 数据库名称;

创建数据库，判断不存在，再创建

create database if not exists 数据库名称; //常用

创建数据库，并指定字符集

create database [if not exists] 数据库名称 character set 字符集名;

创建db4数据库，判断是否存在，并制定字符集为gbk。

create database if not exists db4 character set gbk;

R(Retrieve)：查询
- 查询所有数据库的名称

show databases;

查询某个数据库的字符集（查询某个数据库的创建语句）

show create database 数据库名称;

A(Alter)：修改
- 修改数据库的字符集

alter database 数据库名称 character set 字符集名称;

D(Drop)：删除
- 删除数据库

drop database 数据库名称;

判断数据库存在，存在再删除

drop database if exists 数据库名称; //常用

U(Use)：使用数据库
- 查询当前正在使用的数据库名称

select database(); 或者\s

使用数据库

use 数据库名称;

2、操作数据表：

C(Create)：创建
1. 创建表

语法：create table if exist表名( 列名1 数据类型1 [约束],

列名2 数据类型2 [约束] , ...... );

复制表

语法：1）create table 表名 like 被复制的表名; //仅仅复制表的结构(里面无数据)

create table 表名 select * from 被复制的表名; //复制原表结构+所有数据

3）create table 表名 select 字段名from 被复制的表名 [where 条件]; //复制原表结构+部分数据（单个或多个字段名）

4）create table 表名 select 字段名 from 被复制的表名where 0; （或）

create table 表名 select 字段名 from 被复制的表名where 1 = 2;

//仅仅复制某些字段 where 0，where 1=2均为恒不成立, where 1=1为恒成立

注意：

复制表可以跨库，只需库名.表名即可。

3 创建临时表

语法：CREATE TEMPORARY TABLE 表名(字段名字段类型,...);

特征：

1. 临时表的创建语法需要用到关键字 TEMPORARY

2. 临时表创建完成之后，只有当前连接可见，其他连接是看不到的，具有连接隔离性；

3. 临时表在当前连接结束之后，会被自动删除。

R(Retrieve)：查询
1. 查询某个数据库中所有的表名称

show tables;

查询表结构

DESCRIBE 表名;或 DESC 表名;

SHOW [FULL] COLUMNS FROM 数据表名

A(Alter)：修改
1. 修改表名

alter table 旧表名 rename to 新表名;

或者

RENAME TABLE 旧表名1 TO 新表名1[, 旧表名2 TO 新表名2] ...;

修改字段名，字段属性

ALTER TABLE 表名 CHANGE 旧字段名新字段名新数据类型;

修改字段的数据类型

ALTER TABLE 表名 MODIFY 字段名数据类型;

添加字段

ALTER TABLE 表名 ADD 新字段名数据类型 [约束条件][FIRST|AFTER 已存在字段名]

或者

ALTER TABLE 数据表名 ADD [COLUMN] (新字段名1 字段类型1, 新字段名2 字段类型2, ...);

删除列

ALTER TABLE 表名 DROP 字段名;

修改字段的排列位置

ALTER TABLE表名 MODIFY 字段名1 数据类型 FIRST|AFTER 字段名2

修改表的存储引擎

ALTER TABLE 数据表名 ENGINE=新的存储引擎类型

修改表的字符集

ALTER TABLE 数据表名 [DEFAULT] CHARSET=新的字符集

D(Drop)：删除
1. 删除表

drop table 表名;

判断表存在，存在再删除

drop table if exists 表名; //常用

3、数据操纵：

（1）I(Insert)：插入

1 为所有字段添加数据

INSERT INTO 表名(字段名1，字段名2，……) VALUES(值1，值2，……);

或者

INSERT INTO 表名 VALUES(值1，值2，……);

2 为部分字段添加数据

INSERT INTO 表名(字段1,字段2，…) VALUES(值1,值2,…)

或者

INSERT INTO 表名 SET 字段名1=值1[，字段名2=值2，……]

3 一次添加多行数据

INSERT INTO 表名[(字段名1，字段名2，……) ] VALUES(值1，值2，……)，(值1，值2，……)， … … (值1，值2，……);

或者

INSERT [INTO] 数据表名1 [(字段列表)] SELECT [(字段列表)] FROM 数据表名2;

4 主键冲突解决

主键冲突更新：当插入数据的过程中若发生主键冲突，则插入数据操作利用更新的方式实现。

INSERT [INTO] 数据表名 [(字段列表)] {VALUES | VALUE} (字段列表) ON DUPLICATE KEY UPDATE 字段名1 = 新值1[,字段名2 = 新值2] …;

主键冲突替换：当插入数据的过程中若发生主键冲突，则删除此条记录，并重新插入。

REPLACE [INTO] 数据表名[(字段列表)] VALUES(值列表)

或者

REPLACE [INTO] 目标数据表名[(字段列表1)] SELECT (字段列表2) FROM 源表 [WHERE 条件表达式]

或者

REPLACE [INTO] 数据表名 SET 字段1=值1,字段2=值2,字段3=值3……

（2）U(Update)：更新

UPDATE 表名 SET 字段名1 = 值1[，字段名2 = 值2，……] [WHERE 条件表达式]

（3）D(Delete)：删除

DELETE FROM 表名 [WHERE 条件表达式]

或者

TRUNCATE [TABLE] 表名

4、约束：

（1）分类：

1 主键约束：primary key

2 非空约束：not null

3 唯一约束：unique

4 外键约束：foreign key

5 默认约束：default

6 检查约束：check

（2）主键约束：primary key

1在创建表时，添加主键约束

create table stu(

id int primary key,-- 给id添加主键约束

name varchar(20)

);

2 删除主键

ALTER TABLE stu DROP PRIMARY KEY;

3 创建完表后，添加主键

1) 列级约束

ALTER TABLE stu MODIFY id INT PRIMARY KEY;

2）表级约束

ALTER TABLE stu ADD [CONSTRAINT 外键名] PRIMARY KEY (id);

（2）非空约束：not null，值不能为null

1 创建表时添加约束

CREATE TABLE stu(

id INT,

NAME VARCHAR(20) NOT NULL -- name为非空

);

2 创建表完后，添加name的非空约束

ALTER TABLE stu MODIFY name VARCHAR(20) NOT NULL;

3 删除name的非空约束

ALTER TABLE stu MODIFY name VARCHAR(20);

（3）唯一约束：unique，值不能重复，可以为空。

1创建表时，添加唯一约束

CREATE TABLE stu(

id INT,

phone_number VARCHAR(20) UNIQUE -- 添加了唯一约束

);

2 删除唯一约束

ALTER TABLE stu DROP INDEX phone_number;

3 在创建表后，添加唯一约束

ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;

（4）默认约束：DEFAULT，当在表中插入一条新记录时，如果没有给这个字段赋值，那么，数据库系统会自动为这个字段插入默认值。

1创建表时，添加默认约束

CREATE TABLE stu(

id INT default 1, -- 添加了默认约束，默认值为1

phone_number VARCHAR(20)

);

2 删除默认约束

ALTER TABLE stu MODIFY id int;

3 在创建表后，添加默认约束

ALTER TABLE stu MODIFY id int DEFAULT 10;

（5）自动增长：如果某一列是数值类型的，使用 auto_increment 可以来完成值得自动增长，默认从1开始。

1 在创建表时，添加主键约束，并且设置主键自增长

create table stu(

id int primary key auto_increment,-- 给id添加主键约束

name varchar(20)

);

2 删除自动增长

ALTER TABLE stu MODIFY id INT;

3 添加自动增长

ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;

4 设置自动增长值

ALTER TABLE stu AUTO_INCREMENT = 新值;

5、索引：

（1）分类：

1 普通索引：不应用任何限制条件的索引，可以在任何数据类型中创建。

2 唯一性索引：使用UNIQUE参数可以设置唯一索引。创建该索引时，索引的值必须唯一。

3 单列索引：只对应一个字段的索引。

4 多列索引：在表的多个字段上创建一个索引。

5 全文索引：使用FULLTEXT参数可以设置索引为全文索引。全文索引只能创建在CHAR、VARCHAR或者TEXT类型的字段上。

6 空间索引：使用SPATIAL参数可以设置索引为空间索引。空间索引只能建立在空间数据类型上。

（2）创建索引

1创建表的时候创建索引：

CREATE TABLE 表名（字段名数据类型[完整性约束条件],

字段名数据类型[完整性约束条件],

．．．．．．

字段名数据类型

　　　　　［UNIQUE|FULLTEXT|SPATIAL］ INDEX|KEY

[索引名] (字段名 [(长度)] [ASC|DESC]));

2 使用CREATE INDEX 语句在已经存在的表上创建索引

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名 ON 表名 (字段名 [(长度)] [ASC|DESC]);

3 使用ALTER TABLE语句在已经存在表上创建索引

ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名 (字段名 [(长度)] [ASC|DESC])；

（3）删除索引

DROP INDEX 索引名 ON 表名;

ALTER TABLE 表名 DROP INDEX 字段名

（4）查看索引

SHOW INDEX FROM table_name\G

上机实验一：

实施步骤与技术要点

1、创建数据库Teach，并指定其字符集为UTF-8。

2、创建Teach数据库中的S(学生信息表)、C(课程表)、T(教师信息表) 、SC(学生选课表)、TC(教师授课表)，数据表创建过程中注意主键、非空等约束。

S表：

列名	含义	数据类型	是否可空	主外键
SNo	学生学号	char(10)	N	PK
SN	学生姓名	varchar(20)	N
Sex	性别	char(2)	Y
Age	年龄	int	Y
Dept	所在系别	varchar(20)	Y

C表：

列名	含义	数据类型	是否可空	主外键
CNo	课程代码	char(10)	N	PK
CN	课程名称	varchar(10)	N
CT	课时	int	Y

T表：

列名	含义	数据类型	是否可空	主外键
TNo	教师号	char(10)	N	PK
TN	教师姓名	varchar(20)	N
Sex	性别	char(2)	Y
Age	年龄	int	Y
Prof	职称	char(10)	Y
Sal	工资	int	Y
Comm	岗位津贴	int	Y
Dept	系别	varchar(20)	Y

SC表：

列名	含义	数据类型	是否可空	主外键
SNo	学号	char(10)	N	PK，FK
CNo	课程代码	char(10)	N	PK，FK
Score	成绩	decimal(5，2)	Y

TC表：

列名	含义	数据类型	是否可空	主外键
TNo	教师号	char(10)	N	Pk，FK
CNo	课程代码	char(10)	N	PK，FK

3、使用命令完成数据表的管理和维护。

使用命令在学生表S中添加 “Birthdary”字段，其数据类型为Year，并放在“Age”字段的后面。
将学生表S的姓名字段Sn设置为唯一索引。
将学生表S的所在系别字段Dept设置默认约束为“人工智能学院”。

4、使用命令完成数据表的数据插入、更新和删除。

S (学生关系)

Sno 学号	Sn 姓名	Sex 性别	Age 年龄	Birthdary 出生年份	Dept 系别
S1	赵亦	女	17	2005	计算机
S2	钱尔	男	18	2004	信息
S3	孙珊	女	20	2002	信息
S4	李思	男	21	2001	自动化
S5	周武	男	19	2003	计算机
S6	吴丽	女	20	2002	自动化

C (课程关系)

Cno 课程号	Cn 课程名	Ct 课时
C1	程序设计	60
C2	微机原理	80
C3	数字逻辑	60
C4	数据结构	80
C5	数据库原理	60
C6	编译原理	54
C7	操作系统	60

T (教师关系)

Tno 教师号	Tn 姓名	Sex 性别	Age 年龄	Prof 职称	Sal 工资	Comm 岗津	Dept 系别
T1	李力	男	47	教授	1500	3000	计算机
T2	王平	女	28	讲师	800	1200	信息
T3	刘伟	男	30	讲师	900	1200	计算机
T4	张雪	女	51	教授	1600	3000	自动化
T5	张兰	女	39	副教授	1300	2000	信息

SC(选课关系)

Sno 学号	Cno 课程号	Score 成绩
S1	C1	90
S1	C2	85
S2	C4	70
S2	C5	57
S2	C6	80
S2	C7
S3	C1	75
S3	C2	70
S3	C4	85
S4	C1	93
S4	C2	85
S4	C3	83
S5	C2	89

TC(授课关系)

Tno 教师号	Cno 课程号
T1	C1
T1	C4
T2	C5
T3	C1
T3	C5
T4	C2
T4	C3
T5	C5
T5	C7

向学生表S中添加学生信息：“S7，O’Nei，女，19，2003”。
向学生表S中添加学生信息：“S7，张三，男，17，2005”(若发生主键冲突，则利用替换方式解决)。
将课程表C中的C5的课程名修改为“数据库系统原理”。
删除学生表S中的S7的学生信息。
复制学生表S的结构与内容，新表命名为STUDENT。
无痕删除STUDENT表。

·实施过程与代码

#1、创建数据库Teach，并指定其字符集为UTF-8。
CREATE DATABASE Teach CHARACTER SET utf8;

#S(学生信息表)
CREATE TABLE S (
  SNo char(10) NOT NULL PRIMARY KEY COMMENT '学生学号',
  SN varchar(20) NOT NULL COMMENT '学生姓名',
  Sex char(2) COMMENT '性别',
  Age int COMMENT '年龄',
  Dept varchar(20) COMMENT '所在系别'
);

#C(课程表)
CREATE TABLE C (
  CNo char(10) NOT NULL PRIMARY KEY COMMENT '课程代码',
  CN varchar(10) NOT NULL COMMENT '课程名称',
  CT int COMMENT '课时'
);

#T(教师信息表)
CREATE TABLE T (
  TNo char(10) NOT NULL PRIMARY KEY COMMENT '教师号',
  TN varchar(20) NOT NULL COMMENT '教师姓名',
  Sex char(2) COMMENT '性别',
  Age int COMMENT '年龄',
  Prof char(10) COMMENT '职称',
  Sal int COMMENT '工资',
  Comm int COMMENT '岗位津贴',
  Dept varchar(20) COMMENT '系别'
);

#SC(学生选课表)
CREATE TABLE SC (
  SNo char(10) NOT NULL COMMENT '学号',
  CNo char(10) NOT NULL COMMENT '课程代码',
  Score decimal(5,2) COMMENT '成绩',
  PRIMARY KEY (SNo, CNo),
  FOREIGN KEY (SNo) REFERENCES S(SNo),
  FOREIGN KEY (CNo) REFERENCES C(CNo)
);

#SC(学生选课表)
CREATE TABLE SC (
  SNo char(10) NOT NULL COMMENT '学号',
  CNo char(10) NOT NULL COMMENT '课程代码',
  Score decimal(5,2) COMMENT '成绩',
  PRIMARY KEY (SNo, CNo),
  FOREIGN KEY (SNo) REFERENCES S(SNo),
  FOREIGN KEY (CNo) REFERENCES C(CNo)
);

#(1)使用命令在学生表S中添加 “Birthdary”字段，其数据类型为Year，并放在“Age”字段的后面。
ALTER TABLE S ADD Birthdary YEAR AFTER Age;
#(2)将学生表S的姓名字段Sn设置为唯一索引。
ALTER TABLE S ADD UNIQUE INDEX idx_sn (SN);
#(3)将学生表S的所在系别字段Dept设置默认约束为“人工智能学院”。
ALTER TABLE S ALTER COLUMN Dept SET DEFAULT '人工智能学院';

DESC S;
INSERT INTO S (Sno, Sn, Sex, Age, Birthdary, Dept)
VALUES
('S1', '赵亦', '女', 17, 2005, '计算机'),
('S2', '钱尔', '男', 18, 2004, '信息'),
('S3', '孙珊', '女', 20, 2002, '信息'),
('S4', '李思', '男', 21, 2001, '自动化'),
('S5', '周武', '男', 19, 2003, '计算机'),
('S6', '吴丽', '女', 20, 2002, '自动化');

INSERT INTO C (Cno, Cn, Ct)
VALUES
('C1', '程序设计', 60),
('C2', '微机原理', 80),
('C3', '数字逻辑', 60),
('C4', '数据结构', 80),
('C5', '数据库原理', 60),
('C6', '编译原理', 54),
('C7', '操作系统', 60);

INSERT INTO T (Tno, Tn, Sex, Age, Prof, Sal, Comm, Dept)
VALUES
('T1', '李力', '男', 47, '教授', 1500, 3000, '计算机'),
('T2', '王平', '女', 28, '讲师', 800, 1200, '信息'),
('T3', '刘伟', '男', 30, '讲师', 900, 1200, '计算机'),
('T4', '张雪', '女', 51, '教授', 1600, 3000, '自动化'),
('T5', '张兰', '女', 39, '副教授', 1300, 2000, '信息');

INSERT INTO SC (Sno, Cno, Score)
VALUES
('S1', 'C1', 90),
('S1', 'C2', 85),
('S2', 'C4', 70),
('S2', 'C5', 57),
('S2', 'C6', 80),
('S2', 'C7', NULL),
('S3', 'C1', 75),
('S3', 'C2', 70),
('S3', 'C4', 85),
('S4', 'C1', 93),
('S4', 'C2', 85),
('S4', 'C3', 83),
('S5', 'C2', 89);

INSERT INTO TC (Tno, Cno)
VALUES
('T1', 'C1'),
('T1', 'C4'),
('T2', 'C5'),
('T3', 'C1'),
('T3', 'C5'),
('T4', 'C2'),
('T4', 'C3'),
('T5', 'C5'),
('T5', 'C7');

#(1)向学生表S中添加学生信息：“S7，O’Nei，女，19，2003”。
INSERT INTO s (Sno, Sn, Sex, Age, Birthdary) VALUES ('S7','O’Nei','女',19,2003);
#(2)向学生表S中添加学生信息：“S7，张三，男，17，2005”(若发生主键冲突，则利用替换方式解决)。
INSERT INTO S (Sno, Sn, Sex, Age, Birthdary)
VALUES ('S7', '张三', '男', 17, 2005)
ON DUPLICATE KEY UPDATE Sn = VALUES(Sn), Sex = VALUES(Sex), Age = VALUES(Age), Birthdary = VALUES(Birthdary);
#(3)将课程表C中的C5的课程名修改为“数据库系统原理”。
UPDATE C SET CN = '数据库系统原理' WHERE CNo = 'C5';
#(4)删除学生表S中的S7的学生信息。
DELETE FROM S WHERE SNo = 'S7';
#(5)复制学生表S的结构与内容，新表命名为STUDENT。
CREATE TABLE STUDENT LIKE s;
#(6)无痕删除STUDENT表。
DROP TABLE STUDENT;