MYSQL

数据库介绍

数据库的好处

1.可以持久化数据到本地
2.结构化查询

数据库的常见概念

1.DB：数据库，存储数据的容器
2.DBMS：数据库管理系统，又称为数据库软件或数据库产品，用于创建或管理数据库
3.SQL：结构化查询语言，用于和数据库通信的语言，不是某个数据库软件特有的，而是几乎所有的主流数据库软件通用的语言

数据库存储数据的特点

1.数据存放到表中，然后表再放到库中
2.一个库中可以有多张表，每张表具有唯一的表名用来标识自己
3.表中有一个或多个列，列又称为"字段"，相当于java中的"属性"
4.表中的每一行数据，相当于java中的"对象"

常见的数据库管理系统

mysql，oracle，db2，sqlserver

DQL(Data Query Language)数据查询语言

用来查询数据库中表的记录(数据)。关键字：select, where 等
完整语法：

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

基础查询

语法：

select 查询列表 from 表名;

示例

select id , name from s_emp;

条件查询

语法：select 查询列表 from 表名 where 筛选条件;

分类：

1.按条件表达式筛选

条件运算符：< > = != <>(推荐使用这个来作为不等于) >= <=
示例

select * from t_emp where eid > 10 ;
select * from t_emp where eid < 10 ;
select * from t_emp where eid = 10 ;
select * from t_emp where eid != 10 ;
select * from t_emp where eid <> 10 ; -- 推荐使用这个作为不等于
select * from t_emp where eid >= 10 ;
select * from t_emp where eid <= 10 ;

2.按逻辑表达式筛选

and(&&) or(||) not(!)
示例

select * from t_emp where ( eid < 10 and sex = '女' ) 
or not emp_name = '王五';

3.模糊查询

• like

示例
%匹配多个字符

select * from t_emp where email like '%163%';

_匹配单个字符

select * from t_emp where emp_name like '张_';

特殊字符转义
查询名字是a_b…的人

select * from t_emp where emp_name like 'a$_b%' escape '$';
select * from t_emp where emp_name like 'a\_b%';

• between and（相当于满足大于等于值1，并且小于等于值2的数）

select * from t_emp where age between 20 and 25 ;

• in（判断某字段是否属于in的某一列中），in列表的值必须统一或兼容

select * from t_emp where eid in (1,2,3,4,5);

• is null 或 is not null 判断是不是空/非空

select * from t_emp where email is null or did is not null ;

排序查询

语法：

select 查询列表 from 表名 
【where 筛选条件】order by 排序列表 【asc|desc】
asc：升序 默认排序方式
desc：降序

支持格式

1.单个字段

2.多个字段

3.表达式

4.函数

5.别名

6.筛选条件

示例

-- 单字段
select * from t_emp where eid < 15 order by eid desc ;
-- 多字段
select * from t_emp where eid < 15 order by eid desc , age ;
-- 筛选条件
select * from t_emp where eid < 15 order by age in (20,22,23,24) desc ;
-- 别名
select emp_name as name from t_emp order by name desc ;
-- 表达式
select (emp_name + '1')  from t_emp order by (emp_name + '1') desc ;
-- 函数
select * from t_emp order by length(email) desc ;

常见函数

语法：

select 函数名(实参列表) 【from 表名】;

分类

1,单行函数

1.字符函数

• length(参数):获取参数值的字节个数，英文字母一个占一位，中文一个占3个
• concat(str1,str2,…):字符串拼接
• upper():变大写，lower():变小写
• substr(字符,index):截取从index开始之后的所有字符，包括index；substring(字符,index,length)：从index开始，截取指定长度的字符，包括index，索引都是从1开始
• instr(str,substr):返回子串substr第一次在str中的起始索引，找不到则返回0
• trim(【字符】str):去除str中的前后指定字符，若不写字符，则是去除str中的前后空格
• lpad(字符串,length,字符):用指定的字符左填充使字符串达到指定长度，若长度小于字符串本身长度，则截断使其总长度为length
• rpad(字符串,length,字符):用指定的字符右填充使字符串达到指定长度，若长度小于字符串本身长度，则截断使其总长度为length
• replace(str,from_str,to_str):将str中的from_str全部替换为to_str

