Catalog

 

MySQL Common basic commands of database

One 、 Basic query command syntax

practice ：

Two 、 Give the result set of the query names

3、 ... and 、 De duplication query operation

Four 、MySQL in “+” The sign operator

These are the basic commands and understandings commonly used in my study , Beginners are welcome to learn and discuss .

---

MySQL Common basic commands of database

Foregoing ： Study , We need to lay a solid foundation , Only with a solid foundation can we proceed step by step , Other commands after learning well .（ Come on! ！！ Learning army ）

One 、 Basic query command syntax

SELECT Query list  FROM Table name

characteristic ：

1、 The query list can be Fields in the table 、 Constant values 、 expression 、 function

2、 The query result set is a virtual table （ A table that does not exist in the database ）

practice ：

# First create a new database 
CREATE DATABASE IF NOT EXISTS test;
# Enter the created database 
USE test;
# Create new table 
CREATE TABLE IF NOT EXISTS test_tb(id int(5),name varchar(10));
# Insert several pieces of data ( The insert data command will be discussed later )
INSERT INTO test_tb VALUES(1,"K"),(2,"J"),(3,"L");

# stay MySQL in   Keywords are normalized in uppercase 
# The query syntax 
#SELECT  Query list  FROM  Table name 

# Query a single field 
mysql> SELECT id FROM test_tb;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+
4 rows in set (0.04 sec)
# Query multiple fields 

mysql> SELECT id,name FROM test_tb;
+----+------+
| id | name |
+----+------+
|  1 | K    |
|  2 | J    |
|  3 | L    |
|  4 | h    |
+----+------+
4 rows in set (0.03 sec)


# Query all fields 
# Mode one 
    mysql> SELECT * FROM test_tb;
+----+------+
| id | name |
+----+------+
|  1 | K    |
|  2 | J    |
|  3 | L    |
|  4 | h    |
+----+------+
4 rows in set (0.03 sec)

# Mode two 
    mysql> SELECT id,name FROM test_tb;
+----+------+
| id | name |
+----+------+
|  1 | K    |
|  2 | J    |
|  3 | L    |
|  4 | h    |
+----+------+
4 rows in set (0.03 sec)

# Query constant value 
mysql> SELECT 100;
+-----+
| 100 |
+-----+
| 100 |
+-----+
1 row in set (0.03 sec)

mysql> SELECT "string";
+--------+
| string |
+--------+
| string |
+--------+
1 row in set (0.03 sec)

# Query expression 

mysql> SELECT 100+2;
+-------+
| 100+2 |
+-------+
|   102 |
+-------+
1 row in set (0.03 sec)

mysql> SELECT 100-2;
+-------+
| 100-2 |
+-------+
|    98 |
+-------+
1 row in set (0.03 sec)

mysql> SELECT 100*2;
+-------+
| 100*2 |
+-------+
|   200 |
+-------+
1 row in set (0.03 sec)


mysql> SELECT 100/2;
+---------+
| 100/2   |
+---------+
| 50.0000 |
+---------+
1 row in set (0.03 sec)

# Query function 
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.33    |
+-----------+
1 row in set (0.03 sec)

Case study ： Inquire about test_tb All of the id

SELECT id FROM test_tb;

Two 、 Give the result set of the query names

Alias ： seeing the name of a thing one thinks of its function , It's another name , Because the result set of the query is a virtual table , Therefore, there is no name, only the field name of the queried table , So for the convenience of viewing and understanding , We should learn to alias our watches .

characteristic  

① Easy to understand
② If the fields to be queried have duplicate names , Use aliases to distinguish

# names ：

# Mode one ： Use AS
    SELECT 100*2 AS  result ;

    mysql> SELECT 100*2 AS  result ;
    +------+
    |  result  |
    +------+
    |  200 |
    +------+
    1 row in set (0.04 sec)

    SELECT id AS id Number  FROM test_tb;

    mysql> SELECT id AS id Number  FROM test_tb;
    +------+
    | id Number  |
    +------+
    |    1 |
    |    2 |
    |    3 |
    |    4 |
    +------+
    4 rows in set (0.03 sec)
