MySQL数据库：SQL语言入门 （学习笔记）

SQL（Structured Query Language）是结构化查询语言的简称，它是一种数据库查询和程序设计语言，同时也是目前使用最广泛的关系型数据库操作语言。（95%适用于所有关系型数据库） 【 SQL是关系型数据库通用的操作语言】

在数据库管理系统中，使用SQL语言来实现数据的存取、查询、更新等功能。SQL是一种非过程化语言，只需提出“做什么”，而不需要指明“怎么做”。

SQL语言具有集数据查询、数据操纵、数据定义和数据控制功能于一体，类似自然语言、简单易用以及非过程化等特点，

数据库管理人员（DBA）通过数据库管理系统（DBMS）可以对数据库（DB）中的数据进行操作，

（分页查询语句不同）
MySQL:  limit ?, ?
orcale: top 5 where 

• 介绍MySQL数据库

mysql是一款开放源码，轻量级的关系型数据库
具有体积小，数据快，成本低，开放源码等特点
使用sql语言对数据库进行管理

一，SQL语言组成部分（5）

DQL* 数据的查询语言：查询语句的6个组成部分

DML* 数据的操作语言：insert delete update  

DDL 数据的定义语言：（了解）创建/删除/修改 数据库对象

DCL 数据的控制语言：（了解）授予，撤回 

TCL* 事务的控制语言：提交，回滚  

1，DQL —— 数据查询语言（Data Query Language）

           主要用于数据的查询，其基本结构是使用 select 子句，from 子句和 where 子句的组合来查询一条或多条数据。

（1）组成结构

select  字段名，字段名，字段名，

from  表名，表名，表名

where  条件  and/or 条件 and/or 条件 ———— 【分组前条件判断】

group by  字段名，字段名，字段名————【分组】

having  条件  and/or  条件  and/or  字段名————【分组后条件判断】

order  by 字段名，字段名，字段名————【排序】

【select 语句的执行顺序】
from--where -- group  by–  select   -  having-   order  by

（2）单表查询

【1】介绍 select 部分

===查指定的部位（与表内数据的顺序不关）

 select ename,empno from emp

===查表内全部数据 

 select * from emp

===别名——as

      ----- 真实案例 ：实体类 属性名写错了；   多表查询的时候

           ---改名--》deptno改成eptno

select empno,ename,job,deptno as eptno from emp

      ----- 真实案例 开发组 每周会议

           ---写别名  as可以省略

           --- '  '  可加可不加，如果别名类里面有特殊符号或者空格  必须用！！！

select empno 员工编号,ename 员工姓名,job 岗位名称,deptno as '部门编号' from emp

===简单的算术运算

       ----- 查询表里所有员工的年薪是多少？  （忽视了奖金，流程函数处补全）

              ---空值做任何运算结果都为空！！！

select sal*12 年薪，ename,comm from emp

===去重操作   

去重关键字  distinct

       ----- 查询员工都在哪几个部门工作？

select distinct deptno from emp 
select distinct job,deptno from emp; -- 对后面的所有列组合 去重 ，而不是单独的某一列去重

===介绍 order by

默认情况下是按照升序排列的

 asc---升序 （可以默认不写）
desc---降序

select * from emp order by sal; -- 默认情况下是按照升序排列的
select * from emp order by sal asc; -- asc 升序，可以默认不写
select * from emp order by sal desc; -- desc 降序
select * from emp order by sal asc ,deptno desc; -- 在工资升序的情况下，deptno按照降序排列


select deptno,count(*) num
from emp
where year(hiredate)=1981
GROUP BY deptno        
order by num,deptno desc

【2】介绍  where  部分

将过滤条件放在where子句的后面，可以筛选/过滤出我们想要的符合条件的数据：

===关系运算符 

= ， > ，<  ，>= ，<= ，!= ，<>  (最后两个都是  不等于)

      -----查询入职时间在1981年的所有员工信息

 select * from emp where hiredate>='1981-1-1' and hiredate<='1981-12-31'

      -----查寻部门编号不是30的所有员工信息

select * from emp where deptno!=30

      -----查询工资范围在1000--2000的所有员工信息

