您的位置: 首页  >  文章  >  MySQL学习笔记(2)

MySQL学习笔记(2)

分类: 文章 • 2024-06-27 21:55:34

文章目录

导入示例数据库

  1. 下载示例数据库
    示例数据库结构参考
  2. 解压到如图所示位置；
    MySQL学习笔记(2)
  3. 连接到MySQL服务器并创建数据库；
    MySQL学习笔记(2)
    创建数据库：
    MySQL学习笔记(2)
    导入数据：
    MySQL学习笔记(2)
  4. 测试导入结果；
    MySQL学习笔记(2)

SQL与MySQL

什么是SQL？

SQL是Structured Query Language（结构化查询语言）的缩写。
SQL是一种专门用来与数据库沟通的语言。
设计SQL的目的是很好地完成一项任务——提供一种从数据库中读写数据的简单有效的方法。

SQL有以下优点：

  • SQL不是某个特定数据库供应商专有的语言。几乎所有的DBMS都支持SQL，所以血刺此语言使你几乎能与所有数据库打交道。
  • SQL简单易学。它的语句全都是由有很强描述性的英语单词组成，而且这些单词的数目不多。
  • SQL虽然看上去简单，但实际上是一种强有力的语言，灵活使用其语言元素，可以进行非常复杂和高级的数据库操作。

SQL包含三个部分：

什么是MySQL？

MySQL的官方网址
MySQL的社区版本下载地址
MySQL是一个数据库管理系统，也是一个关系数据库。它是由Oracle支持的开源软件。任何一个人都是可以免费使用MySQL。另外，还可以更改其源代码或进行二次开发来满足需要。
除此以外，还可以从Oracle购买商业许可证版本，以获得高级支持服务（特殊企业用户需要）。
与其他数据库软件相比（Oracle数据库或Microsoft SQL Server）相比，MySQL容易学习和掌握。
MySQL可以再各种平台上运行UNIX,Linux,Windows等。可以将其安装在服务器甚至桌面系统上。此外，MySQL拥有可靠、可扩展和快速地优点。
如果开发网站或Web应用程序，MySQL是一个不错的选择（强烈建议使用）。MySQL是LAMP对战的重要组成部分，包括Linux，Apache，MySQL和PHP。

查询语句 SELECT FROM

语句解释

使用select语句从表或视图获取数据。表由行和列组成，如电子表格。通常，我们只希望看到子集行，列的子集或两者的组合。
select语句的结果称为结果集，它是行列表，每行由相同数量的列组成。
示例数据库yiibaidb中的employees表的结构：
MySQL学习笔记(2)
SQLyog的下载地址 提取码：zb6m

select 语句的语法：

     SELECT
        	column_1,column_2, ……
        FROM 
        	table_1
        [INNER | LEFT | RIGHT ] JOIN table_2 ON conditions
        WHERE
        	conditions
        GROUP BY column_1
        HAVING group_conditions
        ORDER BY column_1
        LIMIT OFFSET, LENGTH;

SELECT语句有以下表中所属的几个子句组成：

  • SELECT 之后是逗号分隔列或星号（*）的列表，表示要返回所有列。
  • FROM 指定要查询数据的表或视图。
  • JOIN 根据某些连接条件从其他表中获取数据。
  • WHERE 过滤结果集中的行。
  • GROUP BY 将一组行组合成小分组，并对每个小分组应用聚合函数。
  • HAVING 过滤器基于 GROUP BY 子句定义的小分组。
  • ORDER BY 指定用于排序的列的列表。
  • LIMIT 限制返回行的数量。

语句中的 SELECT 和 FROM 语句是必须的，其他部分是可选的。

去重语句

使用DISTINCT关键字，指示数据库只返回不同的值（消除结果集中的重复行）。

输入：SELECT DISTINCT vend_id FROM products;
分析： SELECT DISTINCT vend_id 告诉DBMS只返回不同（具有唯一性）的vend_id行。使用DISTINCT关键字，必须直接放在列名的前面。

注意：不能部分使用DISTINCT
DISTINCT关键字作用于所有的列，不仅仅是跟在其后的那一列。

