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

This article mainly introduces you to quickly master MySQL basic tutorial methods, the contents of the articles are carefully selected and edited by the author, with a certain pertinence, for everyone's reference significance is still relatively great, the following with the author to understand the rapid mastery of MySQL basic tutorial methods bar.

1 minute installation

Part1: write at the front

There are three ways to install MySQL:

① rpm package installation

② binary package installation

③ source code installation

Here we recommend binary package installation, which is fine both in terms of installation speed and for production library installation environment. Now the production library generally uses MySQL5.6, and the test library uses MySQL5.7.

MySQL5.6 installation, see here.

Http://suifu.blog.51cto.com/9167728/1846671

MySQL5.7 installation, see here.

Http://suifu.blog.51cto.com/9167728/1855415

8-minute database operation

Part1: login

The login method for MySQL is:

-u is the user name and-p is the password. If you use the installation script for this article, the default password is MANAGER.

[root@HE3] # mysql-uroot-pMANAGER

Mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 11

Server version: 5.7.16-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql >

Part2: table basic operation

① to see which libraries are in the database

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | he1 |

| | he3 |

| | maxscale |

| | mysql |

| | performance_schema |

| | sys |

+-+

7 rows in set (0.00 sec)

② addition, deletion, modification and search

Like Excel, the syntax for adding, deleting, modifying and querying in the database is as follows:

Check:

First select the appropriate library

Mysql > use maxscale

Database changed

Select * from table name; it means to query all the contents of this table.

Select column name, column name from table name; this is the column content you want to see when querying this table.

Mysql > select a dint b from helei

+-+ +

| | a | b | |

+-+ +

| | HE3 | a |

| | write | b |

| | Test | c | |

| | Yu Hao | d |

| | he Lei | e | |

+-+ +

6 rows in set (0.00 sec)

Increase:

Insert into table name values ('what you want to insert'); insert a record into the table

Mysql > insert into helei values ('insert','f')

Query OK, 1 row affected (0.01sec)

Mysql > select a dint b from helei

+-+ +

| | a | b | |

+-+ +

| | HE3 | a |

| | write | b |

| | Test | c | |

| | Yu Hao | d |

| | he Lei | e | |

| | insert | f | |

+-+ +

6 rows in set (0.00 sec)

My watch here is called helei.

Delete:

Delete all records in the helei table where column b is f delete from helei where

Mysql > delete from helei where breadcrumb

Query OK, 1 row affected (0.01sec)

Mysql > select * from helei

+-+ +

| | a | b | |

+-+ +

| | HE3 | a |

| | write | b |

| | Test | c | |

| | Yu Hao | d |

| | he Lei | e | |

+-+ +

5 rows in set (0.00 sec)

You can see that the entire line of column b in f is deleted.

Change:

Update table name set column name = 'change to desired content' where qualification.

Mysql > update helei set baked 'change to' where averse'he Lei'

Query OK, 1 row affected (0.01sec)

Rows matched: 1 Changed: 1 Warnings: 0

Mysql > select * from helei

+-+ +

| | a | b | |

+-+ +

| | HE3 | a |

| | write | b |

| | Test | c | |

| | Yu Hao | d |

| | he Lei | change |

+-+ +

5 rows in set (0.00 sec)

③ table-level operation

Create a tabl

Create table t. Here we use the production library as an example. The id column increments the primary key, and log is of varchar type, which can store 30 characters.

Mysql > CREATE TABLE `t` (

-> `id` int UNSIGNED NOT NULL AUTO_INCREMENT

-> `log`varchar (30) NOT NULL DEFAULT''

-> PRIMARY KEY (`id`)

->)

->

Query OK, 0 rows affected (0.01 sec)

Delete tabl

Delete table t, delete the whole table

Mysql > drop table t

Query OK, 0 rows affected (0.02 sec)

Part3: library basic operation

Create a library

Mysql > CREATE DATABASE helei DEFAULT CHARACTER SET utf8 COLLATE utf8_bin

Query OK, 1 row affected (0.00 sec)

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | he1 |

| | he3 |

| | helei |

| | maxscale |

| | mysql |

| | performance_schema |

| | sys |

+-+

8 rows in set (0.00 sec)

Delete Library

Delete the library named helei, note that this action deletes all tables in the helei library

Mysql > drop database helei

Query OK, 0 rows affected (0.00 sec)

1 minute system-level operation

Part1: start and stop the database

[root@HE3 ~] # / etc/init.d/mysqld status

SUCCESS! MySQL running (3173)

[root@HE3 ~] # / etc/init.d/mysqld stop

Shutting down MySQL.... SUCCESS!

[root@HE3 ~] # / etc/init.d/mysqld start

Starting MySQL.. SUCCESS!

Appendix

Part1: commonly used SQL

Create and authorize users

CREATE USER 'helei'@'%' IDENTIFIED BY' MANAGER'

GRANT SELECT,insert,update,delete ON *. * TO 'helei'@'%'

Create the database:

CREATE DATABASE www CHARACTER SET utf8 COLLATE utf8_bin

Password change:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD (' MANAGER')

Count which ip connections

Mysql > select substring_index (host,':', 1) from information_schema.processlist

Count the number of connections per IP:

Mysql > select substring_index (host, ":", 1) ip, count (*) from information_schema.processlist group by ip

View the number of ip connections to the library level:

Mysql > select db, substring_index (host, ":, 1) ip, count (*) from information_schema.processlist group by db, ip

View the current number of connections

Mysql > show status like 'Threads%'

Rough statistics on the size of each table

Mysql > select table_schema,table_name,table_rows from tables order by table_rows desc

To know the size of each database, the steps are as follows:

1. Enter the information_schema database (store the information of other databases)

Use information_schema

2. Query the size of all data:

Select concat (round (sum (data_length/1024/1024), 2), 'MB') as data from tables

3. View the size of the specified database:

For example, check the size of the database home

Select concat (round (sum (data_length/1024/1024), 2), 'MB') as data from tables where table_schema='home'

4. View the size of a table in the specified database

For example, check the size of the members table in the database home

Select concat (round (sum (data_length/1024/1024), 2), 'MB') as data from tables where table_schema='home' and table_name='members'

Unable to update or delete data. You can avoid this by setting the FOREIGN_KEY_CHECKS variable.

SET FOREIGN_KEY_CHECKS = 0

Settings after deletion is completed

SET FOREIGN_KEY_CHECKS = 1

Other:

Turn off uniqueness check

Set unique_checks=0

Set unique_checks=1

Change character set

ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8

Add primary key

Alter table `helei`add column `id` int (10) not null auto_increment primary key comment 'primary key' first; can lock the table. Test the time in the test library first. If the time is long, try using the pt tool.

Rename table

Alter table helei rename to helei_old

Lock the table (invalid if the user exits)

Flush tables with read lock;unlock table

Lock a table

Lock tables helei read

Find out that id is odd and even

Select * from t where id & 1

Select * from t where id= (id > > 1)

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.