MySQL学习笔记一

MySQL概述

什么是数据库：数据库就是一个存储数据的仓库
MySQL特点：

1. 关系型数据库
2. 跨平台Unix, Linux, Windows上运行MySQL服务
3. 支持多种编程语言：Python，Java，php
   (1) 关系型数据库
   关系型数据库特点：数据是以行和列的形式存储的；这一系列的行和列称为表；表中的每一行叫作一条记录；表中的每一列叫作一个字段；表和表之间的逻辑关联叫作关系；关系型数据库的核心内容是关系，即二维表
   示例：
   (1) 关系型数据库存储
   表1 学生信息表
   姓名 年龄 班级
   小猫猫 1 007
   小小猫 1 008

表2 班级信息表
班级 班主任
007 诗诗
008 诗蕊
(2) 非关系型数据库存储
{姓名:“小猫猫”, 年龄:1, 班级:007, 班主任:“诗诗”}
{姓名:“小小猫”, 年龄:1, 班级:008, 班主任:“诗蕊”}

MySQL安装

Ubuntu 安装MySQL服务

1. 安装服务端
   sudo apt-get install mysql-server
2. 安装客户端
   sudo apt-get install mysql-client

Windows安装MySQL服务

1. 下载MySQL安装包(Windows)
   mysql-install-5.7.msi
2. 按照教程安装

启动和连接MySQL服务

1. 服务端启动
   (1) 查看MySQL服务的状态
   sudo /etc/init.d/mysql status
   
   (2) 启动MySQL服务
   sudo /etc/init.d/mysql start
   (3) 停止MySQL服务
   sudo /etc/init.d/mysql stop
   
   (4) 重启MySQL服务
   sudo /etc/init.d/mysql restart
   
2. 客户端连接
   (1) 命令格式
   mysql -h主机名 -u用户名 -p密码
   or mysql -h主机名 -u用户名 -p
   例如：mysql -hlocalhost -uroot -p123456
   (2) 本地连接可以省略-h选项
   mysql -uroot -p123456
   
   (3) 断开与服务器的连接
   exit
   quit
   | \q

基本的SQL命令

SQL命令的使用规则

1. 每条命令必须以；结尾
2. SQL命令不区分字母大小写
3. 使用\c终止命令的运行

库的管理

库的基本操作：

(1). 查看已有的库
show databases;

(2). 创建库
create databases 库名 default charset=utf8 ;
(3). 查看创建库的语句
show create database 库名 ;

(4) 查看当前所在库
select database();
(5) 切换库
use 库名;
(6) 查看库中已有的表
show tables;
(7) 删除库
drop database 库名;

库的命名规则

(1) 可以使用数字、字母和下划线，但是不能是纯数字
(2) 库名区分字母大小写
(3) 库名具有唯一性
(4) 不能使用特殊字符串和mysql关键字

表的管理

(1) 创建表
create table 表名(
字段名 数据类型,
字段名 数据类型,
…
字段名 数据类型
);

(2) 查看创建表的语句(字符集)
show create table 表名;
(3) 查看表结构
desc 表名;
(4) 删除表
drop table 表名;

注意

(1) 所有的数据都是以文件的形式存储在数据库目录下
(2) Ubuntu数据库默认目录：/var/lib/mysql

表记录的管理

(1) 在表中插入一条记录

insert into 表名 values(值1), (值2), …;

(2) 查看表记录

select * from 表名;
select 字段名1, 字段名2 from 表名;

(3) 更新表记录

update 表名 set 字段名=值, 字段名=值, … where 条件;

注意：update语句后如果不加where子句，表中所有记录该字段的值都会更改

删除表记录

delete from 表名 where 条件;

注意：delete语句后如果不加where条件字句将会把表中所有记录删除

更改默认字符集

(1)方法：通过更改Mysql的配置文件实现
(2) 获取root权限
sudo -i
(3) 修改mysql配置文件
vi /etc/mysql/mysql.conf.d/mysqld.cnf
在[mysqld]文件中加入
character_set_server = utf8
(4) 重启mysql服务
sudo /etc/init.d/mysql restart

客户端把数据存储到数据库服务器上的过程

(1) 连接到数据库服务器 mysql -uroot -p
(2) 选择库: use 库名;
(3) 创建/修改表
(4) 断开与数据库的连接 exit quit \q

MySQL 的数据类型

(1) 数值类型(有符号signed和无符号unsigned)

