阅读 44

针对MySQL基础篇的一些语法细节以及注意点总结

小知识,大挑战!本文正在参与“  程序员必备小知识  ”创作活动

本文同时参与 「掘力星计划」  ,赢取创作大礼包,挑战创作激励金

👨‍🎓作者:Java学术趴

🏦仓库:GithubGitee

✏️博客:CSDN掘金InfoQ云+社区

💌公众号:Java学术趴

🚫特别声明:原创不易,未经授权不得转载或抄袭,如需转载可联系小编授权。

🙏版权声明:文章里的部分文字或者图片来自于互联网以及百度百科,如有侵权请尽快联系小编。微信搜索公众号Java学术趴联系小编。

☠️每日毒鸡汤:谁不是一边受伤,一边学会坚强。

👋大家好!我是你们的老朋友Java学术趴。MySQL是一个关系型数据库管理系统 由瑞典MySQL AB 公司开发,属于Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一,在WEB 应用方面,MySQL是最好的RDBMS (Relational Database Management System,关系数据库管理系统) 应用软件之一。

MySQL是一种关系型数据库管理系统,关系数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。

MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策,分为社区版和商业版,由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,一般中小型网站的开发都选择 MySQL 作为网站数据库。

在实际的开发中,SQL中的关键字使用大写,表名和字段名小写。

在SQL中,只有给列起别名的时候才使用双引号

MySQL中只有int有默认长度为11,其他的类型要给定长度。

数据库中的命名规则:全部使用小写字母,多个单词之间使用"_" 进行分隔。

MySQL中存在一种自动修改列名机制,当两个表联接时,如果两个表的列名相等时,此时mysql会自动将第二章表的列名修改,按照出现的表的顺序修改。

image-20211007163113529

在DOS中编写mysql语句时,可以写上注释,系统会自动忽略。

查询时候的执行顺序

以下代码是正确的,在group by 和 having还有ordey by 之后是支持别名的(在mysql是支持的,但是在Oracle中不支持),但是在where后不支持别名,在显示开发中尽量不用别名处理。

gak编码方式中汉字占两个字节,utf8中汉字占3个字节,英文和数字都占一个字节。

日期类型要使用单引号或者双引号括起来

1. 数据库的编码方式

查看当前数据库的编码方式

show variables like 'character%';
复制代码

在数据库中存在一种自动转换编码的机制:即如果客户端输入的编码方式是gbk,服务端(数据库管理系统)的编码方式是utf8,此时数据库会自动将 gbk-->utf8之后将数据存储。

image-20211005133155743

在数据库中是utf8,而不是utf-8

2.查看建表和建库语句

  • 查看建表语句
show create table users;
复制代码

  • 查看建库语句
show create database test;
复制代码

3.删除语句

  • 删除数据表
drop table users;
复制代码
  • 删除数据库
drop database test;
复制代码

4. 解决编码问题

即使查看了客户端和服务端的编码格式都是utf8,但是还是不能存储汉字和准确的显示汉字。

此时解决问题的方式:

修改客户端的输入方式与输出方式

image-20211005140526544

image-20211005140706091

GB2312一般指信息交换用汉字编码字符集

注意:以上修改仅限于本次使用,下次从新打开需要再次进行设置

5.新建一个数据库并添加数据流程

  • 新建一个数据库
create database test;
复制代码
  • 在该数据库中创建一张表
use test;
create table users(id int(10),name varchar(50));
复制代码
  • 向表中添加数据
insert into users values(1,'jack')
复制代码

5.1 if exist与 if not exists 的用法

  • if exists :代表如果存在。这个多用于在删除表和数据库的时候使用。
DROP table IF EXISTS peolpe;  # 这个代表如果people这张表存在就删除,即使不存在不会报错
DROP database IF EXISTS test; 
复制代码
  • if not exists : 代表如果不存在。这个多用于在创建表和数据库的时候使用。
CREATE database IF NOT EXISTS test; # 代表如果不存在,就创建这个数据库,存在不会报错。
CREATE table IF NOT EXISTS test;
复制代码

6. SQL三大语言分类

  • DML : 数据操作语言。
  • DDL :数据定义语言。(外部导入的sql文件)
  • DCL :数据控制语言。

