mysql补充

目录

select

1. 查找不同行 distinct

select distinct num from test1;

2. 限制输出行数 limit

select num from test1 limit 5;#限制输出的行数最多5行

select num
from test1 limit 5,5;#第一个五表示从第五行开始，第二个五表示输出五行

#注意行号是从0开始的，所以limit1,1相当于从第二行开始的一行
#mysql还有一种平替语法，limit 4 offset 3 == limit 3,4
  • 运行结果:
select * from test1 limit 2,1;

image

image

3. 排序 order by

  • 根据order by的列来排序输出
select *
from  test1 order by num;
#按照num的大小升序输出，默认为升序

select *
from test1
order by num desc; #加上desc之后为降序

select *
from test1
order by num,name; #按照num和name排序，先看num，如果相同再看name

select * 
from test1
order by num,name desc; #这里的降序是只针对name的，num还是升序，所以要全降序，需要每个地方都加desc

select * name
from test1
order by age 
limit 1;
#limit和order by结合可以输出最小和最大的值

4. 筛选where，条件符and和or

select *
from test1
where age=10;#输出所有年龄为10的信息

where... order by ... #order by要在where 之后要不会出错

select *
from test1
where age <> 10;#输出年龄不等于10的信息

select *
from test1
where age between 10 and 20;#输出年龄在10-20之间的信息

select *
from test1
where age is null;#检查是否有空值

select *
from test1
where age>10 and age<20;#符号 and 表示并，需要同时满足

select *
from test1
where age<10 or age>20;#符号 or 表示或，至少满足其中一个即可

select *
from test1
where age<10 or age>20 and name='111';
#and的优先级是高于or的，所以这个条件可以翻译为，name为'111'并且age>20的，或者age<10的
#解决方法是加括号确定优先级

image

5. in操作符，和not

In

  • in操作符给定取值区间，然后判断是否在区间内
select * 
from test1
where age in (10,20,30);#找出age为10，20，30的信息
  • 其实可以发现，in和or很类似，之间完全可以互相转换
  • 那为什么要使用In,有如下优点
    image

not

  • not的作用就是否定
select * 
from test1
where age not in (10,20,30);#找出age不为10,20,30的信息

6.通配符like，(%) (_)

  • 用来匹配一部分值的特殊字符

( % )

  • %可以表示任意数量的字符
select *
from test2
where tnum like '0%';#查询后缀有0的内容，%可以表示任意数量的字符,任意数量可以是0

select *
from test2
where tnum like '110%';#前缀有110的内容

select *
from test2
where tnum like '%0%';#某个位置有0

select *
from test2
where tnum like '1%0';#以1开头0结尾

( _ )

  • 与%类似，但是只能表示一个字符，并且不能为空
select *
from test2
where tnum like '%1_';#表示1前面可以有任意个字符，后面必须有一个字符

+ 通配符使用所需要注意的

image

7. 正则表达式

regexp

select *
from test2
where tnum regexp '10'; // 相当于 where tnum like '%10%';

select *
from test2
where tnum regexp '00.1';// .表示可以代替任意一个字符

select *
from test2
where tnum regexp '00[123]';//相当于in,001 002 003，看看那个能匹配上
# [^123] 就是除了123意外的数
# 还可以简化写[1-3],就是[123]的简化版
  • 关于为什么有like还使用regexp

image

OR匹配

select *
from test2
where tnum regexp '00|1';#和or差不多

转义字符

  • 一个字符串为'adw.we',我们想通过'.'来找到他，然后输出，然后如果我们 regexp '.',会输出全部内容，因为这里的'.'被认为是代替一个字符了，所以我们要写成regexp '\\.' ,如果是'\'这样的字符，则需要写成'\\\'

定位符

image

select * 
from test2
where tnum regexp '^[0-9\\.]'; #只有在第一个是其中数字时才匹配

计算字段

拼接concat

select concat(num,'(',name,')') //有点像规定输出格式
from test2;

image


  • Trim函数,LTrim函数去除对应列左边的空格，RTrim右边的空格
select RTrim(tnum)
from test2;

别名

  • 我们可以将表名替换成一个别名
select concat(num,'(',name,')') as hh
from test2;

image


函数

  • 调用文本处理函数来实现某些功能
    比如:
select upper(str)//upper的功能是将字符转化为大写
from test3;
  • 其他常用的文本处理函数
    image
    image

日期处理函数

  • 常用的函数
    image

  • 一般表示日期的数据类型类datetime,顾名思义就是日期+时间,具体格式为yyyy-mm-nn

select *
from test4
where dat='2002-04-05';
  • 问：上面这种写法是否可靠？
    答： 不可靠，假如其中有一项数据为'2002-04-05 11:30:30',也就是时间不为00:00:00,那么就无法匹配，所以在匹配时应该只让日期匹配，所以就要用date()函数，取其中的日期，同理还有time()函数，取其中的时间
  • 与一些关系符结合
select *
from test4
where date(dat) between '2002-07-01' and '2002-07-31'; //匹配7月份所有数据

select *
from test4
where year(dat)=2002 and month(dat)=7;//也是匹配七月份所有数据，另一种写法

数值处理函数

  • 常用的数值处理函数
    image

聚集函数

  • 常用函数
    image

  • 用法

avg

select avg(num) as avg_num//输出num这一列的平均值
from test1;

count
image

select count(num) as count_num//输出num列的行数
from test1;

