MySQL Build and basic commands

I. MySQL build process

Install MySQL on Ubuntu

1, sudo apt-get install Mysql-server

2, sudo apt-get install mysql-client

3, sudo apt-get install Libmysqlclient-dev

II. Basic Command-----login, exit, add a database, add a table, delete a database, delete a table, display a database, display a table, etc.

1. Login and exit

Mysql-h host-u username-p Password

Example:

mysql-h127.0.0.1-uroot-p123456

2. Exit

Quit, or exit.

3. Display the current database

show databases;

4. Choose to use a database:

Use database_name;

Example:

Use test:

5. List all tables in the current database

Show tables;

6. Create a database:

Create databease database_name;

7. Delete a database

DROP DATABASE database_name;

8. Display the tabular data structure:

Describe table_name;

9. Create a table:

To create a table using a CREATE TABLE statement:

The following information must be given:

The name of the new table, given after the CREATE table:

The names and definitions of the table columns are separated by commas.

The actual table definition (all columns) is separated by commas between the columns in parentheses. The table consists of 9 columns, each of which starts with a column name followed by the data type of the column.

The table's primary key can then be specified with the primary key keyword when the table is created. Here, cust_id is specified as the primary key column. The entire statement ends with a semicolon after the right parenthesis.

When you create a table, you can specify that the table column is a null column, or a NOT NULL column, or a null column is a value that is not given to the column when it is inserted, or that the column value must be given when the record is inserted.

The primary key must be unique. If a single column is used, its value must be unique, and if multiple columns are used, the combined values of these values must be unique.

When you create a primary key that consists of multiple columns, each column name should be given a comma-separated list.

Auto_increment: Just tell MySQL that automatically increments every time this column is incremented, and MySQL automatically increments the column each time an insert operation is performed.

Default value, specified using the default keyword.

InnoDB is a reliable transaction processing engine;

MyISAM is an extremely high-performance engine that supports full-text search as the default engine, but does not support transactional processing.

III. Basic command----SELECT command related

1, the basic information display:

2. Display the columns in the table:

Select Column_name_list from table_name;

(1) Query a few columns:

Select Name,sex,birth,birthaddr from MyTable;

(2) Query a column:

Select name from MyTable;

(3) Display a column and sort the column:

SELECT COLUMN_NAME from table_name order BY COLUMN_NAME;

Example:

Select name from MyTable order by name;

The above is sorted by default in ascending order, then descending order is the end of the command plus DESC;

Example select name from MyTable order by name Desc;

(4) Only some rows of a column are displayed:

Select column_name from table_name LIMIT line_total;

Displays a line_total line starting from the first row with the column named column_name:

Example:

Select name from MyTable LIMIT 2;

Displays a column that shows some rows starting from a row in this column, setting the starting line to display, and setting the total number of total rows displayed:

Select name from MyTable LIMIT line_total OFFSET line_index;

That is, starting from the Line_index+1 line, a total of line_total lines are displayed.

As can be seen in the above, starting from line 3rd.

3. Query all data:

SELECT * FROM MyTable;

4. Display a specific line:

SELECT * FROM table_name where colomn_name= "name";

Example:

SELECT * FROM mytable where name= "Tom";

To display a specific line by combining conditions:

SELECT * FROM table_name where column_name1= "value1" and column_name2= "value2";

Example:

SELECT * FROM mytable where sex= "F" and birthaddr= "China";

5. Display the first or last after sorting: Using Order BY and limit:

For example: Show people with the lowest age:

SELECT * FROM MyTable ORDER BY birth desc LIMIT 1;

6. Filter with the search criteria specified in the Select and where sentences:

Select Column_name_list from table_name where column_name= "value";

Example:

Find out what the name is Tom:

SELECT * FROM mytable where name= "Tom";

7. The SELECT statement has a special WHERE clause that can be used to examine the column with a null value, which is the IS NULL clause:

Select COLUMN_NAME1 from table_name where column_name2 is null;

Example:

Select Birthaddr from mytable where name is null;

8. WHERE clause:

Allows multiple where clauses, in the form of an AND clause or an OR clause:

For example:

Pick a record with both name and sex null:

SELECT * FROM mytable where name is null and sex is null;

When multiple and and or operators are used, parentheses are used to differentiate between true logic.

Four, like operator

1, percent semicolon (%) wildcard characters

% indicates the number of occurrences of any character.

For example, pick out people born in October 1987:

SELECT * FROM mytable where birth like ' 1987-10% ';

2. The underscore _ wildcard character

Match a single character

3, delete the left extra space:

RTrim ()

4. Remove the extra space on the right:

LTrim ()

5. Convert Case:

For example: select Name, Upper (name) as Name_upper from MyTable order by name;

Select name, lower (name) as Name_lower from MyTable order by name;

V. SUMMARY of data

1. Aggregation function

There are several examples of this type of retrieval:

Determine the number of rows in a table;

Obtaining the and of the group of rows in the table;

Find the maximum, minimum, or average value of a table column.

For example:

Select count (name) as Total from mytable:

Six, Text Search:

1. Full Text Search

Performs a full-text search using the two function match () and against (), where match () specifies the column being searched, against specifies the search method to use:

Vii. Inserting data

1. Grammatical structure:

INSERT INTO table_name (table info structure) value (value_list);

For example:

Insert INTO mytable (name, sex, birth, birthaddr) value ("Max", "M", "1886-11-06", "German");

2, you can insert more than one data, in the same statement:

Insert INTO MyTable (name, birth, sex, birthaddr) value ("Linda", "2001-09-16", "F", "Russia"), ("Green", "1698-12-08", " M "," Italy ");

Viii. Updating and deleting data

1. Update data

Update specific rows: use where as qualification

Update all rows: Do not use where

The UPDATE statement uses the UPDATE statement: contains three parts:

the table to update;

The column names and their new values;

Determine the filter criteria to update rows;

Update table name set column name = "First Name" where filter condition

Example:

Update mytable set name= "tt" where birthaddr= "America"

2. Delete data

Use the DELETE statement:

To delete a specific row from a table: a qualifying condition is required, i.e. the where statement;

Delete all rows from the table;

Ix. change the structure of a table

To change the table structure by using the ALTER TABLE statement:

ALTER table must be followed by the name of the table you want to change and the list you want to change;

1. Add a column:

Example:

ALTER TABLE mytable ADD height float;

2. Delete a column:

Example:

ALTER TABLE mytable drop column height;

3. Change the table name:

Rename table name to new table name

MySQL Build and basic commands