1.给出一个表的细节
show columns from tbl_mendian_order_master;
等价于:describe tbl_mendian_order_master;

2.
show status; //用于显示广泛的服务器状态信息
show grants; //用来显示授权用户的安全权限
show errors; //用来显示服务器错误信息
show warnings; //用来显示服务器警告信息
3.不能部分使用distinct关键字
如:select distinct groupID,shopID from table; 除非指定的两个列都不同,否则讲会展示所有满足条件的行(失去了过滤某一列重复数据的功能)。
4.limit语句
select * from table limit 3;
等价于:select * from table limit 0,3; (从第0个开始,展示前3个)
等价于:select * from table limit 3 offset 0;(从第0行开始,取3个,MySQL5才支持)
5.ORDER BY默认是升序排列ASC
在字典排序总,A被视为和a相同,如果需要改变这种规则,需要请求数据库管理员的帮助。
6.MySQL在匹配字符串时,默认不区分大小写
where name = ‘Tree’ 和 where name = ‘tree’ 搜索相同
7.关于null
使用 = ‘null’ , <> ‘null’ , is not null 可以查出来不是null的数据
使用 is null 可以查询到匹配null的数据,但是使用 =’null’ 和 = null 查询不到内容
8.AND优先级高于OR
9.通配符
%表示任何字符出现任意次数(0->n次),%几乎可以匹配任何东西,但null例外
_ 下划线值匹配单个字符而不是多个字符
10.正则表达式
select * from table where name REGEXP ‘.000’; //这会筛选出类似1000 或者2000这样的数据
. 是正则表达式语言中的一个特殊的字符,它表示匹配任意一个字符。
select * from table where name LIKE ‘1000’; //不会返回值是1000的数据
select * from table where name REGEXP ‘1000’; //会返回值是1000的数据
- LIKE匹配整个列,如果被匹配的文本在列值中出现,LIKE将不会找到它,相应的行也不会被返回,除非使用了通配符。
- REGEXP在列值内进行匹配,如果被匹配的文本在列值中出现,则会被返回。
MySQL中的正则表达式匹配(从3.23.4版本后)不区分大小写,如果要区分大小写,可以使用BINARY关键字。
如:select * from table where name REGEXP BINARY ‘Henry’;
a.进行OR匹配
select * from country where Continent REGEXP ‘Asia|Africa|Europe’;
b.特殊字符匹配
如果要匹配特殊字符,必须用\\为前导。如:\\- \\. \\| \\[ \\] \\\ (即_ . | [ ] \)
select * from table where name REXEXP ‘\\.’; //检索出name包含.的
- 多数正则表达式使用单个反斜杠转义特殊字符,但MySQL要求两个反斜杠(MySQL自己解释一个,正则表达式解释另外一个)。
11.拼接字段
多数DBMS使用+或者||来实现拼接,MySQL则使用Concat()函数来实现。
SELECT CONCAT(RTRIM(shopID),'(‘,Trim(orderKey),’)’,LTRIM(shiftName)) AS NewName FROM tbl_mendian_order_master ; //将对应几项作为别名NewName 输出
RTrim()可以去掉字符串右边的空格,LTrim()可以去掉左边的空格,Trim()可以去掉字符串两边的空格。
12.文本处理函数
1.RTrim()
作用:去掉串尾的空格来整理数据。
2.Upper()
作用:将文本转换为大写并返回。
3. Lower()
作用:将文本转换为小写并返回。
4.Length(str)
作用:返回串的长度。
5.Locate(substr,str)
参数:substr待查找的子串,str待查找的串,在str中查找substr第一次出现的位置,如果不存在则返回0。
另一种情况:Locate(substr,str,pos)。作用:返回子串 substr 在字符串 str 中的第 pos 位置后第一次出现的位置。如果 substr 不在 str 中返回 0。
6.Position(substr IN str)
作用:返回substr在str中第一次出现的位置。
7.SubString(str,pos)
作用:返回从第pos位置出现的子串的字符。
另一种情况:substring(str, pos, len)。作用:返回从pos位置开始长度为len的子串的字符。
8.Soundex()
返回串SOUNDEX值,SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。
select name from table where Soundex(name) = Soundex(‘Y Lie’);
可以返回Y Lee这样发音类似的记录。
13.数值处理函数
ABS(x) 返回x的绝对值
BIN(x) 返回x的二进制(OCT返回八进制,HEX返回十六进制)
EXP(x) 返回值e(自然对数的底)的x次方
GREATEST(x1,x2,…,xn) 返回集合中最大的值
LEAST(x1,x2,…,xn) 返回集合中最小的值
LN(x) 返回x的自然对数
LOG(x,y) 返回x的以y为底的对数
MOD(x,y) 返回x/y的模(余数)
PI() 返回pi的值(圆周率)
RAND() 返回0到1内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。
FLOOR(x) 返回小于x的最大整数值,(去掉小数取整)
CEILING(x) 返回大于x的最小整数值,(进一取整)
ROUND(x,y) 返回参数x的四舍五入的有y位小数的值,(四舍五入)
TRUNCATE(x,y) 返回数字x截短为y位小数的结果
SIGN(x) 返回代表数字x的符号的值(正数返回1,负数返回-1,0返回0)
SQRT(x) 返回一个数的平方根
14.日期和时间处理函数
DATE_ADD(date,INTERVAL int keyword) 返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如:SELECTDATE_ADD(CURRENT_DATE,INTERVAL 6 MONTH);
DATE_SUB(date,INTERVAL int keyword) 返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如:SELECTDATE_SUB(CURRENT_DATE,INTERVAL 6 MONTH);
DATE_FORMAT(date,fmt) 依照指定的fmt格式格式化日期date值
FROM_UNIXTIME(ts,fmt) 根据指定的fmt格式,格式化UNIX时间戳ts
MONTHNAME(date) 返回date的月份名(英语月份,如October)
DAYNAME(date) 返回date的星期名(英语星期几,如Saturday)
NOW() 返回当前的日期和时间 如:2016-10-08 18:57:39
CURDATE()或CURRENT_DATE() 返回当前的日期
CURTIME()或CURRENT_TIME() 返回当前的时间
QUARTER(date) 返回date在一年中的季度(1~4)
WEEK(date) 返回日期date为一年中第几周(0~53)
DAYOFYEAR(date) 返回date是一年的第几天(1~366)
DAYOFMONTH(date) 返回date是一个月的第几天(1~31)
DAYOFWEEK(date) 返回date所代表的一星期中的第几天(1~7)
YEAR(date) 返回日期date的年份(1000~9999)
MONTH(date) 返回date的月份值(1~12)
DAY(date) 返回date的天数部分
HOUR(time) 返回time的小时值(0~23)
MINUTE(time) 返回time的分钟值(0~59)
SECOND(time) 返回time的秒值(0-59)
DATE(datetime) 返回datetime的日期值
TIME(datetime) 返回datetime的时间值
返回当前时间:
SELECT DATE_FORMAT(NOW(),’%Y%m%d%H%i%s’);

15.聚集函数
聚集函数运行在行组上,计算和返回单个值得函数。
AVG() //返回某列的平均值
AVG()忽略列值为null的行
COUNT() //返回某列的行数
count(*) 返回所有行的数目,不管是否为NULL
count(name) 返回对应列中有值得行进行计数,忽略NULL
MAX() //返回某列的最大值
MAX()忽略列值为null的行
MIN() //返回某列的最小值
MIN()忽略列值为null的行
SUM() //返回某列值之和
SUM()忽略列值为null的行
16.分组
select name from table group by name WITH ROLLUP;
使用WITH ROLLUP,最后会默认多一条分组的汇总信息(每一列数据的汇总)。
过滤分组(使用having子句):
select name from table group by name HAVING COUNT(*) >=2;
17.内部联结
select name from a,b where a.id = b.id;
等价于:select name from a INNER JOIN b ON a.id = b.id;
18.自联结
//筛选出name是A的对象还有哪些school
select school from table where id = (select id from table where name = ‘A’);
等价于:select t1.school FROM table t1, table t2 WHERE t1.id = t2.id AND t2.name = ‘A’;
19.四种连接
内连接:a和b表相关列的交集(两个表都有的)
select a.*,b.* from a inner join b on a.id=b.parent_id
左外连接:a全量,b对应a相应字段有的部分
select a.*,b.* from a left join b on a.id=b.parent_id
右外连接:b全量,a对应b相应字段有的部分
全连接:a和b的全量,对应部分没有的为null
select a.*,b.* from a full join b on a.id=b.parent_id
20.组合查询
SELECT * FROM a WHERE num >=5 UNION SELECT * FROM a WHERE reportDate = 20180618; //返回满足条件的记录(如果两次查询都有对应记录只显示一条)
SELECT * FROM a WHERE num >=5 UNION ALL SELECT * FROM a WHERE reportDate = 20180618; //返回满足条件的记录(可能会有重复的记录)
可以在最后用ORDER BY子句进行分组,放在最后,对返回所有结果进行排序(并不是只对最后那个select语句排序)。
21.全文本搜索
在创建表时,启用全文本搜索。
然后搜索时用Match()和Against()执行全文本搜索,Match()用来指定被搜索的列,Against()用来指定要使用的搜索表达式。
CREATE DATABASE test;
USE test;
CREATE TABLE fullTextTest
(
id int NOT NULL AUTO_INCREMENT,
name char(10) NOT NULL,
testText text NULL,
PRIMARY KEY(id),
FULLTEXT(testText)
)ENGINE = MyISAM; //仅MyISAM引擎支持全文本搜索
SELECT testText FROM fullTextTest WHERE Match(testText) Against(‘rabbit’); //返回testText字段包含rabbit字符串的记录。
全文本搜索的一个重要部分就是对结果排序,具有较高等级的行会先返回(越早出现对应字符串的行可能会越前返回,而like语句则无法保证)。
使用Match和Against会简历一个计算列,该列包含全文本搜索计算出的等级值,如果不包含对应字符串,则等级值为0,否则,文本中词靠前的记录的等级值会比词靠后的等级值高。
因为全文本搜索有索引,所以数组还相当快。
22.insert操作
insert into student(id,age,name) values (null,18,’tom’),(null,23,’hy’);
INSERT单条语句有多组值,每组用圆括号括起来。
- 单条INSERT语句处理多个插入比使用多条语句快。
23.INSERT SELECT
insert into student(id,age,name) SELECT id,age,firstName FROM foreignStudent;
INSERT SELECT语句,不一定要求列名匹配,实际使用的是对应列的位置。
24.update
update在更新一行或者多行时,如果发生错误,则整个update操作会被取消。
如果即使发生错误也要继续进行更新,则使用IGNORE关键字。
但:以下语句只会更新id=1的内容,id=2的内容将不会更新。


25.DELETE
如果想从表中删除所有行,不要使用DELETE,可以使用TRUNCATE table语句,速度更快。truncate实际是删除了原来的表,并重新创建了一个表,而不是逐行删除。
26.last_insert_id()函数
last_insert_id()函数:获取自增主键上一次插入时候的值。
如果是手动指定主键为某个值,然后插入,则该函数返回的值不变(返回自增主键上次自己插入的值)。
27.引擎
show engines; //查看MySQL引擎

show create table student; //查看student表的信息

MyISAM:
数据库存储对应三个文件,一个索引文件,一个数据文件,一个表结构文件。叶子节点data域存放的是数据的地址。
MyISAM拥有较高的插入、查询速度,但不支持事务,支持表锁,不支持外键。
InnoDB:
数据库存储对应的两个文件,一个表结构文件,一个数据和索引文件。叶子节点存放的是数据。
支持事务安全表(ACID),支持行级锁和外键,InnoDB是默认的MySQL引擎。
MEMORY:
MEMORY存储引擎将表中的数据存储到内存中,MySQL中使用该引擎作为临时表,存放查询的中间结果。数据的处理速度很快但是安全性不高。
Archive:
如果只有INSERT和SELECT操作,可以选择Archive,Archive支持高并发的插入操作,但是本身不是事务安全的。Archive非常适合存储归档数据,如记录日志信息可以使用Archive。
28.ALTER
alter table student add column school varchar(50) not null default ” comment ‘学校’;
alter table student drop column school;
29.DROP TABLE test;
create table test(
id int not null AUTO_INCREMENT,
name varchar(20) NOT NULL DEFAULT '',
primary key (id)
)ENGINE = InnoDB;
DROP TABLE test;
30.RENAME
rename table test to test1, student to student2;
31.VIEW
create view studentView AS select id,age from student where age != 18;
select * from studentView;
视图本身没有数据,对视图进行更新实际是更新其基表。
一般视图用于检索(SELECT)而不用于更新。
32.存储过程
a.创建存储过程

因为创建存储过程需要使用 ;分隔符,但是MySQL也把 ;作为分隔符,所以要创建存储过程,先使用DELIMITER // 语句,将 // 作为新的语句分隔符。创建完存储过程后,再将 ;作为语句分隔符(恢复为原来的鳄鱼局分隔符)。
最后调用call getAvgAge(); 可以使用存储过程并得到返回值。
除 \ 符号外,任何字符都可以用作语句分隔符。
b.删除存储过程:
drop procudure getAvgAge; //后面没有括号
c.检查存储过程
show create procedure getAvgAge;

33.IN OUT INOUT
IN:传递给存储过程
OUT:从存储过程传出
INOUT:对存储过程传入和传出

所有MySQL变量都必须以 @ 开始。
34.select … for update排他锁
开启事务,但是未提交:

另外对该条记录操作,过段时间会timeout:

如果在update后,立马到1处执行commit,则2处update也成功:
