MySQL操作备忘录

数据库

什么是数据库？

数据库是储存和管理数据的仓库。
数据库的本地访问

cmd进入数据库的bin目录，键入mysql -uroot -p输入密码访问。
数据库退出

\q
数据库的通用语法

注释：– 或#或/* */
SQL的分类
- DDL：操作数据库和表
- DML：操作数据库的数据
- DQL：查询数据库的数据
- DCL：授权

数据库的基本操作

操作数据库(DDL)

添加：create
- create database NAME; （创建名为NAME的数据库）
- create database if not exists NAME;（如果NAME不存在，则创建该数据库）
- create database NAME character set xxx;（创建名为NAME的数据库并指定字符编码为xxx）
删除：drop
- drop database NAME;（删除名为NAME的数据库）
- drop database if exists
  NAME;（如果NAME存在。则删除该数据库）
修改：alter
- alter database NAME character set xxx;（将NAME的字符编码修改为xxx）；
查询：show
- show databases;(查询所有数据库)
- show create database NAME;(查询NAME数据库)；
使用：use
- use NAME;（使用NAME数据库）
查询 select
- select database(); （查询正在使用的数据库）

操作表(DML)

添加：create
- create table NAME(LineName1 Data1,LineName2 Data2,……………………… ……………，LineNameN DataN); （创建名为NAME的表，表中有N列数据，每列数据对应数据类型）
- 复制表 | create table 复制 like 被复制; (复制一个NAME1的表，复制于NAME2)
删除：drop
- drop table 表名;（删除名为NAME的数据库）
- drop table if exists 表名;（如果NAME存在。则删除该数据库）
修改：alter

/* 修改 */
- alter table 表名 rename to new表名;（将表名更改为new表名）；
- alter table 表名 character set xxx;（将NAME表的字符编码修改为xxx）；
- alter table 表名 change 旧列名新列名数据类型;（将表中的一列修改为新名字和新的数据类型）；
- alter table 表名 modify 列名数据类型;（修改某列的数据类型）；
  
  /* 添加 */
- alter table 表名 add 列名数据类型;（在表中新增一列）;
  
  /* 删除 */
- alter table 表名 drop 列名;（删除表中的某一列）；
查询：show
- show tables;(查询该数据库下的所有表)
- desc 表名；(查询表的结构)
  数据库的基本类型

类型	含义
int	整数
double(格式)	小数
date	日期，仅包含年月日
datetime	日期，包含年月日时分秒
timestamp	时间戳类型，包含年月日时分秒，如果不对其赋值或赋值为null则默认使用当前的系统时间来自动赋值
varchar（length）	字符串

SQLyog的基本操作

DQL

添加：insert
- insert into 表名（Line1，Line2，Line3……，LineN）values（Values1，Values2，Values3……ValuesN）; （向表中的指定列加入指定数据，如果不指定列，那么就默认添加所有列的数据）
```
/* 除数字类型，其他的所有类型都要用双引号或者单引号括起来，年月日按照“Y-M-D”的格式。
```
删除：delete/truncate
- delete form 表名 where 条件;（删除表中所有符合条件的数据）
- delete form 表名;（删除表中所有的数据，每有一条数据，就会执行一次delete）
- truncate form 表名;（删除表中所有的数据，并新建一个新的空表）
修改：update
- update 表名 set 列名=修改的数据 where 条件（将符合条件的数据的指定列的数据修改）；
```
/* 如果不加条件，则会把所有的数据全部修改 */
```
查询：select
- select *from 表名; （查询表中的信息）
SQLyog查询
- 查询结果集去掉重复：distinct
- select distinct 列名 from 表名；（从表中查询指定列，并去除重复数据）
  -查询结果并计算：
- select 列1，列2，列1+列2 from 表名；（从表中查询列1，列2的信息并相加）
- select 列1，列2，列1+列2 新表头名 from 表名；（从表中查询列1，列2的信息，并运算，运算的数据储存在新表头名的新列中）
```
/* null与任何数据运算的结果都是null，所有当出现null时，要判断，此时使用ifnull（列名，值），当指定列中出现null时，用指定的新值来替换它。 */
```

条件查询：where

逻辑运算符:> / < / != / = / or / and ……………

select *from 表名 where 条件；

表示区间： between -- and --；
表示选择： age in（数1，数2，数3）; 
查询数据为（不为）null： where 数据 is （not） null；

模糊查询：like

select * from 表名 where name like “马%”；（查询姓马的人）；

占位符 _  表示一个字符， 百分号 % 表示多个字符。

排序查询：order by

select * from 表名 order by 列名 ASC/DESC ，列名 ASE/DESC；

默认排序方式是升序，要指定排序方式，在列名后加 ASC（升序）/DESC（降序），如果第一列数据相同，按第二列的数据继续排序。

