mSQL Analysis-Basic Command (II)

Brief introduction of MSQL data type 1, integer type

tinyint, accounted for 1 bytes, signed: -128~127, unsigned bit: 0~255

smallint, accounted for 2 bytes, signed: -32768~32767 unsigned bit: 0~65535

Mediumint is 3 bytes, signed: -8388608~8388607, unsigned bit: 0~16777215:

int, accounting for 4 bytes, signed: -2147483648~2147483647,, unsigned bit unsigned bit: 0~4 284967295

BigInt, Bigint,bigint, accounted for 8 bytes

BOOL equivalent to tinyint

2, floating-point type

float ([m[,d]]) was 4 bytes, 1.17e-38~3.4e+3838~3.4e

Double ([m[,d]]) is 8 bytes

Decimal ([m[,d]]) A floating-point number represented as a string

3, Character type

char ([M]):: fixed-length character, occupies M-byte

Varchar[(M)]:: variable-length character, occupies m+1m+1 bytes, greater than 255 characters: occupies a m+2m+2

tinytext,255 characters (2 of 8 square)

text,65535 characters (2 of 16 square)

mediumtext,16777215 characters (2 of 24 square)

Longtext (2 of 32 Parties)

Enum (Value,value,...) Up to 1/2 bytes can have a maximum of 65,535 member members

Set (Value,value,...) takes up to 1/2/3/4/8 bytes and can have a maximum of 64 members

Ii. MySQL Data operation 1, logical operation and OR not

For example:

Select a record for the book Price (30,60,40,50)

Sql> Select Bname,publishing,price from books where price=30 or price=40 or price=50 or price=60;

12, in operator

The in operator is used in the WHERE expression to support multiple selections in the form of a list, with the following syntax

where colunmm in (value1,value2, ....) ）

where colunmm not in (Value1,value2,..........)

When in is preceded by not, the expression is the opposite of in, neither in the result

Sql> Select Bname,publishing,price from books where Price in (30,40,50,60) Order by price ASC;

23. Arithmetic Operator >= | <=| <> |=

For example

Find records with prices less than 70

Mysql> Select Bname,price from books where Price <= 70;

34, fuzzy query like '%...% '

field name [not] like '%......% ' wildcard character any number of characters

A record containing the words of a program in a query book

Mysql> Select Bname,price from books where bname like '% Program% '

45. Range operation [NOT] between .... and

Find the title and price of the price not between 30 and 60

Mysql> Select Bname,price from books where price is not between and the order by price Desc;

56. Mysql Sub-query

Select is also present in the select where condition

Query type is a book of network technology

Mysql> Select Bname,btypeid from Books where btypeid= (select Btypeid from category where btypename= ' network technology ');

67. Limit limit Display entries

The limit clause can be used to force the SELECT statement to return the specified number of records. LIMIT accepts one or two numeric arguments. Must be an integer constant. If a two number is given, the first specifies the offset of the record row, and the second parameter returns the maximum number of record rows. The initial offset is 0 (not 1).

Syntax: SELECT * FROM Limit m,n

where m refers to the beginning of the record index Indexindex, starting from 0, representing the first record, n refers to starting from the m+1, taking N.

Querying records from 2nd to six rows in the books table

Mysql>select * FROM books limit 1, 6;

78. Connection Query

In a common field, two tables are eligible and set. The two tables are joined together by common fields in the common fields.

Common connections:

Inner joins: Matching according to common fields in the table

Outer joins: The entire record of a given data table and a record in another data table that meet the connection criteria.

Outer connection: Left JOIN, right connection

Internal connection: for Exmaple

CREATE table student (sit int (4) primary key auto_increment,name varchar (40));

INSERT into student values (1, ' Zhang San '), (2, ' John Doe '), (3, ' Harry '), (4, ' Mikel ');

CREATE TABLE teachers (sit int (4), id int (4) primary key Auto_increment,score varchar (40));

