mysql-working with databases, tables, and table structures

1. Processing the database

1. View the database

SHOW DATABASES;

2. Create a database

CREATE DATABASE test;

3. Using the Database

USE test;

4. Deleting a database

DELETE DATABASE test;

2. Processing Data Sheet

1. Create a table

CREATE TABLE employees (    id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,    name VARCHAR(32) NOT NULL,    PRIMARY KEY(id));

2. Conditionally Create a table

CREATE TABLE IF NOT EXISTS employees (    id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,    name VARCHAR(32) NOT NULL,    PRIMARY KEY(id));

3. Copying a table

向数据库增加一个相同的表：CREATE TABLE employee2 SELECT * FROM employee;向数据库增加几列相同的表：CREATE TABLE employee3 SELECT name FROM employee;

4. Create a temporary table (need to have create temporary table permission )

CREATE TEMPORARY TABLE emp_temp SELECT * FROM employee;

5. View available tables in the database

SHOW TABLES;

6. View Table structure

DESCRIBE employee;或者SHOW columns IN employee

7. Delete Table (drop table drop)

语法：DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name, ...]删除表employee: DROP TABLE employee;同时删除多个表：DROP TABLE employee, employee2, employee3;

3. Change the table structure ( ALTER）

1. Add a field

ALTER TABLE employee ADD COLUMN birthday DATE;

2. Keywords to control the location of new columns (first,after, last)

在name后面添加birthday列：ALTER TABLE employee ADD COLUMN birthday DATE AFTER name;birthday不能为空：ALTER TABLE employee ADD COLUMN birthday DATE NOT NULL;

3. Delete Columns

ALTER TABLE employee DROP birthday;

mysql-working with databases, tables, and table structures