[入门学习记录]MySQL基本操作

猫咪老师
230 阅读7分钟

MySQL学习

image.png

MySQL的常用命令(不区分大小写，并以“；”结尾)

管理员命令下

查看MySQL数据库版本号：select version();

查看当前使用的是哪个数据库：select database();

mysql> exit 退出

登录mysql服务器（隐藏密码形式）：mysql -u root -p

查看MySQL中的数据库：show databases

使用某数据库：use 数据库名称（如：mysql)

创建数据库：create databases 数据库名称

删除数据库：drop database 名称

查看数据库中的表：show tables

\c终止命令语句的输入

数据中最基本的元素是表（table）

数据库中以表格形式表示数据

行（row）：数据/记录

列（column）：字段

SQL语句的分类：

  • DQL

    数据查询语言：凡是带有select关键字的都是查询语言

    • select…

  • DML

    数据操作语言：对表中的数据进行增删改查

    • insert

    • delete

    • update

      主要是对表中的数据进行操作

  • DDL

    数据定义语言

    • create

    • drop

    • alter

      主要是对表的结构进行操作

  • TCL

    事务控制语言

    • 事务提交：commit
    • 事务回滚：rollback

  • DCL

    数据控制语言

    • 授权：grant
    • 撤销权限：resolve
导入数据
  • mysql> source D:/Coding/MySQL/bjpowernode.sql（路径下不能有中文）
  • 查看表名：mysql> show tables
  • 查看表中的数据：select * from 表名
  • 不看表中的数据，只看表的结构：desc 表名

SQL语句

简单查询

查询一个字段：select 字段名 from 表名

查询多个字段：用“，”隔开 如： select 字段名1，字段名2 from 表名

查询所有字段：

  • select * from 表名（不建议）
  • select a,b,c… from 表名（把所有字段都写上）
  • 给查询的列起别名（原表不改变名字、只负责查询）：select 字段名 as 要改的名字 from 表名

字段中可以使用数学表达式

条件查询
select
    字段1，字段2，字段3，…
from
    表名
where
    条件;
#条件可由
#1.> = < != <>(!=)判断 
#2.若需要判断字符串 需加 ''  
# and并且   or或者    between……and……在……区间（闭区间）必须遵循左小右大
#and优先级比or高 若想让or先执行 条件加上括号
#在数据库中null不能使用=进行衡量 需要使用 is null  is not null
#in 包含 相当于多个or in不是一个区间 in后面跟的是具体的值 若有多个 则用括号和逗号括起来（ ，） 同样的 还有not in
模糊查询

like

称为模糊查询，支持%或_匹配

%匹配任意多个字符 _匹配任意一个字符

例如 找出名字里含有O的：

select ename from emp where ename like '%O%';
#以T结尾的
select ename from emp where ename like '%T';
#以K开头的
select ename from emp where ename like 'K%';
#第二个字母是A的
select ename from emp where ename like '_A%';
#第三个字母是R的
select ename from emp where ename like '__R%';
#若名字中本身含有下划线 则使用转义字符进行转义
select ename from emp where ename like '%_%';

排序

单个字段查询
select
    字段1，字段2，……
from
    表名
order by
    要筛选的字段（默认是升序）
#若要降序 则在order by 字段 desc;
​
select ename,sal from emp order by sal desc;
多个字段查询

薪资一样 才会按照名字升序排列

select
    字段1，字段2，…
order by
    字段1 asc,字段2 asc;
​
select ename,sal from emp order by sal asc,ename asc;
综合写法
select
    ……
from
    ……
where
    ……
order by
    ……
    
select ename,sal from emp where sal between 1250 and 3000 order by sal;

数据处理函数/单元处理函数

  • lower：转换小写

    select lower(字段) from 表名;

  • upper：转换大写

  • substr：取子串（被截取的字符串，起始下标，截取的长度）

    #起始下标从1开始
select substr(ename,1,1) as ename from emp;
​
#获取首字母是'A'的字符串
select ename from emp where substr(ename,1,1)='A'

  • length：取长度

  • trim：去空格

  • str_to_date：将字符串转换成日期

  • date_format：格式化日期

  • format：设置千分位

  • round：四舍五入

    select round(1234.567,0) as result from emp;
#0表示保留到整数位
#-1表示保留到十位
#-2表示保留到百位

  • rand()：生成随机数

    select rand() from emp;
#生成一百以内的 随机数
select round(rand()*100,0) from emp;

  • ifnull：可以将null转换成一个具体值

    #在所有数据库中，只要有NULL
