Basic operation of MySQL console in Wamp

Input command Note:

1, the command ending symbol is a semicolon;

2, all the symbols are English half angle

3, only meet the semicolon MySQL to end the humanities

4. Multiple commands separated by semicolons CREATE database Stu;drop database stu

5, quotation marks to hit the whole or not to give up the semicolon

Commonly used words: Create (creation), use (usage), drop (delete), show (display), Databases (database), table (table)

First, open the MySQL console in the environment

Second, the basic operation

1, show all the current database show databases;

2. Create database Ceshi of new Ceshi databases;

Manually modify the encoding format of the database: Alter DB Ceshi character set UTF8;

3, using the database use Ceshi;

4, display the table in the database show tables;

Remark: Data type

Digital type:

Integer: tinyint 1 character 2^8 1 character = 256 bytes

int 4 byte 2^64

The total length of decimal (m,d) fixed-point m numbers

Number of digits of D decimal

Decimal (5,2) 1.2896-1.29

999.123-999.12

Character type:

Fixed long character char (255)

Variable-length character varchar (255)

DateTime 8 bytes 2018-06-01 09:05:01

Primary key: Primary key

Self-growth column: auto_increment

Non-empty: NOT NULL

Defaults: Default value

5. Build a table

You need to indicate that the data type is non-empty

Each table is set to a primary key

CREATE TABLE MyClass (

ID int (4) NOT null primary key auto_increment,

Name Char (a) is not NULL,

Sex int (4) NOT null default ' 0 ',

degree Decimal (16,2)

)

6. View Table structure

Desc MyClass;

========================================================

Show = Display

Create = Creation

Database = Databases

7. Inserting data

INSERT into MyClass values (' ', ' Zhang Baozhong ', ' ', ' 98.5 ');

8. View the data in the table

SELECT * from MyClass;

9. Modify the data

Update MyClass Set name = ' Zhang Baozhong ' where id = 1;

10. Delete Data

Delete from MyClass where id = 1;

Two

ALTER TABLE MyClass + statement

1. Modify the table name

Rename as NEWCL;

2. Add fields

Add age int (4) is not null;

Add PID int (4) not NULL first; Adding a PID in the first column

Add SELFSM char (255) not NULL after age; Add SELFSM in the Age column

3. Modify the name and type of the field

Change Age NewAge char (20);

4. Modify the type of a field

Modify Column NewAge int (4);

5, add default value-if there is a default value, first delete and then add

ALTER column newage drop default;

ALTER COLUMN NewAge set default 18;

6. Delete fields

Drop ID;

7. Add PRIMARY Key

Add primary key (field name);

8. Delete primary key

Drop primary key;

9. Delete a table

drop table NEWCL;

Show tables;
10. Delete Database
Drop database Ceshi;
show databases; (View all databases)

Basic operation of MySQL console in Wamp