7. SQL中的注释

  • 单行注释
-- 单行注释(注意:在--后边必须加一个空格)
#单行注释
复制代码
  • 多行注释
/*多行注释*/ 
复制代码

8. 空值问题

  • SQL中的空值参与运算结果还是空值。

此时可以使用 IFNULL 来解决

IFNULL(salary,o) #代表当salary为null的时候,将这个salary当做0处理
复制代码

9. 给列值起别名问题

  • AS可以省略不写,单引号、双引号都可以省略不写,当名字中含有特殊符号(比如空格的时候,必须写双引号或者单引号)
  • 在SQL语句中,只有起别名的时候使用双引号(不是必须使用,就是建议使用)

10. 去重问题

  • 使用关键字 distinct
SELECT DISTINCT job_id FROM emp;
复制代码
  • distinct 不能出现在两个字段之间。
  • distinct 出现在两个字段之前:代表两个字段联合去重
SELECT SUM(DISTINCT salary),SUM(salary) FROM emp; 
# 第一个结果是工资去重后的和 第二个是不去重的结果。
复制代码
  • distinct与分组函数联合使用
SELECT SUM(distinct salary),count(distinct salsry) from emp;
复制代码

11. 查看表结构

  • 使用关键字 describe,这个使用的时候可以简写 desc
DESC emp;
复制代码

12. 比较运算

  • 等于是“=”符号,而不是“== "符号。
  • 不等 <> 或者是 !=

13.其他运算符

  • IN(...,...,...) : 这个代表查询的数据是IN括号中的任意一个即可。

14.模糊查询

  • & :代表零个或者多个任意字符。
  • _ :代表任意一个字符。

如果想要查询名字中带有下划线(_)的名字,此时需要使用转义字符

SELECT name FROM users WHERE name LIKE '%_%'; # \ 这个是SQL中默认的转义符号
SELECT name FROM users WHERE name LIKE '%$_%' ESCAPE '$'; #使用escape自定义。
复制代码

如果要查询的名字中既包含a也包含e;

SELECT name FROM users WHERE name LIKE '%a%e%' or name LIKE '%e%a%';
复制代码

15. 空值与非空值查询

  • IS NULL : 空值 (查询工资为NULL的数据)
SELECT salary FROM users WHERE salary IS NULL;
复制代码
  • IS NOT NULL : 非空值 (查询工资不为NULL的数据)
SELECT salary FROM users WHERE salary IS NULL;
复制代码

16. 排序问题

  • 注意:在MySQL中,将排序语写在最后一句。
  • 升序排序使用关键字:ASC
  • 在SQL语句中,默认就是升序排序。
SELECT salary FROM users ORDER BY salary ASC;
复制代码
  • 降序排序使用关键字:DESC
SELECT salary FROM users ORDER BY salary DESC;
复制代码
  • 排序的列中含有NULL,此时将NULL看作是无穷大
  • 降序的时候NULL在第一个。
  • 升序的时候NULL在最后一个。

17. 使用别名进行排序

  • 注意:使用别名排序时,别名不加双引号。
SELECT salary *12*(1+IFNULL(salaryadd)) as "年薪" FROM users ORDER BY 年薪 DESC;
复制代码

18. 多列排序

先将薪水进行降序排序,再将薪水相同的名字按照升序排序。

注意:先写哪个属性就先对哪个属性进行排序,按顺序执行。

SELECT salary,name FROM users ORDER BY salary DESC,name ASC;
复制代码

19. 在MySQL中还可以进行计算

image-20211005173023041

20. Union 结果集相

20.1 Union 关键字

Union会发生去重现象,当查询出来的结果存在重复项的时候会进行去重处理。

Union中要求查询的结果来自于多个表,且多个表没有直接的连接关系,但查询的信息一直时。

Union的查询结果来自于多个表,且多个表没有直接的连接关系,但查询的信息一致时。

要求多条查询语句查询的每一列的类型和顺序最好一致。

注意:在Mysql中没有要求这两个列的类型一致,但是在Oracle中要求两个列的数据类型也要一致。

  • 将年龄为10岁和20岁的查询出来。
