SQL进阶及查询练习

  • 1.mysql编码问题
  • 2.mysql备份与恢复数据库
  • 3.约束
  • 3.1 主键约束（唯一标识）
  • 3.2 主键自增长
  • 3.3 非空约束
  • 3.4 唯一约束
  • 3.5 概念模型
  • 3.5.1 对象模型
  • 3.5.2 关系模型
  • 3.6 外键约束
  • 3.7 数据库一对一关系
  • 3.8 数据库多对多关系
  • 4.多表查询
  • 4.1 合并结果集
  • 4.2 连接查询
  • 4.2.1 内连接
  • 4.2.2 外连接
  • 4.3 子查询


1.mysql编码问题

  1. 查看MySQL数据库编码：SHOW VARTABLES LIKE ‘char%’;
  2. 编码解释：
  • character_set_client：MySQL使用该编码来解读客户端发送过来的数据
  • 若该编码为UTF8，客户端发送过来的数据不是UTF8，那么就会出现乱码
  • character_set_results：MySQL会把数据转换成该编码后，再发送给客户端
  • 若该编码为UTF8，客户端不使用UTF8来解读，那么就会出现乱码
  1. 控制台乱码问题
  • 插入或修改时出现乱码
  • 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
  1. 指定默认编码
    我们在安装MySQL时已经指定了默认编码为UTF8，所以我们在创建数据库，创建表时，都无需再次指定编码。

2.mysql备份与恢复数据库

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