数据库-MySQL-常用SQL-小知识点

1、查看表结构
DESC xxxtablename；
2、查看创建表SQL语句
show create table xxxtablename \G;
3、删除表
drop table if exists xxxtablenama ；
drop table xxxtablenama；
4、修改表（字段）类型
alter table xxxtablename modify yyy字段 varchar(20)；
5、增加表字段
alter table xxxtablename add column yyy字段 int(3);
6、删除表字段
alter table xxxtablename drop column yyy字段;
7、字段改名
alter table xxxtablename change yyy字段 zzz字段 int(4);
//change和modify都可以修改表的定义，不同的是change后面需要写两次列名，不方便。
//但是change的优点是可以修改列名称，modify则不能。
8、修改字段排列顺序
alter table xxxtablename add yyy字段 date after zzz字段;（date 为日期类型）
alter table xxxtablename modify yyy字段 int(30) first;
//chage/first/after column这些关键字都是mysql在标准sql的扩展，不一定适用于其他数据库。
9、表改名
alter table xxxtablename rename yyytablename;
10、查看数据表状态
show table status \G;
11、查看支持的数据库引擎
SHOW ENGINES \G
SHOW VARIABLES LIKE 'have%';
//其中 Value 显示为“DISABLED”的记录表示支持该存储引擎，但是数据库启动的时候被禁用
alter table ai engine = innodb; 改变存储引擎
常用数据库引擎对比见《数据库-数据库引擎》


--- show warnings；
--- float(7,4) -999.9999 超过范围会被四舍五入
--- decimal 精度较高
--- select * 返回NULL，改用bin() 和 hex() 函数再试

日期时间类型 字节 最小值 最大值
DATE 4 1000-01-01 9999-12-31
DATETIME 8 1000-01-01 00:00:00 9999-12-31 23:59:59
TIMESTAMP 4 19700101080001 2038年的某个时刻
TIME 3 -838:59:59 838:59:59
YEAR 1 1901 2155

二、 运算符
1、“=”运算符，用于比较运算符两侧的操作数是否相等，如果两侧操作数相等返回值为 1，否则为 0。注意 NULL 不能用于“=”比较
2、“<>”运算符，和“=”相反，如果两侧操作数不等，则值为 1，否则为 0。NULL 不能用于“<>”比较。
3、“<=>”安全的等于运算符，和“=”类似，在操作数相等时值为 1，不同之处在于即使操作的值为 NULL 也可以正确比较

---BETWEEN
“BETWEEN”运算符的使用格式为“a BETWEEN min AND max”，当 a 大于等于 min 并且小于等于 max，则返回值为 1，否则返回 0；当操作数 a、min、max 类型相同时，此表达式等价于（a>=min and a<=max），当操作数类型不同时，比较时会遵循类型转换原则进行转换后，再进行比较运算。

---IN
“IN”运算符的使用格式为“a IN (value1,value2,…)”,当 a 的值存在于列表中时，则整个比较表达式返回的值为 1，否则返回 0。

---IS NULL
“IS NULL”运算符的使用格式为“a IS NULL”,当 a 的值为 NULL，则返回值为 1，否则返回 0。

---IS NOT NULL
“IS NOT NULL”运算符的使用格式为“a IS NOT NULL”。和“IS NULL”相反，当 a 的值不为 NULL，则返回值为 1，否则返回 0。

---LIKE
“LIKE”运算符的使用格式为“a LIKE %123%”,当 a 中含有字符串“123”时，则返回值为 1，否则返回 0。

---REGEXP
“REGEXP”运算符的使用格式为“str REGEXP str_pat”,当 str 字符串中含有 str_pat相匹配的字符串时，则返回值为 1，否则返回 0。

---逻辑运算符
NOT 或！ 逻辑非
AND 或&& 逻辑与
OR 或 || 逻辑或
XOR 逻辑异或（不同为1）

---位运算符
运算符 作用
& 位与（位 AND）
| 位或 （位 OR ）
^ 位异或（位 XOR）
~ 位取反
>> 位右移
<< 位左移

---运算符的优先级
优先级顺序 运算符
1 :=
2 ||, OR, XOR
3 &&, AND
4 NOT
5 BETWEEN, CASE, WHEN, THEN, ELSE
6 =, <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
7 |
8 &
9 <<, >>
10 -, +
11 *, /, DIV, %, MOD
12 ^
13 - (一元减号), ~ (一元比特反转)
14 !