INSERT into teachers values (1, 1, ' 1234 '), (1, 2, ' 2345 '), (3,3, ' 2467 '), (bis, ' 2134 ');

Select S.*, t.* from student as s,teachers as T where S.sid=t.sid;

8

Left JOIN: SELECT statement a table Left[outer] join B table on join conditions, a table is the main, are displayed.

b table is from, the main table content has all, the main table more out of the field, from the table does not show null, from the table of the main table more than the field is not displayed.

SELECT * FROM student as S left join teachers as T on S.sit=t.sit;

9

Right connection: SELECT statement a table Right[outer] join B table on join condition, b table is main, all display.

A table is from, the main table content has all, the main table more out of the field, from the table does not show null, from the table of the main table more than the field is not displayed.

SELECT * FROM student as Sright joins teachers as T on S.sit=t.sit;

103, aggregate function 1, Sam () sum

Select SUM (id+score) as g from teachers;

2, AVG () averaging

Select AVG (Id+score) as g from teachers;

3, max () maximum value

Select Max (ID) as g from teachers;

4, min () minimum value

Select min (id) as g from teachers;

5, substr (String,start,len) interception

Select substr (soucr,1,2) as g from teachers;

Start from start, intercept len length, start starting from 1

Concat (Str1,str2,str3 .............. string concatenation, stitching together multiple strings

Select Concat (id,score,sit) as g from teachers;

6. Count () Statistics count record field data bar number

Select COUNT (ID) as g from teachers;

7, Upper () uppercase

Select upper (name) as G from student; #将字段name中英文全部变为大写, but does not change the original value

8, lower () lowercase

Select lower (name) as G from student; #将字段name中英文全部变为小写, but does not change the original value

Iv. Index

MySQL in the index is stored as a file, the table is added and deleted, will be synchronized to the index, the index and the table is consistent, commonly used in the Where field query index.

Advantages: Speed up queries and reduce query time

Disadvantage: Indexes occupy a certain amount of disk space and can affect insert,delete,update execution time

1. Index type

Normal index: Most basic index, not unique

Unique index: The value of the indexed column must be unique, but a null value is allowed. If it is a composite index, the combination of column values must be unique

Primary KEY index: Record value unique, primary key field is rarely changed, cannot be empty, cannot be modified, can be used for one field or multiple fields

Full-Text indexing: Retrieving textual information for larger data, generating full-text index queries faster, but also a waste of time and space

Composite Index: One index contains multiple columns

2. Create an index

Normal index:

# Create a normal index

CREATE TABLE demo (ID int (4), UName varchar (), upwd varchar (), index (UPWD));

# View the process of building a table

Show create table demo;

Demo | CREATE TABLE ' demo ' (

' ID ' int (4) DEFAULT NULL,

' uName ' varchar DEFAULT NULL,

' upwd ' varchar DEFAULT NULL,

KEY ' upwd ' (' upwd ')

) Engine=innodb DEFAULT Charset=utf8 |

Unique index: Field values are allowed only once and can have null values

# Create a unique index

CREATE table demo1 (ID int (4), uName varchar, upwd varchar, unique index (uName));

# View the process of building a table

Show CREATE table demo1;

Demo1 | CREATE TABLE ' Demo1 ' (

' ID ' int (4) DEFAULT NULL,

' uName ' varchar DEFAULT NULL,

' upwd ' varchar DEFAULT NULL,

UNIQUE KEY ' uName ' (' UName ')

) Engine=innodb DEFAULT Charset=utf8 |

Primary KEY index: The field record value is unique, the field is rarely modified, the general PRIMARY KEY constraint is auto_increment or not null unique, cannot be empty, cannot be duplicated.

# Create PRIMARY key index

CREATE TABLE Demo2 (ID int (4) auto_increment primary key,uname varchar (), upwd varchar (20));

# View the Build Table statement

Demo2 | CREATE TABLE ' Demo2 ' (

' ID ' int (4) not NULL auto_increment,

' uName ' varchar DEFAULT NULL,

' upwd ' varchar DEFAULT NULL,

PRIMARY KEY (' id ')

) Engine=innodb DEFAULT Charset=utf8 |

Full-text index: Improve the efficiency of full-text search and solve fuzzy query

# Create a full-text index

CREATE TABLE Demo3 (ID int (4), uName varchar, upwd varchar), fulltext (UNAME,UPWD));

