DBA之路---第一天

作者: ivan_cq | 来源:发表于2019-05-06 10:52 被阅读0次

1、什么是数据库？

存储数据的仓库。
专业的定义：高效的存储和处理数据数据的介质（磁盘和内存两中介质）。注意高效、存储、处理

2、数据库的分类？

数据库基于存储介质的不同进行分类，分为关系型数据库（SQL）和非关系型数据库（NOSQL  ：  not  only  SQL 不是关系型数据库都叫非关系型数据库）

3、不同数据库阵营中的产品有哪些？

关系型数据库
    大型： Oracle,DB2
    中型：SQL-server，Mysql等
    小型：access等

非关系型数据库
      memeached，mongodb,redis
       他们的存储介质都是内存

4、两种数据库阵营的区别？

关系型数据库：安全（保存在磁盘上不容易丢失），比较浪费空间（二维表存储）

非关系型数据库：效率高（存储在内存中），不安全（断电丢失）

关系型数据库

1、什么是关系型数据库？

关系型数据库是一种建立在关系模型（数学模型）上的数据库。

关系模型：一种建在在关系上的模型，关系模型包含三个方面：
     数据结构：数据存储的问题（二维表，有行和列）
     操作指令集合：所有的SQL语句
     完整性约束：表内数据约束（字段与字段），表与表之间的约束（外键）

2、关系型数据库的设计？

以实际案例来进行设计：分析一个教学系统，讲师负责教学，教学生，在教室教学生

1、找出系统种的所有实体：讲师、学生、教室
2、找出实体中应该存在的数据信息
     讲师：姓名，年龄，性别，工资
     学生：姓名，班级，性别，学号，学科
     班级：班级名字，教室编号

      关系型数据库：维护实体内部，实体与实体之间的关系。
实体内部联系：每个学生都有姓名，性别，学科，学号等信息
第二行的所有字段都是在描述程前这个学生（内部联系）；第二列只能放性别（内部约束）


实体与实体之间的联系：一个班级又多个学生
实体与实体之间的约束：外键

关系型数据库的特点之一：如果表中对应的某个字段没有值（数据），但是系统依然要分配空间，所以关系型数据库比较浪费空间

关键字说明（关系型数据库）

      数据库：database
      数据库系统：DBS （database system):是一种虚拟系统，将多种内容联合起来的称呼
    DBS=DB+DBMS
DBMS:数据库管理系统，专门管理数据库
DBA：数据库管理员
行/记录  ：row/record  ,本质上是一个东西，都是指表中一行（一条记录），行是从结构角度出发，记录是从数据角度出发
列/字段：column/field,本质上是一个东西

SQL

SQL:structured  Query  Language ,结构化查询语言（数据以查询为主: 99%  是在进行查询操作）

  SQL  分为三个部分
  

DDL：data  definition  language，数据定义语言，用来维护存储数据的结构（数据库、表代表指令create、drop、alter等）


DML:datamanipulation，数据操作语言，用来对数据进行操作（数据表中的内容代表指令：insert、delete、update等）；其中DML内部又单独进行分类的、DQL数据查询语言（如select ）

**注意：SQL是关系型数据库的操作指令，所有关系型数据库的操作指令，SQL是一种约束，但不是强制（类似于w3c）；不同的数据库产品可能内部会有一些区别。

实验环境配置：
1、我使用的是centos系统，在mysql官网上下载rpm包(这4个包是相互依赖的)使用wget命令下载

mysql-community-client-5.7.26-1.el7.x86_64.rpm  mysql-community-libs-5.7.26-1.el7.x86_64.rpm
mysql-community-common-5.7.26-1.el7.x86_64.rpm  mysql-community-server-5.7.26-1.el7.x86_64.rpm

2、安装
rpm -ivh mysql-community-*
3、启动：systemctl start mysqld
4、初始化：
在一个纯净的环境中第一次初始化会出现以下问题。
首先，我们需要更改配置文件即可！

