1. Connect to the database
Format: mysql-u username-P Password
Ii. Change the password
Format: mysqladmin-u username-P old Password New Password
Iii. database usage
Format: Use Database Name
4. display tables in the database
Format: Use Database Name;
Show tables;
V. display table structure
Format: Describe table name;
6. Create a table
Drop database if exists school; // Delete if school exists
Create Database school; // create a database School
Use school; // open the school library
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
); // Table creation ends
VII. Insert a table
Insert into teacher values ('', 'glengang ', 'shenzhen Zhongyi', '2017-10-10 ');
Insert into teacher values ('', 'jack', 'shenzhen Zhongyi ', '2017-12-23 ');
8. Import text data to the database
Text Format: fields are separated by tabs;
Format: load data local infile "file name" into Table table name;
9. Import/Export data
Import table: mysql> Create Database School;
Mysql> use school;
Mysql> source school. SQL;
(Or change school. SQL to school. Teacher. SQL/school. Teacher. Student. SQL)
Export Database:
Mysqldump -- databases db1 DB2> db1.db2. SQL
Note: Back up database DBL and DB2 to the db1.db2. SQL file. db1.db2. SQL is a text file with a file name.
Choose, open it, and you will see new discoveries.
(For example:
Mysqldump-H host-u user-P pass -- databases dbname> file. Dump
Import the database dbname on the host named "user" and "password pass" to file. Dump .)
Import Database: mysql <db1.db2. SQL
Copy a database:
Mysqldump -- all-databases> all-databases. SQL
Note: backing up all databases to a all-databases. SQL file, a all-databases. SQL is a text file,
File Name
10. Create an index
Create an index using the first 10 characters of the name column:
Create index part_of_name on customer (name (10 ));