(1) Overview

In the postgraduate entrance examination, I had a professional course in database, so I had a solid grasp of the basic knowledge of database at that time. Last week, when I opened the database books I had turned over countless times in the postgraduate entrance examination, I found that many contents had been forgotten. I just decided to start writing database related articles, so I have the following content.

(2) Basic concepts

DB: database DB is a collection of relevant data stored in the computer for a long time, organized and uniformly managed.

DBMS: database management system is a layer of data management software between users and OS. It provides users or applications with methods to access DB.

DBS: database system DBS is a system composed of computer hardware, software and data resources to realize the organized and dynamic storage of a large amount of associated data and facilitate multi-user access, that is, a computer system using database technology.

Connection element: the number of entity sets related to a connection is called the connection element.

Conceptual model: a model of the global logical structure of DB that expresses the views of users' needs.

Logical model: the model of DB global logical structure expressing the viewpoint of computer implementation. Logical models mainly include hierarchy, mesh, relationship and object models.

External model: a model that expresses the local logical structure of DB used by users.

Internal model: a model that represents the physical structure of DB.

DDL: language for defining the three-level structure of DB

DML: language for querying database

DD: data dictionary, which stores the DB defined by the three-level structure. All operations on the database can be realized through DD

Primary key: a combination of data columns or attributes in a database table that uniquely and completely identifies the stored data object. A data column can only have one primary key, and the value of the primary key cannot be missing.

Foreign key: the primary key of another table existing in one table is called the foreign key of this table.

Trigger: a special stored procedure that is triggered by events and executed. For example, an operation on the data of one table will trigger the data operation of another table.

Stored procedure: it is a precompiled SQL statement. Its advantage is that it allows modular design, that is, it only needs to be created once and can be called many times in the program later.

View: a virtual table that can selectively display part of data. It is usually a subset of rows or columns with one or more tables. Changes to the view affect the base table

Temporary table: only the table visible in the current connection. The table space will be cleared automatically after the connection is closed.

create TEMPORARY table test222(
id int(40) not null primary KEY
);

Inner connection: only the matching rows are connected

Left outer join: contains all rows of the left table (no matter whether there are matching rows in the right table) and all matching rows in the right table

Right outer join: contains all rows of the right table (no matter whether there are matching rows in the left table) and all matching rows in the left table

All outer join: contains all rows of the left and right tables, regardless of whether there are matching rows in the other table.

Cross connect: generate Cartesian product - it does not use any matching or selection criteria, but directly matches each row in one data source with each row in another data source.

(3) Database paradigm

When designing relational database, we should comply with different specification requirements and design a reasonable relational database. These different specification requirements are called different paradigms. The higher the paradigm, the lower the data redundancy.

There are three paradigms involved in actual development: the first paradigm, the second paradigm and the third paradigm

3.1 first paradigm

If the attributes of each column in the database are non decomposable atomic values, it indicates that the database meets the first normal form.

3.2 second paradigm

Based on the first paradigm, the second paradigm eliminates the partial functional dependence of non primary attributes on primary attributes. In short, every column in the table should be related to the primary key. It cannot be related to only one part of the primary key

Take such a data as an example, there will be multiple commodities in the same order, so the primary key is the order id and commodity id, but the commodity name only depends on the commodity id, which does not meet the requirement that every column in the table should be related to the primary key, not only a part of the primary key, so it is only the first paradigm.

Modify it to the following table, so as to meet the second paradigm.

3.3 the third paradigm

On the basis of the second normal form, the third normal form eliminates the transfer function dependence of non primary attributes on primary attributes. In short, each column of data is directly related to the primary key, not indirectly.

For example, a student list:

All the above attributes depend on the student number and meet the second paradigm. In short, all the following attributes can be determined through the student number, but the head teacher's gender is brought out through the head teacher's name. There is a transmission dependency of student number - > head teacher's name - > head teacher's gender, so it does not meet the third paradigm.

In order to meet the third paradigm, we must eliminate the transitive dependency.

It can be changed to:

(4) Basic operations of sql

4.1 basic operation of database

#view the database
show databases

#Create database
create database db DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

#Use database
use db;

4.2 users

#Create user
create user 'user name'@'IP address' identified by 'password';

#delete user
drop user 'user name'@'IP address';

#Modify user
rename user 'user name'@'IP address'; to 'New user name'@'IP address';;

#Change Password
set password for 'user name'@'IP address' = Password('New password')

When creating a user:

User name: the name of the user who created the

IP address: specify which server the user can log in from, local user localhost, any remote machine with "%"

Password: the login password of the user

4.3 authority

#View permissions:
show grants for 'user'@'IP address' 

#to grant authorization:
grant jurisdiction on database.surface to 'user'@'IP address'

#Cancel authorization:
revoke jurisdiction on database.surface from 'user'@'IP address'

Permission: the user's operation permission, such as SELECT, INSERT, UPDATE, etc. if you want to grant the permission, use ALL, for example:

GRANT SELECT, INSERT ON student.user TO 'javayz'@'%';

The following command enables the specified user to authorize other users

GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;

4.4 operation of table

#Query all tables
show tables;

#Create table
CREATE TABLE `user` (
  `id` int(11) NOT NULL auto_increment,                   # not null means cannot be empty, auto_increment means self increment
  `name` varchar(255) DEFAULT 'javayz',                 # Default indicates the default value
  PRIMARY KEY (`id`)                                      # Set the id column as the primary key
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#Empty table
truncate table Table name

#Delete table
drop table Table name

#Add column
alter table Table name add Column name type

#Delete column
alter table Table name drop column Listing

#Modify column type
alter table Table name modify column Column name type;

#Modify column name and column type
alter table Table name change Original column name new column name type;

#Add primary key
alter table Table name add primary key(Listing);

#Delete primary key
alter table Table name drop primary key;

#Add foreign key
alter table From table add constraint Foreign key name (such as: FK_From table_(main table) foreign key From table(Foreign key field) references Main table(Primary key field);

#Delete foreign key
alter table Table name drop foreign key Foreign key name

#Modify default values
ALTER TABLE user ALTER name SET DEFAULT 'javayz2';

#Delete default
ALTER TABLE user ALTER name DROP DEFAULT;

4.5 operation of data

Basic addition, deletion, modification and query:

#increase
insert into surface (Listing,Listing...) values (value,value,...)

#Delete
delete from surface where condition

#change
update surface set field='value' where condition

#check
select value from surface where condition

Other conditions:

#Wildcard like% matches multiple characters_ Match single character
select * from surface where name like '%java_'  

#limit limits the number of output lines
select * from surface limit 3   #First 3 lines
select * from surface limit 3,5;  #5 lines starting from line 3

#order by sort   
select * from surface order by column asc  #asc positive order, desc negative order

#Group by grouping (group by must be after where and before order by)
select name from surface group by name