Notes on mysql special commands

Posted by Michael001 on Thu, 05 Mar 2020 07:45:26 +0100

1, Connect to database

Do not use database management software (such as Navicat, etc.), connect mysql server through dos, and then operate database

General format for connecting to database: mysql -P port number - h mysql host name or ip address - u user name - p
Explanation: (- P upper case P represents port, lower case P represents password, h represents host name or ip,u represents user) "

 mysql -P 3306 -h -u root -p

1. Local connection
If the command line is the local machine where mysql is located, and the default port 3306 is used, the statement can be simplified as follows:

mysql -u root -p

2. Remote connection
Note: when using remote connection, the current ip address of the connecting user and the user should be the allowed user and ip address in the remote database, otherwise the connection is not allowed

mysql -P 3306 -h -u root -p

2, Operation database

After successfully logging in mysql database with user name and password, the user can operate the database within the user's authority
db_test is a data name created by myself. When operating data, each statement uses; or \ g to mark the end

1. View all databases

show databases;

2. Create database

create database db_test;

3. Use database

use db_test;

4. Display all tables in the database

show tables;

5. View table structure

show columns from customers; 

Or use the shortcut:

DESCRIBE customers;

3, About importing and exporting command line schema database files

In command line mode, import and export sql files and enter CMD

Export database file
Include exporting database to specified table
1. Export the structure and data of db_test

mysqldump -h localhost -u root -p db_test > D:\db_test.sql

2. Export the structure of db_test (with - d parameter):

mysqldump -h localhost -u root -p db_test -d > D:\db_test_str.sql

3. Export the structure and data of the customers table in the db_test database:

mysqldump -h localhost -u root -p db_test customers > D:\customers.sql

4. Export the structure of customers table in db_test (with - d parameter):

mysqldump -h localhost -u root -p db_test -d > D:\customers_str.sql

5. Import database file
Import database file DB? Test.sql to DB? Test

mysql -h localhost -u root -p db_test < D:\db_test.sql

4, Other common statements

1. Used to display a wide range of server status information


2. MySQL statements used to create specific databases or tables


3. Used to display security rights granted to users (all users or specific users)


4. Used to display server error or warning messages


5. New field

ALTER TABLE s_activity_plan ADD sendMail INT ( 11 ) NOT NULL COMMENT 'Notes';

6. Mobile phone number regular verification

SELECT * FROM shgl_user_account WHERE mobile not REGEXP "^[1][3456789][0-9]{9}$";

7. Check cell phone number duplicate data

			count(seller_mobile) num
	) a
	a.num > 1

8. Modify comments for fields

alter table table name modify column field name field type comment 'modified field comment';

9. Add union unique index

