MySQL database operations common commands

Transferred from the public number "MySQL database": http://mp.weixin.qq.com/s?__biz=MzIyNjIwMzg4Ng==&mid=2655293044&idx=1&sn= E312934e5115105fdbe5da12af150276&scene=0#wechat_redirect

"All over" MySQL database operations common commands2016-07-21 MySQL Database

1. mysql Common commands

Create database name; Create a database
Use DatabaseName; Select Database
Drop database name deletes databases directly and does not alert
Show tables; Show Table
Describe TableName; Detailed description of the table
SELECT * FROM TableName;
MySQL command queries the total number of records for a table (three methods)
Select COUNT (*) from TableName;
Or
Select COUNT (*) as Num from TableName;
Or
Select COUNT (*) as total from TableName;
Add distinct in Select to remove duplicate fields
Mysqladmin drop DatabaseName You are prompted before you delete the database.
Show current MySQL version and current date
Select version (), current_date;

2. Change the password of root in MySQL:
Shell>mysql-u root-p
mysql> Update user Set Password=password ("Xueok654123″) where user= ' root ';

Mysql> flush Privileges//Refresh Database

Mysql>use dbname; Open the database:
Mysql>show databases; Show all databases
Mysql>show tables; Displays all tables in the database MySQL: use MySQL first;
Mysql>describe user; Displays column information for the user table in the MySQL database);

3. Grant
Create a full superuser who can connect to the server from anywhere, but must use a password something do this
Mysql> Grant all privileges on * * to [e-mail protected] identified by ' something ' with
Add new users
Format: Grant Select on database. * To User name @ login host identified by "password"
GRANT all privileges on * * to [email protected] identified by ' something ' with GRANT OPTION;
GRANT all privileges on * * to [e-mail protected] "%" identified by ' something ' with GRANT OPTION;

Remove Authorization:
Mysql> revoke all privileges on * * FROM [email protected] "%";
mysql> Delete from user where user= "root" and host= "%";
mysql> flush Privileges;
Create a user custom login on a specific client it363.com to access a specific database fangchandb
MySQL >grant Select, insert, UPDATE, Delete, Create,drop on fangchandb.* to [e-mail protected] it363.com identified by ' passwd

To rename a table:
mysql > ALTER table t1 rename T2;

4, Mysqldump
Backing Up the database
shell> mysqldump-h host-u root-p dbname >dbname_backup.sql
Recovering a Database
shell> mysqladmin-h myhost-u root-p Create dbname
shell> mysqldump-h host-u root-p dbname < Dbname_backup.sql
If you only want to unload the build instruction, the command is as follows:
Shell> mysqladmin-u root-p-D databasename > A.sql
If you only want to unload the SQL command that inserts the data, and you do not need to create a table command, the command is as follows:
shell> mysqladmin-u root-p-t databasename > A.sql

So what should I do if I only want the data and I don't want any SQL commands?
mysqldump-t./Phptest Driver

Only the-t parameter is specified to unload the plain text file, which represents the directory where the data is unloaded./represents the current directory, that is, the same directory as mysqldump. If you do not specify a driver table, the data for the entire database is unloaded. Each table generates two files, one for the. sql file, which contains the build table execution. The other is a. txt file that contains only data and no SQL instructions.

5. You can store the query in a file and tell MySQL to read the query from the file instead of waiting for keyboard input. The shell can be used to type the redirection utility to complete this work. For example, if you have queries in file My_file.sql, you can execute these queries as follows:

For example, if you want to write the statement in advance in Sql.txt:  
mysql > mysql-h myhost-u root-p Database < sql.txt 

