数据库

1.  wm_concat(column)   //实现字段合并

 

 

 

2. in 操作符允许我们在字段选取中选取多个值

 

 

3. to_char() 是oracle 的字符转换函数。to_char(value，'format') value是待转化的值，'format' 是转化后的 pattern。

 

4. 日期转换：

 

select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual;

 

5.这里样式 fm9900.0099的包括如下含义：

 

     输出的字符串总共占了9位，小数点前4位，小数点后4位。

    9代表含义，如果这个位置是数字就显示数字，否则就用空格替代占位，总之要占住这个位置。

    0代表含义，如果这个位置是数字就显示数字，否则就用0替代占位，总之要占住这个位置。

   

    to_char(1210.73, '9999.9')      would return '1210.7'

    to_char(1210.73, '9,999.99')      would return '1,210.73'

    to_char(1210.73, '$9,999.00')      would return '$1,210.73'

    to_char(21, '000099')      would return '000021'

 

 

6.  to_char(sysdate, 'FMMonth DD, YYYY');      would return 'July 9, 2003'

    to_char(sysdate, 'FMMON DDth, YYYY');      would return 'JUL 9TH, 2003'

    to_char(sysdate, 'FMMon ddth, YYYY');      would return 'Jul 9th, 2003'

 

7，sql通配符：

 

   %      替代 0 个或多个字符

   _      替代一个字符

     [charlist]      字符列中的任何单一字符

     [^charlist]

      或

     [!charlist]      不在字符列中的任何单一字符

 

8.其中搭配以上通配符可以让LIKE命令实现多种技巧：

 

1、LIKE'Mc%' 将搜索以字母 Mc 开头的所有字符串（如 McBadden）。

 

2、LIKE'%inger' 将搜索以字母 inger 结尾的所有字符串（如 Ringer、Stringer）。

 

3、LIKE'%en%' 将搜索在任何位置包含字母 en 的所有字符串（如 Bennet、Green、McBadden）。

 

4、LIKE'_heryl' 将搜索以字母 heryl 结尾的所有六个字母的名称（如 Cheryl、Sheryl）。

 

5、LIKE'[CK]ars[eo]n' 将搜索下列字符串：Carsen、Karsen、Carson 和 Karson（如 Carson）。

 

6、LIKE'[M-Z]inger' 将搜索以字符串 inger 结尾、以从 M 到 Z 的任何单个字母开头的所有名称（如 Ringer）。

 

7、LIKE'M[^c]%' 将搜索以字母 M 开头，并且第二个字母不是 c 的所有名称（如MacFeather）

 

 

9.select * from Websites where name in ('Google','菜鸟教程');

   可以转换成 = 的表达：

  select * from Websites where name='Google' or name='菜鸟教程';

 

 

10.concat（str1，str2）  //返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。

 