1.1 整型
int 大整型(4个字节)；取值范围: 0~ $2^{32}-1$
tinyint 微小整型(1个字节)；取值范围：有符号(-128~+127), 无符号(0 ~255).
smallint 小整型(2个字节); 取值范围: 0 ~ 65535
bigint 极大整型(8个字节); 取值范围: 0 ~ $2^{64}-1$
1.2 浮点型
float(4个字节，最多显示7个有效位)
用法：字段名 float(m, n); m代表总位数，n代表小数位位数
e.g.: float(5, 2) 取值范围：-999.99 ~ +999.99
注意：浮点型插入整数时会自动补全小数位数；小数位如果多于指定位数，会对下一位四舍五入
double(8个字节，最多显示15个有效位)
用法：字段名 double(m, n)
decimal(M+2个字节的存储空间，最多能够显示28个有效位)
用法：decimal(M, D)

(2) 字符类型

char(定长)
宽度取值范围：0 ~ 255
不给定宽度默认宽度为1
varchar(变长)
取值范围：1 ~ 65535
注意： varchar没有默认宽度，必须给定一个宽度；char和varchar使用时都给定宽度，但不能超过各自的范围；
char 和 varchar的特点：char浪费存储空间但性能高；varchar虽然节省存储空间但性能低
字符类型的宽度和数值类型的宽度的区别：数值类型的宽度为显示宽度，只用于select查询时使用，和占用存储空间大小无关，可以用zerofill查看效果；字符类型的宽度超过则无法存储

(3) 枚举类型

定义：字段只能在列举的范围内选择
enum单选：字段名 enum(值1, 值2, …)，最多有65535个不同的值
set多选: 字段名 set(值1, 值2, …)， 最多有64个不同的值
示例：

(4) 日期时间类型

year: YYYY
date: YYYYMMDD
time: HHMMSS
datetime: YYYYMMDDHHMMSS
timestamp: YYYYMMDDHHMMSS

注意：
datetime不给值默认返回NULL
timestamp不给值默认返回系统当前时间

表字段的操作

语法: alter table 表名 执行动作;
(1) 添加字段(add)
添加字段：alter table 表名 add 字段名 数据类型;
添加到开始：alter table 表名 add 字段名 数据类型 first;
添加到指定位置：alter table 表名 add 字段名 数据类型 after 字段名;
(2) 删除字段(drop)
alter table 表名 drop 字段名;
(3) 修改数据类型(modify)
alter table 表名 modify 字段名 新的数据类型;
(4) 修改字段名(change)
alter table 表名 change 旧名 新名 数据类型;
(5) 修改表名
alter table 表名 rename 新表名;

表记录操作

名词：
DB(database):就是数据库，存储数据的仓库
DBMS(database management system): 数据库管理系统，管理数据库的软件MySQL，Oracle等
DBS(database system): 数据库系统，DBS=DB+DBMS+数据库的应用(财务管理系统，人事管理系统)+用户

where条件字句(配合查，改，删操作一起使用)

(1) 语法格式
select * from 表名 where 条件;

运算符操作(配合查询、修改、删除操作)

数值比较&字符比较

(1) 数值比较运算符：=, !=, >, >=, <, <=
(2) 字符比较运算符：=, !=
(3) 语法格式
查询：select * from 表名 where 字段名 运算符 数字/字符;
修改: update 表名 set 字段名=值, … where 字段名 运算符 数字/字符
删除：delete from 表名 where 字段名 运算符 数字/字符

逻辑比较

(1) 运算符
and(多个条件同时满足)
or(多个条件有一个条件满足就可以)

范围内比较

(1) 运算符：between and, in, not in
(2) 语法格式：
字段名 between 值1 and 值2
字段名 in (值1, 值2, …)
字段名 not in (值1, 值2, …)

匹配空，非空

空：is null
非空：is not null
注意：
(1) null: 空值，必须用is或者is not去匹配
(2) “”: 空字符串，用=或者!=去匹配

模糊比较

(1)语法格式：
字段名 like 表达式
(2) 表达式：
_ : 匹配单个字符
% ：匹配0到多个字符

SQL 查询

总结（执行顺序）
select … 聚合函数 from …
where …
group by …
having …
order by …
limit …;

order by

作用：对查询的结果进行排序
语法格式：order by 字段名 排序方式;
排序方式：
ASC(默认)：升序
DESC(降序)：降序

limit(永远放在SQL语句的最后写)

作用：限制显示查询记录的条数
用法：
limit n: 显示n条记录
limit m, n: 从第m条记录开始显示，共显示n条
m的值是从0开始计数的
limit 1, 3: 表示显示2，3，4三条记录

聚合函数

分类：
avg(字段名)：求字段所有值的平均值
sum(字段名)：求字段所有值的和
max(字段名)：求字段的最大值
min(字段名)：求字段的最小值
count(字段名)：求字段的个数, 空值NULL不会被count统计，""可以被count统计。

group by

作用：给查询的结果进行分组
注意：
(1)group by之后的字段必须要为select之后的字段
(2) 如果select之后的字段和group by之后的字段不一致，则必须要对select之后的该字段值做聚合处理

having

