MySQL语句总结（专栏打底文章）

老边
187 阅读10分钟

本文正在参加「技术专题19期 漫谈数据库技术」活动

image.png

一直觉得自己的MySQL能力不好，所以想学习一下，可是，没有一个规范来约束自己，所以借着掘金本月的活动来约束一下自己，总结一下自己数据库部分的知识点，这篇文章作为自己数据库学习专栏的打底文章，来总结一下学过的SQL语句,并且会一直维护下去，有兴趣的小伙伴可以把问题提出来。

表结构操作

在开始数据库的CRUD之前，首先先聊一聊基本的MySQL数据库结构，MySQL是由 库，表，字段，值构成的，如果进行图形化和Excel的结构类似：

MySQL库可以看作是一个Excel文件

MySQL表可以看作Excel文件当中的具体的表（sheet）

MySQL字段可以看作表头

MySQL当中的值可以看作是excel表当中具体的数据。

学习MySQL肯定需要明白这些对象之间的关系，那么基于这些，面向MySQL用户操作，我们需要知道的命令：

进入Mysql

当安装完成MySQL数据库之后，就可以尝试使用命令行进入MySQL数据库，当然会有一些好用的工具，比如navcate等等，我们这里不做讨论，但是至少需要明白，MySQL数据库连接需要的几个关键的参数:

参数描述
hostmysql服务器的地址
usermysql用户
passwordmysql用户对应的密码
port端口，mysql默认的端口是3306

连接命令如下：

mysql -u 用户 -p密码

创建用户

mysql创建用户需要指定用户和服务器地址，就是服务器允许用户通过那个地址访问MySQL服务器

create user '用户名'@'服务器地址' identified by '密码';

用户授权

mysql对不同的用户设置了不同的权限，所以创建用户之后可以设置权限，也可以通过设置权限来创建用户。

grant 权限 on 库.表 to '用户名'@'localhost' identified by '密码';

这里的权限指的是增（create,insert）删(drop,delete)改(update)查(select)

修改密码

set password for '用户名'@'服务器地址' = password(密码);

创建数据库

create database 数据库名称 charset=utf-8;

创建数据库命令一定注意加上编码，否则中文容易出现乱码。

使用数据库

use 数据库名称;

创建数据表

create table 表名(字段名称 字段类型[补充描述], 字段名称1 字段类型[补充描述]);

常用的字段类型

字段类型描述
Char保存固定长度的字符串（可包含字母，数字以及特殊字符）。在括号中指定字符串的长度，最多255个字符。
varchar保存可变的字符串（可包含字母，数字以及特殊字符），在括号中指定字符串最大长度，最多255个字符。如果值得长度大于255，则被转换为 TEXT 类型。（M个字节）
text存放最大长度为 65,535 个字符的字符串。
int-2147483648 到 2147483647 常规。0 到 4294967295 无符号。在括号中规定最大数。
TinyInt-128 到 127 常规。0 到 255 无符号，在括号中规定最大位数。
BigInt-9223372036854775808 到 9223372036854775807 常规。0 到18446744073709551616 无符号，在括号中规定最大数。
Float单精度浮点数。天缘博客提醒这里的D是精度，如果D<=24则为默认的FLOAT，如果D>24则会自动被转换为DOUBLE型。
Double双精度浮点。
Date格式YYYY-MM-DD，从1000-01-01 到 9999-12-31。
Datetime格式YYYY-MM-DD HH:MM:SS，从1000-01-01 00:00:00到 9999-12-31 23:59:59。

举例子：

create table student(id int primary key auto_increment, name varchar(32));

primary key 主键（感觉可以熟练的写出来是一件很酷的事情）

auto_increment 自增长

数据查询

查询单个列：

select 字段 from 表;

查询多个字段：

select 字段1，字段2 from 表;

查询所有字段：

select * from 表;

检索不同值，去重。

select distinct 字段 from 表;

限制查询条数

select * from browse_log limit 5;
select * from browse_log limit 5 offset 5;

limit 可以设置查询返回的条数，在有时候可以防止全文检索。

offset 可以设置从第几条开始查询

数据排序

order_by 排序

select * from browse_log order by id; #结果按照id从小到大排序
select * from browse_log order by id desc; #结果按照id从大到小排序
select * from browse_log order by username,age; #结果按照用户名排序，用户名相同的时候再使用age进行排序
select * from browse_log order by username desc,age; #结果按照用户名倒序，用户名相同的时候再使用age进行排序
select * from browse_log order by 2,3; #结果按照第二第三列排序

条件过滤

在mysql当中可以使用where进行条件过滤

比较查询

