Super detailed MySQL 30000 word summary

Article contents

MySQL Basics

Introduction to database

Database overview

Storage method of data

Create object in Java: Student s = new Student(1, "Zhang San") exists in memory
Learned Java IO flow: save data to a file.

Storage location:

advantage

shortcoming

Memory

Fast

It cannot be saved permanently. The data is temporary.

file

Data can be saved permanently

It is inconvenient to operate data and query some data.

database

(1) The data can be saved permanently (2), the query speed is fast (3), and the data management is convenient

It occupies resources and needs to be purchased.

Concept of database

What is a database

  1. Warehouse for storing data
  2. In essence, it is a file system, or it is stored on the computer of the server in the form of files.
  3. All relational databases can use common SQL
    Statement to manage DBMS DataBase Management System

Common database ranking

MySQL: an open source free database, a small database, has been acquired by Oracle. MySQL6.x also starts to charge. Later, Sun Corporation acquired mysql, and Sun Corporation was acquired by Oracle.


Oracle: a large database charged by Oracle company.

DB2: a database product of IBM, which is charged. It is often used in the banking system.

SQL Server: a medium-sized database charged by MicroSoft. C#,. net and other languages are often used.

SQLite: a small embedded database, applied to mobile phones, such as Android.

Installation and uninstallation of database

The installation process is divided into two parts:

  1. File decompression and copying process, default installation directory:
  2. After installation, the MySQL server must be configured
    The name of the administrator in mysql.

Download address: https://download.csdn.net/download/qq_45771939/21446730?spm=1001.2014.3001.5503

Installation of database

  1. Open the downloaded MySQL installation file, double-click to extract it, and run "mysql-5.5.40-win64.msi".

  2. Select the installation type. There are three options: "Typical", "Complete" and "Custom". Select "Custom" and press "next" to continue.

  3. Click Browse to manually specify the installation directory.
    4. Fill in the installation directory. My name is "d:Program Files (x86)MySQLMySQL Server 5.0", and press "OK" to continue.

  4. Confirm the previous settings. If there is any error, press "Back" to return to redo. Press "Install" to start the installation.



  5. The installation is in progress. Please wait until the following interface appears to complete the installation of MYSQL.

    After the database is installed, you need to configure the database to use the configuration of MYSQL.

  6. After installation, the following interface appears to enter the mysql configuration wizard.

  7. Select the configuration method, "Detailed Configuration" and "Standard Configuration". We select "Detailed Configuration" to familiarize ourselves with the configuration process.


8. Select the server type, "Developer Machine (development test class, mysql takes up little resources)", "Server Machine (server type, mysql takes up more resources)", "Dedicated MySQL Server Machine (special database server, mysql takes up all available resources)
9. Select the general purpose of mysql database, "multi-functional database (general multi-functional, good)", "Transactional Database Only (server type, focusing on transaction processing, general)", "non Transactional Database Only (non transactional, simple, mainly used for monitoring and counting, and the support for MyISAM data type is only non transactional)", and click "Next" to continue.
11. Select the number of concurrent connections to the website, and the number of simultaneous connections: Decision Supportt(DSS)/OLAP (about 20) "," Online Transaction "
Processing(OLTP) (about 500) and "Manual Setting".

  1. Whether to enable TCP/IP connection and set the port. If not, you can only access the MySQL database on your own machine. On this page, you can also select "Enable Strict Mode", so that MySQL will not allow minor syntax errors. If you are new, it is recommended that you cancel the standard mode to reduce trouble. However, after you are familiar with MySQL, try to use the standard mode, because it can reduce the possibility of harmful data entering the database. Press "Next" to continue.
  2. This is to set the default database language encoding of mysql (important). Generally, select UTF-8, and press "Next" to continue.
  3. Select whether to install MySQL as a Windows service. You can also specify the Service Name and whether to add the bin directory of Mysql to the Windows PATH (after adding, you can directly use the files under the bin without pointing out the directory name. For example, connect, "mysql.exe -uusername -ppassword;" It's OK. Don't point out mysql Exe, which is very convenient). I checked all of them here, but the Service Name remains unchanged. Press "Next" to continue.
  4. Ask if you want to change the password of the default root user (super management). "Enable root access from remote machines (whether the root user is allowed to log in on other machines, do not tick it if it is safe, and tick it if it is convenient)". Finally, "create an anonymous account" (create an anonymous user, the anonymous user can connect to the database, but can't operate data, including queries) "is generally not checked. After setting, press" Next "to continue.
  5. Confirm that the settings are correct, and press "Execute" to make the settings effective, that is, to complete the installation and configuration of MYSQL.

    Note: after setting, a common error occurs when you press "Finish", that is, you can't "Start service", which usually occurs on servers that have previously installed mysql. The solution is to ensure that the previously installed MySQL servers are completely uninstalled; If not, check whether the previous password has been modified according to the above step, and follow the above operation; If it still doesn't work, back up and delete the data folder under the MySQL installation directory. After the installation is completed, delete the data folder generated by the installation, move the backed up data folder back, and restart the MySQL service. In this case, you may need to check the database and repair it once to prevent data errors.

Uninstallation of database

  1. Stop the MySQL service of window. Find "this computer" - > "management" - > "service" and stop the MySQL background service.
  2. Uninstall the MySQL installer. Find "control panel" - > "programs and functions" and uninstall the MySQL program.
  3. Delete all files in the MySQL installation directory.
    1. Delete the MySQL directory in the ProgramDate directory on disk c. Path: c: programdatamysql (it is a hidden file and needs to be displayed)
      Come out).

Start and login of database service

There are two ways to start the MySQL server:

  1. Start automatically through service
  2. Manual start mode

Windows service mode startup

Find "this computer" - > "management" - > "service" and click start.

DOS command mode startup

Open cmd with administrator

net start mysql : start-up mysql Service
net stop mysql:close mysql service

Console connection database

