Mysql database command Daquan _ MySQL-mysql tutorial

Mysql commands are often used by everyone, but few of you can sort them out. there are not many projects recently. you can take a moment to summarize the mysql database commands. if you need them, refer to section 1. connect to Mysql.

Format: mysql-h host address-u user name-p user password

1. connect to MYSQL on the local machine.

First, open the DOS window, enter the mysql \ bin directory, type the mysql-u root-p command, and press enter to prompt you to enter the password. note that there can be spaces or spaces before the user name, but there must be no spaces before the password. Otherwise, you can re-enter the password.

If you have just installed MYSQL, the super user root has no password, so press enter to enter MYSQL. the MYSQL prompt is: mysql>

2. connect to MYSQL on the remote host. Assume that the IP address of the remote host is 110.110.110.110, the user name is root, and the password is abcd123. Enter the following command:

Mysql-h110.110.110.110-u root-p 123)

3. exit MYSQL command: exit (press enter)

II. change the password

Format: mysqladmin-u username-p old password new password

1. add a password ab12 to the root user.

First, enter the mysql \ bin directory under DOS, and then type the following command

mysqladmin -u root -password ab12
Note: Because root does not have a password at the beginning, the -p old password item can be omitted.

2. Change the root password to djg345.
mysqladmin -u root -p ab12 password djg345
Third, add new users
Note: Unlike the above, the following are commands in the MYSQL environment, so a semicolon is used as the command terminator.

Format: grant select on database. * To username @ loginhost identified by "password"

1. Add a user test1 with the password abc so that he can log in on any host and have query, insert, modify, and delete permissions for all databases. First connect to MYSQL as the root user, and then type the following command:
 grant select, insert, update, delete on *. * to [email = test1 @ ”%] test1 @”% [/ email] ”Identified by“ abc ”;
But adding users is very dangerous. If someone knows the password of test1, then he can log in to your mysql database on any computer on the Internet and do whatever he wants with your data. See 2 for the solution. .

2. Add a user test2 with the password abc, so that he can only log in on localhost and query, insert, modify, and delete the database mydb (localhost refers to the local host, which is the host where the MYSQL database is located) In this way, even if the user knows the password of test2, he cannot directly access the database from the Internet, but only through the web page on the MySQL host.
grant select, insert, update, delete on mydb. * to [email = test2 @ localhost] test2 @ localhost [/ email] identified by “abc”;

If you don't want test2 to have a password, you can type another command to erase the password.
grant select, insert, update, delete on mydb. * to [email = test2 @ localhost] test2 @ localhost [/ email] identified by “”;
4.1 Create Database

Note: you must connect to the MySQL server before creating the database

Command: create database <database name>

Example 1: Create a database named xhkdb
mysql> create database xhkdb;

Example 2: Create a database and assign users

①CREATE DATABASE database name;

② GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON database name. * TO database name @localhost IDENTIFIED BY 'password';

③SET PASSWORD FOR 'database name' @ 'localhost' = OLD_PASSWORD ('password');

Execute 3 commands in turn to complete the database creation. Note: The Chinese "password" and "database" are set by the user himself.
4.2 Displaying the database
Command: show databases (note: there is an s at the end)
mysql> show databases;

Note: In order to prevent garbled characters from being displayed, modify the database default encoding. The following uses GBK coded pages as an example:

1. Modify the MYSQL configuration file: modify default-character-set = gbk in my.ini
2. Modification when the code is running:
① Java code: jdbc: mysql: // localhost: 3306 / test? UseUnicode = true & characterEncoding = gbk
②PHP code: header ("Content-Type: text / html; charset = gb2312");
③C language code: int mysql_set_character_set (MYSQL * mysql, char * csname);
This function is used to set the default character set for the current connection. The string csname specifies a valid character set name. Connection collation becomes the default collation of the character set. This function works similar to the SET NAMES statement, but it also sets the value of mysql-> charset, which affects the character set set by mysql_real_escape_string ().
4.3 Delete Database

Command: drop database <database name>
For example: delete the database named xhkdb
mysql> drop database xhkdb;

