mysql

关键字

DISTINCT :只返回不同的值 必须放在列名前面 不能部分使用 若后面有多个要查询的属性 则单个属性可能会重复 但是总体不会重复
例如：SELECT DISTINCT id FROM products

LIMIT : 限制查询多少数据
例如：select type,status from room LIMIT 5; 表示查询五条
select type,status from room LIMIT 5，4;表示查询第五条开始的后4条数据（6-9）
select type,status from room LIMIT 1，1;查询的为第二条数据

完全限定名
例如：select room.type,status from room ; 与上述查询结果一样 但是在某些地方需要使用完全限制名

ORDER BY 对查询的结果根据某一属性进行排序（字符串类型以字母顺序）
select room.type,status from room ORDER BY status;查询结果对status排序
select room.type,status from room ORDER BY type,status;查询结果在对type排序的基础上再对status排序 及对type相同的数据进行再排序

DESC
select room.type,status from room ORDER BY type DESC,status;
查询结果对type逆序排序 再对status正序排序 （只能作用于前一属性）
ASC 与DESC相反 表示正序排列 但是实际中没什么用 因为默认为上到下升序
顺序： ORDER BY在FROM后 LIMIT在最后
select room.type,status from room ORDER BY status LIMIT 5，4 ；

RTrim 删除数据右侧多余的空格
select RTrim(room.type),status from room ORDER BY type,status;

where 设置选定条件 =后面若是数值不需要引号 为字符则需要单引号
例如：select room.type,status from room where room_id = 10086 ;
查询房间编号为10086的数据的信息
列举几个特殊的操作符 ：
<> 不等于
!= 不等于
BETWEEN 2 AND 3 在指定的两个值中间（2-3之间的值-）
IS NULL 查询为空的数据
当需要两个或多个查询条件时 使用WHERE 条件a AND 条件b AND …；每多一个条件 多一个AND
当需要几个条件中只用满足某一个时 使用WHERE 条件a OR 条件b…；
也可以同时使用AND 和 OR 注意：同时使用两者时 AND的优先级更高 为了避免判断顺序错误 需要加上括号

IN 与OR的作用基本相同
select room.type,status from room where room_id IN (10086,10243) ;
查询结果为room_id = 10086 与10243的数据 与使用or查询结果一致
IN的优点 ： 1.在使用长的合法选项清单时 IN的操作符语法更清楚更直观
2.计算的次序更容易管理（因为操作符更少）
3.IN操作符一般比OR操作符清单执行更快
4.IN的最大优点是可以包含其他SELECT语句 使得能够更动态地建立WHERE子句

NOT 否定他之后所跟的任何条件
select room.type,status from room where room_id NOT IN (10086,10243) ;查询结果为room_id != 10086 与10243的所有数据

LIKE 使用通配符时需要用到
通配符 % 对于字符串模糊查询
select room.type,status from room where type LIKE ‘大%’ ;
表示查询‘大’开头的的数据 不管后面还有多少字符
%也可用于任何位置 如 ‘%12%’ 表示含有12的字符
通配符 _ 对于字符串模糊查询 与%基本相似 但是_只代表一个字符
select room.type,status from room where type LIKE ‘大_’ ;
表示 查询type为 大x (x为任一字符) 不能多也不能少2.

正则表达式

REGEXP 效果与like相似 但是其作用不同LIKE匹配整个串 REGEXP匹配子串
SELECT user_name FROM users WHERE user_name REGEXP ‘78’;
该句查询结果与like一样
SELECT user_name FROM users WHERE user_name REGEXP ‘.8’;
表示查询结果有8的数据 ‘.’ 表示任意匹配一个字符

OR匹配
SELECT user_name FROM users WHERE user_name REGEXP ‘admin|7’; 同时查询有admin与7的数据 可同时使用多个‘|’

匹配几个字符之一
SELECT user_name FROM users WHERE user_name REGEXP ‘[689]8’;[689]为一数组 只要匹配里面任一一个 再与后面的8相连进行查询
逻辑上相当于SELECT user_name FROM users WHERE user_name 或REGEXP ‘6|8|9 8’; 但是实际上两者查询结果不同 '6|8|9 8’查询结果为6或8或9 8

匹配范围
SELECT user_name FROM users WHERE user_name REGEXP ‘[5-9]8’;查询结果为5-9中任一数据 与8相连查询的结果

匹配特殊字符
SELECT user_name FROM users WHERE user_name REGEXP ‘\.’;
找到带有‘.’的数据 其他特殊字符前同样适用\ 匹配\时则用\
空白元字符\f 换页
\n 换行
\r 回车
\t 制表
\v 纵向制表

匹配字符类
[ :alnum: ]任意字母和数字(同[a-zA-Z0-9])
[:alpha:]任意字符(同[a-ZA-Z])
[ :blank:]空格和制表(同[\t])
[ :cntrl:]ASCII控制字符(ASCII 0到31和127)
[ :digit:]任意数字(同[0-9])
[ :graph: ]与[:print: ]相同，但不包括空格和:
[:lower:]任意小写字母(同[a-z])
[ :print:]任意可打印字符
[ :punct:]既不在[:alnum:]又不在[ :cntrl:]中的任意字符
[ :space: ]包括空格在内的任意空白字符(同[\f\n\r\t\v])
[ :upper:]任意大写字母(同[A-Z] )
[ :xdigit:]任意十六进制数字(同[a-fA-F0-9] )

