SQL学习笔记（3）

1. 插入数据 INSERT INTO

1.1 插入完整的行

INSERT INTO customers
VALUES(100,
      'Tony',
      'USA',
      NULL,
      NULL);

这个例子将一个新的顾客插入到customers表中。存储到表中的每一列数据由VALUES给出，必须给每一列提供一个值。如果某列没有值，就要用NULL（假定该列允许空值NULL）。各列必须以他们在表中定义的顺序填充。

上面的方式有个缺点：过于依赖列的顺序，因此可以使用以下形式

INSERT INTO customers(cust_id,cust_name,cust_city,cust_qq,cust_email)
VALUES(100,
      'Tony',
      'USA',
      NULL,
      NULL);

DBMS将用VALUES中的相应值填充表名后面的对应列。这样就使得插入结果不会随着表的结构改变而改变。

1.2 插入部分行

当使用明确列名来插入行（如上面的例子）时，也可以通过省略部分列来插入部分信息。这表示可以只给某些列提供值，其他列不提供值

INSERT INTO customers(cust_id,cust_name,cust_city)
VALUES(100,
      'Tony',
      'USA');

这个例子没有给cust_qq和cust_email提供值，即插入了部分行。

可以在INSERT操作中省略某些行，但是省略的行必须满足以下某个条件

• 该列定义为允许NULL
• 在表定义中给出默认值，这表示如果不给出值，就使用默认值。

1.3 插入检索出的值 INSERT SELECT

INSERT可以配合SELECT语句，将SELECT出来的结果插入表中

INSERT INTO customers(cust_id,cust_name,cust_city)
SELECT cust_id,cust_name,cust_city
FROM custnew;

上面的例子将从custnew表中检索出的结果插入customers表中。

INSERT SELECT中的表名:

这个例子中select出的列名与insert的列名一致，但其实不一定要列名匹配。实际上DBMS不关心SELECT返回的列名，因为它使用列的位置，因此SELECT的第一列将用来填充INSERT的第一列。。。

1.4 从一个表创建并复制到另一个表

使用CREATE SELECT语句

CREATE TABLE custcopy AS SELECT * FROM customer;

上面的例子将SELECT检索结果复制到一个新的表中。

2. 更新数据 UPDATE

不要省略WHERE子句，因为如果省略WHERE子句就是更新所有行；而如果有WHERE子句就是更新过滤出来的指定行

UPDATE user
SET username = 'wu',password='di'
WHERE username = 'wudi';

过滤出username为’wudi’的行，并将这些行的列值进行更新

如果要删除某个列的值，可以在SET子句中将该列值设为NULL（如果允许该列值为NULL的话）

UPDATE user
SET username = NULL -- 将username列设为NULL
WHERE username = 'wu';

需要注意NULL与空字符串’'不同：

空字符串用’'表示，是一个值；而NULL表示没有值

3. 删除数据

不要省略WHERE子句，因为如果省略WHERE子句就是删除所有行；而如果有WHERE子句就是删除过滤出来的指定行

DELETE FROM user
WHERE username = 'wu'; -- 删除user表中username为'wu'的行

注意DELETE可以删除表中行，甚至表中所有行，但是不能删除表本身。

4. 创建和操作表

4.1 创建表 CREATE TABLE

在创建表时必须保证该表名不存在。为了防止意外覆盖旧的表（比如create table 表名与一个现存表名一致），要求首先手动删除该表，然后再重建它，而不是简单地由创建表语句来覆盖。

格式：

CREATE TABLE 表名
(
	列名 数据类型 约束（是否允许为NULL、默认值等）,
    ...
)

例子：

CREATE TABLE products
(
	id 		CHAR(10) 		NOT NULL, -- 不能为NULL值
    name 	CHAR(25) 		NOT NULL, -- 不能为NULL值
    price 	DECIMAL(8,2)	NULL	  -- 可以为NULL值
 )

每个列要么是NULL（允许是NULL值）要么是NOT NULL（不允许是NULL值）。NULL是默认设置，如果没有写NULL或NOT NULL，DBMS会按照NULL处理。

注意，由于主键是其值唯一标识表中每一行的列，因此主键不能是NULL的

创建带主键的表：PRIMARY KEY

CREATE TABLE products
(
	id 		CHAR(10) 		NOT NULL, -- 不能为NULL值
    name 	CHAR(25) 		NOT NULL, -- 不能为NULL值
    PRIMARY KEY(id)	 -- 设置id列为主键,其中id列不能是NULL值
 )

设置列的默认值：DEFAULT

CREATE TABLE products
(
	id 		CHAR(10) 		NOT NULL, -- 不能为NULL值
    name 	CHAR(25) 		NOT NULL DEFAULT 'WUDI' -- 设置默认值为'WUDI'
 )

5. 更新列（不是更新列的数据，而是增加删除列） ALTER TABLE

**给表增加列 **ADD

ALTER TABLE user
ADD email CHAR(20) NULL DEFAULT '123'; -- 给表user增加一列email，数据类型是char(20),允许NULL,默认值是'123'

给表删除列 DROP COLUMN

ALTER TABLE user
DROP COLUMN email; -- 删除email列