MySQL is a database that requires an account name and password to log in. It is used after logging in. It provides a default root account. You can log in with the password set during installation
4.3.1 login format

// Mode 1
mysql -u user name -p password
// Mode 2
mysql -hip address -u user name -p password
// Mode 3
mysql --host=ip address --user=user name --password=password


Exit MySQL:

quit or exit

SQLyog graphical tool - client

SQLyog is a simple, efficient and powerful graphical MySQL database management tool produced by the famous Webyog company in the industry. Using SQLyog, you can quickly and intuitively maintain the remote MySQL database from any corner of the world through the network.

Log in to the database using SQLyog

Database management system

DataBase Management System (DBMS): refers to a kind of large-scale software for operating and managing databases, which is used to establish, use and maintain databases, and uniformly manage and control databases to ensure the security and integrity of databases. Users access the data in the tables in the database through the DataBase Management System.

Database management system, relationship between database and table

A database management program (DBMS) can manage multiple databases. Generally, developers will create a database for each application. In order to save the data of the entity in the application, multiple tables are generally created in the database to save the data of the entity User in the program.
The relationship among database management system, database and table is as shown in the figure:

Concept of SQL

What is SQL

Structured Query Language

SQL function

1. It is a query specification for all relational databases. Different databases support it.
2. A common database operation language that can be used in different databases.
3. There are some differences between different database SQL statements

SQL statement classification

1. Data Definition Language (DDL) such as: database and table building
2. Data Manipulation Language(DML), such as adding, deleting and modifying records in tables
3. Data Query Language(DQL), such as query operations on tables
4. Data Control Language(DCL), such as setting user permissions

MySQL syntax

1. Each statement ends with a semicolon, if it is not mandatory in SQLyog.
2. SQL is case insensitive, and upper case and lower case are considered the same in keywords
3. 3 Notes:

Syntax of comments

explain

– spaces

Single-Line Comments

/* */

multiline comment

This is a unique annotation method of mysql

DDL operation database

Create database

Several ways to create a database

Create database

CREATE DATABASE Database name;

Judge whether the database already exists. If it does not exist, create the database

CREATE DATABASE IF NOT EXISTS Database name;

Create database and specify character set

CREATE DATABASE Database name CHARACTER SET character set;

Case:

-- Create database directly db1
create database db1;
-- Determine whether it exists. If it does not exist, create a database db2
create database if not exists db2;
-- Create a database and specify the character set as gbk
create database db3 character set gbk;

view the database

View all databases

show databases;

View the definition information of a database

show create database db3;
show create database db1;

modify the database

Modify the default character set of the database

ALTER DATABASE Database name CHARACTER SET character set;

Change the character set of db3 database to utf8

alter database db3 character set utf8;

Delete database

Syntax for deleting a database

DROP DATABASE Database name;

Use database

View the database in use

SELECT DATABASE(); --One used mysql Global functions in

Use / switch database

USE Database name;

DDL operation table structure

Create table

Create table format

CREATE TABLE Table name (
Field name 1 field type 1,
Field name 2 field type 2
);

Keyword Description:

Key to create the table

explain

CREATE

establish

TABLE

surface

MySQL data type

The commonly used data types are as follows:

type

describe

int

integer

double

float

varchar

String type

date

Date type

The created student table contains ID, name and birthday fields

create table student (
id int, -- integer
name varchar(20), -- character string
birthday date -- Birthday, no comma at the end
);

View table

SHOW TABLES;

View table structure

DESC Table name;

View the SQL statement that created the table

SHOW CREATE TABLE Table name;

View all tables in db1 database

use db1;
show tables;

View student's create table SQL statement

show create table student;

Quickly create a table with the same table structure

grammar

CREATE TABLE New table name LIKE Old table name;

Create s1 table. The structure of s1 table is the same as that of student table

-- Create a s1 Table and student Same structure
create table s1 like student;
desc s1;

Delete table

Delete table directly

DROP TABLE Table name;

Judge whether the table exists, and delete the table if it exists

DROP TABLE IF EXISTS Table name;

Case:

-- Delete table directly s1 surface
drop table s1;
-- Determine whether the table exists and delete it s1 surface
drop table if exists `create`;

Modify table structure

ADD table column ADD

ALTER TABLE Table name ADD Column name type;

MODIFY column type MODIFY

ALTER TABLE Table name MODIFY Column name (new type);

Modify column name CHANGE

ALTER TABLE Table name CHANGE Old column name new column name type;

Delete column DROP

ALTER TABLE Table name DROP Column name;

Modify table name

RENAME TABLE Table name TO New table name;

Modify character set

ALTER TABLE Table name character set character set;

Data in DML operation table

It is used to add, delete and modify records in the table

insert record

INSERT [INTO] Table name [Field name] VALUES (field value)

Insert all fields:

Write all the field names

INSERT INTO Table name (Field name 1, Field name 2, Field name 3) VALUES (Value 1, Value 2, Value 3);

Do not write field name

INSERT INTO Table name VALUES (Value 1, Value 2, Value 3);

Insert partial data

INSERT INTO Table name (Field name 1, Field name 2, ...) VALUES (Value 1, Value 2, ...);

Case:

-- Insert all columns into the student table
insert into student (id,name,age,sex) values (1, 'Sun WuKong', 34, 'male');
insert into student (id,name,age,sex) values (2, 'Gohan ', 16, 'male');

Precautions for insert:

  1. The inserted data should be of the same data type as the field
  2. The size of the data should be within the specified range of the column. For example, a string with a length of 80 cannot be added to a column with a length of 40.
  3. The data positions listed in values must correspond to the arrangement positions of the added columns. value can be used in mysql, but it is not recommended to
    Yes, the function is the same as values.
  4. Character and date data should be enclosed in single quotation marks. Double quotation marks can also be used as separators in MySQL.
  5. If no column is specified or null is used, null value is inserted.

Worm replication

What is worm replication

Copy data from an existing table to another table.

Syntax format
Copy all columns in table name 2 to table name 1

