Article brief introduction

When learning relational database, the database in the textbook uses Oracle relational database, while I use MySQL database for other reasons. From the perspective of database type, both are relational databases, and there is not much difference in data access mode. But after all, they are two different relational databases, and Oracle has more powerful functions. After all, one is free and the other has a paid version. This paper takes MySQL data as an example, summarizes some DDL, DML and DCL statements in MySQL database based on the data I summarized during my study.

DDL (Data Definition Language)

Database definition language, mainly used for the definition of data, data tables, etc. It mainly involves create statement, alter statement, drop statement, etc. For example:
Create basic table

create table allbook(
bookid char(20),
ISBN char(20),
borrowed char(4),
borrowedinfo char(50)
); //There is no integrity constraint when creating a table, because later we will talk about how to use alter statement to add constraints. In fact, integrity constraints can be used when creating a table, which will be more convenient.

Modify basic table

alter table tablename add column columnname varchar(20) not null after beforecolumnname;  //Insert column after specified field
ALTER TABLE tb_article MODIFY COLUMN NAME VARCHAR(50); 	 //Modify field type and length
ALTER TABLE tb_article CHANGE name1 name2 VARCHAR(30); 	//Modify field name

Add integrity constraint

alter table test add primary key(test_no);	//Primary key constraint
alter table test add unique(test_student);	//unique constraint
alter table test drop primary key; 	//Delete the primary key in this table

alter table test add foreign key (test_no) references test1(test_no);
alter table test add constraint N1 foreign key (test_no) references test1(test_no);	//Add foreign key constraint
alter table test drop foreign key Foreign key constraint name; 	//Delete foreign key constraint

alter table test modify test_student char(10) not null; 	//Establish non empty constraints
alter table test modify test_student char(10); 	//Delete non empty constraint

alter table worker add check(sex in ('male','female'));	//check constraints
alter table reader add check(readeridcode like '\d{14}[0-9a-zA-Z])|(\\d{17}[0-9a-zA-Z]'); //Use regular expressions for constraints (MySQL may not support)

You can specify a name for a foreign key constraint, for example:

alter table borrow add constraint C3 foreign key (borrowidcode) references reader(borrowidcode);
alter table borrow add constraint C4 foreign key (bookid) references allbooks(bookid);
alter table borrow add constraint C5 foreign key (finecode) references finetype(finecode); //Among them, borrow is the table name of the borrowing table, finetype is the table name of another table, and the attributes in the table are in parentheses.

Create trigger

delimiter //
create trigger tri_borrowbook after insert on borrow
for each row
begin
declare t_borrowcode char(4);
declare t_bookid char(20);
select count(*) from borrow into t_borrowcode;
select bookid from borrow where borrowcode=t_borrowcode into t_bookid;
update allbooks set borrowed='yes' where allbooks.bookid=t_bookid;
commit;
end;
//

MySQL needs to define an end symbol to indicate the end of a trigger or stored procedure when creating a trigger or stored procedure. Use the delimiter to define the terminator. As for the syntax of triggers, please refer to this article https://www.cnblogs.com/fanqisoft/p/10782798.html if you are not clear about what you need to learn
Create stored procedure

delimiter //
create procedure proc_borrow_books(
in p_borrowcode char(20),
in p_borrowidcode char(20),
in p_bookid char(20) //in is followed by the name and type of the input parameter, which can be understood as a function
)
begin
declare p_borrowed char(4); //Defining variables
select borrowed into p_borrowed from allbooks where bookid=p_bookid; //Assign values to variables
if p_borrowed='no' then
insert into borrow(borrowcode,borrowidcode,bookid,borrowtime) values(p_borrowcode,p_borrowidcode,p_bookid,current_date());
update allbooks set borrowed='yes' where bookid=p_bookid;
commit;
else
select 'The book has been lent';
end if;
end
//

The stored procedure is complex. If you can't understand the example, please refer to this blog https://blog.csdn.net/a_yuyes / article / details / 52795793

Create function