Example 1: Delete an existing database

mysql> drop database drop_database;
 Query OK, 0 rows affected (0.00 sec)
Example 2: Delete an uncertain database

 mysql> drop database drop_database;
 ERROR 1008 (HY000): Can't drop database 'drop_database'; database doesn't exist
  // An error occurred, the 'drop_database' database cannot be deleted, the database does not exist.
 mysql> drop database if exists drop_database;
 Query OK, 0 rows affected, 1 warning (0.00 sec) // Generate a warning that this database does not exist
 mysql> create database drop_database;
 Query OK, 1 row affected (0.00 sec)
 mysql> drop database if exists drop_database; // if exists to determine whether the database exists, does not exist and does not generate errors
 Query OK, 0 rows affected (0.00 sec)

4.4 Connecting to the database
Command: use <database name>

For example: If the xhkdb database exists, try to access it:
mysql> use xhkdb;
Screen prompt: Database changed

The use statement can notify MySQL to use the db_name database as the default (current) database for subsequent statements. The database remains the default database until the end of the segment, or until a different USE statement is issued:
mysql> USE db1;
 mysql> SELECT COUNT (*) FROM mytable; # selects from db1.mytable
 mysql> USE db2;
 mysql> SELECT COUNT (*) FROM mytable; # selects from db2.mytable
Using the USE statement to mark a specific current database will not prevent you from accessing tables in other databases. The following example can access the author table from the db1 database and the edit table from the db2 database:
 mysql> USE db1;
 mysql> SELECT author_name, editor_name FROM author, db2.editor
  -> WHERE author.editor_id = db2.editor.editor_id;
The USE statement was established for compatibility with Sybase.

Some netizens asked how to exit after connecting. In fact, there is no need to exit. After using the database, you can use show databases to query all the databases. If you want to jump to other databases, use the name of the other database.
4.5 The currently selected database

Command: mysql> select database ();

The SELECT command in MySQL is similar to print or write in other programming languages. You can use it to display the results of a string, number, mathematical expression, etc. How to use the special features of the SELECT command in MySQL?

1. Display the version of MYSQL
mysql> select version ();
+ ----------------------- +
| version () |
+ ----------------------- +
| 6.0.4-alpha-community |
+ ----------------------- +
1 row in set (0.02 sec)

2. Display the current time
mysql> select now ();
+ --------------------- +
| now () |
+ --------------------- +
| 2009-09-15 22:35:32 |
+ --------------------- +
1 row in set (0.04 sec)
3. Display the date
SELECT DAYOFMONTH (CURRENT_DATE);
+ -------------------------- +
| DAYOFMONTH (CURRENT_DATE) |
+ -------------------------- +
| 15 |
+ -------------------------- +
1 row in set (0.01 sec)
 
SELECT MONTH (CURRENT_DATE);
+ --------------------- +
| MONTH (CURRENT_DATE) |
+ --------------------- +
| 9 |
+ --------------------- +
1 row in set (0.00 sec)
 
SELECT YEAR (CURRENT_DATE);
+ -------------------- +
| YEAR (CURRENT_DATE) |
+ -------------------- +
| 2009 |
+ -------------------- +
1 row in set (0.00 sec)

4. Display string
mysql> SELECT "welecome to my blog!";
+ ---------------------- +
| welecome to my blog! |
+ ---------------------- +
| welecome to my blog! |
+ ---------------------- +
1 row in set (0.00 sec)
5. When used as a calculator
select ((4 * 4) / 10) + 25;
+ ---------------------- +
| ((4 * 4) / 10) + 25 |
+ ---------------------- +
| 26.60 |
+ ---------------------- +
1 row in set (0.00 sec)
6. Concatenate strings
select CONCAT (f_name, "", l_name)
AS Name
from employee_data
where title = 'Marketing Executive';
+ --------------- +
| Name |
+ --------------- +
| Monica Sehgal |
| Hal Simlai |
| Joseph Irvine |
+ --------------- +
3 rows in set (0.00 sec)

Note: The CONCAT () function is used here to concatenate strings. In addition, we also used the previously learned AS to give a pseudonym to the result column 'CONCAT (f_name, "", l_name)'.
5.1 Creating a data table

