week08_day05_DDL之数据类型&&DML&&DQL

最新推荐文章于 2020-06-05 14:17:06 发布

-玫瑰少年-

最新推荐文章于 2020-06-05 14:17:06 发布

阅读量353

点赞数

分类专栏：数据结构与数据库

本文链接：https://blog.csdn.net/shihao1998/article/details/106429387

版权

数据结构与数据库专栏收录该内容

30 篇文章 0 订阅

订阅专栏

首先讲一下数据类型：
注意：这里以MySQL为例，不同的DBMS的都支持数值类型，字符串类型以及日期类型，但他们的实现可能不一样。

数值类型：

整数类型

2.浮点数类型和定点数类型

其中 M 称为精度，表示总共的位数;
D 称为标度，表示小数的位数。
DECIMAL 类型不同于 FLOAT & DOUBLE，DECIMAL 实际是以字符串存放的，它的存储空间并不固定，而是由精度 M 决定的。

为什么要引入定点数DECIMAL呢？
DECIMAL 类似于 java中的BigDecimal类，浮点数是不精确的，DECIMAL类型是精确的，它是以字符串进行加减的。
存储金额的时候不要用浮点数类型。

····································································································································································································································

日期与时间类型
在这里插入图片描述
DATETIME 和 TIMESTAMP 虽然显示的格式是一样的，但是它们有很大的区别：

DATETIME 的系统默认值是 NULL, 而 TIMESTAMP 的系统默认值是当前时间 NOW();
mysql中很多数据类型的默认值都是null，如INT等。
DATETIME 存储的时间与时区无关，而 TIMESTAMP 与时区有关。

为什么TIMESTAMP可以用4个字节表示，而DATETIME得用8个字节呢？
DATETIME表示的是自基准时间以来的毫秒数，而TIMESTAMP表示的是自基准时间以来的秒数。
基准时间：1970年1月1日0点0分0秒（格林威治时间）

# 1) datetime的默认值是null, timestamp的默认值是now()
create database day17;
use day17;
create table t_time(
	a datetime,
    b timestamp
);
insert into t_time(a) values(now()); 
insert into t_time(b) values(now());
desc t_time;
select * from t_time;

执行结果如下：
在这里插入图片描述
insert into t_time(a) values(now()); 执行完后，a列插入当前时间的数据，b列默认值为now()，所以也为当前时间。
insert into t_time(b) values(now());执行完后，b列插入当前时间的数据，a列默认值为null。

字符串类型
在这里插入图片描述
当用char(10)表示一个数据所占的字节个数时，即使这个数据是‘a’，它也占10个字节。
当用varchar(10)表示一个数据所占的字节个数时，表示这个数据最大占10字节，如‘abc’这个数据总共占4字节，1个字节表示数据的长度，3个字节表示数据。（上述图表中L表示数据，+1表示用1个字节记录数据长度，告诉计算机往后读取几个字节能读完这个数据）
char和varchar类型一般用于存储字符串

TINYTEXT用于存储文本类型的数据。
TINYTEXT其实就等于varchar(255)

ENUM存的数据如：‘男’、‘女’。里面的类型都是一种类型，如：字符串类型
SET类型存的数据如：‘打篮球’、‘唱歌’、‘吃’。里面的类型可以是不同类型。

ENUM 类型总有一个默认值，当ENUM 列声明为NULL，则默认值为NULL。如果 ENUM 列被声明为 NOT NULL，则其默认值为列表的第一个元素。

二进制类型
在这里插入图片描述
对比这张表和上面字符串类型的那张表
BINARY 和char类型类似
VARBINARY和varchar类型类似
下面的blob类型和text类型相似
blob类型对应java中的blob类型，text类型对应java中的cblob类型，c表示character，字符

############################### 数据类型 ################################
# 注意事项：字符串类型和日期类型应该用单引号括起来。

# a. 数值类型
# 整数类型：tinyint, smallint, mediumint, int, bigint
# 小数类型：float(M,D), double(M,D), decimal(M, D)

