Creating a database using MySQL in Windows (ii)

First, the database operation

1. View all databases currently in existence

Mysql>show databases;

2, switch the current use of the database

Mysql>use Test

3. Authorization

Mysql>grant all on database name. * To User name @ host name

4. Create a Database

Mysql>create Database Company;

Where: Company is the name of the database to be created

Second, create a table

1. View all tables in the current database

Mysql>show tables;

2. Create a table

CREATE TABLE Employees

(

ID int (4) unsigned Zerofill auto_increment primary key,

Name varchar () NOT NULL default ' unknown ',

Birthday date,

Position enum (' Boss ', ' worker ')

);

which

Employees is the name of the table to be created;

ID, name, birthday are field names;

unsigned unsigned;

Zerofill not enough digits to fill 0;

Auto_increment for the identity starting from 1 each time an increase of 1;

Primary key primary key;

Default ' unknown ' defaults;

Not null non-null;

Enum (' Boss ', ' worker ') enum type.

Common data types:

Char	Fixed-length characters
varchar	Variable-length characters
Int	Integral type
Date	Date
Datetime	Date Time
Decimal	Decimal

3. Display table structure

Mysql>describe employees;

4. Inserting data

A) inserting data by inserting a statement

Insert into employees (Name,birthday) VALUES (' Lucy ', ' 19901231 ');

b) inserting data through a text file

Create a text file, do not fill the fields with \ n Supplement, enter separate different records, \ t separate the different fields, assuming the file name is Emps.txt

Enter the command to load the data:

Load data local infile ' d:\dbdata\emps.txt ' into table employees lines terminated by ' \ r \ n ';