Day02 常用数据类型了解 增删改查sql命令,数据备份(导入导出sql文件) 以及练习题及答案_721

Day02 常用数据类型了解 增删改查sql命令,数据备份(导入导出sql文件) 以及练习题及答案

了解常用的数据类型

主要分三类：

1. 数值类型
2. 字符串类型
3. 日期时间类型

数据类型名称	大小	描述
TINYINT	1Byte	小整数值，带符号的范围是-128到127。无符 号的范围是0到255
SMALLINT	2bytes	小的整数，带符号的范围是-32768到32767.无 符号的范围是0到65535
MEDIUMINT	3bytes	中等大小整数-8388608到8388607，0到 16777215
INT/INTEGER	4bytes	普通大小的整数，-2147483648到 2147483647，0到4294967295
BIGINT	8bytes	大整数，-9223372036854775808到 9223372036854775807，0到 18446744073709551615
FLOAT[(M,D)]	4bytes	小(单精度)浮点数，允许的 值-3.402823466E+38到-1.175494351E-38,0和 1.175494351E-38到3.402823466E+38,这些是理 论限制，基于IEEE标准。实际的范围根据硬 件或操作系统的不同可能稍微小些，M表示所 有位数，D表示小数位数，可以省略
DOUBLE[(M,D)]	8bytes	普通大小(双精度)浮点数，允许的 值-1.7976931348623157E+380 到-2.2250738585072014E-308,0和 2.2250738585072014E-38到 1.7976931348623157E+308.这些是理论限制， 基于IEEE标准。实际的范围根据硬件或操作 系统的不同可能稍微小些
DECIMAL[(M,D)]		存储任何具有M位数字和D位小数的值
DATE		日期，支持的范围为‘1000-01-01’到‘9999-12- 31’，MySQL以’YYYY-MM-DD’格式显示 DATE值，但允许使用字符串（‘20230721’ / ‘2023-07-21’ ）或数字(20230721)为DATE列分 配值
DATETIME		日期和时间的组合。支持的范围是‘上面加上 00：00：00’到‘上面第二个加上23：59： 59’.MySQL以YYYY-MM-DD HH:MM:SS“格式 显示DATETIME值，但允许使用字符串或数字 为DATETIME列分配值
TIMESTAMP		时间戳，范围是’1970-01-01 00:00:00’到2037年
TIME		时间，范围是‘-838：59：59’到‘838：59： 59’.MySQL以‘HH:MM:SS’格式显示TIME值， 但允许使用字符串或数字为TIME列分配值
YEAR		两位或四位格式的年。默认是四位格式。在四 位格式中，允许的值是1901到2155和0000.在 两位格式中，允许的值是70-99和00-69，表示 从1970到2069年。MySQL以yyyy格式显示 YEAR值，但允许使用字符串或数字为YEAR 列分配值
CHAR(M)	0-255 bytes	固定长度字符串，当保存时在右侧填充空格以 达到指定长度。M表示列长度。M的范围是0 到255个字符
VARCHAR(M)	0-65535 bytes	变长字符串。M表示最大列长度。M的范围是 0到65535.（VARCHAR的最大实际长度由最长 的行的大小和使用的字符集确定。最大有效长 度是65535字节）
TINYBLOB	0-255 bytes	不超过255个字符的二进制数据
TINYTEXT	0-255 bytes	短文本字符串
BLOB[(M)]	0-65535 bytes	最大长度为65535(216 -1)字节的BLOB列，可以 给出该类型的可选长度M。如果给出，则 MySQL将列创建为最小的但是足以容纳M字 节长度的值的BLOB类型
TEXT[(M)]	0-65535 bytes	长字符串，最大长度为65535(216-1)字符的 TEXT列。可以给出可选长度M。则MySQL将 列创建为最小的但是足以容纳M字符长度的值 的TEXT类型。
MEDIUMBLOB	0- 16777215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0- 16777215 bytes	中等长度文本数据
LONGBLOB		二进制形式的极大文本数据
LONGTEXT		极大文本数据

数值类型示例：
1). 年龄字段 - 不会出现负数, 而且人的年龄不会太
大
age tinyint unsigned (0-255)
2). 分数 - 总分100分, 最多出现一位小数
score double(4,1)
日期时间示例:
1). 生日字段 birthday
birthday date
2). 创建时间 createtime
createtime datetime
字符类型示例：
1). 用户名 username ------> 长度不定, 最长不会
超过50
username varchar(50) -> 2 2
2). 性别 gender ---------> 存储值, 不是男,就是
女
gender char(10) -> 1 10
3). 手机号 phone --------> 固定长度为11
phone char(11)

