mysql

发表于 | | 阅读次数:
字数统计: 3.3k

mysql命令行工具

查看mysql命令行工具的命令

1
mysql --help

进入mysql工具

  • -u 用户
  • -p 密码
  • -h 主机地址
  • -P 端口号
1
2
3
4
5
6
7
8
# 用户进入
mysql -u root

# 带密码用户进入
mysql -u root -p

# 带密码、主机名、端口号，用户进入
mysql -u root -p -h 10.XX.XX.XX -P 3306

退出mysql工具

1
2
3
quit
或
exit

使用mysql工具

  1. 命令在mysql>后输入。
  2. 命令必须使用;或\g结束后，按enter才能执行。
1
2
3
show databases;

show databases\g
  1. 命令帮助可以借助help或\h。
1
2
3
help select;

\h insert;

库表

库

  1. 查看所有数据库列表
1
show databases;
  1. 使用某一个数据库
1
use 数据库名;

表

  1. 查看所有表
1
show tables;
  1. 查看某个表的所有列字段
1
2
3
show columns from 表名;
或者
describe 表名;

show命令

查看所有show命令。

1
help show;
1
2
3
4
5
6
7
8
9
10
# 查看授权用户及安全权限
show grants;

# 查看创建数据库/表的sql语句
show create database 库名;
show create table 表名;

# 查看服务器的错误/告警信息
show errors;
show warnings;

sql语句

sql语句不区分大小写，但是为了便于阅读，可以采取如下方式：

sql关键词大写，表名和列名小写。

语句执行要求：

  1. mysql命令行工具，必须加上;或\g来结束语句。
  2. 其他客户端工具，单行语句大部分不需要;或\g，多条语句必须使用;或\g来分隔。

select

如果select没有明确指定排序，则返回结果顺序不定。

  1. 默认会按照插入顺序。
  2. 但一旦进行了改删，顺序会受到mysql重用回收存储空间的影响，而变得混乱不定。

除非需要所有列，最好不使用通配符*，会浪费性能。

1
2
3
4
5
6
7
8
# 单字段查询
select name from user;

# 多字段查询
select id, name from user;

# 利用通配符，所有字段查询
select * from user;

distinct

  1. 用于查询结果去重
  2. 只能用于select语句
  3. distinct只能放于所有字段的最前面。
  4. 作用范围是distinct后的所有字段，而不只是紧跟着它的第一个字段。
  5. 对NULL是不进行过滤的，即返回的结果中是包含NULL值的
  6. *代表整列，使用distinct对*操作，即是对所有列组合进行去重。
1
2
3
4
5
6
7
8
# 对name去重
select distinct name from user;

# 对age和name的组合去重
select distinct age, name from user;

# 对所有列的组合去重
select distinct * from user;

由于distinct的特性（只能返回他的目标字段，而无法返回其他字段），所以往往只用distinct来返回不重复字段的条数：

1
select count(distinct name) from user;

如果要查询不重复的记录，可以用group by，而且group by 比 distinct 性能上更快：

1
select id, name from user group by name;

limit

  1. 返回select结果的某几行数据。
  2. LIMIT [offset], rows
    • limit 开始位置索引, 返回的行数
  3. LIMIT rows OFFSET offset
    • limit 返回的行数 offset 开始位置的索引
  4. offset索引从0开始
  5. row行数-1表示直到结束为止，的所有行数
  6. offset越大，性能越优
1
2
3
4
5
6
7
8
# 第六行（索引5）到15行，共10行
SELECT * FROM table LIMIT 5,10

# 第1行（索引0）到10行，共10行
SELECT * FROM table LIMIT 10

# 第11行到所有
SELECT * FROM table LIMIT 10,-1

order by

  1. order by后的排序列，不一定是显式检索列
1
select age, name from user order by id;

  1. 排序方向，默认升序

    • DESC降序
    • ASC升序
1
2
3
4
5
6
7
8
9
# 先按id降序、再按age降序

select age, name from user order by id desc, age desc;

# 先按id降序、再按age升序
select age, name from user order by id desc, age;

# 先按id升序、再按age升序
select age, name from user order by id, age;
order by + limit查找最大/最小值
  1. order by必须位于from之后
  2. order by必须位于where之后
  3. limit必须位于order by之后
