一些数据库的使用技巧
为了保证数据的安全性,我们要经常对数据进行备份,为了保证数据库的效率,要对数据库或者一些语句或者硬件方面进行优化,这就要求我们要掌握一些基本的使用技巧。
MySQL怎么使用命令行备份数据
cd d:/mysql/bin
# cmd打开数据库的bin目录,在命令行输入以下命令,表示将root用户下的test数据库的user表进行备份
# 注意:在“>”前面没有空格
mysqldump -uroot -p12345678 test user> e: est.sql
# 为了安全起见,在-p那里不建议直接输上密码,可以留空,会给提示要输入密码
# 导出数据库test下的多个表
mysqldump -uroot -p12345678 test user1 user2 user3> e:zql.sql
# 导出所有表
mysqldump -uroot -p12345678 test> e:zql.sql
# 导出一个库
mysqldump -uroot -p12345678 -d test> e:zql.sql
# 导入数据使用source命令
先登录数据库
mysql -uroot -p
# 选择数据库
mysql>use test;
# 导入sql文件
mysql>source d: est.sql;
varchar和char的区别
- 定长和变长
- char 表示定长,长度固定,varchar 表示变长,即长度可变,当所插入的字符串超出它们的长度时,视情况来处理,如果是严格模式,则会拒绝插入并提示错误信息,如果是宽松模式,则会截取然后插入。如果插入的字符串长度小于定义长度时,则会以不同的方式来处理,如 char(10),表示存储的是 10 个字符,无论你插入的是
多少,都是 10 个,如果少于 10 个,则用空格填满。而 varchar(10),小于 10 个的话,则插入多少个字符就存多少个。varchar 怎么知道所存储字符串的长度呢?实际上,对于varchar 字段来说,需要使用一个(如果字符串长度小于 255)或两个字节(长度大于 255)
来存储字符串的长度。
- 存储的容量不同
- 对 char 来说,最多能存放的字符个数255,和编码无关。而 varchar 呢,最多能存放 65532 个字符。
IP 该如何保存
最简单的办法是使用字符串(varchar)来保存,如果从效率考虑的话,可以将 ip 保存
为整型(unsigned int),使用 php 或 mysql 提供的函数将 ip 转换为整型,然后存储即可。
PHP 函数:long2ip()
和 ip2long()
MySQL 函数:inet_ntop()
和 inet_pton
项目中优化 sql 语句执行效率的方法
- 尽量选择较小的列
- 将
where
中用的比较频繁的字段建立索引 select
子句中避免使用‘*’- 避免在索引列上使用计算、
not in
和<>等操作 - 当只需要一行数据的时候使用
limit 1
- 保证单表数据不超过 200W,适时分割表。针对查询较慢的语句,可以使用 explain 来分析该语句具体的执行情况。
数据库中的事务
事务(transaction)是作为一个单元的一组有序的数据库操作。如果组中的所有操作都成功,则认为事务成功,即使只有一个操作失败,事务也不成功。如果所有操作完成,事务则提交,其修改将作用于所有其他数据库进程。如果一个操作失败,则事务将回滚,该事务所有操作的影响都将取消。ACID 四大特性,原子性、隔离性、一致性、持久性。
常用的数据库引擎
InnoDB、BDB(BerkeleyDB)、Merge、Memory(Heap)、Example、Federated、
Archive、CSV、Blackhole、MyISAM、MaxDB 等等十几个引擎。
InnoDB 与 MyISAM 的区别:
InnoDB 存储引擎,主要面向 OLTP(Online Transaction Processing,在线事务处理)方面的应用,是第一个完整支持 ACID 事务的存储引擎(BDB 第一个支持事务的存储引擎,已经停止开发)。
特点:
行锁设计、支持外键;
支持类似于 Oracle 风格的一致性非锁定读(即:默认情况下读取操作不会产生锁);
InnoDB 将数据放在一个逻辑的表空间中,由 InnoDB 自身进行管理。从
MySQL4.1 版本开始,可以将每个 InnoDB 存储引擎的表单独存放到一个独立的
ibd 文件中;
InnoDB 通过使用 MVCC(多版本并发控制:读不会阻塞写,写也不会阻塞读)来获得高并发性,并且实现了 SQL 标准的 4 种隔离级别(默认为 REPEATABLE 级
别);
InnoDB 还提供了插入缓冲(insert buffer)、二次写(double write)、自适
应哈希索引(adaptive hash index)、预读(read ahead)等高性能和高可
用的功能;
InnoDB 采用了聚集(clustered)的方式来存储表中的数据,每张标的存储都按
主键的顺序存放(如果没有显式的在建表时指定主键,InnoDB 会为每一行生成
一个 6 字节的 ROWID,并以此作为主键);
InnoDB 表会有三个隐藏字段:除了上面提到了 6 字节的 DB_ROW_ID 外,还有
6 字节的 DB_TX_ID(事务 ID)和 7 字节的 DB_ROLL_PTR(指向对应回滚段的地
址)。这个可以通过 innodb monitor 看到;
MyISAM 存储引擎是 MySQL 官方提供的存储引擎,主要面向
OLAP(Online Analytical Processing,在线分析处理)方面的应用。
特点:
不支持事务,支持表所和全文索引。操作速度快;
MyISAM 存储引擎表由 MYD 和 MYI 组成,MYD 用来存放数据文件,MYI 用来存放
索引文件。MySQL 数据库只缓存其索引文件,数据文件的缓存交给操作系统本
身来完成;
MySQL5.0 版本开始,MyISAM 默认支持 256T 的单表数据
一个小SQL语句,查询一个表中两门及两门以上不及格同学的平均分
SELECT name,AVG(score),SUM(score<60) AS gk FROM grade GROUP BY name HAVING gk>=2;