DataWhale组队学习之SQL编程-9月-task1

Task1 初识数据库

一、 基础SQL语句

1.1 创建数据库

语法：

CREATE DATABASE < 数据库名称 > ;

1.2 创建数据表

语法：

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

1.2.1 命名规则

• 只能使用半角英文字母、数字、下划线（_）作为数据库、表和列的名称
• 名称必须以半角英文字母开头
• 名称不能重复

1.2.2 数据类型的指定

数据类型	描述
INTEGER 型	用来指定存储整数的列的数据类型（数字型），不能存储小数。
CHAR 型	用来存储定长字符串，当列中存储的字符串长度达不到最大长度的时候，使用半角空格进行补足，由于会浪费存储空间，所以一般不使用。
VARCHAR 型	用来存储可变长度字符串，定长字符串在字符数未达到最大长度时会用半角空格补足，但可变长字符串不同，即使字符数未达到最大长度，也不会用半角空格补足。
DATE 型	用来指定存储日期（年月日）的列的数据类型（日期型）。

1.2.3 约束设置

约束设置类别	描述
NOT NULL	非空约束，即该列必须输入数据。
PRIMARY KEY	主键约束，代表该列是唯一值，可以通过该列取出特定的行的数据。

1.3 删除数据表

删除表的语法：

DROP TABLE < 表名 > ;

需要特别注意的是，删除的表是无法恢复的，只能重新插入，请执行删除操作时要特别谨慎。

1.4 数据表新增列

添加列的 ALTER TABLE 语句：

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

1.5 数据表删除行列

• 删除列的 ALTER TABLE 语句：

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

• 删除特定行的ALTER TABLE 语句：

-- 一定注意添加 WHERE 条件，否则将会删除所有的数据
DELETE FROM product WHERE COLUMN_NAME='XXX';

ALTER TABLE 语句和 DROP TABLE 语句一样，执行之后无法恢复。误添加的列可以通过 ALTER TABLE 语句删除，或者将表全部删除之后重新再创建。

1.6 清空表内容

清空表内容的语句：

TRUNCATE TABLE TABLE_NAME;

优点：相比drop / delete，truncate用来清除数据时，速度最快。

1.7 数据更新

1.7.1 单行更新

基本语法：

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

使用 update 时要注意添加 where 条件，否则将会将所有的行按照语句修改。

1.7.2 多行更新

多列更新

UPDATE 语句的 SET 子句支持同时将多个列作为更新对象。

-- 基础写法，一条UPDATE语句只更新一列
UPDATE product
   SET sale_price = sale_price * 10
 WHERE product_type = '厨房用具';
UPDATE product
   SET purchase_price = purchase_price / 2
 WHERE product_type = '厨房用具';  

该写法可以得到正确结果，但是代码较为繁琐。可以采用合并的方法来简化代码。

-- 合并后的写法
UPDATE product
   SET sale_price = sale_price * 10,
       purchase_price = purchase_price / 2
 WHERE product_type = '厨房用具';  

需要明确的是，SET 子句中的列不仅可以是两列，还可以是三列或者更多。

1.8 向数据表插入数据

基本语法：

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

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

二、索引

2.1 索引的作用

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

打个比方，如果合理的设计且使用索引的 MySQL 是一辆兰博基尼的话，那么没有设计和使用索引的 MySQL 就是一个人力三轮车。

拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

索引创建了一种有序的数据结构，采用二分法搜索数据时，其复杂度为 log2(N)，1000多万的数据只要搜索23次，其效率是非常高效的。

2.2 创建索引

创建表时可以直接创建索引，语法如下：

CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
INDEX [indexName] (username(length))  
 
);  

也可以使用如下语句创建：

-- 方法1
CREATE INDEX indexName ON table_name (column_name)

-- 方法2
ALTER table tableName ADD INDEX indexName(columnName)

2.3 索引分类

• 主键索引

建立在主键上的索引被称为主键索引，一张数据表只能有一个主键索引，索引列值不允许有空值，通常在创建表时一起创建。

• 唯一索引

建立在UNIQUE字段上的索引被称为唯一索引，一张表可以有多个唯一索引，索引列值允许为空，列值中出现多个空值不会发生重复冲突。

• 普通索引

建立在普通字段上的索引被称为普通索引。

• 前缀索引

前缀索引是指对字符类型字段的前几个字符或对二进制类型字段的前几个bytes建立的索引，而不是在整个字段上建索引。前缀索引可以建立在类型为char、varchar、binary、varbinary的列上，可以大大减少索引占用的存储空间，也能提升索引的查询效率。

• 全文索引

利用“分词技术”实现在长文本中搜索关键字的一种索引。

语法：

SELECT * FROM article WHERE MATCH (col1，col2，...) AGAINST (expr [ search _ modifier ])

1、MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；

2、MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;

3、只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。

4、如果可能，请尽量先创建表并插入所有数据后再创建全文索引，而不要在创建表时就直接创建全文索引，因为前者比后者的全文索引效率要高。

• 单列索引

建立在单个列上的索引被称为单列索引。

• 联合索引（复合索引、多列索引）

建立在多个列上的索引被称为联合索引，又叫复合索引、组合索引。

三、练习题

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

create table Addressbook (
    regist_no integer not null,
    name varchar(128) not null,
    address varchar(128) not null,
    tel_no char(10),
    mail_address char(10),
    primary key(regist_no)
);

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

alter table Addressbook add column postal_code char(8) not null;

3. SQL 语句来删除 Addressbook 表

drop table Addressbook;

4. 没有SQL语句可以恢复删除掉的 Addressbook 表，只能重新插入。