MySQL学习系列（一）

MySQL简介

创始人芬兰人，2009年以10亿美金MySql卖给Sun公司

1年后，Sun被Oracle收购

MySql不被Oracle重视，开发社区被收缩，开发进度缓慢

开源社区认为MySql存在闭源风险

MySql创始人，在MySql源码基础上，开了一个新的分支 MariaDB

1.mysql客户端

1.连接本机服务器，登陆服务器

mysql -uroot -p
[Enter password]

2.查看数据库

show databases;

3.进入数据库

use 数据库名;

4.查看数据库表

show tables;

5.查看表结构

desc 表名;

6.退出登录\断开连接

exit;
quit;
\q

2.建库、建表

建库

-- 删除db1库
drop database if exists db1;
-- 重新创建db1库
create database db1 charset utf8;
-- 查看、进入db1库
show databases;
use db1;

建表

-- 删除stu学生表
drop table if exists stu;
-- 创建stu表
create table stu(
   id int,
   name varchar(20),
   gender char(1),
   birthday date
);
-- 查看表结构
desc stu;

3.数据类型

数字

数据类型	相关描述
unsigned	无符号，只有正数
zerofill	配合显示位数，不足补0
tinyint
smallint
int	查询时不足位数按规定位数显示
bigint
float	单精度
double	双精度，运算不精确
descimal

字符串

数据类型	相关描述
char	1.定长字符串，存储访问效率高 2.字符串长度不足，补空格 3.超出部分根据数据库设置，可能出错也可能截断4.最长255个字符
varchar	1.变长字符串，存储访问效率比char低 2.最长不超过65535个字节 3.一般超过255个字节，使用test类型
test	长文本类型，最长65535字节

日期时间

日期类型	相关描述
date	格式(年月日)
time	格式(时分秒)
datatime	格式(年月日时分秒)
timestamp	1.时间戳 2.最大表示2038年 3.在插入数据、修改数据时，自动更新系统当前时间

4.sql入门

sql:Structured Query Language是一种结构化的查询语言

sql类型	描述	作用
DDL	数据定义语言	CREATE,DROP,ALTER
DML	数据操作语言	INSERT,UPDATE,DELETE
DQL	数据查询语言	SELECT
DCL	数据控制语言	GRANT,REMOVE ...
TCL	事务控制语言	COMMIT,ROLLBACK...

中文编码

image

-- 把客户端编码告诉服务器，这样服务器可以做正确的编码转换(按自己PC编码设置)
set names gbk;

Insert:插入数据

-- 插入完整表数据
insert into stu values(1,'张三','男','1996-11-23');
-- 插入部分表数据
insert into stu (id,name) values(2,'李四');
--插入多条表数据
insert into stu (id,name) values(3,'王五'),(4,'赵六'),(5,'钱七');

-- 查询表数据
select * from stu;

Update:更新,修改数据

-- 把id为4,赵六的性别和生日修改成'nv','1998-8-8'
update stu set gender ='女',birthday ='1998-8-8';

Delete:删除数据

-- 删除id>4的数据
delete from stu where id>8;

Select:查询数据

-- 查询所有字段
select * from stu;
-- 查询指定字段
select name,gender from stu;

4.sql进阶

-- 准备测试数据
-- hr_mysql.sql(sql脚本数据,文末可获取相关资源)
-- 执行这个文本中的sql代码
source 拖曳脚本到dos窗口(脚本路径)

-- 查看表
show tables;
-- 员工表结构
desc emps;
-- 员工表数据
select * from emps;

select * fname,sal,dept_id from emps

where子句

过滤条件	相关描述
=	等值过滤
<>	不等过滤
> >= < <=
between 小 and 大	>= 小并且 <= 大
in(7,2,9,4)	在指定的一组值中取值
is null\is not null	是null\不是null
like	字符串模糊匹配(%,_ ,%,_,\)
not	not between and,not in(...),is not null,not like
and	并且
or	或者

-- 员工id是122
select id,fname,sal,dept_id from emps where id=122;
-- 部门编号dept_id是30
select id,fname,sal,dept_id from emps where dept_id=30;
-- 工作岗位代码job_id是'IT_PROG'
select id,fname,sal,dept_id,job_id from emps where job_id='IT_PROG';

-- 部门编号dept_id 不是 50
select id,fname,sal,dept_id from emps where dept_id<>50;

-- 工资sal>5000
select id,fname,sal,dept_id from emps where sal>5000;

-- 工资范围[5000,8000]
select id,fname,sal,dept_id from emps where sal>=5000 and sal<=8000;
--
select id,fname,sal,dept_id from emps where sal between 5000 and 8000;

