1, MySQL create data table

CREATE TABLE table_name (column_name column_type);

1. Create a library and set the character set encoding to utf8

create database 'Library name' default character set = 'utf8';

2. Create the table and set the character set encoding to utf8

create table 'Table name'(id int(6),name char(10)) default character set = 'utf8'; 　

example:

create table IF NOT EXISTS user (
	id int(11) not null AUTO_INCREMENT,
    name char(20) NOT NULL DEFAULT '' comment 'user name',
    primary key(id)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

AUTO_INCREMENT defines the attribute whose column is self incrementing. It is generally used for primary keys. The value will be automatically incremented by 1

PRIMARY KEY} keyword is used to define a column as a PRIMARY KEY; Multiple columns can be used to define the PRIMARY KEY, and the columns are separated by commas', '

ENGINE set storage ENGINE

CHARSET set encoding

3. Quick create table

create table table Table name 2 as select * from table Table name 1;

As follows:

create table emp2 as select * from emp;

Create a new query result of emp table as a table; You can copy tables quickly; The table is created and the data in the table is copied

You can also copy some fields of a table

create table mytable as select empno,empname from emp where job = 'salesman';

Note:

If the character set code is not specified as utf8 when creating the table, an error will be reported if Chinese is inserted when inserting data

mysql> insert into t_user values(3,'Wang Er Ma Zi','1992-06-18');
ERROR 1366 (HY000): Incorrect string value: '\xCD\xF5\xB6\xFE\xC2\xE9...' for column 'name' at row 1

2, MySQL delete data table

DROP TABLE table_name;

If the table does not exist in the database, an error will be reported:

mysql> drop table t_user;
ERROR 1051 (42S02): Unknown table 'mydb.t_user'

Therefore, you need to use the following statement to judge whether the table exists before deleting the table

drop table if exists table_name;

So you won't report an error

mysql> drop table if exists t_user;
Query OK, 0 rows affected, 1 warning (0.00 sec)

3, MySQL insert data

INSERT INTO SQL

1. Field names and values should correspond one by one; Quantity and data type should correspond

INSERT INTO table_name ( column_name1, column_name2,...column_nameN )
                       VALUES
                       ( value1, value2,...valueN );
Or (insert values directly without column names)
INSERT INTO table_name VALUES
                       ( value1, value2,...valueN );

example:

insert into user (id,name,) values(null,'Xiao Ming');
perhaps
insert into user values(null,'Xiao Ming');

There is no need to provide the data of the primary key id, because we have set this field when creating the table

AUTO_ The increment attribute. The field is automatically incremented without manual setting

Note:

When the field name is omitted, the values need to be written

As long as the insert statement is executed successfully, a record will be added to the table; If no value is specified for other fields, the default value is NULL

2. Batch insert data

insert into t_user(Field name 1,Field name 2) values(),(),(),();

As follows:

insert into t_user(id,name,birth,create_time) values
		(1,'zs','1992-10-11',now()), 
		(2,'lisi','1993-06-12',now()),
		(3,'wangwu','1999-08-16',now());

perhaps

insert into t_user values
		(1,'zs','1992-10-11',now()), 
		(2,'lisi','1993-06-12',now()),
		(3,'wangwu','1999-08-16',now());

3. Quickly insert data

insert into table Table name 2 select * from table Table name 1; 

As follows:

insert into dept_bak select * from dept; 

4, MySQL query data

SELECT

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]

One or more fields can be used in a query statement. The fields are separated by commas (,), and the WHERE statement is used to set query conditions.

The SELECT command can read one or more records

Using an asterisk (*) instead of other fields, the SELECT statement returns all field data of the table

Use the WHERE statement to include any conditions

Use the LIMIT property to set the number of records returned

Use OFFSET to specify the data OFFSET at which the SELECT statement starts the query. By default, the OFFSET is 0

1. There are two ways to query all fields

[1] Each field is written with a comma (,) in the middle

select a,b,c,d from tablename;

[2] Use an asterisk (*) instead of other fields

select * from tablename;

Note:

Disadvantages of using asterisk (*):

1. Low efficiency; (*) asterisks are also used to replace all fields in the table before querying

2. Poor readability

select and from are both keywords

Both field and table names are identifiers

example:

select * from table_name;

2. distinct remove duplicate records

 select distinct job from emp;

 

Note:

[1] select distinct query the original table data will not be modified, but the query results will be de duplicated

[2] distinct can only appear at the front of all fields

select empname,distinct job from emp;

