(Big Data Engineer Learning path) Fourth Step SQL Foundation course----Create a database and insert data

First, practice content 1, create a new database

First, we create a database, give it a name, such as "Mysql_shiyan", the next few experiments also on the Mysql_shiyan this database operation. The statement format is "CREATE Database < DB name >;", (be careful not to miss out ";") So the specific commands are:

CREATE DATABASE mysql_shiyan;

After creation, enter the command "show databases;" (Be careful not to miss out ";" ) Check:

In most systems, SQL statements are case insensitive, so the following statements are equivalent:

CREATE DATABASE name1;create database name2;CREATE database name3;create DAtabaSE name4;

But out of rigor, and easy to differentiate between reserved words ( reserved word): Refers to words that have already been defined in a high-level language, and the user can no longer use them as variable names or procedure names. and variable names, we capitalize the reserved words, and the variables and data are lowercase.

2. Connect to the database

The next action, just in the Mysql_shiyan you just created, is to connect to the database, using the statement "Use < database name >":

use mysql_shiyan

is displayed, the connection succeeds:

Enter the command "Show tables;" You can see a few tables in the current database, and now the Mysql_shiyan is empty:

3. Data Sheet

Data table (table), which is one of the most important components of a database. The database is just a framework, and the table is the real content. A database usually has more than one table, these separate tables through the establishment of relationships are connected to become a cross-reference, at a glance of the database. Here is a table:

ID	name	Phone
01	Tom	110110110
02	Jack	119119119
03	Rose	114114114

4. New data table

The statement format for creating a new table in the database is:

CREATE TABLE 表的名字(列名a 数据类型(数据长度),列名b 数据类型(数据长度)，列名c 数据类型(数据长度));

We tried to create a table employee in Mysql_shiyan, so the statement was:

CREATE TABLE employee (id int(10),name char(20),phone int(12));

To make the command look neater, you can enter a command like this:

At this time again "show tables;" , you can see the two tables you just added:

5. Data type

In the process of creating a new table, we mentioned data types, MySQL data types are similar to other programming languages, and the following table is some common MySQL data types:

Data Type	size (bytes)	Use	format
Int	4	Integer
FLOAT	4	Single-precision floating-point number
DOUBLE	4	Double-precision floating-point number
Enum		Radio, such as gender	ENUM (' A ', ' B ', ' C ')
SET		Multi-Select	SET (' 1 ', ' 2 ', ' 3 ')
DATE	3	Date	Yyyy-mm-dd
Time	3	Point-in-time or duration	HH:MM:SS
Year	1	Year value	YYYY
CHAR	0~255	Fixed length string
VARCHAR	0~255	Variable length string
TEXT	0~65535	Long Text data

the difference between char and varchar: The length of the char is fixed, and the length of the varchar is changeable, for example, storing the string "abc", for Char (10), the stored character will be 10 bytes (including 7 null characters), while the same varchar (12) takes only 3 bytes of length, 12 is the maximum value, which is stored as the actual length when the character you store is less than 12 o'clock.

the difference between enum and set: The value of the data of the enum type must be one of the values enumerated at the time of the definition, that is, a single selection, and the value of the set type can be multiple.

6. Inserting data

We have just created two new tables, using the statement "SELECT * from Employee;" You can see the employee table, which is now empty:

The SELECT statement that you just used will be described in detail in the next lesson

We insert the data into the table with the INSERT statement in the following format:

INSERT INTO 表的名字(列名a,列名b,列名c) VALUES(值1,值2,值3);

We tried to join Tom, Jack and Rose:

 insert  into employee (id,name,phone) values (01, 110110110); insert INTO Employee values (02, ' Jack ', 119119119); insert INTO Employee (Id,name) Span class= "Hljs-keyword" >values (03, ' Rose ');     

You've noticed that some data needs to be enclosed in single quotes, such as the names of Tom, Jack, and Rose, because their data types are char type. In addition, Varchar,text,date,time,enum and other types of data also require single-quote decoration, while int,float,double and so on are not required.

The first statement is a bit more than the second: (Id,name,phone) This parenthesis is the data to be added ("Tom", 110110110) where each value corresponds to the column in the table. The third statement adds only two columns of data (id,name), so the phone of rose in the table is null.

Now let's use the statement again "SELECT * from Employee;" Looking at the employee table, you can see that the relevant data for Tom and Jack has been saved:

Third, summary

After this exercise, we already have a database called Mysql_shiyan, which has two tables, and we've inserted some data into it. After a complete operation, the basic use of SQL statements is familiar.

(Big Data Engineer Learning path) Fourth Step SQL Foundation course----Create a database and insert data