一.SQL的基本书写规则

1.SQL语句要以分号;结尾

2.数据库名、表名、列名，只能使用半角英文字母、数字、下划线，且必须以半角英文字母开头

二.SQL的基本语法

1.创建数据库

CREATE DATABASE < 数据库名称 > ;

2.在数据库中创建表

CREATE TABLE < 表名 >
( < 列名 1> < 数据类型 > < 该列所需约束 > ,
  < 列名 2> < 数据类型 > < 该列所需约束 > ,
  < 列名 3> < 数据类型 > < 该列所需约束 > ,
  < 列名 4> < 数据类型 > < 该列所需约束 > ,
  .
  .
  .
  < 该表的约束 1> , < 该表的约束 2> ,……);

（1）数据类型：

数据库创建的表，所有的列都必须指定数据类型，每一列都不能存储与该列数据类型不符的数据。

INTEGER型：整数型。不能存储小数

CHAR型：定长字符串。当列中存储的字符串长度小于最大长度时，使用半角空格补足，会造成存储空间的浪费

VARCHAR型：可变长字符串。与CHAR型对比，即时存储的字符数未达到最大长度时，也不会用半角空格补足

DATE型：日期型。指定存储日期（年月日）

（2）列的约束设置

NOT NULL：非空约束。该列必须输入数据

PRIMARY KEY：主键约束。该列值唯一，能据此检索出特定的行

例：创建数据库shop，并在数据库shop中创建表product

#创建名为shop的数据库
CREATE DATABASE shop;
#打开shop数据库。如缺少此步，在后面创建表时会提示"没选择新建的表所在的数据库"
use shop;
#创建名为product的表
CREATE TABLE product
(product_id CHAR(4) NOT NULL,	#product_id列数据是CHAR型，要求该列不空
product_name VARCHAR(100) NOT NULL,
product_type VARCHAR(32) NOT NULL,
sale_price INTEGER,
purchase_price INTEGER,
regist_date DATE,
PRIMARY KEY (product_id));	#主键设置为product_id

创建后效果如下：

 3.表的更新

（1）在表中添加一列

ALTER TABLE < 表名 > ADD COLUMN < 列的定义 >;

例：在表product中添加一列可以存储100位可变长字符串、非空的、名为product_birthplace的列

ALTER TABLE product ADD COLUMN product_birthplace VARCHAR(100) NOT NULL;

（2）在表中删除一列

ALTER TABLE < 表名 > DROP COLUMN < 列名 >;

例：在表product中删除名为product_birthplace的列

ALTER TABLE product DROP COLUMN product_birthplace;

注：ALTER TABLE语句执行之后无法恢复

（3）在表中删除指定的行

一定要注意添加WHERE条件指定要删除的行，否则会删除所有的数据

DELETE FROM product WHERE COLUMN_NAME='XXX';

例：在表product中删除product_name为'milk'的行

注：当product_name列中有很多'milk'行时，会把他们都删除(指定行不一定只有一行)

DELETE FROM product WHERE product_name = 'milk';

说明：删除的时候发生了点小插曲，报错显示“You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.To disable safe mode, toggle the option in Preferences."

原因是此时MySql在safe-updates模式下，可以通过SQL语句来进行状态查询，显示的是ON

此时我们用SQL语句把模式关闭就可以执行删除操作了，可以看到关闭后状态就是OFF了

使用的SQL语句如下：

#状态查询
show variables like 'SQL_SAFE_UPDATES';
#关闭safe-updates模式
SET SQL_SAFE_UPDATES = 0;
#如果想再把safe-updates模式打开的话
#SET SQL_SAFE_UPDATES = 1;

 （4）表中数据更新

使用UPDATE时要添加WHERE条件指定要更新的行，否则会把所有的行都按照语句进行更新修改

UPDATE <表名>
   SET <列名> = <表达式> [, <列名2>=<表达式2>...];  
 WHERE <条件>;  -- 可选，非常重要。
 ORDER BY 子句;  --可选
 LIMIT 子句; --可选

