一.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 表?
不可以,删除的表无法恢复,只能重新插入,所以执行删除操作时需要特别谨慎。