Lesson three--sql operations and data types

"SQL classification: DDL DML DCL", DDL (Database definition Language)

Define different data segments, databases, tables, columns, indexes, and other database objects, common statement keywords: Create drop alter, etc.

1, modify the table field, ALTER TABLE statement use as follows

1) Modify the data type of the table field: ALTER TABLE table_name Modify ...
2) Add table field: ALTER TABLE table_name add ...
3) Delete table field: ALTER TABLE table_name drop ...
4) field rename: ALTER TABLE table_name change ...
5) Change the table name: ALTER TABLE table_name rename ...

Tips:
after entering MySQL, the Help content can be displayed via the "assist;" or "\h" command to clear the command line buffer via the "\c" command
*change and modify can modify the data type of the specified field in the table, except that it is inconvenient to write two times the column name after the change, but the advantage of changing is that you can modify the field name, modify cannot
*

DML (Data manipulation statement) 1, used to add, delete, update and query database records, and detect data integrity, common statement keywords: insert delete update SELECT, etc.

1) Additional deletions: Insert Update Delete Select

update里两表关联操作例子：update cv inner join cv2 on cv.c=cv2.c set cv.v=‘vvv‘;或者update cv,cv2 set cv.v=cv2.v where cv.c=cv2.c;

2) query for records that are not duplicated: DISTINCT keyword
3) conditional query: where keyword
4) Sorting and throttling:
Limit limits Display the number of data bars;
Desc and ASC are sort keywords; order by is sorted by a field. The
order by can be followed by several different sort fields, each with a different collation: if the value of the sort field is the same, the field with the same value is sorted by the second sort field, and if there is only one sort field, the same records will be unordered
5) Aggregation:
commonly used aggregate functions have sum (sum), COUNT (*) (number of records), Max (maximum), min (minimum)
The GROUP by keyword represents the field to be aggregated by classification;
with rollup is an optional syntax, Whether or not the table name summarizes the aggregated results of the classification, and the
having keyword is the filter for the condition after the categorized result;
having and where is the difference: having is a condition filter for the results of the aggregation, Where is the filtering of records prior to aggregation, and if logic allows, it is recommended to use where to filter as much as possible, because the result set is reduced, the efficiency of the aggregation is greatly increased, and then the logical view of whether to re-filter the
6) Table connection:
Table joins are divided into: Left join, right join, INNER join (inner join)
For example:

Mysql> SELECT * FROM t1;+----+------+| ID |  Name |+----+------+| 2 |  Test | | 3 |  Test | | 4 |  Test | | 5 | Test |+----+------+4 rows in Set (0.00 sec) mysql> SELECT * from t2;+----+-------+| ID |  Name |+----+-------+ | 3 |  Test3 | | 4 |  test4 | | 5 |  Test6 | | 0 | Test |+----+-------+4 rows in Set (0.00 sec) mysql> Select T1.id,t1.name,t2.id,t2.name from T1 left join T2 on T1.ID=T2 . ID; +----+------+------+-------+| ID | name | ID |  Name |+----+------+------+-------+| 3 |    Test | 3 |  Test3 | | 4 |    Test | 4 |  test4 | | 5 |    Test | 5 |  Test6 | | 2 | Test | NULL |  NULL |+----+------+------+-------+4 rows in Set (0.00 sec) mysql> Select T1.id,t1.name,t2.id,t2.name from T1 right Join    T2 on T1.id=t2.id; +------+------+----+-------+| ID | name | ID |    Name |+------+------+----+-------+| 3 |  Test | 3 |    Test3 | | 4 |  Test | 4 |    test4 | | 5 |  Test | 5 | Test6 | | NULL |  NULL | 0 | Test |+------+------+----+-------+4 rows in Set (0.00 sec) Mysql> SeleCT t1.id,t1.name,t2.id,t2.name from t1 inner joins T2 on T1.id=t2.id; +----+------+----+-------+| ID | name | ID |  Name |+----+------+----+-------+| 3 |  Test | 3 |  Test3 | | 4 |  Test | 4 |  test4 | | 5 |  Test | 5 | TEST6 |+----+------+----+-------+3 rows in Set (0.00 sec)

7) Sub-query:
Keywords used for subqueries include: in not in =! = Exists NOT exists, etc.
Subqueries can generally be converted to table joins, and table joins are used in many cases to optimize subqueries;
8) Record Union: The keyword is Union, Union all,union is the result of the Union all after a distinct, the result of removing duplicate records

