MySQL入门

数据库简介

用途：用于存储生活的一切数据，如：身份、住房、车票、网站、…
概念：数据库服务器、数据库、数据表、一行数据(一条)、一列数据(字段)
分类：
- 关系型数据库：MySQL、oracle、SQL Server、…
- 非关系数据库：Redis、MongoDB、…
SQL：Structured Query Language，结构化查询语言
命令：
- 数据定义语言(DDL)：创建、删除、修改库或表
- 数据操作语言(DML)：增、删、改
- 数据查询语言(DQL)：查
- 数据控制语言(DCL)：授权、取消授权
- 数据事务语言(DTL)：开启事务、提交事务、操作回滚

MySQL安装

安装过程
- 双击MySQL安装程序：mysql-installer-community-5.7.20.0.msi
- 选择自定义安装：custom
- 选择安装的版本：MySQL Server5.7.20 -X64
- 点击Excute执行安装
- 设置root密码
- 点击Excute执行配置
- 点击Finish完成安装
成功测试
- 查看系统服务：services.msc
- 连接测试：mysql -h host -u root -p
  - -h：指定主机
  - -u：指定用户
  - -p：指定密码，建议输入密码时分两步，这样可以隐藏密码
  - 示例：mysql [-h127.0.0.1] -uroot -p，敲回车，然后根据提示输入密码即可
    - 端口：3306
    - 退出：exit
    - 帮助：help 或 \h
    - 说明：几乎所有命令都是以’;’结束
敲mysql命令无反应
- 说明：那是因为没有将该命令所在目录添加到系统环境变量Path下
- 添加：
  - 打开系统环境变量设置页面
  - 将mysql命令所在的目录添加到系统环境变量Path下
  - 保存即可
  - 新添加的环境变量在已经启动的终端是无效，重启一个新的终端即可

数据定义语言(DDL)

查看库：show databases;，会显示当前服务器上所有的数据库
创建库：create database test;，创建数据库test
删除库：drop database test;，删除数据库test
选择库：use test;，选择数据库test
- 查看当前使用的数据库
  - show tables;
  - select database();
查看表：show tables;，查看当前数据库下的所有数据表
创建表：create table user(username varchar(20), password char(32));
查看表结构：desc user;
查看创建方式：
- 查看库：show create database test;
- 查看表：show create table user;
删除表：drop table user;
指定字符集：
- 创建时指定：create table xxx() charset=utf8;
修改字段：
- alter table user modify username varchar(30);，不能修改字段名
- alter table user change email em varchar(32);，可以修改字段名
添加字段：
- 默认在最后添加：alter table user add age int(3);
- 在指定字段后添加：alter table user add email varchar(60) after password;
- 在开头添加字段：alter table user add id int(11) first;
删除字段：alter table user drop age;
修改字段位置及名称：add/modify/change
- alter table user modify em varchar(32) first;
修改表名：alter table user rename new_user;

sqlyog配置链接虚拟机步骤:
1.进入数据库

grant all privileges on . to root@’%’ identified by ‘123456’ WITH GRANT OPTION;
3.重启数据库服务 service mysql restart
4.sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
将bind_address = 127.0.0.1注释掉
5.再去重启服务
6.打开主机的sqlyog 链接数据库

数据类型

整型：tinyint(1个字节)、smallint(2个字节)、mediumint(3个字节)、int(4个字节)、bigint(8个字节)
浮点型：
- float(m, d)：单精度浮点型，4个字节，m表示总位数，d表示小数位数
- double(m, d)：双精度浮点型，8个字节，m表示总位数，d表示小数位数
- decimal(m, d)：以字符串的形式存储浮点数，多用于金融相关应用
字符串：
- char：定长字符串，0~255个字节
- varchar：变长字符串，0~65535个字节
时间日期：
- date：日期，格式：2018-11-05
- time：时间，格式：15:52:52
- datetime：日期时间，格式：2018-11-05 15:52:52
- timestamp：时间戳
- year：年，只占用一个字节，年份：1901~2155
符合类型：
- set：集合类型，格式：set(s1,s2,…,s63)，最多63种可能
- enum：枚举类型，格式：enum(e1,e2,…,e65535)，最多65535种可能
字段修饰：
- unsigned：无符号数
- zerofill：高位0填充，防止出现负数
- auto_increment：自动增加(1)，用于整数，经常与主键结合使用
- default：默认值
- not null：不能为空
字符集：
- 查看系统支持字符集：show character set;，通常我们只使用utf8
存储引擎：
- 查看系统支持存储引擎：show engines;
- 常用存储引擎：InnoDB和MyISAM

