MySQL基础知识:SQL语法

时间:2024-10-20 17:24:38

1. 基础概念

  1. DQL:select
    查询
  2. DML:insert、delete、update
    增删改
  3. DDL:create、drop、alter、truncate
    表的操作
  4. TCL:commit、rollback
    事务操作

2. 数据类型

整数类型
int:正常情况数字就用它
bigint:处理特别巨大的数字,例如双十一交易量
tinyint:一般对应布尔值true和false

浮点数类型
float:单精度浮点,占用4字节
double:双精度浮点,占用8字节
浮点数可能会有精度丢失的问题

字符串类型
varchar:可变长度,效率较低
char:固定长度,效率较高

varchar和char的区别:二者最大长度都是255,varchar可变长度,可以节省空间,但是动态分配空间速度可能很慢。二者创建时必须手动定义长度。
怎么选:短字符串、定长字符串(如UUID)、频繁改变的字符串,用char好一些;在InnoDB引擎中,内部的行存储格式并没有区分固定长度和可变长度列,而且主要影响性能的因素是数据行的存储总量,由于varchar的占用空间较小,所以除了前面说的三种,其余情况用varchar会好一些。

日期与时间类型
date
datetime

date和datetime区别:date是短日期,只有年月日;datetime是长日期,有年月日时分秒。MySQL用now()函数获取当前时间,是datetime类型的。


3. 函数


3.1 单行处理函数

对具体某一字段的某一个数据进行数学运算、字符串转换等。
单行处理函数的数量很多,可以自行上网搜索。

3.2 多行处理函数

count() sum() avg() max() min()
多行处理函数又叫分组函数,必须先进行分组才能用
如果没有分组,整张表默认为一组

由sql关键字的执行顺序:
from…join…on…where…group by…having…select…order by…limit…
得出:on和where后面不能使用多行处理函数,having和select后面可以。

3.3 其余常用函数

3.3.1 group_concat(distinct xxx separator x)

将分组中括号里字段进行字符串连接,如果分组中括号里的参数xxx有多行,每个字符串之间以特定符号进行分隔(如果不指定,默认以逗号分隔),一般搭配group by使用。

比如这样一张表user:
用户表user
我们想拿到每个部门的成员信息,去重,以分号间隔。

sql语句

select dept_name, group_concat(distinct real_name separator ',') as real_name
from user group by dept_name;

得到的结果
查询结果



3.3.2 find_in_set(str,strlist)

str为要查询的字符串,strlist为字段名,其参数以逗号分隔。返回str在strlist中的位置,以1开始。如果str不在strlist 或strlist 为空字符串,则返回值为0。

find_in_set和in的区别:
我们以上面查询得到的结果表为例,如果我们想找real_name中有老八的数据,我们用in就无法做到。

select dept_name, real_name
from (
select dept_name, group_concat(distinct real_name separator ',') as real_name
from user
group by dept_name
) a
where '老八' in real_name;

报错了,in的正确语法应该是:

select dept_name, real_name
from (
select dept_name, group_concat(distinct real_name separator ',') as real_name
from user
group by dept_name
) a
where '老八' in ('老八', '刘勇', '王境泽');

也就是说,in后面跟着的必须得是常量,写死的。

我们用find_in_set即可达到要求:

select dept_name, real_name
from (
select dept_name, group_concat(distinct real_name separator ',') as real_name
from user
group by dept_name
) a
where find_in_set('老八', real_name) != 0;

拿到了有老八的那行数据。
所以,如果strlist是常量,我们使用in是没问题的,如果是变量,我们使用find_in_set。

find_in_set和like的区别:
find_in_set是精确匹配,也就是说要老八就只要老八。而like,如果有个人名字叫岛市老八,也会被匹配上,导致结果不准确。


4. 关键字

4.1 查询

4.1.1 join

单独一个join是内连接,建议写成inner join增强可读性。
left join 和 right join是外连接,也可以在join前面加一个outer。

内连接:连接结果仅包含符合连接条件的行,参与连接的两个表都要满足on之后的连接条件。
外连接:连接结果不仅包含符合连接条件的行,也包含不符合条件的行。外连接分为左外连接、右外连接和全外连接。

内连接外连接怎么选?
例如这样一个需求:查询所有订单中商品总额(商品数量*商品价格)都大于500的用户昵称。

商品表字段

商品表字段

订单表字段

订单表字段

用户表字段

用户表字段

这种情况,我们在连接商品表和订单表的时候,就要注意:如果我们使用外连接,并把商品总额大于500这个条件放到on后面判断,就会导致结果不准确。

select c.nick_name
from t_order a
left join t_goods b
on a.goods_id = b.goods_id
and a.goods_num * b.goods_price > 500
left join t_user c
on a.user_id = c.user_id;

