Common mysql commands

Mysql common command cutting 1. Table and database display 1. showdatabases; show all databases 2. usedbname; enter the database, ex: usetest; enter the test database. 3. status displays the current database and related data. 4. showtables: Show All Tables. when you enter a database, all tables in the database are displayed. 5. desctablename;

Mysql common command cutting 1. Table and database display 1. show databases; display all databases 2.use dbname; enter the database, ex: use test; enter the test database. 3. status displays the current database and related data. 4. show tables; all tables are displayed. when you enter a database, all tables in the database are displayed. 5. desc tablename;

Mysql common command editing

I. Table and database display

1. show databases; show all databases

2. use dbname; enter the database, ex: use test; and enter the test database.

3. status displays the current database and related data.

4. show tables; all tables are displayed. when you enter a database, all tables in the database are displayed.

5. desc tablename; displays the table structure. You can view table-related field settings.

2. Table Structure operations.

1. create table tablename (

?? -> Id int (4) primary key auto_increment,

?? -> Name varchar (16) not null,

?? -> Birthday date );

? The id of the newly created table is set as the primary key and the table is auto-incrementing. The name is varchar and cannot be empty.

2. alter table tablename

? -> Add classID int (2 );

? Update the table structure, and add a column of classID type to int.

3. alter table tablename

-> Drop classID;

Delete the column classID in the table.

4. alter table tablename

-> Change class classID? Int (4) (change the new column name type attribute of the old column name)

5. rename table test to testnew;

Update the table name and change the original test to testnew.

6. Add a primary key.

Alter table test

-> Add primary key (ID)

Add the column ID as the primary key of the test table.

7. Delete the primary key

Alter table test

-> Drop primary key

Delete the primary key in the test table and change it to a normal column.

8. Auto-increment Deletion

Alter table test

-> Change ID int (12 );

Delete auto-increment on the original ID.

?

Iii. Table Content Query

1. select * from test;

Find all content in the test table.

2. select name, studentID from test;

Check the name and studentID columns in the test table.

3. select * from test where name = "Denny ";

All information about the person named Denny in the test table.

4. select count (*) from test;

Check the total number of data entries in the test table.

5. select name, studentID from test order by studentID;

Locate the name and studentID columns in the test table and sort them by studentID.

6. select name, studentID from test group by name;

Check the name and studentID columns in the test table and use name as the Group. That is to say, the cognominal person only appears once.

7 select * from test limit 5;

Find the first five data entries in the test table.

8. select * from test limit 2, 8;

Find the data between 2nd and 8 in the test table.

9. select * from test order by name limit 2, 8;

Sort test by name and obtain the data between 2nd and 8.

10. select s. name, s. classID, c. classname from student s, class c where c. classID = 2 and s. sex = 'F ';

Perform a joint query to find out the names and class names of the persons whose classID is 2 and whose gender is F in the table class and student.

4. update the table content.

1. update test set name = "DDenny" where name = "Denny ";

Change the name of Denny in the test table to DDenny,

Note: The string can be directly written using the "" or "', int type.

V. insert data.

Insert into student

-> (Name, sex, classID) values ?? /* Note: it is best to enclose the column in parentheses to make it clearer.

-> ("YY", "F", 2 );?? /* Note: Pay attention to the column data type. First desc student should look at the table structure and the Data Types of each column.

Vi. delete data

Delete from student where name = "YY ";

Pay attention to the conditions after where to prevent deletion of objects that should not be deleted

?

?

?

?

?