ALTER TABLE 'table name' ADD UNIQUE INDEX 'index name' (` field 1 ', ` field 2');

10. Delete index

drop index index name on table name;

11. Query by the last number of the specified field
For example, query the data with the last bit of id 1:

SELECT * FROM Table name WHERE RIGHT(id,1)=1

Query data with the last bit of id 0:

SELECT * FROM Table name WHERE RIGHT(id,1)=0

The principle is to use the automatic conversion of number and string to obtain the end value of id.

12. String splicing function


13. Add a primary key to an existing table


14. Execute sql script
There are two ways:
The first method:
At the command line (not connected to the database), enter

 mysql -h localhost -u root -p123456 < F:\test.sql 

(note that the path does not need to be quoted) enter
The second method:
At the command line (the database is connected, and the prompt is MySQL >), enter

 source F:\test.sql 

(note that the path is not quoted) or

 \. F:\test.sql 

(note that the path does not need to be quoted) enter

15. Modify table name

alter table test rename test1;

16. Query database running thread

show processlist

17. Keep two decimal places

Round (value, keep the number of decimals) for example, round(4.555,1) result: 4.6

Function returns the number x rounded to the d decimal place. If the value of d is 0, the result has no decimal point or fraction.
If d is set to a negative number, all lower values after the start of d from the left of (Zeroing) x decimal point are truncated.

SELECT TRUNCATE(100.3465,2),TRUNCATE(100,2),TRUNCATE(0.6,2),TRUNCATE(114.6,-1);

Results: 100.34100, 0.6110

18. mysql null conversion

1. Return 0 if empty

select ifnull(null,0)

2. Return 0 if NULL, otherwise 1

select if(isnull(col),0,1) as col.

19. Anonymous table


20. Time to string

date_format(create_time, '%Y-%m-%d %H:%I:%S')

21. Time to time stamp

select unix_timestamp(now());

22. String transfer time

select str_to_date('2016-01-02', '%Y-%m-%d %H');

23. String to timestamp

select unix_timestamp('2016-01-02');

24. Time stamp

select from_unixtime(1451997924);

25. Time stamp to string

select from_unixtime(1451997924,'%Y-%d');

MySQL date format value range.

Value implication
 Seconds% s,% s two digit seconds (00,01,..., 59)
Score% I, score% I in two digit form (00,01,..., 59)
Hour% H 24 hour system, two digit form hour (00,01,..., 23)
%h 12 hour system, two digit form hour (00,01,..., 12)
%k 24-hour system, number of hours (0,1,..., 23)
%l 12 hour system, number form hour (0,1,..., 12)
%T 24-hour system, time form (HH:mm:ss)
%r 12 hour system, time form (hh:mm:ss AM or PM)
%p AM am or PM PM PM
 Week% W name of each day of the week (Sunday, Monday,..., Saturday)
%a. abbreviation of the name of each day of the week (sun, Mon,..., SAT)
%w identifies the week in digital form (0 = Sunday, 1 = Monday,..., 6 = Saturday)
%U number represents the number of weeks, Sunday is the first day of the week
 %u number represents the number of weeks, Monday is the first day of the week
 Day% d: two digits represent the number of days in the month (01, 02,..., 31)
%e. number of days in the month (1,2,..., 31)
%D. the English suffix indicates the number of days in the month (1st,2nd,3rd...)
%j. number of days in the year in three figures (001002,..., 366)
Month% M English month name (January, February,..., December)
%b. abbreviated month name (Jan, Feb,..., DEC)
%m two digits for the month (01,02,..., 12)
%c. number indicates month (1, 2,..., 12)
Year% Y four digit year (20152016...)
%y two digit year (15,16...)
Text output% text output text content directly

26. Query the first day of last month

			date_format(now(), '%y-%m-%d 00:00:00'),
			INTERVAL extract(DAY FROM now()) - 1 DAY

27. Query the last day of last month

			date_format(now(), '%y-%m-%d 23:59:59'),
			INTERVAL extract(DAY FROM now()) DAY
	) AS date

28. Access to last month

date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y %m')

29. View data of this month

date_format(publish_time, '%Y %m') = date_format(DATE_SUB(curdate(), INTERVAL 0 MONTH),'%Y %m')

30. Query last month's data

date_format(publish_time, '%Y %m') = date_format(DATE_SUB(curdate(), INTERVAL 2 MONTH),'%Y %m')

31. Current date

select DATE_SUB(curdate(),INTERVAL 0 DAY) ;

32. Tomorrow's date

select DATE_SUB(curdate(),INTERVAL -1 DAY) ;

33. Date yesterday

select DATE_SUB(curdate(),INTERVAL 1 DAY) ;

34. The previous hour

select date_sub(now(), interval 1 hour);

35. The next hour

select date_sub(now(), interval -1 hour);

36. The first 30 minutes

select date_add(now(),interval -30 minute)

37. 30 minutes later

select date_add(now(),interval 30 minute)

38. sql error

[error 1]

Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated
column 'partner.r.identity_no' which is not functionally dependent on columns in GROUP BY clause;
this is incompatible with sql_mode=only_full_group_by

terms of settlement:
Edit mysql configuration file my.ini
linux is / etc/my.cnf
Add this section under [mysqld] and [mysql], respectively


