学习笔记之SQL

向数据库插入数据：

第一个表单没有指定要插入数据的列的名称，只提供要插入的值，即可添加一行新的数据：

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...); 

第二种，如果要为表中的所有列添加值，则不需要在SQL查询中指定列名称。 但是，请确保值的顺序与表中的列顺序相同。

INSERT INTO语法如下所示： INSERT INTO table_name VALUES (value1, value2, value3, ...);

您可以通过另一个表上的SELECT语句查询出来的字段值，然后将数据填充到本表中，条件是另一个表所查询的字段与本表要插入数据的字段是一一对应的。

INSERT INTO first_table_name [(column1, column2, ... columnN)] 
SELECT column1, column2, ...columnN FROM second_table_name [WHERE condition]; 

SQL INSERT INTO SELECT 语法 我们可以将所有列从一个表中复制到另一个已经存在的表中：

INSERT INTO table2 SELECT * FROM table1;

或者我们可以把想要的列复制到另一个现有的表中：

INSERT INTO table2 (column_name(s)) SELECT column_name(s) FROM table1;

创建表的时候，NULL 的基本语法如下：

SQL> CREATE TABLE CUSTOMERS( ID INT NOT NULL,
 NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, 
ADDRESS CHAR (25) , 
SALARY DECIMAL (18, 2), 
PRIMARY KEY (ID) )

这里，NOT NULL表示对于给定列，必须按照其数据类型明确赋值。有两列并没有使用 NOT NULL 来限定，也就是说这些列可以为 NULL。值为 NULL 的字段是在记录创建的过程中留空的字段。

top/limit/rownum示例 以下SQL语句从"Customers"表中选择前三个记录： 

SELECT TOP 3 * FROM Customers;

以下SQL语句显示了使用LIMIT子句的等效示例： 

SELECT * FROM Customers LIMIT 3;

以下SQL语句显示了使用ROWNUM的等效示例： 

SELECT * FROM Customers WHERE ROWNUM <= 3;

 

SQL IN 运算符 IN运算符允许您在WHERE子句中指定多个值。 IN运算符是多个OR条件的简写。

SQL IN 语法 SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, ...);

或者 SELECT column_name(s) FROM table_name WHERE column_name IN (SELECT STATEMENT);

DROP INDEX 语句

DROP INDEX 语句用于删除表中的索引。

用于 MS Access 的 DROP INDEX 语法： DROP INDEX index_name ON table_name

用于 MS SQL Server 的 DROP INDEX 语法： DROP INDEX table_name.index_name

用于 DB2/Oracle 的 DROP INDEX 语法： DROP INDEX index_name

用于 MySQL 的 DROP INDEX 语法： ALTER TABLE table_name DROP INDEX index_name

DROP TABLE 语句

DROP TABLE 语句用于删除表。

DROP TABLE table_name DROP DATABASE 语句

DROP DATABASE 语句用于删除数据库。

DROP DATABASE database_name TRUNCATE TABLE 语句

 

如果我们只需要删除表中的数据，而不删除表本身，那么我们该怎么做？

使用TRUNCATE TABLE语句： TRUNCATE TABLE table_name

 

SQL ALTER TABLE 语法 　若要向表中添加列，请使用以下语法：

ALTER TABLE table_name ADD column_name datatype 　

若要删除表中的列，请使用以下语法 （请注意，一些数据库系统不允许这样删除数据库表中的列）：

ALTER TABLE table_name DROP COLUMN column_name 　

若要更改表中列的数据类型，请使用以下语法：

SQL Server / MS Access： ALTER TABLE table_name ALTER COLUMN column_name datatype

My SQL / Oracle： ALTER TABLE table_name MODIFY COLUMN column_name datatype

用于 MySQL 的语法

以下SQL语句将 "Persons" 表中的“ID”列定义为自动递增（auto-increment）主键字段：

CREATE TABLE Persons 
( ID int NOT NULL AUTO_INCREMENT,
 LastName varchar(255) NOT NULL, 
 FirstName varchar(255), 
 Address varchar(255),
 City varchar(255), 
 PRIMARY KEY (ID) )