Command: create table <table name> (<field name 1> <type 1> [, .. <field name n> <type n>]);

For example, create a table named MyClass,
Field name Number type Data width Is blank Yes No Primary key Automatically increase Default value
id int 4 no primary key auto_increment
name char 20 no
sex int 4 no 0
degree double 16 is
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));

5.3 Delete data table
Command: drop table <table name>

For example: delete the table named MyClass
mysql> drop table MyClass;

DROP TABLE is used to cancel one or more tables. You must have DROP permissions for each table. All table data and table definitions will be cancelled, so use this statement with caution!

Note: For a partitioned table, DROP TABLE will permanently cancel the table definition, cancel each partition, and cancel all data stored in these partitions. DROP TABLE also cancels the partition definition (.par) file associated with the dropped table.

For tables that do not exist, use IF EXISTS to prevent errors. When using IF EXISTS, a NOTE is generated for each table that does not exist.

RESTRICT and CASCADE can make partitioning easier. Currently, RESTRICT and CASCADE do not work.
5.4 Table Insert Data

Command: insert into <table name> [(<field name 1> [, .. <field name n>])] values (value 1) [, (value n)]

For example: insert two records into the table MyClass, these two records indicate: Tom with a score of 96.45, Joan with a score of 82.99, and Wang with a score of 96.5 .
mysql> insert into MyClass values (1, 'Tom', 96.45), (2, 'Joan', 82.99), (2, 'Wang', 96.59);
Note: insert into can only insert one record into the table at a time.
5.5 Querying data in a table
1), query all rows
Command: select <field1, field2, ...> from <table name> where <expression>
Example: View all data in the table MyClass
mysql> select * from MyClass;
2) Query the first few rows of data
For example: view the first 2 rows of data in the table MyClass
mysql> select * from MyClass order by id limit 0,2;
Select is generally used with where to query more accurate and complex data.
5.6 Delete data from the table

Command: delete from table name where expression

For example: delete the record number 1 in the table MyClass
mysql> delete from MyClass where id = 1;
The following is a comparison of the table before and after deleting data.
FirstName LastName Age
Peter Griffin 35
Glenn Quagmire 33

The following uses PHP code as an example to delete all records with LastName = 'Griffin' in the "Persons" table:
<? php
 $ con = mysql_connect ("localhost", "peter", "abc123");
 if (! $ con)
 {
  die ('Could not connect:'. mysql_error ());
 }
 mysql_select_db ("my_db", $ con);
 mysql_query ("DELETE FROM Persons WHERE LastName = 'Griffin'"); mysql_close ($ con);
?>
After this delete, the table looks like this:
FirstName LastName Age
Glenn Quagmire 33
5.7 Modify the data in the table
Syntax: update table name set field = new value, ... where condition
 mysql> update MyClass set name = 'Mary' where id = 1;
Example 1: MySQL UPDATE statement for a single table:
 UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1 = expr1 [, col_name2 = expr2 ...] [WHERE where_definition] [ORDER BY ...] [LIMIT row_count]
Example 2: Multi-table UPDATE statement:
UPDATE [LOW_PRIORITY] [IGNORE] table_references SET col_name1 = expr1 [, col_name2 = expr2 ...] [WHERE where_definition]
The UPDATE syntax can update the columns in the original table row with new values. The SET clause indicates which columns to modify and which values to give. The WHERE clause specifies which rows should be updated. If there is no WHERE clause, all rows are updated. If the ORDER BY clause is specified, the rows are updated in the order specified. The LIMIT clause is used to give a limit to limit the number of rows that can be updated.
5.8 Add fields
Command: alter table table name add field type other;
For example: a field passtest is added to the table MyClass, the type is int (4), the default value is 0
 mysql> alter table MyClass add passtest int (4) default '0'
Index
mysql> alter table table name add index index name (field name 1 [, field name 2…]);
example:

 mysql> alter table employee add index emp_name (name);
Add the primary key index
mysql> alter table table name add primary key (field name);
example:

