终端操作缺陷

参考终端进行数据库操作文章

语句无高亮，并且无任何提示
复杂的语句分成多行，格式不美观，且输入容易出错
终端查看所有的数据库和数据时不方便

GUI工具

为了解决上述问题，可以采用以下几款：

Navicat: 推荐使用，不过需要付费，但是又免费的试用时间，应付学习足够了。
SQLYog: 一款免费的SQL工具
TablePlus:常用功能都可以使用，但是会有一些限制（例如：只能开两个标签页）

mysql 语句

sql(structured Query Language) —结构化查询语言

sql语句的常用规范

通常关键字是大写的，比如CREATE,TABLE,SHOW等等。
一条语句结束后，需要以“；”结尾。
如果遇到关键字作为表明或者字段名称，可以用包裹。(类似es6中的模板字符串)

sql语句分类

DDL (Data Definition language) —数据定义语言

可以通过DDL语句对数据库或者表进行：创建，删除，修改操作.

DML(Data Manipulation Language): 数据操作语言

可以通过DML语句对表进行：添加、删除、修改等操作.

DQL(Data Query Language): 数据查询语言

可以通过DQL语句对表进行查询操作.

DCL(Data Control Language): 数据权限语言

可以通过DCL语句对表权限等操作.

DDL数据库的操作—实践

# 查看所有的数据
SHOW DATABASES;

# 选择某一个数据
USE bili;

#查看当前正在使用的数据库
SELECT DATABASE();

# 创建一个新的数据库
-- CREATE DATABASE douyu;
-- CREATE DATABASE IF NOT EXISTS douyu;

CREATE DATABASE IF NOT EXISTS huya DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;


# 删除数据库
DROP DATABASE IF EXISTS douyu;

# 修改数据库的编码

ALTER DATABASE huya CHARACTER SET = utf8 COLLATE = utf8_unicode_ci;

DDL-数据表的操作—实践

# 查看数据表
SHOW TABLES;

# 查看某一个表结构
DESC users;

# 创建数据表
CREATE TABLE IF NOT EXISTS `test` (
  `name` VARCHAR(20),
    age INT,
    height DOUBLE
    );
    
# 删除表
DROP TABLE IF EXISTS `test`;


# 查看某一个表结构
DESC students;

DML-对数据库进行增删改—实践

# 插入数据
INSERT INTO `user` VALUES (110, 'why', '020-123456', '2020-10-20', '2020-11-11');


-- INSERT INTO `user` (`name`, `telPhone`,`createTime`,`updateTime`)
--                         VALUES ('paike','000-1111112','2020-10-10','2030-10-20');

# 需求： createTime和updateTime可以自动设置值
ALTER TABLE `user` MODIFY `createTime` TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE `user` MODIFY `updateTime` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP;

 INSERT INTO `user`    (`name`, `telPhone`)
                    VALUES ('lilei3','000-1111116');
                    
                    
# 删除数据
# 删除所有数据
DELETE FROM `user`;

# 删除符合条件的数据
DELETE FROM `user` WHERE id = 110;

# 更新数据
# 更新所有数据
UPDATE `user` SET `name` = 'lily',telPhone = '010-110110';

# 更新符合条件的数据
UPDATE `user` SET `name` = 'lily',telPhone = '010-110110' WHERE id = 115;

DQL-对数据库进行查询—实践

分页查询

当数据库中的数据非常多时，一次性查询到所有的将结果进行显示是不现实的；

在真实开发中，我们都会要求用户传入offset、limit、或者page等字段
它们的目的是让我们可以在数据库中进行分页查询
它的用法有[LIMIT{[offset, row_count | row_count OFFSET offset]}]

sql数据类型

开发可参考—菜鸟教程—数据类型及其范围

表约束

主键：PRIMARY KEY

一张表中，我们为了区分每一条记录的唯一性，必须有一个字段是永远不会重复，并且不为空的，这个字段我们通常会把它设置为主键：

主键是表中唯一的索引；
并且必须是NOT NULL的，如果没有设置NOT NULL,那么MySQL也会隐式的设置为NOT NULL;
主键也可以是多列索引，PRIMARY KEY(key_part,…),我们一般称之为联合主键
建议：开发中主键字段应该是和业务无关的，尽量不要使用业务字段来作为主键。

唯一：UNIQUE

某些字段再开发过程中，我们希望是唯一的，不回重复的，比如手机号，身份证号，这个字段我们可以使用UNIQUE来约束。
使用UNIQUE约束的字段在表中必须是不同的
对于所有引擎，UNIQUE索引允许NULL包含的多个值NULL.

