MySQLselect字句和常规比较运算符

1.select * from PLAYERS where HOUSENO <=60; ##<=就是比较运算符，该命令行的意思查询PLATERS表中HOUSENO列的值小于等于60 的数据

2.通过使用帮助查看MySQL里有哪些比较运算符
mysql> help contents
mysql> help Functions
mysql> help Comparison operators
通过使用帮助可以查看有以下运算符，也可以再通过help查看每个运算符的释义，例如help !=; ##可以查看到！=的释义

3.通过使用帮助查看到有以下运算符
!=
<
<=
<=> #mysql特有的运算符，

=
上述运算符属于比较常规的，常用于数字的比较！

BETWEEN AND ##一般是时间和数字的比较
COALESCE
GREATEST
IN
INTERVAL
IS
IS NOT
IS NOT NULL
IS NULL
ISNULL
LEAST
NOT BETWEEN
NOT IN

1）关于<=>和=两种运算符的区别
mysql> create table t2(id int,name varchar(10)); #创建t2表，在t2表里创建id和name列
Query OK, 0 rows affected (0.04 sec)

mysql> insert into t2 values(1,‘a’); #在t2表的id列插入1，name列插入a
Query OK, 1 row affected (0.12 sec)

mysql> insert into t2(name) values(‘b’); #只在name列上插入b，id列为空
Query OK, 1 row affected (0.26 sec)

mysql> select * from t2 where id=1; #查询t2表里，id列等于1的数据（使用=运算符）
±-----±-----+
| id | name |
±-----±-----+
| 1 | a |
±-----±-----+
1 row in set (0.01 sec)

mysql> select * from t2 where id=NULL; #查询id列等于空值的数据，查询结果为0
Empty set (0.00 sec)

mysql> select * from t2 where id<=>NULL; #使用<=>运算符查询id列为空的值，可以查询到
±-----±-----+
| id | name |
±-----±-----+
| NULL | b |
±-----±-----+
1 row in set (0.00 sec)
综上：使用<=>运算符更为全面，可以空值进行比较

补充：select * from t1 where WON=2 and LOST=3 ;
select * from t1 where (WON,LOST)=(2,3);
这两个命令都是查找t1表中won列等于2并且lost列等于3的数据

2）BETWEEN…AND…操作符
释义：between 1 and 5 大于等于1小于等于5
省略号的内容一般是日期和数字
select * from t1 where id between 1 and 5; ##在t1表查找介于1到5的id列
select * from t1 where date between ‘2019-03-01’and ‘2019-03-05’; ##在t1表中查看2019年3月1日到2019年3月5日的data列

注：between和and后是数字不用加单引号，如果是字符串或者日期的话，就需要加单引号

3）IN操作符
测试一个列值是否在一个值列表中。值列表中可以是字面量，列或标量子查询

select * from PLAYERS where year(BIRTH_DATE) in (1948,1962) ## year(BIRTH_DATE)表示取出生日期的年，语句表示在PLAYERS表中查询出生日期列的年是1948或1932的数据该语句和select * from PLAYERS where (year(BIRTH_DATE) = 1948) or (year(BIRTH_DATE) = 1962);查看的结果是一样的4）like操作符
语法：like用来匹配字符串
实现模式匹配查询或者模糊查询；测试一个列值是否匹配给出的模式
在匹配模式字符串中，可以有两个具有特殊含义的通配字符：
%：表示0个或者任意多个字符
：只表示一个任意字符
例如：select * from PLAYERS where NAME like ‘B%’; ##表示查询PLAYERS表中的name列以B开头的行select * from PLAYERS where NAME like ‘B___’; ##（有四个下划线）表示查询PLAYERS表中的name列B后边有四个字符的行

select * from PLAYERS where NAME like ‘%l%’; ##表示查询PLAYERS表中的name列含有l的行

select * from PLAYERS where NAME like ‘_l%’; ##表示查询PLAYERS表中的name列含有l并且在l前有两个字符的行
注意：1) 因为字符串的比较，所以要使用’’
2)如果要匹配‘%’或者‘’，需要使用 ESCAPE '转义字符’来指定转义字符
例如：select * from t3 where name like '%/%’ ESCAPE ‘/’; ##指定/为转义字符，也可以指定其他任意字符为转义字符，通常使用/5）REGEXP操作符
mysql特有的操作符。regexp是正则表达式的缩写。他和like功能相似，主要用来比较字符串，但比like功能更加强大
例如：select * from PLAYERS where BIRTH_DATE REGEXP ‘…[0,2,4,6,8]-.[0,2,4,6,8]-.[0,2,4,6,8]’;
##查找PLAYERS表中的BIRTH_DATE列，条件是任意四个字符 加 偶数 加 - 加 任意一个字符 加 偶数 加 - 加 任意一个字符 加 偶数。也就是年月日的最后一个字符是偶数！注：1) like操作符中的，%在REGEXP操作符分别用.和*表示，此外在REGEXP操作符[0-9]表示任意一个数字；’^{b’表示以b开头；’fy$’表示以fy结尾；’w’表示含有w字符；’^…$’或者’}.{5}$’表示有5个字符；具体更多参数参照官方文档
2) REGEXP操作符不区分大小写，添加BINARY参数会区分大小写逻辑操作符
逻辑操作符有4个，分别是and，or，not，xor，用于在where条件中把多个条件组合起来
1.and操作符示例：两个条件要同时满足
select * from PLAYERS where (birth_date > ‘1970-12-31’) and (sex = ‘M’); ##表示在PLAYERS表查
询时，同时要满足birth_date列大于1970-12-31和sex列等于M这两个条件注： （）and（）先执行（）里面的

