30-minute MySQL Quick Start (illustration) and mysql Quick Start

30-minute MySQL Quick Start (illustration) and mysql Quick Start

1. Install MySQL

MySQL download

Http://dev.mysql.com/downloads/mysql/

MySQL version Selection

Custom MySQL feature Installation

Custom Feature Selection

Custom Path Selection

Set the root user password

After the installation is complete, click MySQL Workbench 6.3 CE to enter the MySQL client.

Ii. SQL Basics

SQL statement category

1. DDL statements: Data Definition Language. These statements define different Data segments, databases, tables, columns, indexes, and other database objects. Common statement keywords include create/drop/alter

2. DML (Data Manipulation Language) Statement: a Data Manipulation statement used to add, delete, update, and query database records, and check Data integrity. Common statement keywords include insert/delete/update/select.

3. DCL (Data Control Language) Statement: a Data Control statement used to Control the direct permission and access level of different Data segments. These statements define the database, table, field, user access permission, and security level. Key statement keywords include grant/revoke.

DDL statements (involving the definition and Structure Modification of tables)

I. create statement

Query OK indicates that the statement is successfully executed.

1 row affected indicates that one row of the database is affected.

0.01 sec indicates the time when the operation was executed

create table student(SID int not null auto_increment,sNo int ,sName varchar(50) not null,primary key(SID));

1. view the databases in the system (show databases ;)

2. After viewing the existing database in the system, you can use (use dbname) to select the corresponding database

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

Ii. delete a database

Delete database Syntax: drop databse dbname;

3. 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 in the disk as a directory. The table name can contain characters allowed by any directory name.

Column_name is the column name.

Column_type is the column data type.

Constrationts is a column constraint.

1. view the table definition: desc tablename

2. view the SQL statement for creating a table: show create table tablename

4. delete a table

Drop table tablename;

5. modify a table

Aleter syntax | description

--- | --- Alter table tablename modify columnname newColumnType | modify the table field type (= modify cannot change field name =) alter table tablename add newColumnname newColumnType | adds the table field alter table tablename drop oldCloumnname | deletes the table field alter table tablename change oldColumname newColumnname newColumntype | modifies the field name and type alter table tablename rename) newtablename | modify the table name

Modify the order of fields

The alter syntax is followed by the [first \ after columnname] Option.

alter table user add address varchar(20) first ;alter table user add age int after name ; 

DML (operate on database table records, add (insert) delete (delete) Change (update) query (select ))

1. insert statement

Syntax:

Insert one: insert into tablename (columnname1, columnname2. ..) values (val1, val2 ...);

Insert multiple entries: insert into tablename (columnname1, columnname2. ..) values (val1, val2....), (val1, val2 ...);

2. update statement

Syntax: update tablename set columnname = value [where condition]

If MySQL Workbench is used, an error occurs when the where condition is not added to the update statement. You need to cancel the settings as follows:

-3. delete statement

Syntax: delete from tablename where condition

-4. select statement

Syntax: select * from tablename [where condition]

5. Table join

1. Inner join (only matching data in two tables is selected)

select cno,cname,sname from student inner join course on cno=sno;select cno,cname,sname from student,course where cno=sno;

2. External Connection

External connections are differentiated:

1. left join: contains all records in the left table. Null is not found on the right.

2. right join: contains all records in the right table. null is not found on the left.

6. subquery

-7. Record Union

Syntax:

select * from t1 union all select * from t2;select * from t1 union select * from t2;

Difference between union all and union:

Union all directly merges the result sets, while union performs a distinct operation on the results after union all to remove the repeated results.

DCL statements (DCL statements are mainly used by dba to manage object permissions in the system)

Grant and revoke

Iii. Data Types supported by MySQL

Value Type

MySQL supports specifying the display width in parentheses after the type. For example, int (5) indicates that the display width is filled before the number when the value width is less than 5, if the specified width is not displayed, the default value is int (11 ). If the inserted data is greater than the width of the value, the actual inserted value is not affected, and it is based on the actual size of the int type.

Create table valuetype (age int, age1 int) insert into valuetype (age, age1) values (1, 2); // at this time, the database displays 1 alter table valuetype modify age int zerofill; // at this time, the database will display '123'

When a bit type field is inserted, the data is first converted to binary. If the number of digits is allowed, the data is successfully inserted. If the number of digits is smaller than the actual number, the Data fails to be inserted.

Date and Time Type

Mysql obtains the current time as now (). mssql obtains the current time as getdate ()

Timestamp. The supported range is very small. From 1970-2038, timestamp is affected by the time zone.

create table timestamptest(tp timestamp)

The system automatically assigns the default value current_timestamp (system date) to tp, but mysql only sets the default value for the first timestamp. If the second timestamp type exists, the default value is 0.

String type

1. Differences between char and varchar:

The space at the end of the char column has been deleted, while the varchar space is reserved.

Iv. MySQL Operators

Arithmetic Operators

If the comparison operator is true, 1 is returned. Otherwise, 0 is returned.

Logical operators (boolean operators)

Bitwise operators

Operator priority. In most cases, () is used for operations.

5. Common functions

String Functions

Numeric Functions

Date and Time Functions

Process Functions

Other functions

6. select an appropriate data type

Char and varchar

We recommend that you use the varchar type in the Innodb Storage engine. For Innodb data tables, the internal row storage format does not distinguish between fixed-length and variable-length columns. Therefore, the performance of fixed-length columns is not necessarily better than that of non-variable-length columns.

Text and blob

Generally, when saving a small number of strings, we select char or varchar. When saving large texts, we usually choose text or blob. The difference between the two: text can only store character data, such as logs. Blob can save binary data, such as photos.

Floating Point Number and fixed point number

