MySQL基础

数据库的相关概念

数据库与数据库管理系统的关系

数据库管理系统(DBMS)可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。为保存
应用中实体的数据，一般会在数据库创建多个表，以保存程序中实体用户的数据。

数据库管理系统、数据库和表的关系如图所示：

概述

1. MySQL是一个 开放源代码的关系型数据库管理系统 ，由瑞典MySQL AB（创始人Michael Widenius）公 司1995年开发，迅速成为开源数据库的 No.1。
2. 2008被 Sun 收购（10亿美金），2009年Sun被 Oracle 收购。 MariaDB 应运而生。（MySQL 的创
   造者担心 MySQL 有闭源的风险，因此创建了 MySQL 的分支项目 MariaDB）。
3. MySQL6.x 版本之后分为 社区版 和 商业版 。
4. MySQL是一种关联数据库管理系统，将数据保存在不同的表中，而不是将所有数据放在一个大仓库
   内，这样就增加了速度并提高了灵活性。
5. MySQL是开源的，所以你不需要支付额外的费用。
6. MySQL是可以定制的，采用了 GPL（GNU General Public License） 协议，你可以修改源码来
   开发自己的MySQL系统。
7. MySQL支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
8. MySQL支持大型数据库，支持5000万条记录的数据仓库，32位系统表文件最大可支持 4GB ，64位系
   统支持最大的表文件为 8TB 。
9. MySQL使用 标准的SQL数据语言 形式。
10. MySQL可以允许运行于多个系统上，并且支持多种语言。这些编程语言包括C、C++、Python、 Java、Perl、PHP和Ruby等。

登录

格式：

mysql -h 主机名 -P 端口号 -u 用户名 -p密码

举例：

mysql -h localhost -P 3306 -u root -pabc123 # 这里我设置的root用户的密码是abc123

注意：
（1）-p与密码之间不能有空格，其他参数名与参数值之间可以有空格也可以没有空格。如：

mysql -hlocalhost -P3306 -uroot -pabc123

（2）密码建议在下一行输入，保证安全

mysql -h localhost -P 3306 -u root -p
Enter password:****

（3）客户端和服务器在同一台机器上，所以输入localhost或者IP地址127.0.0.1。同时，因为是连接本机： -hlocalhost就可以省略，如果端口号没有修改：-P3306也可以省略

简写成：

mysql -u root -p
Enter password:****

连接成功后，有关于MySQL Server服务版本的信息，还有第几次连接的id标识。

也可以在命令行通过以下方式获取MySQL Server服务版本的信息：

c:\> mysql -V

c:\> mysql --version

或登录后，通过以下方式查看当前版本信息：

mysql> select version()

退出登录

exit

或

quit

MySQL的使用演示

查看所有的数据库

show databases;

(1)“information_schema”是 MySQL 系统自带的数据库，主要保存 MySQL 数据库服务器的系统信息，
比如数据库的名称、数据表的名称、字段名称、存取权限、数据文件 所在的文件夹和系统使用的
文件夹，等等
(2)“performance_schema”是 MySQL 系统自带的数据库，可以用来监控 MySQL 的各类性能指标。
“sys”数据库是 MySQL 系统自带的数据库，主要作用是以一种更容易被理解的方式展示 MySQL 数据
库服务器的各类性能指标，帮助系统管理员和开发人员监控 MySQL 的技术性能。
(3)“mysql”数据库保存了 MySQL 数据库服务器运行时需要的系统信息，比如数据文件夹、当前使用的
字符集、约束检查信息，等等

创建自己的数据库

create database 数据库名;

#创建atguigudb数据库，该名称不能与已经存在的数据库重名。 
create database atguigudb;

使用自己的数据库

use 数据库名;

#使用atguigudb数据库 
use atguigudb;

说明：如果没有使用use语句，后面针对数据库的操作也没有加“数据名”的限定，那么会报“ERROR 1046 (3D000): No
database selected”（没有选择数据库）
使用完use语句之后，如果接下来的SQL都是针对一个数据库操作的，那就不用重复use了，如果要针对另
一个数据库操作，那么要重新use。

查看某个库的所有表格

show tables from 数据库名;

创建新的表格

create table 表名称( 字段名 数据类型, 字段名 数据类型 );

说明：如果是最后一个字段，后面就用加逗号，因为逗号的作用是分割每个字段。

