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!