MySQL Foundation hardening--Create a database and insert data

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