三、常用函数
MySQL 中的常用字符串函数
函数 功能
CANCAT(S1,S2,…Sn) 连接 S1,S2,…Sn 为一个字符串
select concat('aaa','bbb','ccc') ,concat('aaa',null);
INSERT(str,x,y,instr) 将字符串 str 从第 x 位置开始，y 个字符长的子串替换为字符串 instr
select INSERT('beijing2008you',12,3, 'me') ;
LOWER(str) 将字符串 str 中所有字符变为小写
UPPER(str) 将字符串 str 中所有字符变为大写
LEFT(str ,x) 返回字符串 str 最左边的 x 个字符
RIGHT(str,x) 返回字符串 str 最右边的 x 个字符
SELECT LEFT('beijing2008',7),LEFT('beijing',null),RIGHT('beijing2008',4);
LPAD(str,n ,pad) 用字符串 pad 对 str 最左边进行填充，直到长度为 n 个字符长度
RPAD(str,n,pad) 用字符串 pad 对 str 最右边进行填充，直到长度为 n 个字符长度
select lpad('2008',20,'beijing'),rpad('beijing',20,'2008');
--> beijingbeijingbe2008 | beijing2008200820082 |
LTRIM(str) 去掉字符串 str 左侧的空格
RTRIM(str) 去掉字符串 str 行尾的空格
REPEAT(str,x) 返回 str 重复 x 次的结果
select repeat('mysql ',3);
--> mysql mysql mysql
REPLACE(str,a,b) 用字符串 b 替换字符串 str 中所有出现的字符串 a
select replace('beijing_2010','_2010','2008');
--> beijing2008
STRCMP(s1,s2) 比较字符串 s1 和 s2
TRIM(str) 去掉字符串行尾和行头的空格
SUBSTRING(str,x,y) 返回从字符串 str x 位置起 y 个字符长度的字串

四、数值函数
ABS(x) 返回 x 的绝对值
CEIL(x) 返回大于 x 的最大整数值
FLOOR(x) 返回小于 x 的最大整数值
MOD(x，y) 返回 x/y 的模（相当于取整）
RAND() 返回 0 到 1 内的随机值
ROUND(x,y) 返回参数 x 的四舍五入的有 y 位小数的值
TRUNCATE(x,y) 返回数字 x 截断为 y 位小数的结果

几乎都是在select 语句中使用 select round(2.23332,3)

五、
CURDATE() 返回当前日期
CURTIME() 返回当前时间
NOW() 返回当前的日期和时间
UNIX_TIMESTAMP(date) 返回日期 date 的 UNIX 时间戳
FROM_UNIXTIME 返回 UNIX 时间戳的日期值
---> select FROM_UNIXTIME(1184134516) ;
WEEK(date) 返回日期 date 为一年中的第几周
YEAR(date) 返回日期 date 的年份
HOUR(time) 返回 time 的小时值
MINUTE(time) 返回 time 的分钟值
MONTHNAME(date) 返回 date 的月份名
DATE_FORMAT(date,fmt) 返回按字符串 fmt 格式化日期 date 值
DATE_ADD(date,INTERVAL expr type) 返回一个日期或时间值加上一个时间间隔的时间值
DATEDIFF(expr,expr2) 返回起始时间 expr 和结束时间 expr2 之间的天数
示例 select year(curdate()); //2017

DATE_FORMAT(date,fmt)函数：按字符串 fmt 格式化日期 date 值，此函数能够按指定的
格式显示日期，可以用到的格式符如表 5-4 所示。
表 5-4 MySQL 中的日期时间格式
格式符 格式说明
%S,%s 两位数字形式的秒（00,01,...,59）
%i 两位数字形式的分（00,01,...,59）
%H 两位数字形式的小时，24 小时（00,01,...,23）
%h,%I 两位数字形式的小时，12 小时（01,02,...,12）
%k 数字形式的小时，24 小时（0,1,...,23）
%l 数字形式的小时，12 小时（1,2,...,12）
%T 24 小时的时间形式（hh:mm:ss）
%r 12 小时的时间形式（hh:mm:ssAM 或 hh:mm:ssPM）
%p AM 或 PM
%W 一周中每一天的名称（Sunday,Monday,...,Saturday）
%a 一周中每一天名称的缩写（Sun,Mon,...,Sat）
%d 两位数字表示月中的天数（00,01,...,31）
%e 数字形式表示月中的天数（1,2，...,31）
%D 英文后缀表示月中的天数（1st,2nd,3rd,...）
%w 以数字形式表示周中的天数（0=Sunday,1=Monday,...,6=Saturday）
%j 以 3 位数字表示年中的天数（001,002,...,366）
%U 周（0,1,52），其中 Sunday 为周中的第一天
%u 周（0,1,52），其中 Monday 为周中的第一天
%M 月名（January,February,...,December）
Linux公社 www.linuxidc.com
90
%b 缩写的月名（January,February,...,December）
%m 两位数字表示的月份（01,02,...,12）
%c 数字表示的月份（1,2,...,12）
%Y 4 位数字表示的年份
%y 两位数字表示的年份
%% 直接值“%”
示例：将当前时间显示为“月，日，年”格式：
mysql> select DATE_FORMAT(now(),'%M,%D,%Y');
+-------------------------------+
| DATE_FORMAT(now(),'%M,%D,%Y') |
+-------------------------------+
| July,11th,2007 |
+-------------------------------+
1 row in set (0.00 sec)
 DATE_ADD(date,INTERVAL expr type)函数：返回与所给日期 date 相差 INTERVAL 时间段的
