Mysql指令详细介绍

1. Mysql

1.1 服务的开启和关闭

密码是root
//在管理员的模式下打开cmd
net stop mysql  关闭服务
net start mysql 启动服务

1.2 Mysql的登陆和推出

MYSQL登陆

mysql -uroot -proot
//u表示用户名
//p表示密码
第二种登陆方法
mysql -uroot -p 然后输密码
第三种
mysql --host=ip地址 --user=root --password=root

访问远程的数据库

mysql -h127.0.0.1 -uroot -proot
//host代表ip地址

推出

exit
quit

1.3MYSQL安装目录

1. 安装目录

   配置文件my,ini

2. 数据目录

   数据库：文件夹

   表：文件

   数据：写入的数据

1.4 如何操作数据

SQL:structure query language 就是操作所有关系型数据库的规则。

什么是关系型数据库呢：Oracle也是关系型的数据库。每一钟数据库操作起来略微的差距叫做方言。

2.SQL 语句

2.1 SQL通用语法

1. MySQL语句可以单行或者单行书写，以分号书写。只有遇到分号才会结束

2. 使用空格和tab键来提高可读性

3. 不区分大小写，关键字建议使用大写

4. 三种注释

   1. 单行注释：

      – 两个横一个空格

      #一个警号

   2. 多行注释 / * 这是一个注释 */

2.2 SQL的分类

1. DDL :（data definition language)

   操作数据库和表的.关键字：create drop alter

2. DML：data manipulation language

   增删改,关键字：insert delete update

3. DQL:data quert language

   查询表中的数据关键字：select where

4. DCL：data control language

   授权操作 定义数据库的访问权限和安全级别，以及创建用户，关键字GRANT,TEVOKE

2.3 DDL:操作数据库，表

操作数据库：CRUD

1. create
2. retrieve
3. update
4. delete
5. 使用数据库

2.3.1 数据库的查看创建

show databases; 
show create datebase mysql;#查看创建mysql这个数据库的语句。mysql是文件夹的名字

create database db1; -- 创建一个db1的数据库
-- 为了避免重复我们可以写一个判断语句
create database if not exists db1;

指定字符集创建数据库

create database db3 character set gbk;

判断之后然后指定字符集

create database if not exists db4 character set gbk; -- 先判断在决定字符集

2.3.2 修改删除数据库操作

修改数据库字符集

alter database db3 character set gbk;

删除数据库

drop database db3;

先判断存在在删除

drop database if exists db4;

使用数据库：怎么进入到数据库

查询当前正在使用的数据库

select database();

进入文件夹

use db1;

2.3.3操作数据表

查询某个数据库所有表的名称

show tables;

查看表的结构

desc table; -- table 为表名

创建表

数据类型：

1. int

2. double(5,2)一共有五位小数有两位999.99

3. date:只包含暖月日yyy–MM–dd

4. datetime :包含年月日时分秒yyy–MM–dd HH–mm–ss

5. timeStamp 时间错类型跟上面一样如果不赋值或者时间为null设置为当前时间

6. 字符串类型 varchar

   name varchar(20)指定最大字符长度

create tables 报名（列明1 数据类型，列名2 数据类型）;

表的复制

create table stu like student; 

删除表

drop table 表名;
drop table if exists 表名;

修改表的名称

1. 修改表名
2. 修改表的字符集
3. 添加列名称类型
4. 删除列

修改表名

alter table 类名 rename to 新的类名;

修改表的字符集

alter table 表名 character set 字符集名;

添加一列

alter table 表名 add 列明 数据类型;

改表名

alter table 表名 change 原列明 新列名 新类型;

改列的数据类型

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

删除列

alter table 表名 drop 列名;

2.4 SQLyoung图形化软件

2.5 DML

1. 添加数据

   insert into 表名(列名1，列名2，列名3) values(值1，值2，值3)；--- 列名可以省略意思是给所有的列添加值，列名必须和值一一对应并且数据类型也要写对不然报错

2. 删除数据

   如果不加条件就会删除所有的数据

   delete from stu;
   # 但是还是不建议这么删除表
   truncate table stu;-- 表示删除这个表然后建立一个和表一模一样的表。效率更高

   按照条件删除数据

   delete from stu where number="1";

   修改数据

   如果不加条件会将表里面的所有数据都进行修改

   update 表名 set 列名1 = 值1，列名2 where number =10 ;