select * from emp where sal>1000 and sal<2000

      -----查询经理编号是7321的所有员工信息

select * from emp where mgr=7321

===逻辑运算符   

and  &&     or  | |        in———替换 or

      -----查寻部门编号是10或者20的所有员工信息  

select * from emp where deptno=10 or deptno=20
select * from emp where deptno in (10,20)

===空值判断

       ..... where is null    ————为空
       ..... where is not null ————不为空

      -----查询没有奖金的所有员工信息

select * from emp where comm is null 

===模糊查询     

% ：0~n位任意字符    ；   _  ：1位任意字符

       ----- 查询名字第三位是a的

 select * from emp where ename like '__a%'

       ----- 查询员工姓名里以A开头的员工信息

select * from emp where ename like 'A%'

       ----- 查询岗位名称里，带A或者带C的员工信息

select * from emp where job like '%A%' or '%C%'

       ----- 查询 没有奖金，不是20号部门 ，名字里没有A的员工信息

select * from emp where comm is null and deptno!=30 and ename not like '%A%'

===小括号的使用

因为不同的运算符的优先级别不同，加括号为了可读性

select * from emp where job = 'SALESMAN' or job = 'CLERK' and sal >=1500; -- 先and再or  and > or
select * from emp where job = 'SALESMAN' or (job = 'CLERK' and sal >=1500); 
select * from emp where (job = 'SALESMAN' or job = 'CLERK') and sal >=1500;

【3】函数

• 函数只是对查询结果中的数据进行处理，不会改变数据库中数据表的值。
• 使用函数可以大大提高SELECT语句操作数据库的能力，同时也给数据的转换和处理提供了方便。 （在sql中使用函数）
• 函数的功能：封装了特定的一些功能，我们直接拿过来使用，可以实现对应的功能
• 函数作用：为了提高select的能力

===分类：   

    单行函数  (进去一条数据，出来一条数据)

    多行函数/分组函数/聚合函数（进去一组数据，出来一条数据）

• 除了多行函数（max,min,count,sum,avg），都是单行函数

===单行函数

      ----- 字符串函数

              拼接：concat(str1,str2,。。。strn)

                       ---查询所有  部门编号员工编号员工姓名  的员工信息

select concat(deptno,'_',empno,'_',ename) from emp  

             添加：insert(str,index,n,newstr)

                       ---查询所有 长春吉软_员工姓名 的员工信息

select ename,insert(ename,1,0,'长春吉软_') from emp

             总长度：length(str)

                       ---查询所有 员工姓名长度 大于5位的

select * from emp where length(ename)>5
-- 仅查询了 字节数和员工姓名
select length(ename),ename from emp where length(ename)>5

             替换：replace(str,oldstr,newstr)

              获取：substring(str,index,n)

                       ---查询所有  员工姓名（保留第一位和最后一位字母，中间用。。。代替）

 SELECT REPLACE(ename,substring(ename,2,length(ename)-2),'...') FROM emp
-- 拼接和获取
SELECT CONCAT(substring(ename,1,1),'...',substring(ename,length(ename),1)) FROM emp;

      ----- 日期时间函数

dual ---虚拟表

       ----- 房租一个月后到期，问到期时间？

select ADDDATE(NOW(),INTERVAL 1 MONTH) from dual

       ----- 查询所有员工试用期通过的时间

select  ename,ADDDATE(hiredate,INTERVAL 3 MONTH) from emp

       ----- 查询所有员工里工龄超过20年的所有员工

select * from emp where (DATEDIFF(NOW(),hiredate)/365)>20
select * from EMP WHERE SUBDATE(Now(), INTERVAL 20 YEAR) > hiredate  -- 减
select * from emp where adddate(hiredate,interval 20 year)<now()   -- 加

       ----- 查询所有员工，在1981年9月份入职的所有员工

select * from emp where year(hiredate) = 1981 and month(hiredate) = 9

       -----查询入职时间超100天的所有员工

select DATEDIFF(NOW(),hiredate) from emp where DATEDIFF(NOW(),hiredate)>100

                                        时间与日期函数 

      ----- 流程函数

             ----- 查询表里所有员工的年薪是多少？

