SQL进阶及查询练习

最新推荐文章于 2024-07-31 15:36:36 发布

贾学习

最新推荐文章于 2024-07-31 15:36:36 发布

阅读量696

点赞数

分类专栏：数据库开发阿里云笔记文章标签：阿里云笔记数据库开发

本文链接：https://blog.csdn.net/SSR_J_Study/article/details/99218851

版权

阿里云笔记同时被 2 个专栏收录

8 篇文章 0 订阅

订阅专栏

数据库开发

3 篇文章 0 订阅

订阅专栏

SQL进阶及查询练习

1.mysql编码问题
2.mysql备份与恢复数据库
3.约束
4.多表查询

1.mysql编码问题

查看MySQL数据库编码：SHOW VARTABLES LIKE ‘char%’;
编码解释：
- character_set_client：MySQL使用该编码来解读客户端发送过来的数据
  - 若该编码为UTF8，客户端发送过来的数据不是UTF8，那么就会出现乱码
- character_set_results：MySQL会把数据转换成该编码后，再发送给客户端
  - 若该编码为UTF8，客户端不使用UTF8来解读，那么就会出现乱码
控制台乱码问题
- 插入或修改时出现乱码
  - cmd下默认使用GBK，而character_set_client不是GBK
  - 修改cmd的编码不方便
  - 设置character_set_client为GBK
- 查询出的数据为乱码
  - character_set_results不是GBK，而cmd默认使用GBK
  - 设置character_set_results为GBK
- 设置变量的语句：
  - set character_set_client=gbk;
  - set character_set_results=gbk;
  - 注意：设置变量只对当前连接有效，当退出窗口后，再次登录mysql，还需要再次设置变量，为了一劳永逸，可以在my.ini中设置default-character-set=gbk;
    - 它可以修改三个变量：client，results，connection
指定默认编码

我们在安装MySQL时已经指定了默认编码为UTF8，所以我们在创建数据库，创建表时，都无需再次指定编码。

2.mysql备份与恢复数据库

数据库导出SQL脚本
- mysqldump -u用户名 -p密码数据库名>生成的脚本文件路径
- 注意，不要打分号，不要登录mysql，直接在cmd下运行
- 注意，生成的脚本文件中不包含create database语句，即备份的是数据库内容，而不是备份数据库
执行SQL脚本
1. 第一种方式
  - mysql -u用户名 -p密码数据库<脚本文件路径
  - 注意，不要打分号，不要登录mysql，直接在cmd下运行
  - 注意，先创建数据库再运行该命令
2. 第二种方式
  - 登录mysql
  - source sql脚本路径

3.约束

3.1 主键约束（唯一标识）

特性：非空、唯一、被引用
创建表时制定主键有两种方式
- 列名列类型 PRIMARY KEY,……
- ……,PRIMARY KEY(列名)
修改表时指定主键：ALTER TABLE 表名 ADD PRIMARY KEY(列名);
删除主键：ALTER TABLE 表名 DROP PRIMARY KEY;

3.2 主键自增长

创建表时指定主键自增长：列名 INT PRIMARY KEY AUTO_INCREMENT,……
修改表时设置主键自增长：ALTER TABLE 表名 CHANGE 列名列名 INT AUTO_INCREMENT;
修改表时删除主键自增长：ALTER TABLE 表名 CHANGE 列名列名 INT;
注意，主键自增长列必须为int类型
主键自增长不适应于多服务器环境

3.3 非空约束

当某些列不能设置为NULL值时，可以对列添加非空约束
列名列类型 NOT NULL,……

3.4 唯一约束

当某些列不能设置重复的值，可以对列添加唯一约束
列名列类型 UNIQUE,……
非空和唯一约束可以一起使用

3.5 概念模型

当我们要完成一个软件系统时，需要把系统中的实体抽取出来，形成概念模型

实体之间存在三种关系：1对1，1对多，多对多

3.5.1 对象模型

可以双向关联，而且引用的是对象，而不是一个主键
在java中是domain
- is a
- has a（关联）
- use a

3.5.2 关系模型

只能多方引用一方，而且引用的只是主键，而不是一整行记录
在数据库中是表

