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

This article mainly gives you a brief introduction to the common use and basic knowledge of the operation of MySQL. You can check the relevant professional terms on the Internet or find some related books to supplement them. I will not dabble here and go straight to the topic. I hope I can bring you some practical help.

1. Start and shut down the mysql server:

Service mysql startservice mysql stop

2. Restart the MySQL service:

Service mysql restart

3. Confirm whether the startup is successful. If the mysql node is in the status of LISTEN, the startup is successful:

Sudo netstat-tap | grep mysql

4. Enter the mysql shell interface:

Mysql-u root-p

5. Connect MYSQL:

Format: mysql-h host address-u user name-p user password (note: U and root can not add spaces, others are the same)

1. Connect to the MYSQL on this machine

To find the installation directory of mysql, you can generally type the command mysql-uroot-p directly, enter and prompt you to enter your password. If you have just installed MYSQL, the superuser root does not have a password, so you can enter MYSQL directly by entering enter.

2. Connect to the MYSQL on the remote host

Suppose the IP of the remote host is 10.0.0.1, the user name is root, and the password is 123. Type the following command:

Mysql-h20.0.0.1-uroot-p123

6. Exit the MYSQL command:

Exit (enter)

7. Query the processes being executed by mysql:

Show processlist

8. View users:

Use mysql;select * from user

9. Create new users:

CREATE USER 'user_name'@'host' IDENTIFIED BY' password'

User_name: the name of the user to be created.

Host: indicates which machine the newly created user is allowed to log in from. Enter 'localhost'' if only local login is allowed, or'%'if remote login is allowed

Password: the login database password of the newly created user. If you don't have a password, you don't have to write it.

Example:

CREATE USER 'aaa'@'localhost' IDENTIFED BY' 123456password / indicates a new user created, named aaa, and the new user password is 123456. Only local login is allowed to CREATE USER 'bbb'@'%' IDENTIFED BY' 123456password. The user password is 123456. You can remotely log in to CREATE USER 'ccc'@'%', the server where mysql is located. / / indicates that the newly created user ccc has no password and can log in to the MySQL server remotely from other computers

10. Authorized users:

GRANT privileges ON databasename.tablename TO 'username'@'host'

Privileges: indicates what authority to grant, such as select, insert, delete,update, etc. Enter ALL if all powers are to be granted

Databasename.tablename: indicates which table in which library the user's permissions can be used. If you want the user's permissions to have a special effect on all tables in all databases, enter *. *, * is a wildcard that indicates all.

'username'@'host': indicates the user to whom it is authorized.

For example:

Grant all on *. * to 'test'@'localhost'; # authorizes test users so that test users can exercise all rights to all tables in all libraries. GRANT select,insert ON zje.zje TO' aaa'@'%';// authorizes users to aaa, and aaa implements insert and select to zje tables in the zje library.

Note:

Users authorized with the above command cannot authorize other users. If you want this user to be able to authorize other users, you should add WITH GRANT OPTION after it.

Such as: GRANT ALL ON *. * TO 'aaa'@'%' WITH GRANT OPTION

Correlation

11. Restrict ip login:

For example, setting mysql only 172.29.8.72 and 192.168.3.39 can be connected

GRANT ALL ON *. * TO 'username'@'172.29.8.72' IDENTIFIED BY' password' WITH GRANT OPTION;GRANT ALL ON *. * TO 'username'@'192.168.3.39' IDENTIFIED BY' password' WITH GRANT OPTION;flush privileges

12. Delete a user:

Command: DROP USER 'user_name'@'host'

Example:

Drop user 'test'@'%'; # Delete user test

13. Display the datasheet mode:

Use database name

Show columns from table name

14. Mysql data type:

MySQL supports many types, which can be roughly divided into three types: numeric, date / time, and string (character) types.

MySQL supports all standard SQL numeric data types. As an extension of the SQL standard, MySQL also supports integer types TINYINT, MEDIUMINT, and BIGINT

15. Insert data:

INSERT INTO table_name (field1, field2,...fieldN) VALUES (value1, value2,...valueN)

If the data is character type, you must use single or double quotation marks, such as "value".

16. Query data:

SELECT column_name,column_name FROM table_name [WHERE Clause] [LIMIT N] [OFFSET M]

