-- 1.DDL操作之数据库操作
-- 查看所有数据库
show DATABASEs;
-- 创建数据库
CREATE DATABASE if not EXISTS mydb1; CREATE DATABASE mydb1;
-- 选择使用那个数据库
use mydb1;
-- 删除数据库
DROP DATABASE mydb1; DROP DATABASE if EXISTS mydb1;
-- 修改数据库编码
alter DATABASE mydb1 character set utf8;
-- 2.创建表 -- 选择mydb1
use mydb1;
create TABLE if not EXISTS student(
sid int,
name VARCHAR(20),
gender VARCHAR(10),
age int,
birth date,
address VARCHAR(20),
score double
);
-- 3.查看所有数据库的表
show tables;
-- 4.查看指定表的创建语句
show create table student;
-- 5.查看表结构
desc student;
-- 6.删除表
drop table student; use mydb1;
-- 修改表结构 -- 1.添加列：ALTER table 表名 add 列名 类型（长度）[约束]； -- 为student表添加一个新的字段为：系别dept类型为varchar(20)
alter table student add dept varchar(20);
-- 修改列名和类型 -- 2.改列名和类型：ALTER table 表名 change 久列名 新列名 类型（长度）[约束]； -- 为student表的dept字段更换为deptmentvarchar（30）
alter table student change dept deptment varchar(30);
-- 修改表删除列 -- 3.删除列：ALTER table 表名 drop 列名； -- 删除student表的deptment这列
alter table student drop deptment;
-- 修改表名 -- 4.改表名：rename table 表名 to 新表名； -- 更改student表名为stu
rename table student to stu;
-- 2.MDL操作之数据库操作
-- 1.数据的插入
-- 格式一：inster into 表名(列名1，列名2列名3···) values (值1，值2，值3···);
insert into student (sid,name,gender,age,birth,address,score) values (1001,'张三','男',18,'2001-12-23','北京',85.5); insert into student (sid,name,gender,age,birth,address,score) values (1002,'张无','女',18,'2001-12-30','重庆',90.5), (1003,'张四','男',18,'2001-12-24','北京',86.5); #可以多次加值 insert into student(sid) values(1004); insert into student(sid,name) values(1005,'王强'); #指定赋值
-- 格式二：inster into 表名 values (值1，值2，值3···);
insert into student values (1006,'张鸿','女',22,'2001-11-27','重庆',89);
insert into student values (1007,'石化','男',21,'2002-1-27','云南',82),
(1008,'张天','女',20,'2002-10-25','成都',89);
-- 2.数据修改
-- 格式一：update 表名 set 字段名=值，字段名=值，字段名=值···； -- 格式二：update 表名 set 字段名=值，字段名=值，字段名=值··· where 条件；
-- 1.将所有学生的地址更改为重庆
update student set address ='重庆';
-- 2.1.将学号为1004的学生地址修改为北京
update student set address ='北京' where sid = 1004;
-- 2.2.将学号大于1004的学生地址修改为上海
update student set address ='上海' where sid > 1004;
-- 3.将学号为1005的学生地址修改为北京，成绩修改为100
update student set address ='北京', score = 100 where sid =1005 update student set sid =1003 where name ='张四';
-- 3.数据删除
-- 格式一：delete from 表名 where 条件； -- 格式二：truncate table 表名； 或者 truncate 表名；
-- 1.删除sid为1004的学生数据
delete from student where sid = 1004;
#如果sid > 1004则删除1004以上的所有数据，反之sid <1004删除1004以下的所有数据
-- 2.删除所有表数据
delete from student;
-- 3.清空表数据
truncate table student; truncate student;
#delete和truncate原理不同，delete只删除内容，而truncate类似于drop table，可以理解为是将整个表删除，然后再创建表
练习1
-- 1.创建表 /* 创建员工表employee，字段如下： id（员工编号），name（员工名字），gender（员工性别），salary（员工薪资） */
use mydb1; create table mydb1.employee(#加了mydb.后可以不写use mydb1; id int, name varchar(20), gender varchar(20), salary int );
#更改工作为double类型
alter table employee change salary salary double;
-- 2.插入数据 /* 1.'张三','男',2000 1.'李四','男',1000 1.'王五','女',4000 */
insert employee VALUES (001,'张三','男',2000), (002,'李四','男',1000), (003,'王五','女',4000);
-- 3.修改表数据 -- 3.1 将所有员工的薪水修改为5000
update employee set salary = 5000;
-- 3.2 将姓名为’张三‘的员工薪水修改为3000
update employee set salary = 3000 where name='张三';
-- 3.3 将姓名为‘李四’的员工薪水修改为4000，gender改为女
update employee set salary = 4000 , gender = '女' where name='李四';
-- 3.4 将王五的薪水在原有的基础上增加1000
update employee set salary=salary + 1000 where name='王五';
-- 主键约束
-- 方式一：在createtable语句中，通过primary key关键字来指定主键 /*1.操作添加单列主键 在定义字段的同时指定主键，语法格式如下： create table 表名( 字段名 数据类型 primary key, ); */
create table mydb1.emp1( eid int PRIMARY key, name VARCHAR(20), deptId int, salary double );
-- 方式二 /*2.操作添加单列主键 在定义字段后再指定主键，语法格式如下： create table 表名( ``` [constraint <约束名>] primary key 字段名, ); */
create table mydb1.emp2( eid int, name VARCHAR(20), deptId int, salary double, constraint pk1 PRIMARY KEY(eid) );
-- 主键作用 不能重复，不能为空 -- 主键约束的列非空且唯一
insert into emp2(eid,name,deptId,salary) values(1001,'张三',10,5000) insert into emp2(eid,name,deptId,salary) values(1001,'李四',20,6000)#× insert into emp2(eid,name,deptId,salary) values(null,'王五',20,6000)#×
-- 添加多列主键（联合主键）
/*语法格式： create table 表名( ``` primary key(字段名1,字段名2,字段名3,...,字段名n) ); */
create table mydb1.emp3(
name VARCHAR(20),
deptId int,
salary double,
primary key(name,deptId)
);
insert into emp3 values('张三',10,5000);
insert into emp3 values('张三',20,5000);
insert into emp3 values('王五',20,5000);
-- 联合主键的各列都不能为空，联合主键中只要不是全部重复都可以使用
insert into emp3 values('赵六',null,5000);#不可为空
insert into emp3 values(null,20,5000);#不可为空
-- 通过修改表结构添加主键 -- 主键约束不仅可以在创建表的同时创建，还可以在修改表是添加 /*语法格式： create table 表名(````); alter table 表名 add primary key(字段列表)； */
-- 添加单列主键
create table mydb1.emp4( eid int, name VARCHAR(20), deptId int, salary double ); alter table emp4 add primary key(eid);
-- 添加多列主键
create table mydb1.emp5( eid int, name VARCHAR(20), deptId int, salary double ); alter table emp5 add primary key(name,deptId);
-- 删除主键约束 -- 格式：alter table 表名 drop primary key;
-- 删除单列主键
alter table emp1 drop primary key;
-- 删除多列主键
alter table emp5 drop primary key;
-- 自增长约束
-- 通过给字段添加auto_increment属性实现主键自增长 -- 格式：字段名 数据类型 auto_increment;
use mydb1;
create table mydb1.t_user1(
id int PRIMARY KEY auto_increment,
name VARCHAR(20)
);
insert into mydb1.t_user1 VALUES(null,'张三');
insert into mydb1.t_user1(name) VALUES('李四');
DELETE from t_user1; -- delete删除数据后，自增长还是在原基础上+1
insert into mydb1.t_user1 VALUES(null,'张三');
insert into mydb1.t_user1(name) VALUES('李四');
truncate t_user1;-- truncate删除数据后，自增长从初始值1开始+1
insert into mydb1.t_user1 VALUES(null,'张三');
insert into mydb1.t_user1(name) VALUES('李四');
-- 指定自增长的初始值 -- 方式一：创建表时指定
create table t_user2( id int PRIMARY KEY auto_increment, name VARCHAR(20) )auto_increment=100; insert into t_user2 VALUES(null,'张三');
-- 方式二：创建表后指定
create table t_user3( id int PRIMARY KEY auto_increment, name VARCHAR(20) ); alter table t_user3 auto_increment=200; insert into t_user3 VALUES(null,'张三');
-- 非空约束
-- 格式一：字段名 数据类型 not null;
create table t_user6( id int, name VARCHAR(20) not null,-- 指定非空约束 address VARCHAR(20) not null-- 指定非空约束 ); insert into t_user6(id) VALUES(1001);-- 不可以 insert into t_user6(id,name,address) VALUES(1001,null,null);-- 不可以 insert into t_user6(id,name,address) VALUES(1001,'null','null');-- 可以,被单引号围起来算是字符串 insert into t_user6(id,name,address) VALUES(1001,'','');-- 可以，代表空串
-- 格式二：ALTER tables 表名 modify 字段名 数据类型 not null;
create table t_user7( id int, name VARCHAR(20), address VARCHAR(20) ); alter table t_user7 MODIFY name VARCHAR(20) not null; alter table t_user7 MODIFY address VARCHAR(20) not null; desc t_user7;-- 查看数据库属性
-- 删除非空约束 -- 格式：ALTER tables 表名 modify 字段名 数据类型;
alter table t_user7 MODIFY name VARCHAR(20); alter table t_user7 MODIFY address VARCHAR(20); desc t_user7;
-- 唯一约束
-- 方式一：字段名 数据类型 unique; -- 方式二：ALTER tables 表名 add constraint 约束名 unique(列);
-- 1.创建表时添加指定 字段名 数据类型 unique;
create table t_user8( id int, name VARCHAR(20), phone_number VARCHAR(20) unique ); insert into t_user8 VALUES(1001,'张三',138); insert into t_user8 VALUES(1002,'张三2',139);-- phone_number处不能重复 insert into t_user8 VALUES(1003,'张三3',null); insert into t_user8 VALUES(1001,'张三4',null);-- 在mysql中null和任何值都不重复，包括它本身 desc t_user8;
-- 2.创建表后添加指定 ALTER tables 表名 add constraint 约束名 unique(列);
create table t_user9( id int, name VARCHAR(20), phone_number VARCHAR(20) ); alter table t_user9 add constraint unique_pn unique(phone_number); insert into t_user9 VALUES(1001,'张三',138); insert into t_user9 VALUES(1002,'张三2',138); insert into t_user9 VALUES(1003,'张三3',null); insert into t_user9 VALUES(1001,'张三4',null);
-- 删除唯一约束 -- 格式：ALTER tables 表名 drop index 唯一约束名;
alter table t_user9 drop index unique_pn;
-- 默认约束
-- 方式一：字段名 数据类型 default; -- 方式二：ALTER tables 表名 modify 列名 类型 default 默认值;
-- 1.创建表时添加指定 字段名 数据类型 default;
create table t_user10( id int, name VARCHAR(20), address VARCHAR(20) default '北京' ); insert into t_user10(id,name) VALUES(1001,'张三'); insert into t_user10(id,name,address) VALUES(1002,'张三2','上海'); insert into t_user10 VALUES(1003,'张三3',null);
-- 2.创建表后添加指定 ALTER tables 表名 modify 列名 类型 default 默认值;
create table t_user11( id int, name VARCHAR(20), address VARCHAR(20) ); alter table t_user11 modify address VARCHAR(20) default '深圳'; insert into t_user11(id,name) VALUES(1001,'张三');
-- 删除默认约束 -- 格式：ALTER tables 表名 modify 列名 类型 default null;
alter table t_user11 modify address varchar(20) default null; insert into t_user11(id,name) VALUES(1002,'张三2');
-- 零填充约束
-- 1.添加约束
create table t_user12( id int zerofill,-- 零填充约束 name VARCHAR(20) ); insert into t_user12 VALUES(123,'张三'); insert into t_user12 VALUES(1,'张三2');
-- 添加方式二：alter table 表名 modify 字段名 数据类型 zerofill;
alter table t_user12 modify id int zerofill;
-- 2.删除约束 -- 格式：alter table 表名 modify 字段名 数据类型;
alter table t_user12 modify id int; insert into t_user12 VALUES(2,'张三3');
-- MySQL数据库基本操作-DQL-基本查询
-- 数据准备 -- 创建数据库
create database if not exists mydb2; use mydb2;
-- 创建商品表
create table product( pid int PRIMARY key auto_increment,-- 商品编号 pname VARCHAR(20) not null,-- 商品名字 price double,-- 商品价格 category_id VARCHAR(20)-- 商品所属分类 );
-- 添加数据
insert into product VALUES(null,'海尔洗衣机',5000,'c001'); insert into product VALUES(null,'美的冰箱',3000,'c001'); insert into product VALUES(null,'格力空调',5000,'c001'); insert into product VALUES(null,'九阳电饭煲',5000,'c001'); insert into product VALUES(null,'啄木鸟衬衣',300,'c002'); insert into product VALUES(null,'恒源祥西裤',800,'c002'); insert into product VALUES(null,'花花公子夹克',440,'c002'); insert into product VALUES(null,'劲霸休闲裤',266,'c002'); insert into product VALUES(null,'海澜之家卫衣',180,'c002'); insert into product VALUES(null,'杰克琼斯运动裤',430,'c002'); insert into product VALUES(null,'兰蔻面霜',300,'c003'); insert into product VALUES(null,'雅诗兰黛精华水',200,'c003'); insert into product VALUES(null,'香奈儿香水',350,'c003'); insert into product VALUES(null,'SK-II神仙水',350,'c003'); insert into product VALUES(null,'资生堂粉底液',180,'c003'); insert into product VALUES(null,'老北京方便面',56,'c004'); insert into product VALUES(null,'良品铺子海带丝',17,'c004'); insert into product VALUES(null,'三只松鼠坚果',88,'c004');
简单查询
-- 1.查询所有的商品
select pid,pname,price,category_id from product; select * from product; -- *代表全部列
-- 2.查询商品名和商品价格
select pname,price from product;
-- 3.别名查询.使用关键字是as(as可以省略) -- 3.1表别名
select * from product as p; select * from product p;
-- 3.2列别名
select pname as '商品名',price as '商品价格' from product;-- 别名可以写汉字但是很少使用汉字
-- 4.去掉重复值
select distinct price from product;-- 去重某一列 select distinct * from product;-- 去重表里面出现重复的一行
-- 5.查询结果是表达式（运算查询）：将所有商品加价10元进行显示
select pname,price +10 new_price from product;
运算符操作
-- 算数运算符
select 6+2; select 6-2; select 6*2; select 6/2; select 6%4; -- 将所有商品加价10元 select pname,price +10 new_price from product; -- 将所有商品上涨10% select pname,price *1.1 new_price from product;
-- 条件查询
-- 查询商品名称为“海尔洗衣机”的商品所有信息 select * from product where pname = '海尔洗衣机'; -- 查询价格为800商品 select * from product where price = 800; -- 查询价格不是800的所有商品 select * from product where price != 800; select * from product where price <> 800; select * from product where not(price = 800); -- 查询商品价格大于60元的所有商品信息 select * from product where price > 60; -- 查询商品价格在200到1000之间所有商品 select * from product where price >= 200 and price <= 1000; select * from product where price between 200 and 1000; -- 查询商品价格是200或者800的所有商品 select * from product where price in(200,800); select * from product where price = 200 or price = 800; select * from product where price = 200 || price = 800; -- 查询含有'裤'字的所有商品 select * from product where pname like '%裤';-- %用来匹配任意字符 -- 查询以'海'字开头的所有商品 select * from product where pname like '海%';-- %放在前面表述代替关键词前面的的字符，反正放在后面代表关键词后面的字符 -- 查询第二个字为'蔻'的所有商品 select * from product where pname like '_蔻%';-- 下划线匹配单个字符 -- 重新category_id为null的商品 select * from product where category_id is null;-- 求null不能用=，因为null和任何数都不重复包括他本身 -- 重新category_id不为null分类的商品 select * from product where category_id is not null; -- 使用least求最小值 select least(10,5,6,20) as small_number; select least(10,null,20) as small_number;-- 如果求最小值时，有个null，则不会进行比较，结果直接为null -- 使用greatest求最大值 select greatest(10,20,30,55) as big_number; select greatest(10,null,30,55) as big_number;-- 如果求最大值时，有个null，则不会进行比较，结果直接为null
-- 位运算符（了解）
select 3&5; -- 位与 3=0011 5=0101 -- 二进制 0011 0101 ------ 0001 -- 不相同即为0，相同为1 select 3|5; -- 位或 0011 0101 ----- 0111 = 7 -- 有一个为1结果就是1 select 3^5; -- 位异或 0011 0101 ------ 0110 = 6 -- 相同为0，相异为1 select 3>>1; -- 位左移 0011 >> 1 ---->0001 = 1 select 3<<1; -- 位右移 0011 << 1 -----> 0110 = 6 select ~3; -- 位取反 -- 18446744073709551612 00000000000000011 ->1111111111111100
-- 排序查询
-- 1.使用价格排序(降序) select * from product order by price desc; -- 2.在价格排序(降序)的基础上，以分类排序(降序) select * from product order by price desc,category_id desc; -- 3.显示商品的价格(去重复)，并排序(降序) select distinct price from product order by price desc;
-- 聚合函数
/* 聚合函数 作用 count() 统计指定列不为NULL的记录行数； sum() 计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0 max() 计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算； min() 计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算； avg() 计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0 */
-- 1 查询商品的总条数 select count(*) from product; select count(pid) from product; -- 2 查询价格大于200商品的总条数 select count(*) from product where price > 200; -- 3 查询分类为'c001'的所有商品的总和 select sum(price) from product where category_id = 'c001'; -- 4 查询商品的最大价格 select max(price) from product; -- 5 查询商品的最小价格 select min(price) from product; -- 6 查询分类为'c002'所有商品的平均价格 select avg(price) from product where category_id = 'c002';
-- null值的处理
use mydb2;
-- 创建表
create table test_null(
c1 varchar(20),
c2 int
);
-- 插入数据
insert into test_null values('aaa',3);
insert into test_null values('bbb',3);
insert into test_null values('ccc',null);
insert into test_null values('ddd',6);
-- 测试
select count(*), count(1), count(c2) from test_null;-- 对null值视而不见
select sum(c2),max(c2),min(c2),avg(c2) from test_null;-- 对null值视而不见 -- avg=(3+3+6)/4
-- 分组查询 group by
-- 格式：select 字段1,字段2… from 表名 group by 分组字段 having 分组条件;
- 1 统计各个分类商品的个数 -- 分组之后只能写分组字段和聚合函数 select category_id ,count(*),avg(price),sum(price) from product group by category_id ; -- 2.统计各个分类商品的个数,且只显示个数大于4的信息 -- SQL执行顺序:from -> group by -> count() -> select ->having -> oder by select category_id ,count(*) c from product group by category_id having count(*) > 4 ORDER BY c;
-- 分页查询 limit
-- 方式1-显示前n条：select 字段1，字段2... from 表明 limit n -- 方式2-分页显示：select 字段1，字段2... from 表明 limit m,n -- m: 整数，表示从第几条索引开始，计算方式 （当前页-1）*每页显示条数 -- n: 整数，表示查询多少条数据
-- 查询product表的前5条记录 select * from product limit 5; -- 从第4条开始显示，显示5条 select * from product limit 3,5; -- 分页显示 select * from product limit 0,60; -- 第1页 --->(1-1)*60 select * from product limit 60,60; -- 第2页 --->(2-1)*60 select * from product limit 120,60; -- 第3页 --->(3-1)*60 select * from product limit (n-1)*60,60; -- 第n页 --->(3-1)*60
-- insert into select 语句
/* 将一张表的数据导入到另一张表中，可以使用INSERT INTO SELECT语句 。 格式： insert into Table2(field1,field2,…) select value1,value2,… from Table1 或者： insert into Table2 select * from Table1 */
select * from product;-- 查询表 create table product2( pname VARCHAR(20), price double ); insert into product2(pname,price) select pname,price from product; select * from product2; create table product3( category_id VARCHAR(20), product_count int ); insert into product3 select category_id,count(*) from product group by category_id; select * from product3;
练习2
use mydb2; create TABLE if not EXISTS student( id int, name VARCHAR(20), gender VARCHAR(20), chinese int, english int, math int ); insert into student(id,name,gender,chinese,english,math) VALUES(1,'张明','男',89,78,90); insert into student(id,name,gender,chinese,english,math) VALUES(2,'李进','男',67,53,95); insert into student(id,name,gender,chinese,english,math) VALUES(3,'王五','女',87,78,77); insert into student(id,name,gender,chinese,english,math) VALUES(4,'李一','女',88,98,92); insert into student(id,name,gender,chinese,english,math) VALUES(5,'李财','男',82,84,67); insert into student(id,name,gender,chinese,english,math) VALUES(6,'张宝','男',55,85,45); insert into student(id,name,gender,chinese,english,math) VALUES(7,'黄蓉','女',75,65,30); insert into student(id,name,gender,chinese,english,math) VALUES(7,'黄蓉','女',75,65,30); -- 查询表中所有学生的信息 select * from student; -- 查询表中所有学生的姓名对应的英语成绩 select name,english from student; -- 过滤表中重复数据 select distinct * from student; -- 统计每个学生的总分 × select name,sum(chinese+english+math) '总成绩' from student group by name;-- 错误写法，不能使用sum函数，是多个列 select name,(chinese+english+math) as new from student ;-- 修正写法 -- 在所有学生总分数上加10分特长分 × select name,sum(chinese+english+math) +10 from student;-- 错误写法 select name,(chinese+english+math+10) as new from student ;-- 修正写法 -- 使用别名表示学生分数 select name,chinese '语文成绩',english '英语成绩',math '数学成绩' from student; -- 查询英语成绩大于90的同学 select name,english from student where english> 90; -- 查询总分大于200的所有同学 × select name,sum (chinese+english+math) from student where sum(chinese+english+math) > 200; -- 错误写法 select *,(chinese+english+math) as new from student where new > 200; -- 修改后错误写法 select * from student where (chinese+english+math) > 200; -- 修改后写法 -- 查询英语分数在80-90之间的同学 select * from student where english between 80 and 90; select * from student where english >= 80 and english <= 90; -- 查询英语分数不在在80-90之间的同学 select * from student where english not between 80 and 90; select * from student where not (english between 80 and 90); select * from student where not (english >= 80 and english <= 90); select * from student where english < 80 || english > 90; -- 查询数学分数为89,90,91的同学 select * from student where math in(89,90,91); -- 查询数学分数不为89,90,91的同学 select * from student where math not in(89,90,91); -- 查询所以姓李的学生英语成绩 × select name,english from student where english like '李%';-- 错 select name,english from student where name like '李%'; -- 查询数学分数80且语文分数80的同学 select * from student where math = 80 and chinese = 80; -- 查询英语80或者总分200的同学 select * from student where english = 80 or (chinese+english+math) = 200; -- 对数学成绩降序排序然后输出 select * from student order by math desc; -- 对总分排序后输出，然后再按从高到低的顺序输出 select *,(chinese+english+math) new from student order by (chinese+english+math) desc; -- 对姓李的学生成绩排序输出 × select * from student order by (chinese+english+math) desc where name like '李%';-- × select * from student where name like '李%' order by (chinese+english+math) desc ; -- 查询男生女生分别有多少人，并将人数降序输出 × select gender,count(*) as people from student group by gender order by people desc;-- 修改后正确答案 -- 查询男生女生分别有多少人，并将人数降序输出，查询出人数大于4的性别人数信息 select gender,count(*) as people from student group by gender having people > 4 order by people desc; -- ifnull(x,0) 如果x的值为null，则当做0，不为null，则还是原来的值
-- 正则表达式
-- ^ 在字符串开始处进行匹配
SELECT 'abc' REGEXP '^a';-- 匹配到a 是则为1
select * from product where pname regexp '^海';
-- $ 在字符串末尾开始匹配
SELECT 'abc' REGEXP 'a$'; -- 0 不是就是0
SELECT 'abc' REGEXP 'c$'; -- 1 是就是1
select * from product where pname regexp '水$';
-- . 匹配任意字符，可以匹配换行符之外的任意字符
SELECT 'abc' REGEXP '.b'; -- 1
SELECT 'abc' REGEXP '.c'; -- 1
SELECT 'abc' REGEXP 'a.'; -- 1
-- [...] 匹配括号内的任意单个字符
SELECT 'abc' REGEXP '[xyz]'; -- 0，没有前面的任意字符即为0
SELECT 'abc' REGEXP '[xaz]'; -- 1 有前面的一个字符即为1
-- [^...] 注意^符合只有在[]内才是取反的意思，在别的地方都是表示开始处匹配
SELECT 'a' REGEXP '[^abc]'; -- 前面出现了a 即为0
SELECT 'x' REGEXP '[^abc]'; -- 前面都没有出现 即为 1
SELECT 'abc' REGEXP '[^a]'; -- 处了a有其他的字符也算取反 为1
-- a* 匹配0个或多个a,包括空字符串。 可以作为占位符使用.有没有指定字符都可以匹配到数据
SELECT 'stab' REGEXP '.ta*b';-- 出现一次a 为1
SELECT 'stb' REGEXP '.ta*b';-- 出现0 次a 为1
SELECT '' REGEXP 'a*';-- 包含空，出现0 次 为1
-- a+ 匹配1个或者多个a,但是不包括空字符
SELECT 'stab' REGEXP '.ta+b';-- 出现1次即为1
SELECT 'stb' REGEXP '.ta+b';-- 出现0次，即为0
-- a? 匹配0个或者1个a
SELECT 'stb' REGEXP '.ta?b';-- 匹配0个 为1
SELECT 'stab' REGEXP '.ta?b';-- 匹配1个 为1
SELECT 'staab' REGEXP '.ta?b';-- 匹配2个 为0
-- a1|a2 匹配a1或者a2，中一个就行
SELECT 'a' REGEXP 'a|b';-- 满足a 为1
SELECT 'b' REGEXP 'a|b';-- 满足b 为1
SELECT 'b' REGEXP '^(a|b)';-- 以a或者b开头 满足 为1
SELECT 'a' REGEXP '^(a|b)';-- 以a或者b开头 满足 为1
SELECT 'c' REGEXP '^(a|b)';-- 以a或者b开头 不满足 为0
-- a{m} 匹配m个a
SELECT 'auuuuc' REGEXP 'au{4}c';-- u出现4次 满足 为1
SELECT 'auuuuc' REGEXP 'au{3}c';-- u出现4次 不满足 为0
-- a{m,} 匹配m个或者更多a
SELECT 'auuuuc' REGEXP 'au{3,}c';-- u出现3次及以上 满足 为1
SELECT 'auuuuc' REGEXP 'au{4,}c';-- u出现4次 满足 为1
SELECT 'auuuuc' REGEXP 'au{5,}c';-- u出现4次 不满足 为0
-- a{m,n} 匹配m到n个a,包含m和n
SELECT 'auuuuc' REGEXP 'au{3,5}c';-- u出现4次 满足3-5 为1
SELECT 'auuuuc' REGEXP 'au{4,5}c';-- u出现4次 满足4-5 为1
SELECT 'auuuuc' REGEXP 'au{5,10}c';-- u出现4次 不满足5-10 为1
-- (abc) abc作为一个序列匹配，不用括号括起来都是用单个字符去匹配，如果要把多个字符作为一个整体去匹配就需要用到括号，所以括号适合上面的所有情况。
SELECT 'xababy' REGEXP 'x(abab)y';-- 匹配成功（abab） 为1
SELECT 'xababy' REGEXP 'x(ab)*y';-- （ab）*可出现0次或多次 满足为1
SELECT 'xababy' REGEXP 'x(ab){1,2}y';-- (ab){1,2}出现2次满足为1
-- 外键约束
create database mydb3;
use mydb3;
-- 创建部门表 -- 主表
create table if not exists dept(
deptno varchar(20) primary key , -- 部门号
name varchar(20) -- 部门名字
);
-- 创建员工表，从表，并创建dept_id外键约束 -方式1
-- [constraint <外键名>] foreign key 字段名 [，字段名2，…] references <主表名> 主键列1 [，主键列2，…]
create table if not exists emp(
eid varchar(20) primary key , -- 员工编号
ename varchar(20), -- 员工名字
age int, -- 员工年龄
dept_id varchar(20), -- 员工所属部门
constraint emp_fk foreign key (dept_id) references dept (deptno) -- 外键约束
);
-- 创建员工表，从表，并创建dept_id外键约束 -方式2
-- alter table <数据表名> add constraint <外键名> foreign key(<列名>) references <主表名> (<列名>);
-- 创建部门表
create table if not exists dept2(
deptno varchar(20) primary key , -- 部门号
name varchar(20) -- 部门名字
);
-- 创建员工表
create table if not exists emp2(
eid varchar(20) primary key , -- 员工编号
ename varchar(20), -- 员工名字
age int, -- 员工年龄
dept_id varchar(20) -- 员工所属部门
);
-- 创建外键约束
alter table emp2 add constraint dept_id_fk foreign key(dept_id) references dept2 (deptno);
-- 1、添加主表数据
-- 注意必须先给主表添加数据
insert into dept values('1001','研发部');
insert into dept values('1002','销售部');
insert into dept values('1003','财务部');
insert into dept values('1004','人事部');
-- 2、添加从表数据
-- 注意给从表添加数据时，外键列的值不能随便写，必须依赖主表的主键列
insert into emp values('1','乔峰',20, '1001');
insert into emp values('2','段誉',21, '1001');
insert into emp values('3','虚竹',23, '1001');
insert into emp values('4','阿紫',18, '1002');
insert into emp values('5','扫地僧',35, '1002');
insert into emp values('6','李秋水',33, '1003');
insert into emp values('7','鸠摩智',50, '1003');
insert into emp values('8','天山童姥',60, '1005'); -- 不可以,部门表中没有1005，从表受到主表约束
-- 3、删除数据
/*
注意：
1：主表的数据被从表依赖时，不能删除，否则可以删除
2: 从表的数据可以随便删除
*/
delete from dept where deptno = '1001'; -- 不可以删除，1001在主表中被依赖
delete from dept where deptno = '1004'; -- 可以删除主表中未被依赖
delete from emp where eid = '7'; -- 可以删除，从表数据随便删
delete from emp; -- 可以删除，从表数据随便删
-- 删除外键约束
-- 格式：alter table <表名> drop foreign key <外键约束名>;
alter table emp2 drop foreign key dept_id_fk;
--多对多关系
-- 学生表和课程表(多对多)
-- 1 创建学生表student(左侧主表)
create table if not exists student(
sid int primary key auto_increment,
name varchar(20),
age int,
gender varchar(20)
);
-- 2 创建课程表course(右侧主表)
create table course(
cid int primary key auto_increment,
cidname varchar(20)
);
-- 3创建中间表student_course/score(从表)
create table score(
sid int,
cid int,
score double
);
-- 4建立外键约束(2次)
alter table score add foreign key(sid) references student(sid);
alter table score add foreign key(cid) references course(cid);
-- 5给学生表添加数据
insert into student values(1,'小龙女',18,'女'),(2,'阿紫',19,'女'),(3,'周芷若',20,'男');
-- 6给课程表添加数据
insert into course values(1,'语文'),(2,'数学'),(3,'英语');
-- 7给中间表添加数据
insert into score values(1,1,78),(1,2,75),(2,1,88),(2,3,90),(3,2,80),(3,3,65);
-- 修改和删除时，中间从表可以随便删和修改，但是两边的主表受从表依赖的数据不能删除或者修改
-- 多表查询数据准备
use mydb3;
-- 创建部门表
create table if not exists dept3(
deptno varchar(20) primary key , -- 部门号
name varchar(20) -- 部门名字
);
-- 创建员工表
create table if not exists emp3(
eid varchar(20) primary key , -- 员工编号
ename varchar(20), -- 员工名字
age int, -- 员工年龄
dept_id varchar(20) -- 员工所属部门
);
-- 给dept3表添加数据
insert into dept3 values('1001','研发部');
insert into dept3 values('1002','销售部');
insert into dept3 values('1003','财务部');
insert into dept3 values('1004','人事部');
-- 给emp表添加数据
insert into emp3 values('1','乔峰',20, '1001');
insert into emp3 values('2','段誉',21, '1001');
insert into emp3 values('3','虚竹',23, '1001');
insert into emp3 values('4','阿紫',18, '1001');
insert into emp3 values('5','扫地僧',85, '1002');
insert into emp3 values('6','李秋水',33, '1002');
insert into emp3 values('7','鸠摩智',50, '1002');
insert into emp3 values('8','天山童姥',60, '1003');
insert into emp3 values('9','慕容博',58, '1003');
insert into emp3 values('10','丁春秋',71, '1005');
-- 交叉连接查询
/* 1.交叉连接查询返回被连接的两个表所有数据行的笛卡尔积 2.笛卡尔积可以理解为一张表的每一行去和另外一张表的任意一行进行匹配 3.假如A表有m行数据，B表有n行数据，则返回m*n行数据 4.笛卡尔积会产生很多冗余的数据，后期的其他查询可以在该集合的基础上进行条件筛选 */ -- 格式：select * from 表1,表2,表3….; -- 交叉连接查询 select * from dept3,emp3;
-- 内连接查询
/* 格式： -- 隐式内连接（SQL92标准）：select * from A,B where 条件; -- 显示内连接（SQL99标准）：select * from A inner join B on 条件; */ -- 查询每个部门的所属员工 -- 隐式内连接 select * from dept3,emp3 where dept3.deptno = emp3.dept_id; -- 显示内连接 select * from dept3 inner join emp3 on dept3.deptno = emp3.dept_id; select * from dept3 join emp3 on dept3.deptno = emp3.dept_id; -- 查询研发部的所属员工 -- 隐式内连接 select * from dept3,emp3 where dept3.deptno = emp3.dept_id and name ='研发部'; -- 显示内连接 select * from dept3 join emp3 on dept3.deptno = emp3.dept_id and name = '研发部'; -- 查询研发部和销售部的所属员工 -- 隐式内连接 select * from dept3,emp3 where dept3.deptno = emp3.dept_id and name in( '研发部','销售部'); -- 显示内连接 select * from dept3 join emp3 on dept3.deptno = emp3.dept_id and (name = '研发部' or name = '销售部'); select * from dept3 join emp3 on dept3.deptno = emp3.dept_id and name in( '研发部','销售部'); -- 查询每个部门的员工数,并升序排序 -- 隐式内连接 select deptno,count(1) as total_cnt from dept3,emp3 where dept3.deptno = emp3.dept_id group by deptno order by total_cnt; -- 显示内连接 select deptno,count(1) as total_cnt from dept3 join emp3 on dept3.deptno = emp3.dept_id group by deptno order by total_cnt; -- 查询人数大于等于3的部门，并按照人数降序排序 -- 隐式内连接 select deptno,name,count(1) as total_cnt from dept3,emp3 where dept3.deptno = emp3.dept_id group by deptno having total_cnt >= 3 order by total_cnt desc; -- 显示内连接 select deptno,name,count(1) as total_cnt from dept3 join emp3 on dept3.deptno = emp3.dept_id group by deptno having total_cnt >= 3 order by total_cnt desc;
-- 外连接查询
-- 查询哪些部门有员工，哪些部门没有员工 use mydb3; select * from dept3 left outer join emp3 on dept3.deptno = emp3.dept_id;
-- 多个表的时候 /* select * from a left join b on 条件1 left join c on 条件2 left join d on 条件3; */ -- 查询哪些员工有对应的部门，哪些没有 select * from dept3 right outer join emp3 on dept3.deptno = emp3.dept_id; -- 实现满外连接：fill join -- 使用union关键字实现左外连接和右外连接的并集 -- select * from dept3 full outer join emp3 on dept3.deptno = emp3.dept_id -- 不能执行 select * from dept3 left outer join emp3 on dept3.deptno = emp3.dept_id union select * from dept3 right outer join emp3 on dept3.deptno = emp3.dept_id; -- 将左外和右外直接拼接在一起，有去重 -- ------------------------------------------------- select * from dept3 left outer join emp3 on dept3.deptno = emp3.dept_id union all select * from dept3 right outer join emp3 on dept3.deptno = emp3.dept_id; -- 将左外和右外直接结合在一起，无去重
-- 子查询
/*
子查询就是指的在一个完整的查询语句之中，嵌套若干个不同功能的小查询，从而一起完成复杂查询的一种编写形式，通俗一点就是包含select嵌套的查询。
特点：
单行单列：返回的是一个具体列的内容，可以理解为一个单值数据；
单行多列：返回一行数据中多个列的内容；
多行单列：返回多行记录之中同一列的内容，相当于给出了一个操作范围；
多行多列：查询返回的结果是一张临时表
*/
-- 查询年龄最大的员工信息，显示信息包含员工号、员工名字，员工年龄
select eid,ename,age from emp3 where age = (select max(age) from emp3);
-- 查询年研发部和销售部的员工信息，包含员工号、员工名字
select eid,ename from emp3 where dept_id in (select deptno from dept3 where name in('研发部','销售部'));
-- 1.先查询研发部和销售部的部门号
select deptno from dept3 where namein('研发部','销售部');
-- 2.查询那个员工的部门号是1001或者1002
select * from emp3 where dept_id in (select deptno from dept3 where name in('研发部','销售部'));
-- 查询研发部20岁以下的员工信息,包括员工号、员工名字，部门名字
select eid,age,ename,name from (select * from dept3 where name = '研发部 ') t1,(select * from emp3 where age <20) t2;-- 有误
-- 1.查询在部门表中研发部信息
select *from dept3 where name = '研发部';
-- 2.在员工表中查询年龄小于20的员工信息
select * from emp3 where age < 20;
-- 3.以上两个查询结果进行关联查询
select eid,age,ename,name from (select *from dept3 where name = '研发部') t1 join (select * from emp3 where age < 20) t2 on t1.deptno = t2.dept_id;
-- all关键字
/* select …from …where c > all(查询语句) --等价于： select ...from ... where c > result1 and c > result2 and c > result3 */ -- 查询年龄大于‘1003’部门所有年龄的员工信息 select * from emp3 where age > all(select age from emp3 where dept_id = '1003'); -- 查询不属于任何一个部门的员工信息 select * from emp3 where dept_id != all(select deptno from dept3);
-- any和some关键字
/*格式 select …from …where c > any(查询语句) --等价于： select ...from ... where c > result1 or c > result2 or c > result3 */ -- 查询年龄大于‘1003’部门任意一个员工年龄的员工信息 select * from emp3 where age > any(select age from emp3 where dept_id = '1003') and dept_id != '1003';
-- in关键字
/*格式 select …from …where c in(查询语句) --等价于： select ...from ... where c = result1 or c = result2 or c = result3 */ -- 查询研发部和销售部的员工信息，包含员工号、员工名字 select eid,ename from emp3 where dept_id in (select deptno from dept3 where name = '研发部' or name = '销售部') ;
-- exists关键字
/* 格式：select …from …where exists(查询语句) */ select * from emp3 where exists(select * from emp3);-- 全表输出 select * from emp3 where exists(select 1);-- 全表输出 -- 查询公司是否有大于60岁的员工，有则输出 select * from emp3 a where exists(select * from emp3 b where a.age > 60);-- 有内外之分，用别名区分 -- 查询有所属部门的员工信息 select * from emp3 a where exists(select * from dept3 b where a.dept_id = b.deptno);
-- 自关联查询
/*格式
select 字段列表 from 表1 a , 表1 b where 条件;
或者
select 字段列表 from 表1 a [left] join 表1 b on 条件;
*/
-- 创建表,并建立自关联约束
create table t_sanguo(
eid int primary key ,
ename varchar(20),
manager_id int,
foreign key (manager_id) references t_sanguo (eid) -- 添加自关联约束
);
-- 添加数据
insert into t_sanguo values(1,'刘协',NULL);
insert into t_sanguo values(2,'刘备',1);
insert into t_sanguo values(3,'关羽',2);
insert into t_sanguo values(4,'张飞',2);
insert into t_sanguo values(5,'曹操',1);
insert into t_sanguo values(6,'许褚',5);
insert into t_sanguo values(7,'典韦',5);
insert into t_sanguo values(8,'孙权',1);
insert into t_sanguo values(9,'周瑜',8);
insert into t_sanguo values(10,'鲁肃',8);
-- 进行关联查询
-- 1.查询每个三国人物及他的上级信息，如: 关羽 刘备
select * from t_sanguo a, t_sanguo b where a.manager_id = b.eid;
select a.ename '自己',b.ename '老板' from t_sanguo a, t_sanguo b where a.manager_id = b.eid;
select a.ename '自己',b.ename '老板' from t_sanguo a join t_sanguo b on a.manager_id = b.eid;
-- 2.查询所有人物及上级
select a.ename '自己',b.ename '老板' from t_sanguo a left join t_sanguo b on a.manager_id = b.eid;
-- 3.查询所有人物、上级、上上级
select
a.ename '自己',b.ename '老板',c.ename '老板的老板'
from t_sanguo a
left join t_sanguo b on a.manager_id = b.eid
left join t_sanguo c on b.manager_id = c.eid;
练习3
-- 创建test1数据库
CREATE DATABASE test1;
-- 选择使用test1数据库
use test1;
-- 创建部门表
create table dept(
deptno int PRIMARY KEY,-- 部门编号
dname VARCHAR(14),-- 部门名称
loc VARCHAR(13) -- 部门地址
);
insert into dept VALUES(10,'accounting','new york'),
(20,'research','dallas'),
(30,'sales','chicago'),
(40,'operations','boston');
-- 创建员工表
create table emp(
empno int PRIMARY KEY,-- 员工编号
ename VARCHAR(10),-- 员工名称
job VARCHAR(9), -- 员工工作
mgr int,-- 员工直属领导编号
hiredate date,-- 员工入职时间
sal double,-- 薪资
comm double,-- 奖金
deptno int -- 对应dept表的外键
);
-- 添加部门和员工之间的主外键关系
alter table emp add constraint foreign key emp(deptno) references dept(deptno);
insert into emp VALUES(7369,'smith','clerk',7092,'1980-12-17',800,null,20);
insert into emp VALUES(7499,'allen','salesman',7698,'1981-02-20',800,300,30);
insert into emp VALUES(7521,'ward','salesman',7698,'1981-02-22',800,500,30);
insert into emp VALUES(7566,'jones','manager',7839,'1981-04-02',800,null,20);
insert into emp VALUES(7654,'martin','salesman',7698,'1981-09-28',800,1400,30);
insert into emp VALUES(7698,'blake','manager',7839,'1981-05-01',800,null,30);
insert into emp VALUES(7782,'clark','manager',7839,'1981-06-09',800,null,10);
insert into emp VALUES(7788,'scott','analyst',7566,'1981-07-03',800,null,20);
insert into emp VALUES(7839,'king','president',null,'1981-11-17',800,null,10);
insert into emp VALUES(7844,'turner','salesman',7698,'1981-09-08',800,0,30);
insert into emp VALUES(7876,'adams','clerk',7788,'1981-07-13',800,null,20);
insert into emp VALUES(7900,'james','clerk',7698,'1981-12-03',800,null,30);
insert into emp VALUES(7902,'ford','analyst',7566,'1981-12-03',800,null,20);
insert into emp VALUES(7934,'miller','clerk',7788,'1981-01-23',800,null,10);
-- 创建工资等级表
create table salgrade(
grade int,-- 等级
losal double,-- 最低工资
hisal double -- 最高工资
);
insert into salgrade VALUES(1,700,1200);
insert into salgrade VALUES(2,1201,1400);
insert into salgrade VALUES(3,1401,2000);
insert into salgrade VALUES(4,2001,3000);
insert into salgrade VALUES(5,3001,9999);
-- 返回拥有员工的部门名、部门号
select a.deptno,a.dname from dept a join emp b on a.deptno = b.deptno group by deptno;
-- 工资水平多余smith的员工信息
select * from emp where sal > (select sal from emp where ename = 'smith');
-- 返回员工和所属经理的姓名
select a.ename '员工',b.ename '经理' from emp a join emp b on a.mgr=b.empno;
-- 返回雇员的雇佣日期早于经理雇佣日期的员工及其经理名字
select a.ename '员工',a.hiredate,b.ename '经理',b.hiredate from emp a join emp b on a.mgr = b.empno and a.hiredate < b.hiredate;
-- 返回员工姓名及所在部门名称
select a.ename '员工',b.dname '部门' from emp a join dept b on a.deptno = b.deptno;
-- 返回从事clerk工作的员工姓名和所在部门名称
select a.ename '员工',b.dname '部门' from emp a join dept b on a.deptno = b.deptno and job = 'clerk';
-- 返回部门号及其部门的最低工资
select deptno,min(sal) from emp group by deptno;
-- 返回销售部（sales）所有员工的姓名
select ename from emp a,dept b where a.deptno = b.deptno and b.dname = 'sales';
-- 返回工资水平多于平均工资的员工
select * from emp where sal > (select avg(sal) from emp);
-- 返回与scott从事相同工作的员工
select * from emp where job = (select job from emp where ename = 'scott') and ename != 'scott';
-- 返回工资高于30部门所有员工工资水平的员工信息
select * from emp where sal > all(select sal from emp where deptno = 30);
-- 返回员工工作及其从事此工作的最低工资
select job,min(sal) from emp GROUP BY job;
-- 计算出员工的年薪，并且以年薪排序
select ename,(sal * 12 + ifnull(comm,0)) yearsal from emp ORDER BY (sal * 12 + ifnull(comm,0));
-- 返回工资处于第四级别的员工姓名
select ename from emp where sal between (select losal from salgrade where grade = 4) and (select hisal from salgrade where grade = 4);
-- 返回工资为二等级的职员名字、部门所在地
select a.ename,b.loc
from emp a
join dept b on a.deptno = b. deptno
join salgrade c on sal between (select losal from salgrade where grade = 2) and (select hisal from salgrade where grade = 2) group by a.empno;
select
b.ename,a.loc
from dept a
join emp b on a.deptno = b. deptno
join salgrade c on grade = 2 and b.sal>= c.losal and b.sal <= hisal;
select
b.ename,a.loc
from dept a,emp b,salgrade c
where a.deptno = b. deptno and grade = 2 and b.sal>= c.losal and b.sal <= hisal;
--聚合函数
-- 格式：group_concat([distinct] 字段名 [order by 排序字段 asc/desc] [separator '分隔符'])
create database mydb4;
use mydb4;
create table emp(
emp_id int primary key auto_increment comment '编号',
emp_name char(20) not null default '' comment '姓名',
salary decimal(10,2) not null default 0 comment '工资',
department char(20) not null default '' comment '部门'
);
insert into emp(emp_name,salary,department)
values('张晶晶',5000,'财务部'),('王飞飞',5800,'财务部'),('赵刚',6200,'财务部'),('刘小贝',5700,'人事部'),
('王大鹏',6700,'人事部'),('张小斐',5200,'人事部'),('刘云云',7500,'销售部'),('刘云鹏',7200,'销售部'),
('刘云鹏',7800,'销售部');
-- 将所有员工的名字合并成一行
select group_concat(emp_name) from emp;
select group_concat(emp_name separator ';') from emp;
-- 指定分隔符合并
select department,group_concat(emp_name separator ';' ) from emp group by department;
-- 指定排序方式和分隔符
select department,group_concat(emp_name separator ';' ) from emp group by department;
select department,group_concat(emp_name order by salary desc separator ';' ) from emp group by department;
-- 数学函数
-- abs（） 返回绝对值 select abs(-10); select abs(10); -- CEIL(x) 返回大于或等于 x 的最小整数 向上取整 select ceil(1.1);-- 2 select ceil(1.0);-- 1 -- FLOOR(x) 返回小于或等于 x 的最大整数 向下取整 select floor(1.1);-- 1 select floor(1.9);-- 1 -- GREATEST(expr1, expr2, expr3, ...) 返回列表中的最大值 select greatest(1,5,6,3,9);-- 9 -- LEAST(expr1, expr2, expr3, ...)返回列表中的最小值 select least(1,3,5,8,0);-- 0 -- MAX(expression) 返回字段 expression 中的最大值 -- select max(里面只能为表达式) -- MIN(expression) 返回字段 expression 中的最小值 -- 同上 -- MOD(x,y) 返回 x 除以 y 以后的余数 select mod(9,2);-- 1 -- PI() 返回圆周率(3.141593） select pi(); -- POW(x,y) 返回 x 的 y 次方 select pow(2,3);-- 8 -- RAND() 返回 0 到 1 的随机数 select rand();-- 0.05200662375614664 select floor(rand() * 100);-- 0-100区间，嵌套floor后可以取整 -- ROUND(x) 返回离 x 最近的整数（遵循四舍五入） select round(3.1415926);-- 3 select round(3.5415926);-- 4 -- ROUND(x,y) 返回指定位数的小数（遵循四舍五入） select round(3.14566,3);-- 3.146 select round(3.14536,3);-- 3.145 -- TRUNCATE(x,y)返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入） select truncate(3.1415,3);-- 3.141,不会四舍五入，直接截断到需要位置
-- 字符数函数
-- 1.获取字符串字符个数 charcter_length/char_length
select char_length('hello');-- 5
select char_length('你好啊');-- 3
-- length取长度，返回的单位是字节
select length('hello');-- 5
select length('你好啊');-- 9 一个汉字3个字节
-- 2.字符串合并 concat(没有分隔符)
select concat('hello','world');
select concat(c1,c2) from table_name;-- 可以将两列的表合在一起
-- 2.指定分隔符进行字符串合并 CONCAT_WS(x 指定分隔符, s1,s2...sn)
select concat_ws('-','hello','world');
-- 3.返回字符串在列表中第一次出现的位置 FIELD(s 指定查找,s1,s2...)
select field('aaa','aaa','bbb','ccc');-- 1
select field('aaa','bbb','aaa','ccc');-- 2
-- 4.去除字符串左边空格 LTRIM(s) /右边空格 rTRIM(s)/去除两端
select ltrim(' aaa');-- 去左
select rtrim('aaa ');-- 去右
select trim(' aaa ');-- 去两端
-- 5.字符串截取 MID(s,n,len)
-- 从字符串 s 的 n 位置截取长度为 len 的子字符串，同 SUBSTRING(s,n,len)
select mid("hellowwird",2,3);-- 从第二个字符开始截取，长度为3
-- 6.获取字符串a在b中出现的位置 POSITION(s1 IN s)
select position('abc' in 'hellowabcworld');-- abc在字符串第七的位置开始
-- 7.字符串替换 REPLACE(s,s1,s2) 将s中的s1换成s2
select replace('helloaaaworld','aaa','bbb');
-- 8.字符串翻转 REVERSE(s)
select reverse('hello');-- 将字符串hello的顺序反过来 olleh
-- 9.返回字符串后几个字符 RIGHT(s,n)
select right('hello',3);-- llo
-- 10.字符串比较 STRCMP(s1,s2)
select strcmp('hello','world');-- 比较字符串在字典中的先后顺序
-- 11.字符串截取 SUBSTR(s, start, length)/SUBSTRing(s, start, length)
select substr('hello',2,3);-- 从第二个字符开始截取，长度为3
select substring('hello',2,3);-- 从第二个字符开始截取，长度为3
-- 12.将小写转大写 UCASE(s)/UPPER(s)
select ucase('helloworld');
select upper('helloworld');
-- 13.将大写转为小写 LCASE(s)/LOWER(s)
select lcase('HELLOWORLD');
select lower('HELLOWORLD');
-- 查询员工的入职年
select ename,substr(hiredate,1,4) from emp;
select ename,substring(hiredate,1,4) from emp;
select ename,mid(hiredate,1,4) from emp;
-- 日期函数
-- 1.获取时间戳 UNIX_TIMESTAMP()
select unix_timestamp();
-- 2.将一个日期字符串转为毫秒值 UNIX_TIMESTAMP(DATE_STRING)
select unix_timestamp('2024-7-10 16:04:30');
-- 3.将毫秒值时间戳转为指定格式日期 FROM_UNIXTIME(BIGINT UNIXTIME[, STRING FORMAT])
select from_unixtime('1720598670','%Y-%m-%d %H:%i:%s');-- %Y-%m-%d %H:%i:%s 年-月-日 小时：分钟：秒
-- 4.获取当前年月日 CURDATE()/CURRENT_DATE()
select curdate();
select current_date();
-- 5.获取当前的时分秒 CURRENT_TIME()/CURTIME()
select current_time();
select curtime();
-- 6.获取当前的年月日，时分秒 CURRENT_TIMESTAMP()
select current_timestamp();
-- 7.从日期字符串中获取年月日 DATE()
select date('2022-12-12 12:34:54');-- 书写需要符合规范
-- 8.获取日期之间的差值 DATEDIFF(d1,d2)
select datediff('2024-7-10','2022-12-12');-- 前大后小
select datediff(current_timestamp(),'2022-12-12');
-- 9.获取时间的差值（秒级） TIMEDIFF(time1, time2)
select timediff('16:13:45','12:34:54');-- 前大后小
select timediff(curtime(),'12:34:54');
-- 10.日期格式化 DATE_FORMAT(d,f)
select date_format('2022-12-12 1:1:1','%Y-%m-%d %H:%i:%s');
select date_format('2022-12-1 10:13:1','%Y-%m-%d %H:%i:%s');
-- 11.将字符串转为日期 STR_TO_DATE(string, format_mask)
select str_to_date('2022-12-1 10:13:1','%Y-%m-%d %H:%i:%s');
-- 12.将日期进行减法 DATE_SUB(date,INTERVAL expr type)
-- 日期向跳转
select date_sub('2022-12-01',interval 2 day);
select date_sub('2022-12-01',interval 2 month);
-- 13.将日期进行加法 ADDDATE/DATE_ADD(d，INTERVAL expr type)
-- 日期向后跳转
select date_add('2022-12-01',interval 2 day);
select date_add('2022-12-01',interval 2 month);
-- 14.从日期中获取年月日，时分秒 EXTRACT(type FROM d)
select extract(hour from '2022-12-01 10:13:01');
select extract(year from '2022-12-01 10:13:01');
select extract(month from '2022-12-01 10:13:01');
-- 15.获取给定日期所在月的最后一天 LAST_DAY(d)
select last_day('2022-12-01'); -- 2022-12-31
-- 16. 获取指点年份和天数的日期 MAKEDATE(year, day-of-year)
select makedate('2021',66)
-- 17.根据日期获取年月日，时分秒
select year('2022-12-01 10:13:01'); -- 年
select month('2022-12-01 10:13:01'); -- 月
select day('2022-12-01 10:13:01'); -- 日
select hour('2022-12-01 10:13:01'); -- 时
select minute('2022-12-01 10:13:01'); -- 分
select second('2022-12-01 10:13:01');-- 秒
select quarter('2022-12-01 10:13:01');-- 获取季度
-- 18.根据日期获取信息
select monthname('2022-12-01 10:13:01');-- 返回日期当中的月份名称,英文
select dayname('2022-12-01 10:13:01'); -- 返回日期 d 是星期几,英文
select dayofmonth('2022-12-01 10:13:01');-- 计算日期 d 是本月的第几天
select dayofweek('2022-12-01 10:13:01');-- 日期 d 今天是星期几，1 星期日，2 星期一，以此类推
select dayofyear('2022-12-01 10:13:01');-- 计算日期 d 是本年的第几天
select weekday('2022-12-01 10:13:01');-- 日期 d 是星期几，0 表示星期一，1 表示星期二
select weekofyear('2022-12-01 10:13:01');-- 计算日期 d 是本年的第几个星期，范围是 0 到 53
select yearweek('2022-12-01');-- 202248 2022年第48周
-- yearweek返回年份及第几周（0到53），mode 中 0 表示周天，1表示周一，以此类推
select now();-- 返回当前日期和时间
-- 控制流函数
-- 重要 if() 如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2。 select if(5>3,'大于','小于'); use mydb3; select *,if(score >= 85,'优秀','及格') from score; -- ifnull(v1,v2) 如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。 select ifnull(5,0); select ifnull(null,0); use test1; select *,ifnull(comm,0) com from emp; -- 判断是否为null select isnull(5);-- 0 select isnull(null);-- 1 -- NULLIF(expr1, expr2) 比较两个字符串，如果字符串 expr1 与 expr2 相等 返回 NULL，否则返回 expr1 select nullif(12,12);-- null select nullif(12,13);-- 12
-- case when 语句
-
/* 格式： CASE expression WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... WHEN conditionN THEN resultN ELSE result END CASE 表示函数开始，END 表示函数结束。如果 condition1 成立，则返回 result1, 如果 condition2 成立，则返回 result2，当全部不成立则返回 result，而当有一个成立之后，后面的就不执行了。 */ select case 6 when 1 then 'nihao' when 2 then 'nb' when 5 then '正确' else 'wu' end as '结果'; select case when 2>1 then 'nihao' when 2<1 then 'nb' when 3>2 then '正确' else 'wu' end as '结果'; use mydb4; -- 创建订单表 create table orders( oid int primary key, -- 订单id price double, -- 订单价格 payType int -- 支付类型(1:微信支付 2:支付宝支付 3:银行卡支付 4：其他) ); insert into orders values(1,1200,1); insert into orders values(2,1000,2); insert into orders values(3,200,3); insert into orders values(4,3000,1); insert into orders values(5,1500,2); insert into orders values(6,1500,6); -- 输出各个oid的支付方式 -- 方式1 select * , case when payType=1 then '微信支付' when payType=2 then '支付宝支付' when payType=3 then '银行卡支付' else '其他支付方式' end as payTypeStr from orders; -- 方式2 select * , case payType-- 表达式写在case上 when 1 then '微信支付' when 2 then '支付宝支付' when 3 then '银行卡支付' else '其他支付方式' end as payTypeStr from orders;
-- 窗口函数
-- 数据准备
use mydb4;
create table employee(
dname varchar(20), -- 部门名
eid varchar(20),
ename varchar(20),
hiredate date, -- 入职日期
salary double -- 薪资
);
insert into employee values('研发部','1001','刘备','2021-11-01',3000);
insert into employee values('研发部','1002','关羽','2021-11-02',5000);
insert into employee values('研发部','1003','张飞','2021-11-03',7000);
insert into employee values('研发部','1004','赵云','2021-11-04',7000);
insert into employee values('研发部','1005','马超','2021-11-05',4000);
insert into employee values('研发部','1006','黄忠','2021-11-06',4000);
insert into employee values('销售部','1007','曹操','2021-11-01',2000);
insert into employee values('销售部','1008','许褚','2021-11-02',3000);
insert into employee values('销售部','1009','典韦','2021-11-03',5000);
insert into employee values('销售部','1010','张辽','2021-11-04',6000);
insert into employee values('销售部','1011','徐晃','2021-11-05',9000);
insert into employee values('销售部','1012','曹洪','2021-11-06',6000);
-- 序号函数
-- 对每个部门的员工按照薪资排序，并给出排名 row_number()
select
dname,
ename,
salary,
row_number() over(partition by dname order by salary desc) as rn1,
rank() over(partition by dname order by salary desc) as rn2,
dense_rank() over(partition by dname order by salary desc) as rn3
from employee;
-- 对每个部门的员工按照薪资排序，并给出排名 rank
select
dname,
ename,
salary,
rank() over(partition by dname order by salary desc) as rn
from employee;
-- 对每个部门的员工按照薪资排序，并给出排名 dense-rank
select
dname,
ename,
salary,
dense_rank() over(partition by dname order by salary desc) as rn
from employee;
-- 求出每个部门薪资排在前三名的员工- 分组求TOPN
select
* from
(
select
dname,
ename,
salary,
dense_rank() over(partition by dname order by salary desc) as rn
from employee
)t
where t.rn <= 3
-- 对所有员工进行全局排序（不分组）
-- 不加partition by表示全局排序
select
dname,
ename,
hiredate,
salary,
dense_rank() over( order by salary desc) as rn
from employee;
-- 从头到当前行的最大值
select
dname,
ename,
hiredate,
salary,
max(salary) over(partition by dname order by hiredate) as pv1
from employee;-- 从头到当前行的最大值
-- 从头到当前行的最小值
select
dname,
ename,
hiredate,
salary,
min(salary) over(partition by dname order by hiredate) as pv1
from employee;-- 从头到当前行的最小值
-- 从头到当前行的平均值
select
dname,
ename,
hiredate,
salary,
avg(salary) over(partition by dname order by hiredate) as pv1
from employee;-- 从头到当前行的平均值
-- 以此累加每个部门的薪资情况
select
dname,
ename,
hiredate,
salary,
sum(salary) over(partition by dname order by hiredate) as pv1
from employee;
select
dname,
ename,
hiredate,
salary,
sum(salary) over(partition by dname) as pv3
from employee; -- 如果没有order by排序语句 默认把分组内的所有数据进行sum操作
---------------------------------------------
-- 以此累加每个部门的薪资情况
select
dname,
ename,
hiredate,
salary,
sum(salary) over(partition by dname order by hiredate rows between unbounded preceding and current row) as c1 -- 从第一行数据加到当前行
from employee;
---------------------------------------------
select
dname,
ename,
hiredate,
salary,
sum(salary) over(partition by dname order by hiredate rows between 3 preceding and current row) as c1 -- 从当前行数据向上加三行
from employee;
---------------------------------------------
select
dname,
ename,
salary,
sum(salary) over(partition by dname order by hiredate rows between 3 preceding and 1 following) as c1 -- 从当前行数据向上加三行并向后加一行
from employee;
---------------------------------------------
select
dname,
ename,
salary,
sum(salary) over(partition by dname order by hiredate rows between current row and unbounded following) as c1 -- 从当前行数据加到最后一行
from employee;
-- 分布函数
- CUME_DIST和PERCENT_RANK
select
dname,
ename,
salary,
cume_dist() over(order by salary) as rn1, -- 没有partition语句 所有的数据位于一组
cume_dist() over(partition by dname order by salary) as rn2
from employee;
/*
rn1: 没有partition,所有数据均为1组，总行数为12，
第一行：小于等于3000的行数为3，因此，3/12=0.25
第二行：小于等于4000的行数为5，因此，5/12=0.4166666666666667
rn2: 按照部门分组，dname='研发部'的行数为6,
第一行：研发部小于等于3000的行数为1，因此，1/6=0.16666666666666666
*/
--------------------------------------------------------------------
select
dname,
ename,
salary,
rank() over(partition by dname order by salary desc ) as rn,
percent_rank() over(partition by dname order by salary desc ) as rn2
from employee;
/*
rn2:
第一行: (1 - 1) / (6 - 1) = 0
第二行: (1 - 1) / (6 - 1) = 0
第三行: (3 - 1) / (6 - 1) = 0.4
*/
-- 前后函数-LAG和LEAD
-- lag的用法
select
dname,
ename,
hiredate,
salary,
lag(hiredate,1,'2000-01-01') over(partition by dname order by hiredate) as time1,
lag(hiredate,2) over(partition by dname order by hiredate) as time2
from employee;
/*
last_1_time: 指定了往上第1行的值，default为'2000-01-01'
第一行，往上1行为null,因此取默认值 '2000-01-01'
第二行，往上1行值为第一行值，2021-11-01
第三行，往上1行值为第二行值，2021-11-02
last_2_time: 指定了往上第2行的值，为指定默认值
第一行，往上2行为null
第二行，往上2行为null
第四行，往上2行为第二行值，2021-11-01
第七行，往上2行为第五行值，2021-11-02
*/
-- lead的用法
select
dname,
ename,
hiredate,
salary,
lead(hiredate,1,'2000-01-01') over(partition by dname order by hiredate) as last_1_time,
lead(hiredate,2) over(partition by dname order by hiredate) as last_2_time
from employee;
-- 与lag相似，用法相反
-- 头尾函数-FIRST_VALUE和LAST_VALUE
-- 注意, 如果不指定ORDER BY，则进行排序混乱，会出现错误的结果 select dname, ename, hiredate, salary, first_value(salary) over(partition by dname order by hiredate) as first, last_value(salary) over(partition by dname order by hiredate) as last from employee;
-- 其他函数-NTH_VALUE(expr, n)、NTILE(n)
-- NTH_VALUE(expr, n) 查询每个部门截止目前薪资排在第二和第三的员工信息
select
dname,
ename,
hiredate,
salary,
nth_value(salary,2) over(partition by dname order by hiredate) as second_score,
nth_value(salary,3) over(partition by dname order by hiredate) as third_score
from employee
-- NTILE(n)
-- 根据入职日期将每个部门的员工分成3组
select
dname,
ename,
hiredate,
salary,
ntile(3) over(partition by dname order by hiredate) as rn
from employee;
-- 取出每个部门的第一组员工
select
*
from
(
SELECT
dname,
ename,
hiredate,
salary,
NTILE(3) OVER(PARTITION BY dname ORDER BY hiredate ) AS rn
FROM employee
)t
where t.rn = 1;
去除mysql数据中单位的方法
如果你希望在MySQL查询中简化通用的单位去除过程，可以考虑先使用REPLACE()函数替换掉所有非数字和单位的字符，然后再尝试提取纯数字。这种方法基于假设单位总是紧跟在数字后面。以下是步骤：
-
首先，使用
REPLACE()函数替换所有除了数字和中文数字以外的字符。例如，如果单位是中文的"元"，英文的"USD"或"EUR"等：
SqlSELECT
CAST(REPLACE(price, '[^0-9]', '') AS DECIMAL(10, 2)) AS simplified_price
FROM
orders;
这里[^0-9]是一个正则表达式，表示匹配任何不是数字的字符。
-
如果
REPLACE()之后还会有单位存在，那么可能需要再检查是否还有非数字字符。在这种情况下，可以使用FIND_IN_SET()或SUBSTR_INDEX()来确定最后一个数字的位置，然后截取到那一点：
SqlSELECT
SUBSTR(price, INSTR(price, '0', 1), LENGTH(price)) AS price_without_unit
FROM
orders;
INSTR()函数找出第一个数字字符的位置，LENGTH(price)则是获取整个价格字符串的长度，SUBSTR()函数截取这部分作为新字段。
注意，这个方法对输入格式有一定限制，如果单位不是紧挨着数字，或者有多种可能的单位形式，可能需要进一步优化或者额外校验。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
