Objectives of this unit

First, why to learn database

2. Related concepts of database

DBMS, DB, SQL

Three, the characteristics of database storage data

MySQL > initialize MySQL

Introduction to MySQL products

MySQL product installation ★

Start and stop the MySQL service

Logging in and out of the MySQL service ★

MySQL common commands and syntax specifications

DQL language learning ★

Basic Query

Conditional Query ★

Sort query ★

Common functions

Grouping function

Group Query ★

Connection query

The subquery)

Paging query ★

    unionJoint Query √



6. DML Language learning

Insert statement

Modify the statement

Delete statements

7. DDL language learning

Library and table management √

Common data types √

Common constraints √

8. TCL Language learning

Transactions and transaction processing

Explanation of views √

Ten, variables,

Stored procedures and functions

Xii. Process control structure

Copy the code

Benefits of databases

1. Persist data to the local PC

2. Structured query can be implemented for easy management

Copy the code

Database related Concepts

1. DB: database, a container for storing a group of organized data

2, DBMS: database management system, also known as database software (product), used to manage the data in DB

3, SQL: structured query language, used to communicate with DBMS language

Copy the code

The characteristics of a database for storing data

1. Put data into tables, and tables into libraries

2, a database can have multiple tables, each table has a name, used to identify itself. Table names are unique.

3. Tables have properties that define how data is stored in a table, similar to the Design of "classes" in Java.

4. A table consists of columns, also known as fields. All tables are composed of one or more columns, each of which is similar to the "properties" in Java

5. The data in a table is stored in rows, each row similar to an object in Java.

Copy the code

Introduction and installation of MySQL products

Start and stop the MySQL service

Method 1: Computer - right click Manage - Services

Method 2: Run this command as an administrator

net startService Name (Start service)

net stopService Name (Out of service)

Copy the code

Logging in and out of the MySQL service

Method 1: Use the mysql client

Only root user



Method 2: Use the Windows client

Login:

Mysql [-h host name -p port number] -u user name -p password



Exit:

exitOr CTRL + C

Copy the code

MySQL common commands

1. View all the current databases

show databases;

2. Open the specified library

useThe library

3.View all tables in the current library

show tables;

4. View all tables in other libraries

show tables fromThe library;

5. Create the tables

create tableThe name of the table (



Column name column type,

Column name column type,

.

);

6. View the table structure

Desc table name;





7. Check the server version

Method 1: Log in to the mysql server

select version(a);

Method 2: You have not logged in to the mysql server

mysql --version

or

mysql --V

Copy the code

MySQL syntax specification

1. You are advised to uppercase the keyword and lowercase the table name and column name

2. End each command with a semicolon

3. You can indent or wrap each command as required

4. Comment

One-line comment:# comment text

One-line comment:-- Comment text

Multi-line comments:/* Comment the text */

Copy the code

SQL language classification

Data Query Language (DQL) : Data Query Language

    select 

DML(Data Manipulate Language): Data manipulation language

    insert 、update,delete

DDL(Data DefineLanguge) : Data definition language

    create,drop,alter

TCL (Transaction Control Language) : Transaction control language

    commit,rollback

Copy the code

Common SQL commands

show databases; View all databases

useThe library; Opens the specified library

show tables; Displays all tables in the library

show tables fromThe library; Displays all tables in the specified library

create tableThe name of the table (

Field name Field type,

Field Name Field type

); Create a table



Desc table name; View the structure of the specified table

select * fromThe name of the table. Displays all data in the table

Copy the code

DQL language learning

Step 1: Basic query

Grammar:

SELECTThings to look up

【FROMThe name of the table];



Similar to Java: system.out.println (something to print);

Features:

(1) throughselectThe result of the query is a virtual table, not real

② The things to query can be constant values, can be expressions, can be fields, can be functions

Copy the code

Step 2: Conditional query

Conditional query: Filter the data of the original table according to the conditions to query the desired data

Grammar:

select 

Expression to query field | | | constant value function

from 

    表

where 

Conditions;



Classification:

1. Conditional expressions

Example: salary >10000

Conditional operators:

< >= <= =! = < >



Logical expressions

Example: salary >10000 && salary<20000



Logical operators:



    and(&&) : If both conditions are true, the result istrue, or forfalse

    or(| |) : two conditions as long as there is a set up, the result is zerotrue, or forfalse

    not(!) : If the condition is true, thennotafterfalse, or fortrue



