sql语句

1.新增 

 

2.删除

3.修改

4.查询

单表查询

select  字段名列表  from 表名，索引

聚合函数

select  count(*或表头名)  from  表名;

select max(表头名)  from 表名;

select min(表头名)  from 表名;

select sum(表头名)  from 表名;

日期函数

mysql> select dayofweek('2024-8-7');
+-----------------------+
| dayofweek('2024-8-7') |
+-----------------------+
|                     4 |                     注：表示星期三
+-----------------------+
 

mysql> select last_day('2024-8-7');
+----------------------+
| last_day('2024-8-7') |
+----------------------+
| 2024-08-31           |
+----------------------+
1 row in set (0.00 sec)
 

mysql> select adddate('2024-8-7',3);
+-----------------------+
| adddate('2024-8-7',3) |
+-----------------------+
| 2024-08-10            |
+-----------------------+
1 row in set (0.00 sec)
 

数字函数

round() ：四舍五入

ceiling() :向上取整

floor() :向下取整

mysql分组查询

若⽤ group by 分组查询语句，必须加⼊聚合函数，否则报错

mysql子语句查询

比较运算符的子查询：

exists关键字的子查询：

相当于内层句⼦是⼀个判断句式

mysql多表联合查询

select * from 表1 cross join 表2;

2、多表联合查询

mysql基础权限

权限语法：

为客户机授权：

查看给予客户机的权限：

revoke 权限列表 on 库.* from ⽤户@客户机地址；

刷新权限：

drop user '⽤户名'@'客户机地址';

查询有几条数据：

mysql> select count(*) from user;
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.01 sec)

mysql> select count(1) from user;
+----------+
| count(1) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)
 

别名：

mysql> select id as 编号 from user;
+--------+
| 编号   |
+--------+
|      1 |
|      2 |
|      3 |
|      4 |
+--------+
 

mysql> select * from (select * from product order by qty)as a order by a.price;
+----+-----------+-------+-----+
| id | name      | price | qty |
+----+-----------+-------+-----+
|  1 | 香蕉      |   8.5 | 200 |
|  3 | 菠萝      |  12.4 |  70 |
|  2 | 苹果      |  12.5 | 400 |
|  4 | 哈密瓜    |  18.3 | 400 |
+----+-----------+-------+-----+
4 rows in set (0.00 sec)
 

mysql> select *,price*qty as tt from product;
+----+-----------+-------+-----+-------------------+
| id | name      | price | qty | tt                |
+----+-----------+-------+-----+-------------------+
|  1 | 香蕉      |   8.5 | 200 |              1700 |
|  2 | 苹果      |  12.5 | 400 |              5000 |
|  3 | 菠萝      |  12.4 |  70 | 867.9999732971191 |
|  4 | 哈密瓜    |  18.3 | 400 | 7319.999694824219 |
+----+-----------+-------+-----+-------------------+
4 rows in set (0.01 sec)

mysql> select sum(tt) from (select *,price*qty as tt from product) as a;

+--------------------+
| sum(tt)            |
+--------------------+
| 14887.999668121338 |
+--------------------+
1 row in set (0.00 sec)