使用UPDATE也可以将列更新为NULL，该更新也称为NULL清空。只需将赋值表达式右边的值直接写为NULL即可，相当于将NULL作为一个值来使用。但只有未设置NOT NULL约束和主键约束的列才可以清空为NULL，否则会报错。

#修改所有注册时间(不写WHERE条件，则所有行都更新)
UPDATE product
	SET regist_date = '2021-12-10';
#修改部分商品单价(使用WHERE条件)
UPDATE product
	SET sale_price = sale_price * 10
    WHERE product_type = '学习用品';
#NULL清空
UPDATE product
	SET sale_price = NULL
    WHERE product_name = 'milk';

多列更新：UPDATE语句中的SET子句支持同时将多个列作为更新对象。格式上，只写一个SET关键字，更新语句间用逗号分隔，最后一个更新语句后不用逗号

#基础写法，一条UPDATE语句只更新一列
UPDATE product
	SET sale_price = sale_price + 2
    WHERE product_name = 'bread';
UPDATE product
	SET purchase_price = NULL
    WHERE product_name = 'bread';
UPDATE product
	SET regist_date = '2021-12-01'
    WHERE product_name = 'bread';

#合并写法，SET子句支持多列更新
UPDATE product
	#只写一个SET，更新语句间用逗号分隔，最后一个更新语句后不用逗号
	SET sale_price = sale_price + 2,
		purchase_price = NULL,
		regist_date = '2021-12-01'	#此处没有逗号
    WHERE product_name = 'pen';

个人感觉，能将基础写法改为合并写法也是有前提的，那就是基础写法中的几条更新的WHERE条件都相同。

4.表的清空

TRUNCATE TABLE TABLE_NAME;

例：清空product表

TRUNCATE TABLE product;

5.表的删除

删除的表无法恢复

DROP TABLE < 表名 > ;

例：删除product表

DROP TABLE product;

 注：对比DROP、DALETE、TRUNCATE，TRUNCATE（表的清空）用来清除数据时，速度最快

6.向表中插入数据

首先创建名为productins的表。在创建表时，通过设置DEFAULT约束来向表中插入默认值(初始值)

CREATE TABLE productins
(product_id    CHAR(4)      NOT NULL,
product_name   VARCHAR(100) NOT NULL,
product_type   VARCHAR(32)  NOT NULL,
#用DEFAULT语句将销售单价的默认初始值设定为0
sale_price     INTEGER      DEFAULT 0,
purchase_price INTEGER ,
regist_date    DATE ,
PRIMARY KEY (product_id)); 

这里测试一下初始值的设置，只将非空约束的项赋值，其他项不赋值

INSERT INTO productins (product_id, product_name, product_type) VALUES ('0005', '高压锅', '厨房用具');

从结果可以看到，sale_price项为0，另外两个没赋值的项为空。初值设置有效

 

INSERT基本语法

INSERT INTO <表名> (列1, 列2, 列3, ……) VALUES (值1, 值2, 值3, ……);  

对表进行全列 INSERT 时，可以省略表名后的列清单。这时 VALUES子句的值会默认按照从左到右的顺序赋给每一列。

INSERT INTO <表名> VALUES (值1, 值2, 值3, ……);  

INSERT支持一次插入多行数据。多个插入间用逗号隔开，最后一个插入后写分号。

INSERT 语句中想给某一列赋予 NULL 值时，可以直接在 VALUES子句的值清单中写入 NULL。想要插入 NULL 的列一定不能设置 NOT NULL 约束。

例子：

