Node.js<十四>——创建表细节和数据库查询

Running53
285 阅读6分钟

表约束

主键：PRIMARY KEY

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

  • 主键是表中唯一的索引，比如学生的学号
  • 并且必须是NOT NULL的，如果没有设置NOT NULL，那么MySQL也会隐式的设置为NOT NULL
  • 逐渐也可以是多列索引，PRIMARY KEY(key_part，...)，我们一般称之为联合主键

建议：开发中主键字段应该是和业务无关的，尽量不要使用业务字段来作为主键，因为他们可能会重复

唯一：UNIQUE

UNIQUE和主键不是一个东西，表中的每个字段都可以被设置为UNIQUE，这样每个字段都不会重复，如果有重复就会报错

  • 某些字段在开发中我们希望是唯一的，不会重复的，比如手机号码、身份证号码等，这个字段我们就可以使用UNIQUE来约束
  • 使用UNIQUE约束的字段在表中必须是不同的
  • 对于所有引擎，UNIQUE索引允许表的列中具有多个重复的NULL值（前提是该字段没有设置为 NOT NULL）

不能为空：NOT NULL

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

默认值：DEFAULT

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

自动递增：AUTO_INCREMENT

某些字段我们希望没有设置值时可以自动进行递增，比如用户的id，这个时候可以使用AUTO_INCREMENT来完成；其用在数字类型多一些

创建一个完整的表

  • 将id当做主键，并让其自增
  • 让名字不能为空
  • 让age的默认值为0
  • 让电话默认值为空字符串
  • 生日设置为日期
CREATE TABLE IF NOT EXISTS `users` (
	`id` INT PRIMARY KEY AUTO_INCREMENT,
	`name` VARCHAR(20) NOT NULL,
	`age` INT DEFAULT 0,
	`phone` VARCHAR(20) UNIQUE DEFAULT '',
	`birthday` TIMESTAMP
);

修改表

  1. 修改表的名字
ALTER TABLE `users` RENAME `user`;
  1. 添加一个新的列
ALTER TABLE `user` ADD `updateTime` TIMESTAMP;
  1. 修改字段的名称，但必须要指定数据类型
ALTER TABLE `user` CHANGE `phone` `telPhone` VARCHAR(20);
  1. 修改字段的类型
ALTER TABLE `user` MODIFY `telPhone` VARCHAR(30);
  1. 删除某一个字段
ALTER TABLE `user` DROP `age`;
  1. 想让createTime字段可以在创建时自动设置为当前时间
ALTER TABLE `user` MODIFY `createTime` TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
  1. 想让updateTime字段可以在修改时自动设置为当前时间
ALTER TABLE `user` MODIFY `updateTime` TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
                                                 ON UPDATE CURRENT_TIMESTAMP;

复制表

  1. 根据一个表结构去创建另外一张表；注意：这个是不会将内容复制到新的表中的，只会将主键和字段等复制过去
CREATE TABLE `user2` LIKE `user`;
  1. 根据另外一个表中的所有内容，创建一个新的表；其会复制字段和内容但并不会复制主键那些信息到新的表中
CREATE TABLE `user3` (SELECT * FROM `user`);

插入数据

  1. 不指定要插入的字段，默认按照字段的排列顺序插入
INSERT INTO `user` VALUES (100, 'zxc', '458', '2022-01-02', '2022-05-03')
  1. 指定插入的字段，如果没有写的则使用默认值或者null
INSERT INTO `user` (name, telPhone) VALUES ('wyz', '123')

删除数据

  1. 删除对应表中所有的数据
DELETE FROM `user`;
  1. 删除对应表中符合条件的数据
DELETE FROM `user` WHERE id = 1;

更新数据

  1. 更新对应表中指定字段下所有的数据
UPDATE `user` SET `name` = 'lisa';
  1. 更新对应表中指定字段下符合条件的数据
