个人学习MySql数据库的一些知识点总结

MySql

SQL: Structured Query Language 结构化查询语言

MySql 数据库的SQL语句不区分大小写，关键字建议大写

SQL语言分类：

DDL：Date Definition Language  数据定义语言：用来定义数据库、表、列等；主要关键字：create（创建），创建数据库、表：create database（此填数据库名字）；

//数据库如果不存在，则创建数据库

create database if not exists 数据库名称;

//创建student表：

create table student(id int,name varchar(10),gid int,score int);

drop（删除）drop (table:此填表名)：删除整个表，连同表内的数据一起删除；

【delete】（删除） delete (table:此填表名)：只删除表内数据，不删除表格。

alter（修改）用于修改表名，列，等数据 alter table（此填表名）+字段等。

//修改表名

       alter table 表名 rename to（as) 新的表名;

//修改表的字符集

       alter table 表名 character set 字符集名称;

//添加一列

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

//修改列名称 类型

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

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

【change可以修改字段名，也可以修改类型，可以只修改类型，即字段名写成相同的。

modify只能修改类型。】

//删除列

alter table 表名 drop 列名;

DML：Data Manipulation Language数据库操作语言：用来对数据库表中的数据进行增删改。

关键字：insert ：（插入数据） insert into（此填表名）字段名 values （添加字段的内容）;字段可以是多个，对应values中的数据要一一对应。

//插入数据：

insert into student values(1,'zs',1,80),

【delete】（删除） delete (table:此填表名)：只删除表内数据，不删除表格。

//删除user3表中id字段为1 的记录：

delete from user3 where id=1;

update（修改）update（表名）set (字段名) where（此填条件）。

//将user3表中所有salary字段为3.14的id改为3：

update user3 set id=3 where salary=3.14;

DQL：Date Query Language 数据查询语言：用于查询数据库中的记录：最常用； 关键字：select：（查询）select（字段名）from（表名）where 条件；

//查询score分数大于80的学生

select * from student where score >80;

//用别名的形式查询平均分，这样不会执行两次avg(score):

select gid,avg(score)score_avg  from student where score >80 group by gid having score_avg<90;

执行顺序： From Where Group by Having Select Order by Limit

语法：

       select

              字段列表

       from

              表名列表

       where

              条件列表

       group by

              分组字段

       having

              分组之后的条件

       order by

              排序

       limit

              分页限定

//like:模糊查询

_ :任意一个字符

%：任意多个字符

//根据gid求平均分按降序排序，并分页

select gid,avg(score)score_avg  from student where score >80 group by gid having score_avg>90 order by gid desc,score_avg limit 0,2;

（desc 降序，默认升序asc ）

limit 略过多少条数据（是0可以省），显示多少条数据；

//查询id4-6之间的数据，可以等于两个条件的值 必须从小到大

select * from student where id between 4 and 6;

//in(集合)，id in(1,2,4):id为1或2或4；

is null;只有 id is null, 没有id = null;

is not null；判断非空

//去重复查询处理

select distinct * from person;

---------------------------------------------------------------------

聚合函数：（都是排除null进行处理）

1.查询id的个数（count():去null计数）：select count(id) from person; 

（count(*) : 查询所有的id的个数）：select count(*) from person;

2.查询id列中的最大值：select max(id) from person;

3.查询id列中的最小值：select min(id) from person；

4.查询id列中的平均值：select avg(id) from person;

查询id是否为空，为null用”空“替换：select ifnull(id,"空") from person;

查询id的个数，解决不能查空值的问题：

select count(ifnull(id,"空")) from person;

DCL：Data Control Language：数据控制语言：用于定义数据库的访问权限和安全级别，及创建用户。

1. 查询权限：

查询权限

show grants for '用户名'@'主机名';

show grants for 'lisi'@'%';

关键字：grant:（授予权限）

grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

//给张三用户授予所有权限，在任意数据库任意表上

grant all on *.* to 'zhangsan'@'localhost';

Revoke：（撤销权限）

Revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

Revoke update on db3.`account` from 'lisi'@'%';

约束：

1. 主键约束：primary key

//在创建表时，添加主键约束

create table stu(

id int primary key,-- 给id添加主键约束

name varchar(20)

);

//删除主键

alter table stu drop primary key;

//在创建表时，添加主键约束，并且完成主键自增长

create table stu(

       id int primary key auto_increment,-- 给id添加主键约束

       name varchar(20)

);

2. 非空约束：not null        与 default 值 联想记忆

//创建表完后，添加非空约束

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

直接在类型后面加not null；

//删除name的非空约束

alter table stu modify name varchar(20);

3. 唯一约束：unique

//在表创建完后，添加唯一约束

alter table stu modify phone_number varchar(20) unique;

//删除唯一约束

alter table stu drop index phone_number;

4. 外键约束：foreign key

//在创建表时，可以添加外键  * 语法：

create table 表名(

       ....

       外键列

       constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)

);

//删除外键

alter table 表名 drop foreign key 外键名称;

//创建表之后，添加外键

alter table 表名 add constraint 外键名称 foreign key (外键字段名称) references 主表名称(主表列名称);

多表查询：

1. 内连接查询：

隐式内连接：使用where条件消除无用数据

//查询所有员工信息和对应的部门信息

select * from emp,dept where emp.`dept_id` = dept.`id`;

//查询员工表的名称，性别。部门表的名称

SELECT

      t1.name, -- 员工表的姓名

      t1.gender,-- 员工表的性别

      t2.name -- 部门表的名称

FROM

      emp t1,

      dept t2

WHERE  t1.`dept_id` = t2.`id`;

显式内连接：语法： select 字段列表 from 表名1 [inner] join 表名2 on 条件

//查询所有员工信息和对应的部门信息

* select * from emp join dept on emp.`dept_id` = dept.`id`;   

1. 外链接查询：

左外连接：

* 语法：select 字段列表 from 表1 left [outer] join 表2 on 条件；

//查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称

select t1.*,t2.`name` from emp t1 left join dept t2 on t1.`dept_id` = t2.`id`;

右外连接：

* 语法：select 字段列表 from 表1 right [outer] join 表2 on 条件；

//* 查询的是右表所有数据以及其交集部分。

Select  * from dept t2 right join emp t1 on t1.`dept_id` = t2.`id`;

子查询：

* 概念：查询中嵌套查询，称嵌套查询为子查询。

//查询工资最高的员工信息

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

外查询的条件where id=(子查询)，子查询的必须查询id的值只能有一个（子查询的结果只能是一个），如果多于一个那么数据库就不知道具体跟哪一个值对等

事务：

一个包含对个步骤的业务操作称之为事务。

操作：

1. 开启事务： start transaction;

2. 回滚：rollback;

3. 提交：commit;

事务的四大特征：

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

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

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

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

事务出现安全问题的原因：

       多个事务之间是隔离的、相互独立的，如果多个事务操作同一批数据，会出现数据丢失等问题，因此需要设置隔离等级来解决该问题。

事务的隔离等级：

1. 脏读：读取到另一个事务中没有提交的数据

       2. 不可重复读(虚读)：在同一个事务中，两次读取到的数据不一样。

       3. 幻读：一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。

      

隔离等级	解决的问题
Read uncommitted(读未提交)	任何问题都不能解决
Read committed(读已提交)	解决脏读
Repeatable read(可重复读)	解决脏读、幻读
Serializable(串行化)	可解决所有问题，堪称完美安全等级，但效率低

如有错误，请在评论区指出，核实之后后期加以修改，感谢支持~~~