MySQL database

Database overview

Database related concepts

DB

Database: it is "a warehouse that organizes, stores and manages data according to data structure". Is a long-term storage in the computer, organized, shareable, unified management of a large number of data collection. It is essentially a file system, which exists on the computer of the server in the form of files.

DBMS

Database management system. It is a large-scale software for manipulating and managing database. It is used to establish, use and maintain database. It manages and controls the database uniformly to ensure the security and integrity of the database. Users access the data in the database through dbms, and database administrators also maintain the database through dbms. It provides a variety of functions, which can enable multiple applications and users to establish, modify and query the database at the same time or at different times with different methods. It enables users to easily define and manipulate data, maintain the security and integrity of data, and carry out concurrency control and database recovery under multi-user.

SQL

Structure query language: a language specifically used to communicate with databases.

Advantages of SQL:

1. It is not the exclusive language of a specific database vendor, and almost all DBMS support SQL

2. Easy to learn

3. Using its language elements flexibly, it can carry out very complex and advanced database operations.

Why use a database

Data is stored in memory

Advantages: fast storage and reading speed.

Disadvantages: when the power is off / the program exits, the data is cleared. And memory is expensive.

Data is stored in a file

Advantage: data can be saved permanently.

Disadvantages: it is troublesome to find, add, modify and delete data, and the efficiency is low.

The data is stored in the database

Advantage: data can be saved permanently. You can easily find, add, modify, delete and other operations on data through SQL statements, with high efficiency.

Development and classification of database

In the development history of database, it has experienced various stages, such as hierarchical database, mesh database, relational database, non relational database and so on. At present, the mainstream database in the market is relational database, rather than relational database. Since 2009, the development trend has been rising and has been widely used in many fields.

Relational database

Relational database refers to a database that uses a relational model to organize data. It stores data in the form of rows and columns to facilitate users' understanding. The rows and columns of relational database are called tables, and a group of tables form a database.

Relational model can be simply understood as two-dimensional table model, and a relational database is a data organization composed of two-dimensional tables and their relationships.

Common relational databases include Oracle, DB2, MySQL, PostgreSQL, SQL Server, etc.

Non relational database

NoSQL generally refers to non relational databases. The generation of NoSQL database is to solve the challenges brought by large-scale data sets and multiple data types, especially the problems of big data application. NoSQL(NoSQL = Not Only SQL), which means "not just SQL", is a brand-new revolutionary movement of database. It was proposed in the early stage that the trend will be higher and higher in 2009. NoSQL advocates advocate the use of non relational data storage.

NoSQL has the following advantages:

Easy to expand, NoSQL database has a wide variety, but a common feature is to remove the relational characteristics of relational database. There is no relationship between the data, so it is very easy to expand. Invisible also brings scalable capabilities at the architecture level. Large amount of data and high performance. NoSQL databases have very high read and write performance, especially in large amount of data. This is due to its irrelevance and the simple structure of the database.
Common non relational databases include Redis (key value), MongoDB, HBase, Graph, etc.

MySql database

advantage:

Low cost: open source, generally free trial

High performance: fast execution

Simple: easy to install and use

Start and stop of MySQL server

Method 1: Computer - right click management - Service

Method 2: run as Administrator

net start Server name (start service)
net stop Service name (stop service)

Login and logout of MySql service

Method 1: through the built-in client of mysql (only for root user)

Method 2: through the built-in client of windows

Sign in:
mysql [-h host name -P Port number] -u user name -P password
 sign out:
exit/ctrl+C

Introduction to common MySql commands

View all current databases

show databases;

Open the specified library

use Library name;

View all tables in the current library

show tables;

View all tables in other libraries

show tables from Library name;

Create table

create table Table name(
	Column name, column type,
	Column name, column type,
	. . . 
);

View table structure

desc Table name;

View the version of the server

Mode 1:Log in to mysql Server
select version();
Mode 2:
mysql --version
 or
mysql -V

Graphical user interface client

navicat premium 15 installation tutorial

Navicat Premium is a set of multi connection database development tools, which allows you to connect multiple types of databases simultaneously in a single application: MySQL, MariaDB, MongoDB, SQL Server, SQLite, Oracle and PostgreSQL. You can quickly and easily access all databases at one time.

Use of navicat

Connection successful

use

SQL language

SQL Language Overview

Introduction to SQL language

