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
网友评论