6. 删除和重命名整张表哦

删除整张表 DROP TABLE

DROP TABLE user; -- 删除表user

重命名整张表 RENAME

RENAME TABLE user TO user1; -- 将表user重命名为user1

7. 视图

视图是虚拟的表，只包含动态检索数据的查询结果。

可以简单理解为视图就是select检索语句保存下来作为一个视图VIEW，后续语句使用该VIEW时即为对SELECT语句检索结果进行操作。从而实现了SQL重用，减少了复杂性。

比如我们要对select username from user;检索结果进行多次操作，那么就需要每次先调用该select语句。这里就可以使用view，将该select语句保存为一个视图，之后直接调用该视图即可。

注意视图不是把检索语句结果保存起来，而是把检索语句本身保存起来。因此后续每次引用该视图，就会立即调用该视图引用的检索语句一次。

• 视图名必须唯一（不能与其他表名或视图名重复）
• 视图可以嵌套，但是严重影响性能
• 视图不能索引、也不能有关联的触发器或默认值

7.1 创建视图 CREATE VIEW

CREATE VIEW v1 AS 
SELECT username FROM user
WHERE username > 'aaa';   -- 以下面的select语句创建一个视图并命名为v1

引用该视图

SELECT * FROM v1;

7.2 删除视图 DROP VIEW

DROP VIEW v1; -- 删除视图v1

8. 存储过程

存储过程就是为以后使用而保存的一条或多条SQL语句，可以将其视为批文件，但是其作用不仅限于批处理

可以将存储过程类比的看成一个函数。使用时可以给存储过程传递参数，也可以获取存储过程的返回值。

存储过程优点：简单安全高性能

8.1 执行存储过程 CALL

CALL接受存储过程名和需要传递给它的任何参数

CALL AddNewUser('wudi','1111'); -- 调用存储过程AddNewUser，并传递参数'wudi','1111'

假设AddNewUser是一个将新用户添加进表的存储过程，参数分别指定了行的username列和password列。但是对于主键id并没有指定参数，这是因为由于主键是唯一的，因此如果通过外部参数传递设置主键值不安全（如果设置了一个重复的id主键就会报错），而是应该在存储过程内部自动设置唯一的主键值！

8.2 创建存储过程 CREATE PROCEDURE

语法：

CREATE PROCEDURE 过程名([[IN|OUT|INOUT] 参数名 数据类型[,[IN|OUT|INOUT] 参数名 数据类型…]]) [特性 …] 过程体

CREATE PROCEDURE myproc(OUT s int)
	BEGIN
		SELECT COUNT(*) INTO s FROM students; -- 存储过程体，可以理解为函数体
	END

存储过程根据需要可能会有输入、输出、输入输出参数，如果有多个参数用","分割开。MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT:

IN参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值

OUT:该值可在存储过程内部被改变，并可返回

INOUT:调用时指定，并且可被改变和返回

过程体的开始与结束使用BEGIN与END进行标识。

例子1：用IN参数

DELIMITER //
  CREATE PROCEDURE in_param(IN p_in int)
    BEGIN
    SELECT p_in; -- 检索p_in参数值
    SET p_in=2; -- 将输入参数p_in值设为2，并不影响外部（类比于C++值传递）
    END;
    //
DELIMITER ;
#调用
SET @p_in=1;
CALL in_param(@p_in);

例子2：使用OUT参数

DELIMITER //
  CREATE PROCEDURE out_param(OUT p_out int)
    BEGIN
      SELECT p_out; -- 由于这是OUT参数，因此该检索返回NULL
      SET p_out=2; -- 将参数p_out值设为2，会影响到外部（类比于C++引用传递）
    END;
    //
DELIMITER ;
#调用
SET @p_out=1;
CALL out_param(@p_out);

例子3：使用INOUT参数

DELIMITER // -- 设置语句结束分隔符为'//'
  CREATE PROCEDURE inout_param(INOUT p_inout int)
    BEGIN
      SELECT p_out; -- 由于这是INOUT参数，因此该检索返回p_inout的值
      SET p_inout=2; -- 将输入参数p_inout值设为2，会影响到外部（类比于C++引用传递）
    END;
    // -- 该语句结束（注意不再是分号;）
DELIMITER ; -- 恢复语句结束分隔符为';'
#调用
SET @p_inout=1;
CALL inout_param(@p_inout) ;

DELIMITER：

其实就是告诉MySQL解释器，该段命令是否已经结束了，mysql是否可以执行了。默认情况下，delimiter是分号;。在命令行客户端中，如果有一行命令以分号结束，那么回车后，mysql将会执行该命令。

但有时候，不希望MySQL这么做。比如可能输入较多的语句，且语句中包含有分号。 就比如一个存储过程，中间过程体中有分号;但是不希望立即执行，这种情况下，就需要事先把delimiter换成其它符号，如//或$$。 这样只有当//或$$出现之后，mysql解释器才会执行这段语句 。就比如上面的例子，将整个create proceture语句以//结束，作为一个完整的语句执行（而不是在过程体中由分号结束）

注意使用改变DELIMITER后，要将DELIMITER恢复为;