1
2
3
4
5
# 查找最小值
select age from user order by age limit 1;

# 查找最大值
select age from user order by age desc limit 1;

where

用于返回满足匹配条件的行。

  1. where匹配条件时，默认不区分字符串大小写。
  2. where匹配条件时，字符串用单引号， 数值不用引号。
  3. NULL（无值）可以用 IS NULL 匹配。
1
2
3
4
5
6
7
8
# 默认不区分大小写。也可以搜索到Want、WANT...
select name from user where name='want'

# between and 
select name, age from user where age between 5 and 10

# 用于匹配null
select name from user where name is null;
比较操作符
  1. =
  2. <>
  3. !=
  4. <
  5. <=
  6. >
  7. >=
  8. between and
  9. is null
AND/OR

and和or操作符，都是where条件的子句。

  1. and 同时满足所有条件。

  2. or 只需要满足其中一个条件即可。

  3. and和or条件可以任意组合使用。

  4. and的优先级最高。
  5. 为了避免逻辑混乱，使用圆括号()明确分组。

and和or操作符，都是where条件的子句。

  1. and 同时满足所有条件。

  2. or 只需要满足其中一个条件即可。

  1. and和or条件可以任意组合使用。

  2. and的优先级最高。

  3. 为了避免逻辑混乱，使用圆括号()明确分组。

1
2
3
4
5
6
# 因为and优先级最高，实际上两者等价
select name, age from user where age = 23 or age = 24 and name = 'want'
select name, age from user where age = 23 or (age = 24 and name = 'want')

# 使用()明确逻辑,消除歧义
select name, age from user where (age = 23 or age = 24) and name = 'want'
IN

IN操作符是OR操作符的简写形式。

  1. in更清楚直观
  2. in比or执行的更快
  3. in最大的优点：可以包含其他select语句！
1
2
3
4
select name, age from user where age in (23, 24, 18)

# 等价于
select name, age from user where age = 23 or age = 24 or age = 18
NOT

用于否定之后的条件。
与以下语句结合使用：

  1. IN
  2. BETWEEN
  3. EXISTS
  4. LIKE
1
2
3
select name, age from user where age not in (23, 24, 18)

select name, age from user where age not BETWEEN 23 AND 24
LIKE

like操作符，结合通配符，用来模糊匹配字符。
通配符：

  1. % 任何字符出现的任意次数【任意字符，0-n次】
  2. _ 总是匹配一个字符
  3. %不能匹配NULL

大小写：

  1. 默认不区分大小写

性能建议：

  1. 不要过度使用通配符。如果能用其他操作符达到目的，应该使用其他操作符。
  2. 把通配符放在开头处，搜索起来最慢！
1
2
3
4
5
select name from user where name like 'want%'

select name from user where name like 'want_'

select name from user where name not like 'want_'
正则表达式

LIKE只是正则表达式的子集。

group by

把MySQL升级到5.7或者更高的版本，一些以前看上去不会出错的group by 操作在这个版本以后就会出现语法报错

1
only_full_group_by

在这个模式下，使用分组查询时，出现在select字段后面的字段，必须只能是group by后面的分组字段，或使用聚合函数包裹着的字段。

case/when

mysql 行变列

源数据

1
2
3
4
5
6
7
8
9
10
11
12
13
+----+------+--------+-------+
| id | name | clazz  | score |
+----+------+--------+-------+
|  1 | lux  | 数学   |    10 |
|  2 | want | 数学   |    15 |
|  3 | wei  | 数学   |    20 |
|  4 | lux  | 语文   |    50 |
|  5 | want | 语文   |    55 |
|  6 | wei  | 语文   |    60 |
|  7 | lux  | 英语   |    70 |
|  8 | want | 英语   |    75 |
|  9 | wei  | 英语   |    80 |
+----+------+--------+-------+

目标

1
2
3
4
5
6
7
8
+------+----------+
| name | score    |
+------+----------+
| lux  | 10;50;70 |
| want | 15;55;75 |
| wei  | 20;60;80 |
+------+----------+
select name, group_concat(score Separator ';') as score from grade group by name;

目标

