一.在MySQL数据库中对日期进行加减操作
1.date_add(create_time,interval 1 year) #增加一年
2.date_add(create_time,interval 1 month) #增加一月
3.date_add(create_time,interval 1 day) #增加一天
4.date_add(create_time,interval 1 hour) #增加一小时
5.date_add(create_time,interval 1 second) #增加一秒
6.date_add(create_time,interval 1 microsecond) #增加一毫秒
7.date_add(create_time,interval 1 quarter) #增加一季度
注:减少操作将dete_add换为date_sub即可
二.将时间日期转化为星期使用以下语句
DATE_FORMAT(cart.create_time, '%w')
三.ROUND()函数用于将数字四舍五入到指定的小数位数。
语法如下:SELECT ROUND(列名,数字) FROM 表名;
四.sql查询语句的执行顺序
from --> on --> join --> where --> group by --> having --> select --> distinct-- > order by--> limit
五.sql查询语句on的用法
1.内连接(INNER JOIN):返回两个表中具有匹配值的行。
语法如下:SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;
2.左连接(LEFT JOIN):返回左表中的所有行,以及右表中具有匹配值的行。如果没有匹配的值,则返回NULL值。
语法如下:SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;
3.右连接(RIGHT JOIN):返回右表中的所有行,以及左表中具有匹配值的行。如果没有匹配的值,则返回NULL值。
语法如下:SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;
4.全外连接(FULL OUTER JOIN):返回两个表中的所有行,无论它们是否具有匹配值。如果没有匹配的值,则返回NULL值。
语法如下:SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;
五.将字符中大写字母转化为小写,使用函数如下
lower()
六.将两个表进行拼接使用函数
concat()
七.group by的用法是对数据进行分组,分组时,表中有相同值的分为一组。分组后可以进行聚合查询。
`group by`分组后的查询中,`select`的列不能出现除了`group by `分组条件以及聚合函数外的其他列。
语法如下:select 列1, 列2, (聚合函数) from table_name group by 列1, 列2;
练习例题
mysql> use store
Database changed
mysql> select * from account;
+------+-------+----------+------------+--------+-------+-------+---------------------+
| id | phone | password | type | name | point | money | crate_time |
+------+-------+----------+------------+--------+-------+-------+---------------------+
| 1 | 111 | 111 | 0x00 | 管理员 | NULL | NULL | 2023-03-07 16:50:00 |
| 2 | 222 | 222 | 0x01 | 张三 | 0 | 350 | 2023-03-11 19:50:00 |
| 3 | 333 | 333 | 0x01 | 李四 | 0 | 268.5 | 2023-03-12 19:50:00 |
| 4 | 444 | 444 | 0x01 | 王五 | 0 | 956.3 | 2023-03-13 19:50:00 |
+------+-------+----------+------------+--------+-------+-------+---------------------+
4 rows in set (0.01 sec)
mysql> select * from cart;
+--------+----------+------+------------+---------------------+
| car_id | goods_no | num | account_id | create_time |
+--------+----------+------+------------+---------------------+
| 1 | 1 | 2 | 2 | 2023-03-11 19:50:00 |
| 2 | 2 | 2 | 3 | 2023-03-12 19:50:00 |
| 3 | 4 | 10 | 4 | 2023-03-13 19:50:00 |
| 4 | 6 | 20 | 3 | 2023-03-12 19:50:00 |
| 5 | 9 | 2 | 2 | 2023-03-13 19:50:01 |
| 6 | 2 | 10 | 4 | 2023-03-13 19:50:00 |
+--------+----------+------+------------+---------------------+
6 rows in set (0.00 sec)
mysql> select * from goods;
+---------+------------+------+-------+-------+---------------------+-------------+
| good_no | goods_name | cost | price | count | crate_time | category_no |
+---------+------------+------+-------+-------+---------------------+-------------+
| 1 | 火腿肠 | 0.5 | 2.5 | 50 | 2023-03-13 19:50:00 | 1 |
| 2 | 薯片 | 1.5 | 3.5 | 30 | 2023-03-10 19:50:00 | 1 |
| 3 | 鼠标垫 | 20.5 | 25.5 | 20 | 2023-03-07 19:50:00 | 4 |
| 4 | 萝卜 | 0.8 | 1.5 | 50 | 2023-03-08 19:50:00 | 2 |
| 5 | 白菜 | 0.2 | 0.8 | 50 | 2023-03-09 19:50:00 | 2 |
| 6 | 车厘子 | 30 | 50 | 50 | 2023-03-11 19:50:00 | 3 |
| 7 | 芒果 | 5 | 8 | 50 | 2023-03-11 19:50:00 | 3 |
| 8 | 五粮液 | 1350 | 1700 | 60 | 2023-03-12 19:50:00 | 5 |
| 9 | 鼠标 | 40 | 50 | 20 | 2023-03-13 19:50:00 | 4 |
+---------+------------+------+-------+-------+---------------------+-------------+
9 rows in set (0.00 sec)
mysql> select * from category;
+------+----------+
| no | name |
+------+----------+
| 1 | 零食 |
| 2 | 蔬菜 |
| 3 | 水果 |
| 4 | 电子产品 |
| 5 | 酒水 |
+------+----------+
5 rows in set (0.00 sec)
一.查询买火腿肠的是谁
mysql> select account.name from goods,cart,account
-> where
-> goods.good_no = cart.goods_no
-> and
-> account.id = cart.account_id
-> and
-> goods.goods_name = '火腿肠';
+------+
| name |
+------+
| 张三 |
+------+
1 row in set (0.00 sec)
二.查询谁卖了零食
mysql> select account.name from goods,cart,account,category
-> where
-> goods.good_no = cart.goods_no
-> and
-> account.id = cart.account_id
-> and
-> goods.category_no = category.no
-> and
-> category.no = 1;
+------+
| name |
+------+
| 张三 |
| 李四 |
| 王五 |
+------+
3 rows in set (0.00 sec)
三.查询张三花了多少钱
mysql> select sum(cart.num*goods.price) from goods,cart,account,category
-> where
-> goods.good_no = cart.goods_no
-> and
-> account.id = cart.account_id
-> and
-> goods.category_no = category.no
-> and
-> account.name = '张三';
+---------------------------+
| sum(cart.num*goods.price) |
+---------------------------+
| 105 |
+---------------------------+
1 row in set (0.00 sec)
四.查询买东西的一共花了多少钱
mysql> select sum(cart.num*goods.price) from goods,cart,account,category
-> where
-> goods.good_no = cart.goods_no
-> and
-> account.id = cart.account_id
-> and
-> goods.category_no = category.no;
+---------------------------+
| sum(cart.num*goods.price) |
+---------------------------+
| 1162 |
+---------------------------+
1 row in set (0.00 sec)
mysql> select account.name,sum(cart.num*goods.price) from goods,cart,account,category
-> where
-> goods.good_no = cart.goods_no
-> and
-> account.id = cart.account_id
-> and
-> goods.category_no = category.no
-> GROUP BY account.name;
+------+---------------------------+
| name | sum(cart.num*goods.price) |
+------+---------------------------+
| 李四 | 1007 |
| 王五 | 50 |
| 张三 | 105 |
+------+---------------------------+
3 rows in set (0.00 sec)
五.每个消费时间的价格
mysql> select sum(cart.num*goods.price),cart.create_time from cart,goods
-> where
-> goods.good_no = cart.goods_no
-> group by cart.create_time;
+---------------------------+---------------------+
| sum(cart.num*goods.price) | create_time |
+---------------------------+---------------------+
| 5 | 2023-03-11 19:50:00 |
| 50 | 2023-03-13 19:50:00 |
| 1007 | 2023-03-12 19:50:00 |
| 100 | 2023-03-13 19:50:01 |
+---------------------------+---------------------+
4 rows in set (0.00 sec)
六.分别在周几消费多少钱
mysql> select SUM(cart.num * goods.price),DATE_FORMAT(cart.create_time, '%w') AS day_of_week from cart,goods
-> where
-> goods.good_no = cart.goods_no
-> GROUP BY
-> cart.create_time;
+-----------------------------+-------------+
| SUM(cart.num * goods.price) | day_of_week |
+-----------------------------+-------------+
| 5 | 6 |
| 50 | 1 |
| 1007 | 0 |
| 100 | 1 |
+-----------------------------+-------------+
4 rows in set (0.00 sec)
七.张三的消费时间
mysql> select cart.create_time from
-> cart,account
-> where
-> account.id = cart.account_id
-> and
-> account.name = '张三';
+---------------------+
| create_time |
+---------------------+
| 2023-03-11 19:50:00 |
| 2023-03-13 19:50:01 |
+---------------------+
2 rows in set (0.00 sec)
八.所有的利润值
mysql> select sum(cart.num*goods.price-cart.num*goods.cost)
from goods,cart,account,category
where
goods.good_no = cart.goods_no
and
account.id = cart.account_id
and
goods.category_no = category.no;
+-----------------------------------------------+
| sum(cart.num*goods.price-cart.num*goods.cost) |
+-----------------------------------------------+
| 455 |
+-----------------------------------------------+
1 row in set (0.00 sec)
九.(1)每个商品的利润率
mysql> select goods.goods_name , (goods.price-goods.cost)/goods.cost from goods,category where goods.category_no = category.no ;
+------------+-------------------------------------+
| goods_name | (goods.price-goods.cost)/goods.cost |
+------------+-------------------------------------+
| 火腿肠 | 4 |
| 薯片 | 1.3333333333333333 |
| 鼠标垫 | 0.24390243902439024 |
| 萝卜 | 0.8749999999999999 |
| 白菜 | 3.0000000000000004 |
| 车厘子 | 0.6666666666666666 |
| 芒果 | 0.6 |
| 五粮液 | 0.25925925925925924 |
| 鼠标 | 0.25 |
+------------+-------------------------------------+
9 rows in set (0.00 sec)
(2)每个商品的利润率(保留两位小数)
mysql> select goods.goods_name , round(((goods.price-goods.cost)/goods.cost),2) as 利润率
-> from goods,category
-> where
-> goods.category_no = category.no
-> order by 利润率 desc;
+------------+--------+
| goods_name | 利润率 |
+------------+--------+
| 火腿肠 | 4 |
| 白菜 | 3 |
| 薯片 | 1.33 |
| 萝卜 | 0.87 |
| 车厘子 | 0.67 |
| 芒果 | 0.6 |
| 五粮液 | 0.26 |
| 鼠标 | 0.25 |
| 鼠标垫 | 0.24 |
+------------+--------+
9 rows in set (0.00 sec)
十.购买商品后的余额
mysql> SELECT account.name, account.money - SUM(cart.num * goods.price) AS balance
-> FROM goods, cart, account
-> where
-> goods.good_no = cart.goods_no
-> and
-> account.id = cart.account_id
-> GROUP BY name, account.money;
+------+---------+
| name | balance |
+------+---------+
| 张三 | 245 |
| 王五 | 906.3 |
| 李四 | -738.5 |
+------+---------+
3 rows in set (0.00 sec)
十一.购买商品后剩余的库存
mysql> SELECT goods.goods_name, goods.count-sum(cart.num) AS 剩余库存
-> from goods ,cart
-> where
-> goods.good_no = cart.goods_no
-> group by goods.goods_name,goods.count;
+------------+----------+
| goods_name | 剩余库存 |
+------------+----------+
| 火腿肠 | 48 |
| 薯片 | 18 |
| 萝卜 | 40 |
| 车厘子 | 30 |
| 鼠标 | 18 |
+------------+----------+
5 rows in set (0.00 sec)
mysql>