2.or操作符示例：两个条件满足其中一个就行
select * from PLAYERS where (birth_date > ‘1970-12-31’) or (sex = ‘M’); ##表示在PLAYERS表查询时，满足birth_date列大于1970-12-31或者sex列等于M

3.not操作符示例：取反
select * from PLAYERS where NOT (birth_date = ‘1970-12-31’); ##表示在PLAYERS表查询birth_date列不等于1970-12-31的行

not可以和in搭配4.使用小括号改变运算的顺序
如果在where中同时用到了and和or运算符，先算and后算or，因为and的优先级高，同时NOT的优先级比and高。但是我们也可以使用小括号来改变运算的顺序
例如：优先级NOT>AND>OR5.XOR操作符：异或，和OR相似，区别是XOR去掉了两种条件都满足的情况ORDER BY
作用：排序
如果SELECT语句中没有ORDER BY字句，那么结果集中行的顺序是不可预料的
语法：SELECT expr FROM table [WHERE where_condition]
[GROUP BY {col_name | expr | position}[ASC | DESC], … [WITH ROLLUP]]
其中：ASC：执行升序排序。默认值
DESC：执行降序排序
ORDER BY字句一般在SELECT语句的最后面
示例：
1.select * from PLAYERS ORDER BY BIRTH_DATE； #在 BIRTH_DATE列中按照升序（从小到大）排序
2.select * from PLAYERS ORDER BY BIRTH_DATE DESC；#在 BIRTH_DATE列中按照降序（从大到小）排序
3.select * from PLAYERS ORDER BY t1 asc,t2 desc; #对t1列按照升序，t2列按照降序
补充：ORDER BY也可以对两个列进行排序，第一个列相等的再按照第二个列排序；null值为最小

