MySQL基础核心

MySQL中的数据类型
MySQL中的运算符
MySQL完整性约束
关系型数据库表的关系
关系数据库的范式
常用SQL
问题

MySQL中的数据类型

MySQL提供的数据类型主要包括数值类型、字符串类型、日期时间类型、枚举类型

1.数值类型

以MySQL5.0为例，其支持的数值类型包括整数类型、浮点数类型、定点数类型、位类型。
1. 整数类型：tinyint、smallint、mediumint、int、bigint，分别占用1个、2、3、4、8个字节。对于整形数据，可以在名称后面的小括号内指定显示宽度。例如：int(6)表示整数占6位，不足的话则填充。不指定显示宽度则默认为int(11)
2. 浮点数类型：float、double。浮点数和定点数都可以用类型名称加上(M,D)的方式进行表示，（M,D）表示该值一共显示M位（包括整数位和小数位），D表示小数位。M和D又称为精度和标度。
3. 定点数类型：decimal，decimal在不指定精度时，默认的整数位为10，小数位为0。
4. bit类型：bit(M)用于存放多位二进制数，M范围为1~64，如果不指定M，则默认为1。
如下所示：
问题：定点数和浮点数的区别？
1. 浮点数如果有精度和标度，则会自动将四舍五入后的结果插入，系统不会报错。
2. 而定点数如果不写精度和标度，则会按照默认值decimal（10，0）进行操作，并且如果数据超过了精度和标度值，系统会报错。

2.日期时间类型

以MySQL5.0为例，支持的日期时间类型如下：
timestamp：保存的是一个时间戳，从1970-01-01 00:00:00 UTC到要存储的时间所相差的毫秒数。这一类型的时间数据的插入和删除都受主机所在时区的影响，更能反映实际的日期
datetime：只能反映插入datetime类型的数据时主机所在的时区，其他时区的人查看数据会有误差。

3.字符串类型

以MySQL5.0为例，支持的字符串类型如下：
char类型和varchar类型：char类型的字段长度固定为创建表时声明的长度，在MySQL8.0.34则不是，例子如下；而varchar类型的字段长度为可变字符串的长度。

CREATE TABLE t(c1 CHAR(4), c2 VARCHAR(4));
INSERT INTO  t(c1, c2) VALUES('hi','hi');
select length(c1), length(c2) from t;
+------------+------------+
| length(c1) | length(c2) |
+------------+------------+
|          2 |          2 |
+------------+------------+
1 row in set (0.00 sec)

枚举类型enum：枚举类型的值只允许从值集合中取一个值。例如一个学生信息表中的性别这个字段就可以使用枚举类型，gender ENUM('M','W')
set类型：set类型的值一次性可以取多个。

MySQL中的运算符

表结构如下：

mysql> select * from t2;
+--------+------+
| name   | id   |
+--------+------+
| 张三   | NULL |
| 李四   |    2 |
+--------+------+
2 rows in set (0.00 sec)

等于：=
不等于：!=或者<>

select * from t2 where id != 1;
select * from t2 where id <> 1;

<=>：为NULL

select * from t2 where id is NULL;
select * from t2 where id <=> NULL;

is not NULL：不为NULL

MySQL完整性约束

常用约束如下所示：

主键约束：primary key，使用了这个主键约束的字段其值不能为空，通常另外搭配非空约束，对于整数类型还搭配自增长约束
自增长约束：auto_increment
唯一键约束：unique，这个主键约束的字段其值可以为空，这是和主键的一个区别。
非空约束：not null
默认值约束：default
外键约束：foreign key
无符号约束：unsigned，例如对于年龄字段就可以使用这个约束。

关系型数据库表的关系

父表和子表：表B引用表A的字段作为外键，则表A称之为父表，表B称之为子表。

1.一对一

一对一关系示例：例如用户信息和身份信息就构成一对一的关系，一个用户只能存在一条身份信息，一条身份信息只能有一个用户。

