MySQL笔记

  database
  17 分钟 读完 (大约 2500 个字) 0次访问

MySQL

数据库操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select version();                       -- 显示数据库版本

select now();                           -- 显示时间

show databases;                         -- 查看所有数据库

select database();                      -- 查看当前使用的数据库

use 数据库名;                           -- 使用数据库

create database 数据库名 charset=utf8;  -- 创建数据库

show create database 数据库名;          -- 查看创建数据库的语句    

drop database 数据库名;                 -- 删除数据库

数据表操作

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
28
29
30
31
32
33
34
35
36
-- 查看当前数据库中所有表
show tables;

-- 查看表结构
desc 表名;

-- 查看表的创建语句
show create table 表名;

-- 创建表
-- create table 数据表名字 (字段 类型 约束, ...);
-- 约束：not null、 primary key、default
-- auto_increment表示自动增长(插入数据时可以使用0 / default占位)
create table students (
    id bigint unsigned not null auto_increment primary key,
    name varchar(30) not null, 
    age tinyint unsigned default 0,
    high decimal(5,2),      -- 规定存储的值不超过5位数，且小数点后面有2位数字
    gender enum("男", "女") default "女"
);

-- 插入数据
insert [into] 表名 values(...)
insert [into] 表名(列名,...) values(...)
insert [into] 表名(列名,...) values(...), (...) ... -- 插入多列

--- 修改
update 表名 set 列1=值1,列2=值2... where 条件;

-- 物理删除
delete from 表名 where 条件

-- 逻辑删除： 用一个字段来表示 这条信息是否已经不能再使用了
-- 如：给表添加一个is_delete字段[bit(1)], 为true表示改行信息不在使用
alter table 表名 add is_delete bit(1) default 0;
update 表名 set is_delete=1 where 条件;     -- 修改标记

查询相关

查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
select 字段[as 别名] , 字段[as 别名] from 数据表 where ....;

-- like模糊查询: 效率低。 %替换任意个字符， _替换一个字符
select name from students where name like "小%";

-- rlike 查询中使用正则
select name from students where name rlike "^小.*"; 

-- 范围查询
-- in (), not in(), between ... and ..., not between ... and ...
select name from students where age in (1, 2, 3);
select * from students where age between 18 and 34;

-- 空判断: is null, is not null
select * from students where high is null;

-- 排序
-- order by 字段1 [asc(默认升序) / desc(降)], 字段2 [] ...
select * from students order by age asc, height desc;

-- 聚合函数
-- count(*)总数， max()最大值， min(), sum(), avg(), round(数, 精度)四舍五入
-- 男性的平均身高 保留2位小数
select round(avg(height), 2) from students where gender=2;

分组group by

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
-- 在students表中，按照性别分组,查询所有的性别
-- 失败select * from students group by gender;
select gender from students group by gender;

-- 在students表中，计算每种性别中的人数
select gender,count(*) from students group by gender;

-- 在students表中，计算男性的人数
select count(*) from students where gender=1;
select gender,count(*) from students where gender=1 group by gender;

-- group_concat(...)
-- 在students表中，查询男性中的姓名
select gender, group_concat(name) from students where gender=1 group by gender;
| gender | group_concat(name) |
+--------+--------------------+
| 男     | tt1,tt2            |
+--------+--------------------+

-- having
-- 在students表中，按照性别分组，查询平均年龄超过30岁的性别，以及姓名 having avg(age) > 30
select gender, group_concat(name), avg(age) from students group by gender having avg(age)>30;

-- 在students表中，查询每种性别中的人数多于2个的信息
select gender, group_concat(name), count(*) from students group by gender having count(*)>2;

分页limit

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- limit offset, size    从哪儿开始 每页显示个数        使用在where、order、having后

-- 在students表中，查询前5个数据    offset=0省略
select * from students limit 5;

-- limit (第N页-1)*每页的个数, 每页的个数
-- 在students表中，每页显示2个，第1个页面
select * from students limit 0, 2;
-- 在students表中，每页显示2个，第2个页面
select * from students limit 2, 2;
-- 在students表中，每页显示2个，第3个页面
select * from students limit 4, 2;

-- 在students表中，查询女性信息 并且按照身高从高到矮排序 只显示前2个
select * from students where gender=2 order by height desc limit 0,2;

连接查询

  • left join （左连接）：返回左表中的所有记录和右表中连接字段相等的记录，不相等的记录的所有字段用null填充。
  • right join （右连接）：返回右表。。。
  • inner join （等值连接/内连接）：只返回两个表中连接字段相等的行。
  • full join （全外连接）：返回左右表中所有的记录和左右表中连接字段相等的记录。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 查询 有能够对应班级的学生以及班级信息
select * from students inner join classes on students.cls_id=classes.id;

-- 查询 有能够对应班级的学生以及班级信息，显示学生的所有信息，只显示班级名称
select s.*, c.name from students as s inner join classes as c on s.cls_id=c.id;

-- 查询 有能够对应班级的学生以及班级信息, 并排序（先按班级进行排序，班级内按学生id排序）。
select c.name , s.* from students as s inner join classes as c on s.cls_id=c.id order by c.name, s.id;

select * from students as s left join classes as c on s.cls_id=c.id;

-- 查询没有对应班级信息的学生
-- select ... from xxx as s left join xxx as c on..... where/having .....
select * from students as s left join classes as c on s.cls_id=c.id where c.id is null;

子查询

1
2
3
4
5
6
7
8
-- 查询出高于平均身高的信息
select * from students where height > (select avg(height) from students);