字符串函数
1.LOWER函数
将字符串参数值转换为全小写字母后返回
2.UPPER函数
将字符串参数值转换为全大写字母后返回
举例：LOWER和UPPER函数
select lower(name),upper(name) from PLAYERS; #将 PLAYERS表的name列分别转换为大小写显示 3.使用concat函数将两个或者多个列联合起来显示
示例：select concat(TOWN,’ ‘ STREET) from PLAYERS; #将TOWN和STREET联合显示4.使用concat_ws函数可以主动添加分隔符，作用和concat函数一样
示例：select concat_ws(‘ , ’ , TOWN,STREET) from PLAYERS;5.SUBSTR函数
从源字符串（str）中的指定位置（pos）开始取一个子串并返回。len指定子串的长度，如果省略则一直取到字符串的末尾。该函数是函数SUBSTRING()的同义词。len值为负表示从源字符串的尾部开始取起
示例： select PLAYERSNO，substr(NAME，3，2) from PLAYERS；##查询PLAYERS表中PLAYERSNO列和name列的第1字符开始取两个6.LENGTH函数
返回字符串的存储长度
示例：select PLAYERNO，name，length（name） from PLAYERS; ##查询PLAYERS表中的 PLAYERNO和name列，并将name列中字符串的长度显示出来select length（‘你好’）； ##可以查看到你好的字符长度补充：为什么你好占6个字节呢
因为我们在存储的时候，会将字符转换为编码来存储，不同的编码方式会导致存储的字节长度有差异，例如我们使用UTF8进行编码，你好的长度是6；我们使用GBK编码，你好的长度是4，所以我们通常使用gbk的编码方式来存储中文
7.CHAR_LENGTH函数
返回字符串的字符个数
示例：select char_length(‘你好’); ##查看你好的字符个数8.INSTR函数
从源字符串（str）中返回子串（substr）第一次出现的位置
示例： select instr(‘hello,world’,‘LLO’); ##查找llo第一次出现的位置 在第三个字符开始出现
不区分大小写
select lpad(name,20,’’) from players; ##在name列的左边填充，指定一共是20个字符10.RPAD函数
在源字符串的右边填充给定的字符（padstr）到指定的长度（len），返回填充后的字符串
示例：select rpad(name,20,’’) from players; ##在name列的左边填充，指定一共是20个字符11.TRIM（裁剪）函数
从源字符串（str）中去掉两端，前缀或后缀字符remstr并返回。如果不指定remstr，则去掉str两端的空格。不指定BOTH（包括开头和结尾），LEADING（开头），TRAILING（末尾），则默认为去掉字符串两端的空格
示例：select trim(’ hello '); ##显示时会去掉两端的空格

使用LEADING参数可以指定裁剪开头的字符
示例：select trim(leading ‘x’ from ‘xxxx123xxx’);使用TRAILING参数可以指定裁剪末尾的字符
select trim(trailing ‘x’ from ‘xxxx123xxx’);

使用BOTH参数可以指定裁剪开头和末尾的字符
select trim(both ‘x’ from ‘xxxx123xxx’);
help trim查看帮助

12.REPLACE函数
在源字符串查找所有的子串，找到后使用替代字符串替换它。返回替换后的字符串
示例：select replace(name,’e’,’E’) from players; ##将name列中的e替换为E13.LTRIM RTRIM 函数
去掉字符串的左边或右边的空格

14.REPEAT函数
将字符串重复指定次数后返回
示例：select REPEAT（name,3）from players; ##将name列重复3次显示15.REVERSE函数
将字符串反转后返回
示例：select name，reverse（name）from players; 分别显示name列和反转后的name列16.char函数
他将每个参数n解释为整数（字符的编码），并返回每个整数对应的字符所构成的字符串。null值被忽略
示例： select char(77,121,83,81,76); #查看编码相对应的字符注：英文的编码大致相同
select char(77,121,83,81,76 using utf8); 或者select char(77,121,83,81,76 using gbk);或者select char(77,121,83,81,76 using ascii);对应的字符都是mysql

17.format函数
后边加数字，指定小数点的位数
示例：select format（WON，2）from players; #在won列上指定两位小数点18.space函数
返回由N个空格构成的字符串
示例: select space(10); ##列出10个空格19.left函数
返回最左边的指定长度的子串
示例：select left(name,3) from players; #列出name列上左边的3个子串20.right函数
返回最右边的指定长度的子串
示例：：select right(name,3) from players; #列出name列上最右边的3个子串21.strcmp函数
如果两个字符串是一样的则返回0；如果第一个小于第二个则返回-1；否则返回1
示例：select strcmp(‘1’,‘2’); 22.round函数
将数字四舍五入到指定的小数位数，如果不指定小数位数，默认为0。如果指定的小数位数是负数，表示从小数点的左边进行四舍五入
示例：select round(11.6); #显示12select round(11.655545，3); ##保留3位小数

23.truncate函数
将数字截断到指定的小数位数（不四舍五入）。小数位数为0表示不要小数。如果小数位数是负数，表示从小数点的左边进行截断
示例：select truncate(11.655545,3); ##表示保留3位小数24.mod函数
返回数字n除以m后的余数
示例：select mod(103,3); #求103除以3的余数注意：余数可以有小数。除数为0不会报错

25.ceil函数
返回不小于指定数的最小整数
示例：select ceil(103.123); ##返回不小于103.123的最小整数26.floor函数
返回不大于指定数的最小整数
示例：select floor(103.123); ##返回不大于103.123的最小整数27.rand()函数
返回一个随机数
示例：select rand()*100000; ##将产生的随机数乘以100000时间日期函数
1.NOW函数和SYSDATE函数
返回当前的日期和时间，两个函数的作用和用法大致相同
示例：select now(); select sysdate();select now(5); ##把秒后边的也显示出来 最多可以显示6位

注：时区不同，时间也不同

2.CURDATE()函数
返回服务器的当前日期
示例:select curdate();3.CURTIME()函数
返回服务器当前的时间
示例：select curtime();4.DATE_ADD（加）函数 DATE_SUB（减）函数
为给定的日期date加上或减去一个时间间隔值
示例：select now(),date_add(now(),interval 5 day); ##分别展示现在的时间和五天后的时间
interval表示间隔

select now(),date_sub(now(),interval 100 minute); ##分别展示现在时间和100分钟前的时间

5.DATEDIFF函数
返回两个日期相减，得出相差的天数，只有日期部分参与计算
示例： select datediff(‘2015-12-16’,‘2015-12-11’);6.TIMEDIFF函数
返回两个日期相减，得出相差的时间数，两个参数类型必须相同
示例：select timediff(‘2015-12-11 12:00’,‘2015-12-11 11:55’);7.选取日期时间的各个部分：日期，时间，年，季度，月，日，小时，分钟，秒，微秒示例：mysql> select now(),week(now()); 查看现在的时间和当前周是第几周

8.EXTRACT函数
从日期中抽取某个单独的部分或组合。可以实现上面各个函数的功能示例：select now(),extract(quarter from now()); #查看当前时间和当前是第几季度

9.dayofweek，dayofmonth，dayofyear函数
分别返回日期在一周，一月，一年中是第几天
示例：select dayofweek(now()); ##查看当前是一周的第几天，对于美国周六是第一天补充：我们可以使用show variables like ‘lc%’;查看到lc_time_names用的是 en_US。所以当前周六是第一天
10.dayname()，monthname()函数
返回星期和月份名称。名称是中文还是英文是由系统变量lc_time_names控制，默认值是 en_US
示例：select dayname(now()); ##显示星期名称