#包含列清单
INSERT INTO productins (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES ('1', '高压锅', '厨房用具', 6800, 5000, '2021-12-13');
#全列插入时省略列清单
INSERT INTO productins VALUES ('2', '电饭锅', '厨房用具', 6000, 5000, '2021-12-12');
#一次插入多行数据
INSERT INTO productins VALUES ('3', '运动T恤', '衣服', 500, 320, '2021-12-11'),
							  ('4', '菜刀', '厨房用具', 3000, 2800, '2021-12-10'),
                              ('5', '打孔器', '办公用品', 200, 180, '2021-12-09');
#给某列赋值NULL
INSERT INTO productins VALUES ('6', '叉子', '厨房用具', 500, NULL, '2021-12-06');

 可以使用INSERT … SELECT 语句从其他表复制数据。

例如：将productins表中的数据赋值到productcopy表中，注意要先创建一个表productcopy，而且复制表productcopy的表项要多于或等于原表要复制过来的表项。

具体来说，如果复制表有的项，原表要复制过来的部分没有该项，是可以的。但如果复制表没有的项，原表要复制过来的部分里有该项，就会报错unknown column '该项' in 'field list'

#创建复制表
CREATE TABLE productcopy
(product_id CHAR(4) NOT NULL,
product_name VARCHAR(100) NOT NULL,
sale_price INTEGER DEFAULT 0,
purchase_price INTEGER,
regist_date DATE,
PRIMARY KEY (product_id));
#复制。将原表的部分表项复制过来
INSERT INTO productcopy (product_id, product_name, purchase_price)
SELECT product_id, product_name, purchase_price
FROM productins;

从复制后的productcopy表中可以看到，原表选中的项product_id,product_name,purchase_price都成功复制。 未选择复制的项，sale_price为设置的默认初始值0，regist_date为空

7.表的索引

索引的建立可以大大提高MySQL的检索速度

创建索引：

（1）在创建表的时候就创建索引

#创建表user_1
CREATE TABLE user_1(
user_id INT NOT NULL,
user_name VARCHAR(16) NOT NULL,
#创建索引
INDEX (user_name)
);
#查看索引
show index from user_1;

（2）CREATE INDEX可对表增加普通索引或UNIQUE索引

列名规定需要索引的列

CREATE INDEX 索引名 ON 表名 (列名);

举例：

#创建表user_2
CREATE TABLE user_2(
user_id INT NOT NULL,
user_name VARCHAR(16) NOT NULL
);
#创建索引
CREATE INDEX myIndex ON user_2 (user_name);

（3）ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引

ALTER table 表名 ADD INDEX 索引名 (列名);

举例：

#创建表user_3
CREATE TABLE user_3(
user_id INT NOT NULL,
user_name VARCHAR(16) NOT NULL
);
#创建索引
ALTER table user_3 ADD INDEX myINdex (user_name);

删除索引：用方法一创建的索引，也可以用方法二删除，这个没有必须的对应关系

#方法一
#创建索引
CREATE INDEX myIndex ON user_2 (user_name);
#删除索引
DROP INDEX myIndex ON user_2;

#方法二
#创建索引
ALTER table user_3 ADD INDEX myINdex (user_name);
#删除索引
ALTER TABLE user_3 DROP INDEX myINdex;

三.练习

1.编写一条 CREATE TABLE 语句，用来创建一个包含表中所列各项的表 Addressbook （地址簿），并为 regist_no （注册编号）列设置主键约束 。表如下

CREATE TABLE Addressbook
(regist_no INT NOT NULL,
name VARCHAR(128) NOT NULL,
address VARCHAR(256) NOT NULL,
tel_no CHAR(10),
mail_address CHAR(20),
PRIMARY KEY (regist_no)
);

2.假设在创建练习1中的 Addressbook 表时忘记添加如下一列 postal_code （邮政编码）了，请编写 SQL 把此列添加到 Addressbook 表中。

列名 ： postal_code

数据类型 ：定长字符串类型（长度为 8）

约束 ：不能为 NULL

ALTER TABLE Addressbook ADD COLUMN postal_code CHAR(8) NOT NULL;

 3.请补充如下 SQL 语句来删除 Addressbook 表

(    ) table Addressbook;

DROP table Addressbook;

4.是否可以编写 SQL 语句来恢复删除掉的 Addressbook 表？

不可以，删除的表无法恢复，只能重新插入，所以执行删除操作时需要特别谨慎。