MySQL基础

目录

1. 关系型数据库介绍

1.1 数据结构模型

1.2 RDBMS专业名词

1.3 关系型数据库的常见组件 

1.4 SQL语句 

2. mysql安装与配置 

2.1 mysql安装

2.2 源码编译安装

2.3 二进制包安装

3. mysql的程序组成

3.1 mysql工具使用

 3.2 服务器监听的两种socket地址

4.数据库操作

4.1.创建数据库

4.2 显示数据库

4.3.使用数据库

 4.4 修改数据库

 4.5 删除数据库

5.字符集

5.1 字符集概念

5.2 MySQL字符集

5.3 乱码问题解决

5.4 字符集设置原理

6.校对集

6.1 校对集概念

6.2 校对集设置

6.3 校对集应用

7.数据表操作

7.1 创建数据表

---

1. 关系型数据库介绍

1.1 数据结构模型

数据结构模型主要有：

• 层次模型
• 网状结构
• 关系模型

关系模型：
二维关系：row，column

数据库管理系统：DBMS
关系：Relational，RDBMS

1.2 RDBMS专业名词

常见的关系型数据库管理系统：

• MySQL：MySQL，MariaDB，Percona-Server
• PostgreSQL：简称为pgsql
• Oracle
• MSSQL

SQL：Structure Query Language，结构化查询语言

约束：constraint，向数据表提供的数据要遵守的限制

• 主键约束：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行。且必须提供数据，不能为空（NOT NULL）。
  • 一个表只能存在一个
• 惟一键约束：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行。允许为空（NULL）
  • 一个表可以存在多个
• 外键约束：一个表中的某字段可填入数据取决于另一个表的主键已有的数据
• 检查性约束

索引：将表中的一个或多个字段中的数据复制一份另存，并且这些数据需要按特定次序排序存储

1.3 关系型数据库的常见组件 

关系型数据库的常见组件有：

• 数据库：database
• 表：table，由行（row）和列（column）组成
• 索引：index
• 视图：view
• 用户：user
• 权限：privilege
• 存储过程：procedure
• 存储函数：function
• 触发器：trigger
• 事件调度器：event scheduler

1.4 SQL语句 

SQL语句有三种类型：

• DDL：Data Defination Language，数据定义语言
• DML：Data Manipulation Language，数据操纵语言
• DCL：Data Control Language，数据控制语言

SQL语句类型	对应操作
DDL	CREATE：创建 DROP：删除 ALTER：修改
DML	INSERT：向表中插入数据 DELETE：删除表中数据 UPDATE：更新表中数据 SELECT：查询表中数据
DCL	GRANT：授权 REVOKE：移除授权

2. mysql安装与配置 

2.1 mysql安装

mysql安装方式有三种：

• 源代码：编译安装
• 二进制格式的程序包：展开至特定路径，并经过简单配置后即可使用
• 程序包管理器管理的程序包：
  • rpm：有两种
    • OS Vendor：操作系统发行商提供的
    • 项目官方提供的
  • deb

2.2 源码编译安装

1.安装依赖包
[root@zmq ~]# yum -y install ncurses-devel openssl-devel openssl cmake mariadb-devel

2.创建用户和组
[root@zmq ~]# cd /usr/src
[root@zmq src]# groupadd -r 306 mysql
[root@zmq src]# useradd -r -M -s /sbin/nologin -g mysql mysql

3.下载二进制格式的mysql软件包
[root@zmq ~]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.42-el7-x86_64.tar.gz

4.解压缩软件包到/usr/local/目录下
tar xf mysql-5.7.42-el7-x86_64.tar.gz -C /usr/local/

5.设置软链接
[root@zmq local]# ln -sv mysql-5.7.42-el7-x86_64/ mysql
‘mysql’ -> ‘mysql-5.7.42-el7-x86_64/’

6.修改目录/usr/local/mysql的属主属组
[root@zmq ~]# chown -R mysql.mysql /usr/local/mysql

