case when then else end

demo

case具有两种格式。简单case函数和case搜索函数。

--简单case函数
  case sex
  when '1' then '男'
  when '2' then '女'
  else '其他' end

--case搜索函数
  case when sex = '1' then '男'
  when sex = '2' then '女'
  else '其他' end

这两种方式，可以实现相同的功能。简单case函数的写法相对比较简洁，但是和case搜索函数相比，功能方面会有些限制，比如写判定式。

还有一个需要注重的问题，case函数只返回第一个符合条件的值，剩下的case部分将会被自动忽略。

--比如说，下面这段sql，你永远无法得到“第二类”这个结果
  case when col_1 in ( 'a', 'b') then'第一类'
  when col_1 in ('a')  then '第二类'
  else '其他' end

CONCAT()的用法

mysql CONCAT（）函数用于将多个字符串连接成一个字符串，是最重要的mysql函数之一

mysql CONCAT(str1,str2,…)

返回结果为连接参数产生的字符串。

如有任何一个参数为NULL ，则返回值为 NULL。
如果所有参数均为非二进制字符串，则结果为非二进制字符串。
如果自变量中含有任一二进制字符串，则结果为一个二进制字符串。一个数字参数被转化为与之相等的二进制字符串格式；若要避免这种情况，可使用显式类型 cast, 例如： SELECT CONCAT(CAST(int_col AS CHAR), char_col)

mysql> SELECT CONCAT(’My’, ‘S’, ‘QL’);
-> ‘MySQL’

mysql> SELECT CONCAT(’My’, NULL, ‘QL’);
-> NULL

mysql> SELECT CONCAT(14.3);
-> ‘14.3′

mysql CONCAT_WS(separator,str1,str2,…)

CONCAT_WS() 代表 CONCAT With Separator ，是CONCAT()的特殊形式。第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。如果分隔符为 NULL，则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。

SELECT CONCAT_WS(',','First name','Second name','Last Name');
-> ‘First name,Second name,Last Name’

SELECT CONCAT_WS(',','First name',NULL,'Last Name');
-> ‘First name,Last Name’

mysql CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。

mysql日期字段按照时间更新

ALTER TABLE kpi_pension MODIFY COLUMN UPDATE_TIME datetime NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP;

mysql日期字段取消按照时间更新

ALTER TABLE kpi_pension MODIFY COLUMN UPDATE_TIME datetime NULL DEFAULT NULL;

MYSQL中UNION和UNION ALL的区别有哪些？

在mysql中如何想要对两个结果集进行合并操作，可以使用UNION和UNION ALL，如果只是想要去除掉重复的记录，属于UNION ALL 即可，但是如何想要除掉没有重复行数据，就要使用Union。

具体作用不同

UNION 语句：用于将不同表中相同列中查询的数据展示出来；（不包括重复数据）

UNION ALL 语句：用于将不同表中相同列中查询的数据展示出来；（包括重复数据）

使用不同

使用UNION关键字是将所有查询结果合并到一起，然后去除相同的记录。

使用UNION ALL关键字则是将所有的结果合并到一起。

效率不同

UNION会对结果排序和去重复在73万条数据测试中花费11.84秒查询.

UNION ALL 则不会排序也不会去重复再73万数据测试中花费 4.34秒查询

排重不同

UNION：对两个结果集进行合并操作，多表连接后会去除掉重复的记录没有重复行数据.

UNION ALL ：对两个结果集进行合并操作，多表连接后会去除掉重复的记录

总体上说，union all要比union快很多，如果没有排重的需要，建议直接使用union all；如果需要排重，则使用union。

MYSQL自增主键ID重置

MYSQL在创建一个带有自增主键ID的表时，通常在删除数据时，导致自增主键不连续了。使用下面的SQL脚本可以重置主键。

-- 1、重置已有数据主键
SET @rownum = 0;
UPDATE table_name SET id = @rownum := @rownum +1;

-- 2、修改自增主键，beginIndex为查询出来的最大id+1
SELECT max(id)+1 from table_name;
alter table table_name auto_increment= beginIndex;

