Summary of basic knowledge of mysql

This article brings you a summary of the basic knowledge of mysql. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

1. Startup and Exit

1. Enter MySQL:

Start MySQL Command Line Client (MySQL DOS interface), directly Just enter the password you used during installation. The prompt at this time is: mysql>

or open the terminal and enter the SQL statement:

mysql –uroot –p123

2. Exit MySQL:

quit or exit

2. Library operations

1. Create database

Command: create database e5c249aa15ff4c64720f99bfaecf8a60
For example : Create a database named xhkdb
mysql> create database xhkdb;

2. Display all databases

Command: show databases (note: there is an s at the end)
mysql> show databases;

3. Delete database
Command: drop database e5c249aa15ff4c64720f99bfaecf8a60
For example: delete the database named xhkdb
mysql> drop database xhkdb;

4. Connect to the database
Command: use e5c249aa15ff4c64720f99bfaecf8a60
For example: if the xhkdb database exists, try to access it:
mysql> use xhkdb;
Screen prompt: Database changed

5. The currently selected (connected) database
mysql> select database();

6. The table information contained in the current database:
mysql> show tables; ( Note: there is an s at the end

3. Table operation, you should connect to a database before the operation
1. Create table
Command: create table aa5ac8827a26d90b8d62b9ea710626d5 260b325d7b3ba36c3f7ee2f9c28ff541 [,..f686814ccc99d03e4e7d813be0ec2251 7092c9317cb5623026dd4ad87bcc5192]);

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 table structure
command : desc table name, or show columns from table name

mysql> desc MyClass;
mysql> show columns from MyClass;

3. Delete table
Command: drop table ccc43248daffbac9770dee47fdaff697
For example: delete the table named MyClass
mysql> drop table MyClass;

4. Insert data
Command: insert into 722e3d59fd24604761db25f00f9b264f [( d3e586496c91690a051d0f85cf062cd4[,..9256253ede6e01528fd7f672eeb0c771 from 2b3cb27b56aa39d12a03ed68d9a42b20 where e5cecc6c602dbb26d18b4c9e17db272c
For example: view all data in 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;

6. Delete Data in the table
Command: delete from table name where expression
For example: delete the record numbered 1 in the table MyClass
mysql> delete from MyClass where id=1;

7, Modify the data in the table:
update table name set field = new value,... where condition
mysql> update MyClass set name='Mary' where id=1;

8. Add to the table Field:
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'

9. Change the table name:
Command: rename table original table name to new table name;
For example: change the name of table MyClass 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 4 spaces in front of the article
update article set content=concat(' ',content);

4. Introduction to field types
1 ． INT[(M)] type: normal size integer type
2． DOUBLE[(M,D)] [ZEROFILL] type: Normal size (double precision) floating point number type
3. DATE 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 use strings or numbers to assign values ​​to DATE columns
4. CHAR(M) type: fixed-length string type. When stored, the right side is always filled with spaces to the specified length
5. BLOB TEXT type, the maximum length is 65535 (2^16-1) characters.
6. VARCHAR type: variable length string type

5. Database backup
1. Export the entire database
mysqldump -u username-p --default-character-set= latin1 database name> Exported file name (the default encoding of the database is latin1)
mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql

2. Export a table
mysqldump -u user name -p database name table name> exported file name
mysqldump -u wcnc -p smgp_apps_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 database
Commonly used source commands
Enter the mysql database console,
For example, mysql -u root -p
mysql>use database
Then use the source command, the following parameters are Script file (such as the .sql used here)
mysql>source d:wcnc_db.sql

Related recommendations:

mysql basic knowledge ( mysql tutorial for newbies)

Summary of the basic knowledge of mysql

PHP and MySQL basic tutorial (1)

PHP and MySQL Basic Tutorial (2)

PHP and MySQL Basic Tutorial (3)

PHP and MySQL Basic Tutorial ( 4)

mysql manual tutorial: http://www.php.cn/course/37.html

mysql video tutorial: http ://www.php.cn/course/list/51.html