1
2
3
4
5
6
7
8
+------+----------------------------+
| name | score                      |
+------+----------------------------+
| lux  | 数学10;语文50;英语70       |
| want | 数学15;语文55;英语75       |
| wei  | 数学20;语文60;英语80       |
+------+----------------------------+
select name, group_concat(concat(clazz,score) Separator ';') as score from grade group by name;

目标

1
2
3
4
5
6
7
8
9
10
11
12
13
+------+------+---------+---------+
| name | math | chinese | english |
+------+------+---------+---------+
| lux  |   10 |      50 |      70 |
| want |   15 |      55 |      75 |
| wei  |   20 |      60 |      80 |
+------+------+---------+---------+
select
  name, 
  MAX(case clazz when '数学' then score else 0 end) as math,
  MAX(case clazz when '语文' then score else 0 end) as chinese,
  MAX(case clazz when '英语' then score else 0 end) as english
from grade group by name;

参考：

https://www.cnblogs.com/small8/p/6211009.html

判断字符串字段,是否包含某字符串

方法一：like 模糊判断

* FROM 表名 WHERE 字段名 like "%字符%";```
1
2
3
4
5
6
7
8
9

方法二：find_in_set() 严格判断

1. 利用mysql 字符串函数 find_in_set();
2. find_in_set(str1,str2)函数是返回str2中str1所在的位置索引，不存在就返回0，str2必须以","分割开。

```mysql
SELECT * FROM users WHERE find_in_set('字符', 字段名)
SELECT find_in_set('3','3,6,13,24,33,36')

方法三：locate(字符,字段名) 模糊判断

1
2
3
select * from 表名 where locate(字符,字段)
select * from 表名 where position(字符 in 字段);
update site set url =concat('http://',url) where locate('http://',url)=0

方法四：INSTR(字段,字符) 模糊判断

1
select * from 表名 where INSTR(字段,字符)

时间

DATE_FORMAT(date,format)　　日期格式化

1
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');

STR_TO_DATE(str,format)　　字符串 ===》 日期

1
select str_to_date('2016-01-02', '%Y-%m-%d %H');

UNIX_TIMESTAMP()　　其他数据 ===》时间戳

1
2
select unix_timestamp(now());
select unix_timestamp('2016-02-01');

FROM_UNIXTIME(unix_timestamp,format)　　时间戳格式化

1
2
3
4
5
-- 十三位时间戳转时间。
SELECT FROM_UNIXTIME(time/1000,'%Y-%m-%d %h:%i:%s')  FROM user;

-- 十位时间戳转时间。(%H)
SELECT FROM_UNIXTIME(time,'%Y-%m-%d %h:%i:%s')  FROM user;

时间格式

毫秒 %f 毫秒数（000000…999999）
秒 %S、%s 两位数字形式的秒（ 00,01, …, 59）
分 %I、%i 两位数字形式的分（ 00,01, …, 59）
小时 %H 24小时制，两位数形式小时（00,01, …,23）
%h 12小时制，两位数形式小时（00,01, …,12）
%k 24小时制，数形式小时（0,1, …,23）
%l 12小时制，数形式小时（0,1, …,12）
%T 24小时制，时间形式（HH:mm:ss）
%r 12小时制，时间形式（hh:mm:ss AM 或 PM）
%p AM上午或PM下午
周 %W 一周中每一天的名称，周日到周六（Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday）
%a 一周中每一天的名称的缩写，周日到周六（Sun,Mon,Tue,Wed,Thu,Fri,Sat）
%w 以数字形式标识周（0=Sunday,1=Monday, …,6=Saturday）
%U 数字表示周数，星期天为周中第一天
%u 数字表示周数，星期一为周中第一天
天 %d 两位数字表示月中天数（01,02, …,31）
%e 数字表示月中天数（1,2, …,31）
%D 英文后缀表示月中天数（1st,2nd,3rd …）
%j 以三位数字表示年中天数（001,002, …,366）
月 %M 英文月名（January,February,March,April,May,June,July,August,September,October,November,December）
%b 英文缩写月名（Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec）
%m 两位数字表示月份（01,02, …,12）
%c 数字表示月份（1,2, …,12）
年 %Y 四位数字表示的年份（2015,2016…）
%y 两位数字表示的年份（15,16…）
文字输出 %文字 直接输出文字内容