UPDATE `user` SET `name` = 'haha' WHERE id = 2;

DQL语句

DQL：Data Query Languaga（数据查询语句）

  • SELECT用于从一个或者多个表中检索选中的行（Record）

查询的格式如下：

基本查询

  1. 查询表中所有的字段及对应的信息
SELECT * from user;
  1. 查询指定的字段对应的信息
SELECT `name`,`birthday` from user;
  1. 对查询到的字段结果起一个别名
SELECT `name` as username,`birthday` as userBirthday from user;

where查询条件（一）

在开发中，我们希望根据条件来筛选我们的数据，这个时候我们要使用WHERE来进行条件查询

  1. 查询id小于140的同学名字和生日
SELECT name, birthday from `user` WHERE id < 140;
  1. 查询id等于140的同学名字和生日
SELECT name, birthday from `user` WHERE id = 140;
  1. 查询id不等于140的同学名字和生日
SELECT name, birthday from `user` WHERE id != 140;
SELECT name, birthday from `user` WHERE id <> 140;

where查询条件（二）

  1. 查询id大于140但小于150的同学名字和生日
SELECT name,birthday from `user` WHERE id > 140 AND id < 150;
SELECT name,birthday from `user` WHERE id > 140 && id < 150;
# 注意：使用BETWEEN关键字查询到的信息是包括边界条件的，比如这里的id=140和150的情况
SELECT name,birthday from `user` WHERE id BETWEEN 140 AND 150;
  1. 查询id小于135或者大于145的同学
SELECT * from `user` WHERE id <135 || id > 145;
SELECT * from `user` WHERE id <135 OR id > 145;
  1. 查询某一个字段值为null的数据
 SELECT * from `user` WHERE name IS NULL;
  1. 查询某一个字段值不为null的数据
 SELECT * from `user` WHERE name IS NOT NULL;

where查询条件（三）

模糊查询使用LIKE关键字，结合两个特殊符号

  • %表示匹配任意个（包括0个）的任意字符
  • _表示匹配一个的任意字符
  1. 查询所有以v开头的name
SELECT * from `user` WHERE name LIKE 'v%';
  1. 查所有找带M的name，
SELECT * from `user` WHERE name LIKE '%M%';
  1. 查找所有带M的name但必须是第3个字符
# M前面有两个_
SELECT * from `user` WHERE name LIKE '__M%';

where查询条件（四）

IN表示取多个值中的其中一个即可

* 用||表示或
SELECT * FROM `user` WHERE id = 135 || id = 136 || id = 137;
* 用OR表示或
SELECT * FROM `user` WHERE id = 135 OR id = 136 OR id = 137;
* 用IN取值
SELECT * FROM `user` WHERE id IN(135,136,137);

对结果进行排序

  1. 将名字为2的同学按照id升序排列
SELECT * FROM `user` WHERE name = '2' ORDER BY id ASC;
  1. 将名字为2的同学按照id降序排列
SELECT * FROM `user` WHERE name = '2' ORDER BY id DESC;
  1. 将名字为2的同学按照id升序排列，同时按照成绩score降序排列
SELECT * FROM `user` WHERE name = '2' ORDER BY id ASC, score DESC;

分页查询

LIMIT对应这次查询多少条数据，OFFSET表示从第几条数据开始（不包括临界值）

  1. 从第1条开始查询20条数据
SELECT * FROM `user` LIMIT 20 OFFSET 0;
  1. 从第21条开始查询20条数据
SELECT * FROM `user` LIMIT 20 OFFSET 20;

还可以变一种写法，比如LIMIT offset, limit;这样书写的话我们要先写offset表示从哪里开始查，然后再写limit表示需要查询多少条

  1. 从第1条开始查询20条数据
SELECT * FROM `user` LIMIT 0,20;
  1. 从第21条开始查询20条数据
SELECT * FROM `user` LIMIT 20,20;
avatar
文章
阅读
粉丝