示例

select length(email) from t_emp ;
select concat(emp_name,email) from t_emp ;
select upper(emp_name) from t_emp;
select lower(emp_name) from t_emp;
select substr(email,3) from t_emp;
select instr('abcd','c');
select trim('m',email) from t_emp;
select lpad('a',10,'+');
select rpad('a',10,'+');
select replace(email,'com','') from t_emp;

2.数学函数

• round（参数,[n]）：四舍五入，且将值保留n为小数，不写就是四舍五入为整数
• ceil(参数):向上取整，返回>=该参数的最小整数
• loor(参数):向下取整，返回<=该参数的最大整数
• truncate(x,D):将数值截断为指定位数，即保留小数点后几位，不考虑四舍五入
• mod(a,b):取余，a-b/a*a
  示例

select round(3.1415926,3);
select ceil(3.1415926);
select floor(3.1415926);
select truncate(3.1415926,4);
select mod(3,2);

3.日期函数

• now():返回当前系统日期+时间-curdate():返回当前系统日期，不包含时间
• curtime()：返回当前系统时间，不包含日期
• year(),month(),day(),hour(),minute(),second():获取指定的年、月、日、时、分、秒，
• monthname():获取月份的英文名称
• str_to_date(字符,指定格式):将日期格式的字符转换成指定格式的日期
• date_format(日期):将日期转化成字符
• datediff(时间1，时间2):计算两个时间的差值天数常见格式
  示例

select now() , curtime();
select year('1999-10-10 10:10:10') as year,
       month('1999-10-10 10:10:10') as month,
       day('1999-10-10 10:10:10') as day,
       hour('1999-10-10 10:10:10') as hour,
       minute('1999-10-10 10:10:10') as minute,
       second('1999-10-10 10:10:10') as second ;
select monthname('1999-10-10');
select str_to_date('1999-10-10 10:10:10','%Y-%m-%d');
select date_format(now(),'%Y-%m-%d');
select datediff(now(),'1999-10-10 10:10:10');

4.其他函数

• version():查看mysql版本
• database():查看当前数据库
• user():查看当前用户
  示例

select version() , database() , user() ;

5.流程控制函数

if(条件,符合输出,不符合输出)函数：相当于java中的if else效果
示例

select * , if(age > 23 ,'old' , 'young') from t_emp;

case：
语法1：类似于java中的 switch-case

case 要判断的字段或表达式
when 常量1 then 要显示的值1或者语句1
when 常量2 then 要显示的值2或者语句2
...
else 要显示的值n或者语句n;
end

select last_name,department_id,
    case
        department_id
        when 10
           then salary * 1.1
        when 20
            then salary * 1.2
        then 30
            then salary * 1.3
        else salary * 1.4
        end "工资"
from
    employees ;

语法2:类似于多重 if

case
when 条件1 then 要显示的值1或者语句1
when 条件2 then 要显示的值2或者语句2
...
else 要显示的值n或者语句n;
end

示例

select last_name,salary,
    case
        when salary > 10000
            then '高富帅'
        when salary > 80000
            then '潜力股'
        then salary > 60000
            then '小屌丝'
        else '小草根'
        end "结果"
from
    employees ;

2.分组函数

• sum():求和函数
• avg():求平均值
• max():求最大值
• min():求最小值
• count():求非空的个数,count(*)经常用于统计行数
  注意：
• sum()和avg()一般用于数值型，max()和min(),count()可以是处理任何类型
• 分组函数都忽略null值
• 可以和distinct搭配使用
• 和分组函数一起查询的是group by后面的字段
  示例

