Summary of basic MySql knowledge and basic mysql knowledge

Summary of basic MySql knowledge and basic mysql knowledge

Mysql Overview

MySql is a relational database.

Connect to the mysql database: mysql-u root-p

What is SQL: Structured Query statements

SQL classification:

DDL: Data Definition Language

* Create, alter, drop...

DML: data manipulation language

* Update, insert, delete

DCL: Data Control Language

* Grant, if ..

DQL: Data Query Language

* Select

Add, query, modify, and delete Databases

Create database: Syntax: create database [character Set collate verification rules]

View the database:

View All databases: show databases;

View A database: name of the show create database;

Modify database: Syntax: alter database name character Set collate checking rule

Delete database: Syntax: drop database name;

Switch Database: use Database Name;

View the currently used database: select database ();

Database Table operations

Table creation Syntax:

Create table Name (

Field name type (length) constraint,

Field name type (length) constraint,

Field name type (length) Constraints

);

Data Type:

Java type: MySQL:

Byte/short/int/long tinyint/smallint/int/bigint

String char/varchar

* Difference? Char is a string of Fixed Length and varchar variable length.

* Char (8) and varchar (8)

* If you insert a string hello into char, insert hello. Into varchar and insert hello.

Float

Double

Boolean bit

Date date/time/datetime/timestamp

* Datetime and timestamp are both date types with both date and time

* Difference? Datetime must use an externally passed date. If this value is not passed, it is Null. timestamp uses the current time of the system as the default value.

Text File Text

Binary File BLOB

* ***** Oralce uses CLOB/BLOB

* *** MYSQLExcept for the string type, the default length must be set for other types ******

• Constraints:

Single Table constraints:

* Primary key constraint: primary key (by default, it is unique and not empty)

* Unique constraint: unique

* Non-null constraint: not null

 

• Create a table:
• * ***** Select a database before creating a table: use a database;

Create table employee (

Eid int primary key auto_increment,

Ename varchar (20) not null,

Email varchar (30) unique,

Birthday date,

Job varchar (20 ),

Resume text

);

[View a table]

• View tables in the database:

* Show tables;

• View the table structure:

* Desc table name;

[Delete A Table]

• Table deletion:

Drop table name;

[Table modification]

• Modify the table to add columns:

* Alter tableTable Name addColumn nameType (Length)Constraints;

* Alter table employee add image varchar (50 );

• Modify the table deletion column:

* Alter tableTable Name dropColumn name;

* Alter table employee drop job;

• Modify the type length and constraints of a table's columns:

* Alter tableTable Name modifyColumn nameType (Length)Constraints;

* Alter table employee modify image varchar (80) not null;

• Alter table column name

* Alter tableTable Name changeOld column nameNew column nameType (Length)Constraints;

* Alter table employee change image eimage varchar (60 );

• Modify Table Name

* Rename tableOld table nameNew table name;

* Rename table employee to user;

• Modify the character set of a table:

* Alter tableTable Name character setCharacter Set;

* Alter table user character set gbk;

* ****** Use SQL statements to perform operations on table records in the database (CRUD operations on table records )******

Insert record]

• Syntax

* InsertTable Name (Column name,Column name,...) values (Value 1,Value 2 ,...);---Insert the value of a specified Column

* InsertTable Name values (Value 1,Value 2 ,...);---Insert values of all columns

 

• Note:

*The number of column names corresponds to the number of values.

*The column type corresponds to the value type.The location must also match.

*If the column type is string or date,When writing a value, use single quotes to cause the value.

*The maximum length of the inserted value cannot exceed the maximum length of the column.

[Change Record]

• Syntax:

* UpdateTable setColumn name =Value,Column name =Value [whereCondition];

• Note:

*The column names and values must be of the same type.

*The value cannot exceed the maximum length of the column.

*The value is a string or date,Single quotes are required.

• Exercise:

*Modify employeeAll recorded jobs in the tableFor WORKER

* Update employee set job = 'worker ';

*Modify the employee table to change the mailbox name aaa to aaa@163.com

* Update employee set email = 'aaa @ 163.com 'where ename = 'aaa ';

* Modify the employee table to change the mailbox name bbb to bbb@163.comModify at the same timeJobIsHR

* Update employee set email = 'bbb @ 163.com ', job = 'hr' where ename = 'bbb ';

[Delete records]

• Syntax:

* Delete fromTable [whereCondition];

• Note:

*Delete a row of records in the table,A column value cannot be deleted.

*If you have no conditions to delete all columns in the table.

• Exercise:

*Delete id8Record:

* Delete from employee where eid = 8;

*Delete all records:

* Delete from employee;

• Delete all records in the table truncate tableTable NameAnd delete fromTableDifference?

*Differences:

* Truncate tableDelete table records: Delete the entire table,Create a new table. truncateDDL.

* Delete fromDelete table records: delete one record at a time. deleteBelong to DML.

*Transaction ManagementIt can only work in DMLStatement.If delete is used in another transactionDelete all records and retrieve them.

[Basic query]

• Query statement:

* Select [distinct] * | column name from table [where condition];

[Condition query]

• The where statement can be followed:

Keyword of the condition:

=,>, >=, <, <=, <>

In like, you can use placeholders: _ and %: to match a character. %: can match any number of characters.

[Sort query]

'Order by: sorts data. The default value is ascending. (asc ascending, desc descending)

[Aggregate Function]

'Sum () summation

'Count () Statistics

'Max () Maximum Value

Minimum value of 'min ()

'Avg () mean

[Group]

Group

*Cross join:

* Select * from A, B; --- obtains the Cartesian product of two tables.

*Internal Connection: Inner join -- inner can be omitted

* Explicit inner join: select * from A inner join B on condition;

* Implicit inner join: select * from A, B where condition;

*External Connection: Outer join -- outer can be omitted

* Outer left join: left outer join -- select * from A left outer join B on condition;

* Outer right join: right outer join -- select * from A right outer join B on condition;

【Multi-Table subquery]

• One SQL statement depends on another query statement.

SELECT * FROM customer c, orders o WHERE c. cid = o. cid AND c. cid IN (SELECT cid FROM orders WHERE addr LIKE 'haidian % ');