0 样例表
样例表person,创建语句:
CREATE TABLE person
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(40) NOT NULL DEFAULT '',
age INT NOT NULL DEFAULT 0,
info CHAR(50) NULL,
PRIMARY KEY (id)
);
1 插入数据
1.1 为表的所有字段插入数据
语法:
INSERT INTO 表名(列名列表) VAULES(对应值列表);
(1)指定所有字段名
mysql> INSERT INTO person(id,name,age,info)
-> VALUES(1,'Green',21,'Lawyer');
Query OK, 1 row affected
(2)完全不指定字段名
mysql> INSERT INTO person
-> VALUES(2,'Suse',2,'dancer'),
-> (3,'Mary',24,'Musician');
Query OK, 2 rows affected
Records: 2 Duplicates: 0 Warnings: 0
(3)查看执行结果
mysql> SELECT * FROM person;
+----+-------+-----+----------+
| id | name | age | info |
+----+-------+-----+----------+
| 1 | Green | 21 | Lawyer |
| 2 | Suse | 2 | dancer |
| 3 | Mary | 24 | Musician |
+----+-------+-----+----------+
3 rows in set
1.2 为表的指定字段插入数据
为表的指定字段插入数据,就是在INSERT语句中只向部分字段中插入值,而其他字段的值为表定义的默认值。
mysql> INSERT INTO person(name,age,info) VALUES('Willam',20,'sports man');
Query OK, 1 row affected
mysql> INSERT INTO person(name,age) VALUES('Laura',20);
Query OK, 1 row affected
查看执行结果
mysql> SELECT * FROM person;
+----+--------+-----+------------+
| id | name | age | info |
+----+--------+-----+------------+
| 1 | Green | 21 | Lawyer |
| 2 | Suse | 2 | dancer |
| 3 | Mary | 24 | Musician |
| 4 | Willam | 20 | sports man |
| 5 | Laura | 20 | NULL |
+----+--------+-----+------------+
5 rows in set
1.3 同时插入多条数据
语法:
INSERT INTO table_name(column_list) VALUES(value_list),(value_list2),...,(value_listn);
例子:(等同于执行多条INSERT语句,但MySQL执行单条INSERT语句插入多行数据,比使用多条INSERT语句快。)
mysql> INSERT INTO person(name,age,info)
-> VALUES('Evans',27,'secretary'),
-> ('Dale',22,'singer'),
-> ('Edison',28,'cook');
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0
- Records:表明插入的记录条数
- Duplicates:表明插入时被忽略的记录,原因可能是这些记录包含了重复的主键值
- Warnings:表明有问题的数据值,例如发生数据类型转换
1.4 将查询结果插入到表中
INSERT可以将SELECT语句查询的结果插入到表中,语法格式:
INSERT INTO table_name1(column_list1)
SELECT(column_list2) FROM table_name2 WHERE(condition)
例子:
mysql> SELECT * FROM person;
+----+---------+-----+------------+
| id | name | age | info |
+----+---------+-----+------------+
| 1 | Green | 21 | Lawyer |
| 2 | Suse | 2 | dancer |
| 3 | Mary | 24 | Musician |
| 4 | Willam | 20 | sports man |
| 5 | Laura | 20 | NULL |
| 6 | Evans | 27 | secretary |
| 7 | Dale | 22 | singer |
| 8 | Edison | 28 | cook |
+----+---------+-----+------------+
8 rows in set
mysql> SELECT * FROM person_copy;
+----+---------+-----+----------+
| id | name | age | info |
+----+---------+-----+----------+
| 9 | Harry | 21 | magician |
| 10 | Harriet | 19 | pianist |
+----+---------+-----+----------+
2 rows in set
mysql> INSERT INTO person
-> SELECT * FROM person_copy;
Query OK, 2 rows affected
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM person;
+----+---------+-----+------------+
| id | name | age | info |
+----+---------+-----+------------+
| 1 | Green | 21 | Lawyer |
| 2 | Suse | 2 | dancer |
| 3 | Mary | 24 | Musician |
| 4 | Willam | 20 | sports man |
| 5 | Laura | 20 | NULL |
| 6 | Evans | 27 | secretary |
| 7 | Dale | 22 | singer |
| 8 | Edison | 28 | cook |
| 9 | Harry | 21 | magician |
| 10 | Harriet | 19 | pianist |
+----+---------+-----+------------+
10 rows in set
2 更新数据
使用UPDATE语句更新表中的记录,可以更新特定的行或者同时更新所有的行。语法:
UPDATE table_name
SET column_name1 = value1,column_name2=value2,...,column_namen=valuen
WHERE(condition);
例子:
mysql> SELECT * FROM person WHERE id=10;
+----+---------+-----+---------+
| id | name | age | info |
+----+---------+-----+---------+
| 10 | Harriet | 19 | pianist |
+----+---------+-----+---------+
1 row in set
mysql> UPDATE person SET age=15,name='LiMing' WHERE id=10;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM person WHERE id=10;
+----+--------+-----+---------+
| id | name | age | info |
+----+--------+-----+---------+
| 10 | LiMing | 15 | pianist |
+----+--------+-----+---------+
1 row in set
更新多行数据:
mysql> SELECT * FROM person WHERE age BETWEEN 19 AND 22;
+----+--------+-----+------------+
| id | name | age | info |
+----+--------+-----+------------+
| 1 | Green | 21 | Lawyer |
| 4 | Willam | 20 | sports man |
| 5 | Laura | 20 | NULL |
| 7 | Dale | 22 | singer |
| 9 | Harry | 21 | magician |
+----+--------+-----+------------+
5 rows in set
mysql> UPDATE person SET info='student' WHERE age BETWEEN 19 AND 22;
Query OK, 5 rows affected
Rows matched: 5 Changed: 5 Warnings: 0
mysql> SELECT * FROM person WHERE age BETWEEN 19 AND 22;
+----+--------+-----+---------+
| id | name | age | info |
+----+--------+-----+---------+
| 1 | Green | 21 | student |
| 4 | Willam | 20 | student |
| 5 | Laura | 20 | student |
| 7 | Dale | 22 | student |
| 9 | Harry | 21 | student |
+----+--------+-----+---------+
5 rows in set
3 删除数据
使用DELETE语句删除数据,DELETE语句允许WHERE子句指定删除条件。语法:
DELETE FROM table_name [WHERE<condition>];
如果没有WHERE子句,DELETE语句将删除表中所有记录。
mysql> SELECT * FROM person WHERE id=10;
+----+--------+-----+---------+
| id | name | age | info |
+----+--------+-----+---------+
| 10 | LiMing | 15 | pianist |
+----+--------+-----+---------+
1 row in set
mysql> DELETE FROM person WHERE id=10;
Query OK, 1 row affected
mysql> SELECT * FROM person WHERE id=10;
Empty set
4 说明
阅读《MySQL5.5 从零开始学》笔记记录。