You can use one or more tables in the query statement, split the tables with commas (,), and use the where statement to set the query conditions.

The SELECT command can read one or more records.

You can use an asterisk (*) instead of other fields, and the SELECT statement returns all the field data of the table

You can use the WHERE statement to include any condition.

You can use the LIMIT property to set the number of records returned.

You can specify the data offset to start the query with the SELECT statement through OFFSET. The offset is 0 by default.

17. Limit and offset usage

Paging in mysql is usually implemented with limit.

1 、 select* from article LIMIT 1,3

2. Select * from article LIMIT 3 OFFSET 1

Both of the above two writing methods show that three pieces of data are taken from 2, 3, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 3, 4, 4, 4, 3, 4, 3, 4, 4, 4, 3, 4, 4, 3, 4, 3, 4, 4, 4, 4, 3, 4, 3, 4, 3, 4, 4, 4, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 4, 3, 4, 4, 3, 4, 4, 4, 4, 4, 4, 4,

When limit is followed by two parameters, the first number represents the number to be skipped, and the last bit indicates the number to be fetched, for example

Select* from article LIMIT 1 data 3 is to skip one piece of data, start with the second piece of data, and take 3 pieces of data, that is, take 2 pieces of data, 3 pieces of data, 4 pieces of data

When limit is followed by a parameter, that parameter represents the amount of data to be fetched

For example, select* from article LIMIT 3 means to take the first three pieces of data directly, similar to the top syntax in sqlserver.

When limit and offset are used in combination, only one parameter can be followed by limit, indicating the quantity to be taken, and offset indicating the number to be skipped.

For example, select * from article LIMIT 3 OFFSET 1 means to skip one piece of data, start with the second piece of data, and take three pieces of data, that is, take two pieces of data, three pieces of data, four pieces of data.

18. Update data:

UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]

You can update one or more fields at once.

You can specify any condition in the WHERE clause.

You can update the data at the same time in a single table.

19. Fuzzy query:

Select c field1,field2, … FieldNfrom table_namewhere field1 like condition1 [and[or]] field2='somevalue'

Classification of wildcards:

% percent wildcard: indicates that any character appears any number of times (can be 0 times).

_ underscore wildcard: indicates that you can only match a single character, neither more nor less, that is, one character.

20. Sort

SELECT field1, field2,...fieldN FROM table_name1, table_name2...ORDER BY field1 [ASC [DESC] [default ASC]], [field2...] [ASC [DESC] [default ASC]]

You can add WHERE... LIKE clause to set the condition

21. Group

The GROUP BY statement groups the result set according to one or more columns. We can use COUNT, SUM, AVG and other functions on the grouped columns.

SELECT column_name, function (column_name) FROM table_nameWHERE column_name operator valueGROUP BY column_name

Example:

SELECT name, COUNT (*) FROM employee_tbl GROUP BY name

22. Null value processing

IS NULL, IS NOT NULL, and operators are used to process NULL in MySQL.

IS NULL: this operator returns true when the value of the column is NULL. IS NOT NULL: the operator returns true when the value of the column is not NULL. The comparison operator (unlike the = operator) returns true when the two values compared are NULL.

23 、 in

In is often used in where expressions to query data within a range.

Usage: select * from table where field in (value1,value2,value3, …)

Example:

Query all data in the book table with id 2 and 4:

Select * from book where id in (2pr 4)

24 、 not in

Not in has the opposite effect as in, and the usage and examples are as follows:

Usage: select * from where field not in (value1,value2,value3, …)

25 、 exists

The query shown in this example finds the title of a book published by any publisher in a city that begins with the letter B.

SELECT titleFROM titlesWHERE EXISTS (SELECT * FROM publishers WHERE pub_id = titles.pub_id AND city LIKE'B%')

Use IN:

SELECT titleFROM titlesWHERE pub_id IN (SELECT pub_id FROM publishers WHERE city LIKE'B%')

26. Import data:

Mysql command import

Use the mysql command to import the syntax in the format:

Mysql-u username-p password

< 要导入的数据库数据(runoob.sql) 实例： # mysql -uroot -p123456 < runoob.sql 以上命令将将备份的整个数据库 runoob.sql 导入。 source 命令导入 source 命令导入数据库需要先登录到数库终端： mysql>