delimiter //
Create function func? Name (parameter list) 
returns varchar (50) / / sets the return type of the function
begin
	Function body;
    Return 'function returns the result'; / / the return value of the function
end;
//

Delete function can use

DROP FUNCTION func_name;

The creation of a function is similar to that of a stored procedure, but they are not the same

select function name (parameter list);

The use of functions is also more complex. Please refer to the more detailed article https://blog.csdn.net/u014207606/article/details/84856344

Create user

create user user1 identified by 'user1password';
grant all privileges on user3550 to user1; //Give the user dba permission, and user3550 is the dba role
grant execute on procedure proc_borrow_books to user1; //Give permission to execute stored procedures

Create roles

create role 'Role name'；

Create users and authorize with roles

create user user2 identified by 'user2password';
grant user2_ROLE to user2; //User2? Role is a role that has been created and authorized

Grant and revoke permissions for roles

GRANT ALL ON Database name.* TO 'Role name';   //Give all permissions to all tables in the database
GRANT SELECT ON Database name.* TO 'Role name';     //Query permission of all tables in the database
GRANT INSERT, UPDATE, DELETE ON Database name.* TO 'Role name';  //For the modification permission of all tables in the database, you can change the database name to table name or view name, etc

//Reclaim role or role permissions
REVOKE role FROM user;    //Role of recovering the account
REVOKE INSERT, UPDATE, DELETE ON Database name.* FROM 'Role name';   //Reclaim the power of the role

Changed the rights of the role, which will affect the rights of the account owning the role

Create view

create view VIEW_READER(ISBN,bookname,author,publisher,typename)
as select ISBN,bookname,author,publisher,typename
from books,booktype
where books.typecode=booktype.typecode;

DML (Data Manipulation Language)

Database operation language is mainly used to operate data. For example, select, update, insert, etc. As follows:
select query statement

select max(price),avg(price) from books group by publisher; //Using aggregate functions and group statements

select author,publisher,price from allbooks,books where books.ISBN=allbooks.ISBN and bookname='The Dream of Red Mansion' and borrowed = 'no'; //join query

nested queries

select readername,readerunit,readersex,readeraddress,readerphone
from reader
where borrowidcode in
(select borrowidcode
from borrow
group by borrowidcode
having count(*)>=2); 

Insert insert statement

insert into reader values('20051001','Faye Wong','Computer College of Sichuan Mianyang University of Western Science','female','East six E','13550374857','541324399876532937'); //reader is the table name

update modify statement

update Table name set attribute where Conditional statement;
update allbooks set borrowed='yes' where bookid=p_bookid;

Delete delete statement

DELETE FROM Table name WHERE Conditional statement;
DELETE FROM books WHERE bookid='3550';

Call call statement
Call statements are mainly used to call stored procedures

call Stored procedure name(parameter list); 
call proc_borrow_books('7','20051001','2001231');

DCL (Data Control Language)

The database control language is used to grant or reclaim some privilege to access the database, control the time and effect of database manipulation, and control the database. For example, set statement, commit statement, rollback statement, etc. For example:

Set database security level

set sql_safe_updates =  0; //You can set the security level of the database to 0 or 1. Sometimes it is because the security level of the database is too high when you cannot add, delete, modify or query.

As for commit and rollback statements, you can call them directly when you need to submit data and rollback.

MySQL database backup

Database backup can directly use MySQL's visualization workbench for backup, which is simple and convenient, of course, it can also use the command line. For example:
Export database

mysqldump -u User name -p Database name > Exported file path and file name
mysqldump -u S5120173550 -p user3550 > D:\MySQL_backup\user3550.sql

Export data table

mysqldump -u User name -p Database name table name> Exported file name
mysqldump -u S5120173550 -p user3550 orderborrow > D:\MySQL_backup\user3550_orderborrow.sql

Command line is not recommended, because both export and import are troublesome. It is convenient to use the visualization workbench provided by MySQL directly.

Add: some common MySQL view statements

show create table tablename;	//Check the constraints in the table.
show databases; //Show all databases
show grants for username; //view user permission
show create procedure proc_name; //View stored procedures
show create function func_name; //View created functions