1SELECT CONCAT('update tb_2 set col1=',tb1.col1,' where col2=',tb1.col2,';') from tb_1 tb1 where ....

1select id,LENGTH(string) - LENGTH(REPLACE(string, ",", "")) AS cnt from strings;

1select first_name from employees  order by substring(first_name,-2)

1SELECT
2  dept_no, group_concat(distinct emp_no order by emp_no asc) employees
3FROM dept_emp GROUP BY dept_no

 1select avg(salary) as avgt from salaries
 2where
 3  to_date = '9999-01-01' and salary not in 
 4  (select  max(salary) from
 5       
 6    where
 7      to_date = '9999-01-01'
 8  )
 9  and salary not in (
10    select min(sala  where to_date = '9999-01-01'
11  )

1select * from salaries
2force index (idx_emp_no)
3where emp_no=10005

1#with tb as (select ....) select  
2
3WITH TB_TEMP AS ( SELECT role_id FROM sys_user_role WHERE user_id = '1533428933922820096' ) SELECT sr.role_name 
4FROM
5	sys_role sr 
6WHERE
7	EXISTS ( SELECT 1 FROM TB_TEMP te WHERE te.role_id = sr.id )

 1#根据别的表的数据新建表，方法1，MySQL适用
 2create table if not exists actor_name as (
 3  select
 4    first_name,
 5    last_name
 6  from
 7    actor
 8)
 9
10#方法2
11create table if not exists actor_name(
12first_name  varchar(45)  not null,
13last_name   varchar(45)  not null); -- 创建表
14insert into actor_name select     first_name,last_name from actor; -- 插入查询结果

1-- 创建视图，使用小括号创建视图中的字段名
2create view actor_name_view (first_name_v, last_name_v) 
3	as select first_name,last_name from actor;
4	
5-- 创建视图，在select后面对查询的列重命名作为视图字段名
6create view actor_name_view 
7	as select first_name as first_name_v,last_name as last_name_v from actor

 1-- mysql中触发器如下：
 2	-- trigger_name：标识触发器名称，用户自行指定
 3	-- trigger_time：标识触发时机，取值为 BEFORE 或 AFTER
 4	-- trigger_event：标识触发事件，取值为 INSERT、UPDATE 或 DELETE
 5	-- tbl_name：标识建立触发器的表名，即在哪张表上建立触发器
 6	-- trigger_stmt：触发器程序体，可以是一句SQL语句，或者用 BEGIN 和 END 包含的多条语句，每条语句结束要分号结尾
 7CREATE TRIGGER trigger_name
 8	trigger_time trigger_event ON tbl_name
 9	FOR EACH ROW trigger_stmt
10
11-- 具体创建触发器语句；其中 new表示触发器的所在表新数据，old表示触发器的所在表老数据
12create trigger audit_log 
13after insert on employees_test
14for each row
15begin 
16    insert into audit values(new.id,new.name);
17end

1-- 修改表名；ALTER TABLE 表名 RENAME TO/AS 新表名
2
3ALTER TABLE titles_test RENAME TO titles_2017;

1-- 直接更新
2update titles_test set to_date=null,from_date='2001-01-01'
3
4-- 替换
5UPDATE titles_test SET emp_no = REPLACE(emp_no, 10001, 10005) WHERE id = 5

 1-- 主键；该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
 2ALTER TABLE tbl_name ADD PRIMARY KEY (col_list);
 3
 4-- 唯一索引；这条语句创建索引的值必须是唯一的
 5ALTER TABLE tbl_name ADD UNIQUE index_name (col_list);
 6
 7-- 普通索引；添加普通索引，索引值可出现多次
 8ALTER TABLE tbl_name ADD INDEX index_name (col_list);
 9
10-- 全文索引；该语句指定了索引为 FULLTEXT ，用于全文索引
11ALTER TABLE tbl_name ADD FULLTEXT index_name (col_list);

1-- ALTER TABLE <表名> ADD CONSTRAINT FOREIGN KEY (<列名>) REFERENCES <关联表>(关联列)
2
3ALTER TABLE
4  audit
5ADD
6  CONSTRAINT FOREIGN KEY (emp_no) REFERENCES employees_test(id);

1-- ALTER TABLE <表名> ADD COLUMN <新字段名> <数据类型> [约束条件] [FIRST|AFTER 已存在的字段名];
2alter table actor
3	add column create_date datetime not null default '2020-10-01 00:00:00' 		after last_update;

1-- 需要把记录先展示出来并重命名，然后删除
2DELETE FROM titles_test WHERE
3  id NOT IN ( SELECT * FROM
4		(SELECT MIN(id) FROM titles_test GROUP BY emp_no) a -- 分组查询出id最小的
5  );