One article summarizes the basic knowledge points of the database! (Recommended collection)

One article summarizes the basic knowledge points of the database! (Recommended collection)

I heard that the WeChat search for "Java Fish Baby" will change and become stronger!

This article is included in JavaStarter , which contains my complete Java series articles, you can check it out for study or interview.

(I. Overview

During the postgraduate entrance examination, I had a professional course on databases, so I had a solid grasp of the basic knowledge of databases at that time. When I opened the database books that I had read countless times during the postgraduate entrance examination last week, I found a lot of content. Forget about it. I just decided to start writing database-related articles, and I have the following content.

(2) Basic concepts

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

DBMS : The database management system is a layer of data management software located between the user and the OS. It provides a method for users or applications to access the DB.

DBS : Database system DBS is a system composed of computer hardware, software, and data resources that realizes organized and dynamic storage of large amounts of associated data, which is convenient for multiple users to access, that is, a computer system that uses database technology.

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

Conceptual model : A model of the global logical structure of the DB that expresses the views of user needs.

Logical model : A model of the DB global logical structure that expresses the viewpoint of computer realization. There are four main logic models: hierarchy, mesh, relationship and object model.

External model : A model of the local logical structure of the DB that expresses the user's view of use.

Internal model : A model that expresses the physical structure of the DB.

DDL : A language that defines the three-level structure of DB

DML : The language for querying the database

DD : Data dictionary, storing the DB defined by the three-level structure, the operation of the database must be realized through DD

Primary key : A combination of data columns or attributes in a database table that uniquely and completely identify storage data objects. 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 that exists in one table is called the foreign key of this table.

Trigger : A special stored procedure triggered by an event to be executed. For example, an operation on one table's data will trigger another table's data operation.

Stored procedure : It is a pre-compiled SQL statement. The advantage is that it allows a modular design, that is, it only needs to be created once and can be called multiple times in the program later.

View : It is a virtual table that can selectively display part of the data. The view is usually a subset of rows or columns of one table or multiple tables. Modifications to the view will affect the basic table

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

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

Inner join : only join matching lines

Left outer join : Contains all rows of the table on the left (regardless of whether there are rows matching them in the table on the right), and all matching rows in the table on the right

Right outer join : Contains all rows of the table on the right (regardless of whether there are rows matching them in the table on the left), and all matching rows in the table on the left

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

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

(3) The paradigm of the database

When designing a relational database, follow different specifications and design a reasonable relational database. These different specifications 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 Normal Form

If each column attribute in the database is an indecomposable atomic value, then the database satisfies the first normal form.

3.2 2.Normal Form

On the basis of the first paradigm, the second paradigm eliminates the partial functional dependence of non-primary attributes on the primary attributes. Simply put, every column in the table must be related to the primary key. Not only related to a certain part of the primary key

Taking such a data as an example, there will be multiple products in the same order, so the primary key is the order id and the product id, but the product name only depends on the product id, not satisfying that each column in the table must be related to the primary key, not just It is related to a certain part of the primary key, so it is only the first normal form.

Modify it to the following table so that it satisfies the second normal form.

3.3 Third Normal Form

On the basis of the second paradigm, the third paradigm eliminates the transfer function dependence of non-primary attributes on the primary attributes. Simply put, each column of data is directly related to the primary key, but not indirectly .

For example, a student table:

All the above attributes are dependent on the student number, which satisfies the second paradigm. Simply put, all the following attributes can be determined by the student number, but the class teacher s gender is brought out by the class teacher s name, and there is a student number -> class teacher s name- > The class teacher's gender is transitively dependent, so it does not satisfy the third paradigm.

To satisfy the third normal form, we must eliminate transitive dependence.

Can be changed to:

(4) The basic operation of sql

4.1 Basic operation of the database

# 
show databases

# 
create database db DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

# 
use db;
 

4.2 Users

# 
create user ' '@'IP ' identified by ' ';

# 
drop user ' '@'IP ';

# 
rename user ' '@'IP '; to ' '@'IP ';;

# 
set password for ' '@'IP ' = Password(' ')
 

When creating a user:

Username: Username created

IP address: Specify which server the user can log in from, the local user is localhost, and any remote machine uses "%"

Password: The password for the user to log in

4.3 permissions

# 
show grants for ' '@'IP ' 

# 
grant   on  .  to ' '@'IP '

# 
revoke   on  .  from ' '@'IP '
 

Permission: the user's operating permissions, such as SELECT, INSERT, UPDATE, etc., if you want to grant the permissions, use ALL. For example:

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

Use the following command to allow the specified user to authorize other users

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

4.4 Operations on the table

# 
show tables;

# 
CREATE TABLE `user` (
  `id` int(11) NOT NULL auto_increment,                   # not null ,auto_increment 
  `name` varchar(255) DEFAULT 'javayz',                 # default  
  PRIMARY KEY (`id`)                                      #  id 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 
truncate table  

# 
drop table  

# 
alter table   add    

# 
alter table   drop column  

# 
alter table   modify column    ;

# 
alter table   change      ;

# 
alter table   add primary key( );

# 
alter table   drop primary key;

# 
alter table   add constraint  FK_ _  foreign key  ( ) references  ( );

# 
alter table   drop foreign key  

# 
ALTER TABLE user ALTER name SET DEFAULT 'javayz2';

# 
ALTER TABLE user ALTER name DROP DEFAULT;
 

4.5 Operations on data

Basic addition, deletion, modification and investigation:

# 
insert into   ( , ...) values ( , ,...)

# 
delete from   where  

# 
update   set  =' ' where  

# 
select   from   where  
 

Other conditions:

# like % ,_ 
select * from   where name like '%java_'  

#limit  
select * from   limit 3   # 3 
select * from   limit 3,5;  # 3 5 

#order by     
select * from   order by   asc  #asc desc 

#group by group by  where order by 
select name from   group by name