# b.日期类型 
# year, date, time, datetime, timestamp
# datetime vs timestamp
# 1) datetime的默认值是null, timestamp的默认值是now()
create database day17;
use day17;
create table t_time(
	a datetime,
    b timestamp
);
insert into t_time(a) values(now()); 
insert into t_time(b) values(now());
desc t_time;
select * from t_time;
# 2) datetime的值与时区无关, timestamp的值会随着时区的改变而改变。
set time_zone = '+10:00';   #将时区改成东十区后，b列的值会加两个小时
select * from t_time;
set time_zone = '+8:00';

# c.字符串类型
# char(M), varchar(M), tinytext, text, mediumntext, longtext, enum, set
# char(M): M个字节 
# varchar(M): L+1个字节(L字符的长度)
# enum: 如果enum有非空约束，那么它的默认值是定义时的第一个元素。
drop table t_enum;
create table t_enum (
	gender enum('male','female') not null,
    hobby set('打篮球','唱歌','跑步')
);
insert into t_enum (gender) values ('female');
insert into t_enum (hobby) values ('打篮球');
select * from t_enum;

#以下三条记录只能插入数据null，不能插入其他不存在的数据
insert into t_enum (hobby) values (null);
insert into t_enum (hobby) values ('x');
insert into t_enum (hobby) values ('ab');

#set
create table t_set (
	hobby set('打篮球','唱歌','跑步')
);
insert into t_set (hobby) values ('打篮球');
insert into t_set (hobby) values ('打篮球,唱歌');
insert into t_set (hobby) values ('唱歌,打篮球,跑步');
insert into t_set (hobby) values ('健身');
select * from t_set;

# d.二进制类型 
# bit(M), binary(M), varbinary(M), tinyblob, blob, mediumnblob, longblob

DML（数据操纵语言）

DML:Data Manipulation Language
作用：用于向数据库表中插入、删除、修改数据。
常用关键字：INSERT UPDATE DELETE

Insert语句

INSERT INTO table [(column [, column…])]
VALUES (value [,value…]);

插入的数据应与字段的数据类型相同。
数据的大小应在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中。
在values中列出的数据。
字符串和日期型数据应包含在单引号中。
插入空值 insert into table value(null)

Update语句

UPDATE tbl_name
SET col_name1=expr1 [, col_name2=expr2 …]
[WHERE where_definition]

UPDATE语法可以用新值更新原有表行中的各列。
SET子句指示要修改哪些列和要给予哪些值。
WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行。

Delete语句

delete from table_name
[WHERE where_definition]

delete会删除一条记录，如果只是想删除一条记录的某个属性的值，使用update语句将其置为空即可。

如果不使用where子句，将删除表中所有数据。
Delete语句不能删除某一列的值（可使用update）删除的单位是行(行)
使用delete语句仅删除记录，不删除表本身。如要删除表，使用drop table语句。

DQL数据查询语言（简单查询）
DQL：Data Query Language
作用：查询表中的数据。
关键字：
SELECT

常见运算符介绍
算术运算符
+ - * / %

比较运算符在这里插入图片描述
逻辑运算符
NOT(!) AND(&&) OR(||)

位操作运算符
& | ~ ^ << >>

######################################### DML ##################################
# a. 插入
# 语法：insert into tb_name [column1, column2 ...] values (val1, val2 ...);
use day17;
create table t_user (
	id int,
    name varchar(20),
    age int,
    gender enum('female', 'male')
);
insert into t_user values (1, 'liu', 23, 'male');
insert into t_user (id, name) values (2, '张三');
insert into t_user (id, name, age) values (3, '李四', 20), (4, '王五', 21);
select * from t_user;

create table user (
	id int,
    name varchar(20),
    age int,
    gender enum('male', 'female')
);
#将一张表中的所有数据插入另一张表
insert into user (select * from t_user);
select * from user;

# b.修改
# 语法：update tb_name set col_name=val [, col_name=val...] [where 子句]
# 注意事项：如果没有where子句就会更新所有行 
# 练习1: 把t_user表中所有人的年龄改为18
SET SQL_SAFE_UPDATES = 0;   #修改下数据库模式
update t_user set age=18;
select * from t_user;
# 练习2：把名字为茜茜的用户的年龄改为16, 性别改成female
update t_user set gender='male' where name='张三';