I. Install and configure my sql 
Two. Common mysql command line command  
1. mysql start and stop  
Start MySQL service net start mysql 
Stop MySQL service net St Op mysql 
2. Netstat–na | findstr 3306 to view the port being monitored, findstr to find out if the subsequent port exists  
3. Log in to the MySQL console at the command line, using MY SQL commend line tool 
syntax format mysql–user=root–password=123456 db_name 
or Mysql–u root–p123456 db_n ame 
4. After accessing the MySQL command-line tool, use status, or \s to view the running environment information  
5. The syntax for switching to a connection database: using NEW_DBNAME;&NBSP;
6. Show all numbers According to the library: show databases; 
7. Displays all tables in the database: Show tables; 
8. Displays all information when a table is created: show CREATE TABLE table_name;   &NBSP;
9. View the specific property information of the table and the description of each field in the table  
Describe table_name; abbreviated FORM: desc table_name; 

Three. SQL statements in MySQL
1. Database creation: create databases db_name;
Database deletion: Drop DB db_name; The deletion can be judged by the existence, written as: drop database if exits db_name
2. Build table: syntax for creating data tables: CREATE TABLE table_name (field 1 data type, field 2 data type);
Example: CREATE TABLE mytable (ID int, username char (20));
Delete tables: DROP TABLE table_name; Example: Drop table mytable;
3. Add data: Insert into table name [(Field 1, Field 2, ...)] VALUES (value 1, value 2, ...);
If you insert a value into each field in the table, the field name in the preceding [] parentheses is not writable

Example: INSERT INTO MyTable (id,username) VALUES (1, ' Zhangsan ');

4. Query: Query all data: SELECT * FROM table_name;
Query data for the specified field: Select Field 1, field 2 from table_name;
Example: Select Id,username from MyTable where id=1 ORDER by DESC, multi-table query statement------------reference to 17th instance

5. Update specified data to update data for a field (note that the name of the field is not updated)
Update table_name SET field name = ' new value ' [, Field 2 = ' new value ', ...] [Where Id=id_num] [Order BY Field]
Example: Update mytable set username= ' Lisi ' where id=1;
The order statement is a sequence of queries, such as ORDER by ID DESC (or ASC), in order of two kinds: desc reverse (100-1, which is queried from the latest data), ASC (from 1-100), where and order statements can also be used to query select and Delete del Ete
6. Delete the information in the table:
Delete information from the entire table: delete from table_name;
Delete the statement for the specified condition in the table: DELETE from table_name where condition statement; Conditional statements such as: id=3;
7. Create a database user
You can create multiple database users at a time such as:
CREATE USER username1 identified by ' password ', username2 identified by ' password ' ....
8. Permissions control for users: Grant
Library, table-level permission control: assigning control of a table in a library to a user
Grant all on Db_name.table_name to user_name [indentified by ' Password '];
9. Modification of table structure
(1) Add a field format:
ALTER TABLE table_name Add column (field name fields type); ----This method with parentheses
(2) Specify where the field is inserted:
ALTER TABLE table_name ADD column Name field type after a field;
Delete a field:
ALTER TABLE table_name DROP field name;
(3) Modify field name/type
ALTER TABLE table_name change old field name new field name type of new field;

(4) Change the name of the table
ALTER TABLE table_name Rename to New_table_name;
(5) Clear all data in the table at once
TRUNCATE TABLE table_name; This method also causes the number picker (ID) in the table to start at 1

10. Add primary key, foreign key, constraint, index .... (see 17 examples for using the method)
① constraints (primary key primary key, uniqueness unique, non-null NOT NULL)
② Automatic add-on auto_increment
③ foreign key foreign key-----used in conjunction with reference table_name (col_name column name) and used separately when building a table
④ Delete multiple tables with associated data----set foreign key to set NULL---specific settings reference Help document
11. View the current engine of the database
SHOW CREATE TABLE table_name;
Modifying the Database Engine
ALTER TABLE table_name Engine=myisam | InnoDB;
12. Examples of SQL statements used:
--1 Build Users Table
CREATE table users (ID int primary key auto_increment,nikename varchar () NOT NULL Unique,password varchar (+) NOT NULL, Address varchar ($), reg_date timestamp NOT null default current_timestamp);
--2 build Articles table, set foreign key when building table
CREATE table articles (ID int primary key auto_increment,content Longtext not Null,userid int,constraint foreign key (user ID) references users (ID) on delete set null);
-----------------------------------------------------------------------
--2.1 Build Articles table, do not set foreign key when building table
CREATE table articles (ID int primary key auto_increment,content longtext not null,userid int);
--2.2 setting foreign keys for articles tables
ALTER TABLE articles add constraint foreign key (userid) references users (ID) on delete set null;
------------------------------------------------------------------------
--3. Insert data into the Users table and insert multiple
Insert into users (id,nikename,password,address) VALUES (1, ' lyh1 ', ' 1234 ', null), (Ten, ' lyh22 ', ' 4321 ', ' Hubei Wuhan '), (null, ' lyh333 ', ' 5678 ', ' Beijing Haidian ');

