//微信公众号:关注挨踢小子
1、如果是整个表复制表达如下:
insert into table1 select * from table2
2、如果是有选择性的复制数据表达如下:
insert into table1(column1,column2,column3...)
select column1,column2,colunm3...from table2
3、一个数据库中的表中的数据复制到另一个数据库中的一个表,使用方法如下:
insert into 数据库A.dbo.table1(col1,col2,col3...)
select col1,col2,col3... from 数据库B.dbo.table2
4、直接复制现有表重命名
CREATE TABLE 表名 AS SELECT 语句;
//微信公众号:关注挨踢小子
select name,age,(select date from tableB)
from tableA where age="12";
//微信公众号:关注挨踢小子
update table a, table b
set a.filed1= b.field2, a.field2= b.field1
where a.id = b.id
//微信公众号:关注挨踢小子
update test set priority=
(case when id=1 then
(select priority from test where id=2)
when id=2 then
(select priority from test where id=1) end)
where id=1 or id=2;
//微信公众号:关注挨踢小子
concat(string1,string2,string3,…)
select concat( '1', '01','02')
// 10102
concat_ws(separator,str1,str2,...)
select concat_ws('-','1','2','3')
// 1-2-3
group_concat()
group_concat(
[distinct] 要连接的字段
[order by 排序字段 asc/desc ]
[separator '分隔符']
)
// 例如:
SELECT group_concat(content ORDER BY id DESC separator '-') from t_noteinfo
delete tablename where id not in(select min(id)
from tablename group by name,………………)
delete from info where not exists
(select * from infobz where info.infid=infobz.infid)
select * from 日程安排
where datediff('minute',f 开始时间,getdate())>5
select name from table group by name having min(fenshu)>90
//微信公众号:关注挨踢小子
原表数据结构
year month amount
1991 1 1.1
1991 2 1.2
1991 3 1.3
1991 4 1.4
1992 1 2.1
1992 2 2.2
1992 3 2.3
1992 4 2.4
查成这样一个结果
year m1 m2 m3 m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4
select year,
(select amount from aaa m where month=1 and m.year=aaa.year) as m1,
(select amount from aaa m where month=2 and m.year=aaa.year) as m2,
(select amount from aaa m where month=3 and m.year=aaa.year) as m3,
(select amount from aaa m where month=4 and m.year=aaa.year) as m4
from aaa group by year
SELECT t.dept_id FROM sys_dept t
WHERE FIND_IN_SET (102,ancestors)
//微信公众号:关注挨踢小子
explain select * from tablename