MySQL常用管理命令、常用函数小计

1、Windows系统是MySQL服务器的关闭、重启 ( mysql为服务名 )

　　关闭服务：net stop mysql

　　启动服务：net start mysql 

2、连接mysql服务器

　　在cmd窗口执行命令：mysql -h 127.0.0.1 -P 3306 -u root -p 

　　-h 127.0.0.1 ：指定主机IP 

　　 -P 3306：执行mysql服务端口

　　-u root：指定需要连接的用户

　　-p：指定登录密码（假设为root），可直接无空格方式写在-p命令后（-proot），也可回车后，根据系统提示，密文输入

3、常用管理维护命令

　　mysql命令行方式查看当前数据库版本：select version();

　　查看当前所在的数据库：select database();

　　查看当前数据库用户：select user();

　　cmd方式查看当前数据库版本：mysql --version  或者  mysql -V

　　显示所有数据库：show databases;

　　进入test库：use test;

　　显示当前库表：show tables;

　　显示指定数据库mysql内的表：show tables from mysql;

　　显示info表的结构：desc info;

4、MySQL语法规范

　　（1）不区分大小写，建议关键字大写，表名、列名小写

　　（2）每条命令建议分号结尾

　　（3）命令根据需要缩进、换行

　　（4）单行注释：#注释文字

　　　　  单行注释：--注释文字

　　　　  多行注释：/* 数值文字 */

5、DQL语句

　##查询员工表中的name字段（因为name也是mysql的关键字，为表述清晰，可在字段上加着重符 ` ，不是单引号，是TAB键上边的按键。）

　　SELECT `name` FROM employees;

　##MySQL中的+号只有运算符的功能，不能做连接符

　　SELECT 1+1　　##正常加法，输出2

　　SELECT ‘1’+1　　##尝试将字符1转换为数值型成功，执行加法，输出2

　　SELECT ‘mysql’+1 　　##尝试将字符mysql转换为数值型失败，将其转为0，执行加法，输出1

　　SELECT null+1　　##只要一方为null，结果为null

　##在mysql中使用 CONCAT() 函数进行字段结果的连接

　　SELECT CONCAT(lastname,firstname) as name FROM employees;

　　SELECT CONCAT(lastname,‘+’,firstname) as name FROM employees;　　#字段间使用+号拼接

　　注意：字段中存在null值时，拼接结果将为null，可使用 IFNULL(lastname,"") 函数将null转换为空字符串。

　　　　　另有isNull()函数，判断字段是否为null，为null返回1，不为null，返回0。

　##通配符转义

　　SELECT last_name FROM employees WHERE last_name LIKE '_\_yhon';　　#使用默认转义字符 \ 转义通配符 _

　　SELECT last_name FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$';　　#指定转义字符 $ 转义通配符 _

5、MySQL函数

5.1、单行函数

5.1.1、字符函数

　　LENGTH(string) ：获取字符串string的字节数

　　CONCAT(str1,str2) ：拼接字符串

　　UPPER(string) ：变大写

　　LOWER(string)：变小写

　　DUBSTR(string,n)：截取string字符串从指定索引n处到后面的所有字符，默认下边从1开始，包含起始位置字符

　　DUBSTR(string,n,m)：截取string字符串中从指定索引n处到开始到后面共m位字符

　　INSTR(string1,string2)：返回string2在string1中第一次出现的索引，无则返回0

　　TRIM(string) ：去掉string字符串两端的空格

　　TRIM('m' FROM string)：去掉字符串两端的指定m字符串

　　LPAD(string,n,m)：用指定字符m实现对string字符串左填充到指定长度n (string长度超过n)将会被从后面截取

　　RPAD(string,n,m)：用指定字符m实现对string字符串左填充到指定长度

　　REPLACE(string,str1,str2)：用子串str2去替换string中所有的str1子串

5.1.2、数学函数

　　ROUND(num)：对num进行四舍五入

　　ROUND(num,n)：对num小数点后保留n位四舍五入

　　CELL(num)：向上取整，返回大于等于该参数的最小整数

　　FLOOR(num)：向下取整，返回小于等于该参数的最大整数

　　TRUNCATE(num,n)：截取函数，对于num小数点后保留n位

　　MOD(a,b)：取余函数，结果正负取决于被除数的正负，运算原理：a % b == a - a / b * b

5.1.3、日期时间

　　NOW() ：返回系统当前日期+时间，格式：YYYY-MM-DD HH：mm：ss

　　CURDATE()：返回当前系统日期，格式：YYYY-MM-DD

　　CURTIME()：返回当前系统时间，格式：HH：mm：ss