索引

说明：简单理解就是一本书最前面的目录，可以提高读取效率，但也不是说越多越好。
分类：
- 普通索引(index)：最基本的索引
- 唯一索引(unique)：修改的字段不能重复
- 主键索引(primary key)：是一种特殊的唯一索引，一张表中最多只能有一个字段设置
- 全文索引(fulltext)：对全局数据进行添加索引

示例：

alter table user add index(em);                # 给em字段添加普通索引
alter table user add unique(username);        # 给username字段添加唯一索引
alter table user add primary key(id);        # 给id字段添加主键索引
alter table user drop index em;                # 删除em字段的普通索引

创建表时直接指定：

create table user(
    id int auto_increment,
    name varchar(20),
    primary key(id),
    unique(name)
)engine=innodb default charset=utf8;

数据操作语言(DML)

说明：在大多数的操作中，使用的都是增删改查操作(CURD)。

准备：一张用于测试的表

mysql> create table star(
    -> id int auto_increment,
    -> name varchar(20) not null,
    -> money float not null,
    -> province varchar(20) default null,
    -> age tinyint unsigned not null,
    -> sex tinyint not null,
    -> primary key(id)
    -> )engine=innodb default charset=utf8;

插入数据：
- 方式1：不指定字段，添加数据时需要写完整所有的字段
  
  insert into star values(1, ‘刘亦菲’, 20000000, ‘武汉’, 28, 1);
可以一次性插入多条数据，一条数据需要使用一个()包括起来。
- 方式2：指定字段，只需要传递指定字段的值
  
  insert into star(name, money, age, sex, province) values(‘赵丽颖’, 8000000, 31, 1, ‘河北’);
插入数据的顺序与前面指定的字段名要一致。
- 注意：插入数据时可以不传值的字段
  - 自增的字段
  - 有默认值的
  - 可以为空的
- 说明：插入数据时，第二种方式使用的比较多。
查询数据：select * from star;
修改数据：update star set age=31, money=10000000 where id=1;
- 警告：修改操作一定不要忘了指定条件，否则后果自负。
删除数据：delete from star where id=2;
- 警告：删除操作一定不要忘了指定条件，否则后果自负。

数据查询语言(DQL)

基础查询：select * from star;
指定字段查询：select name, money, province from star;
删除重复记录：select distinct province, sex from star;
- 使用distinct指定的字段不能重复，指定多个字段时，字段组合不能重复

指定条件查询：

条件

条件	说明
>	大于
>=	大于等于
<	小于
<=	小于等于
=	等于
!= 或 <>	不等于
and	并且
or	或者
[not] between m and n	[不]在区间[m, n]区间
[not] in ()	[不]在指定集合中
[not] like 条件	模糊匹配，%表示任意字符

模糊匹配 _是单字符匹配 %是多字符匹配

示例：

select from star where age > 30;
select from star where age > 30 and sex=1;
select from star where age > 30 or province=’河南’;
select from star where age [not] between 30 and 40;
select from star where id [not] in(1,3,5);
select from star where province like ‘%北%’;

结果集排序：
- 示例：
  
  select from star order by age asc;
  select from star order by money desc;
- order by：指定排序字段
- asc：升序，默认方式
- desc：降序
- 多字段排序：当前面字段值一样时，按照后面指定的字段进行排序
  
  select * from star order by sex asc, age desc;
限制结果集：
- limit：用于限制结果集的数量
- 示例：
  
  select from star limit 5; # 取前5条数据
  select from star limit 5 offset 2; # 偏移2条，取5条数据
  select * from star limit 2, 5; # 功能同上
  省略offset的写法就是将偏移量放到前面将限制每页条数的放在后面
- 分页查询：
  
  每页10条数据,用pageSize，page是当前页面，请写出对于页码的查询条件
  第一页：limit 10
  第二页：limit 10, 10
  第三页：limit 20, 10
  page页：limit (page-1) * pageSize, pageSize