select sum(sal) , avg(sal) , max(sal) , min(sal) ,count(*) from t_emp ;

分组查询

语法：

select 分组函数,列    //(要求出现在group by 后面)
from 表
【where 筛选条件】
group by 分组的列表
【order by 子句】

注意：

• 查询列表比较特殊，要求是分组函数和group by 后出现的字段
• 分组函数做条件肯定是放在having子句中
• 能用分组前的用分组前筛选
• 排序在group by的最后面

分类情况：

简单查询

语法

select 分组函数,列 from 表 group by 列;

示例

select sex from t_emp group by sex;

添加分组前筛选条件(筛选的源在原始表中)

语法

select 分组函数,列 from 表 where 筛选条件 group by 列;

示例

select sex , sum(age) from t_emp where age > 22 group by sex ;

添加分组后筛选条件（筛选的源不在原始表中，要先分组，然后在分组中进行判断）

语法

select 分组函数,列 from 表 group by 列 having 筛选条件 ;

示例

select price,count(*), group_concat(title) as article_title
from t_emp group by price having count(title) >=2;

按多个字段分组

语法

select 分组函数,字段1,字段2 from 表 group by 字段1,字段2;

示例

select eid , sum(age) , sex from t_emp
where eid in (1,2,3,4,5,6,7) group by sex , eid order by age ;

连接查询（多表查询）

分类

按年代分类

1.sql 92标准：仅仅支持内连接

内连接

1. 等值连接
   注意：

• 找到多个表的交集部分，然后用等号连接
• n个表至少要n-1个条件
  语法

select 查询列表 
from 表1 别名,表2 别名 
where 连接条件 
【and 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】

示例

select eid , emp_name , age , dep_name
from t_emp t1 , t_dept t2
where t1.did = t2.did
group by eid;

2. 非等值连接
   条件不是等于号，可以是大于，小于这种

select eid , emp_name , age , dep_name
from t_emp t1 , t_dept t2
where eid > 10 
group by eid , dep_name;

3. 自连接
   两张表都是自己，自己连接自己

示例

select t1.id , t1.FIRST_NAME 
from S_EMP t1 , S_EMP t2
where t1.ID = t2.MANAGER_ID ;

2.sql 99 标准[推荐]：支持内连接+外连接（左外和右外）+交叉连接

语法：

select 查询列表 
from 表1 别名 
【连接类型】 join 表2 别名 on 连接条件 
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】

连接类型：内连接–>inner，左外–>left outer,右外–>right outer，全外–>full outer，交叉连接–>cross outer

按功能分类

内连接

1. 等值连接
   示例

select eid , emp_name , age , dep_name
from t_emp t1 inner join t_dept td on t1.did = td.did
group by eid , dep_name;

2. 非等值连接
   示例

select eid , emp_name , age , dep_name
from t_emp t1 inner join t_dept td on t1.did > td.did
group by eid , dep_name;

3. 自连接
   示例

select t1.id , t1.FIRST_NAME
from S_EMP t1 inner join S_EMP t2
on t1.ID = t2.MANAGER_ID ;

外连接（用于查询一个表中有，另一个表中没有的记录）

1. 左外连接

select t1.id , FIRST_NAME , name
from S_EMP t1 left join  S_DEPT t2
                         on t1.MANAGER_ID = t2.ID ;

2. 右外连接
   示例

select t1.id , FIRST_NAME , name
from S_EMP t1 right join  S_DEPT t2
                         on t1.MANAGER_ID = t2.ID ;

3. 全外连接 :内连接的结果+表1有而表2没有+表2有而表1没有
   示例

select t1.id , FIRST_NAME , name
from S_EMP t1 full join  S_DEPT t2
                          on t1.MANAGER_ID = t2.ID ;

等同于

select t1.id , FIRST_NAME , name
from S_EMP t1 left join  S_DEPT t2
                         on t1.MANAGER_ID = t2.ID 