作用：对查询结果进行进行进一步筛选
注意：
(1) having语句通常与group by语句联合使用，用来过滤由group by语句返回的记录集
(2) having 语句的存在弥补了where关键字不能与聚合函数联合使用的不足，having操作的是聚合函数生成的显示列

distinct

作用：不显示字段的重复值
注意：
(1) distinct处理的是distinct和form直接的所有字段，所有字段值必须全都相同才能去重。
(2) distinct不能对任何字段做聚合处理

查询表记录是做数学运算

运算符：+, -, *, /, %

约束

作用：为了保证数据库表中数据的完整性、一致性、有效性
约束的分类：

1. 默认约束(default), 在插入记录时如果不给该字段赋值，则使用默认值
   格式:
   字段名 数据类型 default 值,
   
2. 非空约束(not null)
   作用：不允许将该字段设置为NULL
   格式：字段名 数据类型 not null
   

索引

定义：对数据库表中的一列或者多列的值进行排序的一种机构(MySQL中用Btree方式)
优点：加快数据的检索速度
缺点：
(1) 当对表中的数据进行增加、删除或修改的时候，索引也要动态维护，降低了数据的维护速度
(2) 索引需要占用物理空间
索引分类

1. 普通索引(index):

(1) 一个表中可以有多个index字段
(2) 字段的值可以有重复，且可以为NULL值
(3) 经常把做查询条件的字段设置为index字段
(4) index字段的key标志是MUL
创建索引：
创建表时创建：index(字段名1), index(字段名2)

在已有表中创建index：create index 索引名 on 表名(字段名);
索引名一般和字段名一样

查看普通索引：
(1) desc 表名; #查看key标志
(2) show index from 表名; # 显示索引详细信息
删除索引：
drop index 索引名 on 表名;

2. 主键索引(primary key)

1. 使用规则：
   (1) 一个表中只能有一个主键字段(primary)
   (2) 对应字段的值不允许重复，且不能为空
   (3) 主键字段的key标志PRI
   (4) 把表中能够唯一标识一条记录的字段设置为主键，通常把表中记录编号的字段设置为主键
2. 创建主键(primary key)
   (1) 创建表时创建
   字段名 数据类型 primary key,
   primary key(字段名)
   
   
   (2) 在已有表中创建
   alter table 表名 add primary key(字段名);
   
3. 删除主键
   alter table 表名 drop primary key;
4. 自动增长属性(auto_increment)
   作用：通常和主键字段一起配合使用
   创建：
   (1) 在创建表时创建
   字段名 数据类型 primary key auto_increment
   
   (2) 在已有表中添加自增长属性
   alter table 表名 modify 字段名 数据类型 primary key auto_increment;
5. 删除主键及自增长属性
   (1) alter table 表名 modify 字段名 数据类型;
   (2) alter table 表名 drop primary key;

3. 唯一索引(unique key)

使用规则：
(1) 一个表中可以有多个unique字段
(2) unique字段的值不运行重复，但可以为NULL
(3) unique的key标志是NUI
创建唯一索引unique
(1) 创建表示创建
unique(字段名1), unique(字段名2)
字段名 数据类型 unique
(2) 在已有表中创建
create unique index 索引名 on 表名;


删除唯一索引：
drop index 索引名 on 表名;

注意：index, unique 在删除时只能一个一个删

4. 外键索引(foreign key)

1. 定义：让当前表中的字段值在另一个表的范围内选择
2. 语法格式：
   (1)在创建表时：
   foreign key(参考字段名)
   references 被参考字段名(被参考字段名)
   on delete 级联动作
   on update 级联动作
3. 示例
   表1：缴费信息表(财务)
   学号 姓名 班级 缴费金额
   1 小猫猫 001 20000
   2 小小猫 002 30000
   表2：学生信息表(班主任)
   学号 姓名 缴费金额
   1 小猫猫 20000
   2 小小猫 30000
   
   (2)在已有表中创建外键：
   alter table 表名
   add foreign key(参考字段名) references 被参考表名(被参考字段名)
   on delete 级联动作
   on update 级联动作
   注意：在已有表中添加外键时会受到表中原有数据的限制
4. 删除外键
   alter table 表名 drop foreign key 外键名;
   外键名的查看方式：show create table 表名;
5. 级联动作：
   (1) cascade: 数据级联更新
   当主表删除记录时，如果从表有关联记录，则级联删除
   当主表更新被参考字段的值时，从表级联更新参考字段的值
   (2) restrict(默认)
   当主表删除记录时，如果从表中有向关联记录，则不允许主表删除
   更新同删除
   (3) set null
   当主表删除记录时，从表中向关联记录外键字段值变为null
   更新同删除
   (4) no action
   同restrict，都是立即检查外键限制
6. 外键的使用规则
   (1) 两张表被参考字段和参考字段的数据类型要一致
   (2) 被参考字段必须是KEY的一种，通常是primary key