# View the Build Table statement

| Demo3 | CREATE TABLE ' Demo3 ' (

' ID ' int (4) DEFAULT NULL,

' uName ' varchar DEFAULT NULL,

' upwd ' varchar DEFAULT NULL,

Fulltext KEY ' uName ' (' uName ', ' upwd ')

) Engine=innodb DEFAULT Charset=utf8 |

Five, FOREIGN KEY constraints

FOREIGN KEY constraint: foreign a contract relationship between a key table and a table, because of the existence of this relationship, the data between the table and the table is more integrity and more relevant.

Create a FOREIGN KEY constraint

Create a User Master table

CREATE TABLE user1 (id int (one) auto_increment primary key,name varchar (), sex int (1));

Inserting data

Insert into User1 (name,sex) VALUES ("Mikel", 4), ("Plyx", 6);

Create Order foreign key table

CREATE TABLE ' order ' (order_id Int (one) auto_increment primary key,u_id Int (one), username varchar (+), Monery Int (11), Foreign KEY (u_id) references User1 (ID) on the DELETE cascade on UPDATE cascade) ENGINE=INNODB);

Inserting data

INSERT into ' order ' values (Order_id,u_id,username,monery, ' Mikel ', 2345), (2,2, ' Plyx ', 3456)

Test Cascade Delete

Delete from User1 where id=1

View Order table Records

12

Test cascading updates

Update user1 set id=5 where id=2

13

Test data integrity

Insert a record with a u_id of 6 in the order table

Insert INTO ' Orser ' (u_id) values (6);

Cannot add or update a child row:a FOREIGN KEY constraint fails (' School '. ' Order ', constraint ' order_ibfk_1 ' foreign key (' u_id ') REFERENCES ' user1 ' (' id ') on the DELETE CASCADE on UPDATE CASCADE)

A record with ID 6 does not exist in User1 and now adds a record with ID 6

INSERT into User1 (ID) values (6);

14

You can see that the data is plugged in.

View

is a virtual table, the data structure and data specified by the Select Select SELECT statement, and does not generate a real file

Create VIEW Mikel as select * from School.books;

SELECT * from Mikel;

156. Stored Procedures

Stored procedures are used to encapsulate MySQL code, equivalent to functions, once compiled, to generate binaries, to be permanently valid, and to improve efficiency.

1. Define Stored procedures

CREATE PROCEDURE procedure name (parameter 1, parameter 2, ...). ）

Begin

SQL statements

End

2. Call the stored procedure

Call Procedure Name (parameter 1, Parameter 2, ........). ）

Example: Defining a stored procedure to view all data in a books table

1. Modify SQL Default execution symbols

Delimiter//

CREATE procedure seebooks ();

Begin

SELECT * from Sctudent.books;

End//

Call Seebooks ()//

163. Transfer of stored procedure parameters

In passed in parameter int assigned value

In input parameter: Indicates that the caller passed in a value to the procedure (the incoming value can be literal or variable)

Out output parameter: Indicates that the procedure has outgoing values to the caller (multiple values can be returned) (Outgoing values can only be variables)

InOut input/Output parameter: Indicates whether the caller passed in a value to the procedure, or that the procedure sent a value to the caller (the value can only be a variable)

CREATE PROCEDURE SeeBook (in B int)

Begin

SELECT * from School.books where bid=b;

End//

Call SeeBook (4)

16

Out--------------Outgoing parameters

Select INTO assigns a value to the variable during the procedure and looks at the value of the variable

CREATE PROCEDURE Seebook2 (out B varchar (100))

Begin

Select Bname to B from School.books where bid=4;

End//

17

How to use variables within a procedure

