在日常的工作中,无论是开发还是测试,都不可避免的要操作数据库,但是在实际的操作中,总是遇到这样那样的问题,今天,就基于mysql的数据库操作基础知识进行梳理,以备后续需要时快速查询和使用。
一、 查询语句(查)
Mysql的查询语句是通过select 语句进行查询的,并根据其需要对结果进行筛选、排序、分组、计算等。接下来,将对查询用到的基本规则进行说明和解释并逐一进行举例说明。
- 基本查询,通过select语句对数据库进行查询,
其语法规则为:Select 列名 from 表名;
例如:select * from employees; - 对查询结果进行过滤
(1) 通过where子句对查询结果过滤。通过where子句,仅展示满足where条件的数据。
例如:在emloyees表中查询所有jobtitles 为 sales Rep 的员工的详细信息。
SELECT * FROM employees WHERE jobTitle=“Sales Rep”;
再例如:在employees表中查询jobtitles 为 sales Rep 的员工的姓名
SELECT firstName,lastName FROM employees WHERE jobTitle=“Sales Rep” ;
(2) 通过and 和or 进行多条件过滤,and对多条件取交集,展示满足所有条件的数据,or对多条件取并集,展示满足任一条件的数据
例如:查询在employees表中jobtitle为sales rep 且officecode 为2 的员工的详细信息
SELECT * FROM employees WHERE jobTitle=“Sales Rep” AND officeCode =2;
再例如:查询在employees表中jobtitle为sales rep 或者officecode 为2 的员工的信息。
SELECT employeeNumber,lastName,firstName,officeCode, jobTitle FROM employees WHERE jobTitle=“Sales Rep” or officeCode=2;
(3) 通过in 和not in进行多条件过滤。in 和or等同,即满足条件之一即可。Not in 即不包含在其中的,除此之外的
例如:SELECT * FROM employees WHERE jobTitle=“Sales Rep” AND officeCode IN (2,3);
再例如:SELECT * FROM employees WHERE jobTitle=“Sales Rep” AND officeCode NOT IN (2,3);
(4) 通过通配符对查询结果进行过滤。通配符是模糊匹配。
% 匹配任何字符出现任意次数
_ 匹配单个字符出现任意次数
例如:SELECT * FROM employees WHERE jobTitle like “vp%”;
再例如:SELECT * FROM employees WHERE reportsTo LIKE “11__”;
(5) 通过正则表达式对查询结果进行过滤
正则表达式常用的匹配字符:
元字符 | 说明 |
---|---|
* | 匹配0个或者多个字符 |
+ | 匹配1个或者多个字符 |
? | 匹配0个或者1个字符 |
{n} | 匹配指定数目n |
{n,} | 匹配长度不少于n的字符 |
{n,m} | 长度n~m之间,m不超过255 |
定位符:
元字符 | 说明 |
---|---|
^ | 文本开始,从文本开始处匹配 |
$ | 文本结尾,从文本结尾处匹配 |
[[:<:]] | 词的开始,从词开始处匹配 |
[[:>:]] | 词的结尾,从词结尾处匹配 |
例如:SELECT * FROM employees WHERE extension REGEXP ‘x[0-9]{3}$’;
(6) 对数据进行排序,通过order by子句对过滤数据进行排序,默认为ASC 升序排列,也可设置为DESC降序排列,排序列也可以设置成多列,根据书写先后顺序,先对第一列进行排序,若第一列相同,则根据第二列进行排序,并以此类推。
例如:SELECT * FROM employees WHERE jobTitle=“Sales Rep” ORDER BY officeCode;
再例如:SELECT * FROM employees WHERE jobTitle=“Sales Rep” ORDER BY officeCode DESC;
3. 对查询结果进行分组
(1) 基本分组,通过group by进行分组。注意分组不能查询所有信息,分组查询只能根据分组的列查询与该列相关的信息。
例如:SELECT officeCode,COUNT() FROM employees GROUP BY officeCode;
再例如:SELECT officeCode,COUNT() FROM employees WHERE reportsTo REGEXP “1” GROUP BY officeCode ;
(2) 分组过滤,通过having进行分组过滤
例如:SELECT officeCode,COUNT(*) FROM employees WHERE reportsTo REGEXP “11” GROUP BY officeCode HAVING officeCode>1;
在分组查询中也可以使用where子句,排序子句等,个子句使用的顺序为:
SELECT 列名 FROM 表名 WHERE 条件1 GROUP BY 条件2 HAVING 条件3 ORDER BY 列名 ;
4. 在查询语句中使用函数
例如:SELECT concat(firstName,lastName),officeCode FROM employees WHERE jobTitle=“Sales Rep” ;
(1) 处理函数
(a) 文本处理函数
函数 | 说明 |
---|---|
Left() | 返回串左边的字符 |
Length() | 返回串的长度 |
Locate() | 找出串的一个子串 |
Lower() | 将串转换成小写 |
LTrim() | 去掉串左边的空格 |
Right() | 返回串右边的字符 |
RTrim() | 去掉串右边的空格 |
Soundex() | 返回串的soundex值 |
SubString() | 返回子串的字符 |
Upper() | 将串转换成大写 |
(b) 日期处理函数
函数 | 说明 |
---|---|
AddDate() | 增加一个日期(天,周等) |
AddTime() | 增加一个时间(时,分等) |
CurDate() | 返回当前日期 |
CurTime() | 返回当前时间 |
Date() | 返回日期时间的日期部分 |
DateDiff() | 计算两个日期之差 |
Date_Add() | 高度灵活的日期运算函数 |
Date_Format() | 返回一个格式化的日期或时间串 |
Day() | 返回一个日期的天数部分 |
DayOfWeek() | 对于一个日期,返回对应的星期几 |
Hour() | 返回一个时间的小时部分 |
Minute() | 返回一个时间的分钟部分 |
Month() | 返回一个日期的月份部分 |
Now() | 返回当前日期和时间 |
Second() | 返回一个时间的秒部分 |
Time() | 返回一个日期时间的时间部分 |
Year() | 返回一个日期的年份部分 |
(c) 数据处理函
函数 | 说明 |
---|---|
Abs() | 返回一个数的绝对值 |
Cos() | 返回一个角度的余弦 |
Exp() | 返回一个数的指数值 |
Mod() | 返回除操作的余数 |
Pi() | 返回圆周率 |
Rand() | 发挥一个随机数 |
Sin() | 返回一个角度的正弦 |
Sqrt() | 返回一个数的平方根 |
Tan() | 返回一个角度的正切 |
(2) 汇总函数
函数 | 说明 |
---|---|
AVG() | 返回某列的平均值 |
COUNT() | 返回某列的行数 |
MAX() | 返回某列的最大值 |
MIN() | 返回某列的最小值 |
SUM() | 返回某列值之和 |
- 多表查询
(1) 子查询
(2) 表链接
(3) 组合查询
二、 增加数据(增)
三、 更新数据(改)
四、 删除数据(删)
以上,即为经常说的对数据进行增删改查的基本操作,为应对更加复杂的数据操作,后续会对视图,存储过程,游标等的基本操作进行说明。