# Mode two ： Use spaces 
    SELECT 100*2  result ;

    mysql> SELECT 100*2  result ;
    +------+
    |  result  |
    +------+
    |  200 |
    +------+
    1 row in set (0.04 sec)

    SELECT id id Number  FROM test_tb;

    mysql> SELECT id id Number  FROM test_tb;
    +------+
    | id Number  |
    +------+
    |    1 |
    |    2 |
    |    3 |
    |    4 |
    +------+
    4 rows in set (0.04 sec)

# Case study :  Inquire about test_tb surface  name Field    Alias as  out put
mysql> mysql> SELECT name out put FROM test_tb;
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql> SELECT name out put FROM test_tb' at line 1
# Use error reporting directly 

mysql> SELECT name "out put" FROM test_tb;
+---------+
| out put |
+---------+
| K       |
| J       |
| L       |
| h       |
+---------+
4 rows in set (0.03 sec)

# Because there are spaces in the alias ,MySQL Not directly identifiable , So you need to enclose those with spaces in double quotation marks .

3、 ... and 、 De duplication query operation

The de duplication operation is set to prevent unnecessary repetition .

# First, add a few rows to the table we want to query id Same data 
mysql> INSERT INTO test_tb VALUES(1,"A"),(4,"q"),(4,"w");
Query OK, 3 rows affected (0.13 sec)
Records: 3  Duplicates: 0  Warnings: 0
# Data added successfully 

# First query all the data in the table 
mysql> SELECT * FROM test_tb;
+----+------+
| id | name |
+----+------+
|  1 | K    |
|  2 | J    |
|  3 | L    |
|  4 | h    |
|  1 | A    |
|  4 | q    |
|  4 | w    |
+----+------+
7 rows in set (0.03 sec)

# There are seven pieces of data 

mysql> SELECT id FROM test_tb;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  1 |
|  4 |
|  4 |
+----+
7 rows in set (0.03 sec)

# Go to check again (DISTINCT)
mysql> SELECT DISTINCT id FROM test_tb;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+
4 rows in set (0.03 sec)

Four 、MySQL in “+” The sign operator

#mysql Medium + Number ：
# There's only one function ： Is to be an operator 
# Both query values are numeric , Then add 
mysql> SELECT 100+90;
+--------+
| 100+90 |
+--------+
|    190 |
+--------+
1 row in set (0.03 sec)

# As long as one of them is character type , Trying to convert a character value to a numeric value , If the conversion is successful , Then continue to do the addition 
mysql> select '100'+90;
+----------+
| '100'+90 |
+----------+
|      190 |
+----------+
1 row in set (0.03 sec)

# If the conversion fails , Then convert the character value to 0
mysql> SELECT 'k'+100;
+---------+
| 'k'+100 |
+---------+
|     100 |
+---------+
1 row in set (0.03 sec)

# As long as one of them is null, The result must be null
mysql> SELECT null+100;
+----------+
| null+100 |
+----------+
| NULL     |
+----------+
1 row in set (0.03 sec)

#MySQL The join operation in is CONCAT
# Case inquiry id,name And connect the junction ;
mysql> SELECT CONCAT(id,name) AS  Link  FROM test_tb;
+------+
|  Link  |
+------+
| 1K   |
| 2J   |
| 3L   |
| 4h   |
| 1A   |
| 4q   |
| 4w   |
+------+
7 rows in set (0.04 sec)

Finally, the used in the experiment is attached sql data , If you want to practice , You can take it directly

link : https://pan.baidu.com/s/1YkJMvhI17RE0pln8wQx5Ag Extraction code : evfg 

These are the basic commands and understandings commonly used in my study , Beginners are welcome to learn and discuss .