7.//添加环境变量
[root@zmq ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/bashrc
[root@zmq ~]# . /etc/profile.d/mysql.sh
[root@zmq ~]# echo $PATH
/usr/local/mysql/bin:/usr/local/apache/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

8.//建立数据存放目录
[root@zmq mysql]# mkdir /opt/data
[root@zmq mysql]# chown -R mysql.mysql /opt/data/

9.//初始化数据库
[root@zmq ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/
2018-08-15T07:57:46.168380Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-08-15T07:57:50.542516Z 0 [Warning] InnoDB: New log files created, LSN=45790
2018-08-15T07:57:50.927286Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2018-08-15T07:57:51.071260Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: e8600890-a060-11e8-b1a2-000c294c50b4.
2018-08-15T07:57:51.074566Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2018-08-15T07:57:51.078089Z 1 [Note] A temporary password is generatedfor root@localhost: eispf;wioes

10.配置mysql
[root@zmq ~]# ln -sv /usr/local/mysql/include/ /usr/local/include/mysql
‘/usr/local/include/mysql’ -> ‘/usr/local/mysql/include/’
[root@zmq ~]# echo '/usr/local/mysql/lib' > /etc/ld.so.conf.d/mysql.conf
[root@zmq ~]# ldconfig   

11.//生成配置文件
[root@zmq ~]# cat > /etc/my.cnf <<EOF
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
EOF

12.配置服务启动脚本
[root@zmq ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@zmq ~]# sed -ri 's#^(basedir=).*#\1/usr/local/mysql#g' /etc/init.d/mysqld
[root@zmq ~]# sed -ri 's#^(datadir=).*#\1/opt/data#g' /etc/init.d/mysqld

13.启动mysql
[root@zmq ~]# service mysqld start
Starting MySQL.. SUCCESS! 

14.修改密码
使用临时密码登录
[root@zmq ~]# mysql -uroot -p

[root@zmq mysql]# mysql -uroot -p
mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory
# 设置软链接
[root@zmq lib64]# ln -s /usr/lib64/libncurses.so.6.1 /usr/lib64/libncurses.so.5


[root@zmq mysql]# mysql -uroot -p
mysql: error while loading shared libraries: libtinfo.so.5: cannot open shared object file: No such file or directory
# 设置软链接
[root@zmq lib64]# ln -s /usr/lib64/libtinfo.so.6.1 /usr/lib64/libtinfo.so.5

[root@zmq mysql]# mysql -uroot -p
Enter password: # 上面生成的临时密码直接复制过来
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.22

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

//设置新密码
mysql> set password = password('2664218545Z');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> quit
Bye

2.3 二进制包安装

1.下载二进制软件包
[root@zmq ~]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.42-el7-x86_64.tar.gz

2.创建用户和组
[root@zmq ~]# cd /usr/src
[root@zmq src]# groupadd -r mysql
[root@zmq src]# useradd -r -M -s /sbin/nologin -g mysql mysql

3.解压软件至/usr/local/
[root@zmq ~]# tar xf https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.42-el7-x86_64.tar.gz -C /opt/src
[root@zmq ~]# ls /usr/local/
mysql-5.7.42-el7-x86_64

4.创建软链接
[root@zmq ~]# ln -sv mysql-5.7.42-el7-x86_64 mysql
‘mysql’ -> ‘mysql-5.7.42-el7-x86_64/’

5.修改目录/usr/local/mysql的属主属组
[root@zmq ~]# chown -R mysql.mysql /usr/local/mysql

6.//添加环境变量
[root@zmq ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/bashrc
[root@zmq ~]# . /etc/profile.d/mysql.sh
[root@zmq ~]# echo $PATH
/usr/local/mysql/bin:/usr/local/apache/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

7.//建立数据存放目录
[root@zmq mysql]# mkdir /opt/data
[root@zmq mysql]# chown -R mysql.mysql /opt/data/

8.//初始化数据库
[root@zmq ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/
2018-08-15T07:57:46.168380Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-08-15T07:57:50.542516Z 0 [Warning] InnoDB: New log files created, LSN=45790
2018-08-15T07:57:50.927286Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2018-08-15T07:57:51.071260Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: e8600890-a060-11e8-b1a2-000c294c50b4.
2018-08-15T07:57:51.074566Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2018-08-15T07:57:51.078089Z 1 [Note] A temporary password is generatedfor root@localhost: eispf;wioes

9.生成配置文件
[root@zmq ~]# cat > /etc/my.cnf <<EOF
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
EOF

10.配置服务启动脚本
[root@zmq ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@zmq ~]# sed -ri 's#^(basedir=).*#\1/usr/local/mysql#g' /etc/init.d/mysqld
[root@zmq ~]# sed -ri 's#^(datadir=).*#\1/opt/data#g' /etc/init.d/mysqld

11.启动服务
[root@zmq ~]# service mysqld start
Starting MySQL.. SUCCESS! 
[root@zmq ~]# ss -antl
State       Recv-Q Send-Q Local Address:Port               Peer Address:Port
LISTEN      0      128         *:22                      *:*
LISTEN      0      100    127.0.0.1:25                      *:*
LISTEN      0      80         :::3306                   :::* 


12.登录
[root@zmq mysql]# mysql -uroot -p
Enter password: # 上面生成的临时密码直接复制过来
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.22

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

//设置新密码
mysql> set password = password('2664218545Z');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> quit
Bye

3. mysql的程序组成

• 客户端
  • mysql：CLI交互式客户端程序
  • mysql_secure_installation：安全初始化，强烈建议安装完以后执行此命令
  • mysqldump：mysql备份工具
  • mysqladmin
• 服务器端
  • mysqld

3.1 mysql工具使用

语法：mysql [OPTIONS] [database]
常用的OPTIONS：
    -uUSERNAME      //指定用户名，默认为root
    -hHOST          //指定服务器主机，默认为localhost，推荐使用ip地址
    -pPASSWORD      //指定用户的密码
    -P#             //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307
    -V              //查看当前使用的mysql版本
    -e              //不登录mysql执行sql语句后退出，常用于脚本

 3.2 服务器监听的两种socket地址

socket类型	说明
ip socket	默认监听在tcp的3306端口，支持远程通信
unix sock	监听在sock文件上（/tmp/mysql.sock，/var/lib/mysql/mysql.sock） 仅支持本地通信 server地址只能是：localhost，127.0.0.1

4.数据库操作

4.1.创建数据库

创建数据库：根据项目需求创建一个存储数据的仓库

• 使用create database 数据库名字创建

  • 数据库层面可以指定字符集：charset / character set

  • 数据库层面可以指定校对集：collate

• 创建数据库会在磁盘指定存放处产生一个文件夹

• 创建语法

create database 数据库名字 [数据库选项];

# 示例:
1.创建一个指定名字的数据库
mysql> create database db_1;

2.创建一个指定字符集的数据库
mysql> create database db_2 charset utf8MB4;
//8.0以前叫utf8

3.创建一个指定校对集的数据库
create database db_3 charset utf8MB4 collate utf8mb4_general_ci;

4.2 显示数据库

显示数据库：通过客户端指令来查看已有数据库

• 数据库的查看是根据用户权限限定的

• 数据库的查看分为两种查看方式：

  • 查看全部数据库

  • 查看数据库创建指令

# 示例
1.显示所有数据库
show databases;

2.显示数据库创建指令
show create database db_1;

4.3.使用数据库

使用数据库：指在进行具体SQL指令之前，让系统知道操作针对的是哪个数据库

• 数据库的操作通常是针对数据表或者数据

• 通过使用数据库可以让后续指令默认针对具体数据库环境

• 使用数据库语法：use 数据库名字;

# 示例
use db_1;

 4.4 修改数据库

修改数据库：修改数据库的相关库选项

• 数据库名字不可修改（老版本可以,5.0以前）

  • 先新增

  • 后迁移

  • 最后删除

• 数据库修改分为两个部分（库选项）

  • 字符集

  • 校对集

• 数据库修改指令（与创建指令差不多）

# 示例
1.修改数据库字符集
alter database db_2 charset gbk;

2.修改数据库校对集（如果校队集修改必须同时改变字符集）

alter database db_3 charset gbk collate gbk_chinese_ci;

 4.5 删除数据库

删除数据库：将当前已有数据库删除

• 删除数据库会删除数据库内所有的表和数据

• 删除数据库操作要慎重（删前备份）

• 删除数据库后，对应的存储文件夹就会消失

• 删除语法

# 示例
drop database 数据库名字;

5.字符集

5.1 字符集概念

字符集：charset或者character set，是各种文字和符号在计算机中的总称

• 字符集根据不同国家的符号不同，有不同的字符集

• 不同的字符集占用的存储空间不一样，存储的底层也不一样

• 不同字符集之间可以进行转换

• 常见字符集

  • ASCII：美国信息交换标准码，一般英文符号，一个字节存储

  • latin1：拉丁文字符集，一个字节存储，ISO-8859-1的别名 ，能够向下兼容ASCII

  • GB2312：信息交换用汉字编码字符集 ，是中国1981年的一套国标规范，2个字节存储

  • GBK：汉字内码扩展规范（1995年），两个字节表示（汉字很多超过5000个）

  • Unicode：万国码（统一码），使用统一的编码方式来解决传统的局限，1994年出现

  • UTF-8：8-bit Unicode Transformation Format（万国码） ，针对Unicode的可变长度字符编码，采用1-6个字节编码Unicode字符（目前通用编码规则）。建议使用UTF-8字符集进行数据存储（MySQL8中建议使用utf8mb4）

5.2 MySQL字符集

MySQL字符集：MySQL内部对于数据实际存储的字符集（服务器端）

• MySQL内部对象可以在各个层级设置字符集

• MySQL内部对象存在字符集继承：字段 -> 表 -> 数据库 -> DBMS

• MySQL内部内嵌支持几乎所有主流字符集

• 数据存储的最终字符集由字段控制

• 客户端与服务器进行交互时，需要明确告知服务器客户端自己的字符集（数据格式）

# 示例
查看MySQL支持的所有字符集
show charset;

5.3 乱码问题解决

乱码：指数据不能按照正确的字符集进行存储或者解析

• 乱码原因1：数据在存储的时候已经变成乱码

  • 客户端字符集与服务端解析字符集不一致

  • 读取时想转成其他字符集均会错误

• 乱码原因2：数据存储时正确，但是读取时解析成错误字符集

  • 客户端能解析的字符集与服务器提供的字符集不一致

• 乱码解决方案：不论存储还是读取，都提前告知服务器当前客户端的字符集

# 示例
set names 客户端字符集;

1、MySQL客户端（CMD打开），客户端字符集是固定的GBK
set names gbk;

5.4 字符集设置原理

字符集设置原理：服务器端正确保障对客户端的数据识别

• MySQL服务端提供了变量来记录客户端的字符集

• MySQL对应的存储字符集的变量可以修改

• set names 字符集就是对变量的修改，总共有三个

  • character_set_client：客户端提供的数据的字符集

  • character_set_results：客户端需要服务端提供的数据的字符集

  • character_set_connection：连接使用的字符集，内部数据操作

# 示例
1.查看系统内部存储这些记录字符集的信息
show variables like 'character_set%';	#%表示通配符，匹配后续所有不确定的数据

2.修改客户端字符集变量，保证数据正常存进服务端
set character_set_client = gbk;

3.修改客户端解析字符集变量，保证数据正常被客户端查看
set character_set_results = gbk;

4.使用set names 字符集批量修改，保证客户端被服务端正确理解，同时客户端也能正确解析
set names gbk;

6.校对集

6.1 校对集概念

校对集：collate/collation，即数据比较时对应的规则

• 校对集依赖字符集

• 校对集的校对方式分为三种

  • 大小写不敏感：_ci，case insensitive（不区分大小写）

  • 大小写敏感：_cs，case sensitive（区分大小写）

  • 二进制比较：_bin，binary（区分大小写）

• 校对集是在进行数据比较的时候触发

# 示例
1._ci，大小写不敏感
A 与 a 是相同的，不存在谁大谁小（系统会转换成一种）

2._cs，大小写敏感
A 与 a 有大小关系，所以不同（存储数值）

3._bin，二进制比较
A的二进制是01000001
a的二进制是01100001
二进制按位比较，所以不同

6.2 校对集设置

校对集设置：在创建数据表的时候创建校对规则

• 校对规则可以在MySQL四层对象设计

  • DBMS：系统配置

  • DB：数据库指定（库选项）

  • Table：表指定（表选项）

  • Field：字段指定（字段选项，一般不用）

• 校对集从Field到DBMS继承；优先级Field最高

• 每个校对集都有字符集对应的默认规则

• 校对集设置语法

# 示例
collate 校对集规则;

1.查看MySQL支持的所有校对集
show collation;

2.在数据库层设计校对集（常见）
create database db_4 charset utf8mb4 collate utf8mb4_bin;

3.在数据表层设计校对集
create table t_4(
	id int,
    name varchar(10)
)charset utf8mb4 collate utf8mb4_bin;

4.在字段层设计校对集（一般不常用）
create table t_5(
	id int,
    name varchar(10) collate utf8mb4_bin
)charset utf8mb4;

6.3 校对集应用

校对集应用：触发校对规则的使用

• 校对集的应用通常是通过数据比较触发：order by 字段

• 数据表中数据一旦产生，校对集的修改就无效

# 示例
1.创建校对规则数据表并插入数据
# 创建默认校对规则表（不区分大小写）
create table t_4(
	name varchar(1)
)charset utf8mb4;
insert into t_4 values('B');
insert into t_4 values('A');
insert into t_4 values('b');
insert into t_4 values('a');

# 创建二进制校对规则（区分大小写）
create table t_5(
	name varchar(1)
)charset utf8mb4 collate utf8mb4_bin;
insert into t_5 values('B');
insert into t_5 values('A');
insert into t_5 values('b');
insert into t_5 values('a');

2.触发校对：排序 order by
select * from t_4 order by name; # 升序
select * from t_5 order by name;

3.数据已经存在的表重新修改校对规则无效
alter table t_5 collate utf8mb4_general_ci;

7.数据表操作

7.1 创建数据表

创建数据表：根据业务需求，确定数据表的字段信息，然后创建表结构

• 表与字段不分家，相辅相成

• 表的创建需要指定存储的数据库

  • 明确指定数据库：数据库.表名

  • 先使用数据库：use 数据库名字

• 字段至少需要指定名字、类型

• 数据库表不限定字段数量

  • 每个字段间使用逗号,分隔

  • 最后一个字段不需要逗号

• 表可以指定表选项（都有默认值）

  • 存储引擎：engine [=] 具体存储引擎

  • 字符集：[default] charset 具体字符集（继承数据库）

  • 校对集：collate（继承数据库）

• 表创建语法

• create table [数据库名.]表名(

•     字段名 字段类型,
      ...
      字段名 字段类型
  )[表选项];

# 示例
1.创建简单数据表（指定数据库创建数据表）
create table db_2.t_1(
	name varchar(50)
);

2.创建数据表——多字段
# 使用数据库（进入数据库环境）
use db_2;
create table t_2(
    name varchar(50),
    age int,
    gender varchar(10)
);

3.创建数据表——表选项
create table t_3(
	name varchar(50)
)engine Innodb charset utf8MB4;

7.2 数据新增

1.批量插入：是一种优化数据逐条插入的方式

• 批量插入数据的语法与简单数据插入的语法差不多

• 批量插入分为两种 ：全字段批量插入 和 部分字段批量插入（注意字段默认值）

# 示例
1、用户的操作涉及到多记录同时插入（通常数据批量导入）

2、组装成批量插入SQL指令

* 字段为全字段（逻辑主键不存在没问题）：全字段批量插入SQL
* 部分字段：组织字段列表并检查其他字段是否允许默认值

3、执行批量插入

姓名和学科名共同组成唯一
create table t_30(
	id int primary key auto_increment,
    stu_name varchar(20) not null,
    course varchar(20) not null,
    score decimal(5,2),
    unique key(stu_name,course)
)charset utf8;

1.批量插入学生成绩（t_30全字段）
insert into t_30 values(null,'Tom','Computer',90),
(null,'Lily','Computer',92);

2、批量插入学生考试信息（t_30不包含成绩）
insert into t_30 (stu_name,course) values('Tony','English'),('Ray','Math');

2.蠕虫复制：从已有表中复制数据直接插入到另外一张表（同一张表）

• 蠕虫复制的目标是快速增加表中的数据

  • 实现表中数据复制（用于数据备份或者迁移）

  • 实现数据的指数级递增（多用于测试）

• 蠕虫复制语法

• insert into 表名 [(字段列表)] select 字段列表 from 表名;

• 注意事项

  • 字段列表必须对应上

  • 字段类型必须匹配上

  • 数据冲突需要事先考虑

# 步骤

1、确定一张需要插入数据的表（一般与要蠕虫复制数据的表结构一致）

2、确定数据的来源表

* 字段数量匹配
* 字段类型匹配
* 所选字段不存在冲突（数据可能是重复数据）

3、使用蠕虫复制

# 示例
1、创建一张新表，将t_30表中的数据迁移到新表中
create table t_35(
	id int primary key auto_increment,
    stu_name varchar(20) not null,
    course varchar(20) not null,
    score decimal(5,2)
)charset utf8;

insert into t_35 select * from t_30;

2、快速让t_35表中的数据达到超过100条（重复执行）
insert into t_35 (stu_name,course,score) select stu_name,course,score from t_35;

3.主键冲突：在数据进行插入时包含主键指定，而主键在数据表已经存在

• 主键冲突的业务通常是发生在业务主键上（业务主键本身有业务意义）

• 主键冲突的解决方案

  • 忽略冲突：保留原始记录

  insert ignore into 表名 [(字段列表)] values(值列表);

  • 冲突更新：冲突后部分字段变成更新

  insert into 表名 [(字段列表)] values(值列表) on duplicate key update 字段 = 新值[,字段=新值...];
  # 1、尝试新增
  # 2、更新

  • 冲突替换：先删除原有记录，后新增记录

  replace into 表名 [(字段列表)] values(值列表); # 效率没有insert高（需要检查是否冲突）

# 步骤
1、确定当前业务可能存在主键冲突

2、选择主键冲突的解决方案

# 示例
1、用户名作为主键的用户注册（冲突不能覆盖）：username，password，regtime
create table t_36(
	username varchar(50) primary key,
    password char(32) not null,
    regtime int unsigned not null
)charset utf8;

insert into t_36 values('username','password',12345678);
# 冲突忽略
insert ignore into t_36 values('username','12345678',12345678);

2、用户名作为主键的记录用户使用信息（不存在新增、存在则更新时间）：username，logintime
create table t_37(
	username varchar(50) primary key,
    logintime int unsigned
)charset utf8;

insert into t_37 values('username',12345678);	# 当前时间戳

# 冲突更新（替换部分字段数据）
insert into t_37 values('username',12345678) on duplicate key update logintime = unix_timestamp();	# 当前时间戳

3、用户名作为主键的记录用户使用信息（不存在新增、存在则更新全部）：username，logintime、clientinfo

create table t_38(
	username varchar(50) primary key,
    logintime int unsigned,
    clientinfo varchar(255) not null
)charset utf8;

insert into t_38 values('username',unix_timestamp(),'{PC:chrome}');

# 替换插入
replace into t_38 values('username',unix_timestamp(),'{phone:uc}');

7.3 数据查询

1.创建一个表，结构，插入数据如下

# 库中有一张学生信息表，以及一张爱好表，表中数据如下
mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(64) | NO   |     | NULL    |                |
| age   | int(11)     | YES  |     | NULL    |                |
| sex   | varchar(10) | YES  |     | NULL    |                |
| hobby | int(11)     | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> desc hobby_name;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| h_name | varchar(20) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select * from student;
+----+-----------+------+--------+-------+
| id | name      | age  | sex    | hobby |
+----+-----------+------+--------+-------+
|  1 | 科比      |   18 | 中性   |     1 |
|  2 | 姚明      |   25 | 女     |     2 |
|  3 | 梅西      |   20 | 男     |     3 |
|  4 | 回忆      |   21 | 男     |     3 |
|  5 | 程福兴    |   22 | 男     |     3 |
|  6 | 刘橙      |   22 | 中性   |     1 |
|  7 | 刘文浩    |   18 | 男     |     1 |
|  8 | 李兵达    |   20 | 男     |     2 |
|  9 | 林振龙    |   19 | 女     |     3 |
+----+-----------+------+--------+-------+
9 rows in set (0.00 sec)

mysql> select * from hobby_name;
+------+--------+
| id   | h_name |
+------+--------+
|    1 | 足球   |
|    2 | 乒乓球 |
|    3 | 篮球   |
|    4 | 羽毛球 |
+------+--------+
4 rows in set (0.00 sec)

 2.数据查询的高级使用方法

2.1 group by子句
# group by子句：分组统计，根据某个字段将所有的结果分类，并进行数据统计分析
  1.分组的目的不是为了显示数据，一定是为了统计数据
  2.group by子句一定是出现在where子句之后（如果同时存在）
  3.分组统计可以进行统计细分：先分大组，然后大组分小组
  4.分组统计需要使用统计函数
     group_concat(字段名)：将组里的某个字段全部保留
     any_value(字段名)：不属于分组字段的任意一个组里的值
     count()：求对应分组的记录数量
        count(字段名)：统计某个字段值的数量（NULL不统计）
        count(*)：统计整个记录的数量（较多）
     sum()：求对应分组中某个字段是和
     max()/min()：求对应分组中某个字段的最大/最小值
     avg()：求对应分组中某个字段的平均值
1.统计各爱好的人数
mysql> select hobby,count(*) from student group by hobby;
+-------+----------+
| hobby | count(*) |
+-------+----------+
|     1 |        3 |
|     2 |        2 |
|     3 |        4 |
+-------+----------+
3 rows in set (0.00 sec)


2.多分组，统计各个爱好的性别类型人数
mysql> select sex,count(*) from student group by sex;
+--------+----------+
| sex    | count(*) |
+--------+----------+
| 中性   |        2 |
| 女     |        2 |
| 男     |        5 |
+--------+----------+
3 rows in set (0.00 sec)

3.统计每个爱好不同性别的人数
mysql> select hobby,sex,group_concat(name) from student group by hobby,sex;
+-------+--------+-------------------------+
| hobby | sex    | group_concat(name)      |
+-------+--------+-------------------------+
|     1 | 中性   | 科比,刘橙               |
|     1 | 男     | 刘文浩                  |
|     2 | 女     | 姚明                    |
|     2 | 男     | 李兵达                  |
|     3 | 女     | 林振龙                  |
|     3 | 男     | 梅西,回忆,程福兴        |
+-------+--------+-------------------------+
6 rows in set (0.00 sec)

2.2 order by 语句

# order by子句：排序，根据某个指定的字段进行升序或者降序排序
  1.排序的参照物是校对集
  2.order by子句在having子句字后（如果同时存在）
  3.排序分为升序和降序：默认是升序
     order by 字段 [ASC]：升序
     order by 字段 DESC：降序
  4.多字段排序：在根据某个字段排序好后，可以再细分排序

1.排序，按照年龄升序排序
mysql> select * from student order by age;
+----+-----------+------+--------+-------+
| id | name      | age  | sex    | hobby |
+----+-----------+------+--------+-------+
|  1 | 科比      |   18 | 中性   |     1 |
|  7 | 刘文浩    |   18 | 男     |     1 |
|  9 | 林振龙    |   19 | 女     |     3 |
|  3 | 梅西      |   20 | 男     |     3 |
|  8 | 李兵达    |   20 | 男     |     2 |
|  4 | 回忆      |   21 | 男     |     3 |
|  5 | 程福兴    |   22 | 男     |     3 |
|  6 | 刘橙      |   22 | 中性   |     1 |
|  2 | 姚明      |   25 | 女     |     2 |
+----+-----------+------+--------+-------+
9 rows in set (0.00 sec)

2.排序，按照年龄降序降序排列
mysql> select * from student order by age desc;
+----+-----------+------+--------+-------+
| id | name      | age  | sex    | hobby |
+----+-----------+------+--------+-------+
|  2 | 姚明      |   25 | 女     |     2 |
|  5 | 程福兴    |   22 | 男     |     3 |
|  6 | 刘橙      |   22 | 中性   |     1 |
|  4 | 回忆      |   21 | 男     |     3 |
|  3 | 梅西      |   20 | 男     |     3 |
|  8 | 李兵达    |   20 | 男     |     2 |
|  9 | 林振龙    |   19 | 女     |     3 |
|  1 | 科比      |   18 | 中性   |     1 |
|  7 | 刘文浩    |   18 | 男     |     1 |
+----+-----------+------+--------+-------+
9 rows in set (0.00 sec)

 2.3 limit子句
# limit子句：限制数据的获取数量(记录数)
  1.limit子句必须在order by子句之后（如果同时存在）
  2.limit限制数量的方式有两种
    limit 数量：限制获取的数量（不保证一定能获取到指定数量）
    limit 起始位置,数量：限制数据获取的位置以及数量（分页）
    
1.获取student表中前3条数据
mysql> select * from student limit 3;
+----+--------+------+--------+-------+
| id | name   | age  | sex    | hobby |
+----+--------+------+--------+-------+
|  1 | 科比   |   18 | 中性   |     1 |
|  2 | 姚明   |   25 | 女     |     2 |
|  3 | 梅西   |   20 | 男     |     3 |
+----+--------+------+--------+-------+
3 rows in set (0.00 sec)

2.获取student表中跳过前3条之后的2条数据
mysql> select * from student limit 3,2;
+----+-----------+------+------+-------+
| id | name      | age  | sex  | hobby |
+----+-----------+------+------+-------+
|  4 | 回忆      |   21 | 男   |     3 |
|  5 | 程福兴    |   22 | 男   |     3 |
+----+-----------+------+------+-------+
2 rows in set (0.00 sec)

 2.4 交叉连接查询
交叉连接：cross join，不需要连接条件的连接
 1.交叉连接产生的结果就是笛卡尔积
   左表的每一条记录都会与右表的所有记录连接并保留
 2.交叉连接没有实际数据价值，只是丰富了连接查询的完整性
mysql> select * from student cross join hobby_name;
+----+-----------+------+--------+-------+------+-----------+
| id | name      | age  | sex    | hobby | id   | h_name    |
+----+-----------+------+--------+-------+------+-----------+
|  1 | 科比      |   18 | 中性   |     1 |    1 | 足球      |
|  1 | 科比      |   18 | 中性   |     1 |    2 | 乒乓球    |
|  1 | 科比      |   18 | 中性   |     1 |    3 | 篮球      |
|  1 | 科比      |   18 | 中性   |     1 |    4 | 羽毛球    |
|  2 | 姚明      |   25 | 女     |     2 |    1 | 足球      |
|  2 | 姚明      |   25 | 女     |     2 |    2 | 乒乓球    |
|  2 | 姚明      |   25 | 女     |     2 |    3 | 篮球      |
|  2 | 姚明      |   25 | 女     |     2 |    4 | 羽毛球    |
|  3 | 梅西      |   20 | 男     |     3 |    1 | 足球      |
|  3 | 梅西      |   20 | 男     |     3 |    2 | 乒乓球    |
|  3 | 梅西      |   20 | 男     |     3 |    3 | 篮球      |
|  3 | 梅西      |   20 | 男     |     3 |    4 | 羽毛球    |
|  4 | 回忆      |   21 | 男     |     3 |    1 | 足球      |
|  4 | 回忆      |   21 | 男     |     3 |    2 | 乒乓球    |
|  4 | 回忆      |   21 | 男     |     3 |    3 | 篮球      |
|  4 | 回忆      |   21 | 男     |     3 |    4 | 羽毛球    |
|  5 | 程福兴    |   22 | 男     |     3 |    1 | 足球      |
|  5 | 程福兴    |   22 | 男     |     3 |    2 | 乒乓球    |
|  5 | 程福兴    |   22 | 男     |     3 |    3 | 篮球      |
|  5 | 程福兴    |   22 | 男     |     3 |    4 | 羽毛球    |
|  6 | 刘橙      |   22 | 中性   |     1 |    1 | 足球      |
|  6 | 刘橙      |   22 | 中性   |     1 |    2 | 乒乓球    |
|  6 | 刘橙      |   22 | 中性   |     1 |    3 | 篮球      |
|  6 | 刘橙      |   22 | 中性   |     1 |    4 | 羽毛球    |
|  7 | 刘文浩    |   18 | 男     |     1 |    1 | 足球      |
|  7 | 刘文浩    |   18 | 男     |     1 |    2 | 乒乓球    |
|  7 | 刘文浩    |   18 | 男     |     1 |    3 | 篮球      |
|  7 | 刘文浩    |   18 | 男     |     1 |    4 | 羽毛球    |
|  8 | 李兵达    |   20 | 男     |     2 |    1 | 足球      |
|  8 | 李兵达    |   20 | 男     |     2 |    2 | 乒乓球    |
|  8 | 李兵达    |   20 | 男     |     2 |    3 | 篮球      |
|  8 | 李兵达    |   20 | 男     |     2 |    4 | 羽毛球    |
|  9 | 林振龙    |   19 | 女     |     3 |    1 | 足球      |
|  9 | 林振龙    |   19 | 女     |     3 |    2 | 乒乓球    |
|  9 | 林振龙    |   19 | 女     |     3 |    3 | 篮球      |
|  9 | 林振龙    |   19 | 女     |     3 |    4 | 羽毛球    |
+----+-----------+------+--------+-------+------+-----------+
36 rows in set (0.00 sec)
 
2.5 内连接查询

内连接：[inner] join，将两张表根据指定的条件连接起来，严格连接
  1.内连接是将一张表的每一条记录去另外一张表根据条件匹配
      匹配成功：保留连接的数据
      匹配失败：都不保留
  2.内连接语法： `左表 join 右表 on 连接条件`
  
# inner可以省略  
mysql> select * from student inner join hobby_name on student.hobby = hobby_name.id;
+----+-----------+------+--------+-------+------+-----------+
| id | name      | age  | sex    | hobby | id   | h_name    |
+----+-----------+------+--------+-------+------+-----------+
|  1 | 科比      |   18 | 中性   |     1 |    1 | 足球      |
|  2 | 姚明      |   25 | 女     |     2 |    2 | 乒乓球    |
|  3 | 梅西      |   20 | 男     |     3 |    3 | 篮球      |
|  4 | 回忆      |   21 | 男     |     3 |    3 | 篮球      |
|  5 | 程福兴    |   22 | 男     |     3 |    3 | 篮球      |
|  6 | 刘橙      |   22 | 中性   |     1 |    1 | 足球      |
|  7 | 刘文浩    |   18 | 男     |     1 |    1 | 足球      |
|  8 | 李兵达    |   20 | 男     |     2 |    2 | 乒乓球    |
|  9 | 林振龙    |   19 | 女     |     3 |    3 | 篮球      |
+----+-----------+------+--------+-------+------+-----------+
9 rows in set (0.00 sec)


2.6 外连接查询

外连接：outer join，是一种不严格的连接方式
  1.外连接分为两种
    左外连接（左连接）：left join
    右外连接（右连接）：right join
  2.外连接有主表和从表之分
    左连接：左表为主表
    右连接：右表为主表
  3.外连接是将主表的记录去匹配从表的记录
    匹配成功保留
    匹配失败（全表）：也保留，只是从表字段置空

1.左外连接，查询student表所有信息以及hobby表对应id的内容
mysql> select student.*,hobby_name.h_name from student left join hobby_name on student.hobby = hobby_name.id;
+----+-----------+------+--------+-------+-----------+
| id | name      | age  | sex    | hobby | h_name    |
+----+-----------+------+--------+-------+-----------+
|  1 | 科比      |   18 | 中性   |     1 | 足球      |
|  6 | 刘橙      |   22 | 中性   |     1 | 足球      |
|  7 | 刘文浩    |   18 | 男     |     1 | 足球      |
|  2 | 姚明      |   25 | 女     |     2 | 乒乓球    |
|  8 | 李兵达    |   20 | 男     |     2 | 乒乓球    |
|  3 | 梅西      |   20 | 男     |     3 | 篮球      |
|  4 | 回忆      |   21 | 男     |     3 | 篮球      |
|  5 | 程福兴    |   22 | 男     |     3 | 篮球      |
|  9 | 林振龙    |   19 | 女     |     3 | 篮球      |
+----+-----------+------+--------+-------+-----------+
9 rows in set (0.00 sec)

2.右外连接，查询hobby表对应的人
mysql> select hobby_name.h_name,student.name from student right join hobby_name on student.hobby = hobby_name.id;
+-----------+-----------+
| h_name    | name      |
+-----------+-----------+
| 足球      | 科比      |
| 乒乓球    | 姚明      |
| 篮球      | 梅西      |
| 篮球      | 回忆      |
| 篮球      | 程福兴    |
| 足球      | 刘橙      |
| 足球      | 刘文浩    |
| 乒乓球    | 李兵达    |
| 篮球      | 林振龙    |
| 羽毛球    | NULL      |
+-----------+-----------+
10 rows in set (0.00 sec)

 2.7 子查询
子查询：sub query，通过select查询结果当做另外一条select查询的条件或者数据源
1.查询student表中hobby是篮球的姓名，年龄，性别
mysql> select name,age,sex from student where hobby = (select id from hobby_name where h_name = "篮球");
+--------+------+------+
| name   | age  | sex  |
+--------+------+------+
| 梅西   |   20 | 男   |
| 回忆   |   21 | 男   |
| 程福兴 |   22 | 男   |
| 林振龙 |   19 | 女   |
+--------+------+------+
4 rows in set (0.00 sec)