Detailed table operation:
1. Modify table name:
alter table old table name rename new table name
2. Modify Table engine and character encoding:
alter table table name engine =’engine name’ charset=’encoding name’;
3. Copy table
create table new table name like old table name;
create table new table name select * from table name where condition;
4. Clear table:
truncate table Name:
The table is emptied, including self-incremented fields
Detailed operation of the fields in the table:
1. Need to change the field information:
< p>alter table table name modify field name type [(width) constraint];
2. Modify field name and information:
alter table table name change old field name new field name Type [(width) constraint];
3. Add field name:
(Add at the end)
alter table table name add field name type [(width ) Constraint],add field name type[(width) constraint];
(header add)
alter table table name add field name type[(width) constraint] first;< /p>
(Add at specified location)
alter table table name add field name type [(width) constraint] after old field name;
4. Delete field name:
Alter table table name drop Field name:
Special table user management
Operation prerequisite: log in as root user
1. Important fields :
Host|User|Password
2. New user
create user username@hostname identified by’password’;
insert into mysql.user(Host,User,password) values (“host name”,’user name’,password(‘password’));
3. Set user permissions
< p>grant permissions on the database name. Table Name to username@hostname[with grant option];
Permissions include select, delete, update, insert, drop, all represent all permissions
The database name and table name can be replaced with * , On behalf of all
When setting permissions, if there is no current user, users will be automatically created, and it is recommended to use
4. Revoke permissions
revoke permission name on database name. Table name from username@hostname;
5. Modify password:
set password for username@hostname = password(‘new password’)(ciphertext)
p>
6.Delete user
drop user username@hostname;
Table relationship:
The establishment of the table structure of the mysql database is the data of the table For classification management
The relationship between the tables created by mysql (code level) needs to be dealt with
There are many relationships: many-to-many, one-to-one, One-to-many
Many-to-one:
The rule of table building: first create the master table, and then set the unique field of the master table in the slave table (usually the primary key) As a foreign key
Insert record rules: insert the main table data first, then insert the secondary table data
Update and delete data:
The two tables affect each other,< /p>
Many-to-many:
Rules for table building: Create a third table and form a many-to-many relationship through two foreign keys
One-to-one:
p>
Rules for table building: The table that does not store the foreign key is dependent, it is called the left table, and the table that stores the foreign key is represented as the dependent table, which is called the right table. The left table is operated first, and the right table is operated.