@Description:
- @Author: Rainbow
- @version:v1.0
- @date:2021年6月5日下午9:26:45
- MySQL 关系型数据库,就是一张一张存放数据的表。现在是oracle 的产品。
- 导航猫 Navicat :graphical database management and development software.
- 图形化数据管理开发软件 支持:MySQL oracle MongoDB 等
- MySQL 语句:
- MySQL默认端口号:3306
- 0.命令行登录数据库:mysql -u root -p pass word:root
- 1.显示数据库: show databases;
- 2.进入test 数据库:use test;
-
查询: select
-
SELECT * FROM `company` SELECT * FROM school WHERE class <> 2 //不等于2班的 SELECT * FROM company WHERE salary=2000 SELECT *FROM company WHERE salary >=1000 and salary <=3000 //薪水区间 SELECT DISTINCT teacher FROM school //distinct 字段去重
-
SELECT * FROM company WHERE employee is NULL // where 条件 SELECT * FROM company WHERE salary in(10,2000) // 10-2000范围
-
SELECT * FROM school WHERE teacher in ('a','b') // a 和 b 老师
-
SELECT * FROM school WHERE teacher LIKE '%a%' //名字里带a 的串
-
增加:
-
create tables 创建表
-
删除:
-
修改:
-
排序: order by
-
SELECT * FROM company ORDER BY salary //按薪水排序
-
SELECT * FROM company WHERE salary <> 2000 ORDER BY salary DESC // order by 和 where 的混用。先where对数据进行过滤,然后再进行升序或者降序排列 // 这是薪水不是2000 的薪水排序
-
// ascending order 升序排序-ASC descending order 降序排序-DESC
-
SELECT * FROM company ORDER BY salary DESC //按降序排列
-
函数 select lower() 小写
-
SELECT LOWER(employee) FROM company //员工小写
-
SELECT UPPER(employee) FROM company // upper 大写
-
SELECT CURRENT_DATE //获取当前日期的函数
-
SELECT CURRENT_TIME // 当前时间 13:11:21
-
SELECT NOW() // 日期加时间
-
其他函数去oracle 文档里查。
-
组函数
-
SELECT MAX(salary) FROM company //取出最大的薪水
-
SELECT employee FROM company WHERE salary=(SELECT MAX(salary) FROM company)
-
// 取出薪水最多的人是谁。
-
SELECT min(salary) FROM company // 取最小
-
SELECT avg(salary) FROM company // 取平均值
-
SELECT sum(salary) FROM company // 取总和
-
SELECT count(*) FROM company // count 计算总共的记录条数
-
分组 group by
-
SELECT max(salary) FROM company GROUP BY employee
-
// group by 通过...分组 选出最大薪水,按人名排序。
未完待续…