3.6 外键约束

外键的特性：
1. 外键必须是（另）一个表中的主键的值
2. 外键可以重复
3. 外键可以为空
4. 一张表中可以有多个外键
创建表时添加外键约束：

CONSTRAINT fk_ 主表名 _ 从表名 FOREIGN KEY(主表外键列) REFERENCES 从表名(外键引用列)
修改表时添加外键约束：

ALTER TABLE 表名 ADD CONSTRAINT fk_ 主表名 _ 从表名 FOREIGN KEY(主表外键列) REFERENCES 从表名(外键引用列)

3.7 数据库一对一关系

在表中建立一对一关系，需要让其中一张表的主键，既是主键又是外键。

即：CONSTRAINT fk_ 主表名 _ 从表名 FOREIGN KEY(主表外键列) REFERENCES 从表名(从表主键列)

create table husband(
	hid int primary key,
    ……
);
create table wife(
	wid int primary key,
    ……
    add constraint fk_wife_wid foreign key(wid) references husband(hid)
);

3.8 数据库多对多关系

在表中建立多对多关系，需要使用中间表，即需要三张表。在中间表中使用两个外键，分别引用其他两个表的主键。

create table student(
	sid int primary key,
	……
);
create table teacher(
	tid int primary key,
	……
);
create table stu_tea(
	sid int,
	foreign key(sid) references student(sid),
    tid int,
    foreign key(tid) references teacher(tid)
);

4.多表查询

4.1 合并结果集

要求被合并的结果集中，列的类型和列数完全相同
方式
- UNION：去除重复行
- UNION ALL：不去除重复行

select * from ab
union all
select * from cd;
//最终显示结果集的列名按先查询的表

4.2 连接查询

4.2.1 内连接

方言：SELECT * FROM 表1 别名1,表2 别名2 WHERE 别名1.xx=别名2.xx;
- 若不加条件，查询结果行数为表1表2行数的笛卡尔积，有很多无用信息
- 起别名后，在整条select语句中，表都可以使用别名代替
- 只要是多表查询，在写列名称时都要先指明是哪张表的
标准：SELECT * FROM 表1 别名1 INNER JOIN 表2 别名2 ON 别名1.xx=别名2.xx;
自然：SELECT * FROM 表1 别名1 NATURAL JOIN 表2 别名2;
- natural join会自动将两张表的相同列名进行信息自动匹配
- 可读性较差

4.2.2 外连接

外连接有一主一次，主表中所有的记录无论满足不满足条件，都打印出来，当不满足条件时，用NULL来补位。左外即左表为主，右外即右表为主。
左外：SELECT * FROM 表1 别名1 LEFT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx;
- 左外自然：SELECT * FROM 表1 别名1 NATURAL LEFT OUTER JOIN 表2 别名2;
右外：SELECT * FROM 表1 别名1 RIGHT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx;
全外：SELECT * FROM 表1 别名1 FULL OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx;
- MySQL不支持全外
- 可以用左外右外查询结果集的UNION来模拟全外

注意，多个表进行外连接时，每个连接后都要加上对应的ON，即：

SELECT * 
FROM
 表1 别名1 LEFT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx
          LEFT OUTER JOIN 表3 别名3 ON 别名1.xx=别名3.xx
          ……
WHERE 条件;

4.3 子查询

在查询中有查询，即一句中有多个select
出现的位置：
- where后作为条件存在
- from后作为表存在（多行多列）
条件：
- 单行单列：SELECT * FROM 表1 别名1 WHERE 列1(>,<,>=,<=,!=,=) (SELECT 列 FROM 表2 别名2 WHERE 条件);
- 多行单列：SELECT * FROM 表1 别名1 WHERE 列1(IN,ANY,ALL) (SELECT 列 FROM 表2 别名2 WHERE 条件);
- 单行多列：SELECT * FROM 表1 别名1 WHERE (列1,列2) IN (SELECT 列 FROM 表2 别名2 WHERE 条件);
- 多行多列：SELECT * FROM 表1 别名1 ,(SELECT……) 别名2 WHERE 条件