# c. 删除
# 语法：delete from tb_name [where 子句]
# 注意事项：
# 1) 删除的基本单位时记录, 如果不想删除记录而只是想删除这条记录某个字段的值, 我们可以用update将该字段置为null
# 2) 如果没有where子句，就会删除表中所有的记录
# 3) delete只会删除记录，不会删除表。如果要删除表，可以使用drop table
# 练习1：删除名字为张三的记录
delete from t_user where name='张三'; 
# 练习2：清空t_user表中的所有记录
delete from t_user; 


################################# 数据库备份和恢复 ##############################
# a. 恢复
# 两种方法
# 1) 在cmd中, mysql -u $user -p $db_name < file
#    如：mysql -u root -p King_of_honor < heros.sql
create database King_of_honor;
use king_of_honor;
show tables;   #就可以看到heros中的这张表导入进来了
select * from heros;
drop table heros;
# 2) 登录mysql服务, 进入数据库 source file路径 (注意：后面不要加分号)
#    如：mysql -u root -p
#        use King_of_honor;
#        source E:\WangDao\JavaSE2\db\heros\heros.sql

# b. 备份
# 在cmd中, mysqldump -u $user -p $db_name > file
#   如：mysqldump -u root -p king_of_honor > E:\WangDao\JavaSE2\db\heros2.sql


################################ 常用运算符 ####################################
# a. 算术运算符：+ - * / %
# b. 比较运算符：=, <>(!=), >, <, >=, <=, <=>, is null, is not null
#                between and, in, not in, like
# = 和 <=>  （一定要用<=>）
# 练习：查询没有辅助角色定义的英雄有哪些？ 
select name,role_assist from heros where role_assist = null;
select name,role_assist from heros where role_assist <=> null;
select name, role_assist from heros where role_assist is null;
select name, role_assist from heros where role_assist is not null;

# between and
# 练习：查询hp_max大于等于7000，小于等于8050的英雄有哪些？
select name, hp_max from heros where hp_max >= 7000 and hp_max <= 8050;
select name, hp_max from heros where hp_max between 7000 and 8050;

# in, not in 
# 练习：主要角色定义是战士和法师的英雄有哪些？ 
select name, role_main from heros where role_main = '战士' or role_main = '法师';
select name,role_main from heros where role_main in('战士','法师');
# 练习：主要角色定义不是辅助和坦克的英雄有哪些？
select name,role_main from heros where role_main not in('辅助', '坦克');

# like 与通配符搭配使用, 进行模糊查询。
# %：匹配任何数目的字符，甚至包括零个字符
# _: 匹配一个字符
# 不同的DBMS，通配符可能不一样, 比如SQL Server是 * 匹配所有字符, ? 匹配一个字符
# 练习：查询名字中包含'太'的英雄。
select name from heros where name like '%太%';
# 练习：除第一个字之外，其余字中包含'太'的英雄有哪些？ 
select name from heros where name like '_%太%';

# c. 逻辑运算符：NOT(!)  AND(&&)  OR(||)
# d. 位运算符：& | ~  ^ << >>



################################ 简单查询 ######################################
# a. 计算表达式和函数的值
select now();
select 3*2;
select year(now());
select concat('ab', 'cd', 'xyz');
select trim('   abc   xyz   ');
select abs(-100);

# b. 查询表中的字段
# 查询单个字段的值，比如：查询 heros 表中所有英雄的名字。
select name from heros;

# 查询多个字段的值，多个字段之间用 `,` 分隔。
# 比如：查询 heros 表中所有英雄的名字，最大生命值，最大法力值以及主要角色定位。
select name,hp_max, mp_max, role_main from heros;

# 查询所有字段，还可以用 `*` 代指所有字段。比如：查询 heros 表中所有数据。
select * from heros;   #生产环境中最好不要使用select *，会耗费服务器资源，查询不必要的数据会降低查询和应用程序的效率。开发和测试环境中可以使用

# c. 使用 WHERE 子句过滤记录
# 在 MySQL 中，0 表示 false，非 0 表示 true，mysql底层是用c语言写的
select * from heros where 0;
select * from heros where 1;
select * from heros where name='花木兰';

