Introduction to the basics of mysql

1. Start and exit
1. Enter MySQL:
Launch MySQL Command Line Client (DOS interface for MySQL) and enter your password for installation directly. The prompt at this point is: mysql >
Or open the terminal and enter SQL statement:
mysql � uroot � p123

2. Exit MySQL:
quit or exit

2. The library operation
1. Create a database
Command: create database < The database name >
For example, create a database called 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 the database
Command: drop database < The database name >
For example: delete the database named xhkdb
mysql > drop database xhkdb;

4. Connect to the database
Command: use < The database name >
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. Table information contained in the current database:
mysql > show tables; (note: there is an s at the end.)

7. Create users and grant them rights:
grant all privileges on dbname.* to username @localhost identified by 'pwd123';
Give username, a user of the localhost domain, all rights to the dbname database with password pwd123.

3. Table operation, which should be connected to a database before operation
1, build table
Command: create table < The name of the table > ( < The field name 1 > < Type 1 > [,.. < The 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 the table
Command: drop table < The name of the table >
For example: drop the table named MyClass
mysql > drop table MyClass;

4. Insert data
Command: insert into < The name of the table > [( < The field name 1 > [,.. < The field name n > ])] values (value 1)[, (value n)]
For example, insert two records into table MyClass. These two records indicate that the score of 1 named Tom is 96.45, that of 2 named Joan is 82.99, and that of 3 named Wang is 96.5.
mysql > insert into MyClass values(1,'Tom',96.45),(2,'Joan',82.99), (2,'Wang', 96.59);

5. Query the data in the table
1) query all rows
Command: select < Field 1, field 2... > from < The name of the table > where < expression >
For example, view all the data in table MyClass
mysql > select * from MyClass;
2) query the first few rows of data
For example, look at the first two rows of table MyClass
mysql > select * from MyClass order by id limit 0,2;

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

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

8. Add fields to the table:
Command: alter table table name add field type other;
For example, a field passtest of type int(4) has been added to the table MyClass, with a default value of 0
mysql > alter table MyClass add passtest int(4) default '0'

9. Change the table name:
Command: rename table to new table name;
For example: in table MyClass the name 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);

4. Field type introduction
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 the DATE value in YYYY-MM-DD format, but allows you to assign the value to the DATE column using a string or number
4. CHAR(M) : a fixed-length string that, when stored, is always filled with Spaces to the right of the specified length
5. BLOB TEXT type with a maximum length of 65535(2^16-1) characters.
6. VARCHAR: variable length string type

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

Export 1 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-wcnc-p-d -- add-drop-table smgp_apps_wcnc > d:wcnc_db.sql
-d has no data and add-drop-table adds 1 drop table before each create statement

4. Import database
The source command is commonly used
Go to the mysql database console,
For example, mysql-u root-p
mysql > use database
Then use the source command, followed by the script file (as used here.sql)
mysql > source d:wcnc_db.sql