MySQL Basic statement

View Current Library: Select database ();
Switch directories: Use DB1;

View:
1. View library: show databases;
2. View table: Show tables; View all tables (files) below the current library
Show CREATE TABLE T12; Specify to view description information for a table
Desc t13; View specific information for a table
3. View records: Select Name,id from T13; View information for a single record
SELECT * from T13; View all record information

Increase:
1. Add Library: Create database DB2; Create DATABASE DB2 CharSetUTF8; (Add Library: 1. Library Name 2. Specify the character number, and the sub-file inherits)
2. Add table (i.e. add header information): CREATE TABLE T1 (id int,name char);
ALTER TABLE T1 add age int; You can add a field column, such as: New Age Column
3. Add record: INSERT INTO T1 (id,name) VALUES (1, ' Zhangsan '), (2, ' Lisi ');
< #insert into T1 values (' Wangwu '), (' AAAA '), (' bbbb '); You can also add a field individually >

Change:
1. Change the library (change character encoding): ALTER TABLE DB2 CHARSET GBK;
2. Change the table:
2.1alter table T1 Modify name char (15); Changing the character type individually specifies the length of the stored width
2.2alter table T1 Change name name char (20); Change the name of a field
2.3alter table T1 add age int; You can add a field column for example: New Age Column
3. Change record:UpdateT1SetName= "Lisi_new"where id=2;
< update db1.t1 set name= "Lisi_new" where id=1; can specify file for modify >

Delete:
1. Delete Library: drop database db1;
2. Delete tables: drop table T1; (This is to delete the form file)
3. Delete Record: Delete from T1 where id=3; Deletes a field, specifying the eligible
Delete from T1 (this is the contents of the empty table)
truncate t17; (clears the contents of the table, and also empties the cumulative count of the IDs)




Focus:
starting with a new table you can restrict the table character segments uniformly:
1. The qualified ID cannot be empty and only exists: CREATE TABLE t17 (ID int primary key auto_increment);
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| ID | Int (11) | NO | PRI | NULL | auto_increment |
+-------+---------+------+-----+---------+----------------+
1 row in Set


The difference between delete and truncate:

Delete from T1 after deleting the contents of the table, the data is cleared, but the Auto_increment=5 automatic count is preserved,
-----------------------------------------+
| t17 | CREATE TABLE ' t17 ' (
' id ' int (one) not NULL auto_increment,
' Name ' char () DEFAULT NULL,
PRIMARY KEY (' id ')
) engine=innodb auto_increment=5 DEFAULT Charset=utf8 |
+-------+-------------------------------------------------------------------------------------------+
Clean up and then go to add name, will send the ID will be accumulated from the original 5, will not start from 0:
Insert into t17 (name) VALUES (' AAA '), (' BBB '), (' CCC ');
+----+------+
| ID | name |
+----+------+
| 5 | AAA |
| 6 | BBB |
| 7 | CCC |
+----+------+

Truncate t17; This removal not only cleans up the data, but also cleans up the automatic count of IDs.





Library (folder) detailed operation:
1. Database is case sensitive
2. Cannot use keywords

The format is:
CREATE table library name. Table Name (
Field name 1 type [(width) constraint],
Field Name 2 Type [(width) constraint],
Field Name 3 Type [(width) constraint]
);
Constraints: Additional restrictions on fields outside of the data type

Attention:
1. You cannot add a comma after the last field
2. Field names cannot be the same in the same table
3. Width and constraints are optional, field names and types are required


For example:
CREATE table t4 (id int, age int, sex char);
The name of the library. Table name (field name Type [(width, constraint)]) Note: In general, the brackets are optional items



Integer type:

1.tinyint: Signed By default (-128,127)
Show CREATE TABLE T5;
+-------+----------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------+
| T5 | CREATE TABLE ' T5 ' (
' X ' tinyint (4) DEFAULT NULL
) Engine=innodb DEFAULT Charset=utf8 |
+-------+----------------------------------------------------------------------------------------+
1 row in Set (0.00 sec)
INSERT into T5 values (-1); #可以输入验证是有符号的

