MySQL Daily management

View the version of the database server

Select version ();

Create a database

Create DATABASE dbname Character_set=utf8 collate=utf8_general_ci

Deleting a database

Drop Database dbname;

Select the database to use

Mysql>use dbname;

View the table names that exist in the database

Mysql>show tables;

Mysql>show tables from schema_name;

Create a database table

CREATE TABLE Tbname (column_name column_type contraints,......)

The tables in MySQL are stored as files in the folder.

/var/lib/mysql/table name. frm

When you create a table, you can specify a different character set and collation (collation) for each field

View the DDL statement that created the table

Show CREATE table Tbname \g;

Delete a table

drop table tbname;

Modify Table

ALTER TABLE tbname modify COLUMN_NAME Newtype;

ALTER TABLE tbname add column_name column_type;

ALTER TABLE tbname drop column colname;

ALTER TABLE tbname change Col_name_old col_name_new;

ALTER TABLE Tbname add col_name col_type after col_name2;

ALTER TABLE Tbname add Col_name col_type before col_name2;

ALTER TABLE Tbname Modify Age init (3) first;

ALTER TABLE tbname rename tbname_new;

Retract Insert Permissions

Grant SELECT, insert on test.* to ' uname ' @ ' localhost ' identified by ' PWD ';

Revoke insert on test.* from ' uname ' @ ' localhost ';

View System Variables

Show variables like ' Time_zone ';

Modify the MySQL time zone

Set time_zone= ' +9:00 ';

Data type

If the month Date: Date

Month and day seconds: datetime

Hours and seconds: time

System time: Timestamp

Represents year only: Years

Client Tools for MySQL

1. MySQL

When you do not have a client program that goes into MySQL, you can execute the SQL text in the following manner.

MYSQ < Xxx.sql (initialization parameters can be configured in C:/windows/my.ini)

Using redirection, the following execution is interrupted when a SQL error occurs. In order to continue the execution of the contents later in the file. can be achieved by adding the force option.

MySQL--force < Xxxx.sql

My.ini

----------------------

[Client]

User=root

Password=xxxxx

After you enter the MySQL client, you can execute the contents of the. sql file. If there is an error in SQL, you need to terminate the execution of the following when an error occurs. When you start the MySQL client, add the following parameters. MySQL--abort-source-on-error

Mysql>source Xxxx.sql;

MYSQL-E "Show Databases"; (No login, direct access)

Leave the MySQL client: Exit quit \q

Change database: Use dbname | \u dbname (Note that there are no symbols behind it)

1. mysqladmin

Mysqladmin Create dbname

Mysqladmin Drop dbname

Mysqladmin Extended-status

Mysqladmin password pwd

Mysqladmin Ping--p--host=xxx.xxx.xxx.xx

Mysqladmin Status--sleep=1--count=2

Mysqladmin Status--debug-info

setting of the character set

http://www.nowamagic.net/librarys/veda/detail/138

Problems:

ERROR 1366 (HY000): Incorrect string value

Ensure that the database character set is the same as the character set for the build table and supports the input characters.

Specifying a character set at the database level

The setpoint character set is made in the My.cnf file and is consistent with the database and table under construction.

my.cnf set the following attribute values in the

[Client] Default-character-set=utf8 [MySQL] Default-character-set=utf8 [Mysqld] Character_set_server=utf8 Character_set_filesystem=utf8

Set the default character set by setting the system variable.

Set Character_set_server=utf8

Set Collation_server=utf8_general_bi

Specifying character sets for individual databases

when creating a database, specify the character set and Collation , so when creating a table, if you do not specify it, create the character set specified by the database and Collation to create the table.

Show variables and set commands, which are the session-level commands by default.

Show Session Variables

Show global variables

Set Global xxx=yyy

SET @ @global xxx=yyy

Set Session XXX=YYY

SET @ @session xxx=yyy

1) View the default character set

SHOW VARIABLES like ' character% '

SHOW VARIABLES like ' collation% '

2) Modifying the character set

2.1> modifying the character set at the server level (modify the My.cnf file or place it from the command line)

2.2> modifying character sets at the level of the database (specified when creating the database)

Set Global | Session Character_set_server=utf8

Use Set The variable that is set by the command is invalidated when the database is restarted.

SHOW VARIABLES like ' character% '

SHOW VARIABLES like ' collation% '

Set Character_set_system=utf8 (always set this value, because MySQL server default)

Set Character_set_server=utf8

Set Character_set_results=utf8

Set Character_set_database=utf8

Set Character_set_connection=utf8 the character set of the current connect transfer

Set Character_set_client=utf8 the setting of the character set used by the client

Set Character_set_results=utf8

Set Collation_connection=utf8_general_ci

Set Collation_database=utf8_general_ci

Set Collation_server=utf8_general_ci

SHOW VARIABLES like ' character% '

SHOW VARIABLES like ' collation% '

Set Character_set_system=utf8 (always set this value, because MySQL server default)

Set Character_set_server=utf8

Set Character_set_results=utf8

Set Character_set_database=utf8

Set Character_set_connection=utf8 the character set of the current connect transfer

Set Character_set_client=utf8 the setting of the character set used by the client

Set Character_set_results=utf8

Set Collation_connection=utf8_general_ci

Set Collation_database=utf8_general_ci

Set Collation_server=utf8_general_ci

If the client is using a ASCII connects to the database, but wants to convert all the statement into a UTF8 line,

so character_set_client=ascii

Character_set_connection=utf8

Character_set_result: Character Set results returned to the client

Typically, character_set_client and the Character_set_result should be set to the same.

SELECT collation_name, Is_default from information_schema.collations WHERE character_set_name = ' UTF8 ';

MySQL Daily management