SELECT age,name FROM users WHERE age = 10;
UNION
SELECT age,name FROM users WHERE age = 20;
复制代码

20.2 Union all 关键字

使用Union all 可以解决查询结果去重现象,将查询出来的重复结果去重。

21.limlit 分页

  • 在MySQL中,limit在order by 之后执行。
  • 语法格式:limit startIndex,length (startIndex 是起始下标,length代表长度)
  • 起始下标从0开始。
  • 缺省写法:limlit 5 代表取到是这组数据中的前5个。

找出工资排行前5的

SELECT name,sal FROM emp order by sal desc  limlt 5;
复制代码

找出工资排行在 [3-5] 的员工

SELECT name,sal FROM emp order by sal desc limit 2,3;
复制代码
分页公式:设定pageSize代表每页显示的数据条数
int pageNO = 5; // 第五页
int pageSize = 10;  // 每页显示10条
int startIndex = (pageNO - 1) * pageSize;
得出分页的范围就是 : limit startIndex,pageSize;
复制代码

22.group by 分组函数(聚合函数) 与 having 分组后继续筛选。

22.1 分组函数(聚合函数)

  • sum() 求和
  • avg() 平均值
  • max() 最大值
  • min() 最小值
  • count() 计数

在分组函数中给数值类型,如果给字符类型,产生的结果都是0.

分组函数在使用的时候自动忽略NULL。不用对NULL使用IFNULL处理。

sum,avg 一般用于处理数值型,max min count 可以处理任何类型

NULL并不是代表0,而是什么都没有。所以用is null 与 is not null 处理

数据库中不存在所有字段都是null的数据,但凡这条数据在数据库中存在,那么至少有一个字段不为null

在where中不能直接使用分组函数 group by

分组函数可以联合使用

SELECT avg(sal),max(sal),min(sal) FROM emp;
复制代码

使用分组函数找出每个工作岗位的工资和。

SELECT job,sum(sal) from emp group by job;
复制代码

使用having与group by解决:找出每个部门最高薪资,要求显示最高薪资大于3000的。

SELECT depton,max(sal) AS maxsal FROM emp group by depton having maxsal>3000;
复制代码

注意:having不可以单独使用,having不可以代替where,having必须和group by 联合使用

having的效率要比where的效率低,在开发中尽量使用where

使用where与group by 解决以上问题

SELECT depton,max(sal) FROM emp WHERE sal > 3000 group by depton; 
复制代码

where没有办法解决的

SELECT depton,avg(sal) FROM emp group by depton having avg(sal) > 2500;
复制代码

22.1 count()函数的详细介绍

SELECT COUNT(salary) FROM emp;  #此时统计的是salary这一列不为null的行数。
SELECT COUNT(*) FROM emp;  # 统计这一组数据的行数(每列都为NULL的数据不会存储到数据库)
SELECT COUNT(1) FROM emp; 
# 以上这条sql语句的含义和第二种的意思一样,相当于在每行数据的前边加上一个1,然后统计1的个数。
# 括号里边可以是任意类型的值,COUNT('天气')、COUNT('2')
复制代码

效率问题

  • MYISAM存储引擎下,COUNT(*)的效率要高。
  • INNODB存储引擎下,COUNT(*)和COUNT(1)的效率差不多,比COUNT(字段)要高些。

22.2 MAX() 与 MIN() 函数详解

  • 使用这两个函数作用与varchar等数据类型的字段,会自动将其转换为长度,相当于MAX(LENGTH(字段)); 汉字占两个或者三个长度。
SELECT MAX(name) FROM emp;  # 此时返回的是姓名中最长的那个
SELECT MIN(name) FROM emp;  # 此时返回的是姓名中最短的那个
复制代码

22.3 分组函数(聚合函数)与去重(distinct)联合使用

  • 实现去重的统计。
SELECT SUM(DISTINCT salary) FROM emp;
复制代码
  • 去除重复的工资,然后在将所有的相加。

22.4 分组查询(group by)

  • 查询列表比较特殊,要求是分组函数和group by 后出现的字段。

