manipulating databases and tables

Mysql–u User name –p password

1. Operational database
Create a database
CREATE DATABASE [IF not EXISTS] db_name [create_specification [, Create_specification] ...]
Create_specification:
[DEFAULT] CHARACTER SET Charset_name | [DEFAULT] COLLATE collation_name

Create DATABASE mydb1;//
Create DATABASE mydb2 character Set GBK; GBK Character Set
Create database mydb3 character Set UTF8 collate utf8_bin;//specify character set and proofing rules


View Database
show databases; Querying the database
Show CREATE Database mydb1; Create statement used when MYDB1 is created before querying

Modify Database
ALTER DATABASE MYDB1 character set UTF8; Change the character set of mydb1 to UTF8

Deleting a database
Drop database mydb1;

Select Database
Use MYDB1;
Select Database (); View the currently selected database

2. Operation table
Create a table
CREATE TABLE table_name
{
Field1 datatype,
Field2 datatype,
FIELD3 datatype,
} [Character set character set] [collate proofing rules]

Field: Specify column name datatype: Specify column type

string: VarChar Char
Big Data: Blob (4G spatial binary data) text (large text 4G space such as fiction)
Value: tinyint ( -128~127) samllint (2 bytes -32768 32767) int (4) bigint (8) Float double
Logic: Bit
Date: Date Time datetime timestamp

CREATE TABLE Employee (
ID int primary KEY auto_increment,
Name varchar (unique),
Gender bit NOT NULL,
Birthday date,
Entry_date date,
Job varchar (40),
Salary Double,
Resume text
);
Define primary key Primary key
Define the primary key auto-grow: auto_increment,
Defining UNIQUE constraints: Unique
Define a non-null constraint: NOT NULL

View Table
View all tables in the current database show tables;
View table structure desc table name;
View Build Table statement show create TABLE employee;

To modify a table:
Add a column: ALTER TABLE employee add image blob;
Modify a column length of 60:alter table employee Modify job varchar (60);
Delete a column: ALTER TABLE employee drop gender;
Modify table Name: Rename table employee to user;
Modify the table's character set to Utf8:alter table user character set UTF8;
Modify the name of the column: ALTER TABLE user change name username varchar (20);

Delete tables: drop table employee;

manipulating databases and tables