MySQL Basic operation Summary--additions and deletions to check

This article only summarizes the basic operation of MySQL, adding and removing changes, so that you can forget the time to check

1. Create a database

Syntax: Create DATABASES database name; Example: Create DATABASES student;

2. Deleting a database

Syntax: drop database name; example: Drop DB student;

3: Set MySQL Encoding: (Query in cmd window)

Recommendation: Each login to MySQL is the execution of this statement

Set name GBK

4. Create a data table

 //  syntax:   //  (unsigned/no negative)   Field Name 2 data type (length) constraints,  //   //  example:  create table student (ID  int  (20  ) primary Key,name varchar ( 11   float  ( 20  ))  

5. Copy the structure of a known table

Syntax: CREATE table new table name like old table name example: CREATE table studens_new like student;

6. Data Sheet Insert Data

6.1 Use INSERT keywords insert data for data tables

//Syntax:insert into Data table name (field name 1, field name 2,) VALUES (value 1, value 2,), values (value 1, value 2,);//Example:INSERT into student (Id,name) VALUES (1,'Xiao Ming'), VALUES (2,'Zhang San');//orINSERT into student values (1,'Xiao Ming'), (2,'Zhang San');

6.2. Inserting data into a data table using the insert 's SET keyword

set field 1= value 1, field 2=set id=2, name=' xiaoming ';

7. Update data
UPDATE  .... SET keyword Update data

// Syntax: set field 1= value 1, field 2= value 2 .... where conditional expression; // Example: (1set grade=, name=' xiaoming 'where id=1  ; (update part of data) (2set grade=;(Update all data)

8. Delete Data

Delete data using the delete keyword

// Syntax:  from where The condition limits; // Example: (1fromwhere id=2; (delete part of data) (2 from Student; (Delete all data)

9. Delete data using the TRUNCATE keyword

// Syntax:  from table name; // Example:  from student;

10. Delete a data table using the DROP keyword

// Syntax: drop table name; // Example:drop table student;

11. Use the Alter...rename keyword to modify the data table name (RENAME)

// Syntax: ALTER TABLE old table name rename to new table name; // Example:ALTER TABLE student rename to students;

use alter : . Modify keyword modifies the data type of a field (MODIFY)

// Syntax: ALTER TABLE name MODIFY the data type to be modified by the field name; // Example: int (a);

13. Add a field to the data table using the Alter...add keyword (add)

// syntax:ALTER TABLE name ADD new field name data type () constraint first/afterexisting field; // Example: (1) ALTER TABLE student add sex varchar (2) ALTER TABLE student add sex Varch AR (one) after ID;

14. Delete a field using the Alter...drop keyword (drop)

// Syntax: ALTER TABLE Name drop field name, drop field name ...; // Example: (1) alter TALBE student drop grade; 2) ALTER TABLE name add sex First,drop grade; (Add Sex field at first, delete grade field at the same time)

15. Use the AS keyword to alias a data table or field (without modifying the table name)

//alias a data tableGrammar:Select* fromStudent asAn example of an alias;Select* fromStudent as 'Student Table';
//alias the field (without modifying the field name)Grammar:SelectField 1 asAlias 1, Field 2 asAlias 2 ... froma table name; an example:SelectId as 'School Number', name as 'name'  fromStudent

16. Enter the database from the system (CMD):

Mysql-hlocalhost-uroot-p

17. Open the Database

// Syntax: Use database name; // Example:use student;

18. Check which database is currently open or whether the database has been successfully opened

Select Database ();

19. View all data tables in the current database

20. Specify to view one of the data tables in the current database

Syntax: Show table data table name; Example: Show table student;

21. View all data tables in MySQL

 from MySQL;

22. View the structure of the data table

// syntax:(1from  data table name; (2) DESC data table name;// Example:( 1  from student; ( 2

23. View all data/records of the data sheet

Syntax:Select* from data table name; Example:Select * from student;

9. View the index of the data table

from student; 

MySQL Basic operation Summary--additions and deletions to check