修改数据
INSERT语句
主键自增时,补0
--插入单行数据
INSERT INTO table(column1,column2...) VALUES (value1,value2,...);
--插入多行数据
INSERT INTO table(column1,column2...) VALUES (value1,value2,...),
(value1,value2,...),(value1,value2,...);
--使用 INSERT 和 SELECT 子句完全或部分复制表
INSERT INTO table_1 SELECT c1, c2, FROM table_2;
--将 tasks 表复制到 tasks_bak 表
CREATE TABLE tasks_bak LIKE tasks;
INSERT INTO tasks_bak SELECT * FROM tasks;
--如果在 INSERT 语句中指定ON DUPLICATE KEY UPDATE选项, MySQL将插入新行或使用新值更新原行记录。
INSERT INTO table(task_id,subject,start_date,end_date,description)
VALUES (4,'Test ON DUPLICATE KEY UPDATE','2017-01-01','2017-01-02','Next Priority')
ON DUPLICATE KEY UPDATE
--task_id = task_id + 1,
subject = 'Test ON DUPLICATE KEY UPDATE';
--使用 INSERT INGORE 语句, 则会忽略导致错误的行, 并将其余行插入到表中
USE testdb;
CREATE TABLE IF NOT EXISTS subscribers (
id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(50) NOT NULL UNIQUE
);
INSERT INTO subscribers(email) VALUES('aa@126.com');
INSERT INTO subscribers(email) VALUES('aa@126.com'),('bb@126.com');
--1062 - Duplicate entry 'aa@126.com' for key 'email'
INSERT IGNORE INTO subscribers(email) VALUES('aa@126.com'),('bb@126.com');
--MySQL服务器返回一条消息, 显示插入一行, 另一行被忽略。
--Query OK, 1 row affected
--Records: 2 Duplicates: 1 Warnings: 1
--使用 SHOW WARNINGS 语句, 就会发现警告的详细信息
UPDATE语句
--语法
UPDATE [LOW_PRIORITY] [IGNORE] table_name
SET column_name1 = expr1, column_name2 = expr2, ...
WHERE condition;
--注意事项, WHERE 子句非常重要,如果省略 WHERE 子句, 则 UPDATE 语句将更新表中的所有行。
--UPDATE一列
--更新为新的电子邮件
SELECT firstname, lastname, email FROM employees WHERE employeeNumber = 1056;
UPDATE employees SET email = 'mary.new@126.com' WHERE employeeNumber = 1056;
--UPDATE多列
UPDATE employees SET lastname = 'Hill', email = 'mary.hill@126.com'
WHERE employeeNumber = 1056;
DELETE语句
--语法
DELETE FROM table_name WHERE condition;
--删除单条
DELETE FROM employees WHERE officeCode = 4;
--删除多条
DELETE FROM employees WHERE officeCode in(2,3,5,6);
--全删
DELETE FROM employees;
--限制要删除的行数, 则使用LIMIT子句,并指定行顺序
DELETE FROM table_name ORDER BY c1, c2, ... LIMIT row_count
--按客户名称按字母排序客户, 并删除前 10 个客户
DELETE FROM customers ORDER BY customerName LIMIT 10;
--选择法国(France)的客户, 按升序按信用额度( creditLimit )进行排序, 并删除前 5 个客户
DELETE FROM customers WHERE country = 'France' ORDER BY creditLimit LIMIT 5;
--级联删除:ON DELETE CASCADE,班级中有多个学生,删除班级的同事要把班级中的学生都删除。
该教程暂停一段时间吧!