Tips:
(1) Multiple tables update data at the same time: Update table_a table_b set .... where Table_a.xxx=table_b.xxx
(2) Multiple tables delete data simultaneously: Delete table_a,table_b from Table_a,table_b where table_a.xxx=table_b.xxx and ... [Other conditions], so that the record of the Where condition is met, the Table_a,table_b table will be deleted

Three, DCL (Data control statements)

Controls the level of permissions and access between different data segments that define the database, table, field, user access and security level, Statement keyword: Grant revoke, and so on

================================================

"Summary of data types in MySQL"

MySQL supports a variety of data types and can be broadly divided into three categories: numeric, date/time, and string (character) types.

First, numeric type

The following table shows the storage and scope of each integer type that is required:

1, tinyint smallint mediumint int bigint integer type:

An "Out of range" error message if an operation of type range is out of scope

2, Integer data type, support to specify the display width in parentheses after the type name, as an example of the type int:

(1) can be defined directly as an int type, which does not display the specified width, the default is int (11), or it can display the specified width, such as the definition field int (5), indicating that when the numeric width is not enough 5 bits to fill the width before the number, generally with zerofill use. (Zerofill is filled with 0 if the number of digits does not meet the specified number of digits;
If a column is specified as Zerofill, then MySQL automatically adds the unsigned property to the column, the original int default int (11), With the Zerofil property, the default is int (Ten)
It is worth noting that after the width limit is set, if the insertion is greater than the width limit, it will not be truncated or inserted into the error, display the specified width, only in the case of the width of the insufficient digits with 0 padding, the insertion value is greater than the width , or the actual precision of the installation type is saved.
width format doesn't really make sense anymore

Mysql> desc t3;+-------+---------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| ID1 | Int (11) |     YES | |       NULL | || Id2 | Int (5) |     YES | |       NULL | |+-------+---------+------+-----+---------+-------+2 rows in Set (0.00 sec) mysql> insert into T3 values (; Query OK, 1 row affected (0.01 sec) mysql> select * FROM t3;+------+------+| ID1 |    ID2 |+------+------+|    1 | 1 |+------+------+1 row in Set (0.00 sec) mysql> Select Length (ID1), Length (ID2) from t3;+-------------+-------------+| Length (ID1) |           Length (ID2) |+-------------+-------------+|           1 | 1 |+-------------+-------------+mysql> ALTER TABLE t3 modify ID1 int zerofill;                         Query OK, 1 row affected (0.06 sec) records:1 duplicates:0 warnings:0mysql> select * from T3; +------------+------+| ID1 | Id2 |+------------+------+|    0000000001 | 1 |+------------+------+1 row in Set (0.00 sec) mysql> Select Length (ID1), Length (ID2) from T3; +-------------+-------------+| Length (ID1) |          Length (ID2) |+-------------+-------------+|           10 | 1 |+-------------+-------------+* Note that the length after filling 0 is 10 instead of 11, because when you select a property zerofill, it is unsigned at the same time. (Adding negative values will report warnings value out of range or default to 0),*mysql> insert into t3 (ID1,ID2) values ( -1,1); ERROR 1264 (22003): Out of Range value to column ' Id1 ' at row 1

3. Decimal type:

It is divided into floating-point and fixed-point numbers, floating-point numbers include float (single-precision) and double (double-precision), and fixed-point numbers are decimal
fixed-point decimal is stored as a string inside MySQL, more accurate than floating-point numbers, and is suitable for representing high-precision data such as currency.
(1) floating-point and fixed-point numbers can be represented by the type name plus "(M.D)" (precision, scale), "(m,d)" means that the value shows a total of M-bit numbers (integer digits + decimal places), where the D-bit is located after the decimal point.
MySQL is rounded when the value is saved;
float and double are displayed by default in terms of actual precision (determined by the actual hardware and operating system) when no precision is specified, and if there is precision and scale, the rounded result is automatically inserted and the system does not error
Decimal when the precision is not specified, the default integer bit is 10 bits, the default decimal digit is 0 bits, also is the default decimal (10,0) to operate, if the data beyond the accuracy and scale, the system will error;

4. Bit bit type:

Used to hold bit field values, bit (m) can be used to hold many binary numbers, M range from 1~64, if not write the default 1 bit, for bit fields, directly using select can not see results, need to use Bin () (shown in binary format) or hex () (shown in hexadecimal format) function to read

Ii. Date and Time type

1. Summary:

Years: Year type
Month Date: Date type
Hours seconds: Time type
Month and day hours: Datatime type
Month and day hours: Timestamp type

An timestamp type of characteristic test: (1) defines the first timestamp type field: Add column t timestamp; default T field property is not NULL default Current_timestamp on UPDATE Current_timestamp; Defines the second and above TIMESTAMP type fields: Add column t TIMESTAMP; the Default TT field property is not NULL default ' 0000-00-00 00:00:00 '. That is, the timestamp column is not explicitly defined as NULL, and is set to NOT NULL by default. (2) The first timestamp column in the table, if not defined as NULL, defines the default value, or on update, automatically assigns the default Current_timestamp and on Update current_timestamp properties All timestamp columns after the first timestamp column in the table are automatically assigned the default value ' 0000-00-00 00:00:00 ' if they are not defined as null. When inserting, if the values of these columns are not specified, they are automatically specified as ' 0000-00-00 00:00:00 ' and do not produce a warning. (3) After mysql5.6, the default setting method of the above timestamp type is discarded, when MySQL is started, the method of canceling alarm is setting explicit_defaults_for_timestamp=true parameter, after setting this parameter, The default handling of columns of type timestamp also varies: (3.1) timestamp column supports the Null property if it is not explicitly defined as NOT NULL. Setting the timestamp column value to NULL will not be set to current timestamp; (3.2) The default Current_timestamp and on Update Current_timestamp properties are no longer assigned automatically. These properties must be explicitly specified, (3.3) declared as NOT null and no default value is explicitly specified without a default value. When a column is inserted in a table, and no value is assigned to the timestamp column, an error is thrown if it is in strict SQL mode, and if strict SQL mode is not enabled, the column is assigned a value of ' 0000-00-00 00:00:00′ and a warning appears. (This is the same as when MySQL handles other time-type data, such as DateTime) 

2, testing,

Mysql> desc date_time;+-------+-----------+------+-----+-------------------+-----------------------------+| Field | Type | Null | Key | Default | Extra |+-------+-----------+------+-----+-------------------+-----------------------------+| y | Year (4) |     YES | |                             NULL | || D | Date |     YES | |                             NULL | || T | Time |     YES | |                             NULL | || DT | datetime |     YES | |                             NULL | || ts | Timestamp |     NO | | Current_timestamp | On update current_timestamp |+-------+-----------+------+-----+-------------------+-----------------------------+ 5 rows in Set (0.00 sec) mysql> Insert to Date_time values (now (), now (), now (), now (), now ()); Query OK, 1 row affected, 1 Warning (0.00 sec) mysql> SELECT * from date_time;+------+------------+----------+---------- -----------+---------------------+| y | D | T | DT | TS |+------+------------+----------+---------------------+---------------------+| 2016 | 2016-08-04 | 08:52:58 | 2016-08-04 08:52:58 | 2016-08-04 08:52:58 |+------+------------+----------+---------------------+---------------------+

Note: The difference between a datetime type and a timestamp type:
1, timestamp type is also used to represent dates, but differs from datetime, for timestamp types, The default value, Current_timestamp (System date), is automatically created so that even if you insert a null value and do not even insert a value, the system time is updated by default. The datetime type does not have this attribute
2, the timestamp type is also associated with the time zone, when the date is inserted, it is converted to the local time zone, and when it is removed from the database, it is also necessary to convert the date to the local time zone, so that Two users in different time zones may see the same date differently.
3, timestamp supports a smaller time range than the range of datetime types

Mysql> desc dt_ts;+-------+-----------+------+-----+-------------------+-----------------------------+| Field | Type | Null | Key | Default | Extra |+-------+-----------+------+-----+-------------------+-----------------------------+| DT | datetime |     YES | |                             NULL | || ts | Timestamp |     NO | | Current_timestamp | On update current_timestamp |+-------+-----------+------+-----+-------------------+-----------------------------+ 2 rows in Set (0.00 sec) mysql> Insert to Dt_ts values (now (), now ()); Query OK, 1 row Affected (0.00 sec) mysql> Select * from dt_ts;+---------------------+---------------------+| DT | TS |+---------------------+---------------------+| 2016-08-04 09:01:44 | 2016-08-04 09:01:44 |+---------------------+---------------------+1 row in Set (0.00 sec) mysql> INSERT into Dt_ts valu  ES (null,null); Query OK, 1 row Affected (0.00 sec) MYSQL&GT                SELECT * from Dt_ts; +---------------------+---------------------+| DT | TS |+---------------------+---------------------+| 2016-08-04 09:01:44 | 2016-08-04 09:01:44 | | NULL | 2016-08-04 09:02:19 |+---------------------+---------------------+2 rows in Set (0.00 sec) mysql> INSERT into dt_ts (dt      ) values (null);               Query OK, 1 row Affected (0.00 sec) mysql> select * from Dt_ts; +---------------------+---------------------+| DT | TS |+---------------------+---------------------+| 2016-08-04 09:01:44 | 2016-08-04 09:01:44 | | NULL | 2016-08-04 09:02:19 | | NULL | 2016-08-04 09:05:05 |+---------------------+---------------------+3 rows in Set (0.00 sec)

Three, String type

1. The difference between Cahr and varchar types:

(1) The length of the char column is fixed to the length declared when the table was created, and the length can be any value from 0~255;

Mysql> desc cv;+-------+------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+------------+------+-----+---------+-------+| C | char (2) |     YES | |       NULL | || V | varchar (5) |     YES | |       NULL | |+-------+------------+------+-----+---------+-------+2 rows in Set (0.00 sec) mysql> CREATE TABLE CV (c char (2), V varchar (5)); Query OK, 0 rows affected (0.02 sec) mysql> desc cv;+-------+------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+------------+------+-----+---------+-------+| C | char (2) |     YES | |       NULL | || V | varchar (5) |     YES | |       NULL | |+-------+------------+------+-----+---------+-------+2 rows in Set (0.01 sec) mysql> INSERT into CV (c) VALUES (' cc '); Query OK, 1 row Affected (0.00 sec) mysql> INSERT into CV (c) VALUES (' CCC '); ERROR 1406 (22001): Data too long for column ' C ' at row 1mysql> insert into CV (c) VALUES (' Hello '); Query OK, 1 row Affected (0.00 sec) mysql> INSERT into CV (c) VALUES (' Hello '); ERROR 1406 (22001): Data too long for column ' C ' at row 1//This description, a Chinese character occupies one character, and N in char (n) represents the number of characters instead of the number of bytes///But the char type occupies a certain number of bytes, also In other words, the actual number of characters stored is not necessarily the same as the char type, which actually stores numbers, letters, or Chinese characters.

2, the Varcahr type is only variable long string, the length between 0~65535.

在检索时，char列删除了尾部的空格，而varchar列则保留这些空格mysql> desc cv;+-------+------------+------+-----+---------+-------+| Field | Type       | Null | Key | Default | Extra |+-------+------------+------+-----+---------+-------+| c     | char(2)    | YES  |     | NULL    |       || v     | varchar(5) | YES  |     | NULL    |       |+-------+------------+------+-----+---------+-------+2 rows in set (0.00 sec)mysql> insert into cv values(‘ab ‘,‘ab ‘);Query OK, 1 row affected (0.00 sec)mysql> select length(c),length(v) from cv;+-----------+-----------+| length(c) | length(v) |+-----------+-----------+|         2 |         3 |+-----------+-----------+1 row in set (0.00 sec)mysql> select concat(c ,‘+‘),concat(v ,‘+‘) from cv;+----------------+----------------+| concat(c ,‘+‘) | concat(v ,‘+‘) |+----------------+----------------+| ab+            | ab +           |+----------------+----------------+1 row in set (0.00 sec)

Attention!!
1, modify the field, for example, char (4) is modified to char (2), if the data length is greater than the 2,alter field will fail to error
2. The difference between char and varchar in MySQL:
1), char is fixed length, if the length is insufficient, use the right padding to fill the string to the specified length, and varchar is not, how long it lasts.
2), for retrieval efficiency, char is more efficient than the varchar
3. The length of the char and varchar type declarations represents the maximum number of characters that you want to save. For example, a CHAR (30) can occupy 30 characters. If the value assigned to a char or varchar column exceeds the maximum length of the column, the value is clipped so that it fits. If the character being cropped is not a space, a warning is generated. If you crop a non-whitespace character, it causes an error (rather than a warning) and disables the insertion of values by using strict SQL mode.
4. At the time of retrieval, the char column removes trailing spaces, while the varchar column preserves the spaces

