MySQL programming on linux (III): MySQL advanced syntax, linuxmysql

MySQL programming on linux (III): MySQL advanced syntax, linuxmysql
[Copyright statement: respect originality. For reprinted content, Please retain the Source: blog.csdn.net/shallnet. this document only provides learning and communication, and does not apply for commercial use.] This section describes Mysql advanced syntax, including attempt, stored procedure, and index, optimize database design and query technologies by using attempts and triggers to improve data processing capabilities and efficiency. View operation: A view is a virtual table whose content is defined by the query. A view does not exist in the database as a stored data value set. row and column data are derived from the table referenced by the query of the definition view and are dynamically generated when the reference attempt is made. From the outside of the database system, a view is like a table. Common operations that can be performed on a table can be applied to a view, such as query, insert, modify, and delete.
A view is an SQL statement stored in a database for query. Using a view can make complex queries easy to understand and use, and hide some data to ensure security. Once defined, a view is stored in the database. The corresponding data is not stored in the database as a table. The data displayed in the view is only stored in the basic table. Changes to the data seen in the view or changes to the basic table data are reflected in the basic table or view.
Using a view has the following benefits: 1. A view set allows users to focus only on specific data of interest and specific tasks they are responsible. 2. Simplify the operation. You do not have to re-write these complex query statements every time you execute the same operation query. You only need a simple query view statement. 3. Custom Data and views allow different users to view different datasets in different ways, which is important when many users of different levels share the same database. 4. Merge and split data. 5. Security: users can view and modify only the data they can see through the view. Other databases or tables are neither visible nor accessible.
Create view Syntax: CREATE [or replace] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] check option] select_statement is a SELECT statement that defines a view. This statement can be selected from the base table or other views. View is a database. By default, a new view is created for the current database. To explicitly create a view in a given database, you must specify the name db_name.view_name when creating the view. Tables and views share the same namespace in the database. Therefore, the database cannot contain tables and views with the same name.
For example, create a view containing the id and the last_login:

mysql> create view db_users.v_idlogin as select userid, last_login as login_date  from tb_users where birth < '1980-1-1';Query OK, 0 rows affected (0.04 sec)mysql> show tables;+--------------------+| Tables_in_db_users |+--------------------+| tb_users           || v_idlogin          |+--------------------+2 rows in set (0.00 sec)mysql> select * from v_idlogin;+--------+------------+| userid | login_date |+--------+------------+|  10003 | 2014-09-20 ||  10004 | 2015-02-10 |+--------+------------+2 rows in set (0.02 sec)

The delete view is as follows:

mysql> drop view if exists v_idlogin;Query OK, 0 rows affected (0.00 sec)mysql> show tables;+--------------------+| Tables_in_db_users |+--------------------+| tb_users           |+--------------------+1 row in set (0.00 sec)

Stored Procedure: A good database will use the stored procedure. A stored procedure is a set of SQL statements for specific functions and is compiled and stored in the database. The essence of a stored procedure is to deploy a set of definition codes and SQL statements on the Database End. Common or complex work is written in advance using SQL statements and stored with a specified name, in the future, if you want the database to provide services with the same functions as the predefined stored procedure, you only need to call the service to automatically complete the command. Stored Procedures enhance the functionality and flexibility of the SQL language. Stored procedures can be written using flow control statements to complete complex operations with high flexibility. Stored Procedures ensure data security and integrity. Through stored procedures, users without permissions can indirectly access the database under control to ensure data security. Stored PROCEDURE Syntax: create procedure sp_name ([proc_parameter [,...]) [characteristic...] routine_body statement to CREATE a storage subroutine. To CREATE a subroutine in MySQL 5.1, you must have the create routine permission and the alter routine and EXECUTE permissions are automatically granted to its creator. If the binary log function is permitted, you may also need the SUPER permission. By default, the subroutine is associated with the current database. To explicitly associate a subroutine with a given database, you can specify the name db_name.sp_name when creating a subroutine.
You can use the in... END compound statement to store subprograms to contain multiple statements. [Begin_label:] BEGIN [statement_list] END [end_label] statement_list represents the list of one or more statements. Each statement in statement_list must end with a semicolon. Create a simple stored procedure as follows:

mysql>mysql> delimiter $mysql> create procedure get_one_user(    -> in id int)    -> begin    -> select * from tb_users where userid>id;    -> end;    -> $Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;

Call a stored procedure using the call statement:

mysql> call get_one_user(10005)    -> ;+--------+----------+------------+------------+| userid | username | birth      | last_login |+--------+----------+------------+------------+|  10007 | Hamilton | 1988-07-07 | 2014-11-16 ||  10008 | Johnson  | 1986-06-07 | 2015-01-23 ||  10009 | James    | 1989-08-17 | 2013-12-23 ||  10010 | james    | 1984-12-30 | 2014-12-23 ||  10011 | Gay      | 1987-02-03 | 2014-12-23 ||  10012 | Kaman    | 1981-04-04 | 2010-01-13 ||  10006 | Wade     | 1982-03-04 | 2012-05-19 |+--------+----------+------------+------------+7 rows in set (0.04 sec)Query OK, 0 rows affected (0.04 sec)

Index: an index is a structure used to sort the values of one or more columns in a database table, is a list of logical pointers that physically identify these worthy pages in a table with a set of values and corresponding pointers. Creating an index can greatly improve the system performance. If no index is available, mysql must scan all the records of the entire table from the first record until the required records are found, the more records there are in the table, the higher the cost of this operation. However, if an index has been created on the column used as a search condition, mysql can quickly obtain the location of the target record without scanning any records. Syntax for creating an index: create [unicode | fulltext | spatial] index index_name [using index_type] on tbl_name (index_col_name ,...) mysql provides the following types of indexes: create index, create unique index, primary key, and fulltext ). Common indexes, unique indexes, and primary keys can also be specified when a table is created or modified. Taking the unique index as an example, the syntax for directly creating a unique index is as follows:

mysql> select * from tb_users;+--------+----------+------------+------------+| userid | username | birth      | last_login |+--------+----------+------------+------------+|  10000 | Allen    | 1981-01-01 | 2014-02-02 ||  10001 | Ben      | 1982-04-02 | 2014-04-30 ||  10002 | Curry    | 1985-08-12 | 2014-01-17 ||  10003 | Davis    | 1978-07-12 | 2014-09-20 ||  10004 | Ellis    | 1979-09-02 | 2015-02-10 ||  10005 | Faried   | 1984-02-05 | 2014-12-01 ||  10007 | Hamilton | 1988-07-07 | 2014-11-16 ||  10008 | Johnson  | 1986-06-07 | 2015-01-23 ||  10009 | Jackson  | 1989-08-17 | 2013-12-23 ||  10010 | James    | 1984-12-30 | 2014-12-23 ||  10011 | Gay      | 1987-02-03 | 2014-12-23 ||  10012 | Kaman    | 1981-04-04 | 2010-01-13 ||  10006 | Wade     | 1982-03-04 | 2012-05-19 |+--------+----------+------------+------------+13 rows in set (0.00 sec)mysql> create unique index idx_name on tb_users(username(5));Query OK, 13 rows affected (0.13 sec)Records: 13  Duplicates: 0  Warnings: 0mysql> 

A primary key is a special unique index that does not allow null values. Generally, a primary key index is created when a table is created. A table can have only one primary key.
In general, you should create indexes on these columns: columns that frequently need to be searched (speeding up the search) and columns that are frequently used for connection (speeding up the connection) columns that need to be searched by range, columns that need to be sorted frequently, and columns that are frequently used in the where clause.