[root@server2 ~]# mysqld --initalizd
2019-04-30T16:03:12.169520Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-04-30T16:03:12.171037Z 0 [Note] mysqld (mysqld 5.7.26) starting as process 7839 ...
2019-04-30T16:03:12.173863Z 0 [ERROR] Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root!

2019-04-30T16:03:12.173884Z 0 [ERROR] Aborting

2019-04-30T16:03:12.173898Z 0 [Note] Binlog end
2019-04-30T16:03:12.173941Z 0 [Note] mysqld: Shutdown complete
mysql要求不能以root用户直接初始化mysql

/etc/my.cnf默认加如下内容

explicit_defaults_for_timestamp=1
user=mysql

此时就可以初始化成功

[root@server1 log]# mysqld --initialize
2019-04-29T22:25:33.606511Z 0 [ERROR] --initialize specified but the data directory has files in it. Aborting.
2019-04-29T22:25:33.606569Z 0 [ERROR] Aborting

出现了这个问题的话，原因是，数据目录已存在了，需要删除，数据目录在/var/lib/mysql
rm -rf /var/lib/mysql

mysqld --initialize
5、查看初始化密码（cat /var/log/mysqld.log | grep tempoary）

2019-04-29T22:28:18.345084Z 1 [Note] A temporary password is generated for root@localhost: lh72HL&jvhTy

6、登录并且创建用户设置新密码
如果设置密码出现密码内容不符合要求，就需要更改。
参考：https://www.cnblogs.com/ivictor/p/5142809.html

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '12345678';
Query OK, 0 rows affected (0.00 sec)

如果你的主机上已经有了mariadb，那么请先卸载。