-- 查询最高的男生信息
select * from students where height = (select max(height) from students);

-- 查询学生的班级号能够对应的学生信息
select * from students where cls_id in (select id from classes);

自关联

实现省级联动效果: http://demo.lanrenzhijia.com/2014/city0605/

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 创建areas表
create table areas(
    aid int primary key,
    atitle varchar(20),
    pid int
);
-- 导入数据
source areas.sql

-- 查询所有省份
select * from areas where pid is null; 

-- 查询出山东省有哪些市
select * from areas as province inner join areas as city on city.pid=province.aid having province.atitle="山东省";
select province.atitle, city.atitle from areas as province inner join areas as city on city.pid=province.aid having province.atitle="山东省";

-- 查询出青岛市有哪些县城
select province.atitle, city.atitle from areas as province inner join areas as city on city.pid=province.aid having province.atitle="青岛市";
select * from areas where pid=(select aid from areas where atitle="青岛市")

exists / not exists

exists / not exists子句根据其内查询语句的结果集空或者非空，返回一个布尔值。结果集不为空exists返回true， 为空exists放回false， not exists则相反。

例子

1
2
3
select * from table where exists(select false);		-- exists返回true，有结果
select * from table where exists(select null);		-- exists返回true，有结果
select * from table where exists(select 0);		    -- exists返回true，有结果

exists / in 查询

1
2
select * from a where exists(select * from b where b.a_id = a.id);
select * from a where id in (select a_id from b);

in与exists

  • 执行过程不同。in先执行子表，exists先执行外表。
  • exists强调的是是否返回结果集，不要求知道返回什么。
  • 使用场景:
    • 如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in。
    • 如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。

case when函数

用于分类统计

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
-- 薪资分类
select name, sal,
(case when sal <= 1000 then 'C'
when sal > 1000 and sal <= 2000 then 'B'
else 'A' end) sal_class
from sal;
/*
t1	1000.21	B
t2	2000.21	A
t3	3000.21	A
t4	5000.21	A
t5	100.21	C
*/

-- 按薪资分类统计平均薪资
select 
(case when sal <= 1000 then 'C'
when sal > 1000 and sal <= 2000 then 'B'
else 'A' end) sal_class ,
avg(sal) from sal
group by sal_class;
/*
B	1000.21
A	3333.543333
C	100.21
*/

空值处理

ifnull(col, value)函数

1
select * from emp order by ifnull(comm, -1);

事务

1
2
3
4
5
-- 关闭自动提交
set autocommit = 0;
.......
-- 手动提交
commit;

安装mariadb

安装

在centos系统中安装

刚安装完mariadb后使用mysql_secure_installation脚本，在第一步输入密码可能会失败，可以重启系统再次进行配置

1
2
3
4
5
yum install mariadb mariadb-server
systemctl start mariadb     #启动mariadb
systemctl enable mariadb    #设置开机自启动
mysql_secure_installation    #设置root密码、远程访问等信息
mysql -uroot -p             #登录(默认密码为空)

乱码解决

设置字符集，设置好后，在连接数据库时候，指定编码，比如使用jdbc连接时，指定连接为utf8方式.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 远程/代码控制数据乱码
SHOW VARIABLES LIKE 'character%';   -- 查看系统默认字符集
SHOW VARIABLES LIKE 'collation_%';  
-- 修改编码: 使用mysql命令
SET NAMES 'utf8';       -- 修改client、connection、results 三个字符集
SET character_set_database = utf8 ;
SET character_set_server = utf8 ;

-- 修改编码：该配置文件
vim /etc/my.cnf        
# 在[mysqld]标签下添加下面内容
default-storage-engine = innodb
innodb_file_per_table
max_connections = 4096
collation-server = utf8_general_ci
character-set-server = utf8

vim /etc/my.cnf.d/client.cnf
# 在[client]标签下添加下面内容
default-character-set=utf8

vim /etc/my.cnf.d/mysql-clients.cnf
# 在[mysql]标签下添加下面内容
default-character-set=utf8

DCL

管理用户

1
2
3
4
5
6
7
8
9
10
11
-- 添加用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
-- 删除用户
DROP USER '用户名'@'主机名';           
-- 更改密码           
UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';
SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');

-- 查询用户；mysql.user表中
USE myql;
SELECT user, password, host FROM USER;

权限管理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 查询权限
SHOW GRANTS FOR '用户名'@'主机名';
SHOW GRANTS FOR 'lisi'@'%';

-- 授予权限：
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
-- 给张三用户授予所有权限，在任意数据库任意表上
GRANT ALL ON *.* TO 'zhangsan'@'localhost';
-- 撤销权限：
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

-- 例子: 创建tt用户，并授予最高权限
create user 'tcb'@'%' identified by 'Tan12345';
grant all privileges on *.* to 'tcb'@'%';

-- 赋予用户在指定主机上连接数据库，并赋予相应权限。
-- mysql.user表中记录着权限分配（用户，对应客户端主机）
grant 权限列表 privileges on *.* to [username]@"[host]" identified by "[password]";

例：开放其他主机远程连接数据库权限

1
2
3
4
5
6
-- 允许用户root使用密码root在任何主机上连接该数据库，并赋予该用户所有权限。
-- 1. 服务器开3306端口 
firewall-cmd --add-port=3306/tcp --permanent
firewall-cmd --reload
-- 2. 进入数据库授予用户权限
grant all privileges on *.* to root@"%" identified by "root"   -- %通配符代表任意主机
  , 

喜欢这篇文章？打赏一下作者吧

Java IO流
Git的使用

评论

  关注我
Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×