MySQL basic syntax

1, Mysql Foundation

Classification of SQL:

1,DDL—Data definition language( Data Define Language): create(Create), alter(Modification), drop(Delete), TRUNCATE(Truncation), RENAME(Rename);
2,DML—Data manipulation language( Data Manipulation Language): select(Query), delete(Delete), update(Update), insert(New);
3,DCL—Data control language( Data Control Language): grant(Add permissions), revoke(Recycling authority);
123

DDL operation:
Note: when using DDL language, the verb must be followed by the database object noun (e.g. TABLE, VIEW, INDEX, SCHEMA, TRIGGER, etc.).
Links to databases

# mysql -h 127.0.0.1 -u root -p
1

Common parameter explanations:

-A Do not read the database information in advance to improve the speed of connecting and switching databases,use--disable-auto-rehash replace
--default-character-set  Default character set used
-e Execute the command and exit
-h Host address
-p Password to use when connecting to the server
-P Connected port number
123456

Create database:

create database test1 ;
1

View database:

show databases;
1

Select database:

use mysql;
1

Delete database:

drop database test1;
1

Create table:

CREATE  [TEMPORARY]  TABLE  [IF NOT EXISTS] [database_name.] <table_name>
(
  <column_name>  <data_type>  [[not] null],...
)
1234

Note: TEMPORARY: indicates to create a TEMPORARY table
IF NOT EXISTS: if the table to be created already exists, force no error message to be displayed
　　database_name: database name
　　table_name: table name
　　column_name: column name
　　data_type: data type
　　
View definition:

desc emp;
1

To view the created table:

show create table emp ;
1

Update table name:

 alter table emp rename users;
1

Delete table:

drop table emp;
1

Modify table fields:

alter table emp modify ename varchar(30);
1

Add table fields:

alter table emp add column age int(3);
1

Modify table fields:

alter table emp change age age int(4);
1

To delete a table field:

alter table emp drop column age;
1

change and modify:

The former can modify the column name,The latter cannot. 
change Column names are required twice.
12

add/change/modify / add order:

1 add Add at end of table.
2 change/modify The table field location is not supported.
3 The modified field can be adjusted with the following parameters(frist/after column_name);

alter table emp change age age int(2) after ename;
alter table emp change age age int(3) first;
123456

DML statement:
Insert record:

//Specify fields,
//Fields such as auto increment and default value need not be listed. If there is no default value, it will be automatically set to NULL
insert into emp (ename,hiredate,sal,deptno) values ('jack','2000-01-01','2000',1);

//You can not specify fields, but they should correspond to each other one by one
insert into emp values ('lisa','2010-01-01','8000',2);
123456

Batch record:

insert into emp values ('jack chen','2011-01-01','18000',2),('andy lao','2013-01-01','18000',2);
1

Update record:

update emp set sal="7000.00" where ename="jack";

update emp e,dept d set e.sal="10000",d.deptname=e.ename where e.deptno=d.deptno and e.ename="lisa";
123

Delete record:

//Please check the where conditions carefully and carefully
delete from emp where ename='jack';
12

View records:

//View all fields
select * from emp;

//Query non duplicate records
select distinct(deptno) from emp ;
select distinct(deptno),emp.* from emp ;

//Condition query
//Comparison operator: > < > = < = < >! =
//Logical operators: and or
select * from emp where sal="18000" and deptno=2;
1234567891011

sort

//desc descending, asc ascending (default)
select * from emp order by deptno ;
select * from emp order by deptno asc;
select * from emp order by deptno desc,sal desc;
1234

Limit the number of records:

select * from emp limit 1;
select * from emp limit 100,10;
select * from emp order by deptno desc,sal desc limit 1;
123

Aggregation: function: count(): number of records / sum (sum)/ max(): maximum / min(): minimum

select count(id) from emp ;
select sum(sal) from emp ;
select max(sal) from emp ;
select min(sal) from emp ;
1234

group by group:

//Grouping statistics
select count(deptno) as count from emp group by deptno;
select count(deptno) as count,deptno from emp group by deptno;
select count(deptno) as count,deptno,emp.* from emp group by deptno;
1234

having secondary filtering of grouping results:

select count(deptno) as count,deptno from emp group by deptno having count > 2;
1

with rollup secondary summary of grouping results:

select count(sal),emp.*  from emp group by sal, deptno with rollup ;
1

Table connection:

• Left join: left join, which returns all records in the left table and records with equal connection fields in the right table;
• Right join: right join, which returns all records in the right table and records with equal connection fields in the left table;
• Inner join: inner join, also known as equivalent join, only returns rows with equal connection fields in two tables;
• full join: external join, return rows in two tables: left join + right join;
• cross join: the result is Cartesian product, which is the number of rows in the first table multiplied by the number of rows in the second table.

Inner join: only return rows with equal join fields in two tables

select * from emp as e,dept as d where e.deptno=d.deptno;
select * from emp as e inner join dept as d on e.deptno=d.deptno;
12

Left outer connection: includes all records in the left table and records with equal connection fields in the right table

select * from emp as e left join dept as d on e.deptno=d.deptno;
1

Right outer connection: includes all records in the right table and records with equal connection fields in the left table

select * from emp as e right join dept as d on e.deptno=d.deptno;
1

Subquery:

//=, !=
select * from emp where deptno = (select deptno from dept where deptname="Technology Department");
select * from emp where deptno != (select deptno from dept where deptname="Technology Department");

//in, not in 
//When you need to use the result set inside, you must use in(); 
select * from emp where deptno in (select deptno from dept where deptname="Technology Department");
select * from emp where deptno not in (select deptno from dept where deptname="Technology Department");

