MySQL基础（DML，DQL，聚合函数，约束，事务及其特性）

DML

--添加数据

1给指定列添加数据

    insert into 表名（列名1，列名2，……） values（值1，值2，……）;

2给全部列添加数据

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

3批量添加数据

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

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

--修改数据

   update 表名 set 列名1=值1，列名2=值2，……where  列名1=值1，列名2=值2，……；

（where后面为条件语句。如果不添加，那么数据库会把所有修改的列里面的值都修改）

--删除数据

   delete from 表名 where 条件；

（如果不加where和条件，则会把表中的数据全部删除）

eg：delete from stu where name=‘张三’；

DQL

--基础查询

select  列名，列名2，…… from 表名；

1查询所有的列

select  列名1，列名2，…… from 表名；

select * from 表名；（不推荐使用，不便于别人理解）

 2去重

select distinct 列名1，列名2，…… from 表名；

3为显示的列名起别名

select 列名1 as 别名1，列名2 as 别名2，…… from 表名；

（as可以省略但是别名必须得和列名之间有一个空格）

--条件查询（where）

Select 字段列表 from 表名 where 条件列表；

eg:select age from stu where age>=18 && age<=20;或者（age>=18 and age<=20）

条件查询-模糊查询(like)

  Select * from stu where 列名 like‘关键字符 %’（‘关键字符_’）

（%代表多个任意字符  _代表单个任意字符）

--排序查询(order by)

Select 字段列表 from 表名 order by 排序字段名1 排序方式，排序字段2 排序方式，……；

（排序方式：asc：升序排列（默认）        desc：降序排列）

（排序方式可以不写，但是不写就会默认执行asc升序排列）

注意：如果有多个排序条件的时候，只有当前一个排序条件值一样时才会执行后一个排序条件

--聚合函数

Select 聚合函数名（列名） from 表名；

--分组查询(group by)

Select 字段列表 from 表名 【where 分组前限定条件】group by 分组字段名 【having 分组后过滤条件】……；（【】内可以不用写）

执行顺序：where>聚合函数>having;

注意：分组之后，查询字段为聚合函数和分组字段，查询其他字段无任何意义

Where和having的区别：

执行时机不一样：where是分组之前进行限定，不满足where条件，则不参与分组，而having是分组之后对接过的过滤。

可判断的条件不同：where不能对聚合函数进行判断，having可以

查询：男同学和女同学的数学的平均成绩（可以使用聚合函数）

select sex,avg(math) from stu group by sex；

查询：男同学和女同学的数学的平均成绩和人数（可以使用多个聚合函数）

select sex,avg(math),count(*) from stu group by sex；

查询：男同学和女同学的数学的平均成绩和人数（要求：拥挤分数大于70的，分组后的人数大于2人）

（where是在分组之前进行选择的判断，having是分组之后进行判断的条件）

select sex,avg(math),count(*) from stu where math>70 group by sex having count(*)>2;

分页查询(limit)

Select 字段列表 from 表名 limit 起始索引，查询条目数；

（第一页的索引一般从0开始）

索引计算公式：起始索引=（当前页面数-1）*每页的查询条目数

Tip：分页查询limit是mysql数据库的方言

     Oracle分页查询使用的是rownumber

     SQL Server分页查询使用top

约束条件

Eg：

drop table if EXISTS emp;

-- 员工表

CREATE TABLE emp (

id INT primary key auto_increment, -- 员工id，主键且自增长

ename VARCHAR(50) not null unique, -- 员工姓名，非空且唯一（多个约束可以一起执行）

joindate DATE not null, -- 入职日期，非空

salary DOUBLE(7,2) not null, -- 工资，非空

bonus DOUBLE(7,2) default 0-- 奖金，如果没有将近默认为0

);

Auto_increment：当列是数字类型并且是唯一约束，则会实现自动增长（+1）

Eg：

Insert into empty（id，ename，joindate，salary） values ('张三','1999-11-1',8800,5000);

Insert into empty（id，ename，joindate，salary） values (null，'李四','1999-11-1',8800,5000);

得到的结果会发现，张三id为1，李四id为2，进行自增1.

外键约束

概念：外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性

添加约束：

-- 创建表时添加外键约束

CREATE TABLE 表名(

   列名 数据类型,

   …

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

);

-- 建完表后添加外键约束

ALTER TABLE 表名 ADD constraint 外键名称foreign key(外键列名) references 主表(主表列名) ;

删除约束

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

多表关系实现

一对多的实现方式：

在多的一方建立外键关联单一的表

多对多的实现方式：

通过建立中间表，中间表至少包含两个外键，分别关联双方主键。

一对一的实现方式：

在任意一方建立外键，关联对方主键，并设为外键唯一。

多表查询

内连接

Select * from 表名1，表名2，……【where 【表名1.列名=表名2.列名】 】

如果后面不加where条件语句，那么就会出现笛卡尔积

笛卡尔积：假设有A,B两个集合，去A,B两个集合的所有组合结果。

Where就是防止笛卡尔积，出现无效的数据

外连接

左外连接：

Select 字段列表 from 表1 left join 表2 on 条件（eg：dep.id=emp.id）;

右外连接：

Select 字段列表 from 表1 rignt join 表2 on 条件（eg：dep.id=emp.id）;

将右外连接的表1和表2互换位置就是左外连接

多表查询

子查询

单行单列查询：作为条件值，使用=，！=，>,<等进行判断

Select 字段列表 from 表名 where 字段名=（子查询）；

多行单列查询：作为条件值，使用in等关键字进行条件判断

Select 字段列表 from 表名 where字段名 in（子查询）；

Eg：select * from emp where dep_id in（select id from dept where name=‘张三’ or name=‘李四’）；

多行多列查询：作为虚拟表

Select 字段列表 from（子查询） where 条件；

Eg：select * from （select * from emp where join_dat>’1998-1-1’）ti ，dept where ti.id=dept.id;

--查询emp表中的所有的join_date大于1998-1-1的信息，并给emp起别名为t1，再查询ti中的id和dept中的id想等的所有的信息，一起输出来。

事务

-- 开启事务

START TRANSACTION;

或者  BEGIN;

-- 提交事务

COMMIT;

-- 回滚事务

ROLLBACK;

原子性（Atomicity）: 事务是不可分割的最小操作单位，要么同时成功，要么同时失败

一致性（Consistency） :事务完成时，必须使所有的数据都保持一致状态

隔离性（Isolation） :多个事务之间，操作的可见性

持久性（Durability） :事务一旦提交或回滚，它对数据库中的数据的、改变就是永久的