MySQL Little note

Another application is in Server management, the parent process that does not involve the database server program to stop running, you can also use these tools to terminate. Why can't the parent process of the database server kill with these tools? The simple reason is that these tools will cause the database to generate more file fragments when the database server is forcibly terminated, and the database will be at risk of crashing when the fragmentation reaches a certain level. For example, the MySQL server is best to shut down its normal program , rather than using pkill mysqld or killall mysqld Such dangerous actions Of course, we should use kill to kill the database sub-processes that consume too much resources .

Change the MySQL command prompt in order to prevent accidental operation

1, temporary modification mysql> prompt \[email protected] \r:\m:\s->  , prompt set to ' \[email protected] \r:\m:\s-> ' [email Protected] 01:11:11->2, permanently modify inside the configuration file [Mysql]prompt=\\[email protected] \\r:\\m:\\s->

To view a user's permissions

Select User,host from mysql.user;+------+-----------------------+| user | Host                  |+------+-----------------------+| root | 127.0.0.1             | | root |:: 1                   | |      | localhost             | | root | localhost             | |      | localhost.localdomain | | root | localhost.localdomain |+------+-----------------------+6 rows In Set (0.00 sec) SHOW GRANTS for ' root ' @ ' localhost '; +--------------------------------------------------------------- ------+| Grants for [email protected]                                           |+---------------------------------------------------------------------+| GRANT all privileges on * * to ' root ' @ ' localhost ' with GRANT OPTION | | Grant PROXY on "@" to "root" @ ' localhost ' with GRANT OPTION        |+--------------------------------------------------- ------------------+2 rows in Set (0.00 sec)

Password modification

Set the password for the administrator root user, mysqladmin-u root password ' * * * * * mysqladmin-uroot password oldboy123-s/data/3306/on the Linux command line Mysql.sock Password Change password command line change password mysqladmin-u root-p oldpassword password ' * * * * #mysqladmin-u root-p password Password ' * * * * * -s/data/3306/mysql.sock through the SQL statement to modify the password, this method is suitable for the root password to forget the situation to modify the password, this is risky, the condition must be specified correctly, you must use the password () function to encrypt, Sometimes single quotes may have problems with update Mysql.user set Password=password ("456") where user= ' root ' and host= ' localhost ' flush privileges; Password=password (' * * * ') a single instance after modifying the password, if prompted about the PID of the problem with the following method, you need to mysqladmin-u Root-pnewpassword shutdown, and then open

Multiple instances of the way

Mysql_safe--defaults-file=/data/3306/my.cnf--skip-grant-table &mysql-u root-p-s/data/3306/mysql.sock Login

　　

SQL classification:

1. Data Query Language (DQL) select2, Data Manipulation Language (DML) Insert update Delete3, transaction language (TPL) BEGIN TRANSACTION Commit ROLLBACK4, Data Control Language (DCL) grant Revoke5, Data definition language (DDL) create DROP6, Control Language (CCL) DECLARE cursor fetch into commonly used in the 3 class ddl-data definition language Create alter  dml-Data Manipulation language for Drop ======= OPS Select Insert Delete update======= multi-dcl-Data Control Language for development grant revoke commit rollback======== = = = More for operation and maintenance

To view the build database statement

Show CREATE DATABASE oldboy\g*************************** 1. Row ***************************       database:oldboycreate database:create Database ' Oldboy '/*!40100 DEFAULT CHARACTER set UTF8 */1 row in SET (0.00 sec)

To create a GBK database

