(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