Jiachen's blog

获取整数函数

Posted on 1 8 月, 20232 8 月, 2023 by Jiachen

1、返回大于或等于数值x的最小整数

语法：ceil(x);

mysql> select ceil(4.3),ceil(-2.5);
+-----------+------------+
| ceil(4.3) | ceil(-2.5) |
+-----------+------------+
| 5 | -2 |
+-----------+------------+
1 row in set (0.01 sec)

2、返回小于或等于数值x的最大整数

语法：floor(x);

mysql> select floor(4.3),floor(-2.5);
+------------+-------------+
| floor(4.3) | floor(-2.5) |
+------------+-------------+
| 4 | -3 |
+------------+-------------+
1 row in set (0.00 sec)

3、截取数值函数

语法：truncate(x,y);

返回数值x，保留小数点后y位。

mysql> select truncate(999.345,2),truncate(999.345,-1);/*不四舍五入*/
+---------------------+----------------------+
| truncate(999.345,2) | truncate(999.345,-1) |
+---------------------+----------------------+
| 999.34 | 990 |
+---------------------+----------------------+
1 row in set (0.00 sec)

mysql> select truncate(999.345,2),truncate(999.999,-1);
+---------------------+----------------------+
| truncate(999.345,2) | truncate(999.999,-1) |
+---------------------+----------------------+
| 999.34 | 990 |
+---------------------+----------------------+
1 row in set (0.00 sec)

4、四舍五入函数

语法：round(x);

函数返回值x经过四舍五入操作后的数值。

mysql> select round(999.999);/*取整*/
+----------------+
| round(999.999) |
+----------------+
| 1000 |
+----------------+
1 row in set (0.00 sec)

mysql> select round(903.5),round(-903.4);/*取整*/
+--------------+---------------+
| round(903.5) | round(-903.4) |
+--------------+---------------+
| 904 | -903 |
+--------------+---------------+
1 row in set (0.00 sec)

语法：round(x,y);

返回数据x保留到小数点后y位的值，在具体截取数据时需要进行四舍五入的操作。

mysql> select round(903.53,2),round(903.54,-1);
+-----------------+------------------+
| round(903.53,2) | round(903.54,-1) |
+-----------------+------------------+
| 903.53 | 900 |
+-----------------+------------------+
1 row in set (0.00 sec)

mysql> select round(903.54,0);
+-----------------+
| round(903.54,0) |
+-----------------+
| 904 |
+-----------------+
1 row in set (0.00 sec)

mysql> select truncate(903.54,0);
+--------------------+
| truncate(903.54,0) |
+--------------------+
| 903 |
+--------------------+
1 row in set (0.00 sec)

字符串截取与替换函数

Posted on 21 7 月, 202321 7 月, 2023 by Jiachen

1、从左边或右边截取子字符串

left(str,num);

right(str,num);

返回字符串str中包含前num个字母（从左/或右边数）的字符串。

mysql> select left('mysql',2),right('mysql',3);
+-----------------+------------------+
| left('mysql',2) | right('mysql',3) |
+-----------------+------------------+
| my              | sql              |
+-----------------+------------------+
1 row in set (0.00 sec)

2、截取指定位置和长度的字符串

substring(str,num,len);

mid(str,num,len);

返回字符串str中的第num个位置开始长度为len的子字符串。

mysql> select substring('zhaojd',2,3);
+-------------------------+
| substring('zhaojd',2,3) |
+-------------------------+
| hao |
+-------------------------+
1 row in set (0.00 sec)
mysql> select mid('zhapjd',2,3);
+-------------------+
| mid('zhapjd',2,3) |
+-------------------+
| hap |
+-------------------+
1 row in set (0.00 sec)
mysql> select substring('zhaojd',2,3),mid('zhaojd',2,3);
+-------------------------+-------------------+
| substring('zhaojd',2,3) | mid('zhaojd',2,3) |
+-------------------------+-------------------+
| hao | hao |
+-------------------------+-------------------+
1 row in set (0.00 sec)

3、替换字符串

replace(str,substr,newstr);

将字符串str中的子字符串substr用字符串newstr来替换。

mysql> select replace('这是MySQL数据库','MySQL','db2');
+-----------------------------------------------+
| replace('这是MySQL数据库','MySQL','db2') |
+-----------------------------------------------+
| 这是db2数据库 |
+-----------------------------------------------+
1 row in set (0.00 sec)

查找字符串函数

Posted on 20 7 月, 2023 by Jiachen