Create DATABASE  OLDBOY_GBK default character set GBK collate gbk_chinese_ci;mysql> show CREATE DATABASE OLDBOY_GBK ;+------------+--------------------------------------------------------------------+| Database   | Create Database                                                    |+------------+--------------------------------------------------------------------+| Oldboy _GBK | CREATE DATABASE ' OLDBOY_GBK '/*!40100 DEFAULT CHARACTER SET GBK */|+------------+------------------------------------- -------------------------------+1 Row in Set (0.00 sec

To create a UTF8 database

Create database Oldboy_utf8 character set UTF8 collate utf8_general_ci;

Problems like these character sets can be defined at compile time, and will not be set after specifying, using default values

In the enterprise to determine the character set according to the developed program (recommended UTF8), compile the time to specify the character set
-ddefault_charset=utf8 \
-DDEFAULT_COLLATION=UTF8_GENERAL_CI \

Displays the current database, select Database ();

Delete System Surplus Account

Drop user "user" @ "host" if drop cannot be deleted, delete from Mysql.user where user= ' root ' and host = ' oldboy '; flush privileges;

Create a user and authorize by using the GRANT command:

Example: CREATE USER ' jeffrey ' @ ' localhost ' identified by ' mypass '; GRANT all on db1.* to ' Jeffrey ' @ ' localhost '; GRANT SELECT on Db2.invoice to ' Jeffrey ' @ ' localhost '; GRANT USAGE on *. Jeffrey ' @ ' localhost ' with max_queries_per_hour 90;

Grantall Privilegeson dbname.*to [email protected]indentified by ' passwd ' authorization command corresponds to permissions target: library and table user name and client host user password one step method grant all privileges on test.* to [e-mail protected] ' localhost ' identified by ' 123 ', show grants for [email protected]; First create user re-authorize creation User [email protected] identified by ' 123 ', grant all on dbname.* to [email protected];

Authorized host remote connection database in LAN:

According to the grant command, we know [email protected] location is authorized to access the database host, localhost can use domain name, IP address or IP segment to replace, therefore, to authorize the LAN within the host can be implemented by the following methods: 1, percent semicolon match grant all On * * to [email protected] ' 10.0.0.% ' identified by ' * * * 2, Subnet mask match grant all on * * to [email protected] ' 10.0.0.0/255.255.2 55.0 ' identified by ' * * * * flush privileges;

MySQL user can authorize permissions, when authorization is best not to give all permissions retract INSERT permission revoke insert on test.* from [email protected]; So the enterprise generally give select, insert Update Delete4 a right

　　

CREATE TABLE Table name (< field name 1>< type 1>,......< field name n>< type n>); View Build table statement Show CREATE TABLE * * * * * View the description of the build table desc * * * * *

To create an index on a table's fields

Create a primary key index, query the database, press the primary key query is the fastest, each table can have only one primary key column, but can have multiple ordinary index columns. The primary key column requires that all the contents of the column must be unique, and the normal indexed column does not require that the content be unique. 1, can be built on the table when creating an index create TABLE student (ID int (4) NOT NULL auto_increment, self-increment name char () not null,age tinyint (2) is NOT null Def Ault ' 0 ', Dept varchar (+) default Null,primary key (ID),  primary key index key index_name (name)  Normal index);

　　

2. Add primary key index by ALTER command after building table (not recommended) ALTER TABLE student drop primary Key;alter table student change ID int primary KEY auto_inc Rement

　　

3, the unique index, in the case of a primary key, also want to make a column unique, you will create a unique index ordinary index ALTER TABLE student DROP INDEX index_namealter table student Add index index_name ( name); Index names unique indexes, usually used to constrain the contents of a table (non-primary key) create unique index index_name on student (name);

4. Create a normal index on the first n characters of the field creating index index_name on table (name (8));        Index name     table  column      Show index from TABLE\G

　　

5. Create a federated index for multiple fields of a table if the data condition of the query is multiple columns, we can create a federated index for the columns of multiple queries, and even create a federated index for the first n character columns of the multi-column. Create INDEX ind_name_dept on student (name,dept Drop INDEX Ind_name_dept The first 8 characters of the Student;name column, the first 10 characters of the Dept column, create index ind_name_dept on student (name (8), Dept (10)) Note: When querying data by Condition column, the Federated Index is an index with the prefix effect attribute (a,b,c) only a,ab,abc three query criteria columns can be indexed, nothing else, b,bc,ac,c, etc. cannot use the index

Creation and effective conditions of indexed columns
1, since the index can speed up the query, then give all the Lie Jian index?
Because indexes not only occupy system space, but also need to maintain index data when updating a database, the index is a double-edged sword, not the more the better, the dozens of to hundreds of rows of small tables do not need to index, update frequently, read fewer tables to index
2. Which columns you need to create an index on
Select User,host from Mysql.user where host= ..., the index must be created on the condition column after the where, instead of the Select data column after select, and we try to make an index on a large table with a unique value.

　　

Operations on a table

Insert, preferably BULK Insert do not insert 1, the rules specify all column names, and each column inserts values INSERT into Test (ID, Name) VALUES (1, ' Oldboy '), 2, because the ID column is self-increasing, so, You can insert values only in the Name column insert INTO Test (' Oldgirl '), 3, if you do not specify a column, insert into test values (3, ' adfasdf ') according to the table structure; You can also insert multiple inserts into test values (3, ' adfasdf ') at once, (4, ' 3242342d ');

　　

Backup of the database:

Mysqldump-uroot-ppass-b database >/opt/oldboy_bak.sql    databases empty a file >****** restore Mysql-uroot-ppass Oldboy </ Opt/oldboy_bak.sqlselect, do not use *, with the specific column will be good

　　

Select Id,name from Test limit 2;select id,name from test where Id=1;select id,name from test where name= ' ADSFADSF '; string to be cited Number select Id,name from test order by id desc; flashback, default ascending select Id,name from test order by ID Aesc; ascending

　

Multi-table Query, example

Select Student.no,student.name,sc.grade,cource.name from Student,sc,cource where student.sno=sc.no and COURCE.JJJ

　　

Query the execution plan of a SELECT statement using Ecplain if it is found that this SQL statement does not go through the index, there may be a problem explain select * from Test where name= ' Dfafds ' \g

　　

modifying data in a table

Update test set name= ' Gongli ' where id=3 will change the entire table if not added

　　

Prevent MySQL data from mis-operation

When you log in, add the-u parameter Mysql-uroot-ppass oldboy-u so that you cannot write to the data

　　

Delete data

Delete from test where id=1 is removed without conditions

　　

Clear table

TRUNCATE TABLE test;

　　

Add Field

ALTER TABLE test add sex char (4); ALTER TABLE test add age (4) after name;help ALTER TABLE

　

Change table name

Rename table Test to Test1alter table test1 Rename to test

　　

Delete a table

drop table test;

　　

MySQL inserted in Chinese does not garbled 5 ways:

Temporary client changes: Set names latin1 Permanent: Change the parameters of the MY.CNF client module, you can implement the effect of set names Latin1, and permanently take effect on the server side change profile [mysqld]default-character-set= UTF8 suitable for 5.1 and previous versions character-set-server=latin1 suitable for 5.5 library tables, program CREATE DATABASE oldboy default character set UTF8 collate utf8_ General_ci;

Show variables;

Show status;

Show global status;

KEY_BUFFER/ETC/MY.CNF MyISAM engine Buffers

Mysql-e

   GBK      length 2   is not an international standard   UTF-8        3   English-Chinese mixed environment, it is recommended to use this character set, with a comparison of   latin1       1   mysql default character set   UTF8MB4      4   utf-8 Unicode, which seems to be used in iOS development

How MySQL chooses the right character set

Character Set selection:   1, if the processing of a variety of text, published to different languages country region, should choose the Unicode character set, for MySQL is utf-8 (three bytes per kanji), if the application needs to handle English, only a few Chinese characters utf-8 better   2, If only support Chinese, and the amount of data is very large, performance requirements are very high, optional GBK (fixed length, each Chinese character account for double-byte, English also accounted for double-byte), if a large number of operations, comparative sequencing, fixed long character set, faster, high performance   3, processing mobile internet business, You may need to use the UTF8MB4 character set

　　

Show variables like ' character_set% '; character_set_client client  Character Set client [client]character_set_connection connection character Set client [ Client]character_set_database   Database Character Set, configuration file specify or build a table specifies that you can modify Character_set_results    in the service side of the configuration file, i.e. [mysqld] Returns the result character set client [client]character_set_server     server character set, configuration file specified or built-in database specified can be modified in the service side of the configuration file, i.e. [mysqld]

Changes are required on the system after the character set is modified on/etc/sysconfig/i18n. /etc/sysconfig/i18n effective equivalent to source/etc/sysconfig/i18n
lang= ' Zh_cn.utf8 '

When the character set is not correct, the inserted value is also incorrect. If you change to the correct character set after inserting, it will still show garbled characters.
When the character set is not correct, you need to change it according to the Library and table

So the environment is not a problem, all engage in UTF8

When the MySQL character set problem that is garbled data, the first view of the system, server, client, library, table and other character sets, by default, the default character set when the library is built to follow the system, if you create a database or table when you change the character set will be used show create database/ Table ***\g, view the character set, and then modify the character set for the library/table's character Set set names * * *.

　

There are already recorded character set adjustments that must be exported before the character set is modified and re-imported before it can be completed.

1. Export table structure mysqldump-uroot-p--default-character-set=latin1-d dbname > Alltable.sql, export the--DEFAULT-CHARACTER-SET=GBK character set in latin1 form to connect the-D guide table structure 2, edit Alltable.sql latin1 to GBK3, ensure that the database is no longer updated, Export all data mysqldump-uroot-p--quick--no-create-info--extended-insert--default-character-set=latin1 dbname> Alldata.sql parameter Description:--quick: Used to dump a large table, forcing mysqldump to retrieve data from the server one row at a time instead of retrieving all rows and outputting the front cache into memory. --no-create-info: CREATE TABLE statement is not created--extended-insert: Use a multiline insert syntax that includes several values lists, so that the file is smaller, the IO is small, Importing data is very fast--default-character-set=latin1: Export the data according to the original character set, so that all Chinese is visible in the exported file, and will not be saved as garbled 4, open alldata.sql set names Latin1 modified to set names GBK; (or modify the server and client of the system) 5, build database dbname default charset gbk;6, Build table, execute Alltable.sqlmysql-uroot- P DBNAME<ALLTABLE.SQL7, import data mysql-uroot-p Dbname<alldata.sql

　　

　　

　　

　　

　

　　

　

　　

MySQL Little note