In daily work, mysql is used simply, so the Common commands are summarized as follows:
In daily work, mysql is used simply, so the Common commands are summarized as follows:
1. Start and exit
1. Go to MySQL: Start MySQL Command Line Client (MySQL DOS Interface) and enter the password for installation. The prompt is: mysql>
2. exit MySQL: quit or exit
Ii. Database Operations
1. Create a database
Command: create database <数据库名>
For example, create a database named xhkdb.
Mysql> create database xhkdb;
2. display all databases
Command: show databases (Note: The last s is available)
Mysql> show databases;
3. delete a database
Command: drop database <数据库名>
For example, delete a database named xhkdb.
Mysql> drop database xhkdb;
4. Connect to the database
Command: use <数据库名>
For example, if the xhkdb database exists, try to access it:
Mysql> use xhkdb;
On-screen prompt: Database changed
5. Database Selected (connected)
Mysql> select database ();
6. Table information contained in the current database:
Mysql> show tables; (Note: There is a last s)
Iii. Table operations. A database should be connected before operations
1. Create a table
Command: create table <表名> ( <字段名1> <类型1> [,.. <字段名n> <类型n> ]);
Mysql> create table MyClass (
> Id int (4) not null primary key auto_increment,
> Name char (20) not null,
> Sex int (4) not null default '0 ',
> Degree double (16, 2 ));
2. Get the table structure
Command: desc table name or show columns from Table Name
Mysql> desc MyClass;
Mysql> show columns from MyClass;
3. delete a table
Command: drop table <表名>
For example, delete a table named MyClass.
Mysql> drop table MyClass;
4. insert data
Command: insert <表名> [( <字段名1> [,.. <字段名n> ])] Values (value 1) [, (value n)]
For example, insert two records into the MyClass table. The two records indicate that the result of Tom numbered 1 is 96.45, and the result of Joan numbered 2 is 82.99, wang, numbered 3, scored 96.5.
Mysql> insert into MyClass values (1, 'Tom ', 96.45), (2, 'job', 82.99), (2, 'wang', 96.59 );
5. query the data in the table
1) query all rows
Command: select <字段1，字段2，...> From <Table Name> where <expression>
For example, you can view all data in the MyClass table.
Mysql> select * from MyClass;
2) query the first few rows of data
For example, view the first two rows of data in the MyClass table.
Mysql> select * from MyClass order by id limit 0, 2;
6. Delete table data
Command: delete from table name where expression
For example, delete the record numbered 1 in MyClass.
Mysql> delete from MyClass where id = 1;
7. Modify Table data: update table name set field = new value ,... Where condition
Mysql> update MyClass set name = 'Mary 'where id = 1;
7. Add fields to the table:
Command: alter table name, add, other field types;
For example, a passtest field is added to the MyClass table. The type is int (4) and the default value is 0.
Mysql> alter table MyClass add passtest int (4) default '0'
8. Change the table name:
Command: rename table original table name to new table name;
For example, the MyClass name in the table is changed to YouClass.
Mysql> rename table MyClass to YouClass;
Update field content
Update table name set field name = new content
Update table name set field name = replace (field name, 'old content', 'new content ');
Add four spaces before the article
Update article set content = concat ('', content );
Field Type
1. INT [(M)] type: normal Integer type
2. DOUBLE [(M, D)] [ZEROFILL] type: normal size (DOUBLE Precision) floating point number type
3. DATE type: the supported range is 1000-01-01 to 9999-12-31. MySQL displays DATE values in YYYY-MM-DD format, but allows you to assign values to the DATE column using strings or numbers
4. CHAR (M) type: fixed-length string type. When stored, it always fills the Right to the specified length with spaces
5. blob text type, with a maximum length of 65535 (2 ^ 16-1) characters.
6. VARCHAR: variable-length string type
Use of the mysqldump command
Back up and export Databases
Mysqldump-h database_ip-u Username-p -- opt databasename> backup-file. SQL
Export only the database table structure
Mysqldump-h database_ip-d-u Username-p databasename> database_structure. SQL
Export only a table in the database
Mysqldump -- opt -- add-drop-table-u Username-p databasename tablename> dump. SQL
If you do not want to manually enter the password, use the -- password parameter.
Mysqldump-h database_ip-u Username -- password = 123456 -- opt databasename> backup-file. SQL
Mysqldump-h database_ip-d-u Username -- password = 123456 databasename> database_structure. SQL
Mysql Command
Save query results to files
Select title from book into outfile '/tmp/outfile.txt ';
Searches for redundant duplicate records in a table. duplicate records are determined based on a certain field (peopleId ).
Select * from people where peopleId in (select peopleId from people group
PeopleId having count (peopleId)> 1 );
Query non-repeated records in a table (excluding Repeated Records)
Select * from phome_ecms_wma where title in (select distinct title from phome_ecms_wma );
Deletes duplicate records in a table. duplicate records are determined based on a field (title ).
Select *, count (distinct title) into outfile '/tmp/table. bak' from phome_ecms_wma group by title;
Delete from phome_ecms_wma;
Load data infile '/tmp/table. bak' replace into table phome_ecms_wma character set utf8;
Query the current encoding of the database
Mysql> show variables like "character_set % ";
Modify Table Field Type
Mysql> alter table table_name change last_action datetime not null default '2017-00-00 00:00:00 ';
Add a new field to the table
Mysql> alter table host ADD ks_mac VARCHAR (100 );
Delete a field from the table
Mysql> alter table table_name DROP field_name;
Rename a table
Mysql> alter table t1 rename t2;
Add an index to a field
Mysql> alter table tablename add index name (field name 1 [, field name 2…]);
Mysql> alter table tablename add index emp_name (name );
Index with primary keywords
Mysql> alter table tablename add primary key (id );
Add an index with unique conditions
Mysql> alter table tablename add unique emp_name2 (cardnumber );
Delete An index
Mysql> alter table tablename drop index emp_name;
Remote Access to mysql settings
Mysql> grant all privileges on database_test. * to root@192.168.1.9 identified by '20140901 ';
Mysql> flush privileges;
1. Use the SHOW statement to find out the current database on the server.
Mysql> show databases;
2. Create a database named MYSQLDATA
Mysql> create database mydata;
3. Select the database you created
Mysql> use mydata;
4. view the tables in the current database
Mysql> show tables;
5. Create a database table
Mysql> create table mytable (name varchar (20), sex char (1 ));
6. display table structure:
Mysql> describe mytable;
7. Add records to the table
Mysql> insert into mytable values ("test", "m ");
8. load data into a database table in text format (for example, d: \ mysql.txt)
Mysql> load data local infile "d:/mysql.txt" into table mytable;
9. Import the. SQL FILE command (for example, d: \ mysql. SQL)
Mysql> use database;
Mysql> source d:/mysql. SQL;
10. delete a table
Mysql> drop table mytable;
11. Clear the table
Mysql> delete from mytable;
12. Update table data
Mysql> update mytable set sex = "f" where name = test;
The newly installed MySQL contains a root account with a blank password and an anonymous account, which poses a major security risk. For some important applications, we should improve the security as much as possible, here, you should delete anonymous accounts and Set passwords for root accounts. You can run the following command:
========================================================
Mysql> use mysql;
Mysql> delete from User where User = "";
Mysql> update User set Password = PASSWORD (newpassword) where User = root;
========================================================
If you want to restrict the logon terminal used by the User, you can update the Host field of the corresponding User in the User table. After making the above changes, restart the database service, at this time, the following commands can be used for Logon:
========================================================
Shell> mysql-uroot-p;
Shell> mysql-uroot-pnewpassword;
Shell> mysql mydb-uroot-p;
Shell> mysql mydb-uroot-pnewpassword;
========================================================
The preceding command parameters are part of common parameters. For details, refer to the documentation. Here, mydb is the name of the database you want to log on.
In development and practical applications, users should not only use root users to connect to the database. Although it is convenient to use root users for testing, it will bring significant security risks to the system, it is not conducive to the improvement of management technology. We grant the most appropriate database permissions to the users used in an application. For example, a user who only inserts data should not be granted the permission to delete data. MySQL User management is implemented through the User table. There are two common methods to add new users. One is to insert the corresponding data rows in the User table and set the corresponding permissions; the second is to use the grant command to create a user with certain permissions. The common usage of grant is as follows:
========================================================== ======================================
Mysql> grant all on mydb. * to NewUserName @ HostName identified by "password ";
Mysql> grant usage on *. * to NewUserName @ HostName identified by "password ";
Mysql> grant select, insert, update on mydb. * to NewUserName @ HostName identified by "password ";
Mysql> grant update, delete on mydb. TestTable to NewUserName @ HostName identified by "password ";
========================================================== ======================================
To grant the user the ability to manage permissions on the corresponding object, you can add the with grant option after grant. For users inserted into the User table, use the Password function to update and encrypt the password field to prevent unauthorized users from stealing the Password. Users who do not need permissions should be cleared, and those who pass the permissions should be revoked in a timely manner. To revoke permissions, you can update the corresponding fields in the User table or use the revoke operation. The following is an explanation of common permissions:
========================================================== =====
Global Management Permissions
FILE: read and write files on the MySQL server.
PROCESS: displays or kills service threads of other users.
RELOAD: RELOAD Access Control tables and refresh logs.
SHUTDOWN: Shut down the MySQL service.
Database/data table/data column Permissions
ALTER: Modify existing data tables (such as adding/deleting columns) and indexes.
CREATE: CREATE a new database or data table.
DELETE: DELETE table records.
DROP: delete a data table or database.
INDEX: Create or delete an INDEX.
INSERT: Add Table records.
SELECT: displays/searches for table records.
UPDATE: Modify existing records in the table.
Special Permissions
ALL: allow anything (same as root ).
USAGE: Only logon is allowed. Other operations are not allowed.
========================================================== =====
Common MySQL operations: The following are all tests passed under MySQL5.0. First, note that you must add them at the end of each command. (semicolon)
1. Export the entire database
Mysqldump-u username-p -- default-character-set = latin1 Database Name> exported file name (the default database encoding is latin1)
Mysqldump-u wcnc-p smgp_rj_wcnc> wcnc. SQL
2. Export a table
Mysqldump-u user name-p database name Table Name> exported file name
Mysqldump-u wcnc-p smgp_rj_wcnc users> wcnc_users. SQL
3. Export a database structure
Mysqldump-u wcnc-p-d-add-drop-table smgp_apps_wcnc> d: wcnc_db. SQL
-D no data-add-drop-table add a drop table before each create statement
4. Import the database
Common source commands
Go to the mysql Database Console,
For example, mysql-u root-p
Mysql> use Database
Then run the source command. The following parameter is the script file (for example,. SQL used here)
Mysql> source d: wcnc_db. SQL