2.6 DQL语句

2.6.1 基本查询操作

1. 语法

select 
	字段列表(列名)
from
	表名列表
where
	条件列表
group by
	分组字段
having 
	分组之后的条件
order by
	排序
limit
	分页限定

2. 去重操作

   表里面结果去除掉重复的distinc

   select distinct name from class1;

3. 可以计算操作

   select name,math,english, math+english from class1;

4. 计算时候如果遇到了null 结果还会是null这个很不合理因此我们需要处理一下

   select name math,english,math+ifnull(english,0) from class1;
   -- 这个ifnull(a,b)表示判断a是否为null如果是的话将他的值当作b

5. 取别名

   select name math,english,math+ifnull(english,0) as 总分 from class1;
   -- 这里会将加合的那一行的值列名设置为总分,也可以省略as不写

2.6.2 where查询语句

1. where子句后面跟条件。

2. 运算符号

   判断相等只有一个等号

   不等于 ！= 或者<>

   与：&&或者and(推荐使用)

   between：在某某之间

   or : 或者

   in: 写一个集合然后判断在集合里面就可以实际上就是or

   is null: 判断是否为null不可以使用等号应该使用is null

   like:模糊查询

   1. 占位符

   _:一个占位符

   %：多个占位符

   查询范围

select * from class1 where age between 20 and 30;
-------------等价于下面的语句
select * from class1 where age>20 and age<30;

select * from class1 where age in (23,32,43);
-----等价于
select from class1 where age =23 or age = 32 or age = 43;

查询英语成绩缺考的同学

select * from class where english is null;
---- 下面是不缺考的同学
select * from class where english is not null;

like模糊查询

SELECT * FROM class1 WHERE NAME LIKE "马%";-- 查找姓马的人
select * from class1 where name like "_化%";-- 查找第二个字是化的人
select *from class1 where name like "___"; 查找姓名为三个字的人
SELECT  * FROM class1 WHERE NAME LIKE "%马%";-- 查询姓名中有马的人

2.6.3 排序

排序方式

ASC:升序

DESC 降序

order by 排序字段1，排序字段2，排序方式，--默认按照升序排序

SELECT * FROM class1 ORDER BY math ASC,english ASC;-- 先按照数学排名如果有重名就按照英语成绩排序

2.6.4 聚合函数

聚合函数：将数据作为一个整体，进行纵向的计算。

1. count：计算个数 排除null选择不包含非空的列来统计，选择主键或者可以写count(*)但是在公司里面不推荐写星号
2. max：最大值
3. min：：最小值
4. sum：求和
5. avg:计算平均值

注意：聚合函数的操作都会排除null的个数

计算个数

select count(name) from class1;
----下面来写加上null统计
select count(ifnull(english,0)) from class1;

2.6.5 分组查询

统计具有相同特征的某一类数据,出现的应该是整体的统计信息而不是个人的。se’lect 后面应该写的是聚合函数或者后面聚合的字段

--语法
group by 

SELECT sex, AVG(english),count(id) FROM class1 GROUP BY sex  ;限定条件进行分组只有达到条件的才有资格进行分组

SELECT sex, AVG(english) FROM class1 WHERE english>70 GROUP BY sex  ;

对聚合函数之后的结果进行限定筛选用having

select sex avg(english) from class1 where english>70 group by sex having count(id)>1;

用别名来简化操作

select sex, avg(english),count(id) as 人数 from class1 where english>70 group by sex having 人数>1;

where 和having的区别

1. where在分组之前进行限定不满足条件不参加分组。having是在分组之后进行限定不满足条件不会进行查询显示
2. where后面不可以接聚合函数

2.6.6 分页查询

语法 分页操作是一个方言，mysql特有的语法

limit 开始索引，每页查询的条数

开始的索引 = （页码—1）*每页显示的

SELECT * FROM class1 LIMIT 0,3;

2.7 约束

概念：对表中的数据进行限定，保证数据的正确性，有效性和完整性。

分类

1. 主键约束：primary key

2. 非空约束: not null

3. 唯一约束: unique