select * from browser where id = 3; #查询id为3的数据
select * from browser where id > 3; #查询id大于3的数据
select * from browser where id < 3; #查询id小于3的数据
select * from browser where id >= 3; #查询id大等于3的数据
select * from browser where id <= 3; #查询id小等于3的数据
select * from browser where id != 3; #查询id不等于3的数据
select * from browser where id !> 3; #查询id不大于3的数据
select * from browser where id !< 3; #查询id不小于3的数据
select * from browser where id between 3,6; #查询id在3到6之间
select * from browser where id is null; #查询id为null的数据
select * from browser where id in [3,2,4,5]; #查询id在[3,2,4,5]范围内的数据

逻辑查询

select * from browser where username = "admin" and password = "123456"; #查询用户名为admin并且密码为123456的数据
select * from browser where username = "admin" or password = "123456"; #查询用户名为admin或者密码为123456的数据
select * from browser where username not in ["admin"]; #查询用户名不在[admin]范围之内的数据

模糊查询

select * from browser where username like "刘%"; #查询刘姓用户
select * from browser where username like "%刘%"; #查询名字当中含有刘的用户
select * from browser where username like "刘_"; #查询名字当中含有刘,后面还有一个字符的用户，但是注意_只能匹配一个字符

字段计算

拼接字段

concat 进行字段拼接

select concat(store_name,"(",goods_name,")") from browse_log limit 10;
#展示一下结果吧 
+---------------------------------------+
| concat(store_name,"(",goods_name,")") |
+---------------------------------------+
| 老边超市(小饼干)                      |
| 小卖部(棒棒糖)                        |
| 小卖部(小锤锤)                        |
| 超市(甜甜圈)                          |
| 小卖部(泡泡机)                        |
| 小卖部(棒棒糖)                        |
| 超市(甜甜圈)                          |
| 小卖部(小锤锤)                        |
| 小卖部(跳跳糖)                        |
| 小卖部(泡泡机)                        |
+---------------------------------------+
10 rows in set (0.03 sec)
​
#as 别名，使用频率巨高
mysql> select concat(store_name,"(",goods_name,")") as field from browse_log limit 10;
+------------------+
| field            |
+------------------+
| 老边超市(小饼干) |
| 小卖部(棒棒糖)   |
| 小卖部(小锤锤)   |
| 超市(甜甜圈)     |
| 小卖部(泡泡机)   |
| 小卖部(棒棒糖)   |
| 超市(甜甜圈)     |
| 小卖部(小锤锤)   |
| 小卖部(跳跳糖)   |
| 小卖部(泡泡机)   |
+------------------+
10 rows in set (0.00 sec)
​
#数值计算
select (price * 3) as value from browse_log limit 10; #查询3倍价格结果 
select (price / 3) as value from browse_log limit 10; #查询1/3价格结果
select (price + yf_money) as value from browse_log limit 10; #查询价格+运费的结果
select (price - yh_price) as value from browse_log limit 10; #查询价格-优惠结果的结果

MySQL函数

文本处理

select upper(username) from browse_log; #返回用户名大写数据
select length(username) from browse_log; #返回用户名长度数据
select lower(username) from browse_log; #返回用户名小写数据
select ltrim(username) from browse_log; #返回取消左边的空格的数据
select right(username,3) from browse_log; #返回用户名从右第三个字符截取的内容数据
select left(username,3) from browse_log; #返回用户名从左第三个字符截取的内容数据
select rtrim(username) from browse_log; #返回取消右边的空格的数据

时间处理

select now(); #返回当前时间
select curdate(); #返回当前日期 年月日
select curtime();#返回当前时间 时分秒
select unix_timestamp("2022-01-01"); #时间转换为时间戳
select unix_timestamp("2022-01-01"); #时间转换为时间戳
select unix_timestamp("2022-01-01"); #时间转换为时间戳
select date("2022-11-12 19:00:01"); #截取年月日
select year("2022-11-12 19:00:01"); #截取年
select month("2022-11-12 19:00:01"); #截取月
select day("2022-11-12 19:00:01"); #截取日
select time("2022-11-12 19:00:01"); #截取时间
select hour("2022-11-12 19:00:01"); #截取小时
select minute("2022-11-12 19:00:01"); #截取分钟
select second("2022-11-12 19:00:01"); #截取秒
select date_format("%Y-%m-%d", "2022-11-12 19:00:01"); #按照%Y-%m-%d格式获取年月日
select time_format("%H:%M:%S", "2022-11-12 19:00:01"); #按照%H:%M:%S格式获取时间
select unix_timestamp("2022-11-12 19:00:01"); #时间转换为时间戳
select from_unixtime("123123123"); #时间戳转换为时间

数值处理函数

select abs(-1); #返回绝对值数据
select cos(30); #返回角度的余弦
select exp(3); #返回指数值
select pi(); #返回圆周率
select sin(30); #返回角度正弦
select sqtr(4); #返回数的平方根
select tan(30); #返回角度的正切

聚合查询

select avg(age) from user; #返回平均年龄
select count(id) from user; #返回id的数据
select max(age) from user; #返回最大年龄
select min(age) from user; #返回最小年龄
select sum(money) from user; #返回钱的总和
select avg(distinct age) from user; #返回去重后的年龄的平均值