select sal*12+ IFNULL(comm,0) 年薪 from emp

              ----- 查询所有员工编号，姓名，以及所在的部门名称（单表查询）

select empno,ename, 
case deptno 
       when 10 then '开发部'
       when 20 then '测试部'
       when 30 then '运维部'
       when 40 then '实施部'
       else '最后' end 部门名称 
 from emp

                                                       流程函数 

===多行函数/分组函数/聚合函数

 max()—最大值 ；min()—最小值 ；avg()—平均值 ；sum()—求和 ；count()—求个数 ；

select max(sal),min(sal),avg(sal),sum(sal),count(sal) from emp

      ----- 介绍 group by 和 having 

             ----- 查询每个部门里 的最大工资，最小工资，平均工资

select max(sal),min(sal),avg(sal) 
from emp 
group by deptno

             ----- 查询 每个岗位的最高工资，最低工资，平均工资

select job,max(sal),min(sal),avg(sal) 
from emp
GROUP BY job

             ----- 查询 1000元以上的每个工资的总人数，总人数低于5个的部门不显示

select deptno,count(*) c
from emp 
where sal>1000 
GROUP BY deptno 
having c>=5

             ----- 查询 每个经理人所带的手下员工的总人数

select mgr,count(*)
from emp
GROUP BY mgr

             ----- 查询 在1981年入职的各部门总人数

select deptno,count(*)
from emp
where year(hiredate)=1981
GROUP BY deptno

                                                        其他函数

（3）多表查询

===分类

   内连接查询，外连接查询

-- from部分
select * from emp,dept (缺少关联关系)    造成--笛卡尔积现象   15*3=45

===内连接查询

       ----- 【写法一】 

select * from emp,dept where emp.deptno=dept.deptno 

        ----- inner join  . . . on    【写法二】     

    inner join —— 内连接  ； on —— 通过on做两张表之间的关联查询

select * from emp inner join dept on emp.deptno=dept.deptno 

        ----- 关键字  using        【写法三】

     要求：两张表之间的关联列是同名的

select * from emp inner join dept using(deptno)  

        ----- 自然连接               【写法四】 

      要求：两张表之间的关联列 同名，同类型，同长度

select * from emp e natural join dept d 

        ----- 自身内连接 （一张表）

             ----- 例子：查找每个经理的名字，入职时间，和工资

select distinct m.empno,m.ename,m.hiredate 
from emp m,emp e
where m.empno=e.mgr

===外连接查询

左外连接   右外连接

    ——区分原理：一个查询的SQL语句里，第一个表名 叫 左表；第二个表名叫 右表。

                              如果是内连接查询，左右两个表的地位是一样的

select * from t_emp,dept where dept.deptno=t_emp.deptno 

                               如果使用左外连接查询，那么左表是主表，主表里的数据都会显示出来；右表是子表，子表里的数据，只显示符合连接条件的。

select * from t_emp left join dept on dept.deptno=t_emp.deptno

-- 右表做主表
select * from t_emp right join dept on dept.deptno=t_emp.deptno

（4）子查询

{没有子查询，可能要执行很多次，才能得到最终结果  ；有子查询，执行一次就能拿到最终结果}

===分类

不相关子查询（简单）  ——单行不相关子查询

                                       ——多行不相关子查询

相关子查询  ----》存储过程

        ----- 单行不相关子查询

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

select * from emp where sal=(select max(sal) from emp)

              --- 查询工资高于平均工资的雇员名字和工资

select ename,sal from emp where sal>(select avg(sal) from emp)

        ----- 多行不相关子查询

             any————任何
             all————全部
             select * from emp where sal>any/all(select语句)

        ----- 相关子查询

               ---查询本部门最高工资的员工

select * from emp e 
where sal =(select max(sal) from emp where deptno=e.deptno) 
order by deptno

              --- 查询工资高于其所在岗位的平均工资

select * from emp e 
where sal>= (select avg(sal) from emp where emp.job=e.job) 
order by deptno desc                        