eg：| concat('11','22','33'）  结果：112233

 

 

11.repeat()函数

 

用来复制字符串,如下'ab'表示要复制的字符串，2表示复制的份数

 

    mysql> select repeat('ab',2);

      repeat('ab',2)

         abab         

 

12.不同的 SQL JOIN：

 

    在我们继续讲解实例之前，我们先列出您可以使用的不同的 SQL JOIN 类型：

 

    INNER JOIN：如果表中有至少一个匹配，则返回行

    LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行

    RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行

    FULL JOIN：只要其中一个表中存在匹配，则返回行

 

 

13. inner join <= min(left join, right join)

    full join >= max(left join, right join)

    当 inner join < min(left join, right join) 时， full join > max(left join, right join)

 

14.在使用 left jion 时，on 和 where 条件的区别如下：

    1、 on 条件是在生成临时表时使用的条件，它不管 on 中的条件是否为真，都会返回左边表中的记录。

    2、where 条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有 left join 的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

 

 

15. left join : 左连接，返回左表中所有的记录以及右表中连接字段相等的记录。

    right join : 右连接，返回右表中所有的记录以及左表中连接字段相等的记录。

    inner join : 内连接，又叫等值连接，只返回两个表中连接字段相等的行。

    full join : 外连接，返回两个表中的行：left join + right join。

    cross join : 结果是笛卡尔积，就是第一个表的行数乘以第二个表的行数。

 

 

16.SQL LEFT JOIN关键字

    LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。

 

 

17.SQL RIGHT JOIN关键字

     RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。

 

 

18.DESC 降序   ASC 升序

 

19.SQL FULL JOIN 关键字

     FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行.

     FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。

 

20.UNION 操作符：

     UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

     请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。

 

     UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

 

     UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

 

    

21.使用UNION命令时需要注意，只能在最后使用一个ORDER BY命令，是将两个查询结果合在一起之后，再进行排序！绝对不能写两个ORDER BY命令。

 

另外，在使用ORDER BY排序时，注意两个结果的别名保持一致，使用别名排序很方便。当然也可以使用列数。

 

 

22.SQL SELECT INTO 语句：

 

SELECT INTO 语句从一个表复制数据，然后把数据插入到另一个新表中。

 

  eg：我们可以复制所有的列插入到新表中：

SELECT *

INTO newtable [IN externaldb]

FROM table1;

 

或者只复制希望的列插入到新表中：

SELECT column_name(s)

INTO newtable [IN externaldb]

FROM table1;

 

 

23.SQL SELECT INTO 实例

 

创建 Websites 的备份复件：

SELECT *

INTO WebsitesBackup2016

FROM Websites;

 

只复制一些列插入到新表中：

SELECT name, url

INTO WebsitesBackup2016

FROM Websites;

 

只复制中国的网站插入到新表中：

SELECT *

INTO WebsitesBackup2016

FROM Websites

WHERE country='CN';

 

复制多个表中的数据插入到新表中：

SELECT Websites.name, access_log.count, access_log.date

INTO WebsitesBackup2016

FROM Websites

LEFT JOIN access_log

ON Websites.id=access_log.site_id;

 

提示：SELECT INTO 语句可用于通过另一种模式创建一个新的空表。只需要添加促使查询没有数据返回的 WHERE 子句即可：

SELECT *

INTO newtable

FROM table1

WHERE 1=0;

 

 

24. SQL INSERT INTO SELECT 语句

通过 SQL，您可以从一个表复制信息到另一个表。

INSERT INTO SELECT 语句从一个表复制数据，然后把数据插入到一个已存在的表中。

 

  语法：我们可以从一个表中复制所有的列插入到另一个已存在的表中：

INSERT INTO table2

SELECT * FROM table1;

 

或者我们可以只复制希望的列插入到另一个已存在的表中：

INSERT INTO table2

(column_name(s))

SELECT column_name(s)

FROM table1;

 

 

25.SQL CREATE DATABASE 语句：

   创建数据库

 

 

26.在 SQL 中，我们有如下约束：

 

    NOT NULL - 指示某列不能存储 NULL 值。

    UNIQUE - 保证某列的每行必须有唯一的值。

    PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。

    FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。

    CHECK - 保证列中的值符合指定的条件。

    DEFAULT - 规定没有给列赋值时的默认值。

 

 

27.SQL UNIQUE 约束

 

UNIQUE 约束唯一标识数据库表中的每条记录。

 

UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。

 

PRIMARY KEY 约束拥有自动定义的 UNIQUE 约束。

 

请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

 

 

28.SQL PRIMARY KEY 约束

 

PRIMARY KEY 约束唯一标识数据库表中的每条记录。

 

主键必须包含唯一的值。

 

主键列不能包含 NULL 值。

 

每个表都应该有一个主键，并且每个表只能有一个主键。

 

 

29.SQL CHECK 约束：

    CHECK 约束用于限制列中的值的范围。

 

如果对单个列定义 CHECK 约束，那么该列只允许特定的值。

如果对一个表定义 CHECK 约束，那么此约束会基于行中其他列的值在特定的列中对值进行限制。

 

 

30.SQL DEFAULT约束：

    DEFAULT 约束用于向列中插入默认值。

    如果没有规定其他的值，那么会将默认值添加到所有的新记录

  eg：CREATE TABLE Persons

   (

    P_Id int NOT NULL,

    LastName varchar(255) NOT NULL,

    FirstName varchar(255),

    Address varchar(255),

    City varchar(255) DEFAULT 'Sandnes'

    )

 

 

31.索引：使数据库应用程序可以更快的查找数据，在不读取整个表的情况下

 

您可以在表中创建索引，以便更加快速高效地查询数据。

 

用户无法看到索引，它们只能被用来加速搜索/查询。

 

注释：更新一个包含索引的表需要比更新一个没有索引的表花费更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引。

 

 

32.ALTER TABLE 语句用于在已有的表中添加，删除或修改列

 

 

33.我们想要删除 "Person" 表中的 "DateOfBirth" 列。

 

我们使用下面的 SQL 语句：

ALTER TABLE Persons

DROP COLUMN DateOfBirth

 

 

34.ORDER BY 多列的时候，先按照第一个column name排序，在按照第二个column name排序；如上述教程最后一个例子：

• 1）、先将country值这一列排序，同为CN的排前面，同属USA的排后面；

• 2）、然后在同属CN的这些多行数据中，再根据alexa值的大小排列。

• 3）、ORDER BY 排列时，不写明ASC DESC的时候，默认是ASC。

 

 

35.group by   ...   having  ...   的用法

查找雇员平均工资大于3000的部门的最高薪水和最低薪水：

 

    SELECT dept,MAX(salary) AS MAXIMUM,MIN(salary) AS MINIMUM

    FROM STAFF

    GROUP BY dept

    HAVING AVG(salary) > 3000

    ORDER BY dept

---------------------

作者：bingogirl

来源：CSDN

原文：https://blog.csdn.net/bingogirl/article/details/52559302

版权声明：本文为博主原创文章，转载请附上博文链接！

 

 

36.

sql执行效率小结--待持续新加更新

用EXISTS替代IN

用NOT EXISTS替代NOT IN

用表连接[left、right、inner]查询替换EXISTS

 

37.

'%a'    //以a结尾的数据

'a%'    //以a开头的数据

'%a%'    //含有a的数据

‘_a_’    //三位且中间字母是a的

'_a'    //两位且结尾字母是a的

'a_'    //两位且开头字母是a的

 

38.

如果里面包括 _ % 怎么转义，查下，可以通过 escape ：

select * from username where 用户名 like '段_%' -- 会查出来段煜 段鑫

 

select * from username where 用户名 like '段\_%' escape '\' -- 通过 \转义,只能查出来 段_煜

 

 

38.   AND (substr(a.OPERATE_TIME,0,10)>=P_BEG_DATE OR P_BEG_DATE IS NULL)

        AND (substr(a.OPERATE_TIME,0,10)<=P_END_DATE OR P_END_DATE IS NULL)

1）substr函数格式   (俗称：字符截取函数)

　　格式1： substr(string string, int a, int b);

　　格式2：substr(string string, int a) ;

解释：

    格式1：

        1、string 需要截取的字符串

        2、a 截取字符串的开始位置（注：当a等于0或1时，都是从第一位开始截取）

        3、b 要截取的字符串的长度

 

    格式2：

        1、string 需要截取的字符串

        2、a 可以理解为从第a个字符开始截取后面所有的字符串。

 

39.NVL( string1, replace_with)

功能：如果string1为NULL，则NVL函数返回replace_with的值，否则返回string1的值，如果两个参数都为NULL ，则返回NULL。