MySQL基础

1.DDL

1.1表操作----创建

注意：[``````]中为可选参数，最后一个字段后面没有逗号

create table 表名(
		字段1 字段1类型[comment 表注释],
    	字段2 字段2类型[comment 表注释],
    	字段3 字段3类型[comment 表注释],
    	字段n 字段n类型[comment 表注释]
    )[comment 表注释];

案例：

--在数据库中添加一个员工表;

create table jop(
id int comment'编号',
name varchar(50) comment'姓名',
age int comment '年龄',
gender varchar(10) comment '职位'
)comment'员工表';

1.2表操作----查询

查询当前数据库所以表

show tables;

查看表结构

desc 表名;

查看指定表的键表语句

show create table 表名;

1.3表操作----数据类型

MySQL中的数据类型有很多，主要分为三类：数值类型、字符串类型、时间和日期类型。

1.在数值类型中“范围（有符号）”表示出现负数时的范围，“范围（无符号）”则表示没有负数时的范围。

2.在使用DECIMAL类型时，要去指定M（精度）和D（标度）例如'123.45'这个数，那么他的精度就是整个数值的长度为5，标度就为小数点后面的数值长度为2。

3.假如我们在一张表当中要描述一个用户的年龄，可以用INT或INTEGER类型。

age int

 

也可以用TINYINT范围（无符号）类型，它的取值就会是（0，255）。

TINYINT类型字节大小要比INT或INTEGER类型要小，可以节省内存空间。

age TINYINT UNSIGNED

4.在使用DOUBLE类型时，要指定两个参数, 其中4为整个数的长度，1为小数点后的长度。

money double(4,1)

1.其中带有TEXT的是用来描述文本数据的，带有BLOB是用来描述二进制数据的，包括有视频、音频、安装包等是可以存储在数据库当中的，但在现实开发时很少去做，原因是性能并不高而且不方便管理，对于这类的二进制数据，会采用专门的服务器进项存储。

2.当我们使用char和varchar时都需要在后面跟上一个参数，varchar(10)代表当前字符串能存储的最大长度为10个字符，一旦超出10个字符将会报错。char(10)你存入一个字符也会占用10个字符，其他空间会使用空格进行补位。

3.char性能好，varchar相比于char性能差一些，原因是因为varchar在我们使用时会根据内容来计算它所占用的空间。

 

1.4表操作----修改&删除

添加字段

alter table 表名 add 字段名 类型(长度) [comment注释][约束];
alter table jop add  sex char(1) comment '性别';

 修改数据类型

alter table 表名 modify 字段名 新数据类型(长度);
alter table jop modify sex varchar(10);

修改字段名和字段数据类型

alter table 表名 change 旧字段名 新字段名 类型(长度);
alter table jop change  name jname char(10);

删除字段

alter table 表名 drop 字段名;
alter table jop drop sex;

修改表名

alter table 表名 rename to 新表名;
alter table jop rename to jopwork;

删除表

drop table[if exists] 表名; 
drop table[if exists] jopwork; 

删除指定表。并重新创建该表

注意：在删除表时，表中的数据也会被删除，并且重新创建时数据不会恢复。

truncate table 表名;
truncate table jopwork;

2.DML

1.5表中数据的操作

给指定的字段添加数据

insert into 表名(子段1,字段2,....) values (值1,值2,....);

给全部字段添加数据

insert into 表名 values (值1,值2,.....);

批量添加数据

insert into 表名(子段1,字段2,....) values (值1,值2,....),(值1,值2,....);
insert into 表名 values (值1,值2,....),(值1,值2,....);

注意：插入数据时，指定的字段顺序要与值得顺序时一一对应的。

字符串和日期型数据应该包含在引号中。

插入数据的大小，应该在字段的规定范围内。

修改数据

update 表名 set 字段名1 = 值1，字段名2 = 值2，...[where条件]
--修改id为1的数据，将name改为托马斯
update nbas set name = '托马斯' where id = 1;

注意：修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的数据。

删除数据

delete from 表名 [where 条件];

注意：delete语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的数据。

           delete语句不能删某一个字段的值（可以使用update）。

3.DQL

 1.DQL介绍：DQL英文全称时Data Query Language(数据查询语言)，用来查询数据库中表的记录。查询关键字：select。

1.1DQL基本查询

select 字段1.字段2,字段3...from 表名;
select*from 表名;查询表中所有数据

设置别名

select 字段1[as 别名1],字段2[as 别名2]...from 表名;
select name '名字' from jop;
--起别名时as是可以省略的

