存储引擎
自带的软件功能程序,是表的处理器
mysql 5.0/5.1 myisam | 支持表级锁,不支持事务回滚,外键,表名.frm(表结构) 表名.myi 表名.myd |
mysql 5.5/5.6及以上 innodb | 支持行级锁,支持事务回滚,外键,表名.frm(表结构),表名.ibd(表空间),ibdata1,ib_logfile0/1 |
列出可用的存储引擎类型
Mysql> show engines\G;
*************************** 1. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 2. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
...
*************************** 9. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Percona-XtraDB, Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 10. row ***************************
Engine: Aria
Support: YES
Comment: Crash-safe tables with MyISAM heritage
Transactions: NO
XA: NO
Savepoints: NO
修改表的存储引擎
建表时手动指定,如果未指定,使用默认引擎
mysql> create database db;
Query OK, 1 row affected (0.00 sec)
mysql> use db;
Database changed
mysql> create table test( id int(4) ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
mysql> show create table test\G;
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`id` int(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
可以修改默认存储引擎
msyql> system vim /etc/my.cnf
[mysqld]
...
default-storage-engine=mysiam #引擎名称根据实际需要修改
存储引擎特点
MyISAM | 支持表级锁,不支持事务、事务回滚、外键 相关文件有表名.frm 表名.MYI 表名.MYD |
---|---|
InnoDB | 支持行级锁定,支持事务、事务回滚、外键 相关文件有 表名.frm 表名.ibd ibdata1 ib_logfile0 ib_logfile1 |
MySQL锁机制
查看当前的锁状态 检查Table_lock开头的变量,% 作通配符
mysql> show status like 'Table_lock%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Table_locks_immediate | 36 |
| Table_locks_waited | 0 |
+-----------------------+-------+
2 rows in set (0.00 sec)
事务特性(ACID)
Atomic-原子性:事务的整个操作是一个整体,不可分割,要么全部成功,要么全部失败
Consistency-一致性:事务操作的前后,表中的记录没有变化
Isolation-隔离性:事务操作是相互隔离不受影响的
Durability-持久性:数据一旦提交,不可改变,永久改变表数据
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> select * from test;
Empty set (0.00 sec)
mysql> insert test values(231);
Query OK, 1 row affected (0.00 sec)
mysql> rollback; //数据回滚
Query OK, 0 rows affected (0.01 sec)
MariaDB [db]> commit; //提交数据
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test;
Empty set (0.00 sec)
mysql> insert test values(123);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test;
+------+
| id |
+------+
| 123 |
+------+
1 row in set (0.00 sec)
匹配条件
基本匹配
数值比较(字段类型必须为数据数值类型) = , > >= , < <=, !=(不等于)
字符比较(字段类型必须为字符) = ,!=, IS NULL(匹配空), IS NOT NULL(非空)
逻辑匹配(多个判断条件时用) OR(逻辑或); AND(逻辑与); !(逻辑非); () (提高优先级)
范围内匹配(匹配范围内的任意一个值) in (值列表) 在...里... ; not in (值列表)不在...里...;
between 数字1 and 数字 2 在 ...之间 ...; distinct 字段名 去重显示
高级匹配
模糊查询 where 字段名 like ‘通配符’ (注: _ 匹配单个字符 % 匹配0~N个字符)
mysql> select * from test where id like '1_3';
+------+
| id |
+------+
| 123 |
+------+
1 row in set (0.00 sec)
mysql> select * from test where id like '1%';
+------+
| id |
+------+
| 123 |
+------+
1 row in set (0.00 sec)
正则表达式 where 字段名 regexp ‘正则表达式’
(正则元字符 ^匹配值行首 $匹配值结尾 .匹配任意单个字符 *匹配子表达式任意次 [] 字符集合,匹配其中任意 | 或)
mysql> select * from test where id regexp '^1';
+------+
| id |
+------+
| 123 |
+------+
1 row in set (0.00 sec)
四则运算(字段必须为数值类型) + - * / %
操作查询结果
1.聚集函数:avg(字段名)统计字段平均值,sum(字段名) 字段之和 , min(字段名) 字段最小,max(字段名) 字段最大,count(字段名) 个数
2.查询结果排序:SQL查询 order by 字段名-通常是数值 [asc | desc ]-升/降序;
3.查询结果分组:SQL查询 group by 字段名-通常是字符类型;
4.查询结果过滤:
SQL查询 having 条件表达式;
SQL查询 where 条件 having 条件表达式;
SQL查询 group by 字段名 having 条件表达式;
5.限制查询结果显示行数
SQL查询 limit N; 显示查询结果前N条记录
SQL查询 limit N,M; 显示指定范围内的查询结果
SQL查询 where 条件查询 limit 3; 显示查询结果前3条记录
SQL查询 where 条件查询 limit 3,3; 从第四条开始,共显示3条
mysql> CREATE TABLE student(
-> name varchar(12) NOT NULL,
-> gender enum('boy','girl') DEFAULT 'boy',
-> age int(3) NOT NULL
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO student VALUES
-> ('Jim','girl',24),
-> ('Tom','boy',21),
-> ('Lily','girl',20),
-> ('Jerry','boy',27),
-> ('Mike','boy',21)
-> ;
Query OK, 5 rows affected (0.06 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> SELECT count(*) FROM student;
+----------+
| count(*) |
+----------+
| 5 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT avg(age),max(age),min(age) FROM student;
+----------+----------+----------+
| avg(age) | max(age) | min(age) |
+----------+----------+----------+
| 22.6000 | 27 | 20 |
+----------+----------+----------+
1 row in set (0.00 sec)
mysql> SELECT count(gender) FROM student WHERE gender='boy';
+---------------+
| count(gender) |
+---------------+
| 3 |
+---------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM student WHERE age>21;
+-------+--------+-----+
| name | gender | age |
+-------+--------+-----+
| Jim | girl | 24 |
| Jerry | boy | 27 |
+-------+--------+-----+
2 rows in set (0.00 sec)
mysql> SELECT * FROM student WHERE age BETWEEN 20 and 24;
+------+--------+-----+
| name | gender | age |
+------+--------+-----+
| Jim | girl | 24 |
| Tom | boy | 21 |
| Lily | girl | 20 |
| Mike | boy | 21 |
+------+--------+-----+
4 rows in set (0.00 sec)
ysql> SELECT * FROM student WHERE age < 23 AND gender='girl';
+------+--------+-----+
| name | gender | age |
+------+--------+-----+
| Lily | girl | 20 |
+------+--------+-----+
1 row in set (0.00 sec)
mysql> SELECT * FROM student WHERE name IN
-> ('Jim','Tom','Mickey','Minnie');
+------+--------+-----+
| name | gender | age |
+------+--------+-----+
| Jim | girl | 24 |
| Tom | boy | 21 |
+------+--------+-----+
2 rows in set (0.00 sec)
mysql> SELECT 1234*5678;
+-----------+
| 1234*5678 |
+-----------+
| 7006652 |
+-----------+
1 row in set (0.00 sec)
mysql> SELECT name,age+15 FROM student;
+-------+--------+
| name | age+15 |
+-------+--------+
| Jim | 39 |
| Tom | 36 |
| Lily | 35 |
| Jerry | 42 |
| Mike | 36 |
+-------+--------+
5 rows in set (0.00 sec)
mysql> SELECT * FROM student GROUP BY age ASC;
+-------+--------+-----+
| name | gender | age |
+-------+--------+-----+
| Lily | girl | 20 |
| Tom | boy | 21 |
| Jim | girl | 24 |
| Jerry | boy | 27 |
+-------+--------+-----+
4 rows in set (0.00 sec)