常用聚合函数：

函数说明

count 统计个数

sum 求和

avg 平均值

max 最大值

min 最小值
- 示例：
  
  select count(*) as c from star;
  select max(money) as max_money from star;
as可以给查询结构字段起别名
分组及过滤：
- 示例：
  
  select from star group by sex; # 只分组，结果集中每个组中只有一条数据
  select count() as c, sex from star group by sex; # 分组并统计信息
  select count(*) as c, province from star group by province having c>1;
  having后面做的是分组后的显示过滤
分组后条件不能使用where，只能使用having

函数	说明
count	统计个数
sum	求和
avg	平均值
max	最大值
min	最小值

为什么group by后面不能跟where:

where 是对数据集进行筛选；
group by 是对结果集进行聚集；
也就是说 group by 是在 where 筛选之后进行聚集。
所以 group by 必须跟在where 的后面

创建多表关系:

FOREIGN KEY(o_buyer_id) REFERENCES s_user(u_id),
FOREIGN KEY(o_seller_id) REFERENCES s_user(u_id)
ALTER TABLE EMP ADD CONSTRAINT FOREIGN KEY EMP(DEPTNO) REFERENCES DEPT (DEPTNO);

多表联合查询

隐式内连接：没有出现join关键字
- 示例：select username, name from user, goods where user.gid=goods.gid;
- 说明：查看哪个用户购买了哪个商品
显式内连接：SQL语句中出现了join关键字
- 示例：select username,name from user join goods on user.gid=goods.gid;
- 说明：功能同上
- join前可以添加inner或cross关键字，可以省略
左外连接：以左表为主，left [outer]
- 示例：select username, name from user left [outer] join goods on user.gid=goods.gid;
- 说明：显示左表所有数据，右表有对应就显示数据，没有对应显示NULL
右外连接：以右表位住，right [outer]
- 示例：select username, name from user right [outer] join goods on user.gid=goods.gid;
- 说明：显示有表所有数据，左表有对应就显示数据，没有对应显示NULL

子(嵌套)查询

select * from user where gid in (1,3,5);
select * from user where gid in (select gid from goods);

记录联合
- 语法：select 语句1 union select 语句2
- 说明：
  - union all：将两边查询结果直接拼在一起
  - union：是union all的去重结果
联合更新数据
- 示例：update user u, goods g set u.gid=0, g.price=g.price-200 where u.gid=g.gid and u.id=7;
- 说明：可以给操作的表起别名，还可以对字段的原有只进行运算

事务处理语言(DTL)

说明：测试的表的存储引擎要支持事务(InnoDB)
开启事务：禁止自动提交
- set autocommit=0;
操作回滚：通常在出现操作异常时使用
- rollback;
提交操作：整个事务过程中没有问题
- commit;

开启事务:

start transaction

提交事务:

commit

开启事务:

start transaction

回滚操作:

rollback

数据控制语言(DCL)

查看授权：
- 格式：show grants [for 'user'@'host'];
- 示例：show grants for 'root'@'localhost';
- 说明：查看当前登录用户授权时可以不指定用户及主机
创建用户：
- 格式：create user 'user'@'host' identified by 'password';
- 示例：create user 'test'@'10.8.156.%' identified by '123456';
- 说明：%表示通配符，任意的
用户授权：
- 格式：grant 权限 privileges on 库名.表名 to 'user'@'host' identified by 'password';
- 示例：grant all privileges on *.* to 'test'@'10.8.156.%' identified by '123456';
- 说明：权限可以是select、delete、insert、update等，all表示所有权限；*表示所有
刷新权限：flush privileges;
取消授权：
- 格式：revoke 权限 privileges on 库名.表名 from 'user'@'host';
- 示例：revoke delete privileges on test.* from 'test'@'10.8.156.%';
- 说明：收回当前局域网内的test用户在test库下所有的表的删除权限
删除用户：
- 格式：drop user 'user'@'host';
- 示例：drop user 'test'@'10.8.156.%';
linux下远程登录：
- bind-address=127.0.0.1 改为：bind-address=0.0.0.0
- 添加指定用户在指定主机的操作权限：
  
  grant all privileges on . to ‘root’@’%’ identified by ‘123456’;