Today’s sharing started, please give us more advice ~

What is MySQL

MySQL is a relational database management system

The SQL language used by MySQL is the most commonly used standardized language for accessing databases. Because of its small size, fast speed, low total cost of ownership, especially the characteristics of open source, generally small and medium-sized website development choose MySQL as a website database.

1.2. Connect to the database

Command line connection!

Database XXX language

DDL definition

DML operations

DCL control

DQL query

1.3 Operating the Database

Operation database > Operation tables in the database > Operation data of tables in the database

Creating a database

CREATE DATABASE IF NOT EXISTS school; Create school if no database exists

Deleting a Database

DROP DATABASE IF EXISTS hello; Delete if database hello exists

Using a database

If your table name or field name is a special character, use backquotes

USE SCHOOL

1.4. Database column type

The numerical

  • Tinyint Very small data 1 byte

  • Smalint Smaller data 2 bytes

  • Mediumint Medium size data 3 bytes

  • ==int Standard integer 4 bytes Java int ==

  • Bigint Specifies 8 bytes of larger data

  • Float Indicates the floating point number of 4 bytes

  • Double A floating point number is 8 bytes

  • Decimal floating-point numbers in the form of strings are generally computed using Decimal

string

  • Char The character string contains 0 to 255 characters of fixed size

== vARCHar Variable String 0 to 65535 ** Common String==

  • Tinytext tinytext 2^8-1

  • Text Text string 2^16-1 Saves large text

Time to date

  • Date YYYY-MM-DD indicates the date format

  • Time HH:mm:ss Time format

  • == DateTime YY-MM-DD HH: MM :ss The most commonly used time format ==

Timestamp timestamp 1970.1.1 to present millisecond value!

  • Year Indicates the year.

null

  • No value, unknown

  • Note that NULL is not used for the operation, and the result of the operation is NULL

1.5. Field attributes of database (emphasis)

Unsigned:

● Unsigned integer

● The column cannot be declared negative

Zerofill:

Low zero filling

● Insufficient bits, filled with 0, int(3), 005

Autoincrement:

● Usually understood as increment, automatically +1 based on the previous record (default)

● A unique primary key ~ index must be an integer

Non-null Not NULL

● If set to not null, an error will occur if no value is assigned to it.

●NULL. If no value is entered, the default value is NULL!

The default:

● Set default values!

●sex, the default value is male, if the value of this column is not specified, there will be a default value!

Development:

  • Id primary key

  • Version optimistic locking

  • Is_delete pseudo delete

  • Gmt_create Creation time

  • Gmt_update Specifies the change time

1.6. Create a database table

format

Common commands

1.7 types of data tables

— About the database engine

INNODB by default

MYISAM was used in earlier years

General operation:

  • MYISAM saves space and is faster

  • INNODB has high security, transaction processing, multi-table multi-user operation

The position of existence in physical space

All database files are stored in the data directory, each folder corresponds to a database

The essence is file storage!

The MySQL engine differs in physical files

  • INNODB only has a *. FRM file in the database table and ibdata1 file in the parent directory

  • MYISAM corresponding file

*.frm – Table structure definition file

*.myd – Data files

*.myi – Index file (index)

Sets the character set encoding of the database table

CHARSET=utf8

Latin-1 is the default character set for MySQL.

Configure the default encoding in my.ini

character-set-server=utf8

2. Modify the delete table

2.1, modify,

2.2, delete,

Drop table (if table exists)

DROP TABLE IF EXISTS teacher1

All create and delete operations as much as possible with judgment, to avoid error ~

Note:

  • ‘backquotes are used to enclose the field names

  • Comment — /* */

  • SQL is case-insensitive. You are advised to write it in lower case

  • All symbols are in English!

MySQL data management

3.1, foreign keys

Method 1: Add constraint to create table (cumbersome, complicated)

When deleting a table with a foreign key relationship, you must first delete the referenced table (secondary table) and then delete the referenced table (primary table).

Method 2: After a table is created, add foreign key constraints

The above operations are physical foreign keys, database level foreign keys, we do not recommend using! (Avoid confusion caused by too much data)

The best implementation

  • A database is simply a table that stores only data, only rows (data) and columns (fields).

  • We want to use multiple table data, we want to use foreign keys.

3.2. DML language