# d. 给字段起别名 
# `AS` 可以给字段起别名。比如：
select hp_max as hp, mp_max as mp from heros;

# e. 去除重复行 
# `DISTINCT` 可以对查询结果去重。
# 注意事项：
# a. DISTINCT 关键字必须放在所有查询字段的前面
# b. DISTINCT 是对所有查询字段的组合进行去重，也就是说每个字段都相同，才认为两条记录是相同
# 练习：查询主要角色定位有哪些？
select distinct role_main from heros;
select distinct role_main, role_assist from heros;

# f.排序
# `ORDER BY` 可以对结果集进行排序。`ASC` 表示升序，`DESC` 表示降序，默认情况为升序。
# 练习：查询所有英雄，并按最大生命值排序。
select name, hp_max from heros order by hp_max;    #默认升序
select name, hp_max from heros order by hp_max desc;

# 还可以对多个字段进行排序。即先按照第一个字段排序，当第一个字段相同时，再按照第二个字段排序，依此类推。
# 练习：查询所有英雄，先按最大生命值排序，再按照最大法力值排序
select name, hp_max, mp_max from heros order by hp_max, mp_max;
select name, hp_max, mp_max from heros order by hp_max asc, mp_max desc;

# `ORDER BY` 可以对非选择字段进行排序，也就是说排序的字段不一定要在结果集中。
SELECT name, hp_max FROM heros ORDER BY hp_max ASC, mp_max DESC;

# 甚至，我们还可以对计算字段进行排序。
SELECT name, hp_max FROM heros ORDER BY (hp_max + mp_max) DESC;

# g. 限制结果集 
# `LIMIT` 可以限制结果集的数量。它有两种使用方式：`LIMIT offset, nums` 和 `LIMIT nums OFFSET offset`。
# LIMIT nums OFFSET offset, nums表示显示nums条数据，offset表示偏移量（从第offset条开始算，算nums条数据显示出来）
# 注意：不同的 DBMS 用来限制结果集的关键字是不一样的。比如，Microsoft SQL Server 和 Access  使用的是 TOP 关键字。
# 练习：我们想查询最大生命值最高的5名英雄。
select name, hp_max from heros order by hp_max desc limit 0,5;
select name, hp_max from heros order by hp_max desc limit 5 offset 1;  #建议使用
select name, hp_max from heros order by hp_max desc limit 5;  #当offset为0时，可以把offset 0 给去掉

# 分页查询 (rows, page)   limit rows offset rows * (page - 1)

# h. 计算字段
# 计算字段并不实际存在于数据库表中，它是由表中的其它字段计算而来的。一般我们会给计算字段起一个的别名。
select name, hp_max+mp_max from heros;
select name, hp_max+mp_max as total_max from heros;


# i. 聚合函数：只有5个：count()、sum()、avg()、max()、min()
# count() 
# `COUNT(*)` 可以统计记录数。可以统计null行
# 练习：查询heros表中有多少条记录 
select count(*) from heros;
create table temp (
	a int
);
insert into temp values (null);
select * from temp;
select count(*) from temp;

# COUNT()` 作用于某个具体的字段，可以统计这个字段的非 `NULL` 值的个数。
# 练习：查询有辅助角色定义的英雄有多少个
select count(role_assist) from heros;

# sum()
# `SUM()` 用于统计某个字段非 `NULL` 值的和。
select sum(hp_max) from heros;

# avg()
# `AVG()` 用于统计某个字段非 `NULL` 值的平均值。
select avg(hp_max) from heros;
select round(avg(hp_max)) from heros;   #round()表示对小数点后面的数字四舍五入alter

# max()
# `MAX()` 用于统计某个字段非 `NULL` 值的最大值。
select max(hp_max) from heros;

# min()
# `MIN()` 用于统计某个字段非 `NULL` 值的最小值。
select min(hp_max) from heros;

# distinct 
# 我们还可以对字段中不同的值进行统计。先用 `DSITINCT` 去重，再用聚合函数统计。
select count(hp_max) from heros;
select count(distinct hp_max) from heros;

# j.分组
# `GROUP BY` 可以对记录进行分组。 
# 1)  搭配聚合函数使用
# 练习：按照主要角色定位进行分组，并统计每一组的英雄数目。
select role_main, count(*) from heros group by role_main;
select role_assist, count(*) from heros group by role_assist;

