MySQL数据库

简介

  • MySQL是一个开源免费的关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下公司。MySQL 最流行的关系型数据库管理系统，在 WEB 应用方面MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一，具有成本低、速度快、体积小且开放源代码等优点。

下载及安装

创建配置文件

  • mysql在windows下的配置文件，叫 mysql.ini（MySQL安装目录即可），默认是没有的，我们需要手动创建。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
[mysqld]
; 设置3306端口
port=3306
; 设置mysql的安装目录
basedir="D:mysql"
; 设置mysql数据库的数据的存放目录，就是前面手动创建的data目录
datadir="D:/mysql"
; 允许最大连接数
max_connections=200
; 允许连接失败的次数。
max_connect_errors=10
; 服务端使用的字符集默认为utf8mb4
character-set-server=utf8mb4
; 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
; 默认使用“mysql_native_password”插件认证, mysql_native_password
default_authentication_plugin=mysql_native_password
[mysql]
; 设置mysql网络通信的默认字符集
default-character-set=utf8mb4
[client]
; 设置mysql客户端连接服务端时默认使用的端口
port=3306
; 设置mysql客户端的默认字符集
default-character-set=utf8mb4

每次修改配置文件后，都需要重启MySQL服务才有用

  • 初始化数据库
1
mysqld --initialize --console
  • 注册系统服务
1
2
3
4
mysqld --install mysql80 # mysql80就是自己取的服务名
 
# 注销服务，用于卸载mysql的
# mysqld --remove mysql80

MYSQL指令

  • 启动&停止&重启
1
2
3
net start mysql80  # 启动
net stop mysql80  # 停止
net start mysql80  # 重启
  • 登录
1
2
3
4
5
6
# 启动本地MySQL，回车后输入初始密码
mysql -u root -p
# 使用远程MySQL，-h参数后加主机名
mysql -u root -h [ip] -p
# 退出
exit

进入MySQL

类比理解数据库：数据库（文件夹），数据表（excel文件）

MySQL是关系型数据库，我们操作MySQL就要使用SQL（结构化查询语言）。

  • 以下命令都是在MySQL下完成

SQL规范

  1. 在数据库管理系统中，SQL语句关键字不区分大小写(建议用大写) ，参数区分大小写。建议命令大写，数据库名、数据表名、字段名统一小写，如数据库名、数据表名、字段名与关键字同名，使用反引号圈起来，避免冲突。
  2. SQL语句可单行或多行书写，默认以英文分号（;）结尾，关键词不能跨多行或简写。
  3. 字符串跟日期类型的值都要以 单引号括起来，单词之间需要使用半角的空格隔开。
  4. 用空格和缩进来提高SQL语句的可读性。
  • SQL类型

根据不同的用途，SQL语句通常分3大类型：

  1. 数据定义语言（Data Definition Language，DDL）

    用于创建或删除数据库以及数据表的语句，DDL包含以下几种指令：

    ​ CREATE: 创建数据库和表等对象
    ​ DROP: 删除数据库和表等对象
    ​ ALTER: 修改数据库和表等对象的结构

  2. 数据操纵语言（Data Manipulation Language，DML）

    用于对数据表中的数据进行增删查改的。

    ​ SELECT: 查询表中的数据
    ​ INSERT: 向表中插入新数据
    ​ UPDATE: 变更表中的数据
    ​ DELETE: 删除表中的数据

  3. 数据控制语言（Data Control Language，DCL）

    用于对控制数据库的操作权限的，包括用户权限以及数据操作权限。

    ​ COMMIT: 确认对数据库中的数据进行的变更
    ​ ROLLBACK: 取消对数据库中的数据进行的变更
    ​ GRANT: 赋予用户操作权限
    ​ REMOVE: 取消用户的操作权限

  • 注释
1
2
3
4
5
6
7
8
9
10
第一种：
-- 单行注释

第二种：
/*
   多行注释
*/

第三种：
# mysql支持单行注释
  • 登陆后修改用户名密码
