mysql(十一)表、库的管理

#A、创建库
CREATE DATABASE IF NOT EXISTS books;
#B、更改库
ALTER DATABASE books CHARACTER SET utf8;
ALTER TABLE book CHARACTER SET utf8;
ALTER TABLE author CHARACTER SET utf8;
DESC book;
#C、删除库
drop database if EXISTS books;



#案例1：创建表book
DROP TABLE book;
CREATE TABLE book(
  id INT,
	bName VARCHAR(20),
	price DOUBLE,
	authorId INT,
	publishDate datetime
);
#创建表 author
CREATE TABLE author(
    id INT,
		au_name VARCHAR(20),
		nation VARCHAR(10)
);

#2、表的修改
/*
  ALTER TABLE 表名  + 关键词+ COLUMN 列名【列的类型,约束】;
*/
#(1)、修改列名
ALTER TABLE book CHANGE COLUMN publishDate pubDate DATETIME;
DESC book;
#(2)、修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pubDate TIMESTAMP;
SELECT *FROM book;
#(3)、添加列
ALTER TABLE author ADD COLUMN annual DOUBLE;
DESC author;#显示的是表的状态
#(4)、删除列
ALTER TABLE author DROP COLUMN annual;
#(5)、修改表名
ALTER TABLE author RENAME TO b_author;


#3、表的删除
DROP TABLE IF EXISTS b_author; #仅仅对表和库能用 IF exists
show TABLES;

#通用的写法
DROP DATABASE IF EXISTS 旧库名;
CREATE DATABASE 新库名;

DROP TABLE IF EXISTS 旧表名;
CREATE TABLE 新表名;


#4、表的复制
SELECT DATABASE();
INSERT INTO books.author(id,au_name,nation) VALUES
(2,'鲁迅','中国'),
(3,'唐家三少','中国'),
(4,'天蚕土豆','中国');

#4.1复制表的结构
CREATE TABLE copy LIKE author;
SELECT *FROM author;
SELECT *FROM copy2;
DESC copy;
#4.2复制整张表(数据+结构)
CREATE TABLE copy2 
SELECT *FROM author;#跟了一个子查询

DROP TABLE cpoy2;

#4.3仅仅复制 某些字段
CREATE TABLE copy3 
SELECT id,au_name FROM author
WHERE 0;
DESC copy3;