#创建学生表 
create table student( id int, name varchar(20) #说名字最长不超过20个字符 );

查看一个表的数据

select * from 数据库表名称;

#查看学生表的数据
select * from student;

添加一条记录

insert into 表名称 values(值列表);

#添加两条记录到student表中 
insert into student values(1,'张三'); 
insert into student values(2,'李四');

报错：

mysql> insert into student values(1,'张三'); ERROR 1366 (HY000): Incorrect string value: '\xD5\xC5\xC8\xFD' for column 'name' at row 1 
mysql> insert into student values(2,'李四'); ERROR 1366 (HY000): Incorrect string value: '\xC0\xEE\xCB\xC4' for column 'name' at row 1

字符集的问题

解决方法
步骤1：查看编码命令

show variables like 'character_%';

show variables like 'collation_%';

步骤2：修改mysql的数据目录下的my.ini配置文件
（建议修改之前备份一份）

[mysql]# 大概在65行左右 default-character-set 注释去掉 在后面写utf8
no-beep
default-character-set=utf8

[mysqld] # 大概在76行左右，在其下添加
character-set-server=utf8
collation-server=utf8_general_ci

步骤3：重启服务

步骤4：查看编码命令

show variables like 'character_%';

show variables like 'collation_%';

问题解决

在MySQL 8.0版本之前，默认字符集为latin1，utf8字符集指向的是utf8mb3。网站开发人员在数据库设计
的时候往往会将编码修改为utf8字符集。如果遗忘修改默认的编码，就会出现乱码的问题。从MySQL 8.0
开始，数据库的默认编码改为 utf8mb4 ，从而避免了上述的乱码问题。

查看表的创建信息

show create table 表名称\G 

结果显示student的表格的默认字符集是“latin1”不支持中文

查看数据库的创建信息

show create database 数据库名\G

结果显示atguigudb数据库也不支持中文，字符集默认是latin1

删除表格

drop table 表名称;

删除数据库

drop database 数据库名;

MySQL图形化管理工具

MySQL图形化管理工具极大地方便了数据库的操作与管理，常用的图形化管理工具有：MySQL Workbench、phpMyAdmin、Navicat Preminum、MySQLDumper、SQLyog、dbeaver、MySQL ODBC Connector。

Navicat
Navicat MySQL是一个强大的MySQL数据库服务器管理和开发工具。它可以与任何3.21或以上版本的
MySQL一起工作，支持触发器、存储过程、函数、事件、视图、管理用户等，对于新手来说易学易用。
其精心设计的图形用户界面（GUI）可以让用户用一种安全简便的方式来快速方便地创建、组织、访问和
共享信息。Navicat支持中文，有免费版本提供。 下载地址：http://www.navicat.com/

连接报错

你的帐号不允许从远程登陆，只能在localhost。这个时候只要在localhost的那台电脑，登入MySQL后，更改 “mysql” 数据库里的 “user” 表里的 “host” 项，从"localhost"改称"%"

use mysql;

update user set host = '%' where user = 'root';

flush privileges;

出现这个原因是MySQL8之前的版本中加密规则是mysql_native_password，而在MySQL8之后，加密规则
是caching_sha2_password。解决问题方法有两种，第一种是升级图形界面工具版本，第二种是把MySQL8
用户登录密码加密规则还原成mysql_native_password。
第二种解决方案如下，用命令行登录MySQL数据库之后，执行如下命令修改用户密码加密规则并更新用
户密码，这里修改用户名为“root@localhost”的用户密码规则为“mysql_native_password”，密码值为
“123456”，如图所示。

#使用mysql数据库
USE mysql;
#修改'root'@'localhost'用户的密码规则和密码 
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'abc123';
#刷新权限 
flush privileges;

修改数据库和表的字符集

alter database 数据库名 charset utf8; #修改数据库的字符编码为utf8

alter table 表名 charset utf8; #修改表字符编码为UTF8

alter table 表名 modify name varchar(20) charset utf8; #修改字段字符编码为UTF8

SQL背景知识

• 1946 年，世界上第一台电脑诞生，如今，借由这台电脑发展起来的互联网已经自成江湖。在这几十年里，无数的技术、产业在这片江湖里沉浮，有的方兴未艾，有的已经几幕兴衰。但在这片浩荡的波动里，有一门技术从未消失，甚至“老当益壮”，那就是 SQL

  45 年前，也就是 1974 年，IBM 研究员发布了一篇揭开数据库技术的论文《SEQUEL：一门结构化的英语查询语言》，直到今天这门结构化的查询语言并没有太大的变化，相比于其他语言， SQL 的半衰期可以说是非常长 了。

• 不论是前端工程师，还是后端算法工程师，都一定会和数据打交道，都需要了解如何又快又准确地提取自己想要的数据。更别提数据分析师了，他们的工作就是和数据打交道，整理不同的报告，以便指导业务决策。

• SQL（Structured Query Language，结构化查询语言）是使用关系模型的数据库应用语言， 与数据直 接打交道 ，由 IBM 上世纪70年代开发出来。后由美国国家标准局（ANSI）开始着手制定SQL标准，先后有 SQL-86 ， SQL-89 ， SQL-92 ， SQL-99 等标准。

SQL 分类

SQL语言在功能上主要分为如下3大类：

• DDL（Data Definition Languages、数据定义语言），这些语句定义了不同的数据库、表、视图、索引等数据库对象，还可以用来创建、删除、修改数据库和数据表的结构
  主要的语句关键字包括 CREATE 、 DROP 、 ALTER 等。
• DML（Data Manipulation Language、数据操作语言），用于添加、删除、更新和查询数据库记录，并检查数据完整性。
  主要的语句关键字包括 INSERT 、 DELETE 、 UPDATE 、 SELECT 等。SELECT是SQL语言的基础，最为重要。
• DCL（Data Control Language、数据控制语言），用于定义数据库、表、字段、用户的访问权限和安全级别。
  主要的语句关键字包括 GRANT 、 REVOKE 、 COMMIT 、 ROLLBACK 、 SAVEPOINT 等。

因为查询语句SELECT使用的非常的频繁，所以很多人把查询语句单拎出来一类：DQL（数据查询语言）。
还有单独将 COMMIT 、 ROLLBACK 取出来称为TCL （Transaction Control Language，事务控制语言）。

SQL语言的规则与规范

• SQL 可以写在一行或者多行。为了提高可读性，各子句分行写，必要时使用缩进

• 每条命令以 ; 或 \g 或 \G 结束

• 关键字不能被缩写也不能分行

• 关于标点符号

  必须保证所有的()、单引号、双引号是成对结束的 必须使用英文状态下的半角输入方式 字符串型和日期时间类型的数据可以使用单引号（’ '）表示 列的别名，尽量使用双引号（" "），而且不建议省略as

SQL大小写规范 （建议遵守）

• MySQL 在 Windows 环境下是大小写不敏感的
• MySQL 在 Linux 环境下是大小写敏感的
  数据库名、表名、表的别名、变量名是严格区分大小写的
  关键字、函数名、列名(或字段名)、列的别名(字段的别名) 是忽略大小写的
• 推荐采用统一的书写规范：
  数据库名、表名、表别名、字段名、字段别名等都小写
  SQL 关键字、函数名、绑定变量等都大写

注 释

可以使用如下格式的注释结构

单行注释：#注释文字(MySQL特有的方式) 
单行注释：-- 注释文字(--后面必须包含一个空格。) 
多行注释：/* 注释文字 */

命名规则

• 数据库、表名不得超过30个字符，变量名限制为29个
• 必须只能包含 A–Z, a–z, 0–9, _共63个字符
• 数据库名、表名、字段名等对象名中间不要包含空格
• 同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名
• 必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使用`（着重号）引起来
• 保持字段名和类型的一致性，在命名字段并为其指定数据类型的时候一定要保证一致性。假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了
  举例：

#以下两句是一样的，不区分大小写 
show databases;
SHOW DATABASES;

#创建表格 #
create table student info(...); #表名错误，因为表名有空格 
create table student_info(...);

#其中order使用``飘号，因为order和系统关键字或系统函数名等预定义标识符重名了 
CREATE TABLE `order`();

select id as "编号", `name` as "姓名" from t_stu; #起别名时，as都可以省略 
select id as 编号, `name` as 姓名 from t_stu; #如果字段别名中没有空格，那么可以省略"" 
select id as 编 号, `name` as 姓 名 from t_stu; #错误，如果字段别名中有空格，那么不能省略""

数据导入指令

在命令行客户端登录mysql，使用source指令导入

mysql> source d:\mysqldb.sql

基本的SELECT语句

SELECT…

SELECT 1; #没有任何子句 
SELECT 9/2; #没有任何子句

SELECT … FROM

SELECT 标识选择哪些列 
FROM 标识从哪个表中选择

选择全部列：

SELECT * 
FROM departments;

一般情况下，除非需要使用表中所有的字段数据，最好不要使用通配符‘*’。使用通配符虽然可以节省输入查询语句的时间，但是获取不需要的列数据通常会降低查询和所使用的应用程序的效率。通配符的优势是，当不知道所需要的列的名称时，可以通过它获取它们。
在生产环境下，不推荐你直接使用 SELECT * 进行查询。

选择特定的列：

SELECT department_id, location_id 
FROM departments;

MySQL中的SQL语句是不区分大小写的，因此SELECT和select的作用是相同的，但是，许多开发人员习惯将关键字大写、数据列和表名小写，读者也应该养成一个良好的编程习惯，这样写出来的代码更容易阅读和维护。

列的别名

• 重命名一个列
• 便于计算
• 紧跟列名，也可以在列名和别名之间加入关键字AS，别名使用双引号，以便在别名中包含空格或特殊的字符并区分大小写。
• AS 可以省略
• 建议别名简短，见名知意
• 举例

SELECT last_name AS name, commission_pct comm 
FROM employees;

SELECT last_name "Name", salary*12 "Annual Salary" 
FROM employees;

去除重复行

默认情况下，查询会返回全部行，包括重复行

SELECT department_id FROM employees;

在SELECT语句中使用关键字DISTINCT去除重复行

SELECT DISTINCT department_id 
FROM employees;