库和表的管理
CREATE DATABASE IF NOT EXISTS books;
RENAME DATABASE books TO 新库名;
ALTER DATABASE books CHARACTER SET gbk;
DROP DATABASE IF EXISTS books;
CREATE TABLE IF NOT EXISTS book(
id INT ,
bName VARCHAR ( 20 ) ,
price DOUBLE ,
authorId INT ,
publishDate DATETIME
) ;
CREATE TABLE author(
id INT ,
au_name VARCHAR ( 20 ) ,
nation VARCHAR ( 10 )
) ;
DESC author
ALTER TABLE book CHANGE COLUMN publishDate pubDate DATETIME ;
ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP
ALTER TABLE author ADD COLUMN annual DOUBLE ;
ALTER TABLE author DROP COLUMN annual;
ALTER TABLE author RENAME TO book_author
DESC book
DROP TABLE IF EXISTS book_author;
SHOW TABLES ;
DROP DATABASE IF EXISTS 旧库名;
CREATE DATABASE 新库名;
DROP TABLE IF EXISTS 旧表名;
CREATE TABLE 新表名( ) ;
SELECT * FROM author
INSERT INTO author VALUES
( 1 , '盖世凯1' , '中国' ) ,
( 2 , '盖世凯2' , '中国' ) ,
( 3 , '盖世凯3' , '中国' ) ,
( 4 , '盖世凯4' , '中国' ) ;
CREATE TABLE copy LIKE author;
CREATE TABLE copy2
SELECT * FROM author;
CREATE TABLE copy3
SELECT id, au_name
FROM author
WHERE nation= '中国' ;
CREATE TABLE copy4
SELECT id, au_name
FROM author
WHERE 0 ;
USE test;
CREATE TABLE dept1(
id INT ( 7 ) ,
NAME VARCHAR ( 25 )
) ;
CREATE TABLE dept2
SELECT department_id, department_name
FROM myemployees. ` departments`
CREATE TABLE emp5(
id INT ( 7 ) ,
first_name VARCHAR ( 25 ) ,
last_name VARCHAR ( 25 ) ,
dept_id INT ( 7 )
) ;
ALTER TABLE emp5 MODIFY COLUMN last_name VARCHAR ( 50 )
CREATE TABLE employees2 LIKE myemployees. ` employees` ;
DROP TABLE IF EXISTS emp5
ALTER TABLE employees2 RENAME TO emp5
ALTER TABLE emp5 ADD COLUMN test_column INT ;
ALTER TABLE emp5 DROP COLUMN test_column
常见的数据类型
CREATE TABLE tab_int(
t1 INT ( 7 ) ZEROFILL
t2 INT ( 7 )
)
CREATE TABLE tab_float(
f1 FLOAT ,
f2 DOUBLE ,
f3 DECIMAL
) ;
CREATE TABLE tab_char(
c1 ENUM ( 'a' , 'b' , 'c' )
)
INSERT INTO tab_char VALUES ( 'a' )
INSERT INTO tab_char VALUES ( 'A' )
CREATE TABLE tab_date(
t1 DATETIME ,
t2 TIMESTAMP
) ;
INSERT INTO tab_date VALUES ( NOW ( ) , NOW ( ) )
SELECT * FROM tab_date;
SHOW VARIABLES LIKE 'time_zone'
SET time_zone= '+9:00'
常见的六大约束
CREATE TABLE 表名(
字段名 字段类型 列级约束,
字段名 字段类型,
表级约束
)
CREATE DATABASE students
USE students
CREATE TABLE IF NOT EXISTS stuinfo(
id INT PRIMARY KEY ,
stuName VARCHAR ( 20 ) NOT NULL ,
gender CHAR ( 1 ) CHECK ( gender= '男' OR gender= '女' ) ,
seat INT UNIQUE ,
age INT DEFAULT 18 ,
majorId INT REFERENCES major( id)
) ;
CREATE TABLE IF NOT EXISTS stuinfo(
id INT ,
stuName VARCHAR ( 20 ) ,
gender CHAR ( 1 ) ,
seat INT ,
age INT ,
majorId INT ,
PRIMARY KEY ( id) ,
UNIQUE ( seat) ,
CHECK ( gender = '男' OR gender= '女' ) ,
FOREIGN KEY ( majorid) REFERENCES major( id)
) ;
CREATE TABLE IF NOT EXISTS stuinfo(
id INT PRIMARY KEY ,
stuName VARCHAR ( 20 ) NOT NULL ,
sex CHAR ( 1 ) ,
age INT DEFAULT 18 ,
seat INT UNIQUE ,
majorid INT ,
CONSTRAINT fk FOREIGN KEY ( majorid) REFERENCES major( id)
) ;
CREATE TABLE IF NOT EXISTS major(
id INT PRIMARY KEY ,
majorName VARCHAR ( 20 )
) ;
DESC stuinfo;
SHOW INDEX FROM stuinfo;
DROP TABLE stuinfo;
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
id INT ,
stuname VARCHAR ( 20 ) ,
gender CHAR ( 1 ) ,
seat INT ,
age INT ,
majorid INT
) ;
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR ( 20 ) NOT NULL
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18
ALTER TABLE stuinfo MODIFY COLUMN id PRIMARY KEY ;
ALTER TABLE stuinfo ADD PRIMARY KEY ( id) ;
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE ;
ALTER TABLE stuinfo ADD UNIQUE ( seat) ;
ALTER TABLE stuinfo ADD CONSTRAINT fk FOREIGN KEY ( majorid) REFERENCES major( id)
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR ( 20 ) NULL ;
ALTER TABLE stuinfo MODIFY COLUMN age INT ;
ALTER TABLE stuinfo DROP PRIMARY KEY ;
ALTER TABLE stuinfo DROP FOREIGN KEY fk;