2，DML —— 数据操作语言（Data Manipulation Language）

           主要用于对数据库中的数据进行增加、修改和删除的操作。

（1）insert   增加 数据

insert into 表名（字段名,,,字段名）values（值,,,值） 

insert into 表名 values（值,,,值）

• 一次添加1条：insert into 表 values(值，值，值)
• 一次添加n条：insert into 表 values(值，值，值)，(值，值，值)，(值，值，值)

------ 在t_student数据库表中插入数据

insert into t_student values (1,'张三','男',18,'2022-5-8','软件1班','123@126.com');
insert into t_student values (10010010,'张三','男',18,'2022-5-8','软件1班','123@126.com');
insert into t_student values (2,'张三','男',18,'2022.5.8','软件1班','123@126.com');
insert into t_student values (2,"张三",'男',18,'2022.5.8','软件1班','123@126.com');
insert into t_student values (7,"张三",'男',18,now(),'软件1班','123@126.com');
insert into t_student values (9,"易烊千玺",'男',18,now(),'软件1班','123@126.com');
insert into t_student (sno,sname,enterdate) values (10,'李四','2023-7-5');

（2）delete  删除 数据       truncate  删除表和数据，再创建一个新表

delete from 表名 where 条件 and/or 条件

delete from t_student 【慎用】

delete from t_student where sno = 2;
truncate table t_student;

• delete 和 truncate 的区别

1. delete为数据操作语言DML；truncate为数据定义语言DDL。
2. delete操作是将表中所有记录一条一条删除直到删除完；truncate操作则是保留了表的结构，重新创建了这个表，所有的状态都相当于新表。因此，truncate操作的效率更高。
3. delete操作可以回滚；truncate操作会导致隐式提交，因此不能回滚（在第十章中会讲解事务的提交和回滚）。
4. delete操作删除表中记录后，再次向表中添加新记录时，对于设置有自增约束字段的值会从删除前表中该字段的最大值加1开始自增；truncate操作则会重新从1开始自增。 
5. delete操作执行成功后会返回已删除的行数（如删除4行记录，则会显示“Affected rows：4”）；截断操作不会返回已删除的行量，结果通常是“Affected rows：0”。

（3）update  修改 数据

update 表名 set  字段=值，字段=值  where 条件 and/or 条件
update 表名 set  字段=值，字段=值 【慎用】

-----修改表中数据

update t_student set sex = '女' ;
update t_student set sex = '男' where sno = 10 ;
UPDATE T_STUDENT SET AGE = 21 WHERE SNO = 10;
update t_student set CLASSNAME = 'java01' where sno = 10 ;
update t_student set CLASSNAME = 'JAVA01' where sno = 9 ;
update t_student set age = 29 where classname = 'java01';

注意事项

• 关键字，表名，字段名不区分大小写
• 默认情况下，内容不区分大小写
• 删除操作from关键字不可缺少
• 修改，删除数据别忘记加限制条件 

3，DDL —— 数据定义语言（Data Definition Language）

          主要用针对是数据库对象（数据库、表、索引、视图、触发器、存储过程、函数）进行创

建、修改和删除操作。

（1）create  创建 数据库对象

create  table '表名'（

      '字段名'  类型（长度）约束  注释，

      '字段名'  类型（长度）约束  注释，

      '字段名'  类型（长度）约束  注释

）；

 创建表的时候，同时添加多条数据：
            create table t_emp
            as
            select e.*,d.dname,d.loc 
            from emp e,dept d where e.deptno=d.deptno

-- 查看表的结构：展示表的字段详细信息
desc t_student; 
-- 查看表中数据：
select * from t_student;
-- 查看建表语句：
show create table t_student;

（2）drop  删除 数据库对象      

drop  table  '表名'；

drop table t_student;

（3）alter  修改 数据库对象

alter  table '表名'；

----- 修改表的结构：
----- 增加一列：

alter table t_student add score double(5,2) ; -- 5:总位数  2：小数位数 
update t_student set score = 123.5678 where sno = 1 ;

----- 增加一列（放在最前面）

alter table t_student add score double(5,2) first;

----- 增加一列（放在sex列的后面）

