Basic concepts

DB:DataBase (database, which actually exists as a file on the hard disk)
DBMS: DataBase Management System MySQL Oracle
SQL: structured query language, is a standard general language. When the SQL statement is executed, it will actually be compiled internally before executing the SQL. DBMS is responsible for executing SQL statements and operating the data in DB by executing SQL statements.
SQL statement classification:
1. DQL (data query language): query statements. All select statements are DQL.
2. DML (data operation language): insert delete update to add, delete and modify the data in the table.
3. DDL (data definition language): create drop alter, which is used to add, delete and change the table structure.
4. TCL (transaction control language): commit the transaction, rollback the transaction. (T in TCL is Transaction)
5. DCL (data control language): grant authorization, revoke authority, etc.

Delete database:

drop database bjpowernode;

View table structure:

desc dept;

View which database is currently in use

select database();

View the version number of mysql

select version();

\c command to end a statement.

Exit command to exit mysql.

To view the statement that created the table:

show create table emp;

Simple query statement (DQL)

	select Field name 1,Field name 2,Field name 3,.... from Table name;
Condition query
select 
		field,field...
	from
		Table name
	where
		condition;

between and must be small on the left and large on the right.

Fuzzy query like

			%Represents any number of characters,_Represents any 1 character.

Sort (ascending, descending)

order by
asc indicates ascending order and desc indicates descending order.

Grouping function

count count
sum Sum
avg average value
max Maximum
min minimum value

group by and having

group by :  Group by a field or some fields.
having : having Is to filter the grouped data again.

DQL statement execution order

select		5
from			1	
where			2
group by		3
having		4
order by		6

De duplication distinct

Connection table

	Internal connection:
		Equivalent connection
		Non equivalent connection
		Self connection
	External connection:
		Left outer connection (left connection)
		Right outer connection (right connection)
Internal connection:
	hypothesis A and B Table connection, if internal connection is used, usually A Table and B The table can be queried by matching the records on the table, which is the inner connection.
	AB There is no distinction between the two tables. The two tables are equal.
External connection:
	hypothesis A and B Table connection. If external connection is used, AB One of the two tables is the primary table and the other is the secondary table. It is mainly used to query the primary table
	When the data in the secondary table does not match the data in the main table, the secondary table will be simulated automatically NULL Match it.
	Left outer connection (left connection): indicates that the table on the left is the main table.
	Right outer connection (right connection): indicates that the table on the right is the main table.

Internal connection:
select
**
from
surface
join
Table name
on
**
External connection: (left external connection / left connection)
select
**
from
surface
left join
surface
on
**

External connection features: unconditionally query all the data in the main table.

Subquery

select
		..(select).
	from
		..(select).
	where
		..(select).

Query result set addition

select ***
union
select ***;

paging

 limit startIndex, length
	startIndex Represents the starting position, starting from 0, and 0 represents the first data.
	length Indicates how many to take

Create table:

    create table Table name(
		Field name 1 data type,
		Field name 2 data type,
		Field name 3 data type,
		....
	);

Common data types in MySQL
Int integer type (int in java)
bigint long (long in java)
Float (float double in java)
char fixed length string (String)
varchar variable length string (StringBuffer/StringBuilder)
Date date type (corresponding to java.sql.Date type in Java)
BLOB Binary Large OBject (storing pictures, videos and other streaming media information) Binary Large OBject (corresponding to Object in java)
CLOB Character Large OBject (storing large text, for example, 4G string.) Character Large OBject (corresponding to Object in java)
How to choose between char and varchar?
In the actual development, when the data length in a field does not change, it is fixed. For example, char is used for gender and birthday.
When the data length of a field is uncertain, such as introduction, name, etc., varchar is used.
Use of BLOB and CLOB types?

insert statement inserts data

insert into Table name(Field name 1,Field name 2,Field name 3,....) values(Value 1,Value 2,Value 3,....)

Replication of tables

	create table Table name as select sentence;

Insert query results into a table

insert into Target table select * from Look up table;

Modify data: update

	update Table name set Field name 1=Value 1,Field name 2=Value 2... where condition;

Delete data

	delete from Table name where condition;