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 192.168.1.101 -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 192.168.1.101 -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
SHOW STATUS
2. MySQL statements used to create specific databases or tables
SHOW CREATE DATABASE SHOW CREATE TABLE
3. Used to display security rights granted to users (all users or specific users)
SHOW GRANTS
4. Used to display server error or warning messages
SHOW ERRORS SHOW WARNINGS
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
SELECT a.* FROM ( SELECT seller_mobile, count(seller_mobile) num FROM seller GROUP BY seller_mobile ) a WHERE 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
CONCAT('str')
13. Add a primary key to an existing table
ALTER TABLE Table name ADD id INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT FIRST;
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
TRUNCATE(x,d)
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
dual
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');
Schedule
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
SELECT date_sub( date_sub( date_format(now(), '%y-%m-%d 00:00:00'), INTERVAL extract(DAY FROM now()) - 1 DAY ), INTERVAL 1 MONTH )
27. Query the last day of last month
SELECT date_sub( date_sub( date_format(now(), '%y-%m-%d 23:59:59'), INTERVAL extract(DAY FROM now()) DAY ), INTERVAL 0 MONTH ) AS date
28. Access to last month
SELECT date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y %m')
29. View data of this month
SELECT * FROM content_publish WHERE date_format(publish_time, '%Y %m') = date_format(DATE_SUB(curdate(), INTERVAL 0 MONTH),'%Y %m')
30. Query last month's data
SELECT * FROM content_publish WHERE 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
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
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
select a.*, case when @last_achievement = a.achievement then @rank when @last_achievement := a.achievement then @rank := @rank + 1 end as ranking from (select i.user_id as userId, i.order_no as orderNo, u.user_name as userName, u.head_img as headImg, u.nickname as nickname, u.name 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"> #{userId} </foreach> 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;
Or:
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 INNER JOIN ( SELECT user_id, 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 FROM user_online_time_atu c GROUP BY c.user_id ,date ) b ON a.user_id = b.user_id AND a.`month`=b.date 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 ( SELECT u.user_name, u.invite_code, u.invite_user_code 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
SELECT CEILING(RAND()*900+100);
45. Generate 4-bit random number
SELECT CEILING(RAND()*9000+1000);
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 query_cache_type=0 query_cache_size=0
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.