alter table t_student add score double(5,2) after sex;

----- 删除一列：

alter table t_student drop score;

----- 修改一列：

-- modify修改是列的类型的定义，但是不会改变列的名字

-- change修改列名和列的类型的定义

alter table t_student modify score float(4,1); -- modify修改是列的类型的定义，但是不会改变列的名字
alter table t_student change score score1 double(5,1); -- change修改列名和列的类型的定义

（4）字段类型

#1——整数类型  int

MySQL支持选择在该类型关键字后面的括号内指定整数值的显示宽度(例如，INT(4))。显示宽度并不限制可以在列内保存的值的范围，也不限制超过列的指定宽度的值的显示
主键自增：不使用序列，通过auto_increment，要求是整数类型 

#2——浮点型   double（m，n）【m--一共的位数；n---小数点后的位数】

需要注意的是与整数类型不一样的是，浮点数类型的宽度不会自动扩充。 score double(4,1)
 score double(4,1)--小数部分为1位，总宽度4位，并且不会自动扩充。 

#3——字符串型   varchar（20）【对应Java的String型】

CHAR 和  VARCHA 类型相似，均用于存于较短的字符串，主要的不同之处在于存储方式。CHAR类型长度固定，VARCHAR类型的长度可变。

因为VARCHAR类型能够根据字符串的实际长度来动态改变所占字节的大小，所以在不能明确该字段具体需要多少字符时推荐使用VARCHAR类型，这样可以大大地节约磁盘空间、提高存储效率。

CHAR和VARCHAR表示的是字符的个数，而不是字节的个数 。

注意事项

• int  宽度是显示宽度，如果超过，可以自动增大宽度 int底层都是4个字节
• 时间的方式多样  '1256-12-23'  "1256/12/23"  "1256.12.23"
• 字符串不区分单引号和双引号
• 如何写入当前的时间  now() , sysdate() , CURRENT_DATE()
• char varchar 是字符的个数，不是字节的个数，可以使用binary，varbinary表示定长和不定长的字节个数。
• 如果不是全字段插入数据的话，需要加入字段的名字
   

#4——时间日期   date/datetime 【时间】

TIMESTEMP类型的数据指定方式与DATETIME基本相同，两者的不同之处在于以下几点：
(1) 数据的取值范围不同，TIMESTEMP类型的取值范围更小。
(2) 如果我们对TIMESTAMP类型的字段没有明确赋值，或是被赋与了NULL值，MySQL会自动将该字段赋值为系统当前的日期与时间。
(3) TIMESTEMP类型还可以使用CURRENT_TIMESTAMP来获取系统当前时间。
(4) TIMESTEMP类型有一个很大的特点，那就是时间是根据时区来显示的。例如，在东八区插入的TIMESTEMP数据为2017-07-11 16:43:25，在东七区显示时，时间部分就变成了15:43:25，在东九区显示时，时间部分就变成了17:43:25。   

（5）认识数据库表

• 表（Table）是数据库中数据存储最常见和最简单的一种形式，
• 数据库可以将复杂的数据结构用较为简单的二维表来表示。
• 二维表是由行和列组成的，分别都包含着数据。如图

• 每个表都是由若干行和列组成的，在数据库中表中为记录的行被称为记录，表中的列被称为是这些记录的字段。
• 字段是表里的一列，用于保存每条记录的特定信息。如上表所示的学生信息表中的字段包括“学号”、“姓名”、“性别”和“年龄”。数据表的一列包含了某个特定字段的全部信息。 

 （6）约束

• 为防止不符合规范的数据存入数据库，在用户对数据进行插入、修改、删除等操作时，
• MySQL提供了一种机制来检查数据库中的数据是否满足规定的条件，以保证数据库中数据的准确性和一致性，这种机制就是完整性约束。  

完整性----数据的准确性

#1——约束的使用场合

                DDL建表语句里。

#2——约束的种类（7）

#3——根据DDL建表语句里约束代码的位置，可以分为【约束从作用上可以分为两类：】

列级约束：

• 约束代码直接写在  字段名后面
• 包含在列定义中，直接跟在该列的其它定义之后 ，用空格分隔；不必指定列名；

