1.mysql编码问题

1. 查看MySQL数据库编码：SHOW VARTABLES LIKE ‘char%’;

2. 编码解释：

   • character_set_client：MySQL使用该编码来解读客户端发送过来的数据
     • 若该编码为UTF8，客户端发送过来的数据不是UTF8，那么就会出现乱码
   • character_set_results：MySQL会把数据转换成该编码后，再发送给客户端
     • 若该编码为UTF8，客户端不使用UTF8来解读，那么就会出现乱码

3. 控制台乱码问题

   • 插入或修改时出现乱码
     • 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

4. 指定默认编码

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

2.mysql备份与恢复数据库

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

最后

以上就是高兴花生为你收集整理的SQL进阶及查询练习1.mysql编码问题2.mysql备份与恢复数据库3.约束4.多表查询的全部内容，希望文章能够帮你解决SQL进阶及查询练习1.mysql编码问题2.mysql备份与恢复数据库3.约束4.多表查询所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。