Three, fuzzy query

Example: last_namelike 'a%'

Copy the code

Step 3: Sort the query

Grammar:

select

Things to look up

from

    表

where 

conditions



order bySort of field expression | | | function is an alias"asc|desc】

Copy the code

Advanced 4: Common functions

A single line function

1. Character functions

Concat stitching

Substr intercepts the substring

Upper is converted to uppercase

Lower is converted to lowercase

Trim Removes specified Spaces and characters before and after

Ltrim trim the left space

Rtrim trim the right space

    replacereplace

    lpadLeft filling

Rpad right filling

    instrReturns the index of the first occurrence of a substring

    lengthGet number of bytes



2, mathematical function

    roundrounded

    randThe random number

    floorTake down the whole

    ceilTake up the whole

    modTake more than

    truncatetruncation

3, date function

    nowCurrent system date + time

    curdateCurrent System date

    curtimeCurrent system time

    str_to_dateConverts characters to dates

    date_formatConverts dates to characters

4, process control function

    ifDealing with double branches

    caseStatements handle multiple branches

situation1: Processing equivalence judgment

situation2: Processing condition judgment



5, other functions

    versionversion

    databaseThe current library

    userCurrent Connected User

Copy the code

2. Grouping functions

The sum sum

Max maximum

Min min

Avg average

Count count



Features:

    1The above five grouping functions ignore null values except count(*)

2. Sum and AVG are generally used to deal with numerical types

Max, min, count can handle any data type

3. Both can be used with a distinct result after deduplication

4. The count parameter can support:

Field, *, constant value, generally put 1



Count (*) is recommended.

Copy the code

Step 5: Group query

Grammar:

selectQuery fields, grouping functions

from 表

group byGrouped field





Features:

1, can be grouped by a single field

2, and the grouped function together with the query field is better after the grouped field

3Group screening

For the table position keyword

Filter before grouping: raw tablegroup byIn the front of thewhere

Post-grouping filter: The result set after groupinggroup byThe back of thehaving



4, can be grouped by multiple fields separated by commas

5, can support sorting

6,havingAliases can be supported after

Copy the code

Step 6: Multi-table join query

Cartesian product: Occurs if the joining condition is omitted or invalid

Solution: Add connection conditions

Copy the code

I. Connection in traditional mode: equivalent connection — non-equivalent connection

1. Result of equivalent join = intersection of multiple tables

2. N Table connections require at least N -1 connection conditions

3. Multiple tables have no priority or order requirements

4. Alias the table to improve readability and performance

Copy the code

2, SQL99 syntax: through the join keyword to achieve connection

Meaning: SQL syntax introduced in 1999

Support:

Equivalent connection, non-equivalent connection (internal connection)

Outer join

Cross connection



Grammar:



selectFields,...

from 表1

【inner|left outer|right outer|cross】join 表2 onJoin condition

【inner|left outer|right outer|cross】join 表3 onJoin condition

【whereScreening conditions

【group byGrouping field

【havingScreening criteria after grouping

【order bySorted fields or expressions



Benefits: Statement, join conditions and filter conditions to achieve separation, concise!

Copy the code

3. Self-connection

Example: Query the employee name and the name of the immediate superior

sql99

SELECT e.last_name,m.last_name

FROM employees e

JOIN employees m ON e.`manager_id`=m.`employee_id`;

Copy the code

sql92

SELECT e.last_name,m.last_name

FROM employees e,employees m 

WHERE e.`manager_id`=m.`employee_id`;

Copy the code

Step 7: Subquery

Meaning:

A query statement nested with another completeselectStatement, which is nestedselectStatement, called a subquery or inner query

An external query statement is called a primary query or an external query

Copy the code

Features:

1. Subqueries are enclosed in parentheses

2, Subquery can be placed after from,selectThe back,whereThe back,havingAfter, but usually placed on the right side of the condition

3, the sub-query takes precedence over the main query, which uses the results of the sub-query

4Sub-queries are divided into the following two types according to the number of rows in the query result:

① Single-row subquery

The result set has only one row

The common collocative single-line operators are: > <= <> >= <=

Illegal use of subqueries:

A. The result of a subquery is a set of values

B. The subquery result is null



② Multi-line sub-query

The result set has multiple rows

Commonly used with multi-line operators:any, all,in,not in

    in: belongs to any of the subquery results

    anyAnd all can often be replaced by other queries

