30 minute Mysql QuickStart (illustrated) _mysql

One, MySQL installation

MySQL Downloads

http://dev.mysql.com/downloads/mysql/

MySQL version selection

MySQL feature custom selection installation

Function Customization Selection

Path Customization Selection

Set Root user password

Install complete, click MySQL Workbench 6.3 ce into MySQL client

Second, the SQL base

SQL statement Classification

1.DDL (data definition Languages) statement: A database definition language that defines different data segments, databases, tables, columns, indexes, and so on. Common statement keywords mainly include Create/drop/alter

2.DML (Manipulation Language) statement: Data manipulation statements for adding, deleting, updating, and querying database records, and examining data integrity. Commonly used sentence keywords mainly include insert/delete/update/select, etc.

3.DCL Language statement: A Data control statement that controls the direct permission and access level statements for different data segments. These statements define the database, tables, fields, user's access rights, and security levels. The main statement keywords include grant/revoke, etc.

DDL statements (involving the definition of a table, modification of the structure)

A, create statement

Query OK represents successful statement execution

1 row affected on behalf of the database row receive impact

0.01 sec represents time for operation execution

CREATE TABLE student (
SID int not null auto_increment,
sNo int,
sname varchar (m) not NULL,
primary KEY (S) ID)
);

1. See which databases are in the system (show databases;)

2. After viewing the databases already in the system, you can select the corresponding database (using dbname)

3. After selecting the corresponding database, query all the tables below the database (show tables)

Ii. deletion of the database

Syntax for deleting a database: Drop databse dbname;

Third, create a table

Syntax: CREATE TABLE tablename (column_name_1 column_type_1 constraints,column_name_2 column_type2 constrationts)

The MySQL table name is stored as a directory on disk, and the table name can be a character that is allowed for any directory name.

COLUMN_NAME is the name of the column

Column_type is the data type of the column

Constrationts is the constraint of a column

1. View table Definition: DESC tablename

2. View the SQL statement that created the table: Show CREATE TABLE TableName

Four, delete the table

Delete Table syntax: drop TABLE tablename;

V. Modification of the table

Aleter Grammar | Description

---|---
ALTER TABLE tablename Modify ColumnName Newcolumntype | Modify the type of a table field (==modify cannot change the name = =)
ALTER TABLE TableName Add Newcolumnname newcolumntype| Add table field
ALTER TABLE tablename drop oldcloumnname| Delete table field
ALTER TABLE tablename change Oldcolumname newcolumnname newcolumntype| Modify the name and type of the field
ALTER TABLE tablename rename (to) newtablename| Modify the name

Modify the order in which fields are sorted

After the syntax of ALTER, there are [First\after ColumnName] Options

ALTER TABLE user add address varchar (a);