char 与 varchar 都可以描述字符串，char是定长字符串，指定长度多长，就占用多少个字符,和字段值的长度无关 。而varchar是变长字符串，指定的长度为最大占用长度 。

创建表

create table [if not exists] tab_name(
col_name datatype [comment '注释'],
col_name datatype
);
# 创建一个 - 学生表：学生编号、学生姓名、出生日期
create table if not exists student (id int
comment '编号'
, name varchar(50) comment '姓
名'
, birth date comment '出生日期');

表操作(修改,DDL)

• 添加列

alter table 表名 add [column] 字段名 类型;

• 修改列

alter table 表名 modify [column] 字段名 类型;

• 修改列名称和类型

alter table 表名 change [column] 原字段名 新字段名 新列类型 [comment(注释)] [约束];

• 删除列

alter table 表名 drop [column] 字段名;

• 修改表名

rename table 旧名 to 新名;
-- 或者
alter table 表名 rename to 新名;

• 删除表

drop table [if exists] 表名;
# 删除表之后会创建一个空表

删除表的时候，表中的全部数据也都会被删除

• 创建和某表一样的表

create table 表名 like 要复制的表;
# 当要复制的表不在当前数据库时
create table 表名 like 数据库名.要复制的表;

数据管理

1.插入数据 DML

insert into 表名[(字段名称1,字段名称2,...)] value(value 1,value 2, ...);
insert into 表名[(字段名称1,字段名称2,...)] values (value 1, value 2,...);
#如果前面有字段则按照前面的字段顺序添加,没有则按照默认的顺序添加

#插入多条数据
insert into 表名 value (), (), ();

2.查询数据DQL

# 无条件查询
select *(|字段1, 字段2, 字段3,...) from 表名;

#等值查询
select * from 表名 where 字段名 = value;

#设置列别名
select 字段名 [as] '别名' from 表名 WHERE 条件;

#剔除重复行
select distinct * from 表名;
#只是在显示的时候不显示重复的数据,数据未删除

3.修改数据

update 表名 set 字段 = value where 条
件;
# 修改满足条件的字段值

4.删除数据

delete from 表名 where 条件;
# 删除满足条件的行

数据备份

mysqldump [选项] 数据库名 [表名] > 地址

选项说明:

参数名	缩写	含义
–host	–host	–host
–port	-P	服务器端口号
–user	-u	MySQL 用户名
–password	-p	MySQL 密码
–databases	-B	指定要备份的数据库
–alldatabases	-A	备份mysql服务器上的所有数据库
–no-data	-d	不备份数据，默认为备份数据
–comments	-i	是否有信息备注，默认为打开的，使用–skip-comments 关闭

1.备份表结构

mysqldump -u root -p 数据库名 表1 表2 >
地址
# -d 只备份表结构
mysqldump -u root -p -d `数据库名` dept > `地址`
# 数据+表结构一起备份
mysqldump -u root -p `数据库名` > `地址`
#
mysqldump -u root -p -d -skipcomments `数据库名` > `地址`

2.备份多个数据库

mysqldump -u root -p -d -databases
数据库1 数据库2 > 地址
mysqldump -u root -p -d -B `数据库名1`
`数据库名2` `数据库名3` `数据库名4` > `地址`
# -all-databases 备份所有数据库
mysqldump -u root -p -d -alldatabases / -A > `地址`

3.备份数据和结构

# 备份命令去掉 -d
mysqldump -u root -p -databases 数据
库1 数据库2 > 地址

4.将查询的结果集保存为文件

mysql -u root -p -e "select * from 数
据库.表名" > 地址
mysql -u root -p -e "select * from `表名`" > `地址`
mysql -u root -p -e "select * from `表名`" store > `地址`

5.还原数据结构和数据

# 登录选中数据库之后执行，将数据还原到该数据
库
source 地址; # SQL 语句
# 在服务外面使用 mysql 命令还原
mysql -u root -p 新数据库名 < 地址
mysql -u root -p `表名` < `地址`

练习

创建stroe数据库，在数据库中根据开发喵store业务 创建表，表结构如下，并插入以下数据，完成下面的 sql.

表1:goods

列名	类型	说明
good_no	int	商品编号
goods_name	varchar(50)	商品名称
cost	double	商品成本
price	double	商品售价
count	int	商品库存
crate_time	datetime	商品上架时间
category_no	int	商品分类编号

创建表:

 create table goods(
    -> good_no int comment"商品编号"
 	-> goods_name varchar(50) comment '商品名称'
    -> cost double comment '商品成本',
    -> price double comment '商品售价',
    -> count int comment '商品库存',
    -> crate_time datetime comment '商品上架时间',
    -> category_no int comment '商品分类编号');