Database meaning: Data storage, data management DML: data manipulation language

  • INSERT

  • UPDATE

  • DELETE

3.2.1, add

insert

INsert into table name (select 1, 2, 3,……) Values (‘ 1′,’ 2′,’ 3′,…)

Matters needing attention:

1. Separate fields by commas (,)

2. Fields can be omitted, but the following values should correspond to each other

3. You can insert multiple statements at the same time. Separate VALUES (),(), and…… by commas (,)

3.2.2, modify,

Update modify who (condition) set old value = new value

Conditions: The WHERE clause operator id is equal to a value, greater than a value, and modified within a range

— Locate data by multiple criteria

UPDATE student SET student name=’RYGAR’ AND sex=’ female ‘

UPDATE table_name SET table_name 1= ‘1’, table_name 2= ‘2’,… WHERE conditions

Note:

  • Try to enclose field names with back quotes

  • Condition, a filter condition that, if not specified, modifies all columns

  • A value can be a specific value or a variable

UPDATE student SET name=’ sunwukong ‘,password=’sunwukong123′,birthday=NOW() WHERE name=’ sunwukong ‘AND password=’lisi123’

Both now() and current_time are the current times

3.2.3, delete,

The delete command

Syntax: ‘delete from table name [where condition]

TRUNCATE command

What it does: Completely empties a database table. The table structure and index constraints remain unchanged.

Clear the student table

Syntax: TRUNCATE table name

TRUNCATE student

Difference between DELETE and TRUNCATE

Similarity: Both can delete data, do not delete table structure

Difference:

1.TRUNCATE resets the autoincrement column, and the counter returns to zero

2.TRUNCATE does not affect transactions

DELETE DELETE problems, restart database, symptoms (MySQL5.0 has these symptoms, but MySQL8.0 fixes these problems)

  • INNODB autoincrement columns start at 1

  • MYISAM continues from the last self-increment (stored in file, not lost)

3.2.4 DQL Query data (key)

DQL (Data Query Language)

  • All query operations use it SELECT

  • Simple queries, complex queries it can do

  • The most core language in the database, the most important statement

  • Use the most frequently used statement

Sometimes, the list of names is not so obvious. We alias AS field name AS alias

To heavy DISTINCT

Delete the duplicate data in the SELECT result

— Find out which students took the test and their scores

SELECT * FROM result SELECT * FROM result

SELECT StudentNO FROM result — Query which students have taken the exam

Duplicate data is found, and the DISTINCT keyword is used for deduplication

SELECT DISTINCT studentNo FROM result — Duplicate data is discovered and deduplication is removed

Checking the MySQL version

SELECT version();

Database columns (expressions)

Expressions in the database: text values, columns, NULL, functions, computed expressions, system variables…

Select expression from table

3.2.5 where condition clause

Function: Retrieves qualified values from data

The search criteria consist of one or more expressions! The result is a Boolean value

Logical operator

| | | | operator grammar description

| — – | — – | — – |

| the && and | a and b/a & b | logic and, both are true, the result is true |

| or | | | a or b/a | b | | logic or, one is true, the result is true |

| Not ! | not a / ! A non | logic, true is false, false is true! |

Use English letters whenever possible

Query the result of student no. 1000

Fuzzy query: Comparison operator

Select * from student where name = liu

— LIKE keyword, %(representing 0 to any character) _(one character)

SELECT StudentNo,StudentName FROM student WHERE StudentName LIKE ‘liu %’;

Select * from student where name = ‘liu’

SELECT StudentNo,StudentName FROM student WHERE StudentName LIKE ‘li_ ‘;

Select * from student where name = ‘liu’

SELECT StudentNO,StudentName FROM student WHERE StudentName LIKE ‘liu __’;

Select * from student whose name contains “jia”

SELECT StudentNo,StudentName FROM student WHERE StudentName LIKE ‘&&& ‘;

==IN (specific one or more values) == keyword query 1001,1003,1003 student

SELECT StudentNo,StudentName FROM student WHERE StudentNo IN (1001,1002,1003);

Select student from Anhui and student from Luoyang, Henan

Select * from student where date of birth exists

SELECT StudentNo,StudentName FROM student WHERE BornDate IS NOT NULL;

Query student without date of birth (null)

SELECT StudentNo,StudentName FROM student WHERE BronDate IS NULL;

Today’s share has ended, please forgive and give advice!