如何用sql语句复制一张表

1、复制表结构及数据到新表

CREATE TABLE 新表 SELECT * FROM 旧表，这种方法会将oldtable中所有的内容都拷贝过来，当然我们可以用delete from newtable;来删除。不过这种方法的一个最不好的地方就是新表中没有了旧表的primary key、Extra（auto_increment）等属性。需要自己用"alter"添加，而且容易搞错。

2、只复制表结构到新表

CREATE TABLE 新表 SELECT * FROM 旧表 WHERE 1=2或CREATE TABLE 新表 LIKE 旧表

3、复制旧表的数据到新表(假设两个表结构一样)

INSERT INTO 新表 SELECT * FROM 旧表

4、复制旧表的数据到新表(假设两个表结构不一样)

INSERT INTO 新表(字段1,字段2,.......) SELECT 字段1,字段2,...... FROM 旧表

5、可以将表1结构复制到表2

SELECT * INTO 表2 FROM 表1 WHERE 1=2

6、可以将表1内容全部复制到表2

SELECT * INTO 表2 FROM 表1

7、show create table 旧表;

这样会将旧表的创建命令列出。我们只需要将该命令拷贝出来，更改table的名字，就可以建立一个完全一样的表

数据库新增字段

ALTER TABLE tableName 
ADD COLUMN columnName（要新建的字段名） 
vachar（字段长度） 
DEFAULT NULL 
COMMENT（备注信息）
AFTER columnName（columnName是表中已有的字段，用于确认新字段的位置）。

数据库删除字段

ALTER TABLE tableName DROP columnName（要删除的字段名）

MySQL根据某一个或者多个字段查找重复数据的sql语句

sql 查出一张表中重复的所有记录数据

表中有id和name 两个字段，查询出name重复的所有数据

SELECT
    * 
FROM
    xi a 
WHERE
    ( a.username ) IN (
    SELECT
        username 
    FROM
        xi 
    GROUP BY
        username 
HAVING
    count(*) > 1)

SELECT
    * 
FROM
    xi a 
WHERE
    ( a.username ) IN (
    SELECT
        username 
    FROM
        xi 
    GROUP BY
        username 
HAVING
    count(*) > 1)

查询出所有数据进行分组之后，和重复数据的重复次数的查询数据，先列下：

SELECT
    count( username ) AS '重复次数',
    username 
FROM
    xi 
GROUP BY
    username 
HAVING
    count(*)> 1 
ORDER BY
    username DESC

select count(username) as '重复次数',username from xi group by username having count(*)>1 order by username desc

查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断

SELECT
    * 
FROM
    people 
WHERE
    peopleId IN (
    SELECT
        peopleId 
    FROM
        people 
    GROUP BY
        peopleId 
HAVING
    count( peopleId ) > 1)

删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录

DELETE 
FROM
    people 
WHERE
    peopleId IN ( SELECT peopleId FROM people GROUP BY peopleId HAVING count( peopleId ) > 1 ) 
    AND rowid NOT IN (
    SELECT
        min( rowid ) 
    FROM
        people 
    GROUP BY
        peopleId 
HAVING
    count( peopleId )> 1)

查找表中多余的重复记录（多个字段）

SELECT
    * 
FROM
    vitae a 
WHERE
    ( a.peopleId, a.seq ) IN (
    SELECT
        peopleId,
        seq 
    FROM
        vitae 
    GROUP BY
        peopleId,
        seq 
HAVING
    count(*) > 1)

删除表中多余的重复记录（多个字段），只留有rowid最小的记录

DELETE 
FROM
    vitae a 
WHERE
    ( a.peopleId, a.seq ) IN ( SELECT peopleId, seq FROM vitae GROUP BY peopleId, seq HAVING count(*) > 1 ) 
    AND rowid NOT IN (
    SELECT
        min( rowid ) 
    FROM
        vitae 
    GROUP BY
        peopleId,
        seq 
HAVING
    count(*)> 1)

查找表中多余的重复记录（多个字段），不包含rowid最小的记录

SELECT
    * 
