修改数据
INSERT:插入新记录;
UPDATE:更新已有记录;
DELETE:删除已有记录。
INSERT
INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);
INSERT INTO students (class_id, name, gender, score) VALUES (2, '大牛', 'M', 80);
多条记录:INSERT INTO students (class_id, name, gender, score) VALUES
(1, '大宝', 'M', 87),
(2, '二宝', 'M', 81);
UPDATE
UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;
UPDATE students SET name='大牛', score=66 WHERE id=1;
多:UPDATE students SET name='小牛', score=77 WHERE id>=5 AND id<=7;
其中,SET score=score+10就是给当前行的score字段的值加上了10。UPDATE students SET score=100 WHERE id=999;若去掉WHERE,则全部更新,最好先用SELECT语句来测试WHERE条件是否筛选出了期望的记录集,然后再用UPDATE更新。
如果WHERE条件没有匹配到任何记录,UPDATE语句不会报错,也不会有任何记录被更新。
DELETE
DELETE FROM <表名> WHERE ...;
删除students表中id=1的记录:DELETE FROM students WHERE id=1;
注意到DELETE语句的WHERE条件也是用来筛选需要删除的行,因此和UPDATE类似,DELETE语句也可以一次删除多条记录:DELETE FROM students WHERE id>=5 AND id<=7;
如果WHERE条件没有匹配到任何记录,DELETE语句不会报错,也不会有任何记录被删除。
和UPDATE类似,不带WHERE条件的DELETE语句会删除整个表的数据。
MySQL
安装完MySQL后,除了MySQL Server,即真正的MySQL服务器外,还附赠一个MySQL Client程序。MySQL Client是一个命令行客户端,可以通过MySQL Client登录MySQL,然后,输入SQL语句并执行。
打开命令提示符,输入命令mysql -u root -p,提示输入口令。填入MySQL的root口令,如果正确,就连上了MySQL Server,同时提示符变为mysql>。
输入exit断开与MySQL Server的连接并返回到命令提示符。
在MySQL Client中输入的SQL语句通过TCP连接发送到MySQL Server。默认端口号是3306,即如果发送到本机MySQL Server,地址就是127.0.0.1:3306。
也可以只安装MySQL Client,然后连接到远程MySQL Server。假设远程MySQL Server的IP地址是10.0.1.99,那么就使用-h指定IP或域名:
mysql -h 10.0.1.99 -u root -p
命令行程序mysql实际上是MySQL客户端,真正的MySQL服务器程序是mysqld,在后台运行。
管理MySQL,可以使用可视化图形界面MySQL Workbench。
注意:很多时候,通过SSH远程连接时,只能使用SQL命令。
SHOW DATABASES;列出所有数据库
CREATE DATABASE test;创建一个新数据库
DROP DATABASE test;删除一个数据库
USE test;对一个数据库进行操作时,要首先将其切换为当前数据库
表
SHOW TABLES;列出当前数据库的所有表
DESC students;查看一个表的结构
SHOW CREATE TABLE students;查看创建表的SQL语句
DROP TABLE students;创建表使用CREATE TABLE语句,而删除表使用DROP TABLE语句
ALTER TABLE students ADD COLUMN birth VARCHAR(10) NOT NULL;修改表就比较复杂。给students表新增一列birth
ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL;要修改birth列,例如把列名改为birthday,类型改为VARCHAR(20)
ALTER TABLE students DROP COLUMN birthday;删除列
EXIT退出MySQL
注意:EXIT仅仅断开了客户端和服务器的连接,MySQL服务器仍然继续运行。
实用SQL语句
插入或替换
插入一条新记录(INSERT),但如果记录已经存在,就先删除原记录,再插入新记录。此时,可以使用REPLACE语句,这样就不必先查询,再决定是否先删除再插入:
REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);
若id=1的记录不存在,REPLACE语句将插入新记录,否则,当前id=1的记录将被删除,然后再插入新记录。
插入或更新
插入一条新记录(INSERT),但如果记录已经存在,就更新该记录,此时,可以使用INSERT INTO ... ON DUPLICATE KEY UPDATE ...语句:
INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99;
若id=1的记录不存在,INSERT语句将插入新记录,否则,当前id=1的记录将被更新,更新的字段由UPDATE指定。
插入或忽略
插入一条新记录(INSERT),但如果记录已经存在,就啥事也不干直接忽略,此时,可以使用INSERT IGNORE INTO ...语句:
INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);
若id=1的记录不存在,INSERT语句将插入新记录,否则,不执行任何操作。
快照
对一个表进行快照,即复制一份当前表的数据到一个新表,可以结合CREATE TABLE和SELECT:
-- 对class_id=1的记录进行快照,并存储为新表students_of_class1:
CREATE TABLE students_of_class1 SELECT * FROM students WHERE class_id=1;
新创建的表结构和SELECT使用的表结构完全一致。
写入查询结果集
查询结果集需要写入到表中,可以结合INSERT和SELECT,将SELECT语句的结果集直接插入到指定表中。
例如,创建一个统计成绩的表statistics,记录各班的平均成绩:
CREATE TABLE statistics (
id BIGINT NOT NULL AUTO_INCREMENT,
class_id BIGINT NOT NULL,
average DOUBLE NOT NULL,
PRIMARY KEY (id)
);
然后,我们就可以用一条语句写入各班的平均成绩:
INSERT INTO statistics (class_id, average) SELECT class_id, AVG(score) FROM students GROUP BY class_id;
确保INSERT语句的列和SELECT语句的列能一一对应,就可以在statistics表中直接保存查询的结果:
SELECT * FROM statistics;
+----+----------+--------------+
| id | class_id | average |
+----+----------+--------------+
| 1 | 1 | 86.5 |
| 2 | 2 | 73.666666666 |
| 3 | 3 | 88.333333333 |
+----+----------+--------------+
3 rows in set (0.00 sec)
强制使用指定索引
在查询的时候,数据库系统会自动分析查询语句,并选择一个最合适的索引。但是很多时候,数据库系统的查询优化器并不一定总是能使用最优索引。如果我们知道如何选择索引,可以使用FORCE INDEX强制查询使用指定的索引。例如:
SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;
指定索引的前提是索引idx_class_id必须存在。
事务
在执行SQL语句的时候,某些业务要求,一系列操作必须全部执行,而不能仅执行一部分。例如,一个转账操作:
-- 从id=1的账户给id=2的账户转账100元
-- 第一步:将id=1的A账户余额减去100
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 第二步:将id=2的B账户余额加上100
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
这种把多条语句作为一个整体进行操作的功能,被称为数据库事务。
对于单条SQL语句,数据库系统自动将其作为一个事务执行,这种事务被称为隐式事务。
要手动把多条SQL语句作为一个事务执行,使用BEGIN开启一个事务,使用COMMIT提交一个事务,这种事务被称为显式事务,例如,把上述的转账操作作为一个显式事务:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
有些时候,我们希望主动让事务失败,这时,可以用ROLLBACK回滚事务,整个事务会失败:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
ROLLBACK;
隔离级别
QL标准定义了4种隔离级别,分别对应可能出现的数据不一致的情况:
Read Uncommitted
Read Uncommitted是隔离级别最低的一种事务级别。在这种隔离级别下,一个事务会读到另一个事务更新后但未提交的数据,如果另一个事务回滚,那么当前事务读到的数据就是脏数据,这就是脏读(Dirty Read)。
Read Committed
在Read Committed隔离级别下,一个事务可能会遇到不可重复读(Non Repeatable Read)的问题。
不可重复读是指,在一个事务内,多次读同一数据,在这个事务还没有结束时,如果另一个事务恰好修改了这个数据,那么,在第一个事务中,两次读取的数据就可能不一致。
Repeatable Read
在Repeatable Read隔离级别下,一个事务可能会遇到幻读(Phantom Read)的问题。
幻读是指,在一个事务中,第一次查询某条记录,发现没有,但是,当试图更新这条不存在的记录时,竟然能成功,并且,再次读取同一条记录,它就神奇地出现了。
Serializable
Serializable是最严格的隔离级别。在Serializable隔离级别下,所有事务按照次序依次执行,因此,脏读、不可重复读、幻读都不会出现。
虽然Serializable隔离级别下的事务具有最高的安全性,但是,由于事务是串行执行,所以效率会大大下降,应用程序的性能会急剧降低。如果没有特别重要的情景,一般都不会使用Serializable隔离级别。
网友评论