sql教程5插入和删除及更新

sql快速入门教程1简介

sql教程2数据库和SQL简介

sql教程3 MySQL介绍

sql教程4 MySQL SELECT

sql教程5插入和删除及更新

sql教程6分组、转义与模糊匹配、正则表达式

sql教程7函数

sql教程8 NULL ALTER DROP RENAME LIMIT

插入语法

INSERT INTO table_name(column_1,column_2,...) VALUES (value_1,value_2,...);

主要类型有字符串、数值、日期。

# 注意后面的0
mysql> INSERT INTO members (full_names,gender,physical_address,contact_number) VALUES ('Leonard Hofstadter','Male','Woodcrest',0845738767); 
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO members (full_names,gender,physical_address,contact_number) VALUES ('Sheldon Cooper','Male','Woodcrest', '0976736763');
Query OK, 1 row affected (0.05 sec)

# 部分字段
mysql> INSERT INTO members (contact_number,gender,full_names,physical_address)VALUES ('0938867763','Male','Rajesh Koothrappali','Woodcrest'); 
Query OK, 1 row affected (0.06 sec)

# 插入日期
mysql> INSERT INTO members (full_names,date_of_birth,gender,physical_address,contact_number) VALUES ('Leslie Winkle','1984-02-14','Male','Woodcrest', '0987636553');   
Query OK, 1 row affected (0.06 sec)

＃插入所有值可以不指定字段
mysql> INSERT INTO members VALUES (20,'Howard Wolowitz','Male','1981-08-24','SouthPark','P.O. Box 4563', '0987786553', 'lwolowitz[at]email.me');
Query OK, 1 row affected (0.03 sec)


＃由其他字段导入
mysql> CREATE TABLE categories_archive (category_id int(11) AUTO_INCREMENT, category_name varchar(150) DEFAULT NULL, remarks varchar(500) DEFAULT NULL,PRIMARY KEY (category_id));
Query OK, 0 rows affected (0.45 sec)

mysql> INSERT INTO categories_archive SELECT * FROM categories;  
Query OK, 9 rows affected (0.08 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> select * from categories_archive;
+-------------+-----------------+---------------------+
| category_id | category_name   | remarks             |
+-------------+-----------------+---------------------+
|           1 | Comedy          | Movies with humour  |
|           2 | Romantic        | Love stories        |
|           3 | Epic            | Story acient movies |
|           4 | Horror          | NULL                |
|           5 | Science Fiction | NULL                |
|           6 | Thriller        | NULL                |
|           7 | Action          | NULL                |
|           8 | Romantic Comedy | NULL                |
|           9 | Cartoons        | NULL                |
+-------------+-----------------+---------------------+
9 rows in set (0.00 sec)

删除语法

DELETE FROM table_name [WHERE condition];

INSERT INTO  movies (title, director, year_released, category_id) VALUES ('The Great Dictator', 'Chalie Chaplie', 1920, 7);
INSERT INTO movies (title, director, category_id) VALUES ('sample movie', 'Anonymous', 8);
INSERT INTO  movies (title, director, year_released, category_id) VALUES ('movie 3', 'John Brown', 1920, 8);

DELETE FROM movies WHERE movie_id = 18;

DELETE FROM movies WHERE movie_id  IN (20,21);

更新

UPDATE table_name SET column_name = new_value' [WHERE condition];

实例

mysql> SELECT * FROM members WHERE membership_number = 1;
+-------------------+-------------+--------+---------------+------------------------+----------------+----------------+---------------------+
| membership_number | full_names  | gender | date_of_birth | physical_address       | postal_address | contact_number | email               |
+-------------------+-------------+--------+---------------+------------------------+----------------+----------------+---------------------+
|                 1 | Janet Jones | Female | 1980-07-21    | First Street Plot No 4 | Private Bag    | 0759 253 543   | janetjones@yagoo.cm |
+-------------------+-------------+--------+---------------+------------------------+----------------+----------------+---------------------+
1 row in set (0.00 sec)

mysql> UPDATE members SET contact_number = '0759 253 542' WHERE membership_number = 1;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM members WHERE membership_number = 1;
+-------------------+-------------+--------+---------------+------------------------+----------------+----------------+---------------------+
| membership_number | full_names  | gender | date_of_birth | physical_address       | postal_address | contact_number | email               |
+-------------------+-------------+--------+---------------+------------------------+----------------+----------------+---------------------+
|                 1 | Janet Jones | Female | 1980-07-21    | First Street Plot No 4 | Private Bag    | 0759 253 542   | janetjones@yagoo.cm |
+-------------------+-------------+--------+---------------+------------------------+----------------+----------------+---------------------+
1 row in set (0.00 sec)

mysql> 
mysql> UPDATE members SET full_names = 'Janet Smith Jones', physical_address = 'Melrose 123' WHERE membership_number = 2;
Query OK, 0 rows affected (0.04 sec)
Rows matched: 1  Changed: 0  Warnings: 0