分组查询

#分组查询
select store_name,count(*) as store_number from browse_log group by store_name;
+------------+--------------+
| store_name | store_number |
+------------+--------------+
| 供销社     |         3303 |
| 小卖部     |         3372 |
| 老边超市   |            1 |
| 超市       |         3326 |
+------------+--------------+
4 rows in set (0.01 sec)
​
#分组条件过滤查询
mysql> select store_name,count(*) as store_number from browse_log group by store_name having store_name like "%超%"; ;
+------------+--------------+
| store_name | store_number |
+------------+--------------+
| 老边超市   |            1 |
| 超市       |         3326 |
+------------+--------------+
2 rows in set (0.01 sec)
​
#having和where 都可以作为过滤条件，但是where在分组之前条件过滤，having在分组后过滤，最重要的是where排除的行不会包括在分组当中。
​
#分组排序，条件过滤完进行排序
mysql> select store_name,count(*) as store_number from browse_log group by store_name having store_name like "%超%" order by store_number desc;
+------------+--------------+
| store_name | store_number |
+------------+--------------+
| 超市       |         3326 |
| 老边超市   |            1 |
+------------+--------------+
2 rows in set (0.02 sec)

这里强调一下，执行优先级：

select > from > where > group by > having > order by

嵌套查询

# 作为下个查询的条件
​
select * from browse_log  where id in (select id from browse_log where store_name="超市");
​
#作为下个查询的数据源，这里注意必须要设置别名才可以
select * from (select id,store_name from browse_log where store_name="超市") as tb1; 
​
#作为字段,统计每个店铺的订单数量
select store_name,(select count(*) from orders where orders.store_id = browse_log.id) as orders from  browse_log;

关联查询

这里应该考虑数据表之间的关系了，一对一，一对多，多对多关系,比如：

用户和用户详情的关系 是一对一关系

头像和用户 是一对多关系

角色和权限 是多对多权限

简单关联

也就是内连接

两个表的交集数据

mysql> select `user`.username,orders.order_number from `user`,orders where `user`.id = orders.user_id;
+----------+--------------+
| username | order_number |
+----------+--------------+
| laobian  | 00000001     |
| laobian  | 00000002     |
| laoli    | 00000003     |
+----------+--------------+
3 rows in set (0.00 sec)

inner join

两个表的交集数据

mysql> select `user`.username,orders.order_number from `user` inner join orders on `user`.id = orders.user_id;
+----------+--------------+
| username | order_number |
+----------+--------------+
| laobian  | 00000001     |
| laobian  | 00000002     |
| laoli    | 00000003     |
+----------+--------------+
3 rows in set (0.00 sec)

left join

以左表为主查询，如果右表没有数据返回null

select `user`.username,orders.order_number from `user`  left join orders on `user`.id = orders.user_id;
+----------+--------------+
| username | order_number |
+----------+--------------+
| laobian  | 00000001     |
| laobian  | 00000002     |
| laoli    | 00000003     |
| laowang  | NULL         |
| laoliu   | NULL         |
| laozhang | NULL         |
+----------+--------------+
6 rows in set (0.00 sec)

right join

以右表为主查询，如果左表没有数据返回null

mysql> select `user`.username,orders.order_number from `user`  right join orders on `user`.id = orders.user_id;
+----------+--------------+
| username | order_number |
+----------+--------------+
| laobian  | 00000001     |
| laobian  | 00000002     |
| laoli    | 00000003     |
| NULL     | 00000004     |
+----------+--------------+
4 rows in set (0.00 sec)

union表拼接

这个多用在不同表的相同数据拼接，比如：从用户和员工表里查询所有人的姓名和年龄：

mysql> select username from user union select username from customer;
+----------+
| username |
+----------+
| laobian  |
| laoli    |
| laowang  |
| laoliu   |
| laozhang |
| 老王     |
+----------+
6 rows in set (0.00 sec)

插入数据

#插入一条数据
insert into customer(字段1，字段2) values(字段1对应值，字段2对应值);
Query OK, 1 row affected (0.04 sec)
​
#将查询的数据插入到另外一个表当中，这个在小规模导出数据或者创建测试表超级好用
mysql> insert into customer (username) (select username from user);
Query OK, 5 rows affected (0.04 sec)
Records: 5  Duplicates: 0  Warnings: 0

修改数据

update customer set username="nice" where id = 1;

注意，这里如果不加where条件，嘿嘿嘿

删除数据

#删除库
drop database user;
​
#删除表
drop table user;
​
#删除数据
delete from user where id = 1; #同样，不加where条件的话......
​
#清空数据表
truncate user;

当然还有视图，索引，触发器这些东西，但是需要放到场景里才有意思，所以放到单独的篇章来分享

，这篇博客会一直升级，大家有啥好点子可以评论区留言啊。

本文正在参加「技术专题19期 漫谈数据库技术」活动

avatar
文章
阅读
粉丝