1、返回字符串位置的find_in_set()函数

find_in_set(str1,str2);

该函数会返回在字符串str2中与str1相匹配的字符串的位置，参数str2字符串中包含若干个用逗号隔开的字符串。

mysql> select find_in_set('mysql','oracle,mysql,db2');
+-----------------------------------------+
| find_in_set('mysql','oracle,mysql,db2') |
+-----------------------------------------+
|                                       2 |
+-----------------------------------------+
1 row in set (0.10 sec)

mysql> select find_in_set('mysql','oracle,wonita,db2');/*返回0，不是返回NULL*/
+------------------------------------------+
| find_in_set('mysql','oracle,wonita,db2') |
+------------------------------------------+
|                                        0 |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> select find_in_set('mysql','oracle,mysql   ,db2');/*空格有效*/
+--------------------------------------------+
| find_in_set('mysql','oracle,mysql   ,db2') |
+--------------------------------------------+
|                                          0 |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select find_in_set('mysql','oracle,  mysql,db2');/*空格有效*/
+-------------------------------------------+
| find_in_set('mysql','oracle,  mysql,db2') |
+-------------------------------------------+
|                                         0 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select find_in_set('mysql','oracle,mysql,db2,mysql');/*返回第一个匹配的位置*/
+-----------------------------------------------+
| find_in_set('mysql','oracle,mysql,db2,mysql') |
+-----------------------------------------------+
|                                             2 |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> select find_in_set('mysql  ','oracle,mysql  ,db2');/*空格有效*/
+---------------------------------------------+
| find_in_set('mysql  ','oracle,mysql  ,db2') |
+---------------------------------------------+
|                                           2 |
+---------------------------------------------+
1 row in set (0.00 sec)

2、返回指定字符串位置的field()函数

field(str,str1,str2,...);

该函数返回第一个与字符串str匹配的字符串的位置。

mysql> select field('mysql','oracle','db2','redis','mysql');
+-----------------------------------------------+
| field('mysql','oracle','db2','redis','mysql') |
+-----------------------------------------------+
|                                             4 |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> select field('mysql','mysql  ','db2','redis');\*字符后面的空格被忽略掉*\
+----------------------------------------+
| field('mysql','mysql  ','db2','redis') |
+----------------------------------------+
|                                      1 |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> select field('mysql','db2','  mysql','redis','mysql');\*字符前面的空格没有被忽略*\
+------------------------------------------------+
| field('mysql','db2','  mysql','redis','mysql') |
+------------------------------------------------+
|                                              4 |
+------------------------------------------------+
1 row in set (0.00 sec)

3、返回指定位置的字符串的elt()函数

elt(n,str1,str2,...);

mysql> select elt(1,'mysql','db2','oracle');
+-------------------------------+
| elt(1,'mysql','db2','oracle') |
+-------------------------------+
| mysql                         |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select elt(0,'mysql','db2','oracle');/*超出返回范围*/
+-------------------------------+
| elt(0,'mysql','db2','oracle') |
+-------------------------------+
| NULL                          |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select elt(4,'mysql','db2','oracle');/*超出返回范围*/
+-------------------------------+
| elt(4,'mysql','db2','oracle') |
+-------------------------------+
| NULL                          |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select length(elt(1,'mysql  ','db2','oracle'));/*空格被输出*/
+-----------------------------------------+
| length(elt(1,'mysql  ','db2','oracle')) |
+-----------------------------------------+
|                                       7 |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> select length(elt(1,'  mysql  ','db2','oracle'));/*空格被输出*/
+-------------------------------------------+
| length(elt(1,'  mysql  ','db2','oracle')) |
+-------------------------------------------+
|                                         9 |
+-------------------------------------------+
1 row in set (0.00 sec)

大小写转换函数

Posted on 20 7 月, 2023 by Jiachen

1、将字母转换为大写：

upper();

ucase();

2、将字母转换为小写：

lower();

lcase();

mysql> select upper("mysql"),ucase("mYsql"),lower("MYSQL"),lcase("MySQL");
+----------------+----------------+----------------+----------------+
| upper("mysql") | ucase("mYsql") | lower("MYSQL") | lcase("MySQL") |
+----------------+----------------+----------------+----------------+
| MYSQL          | MYSQL          | mysql          | mysql          |
+----------------+----------------+----------------+----------------+
1 row in set (0.00 sec)

mysql> select upper("--");
+-------------+
| upper("--") |
+-------------+
| --          |
+-------------+
1 row in set (0.00 sec)

