Mysql Common Commands

First, Mysql CREATE database　

Mysql> CREATE DATABASE study CharSet UTF8; Query OK, 1 row affected (0.01 sec)

Second, create a data table

Grammar:

CREATE TABLE table_name (column_name column_type);

Create a student table

mysql> CREATE TABLE student (stu_id int not NULL auto_increment, , name char (+) is not NULL, and age int is not NULL, and register_date date not NULL, and Primar Y key (stu_id); Query OK, 0 rows affected (0.02 sec) mysql> desc student;+---------------+----------+------+-----+---------+-------- --------+| Field | Type | Null | Key | Default | Extra |+---------------+----------+------+-----+---------+----------------+| stu_id | Int (11) | NO | PRI | NULL | auto_increment | | name | char (32) |     NO | |                NULL | || Age | Int (11) |     NO | |                NULL | || Register_date | Date |     NO | |                NULL | |+---------------+----------+------+-----+---------+----------------+4 rows in Set (0.01 sec) 

• If you do not want the fields to be null , you can set the field's property to not null, and you will get an error if the data entered in the field is null when you manipulate the database.
• Auto_increment defines a property that is self-increasing, typically used for a primary key, and the value is automatically added to 1.
• The PRIMARY key keyword is used to define the column as the primary key. You can use multiple columns to define a primary key, and the columns are separated by commas.

Third, data operation

　　3.1 Inserting data

Grammar:

INSERT into table_name (field1, Field2,... fieldn)                       VALUES                       (value1, value2,... Valuen);

Insert data:

mysql> INSERT into student (name,age,register_date) VALUES (' Bigberg ', 11, ' 2018-01-01 '); Query OK, 1 row affected (0.01 sec) mysql> select * from student;+--------+---------+-----+---------------+| stu_id | Name    | age | register_date |+--------+---------+-----+---------------+|      1 | Bigberg |  11 | 2018-01-01    |+--------+---------+-----+---------------+1 row in Set (0.00 sec)

　　3.2 Querying data

Grammar:

SELECT column_name,column_namefrom table_name[where clause][offset M][limit N]

• In a query statement you can use one or more tables, separate the tables with commas (,), and use the where statement to set the query criteria.
• The SELECT command can read one or more records.
• You can use the asterisk (*) instead of the other fields, and the SELECT statement returns all the field data for the table
• You can use the WHERE statement to include any condition.
• You can specify the data offset for the SELECT statement start query by using offset. By default, the offset is 0 and cannot be used alone if used.
• You can use the LIMIT property to set the number of records returned.

SELECT * FROM Student;select * from student where stu_id > 2;select * from student where register_date like ' 2018-02-% ' ;

Mysql> SELECT * FROM student limit 3, 1;+--------+--------+-----+---------------+| stu_id | Name   | age | register_date |+--------+--------+-----+---------------+|      4 | Wangwu |  23 | 2018-02-14    |+--------+--------+-----+---------------+1 row in Set (0.00 sec) # limit is followed by reading from 3rd, reading 1 messages.

Mysql> SELECT * FROM student limit 3 offset 2;+--------+--------+-----+---------------+| stu_id | Name   | age | register_date |+--------+--------+-----+---------------+|      3 | Lisi   |  31 | 2018-02-11    | |      4 | Wangwu |  23 | 2018-02-14    |+--------+--------+-----+---------------+2 rows in Set (0.00 sec) # limit followed by 3 data, offset after is read from 3rd

　　3.3 Where sentence

Grammar:

SELECT field1, Field2,... fieldn from table_name1, table_name2 ... [WHERE condition1 [and [OR]] condition2 .....

operator	Description	Example
=	Equals, detects if two values are equal, returns true if equal	(A = B) returns FALSE.
<>! =	does not equal, detects whether two values are equal if not equal returns True	(A! = B) returns TRUE.
>	Greater than sign, detects if the left value is greater than the right value, and returns True if the left value is greater than the right value	(A > B) returns FALSE.
<	Less than sign, detects if the left value is less than the right value, and returns True if the left value is less than the right value	(A < B) returns TRUE.
>=	Greater than equals sign, detects if the left value is greater than or equal to the right value, if the left value is greater than or equal to the right value returns True	(A >= B) returns false.
<=	Less than equals sign, detects if the left value is less than or equal to the right value, if the left value is less than or equal to the right value returns True	(A <= B) returns True.

A conditional query that uses a primary key as a WHERE clause is very fast.

SELECT * FROM student where register_date > ' 2016-03-04 ';

　　3.4 UPDATE Query

Grammar:

UPDATE table_name SET field1=new-value1, Field2=new-value2[where Clause]

Update data:

Mysql> Update student Set age=22 where stu_id = 1; Query OK, 1 row affected (0.01 sec) Rows matched:1  changed:1  warnings:0

　　3.5 DELETE Statement

　　Grammar:

DELETE from table_name [WHERE Clause]

Delete statement:

mysql> Delete from student where stu_id = 4; Query OK, 1 row Affected (0.00 sec)

　　3.6 Order BY sort

Grammar:

SELECT field1, Field2,... fieldn table_name1, table_name2 ... ORDER by field1, [field2 ...] [ASC [DESC]]

Sort statements:

# Use the ASC or DESC keyword to set the query result to be sorted in ascending or descending order. By default, it is sorted in ascending order. Mysql> select name, age, register_date from student order by age desc;+---------+-----+---------------+| Name    | age | register_date |+---------+-----+---------------+| Lisi    |  31 | 2018-02-11    | | bigberg |  22 | 2018-01-01    | | zhansan |  21 | 2018-01-11    |+---------+-----+---------------+3 rows in Set (0.00 sec)

Mysql> select name, age, register_date from student order by age;+---------+-----+---------------+| Name    | age | register_date |+---------+-----+---------------+| Zhansan |  21 | 2018-01-11    | | bigberg |  22 | 2018-01-10    | | Wangwu  |  22 | 2018-01-02    | | Lisi    |  31 | 2018-02-11    |+---------+-----+---------------+4 rows in Set (0.00 sec) mysql> Select name, age, register_date from Student order by age, register_date;+---------+-----+---------------+| Name    | age | register_date |+---------+-----+---------------+| Zhansan |  21 | 2018-01-11    | | Wangwu  |  22 | 2018-01-02    | | bigberg |  22 | 2018-01-10    | | Lisi    |  31 | 2018-02-11    |+---------+-----+---------------+4 rows in Set (0.00 sec)

　　3.7 Group BY group

Grammar:

SELECT column_name, Function (column_name) from Table_namewhere column_name operator Valuegroup by column_name;

GROUP BY statement

Mysql> Select Age,count (*) as number from student GROUP by age;+-----+--------+| Age | Number |+-----+--------+|  |      1 | |  |      2 | |  |      1 |+-----+--------+3 rows in Set (0.00 sec)

　　3.8 Alter command

Grammar:

# When we need to modify the data table name or modify the fields of the data tables, we need to use the ALTER TABLE student drop register_date to MySQL alter command; #从student表删除register_date   field ALTER TABLE student add phone int (one) not null; # Add Phone field

　　Adding and Removing fields

# Add a Gender field mysql> ALTER TABLE student add gender enum (' M ', ' F ') not NULL; Query OK, 0 rows affected (0.06 sec) records:0  duplicates:0  warnings:0# Delete the age field mysql> ALTER TABLE student DRO P age; Query OK, 0 rows affected (0.04 sec) records:0  duplicates:0  warnings:0mysql> select * from student;+--------+ ---------+---------------+--------+| stu_id | Name    | register_date | gender |+--------+---------+---------------+--------+|      1 | Bigberg | 2018-01-10    | M      | |      2 | Zhansan | 2018-01-11    | M      | |      3 | Lisi    | 2018-02-11    | M      | |      5 | Wangwu  | 2018-01-02    | M      | |      6 | Lily    | 2018-02-23    | F      |+--------+---------+---------------+--------+5 rows in Set (0.00 sec)

　　modifying field types

Mysql> DESC student;+---------------+---------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+---------------+---------------+------+-----+---------+----------------+| stu_id | Int (11) | NO | PRI | NULL | auto_increment | | name | char (32) |     NO | |                NULL | || Register_date | Date |     NO | |                NULL | || Gender | Enum (' M ', ' F ') |     NO | |                NULL | |+---------------+---------------+------+-----+---------+----------------+4 rows in Set (0.00 sec) mysql> Alter Table Student Modify name varchar (50); Query OK, 5 rows affected (0.11 sec) records:5 duplicates:0 warnings:0mysql> desc student;+---------------+-------- -------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+---------------+---------------+------+-----+---------+----------------+| stu_id | Int (11) | NO | PRI | NULL | auto_increment | | name | varchar (50) |     YES | |                NULL | || Register_date | Date |     NO | |                NULL | || Gender | Enum (' M ', ' F ') |     NO | |                NULL | |+---------------+---------------+------+-----+---------+----------------+4 rows in Set (0.00 sec)

　　Modify Table Name

mysql> ALTER TABLE student rename to student_table; Query OK, 0 rows affected (0.01 sec) mysql> Show tables;+-----------------+| Tables_in_study |+-----------------+| Student_table   |+-----------------+1 row in Set (0.00 sec)

　　

Mysql Common Commands