SQL(Structured Query Language) is the abbreviation of structured query language. It uses SQL and DBMS to interact and manipulate the resources in relational database.
SQL is a standard that all database management systems need to follow. Different database manufacturers support SQL statements, but in order to strengthen the language ability of SQL, each manufacturer has added unique content.

SQL language classification

Data Definition Language (DDL): Data Definition Language is used to define database objects, such as data tables, views, indexes, etc.

Data Manipulation Language (DML): Data Manipulation Language is used to update, add and delete records in database tables.

Transaction Control Language (TCL): Transaction Control Language, which is used for transaction management in database.

Data Query Language (DQL): Data Query Language, which is used to query the data in the database.

Data Control Language (DCL): Data Control Language, which is used to control the user permissions of the database.

SQL language features

1. It is not case sensitive. It is recommended to use uppercase for keywords, and lowercase for database names, data table names and column names.

2. It can be written in one line or multiple lines.

3. You can use spaces and indents to enhance the readability of statements.

4. 3 Notes:

Single line comment: - comment content or # comment content (MySQL specific)

Multiline comment: / * comment content*/

DDL data definition language

DDL(Data Definition Language): data definition language, which is used to define database objects, libraries, tables, columns, etc; Create, delete and modify library and table structure. It is mainly divided into DDL of operating database and DDL of operating table.

(1) DDL for operating database

The database mentioned here is different from the MySQL database and Oracle database mentioned earlier. The "database" in MySQL database refers to a database management system (DBMS), and the database mentioned here refers to the structure used to organize and manage some objects (tables, views, etc.). It is easy to understand the folder on the disk.

A database server contains multiple libraries.

A database contains multiple tables.

A table contains multiple records.

1. Create database

(1) Create database directly

grammar

	CREATE DATABASE [IF NOT EXISTS] Database name

Examples

	CREATE DATABASE mydb1
	or
	CREATE DATABASE IF NOT EXISTS mydb2

(2) Create database with specified character set

grammar

	CREATE DATABASE [IF NOT EXISTS] Database name CHARACTER SET character set	

Examples

	CREATE DATABASE mydb3 CHARACTER SET UTF8

(3) Create a database by specifying the character set and collation method

grammar

	CREATE DATABASE[IF NOT EXISTS] Database name CHARACTER SET character set COLLATE Sorting rules

Examples

	CREATE DATABASE  mydb4 CHARACTER SET UTF8 COLLATE utf8_bin

Collation description

MySQL specifies different sorting rules for different character sets (you can refer to the help document of MySQL).

For example: utf8 specified in the utf8 character set_ general_ Ci and utf8_bin, the rules for storing the database in the database are different.

	utf8_general_ci: ci yes case insensitive, Namely "Case insensitive", a and A Will be treated as the same in character judgment.

	utf8_bin: Compile and store each string with binary data, which is case sensitive and can store binary contents.

2. View the database

(1) View all databases

grammar

	SHOW databases

Examples

	SHOW databases

(2) View the specified database

grammar

	SHOW CREATE DATABASE Database name

Examples

	SHOW CREATE DATABASE db1

3. Modify the database

You can only modify database characters and sorting rules, not database names.

grammar

ALTER DATABASE Database name CHARACTER SET character set [COLLATE Sorting rules]

Modify the character set of mydb3 to gbk and the sorting rule to gbk_bin

alter database mydb3 character set gbk collate gbk_bin;

4. Delete database

grammar

	DROP DATABASE Database name

Examples

	#Delete mydb3 database
	DROP DATABASE mydb3

5. Use database

(1) View databases in use

SELECT DATABASE()

(2) Switch database

grammar

USE Database name

Examples

USE mydb1

(2) DDL of operation table

A table is a database object. A table consists of several fields (columns). It is the most frequently used database object when we operate the database.

1. Create table

Note: before creating a table, you must first use the database (that is, the table must be created in a database)

grammar

CREATE TABLE Table name (Field name 1 data type,Field name 2 data type,...,Field name n data type)
	It is suggested to write in the following format:
CREATE TABLE Table name (
Field name 1 data type, 
Field name 2 data type,
     ...,
    Field name n data type
)

Examples

#Create a student table that contains ID, name, age, and Sex fields
CREATE TABLE student (
      id INT,
      name VARCHAR(20),
      birthday date
    )

2. Data type

(1) Value type

Integer type

When creating tables, they are signed by default.
If you want to use unsigned, specify in the table creation statement. For example: id int unsigned