3. Binary and varbinary types

Similar to char and varchar, the difference is that they contain a binary string instead of a non-binary string.

4. Enum type

Enumeration type, 1 bytes are required for enumeration of 1~255 members, 2 bytes for 255~65535 members, up to 65,535 members allowed;

5. Set type

Similar to enum, also a string object that can contain 0~64 members

====================================================================================

"Common SQL Functions" one, data type conversion function 1, cast (xxx as type)

The argument to the CAST () function is an expression that includes the source value and the target data type separated by the AS keyword.

(1) Converting a character type to an integral type

mysql> SELECT CAST(‘12.1‘ AS signed);     +------------------------+| CAST(‘12.1‘ AS signed) |+------------------------+|                     12 |+------------------------+

(2) Converting a time type to a character type

mysql> select cast(current_timestamp as char(20));+-------------------------------------+| cast(current_timestamp as char(20)) |+-------------------------------------+| 2016-08-11 07:56:15                 |+-------------------------------------+1 row in set (0.00 sec)mysql> select cast(current_timestamp as char(10));+-------------------------------------+| cast(current_timestamp as char(10)) |+-------------------------------------+| 2016-08-11                          |+-------------------------------------+

(3) The decimal type is converted to an integer and is rounded

mysql> select cast(99.4 as signed); +----------------------+| cast(99.4 as signed) |+----------------------+|                   99 |+----------------------+1 row in set (0.00 sec)mysql> select cast(99.5 as signed); +----------------------+| cast(99.5 as signed) |+----------------------+|                  100 |+----------------------+1 row in set (0.00 sec)