--id 是(120,122,100,150)
select id,fname,sal,dept_id from emps where id in (120,122,100,150);
--
select id,fname,sal,dept_id from emps where id=120 or id=122 or id=100 or id=150;

-- 没有部门的员工,dept_id 是 null
select id,fname,sal,dept_id from emps where dept_id is null;
-- 有提成的员工,com_pct 不是 null
select id,fname,sal,dept_id from emps where com_pct is not null;

-- fname中包含en
select id,fname,sal,dept_id from emps where fname like '%en%';
-- fname第3,4个字符是en
select id,fname,sal,dept_id from emps where fname like '__en%';

distinct:去重

select distinct a from ... (去除a字段重复值)
select distinct a,b from ...(去除a,b字段组合的重复值)

-- 查询所有部门id
select distinct dept_id from emps where dept_id is not null;

order by 子句

排序(asc 升序[默认],desc 降序)

-- 查询50部门员工,按工资降序
select id,fname,sal,dept_id from emps where dept_id=50 order by sal desc;

--所有员工按部门升序，相同部门按工资降序
select id,fname,sal,dept_id from emps order by dept_id, sal desc;

5.查询执行顺序

select 字段
from
where
order by

where 过滤
选取字段
order by 排序

6.单引号

字符串内容中的单引号,用两个单引号去转义
'I'm ABC'
'I''m ABC'

use db1;
insert into stu(id,name) values(6422,'I''m Xxx');

select * from stu;

sql注入攻击
通过正在sql语句中,注入单引号,改变sql语句结构

select * from user where username='张三' and password = '1' or '1'='1';

select * from user  where username='chenzs'#' and password='';

防止sql和注入攻击(用户填写的内容中,所有单引号都变成两个单引号)

7.函数

字符串函数

函数	相关描述
char_length(str)	获取str字符数
length(str)	获取str字节数
left(str,length)	获取左侧字符
substring(str,start,length)	截取字符串str
instr(str,substr)	查询子串位置
concat(s1,s2,s3...)	字符串连接
lpad(str,8,'*')	左侧填充*

-- fname和lname首字母相同
select id,fname,lname,sal,dept_id from emps where left(fname,1)=left(lname,1);
--
select id,fname,lname,sal,dept_id from emps where substring(fname,1,1)=substring(lname,1,1);

-- fname和lname连接起来,并对其中间空格
select concat(lpad(fname,20,' '),' ',lname);

数字函数

函数	相关描述
ceil(数字)	向上取整到个位
floor(数字)	向下取整到个位
round(数字,2)	四舍五入到小数点2位
truncate(数字,2)	舍弃到小数点2位
rand()	随机数[0,1)

-- 工资上涨11，31%,向上取整到10位
select id,fname,sal,ceil(sal*1.1131/10)*10 from emps;

-- 所有员工随机排序
select id,fname,sal,dept_id from emps order by rand();

日期函数

函数	相关描述
now()	当前日期时间
curdate()	当前日期
curtime()	当前时间
extract(字段 from 日期)	抽取指定字段的值
date_add(日期, interval 字段值)	在指定字段上加一个值
datediff(日期1,日期2)	两个日期之间相差的天数

-- 查询系统当前时间
select now();

-- 1997年入职员工
select id,fname,hdate from emps where extract(year from hdate)=1997;

-- 员工已入职多少年,并按入职时长升序排序
select id,fname,hdate,dateiff(now(),hdate)/365 y from emps order by y;

null值函数

ifnull(a,b)

a不是null返回a,a是null返回b

-- 所有员工年收入降序排序（年薪*提成,部分部门无提成项）
select id,fname,sal,sal*12*(1+ifnull(com_pct,0)) t from emps order by t desc;

多行函数,聚合函数

函数	相关描述
sum()	和
avg()	平均
max()	最大
min()	最小
count()	行数

多行函数不能和其他普通字段一起查询
多个多行函数可以一起查询
==多行函数会忽略null值==
count(*) 记行数
count(distinct a) 去除重复再计数

-- 最低工资值
select min(sal) from emps;

8.group by

按指定字段中相同的值进行分组
分组后分别求多行函数
分组字段，可以查询
group by a （按a字段相同值分组）
group by a,b（按a,b组合的相同值分组）

-- 每个部门的平均工资
select dept_id,avg(sal) from emps where dept_id is not null group by dept_id;

-- 每个工作岗位job_id的人数
select job_id,count(*) from emps group by job_id;