Is SQL difficult

Data and tables

This series of articles consists of personal reading texts and summary content and no organization or individual is permitted to reproduce it for business purposes!

A typical scenario for understanding the database is the address book, similar to the early phone book. On each page, the contact person's situation is recorded based on the relevant information such as name, telephone and address

However, when you have a large amount of contact information to manage, the phone book seems insufficient. So create a database.


We need to use a database, but before we can create a database, we also need to understand the type of data stored and how the data is classified.


One page of Greg's phonebook note contains a contact:

To look at the data from a classification perspective:

The information is arranged horizontally and looks like an Excel line. This data corresponds to the information that the column column and row row should contain in the table table.


What is a database:

The first SQL structure you need to know first is the database, which contains all of the tables.


The database is a container that contains tables and other related SQL structures.

We can query information in the database. This is the behavior of the personal query.


In the flow chart, the database is represented by a cylinder.


Anatomy of a database:

1. The database is made up of tables (there are some other SQL structures). A database can have several tables.

2. A table is a structure that contains data in a database made up of columns and rows.

3. The row of the table contains all information of an object in the table.


The information in the database is made up of the tables.

To form a database we need: the database name, the names of the tables in the database, the names of the columns in the table and the information recorded in the rows of the table;

It should be noted that the names of databases and tables do not need to be capitalized.


Column: is a data item that is stored in a table.

Row: is a collection of columns that describe something.

Tables: Columns and rows form a table;


Fields are also often used to refer to columns.

Records and lines are often used interchangeably.

A data record contains the values ​​of several fields. Combining columns and rows is the table you want.


For past personal information: Each type of information becomes a category, and each tab in the address book is a record.


Next, we need to put the content we understood above into practice in the database.


Accept the command:

First, we need to start the SQL relational database management system (RDBMS) we have installed. How do we use MySQL?

We need to install before using: see "Simplified SQL (3) - Install MySQL on Mac OS X.》 ;

Read the article above and connect to MySQL using the terminal.


The mssql> SQL command can be entered at the prompt.


Step 1: Create a database for the tables

1) Create a database called grep_list.

2) Use the command: CREATE DATABASE grep_list;

3) Spaces must not be displayed in the names of databases and tables in SQL. So it's best to use underscores instead of spaces to name them.

4) Now you need to tell the RDBMS to use the database you just created.

5) Use the command: USE grep_list; means everything we do next will be done in the grep_list database.


Create the database:


Using the database:


No stupid question here:

1. The SQL language requires that all tables be stored in the database. This allows SQL to control the behavior of multiple users accessing the table at the same time, as well as granting or revoking access to the entire database.

2.SQL itself is not case-sensitive, and commands are not capitalized. However, capitalizing commands is good SQL programming practice.

3. The semicolon (;) at the end of each command indicates the end of the command.

4. Command capitalization and underscores help you write SQL programs, although SQL doesn't need them.


Specifying the table: CREATE TABLE statement

1) Create a table to store 'Donuts' and their types.

2) Type the command in the command line window and press Enter to have the SQL RDBMS run the command.


Table: donuts



Command to create a table:

MySQL introduced backticks (note that they are not single quotes) that are used on either side of the name to distinguish between commands and normal characters.

It can be done without writing, but it's a good habit:

Let's watch this command:

1) Include the name and data type of each column in brackets.

2) A comma is used to distinguish newly added columns.

3) What we need to know here is: VARCHAR is the meaning of variable characters that are used to store information stored in text format.

4) "(6)" means that the length of this text may not exceed 6 characters;


Therefore, when designing the build table, we need to determine the correct data type and length for each column.


Let's take an example of the address book table to see what types of data there are:

Table: Contacts



When creating a table, as in the format above, we can enter one line at a time or write a very long line.

It's worth noting that Last Name VARCHAR (3) and Last Name VARCHAR (30) are two completely different columns.


Voice-over: How can it be indicated whether the specified instruction is successful?

SHOW can also be used to show more information:

We'll continue with data types: we've seen VARCHAR and DATE types before. In fact, other types of data, such as B. Numbers, also other data types.


Meet some other data types:

We highlight some of the most useful types of data whose job it is to store your data without destroying it.

CHAR: Sign, it is very strict, the responsible data must have a preset length;

VARCHAR: Stores text data with a maximum length of 255 characters. They are flexible and can be adapted to the length of our dates.

DEZ: Abbreviation for Decimal, it will provide numeric space until it is full;

DATETIME / TIMESTAMP: responsible for recording the date and time;

INT: integer and can handle negative numbers;

BLOB: Store a large amount of text data.

DATE: Record the date without affecting the time.


We can view the details of the data types supported by MySQL and the specific data types:


Examples of data types:

price 5678.12 DEC (6.2)

The two numbers here represent the floating point number format required by the database, the first represents the total number of digits and the second the number of decimal places;

gender F / M CHAR (1)

Store characters with a preset length of 1;