Declare variable name, type, variable within declare procedure not @

Assignment Set Variable name = (SELECT statement)

CREATE PROCEDURE Seebook3 ()

Begin

DECLARE str varchar (100);

Set str= (select Bname from School.books where bid=20);

Select Str;

end//

Call SEEBOOK3 ()//

181. Trigger

When a table appears (add, delete, change, check), it automatically performs its specific operation.

Syntax: CREATE TRIGGER Trigger name trigger timing trigger action on table name for each row

Trigger Name: Custom

Trigger timing: After/before after/before

Trigger action: Insert Update Delete

To create a trigger:

Create trigger delstudent after delete on grade

Delete from student where sid= ' 4 ';

Delete from grade where sid=4;

Mysql> select SID from student where sid=4;

Empty Set

To see if there is a value for sid=4, you can see that it has been deleted

2. Business

A sequence of operations performed by a single logical unit that, by composing a set of operations, either succeeds or fails all, makes the program more reliable and simplifies error recovery.

MySQL transaction is mainly used to deal with large-scale and high-complexity data. For example, in the Personnel Management system, you delete a person, you need to delete the basic information of the person, but also to delete the information related to the person, such as mailbox, articles and so on, so that these database operation statements constitute a transaction!

In MySQL, transactions are supported only by databases or tables that use the INNODB database engine.

Transactions can be used to maintain the integrity of the database, to ensure that batches of SQL statements are either fully executed or not executed at all. Transactions are used to manage insert,update,delete statements.

There are two main methods of MYSQL transaction processing:

1, with BEGIN, ROLLBACK, commit to achieve

Begin a transaction

ROLLBACK transaction Rollback

Commit TRANSACTION Acknowledgement

2, directly with SET to change the MySQL automatic submission mode:

SET autocommit=0 prohibit auto-commit

SET autocommit=1 turn on auto-commit

Create transaction

Begin

Update books set bname= "Plyx" where bid=1;

Update books set bname= "Plyx" where bid=2;

commit//

View the record, has been modified

select * from books;

197. mysql Data structure

Master configuration file My.cnf

Data Catalog:/var/lib/mysql

Process Communication sock file:/var/lib/mysql/mysql.sock

Error log file

[Mysqld_safe]

Log-error=/var/log/mysqld.log

Process PID File: pid-file=/var/run/mysqld/mysqld.pid

Binary files: Log-bin=mysql-bin.log

Eight. Introduction to common storage engines

MyISAM:

Features: 1, do not support transactions, foreign keys are not supported, downtime will destroy the table

2. Use small memory and disk space, fast access speed

3. Table-based lock, table-level lock

4. mysql caches index index only, data is cached by OS

Application Scenario: Log system, portal, low concurrency.

Innodb:

Features: 1, transactional secure storage engine with commit, rollback, crash resiliency

2. Support auto-Grow column, support FOREIGN KEY constraint

3. Use more disk space to preserve data and indexes

4. Full-text indexing is not supported

Scenario: Transaction application required, high concurrency, automatic recovery, brisk based on primary key operation

MEMORY:

Features: 1. The memory storage engine creates tables using content that is present in the contents.

2, each memory table only actually corresponds to a disk file, the format is. frm. The memory type of table access is very fast because its data is in memory and the hash index is used by default, but once the service is closed, the data in the table is lost.

3, Memory storage engine table can choose to use Btree Index or hash index.

Nine, thinking and summary

To this main introduction, MySQL some tips for using, including data types, query methods, stored procedures, foreign key constraints, indexes. Triggers, transactions, also contains some storage engine introduction, to this basic part of the end, as well as the subsequent sharing will be launched, please look forward to!

Summarize

I am Mikel pan, cloud-computing enthusiasts, regularly updated life sentiment, spiritual evolution in Mikel Pan, like I come to find me!

Blog Park Address: http://www.cnblogs.com/plyx/

Jane Book Address: Https://www.jianshu.com/u/5986765934f4

mSQL Analysis-Basic Command (II)