MySQL使用AUTO_INREMENT关键字来执行自动增量（ auto-increment ）任务。

默认情况下，AUTO_INREMENT的起始值为1，每个新记录增加1。

NOT NULL 约束：保证列中数据不能有 NULL 值 DEFAULT 约束：提供该列数据未指定时所采用的默认值 UNIQUE 约束：保证列中的所有数据各不相同 主键约束：唯一标识数据表中的行/记录 外键约束：唯一标识其他表中的一条行/记录 CHECK 约束：此约束保证列中的所有值满足某一条件 索引：用于在数据库中快速创建或检索数据

SQL 中有多种不同的连接：

SQL JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段

就是先确定一个主表作为结果集，然后，把其他表的行有选择性地“连接”在主表结果集上。

内连接（INNER JOIN）：当两个表中都存在匹配时，才返回行。（选择两个表都有的行返回）

左连接（LEFT JOIN）：返回左表中的所有行，即使右表中没有匹配的行。 右连接（RIGHT JOIN）：返回右表中的所有行，即使左表中没有匹配的行。

全连接（FULL JOIN）：只要某一个表存在匹配，就返回行。

笛卡尔连接（CARTESIAN JOIN）：返回两个或者更多的表中记录集的笛卡尔积。

JOIN的执行顺序

以下是JOIN查询的通用结构：

SELECT <row_list> 
FROM <left_table> <inner|left|right> 
JOIN <right_table> ON <join condition> 
WHERE <where_condition>

它的执行顺序如下(SQL语句里第一个被执行的总是FROM子句)：

FROM:对左右两张表执行笛卡尔积，产生第一张表vt1。行数为n*m

（n为左表的行数，m为右表的行数)

ON:根据ON的条件逐行筛选vt1，将结果插入vt2中

JOIN:添加外部行。

如果指定了LEFT JOIN(LEFT OUTER JOIN)，则先遍历一遍左表的每一行，

其中不在vt2的行会被插入到vt2，该行的剩余字段将被填充为NULL，形成vt3；

如果指定了RIGHT JOIN也是同理。

如果指定的是INNER JOIN，则不会添加外部行，上述插入过程被忽略，

vt2=vt3（因此INNER JOIN的过滤条件放在ON或WHERE里执行结果是没有区别的）

WHERE:对vt3进行条件过滤，满足条件的行被输出到vt4

SELECT:取出vt4的指定字段到vt5

PS：其实我们从语义上就能看出LEFT JOIN和RIGHT JOIN没什么差别，

两者的结果差异取决于左右表的放置顺序

SQL INTERSECT 子句：用于组合两个 SELECT 语句，但是只返回两个 SELECT 语句的结果中都有的行。

动态SQL：

1.<where></where> 动态添加where条件

<if test=""></if> 动态判断，判断传入的参数是否正确

