MySQL
1. 简介
SQL:美国国家标准化组织的标准化计算机语言,可以用于访问和操作数据库,可以操作所有关系型数据库
- 创建/ 删除/ 查看数据库
- 创建/ 删除/ 查看/ 修改表
- 新增/ 修改/ 删除/ 查询记录等
- 视图/ 索引/ 存储过程等
注意: mysql区分`` 和 ‘’,字段名使用反引号,注释和插入数据使用单引号’’;
连接数据库:
- 管理员身份启动cmd窗口
- 输入
net start mysql
- 输入
mysql -uroot -p
- 输入密码: ******
- \c 终止输入
- 查看数据库版本
select version()
;
添加注释:
- /* 这是注释内容*/
- – 这是注释内容
- #键加内容
撤回语句: \c (\c可以取消语句的执行)
2. mysql的数据类型
(1) 数值类型:
-
tinyint
: 占用1个字节,相对于java中的byte -
smallint
: 占用2个字节,相对于java中的short -
int
: 占用4个字节,相对于java中的int,最长11 -
biging
: 占用8个字节,相对于java中的long----- 浮点型 -----
-
float
: 4字节单精度浮点类型,相对于java中的float -
double
: 8字节双精度浮点类型,相对于java中的double
跟java中一样,常用到int 和 double 类型;
(2) 字符串类型:
-
char(n)
: 定长字符串,最长255个字符,n表示最大长度,例如:— 创建一个user表,指定用户名为char类型,字符长度不超过10:
create table user( username char(10), ... );
定长,指的是当插入的值长度小于指定长度n,剩余空间会被空格补全(这样会浪费空间,适合存储长度固定的数据如身份证号,这样存储效率会高一些且不会造成空间浪费),char类型存储速度较快
-
varchar(n)
: 变长字符串,n表示字符数,一般不超过255个字符,超过255一般使用text类型,例如:– 创建user表,指定用户名为varchar类型,长度不超过10
create table user( username varchar(10), ... );
不定长,指的是当插入的值小于指定的长度时,剩余的空间可以留给别的数据使用(节省空间)
-
大文本(长文本)类型
最长65535个字节,一般超过255个字符列的会使用text。(在utf-8编码中一个汉字=3个字节,65535字节等于两万多字符)
– 创建user表:
create table user(
resume text
);
text分为多种,其中bigtext(clob)存储数据的长度约为4GB;
(3) 日期类型
date
: 年月日time
: 时分秒datetime
: 年月日 时分秒timestamp
: 时间戳(实际存储的是一个时间毫秒值),与datetime存储日期格式相同。两者的区别是:- timestamp最大表示2038年,而datetime范围是1000~9999
- timestamp在插入数,修改数据时,可以自动更新成系统当前时间——例如获取用户注册时间
(4) 其他类型
clob
: 字符大对象,最多可以存储4GB的字符串,超过255个字符都要采用CLOB字符大对象来存储,一般存储文字类型,Character Large OBjectblob
:二进制大对象,Binary Large OBject,专门用来存储图片、声音、视频等流媒体数据。往其中插入数据的时候需要使用IO流的形式。
3. 创建、删除、查看数据库
S Q L 语 句 对 大 小 写 不 敏 感 , 推 荐 关 键 字 使 用 大 写 , 自 定 义 的 名 称 ( 库 名 , 表 名 , 列 名 等 ) 使 用 小 写 \textcolor{red}{SQL语句对大小写不敏感,推荐关键字使用大写,自定义的名称(库名,表名,列名等)使用小写} SQL语句对大小写不敏感,推荐关键字使用大写,自定义的名称(库名,表名,列名等)使用小写
在插入数据之前,先设置编码: set names gbk;(一个窗口只需要设置一次,新开窗口需要重新设置);
creat database mydb1;
CREAT DATABASE MYDB1; 没有区别
(2)自定义名称时,针对多个单词不使用驼峰命名,而是使用下划线连接;
(例如:tab_name,而不是tabName)
早期mysql数据会全部变成大写,驼峰命名可读性不高,例如(userName会变成USERNAME)
(3)语法:
-
查看mysql服务器中所有数据库 ——
show databases
; -
进入某一数据库(进入数据库后才能操作库中的表和表记录)—— 语法:
use 库名
; -
查看已经进入的库 ——
select database()
; -
查看当前数据库中的所有表 ——
show tables
; -
删除数据库 ——
drop database 库名
; -
当删除的库不存在时,如何避免错误产生 ——
drop databese if exists 库名
; -
创建库,重新创建库名为mydb1的库,指定编码为utf8 ——
create database 库名 charset 编码
; -
如果不存在则创建mydb1 ——
create database if not exists 库名 charset 编码
; -
查看建库时的语句(并验证数据库使用的编码) ——
show create database 库名
;
4. 创建、删除、查看表
-
删除表(如果删除的表不存在会报错) —— drop table 表名;
-
删除表(如果存在就删除,不存在则跳过这条语句)—— drop table if exists 表名;
-
创建表,建表语法:
create table 表名(
列名 数据类型,
列名 数据类型,
....
列名 数据类型
);
例: 创建学生表stu
-
查看表结构 —— desc 表名;
-
查看表中数据 —— select * from 表名; (*是通配符)
5. 新增、更新、删除表记录
-
往学生表(stu)中插入记录(数据)—— insert into 表名(列名1,列名2,列名3, …)values(值1,值2,值3, …);
insert into stu values(1,'张翼德','男','1970-10-1',40);
Tips:
-
当为所有列插入值时,可以省写列名,但值的个数和顺序必须和声明列时的个数和顺序保持一直!
-
SQL语句中的值为字符串或日期时,值的两边要加上单引号(有的版本的数据库双引号也可以,但推荐使用单引号)。
-
在插入数据之前,先设置编码: set names gbk;(一个窗口只需要设置一次,新开窗口需要重新设置);
或者用以下命令连接mysql连接服务器:
mysql --default - character - set=gbk -uroot -p(在连接数据库的同时指定编码类型为gbk)
-
-
查询stu表中所有学生的信息 —— select 列名 | * from 表名;
select * from stu;
-
修改表中所有学生的成绩,加10分特长分—— update 表名 set 列=值,列=值,列=值…[where子句]; (where子句用于筛选,符合条件的参与修改,不加where默认修改全部数据)
update stu set score=score+10;
-
修改stu表中编号为1的学生成绩,将成绩改为80;
update stu set score=80 where id = 1;
where子句用于对记录进行筛选,保留符合条件的记录,将不符合条件的记录剔除。
-
删除stu表中的所有记录 —— delete from 表名 [where条件]
delete from stu;
– 仅删除符合条件的 (使用where筛选)
delete from stu where id > 1 ;
6. 查询表记录
查询这一块内容较多,分为两篇文章来叙述,本篇介绍基础查询,详情请翻阅下一篇;
(1)基础查询
select语句用于从表中选取数据,结果被存储在一个结果表中(结果集)
语法: select 列名称 * 表名;
- *号为通配符,表示查询所有列
- *号会把不必要的列也查询出来,且效率不如直接指定列名
– 查询所有emp中所有列:
select * from emp;
– 查询emp中的所有员工姓名,薪资,奖金
select name,sal,bouns from emp;
– 查询emp表中的所有部门和职位
select dept,job from emp;
– 剔除重复部分,只显示所有内容不完全一样的一整行(在select之后,列名之前使用distinct)
select distinct dept,job from rmp;
(2)where子句查询
where字句查询语法: select 列名 from 表名 where 列 运算符 值;
可使用运算符:
操作符 | 描述 |
---|---|
= | 等于 |
<> 或 != | 不等于 |
> | 大于 |
< | 小于 |
>= | 大于等于 |
<= | 小于等于 |
BETWEEN…ADN… | 在某个范围内(左小右大) |
in | in后面跟具体的值,不是某个区间 |
LIKE | 搜索某种模式 |
-
查询emp表中薪资大于3000的所有员工的姓名,薪资
select name,sal from emp where sal>3000;
-
查询emp表中总薪资(薪资加奖金)大于3500的员工姓名,显示姓名,总薪资;
select name,sal+bonus from emp where (sal+bonus)>3500;
– ifnull(列,值)函数: 判断指定的列是否包含null,如果有null值,用第二个值替换null; ——先将bonus中的null替换为0,再于sal列的值相加;
select name,sal+ifnull(bonus,0) from emp where sal+bonus>3500;
-
– 查看查询结果时,修改表头显示的列名: 列名 as 别名 —— 例如:name as 姓名,as可省略不写,但需保留空格;
select name as 姓名,sal+ifnull(bonus,0) as '总薪资' from emp where sal+ifnull(bonus,0)>3500;
注: where查询中不能使用列别名;
-
– 查询emp表中薪资在3500和4500之间的员工姓名,薪资;
select name,sal from emp where sal>=3000 and sal<=4500;
提示: between…and… 在…之间
select name,sal from emp where sal between 3500 and 4500;
-
查询emp表中薪资为1400,1600,1800的员工姓名和薪资;
select name,sal from emp where sal=1400 or sal=1600 or sal=1800;
或者使用in():
select name,sal from emp where sal in(1400,1600,800);
-
查询薪资不是1400,1600,1800的员工姓名,薪资;
select name,sal from rmp where sal!=1400 and sal!=1600 and sal!=1800;
或者使用not()或者not in():
select name,sal from emp where not(sal=1400 or sal=1600 or sal=1800); #not() select name,sal from emp where sal not in(1400,1600,1800); #not in()
-
查询emp表中薪资大于4000和薪资小于2800的员工,显示员工姓名,薪资;
select name,sal from emp where sal<2800 or sal>4000;
-
查询emp表中薪资大于3000并且奖金小于600的员工,显示姓名,薪资,奖金
select name,sal,bonus from emp where sal>3000 and ifnull(bonus,0)<600;
-
查询没有部门的员工,显示姓名,部门; — null不能直接和变量用=连接,中间用is,在sql语句中,null代表什么也没有,不是一个值
select * from emp where dept is null;
-
查询有部门的员工(部门列不为null)— 在where后面取反(添加not),或直接使用is not null;
select * from emp where not dept is null;
(3) 模糊查询
输入某个字符,查询包含此字符的内容;
可以和通配符%和_配合使用,%表示0或多个任意的字符,下划线表示任意一个字符。——语法: select 列名 from 表名 where 列名 like 值; ——值用单引号圈住
%和_有特殊含义,若查询内容中包含这两个字符,需添加转义字符\
注意:
- %表示任意多个字符,例如
%李
表示李前面可以有任意多个字符(包括0个),一号李、天神下凡李、宇宙救星小杜李等都在%李
的查询范围内 - _ 表示一个字符,如
李_
, 李白,李黑、李子等都在其包含范围内,但只能有一个字符
-
查询emp表中姓名包含涛字的员工,显示姓名;
select name from emp where name like '%涛%';
-
查询emp表中姓名以刘开头的员工,显示姓名;
select name from emp where name like '刘%';
-
查询emp表中姓名以刘开头,且名字只有两个字的员工,显示姓名;
select name from emp where name like '刘_';
(4) 多行函数查询
多行函数也叫聚合(聚集函数),根据某一列或所有列进行统计;
多行函数使用时自动忽略字段中的null(不会统计)
多行函数在查询时,如果没有分组,默认j将整张表按照一组来进行统计,如果有分组,分了几组就会按照多少组进行统计(按组统计)
常见多行函数有:
- count(列名|*): 统计结果集中某一列的行数;
- max(列名): 统计某一列中的最大值
- min(列名): 统计某一列中的最小值
- sum(列名): 统计某一列中所有值的和
- avg(列名): 统计某一列中值的平均值
多 行 函 数 不 能 用 在 w h e r e 子 句 中 \textcolor{red}{多行函数不能用在where子句中} 多行函数不能用在where子句中
-
统计emp表中薪资大于3000的员工个数;
select count(id) from emp where sal>3000; #可以统计全部列 select count(*) from emp where sal>3000;
-
求emp表中的最高/最低/总和/平均薪资
select max(sal) from emp; #最高 select min(sal) from emp; #最低 select sum(sal) from emp; #总和 select avg(sal) from emp; #平均
-
统计emp表中所有员工的薪资总和(不包含奖金)
数据处理函数(单行处理函数)
单行处理函数特点: 一个输入对应一个输出
和单行处理函数相对应的是: 多行处理函数( 多个输入对应一个输出)
常见包括:
操作符 | 描述 |
---|---|
upper(variable) | 转换大写 |
lower(variable) | 转换小写 |
substr(variable,起始位置,截取长度) | 取子串 |
length(variable) | 取长度 |
trim(variable) | 去空格 |
str_to_date | 格式化日期(将日期转化为字符串) |
concat(str1,str2) | 拼接字符串 |
format | 设置千分位 |
round(variable,保留位数) | 四舍五入 |
rand() | 生成随机数 |
ifnul(variable,替换值) | 可以将null转换为一个具体值 |
case…when…then…when…then…else…end | 处理匹配项 |
substr截取字符串: substr(被截取的字串,起始下标,截取长度),起始下标从1开始;
找出emp表中以A为姓名开头的员工信息
#方式一: 模糊查询
select name from emp where name like 'A%';
#方式二:substr函数
select name from emp where substr(name,1,1) = 'A';
concat拼接字符串: concat(字符串1,字符串2)
获取emp表中的所有员工名字,首字母大写:
#首字母大写:
select upper(substr(name,1,1)) from emp;
#获取除首字母外的其余字母:
select substr(name,2,length(name)-1) from emp;
#获取首字母大写的员工名:
select concat(upper(substr(name,1,1)),substr(name,2,length(name)-1)) as Name from emp;
round四舍五入: round(variable,保留位数)
- 保留位数为0: 保留整数
- 正数: 保留小数位数
- 负数: 保留整数位数,从小数点往左
ifnull,在sql语句中,只要有null参与的运算,结果一定为null,为了避免这种情况,必须使用ifnull替换,ifnull(variable,替换值);
case…when…then…when…then…else…end: 处理匹配的数据
如果工作岗位是manager,工资上调10%,如果是salesman,上调50%,其余不变
select name,job,sal as oldsal,(case job when 'manager' then sal*1.1 when 'salesman' then sal*1.5 else sal end) from emp;
(5) 分组查询
group by语句根据一个或多个列对结果集进行分组
在分组的列上可以使用count,sum,avg,max,min等函数(在列名处使用)
语法: select 列|* from 表名 [where子句] group by 列;
w h e r e 子 句 应 放 在 分 组 之 前 \textcolor{red}{where子句应放在分组之前} where子句应放在分组之前
h a v i n g 子 句 应 放 在 分 组 之 后 \textcolor{blue}{having子句应放在分组之后} having子句应放在分组之后
- 对emp表按照部门对员工进行分组
select * from emp group by dept;
-
对emp表按照职位对员工进行分组,并统计每个职位的人数,显示职位对应的人数
select job,count(*) from emp group by job;
-
对emp表按照部门进行分组,显示每个部门的最高薪资和对应部门名称
select dept,sal,max(sal) from emp group by dept;
(6) 排序查询
语句执行顺序:
- from
- where
- select
- order by(排序总是在最后执行)
使用order by子句将结果集根据指定的列排序后再返回
语法: select 列名 from 表名 order by 列名[asc|desc]
asc(默认)升序 desc 降序
-
对emp表中所有员工薪资进行升序排序,显示员工姓名,薪资
select name,sal from emp order by sal asc;
-
对emp表中所有员工奖金进行降序排序,显示员工姓名,奖金
select name,bonus from emp order by bonus desc;
- 可以多个字段共同排序,比如查询员工薪资升序排列,薪资相同按照名字升序排列
select name,sal from emp order by sal asc,name asc;
起主导作用的使order by后的第一个字段,优先级一次往后。
了解:
根据字段的位置也可以排序
select name,sal from emp order by 2;
按照查询结果的第二列sal排序
select name,sal from emp where sal between 1250 and 3000 order by sal desc;
(7) 分页查询
在mysql中,通过limit进行分页查询;
limit用法 :limit startindex,length
limit(页码-1)*每页显示记录数,每页显示记录数;
limit(前几条数据)
在order by后面执行
-
查询emp表中的所有记录,分页显示: 每页显示3条记录,返回第一页;
select * from emp limit 0,3; #第一页,每页显示3条记录 select * from emp limit 3,3; #第二页,每页显示3条记录
(8) 其他函数查询
- curdate() 获取当前日期 ——年月日
- curtime() 获取当前时间 ——时分秒
- sysdate()获取当前日期+时间 ——年月日 时分秒
- now()获取当前日期+时间 —— 年月日 时分秒
- year(date) 返回date中的年份
- month(date) 返回date中的月份
- day(date)返回date中的天数
- hour(date)返回date中的小时
- minute(date)返回date中的分钟
- second(date)返回date中的秒
- concat(s1,s2…)s1,s2等多个字符串拼接合并为一个字符串
- concat_ws(x,s1,s2…)同concat(s1,s2…)函数,但是每个字符串之间要加上x分隔符;
-
查询emp表中所有在1993和1995年之间出生的员工,显示姓名和出生日期
select name,birthday from emp where year(birthday)>=1993 and year(birthday)<=1995;
-
查询emp表中本月过生日的所有员工
select name,birthday from emp where month(birthday)=month(curdate());
-
查询emp表中所有员工的姓名和薪资(薪资格式为xxx(元))
select name,concat(sal,'(元)') from emp;
如果同时存在where 分组 排序 分页,按照where [分组] [排序] [分页]的顺序书写