创建数据库

1
create database grid;

字段类型

java类 mysql数据库
java.lang.Byte byte TINYINT
java.lang.Short short SMALLINT
java.lang.Integer integer INGEGER
java.lang.Long long BIGINT
java.lang.Float float FLOAT
java.lang.Double double DOUBLE
java.lang.BigDecimal big_decimal NUMERIC
java.lang.Boolean boolean BIT
java.lang.String string VARCHAR
java.lang.Boolean yes_no CHAR(1)(‘Y’或’N’)
java.lang.Boolean true_false CHAR(1)(‘Y’或’N’)
java.uitl.Date / java.sql.Date date DATE
java.sql.Time time TIME
java.sql.Timestamp timestamp TIMESTAMP
java.uitl.Calendar celendar TIMESTAMP
java.uitl.Calendar calendar TIMESTAMP
java.io.Serializable serializable VARBINARY/BLOB
java.sql.Clob clob CLOB
java.sql.Blob blob BLOB
java.lang.Class class VARCHAR
java.uitl.Locale locale VARCHAR
java.uitl.TimeZone timezone VARCHAR
java.uitl.Currency currency VARCHAR

创建表

1
2
3
4
5
6
7
8
9
10
11
12
# 创建
create table user
(
	id int AUTO_INCREMENT,
	name varchar(50) not null,
	age int default 0,
	primary key (id)
) engine=InnoDB;


# 查看
SHOW COLUMNS from user;

insert

1
2
3
4
5
# 省略列指定，表示全部列
insert into user values(null, 'want', 23);

# 显式指定列
insert into user(name, age) values('yuni', 18);

alter

1
2
3
4
# 增加新列
alter table user add column time Date;
# 修改列
alter table user modify column time bigint;

存储过程procedure

操作多条语句的完整过程。

创建

1
2
3
4
CREATE PROCEDURE handleage()
BEGIN
	select avg(age) as avgAge from user;
END;

执行

1
CALL handleage(@mymax, @mymin, @myavg)

show character set;

show collation;

show variables like ‘character%’;

show variables like ‘collation%’

通过.sql文件执行命令

方法1：source

1
2
mysql>use mytable;
mysql>source ../testsql.sql;

方法2: < (小于号)

1
mysql -u root -p < ../testsql.sql

账号管理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
use mysql;

show tables;

select user from user;

# 创建账号（带密码）
create user test identified by 'test';

# 创建账号（无密码）
create user want;

# 获取当前账号
select user();

# 重命名账号
rename user want to wanted; 

# 修改密码
[废弃]set password for test = Password('test123');
[新版]alter user test identified by 'test123';

# 修改当前用户的密码
alter user user() identified by 'test';

#删除账号
drop user wanted;

常见问题

客户端连接MySQL报错- authentication plugin ‘caching_sha2_password’ -navicat

原因：

  • mysql 8.0 版本默认使用 caching_sha2_password 身份验证机制

  • mysql 8.0以前版本使用 mysql_native_password

  • 低版本升级到8.0 验证方式不变，新用户使用8.0验证方式改变，而客户端不支持新的加密方式

解决方案：

  1. 方法之一：修改用户的密码和加密方式
1
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';

注意：

  • 之前可能由于设置远程连接的问题而在mysql.user表中对root的Host列做了修改，要确定上面的 localhost 必须存在于该表中user为root的Host列中。
  • 如上语句，只针对使用root用户在localhost下连接生效
  1. 方法二：修改文件 /etc/my.cnf
1
default_authentication_plugin=mysql_native_password

IDEA中配置MySQL出现Server returns invalid timezone问题

原因：
首先，出现该问题的原因是MySQL驱动jar中的默认时区是UTC。

UTC代表的是全球标准时间 ，但是我们使用的时间是北京时区也就是东八区，领先UTC八个小时。

因为时区不一致，所以提示Server returns invalid timezone.

解决方案：

Go to ‘Advanced’ tab and set ‘serverTimezone’ property manually

1
Asia/Shanghai
-------------Keep It Simple Stupid-------------
0%