DISTINCT和NULL
如果列皆有NULL值，并且对该列使用DISTINCT子句，MySQL将保留一个NULL值，并删除其他的NULL值，因为DISTINCT子句将所有NULL值视为相同的值

DISTINCT和聚合函数
可以使用具有聚合函数（例如SUM,AVG和COUNT）的DISTINCT子句中，在MySQL将聚合函数应用于结果集之前删除重复的行。

前N个语句

使用SELECT+LIMIT关键字限制最多返回多少行。

SELECT prod_name FROM Products LIMIT 5；
只检索前5行数据

SELECT prod_name FROM Products LIMIT 5 OFFSET 5；
LIMIT 5 OFFSET 5指示MySQL等DBMS 返回从第五行起的5行数据。

LIMIT指定返回的行数。LIMIT带的OFFSET指定从哪儿开始。

注意：第0行
第一个被检索的行是第0行，而不是第1行。因此，LIMIT1 OFFSET1 会检索第2行而不是第1行。

DISTINCT与LIMIT子句
如果要将DISTINCT与LIMIT子句一起使用，MySQL会在查找LIMIT子句中指定的唯一行数时立即停止搜索。

CASE……END判断语句

什么情况下使用？

  • 当需要依据条件的取值而决定某值的选择时，可以使用case-when-end语句

CASE [col_name] WHEN [value1] THEN [result1] ……ELSE [default] END CASE WHEN [expr] THEN [result1] ……ELSE [default] END

SELECT 
  CASE
    p.purcategory
    WHEN 'A' 
    THEN '工程类' 
    WHEN 'B' 
    THEN '货物类' 
    WHEN 'C' 
    THEN '服务类' 
	else '其他类'
  END AS purcategory 
FROM
  priject p

类似于java中的switch-case

  • 如果“p.purcategory”等于“A”，打印工程类；
  • 如果“p.purcategory”等于“B”，打印货物类；
  • 如果“p.purcategory”等于“C”，打印服务类；
  • 如果“p.purcategory”没有找到相同匹配字段，打印其他类型。

筛选语句 WHERE

语句解释

WHERE子句允许根据指定的过滤表达式或条件来指定要选择的行。

从employees表中获取销售代表员工

 SELECT 
         lastname,firstname,jobtitle
    FROM
    	employees
    WHERE
    	jobtitle = 'Sales Rep';

即使WHERE子句出现在语句的末尾，但MySQL会首先使用WHERE子句中的表达式来选择匹配的行。它选择具有职位名称为销售代表的行记录。

可以向上面的查询一样形成一个简单的条件，或者是将多个表达式与逻辑运算符（如AND，OR等）组合在一起的一个非常复杂的例子。例如，要在办公室代码（officeCode）等于1中查找所有销售代表：

SELECT lastname,firstname,jobtitle  FROM employees WHERE	jobtitle = 'Sales Rep' AND officeCode = 1;

运算符/通配符/操作符

运算符

下表列出了可用于在WHERE子句中形成过滤表达式的比较运算符。

操作符 描述
= 等于，几乎任何数据类型都可以使用它。
<>或!= 不等于
< 小于，通常使用数字和日期/时间数据类型。
> 大于
<= 小于或等于
>= 大于或等于

查询使用不等于(!=)运算符来获取不是销售代表的其它所有员工：

`SELECT lastname, firstname, jobtitle FROM employees WHERE jobtitle <> 'Sales Rep';`

查询将获得办公室代码大于5的每位员工：

SELECT lastname, firstname, officeCode FROM employees WHERE officecode > 5;

除此以外，还可以使用一些有用的运算符在WHERE子句中使用来形成复杂的条件，例如：

  • BETWEEN选择在给定范围值内的值。
  • LIKE 匹配基于模式匹配的值。
  • IN指定值是否匹配列表中的任何值。
  • IS NULL检查该值是否为NULL。

组合WHERE子句（操作符）

  • AND WHERE子句中使用的关键字，指示DBMS只返回满足所有给定条件的行。
  • OR WHERE子句中使用的关键字，用来表示检索皮皮任一给定条件的行。

提示：在WHERE子句中使用圆括号
任何时候使用具有AND和OR操作符的WHERE子句，都应该使用圆括号明确地分组操作符。消除歧义。

  • IN WHERE子句中用来指定要匹配值的清单的关键字，功能与OR相当。