插入数据

insert into goods (good_no,goods_name,cost,price,count,crate_time,category_no) values
    -> (1,'火腿肠',0.5,2.5,50,20230313-190005,1)
    -> (2,'薯片',1.5,3.5,30,20230107195000,1)
    -> (3,'鼠标垫',20.5,25.5,20,20230307195000,4),
    -> (4,'萝卜',0.8,1.5,50,20230308795000,2),
    -> (5,'白菜',0.2,0.8,50,20230309195000,2),
    -> (5,'车厘子',30,50,50,20230310195000,3),
    -> (7,'芒果',5,8,50,20230310195000,3),
    -> (8,'五粮液',1350,1700,60,20230312195000,5),
    -> (9,'鼠标',40,50,20,20230313195000,4);

表2: category

列名	类型	说明
no	int	商品分类编号
name	varchar(50)	商品类别名称

创建表:

create table category(
    -> no int comment '商品分类编号',
    -> name varchar(50) comment '商品类别名称');

插入数据:

insert into category (no,name) values
    -> (1,'零食'),
    -> (2,'蔬菜'),
    -> (3,'水果'),
    -> (4,'电子产品'),
    -> (5,'酒水');

表3:account

列名	类型	说明
id	int	用户编号
phone	varchar(11)	用户手机号
password	varchar(50)	用户密码
type	bit	用户类型 0 管理员 1 会员
name	varchar(50)	用户名
point	int	用户积分
money	double	用户余额
create_time	datetime	用户创建时间

创建表:

 create table account (
    -> id int comment '用户编号',
    -> phone varchar(11) comment '用户手机号',
    -> password varchar(50) comment '用户密码',
    -> type bit comment '用户类型 0管理员 1会员',
    -> name varchar(50) comment '用户名',
    -> point int comment '用户积分',
    -> money double comment '用户余额',
    -> create_time datetime comment '用户创建时间');

插入数据:

insert into account (id,phone,password,type,name,create_time) values
    -> (1,111,111,0,'管理员',20230307165000)
    -> (2,222,222,1,'张三',0,350,20230311195000),
    -> (3,333,333,1,'李四',0,268.5,20230312195000),
    -> (4,444,444,1,'王五',0,956.3,20230313195000),
    -> (5,555,555,1,'孙六',2000,9,20230307175000);

表4:cart

列名	类型	说明
id	int	购物车编号
goods_no	int	商品编号
num	int	商品数量
account_id	int	用户编号
create_time	datetime	加入购物车时间

创建表

create table cart(
    -> id int comment '购物车编号',
    -> goods_no int comment '商品编号',
    -> num int comment '商品数量',
    -> account_id int comment '用户编号',
    -> create_time datetime comment '加入购物车时间');

插入数据:

insert into cart(id,goods_no,num,create_time,account_id) values
    -> (1,1,2,20230311195000,2),
    -> (2,2,2,20230312195000,3),
    -> (3,4,10,20230313195000,3),
    -> (4,6,20,20230312195000,3),
    -> (5,9,1,20230312195000,2),
    -> (6,2,10,20230313195000,4);

练习题:

1. 查询商品库存等于50的所有商品，显示商品编号，商 品名称，商品售价，商品库存。

select good_no,goods_name,price,count from goods where count = 50;

2. 查询商品成本等于60的所有商品，显示商品编号，商 品名称，商品售价，商品库存。

select good_no,goods_name,price,count from goods where cost = 60;

3. 修改购物车中id为5的num数量加一

update cart set num = num + 1 where id = 5; 会员等级表添加一列字段为 id，类型为int~~

4.会员等级表添加一列字段为 id，类型为int

5. 修改购物车表的id字段为cart_id

alter table cart change id cart_id int;

6.查询购物车数量等于10的信息，显示购物车编号，商品编号，商品数量，创建时间，账户编号。

 select cart_id,goods_no,num,account.create_time,account.id from account,cart where num = 10;

7. 删除用户表中账户余额等于9的用户

delete from account where money = 9;

8.查询购物车中用户id为3的所有信息

select * from cart where cart_id = 3;

9.查询goods表中第二页的数据，每页显示5条数据

select * from goods limit 5,5;

10.查询goods表中的数据，按照商品价格升序排序，如果商品价格一样按照成本降序排序，并显示第三页，每页显示3条数据。

select * from goods order by price, cost desc limit 6,3;

11.

12.思考:如何查询用户购物车的单个商品总价