1.查看数据库
show databases; -- 查看数据库
show schemas; -- schemas也是可以查看数据库 
2.进入数据数据库
-- 进入test数据库
use test;
-- 进入mysql数据库
use mysql;
3.查看数据表列表
show tables; -- 查看数据表列表
4 .退出客户端 quit 或 \q
5 远程连接mysql
mysql用户是用“用户名@网络地址”来标识
root@localhost
只能在本机登录
root@192.168.18.219
只能从192.168.18.219指定的主机来连接
root@192.168.18.%
可以从18网段内任意主机来连接
root@%
可以从任意主机来连接
创建跨网络连接的用户
配置用户权限
mysql用户是用“用户名@网络地址”来标识
root@localhost
只能在本机登录
root@192.168.18.219
只能从192.168.18.219指定的主机来连接
root@192.168.18.%
可以从18网段内任意主机来连接
root@%
可以从任意主机来连接
创建跨网络连接的用户
create user 'abc'@'localhost' identified by '123456'
create user 'abc'@'192.168.0.68' identified by ''
create user 'abc'@'192.168.0.%' identified by ''
create user 'abc'@'%' identified by '' 配置用户权限
grant select,insert,update,delete on test.* to 'abc'@'%'
grant create,alter,drop on db1.* to 'abc'@'localhost'
grant all privileges on *.* to 'abc'@'%'
grant select,insert on db1.t1 to 'abc'@'%'
grant select(id, name) on db1.t1 to 'abc'@'%
创建夸网络访问的root用户
-- 创建用户'root'@'192.168.18.%'
create user 'root'@'192.168.18.%' identified by 'root';
-- 对用户授权
grant all privileges on *.* to 'root'@'192.168.18.%';
-- 连接mysql，对方要关闭防火墙
mysql -uroot -p -h192.168.地址 -P3306
5 中文问题
GBK “中”字节码 两个字节
UTF-8 ‘中’字节码 三个字节
服务器端需要知道客户端传过来的中文采用的是什么编码，服务器才能做正确的转码运算
客户端需要告诉服务器，自己发送的中文编码是什么编码
-- windows命令行使用GBK编码
set names gbk;
6 数据库管理
6.1 创建数据库
create database db1;
create database db2
charset utf8;
6.2 查看数据库
show databases;
-- 查看建库语句
show create database db1;
show create database db2;
6.3 修改数据库
只能修改数据库的默认字符编码
alter database db2 charset gbk;
show create database db2;
6.4 删除数据库
删除数据库不可恢复
drop database db2;
show databases;
7 数据表管理
7.1 创建表
-- 修改db1编码
alter database db1 charset utf8;
-- 进入db1库
use db1;
-- 创建表tb1
create table tb1(
name varchar(20),
gender char(1)
) engine=innodb charset=utf8;show tables;
7.2 查看表
show tables;
-- 描述describe，查看表的结构
desc tb1;
-- 查看建表语句
-- \G纵向显示
show create table tb1\G
7.3 修改表
7.3.1 改表名
rename table tb1 to user;
show tables;
7.3.2 改表的属性
改引擎（innodb或myisam），
改字符编码（GBK 或 utf8）
alter table user
engine=myisam charset=GBK;
show create table user\G
7.3.3 添加字段
first 加在第一个
after 加在指定字段的后面
alter table user
add id int first;
desc user;
alter table user
add email varchar(100) after name;
desc user;
alter table user add(
age int,
height decimal(3,2)
);
desc user;
7.3.4 修改字段名
-- gender该名成sex
alter table user
change gender sex char(1);
show tables;
desc user;
7.3.5 修改字段类型
alter table user
modify sex varchar(2);
show tables;
desc user;
7.3.6 修改字段顺序
first 第一个
after 指定字段之后
alter table user
modify email varchar(100) after id;
alter table user
modify age int after name;
desc user;
7.3.7 删除字段
删除字段，同时删除其中的数据，不可恢复
alter table user
drop height;
desc user;
7.4 删除表
drop table user;
-- 如果存在，删除
drop table if exists user;
show tables;
7.5 截断表、重建表
先删除表，再重新创建
truncate table user;
8 sql
结构化的查询语言
DDL 数据定义，建库建表
DML 数据操作，增删改
DQL 数据查询
8.1 insert 插入数据
-- 通知服务器，客户端使用的是什么编码
set names gbk;
use db1;
drop table if exists user;
create table user(
id int,
name varchar(20)
)engine=innodb charset=utf8;
show tables;
-- 向 user 表插入数据
insert into user values(1, '张三');
insert into user values
(2, '李四'), (3, '王五'),
(4, '赵六'), (7, '钱七');
-- 查询
select * from user;
8.2 update
修改数据
update user set
id=999, name='张三三'
where id=1;
select * from user;
8.3 delete
删除数据
delete from user
where id=4;
select * from user;
8.4 select
查询数据
select name from user;
select name,id from user;
select * from user where id>4;
8.5 术语
row ---------------行，一行数据
record -------------记录，一条记录，一行数据
column ------------列
field----------------字段，列
table---------------表
database----------库
schema------------库
9 数据存储引擎
9.1 innodb
默认存储引擎，多数情况下，都是用innodb
支持事务
支持外键
行级锁
9.2 myisam
不支持事务
不支持外键
表级锁
查询多，增删改少，可以使用myisam
9.3 memory
内存表
数据库重新启动，内存数据消失
10 数据类型
10.1 数字
unsigned 无符号，没有负数
zerofill 用0填充，结合位数设置使用
int(5) zerofill
查询结果中，显示成5位
不足5位前面补0,
超出5位，没效果
tinyint 1字节
smallint 2字节
int 4字节
bigint 8字节
float 4字节
double 8字节
decimal 定点数，精确地表示金额
decimal(m,n) 字节量m+2
数字类型测试
drop table if exists tb1;
create table tb1(
a int(3) unsigned zerofill,
b decimal(3,2)
)engine=innodb charset=utf8;
insert into tb1 values
(4231234, 1.72),(3, 5.7678);
insert into tb1 values(62, 754.88);
select * from tb1;
10.2 字符串
char(6)
定长字符串，最长不能超过255个字符
varchar(6)
变长字符串，最长不能超过65535，
如果长度<=255，需要1个额外字节来保存数据长度
如果长度>255，需要2个额外字节来保存数据长度
text
65535个字节
字符串测试
drop table if exists tb2;
create table tb2(
a char(6),
b varchar(6),
c text
)engine=innodb charset=utf8;
insert into tb2 values
('abcdef','abcdef','a'),
('阿宝拆的赋给','阿宝拆的赋给','b');
-- 超出长度报错
insert into tb2 values
('abcdefg','abcdefg','c');
10.3 日期
datetime
年月日时分秒
date
年月日
time
时分秒
timestamp
时间戳，年月日时分秒
时间范围只到2038年
第一个timestamp字段会随着其他字段的修改，自动更新为当前时间
插入数据时，会自动填入时间
timestamp测试
drop table if exists tb3;
create table tb3(
a int,
b timestamp,
c timestamp
)engine=innodb charset=utf8;
insert into tb3(a) values(1);
select * from tb3;
update tb3 set a=2;
select * from tb3;
update tb3 set c='2038-1-1'; 可以修改
update tb3 set c='2039-1-1'; 超出范围
11.商品表tb_item
-- 创建数据库
create database jtdb
charset utf8;
-- 进入jtdb
use jtdb;
-- 创建商品表tb_item
drop table if exists tb_item;
create table tb_item(
id bigint,
cid bigint,
brand varchar(50),
model varchar(50),
title varchar(100),
sell_point varchar(500),
price bigint,
num int,
barcode varchar(30),
image varchar(500),
status tinyint,
created datetime,
updated datetime
)engine=innodb charset=utf8;
show tables;
desc tb_item;
show create table tb_item;
12. 表字段的字节量限制(了解)
一张表中，所有字段的字节量之和，不能超过65535
如果表中存在允许null值的字段，需要一个额外字节来表示 null 值
varchar 255字节内，需要1个字节表示长度，大于255字节，需要2个字节表示长度
text 只占用10个字节的字节量限制
字节量限制测试
use db1;
drop table if exists tb1;
create table tb1( -- 1
a int, -- 4
b double, -- 8
c varchar(65520) -- 2+65520
)engine=innodb charset=latin1;
drop table if exists tb2;
create table tb2( -- 1
a int, -- 4
b double, -- 8
c varchar(21840) -- 2+21840*3
-- d tinyint -- 1
)engine=innodb charset=utf8;
drop table if exists tb3;
create table tb3( -- 1
a int, -- 4
b double, -- 8
c varchar(255), -- 2+255*3
d varchar(21584), -- 2+21584*3
e tinyint -- 1
)engine=innodb charset=utf8;
13. 约束
限制字段的取值
主键
外键
非空
唯一
检查
2.1 主键
一行数据的唯一标识
不能重复
不能为空
会创建索引
一般使用业务无关，无意义数据作为主键
自动增加的整数
随机字符串，UUID
按一定规则生成
存在 null 值，或有重复值
被其他外键引用时，不能删除
学生表
id（主键） 学号 姓名 性别
1 2008151129527 xx xx
2 2008151679528 xx xx
人员表
id 身份证号 姓名 ...
35a8f98c 35234523 xxxx
8f96c9de 324563456 xxxx
电信计费
id 手机号 金额 ..
1 32123235 -2 xx
2 345346 34 xx
2.1.1 添加主键
创建表时添加
修改表时添加
双主键
使用两个字段组合起来作为主键
一般不使用双主键
create table tb4(
id int primary key,
name varchar(20)
)engine=innodb charset=utf8;
create table tb4(
id int,
name varchar(20),
primary key(id)
)engine=innodb charset=utf8;
alter table tb4
add primary key(id);
create table tb4(
a int,
b int,
c varchar(20),
primary key(a,b) -- 双主键
)engine=innodb charset=utf8;
-- 告诉服务器，客户端使用的是什么编码
set names gbk;
insert into tb4 values
(9527, '张三'),(18265, '李四');
insert into tb4 values(9527, '王五');
insert into tb4 values(null, '王五');
2.1.2 查看主键
desc tb4;
show create table tb4\G
2.1.3 删除主键
alter table tb4
drop primary key;
2.1.4 自增主键
插入数据时，主键值不用手动给出而是会自动产生
2.1.4.1 添加自增主键
create table tb5(
id int primary key auto_increment,
name varchar(20)
)engine=innodb charset=utf8;
create table tb5(
id int auto_increment,
name varchar(20),
primary key(id)
)engine=innodb charset=utf8;
alter table tb5
modify id int auto_increment;
insert into tb5(name) values
('a'),('b'),('c');
select * from tb5;
-- 插入null时，也产生自增值填入
insert into tb5 values(null, 'd');
select * from tb5;
-- 插入一个指定的值
insert into tb5 values(1000, 'e');
select * from tb5;
2.1.4.2 查看自增主键
show create table tb5\G
2.1.4.3 删除自增
-- 不加auto_increment就是取消
alter table tb5
modify id int;
show create table tb5\G
2.1.4.4 获得刚生成的主键值
last_insert_id() 函数
只得到当前会话所生成的自增值
会话一：
insert into tb5(name) values('e');
会话二：
insert into tb5(name) values('f');
会话一：
select * from tb5;
select last_insert_id();
会话二：
select * from tb5;
select last_insert_id();
2.2 外键
限制字段的取值，只能取另一个主键中存在的值
允许null值
允许重复
自动创建索引
如果存在不符合外键约束的值，不能添加外键
删除父表数据，必须保证子表中没有引用这个主键值
删除父表，要保证没有其他表引用该表的主键
2.2.1 添加外键
-- 班级表，主表、父表
drop table if exists banji;
create table banji(
id int primary key auto_increment,
name varchar(20)
)engine=innodb charset=utf8;
-- 学生表，子表
drop table if exists xuesheng;
create table xuesheng(
id int primary key auto_increment,
num int,
name varchar(20),
ban_id int,
foreign key(ban_id)
references banji(id)
)engine=innodb charset=utf8;
-- 修改表添加外键
alter table xuesheng
add foreign key(ban_id)
references banji(id);
insert into banji(name) values('A'),('B');
insert into xuesheng(num,name,ban_id) values
(6433, 'a', 1),
(6434, 'b', 2),
(6435, 'c', 1),
(6436, 'd', 2),
(6437, 'e', null);
-- 引用不存在的ban_id，违反外键约束
insert into xuesheng(num,name,ban_id) values
(6433, 'a', 999);
2.2.2 查看外键
show create table xuesheng\G
2.2.3 取消外键
取消外键，索引不会被自动删除
-- 删除外键
alter table xuesheng
drop foreign key xuesheng_ibfk_1;
show create table xuesheng\G
-- 删除索引
alter table xuesheng
drop index ban_id;
show create table xuesheng\G
2.3 非空
限制字段，不允许取 null 值
已经存在null值，不能添加非空
2.3.1 添加非空约束
create table banji(
id int primary key auto_increment,
name varchar(20) not null
)engine=innodb charset=utf8;
alter table banji
modify name varchar(20) not null;
insert into banji(name) values(null);
2.3.2 查看非空约束
desc banji;
show create table banji \G
2.3.3 取消非空约束
-- 不写就是取消
alter table banji
modify name varchar(20);
-- 明确写出，允许null
alter table banji
modify name varchar(20) null;
desc banji;
show create table banji \G
2.4 唯一
限制字段取值，不能取重复的值
允许重复的null值
自动创建索引
已经存在重复的值，不能添加唯一约束
2.4.1 添加唯一约束
create table banji(
id int primary key auto_increment,
name varchar(20) unique
)engine=innodb charset=utf8;
create table banji(
id int primary key auto_increment,
name varchar(20),
unique key(name)
)engine=innodb charset=utf8;
-- 两个字段组合不重复
create table user(
id int primary key auto_increment,
username varchar(20),
ip varchar(20),
unique key(username, ip)
) engine=innodb charset=utf8;
alter table banji
modify name varchar(20) unique;
alter table banji
add unique key(name);
insert into banji(name) values
('A'),('B');
2.4.2 查看唯一约束
desc banji;
show create table banji\G
2.4.3 取消唯一约束
删除索引
alter table banji
drop index name;
2.5 检查
检查字段中的值，是否符合取值条件
MySql 没有实现
可以执行检查约束语法，但没有实现该功能
create table user(
...
age int,
gender char(1),
...,
check(age>=7 and age<=60),
check(gender in('M', 'F'))
);
3 表之间的关系
一对一
一对多
多对多
3.1 一对一
通过不重复的外键，来体现一对一关系
学生和联系方式一对一关系
drop table if exists lianxi;
create table lianxi(
xs_id int primary key,
tel varchar(20),
email varchar(50),
qq varchar(20),
foreign key(xs_id)
references xuesheng(id)
)engine=innodb charset=utf8;
select * from xuesheng;
insert into lianxi(xs_id, tel) values
(1, '35234523'),
(3, '12625647'),
(4, '74523534');
select * from lianxi;
3.2 一对多
允许重复值的外键，来体现一对多关系
3.3 多对多关系
添加中间表，设置两个外键，分别引用两张表的主键
学生和课程的多对多关系
-- 课程表
drop table if exists kecheng;
create table kecheng(
id int primary key auto_increment,
name varchar(20)
)engine=innodb charset=utf8;
-- 学生、课程关系中间表
drop table if exists xs_kc_link;
create table xs_kc_link(
xs_id int,
kc_id int,
primary key(xs_id,kc_id),
foreign key(xs_id)
references xuesheng(id),
foreign key(kc_id)
references kecheng(id)
) engine=innodb charset=utf8;
-- 课程数据
insert into kecheng(name) values
('语文'), ('数学'), ('英语');
-- 学生、课程关系数据
insert into xs_kc_link values
(1,1),(1,2),(1,3),
(2,2),(2,3),
(5,1),(5,3);
select * from xuesheng;
select * from kecheng;
select * from xs_kc_link;
4 SQL - DML数据操作语言
insert
update
delete
4.1 insert
insert into tb1 values(值1, 值2...)
insert into tb1(c,a,b)
values(值3,值1,值3)
批量插入
insert into tb1(a,b,c)
values(1,2,3),(4,5,6),(7,8,9)
将查询结果数据，批量插入到表中
insert into tb1(a,b,c)
select e,f,g from tb2
将查询结果数据，创建成一张表
create table tb1
as -- 可以省略
select a,b,c from tb2
批量插入测试
create table k2
select name,id from kecheng;
select * from k2;
insert into k2
select name,id from kecheng;
select * from k2;
-- 自表的数据复制,自增id会出现批量的跳值
insert into kecheng(name) select name from kecheng;
4.2 update
update tb1 set a=1, b=2, c=3
where 条件
先按指定字段排序，再更新前几条数据
修改销量最低的前5个商品
update tb_item set status=3
order by 销量 limit 5
-- 随机修改2个学生， 学号加10000
-- rand() 产生[0,1）范围的随机浮点数
-- 为每行数据产生一个随机值，然后按随机值排序
update xuesheng set num=num+10000
order by rand() limit 2;
select * from xuesheng;
4.3 delete
delete from tb1
where 条件
先排序，然后删除前几条
delete from tb1
order by a limit 5
默认值和注释：
create table tb1(
a int default 0 comment '注释',
b varchar(10) default '' comment '注释'
);
▪▪▪▪▪▪▪▪Day03.▪▪▪▪▪▪▪▪
1 select 查询
select * from tb1
查询全部字段
select c,a,b from tb1
查询指定的字段列表
2 where 子句
对数据进行过滤查询
=
<> 等值过滤id=100
不等过滤 id<>100
> >= < <= 比较大小id>100
between 小 and 大 范围判断[小, 大]
id between 100 and 120
in 从指定数据列表中取值in(5,1,8,4,9)
is null 判断是否是null值
like 模糊查询
_ 通配单个任意字符
% 通配多个任意字符
\_ 转义成斜杠字符
\% 转义成百分号字符
\\ 转义成一个斜杠字符
not not between 小 and 大
not in
is not null
not like
and 并且,优先级高
or 或者
准备测试数据
source d:\hr_mysql.sql
show tables;
select * from employees;
employee_id 员工id
first_name 名
last_name 姓
email email前缀
phone_number 电话
hire_date 入职时间
job_id 工作岗位代码
salary 工资
commission_pct 提成比例
manager_id 主管id
department_id 部门id
查询测试
1. 查询员工id,first_name,salary
select employee_id,first_name,salary
from employees;
2. 查询员工id是120的员工
select employee_id,first_name,salary
from employees
where employee_id=120;
3. 查询job_id是 'IT_PROG'
select
employee_id,first_name,salary,job_id
from employees
where job_id='IT_PROG';
4. 查询 department_id 是 50
select
employee_id,first_name,salary, department_id
from employees
where department_id =50;
5. 查询主管id，manager_id是100
select
employee_id,first_name,salary, manager_id
from employees
where manager_id =100;
6. 部门不是50，排除50部门
select
employee_id,first_name,salary, department_id
from employees
where department_id<>50;
7. 工资 salary 小于等于 3000
select
employee_id,first_name,salary
from employees
where salary<=3000;
8. 工资是 [8000, 10000]
select
employee_id,first_name,salary
from employees
where salary between 8000 and 10000;
select
employee_id,first_name,salary
from employees
where
salary>=8000 and salary<=10000;
9. 员工id,
employee_id是 100,120,150,170
select
employee_id,first_name,salary
from employees
where
employee_id in(100,120,150,170);
10. 岗位代码是 'IT_PROG','SA_MAN','HR_REP'
select
employee_id,first_name,salary,job_id
from employees
where
job_id in('IT_PROG','SA_MAN','HR_REP');
11. 主管id， manager_id 是null
select
employee_id,first_name,salary,manager_id
from employees
where manager_id is null;
12. 部门id， department_id 是 null
select
employee_id,first_name,salary, department_id
from employees
where department_id is null;
13. first_name 中包含 'en'
select
employee_id,first_name,salary
from employees
where first_name like '%en%';
14. first_name第3和第4个字符是 'en'
select
employee_id,first_name,salary
from employees
where first_name like '__en%';
15. first_name以'El' 开头
select
employee_id,first_name,salary
from employees
where first_name like 'El%';
16. phone_number中包含88
select
employee_id,first_name,salary,phone_number
from employees
where phone_number like '%88%';
17. 工资范围在 <3000>15000
select
employee_id,first_name,salary
from employees
where salary not between 3000 and 15000;
select
employee_id,first_name,salary
from employees
where salary<3000 or="" salary="">15000;
18. 排除 30,50,80,100 部门
select
employee_id,first_name,salary,department_id
from employees
where
department_id not in(30,50,80,100);
19. 提成 commission_pct 不是 null
select
employee_id,first_name,salary, commission_pct
from employees
where commission_pct is not null;
20. first_name不包含en
select
employee_id,first_name,salary
from employees
where first_name not like '%en%';
21. phone_number不包含44
select
employee_id,first_name,salary, phone_number
from employees
where phone_number not like '%44%';
22. 姓名中包含en，并且在80部门
select
employee_id,first_name, last_name,
salary,department_id
from employees
where (first_name like '%en%' or
last_name like '%en%') and
department_id=80;
23. 90部门或者工作岗位后缀是'CLERK'
select
employee_id,first_name, last_name,
salary,department_id,job_id
from employees
where department_id=90 or
job_id like '%CLERK';
3 SQL 语句中的单引号
单引号用两个单引号，转义成一个单引号字符
'I'm super man' --> 'I''m super man'
use db1;
-- 告诉服务器，客户端使用的是什么编码
set names gbk;
insert into banji(name) values('A''B');
select * from banji;
3.1 SQL 注入攻击
用单引号改变 sql 语句的结构
用户名：abc
密码：123
select * from user
where username='abc' and password='123'
用户名：vfwe
密码：1' or '1'='1
select * from user
where username='vfwe' and
password='1' or '1'='1'
解决SQL注入攻击的问题：
一个单引号替换成两个单引号
1' or '1'='1 --> 1'' or ''1''=''1
select * from user
where username='vfwe' and
password='1'' or ''1''=''1'
sql注入
-- 新建user表
use db1;
drop table if exists user;
create table user(
id int primary key auto_increment,
username varchar(20) unique not null,
password char(32) not null
)engine=innodb charset=utf8;
-- 插入用户数据，密码用md5加密
insert into user(username,password) values
('abc', md5('123')),
('def', md5('456')),
('ghi', md5('789'));
-- sql注入
select * from user
where username='vfwe' and
password='1' or '1'='1';
-- 避免sql注入
select * from user
where username='vfwe' and
password='1'' or ''1''=''1';
4 distinct 去除重复
select DISTINCT a from ...
去除a字段的重复值
select DISTINCT a,b,c from ...
去除字段组合额重复值
distinct 测试
use hr;
24. 查询部门id，department_id 去除重复值
select distinct department_id
from employees
where department_id is not null;
25. 不重复的job_id
select distinct job_id
from employees;
26. department_id和job_id组合不重复
select distinct department_id,job_id
from employees;
27. 查询有文员CLERK的部门id
select distinct department_id
from employees
where job_id like '%CLERK';
5 order by 子句
排序
select ...
from ...
where ...
order by ...
order by a
按a字段升序排序
order by a,b
先按a字段升序排序，a的值相同，
再按b字段升序排序
asc 升序排序（默认）
order by a asc
desc 降序排序
order by a desc
order by a desc, b
order by a desc, b desc
order by 排序测试
28. 员工按薪水 salary 升序排序
select employee_id,first_name,salary
from employees
order by salary;
29. 员工按部门排序，相同部门按工资降序排序
select employee_id,first_name,salary,department_id
from employees
order by department_id,salary desc;
30. 薪水>=10000，按部门排序，相同部门按工资降序排序
select employee_id,first_name,salary,department_id
from employees
where salary>=10000
order by department_id,salary desc;
31. 查询50和80部门，按工资降序排序，工资相同，按工作岗位代码排序
select employee_id,first_name,salary,department_id,job_id
from employees
where department_id in(50,80)
order by salary desc, job_id asc;
6 字段别名
可以为字段，或者表达式起一个列的别名
select abc as a from ...
select abc a from ...
as可以省略
select abc a from ... where abc=1
where 后面不能使用字段别名
select abc a from ... where abc=1 order by a
order by 可以使用字段别名
sql语句执行顺序：
1. where 对数据进行过滤
2. select 选取字段
3. order by 排序
字段别名测试
32. 查询年薪，薪水*12，年薪>=10万，并按年薪降序排序
select employee_id,first_name,salary,
salary*12 sal
from employees
where salary*12>=100000
order by sal desc;
33. 姓名连接起来显示，按姓名排序
select employee_id,
concat(first_name,' ',last_name) name
from employees
order by name;
7 函数
字符串
数字
日期
null值
加密
分支
多行函数
7.1 字符串
char_length('a中') - 字符数
length('a中') - 字节数
concat('a','b','cde','fff') - 字符串连接，其他数据库可用 || 连接字符串，'abc' || 'def'
concat_ws(';','abc','def','ggg') - 用分隔符连接字符串
instr('abcdefgdef','def') - 返回第一个子串的位置，从1开始，找不到返回0
locate('abc', '---abc---abc---abc-') - 返回第一个子串的位置，从1开始，找不到返回0
locate('abc', '---abc---abc---abc-',5) - 从指定位置向后找
insert('abcdefghijkl',2, 11, '---') - 用子串取代从2位置开始的11个字符
lower('AdFfLJf') - 变为小写
upper('AdFfLJf') - 变为大写
left('AdFfLJf',3) - 返回最左边的三个字符
right('AdFfLJf',3) - 返回最右边的三个字符
lpad('abc', 8, '*') - 左侧填充，指定长度比源字符串少，相当于left
rpad('abc', 8, '*') - 右侧填充，指定长度比源字符串少，相当于left
trim(' a bc ') - 去除两端空格
substring('abcdefghijklmn', 3) - 从3位置开始的所有字符
substring('abcdefghijklmn', 3, 6) - 从3位置开始的6个字符
repeat('abc', 3) - 重复三遍abc
REPLACE('Hello MySql','My','Your') - 子串替换
REVERSE('Hello') - 翻转字符串
SPACE(10) - 返回10个空格
字符串函数测试
set names gbk;
use db1;
select char_length('a中');
select length('a中');
select concat('*','a','b','c');
select concat_ws('*','a','b','c');
select locate('bc','abcabcabc');
select locate('bc','abcabcabc',3);
select insert('abcdef',2,4,'**');
select left('abcdef',2);
select right('abcdef',2);
select lpad('abc',8,'*');
select rpad('abc',8,'*');
select trim(' abc ');
select substring('abcdef',3);
select substring('abcdef',3,3);
select repeat('abc',3);
select replace('abcabcabc','bc','#');
select reverse('abc');
use hr;
34.first_name和last_name首字母相同
select employee_id,first_name,last_name
from employees
where left(first_name,1)=left(last_name,1);
select employee_id,first_name,last_name
from employees
where substring(first_name,1,1)=
substring(last_name,1,1);
35. first_name和last_name字符长度相同
select employee_id,first_name,last_name
from employees
where
char_length(first_name)=
char_length(last_name);
36.email不符合规则，first_name首字母加last_name
select employee_id,first_name,last_name,email
from employees
where
concat(left(first_name,1), replace(last_name,' ', ''))<>email;
37.first_name和last_name连起来显示，空格居中
select employee_id,
concat(
lpad(first_name,20,' '),' ',last_name) name
from employees
order by name;
7.2 数字
ceil(3.94) - 天花板，向上取整
floor(3.94) - 地板，向下取整
round(673.4974) - 四舍五入
round(673.4974, 2) - 四舍五入到小数点后两位
round(673.4974, -2) - 四舍五入到百
truncate(234.39, 1) - 舍去至小数点后1位
format(391.536, 2) - 数字格式化为字符串，###,###.###，四舍五入，第二个参数为小数位数
rand() - 随机浮点数,[0,1)
数字函数测试
select ceil(3.14);
select ceil(-3.14);
select floor(3.14);
select floor(-3.14);
select round(684.9574);
select round(684.9574, 2);
select round(6834.9574, -2);
select truncate(684.9574, 2);
select format(764364346.7453, 2);
-- [100, 200)范围的随机整数 rand()函数来运算产生
select 100+floor(rand()*100);
38.涨工资 11.64%，向上取整到10位
select
employee_id,first_name,salary,
ceil(salary*1.1164/10)*10 new_sal
from employees;
7.3 日期
NOW() 返回当前的日期和时间
CURDATE() 返回当前的日期
CURTIME() 返回当前的时间
DATE(时间) 提取日期或日期/时间表达式的日期部分
TIME(时间) 提取日期或日期/时间表达式的时间部分
EXTRACT(字段 From 日期) 返回日期/时间按的单独部分
字段的合法值：
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH
DATE_ADD(日期, INTERVAL 数量 字段) 给日期添加指定的时间间隔
字段的合法值同上
DATE_SUB(日期, INTERVAL 数量 字段) 从日期减去指定的时间间隔
DATEDIFF(日期1, 日期2) 返回两个日期之间的天数
DATE_FORMAT(日期, 格式) 用不同的格式显示日期/时间
格式字符： %Y-%m-%d %H:%i:%s
%d/%m/%Y
%Y年%m月%d日
%a 缩写星期名
%b 缩写月名
%c 月，数值
%D 带有英文前缀的月中的天
%d 月的天，数值(00-31)
%e 月的天，数值(0-31)
%f 微秒
%H 小时 (00-23)
%h 小时 (01-12)
%I 小时 (01-12)
%i 分钟，数值(00-59)
%j 年的天 (001-366)
%k 小时 (0-23)
%l 小时 (1-12)
%M 月名
%m 月，数值(00-12)
%p AM 或 PM
%r 时间，12-小时（hh:mm:ss AM 或 PM）
%S 秒(00-59)
%s 秒(00-59)
%T 时间, 24-小时 (hh:mm:ss)
%U 周 (00-53) 星期日是一周的第一天
%u 周 (00-53) 星期一是一周的第一天
%V 周 (01-53) 星期日是一周的第一天，与 %X 使用
%v 周 (01-53) 星期一是一周的第一天，与 %x 使用
%W 星期名
%w 周的天 （0=星期日, 6=星期六）
%X 年，其中的星期日是周的第一天，4 位，与 %V 使用
%x 年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y 年，4 位
%y 年，2 位
LAST_DAY(日期) - 返回当月最后一天
日期函数测试
select now();
select curdate();
select curtime();
select date(now());
select time(now());
select extract(year from now());
select extract(month from now());
select extract(day from now());
select date_add(now(),interval 10 year);
select date_add(now(),interval -10 year);
select datediff(now(),'1992-9-12');
select datediff('1992-9-12',now());
select dateformat(now(),'%Y-%m-%d %H:%i:%s');
select last_day(now());
39.入职25年以上的员工
select employee_id,first_name,salary,hire_date
from employees
where
hire_date<date_add(now(),interval -25 year);
select employee_id,first_name,salary,hire_date
from employees
where
datediff(now(), hire_date)>=25*365+6;
40.97年上半年入职
select employee_id,first_name,salary,hire_date
from employees
where hire_date
between '1997-1-1' and '1997-6-30';
select employee_id,first_name,salary,hire_date
from employees
where
extract(year from hire_date)=1997 and
extract(month from hire_date)<7;
41.不论哪一年，1月入职
select employee_id,first_name,salary,hire_date
from employees
where extract(month from hire_date)=1;
7.4 null值
7.5 加密
7.6 分支
7.7 多行函数
show tables;
扫一扫
1116
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