union
select t1.id , FIRST_NAME , name
from S_EMP t1 right join  S_DEPT t2
                         on t1.MANAGER_ID = t2.ID ;

特点:

• 外连接的查询结果为主表中的所有记录
• 如果从表中有和它匹配的，则显示匹配的值
• 如果从表中没有和它匹配的，则显示null
• 外连接查询结果=内连接结果+主表中有而从表中没有的记录

交叉连接：笛卡尔乘积，把表A和表B的数据进行一个N*M的组合

示例

select t1.id , FIRST_NAME , name
from S_EMP t1 cross  join  S_DEPT t2
                           on t1.MANAGER_ID = t2.ID ;

子查询

出现在其他语句内部的select语句

分类：

按子查询出现的位置

1.select后面

仅仅支持标量子查询
语法

select 查询列表(子查询语句) from 表

示例

select id, FIRST_NAME , 
(select name from S_DEPT d where e.DEPT_ID = d.id ) as dt from S_EMP e;

2.from后面

支持表子查询
将子查询结果充当一张表，要求必须起别名
示例

select emp.* , (select name from S_DEPT where emp.DEPT_ID = S_DEPT.ID)
from (select DEPT_ID , avg(SALARY)  from S_EMP group by DEPT_ID) emp;

3.where或having后面

• 标量子查询
  一般搭配着单行操作符使用 ：> < >= <= = <>
  语法

select 查询列表 from 表 where条件(子查询语句)

示例

select id , FIRST_NAME from S_EMP where id = 10 ;

• 列子查询
  需使用多行比较操作符
• in/not in 等于列表中的任意一个，in的参数为子查询语句
• any|some 和子查询返回的某一个值比较
• all+ 和子查询返回的所有值比较
  示例

select id , FIRST_NAME from S_EMP where id in (1,2,3,4,5,6) ;

• 行子查询
  示例

SELECT *
FROM S_EMP e
WHERE (id,salary)=(
    SELECT MIN(id),MAX(salary)
    FROM S_EMP
);

4.exists后面（相关子查询）

返回一个boolean类型的值，有值则为1，否则为0
示例

select * from S_EMP e where 
exists(select id from S_DEPT d where e.DEPT_ID = d.ID);

按结果集的行列数不同

• 标量子查询（结果集只有一行一列）
• 列子查询（结果集只有一列多行）
• 行子查询（结果集只有一行多列）
• 表子查询（结果集一般为多行多列）

分页查询

应用场景：要显示的结果一页显示不全，需要分页提交sql请求

语法：

select 查询列表 
from 表 
【join type join 表2 on 连接条件
where 筛选条件 
group by 分组字段 
having 分组后的筛选 
order by 排序的字段】
limit offset，size

offset：要显示的条目的起始索引（从0开始）
size：要显示的条目个数
公式：页数page，要显示的条目个数size
select 查询列表 from 表 limit (page-1)*size,size

示例

SELECT *
FROM
    S_EMP
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
    LIMIT 10,10 ;

联合查询

描述：将多条查询语句的结果合并成一个结果（将多个条件拆分成多个子条件）
语法：

查询语句1 union 查询语句2 union...

应用场景：要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时。

特点：

• 要求多条查询语句的查询列数一致
• 要求多条查询语句的查询的每一列的类型和顺序最好一致
• union 关键字 默认去重，如果使用 union all 可以包含重复项
  示例1

select t1.id , FIRST_NAME , name
from S_EMP t1 left join  S_DEPT t2
                         on t1.MANAGER_ID = t2.ID
union
select t1.id , FIRST_NAME , name
from S_EMP t1 right join  S_DEPT t2
                         on t1.MANAGER_ID = t2.ID ;

示例2

select 1,'abc'
union
select 1,'abc'
union
select 1,'abc';

示例3

select 1,'abc'
union all
select 1,'abc'
union all
select 1,'abc';