In MySQL, decimal or (numberic) is used to indicate the number of points.

Date Type Selection

Date/time/datetime/timestamp

VII. design and use of Indexes

Index Overview

Indexes are the most common tools used in databases to improve performance. In MySQL, MyISAM and Innodb Storage engine tables are created by default with Btree indexes.

1. Create an index

create table indexTest(id int not null auto_increment,memberid int not null,createtime datetime not null default current_timestamp,primary key (id))alter table indextest add orderserial varchar(50) not null;create unique index IX_orderserial on indexTest(orderserial);

Insert into indextest (memberid, createtime, orderserial) values (112123, '2017-08-14 ', 'sz121213 ')
Note: The preceding table is created, and orderserial is defined as a unique index.

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

2. index design principles

1. The most suitable index column is the column that appears in the where clause or the column specified in the join clause, rather than the column that appears in the selection list after the select keyword.

2. When using a unique index, consider a value distribution in the column. If the base of the index column type is larger, the index effect is better. For example, you can set a unique index for the order number, because the order number is different. You do not need to use rowstatus because rowstatus is either valid or invalid. There are still a lot of filtering scope, which is meaningless.

3. Do not over-indexing. Because all of them also occupy extra disk space, if an index is rarely used, it is unnecessary to slow down the modification speed of the table.
Display the MySQL execution plan: add the mysql statement after the explain command.

8. View

View)

Definition: A view is a virtual table. It is transparent to users who use the view. The view does not actually exist in the database.

Advantages:

1. Simple. You do not need to worry about the structure/association conditions and filtering conditions of the corresponding table. It is already a filtering result set for users.

2. Security: View users can only access the result set they are allowed to query

3. Data is independent. Once the view structure is determined, the impact of table structure changes on users can be shielded. Adding columns to the source table does not affect the view.

Syntax:

create or replace view index_view asselect * from indextest

1. create [or replace] view viewName as select...

2. query the select * from view name

3. show tables;

4. Delete the view drop view viewname

9. stored procedures and functions

I. store procedure and functions

Stored Procedures and functions are a collection of SQL statements compiled and stored in a database. Calling stored procedures and functions can simplify the work of application developers, reducing data transmission between databases and application servers is advantageous for improving data processing efficiency.

Syntax:

Create database finance; // create a finance database use finance; create table orders (orderId bigint not null auto_increment, memberId int not null default 0, serialNumber varchar (50) not null default '', amount decimal () not null default 0, createTime datetime not null default current_timestamp, primary key (orderid) // create orders order table insert into orders (memberId, serialNumber, amount) values (6561121, 'sz12234222 ', 5), (233444, 's1652233', 10) // insert test data delimiter & create procedure orders_serial (in serial varchar (50 )) reads SQL databeginselect * from orderswhere serialNumber = serial; end &

Note: The delimiter $ command is to change the statement Terminator from a semicolon to another symbol. Here, $ is the end. In this way, the semicolon after the number will not be considered as the end.

1. Call the Stored Procedure

call orders_serial('sz12234222')

2. Benefits of Stored Procedures

The logic is encapsulated in the database. The caller does not need to understand the intermediate processing logic. Once the calling logic changes, the caller only needs to modify the stored procedure, which has no impact on the caller's program.

3. delete a stored procedure

Drop procedure if exists orders_serial // optional if exists

4. view the poor status of the stored procedure

show procedure status like 'orders_serial'

5. query the definition of a stored procedure

show create procedure orders_serial

Ii. Use of Stored Procedure Variables

Variables can be used in stored procedures, and are case-insensitive after MySQL

1. Definition of Variables

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

Declare currentTime date;

2. Variable assignment

Set currentTime = now (); // directly assign a value to select XX into currentTime from XX; // You can also assign a value through an SQL statement.

3. define conditions and Processing

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;

Iii. Use of the cursor

In stored procedures and functions, you can use the cursor to process the result set cyclically. The cursor uses the declaration containing the cursor: open, fetch, close

Definition:

Declare cur_id cursor for select * from orders;
Open cur_id;
Fetch cur_id;
Close cur_id;

Iv. Event Scheduler

The event scheduler is a new feature after MySQL5.1. It can trigger certain operations on the database according to the custom time period. Database Operations are disabled by default. Need to open

create event xon scheduleevery 5 seconddoinsert into orders (memberId,serialNumber,amount) values(6561121,'222',5)

Set global event_scheduler = 1 // enable the scheduler alter event x disable; // disable the event scheduler drop event x; // Delete the event Scheduler

10. triggers

Trigger

The trigger is supported after version 5.02. The trigger is a database object related to the table. It is triggered when the condition is met and runs the set of statements defined in the trigger. Helps applications ensure data integrity at the database end.

drop trigger orderlogdelimiter $create trigger orderlog after insert on orders for each rowbegininsert into orderslog (content) values(new.serialNumber);end insert into orders (memberId,serialNumber,amount) values(6561121,'sz12234222',5)

Description: The above describes how to create a trigger. After inserting data into the Order table, insert a record into the order log table. Old and new are used to reference the Record Content of trigger changes. Currently, only row-level triggering is supported, and statement-level triggering is not supported.

Trigger execution sequence

before insert\before update\after update

11. Transaction Control and lock statements

MySQL storage engine transactions

1. Lock Table and Unlock Table

Syntax:

Use finance;
Lock table orders read;
Unlock table;

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

2. Transaction Control

12. Summary

I'm glad that you can read this article, which may be difficult to absorb so much knowledge in thirty minutes. This article is also integrated with my previous MySQL notes. This article also involves a lot of theories. It is difficult to understand the knowledge points and write some demos. You can understand them as an individual. If there are any deficiencies, please point out. If it is helpful to you, please give me a thumbs up!