【MySQL必知必会】检索数据

普通检索数据

/* 检索单个列 */
SELECT nickname FROM guestbook;

/* 检索多个列 */
SELECT nickname,email FROM guestbook;

/* 检索所有列 */
SELECT *FROM guestbook;

/* DISTINCT 关键字，只返回不重复的值 */
SELECT DISTINCT nickname FROM guestbook;

/* LIMIT 限制不能够超过的行数 */
SELECT nickname FROM guestbook LIMIT 5;

/* LIMIT 5,3   指定返回从 行5 开始的 3行数据 
	 注意：这里的行5实际是第6行，因为第一行为行0
*/
SELECT nickname FROM guestbook LIMIT 5 , 3 ;

/* 此处等同于 LIMIT 5,3  ，只是便于查看  */
SELECT nickname FROM guestbook LIMIT 3 OFFSET 5 ;

/* 使用完全限定的表名 */
SELECT guestbook.nickname FROM guestbook;

排序检索数据

可以使用ORDER BY子句对一个或者多个列进行排序

/* 按照 nickname 字母顺序进行排序 */
SELECT nickname FROM guestbook ORDER BY nickname;

/* 按照多个列排序，仅在nickname相同的时候，以createtime排序 */
SELECT nickname,email,createtime FROM guestbook ORDER BY nickname,createtime;

/*  DESC：降序排序    ASC: 升序排序，默认为升序 */
SELECT nickname , createtime FROM guestbook  ORDER BY createtime DESC;

/* createtime降序排序， nickname仍然升序排序 */
SELECT nickname , createtime FROM guestbook  ORDER BY createtime DESC ,nickname ;

过滤数据

使用where单一子句进行查询

/* 指定搜索条件过滤 */
SELECT nickname,email FROM guestbook WHERE nickname='Tom';

SELECT nickname,email FROM guestbook WHERE nickname='Tom' ORDER BY email;

/* 指定范围 */
SELECT nickname,price FROM guestbook WHERE price BETWEEN 10 AND 50 ;

/* 检索具有NULL值的列 ， 返回没有价格的数据  */
SELECT nickname,email FROM guestbook WHERE price is NULL;

使用where子句进行更复杂的查询

/* 多个where子句 */
SELECT id,nickname FROM guestbook WHERE price > 10 AND nickname ='Tom' ;

/*  OR 匹配任一条件即可 */
SELECT id,nickname,price FROM guestbook WHERE price > 50 OR price < 10 ;

SELECT id,nickname,price FROM guestbook 
WHERE  ( id = 5 OR id = 10 )  AND price > 10;

/*  IN操作符指定条件范围，每个条件都可以匹配 */
SELECT id,nickname,price FROM guestbook 
WHERE id IN ( 3,4,5 ) ORDER BY price DESC;

/*  WHERE子句中的NOT操作符只有一个功能，就是否定它后面的所有条件 */
SELECT id,nickname,price FROM guestbook  WHERE id NOT IN( 2,3,4);

注意：IN操作符在功能上和OR相同，但是IN语法更清晰，执行速度更快，
此外，IN还可以包含其他的SELECT查询语句。

通配符过滤

通配符 用来匹配值的一部分特殊字符，需要使用LIKE操作符
百分号(%)通配符表示任何字符出现的任意次数

/* 查找名字已 w 开头的数据  */
SELECT id,name,email FROM users WHERE name LIKE 'w%';

/* 查找名字里带有ha的数据 */
SELECT id,name,email FROM users WHERE name LIKE '%ha%';

下划线(_)通配符 用来只匹配单个字符而不是多个字符，不能多也不能少

/* 查找名字 后缀是 “iao”的数据 */
SELECT id,name,email FROM users WHERE name LIKE '_iao';

注意：通配符搜索处理一般比其他搜索时间要长，不要过度使用。

正则表达式搜索

/* 查找名字里带有 Mr 的数据 */
SELECT name ,email FROM users WHERE name REGEXP 'Mr';

/* 相当于使用or ,只要匹配其中之一就行  */
SELECT name ,email FROM users WHERE name REGEXP 'Mr|xi';

/* 匹配[]中任意字符即可 */
SELECT name ,email FROM users WHERE name REGEXP '[xy]';

/* 匹配范围： 1 - 9之间的任一数字  */
SELECT name ,email FROM users WHERE email REGEXP '[1-9]';

/* 匹配特殊字符 ，\\ 转义符  */
SELECT name ,email FROM users WHERE name REGEXP '\\.';

计算字段

/* CONCAT拼接串 ,返回如：  xiao ( xiao@123.com )    */
SELECT CONCAT(name,' ( ',email , ' ) ') FROM users;

/*  TRIM 去掉两边的空格  */
SELECT CONCAT( TRIM( name ) ,' ( ',email , ' ) ') FROM users;

/* as 起别名 */
SELECT CONCAT( TRIM(name) ,' ( ',TRIM(email) , ' ) ') as user_title FROM users;

