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