目录

MySQL-SELECT语句-查询

JAY.LIN 收录于 Mysql

2025-03-10 约 2088 字预计阅读 5 分钟

https://bing.ee123.net/img/rand?artid=146141590

目录

MySQL—–SELECT语句-查询

SELECT语句-查询

1.格式

📖简单查询:
格式: select 字段1,字段n from 表名；
起别名: 通过在字段后添加 as 别名 as可以省略 改变表头
eg: select username "用户名",password as "密码" from user

2.操作

1.创建表
mysql> create table user(
-> user_id int primary key auto_increment,
-> username varchar(255),
-> password varchar(255),
-> name varchar(255),
-> hire_date date);
Query OK, 0 rows affected (0.01 sec)

mysql> desc user;
+-----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+----------------+
| user_id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(255) | YES | | NULL | |
| password | varchar(255) | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
| hire_date | date | YES | | NULL | |
+-----------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> insert into user values(null,"zhang","111","张三","1996-05-04");
Query OK, 1 row affected (0.01 sec)

mysql> select * from user;
+---------+----------+----------+--------+------------+
| user_id | username | password | name 	 | hire_date |
+---------+----------+----------+--------+------------+
| 1 	  | zhang	 | 111 		| 张三    | 1996-05-04 |
+---------+----------+----------+--------+------------+
1 row in set (0.00 sec)

插入数据
mysql> insert into user values(null,"zhang","111","张三","1996-05-04"),
-> (null,"zhang1","111","张三1","1997-05-04"),
-> (null,"zhang2","111","张三2","1998-05-04"),
-> (null,"zhang3","111","张三3","1999-05-04"),
-> (null,"zhang4","111","张三4","1990-05-04"),
-> (null,"lijian","222","李剑","1990-08-26"),
-> (null,"lijian1","2221","李剑1","1990-10-30");
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0

2.查所有字段
mysql> select * from user; 
+---------+----------+----------+---------+------------+
| user_id | username | password | name | hire_date |
+---------+----------+----------+---------+------------+
| 1 	  | zhang 	 | 111	    | 张三 | 1996-05-04 |
| 2		  | zhang  	 | 111	    | 张三 | 1996-05-04 |
| 3		  | zhang1 	 | 111 		| 张三1 | 1997-05-04 |
| 4 	  | zhang2	 | 111 		| 张三2 | 1998-05-04 |
| 5 	  | zhang3	 | 111 		| 张三3 | 1999-05-04 |
| 6 	  | zhang4	 | 111 		| 张三4 | 1990-05-04 |
| 7   	  | lijian	 | 222 		| 李剑 | 1990-08-26 |
| 8		  | lijian1	 | 2221 	| 李剑1 | 1990-10-30 |
+---------+----------+----------+---------+------------+
8 rows in set (0.00 sec)

3.查询单个字段
mysql> select username from user;
+----------+
| username |
+----------+
| zhang |
| zhang |
| zhang1 |
| zhang2 |
| zhang3 |
| zhang4 |
| lijian |
| lijian1 |
+----------+
8 rows in set (0.00 sec)

4.查询多个字段
mysql> select username,name from user;
+----------+---------+
| username | name |
+----------+---------+
| zhang | 张三 |
| zhang | 张三 |
| zhang1 | 张三1 |
| zhang2 | 张三2 |
| zhang3 | 张三3 |
| zhang4 | 张三4 |
| lijian | 李剑 |
| lijian1 | 李剑1 |
+----------+---------+
8 rows in set (0.00 sec)

5.起别名
mysql> select username as '用户名',name as '姓名' from user;
+-----------+---------+
| 用户名 | 姓名 |
+-----------+---------+
| zhang | 张三 |
| zhang | 张三 |
| zhang1 | 张三1 |
| zhang2 | 张三2 |
| zhang3 | 张三3 |
| zhang4 | 张三4 |
| lijian | 李剑 |
| lijian1 | 李剑1 |
+-----------+---------+
8 rows in set (0.00 sec)

