1. Start and exit
1. Enter MySQL:
Start MySQL Command Line Client (MySQL DOS Interface) and enter the password for installation. The prompt is: mysql>
Or open the terminal and enter the SQL statement:
Mysql-uroot-p123
2. Exit MySQL:
Quit or exit
Ii. Database Operations
1. Create a database
Command: create database <database Name>
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 <database Name>
For example, delete a database named xhkdb.
Mysql> drop database xhkdb;
4. Connect to the database
Command: use <Database Name>
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)
7. Create a user and grant it the following permissions:
Grant all privileges on dbname. * to username @ localhost identified by 'pwd123 ′;
The username of the localhost domain is used to manage all permissions of the dbname database. The password is pwd123.
Iii. Table operations. A database should be connected before operations
1. Create a table
Command:Create table <table Name> (<field name 1> <type 1> [,... <field name n> <type 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 <table Name>
For example, delete a table named MyClass.
Mysql> drop table MyClass;
4. 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 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 <Field 1, Field 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;
8. 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'
9. 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 );
Iv. Field Types
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
V. Database Backup
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