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.
drop database bjpowernode;
View table structure:
View which database is currently in use
View the version number of mysql
\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)
asc indicates ascending order and desc indicates descending order.
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
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.
External connection: (left external connection / left connection)
External connection features: unconditionally query all the data in the main table.
select ..(select). from ..(select). where ..(select).
Query result set addition
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 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 from Table name where condition;