preface:

Database notes

1, What is a database

• DataBase (DB, DataBase)
• Concept: database, also known as data management system
• Function: save and safely manage data (such as addition, deletion, modification, query, etc.) to reduce redundancy

2, Classification of database

2.1 relational database (SQL)

• There are MySQL, Oracle, Sql Sever, DB2 and SQLlite, which are collectively referred to as SQL
• Data is stored through the relationship between tables and between rows and columns
• It is used to store information similar to static (not changing much).

Thinking: how to store your own positioning information? - > NoSQL

2.2 non relational database (NoSQL)

• NoSQL? No database? (No
• In fact, it's No Only, not just SQL
• These include Redis and MongDB
• Non relational database: an object stores information determined by its own attributes.

3, Database management system

• Database Management System (DBMS) is a computer software system designed to manage databases. It generally has basic functions such as storage, interception, security, backup and so on. (from Wikipedia)
• Here we learn MySQL, why choose it?

3.1 introduction to MySQL

Why MySQL

• 1. It is an open source relational database management system
• 2. Small size, fast speed and low cost, suitable for small and medium-sized websites
• Official website: https://www.mysql.com/cn/
• Use version 5.7 here (as steady as an old dog)

3.2 MySQL installation

I won't repeat it here

4, Four languages of database (SQL)

4.1 DDL (data definition language)

Data Definition Language

effect:
Used to define database objects, such as data tables, views, indexes, etc

creat,drop,alter,truncate

4.2 DML (data manipulation language)

Data Manipulation Language

effect:
Insert, update and delete data in the database

insert,update,delete

4.3 DCL (data control language)

Data Control Language

effect:
Set user permissions and control transaction statements

grant，if...else，while，begin transaction

4.4 DQL (data query language)

Data Query Language

effect:
Query of data table records

A query block consisting of a SELECT clause, a FROM clause, and a WHERE clause

5, Command line connection to database

Open as Administrator cmd，cd /d /D:mysql:bin Switch to bin catalogue
mysqld -install Installing database services
net start mysql --open mysql service
net stop mysql --close mysql service
mysql -u root -p --Connect to database
update myql.user set authentication_string=password('***') where user='root' and Host = 'localhost'; -- Modify user password
flush privileges; -- Refresh permissions

Common statements

All commands need not be followed by a semicolon,All sql Be sure to add a semicolon at the end of the statement
--perhaps# Single-Line Comments 
/**/ multiline comment 
show databases; -- View all databases
use XXX; -- Select XXX database
show tables; -- View all tables in a database
select * from Table name; --View records in table
mysql> *** Switch to***database

show tables; -- View all tables in the database
describe[desc] *** -- see***All information in the table

create database ***; --Create a file called***Database of

exit -- Exit connection

6, Add, delete, modify and query

mysql What are the common data types?

MySQL It supports multiple types, which can be roughly divided into three categories:

-- numerical value

-- date/time

-- character string(character)type

Common operations of data recording:
-- increase
insert
-- delete
delete
-- modify
update
-- query
select

-- Create table create table Table name(field + data type + [constraint],...);
CREATE TABLE pet(
    NAME VARCHAR(20),
    OWNER VARCHAR(20),
    species VARCHAR(20),
    sex CHAR(1),
    birth DATE,
    death DATE
)DEFAULT CHARSET = utf8;


-- Check whether the table was created successfully
SHOW TABLES;

-- View table structure
DESC pet;

-- View data in table
SELECT * FROM pet;

-- Add data record to data table

INSERT INTO pet
VALUES ('lei','bing','daoge','f','2001-02-12',NULL);

-- Delete data

DELETE FROM pet 
WHERE NAME = 'lei';

-- Modify data

UPDATE pet SET NAME = 'leidaoge' 
WHERE OWNER = 'bing';

7, Restraint

mysql Table creation constraint

-- Primary key constraint
-- Self increasing constraint
-- Foreign key constraint
-- Unique constraint
-- Non NULL constraint
-- Default constraint

-- Primary key constraint
primary key
 It is uniquely determined, not repeated and not empty

-- Modify table structure and add primary key
-- grammar: alter table Table name add primary key(Field name);

ALTER TABLE pet ADD PRIMARY KEY(NAME);

-- Delete constraint
-- grammar: alter table Table name drop primary key(Field name);

ALTER TABLE pet DROP PRIMARY KEY(NAME);

-- use modify Modify fields and add constraints
-- grammar:alter table indicate modify Field name + data type + constraint
ALTER TABLE pet MODIFY id INT PRIMARY KEY;
-- composite keys 
Multiple master
 Key, union unique

-- Self increasing constraint
auto_increment

-- Foreign key constraint
foreign key(field X) references Table name X(field X) 
Two tables are involved, the primary table and the secondary table

1,There is no data in the primary table and cannot be used in the secondary table
2,If a record is referenced by a secondary table, it cannot be deleted

-- Unique, non NULL constraint
 The primary key constraint is unique+Non NULL constraint

-- Default constraint
default XXX
 When we insert a field value, the default value is used if no value is passed

-- Three design paradigms of database
1,First paradigm 
Remove field
2,Second paradigm
1+Remove table
3,Third paradigm
2+Cannot have transitive dependencies