MySQL老杜课程学习笔记 (入门级)(一)
MySQL的安装卸载以及MySQL服务的启停可自行搜索教程,本文不做赘述(附带一个视频链接)
MySQL-003-安装MySQL_哔哩哔哩_bilibili
1、sql、DB、DBMS分别是什么,他们之间的关系?
- DB:
DataBase(数据库,数据库实际上在硬盘上以文件的形式存在)
- DBMS:
DataBaseManagementSystem(数据库管理系统,常见的有:MySQL,Oracle,DB2,Sybase,SqlServer…)
- SQL:
结构化查询语言,是一门标准通用的语言。标准的sql适合于所有的数据库产品。SQL属于高级语言。只要能看懂英语单词的,写出来的sql语句,可以读懂什么意思。SQL语句在执行的时候,实际上内部也会先进行编译,然后再执行sql。(sql语句的编译由DBMS完成。)
DBMS负责执行sql语句,通过执行sql语句来操作DB当中的数据。数据管理系统是为了开发数据库的方便而诞生的一种服务,开发人员需要在里边通过SQL语言编写程序,再交给DBMS分析执行
DBMS-(执行)->SQL-(操作)->DB
2、表
表:table是数据库的基本组成单元,所有的数据都以表格的形式组织,目的是可读性强。
-
行:被称为数据/记录(data)
-
列:被称为字段(column)
字段和记录是名称规范,一行就称为一个记录,列就称为字段。
学号(int) | 姓名(varchar) | 年龄(int) |
---|---|---|
110 | 张三 | 20 |
120 | 李四 | 21 |
每一个字段应该包括哪些属性?
字段名、数据类型、相关的约束(常用的有唯一性约束和非空约束)、默认值(可自行设定,默认为NULL)
show tables 命令可以显示当前使用的数据库下所有表
3、SQL语句分类
DQL(数据查询语言):查询语句,凡是select语句都是DQL。
DQL全是查询语句,不会改变数据本身,只是用特定的方式按需求显示出一张表或多张表的数据
DML(数据操作语言):对表中的数据进行增删改。
- insert 增
- delete 删
- update 改
DDL(数据定义语言):对表结构的增删改。
- create 新建
- drop 删除
- alter 修改
此处的增删改针对的是对表结构的修改,与DML不同,注意区分。在实际开发中,表的设计是第一步。在后续开发过程中,往表中维护数据,如果突然对表的结构,比如说修改字段名,或者删除某个表,将会需要大量更改之前的脚本,因此在实际开发中,一旦表设计完成,创建出对应表,DDL语句基本不再使用。
TCL(事务控制语言):TCL中的T是transaction
事务具有是一种原子操作,这是很关键的,原子操作在任何编程语言中都有很高的地位。所谓原子就是绑定执行,多条语句要么同时执行成功,要么失败,不可再分。
-
commit 提交事务
-
rollback 回滚事务
DCL(数据控制语言):
对于一般程序员,数据控制语言可以不用了解,这是针对专门从事数据库开发的工作人员的命令。
-
grant 授权
-
revoke 撤销权限
4、导入数据到自己的数据库
第一步:登录mysql数据库管理系统
Dos(window终端)命令窗口:
mysql -uroot -p 自己sql服务的密码
第二步:查看数据库管理系统中有哪些数据库
show databases;
注意分号不能省略,这不是SQL语句,属于MySQL的命令。
第三步:创建属于自己的数据库
create database 数据库名字(本文使用bjpowernode作为名字);
同样,这个也不是SQL语句,属于MySQL的命令。
第四步:使用bjpowernode数据
use bjpowernode;
第五步:查看当前使用的数据库中有哪些表
show tables;
第六步:初始化数据(导入数据)
mysql> source D:\course\05-MySQL\resources\bjpowernode.sql
其中mysal> 表示终端命令,在dos终端登录mysql服务后自动变为mysql 可自行尝试
5、SQL脚本文件
当一个文件的扩展名是.sql,并且该文件中编写了大量的sql语句,我们称这样的文件为sql脚本。
注意:直接使用source命令可以执行sql脚本。sql脚本中的数据量太大的时候,用记事本打开查看,系统会崩溃,用source命令完成初始化。
6、删除数据库:
drop database bjpowernode;
7、查看表结构:
mysql> desc 表名; 例如:mysql> desc dept;
8、查看表中所有数据
mysql> select * from 表名; 例如:mysql> select * from emp;
9、几个常用的命令
mysql> select database();查看当前使用的是哪个数据库
mysql> select version();查看mysql
的版本号`
键入“\c”,可以结束一条语句、exit命令,退出mysql。
10、查看创建表的信息
show create table emp; 该语句用于显示emp表的详细创建信息,比如什么时间创建的
11、简单的查询语句(DQL)
select 字段名1,字段名2,字段名3,.... from 表名;
select * from 表名;
*符号可以表示查询所有字段,即完整显示表格
注意:
1、任何一条sql语句以“;”结尾。
2、sql语句不区分大小写。
3、在select语句中 字段可以参与数学运算 例如:select 字段名* 2 from 表名;
将该字段所有数值2倍后显示
给查询结果的字段重命名(并不会改变数据库本身的数据,只是显示出来的结果改变)
select ename,sal*12 as yearsal from emp;
表示给sal 12 这个字段重命名为yearsal
select ename,sal*12 as '年薪' from emp;
如果别名为中文,需要用单引号
注意:标准sql语句中要求字符串使用单引号括起来。虽然mysql支持双引号,尽量别用,单引号具有更好的兼容性。
另外,其实as关键字可以省略,但加上会增加可读性
mysql> select empno no,ename name,sal*12 yearsal from emp;
给empno起别名no ,ename起别名no,sal 12 起别名 yearsal
12、条件查询
1.基本语法
语法格式:
select
字段,字段…
from
表名
where
条件;
条件就是常见的’>’ 、‘<’、‘=’、‘>=’、‘<=’ 可以与逻辑‘and’ ‘or’ ‘not’组合使用,and的优先级大于or。在sql中用‘<>’表示不等于,但是也支持‘!=’,但前者是规范。表示区间不仅可以将and与表示大小关系的条件连用,还可以使用between and 关键字
找出工资在1100和3000之间的员工,包括1100和3000
select ename,sal from emp where sal>=1100 and sal<=3000;
select ename,sal from emp where sal between 1100 and 3000;
注意:
-
between and在使用的时候必须左小右大
-
between…and…是闭区间[1100~3000]
between and除了可以使用在数字方面之外,还可以使用在字符串方面。但此时区间为左闭右开
select ename from emp where ename between 'A' and 'C';
在数据库当中NULL不是一个值,代表什么也没有,为空。空不是一个值,不能用等号衡量。必须使用is null或者is not null。
select ename,sal,comm from emp where comm is null;
找出emp表中comm不为空的数据
在sql语句中,in关键字可以用来平替or。相对的,也有not in关键字。
select ename,job from emp where job='SALESMAN' or job='MANAGER';
select ename,job from emp where job in('SALESMAN','MANAGER');
找出工作岗位是MANAGER和SALESMAN的员工
但注意,如果in作用于数字,表示的是等于,而不是区间
select ename,job from emp where sal in(800,5000);
查询工资等于800和5000的人,而不是在这个区间的人
2.模糊查询
模糊查询一把可结合通配符使用,在sql中,'%'代表任意字符,‘_’代表单个字符
select ename from emp where ename like '%O%';
查出名字中带有字母O的记录
select ename from emp where ename like 'A%';
找出名字中一个字母是A的
select ename from emp where ename like '%A';
找出名字中最后一个字母是A的
select ename from emp where ename like '_A%';
找出名字中第二个字母是A的
select ename from emp where ename like '%\_%';
找出名字中带有下划线的,由此可以看出,sql支持转义字符
13、排序(升序、降序)
order by关键字 默认是升序。可以指定升序或降序,asc表示升序,desc表示降序。
select ename,sal from emp order by sal;//升序
select ename,sal from emp order by sal asc;//升序
select ename,sal from emp order by sal desc;//降序
select ename,sal from emp order by sal desc,ename asc;//按工资降序排列,当工资相同的时候再按名字升序排列。
注意:越靠前的字段越能起到主导作用。只有当前面的字段无法完成排序的时候,才会启用后面的字段。
14、单行处理函数
DQL语言用于数据查询演示,处理函数的作用是辅助DQL完成想要显示的数据,因此处理函数作用于字段,是对要显示的字段数据进行处理的,分为单行处理函数和多行处理函数,但处理的对象均是字段,也就是说这些函数的参数都是字段名。
单行处理函数:输入一行,输出一行
-
lower 转换成小写字母
select lower(ename) from emp;
-
upper 转换成大写字母
-
substr 取子字符串
select sunstr(ename,0,3) from emp; 其中1代表起始位置,3代表要截取的长度
注意:substr(被截取字符,起始下标,截取长度),其中起始下标是从1开始的
-
length 取长度
-
trim 去除空格字符
-
str_to_date 将字符串格式化为date数据类型(后续有详细讲解)
-
date_format 格式化日期输出(转换成能显示的字符串,并且可指定显示格式)
-
formt 设置千分位
-
round 四舍五入
-
rand 生成【0-1】随机数
-
ifnull 空处理函数
重点:所有数据库都是这样规定的,只要有NULL参与的运算结果一定是NULL。但可以用innull函数来解决此问题。
select ename,(sal+ifnull(comm,0))*12 as year sal from emp;
ifnull()空处理函数,将comm中为null 的指定为0
15、分组函数
所谓分组函数,就是对某一组的数据进行处理。分组函数还有另一个名字:多行处理函数。多行处理函数的特点:输入多行,最终输出的结果是1行。
-
count计数
-
sum求和
-
avg平均值
-
max最大值
-
min最小值
注意:
-
分组函数会自动忽略NULL
-
*where语句中不能直接使用分组语句,这是由于sql语句的执行顺序决定的,sql语句是先执行were语句,再执行分组语句(group by),如果where语句中出现了分组函数,但由于此时还没执行分组,所以会造成语义错误。
-
select 5
..
from 1
..
where 2
..
group by 3
..
having 4
..
order by 6
..
limit 7
上述是比较完整的一个DQL语句的执行顺序,数字代表执行的先后
-
count(*)和count(具体的某个字段)
count(*):是统计总记录条数。(和某个字段无关)
count(comm):表示统计comm字段中不为NULL的数据总数量。 -
分组函数也能组合起来用:
select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;
找出工资高于平均工资的员工
select ename,sal from emp where sal>(select avg(sal) from emp); 找出工资高于平均工资的员工。
用到了select的嵌套语句`,称为子查询,之后会有讲解
16、group by和having
group by:按照某个字段或者某些字段进行分组。
having: 对分组之后的数据进行再次过滤,where是在分组之前过滤的,因此having是进一步过滤。
select max(sal),job from emp group by job;
找出每个工作岗位的最高薪资
注意:分组函数一般都会和group by联合使用,这也是为什么它被称为分组函数的原因。并且任何一个分组函数都是在group by语句执行结束之后才会执行的。当一条sql语句没有groupby的话,整张表的数据会自成一组。
select ename,max(sal),job from emp group by job;
以上在mysql当中,查询结果是有的,但是结果没有意义,在Oracle数据库当中会报错。语法错误。Oracle的语法规则比MySQL语法规则严谨。记住一个规则:当一条语句中有groupby的话,select后面只能跟分组函数和参与分组的字段。
select job,avg(sal) from emp group by job; 每个工作岗位的平均薪资
多个字段联合起来分组找出每个部门不同工作岗位的最高薪资:
select deptno,job,max(sal) from emp group by deptno,job;
找出每个部门的最高薪资,要求显示薪资大于2900的数据:
select max(sal),deptno from emp group by deptno having max(sal)>2900;
//这种方式效率低。`
select max(sal),deptno from emp where sal>2900 group by deptno;
//效率较高,建议能够使用where过滤的尽量使用where。
但有时必须使用having语句,如下:
找出每个部门的平均薪资,要求显示薪资大于2000的数据
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;//正确
select deptno,avg(sal)from emp where avg(sal)>2000 group by deptno;//错误
where后面不能使用分组函数:这种情况只能使用having过滤。
17、总结一个完整的DQL语句怎么写?
select 5
..
from 1
..
where 2
..
group by 3
..
having 4
..
order by 6
..
limit 7
上述是比较完整的一个DQL语句的执行顺序,数字代表执行的先后