SQL Profile and Table Creation

1. Structure of tables

Relational databases manage data through two-dimensional tables consisting of rows and columns similar to Excel worksheets. Two-dimensional tables used to manage data are abbreviated as tables in relational databases.

The data returned according to the content of the SQL statement must also be in the form of two-dimensional tables, which is also one of the characteristics of relational database. Returns a result that cannot be executed if it is not an SQL statement for a two-dimensional table.

The column (vertical direction) of a table is called a field, which represents the data items stored in the table. The row (horizontal direction) of a table is called a record, which corresponds to a piece of data.

Relational databases must read and write data in a behavioral unit.

Only one data can be entered in a cell.

2.SQL statements and their types

SQL is a language developed for operating databases.

SQL describes the desired operation through a statement and sends it to RDBMS.

SQL can be divided into DDL, DML and DCL according to the purpose of operation.

SQL is a statement (SQL statement) composed of keywords, table names, column names, etc.

Describe the content of the operation.

2.1.DDL

DDL (Data Definition Language) is used to create or delete databases for storing data and tables in databases.

create: create objects such as databases and tables
 drop: Delete objects such as databases and tables
 alert: Modify the structure of objects such as databases and tables

2.2 DML

DML (Data Manipulation Language) is used to query or change records in tables.

select: Data in the query table
 Insert: insert new data into a table
 updata: Update the data in the table
 delete: delete data from tables

2.3DCL

DCL (Data Control Language) is used to confirm or cancel changes to data in the database. In addition, users of RDBMS can also set whether they have permission to operate objects (database tables, etc.) in the database.

commit: Confirm changes to data in the database
 rollback: Cancels changes to data in the database
 Grant: grant user permission to operate
 Revoke: revoke the user's operation rights

3. Table creation

3.1 Create a database

Create database < database name >;
Example: create database shop;

3.2 Create tables

create table <Table name>
(<List 1> <data type> <Constraints required for this column>，
<List 2> <data type> <Constraints required for this column>，
<List 3> <data type> <Constraints required for this column>，
<List 4> <data type> <Constraints required for this column>，
.. .
<Constraint 1 of the table>， <Constraints 2 of the table>，......)
//Example:
create table product
(product_id char(4) not null,
product_name varchar(100) not null,
product_type varchar(32) not null,
sale_price integer,
purchase_price interge ,
regist_date data ,
primary key (product_id));

4. Designation of data types

detailed

Partial data types

Numbers: tinyint, int, bigint float, double, decimal

Strings: char, varchar, text

Time types: data, time, data time, timestamp

Enumeration and collection types: enum, set

4.1 tinyint type

tinyint[(m)] [unsigned] [zerofill] [] is optional

Signed: -128-127 unsigned: 0-255, no Boolean value in SQL, constructed using tinyint(1).

Using 1 for TRUE,0 for FALSE, boolean is of type tinyint(1) in MySQL.
MySQL has four constants: true, false, TRUE and FALSE, which represent 1,0, 1,0, respectively.

mysql> create table t2(YON boolean);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into t2(YON) values(true);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t2;
+------+
| YON  |
+------+
|    1 |
+------+
mysql> create table t3(YON tinyint(1));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t3(YON) values(true);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t3;
+------+
| YON  |
+------+
|    1 |
+------+
mysql> create table t4(YON tinyint(1));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t4(YON) values(0);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t4;
+------+
| YON  |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

4.2 int

The data type (numeric) used to specify columns that store integers cannot store decimals. int(5),5 represents the display length, other data types are storage width, so when we design the table, the field of int type does not need display width, the default is the number of digits of the total length + 1. Negative numbers cannot be zerofill

mysql> create table t2(number int(1));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t2(number) values(123);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t2;
+--------+
| number |
+--------+
|    123 |
+--------+
1 row in set (0.00 sec)

4.3 float type

float[(m,d)] [unsigned] [zerofill]

M is the length and D is the number after the decimal point. The maximum value of M is 255, and the maximum value of D is 30.