#ifnull(数据，被当做哪个值)
select ename,(sal + ifnull(comm,0))*12 as yearsal from emp;

  • contact：字符串的拼接

  • case..when..then..when..then..else..end

    select ename,job,(case job when 'MANAGER' then sal*1.1 when'SALESMAN' then sal*1.5) else sal end) as newsal from emp;

分组函数（多行函数）

  • count：计数

    count(具体字段)：统计该字段下所有不为元素的总数

    count(*)：统计表中的总行数

  • sum：求和

  • avg：求平均值

  • max：最大值

    select max(sal) from emp;

  • min：最小值

    select min(sal) from emp;

分组函数在使用是必须先进行分组

若没有对数据进行分组 则整张表默认为一组

分组函数会自动忽略NULL 不需要对NULL进行处理

所有分组函数可以组合使用

分组查询

select
    ...
from
    ...
group by
    ...

以上关键词的顺序不能颠倒，执行顺序为：

  1. from
  2. where
  3. group by
  4. select
  5. order by
#按照工作岗位分组 然后对工资求和
select job,sum(sal) from emp group by job;
#找出每个部门的最高薪资
select deptno,max(sal) from emp group by deptno;
#找出每个部门不同岗位的最高薪资
select deptno,job,max(sal) from emp group by deptno,job;
#找出每个部门最高薪资 要求显示的最高薪资大于3000
#使用having可以对分完组之后的数据进一步过滤
#having不能单独使用 必须和group by 连用 having不能代替where
#优化策略：where和having 有限选择where 
select deptno,max(sal) from emp group by deptno having max(sal)>3000;
​
select deptno,max(sal) from emp where sal>3000 group by deptno;
​
#找出每个部门的平均薪资 要求显示平均薪资高于2500
select deptno,avg(sal) from emp group by deptno having avg(sal)>2500;

执行顺序：

  1. from
  2. where
  3. group by
  4. having
  5. select
  6. order by

distinct关键字

  • distinct 只能出现在所有字段的最前方

  • distinct出现在job，deptno两个字段之前，表示两个字段联合去重

    select distinct job,deptno from emp;
#统计所有工作岗位的数量
select count(distinct job) from emp;

连接查询

指跨表查询 多张表联合起来查询数据

根据表连接的方式 可分为：

  • 内连接

    • 等值连接
    • 非等值连接
    • 自连接

  • 外连接

    • 左外连接
    • 右外连接

  • 全连接

内连接

等值连接
#查询每个员工所在部门名称
#SQL92语法
 select ename,dname from emp,dept where emp.deptno = dept.deptno;
 #SQL99语法
select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;
非等值连接
#找出每个员工的薪资等级 要求显示员工名、薪资、薪资等级
select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
自连接
#查询员工的上级领导，要求显示员工名和对应的领导名
select a.ename as '员工名',b.ename as '领导名' from emp a join emp b on a.mgr = b.empno;

外连接

右外连接
select e.ename,d.dname from emp e right join dept d on e.deptno = d.deptno;
#right表示将join关键字右边的这张表看成主表主要是为了将这张表的数据全部查询出来，捎带着关联查询左边的表
select e.ename,d.dname from dept d left join emp e on e.deptno = d.deptno;

案例

#查询每个员工的上级领导 要求显示所有员工的名字和领导名
select a.ename as '员工名',b.ename as '领导名' from emp a left join emp b on a.mgr = b.empno;

三张表连接

语法：

select 
    ... 
from 
    a 
join 
    b 
on 
    a和b的连接条件 
join 
    c 
on
    a和c的连接条件 
join
    d 
on 
    a和d的连接条件

案例：

#找出 每个员工的部门名称和工资等级 要求显示员工名、部门名、薪资、薪资等级
select
    e.ename,e.sal,d.dname,s.grade 
from 
    emp e 
join 
    dept d 
on 
    e.deptno=d.deptno
join 
    salgrade s 
on 
    e.sal 
between s.losal and s.hisal;

子查询

select语句中嵌套select语句，被嵌套的select语句被称为子查询

select
    ..(select)..
from
    ..(select)..
where
    ..(select)..

案例：

#找出比最低工资高的员工姓名和工资
select ename,sal from emp where sal>(select min(sal) from emp);
from 后面的子查询

可以将子查询的查询结果当做一张临时表

案例：

#找出每个岗位的平均薪资的薪资等级
#第一步：找出每个岗位的平均薪资的薪资等级
select job,avg(sal) from emp group by job;
#第二步：对平均工资表和等级表进行连接
selec t.*,s.grade from (select job,avg(sal) as avgsal from emp group by job) t join salgrade s on t.avgsal between s.losal and s.hisal;
avatar
文章
阅读
粉丝