重复元字符
元字符 ====== 说明
* ====== 0个或多个匹配
+ ====== 1个或多个匹配(等于{1,})
? ====== 0个或1个匹配(等于{0,1})
{n} ====== 指定数目的匹配
{n,} ====== 不少于指定数目的匹配
{n,m} ====== 匹配数目的范围(m不超过25)

定位元字符
^ 文本的开始
$ 文本的结尾
[[:<:]] 词的开始
[[:>:]] 词的结尾

创建计算字段

拼接字段Concat
SELECT Concat(user_name,’(’,user_telNum,’)’) FROM users;
与RTrim连用
select Concat(RTrim(user_name),’(’,user_telNum,’)’) FROM users;

使用别名AS
select Concat(RTrim(user_name),’(’,user_telNum,’)’) AS user_in FROM users;框上名称变化为 user_in 注意：还可以对表起别名 这样在WHERE子句等其他地方中能够更方便调用

算数计算 + - * \
SELECT user_name + user_id FROM users WHERE user_name REGEXP ‘[5-9]8’; 两者相加

函数

如上述RTrim()就是一函数 删除数据右侧多余的空格
不同DBMS实现的函数有所不同 这里只举例几个常用文本处理函数
Left() 返回串左边的字符
Length() 返回串的长度
Locate() 找出串的一个子串
Lower() 将串转换为小写
LTrim() 去掉串左边的空格
Right() 返回串右边的字符select RIGHT(user_name,1) From users;
Soundex() 返回串的Soundex值
SubString() 返回子串的字符
Upper()将串转换为大写

日期和时间处理函数
AddDate( ) 增加一个日期(天、周等)
AddTime( ) 增加一个时间(时、分等)
CurDate( ) 返回当前日期
CurTime( ) 返回当前时间
Date( ) 返回日期时间的日期部分
DateDiff() 计算两个日期之差
Date Add( ) 高度灵活的日期运算函数
Date_ Format() 返回一个格式化的日期或时间串
Day() 返回一个日期的天数部分
DayOfWeek( ) 对于一个日期，返回对应的星期几
Hour( ) 返回一个时间的小时部分
Minute() 返回一个时间的分钟部分
Month() 返回一个日期的月份部分
Now() 返回当前日期和时间
Second() 返回一个时间的秒部分
Time() 返回一个日期时间的时间部分
Year() 返回一个日期的年份部分

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

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

分组数据

GROUP BY
select user_name,Count(*) AS num From users GROUP BY user_name;
对同样名称的数据进行分组 并统计数据个数

过滤分组 注意 不可以用WHERE where过滤的是行不是分组 也没有分组的概念 需要用到HAVING子句
HAVING与WHERE非常相似 所有WHERE的操作符HAVING都支持
举例：
select user_id,Count() AS num From users GROUP BY user_id HAVING user_id >2 ; 若换成WHERE则报错 WHERE在分组前 HAVING在分组后
分组并排序： select user_id,Count(),user_telNum AS num From users GROUP BY user_id HAVING user_id >2 ORDER BY user_telNum;

子查询

及嵌套查询 可用于查询相关表的数据 一般于IN结合使用 = > <也可用
select user_name num From users WHERE user_id = (select manager_id num From managers WHERE manager_id = 1);
注意 如果两张表中有相同的列名 需要完全限定列名 如：表名.列名

联结表

简单创建联结
SELECT user_name ,score,subject FROM users, stu_score WHERE users.user_id = stu_score.id;使用完全限定名进行联结

INNER JOIN … ON 创建联结
SELECT user_name ,score,subject FROM users INNER JOIN stu_score ON users.user_id = stu_score.id; //与上述查询结果一致
注意 ：SQL可联结多个表 没有限制 但是联结的越多性能下降越多

自联结
SELECT u1.user_name ,u1.user_id FROM users AS u1,users AS u2 WHERE u1.user_id = u2.user_id;

组合查询

UNION多条SELECT中间使用 且必须包含相同的列名 表达式或聚集函数 （会自动去除相同的行 若不去除 使用UNION ALL）
select user_name num From users WHERE user_id = 1 UNION select user_name num From users WHERE user_id = 2;

全文本搜索
Match() Against() Match()中的值必须与FULLTEXT()定义中的相同

插入数据
INSERT INTO
INSERT INTO users(user_id,user_name,user_password,user_telNum ) VALUE(88,88,88,88888888888);

更新数据
update users set user_password=#{userPasswd} where user_name=#{userName}

删除数据
DELETE FROM users where user_name=#{userName}

创建和操作表

创建表： CREATE TABLE A(
列名 数据类型 是否可为空及其他设置
列名 数据类型 是否可为空及其他设置
列名 数据类型 是否可为空及其他设置

)ENGING=InnoDb;

使用视图

CREATE VIEW 来创建视图
SHOW CREATE VIEW viewname 查看创建的视图语句
DROP VIEW viewname 删除视图
CREATE OR REPLACE VIEW 更新视图 （如果更新的视图不存在则直接创建一个新的视图）
作用：隐藏复杂的SQL