数据库概论_基础练习

练习题1

创建一个学生表，要求学号字符型，长度为5，不能为空；姓名字符型长度20，不允许重复，性别字符
型长度1，邮政编码长度为6，年龄整数，系别字符型长度15

-- 一般针对一个应用创建一个数据库 
create database if not exists stu_manager default character set utf8; 

-- 切换当前数据库 
use stu_manager; 

-- 创建表 
create table if not exists tb_student( 
	id char(5) not null, 
	name varchar(20) unique, 
	sex char(1) default '男', 
	postcode char(6), 
	age int, 
	dept varchar(15) 
)engine=innodb default charset utf8;

向tb_student表中增加一个入学时间的列

• 注意：如果修改表之前，表中已经有数据，则新增列的值为null，除非设置了default默认约束

alter table tb_student add come date; 
/* 如果给come列上添加not null非空约束，而且表中已经有数据，则会出错 */

将年龄字段修改为小整型

• 注意：修改表之前如果表中已经存有数据，修改不能破坏已有数据

alter table tb_student modify age tinyint(3); -- 注意(3)的含义

删除姓名字段

alter table tb_student drop column name;

练习题2

创建一个学生选课表，由学号、课程号、成绩组成，请合理定义主键

• 主键=非空+唯一
• 一个表中只能有一个主键，因为主键会影响数据的具体存放位置【索引问题】
• 一个主键可以是由多个列构成。复合主键是由多个列构成的一个主键

create table if not exists tb_choice( 
	sno char(5) comment '学号', 
	cno int comment '课程号', 
	score int, primary key(sno,cno) -- 定义复合主键，cno和sno列上并没有添加not null约束，但是cno和 sno都不允许为空; 但是唯一是指两个属性组合的唯一，并不是其中列的唯一 
)engine=innodb default charset utf8;

表操作DML/DQL

• DML数据操纵语言，用于对数据库表中的记录进行更新操作，插入insert、修改update、删除delete
• DQL数据查询语言，用于对数据库表中的记录进行查询操作，select

样例表

create table tb_users( 
	id bigint primary key auto_increment, 
	username varchar(20) not null, 
	password varchar(24) not null, 
	birth date, 
	sex boolean default 1, 
	score numberic(8,2) 
)engine=innodb default charset utf8;

增加数据

语法1： inser into 表名称 values(值1,值2,....) 用于针对表中的每个列指定对应的值，例如
insert into tb_users values(null,'yanjun','123456','1989-2-3',1,12345.67); ，注意
values后面括号中的值和列一一对应，注意数据类型
语法2： insert into 表名称(列名1,列名2,...) values(值1,值2,...) 人为指定列插入数据，注意两
个括号一一对应，一般用于不是每个列都需要插入数据时使用。例如 insert into tb_users(username,password,birth) values('yanjun','123456','1989-2-3')。如果没有出现
的列而且列上没有default约束，则自动插入值null；如果有default约束则插入默认值

注意：

• 值与字段必须对应，包括个数和数据类型

  • 值必须在合理的字段类型范围内
  • varchar、char和日期类型必须使用单双引号包裹
  • 如果没有任何限制，控制允许插入

• 没有指定列名称时，必须给出完整数据，而且对应顺序

• 优化写法：指定列名称。无须遵守定义表时列的顺序

• 批量插入查询出的数据

  • insert into table1(列名1,列名2,…) select 列1,列2,… from table2；

• 将一个表的查询出的内容插入创建的新表中。不仅克隆表结构，同时克隆表中数据。但是约束会有丢失问题

  • create table newTable as select * from oldTable;

修改数据

语法1： update 表名称 set 列名1=值1,列名2=值2,... 。修改表中所有行的指定列对应的值，等号后面
可以是一个表达式进行计算。例如score新增10%写法 update tb_users set score=score*1.1

• 允许同时修改多个列： update tb_users set score=500, sex=0

语法2： update 表名称 set 列名1=值1,列名2=值2,... where 条件 用于修改表中满足条件的行指定列
的值；例如1989年之前的用户积分新增10%， update tb_users set score=score*1.1 where year(birth)<1989 ，year是系统函数，用于获取指定列的年份值

删除数据

语法1： delete from 表名称; 删除表中所有数据。例如 delete from tb_users;
语法2： delete from 表名称 where 条件; 删除表中满足条件的所有行。例如删除1990年的所有女
生， delete from tb_users where sex=0 and year(birth)=1990

整表删除操作

方法1： delete from tb_student; 可以删除表中的所有数据，属于DML语句，所以支持事务，支持撤
销回滚操作，但是大量的删除数据时需要记录日志，所以执行效率很低。优势在于可以反悔，缺点在于记录日志需要时空代价
方法2： truncate table tb_student; 表的截断操作，可以删除表中的所有数据，属于DDL语句，没
有事务，不会记录操作日志，所以大量删除数据时执行效率高。优势是执行效率，缺点在于没有反悔的
机会。底层原理就是创建一个相同的空表

逻辑删除和物理删除

所谓的物理删除就是从表中直接删除数据；逻辑删除类似于回收站，只是给某行数据上添加一个删除标志，表示该行数据已经删除了

逻辑删除的具体实现：
1、修改表tb_users新增一个额外列，用于表示该行数据是否已经删除

alter table tb_users add deleted boolean default 0; -- 0表示没有删除

2、逻辑删除，并没有真实删除数据，只是修改一个标识列的值

update tb_users set deleted=1 where id=5; -- 删除5号用户信息 delete from tb_users where id=5;物理删除

3、真正查询时为了避免查到已经删除的数据，所以需要额外的查询条件

select * from tb_users where deleted=0; --只查询deleted值为0的行，不要已经标识删除的函 数deleted=1