回顾mysql主从复制
聚合函数
只有 select ⼦句和 having ⼦句、order by ⼦句中能使⽤聚合函 数,where ⼦句不能使⽤聚合函数。当使⽤聚合查询以后,不能 使⽤where条件,如果要添加条件,就使⽤having
计算指定列的最⼤值
select max(表头名) from 表名;
计算指定列的最⼩值
mysql> select min(price) from product;
+------------+
| min(price) |
+------------+
| 8.5 |
+------------+
1 row in set (0.00 sec)
计算指定列的数值和
mysql> select sum(price) from product;
+-------------------+
| sum(price) |
+-------------------+
| 50.10000038146973 |
+-------------------+
1 row in set (0.01 sec)
mysql> select sum(tt) from (select *,price*qty as tt from product) as a;
+--------------------+
| sum(tt) |
+--------------------+
| 14372.000274658203 |
+--------------------+
1 row in set (0.00 sec)
⽇期函数
获取当前⽇期和 时间
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2024-08-07 15:30:32 |
+---------------------+
1 row in set (0.00 sec)
显示输⼊⽇期的 年份
mysql> select year(now());
+-------------+
| year(now()) |
+-------------+
| 2024 |
+-------------+
1 row in set (0.00 sec)
计算输⼊的⽇期 到年初的周数
select weekofyear ('date');(有点⽤但不多)例 如:select year('2023-01-31');(会显示 5,也 就是 5 周)
计算输⼊的⽇期 到年初的天数
select dayofyear ('date');(有点⽤但不多)例 如:select dayofyear('2023-01-31');(会显示 31,也就是 31 天)
显示输⼊⽇期是 ⼀周中的第⼏天 (从周⽇开始)
select dayofweek('date');(有点⽤但不多)例 如:select dayofweek('1998-08-24');(显示 2,也就是周⼀)
数字函数
字符串函数
MySQL分组查询
案例:
先创建一个表
mysql> create table product(
-> id int primary key auto_increment,
-> name varchar(45) not null,
-> price float not null,
-> qty int not null);
Query OK, 0 rows affected (0.03 sec)
mysql> desc product;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(45) | NO | | NULL | |
| price | float | NO | | NULL | |
| qty | int | NO | | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
mysql> insert into product (name,price,qty) values("香蕉",8.5,200);
Query OK, 1 row affected (0.01 sec)
mysql> insert into product (name,price,qty) values("苹果",12.5,400);
Query OK, 1 row affected (0.01 sec)
mysql> insert into product (name,price,qty) values("菠萝",12.4,80);
Query OK, 1 row affected (0.00 sec)
mysql> insert into product (name,price,qty) values("西瓜",16.7,400);
Query OK, 1 row affected (0.01 sec)
查看表内信息
mysql> select * from product order by price;
+----+--------+-------+-----+
| id | name | price | qty |
+----+--------+-------+-----+
| 1 | 香蕉 | 8.5 | 200 |
| 3 | 菠萝 | 12.4 | 80 |
| 2 | 苹果 | 12.5 | 400 |
| 4 | 西瓜 | 16.7 | 400 |
+----+--------+-------+-----+
4 rows in set (0.00 sec)mysql> select * from product order by qty;
+----+--------+-------+-----+
| id | name | price | qty |
+----+--------+-------+-----+
| 3 | 菠萝 | 12.4 | 80 |
| 1 | 香蕉 | 8.5 | 200 |
| 2 | 苹果 | 12.5 | 400 |
| 4 | 西瓜 | 16.7 | 400 |
+----+--------+-------+-----+
4 rows in set (0.00 sec)
#查询平均价钱
mysql> select sum(price) from product;
+-------------------+
| sum(price) |
+-------------------+
| 50.10000038146973 |
+-------------------+
1 row in set (0.01 sec)#查询最低价钱
mysql> select min(price) from product;
+------------+
| min(price) |
+------------+
| 8.5 |
+------------+
1 row in set (0.00 sec)#和
mysql> select sum(tt) from (select *,price*qty as tt from product) as a;
+--------------------+
| sum(tt) |
+--------------------+
| 14372.000274658203 |
+--------------------+
1 row in set (0.00 sec)
MySQL ⼦语句查询
1、⽐较运算符的⼦查询
也就是⽤“=”、“”这类⽐较运算符
创建表之后查询信息
2、exists 关键字的⼦查询
exists 关键字后⾯的参数可以是任意⼀个⼦查询, 它不产⽣任 何数据只返回 true 或 false。⽽当返回值为 true 时外层查询才会 执⾏
MySQL 基础权限
sql语句
1、新增
--insert into 库名称.表名
(id,username,password)values(1,"abc","123");
--insert into 表名称 values(1,"name","word")
--insert into 表名称 select*from 其他表
--insert into 表 value (), ()
2、删除
--delete from 表名
--delete from tablename where id=3
--delete from tablename where age>8
--delete from tablename where on ("a","b","c");
3、修改
--update mysql.user set host='%' where name ='root'
--update user set password ='abc' where
username="zy"
4、查询
-单表查询
select 字段名列表 from 表名称/索引
表中插入数据要注意不要重复字段值,不然报错
但是 使用insert ignore into 表名 values (值1,值2,...)就可以忽略重复记录的插入
MySQL 触发器
触发器是⼀种特殊的存储过程,它在插⼊,删除或修改特定表中 的数据时触发执⾏,它⽐数据库本身标准的功能有更精细和更复 杂的数据控制能⼒
触发器语法:
create trigger 触发器名称 触发的时机 触发的动作 on 表名 for each row 触发器状态
实例:
#查看数据库内的所有表
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| product |
| user |
+----------------+
2 rows in set (0.00 sec)
#创建触发器规则
#命令⼤意:创建名为deltable的触发器,在执⾏每⼀条的删除 class表命令之后,删除student表
mysql> create trigger deltable after delete on class for each row delete from student;
Query OK, 0 rows affected (0.00 sec)
#查看触发器
mysql> show triggers\G;