INSERT INTO Table name 1 SELECT * FROM Table name 2;

Copy only some columns

INSERT INTO Table name 1(Column 1, Column 2) SELECT Column 1, Column 2 FROM student;

Update table record

Syntax format:

UPDATE Table name SET Column name=value [WHERE Conditional expression]

Modify data without conditions

UPDATE Table name SET Field name=value; --Modify all rows

Modify data with conditions

UPDATE Table name SET Field name=value WHERE Field name=value;

Case:

-- Modify data without conditions and change all gender to female
update student set sex = 'female';
-- Modify data with conditions, and id Change the gender of student No. 2 to male
update student set sex='male' where id=2;
-- Modify multiple columns at once, and id The age of the student who is 3 is changed to 26, address Changed to Beijing
update student set age=26, address='Beijing' where id=3;

Delete table record

Syntax format:

DELETE FROM Table name [WHERE Conditional expression]

Delete data without conditions

DELETE FROM Table name;

Delete data with conditions

Delete data with conditions

Use truncate to delete all records in the table

TRUNCATE TABLE Table name;

The difference between truncate and delete:
truncate is equivalent to deleting the table structure and creating a new table.

Case:

-- Delete data with conditions, delete id Record of 1
delete from student where id=1;
-- Delete data without conditions,Delete all data in the table
delete from student;

Data in DQL query table

Query does not modify the data in the database. It is just a way to display the data.

Syntax:

SELECT Column name FROM Table name [WHERE Conditional expression]

Simple query

Use * for all columns

SELECT * FROM Table name;

Query all students:

select * from student;

Query the data of the specified column, with multiple columns separated by commas

SELECT Field name 1, Field name 2, Field name 3, ... FROM Table name;

Query the name and age columns in the student table

select name,age from student;

Specify the alias of the column to query

Advantages of using aliases: new names are used for display without modifying the table structure.

Syntax:
1. Assign an alias to a column

SELECT Field name 1  AS alias, Field name 2 AS alias... FROM Table name;

2. Specify aliases for both columns and tables

SELECT Field name 1 AS alias, Field name 2 AS alias... FROM Table name AS Table alias;

Case:

-- Use alias
select name as full name,age as Age from student;
-- Tables: using aliases
select st.name as full name,age as Age from student as st

Clear duplicate values

Query the specified column and no duplicate data appears in the result

SELECT DISTINCT Field name FROM Table name;

Case:

-- Find out where the students come from
select address from student;
-- Remove duplicate records
select distinct address from student;

Query results participate in calculation

A column of data and fixed value operation:

SELECT Column name 1 + Fixed value FROM Table name;

A column of data and other column data participate in the operation:

SELECT Column name 1 + Column name 2 FROM Table name;

Case:

--Add math and English score columns,Add the corresponding math and English scores to each record, and add the math and English scores when querying

select * from student;
-- Add 5 points to all math
select math+5 from student;
-- query math + english And
select * from student;
select *,(math+english) as Total score from student;
-- as Can be omitted
select *,(math+english) Total score from student;

Condition query

Why do you want to query by criteria
If there are no query criteria, all rows will be queried each time. In practical application, it is generally necessary to specify query conditions. Filter records.

Syntax of conditional query:

SELECT Field name FROM Table name WHERE condition;
--Process: take out each data in the table, return the records that meet the conditions, and do not return the records that do not meet the conditions

operator

Comparison operator

explain

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

< > in SQL, it means not equal to. It can also be used in mysql= No==

BETWEEN...AND

Within a range, for example, between 100 and 200 is equivalent to the condition between 100 and 200, with both the header and the tail

In (set)

Collection represents multiple values, separated by commas

LIKE ' %'

Fuzzy query

IS NULL

Query the NULL value of a column. Note: cannot write = NULL

Case:

- Create presentation table
- CREATE TABLE student3 (
 id int, -- number
 name varchar(20), -- full name
 age int, -- Age
 sex varchar(5), -- Gender
 address varchar(100), -- address
 math int, -- mathematics
 english int -- English
);
INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES 
(1,'Sun WuKong',55,'male','Hangzhou',66,78),
(2,'Qiqi',55,'female','Shenzhen',98,87),
(3,'Gohan ',35,'male','Hong Kong',56,77),
(4,'Bulma ',30,'female','Chengdu',76,65),
(5,'Goten ',28,'male','Beijing',86,NULL),
(6,'Feliza',572,'male','Hong Kong',85,97),
(7,'Xiaofang',21,'female','Hong Kong',99,99),
(8,'Begita',62,'male','Zhengzhou',56,65),
(9,'Majin Buu ',432,'male','Wuhan',83,75),
(10,'Babe',12,'male','Guangzhou',95,63)
);


-- query math Students with scores greater than 80
select * from student3 where math>80;
-- query english Students with scores less than or equal to 80
select * from student3 where english <=80;
-- query age Equal to 20-year-old students
select * from student3 where age = 20;
-- query age Not equal to 20-year-old students, note: not equal to two ways of writing
select * from student3 where age <> 20;
select * from student3 where age != 20;

Logical operator

Logical operator

explain

and

Or & & and, the former is recommended in SQL, but the latter is not universal.

or

Or||

not

Or! wrong

Case:

-- query age Students over 35 and male(Both conditions are satisfied at the same time)
select * from student3 where age>35 and sex='male';
-- query age Students over 35 or male(One of the two conditions is satisfied)
select * from student3 where age>35 or sex='male';
-- query id Students who are 1 or 3 or 5
select * from student3 where id=1 or id=3 or id=5;

in keyword

Syntax:

SELECT Field name FROM Table name WHERE field in (Data 1, Data 2...);

Case:

-- query id Students who are 1 or 3 or 5
select * from student3 where id in(1,3,5);
-- query id Students who are not 1 or 3 or 5
select * from student3 where id not in(1,3,5);

Range query

Syntax:

BETWEEN Value 1 AND Value 2