FROM
    vitae a 
WHERE
    ( a.peopleId, a.seq ) IN ( SELECT peopleId, seq FROM vitae GROUP BY peopleId, seq HAVING count(*) > 1 ) 
    AND rowid NOT IN (
    SELECT
        min( rowid ) 
    FROM
        vitae 
    GROUP BY
        peopleId,
        seq 
HAVING
    count(*)> 1)

在A表中存在一个字段“name”，而且不同记录之间的“name”值有可能会相同，需要查询出在该表中的各记录之间，“name”值存在重复的项；

各记录之间，“name”值存在重复的项；

Select Name,Count(*) From A Group By Name Having Count(*) > 1

如果还查性别也相同大则如下:

Select Name,sex,Count(*) From A Group By Name,sex Having Count(*) > 1

(三)

方法一

declare @max integer,@id integer
declare cur_rows cursor local for
select 主字段,count(*) from 表名 group by 主字段 having count(*) >;
open cur_rows fetch cur_rows into @id,@maxwhile @@fetch_status=0
begin
 select @max = @max -1
 set rowcount @max
 delete from 表名 where 主字段 = @id
fetch cur_rows into @id,@maxend
close cur_rows
set rowcount 0

方法二＂重复记录＂有两个意义上的重复记录，一是完全重复的记录，也即所有字段均重复的记录，二是部分关键字段重复的记录，比如Name字段重复，而其他字段不一定重复或都重复可以忽略。

　　1、对于第一种重复，比较容易解决，使用

select distinct * from tableName

就可以得到无重复记录的结果集。　　

如果该表需要删除重复的记录（重复记录保留1条），

可以按以下方法删除

select distinct * into #Tmp from tableName
drop table tableName
select * into tableName from #Tmp
drop table #Tmp

发生这种重复的原因是表设计不周产生的，增加唯一索引列即可解决。

2、这类重复问题通常要求保留重复记录中的第一条记录，操作方法如下　　假设有重复的字段为Name,Address，要求得到这两个字段唯一的结果集

select identity(int,1,1) as autoID, * into #Tmp from tableName
select min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoID
select * from #Tmp where autoID in(select autoID from #tmp2)

最后一个select即得到了Name，Address不重复的结果集（但多了一个autoID字段，实际写时可以写在select子句中省去此列）

(四)查询重复

select * from tablename where id in (select id from tablenamegroup by idhaving count(id) > 1)

对一个字段查找重复记录

根据sample_code字段找到重复记录

SELECT
    * 
FROM
    tb_table 
WHERE
    sample_code IN ( SELECT sample_code FROM tb_table GROUP BY sample_code HAVING COUNT( sample_code ) > 1 );

对多个字段查找重复记录（这里以2个为例）

根据name和code字段找到重复记录

SELECT
    * 
FROM
    ( SELECT *, CONCAT( NAME, CODE ) AS nameAndCode FROM tb_table ) t 
WHERE
    t.nameAndCode IN (
    SELECT
        nameAndCode 
    FROM
        ( SELECT CONCAT( NAME, CODE ) AS nameAndCode FROM tb_table ) tt 
    GROUP BY
        nameAndCode 
    HAVING
    count( nameAndCode ) > 1 
    )

SQL重置自增长

SQL的自增列挺好用,只是开发过程中一旦删除数据,标识列就不连续了写起来也很郁闷,所以查阅了一下标识列重置的方法发现可以分为三种:

--删除原表数据,并重置自增列,truncate方式也可以重置自增字段
truncate table tablename

--重置表的自增字段,保留数据 
DBCC CHECKIDENT (tablename,reseed,0)

什么是显式插入自增列？

--设置允许显式插入自增列
SET IDENTITY_INSERT tablename  ON

--当然插入完毕记得要设置不允许显式插入自增列
SET IDENTITY_INSERT tablename Off

往自增列插入显式值-SET IDENTITY_INSERT详解

    自增列默认是不能插入显式值的，给自增列插入值时，会报错误：当 IDENTITY\_INSERT 设置为 OFF 时，不能为表 'table1' 中的标识列插入显式值。

    可以通过SET IDENTITY\_INSERT语法来实现为表的自增列插入显式值，这在数据转移的时候尤为有效。