不能为空 NOT NULL

某些字段我们要求用户必须插入值，不可以为空，这个时候我们可以使用NOT NULL来约束

默认值 DEFAULT

某些字段我们希望在没有设置值时给予一个默认值，这个时候我们可以使用DEFAULT来完成。

自动递增： AUTO_INCREMENT

某些字段我们希望不设置值时可以进行递增，比如用户的id,这个时候我们可以使用Auto_INCREMENT来完成。
一般用于数字类型的描述。

外键约束（用于多表关系）

CREATE TABLE IF NOT EXISTS `brand` (
 id INT PRIMARY KEY AUTO_INCREMENT,
 name VARCHAR(20) NOT NULL,
 website VARCHAR(100),
 phoneRank INT
);

INSERT INTO `brand` (name, website, phoneRank) VALUES('华为','www.huawei.com',2);
INSERT INTO `brand` (name, website, phoneRank) VALUES('苹果','www.apple.com',10);
INSERT INTO `brand` (name, website, phoneRank) VALUES('小米','www.mi.com',5);
INSERT INTO `brand` (name, website, phoneRank) VALUES('OPPO','www.oppo.com',12);
INSERT INTO `brand` (name, website, phoneRank) VALUES('京东','www.jd.com',8);
INSERT INTO `brand` (name, website, phoneRank) VALUES('GOOGLE','www.google.com',9);


# 如果在一张表中需要添加一个的字段，这个字段对应另外一张表的字段，并且想要对字段有所限制的时候，可以用外键  
ALTER table `product` add `brand_id` int;
ALTER TABLE `product` DROP  `brand_id`;
# 真实开发会在建表的时候就将xxx设置为外键

# 手动修改brand_id为外键
ALTER TABLE `product` ADD FOREIGN KEY(brand_id) REFERENCES brand(id);

# 设置brand_id的值
UPDATE `product` SET `brand_id` = 1 WHERE brand = '华为';
UPDATE `product` SET `brand_id` = 1 WHERE brand = '苹果'；
UPDATE `product` SET `brand_id` = 1 WHERE brand = '小米'；
UPDATE `product` SET `brand_id` = 1 WHERE brand = 'oppo'；

# 3. 修改和删除外键引用的id
UPDATE `brand` SET `id` = 100 WHERE `id` = 1;


# 4. 修改brand_id关联外键时的action
# 4.1 获取到目前的外键的名称
SHOW CREATE TABLE `product`;

-- CREATE TABLE `product` (
--   `id` int NOT NULL AUTO_INCREMENT,
--   `brand` varchar(20) DEFAULT NULL,
--   `title` varchar(100) NOT NULL,
--   `price` double NOT NULL,
--   `score` decimal(2,1) DEFAULT NULL,
--   `voteCnt` int DEFAULT NULL,
--   `url` varchar(100) DEFAULT NULL,
--   `pid` int DEFAULT NULL,
--   `brand_id` int DEFAULT NULL,
--   PRIMARY KEY (`id`),
--   KEY `brand_id` (`brand_id`),
--   CONSTRAINT `product_ibfk_1` FOREIGN KEY (`brand_id`) REFERENCES `brand` (`id`)
-- ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci


# 4.2 根据名称将外键删除掉
ALTER TABLE `product` DROP FOREIGN KEY product_ibfk_5;

# 4.3 重新添加外键约束
ALTER TABLE `product` ADD FOREIGN KEY (brand_id) REFERENCES brand(id)
             ON UPDATE CASCADE
                         ON DELETE RESTRICT;

完整的数据表—实践

# 创建完整数据表
CREATE TABLE IF NOT EXISTS `users`(
   `id` INT PRIMARY KEY AUTO_INCREMENT,
   `name` VARCHARACTER(20) NOT NULL,
   `age` INT DEFAULT 0,
   `height` DECIMAL(10,2),
     `phoneNum` VARCHAR(20) UNIQUE DEFAULT '',
     `createTime` TIMESTAMP
);

# 修改表
# 1.修改表的名字
ALTER TABLE `users` RENAME TO `user`;

# 2. 添加一个新的列
ALTER TABLE `user` ADD `updateTime` TIMESTAMP;

# 3. 修改字段名称
ALTER TABLE `user` CHANGE `phoneNum` `telPhone` VARCHAR(20);

# 4. 修改字段类型值
ALTER TABLE `user` MODIFY `name` VARCHARACTER(30);


# 5. 删除某个字段
ALTER TABLE `user` DROP `age`;