Approximate number type

In MySQL, approximate values can be expressed by floating-point numbers and fixed-point numbers.

Floating point number

Floating point numbers in MySQL include float and double, which are defined as float(M,D) and double(M,D).

M - indicates that the stored value has m bits in total (the value range of M is 0 ~ 255)

D - indicates that there are d digits after the decimal point (the value range of D is 0 ~ 30)

For example, the data range stored in float(7,4) is -999.9999 ~ 999.9999.

Fixed point number

Decimal is the fixed-point number of MySQL. Compared with floating-point number, the approximation of fixed-point number is more accurate. The definition format is decimal(M,D)

M - indicates that the stored value has m bits in total (the default value of M is 10 and the value range is 1 ~ 65)

D - indicates that there are d digits after the decimal point (the default value of D is 0, and the value range is 0 ~ 30)

(2) String type

MySQL provides a variety of storage types for strings. As shown in the following table

char and varchar
Char and varchar are similar. They are both types used to store short string formats in MySQL database. However, the main difference between the two types lies in the different storage methods. The length of char type is fixed, while the length of varchar type is variable. For example, the storage string "abc", char(5) indicates that the stored characters will occupy 5 bytes (including 2 empty characters), while varchar(5) only occupies the length of 3 bytes, and 5 is only the maximum value. When the stored characters are less than 5, they are stored according to the actual length. At the same time, when retrieving, char type will delete the trailing space, while varchar type will retain the trailing space. Due to the length problem, the data addressing speeds of the two data types are different. If the characters are less than 10 bytes, char is recommended.

(3) Date and time type

For the selection of these data types, there are the following guidelines:

1. If you want to represent month, year and day, you usually use date.

2. If you want to represent year, month, day, hour, minute and second, you usually use datetime.

3. If you want to represent hours, minutes and seconds, you usually use time.

4. If you want to represent a year, you usually use year.

If you want to represent the time of the current system, you usually use timestamp.

timestamp and datetime

Although timestamp type and datetime type can represent date and time, there are differences between them.

1. The time stored in timestamp type is related to the time zone selected during MySQL database system installation. The same batch of data viewed in different time zones will get different results.

View SQL statements for time zone:

 	show variables like 'time_zone' 

The default is consistent with the host, and the display result is SYSTEM.

SQL statement for modifying time zone:

	set time_zone='+9:00'
	#(set as East 9 area)

2. The value range of the two is different

The value range of datetime is 1000-01-01 00:00:00 to 9999-12-31 23:59:59

The value range of timestamp is 1970-01-01 00:00:01.000000 to 2038-01-19 03:14:07.999999 '.

Summary of the most commonly used types: integer int, floating-point double, string varchar, date.

3. Create an example

Requirement: create an employee table, including fields, number (id int), name (emp_name varchar), gender (gender enum('M ',' F ')), hire_date date, salary (salary double) and resume text

create table employee(
   id int,
   emp_name varchar(100),
   gender enum('M','F'),
   hire_date date,
   salary double,
   resume text
)

4. View table

(1) View all tables

	show tables

(2) View table structure

grammar

	DESC Table name
	or
	describe Table name

Examples

	DESC emp

(3) View the SQL statement that created the table

grammar

	SHOW CREATE TABLE Table name

Examples

	show create table emp

5. Copy table structure

grammar

	CREATE TABLE New table name LIKE Old table name

Examples

	 create table emp1 like emp

6. Modification table

(1) Add new column

grammar

	ALTER TABLE Table name ADD Column name type

Examples

	alter table student add remark varchar(200)

(2) Modify the type of column

grammar

	ALTER TABLE Table name MODIFY New type of column name

Examples

    alter table student modify remark varchar(100)

(3) Modify column name

grammar

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

Examples

	alter table student change remark intor varchar(100)

(4) Delete column

grammar

	ALTER TABLE Table name DROP Listing

Examples

	ALTER TABLE student DROP intor

(5) Modify table name

grammar

	RENAME TABLE Table name TO New table name

Examples

	 rename table student to student1

(6) Modify character set

grammar

	ALTER TABLE Table name character set character set

Examples

	 alter table student1 character set utf8

7. Delete table

grammar

	DROP TABLE Table name

Examples

	drop table student1;

DML data manipulation language

Add, delete and modify the data in the table.

(1) Insert record

1. Insertion of specified fields