1
2
3
4
alter user 'root'@'localhost' identified by '123456';
# 'root' 就是要修改密码的用户名
# 'localhost' 表示允许用户在什么地址下可以使用密码登陆到数据库服务器，localhost表示本地登陆
# '123456'  新的密码了

数据库管理

1
2
3
4
5
6
7
8
9
show databases;  # 查看已有的数据库
create database <数据库名字>; # 创建数据库
# 存在就不重复创建，编码类型utf8
create database [if not exists] <数据库名字> [character set utf8]  
drop database <数据库名字>; # 删除数据库
use <数据库名字>; # 进入数据库
alter database  <数据库名字> [character set xxx] # 修改当前使用数据库
select database(); # 查看当前使用的数据库
show tables; # 查看数据库下的数据表

数据表管理

  • 数据表中的一条记录就相当于excel的一行内容。

  • 查看表

    1
    2
    use <数据库名字>; # 进入数据库
    show tables; # 查看数据库下的数据表

  • 创建表（字段名就是列名词）

    1
    2
    3
    4
    5
    create table [if not exists] <表名称号>(
    	字段名，数据类型[ ( 存储空间 )    字段约束 ],
    	字段名，数据类型[ ( 存储空间 )    字段约束 ],
    	字段名，数据类型[ ( 存储空间 )    字段约束 ]，
    )default charset=utf8

    注意：

  1. 上面SQL语句中，小括号中的定义字段语句后面必须以英文逗号结尾，而最后一个字段的定义语句不能有英文逗号出现，否则报错。
  2. 在同一张数据表中，字段名是不能相同，否则报错！
  3. 创建数据表的SQL语句中，存储空间和字段约束是选填的，而字段名和数据类型则是必须填写的。
  • 可以写一行
1
create table tb1(id int, name varchar(16), age int)default charset=utf8;

  • 也可以分开写（直到分号才结束）

    1
    2
    3
    4
    5
    create table tb2(
    id int, 
    name varchar(16), 
    age int
    )default charset=utf8;

  • 两种创建方式均可

  • 此时show tables就可以看到创建的两张表了

  • 不允许为空

    1
    2
    3
    4
    5
    create table tb2(
    id int,
    name varchar(16) not null,  # 不允许此列为空 
    age int  null # 允许此列为空（默认）
    )default charset=utf8;

  • 插入时，写默认值

1
2
3
4
5
create table tb2(
id int, 
name varchar(16), 
age int default 18  # 插入数据时，age列的值默认为18
)default charset=utf8;

  • 主键一般表示当前行的数据的编码

    1
    2
    3
    4
    5
    create table tb2(
    id int primary key,  # 主键，不允许为空，不能重复 
    name varchar(16), 
    age int
    )default charset=utf8;

  • 内部维护，自增 

    1
    2
    3
    4
    5
    create table tb2(
    id int auto_increment primary key,  # 内部维护，自增 
    name varchar(16), 
    age int
    )default charset=utf8;

  • 一般创建不允许id为空

    1
    2
    3
    4
    5
    create table tb2(
    id int not null auto_increment primary key,  
    name varchar(16), 
    age int
    )default charset=utf8;

  • 删除表

    1
    drop table <表名称>;

  • 展示表格的属性

    1
    desc <表名称>;

数据类型

以下列举常用的数据类型，更多参考官方文档

  • tinyint

    有符号：取值范围：-128127（有正负）默认
    无符号：取值范围：0    255（只有正）

  • 以下就是创建无符号的表

    1
    2
    3
    4
    create table tb2(
    id int not null auto_increment primary key,  
    age tinyint unsigned
    )default charset=utf8;

  • int

    int 有符号：取值范围：-21474836482147483647（有正负）默认
    int unsigned无符号：取值范围：0    4294967296（只有正）

  • bigint

    这个的范围比int还大的多

  • float

  • double

  • decimal

    准确的小数值，m是数字总个数（负号不算），d是小数点后放两位，m最大值为65，d最大值为30