Create database abc; # create database mysql > use abc; # use the created database mysql > set names utf8; # set encoding mysql > source / home/abc/abc.sql # import the backup database

Import data using LOAD DATA

LOAD DATA INFILE statements are provided in MySQL to insert data. The following example reads the file dump.txt from the current directory and inserts the data from that file into the mytbl table of the current database.

Mysql > LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl

If you specify the LOCAL keyword, the file is read by path from the customer host. If not specified, the file reads the file by path on the server.

You can explicitly indicate the delimiters and end-of-line tags for column values in the LOAD DATA statement, but the default tags are locators and newlines.

Import data using mysqlimport

The mysqlimport client provides a command line interface for LOAD DATA INFILEQL statements. Most of the options for mysqlimport correspond directly to the LOAD DATA INFILE clause.

To import data from the file dump.txt into the mytbl data Table, you can use the following command:

$mysqlimport-u root-p-- local mytbl dump.txtpassword *

27. Export data:

Use SELECT... INTO OUTFILE statement to simply export data to a text file.

# Export datasheet runoob_tbl data to / tmp/runoob.txt file: mysql > SELECT * FROM runoob_tbl-> INTO OUTFILE'/ tmp/runoob.txt'; # set the specified format of data output through command options. The following example is the export CSV format: mysql > SELECT * FROM passwd INTO OUTFILE'/ tmp/runoob.txt'-> FIELDS TERMINATED BY', 'ENCLOSED BY' "- > LINES TERMINATED BY'\ r\ n' # generate a file with values separated by commas. This format can be used by many programs. SELECT a dagger bjorn axib INTO OUTFILE'/ tmp/result.text' FIELDS TERMINATED BY', 'OPTIONALLY ENCLOSED BY' "'LINES TERMINATED BY'\ n 'FROM test_table

SELECT... The INTO OUTFILE statement has the following properties:

LOAD DATA INFILE is SELECT... INTO

Reverse operation of OUTFILE, SELECT syntax. To write data from a database to a file, use SELECT. INTO

OUTFILE, to read the file back to the database, use LOAD DATA INFILE.

SELECT... INTO OUTFILE

'SELECT in the form of file_name' can write the selected lines to a file. The file is created on the server host, so you must have FILE permissions to use this syntax.

The output cannot be an existing file. Prevent file data from being tampered with.

You need to have an account that logs in to the server to retrieve files. Otherwise, SELECT... INTO OUTFILE won't do anything.

Export tables as raw data

Mysqldump is the utility that mysql uses to transfer the database. It mainly produces a SQL script that contains commands such as CREATE TABLE INSERT that are necessary to recreate the database from scratch.

Exporting data using mysqldump requires the-- tab option to specify the directory specified by the export file, which must be writable.

The following example exports the datasheet runoob_tbl to the / tmp directory:

$mysqldump-u root-p-- no-create-info-tab=/tmp RUNOOB runoob_tblpassword *

Export data in SQL format

Export the data in SQL format to the specified file, as follows:

$mysqldump-u root-p RUNOOB runoob_tbl > dump.txtpassword *

If you need to copy the data to another MySQL server, you can specify the database name and table in the mysqldump command.

Backup the data to the dump.txt file by executing the following command on the source host:

$mysqldump-u root-p database_name table_name > dump.txtpassword *

If you fully back up the database, you do not need to use a specific table name.

If you need to import the backed-up database into the MySQL server, you can use the following command to verify that the database has been created:

$mysql-u root-p database_name < dump.txtpassword *

You can also import the exported data directly to a remote server using the following command, but make sure that the two servers are connected and accessible to each other:

$mysqldump-u root-p database_name | mysql-h other-host.com database_name

Pipes are used in the above command to import the exported data to the specified remote host.

28. MySQL transaction

MySQL transactions are mainly used to deal with data with large amount of operations and high complexity. For example, in the personnel management system, if you delete a person, you need to delete not only the basic information of the person, but also the information related to that person, such as mailboxes, articles, etc., so that these database operation statements constitute a transaction!

In MySQL, only databases or tables that use the Innodb database engine support transactions. Transactions can be used to maintain the integrity of the database, ensuring that batches of SQL statements are either executed or not executed. Transactions are used to manage insert,update,delete statements