2. CONVERT (XXX, type)

(1) Converting a character type to an integral type

mysql> select convert(‘99.91‘,signed);+-------------------------+| convert(‘99.91‘,signed) |+-------------------------+|                      99 |+-------------------------+1 row in set, 1 warning (0.00 sec)

(2) Converting decimal types to shaping (rounding)

mysql> select convert(99.91,signed);  +-----------------------+| convert(99.91,signed) |+-----------------------+|                   100 |+-----------------------+1 row in set (0.00 sec)

(3) converting an integer type to decimal

mysql> select convert(99,decimal);+---------------------+| convert(99,decimal) |+---------------------+|                  99 |+---------------------+1 row in set (0.00 sec)mysql> select convert(99,decimal(3,1));+--------------------------+| convert(99,decimal(3,1)) |+--------------------------+|                     99.0 |+--------------------------+1 row in set (0.00 sec)

(4) Convert to date type, time type, etc.
Mysql> Select CONVERT (current_timestamp,date);
+ ——————————— +
| Convert (current_timestamp,date) |
+ ——————————— +
| 2016-08-11 |
+ ——————————— +
1 row in Set (0.00 sec)

mysql> select convert(current_timestamp,time);+---------------------------------+| convert(current_timestamp,time) |+---------------------------------+| 10:34:57                        |+---------------------------------+1 row in set (0.01 sec)mysql> select convert(current_timestamp,datetime);+-------------------------------------+| convert(current_timestamp,datetime) |+-------------------------------------+| 2016-08-11 10:35:03                 |+-------------------------------------+

Second, the use of Date_format function

mysql> select current_timestamp;+---------------------+| current_timestamp   |+---------------------+| 2016-08-11 10:36:57 |+---------------------+1 row in set (0.00 sec)mysql> select date_format(current_timestamp,‘%Y-%m-%d‘);+-------------------------------------------+| date_format(current_timestamp,‘%Y-%m-%d‘) |+-------------------------------------------+| 2016-08-11                                |+-------------------------------------------+1 row in set (0.00 sec)mysql> select date_format(current_timestamp,‘%H:%i:%s‘);+-------------------------------------------+| date_format(current_timestamp,‘%H:%i:%s‘) |+-------------------------------------------+| 10:39:03                                  |+-------------------------------------------+

Three, the actual length conversion of the character type

Other

From for notes (Wiz)

Third lesson--sql operations and data types