MySQL常用命令与函数

常用命令

登录

mysql -h -P -u -p 
#参数说明 -h:本地或远程数据库的 IP 地址，本机可以忽略此参数；-P:数据库对应的端口；-u: 用户名 -p:用户对应的密码，可以直接写在命令后面，注意不能带空格，也可以不写，回车后再输入密码
#举例
mysql -h locahost -P 3306 -u root -proot
mysql -h 192.163.1.2 -P 3306 -u queryUser -p

退出

exit
或者同时按下键盘的 ctrl + c

查看当前数据库的版本

#未登录mysql 服务，使用环境变量中的 mysql
mysql --version
mysql -V
# 登录 mysql 服务
mysql version();

显示当前用户所拥有的所有数据库

show databases;

切换数据库

use test; # 切换到 test 库
use mysql # 切换到 mysql 库

显示某一个库中的所有的表

show tables for mysql;

查看当前正在使用的库

select database();

查看某张表的表结构

desc user; #会显示 user 表的具体设计	

常用函数

字符串拼接 - concat

#举例
SELECT CONCAT(u.first_name,u.last_name) AS fullName FROM user u;

获取某个字段的字节长度 - length

SELECT LENGTH(u.name) FROM user u;

大小写转换 - upper lower

#举例
SELECT CONCAT(upper(u.first_name),'_'lower(u.last_name)) AS fullName FROM user u;

字符串截取 - substr

#注意 索引从 1 开始
#截取指定索引后面的所有字符,包含索引所在
select substr('京东618狂欢购',3) from user; #结果：618狂欢购
#截取指定索引处，长度为 5 的字符串，从 1 到 5
select substr('京东618狂欢购',1,5); #结果：京东618

去除空格 - trim

select trim('   棒棒糖     ');

左补齐 - LPAD

select LPAD('100',5,'0'); #结果：00100

右补齐同理

替换 - replace

select replace('王大壮999','999','666'); #结果：王大壮666

四舍五入 - round

select ROUND(1.49,1); #结果：1.5
select ROUND(1.49); #结果：1

向上取整 - ceil

select ceil(-5.29); #结果：-5

向下取整 - floor

select FLOOR(-5.29); #结果：-6

截断 - TRUNCATE

SELECT TRUNCATE(1.56,1); #结果：1.5

取模 - mod

select mod(10,3); #结果：1

当前时间 - now() 返回年月日时分秒

select now();

当前时间，不包含时间 - curdate()

select curdate();

当前时间，不包含年月日 - curtime()

select curdate();

单独获取当前时间的年月日时分秒 - year()\month()\day()…

select year(now()); #结果 ：2021

字符串转日期 - str_to_date

select str_to_date('2020-06-20 14:34:34','%Y-%m-%d %H:%i:%s');

#格式说明
%Y: 四位的年份
%y: 两位的年份
%m: 月份（01，02...）
%c: 月份（1，2...）
%d: 日（01，02...）
%H: 小时（24 小时制）
%h: 小时（12小时制）
%i: 分钟
%s: 秒

日期转字符串 - date_format()

select date_format(now(),'%y-%m-%d');

流程控制函数 - if()

select if(age>=18,'成年人'，'未成年人') from user; 

流程控制 - case … when …then …

select salary as '工资',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 '绩效工资' FROM user;

#---------------------------
select salary as '工资',department_id,
case
when salary>10000 then 'A'
when salary>15000 then 'B'
when salary>20000 then 'C'
else 'D'
end as '工资级别' FROM user;

计数 - count

select count(*) from user; 
select count(1) from user;
select count(address) from user;

#区别
count(1) 和 count(*) 都是统计行数；
count(address) 统计 address 字段不为 null 的行数，如果有一行为 null，则不统计
#效率
MYISAM 引擎下，count(*)较快，因为自身有内存计数器
InnoDB 引擎下，count(*)和 count(1) 效率一样，但是比 count(字段) 要快，因为 count(字段)需要判断是否为 null 值