例题:查询每个部门的最高薪资(简单的分组查询,不添加任何的分组条件,整个表就是一组。)

SELECT MAX(salary),dept FROM emp GROUP BY dept;
复制代码

例题:查询邮箱中包含a字符的,每个部门的平均工资。(添加简单的过滤条件)

SELECT AVG(salary),dept FROM emp WHERE email like '%a%' GROUP BY dept;
复制代码

例题:查询哪个部门的员工个数大于2(使用到了having但是没有使用到where)

SELECT COUNT(*),dept FROM emp CROUP BY HAVing COUNT(*) > 2;
复制代码

例题:查询每个部门有奖金的员工的最高薪资大于12000部门和最高薪资。

SELECT MAX(salary),dept FROM emp WHERE aad_salary IS NOT NULL GROUP BY dept HAVING MAX(salary) > 12000;
复制代码

  • 多个字段分组,并且联合排序使用

例题:查询每个部门每个工种的员工的平均薪资 。

SELECT AVG(salalary),dept,type FROM emp GROUP BY dept,type ORDER BY AVG(sala) DESC;  #部门的顺序可以改变。
复制代码

23.几种特殊的加法计算

  • 两个数值类型计算结果就为数值型。
SELECT 100+90;  # 结果为:190
复制代码
  • 这是与Java的区别:在mysql中,只要进行计算,那么就会把字符类型转换为数值类型然后计算(此时字符型可以转换为数字)
SELECT '100'+90;  # 结果为:190
SELECT '100'+'90' # 结果为:190
复制代码
  • 当字符型不可以转化为数值型,此时就将字符型转换为0进行计算
SELECT 'haha'+100;  # 结果为:100
SELECT 'haha'+'houhouhou' # 结果为0
复制代码
  • 当有一方为NULL的时候,此时的计算结果为NULL,在mysql中只要有null参与的计算,其结果一定是null,除了分组函数(max()、sum())会自动忽略null,如果不想让其输出的为null,需要使用if null 处理,此时会将这个null视为用户给定的值处理
SELECT NULL+90; #结果为:null
复制代码
  • 使用ifnull处理。
SELECT IFNULL(NULL,10)+90; // 结果为:100
SELECT IFNULL(NULL,0)+90;  // 结果为:90
复制代码

24.查询常量

SELECT 100;
复制代码
SELECT 'String';
复制代码

25.查询运算

SELECT 100*100;
复制代码
SELECT 100%98;
复制代码

26.查询函数(查询版本号)

SELECT VERSION();
复制代码

27.拼接结果集(concat)

SELECT CONCAT(first_name,last_name) as "姓名" FROM emp;
复制代码

28.mysql中的数据类型

varchar与char的区别:

  • varchar可以动态的分配用户输入数据的空间大小。
  • char不可以动态的分配数据空间大小。

比如:如果用户设定的为 char(10)、varchar(10),用户输入男,此时varchar会动态分配的空间为1,而char分配的空间是10。

数据类型的最大长度

  • varchar 最长为255
  • char 最长为255
  • int 最长为11

注意:这个代表的是长度,不是所代表的字节数,一个汉字和英文都算一个长度。

clob类型

  • 字符大对象,最多可以存储4G的字符串。比如:存储一篇文章,自我介绍等。超过255个字符的采用CLOB字符大对象来存储

blob类型

  • 二进制大对象,专门用来存储图片、声音、视频等流媒体数据。往blob类型的字段上插入数据的时候,列入插入一张图片、视频等,你需要使用IO流才行。

29.IFNULL的用法

  • ifnull是针对处理null的一个关键字,使用格式
SELECT salary FROM emp WHERE IFNULL(salary,0) + 100; 
#此时IFNULL代表的含义是当薪资为NULL的时候,会把这个salary当作0处理。如果不写IFNULL,则输出结果一定是NULL。
​
SELECT salary FROM emp WHERE IFNULL(salary,10) + 100; 
#此时IFNULL代表的含义是当薪资为NULL的时候,会把这个salary当作10处理。
复制代码

-------💘看完的大佬们可以关注一下小编,会一直更新小技巧,免费分享给大家呦!!!💝-----

文章分类
后端