内容导读
MySQL数据类型与约束条件
DQL条件查询与别名及去重
MySQL聚合函数
字符串与日期函数
一、MySQL数据类型与约束条件
1.1 数据类型
数据类型,指的是数据表中列(column)存放数据的类型
mysql数据类型有:数值型、字符串型、日期与时间型、null型
数值类型
类型 | 内存空间 | 范围 | 说明 |
---|---|---|---|
tinyint | 1byte | 有符号 -128~127 无符号 0~255 | 特小型整数 |
smallint | 2byte | 有符号 -32768 ~ 32767 无符号 0~65535 | 小型整数 |
mediumint | 3byte | 有符号 -2^23 ~ 2^23 - 1 无符号 0~2^24-1 | 中型整数 |
int/integer | 4byte | 有符号 -2^31 ~ 2^31 - 1 无符号 0~2^32-1 | 整数 |
bigint | 8byte | 大型整数 | |
float | 4byte | 单精度小数 | |
double | 8byte | 双精度数字 | |
decimal | 由第1个参数指定 | 精确小数decimal(10,2) 表示数值一共有10位 其中小数位有2位 |
字符串类型
存储字符序列的类据
类型 | 字符长度 | 说明 |
---|---|---|
char | 0~255 字节 | 定长字符串,最多可以存储255个字符 ;当我们指定数据表字段为char(n) 此列中的数据最长为n个字符,如果添加的数据少于n,则补'\u0000'至n长度 |
varchar | 0~65536 字节 | 可变长度字符串,此类型的类最大长度为65535 |
tinyblob | 0~255 字节 | 存储二进制字符串 |
blob | 0~65535 字节 | 存储二进制字符串 |
mediumblob | 0~1677215 字节 | 存储二进制字符串 |
longblob | 0~4294967295 字节 | 存储二进制字符串 |
tinytext | 0~255 字节 | 文本数据(字符串) |
text | 0~65535 字节 | 文本数据(字符串) |
mediumtext | 0~1677215 字节 | 文本数据(字符串) |
longtext | 0~4294967295 字节 | 文本数据(字符串) |
日期类型
在MySQL数据库中,我们也可以使用字符串来存储时间,但是如果需要基于时间字段进行查询操作(查询在某个时间段内的数据)就不便于查询实现
类型 | 格式 | 说明 |
---|---|---|
date | 2021-09-13 | 日期,只存储年月日 |
time | 11:12:13 | 时间,只存储时分秒 |
year | 2021 | 年份 |
datetime | 2021-09-13 11:12:13 | 日期+时间,存储年月日时分秒 |
timestamp | 20210913111213 | 日期+时间 (时间戳) |
说明:mysql可以给字段赋值为null。
1.2 字段约束
在创建数据表的时候,指定的对数据表的列的数据限制性的要求(对表的列中的数据进行限制)
保证数据的有效性
保证数据的完整性
保证数据的正确性
约束的分类有:
非空约束(not null):限制此列的值必须提供,不能为null
唯一约束(unique):在表中的多条数据,此列的值不能重复
主键约束(primary key):非空+唯一,能够唯一标识数据表中的一条数据
外键约束(foreign key):建立不同表之间的关联关系
1、非空约束
限制数据表中此列的值必须提供
创建表:设置图书表的book_name为not null
create table books(
book_isbn char(4),
book_name varchar(10) not null,
book_author varchar(6)
);
2、唯一约束
在表中的多条数据,此列的值不能重复
创建表:设置图书表的book_isbn为unique
create table books(
book_isbn char(4) unique,
book_name varchar(10) not null,
book_author varchar(6)
);
3、主键约束
主键:就是数据表中记录的唯一标识,在一张表中只能有一个主键(主键可以是一个列,也可以是多个列的组合)当一个字段声明为主键之后,添加数据时:此字段数据不能为null,此字段数据不能重复
创建表时添加主键约束
create table books(
book_isbn char(4) primary key,
book_name varchar(10) not null,
book_author varchar(6)
);
或者
create table books(
book_isbn char(4),
book_name varchar(10) not null,
book_author varchar(6),
primary key(book_isbn)
);
删除数据表主键约束
alter table books drop primary key;
创建表之后添加主键约束
alter table books modify book_isbn char(4) primary key;
主键自增长:
在我们创建一张数据表时,如果数据表中有列可以作为主键(例如:学生表的学号、图书表的isbn)我们可以直接这是这个列为主键;
当有些数据表中没有合适的列作为主键时,我们可以额外定义一个与记录本身无关的列(ID)作为主键,此列数据无具体的含义主要用于标识一条记录;
在mysql中我们可以将此列定义为int,同时设置为`自动增长`,当我们向数据表中新增一条记录时,无需提供ID列的值,它会自动生成。
定义主键自动增长
定义int类型字段自动增长:auto_increment
create table types(
type_id int primary key auto_increment,
type_name varchar(20) not null,
type_remark varchar(100)
);
注意:自动增长从1开始,每添加一条记录,自动的增长的列会自定+1,当我们把某条记录删除之后再添加数据,自动增长的数据也不会重复生成(自动增长只保证唯一性、不保证连续性)
联合主键
指的是,数据表中的多列组合在一起设置为表的主键
定义联合主键:
create table grades(
stu_num char(8),
course_id int,
score int,
primary key(stu_num,course_id)
);
注意:在实际企业项目的数据库设计中,联合主键使用频率并不高;当一个张数据表中没有明确的字段可以作为主键时,我们可以额外添加一个id字段作为主键。
4、外键约束
指的是一对多的两张表,需要将一的那个表的主键作为多的那个表的外键,从而使两张表关联起来。
实际开发,一般只设置外键字段,不设置外键约束条件。
二、DQL条件查询与别名及去重
1、MySQL的DQL:全称为database query language,数据库查询语言,用于查询表中的数据。
2、查询分类
(1)从单表与多表查询上,分两种:单表查询、多表关联查询。
(2)从语法格式上,也为分两种查询
简单查询:不带条件的查询
条件查询(复杂查询):带有where条件的查询
2.1 where子句
在查询、删除、修改的语句后都可以添加where子句后边带上条件,用于筛选满足特定的添加的数据进行查询、删除和修改操作。
select ... from 表名 where 条件;
delete from 表名 where 条件;
update 表名 set ... where 条件;
条件关系运算符:
# = 等于
select * from stus where stu_num = '20210101';
# != <> 不等于
select * from stus where stu_num != '20210101';
select * from stus where stu_num <> '20210101';
# > 大于
select * from stus where stu_age>18;
# < 小于
select * from stus where stu_age<20;
# >= 大于等于
select * from stus where stu_age>=20;
# <= 小于等于
select * from stus where stu_age<=20;
# between and 区间查询 比如:between v1 and v2
select * from stus where stu_age between 18 and 20;
# in(值1,值2,...值n) 在指定的数中
select * from stus where stu_age(18,16,20,17);
条件逻辑运算符:
# and 并且 筛选多个条件同时满足的记录
select * from stus where stu_gender='女' and stu_age<21;
# or 或者 筛选多个条件中至少满足一个条件的记录
select * from stus where stu_gender='女' or stu_age<21;
# not 取反
select * from stus where stu_age not between 18 and 20;
# 查询某个字段为null的所有记录,此处不应该是stu_name==null
select * from stus where stu_name is null;
# 查询某个字段不为null的所有记录,此处不应该是stu_name!=null
select * from stus where stu_name is not null;
like子句:
在where子句的条件中,我们可以使用like关键字来实现模糊查询
语法:
select * from 表名 where 列名 like 'reg%';
在like关键字后的reg表达式中:
%
表示任意多个字符
_
表示任意单个字符
# 查询学生姓名包含字母o的学生信息
select * from stus where stu_name like '%o%';
# 查询学生姓名第一个字为`张`的学生信息
select * from stus where stu_name like '张%';
# 查询学生姓名最后一个字母为o的学生信息
select * from stus where stu_name like '%o';
# 查询学生姓名中第二个字母为o的学生信息
select * from stus where stu_name like '_o%';
2.2 计算列
对从数据表中查询的记录的列进行一定的运算之后显示出来
# 出生年份 = 当前年份 - 年龄
select stu_name,2021-stu_age from stus;
+-----------+--------------+
| stu_name | 2021-stu_age |
+-----------+--------------+
| omg | 2000 |
| Peter | 2003 |
| Tom | 2001 |
| Lucy | 2000 |
| Polly | 2000 |
| Theo | 2004 |
+-----------+--------------+
2.3 as给字段起别名
可以通过as关键字为查询结果的列名取一个语义性更强的别名 (
as
关键字也可以省略)为了方便使用我们还可以通过as给表取个别名。
# 将2021-stu_age的结果取一个stu_birth_year名
select stu_name,2021-stu_age as stu_birth_year from stus;
+-----------+----------------+
| stu_name | stu_birth_year |
+-----------+----------------+
| omg | 2000 |
| Peter | 2003 |
| Tom | 2001 |
| Lucy | 2000 |
| Polly | 2000 |
| Theo | 2004 |
+-----------+----------------+
# 给stu_name取个别名为:姓名
select stu_name as 姓名,2021-stu_age as 出生年份 from stus;
+-----------+--------------+
| 姓名 | 出生年份 |
+-----------+--------------+
| omg | 2000 |
| Peter | 2003 |
| Tom | 2001 |
| Lucy | 2000 |
| Polly | 2000 |
| Theo | 2004 |
+-----------+--------------+
2.4 distinct消除重复行
从查询的结果中将重复的记录消除
select stu_age from stus;
+---------+
| stu_age |
+---------+
| 21 |
| 18 |
| 20 |
| 21 |
| 21 |
| 17 |
+---------+
select distinct stu_age from stus;
+---------+
| stu_age |
+---------+
| 21 |
| 18 |
| 20 |
| 17 |
+---------+
三、MySQL聚合(组合)函数与日期
SQL中提供了一些可以对查询的记录的列进行计算的函数
count():统计函数,统计满足条件的指定字段的个数(记录数),比如:count(*)或count(id)
max:求所有记录中某字段的最大值
min:求所有记录中某字段的最小值
sum:求所有记录中某字段的和值
avg:求所有记录中某字段的平均值
-
统计满足条件的指定字段的个数(记录数)
# 统计学生表中学生总数
select count(stu_num) from stus;
+----------------+
| count(stu_num) |
+----------------+
| 7 |
+----------------+
或
select count(*) from stus;
+----------+
| count(*) |
+----------+
| 7 |
+----------+
# 统计学生表中性别为男的学生总数
select count(stu_num) from stus where stu_gender='男';
+----------------+
| count(stu_num) |
+----------------+
| 5 |
+----------------+
-
max()
计算最大值,查询满足条件的记录中指定列的最大值
select max(stu_age) from stus;
+--------------+
| max(stu_age) |
+--------------+
| 21 |
+--------------+
select max(stu_age) from stus where stu_gender='女';
+--------------+
| max(stu_age) |
+--------------+
| 21 |
+--------------+
-
min()
计算最小值,查询满足条件的记录中指定列的最小值
select min(stu_age) from stus;
+--------------+
| min(stu_age) |
+--------------+
| 14 |
+--------------+
select min(stu_age) from stus where stu_gender='女';
+--------------+
| min(stu_age) |
+--------------+
| 18 |
+--------------+
-
sum()
计算和,查询满足条件的记录中 指定的列的值的总和
# 计算所有学生年龄的综合
select sum(stu_age) from stus;
+--------------+
| sum(stu_age) |
+--------------+
| 133 |
+--------------+
# 计算所有性别为男的学生的年龄的综合
select sum(stu_age) from stus where stu_gender='男';
+--------------+
| sum(stu_age) |
+--------------+
| 94 |
+--------------+
-
avg()
求平均值,查询满足条件的记录中 计算指定列的平均值
select avg(stu_age) from stus;
+--------------+
| avg(stu_age) |
+--------------+
| 19.0000 |
+--------------+
select avg(stu_age) from stus where stu_gender='男';
+--------------+
| avg(stu_age) |
+--------------+
| 18.8000 |
+--------------+
四、日期函数和字符串函数
4.1 日期函数
当我们向日期类型的列添加数据时,可以通过字符串类型赋值
格式为:'yyyy-mm-dd' 或 'yyyy-MM-dd hh:mm:ss'
我们可以通过以下函数获取当前系统时间:
now()、sysdate()、curdae()、curtime()
desc stus;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| stu_num | char(8) | NO | PRI | NULL | |
| stu_name | varchar(20) | NO | | NULL | |
| stu_gender | char(2) | YES | | NULL | |
| stu_age | int | NO | | NULL | |
| stu_tel | char(11) | NO | UNI | NULL | |
| stu_qq | varchar(11) | YES | UNI | NULL | |
| stu_enterence | datetime | YES | | NULL | |
+---------------+-------------+------+-----+---------+-------+
# 通过字符串类型 给日期类型的列赋值
insert into stus(stu_num,stu_name,stu_gender,stu_age,stu_tel,stu_qq,stu_enterence)
values('20200108','张小三','女',20,'13434343344','123111','2021-09-01 09:00:00');
# 通过now()获取当前时间
insert into stus(stu_num,stu_name,stu_gender,stu_age,stu_tel,stu_qq,stu_enterence)
values('20210109','张小四','女',20,'13434343355','1233333',now());
# 通过sysdate()获取当前时间
insert into stus(stu_num,stu_name,stu_gender,stu_age,stu_tel,stu_qq,stu_enterence)
values('20210110','李雷','男',16,'13434343366','123333344',sysdate());
# 通过now和sysdate获取当前系统时间
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2023-10-15 10:04:55 |
+---------------------+
mysql> select sysdate();
+---------------------+
| sysdate() |
+---------------------+
| 2023-10-15 10:05:05 |
+---------------------+
mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2023-10-15 |
+------------+
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 11:04:18 |
+-----------+
4.2 字符串函数
通过SQL的字符串指令对字符串进行处理
# (1)concat(colnum1,colunm2,...) 拼接多列
select concat(stu_name,'-',stu_gender) from stus;
+---------------------------------+
| concat(stu_name,'-',stu_gender) |
+---------------------------------+
| Peter-女 |
| Tom-男 |
| Lucy-女 |
| Link-男 |
+---------------------------------+
# (2)upper(column) 将字段的值转换成大写
mysql> select upper(stu_name) from stus;
+-----------------+
| upper(stu_name) |
+-----------------+
| Peter |
| TOM |
| LUCY |
| POLLY |
| THEO |
| Link |
+-----------------+
# (3)lower(column) 将指定列的值转换成小写
mysql> select lower(stu_name) from stus;
+-----------------+
| lower(stu_name) |
+-----------------+
| Peter |
| tom |
| lucy |
| polly |
| theo |
+-----------------+
# (4)substring(column,start,len) 从指定列中截取部分显示 start从1开始
mysql> select stu_name,substring(stu_tel,8,4) from stus;
+-----------+------------------------+
| stu_name | substring(stu_tel,8,4) |
+-----------+------------------------+
| Peter | 3311 |
| Tom | 3302 |
| Lucy | 3334 |
+-----------+------------------------+
面试问题
1、MySQL常用数据类型有哪些?
2、MySQL建库与建表命令
3、简单说说DBMS与DB的关系
4、MySQL数据库的特点是什么?
5、如何修改和删除数据表?