mysql> select upper("--mysql");
+------------------+
| upper("--mysql") |
+------------------+
| --MYSQL          |
+------------------+
1 row in set (0.00 sec)

mysql> select upper('--my数据库');
+------------------------+
| upper('--my数据库')    |
+------------------------+
| --MY数据库             |
+------------------------+
1 row in set (0.00 sec)

比较字符串大小函数

Posted on 28 6 月, 202328 6 月, 2023 by Jiachen

length()返回字节长度，一个多字节字符算作多个字节。（utf8编码：一个汉字3个字节，一个数字或字母1个字节。gbk编码：一个汉字2个字节，一个数字或字母1个字节。）

char_lenth()返回字符长度，一个多字节字符算作一个字符。即不管是汉字还是数字或字母都算是1个字符。

strcmp()比较字符串大小函数，如果参数str1大于str2，返回1；如果str1小于str2，返回-1；如果str1等于str2则返回0。比较字符串大小函数是基于当前的字符集，不同字符集比较结果可能会发生变化。

mysql> select length('mysql'),length('汉字'),char_length('mysql'),char_length('
汉字');
+-----------------+------------------+----------------------+-------------------
----+
| length('mysql') | length('汉字')   | char_length('mysql') | char_length('汉字'
)   |
+-----------------+------------------+----------------------+-------------------
----+
|               5 |                6 |                    5 |
  2 |
+-----------------+------------------+----------------------+-------------------
----+
1 row in set (0.01 sec)

mysql> select length(ceil(-2.5)),char_length(ceil(-2.5));
+--------------------+-------------------------+
| length(ceil(-2.5)) | char_length(ceil(-2.5)) |
+--------------------+-------------------------+
|                  2 |                       2 |
+--------------------+-------------------------+
1 row in set (0.01 sec)

mysql> select ceil(-2.5);/*取整函数*/
+------------+
| ceil(-2.5) |
+------------+
|         -2 |
+------------+
1 row in set (0.00 sec)

mysql> select ceil(3.8);
+-----------+
| ceil(3.8) |
+-----------+
|         4 |
+-----------+
1 row in set (0.00 sec)

mysql> select ceil(-3.8);
+------------+
| ceil(-3.8) |
+------------+
|         -3 |
+------------+
1 row in set (0.00 sec)

mysql> select ceil(-2.1);
+------------+
| ceil(-2.1) |
+------------+
|         -2 |
+------------+
1 row in set (0.00 sec)

mysql> select ceil(2.1);
+-----------+
| ceil(2.1) |
+-----------+
|         3 |
+-----------+
1 row in set (0.00 sec)

mysql> select strcmp('abc','abd'),strcmp('abc','abc'),strcmp('abc','abb');
+---------------------+---------------------+---------------------+
| strcmp('abc','abd') | strcmp('abc','abc') | strcmp('abc','abb') |
+---------------------+---------------------+---------------------+
|                  -1 |                   0 |                   1 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> select strcmp('1','2');
+-----------------+
| strcmp('1','2') |
+-----------------+
|              -1 |
+-----------------+
1 row in set (0.00 sec)

mysql> select strcmp('a','A');
+-----------------+
| strcmp('a','A') |
+-----------------+
|               0 |
+-----------------+
1 row in set (0.00 sec)

mysql> select strcmp('你','我');
+---------------------+
| strcmp('你','我')   |
+---------------------+
|                  -1 |
+---------------------+
1 row in set (0.00 sec)

mysql> select strcmp('ab','abc');
+--------------------+
| strcmp('ab','abc') |
+--------------------+
|                 -1 |
+--------------------+
1 row in set (0.00 sec)

mysql> select strcmp('21','3');
+------------------+
| strcmp('21','3') |
+------------------+
|               -1 |
+------------------+
1 row in set (0.00 sec)

合并字符串函数

Posted on 27 6 月, 202328 6 月, 2023 by Jiachen

concat()函数会将传入的参数连接起来返回合并的字符串类型的数据。如果其中一个参数为null，则返回值为null。

concat_ws()函数与concat()相比，多了一个表示分隔符的seq参数，不仅将传入的其他参数连接起来，而且还会通过分隔符将各个字符串分割开来。分隔符可以是一个字符串，也可以是其他参数。如果分隔符为null，则返回结果为null。函数会忽略任何分隔符后的参数null。

mysql> select concat('huang','lao','shi'),concat('huang','lao','shi',null);
+-----------------------------+----------------------------------+
| concat('huang','lao','shi') | concat('huang','lao','shi',null) |
+-----------------------------+----------------------------------+
| huanglaoshi                 | NULL                             |
+-----------------------------+----------------------------------+
1 row in set (0.01 sec)

mysql> select concat(curdate(),12.2);
+------------------------+
| concat(curdate(),12.2) |
+------------------------+
| 2023-06-2712.2         |
+------------------------+
1 row in set (0.00 sec)

mysql> select concat_ws('-','020','83378683');
+---------------------------------+
| concat_ws('-','020','83378683') |
+---------------------------------+
| 020-83378683                    |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select concat('-','020','83378683');
+------------------------------+
| concat('-','020','83378683') |
+------------------------------+
| -02083378683                 |
+------------------------------+
1 row in set (0.00 sec)

mysql> select concat_ws('-','020','83378683','83378683');
+--------------------------------------------+
| concat_ws('-','020','83378683','83378683') |
+--------------------------------------------+
| 020-83378683-83378683                      |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select concat_ws(null,'020','83378683'),concat_ws('-','020',null,'8337868
3');
+----------------------------------+--------------------------------------+
| concat_ws(null,'020','83378683') | concat_ws('-','020',null,'83378683') |
+----------------------------------+--------------------------------------+
| NULL                             | 020-83378683                         |
+----------------------------------+--------------------------------------+
1 row in set (0.00 sec)

mysql> select concat_ws('/','83378683');
+---------------------------+
| concat_ws('/','83378683') |
+---------------------------+
| 83378683                  |
+---------------------------+
1 row in set (0.00 sec)

mysql> select concat_ws('+','83378683');
+---------------------------+
| concat_ws('+','83378683') |
+---------------------------+
| 83378683                  |
+---------------------------+
1 row in set (0.00 sec)

mysql> select concat_ws('/','83378683','83378683');
+--------------------------------------+
| concat_ws('/','83378683','83378683') |
+--------------------------------------+
| 83378683/83378683                    |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> select concat_ws('/','020','83378683');
+---------------------------------+
| concat_ws('/','020','83378683') |
+---------------------------------+
| 020/83378683                    |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select concat_ws('or','010','83378683','83378683');
+---------------------------------------------+
| concat_ws('or','010','83378683','83378683') |
+---------------------------------------------+
| 010or83378683or83378683                     |
+---------------------------------------------+
1 row in set (0.00 sec)

mysql> select concat_ws('abc','010','83378683');
+-----------------------------------+
| concat_ws('abc','010','83378683') |
+-----------------------------------+
| 010abc83378683                    |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> select concat_ws('--','010','83378683','83378683');
+---------------------------------------------+
| concat_ws('--','010','83378683','83378683') |
+---------------------------------------------+
| 010--83378683--83378683                     |
+---------------------------------------------+
1 row in set (0.00 sec)

其他类型的查询

Posted on 9 6 月, 2023 by Jiachen

1、子查询（嵌套查询）

mysql> select * from people;
+------+-----+
| name | age |
+------+-----+
| 甲   |  29 |
| 乙   |  35 |
+------+-----+
2 rows in set (0.00 sec)


mysql> select * from houses where name in (select name from people where age<30);
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲   | 天河           | 1997            |
| 甲   | 天河           | 1998            |
+------+----------------+-----------------+
2 rows in set (0.10 sec)

mysql> select * from houses;
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲   | 天河           | 1997            |
| 甲   | 天河           | 1998            |
+------+----------------+-----------------+
2 rows in set (0.00 sec)

mysql> select * from houses where name='甲';
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲   | 天河           | 1997            |
| 甲   | 天河           | 1998            |
+------+----------------+-----------------+
2 rows in set (0.00 sec)

mysql> select * from houses where name in (select name from people where age >100);
Empty set (0.00 sec)

2、分组查询（group by）

mysql> select house_location from houses group by house_location;
+----------------+
| house_location |
+----------------+
| 天河           |
+----------------+
1 row in set (0.00 sec)

3、聚合查询（count(), sum(), max(), min()）

mysql> select house_location, count(*) as total from houses group by house_location;
+----------------+-------+
| house_location | total |
+----------------+-------+
| 天河           |     2 |
+----------------+-------+
1 row in set (0.06 sec)


mysql> select sum(age) from people;
+----------+
| sum(age) |
+----------+
|       64 |
+----------+
1 row in set (0.04 sec)

mysql> select * from people;
+------+-----+
| name | age |
+------+-----+
| 甲   |  29 |
| 乙   |  35 |
+------+-----+
2 rows in set (0.00 sec)

mysql> select max(age) from people;
+----------+
| max(age) |
+----------+
|       35 |
+----------+
1 row in set (0.04 sec)

mysql> select min(age) from people;
+----------+
| min(age) |
+----------+
|       29 |
+----------+
1 row in set (0.00 sec)

4、组合查询（union）

mysql> select name from houses;
+------+
| name |
+------+
| 甲   |
| 甲   |
+------+
2 rows in set (0.00 sec)

mysql> select name from people;
+------+
| name |
+------+
| 甲   |
| 乙   |
+------+
2 rows in set (0.00 sec)

mysql> select name from houses union distinct select name from people;
+------+
| name |
+------+
| 甲   |
| 乙   |
+------+
2 rows in set (0.03 sec)

mysql> select name from houses union all select name from people;
+------+
| name |
+------+
| 甲   |
| 甲   |
| 甲   |
| 乙   |
+------+
4 rows in set (0.00 sec)

mysql> select name from houses union select name from people;/*等同于 union distinct*/
+------+
| name |
+------+
| 甲   |
| 乙   |
+------+
2 rows in set (0.00 sec)

连接查询

Posted on 1 6 月, 2023 by Jiachen

MySQL的连接查询分为内连接查询（INNER JOIN）、外连接查询（OUTER JOIN）和复合连接查询，其中外连接查询又包含左连接查询（LEFT JOIN）和右连接查询（RIGHT JOIN）。

mysql> use learning;
Database changed
mysql> show tables;
+--------------------+
| Tables_in_learning |
+--------------------+
| houses             |
| housesview         |
| people             |
| people2            |
+--------------------+
4 rows in set (0.01 sec)

mysql> select * from houses;
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲   | 天河           | 1997            |
| 甲   | 天河           | 1998            |
| 乙   | 番禺           | 1998            |
| 丙   | 番禺           | 1998            |
| 丙   | 天河           | 1999            |
| 丁   | 天河           | 2000            |
+------+----------------+-----------------+
6 rows in set (0.00 sec)

mysql> select * from people;
+------+------+
| name | age  |
+------+------+
| 甲   |   29 |
| 乙   |   35 |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from people2;
+------+------+
| name | age  |
+------+------+
| jia  |   29 |
| yi   |   35 |
+------+------+
2 rows in set (0.00 sec)


mysql> select a.name name1,a.house_location house_location3, a.purchasing_year
购买年份,b.name name45,b.age from houses a
    -> right join people2 b
    -> on a.name=b.name;
+-------+-----------------+--------------+--------+------+
| name1 | house_location3 | 购买年份     | name45 | age  |
+-------+-----------------+--------------+--------+------+
| NULL  | NULL            | NULL         | jia    |   29 |
| NULL  | NULL            | NULL         | yi     |   35 |
+-------+-----------------+--------------+--------+------+
2 rows in set (0.00 sec)

mysql> create table housing_price (house_location varchar(3) not null, price int
(3) not null);
Query OK, 0 rows affected (0.07 sec)

mysql> desc housing_price;
+----------------+------------+------+-----+---------+-------+
| Field          | Type       | Null | Key | Default | Extra |
+----------------+------------+------+-----+---------+-------+
| house_location | varchar(3) | NO   |     | NULL    |       |
| price          | int(3)     | NO   |     | NULL    |       |
+----------------+------------+------+-----+---------+-------+
2 rows in set (0.01 sec)


mysql> insert into housing_price values('天河','500');
Query OK, 1 row affected (0.01 sec)

mysql> insert into housing_price values('番禺','400');
Query OK, 1 row affected (0.00 sec)

mysql> select * from housing_price;
+----------------+-------+
| house_location | price |
+----------------+-------+
| 天河           |   500 |
| 番禺           |   400 |
+----------------+-------+
2 rows in set (0.00 sec)

mysql> show tables;
+--------------------+
| Tables_in_learning |
+--------------------+
| houses             |
| housesview         |
| housing_price      |
| people             |
| people2            |
+--------------------+
5 rows in set (0.00 sec)


mysql> alter table housing_price rename to housingprice;
Query OK, 0 rows affected (0.04 sec)

mysql> show tables;
+--------------------+
| Tables_in_learning |
+--------------------+
| houses             |
| housesview         |
| housingprice       |
| people             |
| people2            |
+--------------------+
5 rows in set (0.00 sec)

mysql> select * from houses;
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲   | 天河           | 1997            |
| 甲   | 天河           | 1998            |
| 乙   | 番禺           | 1998            |
| 丙   | 番禺           | 1998            |
| 丙   | 天河           | 1999            |
| 丁   | 天河           | 2000            |
+------+----------------+-----------------+
6 rows in set (0.00 sec)

mysql> select * from people;
+------+------+
| name | age  |
+------+------+
| 甲   |   29 |
| 乙   |   35 |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from people2;
+------+------+
| name | age  |
+------+------+
| jia  |   29 |
| yi   |   35 |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from housingprice;
+----------------+-------+
| house_location | price |
+----------------+-------+
| 天河           |   500 |
| 番禺           |   400 |
+----------------+-------+
2 rows in set (0.00 sec)

mysql> select a.name,a.house_location,a.purchasing_year,b.age from houses a
    -> inner join people b
    -> on a.name=b.name;
+------+----------------+-----------------+------+
| name | house_location | purchasing_year | age  |
+------+----------------+-----------------+------+
| 甲   | 天河           | 1997            |   29 |
| 甲   | 天河           | 1998            |   29 |
| 乙   | 番禺           | 1998            |   35 |
+------+----------------+-----------------+------+
3 rows in set (0.00 sec)

mysql> select a.name,a.house_location,a.purchasing_year,b.age from houses a
    -> join people2 b
    -> on a.name=b.name;
Empty set (0.00 sec)

mysql> select a.name,a.house_location,a.purchasing_year,b.age from houses a
    -> left join people b
    -> on a.name=b.name;
+------+----------------+-----------------+------+
| name | house_location | purchasing_year | age  |
+------+----------------+-----------------+------+
| 甲   | 天河           | 1997            |   29 |
| 甲   | 天河           | 1998            |   29 |
| 乙   | 番禺           | 1998            |   35 |
| 丙   | 番禺           | 1998            | NULL |
| 丙   | 天河           | 1999            | NULL |
| 丁   | 天河           | 2000            | NULL |
+------+----------------+-----------------+------+
6 rows in set (0.00 sec)


mysql> select a.name,a.house_location,a.purchasing_year,b.age from people b
    -> left join houses a
    -> on a.name=b.name;
+------+----------------+-----------------+------+
| name | house_location | purchasing_year | age  |
+------+----------------+-----------------+------+
| 甲   | 天河           | 1997            |   29 |
| 甲   | 天河           | 1998            |   29 |
| 乙   | 番禺           | 1998            |   35 |
+------+----------------+-----------------+------+
3 rows in set (0.00 sec)

mysql> select a.name,a.house_location,a.purchasing_year,b.age from houses a
    -> left join people b
    -> on a.name=b.name and b.name='乙';
+------+----------------+-----------------+------+
| name | house_location | purchasing_year | age  |
+------+----------------+-----------------+------+
| 乙   | 番禺           | 1998            |   35 |
| 甲   | 天河           | 1997            | NULL |
| 甲   | 天河           | 1998            | NULL |
| 丙   | 番禺           | 1998            | NULL |
| 丙   | 天河           | 1999            | NULL |
| 丁   | 天河           | 2000            | NULL |
+------+----------------+-----------------+------+
6 rows in set (0.00 sec)

mysql> select a.name,a.house_location,a.purchasing_year,b.age from houses a
    -> left join people b
    -> on a.name=b.name where b.name='乙';
+------+----------------+-----------------+------+
| name | house_location | purchasing_year | age  |
+------+----------------+-----------------+------+
| 乙   | 番禺           | 1998            |   35 |
+------+----------------+-----------------+------+
1 row in set (0.00 sec)

mysql> insert into people values('戊','45');
Query OK, 1 row affected (0.00 sec)

mysql> select * from people;
+------+------+
| name | age  |
+------+------+
| 甲   |   29 |
| 乙   |   35 |
| 戊   |   45 |
+------+------+
3 rows in set (0.00 sec)

mysql> select a.name,a.house_location,a.purchasing_year,b.age from houses a
    -> right join people b
    -> on a.name=b.name;
+------+----------------+-----------------+------+
| name | house_location | purchasing_year | age  |
+------+----------------+-----------------+------+
| 甲   | 天河           | 1997            |   29 |
| 甲   | 天河           | 1998            |   29 |
| 乙   | 番禺           | 1998            |   35 |
| NULL | NULL           | NULL            |   45 |
+------+----------------+-----------------+------+
4 rows in set (0.00 sec)

mysql> select a.name 姓名,a.house_location,a.purchasing_year 购买年份,b.age 年龄
 from houses a
    -> right join people2 b
    -> on a.name=b.name;
+--------+----------------+--------------+--------+
| 姓名   | house_location | 购买年份     | 年龄   |
+--------+----------------+--------------+--------+
| NULL   | NULL           | NULL         |     29 |
| NULL   | NULL           | NULL         |     35 |
+--------+----------------+--------------+--------+
2 rows in set (0.00 sec)

mysql> select a.name,a.house_location,a.purchasing_year,b.age,c.price from house
s a
    -> inner join people b
    -> right join housingprice c
    -> on a.name=b.name
    -> and a.house_location=c.house_location;
+------+----------------+-----------------+------+-------+
| name | house_location | purchasing_year | age  | price |
+------+----------------+-----------------+------+-------+
| 甲   | 天河           | 1997            |   29 |   500 |
| 甲   | 天河           | 1998            |   29 |   500 |
| 乙   | 番禺           | 1998            |   35 |   400 |
+------+----------------+-----------------+------+-------+
3 rows in set (0.00 sec)

mysql>

带关键字查询

Posted on 29 5 月, 202329 5 月, 2023 by Jiachen

带关键字WHERE的查询，语法select<字段名>from<表或视图名>where<字段名>=’某某’;
带关键字IN的查询，语法select<字段名>from<表或视图名>where<字段名>in(‘某某’,’某某1′);
带关键字BETWEEN AND的范围查询（闭区间），语法select<字段名>from<表或视图名>where<字段名>between’某某’and’某某2‘;
带关键字LIKE的字符匹配查询，语法select<字段名>from<表或视图名>where<字段名>like’某%’;
带关键字IS NULL空值查询或IS NOT NULL非空值查询，语法select<字段名>from<表或视图名>where<字段名>IS NULL;
带关键字AND的多条件查询，语法select<字段名>from<表或视图名>where<字段名>=’某某’ and <字段名1>=’某某1′;
带关键字OR的多条件查询，语法select<字段名>from<表或视图名>where<字段名>=’某某’ or <字段名1>=’某某1′;
用关键字DISTINCT去除结果集重复行，语法select distinct<字段名>from<表或视图名>;
用关键字ORDER BY对查询结果排序，语法select<字段名>from<表或视图名>order by<字段名>asc或desc;
用关键字LIMIT限制查询结果的数量，语法select<字段名>from<表或视图名>limit 1;

mysql> select * from houses;
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲   | 天河           | 1997            |
| 甲   | 天河           | 1998            |
+------+----------------+-----------------+
2 rows in set (0.01 sec)

mysql> select * from houses where purchasing_year='1998';
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲   | 天河           | 1998            |
+------+----------------+-----------------+
1 row in set (0.00 sec)

mysql> select * from houses where purchasing_year in ('1997','1998','1999');
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲   | 天河           | 1997            |
| 甲   | 天河           | 1998            |
+------+----------------+-----------------+
2 rows in set (0.00 sec)

mysql> select * from houses where purchasing_year between '1996' and '1997';
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲   | 天河           | 1997            |
+------+----------------+-----------------+
1 row in set (0.00 sec)

mysql> select * from houses where purchasing_year like '199%';
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲   | 天河           | 1997            |
| 甲   | 天河           | 1998            |
+------+----------------+-----------------+
2 rows in set (0.00 sec)

mysql> select * from houses where purchasing_year like '1997';
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲   | 天河           | 1997            |
+------+----------------+-----------------+
1 row in set (0.00 sec)

mysql> select * from houses where purchasing_year like "1%";
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲   | 天河           | 1997            |
| 甲   | 天河           | 1998            |
+------+----------------+-----------------+
2 rows in set (0.00 sec)

mysql> select * from houses where purchasing_year like '%19';
Empty set (0.00 sec)

mysql> select * from houses where purchasing_year like '%97';
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲   | 天河           | 1997            |
+------+----------------+-----------------+
1 row in set (0.00 sec)

mysql> select * from houses where purchasing_year is null;
Empty set (0.00 sec)

mysql> select * from houses where purchasing_year is not null;
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲   | 天河           | 1997            |
| 甲   | 天河           | 1998            |
+------+----------------+-----------------+
2 rows in set (0.00 sec)

mysql> select * from houses where name='甲' and purchasing_year='1997';
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲   | 天河           | 1997            |
+------+----------------+-----------------+
1 row in set (0.00 sec)


mysql> select * from houses where name='乙' or (house_location='天河' and purcha
sing_year='1997');
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲   | 天河           | 1997            |
+------+----------------+-----------------+
1 row in set (0.00 sec)

mysql> select name from houses;
+------+
| name |
+------+
| 甲   |
| 甲   |
+------+
2 rows in set (0.00 sec)

mysql> select distinct name from houses;
+------+
| name |
+------+
| 甲   |
+------+
1 row in set (0.00 sec)

mysql> select * from houses order by purchasing_year asc;
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲   | 天河           | 1997            |
| 甲   | 天河           | 1998            |
+------+----------------+-----------------+
2 rows in set (0.00 sec)

mysql> select * from houses order by purchasing_year desc;
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲   | 天河           | 1998            |
| 甲   | 天河           | 1997            |
+------+----------------+-----------------+
2 rows in set (0.00 sec)

mysql> select * from houses limit 1;
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲   | 天河           | 1997            |
+------+----------------+-----------------+
1 row in set (0.00 sec)

mysql> select * from houses limit 1,1;
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲   | 天河           | 1998            |
+------+----------------+-----------------+
1 row in set (0.00 sec)

mysql> select * from houses limit 0,2;
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲   | 天河           | 1997            |
| 甲   | 天河           | 1998            |
+------+----------------+-----------------+
2 rows in set (0.00 sec)

mysql>

数据控制语言DCL

Posted on 25 5 月, 202325 5 月, 2023 by Jiachen

Data Control Language 用来授予或回收访问数据库的某种特权。

mysql> create user jiachen@'%' identified by '123';/*创建用户*/
Query OK, 0 rows affected (0.02 sec)

mysql> show grants for jiachen;
+-------------------------------------+
| Grants for jiachen@%                |
+-------------------------------------+
| GRANT USAGE ON *.* TO `jiachen`@`%` |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> use learning3;
Database changed
mysql> show tables;
+---------------------+
| Tables_in_learning3 |
+---------------------+
| test                |
+---------------------+
1 row in set (0.01 sec)

mysql> grant select,insert,update,delete on learning3.test to jiachen@'%';/*赋予用户所列出的在特定数据库表中的权限*/
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for jiachen;/*显示权限信息*/
+-----------------------------------------------------------------------------+
| Grants for jiachen@%                                                        |
+-----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `jiachen`@`%`                                         |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `learning3`.`test` TO `jiachen`@`%` |
+-----------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> grant all privileges on *.* to jiachen@'%';/*赋予用户全部权限*/
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;/*刷新权限*/
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for jiachen;
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------+
| Grants for jiachen@%




          |
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS,
 FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES,
 LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW
VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESP
ACE, CREATE ROLE, DROP ROLE ON *.* TO `jiachen`@`%`
          |
| GRANT APPLICATION_PASSWORD_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_A
DMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,PERSIST_RO_VA
RIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,R
OLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SYSTEM_US
ER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `jia
chen`@`%` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `learning3`.`test` TO `jiachen`@`%`




          |
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------+
3 rows in set (0.00 sec)

mysql> revoke select,insert,update,delete on learning3.test from jiachen@'%';/*撤销所列权限*/
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for jiachen;
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------+
| Grants for jiachen@%




          |
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS,
 FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES,
 LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW
VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESP
ACE, CREATE ROLE, DROP ROLE ON *.* TO `jiachen`@`%`
          |
| GRANT APPLICATION_PASSWORD_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_A
DMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,PERSIST_RO_VA
RIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,R
OLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SYSTEM_US
ER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `jia
chen`@`%` |
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------+
2 rows in set (0.00 sec)

mysql> revoke all privileges on *.* from jiachen@'%';/*撤销用户全部权限*/
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for jiachen;
+-------------------------------------+
| Grants for jiachen@%                |
+-------------------------------------+
| GRANT USAGE ON *.* TO `jiachen`@`%` |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> drop user jiachen;/*删除用户*/
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set, 1 warning (0.01 sec)

mysql> set global autocommit='on';/*设置打开自动提交*/
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'autocommit';/*显示自动提交仍为关闭状态*/
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set, 1 warning (0.01 sec)

mysql> exit
Bye


C:\Windows\system32>mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 36
Server version: 8.0.16 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'autocommit';/*重新登录后自动提交生效*/
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set, 1 warning (0.01 sec)