IN操作符的优点
在有很多合法选项时，IN操作符的语法更清楚，更直观。
在与其他AND和OR操作符组合使用IN时，求值顺序更容易管理。
IN操作一般比一组OR操作符执行得更快。
IN的最大优点是可以包含其他SELECT语句，能够更动态地建立WHERE子句。

  • NOT WHERE子句中用来否定其后条件的关键字。

在与IN操作符联合使用时，NOT可以非常简单地找出与条件列表不匹配的行。

通配符

通配符（wildcard）：用来匹配值的一部分的特殊字符。
搜索模式（search patterm）：由字面值、通配符或两者组合构成的搜索条件。

  • LIKE操作符

  • 百分号（%）通配符 在搜索串中，%表示任何字符出现任意次数（可以匹配0个字符）。

注意：请注意NULL
通配符%看起来像是可以匹配任何东西，但有个例外，就是NULL。

  • 下划线（_）通配符 一个_总是匹配刚好一个字符，不能多也不能少。
  • 方括号（[ ]）通配符 用来指定一个字符集，必须匹配指定位置（通配符的位置）的一个字符。

找出所有名字以J或M起头的联系人：
[JM]% 这一搜索模式使用了两个不同的通配符。
[JM]匹配方括号中任意一个字符，它也只能匹配单个字符。因此，任何多于一个字符的名字都不匹配。
[JM]之后的%通配符匹配第一个字符后的任意数目的字符，返回所需结果。

使用通配符的技巧

  • 不要过度会用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
  • 在确实需要使用通配符时，也尽量不要把他们用来搜索模式的开始出。把通配符置于开始出，搜索起来是最慢的。
  • 仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。

分组语句 GROUP BY

语句解释

GROUP BY子句指示DBMS分组数据，然后对每个组而不是整个结果集进行聚集。

  • GROUP BY 子句可以包含任意数目的列，因而可以对分组进行嵌套，更细致地进行数据分组。
  • 如果在GROUP BY中嵌套了分组，数据将在最后指定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）
  • GROUP BY子句中列出的每一列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT子句中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。
  • 大多数SQL实现不允许GROUP BY列带有长度可变的数据类型（如文本或备注型字段）。
  • 除聚集计算语句外，SELECT语句中的每一列都必须在GROUP BY子句中给出。
  • 如果分组列中包含具有NULL值的行，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
  • GROUP BY子句必须出现在WHERE子句之后，ORDER BY 子句之前。

聚集函数

函数 说明
AVG() 返回某列的平均值
COUNT() 返回某列的行数
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列值之和

HAVING子句

HAVING类似于WHERE。
目前为止所学过的所有类型的WHERE子句都可以用HAVING来替代。
唯一的差别是，WHERE过滤行，HAVING过滤组。
如果不指定GROUP BY，则大多数DBMS会同等对待WHERE与HAVING。

排序语句 ORDER BY

语句解释

ORDER BY 子句取一个或多个列的名字，据此对输出进行排序。

注意：ORDER BY子句的位置
在指定一条ORDER BY子句时，应该保证它是SELECT语句中最后一条子句。如果不是最后的子句，将会出现错误消息。

正序、逆序

正序：ASC
逆序：DESC

函数

大多数SQL实现支持以下类型的函数：

  • 用于处理文本字符串（如删除或填充值，转换值为大写或小写）的文本函数
  • 用于在数值数据上进行算术操作（如返回绝对值，进行代数运算）的数值函数
  • 用于处理日期和时间值并从这些值中提取特定成分（如返回两个日期之差，检查日期有效性）的日期和时间函数
  • 返回DBMS正使用的特殊信息（如返回用户登录信息）的系统函数

文本处理函数

函数 说明
LEFT()（或使用子字符串函数） 返回字符串左边的字符
LENGTH()（也是用DATALENGTH（）或LEN（）） 返回字符串的长度
LOWER() 将字符串转换为小写
LTRIM() 去掉字符串左边的空格。
RIGHT()（或使用子字符串函数） 返回字符串右边的字符
RTRIM() 去掉字符串右边的空格。
SOUNDEX() 返回字符串右的SOUNDEX值
UPPER() 将字符串转换为大写