# 用户表
CREATE TABLE t_user (
	uid INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	nickname varchar(32) UNIQUE,
	age TINYINT UNSIGNED DEFAULT 18,
	gender ENUM('male','female')
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

# 身份信息表
CREATE TABLE info (
	carid CHAR(18) NOT NULL,
	addrinfo VARCHAR(64) NOT NULL
);

对于存在一对一关系的实体之间，在设计表的时候，通常在子表中增加一个字段关联父表的主键。因此对于1中的身份信息表，需要增加一个字段uid，关联父表t_user。这个字段可以不设置外键，因为外键概念在应用层解决。

2.一对多

一对多关系示例：用户和订单之间的关系就是一对多的关系，一个用户可以有多个订单，但是一个订单只能有一个用户下单。
对于存在一对多关系的实体之间，在设计表的时候，和上述一样，通常在子表中增加一个字段关联父表的主键。

3.多对多

多对多关系示例：商品和订单之间的关系就是多对多的关系，一个商品可以存在多个订单号，一个订单号也可以购买多个商品。
对于存在多对多关系的实体之间，在设计表的时候，通常增加一个中间表。例如在商品和订单之间增加一个商品订单表。

关系数据库的范式

关系型数据库的范式主要为了减少数据的冗余，以及消除删除异常、更新异常、插入异常。但是并不是范式越高越好，因为应用的范式越高，拆分的表越多。表太多会带来问题：
1. 查询时需要连接多个表，增加了SQL查询的复杂度
2. 查询时需要连接多个表，降低了数据库的查询性能。
目前关系型数据库的范式有6个。
1. 第一范式（1NF）：每一个字段保持原子特性
2. 第二范式（2NF）：非主键属性完全依赖于主键（包括联合主键，只依赖于联合主键的其中一个字段，也不符合第二范式），如果不是完全依赖于主键，应该拆分成新的实体。
3. 第三范式（3NF）：属性不依赖于其他非主键属性。一般关系型数据库满足第三范式就可以。
4. 第四范式（4NF）：消除表中的多值依赖。表中某个属性包含多个值，为了减少维护数据一致性的工作，将包含多值的属性放入一个新表。
5. 第五范式（5NF）:
6. BC范式（BCNF）：每个表中只有一个候选键。什么是候选键：可以唯一标识一条记录的字段，这个字段可以作为候选键。

常用SQL

SQL主要分为三个类别，分别是DDL（数据定义语句），DML（数据操纵语句），DCL（数据控制语句）。

1.库操作

库操作属于DDL语句。

查询系统中存在哪些数据库：show databases;
创建数据库：create database 数据库名称
删除数据库：drop database 数据库名称
选择数据库：use 数据库名称，选择了数据库后接下来才可以对数据库中的表进行操作。

2.表操作

查看数据库下的所有表：show tables;
创建表：使用CREATE关键字

CREATE TABLE t_user (
		uid INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
		nickname varchar(32) UNIQUE,
		age TINYINT UNSIGNED DEFAULT 18,
		gender ENUM('male','female') NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

查看表的结构：desc 表名称
查看建表的SQL语句：show create table 表名称
删除表：drop table 表名称。使用drop和delete删除表的区别是drop不仅仅删除表中的数据，表的定义结构都会删除；而delete仅仅删除表中的数据

3.CRUD

增加：

insert into t_user(nickname, age, gender) values('张三', 23, 'male');

删除：

delete from t_user where uid = 1;

修改：

update t_user set nickname = '李四' where uid = 2;

select查询

select nickname, age, gender from t_user where age between 20 and 23;

去重查询：使用distinct关键字

select distinct nickname from t_user;

空值查询：

select * from t_user where nickname is null;

union合并查询，前提两表的列数相等。

# union默认去重
select * from 表1 union select * from 表2;
# all表示显示所有重复值
select * from 表1 union all select * from 表2;

带in的子查询

select nickname from t_user where age in(23);

分页查询


# 从偏移0开始，查询两条
select * from t_user limit 0, 2;

分组查询，用于统计操作，通常结合sum、avg、max、min、count这些聚合函数使用。此外，group by会按照分组的列进行排序。通常按照分组的列也应该建立索引。

# 查询每门课程的平均成绩
select cid, avg(score) as '平均成绩' from exam group by cid;

排序查询

# 默认升序
select cid, avg(score) as score from exam group by cid order by score;

# 平均成绩降序
select cid, avg(score) as score from exam group by cid order by score desc;

4.多表连接查询

下列SQL语句使用的表结构如下所示：

# 学生表
create table student(
	uid int unsigned primary key auto_increment comment '学生id',
	name varchar(16) comment '学生姓名',
	age tinyint unsigned default 18 comment '学生年龄',
	sex enum('male','female') not null comment '学生性别'
);

insert into student(name, age, sex) values('张三',23, 'male'),
('李四',22,'male'),
('刘娟',20,'female'),
('王丽丽',26,'female');

# 课程表
create table course(
	cid int unsigned primary key auto_increment comment '课程id',
	cname varchar(32) unique not null comment '课程名称',
	cdesc varchar(128) default '' comment '课程描述',
	credit int comment '课程学分'
);
insert into course(cname, cdesc, credit) values('C++实战开发','这是一门注重实践的精品课程', 6),
('java程序设计','这是一门讲解Java应用开发的课程',6),
('数据结构','这是一门讲解数据结构与算法的重要课程', 12),
('编译原理', '这门课程注重理论，用处多多',12);

# 成绩表
create table exam(
	uid int unsigned not null comment '学生id',
	cid int unsigned not null comment '课程id',
	score double comment '课程成绩',
	primary key(uid, cid)
);

insert into exam(uid, cid, score) values(1, 1, 80),
(2, 1, 70),
(3, 1, 99),
(4, 1, 66),
(4, 2, 94),
(4, 3, 87),
(4, 4, 46),
(3, 3, 77),
(2, 2, 84),
(1, 3, 100),
(2, 3, 97);

内连接查询：本质查询两个表公有的部分，默认的多表连接查询就是内连接查询。

查询所有参加C++实战开发这门课程的考生信息以及考试成绩

# as关键字可以省略
# inner关键字也可以省略
select b.uid, b.name, b.age, b.sex, a.score from exam a inner join student b on a.uid = b.uid inner join coursee c on a.cid = c.cid where c.cname = 'C++实战开发';

查询1号考生参加数据结构考试的课程信息和考生信息，考试成绩信息。

select a.uid,a.name,a.age,a.sex,b.cid,b.cname,b.cdesc,b.credit,c.score from exam c inner join student a on c.uid = a.uid inner join course b on c.cid = b.cid where b.cname = '数据结构'  and c.uid = 1\G

查询参加数据结构考试，考生成绩在90以上的课程信息以及统计人数

select b.cid,b.cname,b.cdesc,b.credit,count(*) from exam c inner join course b on c.cid = b.cid where c.score>= 90 group by c.cid having c.cid = 3;

查询Java程序设计这门考试成绩最高分的学生信息和课程信息

# 1
select a.uid,a.name,a.age,a.sex,b.cid,b.cname,b.cdesc,b.credit,c.score from exam c inner join student a  on c.uid = a.uid inner join course b on c.cid = b.cid where c.cid = 2 order by c.score desc limit 1;
# 2
select a.uid,a.name,a.age,a.sex,b.cid,b.cname,b.cdesc,b.credit,max(c.score) from exam c inner join student a
on c.uid = a.uid inner join course b on c.cid = b.cid where c.cid = 2 group by c.uid,c.cid,c.score limit 1,1;

查询每门课程考试的平均成绩和课程信息

select b.cid,b.cname,b.cdesc,b.credit,avg(a.score) as '平均成绩' from exam a inner join course b on b.cid = a.cid group by a.cid order by b.cid;

外连接查询之左连接查询：查询不在右表的左表数据，在右表中不存在则显示null
1. 查询没有参加Java程序设计这门考试的所有学生信息
```
# 左连接查询的话左表全表搜索
select a.uid,a.name,a.age,a.sex,b.* from student a left outer join exam b on a.uid = b.uid and b.cid = 2 where b.cid is null;
```
外连接查询之右连接查询：查询不在左表的右表数据，在左表中不存在则显示null
1. 查询没有参加编译原理考试的所有学生信息
```
select b.uid,b.name,b.age,b.sex from exam a right join student b on a.uid = b.uid and a.cid = 4 where a.cid is null;
```

问题

MySQL中创建数据库时，指定COLLATE是什么意思？用于指定字符串排序或者比较的规则
MySQL创建数据库时，指定CHARSET是什么意思？用于设置字符串的编码集

posted on 2023-09-30 23:39 scrutiny-span 阅读(13) 评论(0) 编辑收藏举报

刷新页面返回顶部

导航