MySQL views, stored procedures, user management

1. View

A virtual table is a query that is often used as a virtual table. Developers do not need to write SQL again when querying, but directly call the corresponding view. After calling the view, MySQL will execute the query SQL.

Create a view

create view vi_select as select * from emps ;

Create or replace an existing view

create or replace view vi_select as select * from emps inner join dept on dept.id=emps.id

# modify view

Alter VIEW View name as SELECT statement



Display the creation of the view

Show create view Specifies the view name

# view view

show tables like 'vi_%' ; This is the same as the query table, so it is better to create a view with a prefix, through fuzzy query results



# delete view

Drop view Specifies the view name.



# rename

Rename table view name to New view name;



Copy the code

1.1 Failure to Perform DML operations on a View

selectClause containsdistinct
selectClause contains group functions
selectThe statement containsgroup by
selectThe statement containsorder by
selectThe statement containsunionorunion allEtc set operator
whereClause contains subqueries
fromContains more than one table
View columns contain computed columns
Cannot proceed if there are non-null constraints in the base tableinsert

2. The function

As with functions in other languages, there is a return value

Grammar:

Create a function

Create function Specifies the function name (parameter list) returns The data type

begin

	sql ;

	return ;

end ;



# call function

Select the function name



# check the function creation statement

Show create function Specifies the function name

View all functions

show function status like '' ;



# delete function

Drop function Specifies the function name

Copy the code

3. Stored procedures

A set of programmable functions, created by compilation and stored in a database to complete a set of SQL statements for a particular function, that can be called by the name of a stored procedure.

DELIMITER is usually used together. DELIMITER changes the end symbol of SQL statements. By default;

Create a stored procedure and pass IN parameters, IN for input parameters and OUt for output parameters.

DELIMITER $

Create procedure name (IN a int, IN b int,out sum int)

begin

Declare a variable C

	declare c int ;

	if a is null then set a = 0 ;

	end if

	if b is null then set b = 0 ;

	end if ;

	set sum = a+b ;

END

$

DELIMITER ;

Copy the code

4. The trigger

Indicates that an event (write operation) occurs on a table, and the pre-compiled SQL statement is automatically executed to perform the related operation. Trigger events are atomic to the operations of SQL statements in triggers.

It can be used to preprocess data

Create trigger Indicates the name of the trigger

{after | before} # trigger time

{insert | update | delete} # triggering events

For each row # fixed, this trigger is triggered whenever a row of data changes

begin 

	SQL

end ;	



# check trigger

show triggers from databaseName;



# delete trigger

drop trigger if exists 

Copy the code

Insert data

Generate a random string with n for digits

create function rand_string(n int) returns varchar(255)

begin 

    declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwyzABCDEFGHIJKLMNOPQRSTUVWXYZ'; 

    declare return_str varchar(255) default ''; 

    declare i int default 0 ; 

    while i< n do 

        set 	return_str=concat(return_str,substring(chars_str,floor(1+rand()*52),1));

        set i = i +1 ; 

    end while ; 

    return return_str ; 

end?



# Generate a random number

create function rand_num() returns int(5)

begin  

    declare i int default 0 ; 

    set i = floor(100+rand()*10); 

    return i ; 

end?



Create a stored procedure to insert data

crate procedure insert_emp(IN start int(10),IN max_num int(10)) 

begin 

    declare i int default 0 ; 

    set autocommit = 0 ; 

    repeat 

        set i = i +1 ; 

        insert into emp(name,age) values(rand_string(6),(start+i)); 

        until i = max_num 

    end repeat; 

    commit; 

end?



Call insert_emp (100500000);

Copy the code

5. User management

# create user

Create user User name identified by password

MySQL > query all users in MySQL

select host,user from mysql.user;

Change the password of the current user

Set password = password(password)

Change the password of another user

Update mysql.user set password=password(password) where user=" username"

Change the user name

Update mysql.user set user = user where user = "user"

# delete user

Drop user Indicates the user name

Copy the code

Field in the user table. You can grant permissions to users directly by modifying the fields in this table

Select_priv. Determines whether the user can SELECT data using the SELECT command.



Insert_priv. Determines whether the user can INSERT data using the INSERT command.



