My simple MySQL statements

1. Enter the Database Password root through the command line
2. Create my database mydatabase
Create Database mydatabase;
3. Create a table userinf under the database mydatabase
First use mydatabase;
Create Table userinf (user_id int primary key, user_name varchar (20 ));
4. insert data into the database
Insert into userinf values (1, 'yuan wei ');
Insert into userinf values (2, 'wang wei ');
Insert into userinf values (3, 'dai jianghua ');
Insert into userinf values (4, 'zhang Zhihui ');
Insert into userinf values (5, 'Liu yuan ');
Insert into userinf values (6, 'hangzhou Donghai ');
Insert into userinf values (7, 'vinn ');
Insert into userinf values (9, 'shaofeng ');
Insert into userinf values (10, 'wu Donghai ');
5. Submit after creation
Commit
6. modify a record (Change the name with id 10 to Wu)
Update userinf set user_name = 'wu wu' where id = 10;
7. delete a record (delete the record with ID = 10)
Delete from userinf where user_id = 10;

8. Add a column (for example, add a column of age)
Alter table userinf add column (age INT); (a column is added to the end by default)
Alter table userinf add columnage int after Id; (specify to add a column after the ID)
9. delete a column (delete the age column)
Alter table userinf drop age;
 
10. Modify the field name type (change age to gender sex, data type char)
Alter table userinf change age sex char;
11. No foreign keys are set for creating a table.
Create Table articles (ID int primary key, content longtext not null, userid INT );
Add a foreign key to the command line
Alter table articles add constraint foreign key (userid) References userinf

(User_id) on Delete set NULL; (set null is the data associated with the deletion)
12. Set a foreign key when creating a table
Create Table articles (ID int primary key, content longtext not null, userid

Int, constraint foreign key (userid) References userinf (user_id ));
13. Create a view
Create view view_userinf (ID, name) as select * From userinf;
14. delete a view
Drop view view_userinf;
15. Create an index
Create index index_user on userinf (user_id );
16. delete an index
Drop index index_user on userinf (user_id); (more on userinf (user_id) than Oracle ))
17. Create a stored procedure and call
First, change the Terminator.
 
Mysql> delimiter // (replaced by //; indicates the end symbol)
Mysql> Create procedure my (Out param1 varchar (20 ))
-> Begin
-> Select user_name into param1 from userinf where user_id = 1;
-> End
-> //
Mysql> call my (@ B)
-> //
Mysql> select @ B //
18. Create a trigger (the trigger is a table-related named database object. When a specific event occurs on the table, this object is activated.

. For example, an event occurs when we insert a row of data to a table or an event is triggered when a record is deleted)

Example: Create a numuser table. When a data entry is inserted into the userinf table, numuser's num plus 1 is triggered.

First create a table numuser
Create Table numuser (Num int not null default 0 );
Create a trigger
Mysql> delimiter //
Mysql> Create trigger testnum after insert on userinf for each row
-> Begin
-> Update numuser set num = num + 1;
-> End
-> //
19. Command Line command: delimiter // indicates that the end ends //