SQL语句实践
1. DML语句之管理表中的数据
1、往表中插入数据
在插入数据前,先新建一个简单的测试表test:
create table test(id int(4) NOT NULL AUTO_INCREMENT, name char(20) NOT NULL, PRIMARY KEY(id));
desc test;
往表中插入数据有几种不同的语法
第一种:按规则制定所有的列名,并且每列都插入值:
insert into test(id,name) values(1,'oldboy');
第二种:只在name列插入值,由于id列是自增的,所以可以这样操作:
insert into test(name) values('oldgirl');
也可以执行下面的语句来实现,注意,这两种情况只选择一条执行:
insert into test(id,name) values(null,'zhangsan');
第三种:如果不指定列,就要按规则为每列都插入恰当的值:
insert into test values(3,'inca');
第四种:批量插入数据的方法,可提升效率:
insert into test values(4,'zuma'),(5,'kaka');
下面是插入数据的实践:
mysql> use oldboy;
Database changed
mysql> drop table test;
Query OK, 0 rows affected (0.03 sec)
mysql> create table test(id int(4) NOT NULL AUTO_INCREMENT, name char(20) NOT NULL, PRIMARY KEY(id));
Query OK, 0 rows affected (0.06 sec)
mysql> desc test;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | | NULL | |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
通过以下命令插入第一行,请注意语法的细微不同:
mysql> insert into test(id,name) values(1,'oldboy');
Query OK, 1 row affected (0.02 sec)
mysql> insert into test(name) values('oldgirl');
Query OK, 1 row affected (0.02 sec)
mysql> insert into test values(3,'inca');
Query OK, 1 row affected (0.04 sec)
mysql> insert into test values(4,'zuma'),(5,'kaka');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from test; ---查看插入后得结果
+----+---------+
| id | name |
+----+---------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
| 4 | zuma |
| 5 | kaka |
+----+---------+
5 rows in set (0.00 sec)
mysql> delete from test; ---全部删除
Query OK, 5 rows affected (0.00 sec)
mysql> select * from test;
Empty set (0.00 sec)
mysql> INSERT INTO `test` VALUES (1,'oldboy'),(2,'oldgirl'),(3,'inca'),(4,'zuma'),(5,'kaka'); ---批量重新插入
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from test;
+----+---------+
| id | name |
+----+---------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
| 4 | zuma |
| 5 | kaka |
+----+---------+
5 rows in set (0.00 sec)
退出数据库,备份上述数据,留着备用:
mysql> quit
Bye
[root@oldboy ~]# mysqldump -uroot -poldboy123 -B oldboy >/opt/bak.sql
Warning: Using a password on the command line interface can be insecure.
备份后检查一下备份的SQL数据内容,同时过滤无用信息:
[root@oldboy ~]# egrep -v "#|\/\^$|--|^\/" /opt/bak.sql
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `oldboy` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `oldboy`;
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
LOCK TABLES `test` WRITE;
INSERT INTO `test` VALUES (1,'oldboy'),(2,'oldgirl'),(3,'inca'),(4,'zuma'),(5,'kaka');
UNLOCK TABLES;
可以看到上面备份的语句,就是我们执行的语句内容
2、查询表中的数据
查看表test中的所有数据,可采用如下两种方法
方法1:进入指定库后再查询(常用):
mysql> use oldboy
Database changed
mysql> select * from test;
+----+---------+
| id | name |
+----+---------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
| 4 | zuma |
| 5 | kaka |
+----+---------+
5 rows in set (0.00 sec)
方法2:直接在库外,使用“库.表”的方式查询:
mysql> select * from oldboy.test; ---用点号分隔库和表,其中,这里的"*"表示所有字段
+----+---------+
| id | name |
+----+---------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
| 4 | zuma |
| 5 | kaka |
+----+---------+
5 rows in set (0.00 sec)
可通过以下命令查询mysql库user表对应user和host列的用户:
mysql> select user,host from mysql.user;
+--------+---------------------------+
| user | host |
+--------+---------------------------+
| root | 127.0.0.1 |
| blog | 192.168.9.% |
| test | 192.168.9.% |
| test1 | 192.168.9.% |
| test | 192.168.9.0/255.255.255.0 |
| root | localhost |
| system | localhost |
| test | localhost |
+--------+---------------------------+
8 rows in set (0.03 sec)
查看表test中的前2行数据:
mysql> select * from test limit 2;
+----+---------+
| id | name |
+----+---------+
| 1 | oldboy |
| 2 | oldgirl |
+----+---------+
2 rows in set (0.00 sec)
mysql> select * from test limit 0,3; ---从第0行开始查,查3行记录
+----+---------+
| id | name |
+----+---------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
+----+---------+
3 rows in set (0.00 sec)
查询id为1的行:
mysql> select * from test where id=1; ---查询数字列无需加引号,但是看起来是数字,实际属于字符列就需要加引号了
+----+--------+
| id | name |
+----+--------+
| 1 | oldboy |
+----+--------+
1 row in set (0.00 sec)
mysql> select * from test where name='oldgirl'; ---查询字符串必须加引号
+----+---------+
| id | name |
+----+---------+
| 2 | oldgirl |
+----+---------+
1 row in set (0.00 sec)
若要同时查询多个条件,则取交集(and),例如查询id为2,并且name为oldgirl的命令如下:
mysql> select * from test where id=2 and name='oldgirl';
+----+---------+
| id | name |
+----+---------+
| 2 | oldgirl |
+----+---------+
1 row in set (0.00 sec)
指定固定条件范围查询数据:
mysql> select id,name from test where id>2 and id<5; ---多个条件,and取交集
+----+------+
| id | name |
+----+------+
| 3 | inca |
| 4 | zuma |
+----+------+
2 rows in set (0.00 sec)
mysql> select id,name from test where id>3 or id<2; ---多个条件,or取并集
+----+--------+
| id | name |
+----+--------+
| 1 | oldboy |
| 4 | zuma |
| 5 | kaka |
+----+--------+
3 rows in set (0.00 sec)
查询排序功能:
mysql> select id,name from test where id>3 order by id asc; ---order by ... asc为顺序排序
+----+------+
| id | name |
+----+------+
| 4 | zuma |
| 5 | kaka |
+----+------+
2 rows in set (0.00 sec)
mysql> select id,name from test where id<3 order by id desc; ---order by ... desc为倒序排序
+----+---------+
| id | name |
+----+---------+
| 2 | oldgirl |
| 1 | oldboy |
+----+---------+
2 rows in set (0.00 sec)
3、修改表中的数据
先查看要修改的表:
mysql> select * from test;
+----+---------+
| id | name |
+----+---------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
| 4 | zuma |
| 5 | kaka |
+----+---------+
5 rows in set (0.00 sec)
将id为3的行的名字修改为gongli:
mysql> update test set name='gongli' where id=3;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from test where id=3;
+----+--------+
| id | name |
+----+--------+
| 3 | gongli |
+----+--------+
1 row in set (0.00 sec)
4、修改数据导致的事故案例和解决方案
如果不带条件更改所有表的记录:
mysql> update test set name='xiaoting'; ---如果不加条件则要十分小心。专业做法是要多向开发人员确认,发送给开发人员的语句一定要括号注明,以防DBA误会。
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5 Changed: 5 Warnings: 0
mysql> select * from test; ---可以发现,所有得name列的内容都是相同的了
+----+----------+
| id | name |
+----+----------+
| 1 | xiaoting |
| 2 | xiaoting |
| 3 | xiaoting |
| 4 | xiaoting |
| 5 | xiaoting |
+----+----------+
5 rows in set (0.00 sec)
面对上面的故障,开始用之前备份的数据进行恢复(备份的重要性):
mysql> drop table test;
Query OK, 0 rows affected (0.03 sec)
mysql> source /opt/bak.sql
Query OK, 0 rows affected (0.03 sec)
mysql> select * from test;
+----+---------+
| id | name |
+----+---------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
| 4 | zuma |
| 5 | kaka |
+----+---------+
5 rows in set (0.00 sec)
5、删除表中得数据
删除表test中编号为1的记录:
mysql> use oldboy
Database changed
mysql> delete from test where id=1;
Query OK, 1 row affected (0.00 sec)
mysql> delete from test where name='oldboy';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test;
+----+---------+
| id | name |
+----+---------+
| 2 | oldgirl |
| 3 | inca |
| 4 | zuma |
| 5 | kaka |
+----+---------+
4 rows in set (0.00 sec)
提示:
- 不加任何条件(where)就是全部删除,这也是非常危险的操作。对于不加条件的delete、update,一定要慎用回车键,按照流程操作(运维人员和开发人员一起操作)
- 开发人员在程序里可能不用delete语句,而是用update语句来更新显示的状态(实现逻辑删除)
有些企业开发人员在开发程序时,会通过状态来判断页面内容显示。比如,在test表中有如下数据,环境接上下文:
mysql> alter table test add state tinyint(2) not null default 1;
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc test;
+-------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | | NULL | |
| state | tinyint(2) | NO | | 1 | |
+-------+------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
然后,程序显示内容时,使用如下语句:
mysql> select * from test where state=1;
+----+---------+-------+
| id | name | state |
+----+---------+-------+
| 2 | oldgirl | 1 |
| 3 | inca | 1 |
| 4 | zuma | 1 |
| 5 | kaka | 1 |
+----+---------+-------+
4 rows in set (0.00 sec)
如果要删除数据,就可以用update替代delete实现逻辑删除,现在删除oldgirl所在的行:
mysql> update test set state=0 where name='oldgirl';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
查看删除结果,可以发现oldgirl 所在的行确实消失了,但实际上数据表里依然有,只不过状态为0,从而不显示了:
mysql> select * from test where state=1;
+----+------+-------+
| id | name | state |
+----+------+-------+
| 3 | inca | 1 |
| 4 | zuma | 1 |
| 5 | kaka | 1 |
+----+------+-------+
3 rows in set (0.00 sec)
提示:
网页正常显示的数据SQL语句为“select * from test where state=1;”删除上述oldgirl的记录语句为“update test set state=0 where name='oldgirl';”因此实际上数据并未真的删除,而是显示状态变为0了,如果想要真正删除,还可以写个在夜里定时任务清理state为0的状态行。
6、清空表中的数据
mysql> select * from test; ---清空前查看下
+----+---------+-------+
| id | name | state |
+----+---------+-------+
| 2 | oldgirl | 0 |
| 3 | inca | 1 |
| 4 | zuma | 1 |
| 5 | kaka | 1 |
+----+---------+-------+
4 rows in set (0.00 sec)
mysql> truncate table test; ---执行清空命令
Query OK, 0 rows affected (0.03 sec)
mysql> select * from test; ---查询发现数据为空了
Empty set (0.00 sec)
truncate和delete是有区别的:
- TRANCATE与不带WHERE语句的DELETE功能相同:两者均删除表中的全部行,但TRUNCATE比DELETE速度更快
- TRANCATE通过释放存储表数据所用的数据页来删除数据,并且只在事务日志中记录页的释放,因此使用的系统和事务日志资源更少
- DELETE语句每次删除一行,并且会在事务日志中为所删除的每一行记录一项