去除重复记录

select distinct 字段列表 from 表名;

1.2条件查询

select 字段列表 from 表名 where 条件列表;
select * from jop where age = 23;
--查询年龄为23的员工

条件:

.3聚合函数

1.介绍：将一列数据作为一个整体，进行纵向(作用于某一列数据)计算。

2.常见的聚合函数

 

3.语法

select 聚合函数(字段列表)from 表名;
--统计jop表中员工的平均年龄
select avg(age) from jop;

 注意：null值不参与所有聚合函数的运算。

1.3分组查询

select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];
--根据性别进行分组，统计男性员工和女性员工的数量
select sex,count(*) from jop group by sex;
--查询年龄小于45的员工，并根据工作地址进行分组，获取员工数量大于三的工作地址
select work,count(*) from jop where age<45 group by having count(*)>=3;

where与having区别

执行时机不同：where是分组之前进行过滤，不满足where条件，就不参与分组；而having是分组之后对结果进行过滤。

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

注意：执行顺序：where>聚合函数>having。

分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

就比如select sex,count(*) from jop group by sex;这句语法输出的结果就显示分组字段名sex和聚合函数count()的值，如果在sql语句前加上一个name字段select name ,sex,count(*) from jop group by sex;就只能输出一个人的数据，没有意义。

1.4排序查询

select 字段列表 from 表名 order by 字段1 排序方式1,字段2 排序方式2;
--根据年龄对公司员工进行升序查寻，年龄相同，再按照入职时间进行降序排列
select * from jop order by age asc,money desc;

 

排序方式

ASC:升序（默认值）

DESC:降序

注意：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段尽行排序

1.5分页查询

select 字段列表 from 表名 limit 起始索引,查询记录数;

注意：

起始索引从0开始，起始索引= （查询页码-1）*每条显示记录数。

分页查询时数据库的方言，不同的数据库有不同的实现，MySQL时LIMIT。

如果查询的是第一也数据，起始索引也可以省略，直接简写为limit 10。

练习：

--1.查询年龄为20，21，22，23岁的女性员工信息
select * from jop where sex='女'and id in(20,21,22,23);
--2.查询性别为男，并且年龄在20-40岁（含）以内的姓名为三个字的员工。
select *from jop where sex='男'and age between 20 and 40 and name like '___';
--3.统计员工表中，年龄小于60岁的，男性员工和女性员工的人数。
select sex count(*) from jop where age<60 group by sex;
--4.查询所有年龄小于60岁得到，男性员工和女性员工的人数。
select name,age from jop where age<=35 order by age asc,times desc;
--5.查询性别为男，且年龄在20-40岁（含）以内的前五个员工信息，对查询的结果按年龄升序排序，年龄相同按入职时间升序排序。
select * from jop where sex = '男'and (age between 20 and 40) order by limit 5 ,sex asc, times asc limit 5 ;

1.6DQL执行顺序

 

4.函数

1.1字符串函数

MySQL中内置了很多字符串函数，常用的几个如下：

语法：

select 函数(参数);

 

1.2数值函数

常见的数值函数如下：

 

1.3日期函数

常见的日期函数：

 

1.4流程函数

流程函数也是很常用的一类函数，可以在SQL语句中实现条件筛选，从而提高效率的语句。

5.约束

1.1概述

1.概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。

2.目的：保证数据库中数据的正确、有效性和完整性。

3.分类：

注意：约束是作用于表中字段上的，可以在创建/修改表的时候添加约束。

1.2约束演示

根据需求完成表的构建

 

create table user(
	id int primary key auto_increment,
	name varchar(10) not null unique,
    age int check( age>0 && age<=120 ),
    status char(1) default '1',
    gender char(1) 
);

 

1.3外键约束

概念：外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

第一种方式为构建表时直接加入外键约束

create table 表名(
	字段名 数据类型,
    ...
    [constrain] [外键名称] foreign key(外键字段名) references 主表(主表列名)
);

 第二种方式是构建表之后在去加入外键约束

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

外键约束删除/更新行为

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

 

6.多表查询

1.1多表关系

概述

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在者各种联系，基本上分为三种：一对多（多对一）、多对多、一对一。

一对多（多对一）

案例：部门与员工的

关系：一个部门对应多个员工，一个员工对应多个部门

实现：在多的一方建立外键，指向一的一方的主键

多对多

案例：学生课程的关系

关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择

实现：建立第三张中间信息表，中间信息表至少包含两个外键，分别关联两方主键

 