我们发现匹配的结果比预计的要多,找一下原因。

select c.nick_name, b.goods_price, a.goods_num
from t_order a
left join t_goods b
on a.goods_id = b.goods_id
and a.goods_num * b.goods_price > 500
left join t_user c
on a.user_id = c.user_id;

多查询出两个字段,得出了这样的结果。

join的错误查询

发现原因:我们连接商品表和订单表的时候,使用left join确定t_order表为主表,也就是说就算数据不匹配,t_order表中所有数据都会展示,t_goods表中不匹配的数据被查询出为Null,所以我们连接订单表与用户表查询用户昵称的时候,就会把所有订单对应的用户昵称查询出来,这显然是不准确的。

还有一点需要注意:一个人可能下了多个订单,所以我们在查询用户昵称时要做去重处理。

解决完这些问题后,sql代码如下:

判断条件放到where里

select distinct c.nick_name
from t_order a
left join t_goods b
on a.goods_id = b.goods_id
left join t_user c
on a.user_id = c.user_id
where a.goods_num * b.goods_price > 500;

使用内连接代替外连接

select distinct c.nick_name
from t_order a
inner join t_goods b
on a.goods_num * b.goods_price > 500
and a.goods_id = b.goods_id
left join t_user c
on a.user_id = c.user_id;

4.1.2 distinct

做查询结果(一条数据)去重,只能出现在所有字段最前方

4.1.3 limit

语法:limit startIndex,length 或者 limit length
前者表示从第startIndex条(第一条数据为0)开始,往后取length条数据,后者就相当于startIndex等于0的情况
例如:limit 0, 10 相当于 limit 10,取前十条数据。
做翻页时,如果每页显示pageSize条数据,要查询第pageNo页,sql语句:
limit (pageNo-1)*pageSize , pageSize

4.1.4 union

两张表连接查询,匹配次数为两张表条数的乘积,又称笛卡尔积
多表联查时,union的查询效率要更高,因为可以减少匹配的次数。


4.1.5 SQL查询关键字执行顺序

from…join…on…where…group by…having…select…order by…limit…


4.2 插入

4.2.1 语法规则

单条插入

insert into 表名(字段名1, 字段名2) values (1,2);

多条插入

insert into 表名(字段名1, 字段名2) values (1,2), (3,4), (5,6);

查询插入,要求插入列与查询列一一对应。

insert into 表A(字段名1, 字段名2) select (字段名1, 字段名2) from 表B;

4.3 删除

4.3.1 语法规则

delete

delete from 表名 where 字段名 = 字段值;

如果没有where筛选,则会删除表里所有数据。

truncate
截断 删除表中全部数据

truncate table 表名;

drop

drop table 表名;

4.3.2 truncate、delete、drop区别

delete把表中数据删除了,但是数据在硬盘上真实存储空间不会被释放。
缺点:效率较低;优点:如果后悔了,可以回滚数据。
truncate把表一次截断,效率很高,但是不支持回滚。
truncate为高危操作,轻易不要使用。
drop删除表,整个表都没了。效率最高,但是也不能回滚。

delete属于DML语句,drop和truncate属于DDL语句


4.4 更改

4.4.1 语法规则

update 表名 set 字段名1 = 字段值1, 字段名2 = 字段值2 where 字段名 = 字段值;

4.4.2 case…when…then…else…end

语法格式是这样的

CASE
WHEN cond1 THEN value1
WHEN cond2 THEN value2
WHEN condN THEN valueN
ELSE value
END;

我们以上面的商品表为例,有一个需求:为所有价格超过100元的商品打五折,如果打折后小于100则置为100。

直接上代码

update t_goods
set goods_price = (
case when goods_price > 100
and goods_price < 200
then 100
when goods_price > 200
then goods_price * 0.5
else goods_price
end);

4.5 创建表

创建表语句一般不需要我们自己写,一般是用Navicat创建,然后Navicat会自动给我们生成建表语句。
创建表的时候会有一些字段的约束条件,这个在另一篇文章会有详细讲解。

4.6 更改表结构

4.6.1 添加/删除字段

alter table 表名 add 字段名 字段类型(长度);
alter table 表名 drop 字段名;

4.6.2 更改字段名/字段类型

alter table 表名 change 老字段名 新字段名 字段类型(长度);

4.6.3 增加/删除字段默认值约束

alter table 表名 modify 字段名 字段类型(长度) default 默认值;
alter table 表名 modify 字段名 字段类型(长度);

4.6.4 增加/删除字段非空约束

alter table 表名 modify 字段名 字段类型(长度) not null;
alter table 表名 modify 字段名 字段类型(长度);

4.6.5 增加/删除字段唯一约束

alter table 表名 modify 字段名 字段类型(长度) unique;
alter table 表名 drop index 字段名;