MySQL Learning (2)-SQL statement creation, deletion, modification, and Chinese garbled characters

MySQL Learning (2)-SQL statement creation, deletion, modification, and Chinese garbled characters

1. Database Operations

1. Create a database

Create database name;

Create an encoded: create database name character set encoding;

View the encoding: show create database name;

2. delete a database

Drop database name;

3. database used

Use Database Name;

4. view the database currently being operated

Select database ();

Ii. Operations on database tables

1. Create a table

Create 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 a table

1) Add a column

Alter table name add field name type (length) [constraint];

Eg. alter table user add uinfo varchar (32) not null;

2) modify the column type (length and constraints)

Alter table name modify Field name type (length) to be modified [constraint];

Eg. alter table user modify uinfo varchar (64) null;

3) modify the column name

Alter table name change old column name new column name type (length) [constraint];

4) delete a table column

Alter table Name drop column name;

5) modify the table name

Rename table name to new table name

6) modify the character set of a table

Alter table name character set encoding;

View the code of the current table: show create table tbl_user;

3. Operate (modify) database table records)

1. insert 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) how to solve the problem of Chinese garbled characters inserted into data

Method 1: (not recommended !)

Directly modify my. line 57th Of The INI file (in the MySQL \ MySQL Server 5.0 directory, if MySQL is directly modified on the C drive, access is denied. copy the INI file to another disk after modification)

Default-character-set = utf8

To make it take effect, you must re-open the command prompt and restart mysql (first, net stop mysql and then net start mysql and then log on)

Method 2:

Enter mysql> set names gbk at the command prompt;

2. Modify Table records

1) without conditions

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

It will change all records of this column

2) conditional

Update table name set field name = value, field name = value, field name = value ...... where Condition

3. Delete table records

1) conditional

Delete from table name where condition;

Note: If you delete a record whose uid is 1, the uid will not be reset after the record is deleted.

2) without conditions

Delete operation: delete from table name;

3) Interview Questions

What is the difference between delete and truncate?

The delete operation is a delete record. It works with transactions to retrieve the deleted data.

Truncate is used to delete the entire table and create an identical table. The deleted data cannot be retrieved.

Delete operation Demo:

Truncate operation Demonstration:

Note: When delete is deleted, the uid will not be reset. When the truncate operation is used, the uid will be reset (because it deletes the table structure and then creates an identical table, so when you insert data again, it starts from 1)