Normal situation
INSERT into T5 VALUES (-127);
+------+
| x |
+------+
| -1 |
| -127 |
+------+
Exception Condition: (non-strict mode if the input is out of range, will be populated according to the nearest limit value)
Mysql> INSERT into T5 values (-129);
Query OK, 1 row affected, 1 warning (0.06 sec)

Mysql> SELECT * from T5;
+------+
| x |
+------+
| -1 |
| -127 |
| -128 |
+------+
3 Rows in Set (0.00 sec)


Set Strict mode:
Before change: select @ @sql_mode;
+------------------------+
| @ @sql_mode |
+------------------------+
| no_engine_substitution | #初始模式
+------------------------+
1 row in Set (0.00 sec)
Change: Set global sql_mode= ' Strict_trans_tables '; (Restart client after change)
Mysql> INSERT into T5 values (128);
ERROR 1264 (22003): Out of Range value to column ' X ' at row 1 #再次输入会报错
Mysql> SELECT @ @sql_mode;
+---------------------+
| @ @sql_mode |
+---------------------+
| Strict_trans_tables | #模式已经发生变化
+---------------------+
1 row in Set (0.00 sec)


tinyint integer type: Convert to unsigned (0-255)
CREATE table T6 (x tinyint unsigned);
mysql> desc T6;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| x | tinyint (3) unsigned | YES | | NULL | |
+-------+---------------------+------+-----+---------+-------+
1 row in Set (0.02 sec)

2.int type: Not limit storage width, limit display width
CREATE TABLE t7 (ID int (5));
INSERT into T7 values (30000);
Mysql> SELECT * from T7;
Mysql> SELECT * from T7;
+-------+
| ID |
+-------+
| 30000 | #id Int (5) refers to 5 characters, not enough of the front means to be filled with control
| 1 |
| 2 |
| 33 |
| 333 |
+-------+

int comes with 11 bits (-10 total 11-bit signed range), so you don't need to specify the width, the default is the most reasonable



3. Float Type:
Format: float (all digits, decimal digits)
CREATE TABLE T8 (x float (255,30));
CREATE table T9 (x double (255,30));
CREATE table T10 (x Decimal (65,30));

Accuracy view: Decimal precision is the highest
INSERT into T8 values (1.11111111111111111111111);

INSERT into T9 values (1.11111111111111111111111);

INSERT into T10 values (1.11111111111111111111111);
Mysql> SELECT * from T8;
+----------------------------------+
| x |
+----------------------------------+
| 1.111111164093017600000000000000 |
+----------------------------------+
1 row in Set (0.00 sec)

Mysql> select * from T9;
+----------------------------------+
| x |
+----------------------------------+
| 1.111111111111111200000000000000 |
+----------------------------------+
Mysql> select * from T10;
+----------------------------------+
| x |
+----------------------------------+
| 1.111111111111111111111111111111 |
+----------------------------------+

In general the use of float accuracy is sufficient.


4. Date Type:

First build a student file: Set the desired properties of the file header
CREATE TABLE Student (
ID int primary KEY auto_increment,
Name Char (16),
Born_year year,
Birth date,
Class_time time,
Reg_time datetime
);

mysql> desc Student;
+------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+----------------+
| ID | Int (11) | NO | PRI | NULL | auto_increment |
| name | char (16) | YES | | NULL | |
| Born_year | Year (4) | YES | | NULL | |
| Birth | Date | YES | | NULL | |
| Class_time | Time | YES | | NULL | |
| Reg_time | datetime | YES | | NULL | |
+------------+----------+------+-----+---------+----------------+
6 rows in Set (0.02 sec)

Increase the value of each property:
INSERT into student (Name,born_year,birth,class_time,reg_time) VALUES (' Yzz ', now (), now (), now (), now ());

View Table Results:
Mysql> select * from student;
+----+------+-----------+------------+------------+---------------------+
| ID | name | Born_year | Birth | Class_time | Reg_time |
+----+------+-----------+------------+------------+---------------------+
| 1 | Yzz | 2018 | 2018-07-19 | 22:26:10 | 2018-07-19 22:26:10 |
+----+------+-----------+------------+------------+---------------------+
1 row in Set (0.00 sec)


