Second, development preparation
Before using the lab building Linux environment for this experiment, use the following two commands to open the MySQL service and log in with root:
# 打开 MySQL 服务sudo service mysql start #使用 root 用户登录,密码为空mysql -u root
Subsequent experiments are performed in the MySQL console input command.
Third, experiment step 3.1 new database
First, we create a database, give it a name, for example mysql_shiyan
, the next few experiments are also on the mysql_shiyan
database operation. The statement format is CREATE DATABASE <数据库名字>;
, (note Do not miss ;
the semicolon), the previous CREATE DATABASE can also use lowercase, the specific command is:
CREATE DATABASE mysql_shiyan;
After the creation is successful, enter the command show databases;
(take care not to miss it) and check it out ;
:
In most systems, SQL statements are case-insensitive, so the following statements are valid:
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.
3.2 Connecting the database
The next operation, just created in the mysql_shiyan
first to connect to the database, using the statement use <数据库名字>
:
use mysql_shiyan;
is displayed, the connection succeeds:
Enter a command show tables;
to see a few tables in the current database, which are now mysql_shiyan
empty:
3.3 Data Sheets
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 |
3.4 New data table
The statement format for creating a new table in the database is:
CREATE TABLE 表的名字(列名a 数据类型(数据长度),列名b 数据类型(数据长度),列名c 数据类型(数据长度));
We try to mysql_shiyan
create a new table in employee
, containing name, ID and phone information, so the statement is:
CREATE TABLE employee (id int(10),name char(20),phone int(12));
Then create a table department
that contains the name and phone information to make the command look neater, so you can type in the command:
Then again show tables;
, you can see the two tables you just added:
3.5 Data types
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 |
8 |
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 |
|
Integers in addition to INT, there are TINYINT, SMALLINT, Mediumint, BIGINT.
The difference between char and varchar: the length of char is fixed, and the length of the varchar is changeable, for example, storing the string "abc", for CHAR (10), the stored character will occupy 10 bytes (including 7 null characters), and the same varchar (1 2) only 4 bytes of length, 增加一个额外字节来存储字符串本身的长度
12 is the maximum value, when you store characters less than 12 o'clock, according to the actual length of storage.
The difference between enum and SET: The value of the enum type data 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.
To learn more about MySQL data types, refer to the following two blogs.
- Introduction to data types in MySQL
- MySQL data type
3.6 Inserting data
Just now we have created two tables, using statements to SELECT * FROM employee;
view the contents of the table, you can see that the employee table is still empty:
The SELECT statement that you just used will be described in more detail in the next section of the experiment
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 add Tom, Jack, and Rose to the employee:
insert into employee (id,name,phone" values ( 01, ' Tom ', 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: the (id,name,phone)
parentheses list the data that will be added, (01,‘Tom‘,110110110)
each of which corresponds to the column in the table. The third statement adds only (id,name)
two columns of data, so the phone of rose in the table is null.
Now we use the statement SELECT * FROM employee;
to see the employee table again, and the relevant data for Tom and Jack have been saved in it:
Iv. Summary of the experiment
After this experiment, we already have a database called, mysql_shiyan
with two tables, and we've inserted some data into it. Be sure to complete the operation again, with the use of skilled SQL statements. In the next section we will go into the study of SQL constraints.
MySQL Foundation hardening--Create a database and insert data