关于SOUNDEX
SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。
SOUNDEX考虑了类似的发音字符和音节，使得对字符串进行发音比较而不是字母比较。

时间函数

日期和时间采用相应的数据类型存储在表中，每种DBMS都有自己的特殊形式。日期和时间值以特殊的格式存储，以便能快速和有效地排序和过滤，并且节省物理存储空间。

  • YEAR()函数提取和使用日期的成分（年） 。按月份过滤，可以进行相同的处理，使用AND操作符可以进行年和月份的比较。
    WHERE YEAR(order_date) = 2012：只返回2012年的订单

数值函数

数值函数仅处理数值数据。这些函数一般主要用于代数、三角或几何运算。

函数 说明
ABS() 返回一个数的绝对值
COS() 返回一个角度的余弦
EXP() 返回一个数的指数值
PI() 返回圆周率
SIN() 返回一个角度的正弦
SQRT() 返回一个数的平方根
TAN() 返回一个角度的正切

SQL注释

使用SQL注释的原因：

  • 随着SQL语句变长，复杂性增加，添加一些描述性的注释，便于自己今后参考，或者供项目后续参与人员参考。注释需要嵌入在SQL脚本中，但不能进行实际的DBMS处理。
  • 同样适用于SQL文件开始处的内容，可能包含程序员的联系方式、程序描述以及一些说明。
  • 注释的另一个重要应用是暂时停止要执行的SQL代码。如果在一个长SQL语句中只想测试其中一部分，可以选择注释掉一些代码，以便DBMS将其视为注释而加以忽略。

行内注释

SELECT prod_name -- 这是一条注释
FROM Products；

注释使用-- （两个连字符）嵌在行内。–之后的文本就是注释。

#这是一条注释
SELECT prod_name
FROM Products;

在一行的开始处使用#，这一整行都将作为注释。

多行注释

注释可以在脚本的任何位置停止和开始。

/*SELECT prod_name,vend_id
FROM  Products;*/
SELECT prod_name
FROM Products;

注释从/*开始，到*/结束，/*和*/之间的任何 内容都是注释。这种方式常用于代码加注释。此例中，第一个SELECT语句不会执行，因为已经被注释。

SQL代码规范

命名的建议

  • 使用同一的、描述性强的字段命名规则
  • 保证字段名是独一无二且不是保留字的，不要使用连续的下划线，不同下划线结尾
  • 最好以字母开头

id 标识符——user_id 用户标识 item_id 商品标识
dt 表示某个事件发生的时间——ord_dt 订单时间 exam_dt 考试时间
num 表示某事相关的数字——sale_num 销量
name 用字母表示名称——stu_name 学生名 等

格式建议

  • 最好使用标准SQL函数而不是特定供应商Oracle、Mysql等的函数以提高可移植性
  • 大小写的运用，系统关键字大写，字段表名小写
  • 灵活使用空格和缩进来增强可读性——两大法宝空白隔道与垂直间距
  1. 利用空格保持关键字对齐
  2. 在等号前后（=）在逗号后（,）单引号前后（’）加上空格
  3. 子查询缩进并对齐

语法建议

  • 尽量使用BETWEEN而不是多个AND
  • 同样，使用 IN 而不是多个OR
  • 利用CASE语句嵌套处理更复杂的逻辑结构
  • 避免UNION语句与临时表

TOOLS

推荐SQL插件SQLinForm

项目一：查找重复的电子邮箱

  • 创建表
    MySQL学习笔记(2)
  • 插入数据
    MySQL学习笔记(2)
  • 查找重复的电子邮箱
    MySQL学习笔记(2)

项目二：查找大国

  • 创建表
    MySQL学习笔记(2)
  • 插入数据
    MySQL学习笔记(2)
  • 输出表中所有大国家的名称、人口和面积
    如果一个国家的面积超过300万平方公里，或者(人口超过2500万并且gdp超过2000万)，那么这个国家就是大国家。
    MySQL学习笔记(2)

参考：
Mysql的case-when-end语句
导入示例数据库
SQL编程格式的优化建议
SQL Style Guide
mysql 中的CASE-WHEN-THEN-END
SQL必知必会（第4版）
易佰教程

相关推荐