MySQL

创建数据库

CREATE DATABASE book;

使用数据库

USE book;

直接删除数据库

DROP DATABASE book;

建表

CREATE TABLE library (
  id INT,
  address CHAR
);

删除整表

DELETE FROM library;

显示表

SHOW TABLES;

表的详细描述

DESCRIBE book.library;

显示当前mysql版本、当前日期、用户、数据库\

SELECT version(),current_date,user(),database();

往表中插入数据

INSERT INTO library (id,address) VALUES (6,'qihe');

删除表中的数据

DELETE FROM library WHERE id = 5;

选择数据

SELECT id FROM library WHERE id = 6;
SELECT * FROM library WHERE id = 6;

更新数据

UPDATE library SET address = 'beijing' WHERE id = 6;

删除指定条件的信息

DELETE FROM library WHERE id = 6;

创建数据库用户

CREATE USER aws10086 IDENTIFIED BY '123456';

用户的权限控制

Grant all ON book.library TO aws10086 IDENTIFIED by '123456';

修改表

// 向表中添加一个字段
ALTER TABLE library ADD COLUMN (name CHAR(16));
// 指定字段插入的位置
ALTER TABLE library ADD COLUMN level INT AFTER id;
// 修改字段名称/类型
ALTER TABLE library CHANGE name lib_name CHAR(30);
// 修改表的名字
ALTER TABLE library RENAME TO librarys;
// 一次清空表的所有数据
TRUNCATE TABLE librarys;

查看数据库当前的引擎

SHOW CREATE TABLE librarys;

建user表

create table users (
  id int primary key auto_increment,
  nikename varchar(20) not null unique,
  password varchar(100) not null,
  address varchar(200),
  reg_date timestamp not null default CURRENT_TIMESTAMP
);

创建articles表，并设置外键

create table articles (
  id int primary key auto_increment,
  content longtext not null,
  userid int,constraint foreign key (userid) references users(id) on delete set null
);