View the MSYQL version

 mysql --version
 ​
 mysql -V
Copy the code

Connect the MySql

 -uroot -proot
Copy the code

-u, -p, user name, and password cannot contain Spaces. Note this problem when writing configuration files

Secure login

Mysql -uroot -p mysql -uroot -pCopy the code

Creating a database

 create database be;
Copy the code

End SQL statement

Multiple SQL statements must start with a semicolon (;). Space.

MySQL, like most DBMSS, does not require a semicolon after a single SQL statement.

Of course, you can always add a semicolon if you want. In fact, it certainly doesn’t hurt to add a semicolon, even if it’s not necessary.

If you are using the mysql command line, you must add a semicolon to end the SQL statement.

Select database

 use be
Copy the code

Import data

 source  D:\ bjpowernode.sql
Copy the code

Deleting a Database

 drop database be;
Copy the code

Query the database currently in use

 select  database();
 ​
Copy the code

Terminate a statement

 \c
Copy the code

Exit the mysql

 exit
Copy the code

Checking command execution (mysql)

Preceded the command with explain

Viewing an existing database

 show databases;
Copy the code

Remember to add a semicolon

View the tables in the current library

 show tables;
Copy the code

Display table columns (data type, primary key, other data)

show columns from customers; // DESCRIBE customers;Copy the code

Look at tables in other libraries

 show tables from emp;
Copy the code

View table structure

 desc emp;
Copy the code

View the table creation statement

 show create table emp;
Copy the code

View library creation statements

 SHOW CREATE DATABASE
Copy the code

Common queries (SELECT)

Query a field

 select ename from emp;
Copy the code

Query multiple fields

 select empno, ename from emp;
Copy the code

Separated by commas

Query all fields

 select * from emp;
Copy the code

Select * is not recommended when writing SQL statements in the program. It is recommended to specify the fields, which is more readable.

Display the queried fields as Chinese (as)

Select empno from emp where ename = 1 and ename = 1;Copy the code

Note: the string must be added single quotes | double quotation marks

Retrieve lists with DISTINCT values (DISTINCT)

Only different values are displayed

 select distinct id from emp;
Copy the code
 SELECT DISTINCT vend_id, prod_price from emp;
Copy the code

All rows will be retrieved unless both columns specified are different.

Limit results

 select id from emp limit 5;
Copy the code

Limit 5 indicates that MySQL returns no more than 5 rows

To arrive at the next five rows, specify the starting and number of rows to retrieve

Select id from emp limit 5,5;Copy the code

LIMIT 5, 5 instructs MySQL to return 5 rows starting at row 5

Use fully qualified table names (using both table names and column words)

 select emp.id from emp;
Copy the code

You can also fully qualify the database

 select emp.id from be.emp;
Copy the code

Be stands for database

(select,order by)

If there is a WHERE clause, order by must be placed after the where statement. If there is a WHERE clause, order by must be placed after the WHERE statement

Single-field sort

 select * from emp order by sal;
Copy the code

Default from small to large

Sort by multiple columns

 select * from emp order by id,name;
Copy the code

Specify sort direction

The default is ascending

 select * from emp order by sal desc;
Copy the code

Specify multiple parameters

The DESC keyword applies only to column names directly preceding it.

The highest or lowest value in a column

To find the largest

 select * from emp order by sal desc limit 1;
Copy the code

Conditional search (WHERE,select)

Equal to the test

 select id from emp where id=1;
Copy the code

The WHERE clause operator

Where uses clause operators to control conditions.

The operator instructions
= Is equal to the
<> Is not equal to
! = Is not equal to
< Less than
< = Less than or equal to
> Is greater than
> = Greater than or equal to
between Between two specified values

Checking individual values

 select name from emp where name='Tom';
Copy the code

A single value requires only the corresponding transformation condition

SQL is case insensitive

If you are comparing values with columns of string type, you need to qualify quotes. Values used to compare with numeric columns are not quoted.

Range check

 select id from emp where between 1 and 5;
Copy the code

NULL check (NULL)

 select name from emp where name is null;
Copy the code

This statement returns all columns with no name, and none.

The and operator

Combine WHERE statements

 select id,price from emp where id=1 and where price<=8;
Copy the code

AND is used as a keyword in the WHERE clause to indicate that rows that meet all given criteria are retrieved.

Multiple filter criteria can be added, using an AND for each one.

The or operator

MySQL retrieves rows that match any of the criteria.

 select id,price from emp where id=1 or where price<=8;
Copy the code

Use and and or together

SQL (like most languages) processes the AND operator before the OR operator.

Use parentheses to explicitly group the corresponding operators

 select name,price from emp where (id=1 or id=6) and price>=10;
Copy the code

The in operator

The IN operator is used to specify a range of conditions IN which each condition can be matched.

Select name,price from emp where id in (1,3) order by name;Copy the code

The IN operator is generally faster than the OR operator list.

The not operator

The NOT operator in the WHERE clause has one and only function: to negate any conditions that follow it.

 select * from emp where not (sal = 1600 or sal = 3000);
Copy the code

Filter using wildcard characters

The LIKE operator & percent (%) wildcard

To use wildcards in search clauses, you must use the LIKE operator.

In the search string, % represents any number of occurrences of any character.

Find all products that start with the word pri

 select id,name from emp where name like 'pri%';
Copy the code

Case sensitive Depending on how MySQL is configured, searches can be case sensitive.

Matches any value that contains the text MED at any location

 select id,name from emp where name like '%med%';
Copy the code

Find everything that starts with S and ends with E

 select id,name from emp where name like 's%e';
Copy the code

% represents 0, 1, or more characters at a given position in the search pattern.

Underscore (_) Wildcard

Underscores serve the same purpose as %, but only match a single character instead of multiple characters.

The technique of using wildcards

Wildcard searches generally take longer to process than the other searches discussed earlier.

Don’t overuse wildcards. Other operators should be used if they serve the same purpose.

When you do need to use wildcards, don’t use them at the beginning of a search pattern unless absolutely necessary. Placing wildcards at the beginning of a search pattern is the slowest.

Matters needing attention

When SQL statements are processed, all whitespace is ignored.

Case sensitive and sort order

When sorting textual data, whether A is the same as A depends on how the database is set up. In dictionary sort order, A is treated as the same as A, which is the default behavior of MySQL (and most database management systems).

The position of the WHERE clause

When using both the ORDER BY and WHERE clauses, you should place the ORDER BY after WHERE, otherwise an error will occur

Use parentheses in the WHERE clause

Any time you use a WHERE clause with AND AND OR operators, you should explicitly group the operators with parentheses. Don’t rely too much on the default calculation order, even if it is exactly what you want. There is no harm in using parentheses; they disambiguate.

Notice the trailing space

Trailing whitespace may interfere with wildcard matching. A simple solution to this problem is to append a % to the end of the search pattern. A better approach is to use functions to remove the leading and trailing whitespace.