SQL 语句使用记录

hresh 588 0

SQL 语句使用记录

在工作的过程中,会使用各种数据库,其中有些操作比较常用,为了加强记忆,便将相关 SQL 语句记录下来。以下记录涉及到 Oracle,Sql Server,MySQL,DB2等。

Oracle

SQL 语句查询字段拼接

首先创建一个 student 表,

CREATE TABLE `STUDENT` (
  `ID` VARCHAR2(12 BYTE) DEFAULT ' '  NOT NULL ,
  `NAME` VARCHAR2(50 BYTE) DEFAULT ' '  NOT NULL ,
  `CLASS_ID` NUMBER(5) DEFAULT 0  NOT NULL ,
  'SEX' VARCHAR2(12 BYTE) DEFAULT ' '  NOT NULL ,
  'AGE' NUMBER(5) DEFAULT 0  NOT NULL ,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `test`.`STUDENT` (`ID`, `NAME`, `CLASS_ID`,'SEX','AGE') VALUES ('1', '叶良辰', '1','男','21');
INSERT INTO `test`.`STUDENT` (`ID`, `NAME`, `CLASS_ID`,'SEX','AGE') VALUES ('2', '', '龙傲天','男','23');

查询时,字段拼接有以下两种方法:

1、

SELECT CONCAT(NAME,SEX) FROM SUDENT
SELECT CONCAT(CONTACT(NAME,'-'),SEX) FROM STUDENT

2、

SELECT NAME || '-' || SEX FROM STUDENT

SQL 语句查询根据字段值返回不同的内容

SELECT CASE
            WHEN AGE > 22 THEN  '大叔'
            ELSE '哥哥'
            END
FROM STUDENT

#当 age 为 21 时,返回”小小小“
SELECT CASE AGE
            WHEN 21 THEN  '小小小'
            WHEN 23 THEN  '哒哒哒'
            ELSE '咯咯咯'
            END
FROM STUDENT

SQL 字符串模糊查询并替换

SELECT * FROM STUDENT WHERE NAGE LIKE '%傲%'
UPDATE SET NAME = REPLACE(NAME,'傲','奥') WHERE NAGE LIKE '%傲%'

判断字符串是否全为数字

1、使用 trim+translate 函数:

select * from dual where trim(translate(column,'0123456789',' ')) is NULL;

这里要注意的是:translate 函数的第三个参数是一个空格,不是'', 因为translate的第三个参数如果为空的话,那么永远返回'',这样的就不能达到过滤纯数字的目的。这样把所有的数字都转化为空格,如果全部是由数 构成,那么一旦trim后自然是空,实现了上述目标。当然如果想排除空项的话,可以这样写:

select * from dual where trim(translate(nvl(column,'x'),'0123456789',' ')) is NULL;--x 表示任何'0-9'以外的字符。

NVL函数是一个空值转换函数

NVL(exp1,exp2)函数,如果exp1为空值,则返回exp2;否则返回exp1。

注意:当 column 的值为空格时,也会被筛选到,所以比较好的方法是使用 replace+translate 函数。

2、使用 replace+translate 函数:

select * from dual where replace(translate(column,'0123456789','0'),'0','') is NULL;

3、使用 regexp_like 函数:

select * from dual where regexp_like(column,'^[0-9]+[0-9]$')

这里需要注意的是:regexp_like 函数不是在所有的 Oracle 版本中都能使用。regexp_like 是 Oracle 支持正则表达式的函数中的一个,共有四个函数:regexp_like ,regexp_replace,regexp_instr,regexp_substr。

DB2

DB2 转义字符

百分号(%)转义:
select * from table where col1 like '%/%%' escape '/'
这个语句的意思就是:查找出col1中包含 了百分号(%)字符的记录。其中escape定义了转义字符"/"

下划线(_)转义:
select * from table where col1 like '%!_%' escape '!'
这个语句的意思就是:查找出col1中包含 了下划线(_)字符的记录。其中escape定义了转义字符"!"

DB2 执行 SQL 语句错误代码大全

史上最全的 DB2 错误代码大全

MySQL

添加或创建索引

建立索引的目的是:提高对表的查询速度;对表有关列的取值进行检查。
注:提高查询速度的方法还有在表上建立主键,主键与唯一索引的差别在于唯一索引可以空,主键为非空。

1.添加PRIMARY KEY(主键索引)

mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

2.添加UNIQUE(唯一索引)

mysql>ALTER TABLE `table_name` ADD UNIQUE (
`column`
)

3.添加INDEX(普通索引)

mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

4.添加FULLTEXT(全文索引)

mysql>ALTER TABLE `table_name` ADD FULLTEXT ( `column`)

5.添加多列索引

mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

创建索引

CREATE [unique] INDEX [user.]index
ON [user.]table (column [ASC | DESC] [,column
[ASC | DESC] ] ... )
[CLUSTER [scheam.]cluster]
[INITRANS n]
[MAXTRANS n]
[PCTFREE n]
[STORAGE storage]
[TABLESPACE tablespace]
[NO SORT]
Advanced

其中:

  • schema ORACLE模式,缺省即为当前帐户
  • index 索引名
  • table 创建索引的基表名
  • column 基表中的列名,一个索引最多有16列,long列、long raw
    列不能建索引列

  • DESC、ASC 缺省为ASC即升序排序

  • CLUSTER 指定一个聚簇(Hash cluster不能建索引)
  • INITRANS、MAXTRANS 指定初始和最大事务入口数
  • Tablespace 表空间名
  • STORAGE 存储参数,同create table 中的storage.
  • PCTFREE 索引数据块空闲空间的百分比(不能指定pctused)
  • NOSORT 不(能)排序(存储时就已按升序,所以指出不再排序)

判断字符串是否全为数字

1、 通过【0+col】判断其长度 (该方法存在局限性)

SELECT * FROM learn_resource WHERE LENGTH(0+author) = LENGTH(author)
Sql语句 结果
SELECT LENGTH('abc') as val; 3
SELECT LENGTH(0+'abc') as val; 1
SELECT 0+'abc' as val; 0
SELECT LENGTH('30') as val; 2
SELECT LENGTH(0+'30') as val; 2
SELECT 0+'30' as val; 30

MySQL 数据库中关于字符串的存储类型为 char 类型,当字段值为字符串时,0 加上字符串得到的结果为 0。当字段值为数字时,数字加 0 后仍等于自身。

当字段中的内容为字母或特殊字符时,即长度为1的情况下,上述判别方法就不适用了。

2、 通过正则表达式

SELECT * FROM learn_resource WHERE (author REGEXP '[^0-9.]') = 0

{String} REGEXP '[^0-9.]'的作用就是如果 String 中含有不是 0-9 之间的数字或者是小数点时,返回 true(1) ,反之则返回 false(0)。返回值是0的时候正好符合我们的要求。

扩展:判断字符串是否是指定格式的小数

SELECT * FROM TEST WHERE RESULT REGEXP '(^[0-9]+.[0-9]+)|(^[0-9])'

上述方法既可以判断整数形式,也可以判断小数形式。

后续会继续更新关于 SQL 语句的操作。

发表评论 取消回复
表情 图片 链接 代码

分享