日期。
其中 INTERVAL 是间隔类型关键字，expr 是一个表达式，这个表达式对应后面的类型，type
是间隔类型，MySQL 提供了 13 种间隔类型，如表 5-5 所示。
表 5-5 MySQL 中的日期间隔类型
表达式类型 描述 格式
HOUR 小时 hh
MINUTE 分 mm
SECOND 秒 ss
YEAR 年 YY
MONTH 月 MM
DAY 日 DD
YEAR_MONTH 年和月 YY-MM
DAY_HOUR 日和小时 DD hh
DAY_MINUTE 日和分钟 DD hh:mm
DAY_ SECOND 日和秒 DD hh:mm:ss
HOUR_MINUTE 小时和分 hh:mm
HOUR_SECOND 小时和秒 hh:ss
MINUTE_SECOND 分钟和秒 mm:ss
来看一个具体的例子，在这个例子中第 1 列返回了当前日期时间，第 2 列返回距离当前日期
31 天后的日期时间，第 3 列返回距离当前日期一年两个月后的日期时间。
mysql> select now() current,date_add(now(),INTERVAL 31 day) after31days,
date_add(now(),INTERVAL '1_2' year_month) after_oneyear_twomonth;
+---------------------+---------------------+------------------------+
| current | after31days | after_oneyear_twomonth |
+---------------------+---------------------+------------------------+
| 2007-09-03 11:30:48 | 2007-10-04 11:30:48 | 2008-11-03 11:30:48 |
+---------------------+---------------------+------------------------+
1 row in set (0.01 sec)
Linux公社 www.linuxidc.com
91
同样也可以用负数让它返回之前的某个日期时间，如下第 1 列返回了当前日期时间，第 2
列返回距离当前日期 31 天前的日期时间，第 3 列返回距离当前日期一年两个月前的日期时
间。
mysql> select now() current,date_add(now(),INTERVAL -31 day) after31days,date_a
dd(now(),INTERVAL '-1_-2' year_month) after_oneyear_twomonth;
+---------------------+---------------------+------------------------+
| current | after31days | after_oneyear_twomonth |
+---------------------+---------------------+------------------------+
| 2007-09-03 11:36:35 | 2007-08-03 11:36:35 | 2006-07-03 11:36:35 |
+---------------------+---------------------+------------------------+
1 row in set (0.00 sec)
 DATEDIFF（date1，date2）函数：用来计算两个日期之间相差的天数。
下面的例子计算出当前距离 2008 年 8 月 8 日的奥运会开幕式还有多少天：
mysql> select DATEDIFF('2008-08-08',now());
+------------------------------+
| DATEDIFF('2008-08-08',now()) |
+------------------------------+
| 328 |
+------------------------------+
1 row in set (0.01 sec)

六、流程函数（跟存储过程好像有点区别）
流程函数也是很常用的一类函数，用户可以使用这类函数在一个 SQL 语句中实现条件选择，
这样做能够提高语句的效率。
函数 功能
IF(value,t f) 如果 value 是真，返回 t；否则返回 f
应用：

IFNULL(value1,value2) 如果 value1 不为空返回 value1，否则返回 value2

应用：
IFNULL(device_no1,"") !="" 用于where
综合应用：
IF(IFNULL(device_no1,"")="",null,device_no1) 用于select




CASE WHEN [value1]
THEN[result1]…ELSE[default]END
如果 value1 是真，返回 result1，否则返回 default

CASE [expr] WHEN [value1]
THEN[result1]…ELSE[default]END
如果 expr 等于 value1，返回 result1

示例： select case salary when 1000 then 'low' when 2000 then 'mid' else 'high' end from salary;
// WHEN [value1]
THEN[result1] 可重复
[expr] ---> salary

七、其他函数
select DATABASE(); 返回当前的数据库名
select version(); 返回当前的数据库版本
select INET_ATON('192.168.1.1');返回 IP 地址的网络字节序表示。
select INET_NTOA(3232235777); 返回网络字节序代表的 IP 地址。
--- 用于网段比较
select * from t where ip>='192.168.1.3' and ip<='192.168.1.20'
Empty set (0.01 sec) ;//字符串比较必然为空，所以要先转换
select PASSWORD('123456'); 返回字符串 str 的加密版本，一个 41 位长的字符串
只用来设置系统用户的密码，不能用于处理数据库数据加密
select MD5('123456'); 返回MD5加密结果，可用于数据库数据加密


附录：
MySQL创建用户与授权方法
--- http://www.jb51.net/article/31850.htm

CREATE USER 'js'@'%' IDENTIFIED BY 'js'; //创建任意服务器可以连接的用于名
测试 mysql -h 192.168.1.5 -P3306 -ujs -pjs //不能查看数据库
GRANT ALL ON *.* TO 'js'@'%'; //授权查看所有数据库 quit后重新登录


----来源《深入浅出MySQL全文.pdf》