允许将显式值插入表的标识列中：

SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON | OFF }

参数:

database_name	指定的表所在的数据库的名称。
schema_name	表所属的架构的名称。
table	包含标识列的表的名称。

备注:

   任何时候，一个会话中只有一个表的 IDENTITY\_INSERT 属性可以设置为 ON。如果某个表已将此属性设置为 ON，则对另一个表发出 SET IDENTITY\_INSERT ON 语句时，SQL Server 2005 将返回一个错误信息，指出 SET IDENTITY\_INSERT 已设置为 ON，并报告已将其属性设置为 ON 的表。

如果插入值大于表的当前标识值，则 SQL Server 自动将新插入值作为当前标识值使用。

SET IDENTITY_INSERT 的设置是在执行或运行时设置的，而不是在分析时设置的。

示例：

我们先创建一个带有自增列的表：

create table hr_member(ID int identity(1,1) primary key,name varchar(100))

然后我们尝试往该表插入数据：

insert into hr_member(id,name) values (1,'wfs')

会报以下的错误：

当 IDENTITY_INSERT 设置为 OFF 时，不能为表 'hr_member' 中的标识列插入显式值。

设置identity_insert的值：

set identity_insert hr_member on

插入数据：

insert into hr_member(id,name) values (1,'wfs')

执行成功!

注意：在同一个会话中，只能有一个表的identity_insert可以设置为ON，我们再创建另一个表:

create table hr_member_1(ID int identity(1,1) primary key,name varchar(100))

然后执行:

set identity_insert hr_member on

set identity_insert hr_member_1 on

会报以下的错误：

表 'test.dbo.hr_member' 的 IDENTITY_INSERT 已经为 ON。无法为表 'hr_member_1' 执行 SET 操作。

修改代码：

set identity_insert hr_member off

set identity_insert hr_member_1 on

执行成功!

注意：我们在在对标识列执行插入操作时，一定要列出此标识列的列名：

如下代码：

set identity_insert hr_member on
insert into hr_member
select 2,'abc'

会报以下错误：

仅当使用了列列表并且 IDENTITY_INSERT 为 ON 时，才能为表'hr_member'中的标识列指定显式值。

修改代码：

set identity_insert hr_member on
insert into hr_member(ID,name)
select 2,'abc'

执行成功!

菜单

分享

SQL工作记录

case when then else end

CONCAT()的用法

mysql CONCAT(str1,str2,…)

mysql CONCAT_WS(separator,str1,str2,…)

mysql日期字段按照时间更新

mysql日期字段取消按照时间更新

MYSQL中UNION和UNION ALL的区别有哪些？

MYSQL自增主键ID重置

如何用sql语句复制一张表

1、复制表结构及数据到新表

2、只复制表结构到新表

3、复制旧表的数据到新表(假设两个表结构一样)

4、复制旧表的数据到新表(假设两个表结构不一样)

5、可以将表1结构复制到表2

6、可以将表1内容全部复制到表2

7、show create table 旧表;

数据库新增字段

数据库删除字段

MySQL根据某一个或者多个字段查找重复数据的sql语句

sql 查出一张表中重复的所有记录数据

表中有id和name 两个字段，查询出name重复的所有数据

查询出所有数据进行分组之后，和重复数据的重复次数的查询数据，先列下：

查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断

删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录

查找表中多余的重复记录（多个字段）

删除表中多余的重复记录（多个字段），只留有rowid最小的记录

查找表中多余的重复记录（多个字段），不包含rowid最小的记录

在A表中存在一个字段“name”，而且不同记录之间的“name”值有可能会相同，需要查询出在该表中的各记录之间，“name”值存在重复的项；

各记录之间，“name”值存在重复的项；

如果还查性别也相同大则如下:

SQL重置自增长

往自增列插入显式值-SET IDENTITY_INSERT详解

评论

SQL工作记录

Debian13安装脚本