Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Mysql common commands-examples-the most complete in history

2025-01-28 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

MySQL Command Daquan (A Xing Archive on November 22, 2018)

Mysql

Login MySQL method

1. Login method of single instance MySQL

Mysql # has just installed the password-free login method of the system, and does not need a password mysql-uroot # login mysql-uroot-p # where the standard dba command line login mysql-uroot-poldboy # is not generally used in non-scripts, the password will be disclosed in plaintext

two。 A method suitable for multi-instance to prevent password disclosure

Set variable

HISTCONTROL=ignorespacemysql-uroot-poldboy-S / data/3306/mysql.sock will not record login information if preceded by a space

Add 700 permissions to startup scripts, backup scripts, etc., and change users and groups to root

Chmod 700 / data/3306/mysqlchmod 700 / server/scripts/bak.sh

Delete command line record

History-d historical command serial number history-c after all cleanups, there will be records of cat ~ / .bash.history in the root home directory.

Multi-instance MySQL local login

Mysql-uroot-p-S / data/3306/mysql.sockmysql-uroot-p-S / data/3307/mysql.sock hint: multiple instances log in to different services by specifying different sock files through the-S command of mysql

Note: there is no need to specify a sock path for multi-instance remote connections

Mysql-uroot-p-h 127.0.0.1-P3307murh specifies the IP address,-P specifies the port number

The default prompt after login is: mysql > this prompt can be changed, just like the linux command line prompt

Mysql

To prevent misoperation, you can mark the prompt as the test environment, or you can write the configuration to take effect permanently

■ modifies the login prompt on the command line

Mysql > prompt\ u@abcdocker\ r:\ m:\ s-> PROMPT set to'\ u@abcdocker\ r:\ m:\ s-> 'root@abcdocker 05 u@abcdocker 03u@abcdocker 09-> root@abcdocker 05VO3u@abcdocker 11-> root@abcdocker 05VO3u@abcdocker 12->

Profile modifies login prompt

Add the following under the [mysql] module in the my.cnf configuration (note, not [mysqld]). After saving, you do not need to restart mysql, exit the current session, and log in again.

[mysql] prompt=\\ u@oldboy\ r:\\ m:\\ s-> MySQL help helps Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql > SHOW DATABASES LIKE'% MY%'; fuzzy match mysql > show databases like 'd3306% ambiguous match MySQL > show databases like'% 3306 alternative MySQL > show databases like'% 33% fuzzy matching; more parameters help show

■ 's method of forcibly shutting down the database (with caution)

Killall mysqldpkill mysqldkillall-9 mysqldmysqld: no process killedkill-9 pid

■ had better use elegant stop

Mysqladmin-uroot-poldboy123 shutdown

The case of a failed enterprise caused by ■ 's brutal and rude killing of the database:

Http://oldboy.blog.51cto.com/2561410/1431161http://oldboy.blog.51cto.com/2561410/1431172

Enterprise practice question 7:

Develop mysql multi-instance startup script: it is known that the mysql multi-instance startup command is: mysqld_safe-defaults-file=/data/3306/my.cnf& stop command: mysqladmin-u root-poldboy123-S / data/3306/mysql.sockshutdown Please complete the writing requirements of mysql multi-instance startup script: achieve with functions, case statements, if statements and so on.

Related address:

Develop MySQL multi-instance startup script ~

The old boy Shell enterprise interview questions 30 [answers]

Introduction of MySQL Database Security Policy

Set more complicated passwords for root, delete user accounts in useless mysql libraries, and only leave root@localhost to delete the default test database when deleting users, the authorized permissions are as minimum as possible, the scope of hosts allowed to access minimizes the user processing for mysql databases, and there are more stringent practices, such as deleting root users and adding new administrator users.

The difference between truncalt table test and delete from test;

■ truncate table test; is faster. Directly empty the physical file of the corresponding data.

■ delete from test; is slow, logical clear, delete by line

Set password method for administrator root user

Mysqladmin-uroot password 'oldboy' # mysqladmin-uroot-p oldboy password oldboy123-S / data/3306/mysql.sock without a password

It is suitable for multiple instances to change the password. Emphasize that the above command is executed on the command line rather than entering the root.

■ modify administrator root password method 1:

Linux command modification method

Mysqladmin-uroot-p oldboy password 'oldboy123' # original password, new password mysqladmin-uroot-p oldboy password oldboy456-S / data/3306/mysql.sock is suitable for multiple instances

