Mysql基础知识系列一增删改查

一、新增

1. 基本语句

   insert into 表名[字段1,字段2,……] values(value1,value2,……)
   

2. 批量新增

   • 第一种

   # 增加多条数据
   INSERT INTO 表 (列名,列名...) VALUES(值,值,值...),(值,值,值...);
   

   • 第二种

   insert into t2 (字段1, 字段2) select 字段1, 字段2 from t1 where 条件
   

二、删除

1. 基本语句

   删除数据：delete from 表名 where 条件 #清空表，自增列依然有记忆

   删除数据：truncate (table) 表名 #快速清空表，有自增列也会被重置

   删除表：drop table 表名 #删除表

2. 在条件中使用查询结果

   示例：delete from date_db where a=(select a from all_db where c = 2)

   注意：查询的结果只能是单行单列，否则，会报错

三、修改

1. 基本语句

   update 表名 set 字段1=值1 字段2=值2…… where 条件

2. 通过查询结果批量修改

   示例：update t1 set t1.a = (select t2.b from t2 where t1.id = t2.id)

   注意：修改表时，表的字段值可以作为参数使用

3. UPDATE JOIN语法

   UPDATE A JOIN B ON A.URL = B.URL SET member_id = '00012138' 
   WHERE LOGIN_time BETWEEN '2019' AND '2020' AND B.class_id = 'TNT';
   

四、查询

1. 基本语句

   select 字段1，字段2，…… from 表名 where 条件 group by 字段 having 条件 order by 字段
   

2. 子查询

   子查询就是将一个查询（子查询）的结果作为另一个查询（主查询）的数据来源或判断条件的查询。常见的子查询有WHERE子查询，HAVING子查询，FROM子查询，SELECT子查询，EXISTS子查询，子查询要使用小括号（）；

   并且，在子查询中，是可以用主查询中字段值作为条件，就是常说的代入查询

   如： select * from t1 where t1.a in (select t2.b from t2 where t1.id = t2.id)

3. 查询语句的执行顺序

   (8)SELECT (9)DISTINCT <select list>
   
   (1)FROM[left_table]
   
   (3)<join_type>JOIN<right_table>
   
   (2)ON<join_condition>
   
   (4)WHERE<where_condition>
   
   (5)GROUPBY<group_by_list>
   
   (6)WITH<CUBE | RollUP>
   
   (7)HAVING<having_condition>
   
   (10)ORDERBY<order_by_list>
   

   逻辑查询处理阶段顺序

   FROM：对FROM子句中的前两个表执行笛卡尔积（Cartesian product)(交叉联接），生成虚拟表VT1

   ON：对VT1应用ON筛选器。只有那些使<join_condition>为真的行才被插入VT2。

   OUTER(JOIN)：如 果指定了OUTER JOIN（相对于CROSS JOIN 或(INNERJOIN),保留表（preserved table：左外部联接把左表标记为保留表，右外部联接把右表标记为保留表，完全外部联接把两个表都标记为保留表）中未找到匹配的行将作为外部行添加到 VT2,生成VT3.如果FROM子句包含两个以上的表，则对上一个联接生成的结果表和下一个表重复执行步骤1到步骤3，直到处理完所有的表为止。

   WHERE：对VT3应用WHERE筛选器。只有使<where_condition>为true的行才被插入VT4.

   GROUPBY：按GROUP BY子句中的列列表对VT4中的行分组，生成VT5.

   CUBE|ROLLUP：把超组(Suppergroups)插入VT5,生成VT6.

   HAVING：对VT6应用HAVING筛选器。只有使<having_condition>为true的组才会被插入VT7.

   SELECT：处理SELECT列表，产生VT8.

   DISTINCT：将重复的行从VT8中移除，产生VT9.

   ORDERBY：将VT9中的行按ORDER BY 子句中的列列表排序，生成游标（VC10).

五、关键字

数据库常见关键字 any,all,in,exists,limit

1. any,all

   • any：用于获取条件中任意一个值进行比较，当使用等于号时，效果和in 一致

     如：查找t1表中大于任意一个t2表id的值，也就是大于条件中的最小值，小于就是小于条件中的最大值，

      select * from t1 where t1.id > any(select id FROM t2)
     

   • all：用于获取条件中所有值进行比较

     如：查找t1表中大于t2表所有id的数据，也就是大于条件中的最大值，如果是小于，就是小于条件中最小值

     select * from t1 where t1.id > all(select id FROM t2)
     

2. in,exists

   • in：用于判断字段值是否存在于条件中

     select * from t1 where a in (select a FROM t2)
     

   • exists：用于判断exists 后的子查询是否有值

     select * from t1 where exists (select b FROM t2  where a = t1.a )
     

   • 两者比较

     两者有时能通用，但是会有效率问题，in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。简而言之，exists适合内小外大的查询，in适合内大外小的查询。

     性能的主要差异来源于索引的使用，如果是t1表更大，示例1中使用的是t2表的索引，没有使用t1表的，这是因为使用in时，不是走索引

     而示例2中使用的t1表的索引，所以效率高一点，反之亦然。

     如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

3. limit

   SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
   

   LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)： 为了与 PostgreSQL 兼容，MySQL 也支持句法： LIMIT # OFFSET #。

   mysql> SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15  
     
   //为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1：   
   mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.  
     
   //如果只给定一个参数，它表示返回最大的记录行数目：   
   mysql> SELECT * FROM table LIMIT 5; //检索前 5 个记录行  
   
   //换句话说，LIMIT n 等价于 LIMIT 0,n。 
   
   

六、修改表结构

1. 创建表

CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

create table if not exists `sys_user`(
`user_id` varchar(32) NOT NULL,
`user_name` varchar(50) NOT NULL,
`user_weight` double(6,2) default 0,
`user_createTime` datetime,
`user_age` int(3),
 PRIMARY KEY ( `user_id` )
)
ENGINE=innodb default charset = utf8mb4;

2. 修改表结构

修改表名
rename table 老表名 to 新表名;

添加列
ALTER TABLE 表名 ADD 列名 类型;

删除列
ALTER TABLE 表名 DROP COLUMN 列名;

修改列
ALTER TABLE 表名 MODIFY COLUMN 列名 类型; 一般只改类型;
ALTER TABLE 表名 CHANGE 原列名 新列名 类型; 可改列名+类型;

添加主键
ALTER TABLE 表名 ADD PRIMARY KEY(列名);

删除主键
ALTER TABLE 表名 DROP PRIMARY KEY;

添加外键
ALTER TABLE 从表 ADD CONSTRAINT 外键名称（形如：FK_从表_主表）FOREIGN KEY 从表(外键字段) REFERENCES 主表(主键字段);

删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

修改默认值
ALTER TABLE 表名 ALTER 列名 SET DEFAULT 1000;

删除默认值
ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;