mysql> create table t4(digite float(6,3) zerofill);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t4(digite) values(1.2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t4;
+--------+
| digite |
+--------+
| 01.200 |
+--------+

4.4 char type

Char is the abbreviation of character and is used to specify the data type (character type) of the column that stores the string. You can specify in parentheses the length (maximum length) of the string that the column can store, as in char(10) or char(200). The part of the string that exceeds the maximum length cannot be entered into the column.

Strings are stored as fixed-length strings in columns designated as chars. The so-called fixed-length string is that when the length of the string stored in the column does not reach the maximum length, half-corner space is used to make up for it. For example, when we type'abc'into a column of type char(8),
It will be saved in the form of'abc'(five half-corner spaces behind the abc).

When searching or querying, the result will automatically delete the space at the end unless we open the pad_char_to_full_length SQL mode (set the SQL mode: SET sql_mode = PAD_CHAR_TO_FULL_LENGTH');

4.5 varchar type

Like char type, varchar type is also used to specify the data type (string type) of the column in which the string is stored, or the length (maximum length) of the string can be specified by numbers in parentheses. But this type of column is a variable length string to save the fixed length of the string string, when the number of characters does not reach the maximum length, it will be filled with half-corner space, but the variable length string is different, even if the number of characters does not reach the maximum length, it will not be filled with half-corner space. For example, when we enter the string'abc'into a column of varcha(8), we save the string'abc'. When creating tables, type of fixed length is put forward and type of variable length is put back.

mysql> create table t1(x char(5),y varchar(5));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t1 values('abc ','abc ');
Query OK, 1 row affected (0.00 sec)

mysql> select @@sql_mode;
+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)
#When retrieving char-type data, the space at the end is not included in the character length (including the one you created)
mysql> select x,char_length(x),y,char_length(y) from t1;
+------+----------------+------+----------------+
| x    | char_length(x) | y    | char_length(y) |
+------+----------------+------+----------------+
| abc  |              3 | abc  |              4 |
+------+----------------+------+----------------+
1 row in set (0.00 sec)

mysql> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@sql_mode;
+-------------------------+
| @@sql_mode              |
+-------------------------+
| PAD_CHAR_TO_FULL_LENGTH |
+-------------------------+
1 row in set (0.00 sec)

mysql> select x,char_length(x),y,char_length(y) from t1;
+-------+----------------+------+----------------+
| x     | char_length(x) | y    | char_length(y) |
+-------+----------------+------+----------------+
| abc   |              5 | abc  |              4 |
+-------+----------------+------+----------------+

On: sql_mode

4.5 Time

mysql> create table t3(y year,d date,dt datetime,t time);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t3(y,d,dt,t) values(now(),now(),now(),now());
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from t3;
+------+------------+---------------------+----------+
| y    | d          | dt                  | t        |
+------+------------+---------------------+----------+
| 2019 | 2019-08-21 | 2019-08-21 21:14:43 | 21:14:43 |
| 2019 | 2019-08-21 | 2019-08-21 21:14:55 | 21:14:55 |
+------+------------+---------------------+----------+

4.6 enum and set

mysql> create table t5(id int,se enum('man','woman'));
Query OK, 0 rows affected (0.03 sec)
#Insertion values are selected from enum
mysql> insert into t5(id,se) values(1,'man');
Query OK, 1 row affected (0.00 sec)

mysql> create table t6(id int,hobby set('sing','dance','rap'));
Query OK, 0 rows affected (0.04 sec)
#Insert values are optional from set. Note the grammar here.
mysql> insert into t6(id,hobby) values(1,'sing,rap');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t6;
+------+----------+
| id   | hobby    |
+------+----------+
|    1 | sing,rap |
+------+----------+

5. Constraint Settings

Constraints are functions that restrict or add conditions to data stored in columns in addition to data types to ensure data integrity and consistency.

In addition, following the create table statement for creating the product table, the following description is given. primary key (product_id) is used to set primary key constraints for the product_id column. Keys are combinations of columns used when specifying specific data. There are many kinds of keys. Primary keys are columns that can specify a row of data. That is, if the product_id column is designated as the primary key, specific commodity data can be retrieved from that column. Conversely, if duplicate data is entered into the product_id column, the only specific data cannot be fetched (because the only row of data cannot be determined). This allows you to set primary key constraints for a column.

5.1 null and default

Null is the key word for blank (unrecorded). The null is preceded by a negative not, which means that the column is constrained by the fact that it cannot enter a blank space, i.e. the data must be entered (if nothing is entered, it will make a mistake).

Default default values are automatically added when the values are not actively set when data is interpolated.

mysql> create table t1(product_id int not null,pruduct_name char(10) default 'unknow');
Query OK, 0 rows affected (0.06 sec)
mysql> insert into t1(product_id) values(1);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t1;
+------------+--------------+
| product_id | pruduct_name |
+------------+--------------+
|          1 | unknow       |
+------------+--------------+

mysql> create table t2(id int not null default 0);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t2 values();
Query OK, 1 row affected (0.00 sec)

mysql> select * from t2;
+----+
| id |
+----+
|  0 |
+----+

5.2 unique

Unique means that the column is unique and multiple columns can be added. If set separately at the end of the table, the example: unique(id,name) indicates that the combination is unique, and the ID and name can be inserted if they are different.

mysql> create table t3(id int,name char(5),unique(id,name));
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t3(id,name) values(1,'a'),(2,'a');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into t3(id,name) values(1,'a');
ERROR 1062 (23000): Duplicate entry '1-a' for key 'id'

5.3 primary key

There is only one primary key in a table of MySQL. There can be no multiple primary keys, but there can be multiple primary keys.

In a table, you can use a single column as the primary key and multiple columns as the primary key (compound primary key).

Constraints: Equivalent to not null unique, field values are not empty and unique

The storage engine defaults to innodb: For an InnoDB storage engine, a table must have a primary key.

mysql> create table t4(id int ,name char,primary key(id));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t4(id,name) values(1,'a');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t4(id,name) values(1,'b');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into t4(name) values('c');
ERROR 1364 (HY000): Field 'id' doesn't have a default value

5.4 auto_increment

Constrained fields grow automatically, and constrained fields must be constrained by key s at the same time.

mysql> create table t5(id int primary key auto_increment,name char(5));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t5(name) values('a');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t5(name) values('b');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t5;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+
2 rows in set (0.00 sec)

mysql> delete from t5;
Query OK, 2 rows affected (0.00 sec)
#Even if delete d, fields continue to grow, but not if truncate is used.
mysql> insert into t5(name) values('d');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t5;
+----+------+
| id | name |
+----+------+
|  3 | d    |
+----+------+

5.5 foreign key

1. Create associated tables

2. Create association tables

3. Insert data into associated tables

4. Insert data into associated tables

mysql> create table department(seq int primary key auto_increment,name varchar(20) not null);
Query OK, 0 rows affected (0.05 sec)

mysql> create table staff(id int primary key,name char(10),dep_seq int,constraint fk foreign key(dep_seq) references department(seq));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into department(name) values('Production Department'),('Technical Department'),('Sales Department'),('Finance Department');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into staff(id,name,dep_seq) values(1,'Zhao',2),(2,'money',3);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from department;
+-----+-----------+
| seq | name      |
+-----+-----------+
|   1 | Production Department    |
|   2 | Technical Department    |
|   3 | Sales Department    |
|   4 | Finance Department    |
+-----+-----------+
4 rows in set (0.00 sec)

mysql> select * from staff;
+----+------+---------+
| id | name | dep_seq |
+----+------+---------+
|  1 | Zhao   |       2 |
|  2 | money   |       3 |
+----+------+---------+
2 rows in set (0.00 sec)

#If a data in the associated table is associated, the row cannot be deleted
#When creating an association table, add synchronous deletion and update, then delete the records of the association table, and the records in the association table are deleted and updated.
 create table staff(id int primary key,name char(10),dep_seq int,constraint fk foreign key(dep_seq) references department(seq) on delete cascade on update cascade);