4. 外键约束: foreign key

2.7.1 非空约束

1. 创建表的时候日添加约束

   create table student (id int,name varchar(20) not null);

   – 创建表之后添加约束

   alter table student modify name varchar(20) not null;

2. 删除约束

   alter table stuent modify name varchar(20);

2.7.2 唯一约束

1. 创建表时添加唯一约束

   create table student (id int, number varchar(30) unique);

   但是可以同为null

   – 创建之后添加唯一约束

   alter table student modify number varchar(20) unique;

2. 删除唯一约束（唯一约束称为唯一索引)

   alter table student drop index number;

2.7.3主键约束

主键约束代表非空且唯一，表中只可以有一个主键。

1. 创建表时添加主键约束

   create table stuent(id int primary key, name varchar(20));

2. 删除主键

   alter table student drop primary key;

3. 创建完之后添加主键

   alter table student modify name primaty key;

4. 自动增长

   概念：如果某一列是数值类型使用auto_increment 可以完成值自动增长，只跟上衣条数据相关。自动增长一般都和主键一起使用。

   create table student(id int primary key auto_increment,name varchar(20));

   CREATE TABLE student(id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(29));
   INSERT INTO student VALUE(NULL,"雷军");
   -- 这个主键会自动增加

   删除自动增长

   alter table student modify id int;--- 这样可以删除自动增长但是没办法删除主键

2.7.4 外键约束foreign key

语法

create table 表名（
...
	外键列
	constraint 外键名称 foreign 外键列名称 reference 主表名称(著列表名称); 

1. 创建表之后删除外键

   alter table employee drop foreign key 外键名称--之前随意取得名称，不可以重复

2. 添加外键

   alter table employee add constraint emp_dep_fk foreign key reference dep(id);

3. 级联操作

   alter table employee add constraint emp_dep_fk foreign key reference dep(id) on update cascade;
   

   级联删除

   alter table employee add constraint emp_dep_fk foreign key reference dep(id) on delete cascade;--级联删除和级联更新可以同时添加

2,8数据库的设计

1. 多表之间的关系

   1. 一对一

      例如任何身份证

   2. 一对多或者多对一

      例如：一个部门有多个员工，一个员工只有一个部门

   3. 多对多

      一个学生可以有多个课程，一个课程也有多个学生。

2.8.1 多表关系一对多的表

实现方式:再多的一方建立外键指向一的主键。

比如员工和部门，员工是多的一方，我们在员工这边建立一个外键来指向部门的主键编号

2.8.2 多对多的关系表

学生和课程就是多对多的关系。需要借助第三张表来解决这个问题。第三个表两个字段：第一个用学生id第二个用课程的id 都是各自表的主键。这样就实现了多对多的问题。

2.8.3 一对一的关系

可以在任意的一方添加外键指向另一方的主键，并且让外键唯一。如果是一对一 的关系最好的方式就是合成一张表。

USE db1;
CREATE TABLE category(
	cid INT PRIMARY KEY AUTO_INCREMENT,
	cname VARCHAR(100) NOT NULL);
CREATE TABLE route(
	rid INT PRIMARY KEY AUTO_INCREMENT,
	rname VARCHAR(100) NOT NULL UNIQUE,
	price DOUBLE,
	rdate DATE,
	cid INT,
	FOREIGN KEY (cid) REFERENCES category(cid)
	);
CREATE TABLE tab_user ( 
	uid INT PRIMARY KEY AUTO_INCREMENT,
	username VARCHAR(100) UNIQUE NOT NULL,
	PASSWORD VARCHAR(100) NOT NULL,
	NAME VARCHAR(100),
	birthday DATE,
	sex CHAR(1) DEFAULT '男',
	telephone VARCHAR(100),
	email VARCHAR(100)
	);
	
CREATE TABLE tab_favorite(
		rid INT,
		uid INT,
		PRIMARY KEY(rid,uid),
		FOREIGN KEY(rid) REFERENCES route(rid),
		FOREIGN KEY(uid) REFERENCES tab_user(uid)
		);
	

2.9范式

概念：设计数据库时遵从不同的规则要求，设计出合理的关系数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小

目前有六钟:

第一范式

第二范式

第三范式

巴斯-可得范式