No stupid question here:

1.CHAR and VARCHAR only occupy a certain space to love you, no more than 256 characters.

BLOB takes up a lot of storage space. As the database grows, there is a risk that disk space will run out as disk space is consumed.

In addition, some important string operations cannot work on BLOB data and can only be used on VARCHAR or CHAR.

2. Choosing the most appropriate data type for each column in the table can make the table thinner and make database operations faster.

3.TIMESTAMP is usually used to record the moment of the "now". DATETIME is better for storing future time.


Let's review what we've done so far:

1. Classify the data before creating the table.

2. Use the CREATE DATABASE statement to create a database that stores all of the tables.

3. Use the USE database statement to enter the database and create a table.

4. All tables are created using the CREATE TABLE statement, which contains the column names and their data types.

5. Some commonly used data types are CHAR, VARCHAR BLOB INT DEC DATE DATETIME. The storage rules for each data type are different.


Check the table you just created: use the DESC command

Recreate existing tables or databases in the database.

Write SQL statements frequently. Good text editing software is better suited. We'll introduce them later.

We can copy the written statement and paste it into the SQL console for execution.


With the act of creating a table, we can also drop the table, and it's easier;

Use the command: DROP TABLE table;

DROP TABLE runs regardless of whether there is any data in the table. Be sure to use these. As soon as the table is deleted, the data in it disappears.

DROP TABLE deletes your table and all of the data in the table.


Delete the table and all of the data in the table:


After deleting, you can also recreate the following:

Since the table has been created, we need to add our address book data to the table.

To add data to the table, you need an INSERT statement.


INSERT statement format:

INSERT INTO table (columns, column1, ...) VALUES (‘values’, 1, ...);

Let's analyze:

1) Start with the INSERT INTO keyword;

2) column names in brackets with column names separated by commas;

3) Another VALUES keyword begins, followed by the value of each column.

4) The list of values ​​is also separated by commas.

5) The entered text data must be put in single quotation marks.

6) The values ​​in the second group of brackets must be in the same order as the column names.


Add data to the table:

Note that the order of the columns and values ​​must be one-to-one.


Further "" "" "":

We first check the current table structure in the database and then continue to open today's learning dataset.


Create a new table to record the donut order record: donut_purchases

SQL statement:

Insert order data record:

SQL statement:

As you can see from the previous two examples:

The value of CHAR VARCHAR DATE BLOB in the INSERT statement must be enclosed in single quotation marks and the value of DEC INT is not required.


Variation of the INSERT statement:

1) You can change the order of the columns: as long as the order of the data values ​​is adjusted together;

2) Omit column names: INSERT INTO table VALUES (’values’, ...);

The column names can be omitted, but the data values ​​must be completely filled in and exactly match the column order in which the table was originally created

3) Omit some columns: you must clearly indicate the column name that corresponds to the data value.


Add records with incomplete values ​​to the my_contacts table:

Spread the table with the SELECT statement:

DESC can only display the structure of the table. We need a SELECT statement for the data in the table.

Use select * to display all of the data in the table:

1) The command SELECT * FROM table;

2) * means to select all content;

We see: NULL is displayed in the column to which no value has been assigned.


NULL interpretation:

1) NULL is neither equal to 0 nor to any other NULL. In fact, two NULLs cannot be compared with each other at all.

2) The value can be NULL, but it is not equal to NULL because NULL is an undefined value.

3) When creating the table, specify the columns that will not display NULL and that will not display.

You do not need to add NOT NULL until you have created the data type of the table.


Fill in the gap with DEFAULT:

1) If some columns usually have a certain value, we can assign the certain value to DEFAULT as the default value.

2) The value after the DEFAULT keyword is automatically inserted into the table each time a new data record is added, provided no other value is assigned.

3) Note that the type of the default value must be the same as the type.


Create a new donut_list table, do not set null and default settings;

This table already exists before. Drop the donut_list table.

Next, you can insert several pieces of data for testing:


This chapter explains how to create databases and tables and put the most common types of data in a table while making sure that the columns that require values ​​contain values.

1.CREATE DATABASE database; Specify the database for loading tables.

2.USE database; enter the database;

3. CREATE TABLE table (column names, ...), column names and data types of the columns being obtained by analysis;

4. NULL and NOT NULL: Knowing which columns should not be NULL is a great way to find and organize data.

5.DEFAULT is used to specify the default value of a column when a record is entered but no value is assigned to a column.

6. Use DESC statements to view the table structure.

The 7.DROP TABLE statement is used to drop the table.

8. When inserting data into the table, you can use any INSERT statement.

9. NULL is an undefined value that is not equal to zero or equal to a null value. The value can be NULL, but it is never equal to NULL.

10. You can change the column so that it does not accept NULL values. Use the NOT NULL keyword when creating the table.

11. Use DEFAULT when creating a table. You can automatically enter default values ​​when entering records that are missing some data in the future.