一对一

案例：用户与用详情的关系

关系：一对一关系，多用于单表拆分，将一张表的的基础字段放在一张表中，其他字段详情放在另一张表中，以提升操作效率。

实现：在任意一方加入外键，关联另一方的主键，并设置外键为唯一的（unique）

 

 

 

1.2多表查询

概述：从多表中查询数据

在多表查询中，如果用这条语句 select * from 表1名称 ,表2名称 ;来查询的话结果并不会报错，但是表1中的一个数据会和表2中的每一条数据进行匹配，这样会出现许多条数据，不是我们想要查询的结果，这种现象我们称为笛卡尔积。

笛卡尔积：笛卡尔积是指在数学中，两个集合和A和B的所有组合情况。（再多表查询时要消除无效的笛卡尔积)

 

消除笛卡尔积

select * from 表1名 ,表2名 where 表1与表2关联的外键 = 表2的主键; 

多表查询分类

连接查询

内连接：相当于查询A、B交集部分

外连接：

左外连接：查询左表所有数据，以及两张表的交集部分

右外连接：查询右表所有数据，以及两张表的交集部分

自链接：当前表与自身的连接，自链接必须使用表别名

子查询

1.内连接查询

内连接查询语法：

隐式内连接

select 字段列表 from 表1,表2 where 条件...;

 显式内连接

select 字段列表 from 表1 [inner] join 表2 连接条件...;

2.外连接查询

外连接查询语法：

左外连接

select 字段列表 from 表1 left [outer] join 表2 on 条件...;

右外连接

select 字段列表 from 表1 right [outer] join 表2 on 条件...;

3.自链接

自链接查询语法：

select 字段列表 from 表A  别名A join 表A 别名B on 条件...;

自连接查询，可以是内连接查询，也可以是外连接查询。

4.联合查询-union，union all

对于union查询 ，就是把多次查询的结果合并起来，形成一个新的查询结果集。

select 字段列表 from 表A...
union [all]
select 字段列表 from 表B...;

对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。

union all 会将全部数据拼接到一起，union会对合并之后数据去重。

5.子查询

概念：SQL语句中嵌套select语句，称为嵌套查询，又称子查询。

select * from t1 where column1 = (select column1 from t2);

子查询外部的语句可以是insert/update/delete/select的任何一个。

根据子查询结果不同，分为：

标量子查询（子查询结果为单个值）

列子查询（子查询结果为一列）

行子查询（子查询结果为一行）

表子查询（子查询结果为多行多列）根据子查询的位置，分为：where之后、from之后、select之后。

1.标量子查询

子查询返回的结果是单个值（数值、字符串、日期等），最简单的形式，这种子查询称为标量子查询。

常用操作符：= 、<>、 >、 >=、 <、 <=

2.列子查询

子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。

常用的操作符：in、not in、any、 some、all

3.行子查询

子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。

常用操作符：=、<>、in、not in

4.表子查询

子查询返回的结果是多行多列，这种子查询被称为表子查询。

常用的操作符：in

7.事务

事务介绍

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

默认MySQL的事物是自动提交的，也就是说，当执行一条DML语句，MySQL就会立即隐式的提交事物。

事务操作

查看/设置事务提交方式

select @@autocommit;
set @@autocommit = 0;

 提交事务

commit;

开启事务

start transaction 或 begin;

事务回滚

rollback;

案例:

set @@autocommit = 0;--设置为手动提交事物
--转账操作（张三给李四转账1000）
--1.查询张三账户余额 （account为表名）
select * from account where name = '张三';
--2.将张三账户余额-1000
update account set money = money-1000 where name = '张三';
--3.将李四账户余额+1000
update account set money = money + 1000 where name = '李四';
--操作成功 提交事务
commit;
--操作失败 回滚事务
rollback;

事务四大特性

原子性（Atomicity）：事务是不可分割的最小单元，要么全部成功，要么全部失败。

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

隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。

 

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

 

 

并发事务问题

脏读：一个事务读到另外一个事物还没有提交的数据。

不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。  

 幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在了，好像出现了“幻影”。

事务的隔离级别

 

此表中隔离级别从上到下越来越高，但是Serialixsble是性能最差的，Read uncommitted性能最高，所以在业务当中去选择业务隔离级别的时候，既要考虑数据的安全性，又要去考虑数据库的并发性能。

--查看事务的隔离级别
select @@transaction_isolation;
--设置事务隔离级别
set [session|global] transaction isolation level {隔离级别}