■ modifies the administrator's root password method 2: * sql

Sentence modification method

Mysql > update mysql.user set password=oldboy456 where user='root' and host='localhost'; cannot be logged in with this setting

The password set in this way cannot be used and needs to be encrypted.

Mysql > update mysql.user set password=password ('oldboy456') where user='root' and host='localhost'

Result: if you do not use the password variable, the password below will be in clear text, and we cannot login in clear text

Mysql > select user,host,password from mysql.user +-+ | user | host | password | +- -- + | root | localhost | * 7DB922C59F217871B8165D72BEC8ED731A0EFFA1 | | root | db01 | root | 127.0.0.1 | root |:: 1 | | localhost | db01 | | +-- | -+ 6 rows in set (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 need to check whether it is successful The appearance of Changed indicates success.

After the setup is complete, we need to refresh before we can log in.

Mysql > flush privileges

Modify administrator root (all) password method 3

Set password=password ('oldboy123'); how to recover a lost MySQL password?

■ single instance

A, / etc/init.d/mysqld stopb, mysqld_safe-- skip-grant-tables-- user=mysql & c, mysqld, restart e after password change, / etc/init.d/mysqld restart

■ multiple instances

/ data/3306/mysql stop cannot stop killall mysqldmysqld_safe-- defaults-file=/data/3306/my.cnf-- skip-grant-tables-- user=mysql & update mysql.user set password=password ('oldboy456') where user='root' and host='localhost';flush privileges;mysql login mysqladmin-uroot-poldboy shutdown/etc/init.d/mysqld startSQL structured query language

What is SQL?

SQL, the full name of Structured Query Language in English, means structured query language in Chinese. It is a language method for defining and manipulating data in relational databases, and it is an industry standard language supported by most relational database management systems. Structured query language SQL is a database query and programming language, which is used to access data and query, update and manage relational database systems. At the same time, sql is used as the extension of MySQL logical backup files. Structured query language is a high-level non-procedural programming language that allows users to work on high-level data structures. He does not require the user to specify the data storage method, nor does it require the user to know the specific data storage method.

Summary: the most common categories of SQL statements are generally three categories.

DDL (DataDefinition Language)-data definition language (CREATE,ALTER,DROP) manages basic data, such as: library, table DCL (DataControl Language)-data Control language (GRANT,REVOKE,COMMIT,ROLLBACK) user authorization, privilege recovery, data submission rollback, etc. DML (DataManipulation Language)-data manipulation language (SELECT,INSERT,DELETE,UPDATE) operates on the data in the table in the database, recording commands explain

■ creates a database

Mysql > create database abcdocker

■ looks at the statement that creates the library

Show create database oldboy +-+-+ | Database | Create Database | +- -- + | abcdocker | CREATE DATABASE `oldboy` / *! 40100 DEFAULT CHARACTER SET utf8 * / | + -+ 1 row in set (0.00 sec)

Utf8 is specified at compile time, so utf8 is displayed here.

1. Website program character set 2. Client character set 3. Server-side character set 4.linux client character set 5. All the above should be unified, otherwise there will be garbled codes in Chinese.

■ commands to create databases with different character set formats

Mysql > create database abcdocker; # default database configuration, which is equivalent to creating Latin character set database mysql > create database abcdocker_gbk character set gbk collate gbk_chinese_ci; creating gbk format file mysql > help create databasemysql > show character set; # View character set mysql > show create database oldboy_gbk +-+-- + | Database | Create Database | +-+- -+ | abcdocker_gbk | CREATE DATABASE `oldboy_ gbk` / *! 40100 DEFAULT CHARACTER SET gbk * / | +-- -- + 1 row in set (0.00 sec)

If you specify a specific character set at the time of compilation, you do not need to specify a character set to create a database of the corresponding character set later.

-DDEFAULT_CHARSET=utf8\-DDEFAULT_COLLATION=utf8_general_ci\-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii\ Tip: binary software package, installed database character set default latinl

■ View Database

Show databases;select database (); # is equivalent to pwdselect user (); # View the current user select version (); # View the current version () is a function

Table information contained in the current database of ■

Use oldboy # is equivalent to cdshow tables # View tables orshow tables from wordpress # View table files under the wordpress library

■ Delete user

Drop user 'root'@'::1'; if the drop cannot be deleted (general reshuffle symbol or uppercase), you can delete it in the following way (take root, user as an example) delete from mysql.user where user='root' and host='oldboy';flush privileges; creates the MySQL user and grants the user permission

1. You can get the following information by typing helpe grant in mysql

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 *. * TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90

two。 Create a user and authorize it through the grant command

The simple syntax of the grant command is as follows: grant all privileges on dbname.* to username@localhost identified by 'passwd'

Note: the above command authorizes all permissions on the localhost host to manage the dbname database through the user username. The password is passwd, in which username,dbname,passwd can be modified according to the business situation.

User authorization for web connections should be minimized as far as possible. A lot of open source software is installed on the web interface, so in addition to the 4 permissions of select,insert,update,delete, more dangerous permissions such as create,drop are required during installation.

Grant select,insert,update,create,drop on blog.* to blog@localhost identified by '123'

Under normal circumstances, you can grant 4 permissions to select,insert,update,delete, and some source software, such as discuz,bbs, also need more dangerous permissions such as create,drop. After generating the database table, withdraw the create,drop authorization

Authorization of the production environment for users of the main library (write primary read as secondary)

■ normal environment:

Local: lnmp,lamp environment database authorization grant all privileges ON blog.* to blog@localhost identified by '123456' application server and database server are not authorized on one host; grant all privileges ON blog.* to blog@10.0.0.% identified by' 123' strict authorization: emphasis on security at the expense of convenience Grant select,insert,update,delete ON blog.* to blog@10.0.0.% identified by '123' production environment from the library (read-only) user's authorization; grant select ON blog.* to blog@10.0.0.% identified by' 123' view the specific authorization rights of authorized user oldboy show grants for 'oldboy'@'localhost'

Case 1. Create an abcdocker user with all permissions to the test library, allow login from the localhost host, and the password is abcdocker123

Grant all on test.* to abcdocker@localhost identified by 'abcdocker123'

■ the first kind: authorized user

Grant all on test.* to oldboy@127.0.0.% identified by 'oldboy123'show grants for oldboy@'127.0.0.%' View authorized users +- -- + | Grants for root@127.0.0.1 | +- -- + | GRANT USAGE ON *. * TO 'root'@'127.0.0.1' IDENTIFIED BY PASSWORD' * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' | GRANT ALL PRIVILEGES ON `test`.* TO 'root'@'127.0.0.1' | +- -+ 2 rows in set (0.00 sec)

The second method of ■: authorization

Create user bbs@'172.16.1.1/255.255.255.0' identified by '123456; first authorize mysql > show grants for bbs@'172.16.1.1/255.255.255.0';mysql > grant select on wordpress.* to bbs@'172.16.1.1/255.255.255.0' that can be logged in.

Authorize LAN hosts to connect to remote databases

a. One command percent matching method

Grant all on *. * to'test@10.0.0.%'identified by 'test123'

B. one command subnet mask configuration method

Grant all on *. * to test@'10.0.0.0/255.255.255.0' identified by 'test123'

C, the realization of two commands

First create the user and set the password

Create user test@'10.0.0.%' identified by 'test123'; then assigns permissions to the user and manages the database table grant all on *. * to test@10.0.0.0/255.255.255.0

Finally, remember that each of the above grant commands needs to refresh permissions

Flush privileges

Database remote login

Mysql-uwordpress-poldboy123-h 172.16.1.51-P3306murh specifies the IP address, and-P specifies the service port number

Create an administrator user similar to the root series, and you can create users of subordinate users

Grant all privileges on *. * to root@'127.0.0.1' identified by 'oldboy123' with grant option; only need to enter with grant option at the end

Reclaim user rights

REVOKE INSERT ON *. * FROM 'jeffrey'@'localhost'

Display the information of the library

Mysql > show create database oldboy\ G

MySQL inclusion relation

Database server > database (multiple instances) > multiple libraries > multiple tables > multiple field rows (data)

Create a tabl

Basic command syntax for creating tables:

Create table (); hint: where create table is the keyword and cannot be changed, but the case can be changed

Construction table sentence

The following is an example of a table statement designed by manual writing, table name student

Create table student (id int (4) not null, name char (20) not null, age tinyint (2) NOT NULL default'0, dept varchar (16) default NULL)

View the statement that creates the table

Mysql > show create table student Create table student (# create table represents the fixed keyword that creates the table, student is the table name id int (4) not null, # student number column, numeric type, length 4, cannot be null name char (20) not null, # name column, fixed length character type, length 20, cannot be empty age tinyint (2) NOT NULL default '0characters, # age column, very small numeric type, length 2, cannot be empty Default is 0 value dept varchar (16) default NULL # column, variable length character type, length 16, default is empty. ENGINE=lnnoDB DEFAULT CHARSET=latinl # engine and character set, engine defaults to InnoDB, character set, latinl of inherited library)

The visual display of the student table can be displayed in the following table.

Create table student (id int (4) not null,name char (20) not null,age tinyint (2) NOT NULL default '0parallel ENGINE=InnoDB DEFAULT CHARSET=latinl dept varchar (16) ENGINE=InnoDB DEFAULT CHARSET=latinl

Things to pay attention to: MySQL5.1 and MySQL5.5

The engine in the default table statement of the environment is different. If you want to control the engine, you should build the table in the specified engine shown in the table statement.

MySQL5.1 and the default engine is MyISAM,MySQL5.5. The default engine is the field type of InnoDBMySQL table.

1) numeric type

2) date and time type (DATE date type: the supported range is 1000-01-01 to 9999-12-31. MySQL displays date values in YYYY-MM-DD format, but allows you to assign values to DATE columns using strings or numbers)

3) string type

1. INT [(M)] type: normal size integer type

2. CHAR (M) type: fixed-length string type, when storing, always fill the right side with spaces to the specified length.

3. VARCHAR type: variable length string type

For more information about MySQL field types, please refer to the MySQL manual

1. INT [(M)] type: normal size integer type

2. DOUBLE [MMagar D] [ZEROFILL] type: normal size (double precision) floating point number type

3. DATE date type: the supported range is 1000-01-01 to 9999-12-31.MySQL to display date values in YYY-MM-DD format, but allows you to assign values to DATE columns using strings to numbers

4. CHAR (M) type: fixed length string type. When storing, the right side is always filled with spaces to the specified length.

5. BLOB TEXT type with a maximum length of 65535 (2 ^ 16-1) characters

6. VARCHAR type: variable length string type

The following figure illustrates the difference between CHAR and VARCHAR:

Explanation: for example, the VARCHAR (10) column can hold a string with a maximum length of 10. The actual storage requirement is the length of the string (L), plus a byte that records the length of the string. For the string 'abcd',L is 4, storage requires 5 bytes.

Summary

■ char fixed length, insufficient space completion, waste of storage space, fast query speed, most system table fields are fixed length

■ varchar becomes longer and query speed is slow

Example: mysql.user uses fixed length

`Password` char (41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT''

The user table uses CHAR

Production scenario case:

Formal table statement for the production of a sns product

Use sns;set names gbk CREATE TABLE `subject_comment_ manager` (`edit_user_ id` bigint (12) NOT NULL auto_increment COMMENT 'primary key', `subject_ type` tinyint (2) NOT NULL COMMENT 'material type', `subject_primary_ key` varchar (255) NOT NULL COMMENT 'primary key', `subject_ title`varchar 'NOT NULL COMMENT' material name', `edit_user_ nick`varchar (64) default NULL COMMENT 'modifier', `edit_user_ time`timestamp NULL default NULL COMMENT 'modification time' `edit_ comment` varchar (255) default NULL COMMENT 'reason for modification', `state` tinyint (1) NOT NULL default'1' COMMENT'0 means shutdown 1 means normal', PRIMARYKEY (`subject_primary_ id`), KEY `IDX_ PRIMARYKEY` (`subject_primary_ key` (32)), # show index from student

Create statement

Create index index_age on student (name (8)) Show index from student\ G View table location shows * * 3. Row * * Table: student Non_unique: 1 Key_name: index_age Seq_in_index: 1 Column_name: age Collation A Cardinality: 0 Sub_part: 8 Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: 3 rows in set (0.00 sec) create a federated index for multiple fields of the table

If the condition for querying data is multiple columns, we can create a federated index for the columns of multiple queries, or even for the first n characters of multiple columns, as demonstrated below:

Create a federated index that does not conflict with a single index

Mysql > create index ind_name_dept on student (name,dept)

Joint index function, query faster

Mysql > show index from student\ G * * 1. Row * * Table: student Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: a Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: * * 2. Row * * Table: student Non_unique: 1 Key_name: index_name Seq_in_index: 1 Column_name: name Collation: a Cardinality: 0 Sub_part : NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: * * 3. Row * * Table: student Non_unique: 1 Key_name: index_age Seq_in_index: 1 Column_name: name Collation A Cardinality: 0 Sub_part: 8 Packed: NULL Null: Index_type: BTREE Comment: Index_comment: * * 4. Row * * Table: student Non_unique: 1 Key_name: ind_name _ dept Seq_in_index: 1 Column_name: name Collation: a Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: * * 5. Row * * Table: student Non _ unique: 1 Key_name: ind_name_dept Seq_in_index: 2 Column_name: dept Collation: a Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: 5 rows in set (0.00 sec)

Create a federated index and specify the value size

Create index ind_name_dept on student (name (8), dept (10)) The first 8 characters of name First 10 characters of dept * * 2. Row * * Table: student Non_unique: 1 Key_name: index_dept Seq_in_index: 1 Column_name: name Collation: a Cardinality: 0 Sub_part: 8 Packed: NULL Null: Index_type: BTREE Comment: Index_comment: * * 3. Row * * Table: student Non_unique: 1 Key_name: index_dept Seq_in_index: 2 Column_name: dept Collation A Cardinality: 0 Sub_part: 10 Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: 3 rows in set (0.00 sec)

Tip: try to build an index on a large table with many unique values.

When will the federated index be created?

Only the program uses these two conditions to query, using a joint index, which mainly depends on the development.

Tip: when querying data by conditional column, the federated index is effective by the prefix

Create a unique index (non-primary key)

Create unique index uni_ind_name on student (name)

Index summary:

Create primary key index alter table student chage id id int primary key auto_increment; delete primary key index (primary key column cannot be incremented) alter table student drop primary key; create normal index alter table student add index index_dept (dept); create index create index index_dept on student based on the first n characters (dept (8)); create federated index create index index_name_dept on student (name,dept) based on multiple columns; create unique index create unique index uni_ind_name on student (name) Delete general index and unique index alter table student drop index index_dept;drop index index_dept on student; index column creation and effective conditions

Question 1. Since indexes can speed up queries, why don't you index all the columns?

Answer: because the index not only takes up the system space, but also needs to maintain the index data when updating the data, so the index is a double-edged sword, not the more the better. For example, there is no need to build an index on a small table with tens to hundreds of rows, frequent insertions and updates, and less need to build an index with less reading.

Question 2. Which columns need to be indexed to speed up the query?

Select user,host from mysql.user wherepassword=... .., the index must be created on the conditional column after where, not on the column of selected data after select. In addition, we should try our best to select the columns on the large table with more unique values to build the index. for example, the gender columns of boys and girls have fewer unique values and are not suitable for indexing.

View the number of unique values

Select count (distinct user) from mysql.user

The only value is the same number. For example, if you query user, the same user is the only value.

Mysql > select count (distinct user) from mysql.user;+--+ | count (distinct user) | +-+ | 7 | +-+ 1 row in set (0.07 sec)

List of users, which is explained according to the upload

Mysql > select user,host from mysql.user +-+-- + | user | host | +-+-+ | cyh | 10.1.1.% | | root | 127.0.0.1 | | bbs | 172.16.1.1 Accord 255.255.255.0 | | wordpress | 192.168.1% | oldboy | 192.168.1% with 255.255.255.0 | | abc | localhost | | blog | localhost | | oldboy | localhost | root | localhost | +-| -+ 9 rows in set (0.00 sec)

Summary of the basics of creating an index:

■ index book-like catalogs, which will speed up the query of data.

■ wants to create an index on the column (field) of the table

■ indexes speed up queries, but also affect the speed of updates, which are required to maintain index data

The ■ index column is not as many as possible. Create an index on the conditional column after the frequently queried table statement where

■ small tables or columns with many duplicate values can not be indexed, but indexes should be created on large tables and conditions with few duplicate values.

■ multiple column federated indexes have prefix validity feature.

■ when the first N characters of a field are nearly unique, you can create an index on the first N characters of the field.

■ indexes differ from the way they work. There are primary keys, unique indexes, and general indexes.

■ index types include BTREE (default) and hash (suitable for caching (in-memory database) and so on.

The difference between primary key index and unique index

(1) A unique index is automatically established for primary keys / unique constraint oracle/sql server/mysql, etc.

(2) the primary key does not necessarily contain only one field, so it is necessary if you create a unique index on one of the fields of the primary key.

(3) the main key can be used as external key, but the unique index is not allowed.

(4) the main key cannot be empty, and the unique index can

(5) the main key is also a combination of multiple fields.

(6) the primary key differs from the unique index in that:

a. Have not null attribute

b. There can be only one table per table.

Insert data into the table

● command syntax

Insert into [([..])] values (value 1) [, (value n)]

● sets up a simple test table test

CREATE TABLE `test` (`id` int (4) NOT NULL AUTO_INCREMENT, `name` char (20) NOT NULL, PRIMARY KEY (`id`)); mysql > desc test +-+ | Field | Type | Null | Key | Default | Extra | +-+ -+ | id | int (4) | NO | PRI | NULL | auto_increment | | name | char (20) | NO | | NULL | | +-+-+

● insertion value

Insert into test (id,name) values (1); insert into test (id,name) values (1)

● query

Mysql > select * from test;+----+-+ | id | name | +-+-+ | 1 | oldboy | +-+-+ 1 row in set (0.00 sec)

The second method of ●:

The id column can grow by itself without being specified.

Insert into test (name) values ('oldgirl'); mysql > select * from test;+----+-+ | id | name | +-- +-+ | 1 | oldboy | | 2 | oldgirl | +-+-- + 2 rows in set (0.00 sec)

The third method of ● can be inserted sequentially without specifying columns.

Mysql > insert into test values; Query OK, 1 row affected (0.00 sec) mysql > select * from test;+----+-+ | id | name | +-- +-+ | 1 | oldboy | | 2 | oldgirl | | 3 | inca | +-- + 3 rows in set (0.00 sec)

● bulk insert:

Mysql > insert into test values; Query OK, 2 rows affected (0.00 sec) Records: 2Duplicates: 0 Warnings: 0mysql > select * from test;+----+-+ | id | name | +-- +-+ | 1 | oldboy | 2 | oldgirl | 3 | inca | 4 | zuma | 5 | kaka | +-+ + 5 rows in set (0.00 sec)

Clear all values

Mysql > truncate table test;Query OK, 0 rows affected (0.00 sec) mysql > select * from test;Empty set (0.00 sec)

One command to solve all the above configurations

Mysql > insert into test values (1 sec sec'), (2) Warnings: 0mysql > select * from test'), (3) Warnings: 0mysql > select * from test'), (4) Zuma'), (5 sec) Records: 5Duplicates: 0 Warnings: +-+-+ | id | name | +-+-+ | 1 | oldboy | | 2 | oldgirl | | 3 | inca | | 4 | zuma | | 5 | kaka | +-+-+ 5 rows in set (0.00 sec) query data

Query all data rows of a table

1. Command syntax:

Selectfrin

Where

Among them, select,from,where cannot be changed at will. It is a keyword and supports upper and lower case.

two。 Columns: viewing all data in table test

A. Query after entering the specified library

If you do not enter the library, you can use the

Mysql > select * from oldboy.test;mysql > select user,host,password from mysql.user +-+ | user | host | password | +-+- -+-- + | root | localhost | * 7495041D24E489A0096DCFA036B166446FDDD992 | | root | 127.0.0.1 | * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | wordpress | 192.168.1% | * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | cyh | 10. 1.1.1% | * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | bbs | 172.16.1.1 plus 255.255.255.0 | * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | blog | localhost | * 7495041D24E489A0096DCFA036B166446FDDD992 | | oldboy | localhost | * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | oldboy | 192.168.1% scar 255.255.255.0 | * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | abc | localhost | * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +-+- -+

Query only the first two lines

Mysql > select * from test limit 2 + | id | name | +-+-+ | 1 | oldboy | | 2 | oldgirl | +-+-- + 2 rows in set (0.00 sec)

Start with the second, find 2.

Mysql > select * from test limit 1 id | id | name | +-+-- + | 2 | oldgirl | | 3 | inca | +-+-- + 2 rows in set (0.00 sec)

Query according to conditions

Mysql > select * from test where id=1;+----+-+ | id | name | +-+-+ | 1 | oldboy | +-+-+ 1 row in set (0.00 sec)

Hint: mysql > select * from test where name='abcdocker'

Select * from test where name='abcdocker';+----+-+ | id | name | +-+-+ | 1 | abcdocker | +-+-+ 1 row in set (0.00 sec)

Tip: the value of the condition to find a string type should be in single quotation marks, and the numeric value should not be in quotation marks.

Query multiple conditions

Mysql > select * from test where name='oldgirl' and id=2;+----+-+ | id | name | +-+-+ | 2 | oldgirl | +-+-+ 1 row in set (0.00 sec)

Range query

Mysql > select * from test where id > 2There is the same as mysql > select * from test; > 2 and id select * from test where id > 2 or id select * from test; > 2 or id select * from test; > 2 or id select * from test; = mysql > select * from test order by id asc; in reverse order mysql > select * from test order by id desc;

Example: create a student table

Drop table student;create table student (Sno int (10) NOT NULL COMMENT 'student number', Sname varchar (16) NOT NULL COMMENT 'name', Ssex char (2) NOT NULL COMMENT 'gender', Sage tinyint (2) NOT NULL default'0' COMMENT 'student age', Sdept varchar (16) default NULL COMMENT 'student's department', PRIMARY KEY (Sno), key index_Sname (Sname) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

Insert content

Create table course (Cno int (10) NOT NULL COMMENT 'course number', Cname varchar (64) NOT NULL COMMENT 'course name', Ccredit tinyint (2) NOT NULL COMMENT 'credits', PRIMARY KEY (Cno)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

Insert content

CREATE TABLE `SC` (SCid int (12) NOT NULL auto_increment COMMENT 'primary key', `Cno` int (10) NOT NULL COMMENT 'course number', `Sno` int (10) NOT NULL COMMENT 'student number', `Grade`tinyint (2) NOT NULL COMMENT 'student grade', PRIMARY KEY (`SCid`) ENGINE=InnoDB DEFAULT CHARSET=latin1

Insert content

INSERT INTO course values (1001); INSERT INTO course values (1002); INSERT INTO course values (1003); INSERT INTO course values (1004); INSERT INTO course values (1005)

Insert content

INSERT INTO SC (Sno,Cno,Grade) values; INSERT INTO SC (Sno,Cno,Grade) values (0002); INSERT INTO SC (Sno,Cno,Grade) values (0002) values (0002); INSERT INTO SC (Sno,Cno,Grade) values (0002) INSERT INTO SC (Sno,Cno,Grade) values; INSERT INTO SC (Sno,Cno,Grade) values (0004); INSERT INTO SC (Sno,Cno,Grade) values (0004) INSERT INTO SC (Sno,Cno,Grade) values (0005 Sno,Cno,Grade 1001); INSERT INTO SC (Sno,Cno,Grade) values (0005) 1002); INSERT INTO SC (Sno,Cno,Grade) values (0005) 1003); INSERT INTO SC (Sno,Cno,Grade) values (0005) 1004)

Check, view table format or table contents

Mysql > desc SC; or select * from SC; optimization

Sql statement optimization

Explain to see if there is an indexing statement mysql > explain select * from test where name='oldboy'; adding explain before a statement is equivalent to simulating a query

Create an index

Mysql > create index index_name on test (name) | | name | char (20) | NO | MUL | NULL | mysql > explain select * from test where name='oldboy'\ Graph * 1. Row * * id: 1 select_type: SIMPLE table: test | Type: ref possible_keys: index_name key: index_name key_len: 60 ref: const rows: 1 query rows Indicates that only one line of Extra: Using where has been queried. Using index 1 row in set (0.00 sec)

Original picture:

Mysql > explain select * from test where name='oldboy'\ G * * 1. Row * * id: 1 select_type: SIMPLE table: test type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 5 Extra: Using where 1 row in set (0.00 sec)

Help

Mysql > help explain contains the use of EXPLAIN to optimize the basic flow of SQL statements (select statements) using the explain command

■ slow query SQL syntax method

Enter mysql > SHOW FULL PROCESSLIST; every 2 seconds. If it appears twice, it means slow query mysql > SHOW FULL PROCESSLIST.

■ Analysis slow query Log

Configure parameter record slow query statement log_query_time = 2log_queries_not_using_indexeslog-slow-queries = / data/3306/slow.log

■ indexes the conditional columns that need to be indexed

Large tables cannot be indexed during peak hours, with 3 million records (if the access is already slow, you can create it directly)

Mysqlala, a tool for ■ to analyze slow query SQL (send email every morning)

Cut the slow query log and send it to everyone after re-analysis. if the concurrency is too large, you can repeat it by the hour.

1) mv and then flush process 2) cp copy, and then use > to clear. 3) scheduled task mv / data/3306/slow.log / opt/$ (date +% F) _ slow.logmysqladmin-uroot-poldboy-S / data/3306/mysql.sock flush-logs

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 225

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report