mysql> select username '用户名',name '姓名' from user; --6.省略as起别名
+-----------+---------+
| 用户名 | 姓名 |
+-----------+---------+
| zhang | 张三 |
| zhang | 张三 |
| zhang1 | 张三1 |
| zhang2 | 张三2 |
| zhang3 | 张三3 |
| zhang4 | 张三4 |
| lijian | 李剑 |
| lijian1 | 李剑1 |
+-----------+---------+
8 rows in set (0.00 sec)

3.算数表达式

1. 两个整数运算，做算数
mysql> select 100+200; 
+---------+
| 100+200 |
+---------+
| 300 |
+---------+
1 row in set (0.00 sec)

2.字符串和整数的运算，先转化字符串，如果成功则算数，如果不成功则字符串变0
mysql> select '100'+200; 
+-----------+
| '100'+200 |
+-----------+
| 300 |
+-----------+
1 row in set (0.00 sec)

3.字符串的转换，可以包含字母，直接省略---一般数字开头的字符串
mysql> select '100q'+200;
+------------+
| '100q'+200 |
+------------+
| 300 |
+------------+
1 row in set, 1 warning (0.00 sec)

4.转化不成功则使用0算数
mysql> select 'q100q'+200;
+-------------+
| 'q100q'+200 |
+-------------+
| 200 |
+-------------+
1 row in set, 1 warning (0.00 sec)

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

5.只要是运算，都需要转化字符串
mysql> select 'q100q'+ 'q100q'; 
+------------------+
| 'q100q'+ 'q100q' |
+------------------+
| 0 |
+------------------+
1 row in set, 2 warnings (0.00 sec)

6.NULL值参与的运算结果直接为null
mysql> select NULL + 200;
+------------+
| NULL + 200 |
+------------+
| NULL |
+------------+
1 row in set (0.00 sec)

mysql> select 200+ 300 + NULL + 200;
+-----------------------+
| 200+ 300 + NULL + 200 |
+-----------------------+
| NULL |
+-----------------------+
1 row in set (0.00 sec)

mysql> select * from user;
+---------+----------+----------+---------+------------+
| user_id | username | password | name | hire_date |
+---------+----------+----------+---------+------------+
| 1 | zhang | 111 | 张三 | 1996-05-04 |
| 2 | zhang | 111 | 张三 | 1996-05-04 |
| 3 | zhang1 | 111 | 张三1 | 1997-05-04 |
| 4 | zhang2 | 111 | 张三2 | 1998-05-04 |
| 5 | zhang3 | 111 | 张三3 | 1999-05-04 |
| 6 | zhang4 | 111 | 张三4 | 1990-05-04 |
| 7 | lijian | 222 | 李剑 | 1990-08-26 |
| 8 | lijian1 | 2221 | 李剑1 | 1990-10-30 |
+---------+----------+----------+---------+------------+
8 rows in set (0.00 sec)

7. 查询字段可以运算
mysql> select username,password * 6,name from user;
+----------+--------------+---------+
| username | password * 6 | name |
+----------+--------------+---------+
| zhang | 666 | 张三 |
| zhang | 666 | 张三 |
| zhang1 | 666 | 张三1 |
| zhang2 | 666 | 张三2 |
| zhang3 | 666 | 张三3 |
| zhang4 | 666 | 张三4 |
| lijian | 1332 | 李剑 |
| lijian1 | 13326 | 李剑1 |
+----------+--------------+---------+
8 rows in set (0.00 sec)

8.运算符的优先级 括号内->从左向右->乘除->加减
mysql> select username,password * (6 + 1),name from user;
+----------+--------------------+---------+
| username | password * (6 + 1) | name |
+----------+--------------------+---------+
| zhang | 777 | 张三 |
| zhang | 777 | 张三 |
| zhang1 | 777 | 张三1 |
| zhang2 | 777 | 张三2 |
| zhang3 | 777 | 张三3 |
| zhang4 | 777 | 张三4 |
| lijian | 1554 | 李剑 |
| lijian1 | 15547 | 李剑1 |
+----------+--------------------+---------+
8 rows in set (0.00 sec)