2018-07-29 Learning MySQL Summary

Before studying in school, just know the database is very important, but did not take the initiative to learn to understand. Now learn the process of learning MySQL once again, simply do a summary of it.

First of all, remember the three concepts:

1. A database is a repository of data that is organized, stored, and managed according to the information structure on a computer storage device.

2.SQL: Structured querying language (structured query Language)

3.MySQL: relational database management system

Database stores a variety of data, SQL statements to find the data we need from the database, MySQL is an application software, through the statement to the database operation.

MySQL I am using version 5.6, after opening cmd as administrator, enable MySQL service for: net start mysql56, shutdown Service for: net stop mysql56. Login: mysql-h localhost-u root-p Enter password: 123456 (user name and password are set during installation)

The following is a summary of four areas:

1. Data definition language (DDL)

2. Data manipulation Language (DML)

3. Data Query Language (DQL)

4. Functions

This summary is mostly statement format, if you need code implementation, as well as some additional knowledge point labeling, you can download and install the Xmind software, download the mind map in the cloud disk to view.

Link: https://pan.baidu.com/s/18a3gY9Rzu7TOox-Tkgq-_w Password: Qrie

XIMD content is as follows:

Data definition Language (DDL): 1. Build a table:

CREATE TABLE table name (CREATE TABLE test (

Column Name 1 data type, ID int,

Column Name 2 data type, name char (10),

.........                                                                                    ......

Column name n data type); birthday date);

Common data types: integers (int,tinyint,smallint), decimals (float,decimal), Strings (Char,varchar), Time (Date,time)

2. Constraints:

Primary key (PRIMARY key): Constraint uniquely identifies each record in a database table

Foreign key (foreign key): foreign key is the relationship between a table and a table, pointing from one table to another

Unique: Constraints are used to restrict the type of data joined to the table

When you build a table, you can add it after the data type:

CREATE TABLE test (ID int primary key);

CREATE TABLE Test (ID int,primary key (ID);

Or when you create a table, add it later if you want to:

ALTER TABLE test add primary key (ID);

Delete PRIMARY key: ALTER TABLE test drop PRIMARY key;

Unique usage is the same as primary key.

Use of foreign keys:

CREATE TABLE t1 (ID int primary Key,name char (10));

CREATE TABLE t2 (ID int primary key,pri_id int,name char (10),

Constraint I foreign key (pri_id) references T1 (id));

pri_id in T2 as a foreign key, pointing to the primary key ID in T1

Delete foreign key: constraint I, name the foreign key to I, convenient for us to delete the foreign key

ALTER TABLE T2 drop FOREIGN key i;

3. Field Properties

1.unsigned (unsigned), can only be used in numeric fields

2.zerofill (Auto-fill 0), can only be used in numeric fields, leading zeros, and the field is automatically unsigned

3.auto_increment (auto-grow), parasitic on primary key

4.NOT null: Force constraint column to not keep NULL value, i.e. cannot insert data without adding numeric value

5. Default: Give Data a default value (system default is NULL) and cannot be used on one field with Auto_increment

The notation is: CREATE TABLE test (ID int field property);

DML (Data Manipulation language): 1. Index:

Created: Create index index name on table name (field); Create INDEX i on test (ID);

Delete: Drop index name; Drop INDEX i;

2. Operation on data: Insert (INSERT):

Insert separate data: INSERT into Table Name field 1, Fields 2 ... values (value 1, value 2 ...) ）;

Insert default data: Insert Inro Table name values (value 1, value 2 ...);

INSERT into Test (Id,name) VALUES (008, ' Week Stars ');

Update (Modify, UPDATE): Modify (UPDATE) Data: Update table name SET field = new value where column name = value

Update test set name= ' James ' where id=008;

To modify more than one piece of data:

Update test set Name=case ID

When 001 and then ' Qwe '

When 002 Then ' ASD '

End

where ID in (001,002)

Replace (bulk update data) (also available for data addition): Replace into table name (Field 1, field 2 ...) VALUES (value 1, value 2 ...)

Replace into Test (Id,name) VALUES (002, ' 777 ');

Data that is not added will default to NULL

Batch update (via Update):

INSERT into Test (Id,name) VALUES (001, ' I am 001 '), (002, ' I am 002 ')

On duplicate key update

Id=values (ID), name=values (name);

Delete Deletes data (one): Delete from table name where condition

Delete from test where id=001;

3. Modify table name for table operation: ALTER TABLE name rename as new table name;

ALTER TABLE test rename as Father;

Modify the data type of a field: ALTER TABLE table name modify field name data type

ALTER TABLE test modify ID char (10);

Add field: ALTER TABLE name add field name 1 data type

ALTER TABLE test add address char (30);

Delete field: ALTER TABLE name drop field name;

ALTER TABLE test drop address;

DQL (data query statement insert): 1. Cross-query: Select Table 1. fields, table 2. Fields from table 1, table 2, multi-table union query: Select Table 1. field, table 2. field from table 1, table 2 where table 1.id= table 2.id;

SELECT * from T1,t2 where t1.id=t2.id;

2. Querying for data that is not duplicated: SELECT distinct field from table name;

Select distinct ID from test;

3. Take alias Alias:select * from table name as alias;

Self-connect: Select b.* from shop as a,shop as B where a.name= ' bun ' and A.price

Alias the shop table to a A, a, and B to compare your own data

4.limit (offset):

Query Top N data: Select *from table name limit n;

I data after querying the first n data: Select *from table name limit n,i;

5.in (Specify multiple values after where): Select field from table name where field in (value 1, value 2);

SELECT * from test where ID in (A/n);

6.like (for the specified pattern in the search column in the WHERE clause): Select field from table name where field like expression;

For example, search for the surname youth:

Select *from test where name like ' King% ';

%: Indicates 0~ multiple characters

_: Represents a character

7.order by (Sort): Select field from table name order By field (sort by) [desc] (in reverse order)

SELECT * FROM test order by id DESC;

Indicates that the display is sorted by ID from large to small

8.join Connection:

Inner Jor (inner connection), left join (connected to right)

Full join (fully connected) (MySQL does not support full connection)

The format is the same: Select field from table 1 inner/left/right join table 2 on table 1. field = table 2. field;

9.union (joint query): Select *from Table 1 union select *from Table 2;

Functions: 1. Collection function: The operation targets a series of values and returns a single value

All the same:

Query the average of a column: Select AVG (field) from table name;

Returns the number of rows in a column: Select count (field) from table name;

Query the maximum value of a column: select Max (field) from table name;

Query the minimum value of a column: select min (field) from table name;

Returns the sum of a column: select SUM (field) from table name;

Grouped display totals: select SUM (field) from Table name Group By field;

2. Scalar function: The operation targets a single value and returns a single value based on the input value

The same wording:

UCase (Converts the value of the field to uppercase): Select UCase (field) from table name;

LCase (Converts the value of the field to lowercase): Select LCase (field) from table name;

Mid (extract character): Mid (field, start, end):

Select Mid (name,2,3) from test;

Represents the start of the second data in the Name column, with each data showing only 3 bits

Len (return text length): Select Length (field) from table name;

Round (rounding values and preserving corresponding decimal digits): Select round (field, number) from table name;

Now () (query current time): The name of the from table (with several data appearing)

Although this article is my own learning summary, but also as a beginner I wrote, but also hope that more friends can see my article, if there are shortcomings or questions, welcome to the message area message.

2018-07-29 Learning MySQL Summary