select * from emp 
<where> 
    <if test="name != null and name !=''"> 
        name like concat('%',#{name},'%') 
    </if> 
    <if test="job != null and job != ''"> 
        and job = #{job} 
    </if> 
</where>

2.<foreach collection="ids" item="id" separator="," open="(" close=")"></froeach>

<insert id="addBatchSave"> 
    insert into tbl_employee(last_name,email,gender) 
    values 
    <foreach collection="emps" item="emppp" separator=","> 
        (#{emppp.lastName},#{emppp.email},#{emppp.gender}) 
    </foreach> 
</insert>

3.<set></set> 动态更新

<update id="updateEmp"> 
    update emp 
    <set> 
        <if test="name != null and name != ''"> 
            name=#{name}, 
        </if> 
        <if test="job != null and job != ''"> 
            job=#{job} 
        </if>
    </set> 
    where id=#{id} 
</update>

4.<sql id="idName"></sql> 将经常用的片段用sql进行封装，引用是<includ refid="idName">

<sql id="insertColumn"> 
    <if test="_databaseId=='mysql'"> 
        id,last_name,email,gender 
    </if> 
    <if test="_databaseId=='oracle'"> 
        id,last_name,email,department_id 
    </if> 
</sql> 
<insert id="addEmployeeBysql" databaseId="mysql"> 
    insert into tbl_employee( <!-- 引用外部定义的sql --> 
    <include refid="insertColumn" ></include> ) 
        values (#{id},#{lastName},#{email},#{gender}) 
</insert>

5.<trim prefix="where" prefixOverrides="" suffix="" suffixOverrides="and">

prefix="":前缀：trim标签体重视整个字符串 拼串后的结果。prifix给拼串后的整个字符串加一个前缀

prifixOverrides="":前缀覆盖：去掉整个字符串前面多余的字符

suffix="":后缀：给拼串后的的整个字符串加一个后缀

suffixOverrides="":后缀覆盖：去掉整个字符串后面多余的字符

用prefix的值替换整体开头部分，用suffix替换整体的最后部分。

select * from emp 
    <trim prefix="where" prefixOverrides="" suffix="" suffixOverrides="and"> 
        <if test="job != null and job != ''"> 
            job=#{job} and 
        </if> 
        <if test="topid != null and topid != ''"> 
            topid=#{topid} and 
        </if> 
    </trim>

6.<chose><when test=""></when><when test=""></when></chose>

第一个when成立时，不会往下执行。只要前面一个不成立，就往下进行判断，找到成立的结束。

select * from emp 
<where> 
    <choose> 
        <when test="name != null and name != ''"> 
            name like concat('%',#{name},'%') 
        </when> 
        <when test="job != null and job !=''"> 
            and job=#{job} 
        </when> 
    </choose> 
</where>

join on后面的条件和where条件的区别：

1、select * from tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name='AAA' 2、select * from tab1 left join tab2 on (tab1.size = tab2.size and tab2.name=‘AAA’) 第一条SQL的过程： 1、中间表on条件：tab1.size = tab2.size

 

tab1.id	tab1.size	tab2.size	tab2.name
1	10	10	AAA
2	20	20	BBB
2	20	20	CCC
3	30	(null)	(null)

2、再对中间表过滤where条件：tab2.name=‘AAA’

 

tab1.id	tab1.size	tab2.size	tab2.name
1	10	10	AAA

第二条SQL的过程： 1、中间表on条件：tab1.size = tab2.size  and  tab2.name = ‘AAA’

 

tab1.id	tab1.size	tab2.size	tab2.name
1	10	10	AAA
2	20	(null)	(null)
3	30	(null)	(null)

sql中date、date_format、str_to_date的区别：

1、date(datestring)

datestring是合法的日期表达式

如：SELECT date('2017-02-09 15:25:46.635') FROM dual; -->'2017-02-09'

2、date_format(datestring，format)

datestring参数是合法的日期。format 规定日期/时间的输出格式。

如：SELECT STR_TO_DATE('2017-02-09 15:25:46.635','%Y-%m-%d')

FROM dual; -->'2017-02-09'

3.str_to_date(str,format)

使用函数str_to_date (str,format),其中，format格式依然如同上述所示。

值得注意的是，str的格式需要和format的格式保持一致，例如 str_to_date('2017-08-07 16:56:12'，‘%Y-%m-%d %H:%i:%s’),若是前后不一致则会报错。不再赘述。

 

SQL优化：常见优化规则

2.1 表连接数

• 连接的表越多，性能越差
• 可能的话，将连接拆分成若干个过程逐一执行
• 优先执行可显著减少数据量的连接，既降低了复杂度，也能够容易按照预期执行
• 如果不可避免多表连接，很可能是设计缺陷
• 外链接效果差，因为必须对左右表进行表扫描
• 尽量使用inner join查询

2.2 使用临时表

如果不可避免，可以考虑使用临时表或表变量存放中间结果。

2.3 少用子查询

2.4 视图嵌套

不要过深,一般视图嵌套不要超过2个为宜。

3、SQL编写注意事项

3.1 NULL列

Null列使用索引没有意义，任何包含null值的列都不会被包含在索引中。因此where语句中的is null或is not null的语句优化器是不允许使用索引的。

3.2 concat或||

concat或||是mysql和oracle的字符串连接操作，如果对列进行该函数操作，那么也开会忽略索引的使用。比较下面的查询语句：

-- 忽律索引 select ... from .. where first_name || '' || last_name = 'bill gates' ;

-- 使用索引 select ... from .. where first_name = 'bill' and last_name = 'bill gates' ;

3.3 like

通配符出现在首位，无法使用索引，反之可以。

-- 无法使用索引 select .. from .. where name like '%t%' ;

-- 可以使用索引 select .. from .. where name like 't%' ;

3.4 order by

order by子句中不要使用非索引列或嵌套表达式，这样都会导致性能降低。

3.5 Not运算

not运算无法使用索引，可以改成其他能够使用索引的操作。如下：

-- 索引无效 select .. from .. where sal != 3000 ;

-- 索引生效 select .. from .. where sal < 3000 or sal > 3000;

3.6 where与having

select .. from .. on .. where .. group by .. having .. order by .. limit ..，以上是sql语句的语法结构，其中on、where和having是有过滤行为的，过滤行为越能提前完成就越可以减少传递给下一个阶段的数据量，因此如果在having中的过滤行为能够在where中完成，则应该优先考虑where来实现。

3.7 exists替代in

not in是最低效的，因为要对子查询的表进行全表扫描。可以考虑使用外链接或not exists。如下：

-- 正确 SELECT * FROM EMP

WHERE EMPNO > 0 AND EXISTS (SELECT ‘X' FROM DEPT

WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB')

-- 错误 SELECT * FROM EMP

WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB')

3.8 索引

索引的好处可以实现折半查找，时间复杂度是O(log2n)

，但是也有成本，需要额外的空间存放索引数据，并且每次insert、update和delete都会对索引进行更新，因此会多增加4、5次的磁盘IO。所以给一些不必要使用索引的字段增加索引，会降低系统的性能。对于oracle来讲，SQL语句尽量大写，内部需要向将小写转成大写，再执行。

不要在索引列上使用函数，这样会停止使用索引，进行全表扫描，如下：

-- 错误 SELECT … FROM DEPT WHERE SAL * 12 > 25000;

-- 正确 SELECT … FROM DEPT WHERE SAL > 25000/12;

3.9 >与>=

-- 直接定位到4的记录(推荐) select .. from .. where SAL >= 4 ;

-- 先定位到3，再向后找1个(不推荐) select .. from .. where SAL > 3 ;

3.10 union代替or

在索引列上，可以使用union替换or操作。索引列上的or操作会造成全表扫描。

-- 高效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION

WHERE LOC_ID = 10 UNION SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION

WHERE REGION = 'MELBOURNE'

-- 低效: SELECT LOC_ID ,LOC_DESC ,REGION FROM LOCATION

WHERE LOC_ID=10 OR REGION ='MELBOURNE'

3.11 is null & is not null

如果列可空，避免使用索引。对于多个列使用的索引，起码保证至少有个列不为空。对于多列索引，只有访问了第一个列才会启用索引，如果访问后面的列则使用的是全表扫描。

-- 低效: (索引失效) SELECT .. FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL;

-- 高效: (索引有效) SELECT .. FROM DEPARTMENT WHERE DEPT_CODE >=0;

3.12 union & union all

union具有去重的操作，增加了计算时间。union all不需要去重，但会包含相同记录。同样功能下，首选union all操作。

数据库视图：视图是从一个或几个基本表(视图)导出的表，它与基本表不同，是一个虚表；

CREATE VIEW Student（Sno，Sname，Ssex，Sage，Sdept） AS SELECT SX.Sno，SX.Sname，SY.Ssex，SX.Sage，SY.Sdept FROM SX，SY WHERE SX.Sno=SY.Sno；

 

简单存储过程创建：

create procedure procedureName()

begin

select name from user;

end;

调用存储过程：

call procedureName;

删除存储过程：

DROP PROCEDURE IF EXISTS proceduceName;

存储过程之参数：

in :向存储过程传入参数

out ：从存储过程中传出参数

inout 存储过程传入传出参数