Basic operations for SQL database

Command line
1. Displays the list of databases in the current database server:mysql> show DATABASES;
2. Set up Database:mysql> the name of Create library;
3, set up the data table:mysql> use library name;mysql> CREATE TABLE table name (field name VARCHAR (20), Field name CHAR (1));
4. Delete database:mysql> drop DB name;
5. Delete data table:mysql> drop table name;
6. Empty the table record:mysql> DELETE from table name;
7. Insert records into table:mysql> insert into table name VALUES ("HyQ", "M");
8. Update table Data:mysql-> Update table name SET field name 1= ' A ', field name 2= ' B ' WHERE field name 3= ' C ';
9.:mysql> Load Data local infile "d:/mysql.txt" into table name by loading data into table in text mode;
10. Import. sql file command:mysql> use database name;mysql> source D:/mysql.sql;
11, the command line to modify the root password:mysql> update mysql.user set Password=password (' New password ') where user= ' root ';mysql> flush privileges;
12. Three ways to change the password: mysql>update user set Password=password (' 123456 ') where user= ' joy_pen '; mysql>flush privileges; Mysql>set password for ' Joy_oen ' =password (' 123456 '), mysql>grant usage on *. * to ' joy_pen ' identified by ' 123456 ';
1. Create a database
Command: Create databases < database name > For example: To create a database named Xhkdb mysql> create databases xhkdb;
2. Show all databases
Command: Show databases (note: Finally there is a s) mysql> show databases;
3. Delete Database
Command: Drop databases < database name > For example: Delete database named Xhkdb mysql> drop DB xhkdb;
4. Connect to the database
Command: Use < database name > For example: If the XHKDB database exists, try to access it:mysql> use XHKDB; ScreenTip: Database changed
5, the current selection (connection) of the database mysql> Select Database ();
6, the current database contains table information:mysql> show tables; (Note: There is a last s)
Third, table operation, should connect a database before operation
1. Build 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 () 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>describe MyClass
mysql> desc MyClass;
Mysql> show columns from MyClass;
3. Delete a table
Command: DROP table < table name >
For example: Delete table named MyClass table mysql> drop table MyClass;
4. Inserting data
Command: INSERT into < table name > [(< Field name 1>[,.. < field name n >])] VALUES (value 1) [, (value N)]
For example, insert two records into table MyClass, which two records indicate that a score of 1, named Tom, is 96.45, 2 is named Joan, and 82.99 is named Wang with a score of 3.
mysql> INSERT INTO MyClass values (1, ' Tom ', 96.45), (2, ' Joan ', 82.99), (2, ' Wang ', 96.59);
5. Querying the data in the table
1), Query all rows
Command: Select < Field 1, field 2,...> from < table name > where < expression >
For example: View all data in table MyClass mysql> select * from MyClass;
2), query the first few rows of data
Example: Viewing the first 2 rows of data in a table MyClass
Mysql> SELECT * from MyClass ORDER by ID limit 0, 2;
6. Delete data from the table
Command: Delete from table name where expression
Example: Deleting a record with number 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 condition
mysql> Update MyClass set name= "Mary" where id=1;
8. Add a field to the table:
Command: ALTER TABLE name add field type other;
For example: Added a field passtest in table MyClass, type int (4), default value of 0
Mysql> ALTER TABLE MyClass add passtest int (4) default ' 0 '
9. Change the table name:
Command: Rename table name to new table name;
For example, change the name of the table MyClass to Youclass
Mysql> Rename table MyClass to Youclass;
Update Field Contents
Update table name set field name = new Content
Update table name set field name = Replace (field name, ' old content ', ' new content ');

Basic operations for SQL database