卸载
rpm -qa  |grep mariadb
rpm -e  --nodeps  上面查找的包  或者   yum -y remove mariadb* 
rm  -rf  /var/lib/mysql/*

此时安装完毕！！！

mysql概述

mysql数据库是一种c/s结构的软件；若想访问服务器必须通过客户端（服务器一直运行，客户端在需要的时候运行）

交互方式：
1、客户端连接认证：连接服务器，认证身份

[root@server2 ~]# mysql -uroot -hlocalhost -P3306 -p12345678
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.26 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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>

2、客户端发送SQL指令
3、服务器接收指令，处理指令，返回操作结构
4、客户端接收结果显示结果
5、断开连接（\q或者exit 或者quit）

mysql服务器对象
mysql服务器内部对象分为四层：
系统（DBMS）相当于主机上的操作系统
数据库（DB）
数据表（table）
字段（field）

SQL的基本操作（CRUD）

将SQL的基本操作根据操作对象分为三类:库操作、表操作（字段）、数据操作

1、库操作：对数据库增删改查

创建数据库

基本语法：create  database 数据库名字  【库选项】
            库选项：用来约束数据库，分为两个选项
               字符集设定：charset/character set 具体字符集
                （数据存储的编码格式：常用字符集F=GBK和UTF8）
            校对集设定：collete  具体校对集（数据比较规则）

mysql> create database sql;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sql' at line 1
mysql> create database cq charset UTF8;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cq                 |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql>

要注意数据库名字不能用关键字（已经使用或者被保留的字）如果非要使用关键字或者保留字，那么必须使用反引号（esc下面的键在英文状态下输出
要注意：SQL语句报错：只会告诉用户报错的大概位置，不会说明报错原因

当创建数据库的SQL执行的时候，增加了对应数据库的信息，会在保留数据的目录下，创建一个对应数据库名字的目录，每个数据库下都有一个opt文件(linux默认在/var/lib/mysql)

[root@server2 ~]# cat /var/lib/mysql/c
ca-key.pem       ca.pem           client-cert.pem  client-key.pem   cq/              
[root@server2 ~]# cat /var/lib/mysql/cq/db.opt 
default-character-set=utf8
default-collation=utf8_general_ci

这个opt文件存放的是数据库的字符集和校对集，我们发现校对集依赖字符集

查看数据库

1、查看所有数据库：show databases;
2、查看指定部分的数据库，模糊查询
show databases like 'pattern';
pattern 表示模式 %表示多个字符 _只能匹配一个字符,注意引号

mysql> show databases like '%q%';
+----------------+
| Database (%q%) |
+----------------+
| cq             |
| mysql          |
+----------------+
2 rows in set (0.00 sec)

mysql> show databases like '_q';
+---------------+
| Database (_q) |
+---------------+
| cq            |
+---------------+
1 row in set (0.00 sec)

mysql>

3、查看数据库的创建语句（查看内容发现，数据库在执行sql语句之前会优化SQL，系统保存的结果是优化后的结果）

mysql> show create database cq;
+----------+-------------------------------------------------------------+
| Database | Create Database                                             |
+----------+-------------------------------------------------------------+
| cq       | CREATE DATABASE `cq` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------+
1 row in set (0.01 sec)

mysql>

4、更新数据库
数据库名字不可以修改
数据库的修改仅限于库选项：字符集和校对集（校对集依赖于字符集）

[root@server2 cq]# cat db.opt 
default-character-set=utf8
default-collation=utf8_general_ci
[root@server2 cq]# mysql -uroot -hlocalhost -P3306 -p12345678
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.26 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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> alter  database cq charset GBK;
Query OK, 1 row affected (0.00 sec)

mysql> \q
Bye
[root@server2 cq]# ls
db.opt
[root@server2 cq]# cat db.opt 
default-character-set=gbk
default-collation=gbk_chinese_ci

alter database 数据库名字  【库选项】；
charset    字符集
collate      校对集

操作集，依赖字符集，一旦数据库定义好后，不建议修改库选项，使用utf-8，因为目前主要针对中国市场，而为了使得将来发展可以兼容到别国市场，必须使用utf-8

5、删除数据库
在所有的操作中，删除数据库是最简单的。

语法： drop database  数据库名字；

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cq                 |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> drop database cq;
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

当执行这句删除语句的时候发生了什么？

1、在数据库内部看不到，看不到对应的数据库
2、在对用的数据库存储的文件夹内：数据库名字对应的文件夹也被删除（级联删除，里面的数据表全被删除）

注意数据库的删除是很严重的，不可逆的，应该先进行备份后，再进行删除操作（负法律责任）

表操作

表与字段是密不可分的

1、新增数据表

语法：
create  table  [if not exist]  表名(字段名字   数据类型，字段名字   数据类型)【表选项】    --**最后一行不需要逗号**

if  not  exist  如果表名不存在，那么就创建，否则不执行创建代码

表选项  ：  控制表的表现
          字符集：charset /character  set  具体字符集；
          校对集：collate   具体校对集；
           存储引擎： engine  具体的存储引擎（innodb  和  myisam）

（那么数据库所指定的字符集，表示数据库中的所有表默认的字符集，而表的字符集表示表里面的字段的字符集，所以数据库不设置也行，DBMS 默认utf-8，在安装的时候为操作系统设置的）

mysql> create database cq;
Query OK, 1 row affected (0.00 sec)

mysql> use cq;
Database changed
mysql> create table if not exist student(name varchar(10),gender varchar(10),num varchar(10),age int)charset utf8;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'exist student(name varchar(10),gender varchar(10),num varchar(10),age int)charse' at line 1
mysql> create table if not exist student(name varchar(10),sex varchar(10))charset utf8;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'exist student(name varchar(10),sex varchar(10))charset utf8' at line 1
mysql> create table student(name varchar(10),sex varchar(10))charset utf8;
Query OK, 0 rows affected (0.06 sec)

mysql> create table if not exists teacher(name varchar(10),sex varchar(10))charset utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+--------------+
| Tables_in_cq |
+--------------+
| student      |
| teacher      |
+--------------+
2 rows in set (0.00 sec)

任何一个表的创建必须先要进入数据库

方案一：显示的指定表所属的数据库
create table  数据库名.表名（）；
将当前数据表创建到指定的数据库下

mysql> create database cq2;
Query OK, 1 row affected (0.00 sec)

mysql> create table cq2.student(name varchar(10),sex varchar(10))charset utf8;
Query OK, 0 rows affected (0.01 sec)


方案二：隐式的指定表所属的数据库：先进入到某个数据库环境，然后这样创建的表自动归属到某个指定的数据库
use  database;
create table;

当创建数据表的SQL指令执行后到底发生了什么？

1、指定数据库下已经存在对应的表
2、在数据库对用的文件夹下，会产生对应表的结构文件（跟存储引擎有关系)

[root@server2 mysql]# cd cq2
[root@server2 cq2]# ls
db.opt  student.frm  student.ibd
[root@server2 cq2]# cd ..
[root@server2 mysql]# ls
auto.cnf    ca.pem           client-key.pem  cq2             ibdata1      ib_logfile1  mysql       mysql.sock.lock     private_key.pem  server-cert.pem  sys
ca-key.pem  client-cert.pem  cq              ib_buffer_pool  ib_logfile0  ibtmp1       mysql.sock  performance_schema  public_key.pem   server-key.pem
[root@server2 mysql]# cd cq
cq/  cq2/ 
[root@server2 mysql]# cd cq
[root@server2 cq]# ls
db.opt  student.frm  student.ibd  teacher.frm  teacher.ibd

frm就是结构文件
ibd文件存放数据，文件的后标和引擎有关系

2、查看数据表
数据库查看的方式，表都可以使用。

1、查看所有表
2、模糊查询（匹配）
3、查看创建语句
4、查看表中的字段信息
mysql> show tables;
+--------------+
| Tables_in_cq |
+--------------+
| student      |
| teacher      |
+--------------+
2 rows in set (0.00 sec)

mysql> show tables like '_t%';
+--------------------+
| Tables_in_cq (_t%) |
+--------------------+
| student            |
+--------------------+
1 row in set (0.00 sec)

尽量使用前面确定后面模糊，因为这样效率高

mysql> show create table teacher\g
+---------+-----------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                      |
+---------+-----------------------------------------------------------------------------------------------------------------------------------+
| teacher | CREATE TABLE `teacher` (
  `name` varchar(10) DEFAULT NULL,
  `sex` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+-----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table teacher\G
*************************** 1. row ***************************
       Table: teacher
Create Table: CREATE TABLE `teacher` (
  `name` varchar(10) DEFAULT NULL,
  `sex` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

\g等价于分号
\G看数据舒服，旋转了，也等价于分号

mysql> show columns from student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(10) | YES  |     | NULL    |       |
| sex   | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> describe student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(10) | YES  |     | NULL    |       |
| sex   | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(10) | YES  |     | NULL    |       |
| sex   | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

三种方式查看表的结构
第一列是字段名
第二列是列类型（字段类型\数据类型）
第三列是列属性（是否允许为空）
第四列：key是索引(索引类型 PRI、UNI)
第五列：列属性（默认值）
第六列：列属性（扩充（额外的））

修改数据表

修改数据表分为修改数据表本身还有修改字段

1、修改表本身
修改表名

mysql> rename table student to my_student\g
Query OK, 0 rows affected (0.32 sec)

mysql> show tables\g
+--------------+
| Tables_in_cq |
+--------------+
| my_student   |
| teacher      |
+--------------+

修改表选项(字符集、表选项、引擎)

mysql> show create table my_student\G
*************************** 1. row ***************************
       Table: my_student
Create Table: CREATE TABLE `my_student` (
  `name` varchar(10) DEFAULT NULL,
  `sex` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> alter table my_student charset=GBK;
Query OK, 0 rows affected (0.47 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table my_student\G
*************************** 1. row ***************************
       Table: my_student
Create Table: CREATE TABLE `my_student` (
  `name` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
  `sex` varchar(10) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)

修改字段：
新增、修改、重命名、删除

新增字段

语法：
alter  table  表名  add  [column] 字段名  数据类型 【列属性】 【位置】

位置：
      字段名可以存放在表中任意一个位置
        first：第一个位置
        after： 在哪个字段之后：after  字段名

给my_student 这个表增加num放到第一个位置


mysql> alter table my_student 
   -> add num int
   -> first\g

Query OK, 0 rows affected (0.51 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> desc my_student\g
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| num   | int(11)     | YES  |     | NULL    |       |
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| sex   | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

我们使用三行，因为mysql会自动寻找分号：作为语句结束符

修改字段：通常是修改属性或者是数据类型
语法：alter  table  表名  modify  字段名 数据类型  【属性】  【位置】

将学生表中的num字段变成固定长度，且放在第二位
mysql> desc my_student\g
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| num   | int(11)     | YES  |     | NULL    |       |
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| sex   | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table my_student
    -> modify num char(10) after id;
Query OK, 0 rows affected (0.51 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc my_student\g
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| num   | char(10)    | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| sex   | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

重命名字段

···
语法：
alter table 表名
change 旧字段新字段名数据类型【属性】【位置】；
···

修改学生表中的sex字段变成gender类型


mysql> desc my_student\g
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| num   | char(10)    | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| sex   | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table my_student
    -> change sex  gender varchar(10);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc my_student\g
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| num    | char(10)    | YES  |     | NULL    |       |
| name   | varchar(10) | YES  |     | NULL    |       |
| gender | varchar(10) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

删除字段

语法：
alter table  表名 drop 字段名；

  mysql> desc my_student\g
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| num    | char(10)    | YES  |     | NULL    |       |
| name   | varchar(10) | YES  |     | NULL    |       |
| gender | varchar(10) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table my_student drop num;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc my_student\g
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(10) | YES  |     | NULL    |       |
| gender | varchar(10) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

注意：如果表中已经存在数据，那么删除字段就会删除该字段的所有数据，并且这个操作不可逆

数据操作

新增数据

方案一：给全表字段插入数据，不需要指定地段列表；要求数据的值出现的顺序必须与表中设计的字段出现的顺序一致。都需要以引号（建议是单引号）包裹

insert into 表名 values（值列表）【,(值列表)】

mysql> desc my_student\g
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(10) | YES  |     | NULL    |       |
| gender | varchar(10) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into my_student values(1,'cq','female'),
    -> (2,'lyf','female');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

方案二：
给部分字段插入数据，需要选定字段列表：字段列表出现的顺序与字段的顺序无关；但是值列表的顺序必须与选定的字段顺序一致


mysql> insert into my_student (id,name) values (1,'sss'),(2,'saas');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from my_student\g
+------+------+--------+
| id   | name | gender |
+------+------+--------+
|    1 | cq   | female |
|    2 | lyf  | female |
|    1 | sss  | NULL   |
|    2 | saas | NULL   |
+------+------+--------+
4 rows in set (0.00 sec)

查看数据

语法：
select  *或者字段列表（逗号隔开） from   表名 【where  条件】；

查看所有数据
mysql> select * from my_student\g
+------+------+--------+
| id   | name | gender |
+------+------+--------+
|    1 | cq   | female |
|    2 | lyf  | female |
|    1 | sss  | NULL   |
|    2 | saas | NULL   |
+------+------+--------+
4 rows in set (0.00 sec)

查看指定字段，指定条件的数据

mysql> select id,name from my_student where id=1;
+------+------+
| id   | name |
+------+------+
|    1 | cq   |
|    1 | sss  |
+------+------+
2 rows in set (0.29 sec)

更新数据

语法：
update  表名 set  字段=值 【where 条件】；  建议都有where，要不然更新全部数据

mysql> select * from my_student\g
+------+------+--------+
| id   | name | gender |
+------+------+--------+
|    1 | cq   | female |
|    2 | lyf  | female |
|    1 | sss  | NULL   |
|    2 | saas | NULL   |
+------+------+--------+
4 rows in set (0.00 sec)

mysql> update my_student set gender='male' where name ='cq';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from my_student\g
+------+------+--------+
| id   | name | gender |
+------+------+--------+
|    1 | cq   | male   |
|    2 | lyf  | female |
|    1 | sss  | NULL   |
|    2 | saas | NULL   |
+------+------+--------+
4 rows in set (0.01 sec)

如果要更新的数据是字符串，那么就一定要加引号

更新不一定会成功

判断数据是否更新成功的依据
mysql> select * from my_student\g
+------+------+--------+
| id   | name | gender |
+------+------+--------+
|    1 | cq   | female |
|    2 | lyf  | female |
|    1 | sss  | NULL   |
|    2 | saas | NULL   |
+------+------+--------+
4 rows in set (0.00 sec)

mysql> update my_student set gender='male' where name ='cq';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from my_student\g
+------+------+--------+
| id   | name | gender |
+------+------+--------+
|    1 | cq   | male   |
|    2 | lyf  | female |
|    1 | sss  | NULL   |
|    2 | saas | NULL   |
+------+------+--------+
4 rows in set (0.01 sec)

mysql> update my_student set gender='male' where name ='cq';
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0


注意：第一个是显示改变了一个
第二个显示未发生改变

删除数据

删除是不可逆的：所有有风险，谨慎删除

语法：
delete  from my_student  where sex='male';

mysql> delete from my_student where gender=male\g
ERROR 1054 (42S22): Unknown column 'male' in 'where clause'
mysql> delete from my_student where gender='male'\g
Query OK, 1 row affected (0.03 sec)

mysql> select * from my_student\g
+------+------+--------+
| id   | name | gender |
+------+------+--------+
|    2 | lyf  | female |
|    1 | sss  | NULL   |
|    2 | saas | NULL   |
+------+------+--------+
3 rows in set (0.01 sec)

注意：字符型一定要加引号

中文数据问题

中文数据问题本质上是字符集问题

由于我们之前进行设置创建表的字符集默认为GBK，那么理论上，创建我们可以向表中插入中文数据。

当我们，插入数据的时候，如果插入中文出现问题。
客户端向服务器插入中文数据没有成功。

incorrect string value: '\xD5\xC5\xD4\xBD' for column 'name' at row1

原因分析：
代表的是插入的中文数据“张越”在当前编码（字符集）下对应的二进制编码转换城的16进制：两个汉字对应4个字节，所以肯定是GBK。

报错：服务器没有是被对应的4个字节，因为服务器任务数据是utf8的，他会认为一个汉字占用三个字节，所以服务器最终读取失败。

所有的数据库服务器认为的一些特性都是通过服务器端的一些变量来保存。
系统会先读取自己的变量，看看应该怎么表现。

查看服务器识别哪些字符集（一共支持39种字符集）基本上服务器是万能的：

mysql> show character set\g

问题就是客户端的字符集无法改变（windows上），那么就应该调整服务器和客户端的对话。

查看服务器和客户端打交道的字符集

  mysql> mysqlvariables like '> show ch_set%'; set\g
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |          客户端发送给服务器的数据的字符集为utf8
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |  当前所在数据库的字符集，show create database cq
| character_set_filesystem | binary                     |  
| character_set_results    | utf8                       |    服务器默认的给外部数据的字符集
| character_set_server     | latin1                     |    
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)




mysql> show create database cq;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| cq       | CREATE DATABASE `cq` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)

所以问题根源：客户端数据只能是GBK而服务器只能识别utf8

改变服务器，将默认的接收字符集改成GBK

mysql> set character_set_client=gbk;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'character_set%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | gbk                        |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

改了之后问题解决

但是当查看数据的时候，又出现了乱码问题


mysql> select * from my_student\g
+------+-----------+--------+
| id   | name      | gender |
+------+-----------+--------+
|    2 | lyf       | female |
|    1 | sss       | NULL   |
|    2 | saas      | NULL   |
|    5 | 鍝堝搱    | 鐢     |
+------+-----------+--------+
4 rows in set (0.00 sec)

原因：客户端发送的数据是utf-8（三个字节一个汉字）的，而客户端解析数据时只采用GBK（两个字节一个汉字）

解决方案：修改服务器给客户端的字符集为GBK

效果：

set  属性=值；这种方式的修改只是会话级别的，在当前客户端生效，关闭连接生效

快速设置字符集

set names gbk;

**在这个语句执行后，有三个变量发生了变化
character_set_results、character_set_connection、character_set_client
**
**connection 数据库字符转换的中间集，如果改变了效率更高，不改变也没有关系
**

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.26, for Linux (x86_64) using  EditLine wrapper

Connection id:      4
Current database:   cq
Current user:       root@localhost
SSL:            Not in use
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     5.7.26 MySQL Community Server (GPL)
Protocol version:   10
Connection:     Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    gbk
Conn.  characterset:    gbk
UNIX socket:        /var/lib/mysql/mysql.sock
Uptime:         1 hour 10 min 6 sec

校对集问题

校对集：数据比较的方式
校对集有三种格式：

_bin:  binary,二进制比较，取出二进制位一位一位进行比较（从左到右） 区分大小写
_case:  sensitive  大小写敏感，区分大小写
_ci:  case  insensitice  大小写不敏感，不区分大小写

查看数据库所支持的校对集(yes 代表不区分大小写)：

show collation;

校对集的一个应用：只有当数据产生比较的时候校对集才会生效

[root@server2 ~]# cat /var/lib/mysql/cq/db.opt 
default-character-set=latin1
default-collation=latin1_swedish_ci

我们创建的数据库默认都是ci结尾的，那么是不区分大小写的，也就是不进行比较。
所以校对集不产生作用

对比：使用utf8的_bin和_ci来验证不同校对集的效果
1、创建不同的校对集对应的表

mysql> use cq;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+--------------+
| Tables_in_cq |
+--------------+
| my_student   |
| teacher      |
+--------------+
2 rows in set (0.00 sec)

mysql> create table my_collate_bin(name char(1))charset utf8 collate utf8_bin;
Query OK, 0 rows affected (0.01 sec)

mysql> create table my_collate_ci(name char(1))charset utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+----------------+
| Tables_in_cq   |
+----------------+
| my_collate_bin |
| my_collate_ci  |
| my_student     |
| teacher        |
+----------------+
4 rows in set (0.00 sec)

2、插入数据

mysql> select * from my_collate_bin;select * from my_collate_ci;
+------+
| name |
+------+
| a    |
| A    |
| B    |
| b    |
+------+
4 rows in set (0.00 sec)

+------+
| name |
+------+
| a    |
| A    |
| B    |
| b    |
+------+
4 rows in set (0.00 sec)

3、比较：根据某个字段进行排序

mysql> select * from my_collate_bin order by name;
+------+
| name |
+------+
| A    |
| B    |
| a    |
| b    |
+------+
4 rows in set (0.00 sec)

mysql> select * from my_collate_ci order by name;
+------+
| name |
+------+
| a    |  
| A    |  
| B    |
| b    |
+------+
4 rows in set (0.00 sec)

我们默认的是不区分大小写的ci，那么，这样小a和A排序的时候是一样大的

注意：** 有数据后修改校对集是无效的 **

DBA之路---第一天

关系型数据库

关键字说明（关系型数据库）

SQL

mysql概述

SQL的基本操作（CRUD）

创建数据库

查看数据库

表操作

校对集问题

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读