数据库的基本操作

目录

基础查询

Select 语法

语法规范

数值比较

范围匹配

模糊匹配

正则匹配

逻辑比较

字符比较

别名/去重/合并 

常用函数

函数介绍

字符函数

数学函数

日期函数

聚集函数

数学计算

if函数

case函数

查询结果处理

分组

排序

过滤

分页

管理表记录

插入

修改

删除

---

基础查询

Select 语法

• 语法格式1         select 字段列表 from 库名.表名;
• 语法格式2         select 字段列表 from 库名.表名 where 筛选条件;

语法规范

必须遵守，命令才会执行

        -\c 终止sql命令

        -默认命令不支持tab键不全

        -每条SQL命令以；或  \G  结束

        -每条命令可以根据需要进行缩进或换行

        -SQL命令不区分字母大小写(密码、变量值除外)

进入操作的数据库中，可直接写表名；

use 数据库名；//进入数据库
select 字段列表 from 表名；//直接使用表名

数值比较

        符号的两边必须是数字或数值类型的表头

比较符号	说明	例子
=	相等	uid = 3
>	大于	uid > 3
>=	大于等于	uid >= 3
<	小于	uid < 3
<=	小于等于	uid <= 3
!=	不等于	uid != 3

mysql> select × from user where id <=10; #查询user表中所有id小于等于10的数据

范围匹配

• 匹配范围内的任意一个值即可

  比较符号	说明
  in （值列表）	在…里
  not in （值列表）	不在…里...
  between 数字1 and 数字2	在...之间

        

mysql> select name,id from user 
        -> where
        -> id between 10 and 20;
//id表头的值 在 10  到  20 之间即可 包括 10 和  20  本身

模糊匹配

• 用法

        where 字段名 like '通配符'       

        _  表示1个字符

        % 表示0到多个字符

//找名字以字母a开头的
mysql> select name from user where name like "a%";  

正则匹配

格式：select 字段名列表 from 库名.表名 where字段名 regexp '正则表达式'；

        ^ 匹配行首

        $ 匹配行尾

        [] 匹配范围内任意一个

        * 前边的表达式出现零次或多次

        | 或者

        . 任意一个字符

mysql> select name from  user where name regexp "^[0-9]";
//查询user表里名字以数字开头的数据，^匹配行首

逻辑比较

• 多个判断条件时使用and的优先级高于or

比较符号	说明	例子
or（ || ）	逻辑或	条件1  or  条件2  or  条件3
and（ && ）	逻辑与	条件1  and  条件2  and  条件3
not（ ！）	逻辑非	not     条件
（ ）	提高优先级	（表达式）

多个判断条件

        逻辑与 and （&&） 多个判断条件必须同时成立

        逻辑或 or （||） 多个判断条件其中某个条件成立即可

        逻辑非 not （!） 取反

mysql> select id , name from user where not id between 10 and 20 ;
//查询user表里id值不在10到20之间的数据 

（）提高优先级

mysql> select   2  + 3 * 5 ; //使用默认计算顺序 先乘除后加减
结果： 17
mysql> select   (2  + 3 ) * 5 ; //先加法再乘法
结果： 25

逻辑匹配什么时候需要加（）

逻辑与and 优先级高于逻辑或 or

如果在筛选条件里既有and 又有 or 默认先判断and 再判断or

字符比较

• 符号两边必须是字符 或字符类型的表头

比较符号	说明	例子
=	相等	name = "root"
!=	不相等	name != "root"
is  null	空	name  is  null
is  not  null	非空	name  is  not  null

         = 相等比较

        != 不相等比较。

//查看user表里是否有名字叫apache的用户
mysql> select  name  from  user where name = "apache" ;
//输出user表中解释器不是/bin/bash的用户名 及使用的解释器
mysql> select  name , shell  from  user where shell != "/bin/bash";

        空 is null 表头下没有数据

        非空 is not null 表头下有数据

//查看user表中name 表头没有数据的行 仅显示行中id表头 和 naeme 表头的值
mysql> select id , name from  user where name is null;
//查看user表中name 表头有数据的行， 仅显示行中id表头 和 name 表头的值
mysql> select id , name from  user where name is not null;

别名/去重/合并 

• 改变查询结果的显示效果

  命令	说明	例子
  as  或  空格	别名	表头  as  姓名
  distinct	去重	distinct  表头名
  concat()	合并	concat(表头名,"连接符号",表头名)

  select name as 姓名 from user where name = "root";
  //查询user表中的name数据设置别名为 姓名
  select distinct shell from user;
  //查询user表中的shell数据，并去掉重复的
  select concat(name,"-",uid) from user;
  //查询user表中name和uid的数据，并合并为一列输出，中间用“-”隔开

常用函数

函数介绍

• 介绍

                MySQL服务内置命令

                语法：        函数名（表头名）