Query students with english scores greater than or equal to 75 and less than or equal to 90

select * from student3 where english between 75 and 90;

like keyword

Syntax:

SELECT * FROM Table name WHERE Field name LIKE 'Wildcard string';

MySQL wildcard:

wildcard

explain

%

Match any number of strings

_

Match one character

-- Inquire about students surnamed sun
select * from student3 where name like 'Sun%';
select * from student3 where name like 'Sun';
-- The query name contains:'cloth'A student of Chinese characters
select * from student3 where name like '%cloth%';
-- Query students whose surname is Bei and whose name has two characters
select * from student3 where name like 'shellfish_';

MySQL table constraints and database design

DQL query statement

sort

adopt ORDER BY Clause to sort the query results(Sorting is only a display method and will not affect the order of data in the database)
SELECT Field name FROM Table name WHERE field=value ORDER BY Field name [ASC|DESC];
ASC: Ascending, default
DESC: Descending order

Single column sorting: sort by only one field.

-- Query all data,Sort by age in descending order
select * from student order by age desc;

Combined sorting: multiple fields are sorted at the same time. If the first field is equal, the second field is sorted, and so on.

Combined sort syntax:

SELECT Field name FROM Table name WHERE field=value ORDER BY Field name 1 [ASC|DESC], Field name 2 [ASC|DESC];


-- Query all data,On the basis of descending order of age, if the age is the same, it will be sorted in ascending order of math scores
select * from student order by age desc, math asc;

Aggregate function

The queries we have done before are all horizontal queries, which are judged line by line according to the conditions. The query using aggregate functions is a vertical query, which calculates the value of a column and then returns a result value. The aggregate function ignores NULL.

Five aggregate functions:

Aggregate functions in SQL

effect

Max (column name)

Find the maximum value of this column

Min (column name)

Find the minimum value of this column

AVG (column name)

Average this column

Count (column name)

Count the number of records in this column

Sum (column name)

Sum this column

Syntax:

SELECT Aggregate function(Column name) FROM Table name;


-- Query the total number of students
select count(id) as Total number from student;
select count(*) as Total number from student;

Statistics null:

-- query id Field, if it is null，Use 0 instead
select ifnull(id,0) from student;

select count(ifnull(id,0)) from student;

Case:

-- Query the total number of people older than 20
select count(*) from student where age>20;
-- Query math score
select sum(math) Total score from student;
-- Average score of query mathematics
select avg(math) average from student;
-- Query the highest score in Mathematics
select max(math) Highest score from student;
-- Query the lowest score of Mathematics
select min(math) Lowest score from student;

grouping

Group query refers to grouping the query information using the GROUP BY statement, with the same data as a group

Syntax:

SELECT Field 1,Field 2... FROM Table name GROUP BY Group field [HAVING condition];

GROUP BY takes the same content in the result of grouping field as a group and returns the first piece of data of each group, so grouping alone is useless. The purpose of grouping is for statistics. Generally, grouping is used together with aggregation functions.

-- Group by gender and find the average score of boys and girls in Mathematics
select sex, avg(math) from student3 group by sex;

Use having to query people over 25 years old, group them by sex, count the number of people in each group, and only display the data with the number of men and women greater than 2:

-- Filter the results of grouping query
SELECT sex, COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex having COUNT(*) >2;

Sub name

effect

where clause

1. Before grouping the query results, remove the rows that do not meet the where criteria, that is, filter the data before grouping, that is, filter first and then group. 2. Aggregate function cannot be used after where

having clause

1. The having clause is used to filter the groups that meet the conditions, that is, filter the data after grouping, that is, grouping first and then filtering. 2. Aggregate functions can be used after having

limit statement

Function of limit:
LIMIT means LIMIT, so the function of LIMIT is to LIMIT the number of query records.

LIMIT syntax format:

LIMIT offset,length
--offset: Starting number of lines, counting from 0. If omitted, the default is 0
--length:  Number of returned rows

Case:

INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES
(11,'Tang Monk',25,'male','Chang'an',87,78),
(12,'Sun WuKong',18,'male','Huaguo Mountain',100,66),
(13,'Zhu Bajie',22,'male','Gao Laozhuang',58,78),
(14,'Monk Sha',50,'male','Liusha River',77,88),
(15,'Baigujing',22,'female','Baihuling',66,66),
(16,'spider goblin',23,'female','Coiled wire hole',88,88);

-- Query the data in the student table and display it from item 3 to 6.
select * from student3 limit 2,6;

-- Finally, if there are less than 5, how many will be displayed

Database backup and restore

Application scenario of backup

Data failure may occur when data transmission, data storage and data exchange are performed on the server. Such as unexpected shutdown or damage of storage media. At this time, if data backup and data recovery methods and measures are not taken, data loss will be caused, and the resulting loss is irreparable and incalculable.

Backup and restore statements

Backup format: under DOS, when not logged in. This is an executable exe in the bin folder.

mysqldump -u user name -p Password database > Path to file

Restore format: commands in mysql can only be operated after login

USE database
SOURCE Path to import file;

Case:

-- backups day21 Data in database to d:a.sql In the file:
mysqldump -uroot -proot day21 > d://a.sql

--reduction
use day21;
source d://a.sql;

Graphical interface backup and restore

Back up the data in the database

  1. Select the database and right-click backup / export
  2. Specify the export path and save it as sql file

Restore data in the database

  1. Delete database
  2. Right click execute SQL script in the database list area to specify the SQL file to execute

Constraints on database tables

Overview of Database Constraints

Role of constraints:
Limit the data in the table to ensure the correctness, validity and integrity of the data. If constraints are added to a table, incorrect data cannot be inserted into the table. It is appropriate to add constraints when creating tables.

Constraint type:

Constraint name

Constraint keyword

Primary key

primary key

only

unique

Non empty

not null

Foreign key

foreign key

Check constraints

check (Note: mysql does not support)

Primary key constraint

Primary key functions:
It is used to uniquely identify each record in the database

Which field should be the primary key of the table?
Usually, the business field is not used as the primary key. Each table is designed with an id field, and the id is used as the primary key. Primary keys are used for databases and programs, not for final customers. Therefore, it doesn't matter whether the primary key has meaning, as long as it is not duplicate and non empty.

Create primary key:
Primary key keyword: primary key

Features of primary key:

  1. not null
  2. only

Create primary key method:

  1. When creating a table, add a primary key to the field

    Field name field type PRIMARY KEY

  2. Add a primary key to an existing table

    ALTER TABLE table name add primary key (field name)

Case:

-- Create table student table st5, Include fields(id, name, age)take id As primary key
create table st5 (
 id int primary key, -- id Is the primary key
 name varchar(20),
 age int
)
desc st5;
-- Insert duplicate primary key value
insert into st5 values (1, 'Guan Yu', 30);
-- Error code: 1062 Duplicate entry '1' for key 'PRIMARY'
insert into st5 values (1, 'Guan Yunchang', 20);
select * from st5;
-- insert NULL Primary key value of, Column 'id' cannot be null
insert into st5 values (null, 'Guan Yunchang', 20);


-- delete st5 Primary Key 
alter table st5 drop primary key;
-- Add primary key
alter table st5 add primary key(id);

Self increment of primary key:
If we add the primary key ourselves, it is likely to repeat. We usually want the database to automatically generate the value of the primary key field every time a new record is inserted.

AUTO_INCREMENT Indicates automatic growth(Field type must be integer type)


-- insert data
insert into st6 (name,age) values ('Sun Quan',17);
insert into st6 (name,age) values ('Sun CE',25);
-- Another way of writing
insert into st6 values(null,'Sun Jian',36);
select * from st6;

Modify the default value of self growth (starting value)
Auto by default_ The starting value of increment is 1. If you want to modify the starting value, please use the following SQL syntax

CREATE TABLE Table name(
Column name int primary key AUTO_INCREMENT
) AUTO_INCREMENT=Starting value;

Case:

-- Specify a starting value of 1000
create table st4 (
 id int primary key auto_increment,
 name varchar(20)
) auto_increment = 1000;
insert into st4 values (null, 'kong ming');
select * from st4;

Unique constraint

What is unique constraint: a column in a table cannot have duplicate values.

Basic format of unique constraint:

Field name field type UNIQUE

Case:

-- Create student table st7, Include fields(id, name),name Set unique constraint for this column,Students with the same name cannot appear
create table st7 (
 id int,
 name varchar(20) unique
)
-- Add a student with the same name
insert into st7 values (1, 'Zhang San');
select * from st7;
-- Duplicate entry 'Zhang San' for key 'name'
insert into st7 values (2, 'Zhang San');
-- Insert multiple repeatedly null 
insert into st7 values (2, null);
insert into st7 values (3, null);

Non empty constraint

What is a non empty constraint: a column cannot be null.

Basic syntax format of non empty constraint:

Field name field type NOT NULL


-- Create table student table st8, Include fields(id,name,gender)among name Cannot be NULL
create table st8 (
id int,
name varchar(20) not null,
gender char(1)
)
-- Add a record in which the name is not assigned
insert into st8 values (1,'Zhang Sanfeng','male');
select * from st8;
-- Column 'name' cannot be null
insert into st8 values (2,null,'male');

Default

Syntax:

Field name field type DEFAULT Default

Case:

-- Create a student table st9，Include fields(id,name,address)， The default address is Guangzhou
create table st9 (
 id int,
 name varchar(20),
 address varchar(20) default 'Guangzhou' )
-- Add a record,Use default address
insert into st9 values (1, 'Li Si', default);
select * from st9;
insert into st9 (id,name) values (2, 'Li Bai');
-- Add a record,Do not use default address
insert into st9 values (3, 'Li Siguang', 'Shenzhen');

Foreign key constraint

CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(30),
age INT,
dep_name VARCHAR(30),
dep_location VARCHAR(30)
);
-- Add data
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('Zhang San', 20, 'R & D department', 'Guangzhou');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('Li Si', 21, 'R & D department', 'Guangzhou');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('Wang Wu', 20, 'R & D department', 'Guangzhou');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('Lao Wang', 20, 'Sales Department', 'Shenzhen');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('king', 22, 'Sales Department', 'Shenzhen');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('Xiao Wang', 18, 'Sales Department', 'Shenzhen');

Disadvantages of the above data table:

  1. data redundancy
  2. Later, there will be problems of addition, deletion and modification

Solution:

