Shulou(Shulou.com)06/01 Report--

This article mainly introduces the detailed introduction of the VIEW view of the Mysql database, the contents of the article are carefully selected and edited by the author, with a certain pertinence, and is of great significance to everyone's reference, the following is a detailed introduction of the VIEW view of the Mysql database with the author.

I. brief introduction

Developed by MySQL AB, it is the most popular open source SQL database management system with the following main features:

1. It is a database management system.

2. It is an associated database management system.

3. It is a kind of open source software, and there are a lot of shared MySQL software available

4. MySQL database cloud servers are fast, reliable and easy to use.

5. MySQL CVM works in client / CVM mode, or in embedded system

The InnoDB storage engine saves InnoDB tables in a tablespace that can be created by several files. In this way, the size of the table can exceed the maximum capacity of individual files. Tablespaces can include raw disk partitions, making large tables possible. The maximum capacity of the tablespace is 64TB.

2. VIEW view (table)

2.1. Introduction to the View VIEW command:

The VIEW view is the stored SELECT statement data

1. Create view command format:

CREATE

[OR REPLACE]

[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

[DEFINER = {user | CURRENT_USER}]

[SQL SECURITY {DEFINER | INVOKER}]

VIEW view_name [(column_list)]

AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

Format: CREATE VIEW view_name AS select_statement creates a file named view_name

The view, the content of the view, is the content queried by the select statement.

Usage: CREATE VIEW sct AS SELECT Name,Cname,Tname FROM students,courses,teachers WHERE

Students.CID1=courses.CID AND courses.CID=teachers.TID

Store the CID equivalent data in the query students,courses,teachers table in the sct view.

2. Command function:

The SELECT data of the database is stored as a view, and the view is also equivalent to a virtual table. The table in the SELECT statement stored in the view is called the base table. The view can not be modified, but can only be modified based on the base table. Therefore, it is generally not recommended that MySQL create views.

3. Command parameters:

DISTING unique, non-repetitive

SELECT [DISTINCT] * FROM tb_name WHERE qualification

Select a unique, non-repeating entry that meets the criteria from the tb_ name table.

4. Example

1. See how the created table is created

Mysql > SHOW CREATE TABLE class\ G

* * 1. Row *

Table: class

Create Table: CREATE TABLE `class` (

`ID`int (11) NOT NULL AUTO_INCREMENT

`Name` char (20) NOT NULL

`Age` tinyint (4) NOT NULL

`Gender` varchar (10) NOT NULL

PRIMARY KEY (`ID`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=gbk

1 row in set (0.00 sec)

ERROR:

No query specified

Third, directly do not log in to the mysql client to modify the data in the mysql database

[root@lamp] # mysql-e 'CREATE DATABASE edb;' directly set up database edb

[root@lamp] # mysql-e 'SHOW DATABASES;'

+-+

| | Database |

+-+

| | information_schema |

| | edb |

| | hellodb |

| | mydb |

| | mysql |

+-+

[root@lamp ~] # mysql-e 'DROP DATABASE edb;' # delete edb database directly

[root@lamp] # mysql-e 'SHOW DATABASES;'

+-+

| | Database |

+-+

| | information_schema |

| | hellodb |

| | mydb |

| | mysql |

| | performance_schema |

| | students |

| | test |

| | testdb |

+-+

[root@lamp] # mysql-e'SELECT * FROM students.class;'

# do not log in to the mysql client and directly query the data in the class table in the students database.

+-- +

| | ID | Name | Age | Gender | |

+-- +

| | 1 | Yang Guo | 22 |

| | 2 | Guo Jing | 46 |

| | 3 | Xiao Longnv | 18 |

| | 4 | Huang Rong | 40 |

+-- +

Third, automatically generate random data values for testing

[root@lamp ~] # cat autoinsert.sh

#! / bin/bash

Function rand () {

Min=$1

Max=$ (($2-$min+1))

Num=$ (date +% s% N)

Echo $(($num%$max+$min))

}

Name= "stu"

For i in {1..11000}

Do

Agernd=$ (rand 1 10)

Genderrnd=$ (rand 1 2)

CID1rnd=$ (rand 1 10)

CID2rnd=$ (rand 1 20)

TIDrnd=$ (rand 1 10)

CTime=$ (date "+% Y-%m-%d% H:%M:%S")

Mysql-e "INSERT INTO jiaowu.students (Name,Age,Gender,CID1,CID2,TID,CreateTime) VALUES ('$Name$i','$Agernd','$Genderrnd','$CID1rnd','$CID2rnd','$TIDrnd','$CTime')"

Done

Exit 0

[root@lamp] # mysql-e "select COUNT (*) from jiaowu.students;"

+-+

| | COUNT (*) |

+-+

| | 11034 |

+-+

1:shell is attached to generate a specified range of random numbers and random strings

1. Use the system's $RANDOM variable

[plain] view plain copy

Fdipzone@ubuntu:~$ echo $RANDOM

17617

The range of $RANDOM is [0, 32767]

If you need to generate a random number of more than 32767, you can do it in the following ways.

Example: generate a random number of 400000 to 500000

[plain] view plain copy

#! / bin/bash

Function rand () {

Min=$1

Max=$ (($2-$min+1))

Num=$ (($RANDOM+1000000000)) # add a number of 10 digits and then find the remainder

Echo $(($num%$max+$min))

}

Rnd=$ (rand 400000 500000)

Echo $rnd

Exit 0

#! / bin/bashecho $($RANDOM%50+1)

two。 Use date +% s% N

Example: generate a random number of 1 to 50

[plain] view plain copy

#! / bin/bash

Function rand () {

Min=$1

Max=$ (($2-$min+1))

Num=$ (date +% s% N)

Echo $(($num%$max+$min))

}

Rnd=$ (rand 1 50)

Echo $rnd

Exit 0

3. Use / dev/random and / dev/urandom

/ dev/random stores the real-time data of the current running environment of the system, is a blocked random number generator, and sometimes needs to wait for reading.

/ dev/urandom non-blocking random number generator, read operation will not cause blocking.

Example: use / dev/urandom to generate a random number of 100,500, and use urandom to avoid blocking.

[plain] view plain copy

#! / bin/bash

Function rand () {

Min=$1

Max=$ (($2-$min+1))

Num=$ (cat / dev/urandom | head-n 10 | cksum | awk-F'{print $1}')

Echo $(($num%$max+$min))

}

Rnd=$ (rand 100500)

Echo $rnd

Exit 0

4. Use linux uuid

The full name of uuid is a universal unique identification code. The format contains 32 hexadecimal digits and is divided into five segments with a'- 'connection number. 32 characters in the form 8-4-4-4-12.

[plain] view plain copy

Fdipzone@ubuntu:~/shell$ cat / proc/sys/kernel/random/uuid

Fd496199-372a-403e-8ec9-bf4c52cbd9cd

Example: use linux uuid to generate 100,500 random numbers

[plain] view plain copy

#! / bin/bash

Function rand () {

Min=$1

Max=$ (($2-$min+1))

Num=$ (cat / proc/sys/kernel/random/uuid | cksum | awk-F'{print $1}')

Echo $(($num%$max+$min))

}

Rnd=$ (rand 100500)

Echo $rnd

Exit 0

5. Generate random strings

Example: generate a 10-bit random string

[plain] view plain copy

# generate random strings using date

Date +% s% N | md5sum | head-c 10

# generate random strings using / dev/urandom

Cat / dev/urandom | head-n 10 | md5sum | head-c 10

After reading the above detailed introduction of the VIEW view of Mysql database, many readers must have some understanding. If you need to get more industry knowledge and information, you can continue to follow our industry information column.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.