grammar

	INSERT INTO Table name (Field name 1, Field name 2...,field n) VALUES (Value 1, Value 2...,value n)

Examples

# Replace the database and create the student table
create table student(
   id int,
   name varchar(20),
   age int,
  sex char(1),
  address varchar(100)
); 
# Add ID, name, age and sex data to the student table
INSERT INTO student (id, name, age, sex) VALUES (1, 'Zhang San', 20, 'male');

matters needing attention

The value must correspond to the field, with the same number and type

The data size of the value must be within the length of the field varchar()

In addition to the numeric type, the values of other field types must be caused by quotation marks (single quotation marks are recommended)

If you want to insert a null value, you can not write the field or insert null

2. Insertions without specifying fields

All fields need to be maintained.

grammar

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

Examples

	INSERT INTO student VALUES (3, 'Wang Wu', 18, 'male', 'Beijing');

matters needing attention

If no column is specified, it is equivalent to specifying all columns, and the order is the same as that when creating a table.

3. Worm replication

Based on the existing data, copy the original data and insert it into the corresponding table.

grammar

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

Examples

	# Create the student2 table. The structure of student2 is the same as that of the student table
	CREATE TABLE student2 LIKE student;

	# Add the data from the student table to the student2 table
	INSERT INTO student2 SELECT * FROM student

(2) Update table records

1. Update without conditions

grammar

	UPDATE Table name SET Field 1=Value 1[,Field 2=Value 2,,...,field n=value n]

Examples

	UPDATE student1 set sex='male';

explain

The sex of all rows in the table will be updated to male

2. Update with conditions

grammar

	UPDATE Table name SET Field 1=Value 1[,Field 2=Value 2,,...,field n=value n][where condition]

Examples

	# Change the gender of the student with id number 2 to female
	UPDATE student SET sex='female' WHERE id=2;

(3) Delete record

1. Delete with conditions

grammar

	DELETE FROM Table name WHERE condition

Examples

	#Delete data with conditions and delete the record with id 3
	DELETE FROM student WHERE id=3

2. Delete without conditions

grammar

	DELETE FROM Table name

Examples

	DELETE FROM student;

explain

Deleting without conditions will delete all data in the table.

DQL data query language

The purpose of DQL statement is to query the data in the database. Query does not modify the data in the database, but is a way to display the data.

1. Simple query

(1) Query all data in the table

grammar

SELECT  *  FROM Table name

Examples

SELECT * FROM student

grammar

SELECT Field name 1, Field name 2,  ...field n  FROM Table name
 Write all column names

Examples

Select id,name,age,sex,address from student

(2) Query specified column

grammar

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

Examples

# Query the name and age columns in the student table
SELECT NAME, age FROM student

(3) Alias query

When querying, give the queried column or table another name (usually a shorter name, or to prevent duplication) which is called alias. The advantage of using alias is that it is convenient to view and process the queried data.

grammar

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

Examples

# Query the name and age columns in the sudent table. The alias of the name column is "name" and the alias of the age column is "age"
SELECT NAME AS 'full name', age AS 'Age' FROM student;
# AS can be omitted
SELECT NAME 'full name', age 'Age' FROM student;

explain

Aliasing a table during query has no effect at present. Its benefits can only be reflected when querying multiple tables.

(4) Remove duplicate values

When querying the specified column, if there is duplicate data in the specified column, it will be displayed by default.

for example

You can use the distinct keyword to remove duplicate data

grammar

SELECT DISTINCT Field name FROM Table name

Examples

SELECT DISTINCT name FROM student

explain

When multiple columns appear after distinct, it is called duplicate data only when the data of multiple columns are repeated.

(5) Query results participate in operation

In the query statement, the column of the query can perform mathematical operations (addition, subtraction, multiplication, division, etc.) with other values. The operation results will only affect the display and will not affect the data in the table.

grammar

SELECT Column name 1 + Fixed value FROM Table name
SELECT Column name 1 + Column name 2 FROM Table name

Examples

# Add math and English score columns to the student table, and add corresponding math and English scores to each record
# Modify the student table structure and add math and English score columns
ALTER TABLE student ADD math INT;
ALTER TABLE student ADD english INT

# Query the sum of math + english and use the alias "total score"
SELECT math + english 'Total score' FROM student

# Query the sum of all columns and math + english, and use the alias "total score"
SELECT *, math + english 'Total score' FROM student