1
2
3
4
create table tb3(
id int not null auto_increment primary key,  
salary decimal(m,d)
)default charset=utf8;

  • char，速度快

    定长字符串(固定用11个字符存储) ，最多可容纳255个字符

    1
    2
    3
    4
    create table tb4(
    id int not null auto_increment primary key,  
    moble char(11)
    )default charset=utf8;

  • varchar ，节省时间

    变长字符串，真实数据多长，就按照多长存储。最大根据编码最大字节/3

    1
    2
    3
    4
    create table tb5(
    id int not null auto_increment primary key,  
    moble varchar(11)
    )default charset=utf8;

  • text

    text数据类型用于保存变长的最大字符串，则可以多达六万多字符，mediumtext， longtext更多
    一般，长文本用text，

  • mediumtext

  • longtext

  • datatime

    YYYY-MM-DD HH:MM:SS

  • data

    YYYY-MM-DD

数据行操作

  • 新增数据
    1
    insert into 表名(<字段名>,<字段名>) values(值,值);
  • 删除数据
1
2
delete from 表名;
delete from 表名 where 条件;
1
2
3
4
5
delete from usertable where id = 3;
delete from usertable where id = 3 and name = "王";
delete from usertable where id != 3;
delete from usertable where id > 3;
delete from usertable where id in (3,5); # 产出3和4

  • 修改数据

    1
    2
    3
    update 表名 set 列=值;
    update 表名 set 列=值,列=值;
    update 表名 set 列=值 wherr 条件;

  • 查询数据

    1
    2
    3
    select * from 表名称;
    select 列名称 from 表名称;
    select 列名称 from 表名称 where 条件;

  • 创建用户表（练习）

    1
    2
    3
    4
    5
    6
    7
    8
    9
    create table usertable(
    	id int not null auto_increment primary key,  
    	name varchar(64) not null,
    	password char(64) not null,
    	email varchar(64) not null,
    	age tinyint,
    	salary decimal(10,2),
    	ctime datetime
    )default charset=utf8;

查询数据

1
2
3
4
5
6
SELECT *|<字段名>,<字段名> ...   FROM <数据表名>
                 WHERE 条件
                 GROUP BY field
                 HAVING 筛选
                 ORDER BY field
                 LIMIT 限制条数
  • where
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 比较运算符：
> < >= <= <> !=
between 80 and 100 值在10到20之间
in(80,90,100) 值是10或20或30
like 'a%'
/*
pattern可以是%或者_，
如果是%则表示任意多字符
如果是_则表示一个字符_，两个_则表示两个字符：__
*/

# 逻辑运算符
在多个条件直接可以使用逻辑运算符 and or not

# 正则 
SELECT * FROM emp WHERE emp_name REGEXP '^yu';
SELECT * FROM emp WHERE name REGEXP 'n$';
  • orde
1
2
select *|<字段名>,<字段名>... from tab_name order by field [Asc|Desc]
# Asc 升序、Desc 降序，其中asc为默认值 ORDER BY 子句应位于SELECT语句的结尾。
  • group by：分组查询

GROUP BY 语句根据某个列对结果集进行分组。分组一般配合着聚合函数完成查询。

常用聚合(统计)函数

  • max()：最大值。
  • min()：最小值。
  • avg()：平均值。
  • sum()：总和。
  • count()：个数。

在MySQL的SQL执行逻辑中，where条件必须放在group by前面,也就是先通过where条件将结果查询出来，再交给group by去分组进行统计，统计之后的查询用having。

  • limit：记录条数限制
1
2
3
SELECT * from <数据表名> limit 1;
SELECT * from <数据表名> limit 2,5; # 跳过前两条显示接下来的五条纪录
SELECT * from <数据表名> limit 2,2;
  • distinct：查询去重
1
SELECT distinct salary from <数据表名> order by salary;
  • 更新记录
1
UPDATE <表名> SET 字段 1=值 1 [,字段 2=值 2… ] [WHERE 子句 ]
  • 删除记录
1
DELETE FROM <表名> [WHERE 子句] [ORDER BY 子句] [LIMIT 子句]

关联表

创建两张表，第二个表的单行/多行与第一张表的一行相关，但第二个表中的一个行只可以与第一个表中的一个行相关。这是为了节约数据库存储空间。