DML (Operation of database table records, add (insert) Delete (delete) (update) check (SELECT)

1.insert statement

Grammar:

Insert a: INSERT INTO TableName (columnname1,columnname2 ...) VALUES (Val1,val2 ...);

Insert multiple: INSERT INTO TableName (columnname1,columnname2 ...) VALUES (Val1,val2 ...), (Val1,val2 ...);

2.update statement

Syntax: UPDATE tablename set columnname=value [Where condition]

If you use the MySQL workbench,update statement without a Where condition to perform an error, you need to set the cancellation settings as follows:

-3.delete statement

Syntax: Delete from tablename where condition

-4.select statement

Syntax: SELECT * FROM tablename [WHERE condition]

5. Table Connection

1. In-Connection (select only two tables to match each other data)

Select Cno,cname,sname from student inner join course on Cno=sno;
Select Cno,cname,sname from Student,course where Cno=sno;

2. Outer connection

The outer joins also differentiate:

1. Left join: All records containing the left table, NULL on the right

2. Right join: All records containing the table on the right, null on the left

6. Sub-query

-7. Record Union

Grammar:

SELECT * FROM t1 UNION ALL select * from T2;
SELECT * FROM t1 Union select * FROM T2;

The difference between Union all and Union:

Union All is to combine the result set directly, and union is to distinct the result of union all, and to remove the result of repetition.

DCL statements (DCL statements are primarily used by DBAs to manage object permissions in the system)

Grant and REVOKE

Third, MySQL supported data types

Numeric type

The parentheses following the MySQL support type specify the display width, for example: Int (5) indicates that the width is filled before the number when the value is less than 5, and the default is int (11) If the specified width is not displayed. If the inserted data is greater than the width of this value, the actual insertion value is not affected by the actual size of the int type.

Create TABLE valuetype (age
int,
age1 int
)
inserts into ValueType (Age,age1) values (1,2);/This time the database shows 1
ALTER TABLE valuetype modify age int zerofill;//This time the database shows ' 0000000001 '

When data is inserted into a bit type field, it is first converted to binary, and if the number of digits is allowed, the insert succeeds and the insertion fails if the number of digits is less than the actual position.

Date Time Type

MySQL inside gets the current time for now (). MSSQL Gets the current time for GETDATE ()

Timestamp, the scope of support is very small, from 1970-2038, timestamp affected by time zone

CREATE TABLE timestamptest (
tp timestamp)

The system automatically assigns the TP a default value of Current_timestamp (System date), but MySQL only sets the default value for the first timestamp, and if there is a second timestamp type, the default value is set to 0

String type

1.char differs from the varchar type:

The last space in the Char column has been deleted, while varchar preserves the space

Four, the MySQL operator

Arithmetic operators

Comparison operator, satisfies return 1, otherwise returns 0

Logical operators (Boolean operators)

Bitwise operators

Operator precedence, which is most often done using ()

V. Common functions

String functions

numeric function

Date and Time functions

Process functions

Other functions

Choose the right type of data

Char and varchar

In the InnoDB storage engine, it is recommended that you use the varchar type. For INNODB data tables, the internal row storage format does not distinguish between fixed-length and variable-length columns, so the performance of fixed-length columns is not necessarily better than immutable length.

Text and Blob

In general, when we save a few strings, we choose char or varchar, and when we save the larger text, we usually choose to use a text or BLOB. The difference between the two: text can only save character data, such as logs. A blob can hold binary data, such as a photo.

Floating-point numbers and fixed-point number

In MySQL, decimal or (numberic) is used to indicate the number of fixed-point

Selection of date types

Date/time/datetime/timestamp

Vii. design and use of indexes

Index overview

An index is the most common tool used in a database to lift performance. In MySQL, the MyISAM and InnoDB storage engine tables are created by default Btree indexes.

1. Creation of indexes

CREATE TABLE indextest (
ID int not NULL auto_increment,
MemberID int not null,
createtime datetime not NULL de Fault Current_timestamp,
primary key (ID)
ALTER TABLE indextest add orderserial varchar (m) not null;
Create unique index ix_orderserial on indextest (orderserial);

Insert into Indextest (memberid,createtime,orderserial) VALUES (112123, ' 2016-08-14 ', ' sz121213 ')
Description: Creates a table above that defines orderserial as a unique index.

Syntax: Create [unique\fulltext\spatial] index index_name on tablename (columname)

2. Principles for designing indexes

1. The most appropriate index column is the column that appears in the WHERE clause, or the column specified in the JOIN clause, rather than the column in the select list that appears after the SELECT keyword

2. Using a unique index, you need to consider a desirable distribution in the column, and the better the index if the cardinality of the indexed column is larger. For example: The order number can be set to a unique index because the order number is different. It is not necessary for rowstatus, because rowstatus is either valid or invalid. There's still a lot of scope for this sort of screening, it doesn't make sense.

3. Do not overdo indexing. Because all of this also takes up extra disk space, if one index is used infrequently, it will unnecessarily slow down the modification of the table.
Show MySQL execution plan: Explain followed by MySQL statement

Eight, view

Views (view)

Definition: A view is a virtual presence table and is essentially transparent to users who use the view, and the view does not actually exist in the database.

Advantage:

1. Simple, the user does not need to care about the following table structure/association conditions and filter criteria. It is already a filtered and qualified result set for the user

2. Security, users who use views can only access the result set that they are allowed to query

3. Data independence, once the structure of the view is determined, you can screen the changes in the table structure to the user's influence, the source table added column view has no effect.

Grammar:

Create or replace view Index_view as
select * from Indextest

1. Create [or replace] view ViewName as SELECT ...

2. Query select * FROM view name

3. Display view show tables;

4. Delete View Drop view ViewName

Ix. stored procedures and functions

First, stored procedures (store procedure) and functions

Stored procedures and functions are compiled in advance and there is a collection of SQL statements in the database, and invoking stored procedures and functions simplifies many of the work of application developers, reducing the transmission of data between databases and application servers, and is useful for improving the efficiency of processing.

Grammar:

Create database finance;//creating finance databases use
finance;
CREATE TABLE orders (
orderId bigint not null auto_increment,
MemberID int NOT null default 0,
serialnumber var char (NOT NULL default ",
amount Decimal (18,2) NOT null default 0,
createtime datetime NOT NULL default Curren T_timestamp,
primary KEY (OrderID)
//Create orders Order table
insert into orders (Memberid,serialnumber,amount) VALUES (6561121, ' sz12234222 ', 5), (233444, ' ys1652233 ', 10)//INSERT test Data
delimiter &
CREATE PROCEDURE Orders_ Serial (in serial varchar)
reads the SQL data
begin
SELECT * FROM Orders
where serialnumber=serial; End
&

Note: The delimiter $$ command is to change the terminator of the statement from a semicolon to a different symbol, which means the end of $$. This way, the semicolon after number will not be considered an end.

1. Call the stored procedure

Call Orders_serial (' sz12234222 ')

2. Benefits of Stored Procedures

Logic is encapsulated on the database side, the caller does not need to understand the middle processing logic, once the invocation logic changes, only need to modify the stored procedure, and the caller's program has no effect.

3. Delete stored Procedures

drop procedure if exists orders_serial
//if exists optional

4. View stored procedure Poor status

Show procedure status like ' Orders_serial '

5. Query the definition of stored procedure

Show CREATE PROCEDURE Orders_serial

Second, the use of stored procedure variables

Stored procedures can use variables and, after MySQL5.1 versions, are case-insensitive

1. Definition of variable

The scope of a variable can only be in a begin...end block and nested within a block

declare currenttime date;

2. Assignment of variables

Set Currenttime=now ()//Direct assignment
Select XX into CurrentTime from xx;//can also be assigned through an SQL statement

3. Defining conditions and handling

Declare handler_type handler for Contidtion_value;

Handler_type:

1.continue;
2.exit;
3.undo;

Condition_value:

1.sqlstate
2.sqlwarning
3.not found
4.sqlexception

Eg:declare continue handler for SQLState ' 2 ' Set @x=1;

Third, the use of the cursor

In stored procedures and functions, you can use the cursor to loop through the result set, using the cursor's declaration: Open, Fetch, close

Defined:

Declare cur_id cursor FOR SELECT * FROM orders;
Open cur_id;
Fetch cur_id;
Close cur_id;

Four, Event Scheduler

The event Scheduler is a new feature later in the MySQL5.1 that triggers an action on the database according to a custom time period. The database default action is off. Need to open

Create event x on
schedule
every 5 second
does
insert into orders (Memberid,serialnumber,amount) VALUES ( 6561121, ' 222 ', 5)

Set global Event_scheduler =1//Open Scheduler
alter event x disable;//Disable Event Scheduler Drop event
x;//Delete Events Scheduler

Ten, triggers

Trigger

Triggers are supported after version 5.02, triggers are database objects that are related to a table, are triggered when conditions are met, and the collection of statements defined in the trigger is executed. can assist application to ensure data integrity on the database side

Drop trigger Orderlog
delimiter $
CREATE trigger Orderlog after insert on orders for each 
row
begin
   
    insert into Orderslog (content) values (new.serialnumber);
End 
INSERT into orders (Memberid,serialnumber,amount) VALUES (6561121, ' sz12234222 ', 5)
   

Explanation: The above describes creating a trigger, inserting a record in the Order Log table after inserting data into the order table. Use old and new to refer to the record content of the trigger change, only the row-level trigger is currently being paid, and the statement-level trigger is not supported

The order in which triggers are executed

Before Insert\before update\after update

Xi. transaction control and locking statements

Transaction description for MySQL storage engine

1.Lock table and Unlock table

Grammar:

Use finance;
Lock table orders read;
Unlock table;

If a process (session1) locks a table, other processes (Session2) can query, but cannot update until the first process releases the lock

2. Transaction control

12, summary

I'm glad you can read here, it may be difficult to absorb so much knowledge in 30 minutes, this article is also my previous study of MySQL notes integration. This article is also more theoretical, for which more difficult to understand the knowledge point to write some demo, the right when the individual understanding, if there is insufficient place, please point out. If it is helpful to you, please order a compliment!