————非空，唯一，默认值，自增，检查

表级约束：

• 约束代码脱离字段，单独一行存在
• 可以约束表中任意一个或多个字段。与列定义相互独立，不包含在列定义中；与定义用‘，’分隔；必须指出要约束的列的名称；

————主键，外键，检查

• 使用主键约束可以快速查找表中的记录。就像人的身份证、学生的学号等等，设置为主键的字段取值不能重复（唯一），也不能为空（非空），否则无法唯一标识一条记录。

• 设置了非空约束的字段，在插入的数据为NULL时，数据库会提示错误，导致数据无法插入。

•  一张表中只能有一个自增长字段，并且该字段必须定义了约束（该约束可以是主键约束、唯一约束以及外键约束），如果自增字段没有定义约束，数据库则会提示“Incorrect table definition; there can be only one auto column and it must be defined as a key”错误。
• 由于自增约束会自动生成唯一的ID，所以自增约束通常会配合主键使用，并且只适用于整数类型。一般情况下，设置为自增约束字段的值会从1开始，每增加一条记录，该字段的值加1

• 外键约束（FOREIGN KEY，缩写FK）是用来实现数据库表的参照完整性的。
• 外键约束可以使两张表紧密的结合起来，特别是针对修改或者删除的级联操作时，会保证数据的完整性。
• 外键是指表中某个字段的值依赖于另一张表中某个字段的值，而被依赖的字段必须具有主键约束或者唯一约束。被依赖的表我们通常称之为父表或者主表，设置外键约束的表称为子表或者从表。

#4——使用约束

（1）6种约束使用展示

create table t_student(
        sno int(6) primary key auto_increment, 
        sname varchar(5) not null, 
        sex char(1) default '男' check(sex='男' || sex='女'),
        age int(3) check(age>=18 and age<=50),
        enterdate date,
        classname varchar(10),
        email varchar(15) unique
);

（2）创建数据库表

    ——constraint   约束关键字

create table t_student(
        sno int(6) auto_increment, 
        sname varchar(5) not null, 
        sex char(1) default '男',
        age int(3),
        enterdate date,
        classname varchar(10),
        email varchar(15),
        constraint pk_stu primary key (sno),  -- pk_stu 主键约束的名字
        constraint ck_stu_sex check (sex = '男' || sex = '女'),
        constraint ck_stu_age check (age >= 18 and age <= 50),
        constraint uq_stu_email unique (email)

—— 在创建表以后添加约束：

-- 创建数据库表：
create table t_student(
        sno int(6), 
        sname varchar(5) not null, 
        sex char(1) default '男',
        age int(3),
        enterdate date,
        classname varchar(10),
        email varchar(15)
);

-- 在创建表以后添加约束：
alter table t_student add constraint pk_stu primary key (sno) ; -- 主键约束
alter table t_student modify sno int(6) auto_increment; -- 修改自增条件
alter table t_student add constraint ck_stu_sex check (sex = '男' || sex = '女');
alter table t_student add constraint ck_stu_age check (age >= 18 and age <= 50);
alter table t_student add constraint uq_stu_email unique (email);

（3）创建表后，添加外键约束   

    ————前者关联后者

alter table emp 
    add CONSTRAINT fk_emp_deptno FOREIGN key (deptno) REFERENCES dept(deptno)

4，DCL —— 数据控制语言（Data Control Language）

          用来授予或回收访问 数据库的权限。

（1）grant  授予用户某种权限

（2）revoke  回收授予的某种权限

5，TCL —— 事务控制语言（Transaction Control Language）

          用于数据库的事务管理。

•  删除数据的三种方式 以及之间的区别

1. delete  from 表  === 只删除数据，保留表    ；可以回滚   ； 添加数据时，下标继续累加。【DML数据操作语言】
2. truncate table 表 === 先把表以及表里的数据都删掉。然后再创建一个新表  ； 不能回滚  ；添加数据时，下标从新开始。【DDL数据定义语言】
3. drop table 表 ===删除表以及表里的数据  ；【DDL数据定义语言】