MySQL Basic use

Install MySQL

Installing MySQL on Ubuntu is very simple and requires just a few commands to complete.

sudo apt-get install mysql-serversudo apt-get isntall mysql-clientsudo apt-get install libmysqlclient-dev

During installation, you will be prompted to set a password or something, note that the settings do not forget that after the installation is complete, you can use the following command to check if the installation is successful:
sudo netstat -tap | grep mysql
After checking with the above command, if you see a socket with MySQL in the Listen state, the installation is successful.
Log in to MySQL database by the following command:
mysql -u root -p
-U means to select the login user name,-p indicates the login user password, the above command input will prompt for a password, then enter the password can log in to MySQL.

Mysql

MySQL is the most widely used relational database. A relational database uses a relational model as a way to store data, which can be divided into four levels of structure:

• database, such as an application corresponding to a database;
• Table, a database contains several tables;
• Records, a table contains multiple records;
• field, a record contains several fields;

The structure of a table can be imagined as an Excel table with multiple fields, each of which can store a specific type of data, such as a string or a number, and you can specify the type when creating the table. A relational connection (a logical relationship) between a table and a table can be queried by a relationship across multiple tables when querying data.

The most important feature of a relational database is to satisfy the ACID properties:

• A atomicity represents atomicity;
• C consistency represents consistency;
• I isolation represents isolation;
• D durability stands for permanence;

ACID can guarantee the reliability of the transaction, what does it mean? This means that a complete logical process consisting of a series of database operations can be guaranteed, either fully executed or completely non-executed, with no execution of half the case. For example, bank transfer, deduction of the amount from the original account, and the addition of the amount to the target account, the sum of the two database operations, constitutes a complete logical process that cannot be split.

To satisfy the ACID nature, MySQL supports a variety of constraints, such as inserting a piece of data, you need to check the presence of foreign keys, which can ensure consistency of data, but often reduce the ability of concurrent operations, so in today's Internet site, If there are high concurrency requirements often no longer use the ACID nature of relational databases, the more direct use of non-relational databases.

In addition, here are some basic conceptual knowledge of relational keys in relational databases to help understand the following.

• Primary KEY (English: Primary key). A combination of data columns or properties in a database table that uniquely and fully identifies the stored data object. A data column can have only one primary key, and the value of the primary key cannot be missing, that is, it cannot be a null value (NULL).
• Foreign KEY (English: foreign key). In fact, in a relational database, each data table is connected to each other by a relationship, and the parent key (primary key) is placed in another data table as a property to create relationships with each other, and this property is a foreign key.

For example, students and teachers correspond to the teaching relationship, the student data sheet will have a property called the instructor (foreign key), and this value is corresponding to the teacher's data sheet of the teacher code (primary key).

MySQL Basic operation

Start/Stop MySQL

sudo service mysql startsudo service mysql stop

Creation and deletion of databases

Creating a database is simple, just enter create DB <db_name>; On it, where <db_name> represents the database name, view all the databases can be through show databases; (Note that there is an s) command here, and deleting a database can be done by dropping the databases <db_name>; ，

• Create

mysql> create database news;Query OK, 1 row affected (0.00 sec)mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || news               || performance_schema || sys                |+--------------------+5 rows in set (0.00 sec)

• Delete

mysql> drop database news;Query OK, 0 rows affected (0.00 sec)mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                |+--------------------+4 rows in set (0.00 sec)

Basic operation of the table

1. Table creation and deletion

Once the database has been created successfully, you can create the table. Before you create a table, you need to pass the use Shiyanlou; command to connect to the Shiyanlou database.

The basic commands for creating a table are as follows:

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

Data Type	size (bytes)	Use	format
FLOAT	4	Single-precision floating-point number
DOUBLE	8	Double-precision floating-point number
Int	4	Integer
SET	4	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
Enum		Radio, such as gender	ENUM (' A ', ' B ', ' C ')

Here's an attempt to create a table with a table named user with 3 fields:

• ID number, integer type, using int type;
• Name User name, string, using varchar variable character type;
• Email mailbox, string, using varchar variable character type;

mysql> use pyclass;Database changedmysql> create table user(    -> id int(10),    -> name varchar(20),    -> email varchar(64)    -> );Query OK, 0 rows affected (0.47 sec)mysql> show tables;+-------------------+| Tables_in_pyclass |+-------------------+| user              |+-------------------+1 row in set (0.00 sec)

The user table can be created with the above command, and it is important to note that when you enter the CREATE table user carriage return, the client automatically recognizes that this is an unfinished command, so a prompt appears. After the table is created successfully, it can be tables by show; To view all the tables, delete the tables by dropping table <table_name>;.

If you want to view field information for a table, you can use show create TABLE <table_name>; or describe <table_name>; directive, as follows:

Mysql> Show CREATE TABLE user;+-------+------------------------------------------------------------------------ -----------------------------------------------------------------------------------------+| Table |                                                                                                                                                    Create Table |+-------+-------------------------------------------------------------------------- ---------------------------------------------------------------------------------------+| user | CREATE TABLE ' user ' (' id ' int ') default null, ' name ' varchar () default NULL, ' email ' varchar (+) default null) ENG Ine=innodb DEFAULT charset=latin1 |+-------+--------------------------------------------------------------------- --------------------------------------------------------------------------------------------+1 Row in Set (0.01 sec ) mysql> describe user;+-------+-------------+------+-----+---------+-------+| Field | Type       | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| ID | Int (10) |     YES | |       NULL | || name | varchar (20) |     YES | |       NULL | || email | VARCHAR (64) |     YES | |       NULL | |+-------+-------------+------+-----+---------+-------+3 rows in Set (0.00 sec)

2. Inserting data

After the table is created successfully, you can insert the data. You can insert data using the Insert Directive, the complete command format is as follows:

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

When you insert a value that is consistent with the number of fields defined by the table and in the same order, you can omit the column name information and insert the data into the user table with the following command:

mysql> insert into user(id,name,email) values(1,‘mmm‘,‘[email protected]‘);Query OK, 1 row affected (0.46 sec)mysql> insert into user values(2,‘zzz‘,‘[email protected]‘);Query OK, 1 row affected (0.01 sec)

To view the inserted data:

mysql> select * from user;+------+------+------------+| id   | name | email      |+------+------+------------+|    1 | mmm  | [email protected] ||    2 | zzz  | [email protected] |+------+------+------------+2 rows in set (0.00 sec)

3. Modify Table Name

There are several forms of renaming a table, and the following 3 formatting effects are the same:

RENAME TABLE 原名 TO 新名字;ALTER TABLE 原名 RENAME 新名;ALTER TABLE 原名 RENAME TO 新名;

Use the command to try to modify the user's name to User1:

mysql> rename table user to user1;Query OK, 0 rows affected (0.44 sec)mysql> show tables;+-------------------+| Tables_in_pyclass |+-------------------+| user1             |+-------------------+1 row in set (0.00 sec)mysql> alter table user1 rename user;Query OK, 0 rows affected (0.01 sec)mysql> show tables;+-------------------+| Tables_in_pyclass |+-------------------+| user              |+-------------------+1 row in set (0.00 sec)

4. Modify Table Structure

The statement format for adding a column to the table is:

ALTER TABLE 表名字 ADD COLUMN 列名字 数据类型 约束; ALTER TABLE 表名字 ADD 列名字 数据类型 约束;

Increase the Age field, the default value of 18:

mysql> alter table user add age int(4) default 18;Query OK, 0 rows affected (0.56 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> select * from user;+------+------+------------+------+| id   | name | email      | age  |+------+------+------------+------+|    1 | mmm  | [email protected] |   18 ||    2 | zzz  | [email protected] |   18 |+------+------+------------+------+2 rows in set (0.00 sec)

You can see that the newly added columns are placed by default at the far right of the table. If you want to insert the added column at the specified location, you need to use the After keyword at the end of the statement ("after column 1" means that the new column is placed after "column 1").

For example, we add a new column of weight (weight) placed behind name:

mysql> alter table user add weight int(4) default 120 after name;Query OK, 0 rows affected (0.60 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> select * from user;+------+------+--------+------------+------+| id   | name | weight | email      | age  |+------+------+--------+------------+------+|    1 | mmm  |    120 | [email protected] |   18 ||    2 | zzz  |    120 | [email protected] |   18 |+------+------+--------+------------+------+2 rows in set (0.00 sec)

The effect above is to add the new column to the back of a position, if you want to place in the first column, then use the primary keyword, such as statement:

ALTER TABLE employee ADD test INT(10) DEFAULT 11 FIRST;

The deletion of a column in a table is similar to the one for the new column you just used, except that the keyword ADD is changed to DROP and no data type, constraint, or location information is required after the statement. Specific Statement format:

ALTER TABLE 表名字 DROP COLUMN 列名字;

Or: ALTER table table name DROP column name;
We removed the new Age of age:

mysql> alter table user drop age;Query OK, 0 rows affected (0.52 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> select * from user;+------+------+--------+------------+| id   | name | weight | email      |+------+------+--------+------------+|    1 | mmm  |    120 | [email protected] ||    2 | zzz  |    120 | [email protected] |+------+------+--------+------------+2 rows in set (0.00 sec)

Rename a column, this statement can not only be used to rename a column, to be precise, it is to modify a column (change):
ALTER TABLE 表名字 CHANGE 原列名 新列名 数据类型 约束;
Note: the "data type" After this rename statement cannot be omitted, otherwise renaming fails.

When the original column name and the new column name are the same, specifying a new data type or constraint can be used to modify the data type or constraint. It is important to note that modifying the data type may result in data loss, so use it sparingly.

We use this statement to rename the "Weight" column "Tizhong", the effect is as follows:

mysql> alter table user change weight tizhong int(4) default 125;Query OK, 0 rows affected (0.47 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> select * from user;+------+------+---------+------------+| id   | name | tizhong | email      |+------+------+---------+------------+|    1 | mmm  |     120 | [email protected] ||    2 | zzz  |     120 | [email protected] |+------+------+---------+------------+2 rows in set (0.00 sec)

To modify the data type of a column, you can use the Modify statement in addition to the change statement that you just made:
ALTER TABLE 表名字 MODIFY 列名字 新数据类型;
Again, you must be careful about modifying data types, as this can result in data loss. Please consider carefully before attempting to modify the data type.

Most of the time we need to make changes not the entire database or the entire table, but one or more of the data in the table, which requires us to use this command to achieve precise modification:
UPDATE 表名字 SET 列1=值1,列2=值2 WHERE 条件;
For example, we want to change the tizhong of zzz to 125,email [email protected]:

mysql> update user set tizhong=125,email=‘[email protected]‘ where name = ‘zzz‘;Query OK, 1 row affected (0.43 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from user;+------+------+---------+-------------+| id   | name | tizhong | email       |+------+------+---------+-------------+|    1 | mmm  |     120 | [email protected]  ||    2 | zzz  |     125 | [email protected] |+------+------+---------+-------------+2 rows in set (0.00 sec)

Deleting a row of data in a table must also be accompanied by a where condition, otherwise the entire column of data will be deleted. Delete statement:
DELETE FROM 表名字 WHERE 条件;

Constraints

Constraint classification
Listen to the name. Constraint is a restriction that ensures the integrity and uniqueness of the table's data by restricting the data of the table's rows or columns.

In MySQL, there are usually these kinds of constraints:

Constraint Type	PRIMARY Key	Default Value	only	FOREIGN Key	Non-empty
Key words	PRIMARY KEY	DEFAULT	UNIQUE	FOREIGN KEY	Not NULL

eg

  CREATE DATABASE mysql_shiyan;use Mysql_shiyan; CREATE TABLE Department (dpt_name CHAR) not NULL, People_num INT (Ten) DEFAULT ' Ten ', CONSTRAINT dpt_pk PRIMARY KEY ( Dpt_name)); CREATE TABLE employee (id int () PRIMARY KEY, name CHAR, age INT (Ten), salary int (ten) is not NULL, Phon e INT (a) not NULL, IN_DPT CHAR (a) NOT NULL, UNIQUE (phone), CONSTRAINT emp_fk FOREIGN KEY (IN_DPT) REFERENCES DEP Artment (Dpt_name));   CREATE TABLE Project (Proj_num INT) is not NULL, Proj_name CHAR (a) is not NULL, start_date date is not NULL, end_date DATE DEFAULT ' 2015-04-01 ', OF_DPT CHAR (REFERENCES Department (dpt_name), CONSTRAINT proj_pk PRIMARY KEY (proj_num , Proj_name));  

• Primary key
  The primary key (PRIMARY key) is used to constrain a row in the table, and as a unique identifier for the row, the primary key is the key to the exact row in one table. The primary key cannot be duplicated and cannot be empty.
  To define a primary key:
  
  You can also define this:
  
  There is also a special primary key-the composite primary key. A primary key can be a column in a table, or it can be identified by two or more columns in a table, such as:
  

• Default value Constraint
  The default value constraint, default, specifies that when a column with a default constraint is blank, the Insert data is empty, and the defaults are used.
  people_num INT(10) DEFAULT ‘10‘The default value is 10
  The default constraint is only reflected when using the INSERT statement (as described in the previous experiment), where the default constraint is populated with the value of the default, such as a statement:

  INSERT INTO department(dpt_name,people_num) VALUES(‘dpt1‘,11);# 正常插入数据INSERT INTO department(dpt_name) VALUES(‘dpt2‘);#插入新的数据，people_num 为空，使用默认值

• Unique constraint
  A unique constraint is simple, which specifies that the value of a column specified in a table must not have duplicate values, that is, each value in this column is unique.
  
  Insert fails if there is a unique constraint when the newly inserted data and the existing data are duplicated by the INSERT statement, for example:

INSERT INTO employee VALUES(01,‘Tom‘,25,3000,110110,‘dpt1‘);INSERT INTO employee VALUES(02,‘Jack‘,30,3500,110110,‘dpt2‘);

• FOREIGN KEY constraints
  The foreign key (FOREIGN key) ensures both data integrity and the relationship between tables.
  A table can have multiple foreign keys, and each foreign key must REFERENCES (reference) The primary key of another table, the column that is constrained by the foreign key, and the value must have a corresponding value in the column it references.
  
  Insert fails if the value of the foreign KEY constraint does not correspond in the Reference column at insert

• Non-null constraint
  A non-null constraint (NOT NULL), which can be understood by a name, is a non-null constrained column that must not be empty when inserting a value.
  
  Violation of non-null constraint in MySQL, no error, only warning

MySQL Basic use