Copy the code

Step 8: Paging query

Application Scenarios:

In actual Web projects, SQL statements corresponding to paging queries should be submitted according to user requirements

Copy the code

Grammar:

selectField | expression,...

from 表

【whereConditions 】

【group byGrouping field

【havingConditions 】

【order bySorted fields

limit[Initial entry index,] number of entries;

Copy the code

Features:

1. The start item index starts at 0



2. The limit clause is placed at the end of the query statement



3. The formula:select * from  表 limit(page- 1) * sizePerPage sizePerPage

If:

SizePerPage displays the number of entries per page

Number of pages to display page

Copy the code

Step 9: Joint query

A union is a union

Grammar:

selectField expression | | | constants function 【fromTable 】 【whereConditions 】union【 all 】

selectField expression | | | constants function 【fromTable 】 【whereConditions 】union【 all 】

selectField expression | | | constants function 【fromTable 】 【whereConditions 】union【 all 】

.

selectField expression | | | constants function 【fromTable 】 【whereConditions 】

Copy the code

Features:

1The number of columns in multiple query statements must be the same

2, multiple query statements have almost the same type of columns

3,unionIs for deweighting,unionAll stands for no weight

Copy the code

DML language

insert

Insert into table name Values (1,…) ;

Features:

1, the field type and value type are consistent or compatible, and there is a one-to-one correspondence

2, can be empty, can not insert a value, or usenullfill

3, cannot be considered empty fields, values must be inserted

4The number of fields and values must be the same

5, fields can be omitted, but by default, all fields are stored in the same order as those in the table

Copy the code

Modify the

Modify single table syntax:

updateThe name of the tablesetField = new value, field = new value

【whereConditions 】

Copy the code

Modify the multipredicate method:

update 表1The alias1The table2The alias2

setField = new value, field = new value

whereJoin condition

andfilter

Copy the code

delete

Method 1: Delete statement

Select * from table_name where table_name = 1 where table_name = 1

Delete aliases 1, aliases 2 from table 1 aliases 1, aliases 2 where join condition and filter condition;

Mode 2: truncate statement

truncate tableThe name of the table

Copy the code

The difference between the two methods

# 1.truncateCan't addwhereConditions, anddeleteYou can addwhereconditions



# 2.truncateThe efficiency is higher



# 3.truncateIf you insert data after deleting a table with self-growing columns, the data starts at 1

#deleteIf you insert data after deleting a table with self-growing columns, the data starts at the last breakpoint



# 4.truncateDelete cannot be rolled back,deleteA deletion can be rolled back

Copy the code

DDL statements

Management of libraries and tables

Library management:

Create a library

create databaseThe library

Delete the library

drop databaseThe library

Copy the code

Table management: #1. Create a table

CREATE TABLE IF NOT EXISTS stuinfo(

    stuId INT.

    stuName VARCHAR(20),

    gender CHAR.

    bornDate DATETIME





);



DESC studentinfo;

Alter table alter table alter table

Grammar:ALTER TABLEThe name of the tableADD|MODIFY|DROP|CHANGE COLUMNField name field type;



# change the name of the field

ALTER TABLE studentinfo CHANGE  COLUMN sex gender CHAR;



Alter table name

ALTER TABLE stuinfo RENAME [TO]  studentinfo;

# 3 Modify the field type and column level constraints

ALTER TABLE studentinfo MODIFY COLUMN borndate DATE ;



# 4 Add a field



ALTER TABLE studentinfo ADD COLUMN email VARCHAR(20) first;

#⑤ Delete the field

ALTER TABLE studentinfo DROP COLUMN email;





# 3. Delete table



DROP TABLE [IF EXISTS] studentinfo;

Copy the code

Common type

Integer:



Decimal Numbers:

floating-point

Point type

Character:

The date type:

Blob type:

Copy the code

Common constraints

NOT NULL

DEFAULT

UNIQUE

CHECK

PRIMARY KEY

FOREIGN KEY

Copy the code

Database transaction

meaning

Data is switched from one state to another through a set of logical operation units (A set of DML -- SQL statements)

Copy the code

The characteristics of

(ACID)

Atomicity: Either all execute or all rollback

Consistency: Data status is the same before and after an operation

Isolation: When multiple transactions simultaneously operate on the same data in the same database, one transaction can execute without interference from another transaction

Persistence: Once a transaction is committed, data is persisted locally unless it is modified by other transactions