max min

  • 返回一列中最大的（最小的）
    image

  • min类似

  • 聚集函数中的列，默认为ALL,也就是选择全部的行，如果前面加上distinct,则只会选择不同的行


分组

  • 创建分组group by

  • 就是按照group by的列，将其中相同的分为一组用于聚集函数的使用
  • group by一定要出现在where之后，order by之前
select age,sum(tnum) as cnt
from test4
group by age;

select age,count(*) as cnt
from test4
group by age;

  • 过滤分组having

  • having语法和where相似，之所以使用having是因为where只能筛选某些行，不能筛选某些分组
select age,count(*) as cnt,sum(tnum)
from test4
group by age
having count(*) >1;
  • 利用order by排序
select age,sum(tnum) as cnt
from test4
group by age
order by cnt;#按照每组的总和进行排列

select子句小结

image
image


子查询

  • 可以理解为嵌套查询,一般会和In一起使用
select age
from test4
where tnum in(select tnum
              from test2);//与In一起使用用于子查询

select age,(select count(*) from test2)//计算字段作为子查询
from test4;

联结

  • 将多个表互相关联然后筛选

  • 联结两个表的例子:

select *
from test4,test2
where test4.tnum = test2.tnum;

select *
from test4 inner join test2
on test2.tnum=test4.tnum;//两种写法最后输出的相同，一个是利用where筛选，一个是用on

  • 联结多个表
    image

  • 联结所需要注意的
    image

  • 联结很多情况下可以很好的替代子查询

创建高级联结

别名

select a.tnum
from test4 a
where a.age=11;
  • 使用联结和联结条件
    image

组合查询

union

image

  • union组合两个查询时，会自己去重，如果想输出全部行，可以使用 union all
  • union的规则
    image
    image


insert

insert into test4 values('张三',10,20);
#需要插入全部数据不能有空，而且要保证插入的数据跟列一一对应

insert into test4(name,age,tnum) values('张三',10,20);
#选择要插入数据的列，不选择的列需要是可以为空，或者有默认值

  • 很明显可以看出第二种写法要比第一种方法繁琐，那么为什么还需要第二种写法，因为第一种写法，你需要数值与列一一对应，如果原表结构发生变化，那么就会有问题，但第二种写法并不会，更加安全

  • 要插入多个数据，我们可以分多条insert语句，或者放在一个语句

insert into test4(name,age,tnum)
values('111',10,20),
('222',20,30);
  • 这种写法要比多条insert效率高，因为mysql处理单条语句速度比多条快

  • 插入检索出的数据
insert into test4(name,age,tnum)
select name,age,tnum
from test2;



update

  • 更新某些数据 update,使用update一定要注意where的使用，防止更新所有行
  • 格式
update test4
set age=23
where name='111';//更新单列

update test4
set age=23,
    tnum=13
where name='111';//更新多列

update test4
set age=null
where name='111';//删除数据

update ignore test4....//更新发生错误时会停止，加上ignore就是让他发生错误时也继续执行

delete

  • delete的作用
    image
delete  from test4
where name='111';

delete from test4;

  • delete删除的是整行，不是某个列，列的删除使用update

  • 如果要删除全部内容，可以使用truncate table ,这个的速度要比delete快，因为truncate table其实是删除原来的表然后新建立一个

  • 不管是delete还是update，都需要注意where的使用，否则会影响所有数据

image

视图

  • 为什么使用视图
    image
  • 视图的性能
    image

  • 视图就是一个虚表,可以用这个表来完成一些复杂的联结
  • 使用格式
    image

  • 用select语句来构造视图
    image
  • 视图可以用来更新

存储过程

  • 存储过程就是创建一个过程，然后在这个过程里对命令进行批处理，本质上就是一个函数

  • 写法:
create procedure solve(//创建
    in numa int,
    out nameb varchar(20)
)
begin
    select name
        from test4 where tnum=numa
    into nameb;
end;

call solve(30,@total);//调用
  • 更复杂的应用
    image
  • 其中的declare是创建一个局部变量
  • 其中的if语句，有其他的写法，个人倾向于下面的写法
IF 条件表达式
BEGIN
	语句1...
END
ELSE
BEGIN
	语句2...
END

触发器

  • 只可能在select,update,delete调用时执行,作用也是执行某些过程
create trigger [触发器名]
    after/before insert/update/delete on [表名]
    for each row
    begin
        ...//要执行的sql语句
    end;
  • 其中如果要读取数据要存入变量里，然后再读取
begin
	select new.num into @tmp;
end;//将新插入的行中的num输入到变量@tmp中


select @tmp;//这样就可以读取



安全管理

  • mysql中的用户信息都存储在 mysql数据库中的user表中
    image
  • 创建用户
create user np identified by 'password';//创建用户，用户名:np,密码:password

rename user np to nb;//将用户重命名为nb

drop user nb;//删除用户nb
  • 设置访问权限
show grants for nb;//查看某个用户的权限

grant select on test2 to ben;//分配权限，给ben用户分配了查询test2的权限

grant select on ccp.test1 to ben;//可以具体说明是那个数据库的那个表
//ccp.* 也可以用正则表示式

  • 具体的权限
    image
    image
    image

  • 更改口令

set password for ben = password('666');//将ben用户的口令更改为666
posted @ 2023-03-20 21:54  HHzp  阅读(18)  评论(0)  编辑  收藏  举报