• 格式

                select        函数（表头名）        from        表名；

                select        函数（表头名）        from        表名        where         条件；

字符函数

作用：处理字符或字符串类型的表头

length(str)                返回字符串长度，以字节为单位

char_length(str)        返回字符串长度，以字符为单位

upper(str)和ucase(str)        将字符串中的字母全部转化成大写

lower(str) 和lcase(str)        将str中的字母全部转换成小写

substr（是，start，end）        从s的start位置开始取出到end长度的子串

instr（str，str1）                返回str1参数，在str参数内的位置

trim（s）                               返回字符串s删除了两边空格之后的字符串

//LENGTH(str)             返字符串长度，以字节为单位
mysql> select name from user where name = "root" ;

+------+
| name |
+------+
| root |
+------+
1 row in set (0.00 sec)
//一个汉字3个字节
mysql> select name , length(name) from employees where employee_id = 3 ;
+-----------+--------------+
| name      | length(name) |
+-----------+--------------+
| 李玉英    |            9 |
+-----------+--------------+

//LOWER(str)和LCASE(str)    将str中的字母全部转换成小写
mysql> select lower("ABCD") ;
+---------------+
| lower("ABCD") |
+---------------+
| abcd          |
+---------------+
1 row in set (0.00 sec)

数学函数

作用：处理数字或数值类型的表头

abs(x)                                返回x的绝对值

//ABS(x)    返回x的绝对值
mysql> select abs(-11);
+----------+
| abs(-11) |
+----------+
|       11 |
+----------+

pi()                                     返回圆周率π，默认显示6位小数

//PI()        返回圆周率π，默认显示6位小数
mysql> select pi() ;
+----------+
| pi()     |
+----------+
| 3.141593 |
+----------+

mod(x,y)                            返回x被y除后的余数

//MOD(x,y)    返回x被y除后的余数 
mysql> select mod(10,3);
+-----------+
| mod(10,3) |
+-----------+
|         1 |
+-----------+

ceil(x)、ceiling(x)               返回不小于x的最小整数（x 是小数）

//CEIL(x)、CEILING(x)    返回不小于x的最小整数 (x 是小数)
mysql> select ceil(9.23);
+------------+
| ceil(9.23) |
+------------+
|         10 |
+------------+

floor(x)                                返回不大于x的最大整数（x 是小数）

//FLOOR(x）            返回不大于x的最大整数 (x 是有小数的数字)
mysql> select floor(9.23);
+-------------+
| floor(9.23) |
+-------------+
|           9 |
+-------------+

日期函数

作用： 获取系统或指定的日期与时间

函数	说明	函数	说明
curtime()	获取系统时间	hour()	获取小时
curdate()	获取系统日期	minute()	获取分钟
now()	获取日期和时间	second()	获取秒
year()	获取年	quarter()	获取一年中第几季度
month()	获取月	monthname()	获取月份名称
day()/week()	获取日/一年中的第几周	dayname()	获取日期对应的星期名
date()/weekday()	获取日期/一周中的周几	dayofyear()	获取一年中的第几天
time()	获取时间	dayofmonth()	获取一月中的第几天

例：

mysql> select curtime(); //获取系统时间
+-----------+
| curtime() |
+-----------+
| 17:42:20  |
+-----------+

mysql> select curdate();//获取系统日期
+------------+
| curdate()  |
+------------+
| 2023-05-24 |
+------------+

mysql> select year(now()) ; //获取系统当前年
+-------------+
| year(now()) |
+-------------+
|        2023 |
+-------------+

聚集函数

作用： 数据统计命令

avg（字段名）                        //计算平均值

mysql> select avg(uid) from user;        //输出user表中uid的平均值
+-----------+
| avg(uid)  |
+-----------+
| 2671.6538 |
+-----------+
1 row in set (0.00 sec)

sum（字段名）                        //求和

mysql> select sum(uid) from user;     //输出user表中所有uid的和
+----------+
| sum(uid) |
+----------+
|    69463 |
+----------+

min（字段名）                        //获取最小值

mysql> select min(uid) from user;        //输出user表中uid的最小值
+----------+
| min(uid) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

max（字段名）                        //获取最大值

mysql> select max(uid) from user;        //输出user表中uid的最大值
+----------+
| max(uid) |
+----------+
|    65534 |
+----------+
1 row in set (0.00 sec)

count（字段名）                        //统计字段值个数

mysql> select count(name) from user;        //输出user表中name的个数
+-------------+
| count(name) |
+-------------+
|          33 |
+-------------+
1 row in set (0.00 sec)

数学计算

作用： 对行中的列做计算

符号	用途
+	加法
-	减法
*	乘法
/	除法
%	取余数（求模）
（ ）	提高优先级