Copy the code

Related steps:

1. Start transactions

Write a set of logical operation units (multiple SQL statements) for a transaction

Commit or roll back the transaction

Copy the code

Classification of transactions:

Implicit transactions, with no obvious flags to start and end a transaction

Such as

insert,update,deleteThe statement itself is a transaction

Copy the code

Explicit transactions, with distinct flags that start and end the transaction

1. Start transactions

Cancel the automatic transaction submission function



Write a set of logical operation units (multiple SQL statements) for a transaction

    insert

    update

    delete



    3, commit the transaction or roll back the transaction

Copy the code

The keyword used

set autocommit=0;

start transaction;

commit;

rollback;



savepointThe breakpoint

commit toThe breakpoint

rollback toThe breakpoint

Copy the code

Transaction isolation level:

How do transaction concurrency problems occur?

When multiple transactions simultaneously operate on the same data in the same database

Copy the code

What are the concurrency issues with transactions?

Dirty read: a transaction reads uncommitted data from another transaction

Non-repeatable read: Data read in the same transaction is inconsistent

Phantom read: when one transaction reads data, another transaction updates it, causing the first transaction to read unupdated data

Copy the code

How do I avoid concurrency problems in transactions?

By setting the isolation level of the transaction

1,READ UNCOMMITTED

2,READCOMMITTED can avoid dirty reads

3And the REPEATABLEREADYou can avoid dirty reads, unrepeatable reads, and some magic reads

4, SERIALIZABLE can avoid dirty read, unrepeatable read and phantom read

Copy the code

Set the isolation level:

set session|global  transaction isolation levelIsolation level name;

Copy the code

To view the isolation level:

select@ @tx_isolation;

Copy the code

view

Meaning: understood as a virtual table

The difference between a view and a table

Usage mode Occupies physical space



The view is exactly the same and doesn't take up any of it, it just stores the SQL logic



Table exactly the same occupation

Copy the code

Benefits of views:

1, SQL statements improve reuse, high efficiency

2, and table to achieve separation, improve security

Copy the code

View creation

Grammar:

CREATE VIEWView name

AS

Query statement;

Copy the code

Add, delete, modify, and query views

1. View the view data



SELECT * FROM my_v4;

SELECT * FROM my_v1 WHERE last_name='Partners';



2. Insert view data

INSERT INTO my_v4(last_name,department_id) VALUES('falsely bamboo'.90);



3. Modify the view data



UPDATE my_v4 SET last_name ='dream gu' WHERE last_name='falsely bamboo';





4. Delete the view data

DELETE FROM my_v4;

Copy the code

Some views cannot be updated

SQL statements containing the following keywords: grouping function,distinct,group  by, having, Union, or Union all

Constant view

SelectContains subqueries

join

fromA view that cannot be updated

whereClause is referenced by the subqueryfromTable in clause

Copy the code

Update the view logic

# Method 1:

CREATE OR REPLACE VIEW test_v7

AS

SELECT last_name FROM employees

WHERE employee_id>100;



# Method 2:

ALTER VIEW test_v7

AS

SELECT employee_id FROM employees;



SELECT * FROM test_v7;

Copy the code

View deletion

DROP VIEW test_v1,test_v2,test_v3;

Copy the code

View the view structure

DESC test_v7;

SHOW CREATE VIEW test_v7;

Copy the code

The stored procedure

The benefits of a set of pre-compiled SQL statements

1, improve the reuse of SQL statements, reduce the pressure of developers

2. Improved efficiency

3, reduce the number of transmission

Copy the code

Classification:

1, no return no parameter

2And just takeinType, with no return parameters

3And just takeoutType, with return and no parameter

4, with bothinAnd aout, have return have parameter

5,inout, have return have parameter

Note:in,out,inoutCan take more than one in a stored procedure

Copy the code

Creating a stored procedure

Grammar:

create procedureStored procedure name(in|out| inout parameters of types,...).

begin

Stored procedure body



end

Copy the code

Similar to method:

Return type method name (parameter type parameter name,.) {



Methods the body;

}

Copy the code

Pay attention to

1, you need to set a new end tag

Delimiter New end tag

Example:

delimiter $



CREATE PROCEDUREStored procedure name(IN|OUT| INOUT parameters of types,...).

BEGIN

    sqlStatements 1;

The SQL statement2;



END $



2The stored procedure body can contain multiple SQL statements. If only one SQL statement is required, you can omit itbegin end