//exists , not exists
//Use exists() when you need to determine whether the following query results exist;
select * from emp where exists (select deptno from dept where deptno > 5);
select * from emp where not exists (select deptno from dept where deptno > 5);
12345678910111213

Record Union:

union:Returns the result after de duplication
select ename from emp union select ename from emp;

union all:Return all results
select ename from emp union all select ename from emp;
12345

DCL statement:
Add permissions:

grant select,insert on test.* to 'db_user_1'@'localhost'  identified by '123456';
flush privileges;
12

Recycle permission:

revoke insert on test.* from 'db_user_1'@'localhost';
1

2, Mysql data type

Integer type:

Specify width:
The specified display width is 5, which does not affect the actual data

create table t1 (id int , id2 int(5));
1

Note: INT (4) and INT (5). The characters in parentheses indicate the display width. The display width of the integer column has nothing to do with how many characters MySQL needs to display the value of the column, and has nothing to do with the size of the storage space required by the integer. The upper limit of data that can be stored in INT type fields is 2147483647 (signed) and 4294967295 (unsigned). In fact, when we choose to use the type of INT, whether it is INT (4) or INT (5), it stores a length of 4 bytes in the database.
zerofill:
Zero filling is used, and less than 5 bits are filled with 0 to match the data width

create table t2 (id int , id2 int(5) zerofill);
1

unsigned:

create table t3 (id int , id2 int(5) unsigned);
1

auto_increment:
Only for integer types
Generate unique identification
The value starts with 1 and increases line by line
At most one auto increment column can exist in a table
Auto incrementing column should be defined as not null
The auto increment column should only be primary key or unique

id int not null auto_increment primary key
1

Floating Point Types

Fixed point number type

Float, double, decimal features:
1.(m,d) representation: m refers to integer digits and D refers to decimal digits (also known as precision and scale)
2.float/double rounding loses precision. decimal will truncate the data and output warning
3. If the precision is not specified, float/double adopts the operating system default, and decimal is (10,0)
Bit type

1 storage bit field value
2 specifies the length of storing multi bit binary, which is 1 by default (range: 1 ~ 64)
3. bin()/hex() is required for reading. The normal select reading result is null
4 the inserted value will be converted into binary code. If the length runs, it will be processed normally, otherwise the insertion fails

create table t6 (id bit(1));
select bin(id) from t6;
12

Date and time type

Current system date

timestamp:return yyyy-mm-dd hh:mm:ss Width 19

timestamp:Not suitable for long-term storage,If it is out of range, it will be filled with zero value

//Display zero values in different formats
d date, t time,dt datetime
+------------+----------+---------------------+
| d          | t        | dt                  |
+------------+----------+---------------------+
| 2016-11-25 | 14:52:44 | 2016-11-25 14:52:44 |
+------------+----------+---------------------+

//Embodiment of default value
id1 timestamp
+---------------------+
| id1                 |
+---------------------+
| 2016-11-25 14:55:45 |
+---------------------+

//The timestamp field can only have one 'CURRENT_TIMESTAMP'
+-------+-----------+------+-----+---------------------+-----------------------------+
| Field | Type      | Null | Key | Default             | Extra                       |
+-------+-----------+------+-----+---------------------+-----------------------------+
| id1   | timestamp | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| id2   | timestamp | NO   |     | 0000-00-00 00:00:00 |                             |
+-------+-----------+------+-----+---------------------+-----------------------------+

//timestamp is related to time zone: SYSTEM refers to the time zone consistent with the host
show variables like "%_zone";
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
+------------------+--------+

//Modify time zone
set time_zone="+9:00"/stay [mysqld] Add below default-time-zone=timezone
123456789101112131415161718192021222324252627282930313233343536373839

particular year

year:The default is 4-bit format.1901~2155 And 0000. 2 Bit is no longer recommended,The higher version is no longer supported. 
1

Difference between timestamp and datetime:
1. timestamp supports a small range (from 1970-01-01 08:00:01 to a certain point in 2038)
2. The first timestamp field in the table will adopt the current system time by default If other fields are updated and the field has no assignment, the field will be updated automatically If the specified field does not meet the specification, it is filled with a zero value
3. timestamp queries and inserts are affected by the local time zone

datetime supports a wide range (1000-01-01 00:00:00 to 9999-12-31 23:23:59)

String type

The difference between char and varchar:

• char has a fixed length and high efficiency. It has been specified when creating fields. It is generally used for data storage of form submission with fixed length
• char returns to the trailing space when retrieving
• varchar is the dynamic length
• varchar keeps trailing spaces when retrieving
• varchar stores the actual content separately outside the cluster index. The actual length is represented by 1 to 2 bytes at the beginning of the content (2 bytes are required when the length is > 255)
• If the character type is gbk, each character can occupy up to 2 bytes; if the character type is utf8, each character can occupy up to 3 bytes

Enumeration type:

• Enumeration can only take one value in the collection
• If the value does not exist, the first value is used as the default
• If there is no error in inserting NULL, NULL is written to the table
• The number of members occupies 1 byte at 1255 and 2 bytes at 25565535, saving resources

create table `t8` (
    `gender` enum('m','f') default null
) engine=innodb default charset=utf8
123

Collection type

• Collection types are similar to enumerations, but support multivalued selection
• Up to 64 members can be saved, and every 8 members account for 1 byte
• Duplicate values will only be inserted once. If the collection range is found out, the insertion is null

create table t9 (col set ('a','b','c','d'));
1

3, Mysql operator

To be continued....

4, Common Mysql functions

5, MySQL management tools and Applications

6, Mysql storage engine

7, Selection of common data types

8, Mysql character set

9, Mysql index design and use

10, Views in Mysql

11, Transaction control and locking

12, Others
1. mysql - modify the initial value of self incrementing primary key

alter table  m_money_process  AUTO_INCREMENT =10;