-- Solution: divided into 2 tables
-- Create department table(id,dep_name,dep_location)
-- One side, main table
create table department(
id int primary key auto_increment,
dep_name varchar(20),
dep_location varchar(20)
);
-- Create employee table(id,name,age,dep_id)
-- Multi party, slave table
create table employee(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int -- The foreign key corresponds to the primary key of the primary table
)
-- Add 2 departments
insert into department values(null, 'R & D department','Guangzhou'),(null, 'Sales Department', 'Shenzhen');
select * from department;
-- Add employee,dep_id Indicates the Department of the employee
INSERT INTO employee (NAME, age, dep_id) VALUES ('Zhang San', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('Li Si', 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('Wang Wu', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('Lao Wang', 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('king', 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('Xiao Wang', 18, 2);
select * from employee;

Question: when we DEP in employee_ id
Enter the Department that does not exist, and the data can still be added However, there is no corresponding department, so this cannot happen in practical application. Dep of employee_ The data in ID can only be
id existing in department table

Target: dep needs to be constrained_ id can only be an id that already exists in the department table

Solution: use foreign key constraint

What is a foreign key constraint:

What is a foreign key: the column in the slave table that corresponds to the primary key of the master table, such as DEP in the employee table_ id
A table used by one party to bind others
Slave table: a multi-party, constrained table

Syntax for creating constraints:
1. Add foreign key when creating a new table:

[CONSTRAINT] [Foreign key constraint name] FOREIGN KEY(Foreign key field name) REFERENCES Primary table name(Primary key field name)

2. Adding foreign keys to existing tables:

ALTER TABLE From table ADD [CONSTRAINT] [Foreign key constraint name] FOREIGN KEY (Foreign key field name) REFERENCES Main table(Primary key field name);

Case:

-- 1) Delete secondary table/From table employee
drop table employee;
-- 2) Create from table employee And add foreign key constraints emp_depid_fk
-- Multi party, slave table
create table employee(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int, -- The foreign key corresponds to the primary key of the primary table
-- Create a foreign key constraint
constraint emp_depid_fk foreign key (dep_id) references department(id)
)
-- 3) Add data normally
INSERT INTO employee (NAME, age, dep_id) VALUES ('Zhang San', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('Li Si', 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('Wang Wu', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('Lao Wang', 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('king', 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('Xiao Wang', 18, 2);
select * from employee;
-- 4) Failed to add data with wrong department
-- Insert department that does not exist
-- Cannot add or update a child row: a foreign key constraint fails
INSERT INTO employee (NAME, age, dep_id) VALUES ('Lao Zhang', 18, 6);

Delete foreign key:

Syntax:

ALTER TABLE From table drop foreign key Foreign key name;

Case:

-- delete employee Tabular emp_depid_fk Foreign key
alter table employee drop foreign key emp_depid_fk;
-- stay employee Add a foreign key when an expression exists
alter table employee add constraint emp_depid_fk
foreign key (dep_id) references department(id);

Cascade of foreign keys:
New problems arise

select * from employee;
select * from department;
-- We should take the id If the value is changed from 2 to 5, can I update it directly?
-- Cannot delete or update a parent row: a foreign key constraint fails
update department set id=5 where id=2;
-- To delete a department: id Department equal to 1, Can I delete it directly?
-- Cannot delete or update a parent row: a foreign key constraint fails
delete from department where id=1;

What is cascading operation:
When modifying and deleting the primary key of the primary table, the foreign key value of the secondary table is updated or deleted at the same time, which is called cascade operation.

Cascade operation syntax

describe

ON UPDATE CASCADE

Cascading updates can only be used to create cascading relationships when creating tables. Update the primary key in the primary table, and the foreign key columns in the secondary table are automatically updated synchronously

ON DELETE CASCADE

cascading deletion

-- delete employee Table, recreate employee Tables, adding cascading updates and deleting cascading
drop table employee;
create table employee(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int, -- The foreign key corresponds to the primary key of the primary table
-- Create a foreign key constraint
constraint emp_depid_fk foreign key (dep_id) references
 department(id) on update cascade on delete cascade
)
-- Add data to employee table and department table again
INSERT INTO employee (NAME, age, dep_id) VALUES ('Zhang San', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('Li Si', 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('Wang Wu', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('Lao Wang', 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('king', 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('Xiao Wang', 18, 2);
-- Delete department table? Can I delete it directly?
drop table department;
-- Put the Department in the table id The Department equal to 1 is changed to id Equal to 10
update department set id=10 where id=1;
select * from employee;
select * from department;
-- Delete the Department whose department number is 2
delete from department where id=2;

Data constraint summary:

Constraint name

keyword

explain

Primary key

primary key

1. Unique 2. Non empty

default

default

If a column has no value, use the default value

Non empty

not null

This column must have a value

only

unique

This column cannot have duplicate values

Foreign key

foreign key

The primary key column in the master table and the external key column in the slave table

Relationship between tables

Concept of table relation

In real life, there must be relationships between entities, such as husbands and wives, departments and employees, teachers and students. Then when we design tables, we should reflect this relationship between tables!

Three relationships between tables

  1. One to many: the most commonly used relationship departments and employees
  2. Many to many: student selection list and student list, one course can be selected by multiple students, and one student can select multiple courses
  3. One to one: relatively less used. Employee form resume form citizen form passport form

One to many

One to many (1:n) for example: classes and students, departments and employees, customers and orders, classifications and commodities
One to many table creation principle: create a field in the slave table (multi-party), and the field points to the primary key of the master table (one party) as a foreign key

Many to many

Many to many (m:n) for example: teachers and students, students and courses, users and roles
Principle of creating many to many tables: you need to create a third table. There are at least two fields in the middle table. These two fields are used as foreign keys to point to the primary keys of their respective parties.

one-on-one

One to one (1:1) is not widely used in actual development, because one to one can be created into a table.
Two kinds of table building principles:

One to one table building principle

explain

Foreign key unique

The primary key of the master table and the foreign key (UNIQUE) of the slave table form a master foreign key relationship. The foreign key is UNIQUE

The foreign key is a primary key

The primary key of the master table and the primary key of the slave table form a primary foreign key relationship

Database design

Data normalization

What is a paradigm:
A good database design will have an important impact on data storage performance and later program development. To establish a scientific and standardized database, we need to meet some rules to optimize the design and storage of data. These rules are called paradigms.

Three paradigms:
At present, there are six paradigms in relational databases: the first normal form (1NF), the second normal form (2NF), the third normal form (3NF), the bath Codd normal form (BCNF), the fourth normal form (4NF) and the fifth normal form (5NF, also known as the perfect normal form).
The normal form satisfying the minimum requirement is the first normal form (1NF). The second normal form (2NF) that further satisfies more specification requirements on the basis of the first normal form is called the second normal form, and so on. Generally speaking, the database only needs to meet the third normal form (3NF).

1NF

Concept:
Each column of a database table is an indivisible atomic data item, and cannot be a set, array or other non atomic data item. That is, when a column in a table has multiple values, it must be split into different columns. In short, each column of the first normal form cannot be split, which is called atomicity.

Student schedule:

Existing problems:
1. There is very serious data redundancy (duplication): name, department name, department head
2. There is a problem in adding data: when adding a new department and department head, the data is illegal
3. Problems in data deletion: Zhang Wuji graduated and deleted the data of the Department.

2NF

Concept:
On the premise that the first normal form is satisfied, each field in the table is completely dependent on the primary key.
The so-called complete dependency means that there can be no columns that depend on only a part of the primary key. In short, the second normal form is based on the first normal form, and all columns are completely dependent on primary key columns. When a composite primary key contains multiple primary key columns, the second normal form is not met. For example, if a primary key has two columns, it cannot have such an attribute. It only depends on one of the columns, which is inconsistent with the second normal form.
Characteristics of the second paradigm:

  1. A table only describes one thing.
  2. Each column in the table is completely dependent on the primary key

Student schedule:

Existing problems:
1. (has been solved)
2. There is a problem in adding data: when adding a new department and department head, the data is illegal
3. Problems in data deletion: Zhang Wuji graduated and deleted the data of the Department.

3NF

Concept:
Under the premise of satisfying the second normal form, each column in the table directly depends on the primary key, rather than indirectly relying on the primary key through other columns.
In short, the third normal form is that all columns do not depend on other non primary key columns, that is, on the basis of meeting 2NF, any non primary column must not pass the dependency on the primary key. The so-called transitive dependency means that if there is A decisive relationship of "A → B → C", then C transitively depends on A. Therefore, the third norm is satisfied
The database table of type should not have the following dependencies: primary key column → non primary key column x → non primary key column y

Student information form:

Summary of three paradigms

normal form

characteristic

1NF

Atomicity: each column in the table cannot be split.

2NF

No local dependency, a table only describes one thing

3NF

There is no transitive dependency, and each column in the table directly depends on the primary key. Instead of relying on the primary key indirectly through other columns.

MySQL multi table query and transaction operation

Table connection query

What is multi table query

# Create department table
create table dept(
 id int primary key auto_increment,
 name varchar(20)
)
insert into dept (name) values ('Development Department'),('Marketing Department'),('Finance Department'); 
# Create employee table
create table emp (
 id int primary key auto_increment,
 name varchar(10),
 gender char(1), -- Gender
 salary double, -- wages
 join_date date, -- Entry date
 dept_id int,
 foreign key (dept_id) references dept(id) -- Foreign key, related department table(Primary key of department table) ) 
insert into emp(name,gender,salary,join_date,dept_id) values('Sun WuKong','male
',7200,'2013-02-24',1);
insert into emp(name,gender,salary,join_date,dept_id) values('Zhu Bajie','male
',3600,'2010-12-02',2);
insert into emp(name,gender,salary,join_date,dept_id) values('Tang Monk','male',9000,'2008-
08-08',2);
insert into emp(name,gender,salary,join_date,dept_id) values('Baigujing','female
',5000,'2015-10-07',3);
insert into emp(name,gender,salary,join_date,dept_id) values('spider goblin','female
',4500,'2011-03-14',1);

Functions of multi table query:
For example, if we want to query the name of Monkey King and the name of his department, we need to use multi table query.
If a SQL statement queries multiple tables, the query results are in multiple different tables. Each table takes one or more columns.

Cartesian product:

  1. There are two sets a and B. take all the components of these two sets.
  2. To complete multi table query, you need to eliminate useless data

Classification of multi table query:

Internal connection query

  1. Implicit inner connection: eliminating useless data with where condition
    example:

    – query all employee information and corresponding department information
    SELECT * FROM emp,dept WHERE emp.dept_id = dept.id;
    – query the name and gender of the employee table. Name of department table
    SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.dept_id = dept.id;

    SELECT
    t1.name, – name of employee table
    t1.gender, -- gender of employee table
    t2.name – name of the Department table
    FROM
    emp t1,
    dept t2
    WHERE
    t1.dept_id = t2.id;

  2. Explicit inner connection:
    Syntax: select field list from table name 1 [inner] join table name 2 on condition
    For example:

    SELECT * FROM emp INNER JOIN dept ON emp.dept_id = dept.id;
    SELECT * FROM emp JOIN dept ON emp.dept_id = dept.id;

  3. Internal connection query:
    1. Which tables to query data from
    2. What are the conditions
    3. Which fields to query

External link query

  1. Left outer connection:
    Syntax:

    select field list from table 1 left [outer] join table 2 on condition;

All the data in the left table and its intersection are queried.

example:

-- Query all employee information. If an employee has a department, the Department name will be queried. If there is no department, the Department name will not be displayed
SELECT 	t1.*,t2.`name` FROM emp t1 LEFT JOIN dept t2 ON t1.`dept_id` = t2.`id`;

  1. Right outer connection:
    Syntax: select field list from table 1 right [outer] join table 2 on condition;
    All the data in the right table and its intersection are queried.
    example:

    SELECT * FROM dept t2 RIGHT JOIN emp t1 ON t1.dept_id = t2.id;

Subquery

Concept: nested queries in queries are called subqueries.

-- Query the information of the highest paid employee
-- 1 How much is the highest salary? 9000
SELECT MAX(salary) FROM emp;
				
-- 2 Query employee information, and the salary is equal to 9000
SELECT * FROM emp WHERE emp.`salary` = 9000;
				
-- One sql This completes the operation. Subquery
SELECT * FROM emp WHERE emp.`salary` = (SELECT MAX(salary) FROM emp);

Different situations of sub query
1. The results of sub query are single line and single column:
Subquery can be used as a condition, and operators can be used to judge. Operator: > > = < < ==

-- Query employees whose salary is less than the average salary
SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);

  1. The results of the subquery are multi row and single column:
    Subquery can be used as a condition, and the operator in is used to judge

    – query all employee information of 'finance department' and 'marketing department'
    SELECT id FROM dept WHERE NAME = 'finance department' OR NAME = 'marketing department';
    SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;
    – subquery
    SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = 'finance department' OR NAME = 'marketing department');

  2. The results of the subquery are multi row and multi column:
    A subquery can participate in the query as a virtual table

    – query employee information and department information of employees whose entry date is after November 11, 2011
    – subquery
    SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.join_date > '2011-11-11') t2
    WHERE t1.id = t2.dept_id;

    – common internal connection
    SELECT * FROM emp t1,dept t2 WHERE t1.dept_id = t2.id AND t1.join_date > '2011-11-11'

affair

Basic introduction to transactions

  1. Concept:
    If a business operation with multiple steps is managed by transactions, these operations will either succeed or fail at the same time.

  2. Operation:

  1. Start transaction: start transaction;
    2. Rollback: rollback;
    3. Submit: commit;

  1. example:

    CREATE TABLE account (
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(10),
    balance DOUBLE
    );
    – add data
    INSERT INTO account (NAME, balance) VALUES ('zhangsan', 1000), ('lisi', 1000);

    SELECT * FROM account;
    UPDATE account SET balance = 1000;
    – Zhang San transfers 500 yuan to Li Si

    – 0. Start transaction
    START TRANSACTION;
    – 1. Zhang San's account-500

    UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';
    – 2. Li Si account + 500
    – something went wrong
    UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';

    – if there is no problem in execution, submit the transaction
    COMMIT;

    – if a problem is found, roll back the transaction
    ROLLBACK;

  2. Transactions in MySQL database are automatically committed by default

    There are two ways to commit a transaction:
    1. Auto submit:
    mysql is automatically submitted
    A DML statement will automatically commit a transaction.
    2. Manual submission:
    By default, the Oracle database commits transactions manually
    You need to start the transaction before committing

Modify the default commit method of transactions:
1. View the default commit method of transactions:

SELECT @@autocommit; -- 1 Represents automatic submission, 0 represents manual submission

2. Modify the default submission method:

set @@autocommit = 0;

Four characteristics of transactions

	1. Atomicity: it is the smallest inseparable unit of operation. It can succeed or fail at the same time.
	2. Persistence: after a transaction is committed or rolled back, the database will persistently save data.
	3. Isolation: between multiple transactions. Independent of each other.
	4. Consistency: the total amount of data remains unchanged before and after the transaction operation

Isolation level of the transaction

Concept: multiple transactions are isolated and independent. However, if multiple transactions operate on the same batch of data, some problems may arise. These problems can be solved by setting different isolation levels.

Problems:
1. Dirty read: one transaction reads uncommitted data in another transaction
2. Non repeatable reading (virtual reading): in the same transaction, the data read twice is different.
3. Phantom reading: if one transaction operates all the records in the DML data table and another transaction adds a piece of data, the first transaction cannot query its own modifications.

Isolation level:
1. read uncommitted: read uncommitted
Problems: dirty reading, non repeatable reading, unreal reading
2. read committed: read committed (Oracle)
Problems: non repeatable reading and unreal reading
3. repeatable read: repeatable read (MySQL default)
Problem: unreal reading
4. serializable: serialization
Can solve all problems

Note: the isolation level increases from small to large, with higher security but lower efficiency
Isolation level of database query:
select @@tx_isolation;
Database setting isolation level:
set global transaction isolation level string;

Case:

set global transaction isolation level read uncommitted;
start transaction;
-- Transfer operation
update account set balance = balance - 500 where id = 1;
update account set balance = balance + 500 where id = 2;

DCL

DBA: Database Administrator

DCL: manage users, authorize

Manage users

1. Add user:
Syntax:

CREATE USER 'user name'@'host name' IDENTIFIED BY 'password';

2. Delete user:
Syntax:

DROP USER 'user name'@'host name';

3. Modify user password:

UPDATE USER SET PASSWORD = PASSWORD('New password') WHERE USER = 'user name';
UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi';
SET PASSWORD FOR 'user name'@'host name' = PASSWORD('New password');
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');

Forgot the password of the root user in mysql?

				1. cmd -- > net stop mysql stop it mysql service
					* An administrator is required to run the cmd
				2. Start with no authentication mysql Services: mysqld --skip-grant-tables
				3. Open new cmd window,Direct input mysql I command you to knock back. You can log in successfully
				4. use mysql;
				5. update user set password = password('Your new password') where user = 'root';
				6. Close both windows
				7. Open Task Manager and end manually mysqld.exe Process of
				8. start-up mysql service
				9. Log in with a new password.

  1. Query user:
    – 1. Switch to mysql database
    USE myql;
    – 2. Query the user table
    SELECT * FROM USER;

     		* Wildcard: % Indicates that users can log in to the database on any host

Authority management

		1. Query permission:


-- Query permission
				SHOW GRANTS FOR 'user name'@'host name';
				SHOW GRANTS FOR 'lisi'@'%';

2. Authorization:

-- Grant permission
grant Permission list on Database name.Table name to 'user name'@'host name';
-- Grant all permissions to user Zhang San on any database or table
GRANT ALL ON *.* TO 'zhangsan'@'localhost';

3. Revoke authority:

-- Revoke permission:
revoke Permission list on Database name.Table name from 'user name'@'host name';
REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%';

last

I know that most junior and middle-level Java engineers want to improve their skills, often by groping for growth or signing up for classes, but for training institutions, the tuition fee is nearly 10000 yuan, which is really a lot of pressure. The self-study effect that is not systematic is inefficient and long, and it is easy to encounter the stagnation of ceiling technology!

Therefore, I have collected and sorted out a complete set of learning materials for Java development and sent it to you. The original intention is also very simple, that is, I hope to help friends who want to improve themselves by self-study but do not know where to start, and at the same time reduce everyone's burden.

Xiaobian has been encrypted: ahr0chm6ly9kb2nzlnfxlmnvbs9kb2mvrvrvm9asgxqzuvstlkwunc = = for security reasons, we have encoded the website through base64. You can get the website through base64 decoding.

Tags: Database MySQL Operation & Maintenance Interview server programming language

Posted by ashebrian on Sat, 10 Sep 2022 22:53:34 +0530

Hot Tags

©2023 Programs Team