数据处理函数

1. 文本处理函数

   /*  UPPER将文本大写 */
   SELECT UPPER(name) as  name_upcase FROM users;

常用文本处理函数还有很多：

函数	说明
Left()	返回串左边的字符
Length()	返回串的长度
Locate()	找出串的一个子串
Lower()	将串转换为小写
LTrim()	去掉串左边的空格
Right()	返回串右边的字符
RTrim()	去掉串右边的空格
Soundex()	返回串的SOUNDEX值
SubString()	返回子串的字符
Upper()	将串转换为大写

2. 时间处理函数

   /* Date 是日期(1996-08-09)   Time 是时间(10:31:59) */
   SELECT name ,Date(created_at) , Time(created_at) FROM users;
   
   /* 查询日期范围  */
   SELECT name,created_at FROM users 
   WHERE DATE(created_at) BETWEEN '2001-02-08' AND '2018-02-08';
   
   /* 根据年份和月份查找 */
   SELECT name,created_at FROM users 
   WHERE YEAR(created_at) = 2018 AND MONTH(created_at) = 6;

常用日期和时间处理函数:

函数	说明
AddDate()	增加一个日期（天、周）
AddTime()	增加一个时间（时，分）
CurDate()	返回当前日期
CurTime()	返回当前时间
Date()	返回日期时间的日期部分
DateDiff()	计算两个日期之差
Date_Add()	高度灵活的日期计算函数
Date_Format	返回一个格式化的日期或字符串
Day()	返回一个日期的天数部分
DayOfWeek()	对于一个日期返回对应的星期几
Hour()	返回一个时间的小时部分
Minute()	返回一个时间的分钟部分
Month()	返回一个时间的月份部分
Now()	返回当前日期
Second()	返回一个时间的秒部分
Time()	返回一个日期时间的时间部分
Year()	返回一个日期的年份部分

3. 数值处理函数

函数	说明
Abs()	返回一个数的绝对值
Cos()	返回一个角度的余弦
Exp()	返回一个数的指数值
Mod()	返回除操作的余数
Pi()	返回圆周率
Rand()	返回一个随机数
Sin()	返回一个角度的正弦
Sqrt()	返回一个数的平方根
Tan()	返回一个数的正切

汇总数据

1. SQL聚集函数

函数	说明
Max()	求最大
Min()	求最小
Sum()	求总和
Avg()	求平均
Count()	计算总行数

/*  获取产品的平均价格  */
SELECT AVG( price) AS avg_price FROM guestbook ;

/* 对所有行计数 */
SELECT COUNT(*) AS num FROM guestbook;

2. 聚集不同值
   在表中，可能会包含重复值，可以使用DISTINCT 用于返回唯一不同的值。

   /* 获取唯一的价格 */
   SELECT  DISTINCT price  FROM guestbook ；
   
   SELECT AVG(DISTINCT price) AS avg_price FROM guestbook ；

分组数据

1. 创建分组

   /* 根据nickname分组计算个数  */
   SELECT nickname ,COUNT(*) FROM guestbook GROUP BY nickname  ;

GROUP BY子句是SELECT语句的可选子句,GROUP BY子句必须出现在FROM和WHERE子句之后

2. 过滤分组
   where过滤指定的行而不是分组，可以使用HAVING ， HAVING 类似于where,只是关键字不同。
   where在数据分组前过滤，HAVING在数据分组后进行过滤。

   /* 根据nickname分组计算个数,并过滤出>= 2的数据  */
   SELECT nickname ,COUNT(*) FROM guestbook  WHERE id <100 GROUP BY nickname HAVING COUNT(*) >= 2 ;

联结

/* 等值联结：statuses表中的外键user_id 是users的主键  */
SELECT users.id,name,email,content FROM users,statuses 
WHERE users.id = statuses.user_id  ORDER BY users.id;

/*  使用INNER JOIN   ON      */
SELECT users.id,name,email,content FROM users 
INNER JOIN statuses ON users.id = statuses.user_id ORDER BY users.id;

组合查询(union)

/*  使用UNION把多次查询的结果组合成一个结果集 */
SELECT id,name FROM users WHERE id < 5
UNION 
SELECT id,name FROM users WHERE id > 6 && id< 10 ;

注意 UNION 会自动过滤重复的列表，如果不需要过滤请使用 UNION ALL
组合查询 和 多个where子句查询完成的工作相同。

全文本搜索

创建表时启用全文本搜索，使用fulltext对note_text进行索引，在增删改查时，索引自动更新。
不要在导入数据时使用fulltext。

CREATE TABLE productnotes
(
   note_id             int         not null  auto_increment,
   prod_id             char(10)    not null,
   note_date           datetime    not null,
   note_text           text        null,
   primary key(note_id),
   fulltext(note_text)
)  engine = MyISAM;

暂时不太清楚全文本搜索，待以后查找

赏

谢谢你请我吃糖果

支付宝

微信