# Query the name and age, and increase everyone's age by 10 years
SELECT name, age + 10 FROM student

# The result of any operation between null value and any value is null
# Add data to student table
insert into student values(4,'Zhao Liu',22,'male','Beijing',null,90)  
# Zhao Liu missed the math exam and got no grades.

# Query the sum of all columns and math + english, and use the alias "total score"
SELECT *, math + english 'Total score' FROM student

2. Condition query

(1) Prepare tables and data

CREATE TABLE student3 (
   		id int,
  		name varchar(20),
  		age int,
 		 sex varchar(5),
  		address varchar(100),
  		math int,
  		english int
	);

INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES (1,'Jack Ma',55,'male','Hangzhou',66,78),(2,'pony ',45,'female','Shenzhen',98,87),(3,'Ma Jingtao',55,'male','Hong Kong',56,77),(4,'Liuyan',20,'female','Hunan',76,65),(5,'Liu Qing',20,'male','Hunan',86,NULL),(6,'Lau Andy',57,'male','Hong Kong',99,99),(7,'Mader',22,'female','Hong Kong',99,99),(8,'Demacia',18,'male','Nanjing',56,65);

(2) Comparison operator

In query criteria, you can use a variety of comparison operators to represent query criteria.

=Equal to, > greater than, < less than, < = less than or equal to, > = greater than or equal to, < > or= Not equal to

Example 1:

# Query students whose math score is greater than 80
SELECT * FROM student3 WHERE math>80

Example 2:

# Query students whose english score is less than or equal to 80
SELECT * FROM student3 WHERE english<=80;

Example 3:

# A student whose age is equal to 20 years old
SELECT * FROM student3 WHERE age=20;

Example 4:

# Query age is not equal to 20-year-old students
SELECT * FROM student3 WHERE age!=20;
SELECT * FROM student3 WHERE age<>20;

(3) Logical operator

And (& &) multiple conditions are met at the same time

or(|) one of multiple conditions is satisfied

not(!) dissatisfaction

Example 1:

# Query students with age greater than 35 and male gender (both conditions are met)
SELECT * FROM student3 WHERE  age>35 AND sex='male';

Example 2:

# Query students whose age is greater than 35 or whose gender is male (one of the two conditions is met)
SELECT * FROM student WHERE age>35 OR sex='male';

(4) Query in within the specified range

grammar

SELECT Field name FROM Table name WHERE field in (Data 1, Data 2...); 
in Each data in it will be used as a condition, and it will be displayed as long as the conditions are met.

Example 1:

# Query students whose id is 1 or 3 or 5
SELECT * FROM student3 WHERE id IN (1,3,5)

explain

This is equivalent to using or to connect multiple conditions.

Example 2:

# Query students whose id is not 1 or 3 or 5
SELECT * FROM student3 WHERE id NOT IN (1,3,5);

(5) Scope

BETWEEN value 1 AND value 2 indicates the range from value 1 to value 2, including the beginning value and the end value.

For example, age BETWEEN 80 AND 100 is equivalent to age > = 80 and age < = 100

Example: query students whose english scores are greater than or equal to 75 and less than or equal to 90

SELECT * FROM student3 WHERE english>=75 AND english<=90;
SELECT * FROM student3 WHERE english BETWEEN 75 AND 90;

(6) Fuzzy query like

SELECT * FROM table name WHERE field name like 'wildcard string';
The data that meets the wildcard string rules will be displayed.

There are two MySQL wildcards:

%: indicates 0 or more characters (any character)

_: Represents a character

Example 1: query students surnamed Ma

SELECT * FROM student3 WHERE NAME LIKE 'horse%';

Example 2: query students whose names contain the word "de"

SELECT * FROM student3 WHERE NAME LIKE '%virtue%';

Example 3: query students whose surname is Ma and whose name has three words

SELECT * FROM student3 WHERE NAME LIKE 'horse__';

(7) Null query is null

To query the data whose field is empty (null), instead of using = null (null is not equal to any value), use is null to judge.

Examples

# null student information when querying english grades
select * from student3 where english is null

end

All your efforts will be recognized. Just walk steadily and steadily. The rest of your life is still long. Please don't panic. It's not that the ending is not perfect. It's to meet you when you save enough good luck. May you follow your heart, do your own thing bravely, have the confidence to stop and have the courage to move forward.

I want to study quietly and kill everyone!!!