Update_priv. Determines whether users can modify existing data using the UPDATE command.



Delete_priv. Determines whether the user can DELETE existing data by using the DELETE command.



Create_priv. Determines whether users can create new databases and tables.



Drop_priv. Determines whether the user can delete existing databases and tables.



Reload_priv. Determines whether a user can execute specific commands to refresh and reload the various internal caches used by MySQL, including logs, permissions, hosts, queries, and tables.



Shutdown_priv. Determines whether the user can shut down the MySQL server. You should be very careful about giving this permission to anyone other than the root account.



Process_priv. Determines whether users can view the processes of other users by running the SHOW PROCESSLIST command.



File_priv. Determine whether a user can run the SELECT INTO OUTFILE and LOAD DATA INFILE commands.



Grant_priv. Determines whether a user can grant permissions already granted to the user to another user. For example, if a user can insert, select, and delete information in database Foo, and GRANT permission, that user can GRANT any or all of his or her permissions to any other user in the system.



References_priv. The present is just a placeholder for some future feature; It doesn't work right now.



Index_priv. Determines whether a user can create and drop table indexes.



Alter_priv. Determines whether the user can rename and modify the table structure.



Show_db_priv. Determines whether the user can view the names of all databases on the server, including those to which the user has sufficient access. Consider disabling this permission for all users unless there are compelling reasons.



Super_priv. Determine whether the user can perform some powerful administrative functions, such as deleting user processes by using the KILL command and using SET

GLOBAL Modifies GLOBAL MySQL variables and executes various commands about replication and logging.



Create_tmp_table_priv. Determines whether users can create temporary tables.



Lock_tables_priv. Determines whether a user can use the LOCK TABLES command to block access/changes to the table.



Execute_priv. Determines whether the user can execute the stored procedure. This permission is only meaningful in MySQL 5.0 and later.



Repl_slave_priv. Determines whether users can read binary log files used to maintain a replicated database environment. This user is located in the main system and facilitates communication between the host and client.



Repl_client_priv. Determines whether the user can determine the location of the replication slave and master servers.



Create_view_priv. Determines whether a user can create a view. This permission is only meaningful in MySQL 5.0 and later. For more information on views, see Chapter 34.



Show_view_priv. Determines whether the user can view the view or see how the view is executed. This permission is only meaningful in MySQL 5.0 and later. For more information on views, see Chapter 34.



Create_routine_priv. Determines whether users can change or discard stored procedures and functions. This permission was introduced in MySQL 5.0.



Alter_routine_priv. Determines whether a user can modify or delete storage functions and functions. This permission was introduced in MySQL 5.0.



Create_user_priv. Determines whether the USER can execute the CREATE USER command, which is used to CREATE a new MySQL account.



Event_priv. Determines whether users can create, modify, and delete events. This permission was added to MySQL 5.1.6.



Trigger_priv. Determines whether a user can create and delete triggers. This is new in MySQL 5.1.6.

Copy the code

5.1 Viewing Current User Rights

show grants for root@'%'

% indicates that all IP addresses are accessible



Select * from mysql.user where user=' username '

Copy the code

5.2 Granting User Rights

Directly modifyingmysql.userTable.

# grant permission

update mysql.user set Select_priv='Y' , Insert_priv = 'Y',Update_priv ='Y' ,Delete_priv='Y',Create_priv='Y' where user='stack';

update mysql.user set reload_priv='Y', process_priv='Y',index_priv='Y', alter_priv='Y',lock_tables_priv='Y',execute_priv='Y' where user='stack';

update mysql.user set create_view_priv='Y',show_view_priv='Y',create_routine_priv='Y',alter_routine_priv='Y' where user='stack';

# refresh permission

flush privileges;

Copy the code

This article is formatted using MDNICE

MySQL views, stored procedures, user management

1. View

1.1 Failure to Perform DML operations on a View

2. The function

3. Stored procedures

4. The trigger

5. User management

5.1 Viewing Current User Rights

5.2 Granting User Rights

Related Posts

MySQL > select * from ‘MySQL’;

50,000 alarms a day, Tencent how to achieve “coffee operation and maintenance”?

UI design industry “Yanxi strategy”, teach you from bronze to king