　　MONTH(NOW())：返回当前月

　　MONTHNAME(NOW())：返回当前月的英文　　##年、日、小时、分钟、秒等类似

　　DATADIFF(time1,time2) ：返回time1和time2相差的天数

　　STR_TO_DATE('9-13-1999','%m-%d-%Y')：将日期格式的字符串转换成指定格式的日期，输出 1999-09-13

　　DATE_FORMATE('2018/6/6','%Y年%m月%d日')：将日期字符串转换成字符，输出 2018年06月06日

　　##常用格式符说明

　　%Y：四位的年份　　%y：两位的年份　

　　%c：固定两位的月份（01,02...11,12）　　%m：非固定格式的月份（1,2,3...11,12）

　　%d：日（01,02,03...）

　　%H：小时（24小时制）　　%h：小时（12小时制）

　　%i：分钟（00,01,02...59）

　　%s：秒（00,01,02...59）

5.1.4、流程控制函数

　　## IF函数：IF(expr1,expr2,expr3)

　　SELECT name,commission_pct,IF(commission_pct IS NULL,'没奖金','有奖金');

　　## CASE函数（1、单数值匹配） ：CASE expr1 WHEN c1 THEN expr2;WHEN c2 THEN expr3;......ELSE expr4;

　　SELECT name,salary,department_id,

　　CASE department_id

　　WHEN 10 THEN salary*1.1

　　WHEN 20 THEN salary*1.2

　　WHEN 30 THEN salary*1.3

　　ELSE salary

　　END AS new_salary

　　FROM employees;

　　## CASE函数（2、区间值匹配） ：CASE  WHEN expr1 THEN res1 ;WHEN expr2 THEN res2;......ELSE expr3 END;

　　SELECT salary,

　　CASE 

　　WHEN salary>2000 THEN 'A'

　　WHEN salary>1500 THEN 'B'

　　WHEN salary>1000 THEN 'C'

　　ELSE 'D'

　　END AS salary_class

　　FROM employees;

5.2、分组函数（聚合函数、统计函数、组函数）

5.2.1、简单分组函数

　　## 求和函数 SUM(c1)，求平均 AVG(c1)，求最大 MAX(c1)，求最小 MIN(c1)，求个数 COUNT(c1)

　　SELECT SUM(salary), AVG(salary), MAX(salary), MIN(salary), COUNT(salary) FROM employees;

　　 说明：SUM/AVG一般计算数值型数据；MAX/MIN/COUNT可用于任何字段;

　　SUM/AVG/MAX/MIN/COUNT均会忽略NULL值；

　　以上分组函数可以和DISTINCT()函数配合使用，达到去重的效果：

　　SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;

　　和分组函数一同查询的字段要求是group by后的字段。

　　##COUNT(*) 和 COUNT(1)

　　都是用来统计数据表的总记录数，COUNT(1)相当于对表扩展了一列(1)，去统计该列的行数；

　　MYISAM引擎下，COUNT(*) 效率高；INNODB引擎下，两者差不多。

5.2.2、分组查询

　　## GROUP BY 语句，查询的列表要求是分组函数和GROUP BY后出现的字段,同时MySQL的GROUP BY 和HAVING语句后支持别名，Oracle不支持。

　　## 简单 GROUP BY语句

　　SELECT MAX(salary),job_id FROM employees GROUP BY job_id;

　　## 添加分组前筛选条件的 GROUP BY 语句（针对原始表的数据源，在GROUP BY 前，使用WHERE）

　　SELECT MAX(salary),job_id FROM employees WHERE email LIKE '%a%' GROUP BY job_id;

　　##添加分组后筛选条件的 GROUP BY 语句（针对分组后的结果集，在GROUP BY 后，使用HAVING）

　　SELECT COUNT(*),department_id FROM employees GROUP BY department_id HAVING COUNT(*)>2;

　　##分组前、后均有筛选条件的 GROUP BY 语句

　　SELECT COUNT(*),department_id FROM employees WHERE  email LIKE '%a%'  GROUP BY department_id HAVING COUNT(*)>2;

　　##按多个字段进行 GROUP BY分组

　　SELECT AVG(salary),department_id,job_id FROM employees GROUP BY job_id,department_id;

　　##带有排序的 GROUP BY 语句

　　SELECT AVG(salary),department_id,job_id FROM employees GROUP BY job_id,department_id ORDER BY AVG(salary) DESC;

　　##使用表达式或者函数分组(GROPU / HAVING 后支持别名，oracle不支持)

　　SELECT COUNT(*) FROM employee GROUP BY LENGTH(last_name);