An error will be reported as follows:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct job from emp' at line 1

 

(1) distinct appears before the two fields, indicating that the two fields are combined to remove duplication

select distinct job,deptno from emp;

(2) distinct can be used with grouping functions

 select count(distinct job) as jobcount  from emp;

 

5, MySQL WHERE clause

Use the WHERE clause to read data from the data table

SELECT column1, column2,...columnN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....

example:

select * from user where id = '1';

One or more tables can be used in a query statement, separated by commas, and WHERE statements can be used to set query conditions.
You can specify any condition in the WHERE clause.
You can specify one OR more conditions using AND OR.
The WHERE clause can also be applied to SQL DELETE or UPDATE commands.
The WHERE clause is similar to the if condition in the programming language. It reads the specified data according to the field values in the MySQL table.

Operator	describe	example
=	The equal sign detects whether the two values are equal. If they are equal, it returns true	(A = B) returns false.
<>, !=	If not, check whether the two values are equal. If not, return true	(a! = b) returns true.
>	Greater than sign, check whether the value on the left is greater than the value on the right. If the value on the left is greater than the value on the right, return true	(a > b) returns false.
<	The less than sign checks whether the value on the left is less than the value on the right. If the value on the left is less than the value on the right, it returns true	(a < b) returns true.
>=	The greater than or equal sign checks whether the value on the left is greater than or equal to the value on the right. If the value on the left is greater than or equal to the value on the right, it returns true	(a > = b) returns false.
<=	The less than or equal sign checks whether the value on the left is less than or equal to the value on the right. If the value on the left is less than or equal to the value on the right, it returns true	(a < = b) returns true.

Is null is null; is not null is not empty

Null cannot be measured with the equal sign "=" in the database; is null is required

null in the database represents nothing. It is not a value, so it cannot be measured with the equal sign "="

String comparison in WHERE clause of MySQL is case insensitive;; You can use the BINARY keyword to set the string comparison of the WHERE clause to be case sensitive

ELECT * from user WHERE BINARY name='xiaoming';

6, MySQL UPDATE

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

You can update one or more fields at the same time
You can specify any condition in the WHERE clause
Data can be updated simultaneously in a single table

Note:

No conditional restrictions will cause all data to be updated

7, MySQL DELETE statement

DELETE FROM table_name [WHERE Clause]

If the WHERE clause is not specified, all records in the MySQL table will be deleted
You can specify any condition in the WHERE clause
Records can be deleted once in a single table

1. Delete recoverable

(1) Execute {start transaction before deletion;

start transaction;

The delete command is in progress

If you want to recover, execute rollback;

rollback;

Data recovery can be seen when querying

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from dept_bak;
Query OK, 8 rows affected (0.00 sec)

mysql> select * from dept_bak;
Empty set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from dept_bak;
+--------+------------+-----------+
| DEPTNO | DEPTNAME   | CITY      |
+--------+------------+-----------+
|     10 | ACCOUNTING | BEI JING  |
|     20 | PURCHASE   | TIAN JIN  |
|     30 | SALES      | SHANG HAI |
|     40 | HR         | SU ZHOU   |
+--------+------------+-----------+
8 rows in set (0.00 sec)

2. Delete unrecoverable truncate

Execute the following command

truncate table table Table name;

mysql> truncate table dept_bak;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from dept_bak;
Empty set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

Principle of delete statement deleting data
The data in the table is deleted, but the real storage space of this data on the hard disk will not be released
The disadvantage of this deletion is that the deletion efficiency is relatively low
The advantage of this deletion is that it supports rollback and can recover data again
    
Principle of truncate statement deleting data
This deletion is more efficient. The table is truncated at one time and physically deleted
Disadvantage of this deletion: rollback is not supported
Advantages of this deletion: fast

truncate is to delete the data in the table, and the table is still there

3. Logical deletion and physical deletion

(1) Logical deletion (soft deletion): logical deletion is to mark the data to be deleted with a deletion mark, usually using an is_ The deleted field indicates whether the row record is deleted (or a status field is used to represent the so-called "deleted" status). Logically, the data is deleted, but the data itself still exists

(2) Physical deletion

It refers to that the magnetic storage area used for file storage is really erased or cleared, so the deleted files cannot be recovered. Physical deletion is a concept when the computer processes data. If the table data is deleted in the database directly by delete, drop and truncate, it is difficult to recover the data without backup.

Note:

If there are no restrictions, all the data in the whole table will be deleted!