一. MySQL存储引擎
1. 什么是存储引擎?
mysql软件自带功能程序
是用来处理表的处理器
不同的存储引擎有不同的功能和数据存储方式
2. 查看数据库默认存储引擎
show engines
3. 修改数据库默认使用的存储引擎
vim /etc/my.cnf
[mysqld]
default-storage-engine=myisam
systemctl restart mysqld
4. 查看\设置\修改表的存储引擎
show create table 表名; //查看
careate table 表名 (字段列表)engine=存储引擎名; //设置
alter table 表名 engine=存储引擎名; //修改
5. innodb特点
共享表空间(5.5版本之后)
支持事务、事务回滚、外键
锁粒度:行级锁
适合写操作多的表,因为并发访问量大。
t222.frm 表结构
t222.idb 索引信息+数据
6. myisam特点
独享表空间
不支持事务、事务回滚、外键
锁粒度:表级锁
适合读操作多的表,因为节省资源。
t1.frm 表结构 desc t1;
t1.MYI 索引信息 index
t1.MYD 表数据 select * from t1;
7. 锁类型
读锁(共享锁):执行select操作,支持并发读
写锁(排它锁):执行insert,update,delete操作,上锁期间其他进程不能读写
8. 事务
事务:访问数据时从开始连接、操作记录、断开连接的过程。
事务回滚:在事务执行过程中,有任意一步没有执行成功,就恢复之前的所有操作。
事务特性:ACID
Atomic-----------> 原子性
Consistency------> 一致性
Isolation--------> 隔离性
Durability-------> 持久性
show variables like "autocommit";
set autocommit=off;
9. MySQL体系结构
管理工具:提供数据库服务的软件自带的命令
连接池:响应客户端连接
sql接口:把sql命令传递给mysql服务
分析器:检查执行的sql命令
优化器:自动对执行的sql命令做优化
查询缓存:服务启动时,空间从物理内存划分出来,存储曾经查询的记录
存储引擎:处理表的处理器,或者称为表格类型
文件系统:存储数据的硬盘
二. 数据导入导出
1. 查询默认搜索目录
show variables like "secure_file_priv";
默认路径为: /var/lib/mysql-files/
2. 数据导入
load data infile “目录名/文件名” into table 库名.表名 FIELDS TERMINATED BY “分隔符” LINES TERMINATED BY “\n”;
将/etc/passwd导入到数据库中:
create table user(
name varchar(35),
password char(1),
uid int(2),
gid int(2),
comment varchar(100),
homedir varchar(60),
shell varchar(30),
index(name)
);
cp /etc/passwd /var/lib/mysql-files/
load data infile "/var/lib/mysql-files/passwd"
into table user
fields terminated by ":"
lines terminated by "\n";
3. 自定义数据导入导出文件默认目录
mkdir /mydata
chown mysql /mydata #将属主设置为mysql
cp /etc/passwd /mydata/
vim /etc/my.cnf
[mysqld]
secure_file_priv= /mydata
systemctl restart mysqld
4. 数据导出
sql查询命令 INTO OUTFILE "目录名/文件名" [ fields terminated by "符号" lines terminated by "符号"] ;
show variables like "secure_file_priv";
select * from userdb.user where id=18 into outfile "/var/lib/mysql-files/user1.txt";
system cat /var/lib/mysql-files/user.txt
三. 管理表记录(增 查 改 删)
1. insert into 插入
一次插入1条记录给所有字段赋值:
insert into 库名.表名 values(值列表);
insert into userdb.user
values
(50,"bob","x",2022,2022,"student","/home/bob","/bin/bash");
一次插入N条记录给所有字段赋值:
insert into 库名.表名 values(值列表),(值列表),(值列表);
insert into userdb.user
values
(60,"bob","x",2122,2122,"student","/home/bob","/bin/bash"),
(63,"lucy","x",2322,2322,"student","/home/lucy","/bin/bash");
插入记录时只给指定的字段赋值:
insert into 库名.表名(字段名列表)values(值列表);
insert into 库名.表名(字段名列表)values(值列表),(值列表),(值列表);
insert into userdb.user(name,uid) values("tom",2300),("alic",2987);
2. select 查询
select name,uid from userdb.user where uid<10;
select * from userdb.user where id<=5;
3 update 修改
批量修改
update 库.表 set 字段名="值",字段名=值;
update userdb.user set linuxser=100,linuxsys=80;
修改与条件匹配的记录字段的值;
update 库.表 set 字段名="值",字段名=值 where 条件;
update userdb.user set age=18 where name="root";
update userdb.user set shell=null where name="bin"; //shell为NULL
update userdb.user set shell="" where name="root"; //shell有值
4. delete 删除
delete from userdb.user where shell is null;
三. 基本匹配条件
1. 数值比较 > >= < <= = !=
select name,uid from userdb.user where uid<10;
select * from userdb.user where id<=5;
2. 字符比较 = !=
select name from userdb.user where name="apache";
select name,uid from userdb.user where shell="/bin/bash";
3. 范围内查找
in (值列表) 在...里
not in (值列表) 不在...里
between...and... 在...之间
select name from userdb.user where uid in (10,20,30);
select name,uid from userdb.user where shell not in ("/bin/bash","/sbin/nologin");
select name,uid from userdb.user where uid between 10 and 20;
4. 多个查询条件
逻辑与 and 多个查询条件必须同时成立
逻辑或 or 多个查询条件某一个条件成立就可以
逻辑非 ! 取反
select name,uid from userdb.user
where
name="root" or name="bin" and uid=1;
select name,uid from userdb.user
where
name="root" and name="bin" or uid=1;
5. DISTINCT 不显示重复的值
select distinct shell from userdb.user;
select distinct shell from userdb.user where uid<1000;
6. 空匹配
is null 匹配空
is not null 匹配非空
select id , name from userdb.user where name is null;
select id , name from userdb.user where name is not null;
select id , name from userdb.user where name="null";
select id , name from userdb.user where name="";
四. 高级匹配条件
1. 模糊匹配
(1) like ‘表达式’
% ---------> 零个或多个字符
_ ----------> 任意单个字符
select name,uid from userdb.user where name like '%a%';
select id,name,uid from userdb.user where name like '%';
select id,name,uid from userdb.user where name like '_a_';
(2) 正则匹配
regexp '正则表达式'
^ $ . * []
select name from userdb.user where name regexp '^[0-9]';
select name from userdb.user where name regexp '[0-9]$';
select name from userdb.user where name regexp '.....'; //大于等于5个字符
select name from userdb.user where name regexp '^.....$'; //等于5个字符
select id,name,uid from userdb.user where name regexp '^r.*t$';
select id,name,uid from userdb.user where name regexp '.*';
(3) 四则运算 + - * / %
select name,linuxsys,linuxser,linuxsys+linuxser as zcj
from userdb.user where name="root";
select name,linuxsys,linuxser,linuxsys+linuxser as zcj,
(linuxsys+linuxser)/2 as pjf from userdb.user where name="root";
select name,age,2017-age as s_year from userdb.user
where name="root";
(4) 聚集函数
sum(字段名) 求和
avg(字段名) 平均值
min(字段名) 最小值
max(字段名) 最大值
count(字段名) 个数
select sum(linuxsys),avg(linuxsys) from userdb.user where uid<=10;
select count(id),count(name) from userdb.user;
五. 操作查询结果
(1) 限制显示查询记录数
limit 数字 显示查询结果的前几行
limit 起始行(从0开始),总行数
select * from userdb.user limit 3;
select * from userdb.user where uid<=10 limit 5;
select * from userdb.user limit 1,3;
(2) 查询排序
order by 字段名 asc/desc 升序(默认)/降序
select name,uid,shell from userdb.user
where shell!="/bin/bash" and shell is not null order by uid;
select name,uid,shell from userdb.user
where shell!="/bin/bash" and shell is not null order by uid desc;
select name,uid,shell from userdb.user
where shell!="/bin/bash" and shell is not null order by uid desc limit 3;
(3) 查询分组
group by 字段名;
select shell from userdb.user where uid>=10 group by shell;
select gid from userdb.user group by gid having gid>100;
(4) where嵌套查询(把内层的查询结果作为外层查询的查询条件)
select 字段名列表 from 库.表 where 条件 (select 字段名列表 from 库.表);
select name,linuxsys from userdb.user
where linuxsys <
(select avg(linuxsys) from userdb.user);
select name,linuxsys from userdb.user
where linuxsys >
(select avg(linuxsys) from userdb.user);
select name from userdb.user
where name in
(select user from mysql.user where user="root" and host="localhost");