--4. Inserting three data into the article
Insert into articles (Id,content,userid) VALUES (2, ' Hahahahahaha ', one-by-one), (null, ' XIXIXIXIXIX ', 10), (13, ' Aiaiaiaiaiaiaiaiaiaiaiaia ', 1), (+, ' hohoahaoaoooooooooo ', 10);
--5. Make a multi-table query, select all messages posted by users in the user table id=10 and all the information for that user
Select articles.id,articles.content,users.* from Users,articles where users.id=10 and articles.userid=users.id order by Articles.id desc;
--6. To view the database engine type
Show create table users;
--7. Modifying the Database engine type
ALTER TABLE users Engine=myisam; ---because the IDs in the users table are set to foreign keys, there is an error executing this sentence
--8. In the case of a table query, a condition is known. The query ID number is greater than the user LYH1 ID number for all users
Select a.id,a.nikename,a.address from users a,users b where b.nikename= ' lyh1 ' and a.id>b.id;
------can also be written
Select Id,nikename,address from the Users where id> (select ID from users where nikename= ' lyh1 ');
9. Show employees older than the leader:
Select A.name from users a,users b where a.managerid=b.id and a.age>b.age;
Query number 2: first check the articles table, get the number of the post, and then according to the number of users to find the user name.
Then use the correlation query.
SELECT * from articles,users get Cartesian product, plus order by articles.id to observe
Use SELECT * from Articles,users where articles.id=2 to filter out 2nd posts with each user's combined record
Use SELECT * from Articles,users where articles.id=2 and articles.userid=users.id to select a record of the post ID of the Users.id equal to 2nd posts.

User name only: Select User where user.id= (select UserID from articles where article.id =2)
Find out who is older than Xiao Wang: assuming that Xiao Wang is 28 years old, first want to find out the age of more than 28 people
SELECT * from the Users where age> (select age from Users where name= ' Xiaowang ');
The records to be queried need to refer to the other records in the table:
Select A.name from users a,users b where b.name= ' Xiaowang ' and a.age>b.age
Each user in the table wants to PK a bit. Select A.nickname,b.nickname from users a,users b where a.id>b.id;
The more insured statement: Select A.nickname,b.nickname from (SELECT * from the users order by ID) A, (SE
Lect * from the users order by ID) b where a.id>b.id;
Then check all posts from someone.
Select b.* from articles A, articles B where a.id=2 and A.userid=b.userid
Description: There is a relationship between the tables, an explanation of the ER concepts, and a demonstration of the relationship between tables with the sample database in Access. Only InnoDB engines support foreign key,mysql any engine that does not currently support check constraints.
Four, the character set error resolution method
Problems that arise:
Mysql> Update Users
Set username= ' Guan Yu '
where userid=2;
ERROR 1366 (HY000): Incorrect string value: ' \xb9\xd8\xd3\xf0 ' for column ' Usern
Ame ' at row 1
An error occurred while inserting Chinese characters into the table.

Mysql> select * from users;
+--------+----------+
| UserID | Username |
+--------+----------+
| 2 |???? |
| 3 |???? |
| 4 |? í?ù|
+--------+----------+
3 Rows in Set (0.00 sec)
The Chinese character bits in the table are garbled.

