连接客户端时通过参数指定
shell>mysql -uroot -proot --prompr 提示符
连接上客户端后,通过prompt命令修改
mysql>prompt 提示符
显示当前服务器版本
SELECT VERSION();
+------------+
| VERSION() |
+------------+
| 5.7.16-log |
+------------+
显示当前日期时间
SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2020-05-23 17:19:26 |
+---------------------+
1 row in set (0.01 sec)
显示当前用户
SELECT USER();
+----------------+
| USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
函数:
select database()
现在在哪一个库中;
desc stunifo;
看表中的结构;
show tables from 库名
查看其他库中的表
use 库名
打开指定的库
创建表
create table 表名(
列名 列类型
)
update stuinfo set name=‘hejia’ where id=1;
更新
insert into stuinfo(id,name) values(1,
‘join’)
;
添加
select * from stuinfo;
查找
基础查询
#查询常量
select 100 ;
#查询表达式
SELECT 100%3;
#查询单个字段
SELECT last_name FROM employees;
#查询多个字段
SELECT
last_name,
email,
employee_id
FROM
employees;
#查询所有字段
SELECT
*
FROM
employees;
# 起别名
SELECT USER();
# 方式1使用as
SELECT USER() AS HJ;
# 方式2使用空格
SELECT USER() HJD;
#使用concatpinjie
SELECT CONCAT(first_name,last_name) AS '姓名'
From employees;
# DISTINCT的使用去重
SELECT DISTINCT department_id FROM employees;
#若表中某个数据可能为null则放入ifnull
SELECT IFNULL( ,'空')
1 mysql在windows不区分大小写,在linux中要区分,但建议关键字大写。
2 每条命名用分号结尾
3 注释
单行注释 #注释文字
单行注释 – 注释文字
多行注释 /* */
回来
特点
- 查询的结果集是一个虚拟表
- select 类似于system.out.println
select后面跟的查询列表,可以有多个部分组成,中间用逗号隔开
例如 select 字段1 字段2 表达式from表;
sout的打印内容,只有一个。 - 执行顺序
select first_name from employees;
先去from查看表,再去select筛选。 - 查询列表可以是:字段、表达式、常量、函数。
- mysql中的+
1.加法运算
两个操作数都是数值型
100+1.5
其中一个操作数为字符型,将字符型数据强制转换成数值型,如果无法转换,则直接当成0处理
其中一个为null 则结果为null
条件查询
select 查询列表
from 表名
where 筛选条件
执行顺序
1.from语句
2.where语句
3.select语句
特点:按条件表达式筛选
关系运算符:> < >= <= <>
!=也可使用,但不建议
逻辑运算符:and or not
3.模糊查询
like
in
between and isnull
like
功能 一般和通配符搭配使用,对字符型数据进行部分匹配查询常见的通配符:
_任意单个字符
%任意多个字符
若想查询的字符包括_或%
SELECT *
FROM employees
WHERE last_name like '_$_%'ESCAPE '$';
同理还有not like
in
功能:查询某字段的值是否属于指定的列表之内
a in(常量1,常量2,常量3,…)也不一定非要是常量
如我想查询id为30 ,60 ,70
SELECT id
FROM employees
WHERE id IN(30,60,70);
同理还有not in;
3.between and
功能:判断某个字段的值是否介于XX之间
SELECT department_id
FROM employees
WHERE id BETWEEN 30 ADN 90;
不可颠倒
排序查询
语法
select 查询列表
from 表名
where 筛选条件
order by 排序查询
执行顺序:
- from
- where
- select
- order
特点:
1.排序列表可以是单个字段、多个字段、表达式、函数、列数、以及以上的组合。
2升序 通过asc ,默认行为
降序 通过desc
# 按单个排序
SELECT
*
FROM
employees
WHERE
employee_id > 120
ORDER BY
salary DESC;
# 按表达式排序对有奖金的员工,按年薪排序
SELECT
*,
salary * 12 *(
1 + IFNULL( commission_pct, 0 )) 年薪
FROM
employees
ORDER BY
salary * 12 *(
1 + IFNULL( commission_pct, 0 )) DESC;
#按别名排序
SELECT
*,
salary * 12 *(
1 + commission_pct
) 年薪
FROM
employees
WHERE
commission_pct IS NOT NULL
ORDER BY
年薪 DESC;
#按函数的结果排序
#案例1 按姓名的字数长度进行升序
SELECT
last_name
FROM
employees
ORDER BY
LENGTH( last_name );
# 按多个字段排序
-- 查询员工的姓名、工资、部门编号。先按工资排序,再按部门编号排序
SELECT
salary,
employee_id
FROM
employees
ORDER BY
salary ASC,
employee_id DESC;
#按列数排序
SELECT
*
FROM
employees
ORDER BY
2;
数据库相关概念
DB:database数据库,存储一系列有组织数据的容器
DBMS:Database Management System数据库管理系统,使用DBMS管理和维护DB
SQL:StructureQueryLanguage结构化查询语言,程序员用于和DBMS通信的语言。
数据库存储数据的特点
- 数据先放在表中,表再放在库中
- 一个库可以有多张表,每张表都有自己唯一的标识名
- 一张表的设计,类似于java中的类的设计 表中的字段的设计,类似于属性的设计
表中的单条记录,类似于对象
表中的所有记录类似于对象的集合
orm: object relation mapping 对象关系映射
mysql特点
- 体积小、安装较方便
- 开源、除企业版免费
- 性能好,稳定性高
- 兼容性好
常见的函数
函数 相当于java中学过的方法,为了解决某个问题,将编写的一系列的命令集合封装在一起,对外仅仅暴露方法名,供外界使用。
- 自定义方法(函数)
- 调用方法(函数)
叫什么:函数名
干什么:函数功能
常见函数:
字符函数
数学函数
日期函数
流程控制函数 - concat 拼接字符
- LENGTH 获取字节长度
SELECT LENGTH(‘hello,hejia’) - CHAR_LENGTH 获取字符个数
- SUBSTRING 截取子串
sql中的起始索引从1开始,
SELECT SUBSTR(str,起始索引,截取的字符长度)
有重载substr(str,起始索引) - insert获取字符第一次出现的索引
SELECT INSERT(‘三打白骨精啊啊啊、白骨精’,‘白骨精’) - trim 去前后指定的字符 默认空格
SELECT TRIM(’ 虚 竹 ');
SELECT TRIM(‘x’ FROM ‘xxxx虚竹xxxx’) - LPAD/RPAD 左填充/右填充
SELECT LPAD(‘木婉清’,10,‘a’); - UPPER/LOWER 变大写/变小写
- STRCMP 比较两个字符大小
- LEFT/RIGHT 截取子串
数学函数 - ABS 绝对值
SELECT ABS(-2.4); - CEIL 向上取整
SELECT CEIL(1.09); - FLOOR 向下取整
- ROUND 四舍五入
- TRUNCATE 截断
SELECT TRUNCATE(1.8712345,1);
1.8 - MOD 取余
SELECT MOD(-10,3);
日期函数
1.NOW
SELECT NOW(); - CURDATE
SELECT CURDATE(); - CURTIME
SELECT CURTIME(); - DATEDIFF
SELECT DATEDIFF(‘1998-7-16’,‘2019-7-13’)日期之差 前减去后 这个出来就是负数 - DATE_FORMAT(‘1998-7-16’,’%Y年’)
分组函数(聚合函数)
说明:
分组函数往往用于实现一组数据进行统计计算,最终得到一个值,又称为聚合函数或统计函数
分数函数
- sum(字段名)
将该字段所有值求和 - avg(字段名)
求平均数 - max
- min
- count 计算非空字段个数
count的补充介绍
SELECT COUNT()
直接用于该结果的总行数
select count()from xxx where xxx;
则count用于筛选后
SELECT count(1)等于每一行加了个常量列 和效果一样但更好(效率高,语义好)
搭配distinct就可实现去重
分组查询
当需要分组查询时需要使用GROUP BY 子句,例如查询每个部门的工资和,这说明要使用部分来分组
select 查询列表
from 表名
where 筛选条件
group by 分组列表;
特点
- 查询列表往往是,分组函数和被分组的字段
- having用作分组前筛选
- 执行顺序:
sql 92语法
- 内连接
等值连接
select 查询列表
from 表名1 表名2
where 等值连接的连接条件
特点
为了解决多表中的字段重名问题,往往为表起别名
表的顺序无要求
起了别名 表名就无法查询(查询顺序from在前)
若有筛选条件可直接在where上AND
非等值
将等于符号改作其他符号
例如
想查询员工的工资和工资级别
SELECT salary,grade_leval
FROM emplyees e, job_grades g
WHERE salary BETWEEN g.lower_sal AND g.highest_sal;
自连接
列如在员工中找领导 第一次是当中员工表 第二次当做领导表
SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e,employees m
where e.manager_id=m.employee_id;
截图
:
sql99语法
内连接
等值连接
select 查询列表
from 表1 别名
(inner) join 表2别名
on 连接条件
where 筛选条件
外连接
应用查询 :用于查询一个表中有,另外一个表中没有
特点
- 外连接的查询结果为主表中所有记录
如果从表中有和它匹配的,则显示匹配的值
如果从表中没有和它匹配的,则显示null
外连接查询结果=内连接结果+主表中有从表中没有 - 左外连接:left左边的是主表
右外连接 right join 右边的是主表 - 左外和右外交换两个表的顺序,可以实现同样的效果
SELECT b.name,bo.*
FROM beauty b
left JOIN boys bo
ON b.boyfriend_id=bo.id;
全外查询
内连接+表1中有表2无+表2有表1中无
full outer
mysql不支持
交叉连接
使用cross 结果是笛卡尔乘积
子查询
含义
出现在其他语句中的select语句,称为子查询或内查询
外部的查询语句,称为主查询或外查询
分类
按子查询出现的位置
select后面
仅仅支持标量子查询
from后面
支持表子查询
where或having后面
支持标量子查询
列子查询 (多行子查询)
行子查询
特点
子查询放在小括号内
子查询一般放在条件的右侧
标量子查询,一般搭配单行操作符
列子查询,一般搭配着多行操作符
in ,any/some all
in/not in 等于列表中的任意一个
any|some 和子查询返回的某一个值比较
例如a>any(10,20,30) 只要大于其中任一个
All 和子查询返回的所有值比较
行子查询(结果一行多列,或则多列多行)
筛选条件都为一样的操作符
exists后面(相关子查讯)
按结果集的行列数不同:
标量子查询(按结果集只有一行一列)
列子查询(结果集只有一列多行)
行子查询(结果集有一行多列)
表子查询(结果集)
# 表
创建表:
CREATE TABLE 表名(
字段名 字段类型 【字段约束】,
字段名 字段类型 【字段约束】,
…
);
a) 修改之添加列:给stus表添加classname列:
ALTER TABLE stu ADD (classname varchar(100));
b) 修改之修改列类型:修改stu表的gender列类型为CHAR(2):
ALTER TABLE stus MODIFY gender CHAR(2);
c) 修改之修改列名:修改stu表的gender列名为sex:
ALTER TABLE stus change gender sex CHAR(2);
d) 修改之删除列:删除stsu表的classname列:
ALTER TABLE stus DROP classname;
e) 修改之修改表名称:修改stu表名称为student:
ALTER TABLE stus RENAME TO student;
主键
当某一列添加了主键约束后,那么这一列的数据就不能重复出现。这样每行记录中其主键列的值就是这一行的唯一标识。例如学生的学号可以用来做唯一标识,而学生的姓名是不能做唯一标识的,因为学习有可能同名。
主键列的值不能为NULL,也不能重复!
指定主键约束使用PRIMARY KEY关键字
创建表:定义列时指定主键:
CREATE TABLE stu(
sid CHAR(6) PRIMARY KEY,
sname VARCHAR(20),
age INT,
gender VARCHAR(10)
);
创建表:定义列之后独立指定主键:
CREATE TABLE stu(
sid CHAR(6),
sname VARCHAR(20),
age INT,
gender VARCHAR(10),
PRIMARY KEY(sid)
);
修改表时指定主键:
ALTER TABLE stu ADD PRIMARY KEY(sid);
删除主键(只是删除主键约束,而不会删除主键列):
ALTER TABLE stu DROP PRIMARY KEY;