mysql> alter table employee add primary key (id);
Index with unique constraints

mysql> alter table table name add unique index name (field name);
example:

mysql> alter table employee add unique emp_name2 (cardnumber);
Delete an index
mysql> alter table table name drop index index name;
example:

 mysql> alter table employee drop index emp_name;
Add field:
mysql> ALTER TABLE table_name ADD field_name field_type;
Modify the original field name and type:
mysql> ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;
Delete field:
MySQL ALTER TABLE table_name DROP field_name;

5.9 Modify Table Name

Command: rename table original table name to new table name;

For example: in the table MyClass name changed to YouClass
mysql> rename table MyClass to YouClass;
When you execute RENAME, you cannot have any locked tables or active transactions. You must also have ALTER and DROP permissions on the original table, and CREATE and INSERT permissions on the new table.

If MySQL encounters any errors during the multi-table renaming, it will roll back all the renamed tables and return everything to its original state.

RENAME TABLE was added in MySQL 3.23.23.
6, backup database
The command is executed in the DOS [url = file: // \\ mysql \\ bin] \\ mysql \\ bin [/ url] directory

1. Export the entire database
The export file is stored in the mysql \ bin directory by default.
mysqldump -u username -p database name> exported file name
 mysqldump -u user_name -p123456 database_name> outfile_name.sql
2. Export a table
mysqldump -u username -p database name table name> exported file name
 mysqldump -u user_name -p database_name table_name> outfile_name.sql
3. Export a database structure
mysqldump -u user_name -p -d --add-drop-table database_name> outfile_name.sql
-d no data --add-drop-table add a drop table before each create statement

4. Export with language parameters
 mysqldump -uroot -p --default-character-set = latin1 --set-charset = gbk --skip-opt database_name> outfile_name.sql
For example, back up the aaa library to the file back_aaa:
[Root @ test1 root] # cd / home / data / mysql
[Root @ test1 mysql] # mysqldump -u root -p --opt aaa> back_aaa
7.1 An example of building a database and building a table 1

drop database if exists school; // Delete if SCHOOL exists
create database school; // Create library SCHOOL
use school; // Open the library SCHOOL
create table teacher // Create table TEACHER
(
 id int (3) auto_increment not null primary key,
 name char (10) not null,
 address varchar (50) default ‘Shenzhen’,
 year date
); // End of table creation

// The following is the insertion field
insert into teacher values (", 'allen', 'Dalian First Middle School', '1976-10-10');
insert into teacher values (", 'jack', 'Dalian Second Middle School', '1975-12-23');

If you type the above command at the mysql prompt, it is also possible, but it is not convenient for debugging.
1. You can write the above command to a text file as it is, assuming school.sql, and then copy it to c: \\, and enter the directory in DOS state [url = file: // \\ mysql \\ bin] \\ mysql \\ bin [/ url], then type the following command:
mysql -uroot -p password <c: \\ school.sql
If successful, there is no display in a blank line; if there is an error, there will be a prompt. (The above command has been debugged, you can use it by removing the comment of //).

2. Or use mysql> source c: \\ school.sql after entering the command line; you can also import the school.sql file into the database.

7.2 An example of building a database and building a table 2

drop database if exists school; // Delete if SCHOOL exists
create database school; // Create library SCHOOL
use school; // Open the library SCHOOL
create table teacher // Create table TEACHER
(
 id int (3) auto_increment not null primary key,
 name char (10) not null,
 address varchar (50) default `` Shenzhen '',
  year date
); // End of table creation

// The following is the insertion field
insert into teacher values (`` '', `` glchengang '', `` Shenzhen No.1 Middle School '', `` 1976-10-10 '');
insert into teacher values (`` '', `` jack '', `` Shenzhen No.1 Middle School '', `` 1975-12-23 '');

Note: In the built table

1. Set the ID to a numeric field of length 3: int (3); and let it automatically add one to each record: auto_increment; and cannot be empty: not null; and let him become the primary key of the primary field.

2. Set NAME to a character field of length 10

3. Set ADDRESS to a character field of length 50, and the default value is Shenzhen.

4. Set YEAR as the date field.