Then restart mysql service
linux restart command service mysqld restart

[error 2]
Too many mysql 1040 connections mysql Error 1040 too many connection solutions
1. Restart mysql service

service mysqld restart

2. Log in mysql

mysql -p

3. View the current maximum number of connections

show variables like 'max_connections';

4. Modify maximum connections

set global max_connections = 3600;

#View connection progress

show full processlist

show processlist; can display the first 100 connection information

39. Group statistics and ranking

      when @last_achievement = a.achievement
        then @rank
      when @last_achievement := a.achievement
        then @rank := @rank + 1
      end as ranking
      i.user_id as userId,
      i.order_no as orderNo,
      u.user_name as userName,
      u.head_img as headImg,
      u.nickname as nickname, as name,
      sum(f.total_order_goods_amount) as achievement
    from incomes_expenses_record i
    left join app_user u on i.user_id = u.user_id
    left join profit_record f on f.incomes_expenses_record_id = i.incomes_expenses_record_id
      where i.flag_del = 0
        and i.incomes_expenses_type = 3
        and i.operation_type = 1
        and date_format(i.create_time, '%Y-%m') = date_format(now(), '%Y-%m')
        and i.user_id in
    <foreach collection="teamUserIds" separator="," open="(" close=")" item="userId">
    group by i.order_no,i.user_id
    order by achievement desc) a,(SELECT @rank := 0,@last_achievement := NULL) r

40. Precautions for mysql left connection

A table left join B table query
If you want to take the data of table A as the benchmark, remember not to bring the condition of table B after the where condition

41. Copy table

select into from and insert into select
Are used to copy tables
The main differences between the two are: select into from requires that the target table does not exist, because it will be created automatically when inserting; insert into select from requires that the target table exists.
1. Copy table structure and its data:

create table table_name_new as select * from table_name_old

2. Copy table structure only:

create table table_name_new as select * from table_name_old where 1=2;


create table table_name_new like table_name_old

3. Copy table data only:
If two tables have the same structure:

insert into table_name_new select * from table_name_old

If the structure of the two tables is different:

insert into table_name_new(column1,column2...) select column1,column2... from table_name_old

42. mysql update select updates data from query results

Example 1

UPDATE user_online_month_atu a
sum(c.online_times) as online_times,
SUM(c.login_count) as login_count,
Sum(c.view_page_count) as view_page_count,
LEFT(c.log_date,length(c.log_date) - 2) as date
user_online_time_atu c
c.user_id ,date
) b ON a.user_id = b.user_id AND a.`month`
SET a.online_time = b.online_times ,a.login_count=b.login_count,a.view_page_count=b.view_page_count

Example 2

UPDATE app_user_info a INNER JOIN (
	from user_code u
) b on a.user_name = b.user_name
set a.invite_code = b.invite_code,a.invite_user_code = b.invite_user_code

43. Generate random string with mysql

select substring(MD5(RAND()),1,20)

44. Generate 3-bit random number


45. Generate 4-bit random number


46. Get the current timestamp

select unix_timestamp(now());

47. Get the current time stamp milliseconds

select REPLACE(unix_timestamp(current_timestamp(3)),'.','');

48. Query mysql cache configuration

show global variables like '%query_cache%';

49. Query table engine:

show variables like '%storage_engine%';

50. mysql cache
Temporary session modification, cache off

mysql> set query_cache_type=0;

Temporary global modification, cache off

mysql> set global query_cache_size=0;
mysql> set global query_cache_type=0;

Enable global caching

set global query_cache_size=20971520;
set global query_cache_type=1;

Permanent modification

vi my.cnf

Another way is to add oracle like hint

select sql_no_cache count(*) from mysql.user;

If this article can help you, please leave a message to like it! Your support and affirmation is the biggest driving force for me to keep updating.

57 original articles published, praised 23, visited 10000+
Private letter follow

Topics: MySQL Database SQL mysqldump