Generally speaking, a transaction must satisfy four conditions (ACID): atomicity (Atomicity, or indivisibility), consistency (Consistency), isolation (Isolation, also known as independence), and persistence (Durability).

There are two main methods of MYSQL transaction processing:

1. Implement it with BEGIN, ROLLBACK and COMMIT.

BEGIN starts a transaction ROLLBACK transaction rolls back COMMIT transaction confirmation

2. Directly use SET to change the automatic submission mode of MySQL:

SET AUTOCOMMIT=0 forbids autocommit SET AUTOCOMMIT=1 turns on autocommit

29. Character set settings:

Set the MySQL character set to utf8 under Ubuntu

1.mysql profile address

/ etc/mysql/my.cnf

two。 Add the following code below [mysqld]

[mysqld] init_connect='SET collation_connection = utf8_unicode_ci'init_connect='SET NAMES utf8'character-set-server=utf8collation-server=utf8_unicode_ciskip-character-set-client-handshake

3. Restart the mysql service

Sudo service mysql restart

4. Detect whether the character set is updated to utf8.

Enter mysql,mysql-u root-p and enter show variables like'% character%' to view the character set

+-- +-- + | Variable_name | Value | +-- +-- + | character _ set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | character_set_results | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | / usr/share/mysql/charsets/ | +-+-+

Note: in the database that has been established before modifying the character set, the character_set_database value will not change, and inserting Chinese data into the database will still show garbled, so it is best to change the character set to utf8 after installing MySQL, otherwise subsequent modifications will be more troublesome.

Character set modification:

Show character set; # View the character set, character order and the maximum byte length of the character set supported by the current MySQL service instance

As shown below:

Show variables like 'character%';# to view the character set used by the current MySQL session

The result is shown in the figure:

Character_set_client: the character set used by the client source data

Character_set_connection: the character set of a data communication link in which data is encoded when a MySQL client sends a request to the server

Character_set_database: database character set

Character_set_filesystem: the character set of the MySQL server file system, which is a fixed binary.

Character_set_results: the character set of the result set in which the MySQL server encodes the execution result when it returns to the MySQL client

Character_set_server: internal operation character set (MySQL service instance character set) character_set_system: character set of metadata (field name, table name, database name, etc.) defaults to utf8

Modify the character set:

Set character_set_results=gbk

As shown in the figure:

30. Time synchronization:

Tzselect set time zone command, according to the option to select China's Beijing time.

Execute the following command:

Vi .bash _ profile

Add these two lines at the end:

TZ='Asia/Shanghai'export TZ

Then run the command to refresh the configuration:

Source .bash _ profile

Run the date command again to find that the time zone has been successfully modified:

Hadoop@Master:~$ date-RTue, 30 Jul 2019 19:42:41 + 0800

After the linux time zone is reset, it is found that the data inserted by mysql is still the time of the original time zone. Here is how to reset the mysql time zone:

Go to the mysql console and run the following instructions to check the mysql time.

Select CURTIME ()

After the query, it is found that the query time is not the same as that queried by the date command. The following changes begin:

Run the temporary resolution command:

SET time_zone ='+ 8VR 00'

Refresh the configuration:

Flush privileges

Modify the configuration file to make it permanent after the next restart of the mysql service

Vi / etc/my.cnf

Add the following configuration:

[mysqld] default_time_zone ='+ 8RV 00'

31. Grep command:

1. Action

Grep command is a powerful text search tool in Linux system. It can use regular expressions to search text and print matching lines. The full name of grep is Global Regular Expression Print, which represents the global regular expression version, and its permission is for all users.

two。 Format

Grep [options]

Simple example:

$grep 'test' d * # shows all lines that start with d that contain test. $grep 'test' aa bb cc # shows the lines that match test in the aa,bb,cc file.

32. Awk command:

Awk is a powerful text analysis tool, compared with the search of grep and the editor of sed, awk is particularly powerful in analyzing data and generating reports. To put it simply, awk is to read the file line by line, slice each line with a space as the default separator, and then analyze the cut part.

Usage: awk'{pattern + action}'{filenames}

Operation of MySQL common and basic knowledge will first tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.