MySQL Basic operation

Start Mysql:mysql.exe-hlocalhost-p3306-uroot–p

Exit: Exit,quit,\q

Database operations:

1. New Database

Create database name "Options"

Library options: Used to constrain the database

-Character Set: Charset/character set specific character set (encoding format for data storage): Common character sets: GBK and UTF8

-Proofing Set Settings: Collate specific proofing sets (rules for data comparisons)

Example: Create DATABASE MyDatabase charset UTF8

The database name cannot be used for keywords or reserved words (which may be used in the future). If you want to use it, you must add an inverse quotation mark (the key below the ESC key is entered in the English state)

Chinese database is also possible, but to ensure that the server can be recognized, but it is best not to, of course, can also tell the server the current Chinese character set is what-set names GBK

2. View the database

1) View all databases

Show databases

2) View the specified database: Fuzzy query

Show databases likes ' pattern '--pattern is a matching pattern

%: Indicates that multiple characters are matched

_: Represents a single character match

However, a wildcard character is escaped by "\".

3) View "Database creation statement"

Show CREATE DATABASE name

3. Update the database

Database modification limited to library options: Character set and proofing set (collation set dependent character set)

ALTER DATABASE name "library Options"

ALTER DATABASE MyDatabase CharSet UTF8

4. Delete Database

Drop database name

Table Operations

1. New Data Sheet

The CREATE table [if not EXISTS] table name (

Field name data type,

Field name data type,

。。。。。 The last line does not require commas

) "Table Options"

[If not EXISTS]: created if the table does not exist, otherwise not created

Table options:

-Character Set: Charset/character set specific character set

-Proofing Set Settings: Collate specific proofing sets

-Storage Engine: Engine specific storage engines (InnoDB and MyISAM)

Note: Data table creation to specify database

1) Displays the database to which the specified table belongs: CREATE table if not exists mydatabase.student

2) Enter database first: Use database name

Cases:

CREATE table if not exists mydatabase.student (

Name varchar (10),

Gender varchar (10),

Number varchar (10),

Age int

) CharSet UTF8;

2. View Data Sheet

1) View all tables

Show tables

2) View the specified database: Fuzzy query

Show tables likes ' pattern '

3) View Table creation statement

Show CREATE table name "\g or \g"

\g Portrait display \g landscape display

4) View the field information in the table

Yfdesc\describe\show columns from table name

Example: Desc\describe student; Show columns from student

Whether field name fields are allowed to be empty index default value extension

3. Modify the Data sheet

1) Modify the table name

Rename table cousin name to new name

2) Modify Table options

ALTER TABLE table name options

Example: ALTER TABLE student CharSet GBK

3) Modify the field

-New fields

ALTER TABLE name add "column" Field name data Type "Columns property" "Position"

Location: first: Position one

After: Which field is after; default in last field

-Modify Fields

ALTER TABLE name modify field name Data Type property "Location"

Example: ALTER TABLE student modify number char (ten) after name;

-Rename Fields

ALTER TABLE name change old field new field data Type property "position"

Cases ALTER TABLE student change gender sex char (TEN) after name

-delete Field

ALTER TABLE name drop field name

4. Delete Data Sheet

drop table name 1, table Name 2 ....

Data manipulation

1. New data (char, Chinese plus single quotation mark)

1) Insert data into the whole table without specifying a field list, but the data order is consistent with the field order.

Insert into VALUES (list of values) "(Value list)" .... Multiple records can be inserted at once

2) inserting data into some fields

Insert INTO (field List) VALUES (list of values) "(Value list)" .... Multiple records can be inserted at once

2. View data (same as SQL Server)

Select field 1, Field 2 .....

From table

Where condition

3. Update data

Updata Table Name

Set field = value

[Where Condition]

4. Delete data

Delete from table

[Where Condition]

Chinese data issues

Chinese data problem is actually a character set problem

View all character sets supported by the server

Show character set;

View the server default character set for external processing

Show variables like ' character_set% '

To resolve the insert problem:

Modify the server Word default receive character set for the client character set

Set character_set_client= "Character Set"

Solve garbled problem;

Modify the server Word by default the character set that is sent to the client is the client character set

Set character_set_results= "Character Set"

Connection layer: Character_set_connection, is the intermediary of the character set transformation, if agreed, more efficient, can also not change

The function of Set names "character set" is equivalent to modifying the above three

Set variable = value modified knowledge Session level (current client, when the secondary connection is valid)

Proofing Set Issues

Proofing sets: how data is compared

Three formats for proofing sets:

_bin:binary, binary comparison, removal of bits, one one comparison case-sensitive

_cs:case sensitive, case-sensitive, uppercase and lowercase

_ci:case insensitive, case insensitive, case insensitive

To view all the proofing sets supported by the database:

Show collation

Common Proofing Sets: Utf8_bin,utf8_general_ci, Gbk_chinese_ci,gbk_bin

The proofing set must be set before the data is available, and if there is data in the proofing set modification, the modification is not valid

MySQL Basic operation