MySQL Learning (ii)--SQL statement creation Delete modification and Chinese garbled problem

First, the operation of the database

1. Create a library

Create database name;

Create a character set code with encoding: Create database name;

View Code: Show create database name;

2. Delete a library

drop database name;

3. Use Library

Use library name;

4. View the currently operating library

Select Database ();

Second, the operation of the database table

1. Create a table

CREATE TABLE Table name (

Field Name type (length) [constraint],

Field Name type (length) [constraint],

Field Name type (length) [constraint]

);

2. View database tables

Show tables;

View table structure: DESC table name;

3. Delete a table

drop table name;

4, modify the table

1) Add a column

ALTER TABLE name add field name type (length) [constraint];

eg. ALTER TABLE user add Uinfo varchar (+) not null;

2) Modify the type of column (length, constraint)

ALTER TABLE name modify the type of field name (length) [constraint] to be modified;

eg. ALTER TABLE user Modify Uinfo varchar (+) null;

3) Modify column names for columns

ALTER TABLE name change old column name new column name type (length) [constraint];

4) Delete the table's columns

ALTER TABLE name drop column name;

5) Modify the table name

Rename table table name to new name

6) Modify the character set of the table

ALTER TABLE name character set code;

To view the encoding of the current table: Show create TABLE Tbl_user;

Iii. operation of database table records (modified)

1. Inserting records

1) Insert into table name (column name 1, column name 2, column name 3 ...) value (value 1, value 2, value 3 ...);

eg. Insert into Tbl_user (Uid,uname,upassword) value (null, ' Zhangsan ', ' 123 ');

View record: SELECT * from table name;

2) insert into table name value (value 1, value 2, value 3 ...);

3) inserting data in Chinese garbled problem solving method

Way one: (Not recommended!) ）

Directly modify the database installation directory in the My.ini file 57th line (in the Mysql\mysql Server 5.0 directory, if the MySQL installation in the C-drive direct modification will deny access, then you can cut the My.ini file to another disk, modified and then copied over)

Default-character-set=utf8

To get it into effect, you must reopen the command prompt, restart MySQL (first net stop MySQL then net start MySQL again login)

Way two:

At the command prompt, enter mysql>set names GBK;

2, modify the table record

1) Non-conditional

Update table name SET field name = value, field name = value, field name = value ...

It will change all the records for that column

2) with conditions

Update table name SET field name = value, field name = value, field name = value ... where condition

3. Delete Table records

1) with a conditional

Delete from table name where condition;

Note that if you delete a uid=1 record, the UID is not reset after deletion

2) Non-conditional

Delete operation: delete from table name;

3) face question

What is the difference between delete and truncate?

Delete is deleted by a delete record, it with the transaction, you can retrieve the deleted data

Truncate DELETE, it destroys the entire table, and then creates an identical table. The data it deleted cannot be retrieved

The delete operation demonstrates:

Truncate operation Demo:

Note: Delete is deleted, the UID is not reset, and with the truncate operation, the UID is reset (because it deletes the table structure and then creates an identical table, so the data is inserted again from 1)

MySQL Learning (ii)--SQL statement creation Delete modification and Chinese garbled problem