创建数据库
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
);
