MySQL table structure definition and related syntax

MySQL syntax and related commands
1. Each SQL command needs to use a semicolon to complete
2. You can write a command into multiple lines
3. You can cancel our order by \c
4. You can exit the current client by \g, exit, CTRL + C, or quit
5. You can query shortcut keys by using the Help command
6. \g the data after the query
7. Show current server Status \s
8. Change the performer \d
9. Use the query command help, such as create

Database related operations:
Create DATABASE [if not EXISTS] databasename creating a db named DatabaseName

Show Databases View Database

Drop database DatabaseName Delete databasename databases

Select Database () view current databases

Use databasename using the DatabaseName database, you must select the database before creating the table

Data table related operations:
1. Create a table
CREATE TABLE table_name (
Field Name field type [Field Properties] [index],
Field Name field type [Field Properties] [index]
) Table engine Character Set

2. View table structure
DESC table_name

3. View the Build Table statement
Show CREATE TABLE table_name

4. Delete the table (both the table and the data are deleted)
DROP TABLE table_name

5. Clear the table (the table is still there, just no data, equivalent to delete the table and data and then re-build a structure of the table)
TRUNCATE TABLE_NAME

To modify a table structure:
Add a table field
ALTER TABLE table_name ADD Field Name field Type field property

Delete a table field
ALTER TABLE table_name DROP field name

Modify a table field
ALTER TABLE table_name change old field name new Field Name field Type field property

ALTER TABLE table_name modify old Field Name field Type field property

Modify Table Name
ALTER TABLE Oldtablename Rename as Newtablename
Change the table named Oldtablename to Newtablename

Table Index
Primary KEY index: PRIMARY key only one primary key can exist in a table, no, and the primary key value cannot be empty
Unique index: Unique The value of this column cannot be duplicated, there can be multiple unique indexes in a single table
General Index: Index
Full-text index: fulltext

View the current table index
Show indexes from table_name

Add index
ALTER TABLE table_name Add index/unique/primary key (field name)

Table Index related operations
Delete primary key index
Remove the auto-Grow attribute first, then delete the primary key index
A. Modifying a table field with alter to remove the auto-Grow attribute
B.alter table table_name Drop PRIMARY key

Both the unique index and the regular index are deleted using the index
ALTER TABLE table_name DROP INDEX IndexName
If you do not specify an index name (indexname), you can use the field name

Create a new user and authorize
Grant privileges on databasename.tablename to [email protected] identified by password
Example: Grant Select,insert,update,delete on * * to ' zhangsan ' @ '% ' identified by ' 123 '
Authorized user Zhangsan, allowing him to log in from any host via password ' 123 ', the TableName data table in the database databasename
have permission to increase and revise checks

MySQL table structure definition and related syntax