数据库操作-建库建表

一： 库（database（mysql））的操作

1. 建库

CREATE SCHEMA `node-blog`;
// or 
CREATE DATABASE `node-blog`;

2. 查看库

SHOW DATABASES;

3. 选择库

-- 选择表
USE `node-blog`;

二： 表的操作

1. 建表

CREATE TABLE IF NOT EXISTS `blogs`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `title` VARCHAR(100) NOT NULL,
   `content` VARCHAR(200) NOT NULL,
   `author` VARCHAR(100),
     `create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP comment '创建时间',
     `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP comment '更新时间',
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `users`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `username` VARCHAR(100) NOT NULL,
   `password` VARCHAR(100) NOT NULL,
   `phone` INT,
     `create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP comment '创建时间',
     `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP comment '更新时间',
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

3. 单表操作

3.1 增

INSERT INTO users(username, `password`, phone) VALUES ('lisi2', '12', '12345678888');

3.2 删

DELETE FROM users WHERE username="zhangsan";

3.3 改

UPDATE users SET password='newpassword' WHERE username='你好';

3.4 查

-- 查询全部
SELECT * FROM users;
-- 展示固定的列
SELECT id, username FROM users;
-- 交集查询
SELECT id, username FROM users WHERE username='zhangsan' and `password`='123';
-- 并集查询
SELECT id, username FROM users WHERE username='zhangsan' or `password`='123';
-- 模糊查询
SELECT * FROM users WHERE username LIKE '%zhan%';
-- 查询结果排序
SELECT * FROM users WHERE username LIKE '%zhan%' ORDER BY id;
-- 倒序查询
SELECT * FROM users WHERE username LIKE '%zhan%' ORDER BY id DESC;