定义:mysql服务软件自带的功能程序,处理表的处理器;
mysql> show engines; #查看可用的存储引擎
Supports transactions, row-level locking, and foreign keys
最常用的存储引擎 : innodb myisam
设置mysql服务默认的存储引擎: 只是影响新建的表
- mysql> SET default_storage_engine=MyISAM; //改用MyISAM引擎 ,只是临时修改默认引擎
[root@host50 ~]# vim /etc/my.cn # 在配置文件修改 永久修改
[m ysqld] # 在[mysqld] 下面新加的
default-storage-engine=myisam
mysql> use db3; # 进入udb3库
mysql> create table t1(name char(10));
mysql> show create table t1; #查看新建表中的存储引擎
+-------+-------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`name` char(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
innodb 特点:
表名.frm 表结构
表名.ibd:表数据+ 表索引
支持行级锁定,支持事务 ,事务回滚, 外建
事务日志文件 /var/lib/mysql
ibdata1
ib_logfile1
ib_logfile0
myisam 特点:
表名.frm 表结构
表名.MYD 表数据
表名.MYI 表索引
支持表级锁
不支持事务,不支持回滚,不支持外键
mysql 锁机制
锁:解决并发访问的冲突问题
锁类型:
读锁 (select) 共享锁: 可以支持并发读
写锁(互斥锁,排他锁)insert,update:是读占锁,上锁期间其他线程不能读表或写表
锁粒度:加锁时的锁的范围
表级锁:给一表加锁(范围大)myisam
行级锁:仅仅给被访问的每一行分别加锁(范围小) innodb
事务:一次访问从建立连接到断开连接的 整个过程
事务回滚: 在执行事务时任意一步失败,能恢复所有的操作。 rollback
事务特点:ACID
Atomic :原子性 Consistency:一致性 lsolation :隔离性,durability 持久性
mysql> show variables like "%commit%"; #查看提交状态
mysql> set autocommit=off; # 关闭自动提交
mysql> show variables like "%commit%";
mysql> insert into t3 values (1); # 往t3表里面填入数据
mysql> select * from t3; #如果打开第二个终端,这个命令是显示空的
+------+
| id |
+------+
| 1 |
| 2 |
+------+
mysql> select * from t3; # 打开第二个终端显示为空
Empty set (0.00 sec)
mysql> commit; # 提交数据后 第二个终端mysql> select * from t3; 显示数据
mysql> select * from t3; #删除t3表里面的数据
mysql> rollback; # 在未敲commit 时进行事务回滚,可以恢复数据
mysql> select * from t3;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
一。工作中建表如何决定表使用的是哪种存储引擎
select 读锁共享锁
查询访问多的表使用mysam存储引擎,节省系统资源 表级锁
写操作多 的表使用innodb存储引擎,并发访问量大; 行级锁
二。 数据导入到出 步骤: 1.建表 2导入数据
数据导入: 把系统文件内容存储在表里面;
命令格式: load data infile "目录/文件名" into table 库.表 fileds terminated by "分隔符号" line terminated by "\n"
数据导出: 把表记录保存到系统文件里;
命令格式:sql查询命令 into outfile "目录/文件名" fields terminated by "分隔符号" line terminated by "\n";
例子:将系统文件中的/etc/password的文件导入表里:
建立表格:
mysql> create table user(
-> name char(50),
-> password char(1),
-> uid int,
-> gid int,
-> comment char(50),
-> homedir char(100),
-> shell char(80)
-> );
mysql> desc user;
搜索路径:
mysql> show variables like "secure_file_priv"; # 查看默认使用的目录以及目录是否存在
+------------------+-----------------------+
| Variable_name | Value |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
[root@host50 db3]# cd /var/lib/mysql-files/
[root@host50 mysql-files]# cp /etc/passwd /var/lib/mysql-files/ #将需要导入的文件复制到数据库里面使用的目录
mysql> load data infile "/var/lib/mysql-files/passwd" into table db3.user fields terminated by ":" lines terminated by "\n";#将系统文件导入数据库里面所建立的user表中
mysql> select * from user;
mysql> alter table user add id int primary key auto_increment first; #增加字段id
例子二: 将数据库里面的表格数据导出到文件系统里:
mysql> select * from user where id <= 5 into outfile "/var/lib/mysql-files/a.txt"; # 可以不指定分隔符和换行符号,系统默认给出
mysql> select * from user where id <= 5 into outfile "/var/lib/mysql-files/a1.txt" fields terminated by "#"; # 指定列的分隔符是#
mysql> select * from user where id <= 5 into outfile "/var/lib/mysql-files/a2.txt" fields terminated by "#" lines terminated by " !!! "; # 将查找到的内容 导出到文件系统/var/lib/mysql-files/a2.txt中 此时指定的列分隔符是#,行的分隔符是 !!!
Query OK, 5 rows affected (0.00 sec)
[root@host50 ~]# cd /var/lib/mysql-files/
[root@host50 mysql-files]# ls
a1.txt a2.txt a.txt passwd # 此时该目录下拥有3个文件;
修改检索的目录:
[root@host50 mysql-files]# mkdir /myload #自定义目录
[root@host50 ~]# vim /etc/my.cnf
[mysqld]
secure_file_priv="/myload"
[root@host50 ~]# ll -ld /myload/
drwxr-xr-x. 2 root root 6 5月 7 16:24 /myload/
[root@host50 ~]# chown mysql /myload/ # 给自定义的目录的所属主为mysql
[root@host50 ~]# ll -ld /myload/
drwxr-xr-x. 2 mysql root 6 5月 7 16:24 /myload/
mysql> select * from user where id <= 5 into outfile "/myload/a3.txt" fields terminated by "#" lines terminated by " !!! ";
mysql> select * from user where uid < 100 limit 10; 输出10行
管理表记录 : 1增加表记录,2查询记录,3更新表记录,4删除表记录
(1)增加表记录
格式1: insert into 表名 values (字段值列表)
格式2:insert into 表名 values (字段值列表),(字段值列表);
mysql> insert into user values ( 42,"bob","x",2000,2000,"test user","/home/bob","/bin/bash");#添加一条表记录,给所有字段赋值
mysql> insert into user values ( 43,"bob","x",2000,2000,"test user","/home/bob","/bin/bash") , ( 44,"lucy","x",2000,2000,"test user","/home/bob","/bin/bash"), ( 45,"lilei","x",2000,2000,"test user","/home/bob","/bin/bash"); # 添加3条表记录,给所有字段赋值)
mysql> insert into user(name,uid,gid) values("tom",3000,3000),("you",4000,2000); #给name,uid,gid字段赋值,未给出的赋默认值
注意事项:
字段值要与字段类型相匹配
对于字符类型的字段,要用双引号或者是单引号括起来
依次给所有的字段赋值时,字段名可以省略
只给一部分字段赋值的时,必须明确给出对应的字段的名称;
(2)查询表记录 : select 字段名,字段名 from 表名 where 条件表达式 #
mysql> select name,uid,shell from db3.user where shell="/bin/bash"; # select 后面加的表示显示列内容,where后面是显示行的内容
+-------+------+-----------+
| name | uid | shell |
+-------+------+-----------+
| root | 0 | /bin/bash |
| lisi | 1000 | /bin/bash |
| bob | 2000 | /bin/bash |
| bob | 2000 | /bin/bash |
| lucy | 2000 | /bin/bash |
| lilei | 2000 | /bin/bash |
+-------+------+-----------+
6 rows in set (0.00 sec)
mysql> select * from user where id=10;
(3)更新表记录: update
update 表名 set 字段1=字段1值,字段2=字段2值 where 条件表达式 修改字段1 和字段2的值
mysql> update user set password="f",comment="student"; # 将user表里面的password字段的内容更新为 f, comment字段里面的内容更新为 student
mysql> select password,comment from user;
+----------+---------+
| password | comment |
+----------+---------+
| f | student |
| f | student |
| f | student |
| f | student |
mysql> select * from user where name="root";
+----+------+----------+------+------+---------+---------+-----------+
| id | name | password | uid | gid | comment | homedir | shell |
+----+------+----------+------+------+---------+---------+-----------+
| 1 | root | f | 0 | 0 | student | /root | /bin/bash |
+----+------+----------+------+------+---------+---------+-----------+
mysql> update user set password="x",comment="root" where name="root";
mysql> select * from user where name="root";
+----+------+----------+------+------+---------+---------+-----------+
| id | name | password | uid | gid | comment | homedir | shell |
+----+------+----------+------+------+---------+---------+-----------+
| 1 | root | x | 0 | 0 | root | /root | /bin/bash |
+----+------+----------+------+------+---------+---------+-----------
(4)删除 表记录
格式1: delect from 表名 where 条件表达式 #仅仅删除符合条件的记录
格式2: delete from 表名; #删除所有的表记录
mysql> delete from user where id>=43; # 删除id>=43的所有行
匹配条件 (update delete select )
基本匹配条件
数值比较:= > >= < <= !=
字符比较:= != is null is not null
逻辑匹配 : 多个条件使用时, or and ! ()提高优先级
mysql> select * from user where name="syn" and uid=20 and shell="/bin/bash";
mysql> select * from user where name="syn" or uid=20 or shell="/bin/bash";
范围内匹配: in (在,,里) not in (不在,,里) between (在,,之间) distinct (去重显示)
in ( 值列表)
not in (值列表)
between 数字1 and 数字2
distinct 字段名
mysql> select name from user where name in ( "root","mysql","sync","adm");
+-------+
| name |
+-------+
| root |
| adm |
| sync |
| mysql |
mysql> select name shell from user where shell not in("/bin/bash","/sbin/nologin");
+----------+
| shell |
+----------+
| sync |
| shutdown |
| halt |
| mysql |
+----------+
mysql> select name from user where uid between 100 and 200; #在user 表里面输出name字段里uid 从100到200 的内容
+-----------------+
| name |
+-----------------+
| systemd-network |
| abrt |
| rtkit |
| usbmuxd |
| qemu |
| pulse |
+-----------------+
mysql> select distinct shell from user; # 在user 表里面输出shell 字段里不重复的内容
+----------------+
| shell |
+----------------+
| /bin/bash |
| /sbin/nologin |
| /bin/sync |
| /sbin/shutdown |
| /sbin/halt |
| /bin/false |
+----------------+
6 rows in set (0.00 sec)
高级匹配条件
模糊查询 : where 字段名 like ‘通配符’
_ 匹配单个字符, % 匹配0~n 个字符
mysql> select name from user where name like '___'; #___表示3个下划线,即是_ _ _ ,输出name 字段里面的内容只有3个字符的
mysql> select name from user where name like '%a%'; # 输出name字段内容里面的是包含a的
mysql> select name from user where name like '_ _%_ _'; # 输出name字段内容里面含有4个字符以及4个字符以上的
正则表达式:
格式: where 字段名 regexp '正则表达式'
正则元字符 :^ $ . [] * |
mysql> select name from user where name regexp '^a|t$'; # 输出name 字段里面以a 开头或者以t结尾的内容
mysql> select name from user where name regexp '^a.*t$'; # 输出name 字段里面以a 开头以t结尾的内容,中间的内容是任意的
mysql> select name from user where name regexp '[0-9]';#
四则运算
字段必须是数字类型的 :+ - * / % (取余)
mysql> select name,uid,gid,uid+gid he from user where id <= 10;
+----------+------+------+------+
| name | uid | gid | he |
+----------+------+------+------+
| root | 0 | 0 | 0 |
| bin | 1 | 1 | 2 |
mysql> update user set uid=uid+1 where name="root";
mysql> update user set uid=uid+1;
mysql> select name,uid,gid,(uid+gid)/2 pjz from user where name="bin";
操作查询结果
聚集函数:服务内置的对数据统计的命令
avg(字段名) # 统计字段平均值
sum(字段名) # 统计字段之和
min(字段名) #统计字段内容里最大值
max(字段名) # 统计字段里面最大值
count(字段名) # 统计字段值个数
mysql> select max(uid) from user;
mysql> select min(uid) from user;
mysql> select name from user where shell!="/sbin/nologin";
mysql> select count(name) from user where shell!="/sbin/nologin";
mysql> select * from user; # 查询所有表的记录;
mysql> select count(*) from user; # 统计表记录个数; 所有行的行数,所有列里面的内容的个数
查询结果排序:
格式 : sql 查询命令 order by 字段名 [asc|desc]
sql 查询命令 group by 字段名
sql 查询命令 where 条件 having 条件表达式
sql 查询命令 group by 字段名 having 条件表达式子
mysql> select name,uid from user where uid <= 500 order by uid desc; # uid降序
mysql> select name,uid from user where uid <= 500 order by uid asc; # uid 升序
mysql> select shell from user where uid >= 10 group by shell;
mysql> select distinct shell from user where uid >= 10; # 绿色字体的查询结果是一样的,只是执行的动作不一样,后面那条比较耗费cpu
mysql> select name from user where shell!="/bin/bash" having name="nobody";
mysql> select name from user where name="nobody" and shell!="/bin/bash"; 蓝色这两条的操作结果是一样的,只是后面那条比较耗费cpu
限制查询结果行数:
格式 : sql 查询命令 limit N; 查询结果显示前N条记录
sql 查询命令 limit N,M //显示指定范围内的查询记录
sql 查询命令 where limit 3; // 查询结果显示前3条记录
sql 查询命令 limit 3,3 //从第4调开始,共显示3条记录
mysql> select name,uid from user where uid <= 10 limit 1;
mysql> select name,uid from user where uid <= 10 limit 3;
mysql> select name,uid from user where uid <= 10 limit 3,3;
mysql> select * from user where uid >= 100 and uid <=65535 order by uid desc limit 5;