本文共 5125 字，大约阅读时间需要 17 分钟。

一、mysql存储引擎

自带功能，处理表的处理器MySQL 5.0/5.1 ----> MyISAM

MySQL 5.5/5.6 ----> InnoDB

列出可用的存储类型

show engines;或show engines\G

 MyISAM   | DEFAULT | MyISAM storage engine   | NO    | NO   |NO   

默认存储引擎：InnoDB

mysql>show create table 表名\G;ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1

设置默认存储引擎 mysql>set default_storage_engine=myisam;

修改/etc/my.cnf

[mysqld] ..default-storage-engine=myisam

 

[root@localhoat~]#systemctl restart mysqld

可建表时指定引擎

> create table 表名(id int )engine=myisam

 

MyISAM存储引擎 特点：支持表级锁 相关文件：.frm(表结构)、 

.MYI(表索引)、 

.MYD 表数据  (/var/lib/mysql/表名/*.frm)

InnoDB存储引擎 特点：支持行级锁；支持事务、事务回滚、外键 相关文件： .frm 、 .ibd 、 ibdata1 、 ib_logfile0  、 ib_logfile1

MySQL锁机制

*锁:解决并发访问冲突问题

锁粒度

表级锁：一次直接对整张表进行加锁(范围大)myisam

行级锁：只锁某一行    (范围小)innodb 页级锁：对整个页面(MySQL管理数据的基本存储单位)进行加锁

锁类型

读锁(select)：支持并发读 写锁(insert update delete)：独占锁（互斥锁、排它锁），上锁期间其他线程无法读表或写表

查询访问多的表使用myisam存储引擎，节省系统资源--->表级锁 写操作多的表使用innodb存储引擎，并发访问量大------>行级锁

查看当前锁状态，%作通配符

mysql>show  status like 'Table_lock%';

+-------------------------------+-------+

| Variable_name             | Value | +--------------------------------+-------+ | Table_locks_immediate | 908   | | Table_locks_waited        | 0     | +--------------------------------+-------+

事务特性  ACID 1次访问从建立到断开连接 整个过程

Atomic :原子性

-事务整个操作是一个整体，不可分割，要么全部成功（或失败） Consistency：一致性 -事务操作的前后，表中的记录没有变化 Isolation：隔离性 -事务操作是相互隔离不受影响的 Durability：持久性 -数据一旦提交，不可改变，永久改变数据

事务回滚：在执行事务时任意一步失败，会恢复所有操作--->rolback;

mysql>show variables like "autocommit";查看提交状态

+--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | autocommit      | ON    | +---------------------+-------+

mysql>set autocommit=off;关闭自动提交

mysql>rolback;数据回滚 mysql>commit;提交数据

开2个终端测试

工作中建表如何决定表使用那种存储引擎？

select 读锁共享锁

查询访问多的表使用myisam存储引擎，节省系统资源。

写操作多的表使用innodb存储引擎，并发访问量大。

终端1]#  UPDATE  T1  SET  NAME=ZHANGSAN  where id<=5；

终端2]#  update t1 set name=lisi where id >=10;

提交前查看表记录的不同

二、数据导入导出

查看默认使用目录

mysql>show variables like "secure_file_priv";

+-----------------------------------------------+-------------------------+

| Variable_name                               |        Value                | +---------------------------------------------+--------------------------+ | secure_file_priv                               | /var/lib/mysql-files/ | +----------------------------------------------+--------------------------+ [root@localhost~]#ls -ld /var/lib/mysql-files/ drwxr-x---. 2 mysql mysql 72 5月   7 16:18 /var/lib/mysql-files/

设置目录

mkdir /mypriv  ; chown mysql:mysql /mypriv

vim /etc/my.cnf

[mysqld]

secure_file_priv="/mypriv"

重启mysqld

mysql> show variables like "secure_file_priv"

• 数据导入

load  data infile "目录/文件名(默认搜索路径)" into table 库.表

fields terminated by "分隔符" lines terminated by "\n"; 字段分隔符要与文件内的一致； 指定导入文件的绝对路径； 导入数据的表字段类型要与文件字段匹配； 禁用SELinux保护机制

• 数据导出

sql查询命令(select * from user) into outfile  "绝对路径/文件名(默认搜索路径下新建文件)" 

fields terminated by "分隔符" lines terminated by "\n";

导出内容由查询语句决定；

导出的是表中记录，不包括字段名； 禁用SELinyx

例子：

1.将/etc/passwd文件cp到搜索目录下，数据库建库Pass， 然后建好表结构(name,password,uid,gid,commit,homedir,shell), 再 mysql>load data infile "/mypriv/passwd" into table Pass.user ->fields terminated by ":" lines terminated by "\n";

最后加个id int not null priamry key auto_increment 方便查看

2.mysql>select * from Pass.user where uid<100  limit 10  into outfile  "/mypriv/user.txt" 可加fields terminated by ":"

lines terminated by "\n";

select name,homedir(列)  from user where(行) shell="/bin/bash";

三、管理表记录

字段值要与字段类型相匹配

1.增加表记录

--insert  into 表名(可加字段名列表)  values (字段值列表),(字段值列表)(N..)可多个表记录; #指出各项值所对应的字段,未赋值的字段应设置有默认值或者有自增填充属性或者允许为空，否则插入操作将会失败

2.查询 --select  字段1，..或*(所有)..字段N from 库.表名 where 条件表达式;

3.更新 --update 表名 set 字段1=字段1值，字段2=字段2值，字段N=字段N值 where  条件表达式;(若没有where会更新所有记录)

4.删除 --delete from 表名 where 条件表达式; --delete  from 表名;(删除所有表记录)

四、匹配条件

 

• 1.基本匹配条件

字段类型必须数据值类型（数值比较）

= >,>= >.>= !=

字符比较 = != is null is not null

逻辑匹配 or and ! ()  提高优先级，(5+6)/3先算括号内再算括号外

范围内匹配 in(值列表)  在..里.. not in (值列表)  不在..里.. between 数字1 and 数字2   在..之间.. distinct 字段名 去重显示 例：select * from user where name in("root","bin","sync");

----------------------------------------

• 2.高级匹配条件

1）模糊查询

where 字段名 like '通配符'  _  匹配单个字符； % 匹配任意多个字符 类似于  .*(正则，匹配任意字符多次,与regexp使用)

例：select * from user where name like 'r%' or name like '%t';

2）正则表达式

where 字段名 regexp '正则表达式' 正则元字符  ^  $ . [ ] * |  例：select * from user where name regexp '^r|t$';

 

• 3.四则运算

注：字段必须数值类型

+  加法 -  减法 *  乘法 /  除法 %  取余数(求模)

• 4.操作查询结果

1）MySQL内值数据统计函数

avg(字段名)    统计字段平均值

sum(字段名)    统计字段之和

min(字段名)    统计字段最小值

max(字段名)    统计字段最大值

count(字段名) 统计字段值个数

2）查询结果排序

SQL查询 order by 字段名(数值类型字段) asc(升序)|desc(降序);

3）查询结果分组

SQL查询 group by 字段名(字符类型字段);

例：select shell from user where uid>=10  group by shell; 或  select distinct shell from user where uid>=10; 结果一样但过程不一样

4）查询结果过滤

SQL查询 having 条件表达式;

SQL查询 where 条件 having 条件表达式;

SQL查询 group by 字段名 having 条件表达式;

注：SQL查询出的结果再由having查询一遍

例：select name from user where shell !="/bin/bash"  having name="nobody"; 表里查询结果再过滤一遍(节省CPU) 或 select name from user where shell !="/bin/bash"  and name="nobody; 每行查询符合条件

5）限制查询结果显示行数

SQL查询 limit N; 显示查询 结果前N条记录

SQL查询 limit N,M; 显示指定范围内的查询记录 SQL查询 where 条件查询 limit 3;  显示查询结果前3条记录 SQL查询 where 条件查询 limit 3,3; 从第4条开始，共显示3条  (0开始计数，0就是1,1是2..)

----------------------------------------------------------------------------------------------------

导入数据要与字段类型匹配(alter table 表 modify 字段名 类型..)

导出对应的目录要让mysql有w权限(默认搜索路径)

 

转载地址：http://iriqi.baihongyu.com/