介绍一下几个 mysql 比较实用的sql语句

目录

1、快照（复制一份当前表的数据到一个新表）

2、写入查询结果集

3、强制使用指定索引

4、插入或替换

5、插入或更新

6、插入或忽略

---

创建一个班级表：

create table class(id int primary key,classname varchar(20));

创建一个学生表：

create table student(id int primary key,name varchar(20),age int,classid int)

插入班级数据：

insert into class(id,classname) values(1,'一年级');
insert into class(id,classname) values(2,'二年级');

插入学生数据：

insert into student(id,name,age,classid) values(1,'张三',11,1);
insert into student(id,name,age,classid) values(2,'李四',16,1);
insert into student(id,name,age,classid) values(3,'王五',20,2);

1、快照（复制一份当前表的数据到一个新表）

如果想要对一个表进行快照，即复制一份当前表的数据到一个新表，可以结合CREATE TABLE和SELECT：

create table student_class2 select * from student where classid=2;

新创建的表结构和SELECT使用的表结构完全一致。

2、写入查询结果集

如果查询结果集需要写入到表中，可以结合INSERT和SELECT，将SELECT语句的结果集直接插入到指定表中。

例如，创建一个统计年龄的表statistics，记录各班的平均年龄：

CREATE TABLE statistics (
    id BIGINT NOT NULL AUTO_INCREMENT,
    classname varchar(20) NOT NULL,
    average DOUBLE NOT NULL,
    PRIMARY KEY (id)
);

然后，我们就可以用一条语句写入各班的平均年龄：

INSERT INTO statistics (classname, average) SELECT b.classname, AVG(a.age) FROM student a
left join class b on a.classid=b.id GROUP BY a.classid;

3、强制使用指定索引

在查询的时候，数据库系统会自动分析查询语句，并选择一个最合适的索引。但是很多时候，数据库系统的查询优化器并不一定总是能使用最优索引。如果我们知道如何选择索引，可以使用FORCE INDEX强制查询使用指定的索引。

首先为student表创建一个索引：

alter table student add index ind_name(name);

指定索引查询：

select * from student force index (ind_name) order by classid desc;

4、插入或替换

如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就先删除原记录，再插入新记录。此时，可以使用REPLACE语句，这样就不必先查询，再决定是否先删除再插入：

replace into student(id,name,age) values(1,'张三1',20);

 若id=1的记录不存在，REPLACE语句将插入新记录，否则，当前id=1的记录将被删除，然后再插入新记录。

5、插入或更新

如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就更新该记录，此时，可以使用INSERT INTO ... ON DUPLICATE KEY UPDATE ...语句：

insert into student(id,name,classid)values(1,'张三',1) 
on duplicate key update name='张三',classid=2;

 若id=1的记录不存在，INSERT语句将插入新记录，否则，当前id=1的记录将被更新，更新的字段由UPDATE指定。

6、插入或忽略

如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就啥事也不干直接忽略，此时，可以使用INSERT IGNORE INTO ...语句：

insert ignore into student(id,name)values(1,'张三111');

 若id=1的记录不存在，INSERT语句将插入新记录，否则，不执行任何操作。