Task01 环境搭建，初识数据库

Task01 环境搭建，初识数据库

1.1 Linux（ubuntu）下MySQL的过程及问题

• 环境：ubuntu20.04

• 安装过程：ubuntu安装MySQL

  sudo apt update
  sudo apt install mysql-server
  

  • 安装成功之后MySQL会自动启动

    # 查看MYSQL的运行状态
    sudo systemctl status mysql
    

  • 登录root用户：

    mysql -uroot -p
    

  • 修改密码，如果过于简单会报错：

    ALERT USER 'root'@'localhost' IDENTIFIED BY '123456';
    

  

  • 原因是因为 MySQL 有密码设置的规范, 具体是与 validate_password_policy 的值有关：

  

  • MySQL 完整的初始密码规则可以通过如下命令查看：

    SHOW> VARIABLES LIKE 'validate_password%';
    

    

  • 密码的长度是由 validate_password_length 决定的, 而 validate_password_length 的计算公式是：

    validate_password_length = validate_password_number_count + validate_password_special_char_count + (2 * validate_password_mixed_case_count)
    

  • validate_password_policy代表密码策略, 默认是 1：符合长度, 且必须含有数字, 小写或大写字母, 特殊字符. 设置为 0 判断密码的标准就基于密码的长度了. 一定要先修改两个参数再修改密码:

    mysql> set global validate_password.policy=0;
    

  • validate_password_length代表密码长度, 最小值为 4

    mysql> set global validate_password.length=4;
    

  • 修改完,如图:

  

  • 此时密码就可以设置的很简单, 例如 1234 之类的. 到此数据库的密码设置就完成了。

• 连接MySQL工具：

  • Workbench
  • HeidiSQL
  • DBeaver
  • Navicat
  • SQLyog
  • DataGrip

1.2 SQL的基本规范

• SQL语句要以分号（;）结尾
• SQL不区分关键字的大小写，但是插入到表中的数据是区分大小写的
• win系统不区分表名及字段名的大小写
• linux/max严格区分表名及字段名的大小写
• 单词需要使用半角空格（不能使用全角空格）或者换行来分隔

1.3 SQL

• 数据库的创建：

  CREATE DATEBASE <数据库名>;
  -- eg
  CREATE DATABASE shop;
  show tables;  -- 查看shop有哪些表
  

• 表的创建

  CREATE TABLE < 表名 >
  ( < 列名 1> < 数据类型 > < 该列所需约束 > ,
    < 列名 2> < 数据类型 > < 该列所需约束 > ,
    < 列名 3> < 数据类型 > < 该列所需约束 > ,
    < 列名 4> < 数据类型 > < 该列所需约束 > ,
    .
    .
    .
    < 该表的约束 1> , < 该表的约束 2> ,……);
    
  -- eg
  CREATE TABLE product
  (product_id CHAR(4) NOT NULL,
   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));
   
   -- 查看是否创建成功
   show columns from product;
  

• 命名规则：

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

• 数据类型的指定

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

• 约束设置：

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

• 表的删除和更新：

  -- 删除
  DROP TABLE <表名>;
  
  -- 删除product表
  DROP TABLE product;
  
  -- 添加列
  ALERT TABLE <表名> ADD COLUMN <列的定义>;
  
  -- 添加一列可以存储100位的可变长字符串product_name_pinyin列
  ALERT TABLE product ADD COLUMN product_name_pinyin VARCHAR(100);
  
  -- 删除列的ALERT TABLE语句
  ALERT TABLE <表名> DROP COLUMN <列名>;
  
  -- 删除product_name_pinyin列
  ALERT TABLE product DROP COLUMN product_name_pinyin;
  
  -- 删除表中特定的行
  ALERT TABLE product WHERE COLUMN_NAME='XXX';
  
  -- 清空表内容, 与drop/delete相比，truncate用来清除数据时更快
  TRUNCATE TABLE TABLE_NAME;
  

• 数据的更新：

  UPDATE <表名>
  	SET <列名> = <表达式> [, <列名2>=<表达式2>...]
  	WHERE <条件>  -- 可选，非常重要
  	ORDER BY 子句 -- 可选
  	LIMIT 子句;  -- 可选
  	
  -- 修改所有的注册时间
  UPDATE product
  	SET regist_date = '2009-10-10';
  	
  -- 修改部分商品的单价，使用update时注意添加where条件，否则会将所有的行按照语句修改
  UPDATE product
  	SET sale_price = sale_price * 10
  	WHERE product_type = '厨房用具'
  	
  -- NULL清空，将列更新为NULL
  UPDATE product 
  	SET regist_date = NULL
  	WHERE product_id = '0008';	
  

  • UPDATE只有在未设置NOT NULL约束和主键约束的列才可以清空NULL

  • 多列更新：

    UPDATE product
    	SET sale_price = sale_price * 10,
    		purchase_price = purchase_price / 2;
    	WHERE product_type = '厨房用具'；
    

• 向product表中插入数据

  CREATE TABLE productions
  (product_id    CHAR(4)      NOT NULL,
  product_name   VARCHAR(100) NOT NULL,
  product_type   VARCHAR(32)  NOT NULL,
  sale_price     INTEGER      DEFAULT 0,
  purchase_price INTEGER ,
  regist_date    DATE ,
  PRIMARY KEY (product_id)); 
  
  -- 基本语法
  INSERT INTO <表名> (列1， 列2， 列3，...) vALUES (值1， 值2， 值3)
  
  -- 包含列表清单
  INSERT INTO productions (product_id, product_name, product_type, sale_price, purchase_price, regist_date) 
  VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15')
  
  -- 对全列插入可以省略列表清单，默认从左往右赋值给每一列
  INSERT INTO productions VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15')
  
  -- INSERT语句给某一列赋予NULL值，直接在VAlUES清单中写入NULL
  INSERT INTO productins (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');  
  
  -- 向表中插入默认初试值
  CREATE TABLE productins
  (product_id CHAR(4) NOT NULL,
  （略）
  sale_price INTEGER
  （略）	DEFAULT 0, -- 销售单价的默认值设定为0;
  PRIMARY KEY (product_id));  
  
  -- 使用INSERT ... SELECT从其他表复制数据
  INSERT INTO productcopy (product_id, product_name, product_type, sale_price, purchase_price, regist_date)
  SELECT product_id, product_name, product_type, sale_price, purchase_price, regist_date
    FROM Product;  
  

• 索引：（索引可以大大提高MySQL的检索效率）

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

    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)
    

  • 索引分类：

    • 主键索引： 一张数据表只能有一个主键索引，索引列值不允许有空值，通常在创建表时一起创建。

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

    • 普通索引:

    • 前缀索引：前缀索引可以建立在类型为char、varchar、binary、varbinary的列上

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

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

      • 只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
      • 如果可能，请尽量先创建表并插入所有数据后再创建全文索引，而不要在创建表时就直接创建全文索引，因为前者比后者的全文索引效率要高。

    • 单列索引：

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

1.4 练习

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

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

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

  • 列名 ： postal_code

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

  • 约束 ：不能为 NULL

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

• 1.4.3 请补充如下 SQL 语句来删除 Addressbook 表。

  DROP table Addressbook;
  

• 1.4.4 是否可以编写 SQL 语句来恢复删除掉的 Addressbook 表？ (否)

​

​

​

​
​