MySQL
1.登录
mysql -u用户名 -p密码
2.select
1.基础
select 列名(属性) form 表名 where 条件;
2.运算
如user表中存储员工的id、姓、名、年龄及日薪,如下表所示:
select * from user;
+----+------------+-----------+-----+--------+
| id | first_name | last_name | age | salary |
+----+------------+-----------+-----+--------+
| 1 | 张 | 三 | 20 | 100.00 |
| 2 | 李 | 四 | 22 | 120.00 |
| 3 | 王 | 五 | 21 | 90.00 |
+----+------------+-----------+-----+--------+
现要求显示id及月薪(30天制)
select id,salary*30 from user;
结果如下表:
+----+-----------+
| id | salary*30 |
+----+-----------+
| 1 | 3000.00 |
| 2 | 3600.00 |
| 3 | 2700.00 |
+----+-----------+
4.别名
现要求显示id及月薪(30天制),月薪命名为“month_salary”
select id,salary*30 month_salary from user;
或者
select id,salary*30 as month_salary from user;
as关键字可有可无
5.拼接
使用关键字concat()
要求显示id及员工全名(姓+名)
select id,concat(first_name,last_name) name from user;
结果:
+----+--------+
| id | name |
+----+--------+
| 1 | 张三 |
| 2 | 李四 |
| 3 | 王五 |
+----+--------+
6.distinct
该关键字可以将重复数据去除。
注意,distinct关键词只能放在select关键词后面
7.排序
格式:
select col_name,...
from tb_name
order by col_name [asc|desc]
显示员工信息并按月薪降序
select id,concat(first_name,last_name) name,age,salary*30 month_salary from user order by month_salary desc;
结果:
+----+--------+-----+--------------+
| id | name | age | month_salary |
+----+--------+-----+--------------+
| 2 | 李四 | 22 | 3600.00 |
| 1 | 张三 | 20 | 3000.00 |
| 3 | 王五 | 21 | 2700.00 |
+----+--------+-----+--------------+
8.条件查询
where 条件
显示月薪大于等于3000的员工信息并按月薪降序
select id,concat(first_name,last_name) name,age,salary*30 month_salary from user where salary>=100 order by month_salary desc;