Workaround:
Use the command:
mysql> status;
--------------
MySQL Ver 14.12 distrib 5.0.45, for Win32 (IA32)

Connection Id:8
Current Database:test
Current User: [email protected]
Ssl:not in use
Using delimiter:;
Server version:5.0.45-community-nt MySQL Community Edition (GPL)
Protocol version:10
Connection:localhost via TCP/IP
Server characterset:latin1
Db characterset:latin1
Client CHARACTERSET:GBK
Conn. CHARACTERSET:GBK
TCP port:3306
Uptime:7 hours-min. sec
Threads:2 questions:174 Slow queries:0 opens:57 Flush tables:1 Open ta
Bles:1 Queries per second avg:0.006
--------------
Viewing the MySQL Discovery server characterset,db CharacterSet's character set is set to Latin1, so Chinese garbled characters appear.

Mysql> Show tables;
+----------------+
| Tables_in_test |
+----------------+
| Users |
+----------------+
1 row in Set (0.00 sec)

Change the character set of the table.
mysql> ALTER TABLE users character set GBK;
Query OK, 3 rows affected (0.08 sec)

Records:3 duplicates:0 warnings:0

To view the structure of a table:
Mysql> Show create users;
Error 1064 (42000): You have a error in your SQL syntax; Check the manual that
Corresponds to your MySQL server version for the right syntax to use near ' users
' at line 1
Mysql> Show create table users;
+-------+-----------------------------------------------------------------------
------------------------------------------------------------------------------+
| Table | Create Table
|
+-------+-----------------------------------------------------------------------
------------------------------------------------------------------------------+
| Users | CREATE TABLE ' users ' (
' userid ' int (one) default NULL,
' username ' char (character set latin1 default NULL)
) Engine=innodb DEFAULT CHARSET=GBK |
+-------+-----------------------------------------------------------------------
------------------------------------------------------------------------------+
1 row in Set (0.00 sec)

mysql> desc users;
+----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| UserID | Int (11) | YES | | NULL | |
| Username | char (20) | YES | | NULL | |
+----------+----------+------+-----+---------+-------+
2 rows in Set (0.02 sec)

In this case, insert Chinese into the table and then there is an error.
Mysql> INSERT into users values (88, ' Chinese ');
ERROR 1366 (HY000): Incorrect string value: ' \xd6\xd0\xce\xc4 ' for column ' Usern
Ame ' at row 1
Mysql> INSERT into users values (88, ' Chinese ');

Mysql> INSERT into users values (88, ' Chinese ');
ERROR 1366 (HY000): Incorrect string value: ' \xd6\xd0\xce\xc4 ' for column ' Usern
Ame ' at row 1

Also change the username character set of the users table.
Mysql> ALTER TABLE users modify username char (character set GBK;
ERROR 1366 (HY000): Incorrect string value: ' \xc0\xee\xcb\xc4 ' for column ' Usern
Ame ' at row 1
Mysql> ALTER TABLE users modify username char (character set GBK;
ERROR 1366 (HY000): Incorrect string value: ' \xc0\xee\xcb\xc4 ' for column ' Usern
Ame ' at row 1

Changing the username character set does not work because there is already data in the table * * *
Emptying the data in the Users table
mysql> TRUNCATE TABLE users;
Query OK, 3 rows affected (0.01 sec)

To change the character set of username in the user table from new
Mysql> ALTER TABLE users modify username char (character set GBK;
Query OK, 0 rows affected (0.06 sec)
records:0 duplicates:0 warnings:0

Then insert Chinese characters, insert into * * *.
Mysql> INSERT into users values (88, ' Chinese ');
Query OK, 1 row affected (0.01 sec)

Mysql> select * from users;
+--------+----------+
| UserID | Username |
+--------+----------+
| 88 | English |
+--------+----------+
1 row in Set (0.00 sec)
Mysql>

MySQL database operations common commands