Mysql common operations, table connection query, addition, deletion, modification, and query, mysql addition and Deletion

Mysql common operations, table connection query, addition, deletion, modification, and query, mysql addition and Deletion
I. Table creation 1. Simplest table Creation
Create table user (
Id int,
Name char (20 ),
Age int
);
2. Create a table with a primary key with comments and default values
Create table user (
Id int primary key AUTO_INCREMENT COMMENT 'set primary key auto incrementing ',
Name VARCHAR (200) default '1' comment' add default value to name 1 ',
Age int COMMENT 'I am a column annotate'
) COMMENT = 'I am a table annotate'

Ii. add, delete, modify, and query
1. Insert data
Add a data row at a time
Insert into 'user' ('id', 'name', 'age') VALUES
('4', '1', '2 ');
Add multiple rows at a time
Insert into 'user' ('id', 'name', 'age') VALUES
('4', '1', '2 '),
('5', '1', '2 ),
('6', '1', '2 '),;
Add data to a field
Insert into 'user' ('age') VALUES (6)
2. delete data (it is best to use less and delete data as soft as possible)
Delete a piece of data with id 1, where is followed by a range
Delete from user WHERE id = 1
Delete databases and tables (if you are ready to run)
Delete table: drop table table1; If a foreign key constraint cannot be deleted, you can use drop table table1 cascade constraints;
Delete database: drop database database1
Of course, the deleted table will still be in the recycle bin. You can restore the deleted table immediately.
3. Update
Update table1 set id = 1 where range
Set is followed by the value to be updated
4. Query
Select * from user where range
Iii. Table connection Query
There are two tables
User

Writers

 

1. INNER JOIN
SELECT * from user inner join writers on user. id = writers. id
A set of conditions that the user and writers meet is generated.

 

2. FULL JOIN
Mysql does not support full-connection queries, so it cannot be used directly.
SELECT * from user LEFT join writers on user. id = writers. id
UNION
SELECT * from user right join writers on user. id = writers. id
Use left join and union and right join to realize full join

In this way, the matching result is that if the user does not find the writers result, it will replace the writers table with null.

 

3. union usage
Union is used to remove duplicates. It can be used in this way, but the columns of the two main tables must be the same and the columns must have the same data type.
SELECT id FROM 'user'
UNION
SELECT id FROM writers

4. left join

Query all the data associated with the left table and the data associated with the right table. The right table is not replaced by null.

SELECT * from user left join writers on user. id = writers. id

5. right join and left join are similar.

SELECT * from user right join writers on user. id = writers. id