# 2) GROUP_CONCAT
# 如果我们想知道每种角色的英雄都有哪些？ 
# select name, role_main from heros group by role_main;
select role_main, group_concat(name) from heros group by role_main;

# 3) 多字段分组
# 我们可以对多个字段进行分组。也就是说，每个字段的值都相同的记录为一组。
# 练习：根据role_main, role_assist 进行分组，role_main, role_assist 相同的为一组
SELECT COUNT(*) AS num, role_main, role_assist 
FROM heros 
GROUP BY role_main, role_assist 
ORDER BY num DESC;

# 4)  HAVING 过滤分组
# `HAVING` 可以过滤分组。
# 我们想要按照英雄的主要角色定位，次要角色定位进行分组，并且筛选分组中英雄数目大于 5 的组，
# 最后根据每组的英雄数目从高到低进行排序。
SELECT COUNT(*) AS num, role_main, role_assist 
FROM heros 
GROUP BY role_main, role_assist 
having num>5
ORDER BY num DESC;

# 练习：筛选最大生命值大于 6000 的英雄，按照主要角色定位，次要角色定位分组，
# 并且筛选英雄数目大于 5 的分组，最后按照英雄数目从高到低进行排序。
SELECT COUNT(*) AS num, role_main, role_assist 
FROM heros 
where hp_max>6000
GROUP BY role_main, role_assist 
having num>5
ORDER BY num DESC;

#where 与 having 的区别：
# where 是分组前用来过滤记录的，having 是分组后用来过滤分组的。

查询顺序：

关键字的顺序
SELECT…FROM…WHERE…GROUP BY…HAVING…ORDER BY…LIMIT;
底层执行的顺序
FROM > WHERE > GROUP BY > HAVING > SELECT字段 > DISCTINCT > ORDER BY > LIMIT;

····································································································································································································································

作业：

在王者荣耀的heros表中，做如下查询：
a. 查询名字长度为3的近战英雄(提示：char_length()函数可以求字符串的长度)
b. 查询既有主要角色定位，又有辅助角色定位的英雄有哪些？
c. 查询李姓的英雄有哪些？
d. 查询不同的攻击范围？
e. 查询最大生命值在[6500, 8000]范围, 并且主要角色定位为刺客或者战士的英雄有哪些，并且按照生命值从大到小排序。
f. 查询初始物理攻击最高的前3名英雄

use king_of_honor;
# a. 查询名字长度为3的近战英雄(提示：char_length()函数可以求字符串的长度)
select name,attack_range from heros where char_length(name)=3 and attack_range='近战';

# b. 查询既有主要角色定位，又有辅助角色定位的英雄有哪些？
select name, role_main, role_assist from heros where role_main is not null and role_assist is not null;

# c. 查询李姓的英雄有哪些？
select name from heros where name like '李%';

# d. 查询不同的攻击范围？
select distinct attack_range from heros;

# e. 查询最大生命值在[6500, 8000]范围, 并且主要角色定位为刺客或者战士的英雄有哪些，并且按照生命值从大到小排序。
select name, hp_max, role_main
from heros 
where hp_max between 6500 and 8000
and role_main in ('刺客','战士')
order by hp_max desc;

# f. 查询初始物理攻击最高的前3名英雄
select name,attack_start 
from heros 
order by attack_start desc 
limit 3 offset 0;

-玫瑰少年-

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
week08_day05_DDL之数据类型&&DML&&DQL

首先讲一下数据类型：注意：这里以MySQL为例，不同的DBMS的都支持数值类型，字符串类型以及日期类型，但他们的实现可能不一样。数值类型：整数类型2.浮点数类型和定点数类型其中 M 称为精度，表示总共的位数;D 称为标度，表示小数的位数。DECIMAL 类型不同于 FLOAT & DOUBLE，DECIMAL 实际是以字符串存放的，它的存储空间并不固定，而是由精度 M 决定的。为什么要引入定点数DECIMAL呢？DECIMAL 类似于 java中的BigDecimal类，浮
复制链接

扫一扫