//查询salary 表中8号员工2019年1月10 工资总和
mysql> select employee_id ,date,basic+bonus as 总工资 from salary where employee_id = 8 and date=20190110;
+-------------+------------+-----------+
| employee_id | date       | 总工资    |
+-------------+------------+-----------+
|           8 | 2019-01-10 |     24093 |
+-------------+------------+-----------+
1 row in set (0.00 sec)

if函数

语法：

        if(条件,v1,v2) 如果条件是TRUE则返回v1，否则返回v2

        ifnull(v1,v2) 如果v1不为NULL，则返回v1，否则返回v2

//if（）语句执行过程
mysql> select if(1=2,"a","b");
+-----------------+
| if(1=2,"a","b") |
+-----------------+
| b               |
+-----------------+
1 row in set (0.00 sec)

mysql> select if(1!=2,"a","b");
+------------------+
| if(1!=2,"a","b") |
+------------------+
| a                |
+------------------+
1 row in set (0.00 sec)

//ifnull（）语句执行过程
mysql> select ifnull("abc","xxx");
+---------------------+
| ifnull("abc","xxx") |
+---------------------+
| abc                 |
+---------------------+
1 row in set (0.00 sec)

mysql> select ifnull(null,"xxx");
+--------------------+
| ifnull(null,"xxx") |
+--------------------+
| xxx                |
+--------------------+
1 row in set (0.00 sec)

case函数

命令格式

CASE 表头名 WHEN 值1 THEN 输出结果 WHEN 值2 THEN 输出结果 WHEN 值3 THEN 输出结果 ELSE 输出结果 END

CASE WHEN 判断条件 THEN 输出结果 WHEN 判断条件 THEN 输出结果 WHEN 判断条件 THEN 输出结果 ELSE 输出结果 END

如果表头名等于某个值，则返回对应位置then后面的值并结束判断，

如果与所有值都不相等，则返回else后面的结果并结束判断

//输出departments表中部门类型
mysql> select dept_id,dept_name,
    -> case
    -> when dept_name in ("运维部","开发部","测试部") then "技术部"
    -> else "非技术部"
    -> end as 部门类型  from departments;
+---------+-----------+--------------+
| dept_id | dept_name | 部门类型     |
+---------+-----------+--------------+
|       1 | 人事部    | 非技术部     |
|       2 | 财务部    | 非技术部     |
|       3 | 运维部    | 技术部       |
|       4 | 开发部    | 技术部       |
|       5 | 测试部    | 技术部       |
|       6 | 市场部    | 非技术部     |
|       7 | 销售部    | 非技术部     |
|       8 | 法务部    | 非技术部     |
|       9 | 小卖部    | 非技术部     |
|      10 | 行政部    | 非技术部     |
|      11 | 公关部    | 非技术部     |
|      12 | 大卖部    | 非技术部     |
+---------+-----------+--------------+
12 rows in set (0.03 sec)

查询结果处理

说明：对select语句查找到的数据再做处理

语法：

        select 表头名 from 库名.表名 [where 条件]

分组 | 排序 | 过滤 | 分页；

分组

语法：

        select 表头名，from   表名   where   条件   group   by   表头名；

说明：

        查询列表必须是分组函数和出现在group by 后面的字段

        字段中值相同的为一组

排序

语法：

        select 语句 order   by   字段名   [asc | desc ] ;

        asc   升序排序（默认）

        desc  降序排序

过滤

作用：在查询到的数据里 筛选符合条件的数据

语法：

        select 表头名   from  库名.表名  where  筛选条件  having  筛选条件；

分页

作用：限制查询结果显示行数 （默认显示全部的查询结果）

语法： 

        select语句  limit 数字；                                显示查询结果前多少条记录

        select语句  limit 数字1 ，数字2；                显示指定范围内的查询记录

        ----数字1    起始行    （0表示第1行）

        ----数字2    总行数

管理表记录

插入

• 不指定列名插入记录（必须给所有列赋值）

         注意：值得顺序必须和表中列的顺序一致，且数据类型要匹配

• 指定列名插入记录（仅须给指定列赋值）

        注意：列和值的顺序要一致；列名先后顺序不重要；没有赋值的列使用默认值、自增长结果赋值

• 使用select 查询结果赋值

        注意： select查询语句的字段个数和顺序必须和insert语句字段顺序和个数一致

• 使用set命令赋值

        insert   into   库名.表名   set    字段=值，字段=值，...........

修改

语法格式：

        //批量修改

update   库名.表名   set    字段=值，字段=值；

        //仅修改与条件匹配的

update    库名.表名   set    字段=值，字段=值    where   条件；

删除

语法格式：

        delete   from   库名.表名   where   条件        //仅删除与条件匹配的行

        delete   from   库名.表名；                            //清空表记录

        truncate    table   库名.表名；                       //清空表记录     

拓展知识：

        truncate   table  不支持where 条件

        自增长列，truncate 后从1开始；delete  继续编号

        truncate不能回滚，delete 可以

        效率略高于delete