20141229 MySQL basic Operation II

Modify Table

There are two types of modified tables: modify the table itself, modify the fields inside the table

Modify table itself: Modify table name and table options

Modify table name

Rename table name to the new name;

Modify Table Options

ALTER TABLE table name option;

Modifying the storage engine modifies files under the corresponding database folder

modifying table fields: Adding and deleting changes

ALTER TABLE name [Add/drop/modify/change] Field name data type [position]

Location: First/after field name, after the default is itself or all fields

Add Field

ALTER TABLE name add [colomn] Field name data type [position]

Modify a field: Modify the name and modify the field's data type

Modify Field name

ALTER TABLE name change field formerly known as new name data type;

modifying field data types

ALTER TABLE name modify field name data type [position];

Delete a field

ALTER TABLE name drop field name;

Delete a table

Delete the table and the data inside the table

Grammar

drop table name, table name;

You can delete multiple tables at once, separated by commas.

Note: The data table should not be deleted easily, it is best to back up before deleting.

Data Manipulation

Increase and revise the data in the table

New Data

Data is different from structure, SQL uses keyword insert into

Grammar

insert into table name [(Field list: field comma delimited)] VALUES (Value list: must correspond exactly to the field list.) Quotation marks, comma separated values) [, (Value list)]

There are several new ways to add data:

1. Part of the field to add data, the system will automatically use the default value of the field to fill, you must specify the field list and value list, the order must be one by one corresponding (field List field can be different from the field in the table structure)

Note: If you insert data in this way, you must have a default value for fields with no data inserted or self-growing

2. Add data to all fields: there can be no field list, and if there is no field list, the order of the data in the value list must match the order of the fields in the table structure.

3. You can insert multiple data (records) at once, separating them with parentheses and commas after values

View Data

Take the data out of the table to view

Grammar:

SELECT */Field list from table name where condition;

Where conditions are used to filter data

Update Data

Grammar

Update table name SET field name = value [, field = value ...] where condition;

Update multiple records

Delete Data

Grammar

Delete from table name where condition; --without conditions, all data will be deleted.

Character Set

1. What is a character set?

A character set is a collection of characters, and the rules of a transformation should be followed when different characters are stored in binary.

Character Set problem resolution

1. Understand what character sets the server can support?

Show character set;

2. Understanding default Character set support for external clients of the server default

Show variables like ' character_set% ';

MySQL client only supports GBK, while server default client is UTF8 character set

Solution: Change the data sent by the client that the server considers to be UTF8 to GBK

Set character_set_client = GBK;

Resolving effects

Query effect

Contradiction: The results of the query is still garbled, because the server thinks the client can parse the result of the character set is UTF8, the client is actually GBK,GBK can parse, just parse into garbled.

Solution: Modify the set of characters that the server thinks the client can receive results for GBK;

Set character_set_results = GBK;

Set names GBK: What you do is to make the server-related character set (client and results) become GBK, and the system will also turn connection into GBK;

Connection is just for the data to be converted internally, the efficiency of the conversion is increased

Schematic diagram

Settings for the character set: set according to the client's own character set.

20141229 MySQL basic Operation II