MySQL存储引擎 、 数据导入导出 管理表记录 匹配条件
mysql体系结构
存储引擎组成模块：
连接池：对客户端访问数据库服务器时检查自己有池、有空闲进程、有内存工作，方可连接。
sql接口
分析器：检查是否有错命令
优化器：加快处理速度
查询缓存：存的查询过的数据，当mysql服务器接受到请求时先去查缓存的数据，找到就直接给客户。没有就去表里找文件在/var/lib/mysql/库名/下
存储引擎
文件系统
管理工具
生产环境多用的默认引擎：
mysql 5.0/5.1 --->myisam
mysql 5.5/5.6 --->innodb
mysql> show create table teadb.t3; //查看当前表的引擎
+-------+----------------------------------------------------------------------------------------+
|Table | Create Table |
+-------+-----------------------------------------------------------------------------------------+
| t3 | CREATE TABLE `t3` (
`age` tinyint(3) unsigned DEFAULT NULL,
`pay` float(7,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------+
mysql> show engines; //查看当前支持的存储引擎
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
永久改默认引擎
Vim /etc/my.cnf
[mysql]
default-storage_engine=myisam
查看某变量值状态（优化使用的,了解）
mysql> show status like "%lock%"; //不加like显示所有
mysql> show variables like "autocommit"; //自动提交功能，回车不提交
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.00 sec)
mysql> set autocommit=off; //关闭自动提交，（当前设置）一般不设开机启动
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like "autocommit";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
myisam存储引擎
主要特点：支持表级锁
不支持事务、事务会滚、外键
相关表文件： .frm：存的是表的结构
.MYI：存的是表的数据
.MYD：存的是索引信息
innodb存储引擎
主要特点：支持行级锁定
支持事务、事务会滚、外键
相关的表文件：.frm:表的结构
.ibd:存的表数据和索引信息
ibdata1、ib_logfileo、ib_logfile1:这三个文件存的是 事务日志
事务：记录从开始访问到结束的过程
mysql锁机制
锁粒度
表级锁：一次直接对整张表进行加锁
行级别锁：只锁定某一行
页级锁：对整个页面（mysql管理数据的基本存储单位）进行加锁
事务特性（ACID）
atomic:原子性
事务的整个操作是一个整体，不可分割，要么全部成功，要么全部失败。
consistency:一致性
事务操作的前后，表中的记录没有变化。
isolatin:隔离性
事务操作是相互隔离不受影响的。
durability:持久性
数据一旦提交，不可改变，永久改变表数据。
数据会滚
写入数据后提交（commit），再写入数库不提交只回车，再数回滚（rollback），可恢复到原来的样子
隔离性：
A用户写入数据没有提交则，B用户访问还是原来的数据，只有用户A提交后，B用户查看数据才会更新。
用查询操作多的表适合用myisam存储引擎，可以节省系统的资源（当改某行数据时，全表加锁，只需要锁一次，节省cpu）
用写操作多的表适用innodb引擎，可以加大并发访问量（多个客户访问时，会锁多次，怎加cup负载）
数据导入，默认要先存到/var/lib/mysql-files/目录下
创建表：
mysql> create table user(name char(50),password char(1),uid int(1),gid int(1),comment varchar(150),homedir varchar(100),shell char(30));
导入数据：
mysql> load data infile "/var/lib/mysql-files/passwd" into table teadb.user fields terminated by ":" lines terminated by "\n";
//passwd文件“：”隔开，表的内容与格式一样，否则报错
mysql> system cp /etc/passwd /var/lib/mysql-files/ //加system可执行系统命令
mysql> system ls /var/lib/mysql-files
passwd
mysql> alter table user add id int(2) primary key auto_increment first;
修改搜索路径（导入文件）：
mysql> system mkdir /mydir
mysql> system chown mysql /mydir
mysql> system chown mysql /mydir
mysql> system ls -ld /mydir
drwxr-xr-x. 2 mysql root 6 10月 17 14:03 /mydir
[mysqld]
secure_file_priv=/mydir
mysql> system systemctl restart mysqld
mysql> show variables like "secure_file_priv";
+------------------+---------+
| Variable_name | Value |
+------------------+---------+
| secure_file_priv | /mydir/ |
+------------------+---------+
导出数据，只有表内容没有表字段，默认不指定格式，空格用一个tab键距离
授权的目录才可以导出或者在导入目录里
mysql> select * from teadb.t3 into outfile "/mydir/a.txt";
mysql> select * from teadb.t3 into outfile "/mydir/b.txt"fields terminated by "##" lines terminated by "***";
##为空格
***为换行符号
mysql> system cat /mydir/*
18 38000.88
18##38000.88***mysql>
管理表记录
添加表记录
mysql> insert into user values (43,"bob","x",3003,3003,"test user","/home/bob","shell/bash");
单独字段插入多条记录
mysql> insert into user(name,uid,shell) values("yu",123,"/bin/bash"),("lala",111,"/bin/bash"),("long",212,"/bin/bash");
查询表记录
查询小范围的内容，按所给值前后顺序排序
mysql> select name,shell,homedir from user where shell="/bin/bash";
+------+-----------+------------+
| name | shell | homedir |
+------+-----------+------------+
| root | /bin/bash | /root |
| lisi | /bin/bash | /home/lisi |
| han | /bin/bash | NULL |
| yu | /bin/bash | NULL |
| lala | /bin/bash | NULL |
| long | /bin/bash | NULL |
+------+-----------+------------+
查询时给字段起一个别名称
mysql> select name username,uid id_uid from user;
+---------------------+--------+
| username | id_uid |
+---------------------+--------+
| root | 0 |
| bin | 1 |
基本匹配，高级匹配
基本数值比较查询
mysql> select name,uid,gid from user where uid=gid;
+-----------------+-------+-------+
| name | uid | gid |
+-----------------+-------+-------+
| root | 0 | 0 |
| bin | 1 | 1 |
| daemon | 2 | 2 |
mysql> select name,uid,gid from user where uid<=10;
+----------+------+------+
| name | uid | gid |
+----------+------+------+
| root | 0 | 0 |
| bin | 1 | 1 |
| daemon | 2 | 2 |
| adm | 3 | 4 |
| lp | 4 | 7 |
| sync | 5 | 0 |
| shutdown | 6 | 0 |
| halt | 7 | 0 |
| mail | 8 | 12 |
+----------+------+------+
mysql> select * from user where id=3;
+----+--------+----------+------+------+---------+---------+---------------+
| id | name | password | uid | gid | comment | homedir | shell |
+----+--------+----------+------+------+---------+---------+---------------+
| 3 | daemon | a | 2 | 2 | daemon | /sbin | /sbin/nologin |
+----+--------+----------+------+------+---------+---------+---------------+
mysql> select * from user where name="mysql";
+----+-------+----------+------+------+--------------+----------------+------------+
| id | name | password | uid | gid | comment | homedir | shell |
+----+-------+----------+------+------+--------------+----------------+------------+
| 42 | mysql | a | 27 | 27 | MySQL Server | /var/lib/mysql | /bin/false |
+----+-------+----------+------+------+--------------+----------------+------------+
mysql> select name,shell from user where shell!="/bin/bash"; //不等于/bin/bash的
+---------------------+----------------+
| name | shell |
+---------------------+----------------+
| bin | /sbin/nologin |
| daemon | /sbin/nologin |
| adm | /sbin/nologin |
mysql> insert into user(id ,name)values(48,"null"),(49,null),(50,"");
赋值时 ： "null"是普通字符 null：为空 ""为0字符（什么都没有）
逻辑匹配（多个匹配条件）
or ：某个条件成立即可
and ：多个条件必须同时成立
！ 或 not ：取反
条件可加多个
与或可同用，会先做“与”判断，再做“或”判断
in (值列表) 在。。里。。
not in（值列表） 不在。。里。。
between 数字1 and 数字2 在。。。之间。。
distinct 字段名 去重显示
mysql> select name from user where name in ("mysql");
+-------+
| name |
+-------+
| mysql |
+-------+
mysql> select name from user where uid not in (10,20,30,40);
mysql> select id ,name,shell from user where id between 10 and 20;
+----+-----------------+---------------+
| id | name | shell |
+----+-----------------+---------------+
| 10 | operator | /sbin/nologin |
| 11 | games | /sbin/nologin |
| 12 | ftp | /sbin/nologin |
| 13 | nobody | /sbin/nologin |
| 14 | systemd-network | /sbin/nologin |
| 15 | dbus | /sbin/nologin |
| 16 | polkitd | /sbin/nologin |
| 17 | libstoragemgmt | /sbin/nologin |
| 18 | rpc | /sbin/nologin |
| 19 | colord | /sbin/nologin |
| 20 | saslauth | /sbin/nologin |
+----+-----------------+---------------+
mysql> select distinct shell from user;
+----------------+
| shell |
+----------------+
| /bin/bash |
| /sbin/nologin |
| /bin/sync |
| /sbin/shutdown |
| /sbin/halt |
| /bin/false |
| shell/bash |
| NULL |
+----------------+
模糊查询 关键字：like
mysql> select name from user where name like '____'; //任意4位数
mysql> select name from user where name like 'r__t';
mysql> select name from user where name like 'r%t'; //中间任意值
mysql> select name from user where name like '____%'; //至少4位数
mysql> select name from user where name like '%'; //匹配所有以及0值，不匹配NULL空值
正则表达式查询 关键字：regexp
mysql> select name from user where name regexp '[0-9]'; //匹配范围
+-------+
| name |
+-------+
| yaya1 |
| yaya2 |
| yaya3 |
| yaya4 |
+-------+
mysql> select name,uid from user where uid regexp '^...$'; //只能3个字符
mysql> select name, uid from user where uid regexp '...'; //不少于3个字符
mysql> select name ,uid from user where name regexp '^a|^r'; //a或者r开头
四则运算（字段类型必须数字 整数） //uid加1
+ - * / %
mysql> update user set uid=uid+1 where id<=10;
mysql> alter table user add ageee tinyint(2) unsigned default 19 after name;
mysql> select name,ageee,2018-ageee start from user where name="root";
+-----------+-------------+--------------+
| name | ageee | start |
+-----------+-------------+--------------+
| root | 19 | 1999 | //显示自定义计算后的表start
+-----------+-------------+--------------+
1 row in set (0.00 sec)
聚集函数
mysql内置数据统计函数
avg(字段名) //统计字段平均值
sum(字段名) //统计字段之和
min(字段名) //统计字段最小值
max(字段名) //统计字段最大值
count(字段名) //统计字段值个数
mysql> select avg(uid) from user;
+-----------+
| avg(uid) |
+-----------+
| 1928.0217 |
+-----------+ //统计字段平均值
1 row in set (0.00 sec)
mysql> select max(uid) from user;
+----------+
| max(uid) |
+----------+
| 65534 |
+----------+
1 row in set (0.00 sec)
mysql> select min(uid) from user;
+----------+
| min(uid) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
mysql> select sum(uid) from user;
+----------+
| sum(uid) |
+----------+
| 88689 |
+----------+
1 row in set (0.00 sec)
mysql> select count(ageee) from user where shell!="/bin/bash";
+--------------+
| count(ageee) |
+--------------+
| 41 |
+--------------+
desc 降序
mysql> select name,uid from user where uid regexp '^...$' order by uid desc;
asc升序
mysql> select name,uid from user where uid regexp '^...$' order by uid asc;
分组 group by 字段名
给相同的字符分为一组
mysql> select gid from user group by gid;
mysql> select gid from user where id<=20
group by gid;
having查询过滤
msql> select id,name from user where name like '%' having name="";
+----+------+
| id | name |
+----+------+
| 50 | |
+----+------+
mysql> select id,name from user where name like '%' and name="";
+----+------+
| id | name |
+----+------+
| 50 | |
+----+------+
//having查找是将将过滤查询后的表再进行查找(提高效率)，and是要将表查两遍
控制查询结果显示行数limit（默认输出所有查询结果）
mysql> select id,name,shell from user limit 10;
mysql> select id,name,shell from user limit 9,6;
限制查询结果显示行数
不指定显示全部
mysql> select name,shell from user where shell!="/bin/bash"
-> limit 3;
//显示表的前三条记录(1,2,3)
mysql> select name,shell from user where shell!="/bin/bash"
-> limit 3,3;
//显示表第三条下一条记录的后三条记录(4,5,6)
浙公网安备 33010602011771号