聚合函数：
- count：select count （列名） from 表名；（查询表中某列有多少个元素）
- max：select max (列名) from 表名；（查询表中某列的最大值）
- min：select min (列名) from 表名；（查询表中某列的最小值）
- sum：select sum (列名) from 表名；（查询表中某列的总和）
- avg：select avg (列名) from 表名；（查询表中某列的平均值）

分组查询：group by

select 列名，聚合函数 from 表名 where 条件 group by 列；（查询表中的某列，运算，并根据列来分组）
```
having
```

对已分组的再判断

 select 列名，聚合函数 from 表名 where 条件 group by 列 having 条件；（对某条件进行分组查询后对查询结果进行再判断，不符合having条件的结果不予显示）
/* having与where的区别 */
   1.having后可以对聚合函数进行判断，而where不可以。
   2.having在分组后进行再判断。where在分组前进行判断。

分页查询：limit
- select *from 表名 limit 开始的数据，每页显示的数据条数；
```
limit每条开始的索引=（当前页码-1）*页数
```
  约束

非空约束：not null

在创建表时添加非空约束

create table 表名(
xxx yyy,
ddd qqq not null
）;

创建表完成后再添加非空约束

alter table 表名 modify 列名 数据类型 not null;

删除某列的非空约束

alter table 表名 modify 列名 数据类型;

唯一约束:unique

在创建表时添加唯一约束

create table 表名(
xxx yyy,
ddd qqq unique
）;

创建表完成后再添加唯一约束

alter table 表名 modify 列名 数据类型 unique;

删除某列的唯一约束
```
alter table 表名 drop index 列名;
```

主键约束：primary key （非空且唯一，一张表中只能有一个字段时主键，主键时表中记录的唯一标识）
- 在创建表时添加主键约束
```
create table 表名(
xxx yyy,
ddd qqq primary key
）;
```
- 创建表完成后再添加唯一约束
```
alter table 表名 modify 列名 数据类型 primary key;
```
- 删除某列的主键约束
```
alter table 表名 drop primary key;
```
自动增长：如果某一列是数值类型的，使用 auto_increment来完成数值的自动增长。
- 在创建表时添加自动增长
```
create table 表名(
xxx yyy,
ddd qqq primary key auto_increment
）;
```
- 创建表完成后再添加唯一约束
```
alter table 表名 modify 列名 数据类型 auto_increment；
```
- 删除某列的主键约束
```
alter table 表名 modify 列名 数据类型;
```

外键约束：foreign key

在创建表时添加外键

create table 表名(
…… ……
外键列
constraint 外键名称 foreign key （外键列名称） references 主表名称 （主列表名称）
）;

创建表完成后再添加外键

alter table 表名 add constraint 外键名称 foreign key （外键列名称） reference 主表名称（主表列名称）；

删除外键

alter table 表名 drop foreign key 外键名；

数据库的三大范式:

第一范式(1NF) :

每一列都是不可分割的原子数据项

第二范式(2NF) :

在1NF的基础上,非码性必须完全依赖于码(在1NF基础上消除非主属性对主码的部分函数依赖)

第三范式(3NF) ：

在2NF的基础上，消除传递依赖。

几个概念:
- 函数依赖: A–>B ,如果通过AN属性(属性组)的值，可以确定唯一B属性性的值。则称B依赖于A
  例如:学号– >姓名。
  (学号，课程名称) –> 分数
- 完全函数依赖: A–>B，如果A是一个属性组，则B的属性值得确定:要依赖于A性组中所有的属性值。
  例如: (学号，课程名称) –> 分数
- 部分函数依赖: A–>B，如果A是一个的性组，则B属性值得确定只需要依赖于A性组中某- -些值即可。
  例如: (学号，课程名称) – >姓名
- 传递函数依赖: A–>B, B – >C .如果通过A:属性(属性性组)的值，可以确定唯一B属性的值, 在通过B属性(属性组)的值可以确定唯一C属性的值，则称C传递函数依赖于A
  例如:学号–>系名，系名–>系主任
- 码:如果在一张表中，-个性或后性组,被其他所有属性所完全依赖，则称这个属性(加性组)为该表的码
  例如:该表中码为: (学号，课程名称)
```
* 主属性:码属性组中的所有属性
* 非主属性:除过码屁性组的属性
```

数据库的备份与还原

在命令行界面操作备份与还原
- 备份: mysqldump -u用户名 -p用户密码要备份的数据库名称 > 备份保存路径
- 还原: 1.登陆数据库 2.创建数据库 3.使用数据库 4.执行 source
```
1.create database NAME 2.use NAME 3.source 备份文件目录
```
  多表查询

内连接查询

隐式内连接：SELECT * FROM class,student WHERE class.class_id=student.class;

select 某表的某个列 ……… from 表名 …… where 条件;
在查询时，两个子集的查询结果会呈现A*B个数个结果，这时需要使用where限定条件来消除无用数据。

显式内连接：select * from 表1 inner join 表2 on 条件;

SELECT class.`class_name`,student.`name` FROM class INNER JOIN student ON class.`class_id`=student.`class`;

外连接查询

左外连接: select 查询的值 from 表1 left outer join 表2 on 条件;
```
查询的是左表所有数据和要查询的数据的交集
```

右外连接: select 查询的值 from 表1 right outer join 表2 on 条件;

查询的是右表所有数据和要查询的数据的交集
  /* join左边的就是左边,右边的就是右表 */

子查询(嵌套查询)
- 子查询作为条件:SELECT * FROM student WHERE score < ( SELECT AVG(score) FROM student);
- 子查询作为条件用IN来判断: SELECT * FROM student WHERE NAME IN (SELECT NAME FROM student WHERE NAME=’张三’ OR NAME = ‘李四’ );
- 子查询作为一张新的虚拟表来进行查询 SELECT * FROM student, ( SELECT AVG(score) FROM student) WHERE score < 80;

事务

事务开启: start transaction
事物提交: commit;
事务回滚: rollback;

事物的自动提交与手动提交

查询提交方式 select @@autocommit; 结果为1代表自动提交, 结果为0代表手动提交.
设置提交方式 set @@autocommit = 1; 设置为自动提交

事务的四大特征:

1.原子性:是不可分割的最小操作单位，要么同时成功，要么同时失败。

2.持久性:当事务提交或回滚后，数据库会持久化的保存数据。

3.隔离性:多个事务之间。相互独立。

4.一致性:事务操作前后，数据总量不变

事务的隔离级别(了解)

 概念:多个事务之间隔离的，相互独立的。但是如果多个事务操作同-批数据，则会引发一 些问题，设置不同的隔离级别就可以解决这些问题。
      存在问题:
      1.脏读:一个事务，读取到另一个事务中没有提交的数据
      2.不可重复读(虚读) :在同-个事务中，两次读取到的数据不-样。
      3.幻读:一个事务操作(DML )数据表中所有记录，另一个事务添加了-条数据，则第一个事 务查询不到自己的修改。
       隔离级别:
      1. read uncommitted :读未提交
        产生的问题:脏读、不可重复读、幻读
      2. read committed :读已提交
        产生的问题:不可重复读、幻读
      3. repeatable read :可重复读(MySQL默认方式)
        产生的问题:幻读
      4. serializable :串行化
        可以解决所有的问题
        从上到下,执行效率越来越低.
- 查询数据库的隔离级别
      select @@tx_isolation;
- 设置数据的隔离级别
      set global tr ansaction isolation level;

DCL管理员

管理用户：
- 添加用户：
  语法：CREATE USER‘用户名‘@’主机名’IDENTIFIED BY ‘密码’;
- 删除用户：
  语法:PROP USER ‘用户名‘@”主机名’;
- 修改用户密码：
  UPDATE USER SET PASSWORD = PASSWORD( ‘新密码’) WHERE USER = ‘用户名’;
  SET PASSWORD FOR ‘用户名‘@’主机名’= PASSWORD( ‘新密码’);

在MySQL中忘记了root用户的密码。
    1. cmd -- > net stop mysql 停止mysq1服务。
        (需要管理员运行该cmd)

    2. 使用无验证方式启动mysq1服务: 
        mysqld -- skip- grant -tables
    3. 打开新的cmd窗口,直接输入mysql命令，敲回车。就可以登录成功！
    4. use mysql;
    5. update user set password = password( ‘你的新密码') where user = ' root' ;
    6. 关闭两个窗口
    7. 打开任务管理器，手动结束mysqld. exe的进程
    8. 启动mysq1服务
    9. 使用新密码登录。

查询用户：
- 1.切换到mysq1数据库USE mysql;
- 2.查询user表
  SELECT * FROM USER;
  通配符: %表示可以在任意主机使用用户登录数据库
权限管理
- 查询权限：SHOW GRANTS FOR ‘用户名‘@’主机名’;
- 授予权限：grant权限列表 on 数据库名.表名 to ‘用户名‘@’主机名’;
```
给张三用户授予所有权限，在任意数据库任意表上:GRANT ALL ON *.* TO ' zhangsan '@' localhost';
```
- 撤销权限:revoke 权限列表 on 数据库名.表名 from ‘用户名‘@主机名’;

数据库

数据库的基本操作

数据库的基本类型

SQLyog的基本操作

DQL

约束

数据库的三大范式:

数据库的备份与还原

多表查询

事务

DCL管理员