3, the meaning of the symbol before the parameter

in: This parameter can only be used as input (this parameter cannot be returned)

out: This parameter can only be used as output (this parameter can only be used as return value)

Inout: Can do both input and output

Copy the code

Calling a stored procedure

callStored procedure name (argument list)

Copy the code

function

Create a function

Syntax: LENGTH, SUBSTR, CONCAT, etc.

CREATE FUNCTIONFunction name (parameter name Parameter type...)RETURNSThe return type

BEGIN

The body of the function



END

Copy the code

Call a function

SELECTFunction name (argument list)

Copy the code

The difference between functions and stored procedures

Keyword call syntax Return value Application scenario

functionFUNCTION    SELECTfunction(a)Can only be a query generally used when the result of a query is one value and returned, when there is a return value and only one value

The stored procedurePROCEDURE   CALLThe stored procedure(a)There can be zero or more generally used for updates

Copy the code

Flow control structure

System variables

Global variables

Scope: valid for all sessions (connections), but not across restarts

View all global variables

SHOW GLOBAL VARIABLES;

View some of the system variables that meet the conditions

SHOW GLOBAL VARIABLES LIKE '%char%';

Views the value of the specified system variable

SELECT @@global.autocommit;

Assign a value to a system variable

SET @@global.autocommit=0;

SET GLOBAL autocommit=0;

Copy the code

Session variables

Scope: Valid for the current session (connection)

View all session variables

SHOW SESSION VARIABLES;

View some of the session variables that meet the criteria

SHOW SESSION VARIABLES LIKE '%char%';

Views the value of the specified session variable

SELECT @@autocommit;

SELECT @@session.tx_isolation;

Assign a value to a session variable

SET @@session.tx_isolation='read-uncommitted';

SET SESSION tx_isolation='read-committed';

Copy the code

Custom variable

User variables

Declare and initialize:

SET@ Variable name = value;

SET@ Variable name := value;

SELECT@ Variable name := value;

Copy the code

Assignment:

Method 1: Generally used to assign simple values

SETVariable name = value;

SETVariable name := value;

SELECTVariable name := value;





Method 2: Generally used to assign field values in the table

SELECTField name or expressionINTOvariable

FROMTable;

Copy the code

Use:

select@ Variable name;

Copy the code

2. Local variables

Statement:

declareVariable name type [defaultValue 】;

Copy the code

Assignment:

Method 1: Generally used to assign simple values

SETVariable name = value;

SETVariable name := value;

SELECTVariable name := value;





Method 2: Generally used to assign field values in the table

SELECTField name or expressionINTOvariable

FROMTable;

Copy the code

Use:

selectThe variable name

Copy the code

The difference between the two:

The scope defines the location syntax

Copy the code

User variable The @ sign is added anywhere in the current session. The type is not specified. The first sentence of BEGIN END in a local variable is usually not added

branch

If (condition, value 1, value 2) can be used anywhere

Case statement

Grammar:

Case 1: Similar to switch

caseexpression

when 值1 thenThe results of1Or statements1(If it is a statement, use a semicolon.)

when 值2 thenThe results of2Or statements2(If it is a statement, use a semicolon.)

.

elseResult n or statement n(semicolon if statement)

end 【caseIf it is placed inbegin endNeed to addcaseSelect (select)



Case two: Similar to multipleif

case 

whenconditions1 thenThe results of1Or statements1(If it is a statement, use a semicolon.)

whenconditions2 thenThe results of2Or statements2(If it is a statement, use a semicolon.)

.

elseResult n or statement n(semicolon if statement)

end 【caseIf it is placed inbegin endNeed to addcaseSelect (select)

Copy the code

Features: Can be used in any position

Elseif statement

Grammar:

ifsituation1 thenstatements1;

elseifsituation2 thenstatements2;

.

elseStatements n;

end if;

Copy the code

Feature: This parameter can be used only in begin End ！！！！！！！！！！！！！！！

If function simple double branch case structure equivalent judgment multi-branch if structure interval judgment multi-branch

cycle

Grammar:

【 tag: 】WHILE loop conditionDO

The loop body

END WHILE【 label 】;

Copy the code

Features:

Only on theBEGIN ENDinside



If you want to accompany a leave jump statement, you need to use the tag, otherwise you don't need the tag



Leave is similar to JavabreakStatement, out of the loop!!

Copy the code