MySQL notes (1)

Database and SQL Concepts

A database ( Database ) is a warehouse that organizes, stores, and manages data in accordance with its structure, which has been generated more than 60 years ago. With the development of information technology and market, the database becomes ubiquitous: it is widely used in many fields such as e-commerce and banking system, and becomes an important part of its system.

Database used to record data, the use of database record data can show the relationship between various data, but also can easily be recorded data to increase, delete, change, check and other operations.

The Structured Query Language ( Structured Query Language ), referred to as SQL, was developed by IBM in the 70 and used to manipulate databases. In more detail, SQL is a database query and programming language for accessing data and querying, updating, and managing relational database systems, as well as the extension of database script files.

MySQL Introduction

MySQL is a DBMS (database management System), developed by the Swedish Mysqlab company, currently belongs to the Oracle company, MySQL is the most popular relational database management system (relational database, is built on the basis of relational database model database, Using concepts and methods such as set algebra to process data in a database. Because of its small size, fast speed, low total cost of ownership, especially the open source of this feature, the general small and medium-sized web site developers have chosen MySQL as the site database. MySQL operates using the SQL language.

MySQL Installation

The following is the process of installing MySQL under Linux, under Windows I use the Wamp integration environment

1 Pre-installation checks

To check if MySQL is already installed on your Linux system, enter the command to try to open the MySQL service:

1 sudo service MySQL start

When you enter a password, the following prompt indicates that MySQL is already installed in the system:

If this is the case, then there is no MySQL in the system and you need to continue with the installation:

1 mysql:unrecognized Service

2 Ubuntu Linux Installation configuration MySQL

The simplest way to install MySQL on Ubuntu is to install it online. Only a few lines of simple commands ( # followed by comments) are required:

1 #安装 MySQL Server, core program 2 sudo apt-get install mysql-server34#安装 mysql client 5 sudo apt-get Install Mysql-client

During installation, you will be prompted to confirm the input yes, set the root user password (which can then be modified), etc., and wait a few moments for the installation to succeed.

After the installation is complete, use the command to verify that it is installed and started successfully:

1 sudo netstat-tap | grep MySQL

If the following prompt appears, the installation succeeds:

At this point, you can modify the MySQL configuration file (my.cnf) with gedit according to your own needs, using the following command:

1 sudo gedit/etc/mysql/my.cnf

Try MySQL

1). Open MySQL

Use the following two commands to open the MySQL service and log in with the root user:

1 # Start the MySQL service 2 sudo service MySQL start              3 4 # Log in with the root user, the lab building environment password is empty, the direct return can log in 5 Mysql-u Root

The following prompt will appear for successful execution:

2). View the database

Use show databases; the commands to see which databases are available (be careful not to omit the semicolon ; ):

There are already three databases, namely "Information-schema", "MySQL", "Performance-schema".

3). Connect to the database

Choose to connect one of the databases, the statement format is use <数据库名> , here can not add a semicolon, here we select the information_schema database:

1 Use Information_schema

4). View Table

Use the commands to show tables; see which tables are in the database (be careful not to miss out ";") ）：

5). Exit

quit 、 exit quit MySQL using commands or shortcut keys (Ctrl + C).

At this point, MySQL has been installed, configured to complete, can be used normally.

Create a database and insert data The following are new databases, new tables, inserting data, and knowledge about basic data types.

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:

1 New Database

First, create a database, give it a name, for example test .

The statement format is CREATE DATABASE <数据库名字>; , (note Do not miss ; the semicolon), the previous CREATE DATABASE can also use lowercase, the specific command is:

1 CREATE DATABASE test;

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:

1 CREATE DATABASE name1; 2 CREATE database name2; 3 CREATE database Name3; 4 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 word, and the variable and the data are lowercase.

2 Connecting the database

The next operation, just created in the test first to connect to the database, using the statement use <数据库名字> :

1 Use test

is displayed, the connection succeeds:

Enter a command show tables; to see a few tables in the current database, which are now test empty:

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

4 New data table

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

1 name of the CREATE table table 2 (3 column name a data type (data length),4 column name B data type (data length),5 column name C data type (data length) 6 );

testCreate a new table in, people containing name, ID, and phone information, so the statement is:

1 int (tenchar(N));

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:

5 Data types

In the process of creating a new table, data types are used, 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 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 4 bytes of length, ,12 is only the maximum value, and when you store characters less than 12 o'clock, the actual length is stored.

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

Just now we have created two tables, using statements to SELECT * FROM people; view the contents of the table, you can see that the people table is still empty:

Insert the data into the table with the INSERT statement in the following format:

1 INSERT into table name (column name A, column name B, column name C) VALUES (value 1, value 2, value 3);

Add Tom, Jack, and Rose to the people:

 1  INSERT into people (Id,name,phone) VALUES ( 01 ,   , 110110110  ); 2  3  INSERT into people VALUES (02 ,  jack   ",  ); 4  5  INSERT into people (Id,name) VALUES ( Span style= "color: #800080;" >03 ,  rose  ); 

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:

MySQL notes (1)