第四范式

第五范式

分类:

1. 第一范式:

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

2. 第二范式:

   在第一范式的基础上非码属性必须完全依赖于候选码,(在1nf的基础上消除非主属性对主码的部分函数依赖)

   几个概念:

   1. 函数依赖

      A–B B由A唯一确定

   2. 完全依赖

      A-B B 由A的所有属性完全确定

   3. 部分依赖

      B由A的部分属性确定

   4. 传递依赖

      B依赖C,C依赖A A被C传递依赖

   5. 码:

      如果在一张表中一个属性或者属性组被其他属性完全依赖,则这个属性(属性值)为该表的码.

      主属性:码属性组中的所有属性

      非主属性:除过码属性组的属性

   6. 第三范式:

      在2NF的基础上,任何非主属性不依赖于其他非主属性(在2NF的基础上消除传递依赖)

2.9 数据库的备份和还原

保证数据的安全每天都要备份一下

1. 命令行的备份操作

mysqldump -rusername -ppassword 指定数据库的名称> 保存的路劲

2. 还原的语法

   1. 先创建一个数据库
   2. 然后使用这个数据库
   3. 然后使用source 加备份的文件

   create database db1;
   use db1;
   source +路劲;-- 
   

2.10 多表查询

笛卡尔积:取A和b的所有组合情况,但是会存在一些无用的数据我们要做的操作就是消除无用的数据.消除无用的笛卡尔积.

分类

1. 内连接查询
2. 外连接查询
3. 子查询

2.10.1 内链接查询(查询的是交集部分)

1. 隐式内连接

   select * from ept dpt where eid=pid;

2. 语法

   select 
   	t1.name,-- 方便写注释
   	t1.gender,
   	t2.names --指定哪个表的内容
   from 
    	emp t1, dep t2-- 取一个别名
   where t1.id=t2.id;

3. 显示内连接

   --- 语法跟上面的这个1的结果一模一样呀
   select 字段列表 from 表名1 inner join 表名2 on 条件

4. 内连接查询的注意事项

   1. 从哪些表中查询

   2. 确定条件

   3. 查询哪些字段

2.10.2 外链接查询

左外连接:查询的式左表的所有部分以及交集的部分

select 字段列表 from 表1 left [outer] join dept on 条件

右外连接:查询的是右表的所有部分加上交集部分

select 字段列表 from 表1 right [outer] join dept on 条件

两个其实只需要掌握一个就可以了通常采用左外.

2.10.2 子查询

概念:查询中嵌套查询,嵌套的哪个叫做子查询.

一条sql完成查询工资最高的返回其基本信息

1. 子查询的结果是单行单列的

   select * from emp where emp.salary =(select max(salary) from emp);

   子查询可以当作条件使用运算符

2. 子查询的结果是多行单列的

   子查询也可以当作条件,可以使用运算符in进行条件判断

   select *from emp where dep_id in (select id from dept where name ="财务部" or name = "市场部");

3. 子查询的结果是多行多列的.

   可以将查询到的结果当作一张虚拟表进行操作:使用多表查询你.注意使用别名来简化操作

   select * from dept t1 , (select * from emp where joindate > "2011-1-11") t2 where t1.id=dept_id

2.11DCL

管理用户

1. 查询用户

   use mysql;
   SELECT * FROM USER;-- 通配符%表示可以在任意主机登陆
   

2. 创建用户的基本语法

   create user 'usernaem' @'主机名' identified by "密码";
   

   create user 'usernaem' @'主机名' identified by "密码";
   

3. 删除用户

   drop user "username" @'主机名'；
   

4. 修改密码

   update user set password = password(新密码) where user="";
   

   -- 第二种方式修改密码
   set password for "username"@"主机号" = password();
   

5. 忘记密码操作

   1. cmd -->net stop mysql
   2. 使用无验证方式启动MySQL
   	mysqld --skip-grant-tables
   3. use mysql
   4. set password for 'root'@'localhost' = password(新密码)
   5. 关闭窗口
   6. 关闭服务
   7. 重新登陆
   

权限管理

1. 查询权限

   show grants for "用户名"@"主机名"