DML(Data Manipulation Language)数据操作语言

用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等

插入语句insert

方式一：经典插入

语法：

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

注意：

• 插入的值的类型与列的类型一致或兼容
• 不可以为null的列必须插入值，可以为null的列有两种方式
  方式1：可以为空列的列名写上，但是值赋值为null
  方式2：可以为空列的列名和值都不写，直接省略
• 列的顺序可以调换，但是要保证值的顺序与之对应
• 列数和值的个数必须保持一致
• 可以省略列名，默认所有列，而且列的顺序和表中列的顺序一致
  示例

insert into t_user (id, username, password, age, sex, email) VALUES 
(null,'a','a',22,'男','123@163.com'),
(null,'a','a',22,'男','123@163.com'),
(null,'a','a',22,'男','123@163.com');

方式二：使用set

语法：

insert into 表名 
set 列名=值,列名=值,
...;

示例

insert into t_user 
set id = null , username = 'a' , password = 'b' , age = 23 , sex = '女' , email = '111@11.com';

比较
1.方式1支持插入多行，方式2不支持
2.方式1支持子查询，方式2不支持

修改语句：update

修改单表的记录

语句：

update 表名 
set lie=新值,列=新值,
... 
where 筛选条件;

示例

update t_user set username = 'aaa' , password = 'b' where id =` 1 ;

修改多表的记录

语法：

• sql 92语法：

update 表1 别名 ,表2 别名 
set 列=值
...
where 连接条件
and 筛选条件;

示例

update t_emp e , t_dept d set e.emp_name = 'cc' 
where e.did = d.did and e.age > 22;

• sql 99语法：

update 表1 别名 
inner|left|right join 表2 别名 on 连接条件 
set 列=值
... 
where 筛选条件;

示例

update t_emp e inner join t_dept d on e.did = d.did 
set e.emp_name = 'cc' where e.age > 22;

删除语句delete

方式一：delete 将满足条件的删除（删除行）

语法：

单表

语法

delete from 表名 while 筛选条件

示例

delete from t_dept where did  = 2 ;

多表

sql 92语法：

delete 表1的别名 ,表2的别名 
from 表1 别名,表2 别名 
where 连接条件 
and 筛选条件;

sql 99语法：

delete 表1的别名 ,表2的别名 
from 表1 别名 
inner|left|right join 表2 别名 on 连接条件 
where 筛选条件;

示例1

• sql92语法

delete e , d from t_emp e , t_dept d where e.did = d.did and e.age > 22;

示例2

• sql99语法

delete e , d from t_emp e inner join t_dept d on e.did = d.did where e.age > 22;

方式二：truncate 将整个表删除

语法

truncate table 表名;

示例

truncate table t_dept;

DDL(Data Definition Language)数据定义语言

用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等

库的管理

库的创建

语法结构

create database [if not exists] 库名;

示例

create database if not exists my_database;

库的修改

修改库名

rename database 库名 to 新库名;

mysql 不支持修改库名

rename database my_database to my_new_database;

更新库的字符集

alter database 库名 character set 字符格式;

示例

alter database mybatis character set 'utf-8';

库的删除

语法

drop database [if exits] 库名;

示例

drop database if exists mybatis;

表的管理

表的创建

语法结构
create table 【if not exists】表名(
列名 列的类型 【长度 约束】,
列名 列的类型 【长度 约束】,
列名 列的类型 【长度 约束】,
列名 列的类型 【长度 约束】,
…
列名 列的类型 【长度 约束】

– 示例
create table if not exists my_database(
id int primary key ,
name varchar(20) not null ,
age int ,
sex char(2)
);

表的修改

完整语法

alter table 表名 
add|drop|modify|change 
column 列名 【列类型 约束】

修改列名

alter table 表名 change column 旧列名 新列名 列类型;

示例

alter table my_database modify sex gender varchar(10) ;

修改列的类型或约束

alter table 表名 modify column 列名 新的类型|新约束;

示例

alter table my_database modify sex varchar(10) not null ;

添加新列

alter table 表名 add column 添加的列名 对应的类型|约束;	

示例

alter table my_database add  password varchar(20) not null ;

删除列

语法

alter table 表名 drop column 要删除的列名;	

示例

alter table my_database drop password ;

修改表名

语法

alter table 表名 rename to 新表名;	

示例

alter table my_database rename to my_new_database ;

表的删除

语法

drop table 【if exists】 表名;

示例

drop table if exists my_database;

表的复制

1.仅仅复制表的结构

create table 表名 like 要复制的表名;

– 示例
create table my_database like t_dept ;

2.复制表的结构 + 数据(*是所有的数据，也可以自己指定要复制的数据)

create table 表名 select * from 要复制的表名;

– 示例
create table my_database select * from t_dept ;

3.仅仅复制部分表结构（部分字段）

0为false，1为true 条件恒不成立
语法

create table 表名 select 要复制的字段列表 from 要复制的表名 where 0;

示例

create table my_database select dep_name from t_dept where 0 ;

常见的数据类型

数值型：

整数：BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT

小数：

浮点数：FLOAT、DOUBLE
定点数：DECIMAL

字符型：

较短的文本：char，varchar
较长的文本：text，blob（较长的二进制数据）

日期型：

Date、DateTime、TimeStamp、Time、Year

其他数据类型

BINARY、VARBINARY、ENUM、SET、Geometry、Point、MultiPoint、LineString、MultiLineString、Polygon、GeometryCollection等

约束

含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性

分类：六大约束

• not null: 非空，用于保证该字段的值不能为空
• default： 默认，用于保证该字段有默认值
• primary key： 主键，用于保证该字段的值具有唯一性，并且非空
• unique： 唯一，用于保证该字段的值具有唯一性，可以为空
• check： 检查约束【 mysql中不支持 】
  -f oreign key： 外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值

添加约束的时机：

1.修改表时

• 添加列级约束
  语法结构

alter table 表名 modify column 字段名 字段类型 新约束;

示例

alter table stuinfo modify column seat int not null

• 添加表级约束
  语法结构

alter table 表名 add 
【 constraint 约束名 】 约束类型（字段名） 【 外键的引用 】;

示例

alter table stuinfo add 
constraint fk foreign key(majorId) reference major(id)

2.创建表时

• 添加列级约束
  语法：直接在字段名或数据后面追加 约束类型
  mysql只支持：默认，非空，主键，唯一
  示例

create table stuinfo(
id int primary key, #主键
stuName varchar(20) not null, #非空
gender char(1) check(gender = '男' or gender = '女'), #检查
seat int unique, #唯一
age int default 18, #默认约束
majorId int foreign key references major(id) #外键
);

create table major(
id int primary key,
majorName varchar(20)
);

• 添加表级约束
  语法：在各个字段的下面追加，使用condtraint
  示例

create table stuinfo(
id int,
stuName varchar(20),
gender char(1),
seat int,
age int,
majorId int foreign key references major(id) #外键
constraint pk primary key(id), #主键
constraint uq unique(seat), #唯一
constraint ck check(gender = '男' or gender = '女') #检查
);

约束的添加分类：

• 列级约束：
  六大约束都可以
• 表级约束：
  通用写法：一般外键添加表级约束
  示例

create table stuinfo(
id int primary key, #主键
stuName varchar(20) not null, #非空
gender char(1) check(gender = '男' or gender = '女'), #检查
seat int unique, #唯一
age int default 18, #默认约束
majorId int,
constraint fk foreign key(majorId) references major(id) #外键
);

主键和唯一的比较

外键约束：

1. 要求在从表设置外键关系
2. 从表的外键列的类型和主表的关联列的类型要求是一致或兼容，名称无要求
3. 主表的关联列必须是一个key（一般是主键或唯一）