To set the date and time yourself:
INSERT into student (Name,born_year,birth,class_time,reg_time) values
(' Egon1 ', 2000, ' 2000-11-11 ', ' 08:30:00 ', 20171111111111);
#1. Without the quotation marks, the number is together, the system default output will not be all-
#2. If the quotation mark "08:30:00", the inside according to the input format to fill in,
Output: Year-month-day time: minutes: seconds this format

5. Character type: auto-fill the space is after the completion of
Char: fixed length
VARCHAR: variable length

The same point: width refers to the maximum number of characters stored, more than can not be stored properly

Different points:
CHAR (5):
' m '---> ' m ' 5 characters #给1个m就补4个字符
varchar (5):
' m '---> ' m ' 1 characters #给1个就存1个字符

Out of range will be an error:
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| ID | Int (11) | YES | | NULL | |
| name | char (2) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
mysql> INSERT INTO T12 (id,name) VALUES (1, ' Yangzhizong '), (2, ' m ');
1406-data too long for column ' name ' at row 1

Turn on Strict mode: error when exceeding set length
Set global sql_mode= "Strict_trans_tables,pad_char_to_full_length"

+----+------+
| ID | name |
+----+------+
| 1 | Ya |
| 2 | m | #右边有空格计算长度时是忽略的
+----+------+
Select Char_length (name) from T12;
Note the point:
1. The space on the right is not automatically added when calculating the length.
2. The left side will be added automatically.
Insert into T12 (Id,name) VALUES (6, ' Zhangsan '), (7, ' Lisi '), (8, ' Yangzhi ');
+-------------------+
| Char_length (name) |
+-------------------+
| 2 |
| 1 |
| 8 |
| 3 |
| 3 |
| 10 |
| 8 |
| 10 |
+-------------------+


Note: MySQL ignores the right space at query time for where field = "value", that is, where field = "Value"
When there is a space on the left: SELECT * from T12 where name= "Yangzhi" must be followed by a space
There are spaces on the right: you can manually add a space when you query, or you can not add spaces

1. The above search method data accurately match;
2. If the like fuzzy match will not ignore the right side of the space
+----+------------+
| ID | name |
+----+------------+
| 1 | Ya |
| 2 | m |
| 3 | Mawenjie |
| 4 | MWJ |
| 5 | Yzz |
| 6 | Zhangsan |
| 7 | Lisi |
| 8 | Yangzhi |
+----+------------+
SELECT * from T12 where name is like "m%"; #任意无穷个, all M-Start
+----+----------+
| ID | name |
+----+----------+
| 2 | m |
| 3 | Mawenjie |
| 4 | MWJ |
+----+----------+
SELECT * from T12 where name is like "m_"; #任意一个例如: MI, followed by only one character, that is, counting all the 2 characters starting with M
SELECT * from T12 where name is like "m_ _ _"; You can follow the number of digits you're looking for (no spaces between underscores, to illustrate the effect)

VARCHAR: Save well, take bad, need to add another head in front, such as the following pseudo-code:
CHAR (5)
Egon |axx |LXX |FM |

varchar (5)
1bytes+egon|1bytes+axx|1bytes+lxx|1bytes+fm|


6. Enumerations and collection types
enum enum (' A ', ' B ', ' C '): choose one More
Set set (' A ', ' B ', ' C '): Multiple selection
CREATE TABLE T13 (name varchar (), sex enum (' mame ', ' female ', ' unkown '), hobbies set (' Read ', ' Music ', ' play ')); Input
Desc t13; View
+---------+--------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------------------------+------+-----+---------+-------+
| name | varchar (15) | YES | | NULL | |
| sex | Enum (' mame ', ' female ', ' unkown ') | YES | | NULL | |
| Hobbies | Set (' Read ', ' Music ', ' play ') | YES | | NULL | |
+---------+--------------------------------+------+-----+---------+-------+

Logging information: Restrict user from Enum enumeration, set set inside to select input
INSERT into t13 values (' Zhangsan ', ' female ', ' play '); Select a single

INSERT into t13 values (' Lisi ', ' mame ', ' music,play,read '); Collections can be counted to select multiple

MySQL Basic statement