2. 授予权限

   GRANT SELECT，delete,update db1.stu FOR "用户名"@"主机号";-- 只可以查询表

   --授予所有权限
   grant all *.* for "usename"@"主机号"

3. 撤销权限

   revoke 权限列表 数据库名.表名 from "username"@"主机名";
   

三. 事务

概念：如果一个包含多个步骤的业务操作被事务管理，那么这些操作同时成功或者同时失败.避免转账一方失败

CREATE TABLE bill(id INT PRIMARY KEY AUTO_INCREMENT, balabce DOUBLE);

INSERT INTO bill VALUES(1,1000),(2,1000);
UPDATE bill SET balabce=1000;
SELECT * FROM bill;

START TRANSACTION;
UPDATE bill SET balabce=balabce-500 WHERE id=1;

UPDATE bill SET balabce=balabce+500 WHERE id=2;

ROLLBACK -- 如果发生错误就回滚到开启事务处
COMMIT -- 如果不发生错误就提交

mysql默认自动提交事务，如果你开启事务不手动提交的话默认回滚 。一条DML会自动提交一次

Oracle默认手动提交

1. 查看默认提交模式

   select @@autocommit; -- 0 代表手动提交 1 代表自动提交
   set @@autocommit =0 ;-- 修改为手动提交
   

3.2 事务的四大特征

1. 原子性：不可分割的最小分割单位。
2. 持久性：如果事务一旦提交或者回滚之后，数据会持久化的被保存
3. 隔离性：多个事物之间，相互独立。
4. 一致性：事务操作前后总量不变。

3.3事务的隔离级别

概念：多个事物之间，相互隔离独立的。但是如果多个事务操作同一批数据则会引发一些问题，设置不同的隔离级别可以解决这些问题

1. 脏读：一个事务读取到另一个事务没有提交的数据
2. 虚读：不可重复度在同一个事务中两次读取到的数据不一致.
3. 幻读：一个事务操作(DML)数据表中所有记录。另一个事务却添加了一个数据，则第一个事务查询不到自己的修改。

四种隔离级别：

1. read uncommit:读未提交

   产生的问题：脏读，不可重复度，幻读

2. read commit :（Oracle

   产生问题：不可重复读 幻读

3. repeatable:（mysql

   幻读

4. serializable:

   解决所有问题,类似于加锁操作

   隔离级别从小到大：速度越来越慢

设置隔离级别

SELECT @@tx_isolation --
set @@

RT INTO bill VALUES(1,1000),(2,1000);
UPDATE bill SET balabce=1000;
SELECT * FROM bill;

START TRANSACTION;
UPDATE bill SET balabce=balabce-500 WHERE id=1;

UPDATE bill SET balabce=balabce+500 WHERE id=2;

ROLLBACK – 如果发生错误就回滚到开启事务处
COMMIT – 如果不发生错误就提交

mysql默认自动提交事务，如果你开启事务不手动提交的话默认回滚 。一条DML会自动提交一次

Oracle默认手动提交

1. 查看默认提交模式

   ```mysql
   select @@autocommit; -- 0 代表手动提交 1 代表自动提交
   set @@autocommit =0 ;-- 修改为手动提交

3.2 事务的四大特征

1. 原子性：不可分割的最小分割单位。
2. 持久性：如果事务一旦提交或者回滚之后，数据会持久化的被保存
3. 隔离性：多个事物之间，相互独立。
4. 一致性：事务操作前后总量不变。

3.3事务的隔离级别

概念：多个事物之间，相互隔离独立的。但是如果多个事务操作同一批数据则会引发一些问题，设置不同的隔离级别可以解决这些问题

1. 脏读：一个事务读取到另一个事务没有提交的数据
2. 虚读：不可重复度在同一个事务中两次读取到的数据不一致.
3. 幻读：一个事务操作(DML)数据表中所有记录。另一个事务却添加了一个数据，则第一个事务查询不到自己的修改。

四种隔离级别：

1. read uncommit:读未提交

   产生的问题：脏读，不可重复度，幻读

2. read commit :（Oracle

   产生问题：不可重复读 幻读

3. repeatable:（mysql

   幻读

4. serializable:

   解决所有问题,类似于加锁操作

   隔离级别从小到大：速度越来越慢

设置隔离级别

SELECT @@tx_isolation --
set @@