数据库 - MySql语句
库的增删改查
-
增
create database testdb1; #创建数据库testdb1 create database testdb2 charset='gbk'; #创建指定编码的库testdb2
-
删
drop database testdb2; #删除testdb2
-
改
alter database testdb2 charset='utf8'; #修改testdb2编码为utf-8
-
查
show databases; #查看所有数据库 show create database testdb2; # 查看单个数据库testdb2
表的增删改查
-
前置
use testdb1; #切换库 select database(); #查看所在库的名称
-
增
create table t1(id int,name char); #创建一个id是int格式,name是char格式的t1 create table t1(id int,name char); #创建一个id是int格式,name是char格式,且长度是4的t1
-
删
drop table t2;
1. 删除字段名称
alter table t2 drop hobbies; #1.删除字段
2. 删除字段属性
alter table t1 modify id int not null; #1.删除自增 alter table t1 drop primary key; #2.删除字段主键
-
改
alter table t1 modify name char(16);
1. 修改表名
alter table t1 rename t11;
2. 修改字段名称及属性
alter table t11 modify department varchar(6) unique; #修改字段属性 alter table t11 change describle varchar(12) default '无' ; #修改字段名称,不能修改属性
-
查
show tables; #查看所有表 show create table t2; describe t2; desc t2; #简写
数据的增删改查
-
增
insert into t1 value(1,'甲'); #单独增加一条数据 insert into t1 value(2,'乙'),(3,'丙'),(4,'丁'); #一次性增加多个
-
删
delete from t1 where id=1; #删除id=1的数据 delete from t1; #清空t1表中所有数据
-
改
update t1 set name='ABCD' where id=4; #修改id=4的数据
-
查
select * from t1; #查看表中所有数据 select name from t1; #查看表中单独字段的数据 select * from pre_ucenter_members \G;
约束条件
-
default默认
create table t1(id int,name varchar(6),gender enum('female','male') default 'male' ); insert t1(name,id) values('jack',1);
-
unique唯一
1.单列唯一create table t2(id int unique,name varchar(6),gender enum('male','female') default 'male'); # 默认值要加字段名
2.联合唯一
create table t3(id int,ip char(16),port int,unique(ip,port)); insert t3 values(1,'127.0.0.1',8080),(2,'127.0.0.1',8081),(3,'127.0.0.1',8082); insert t3 values(3,'127.0.0.2',8080),(2,'127.0.0.12',8081),(3,'127.0.0.12',8082);
-
primary key主键
1、单单从约束效果上来看primary key等价于not null + unique,非空且唯一!!!
2、它除了有约束效果之外 它还是Innodb存储引擎组织数据的依据,Innodb存储引擎在创建表的时候必须要有primary key,因为它类似于书的目录 能够帮助提示查询效率并且也是建表的依据create table t4(id int primary key); insert t4 values(1); insert t4 values(null); #报错:Column 'id' cannot be null insert t4 values(1); #报错:Duplicate entry '1' for key 'PRIMARY'
一张表中有且只有一个主键 如果你没有设置主键 那么会从上往下搜索直到遇到一个非空且唯一的字段将它自动升级为主键
create table t5(id int not null unique,name varchar(6),age int);
如果表中没有主键也没有其他任何的非空且唯一字段 那么Innodb会采用自己内部提供的一个隐藏字段作为主键,隐藏意味着你无法使用到它 就无法提示查询速度
一张表中通常都应该有一个主键字段 并且通常将id/uid/sid字段作为主键- 单个字段主键
create table t6(id int primary key,name varchar(6));
- 联合主键(多个字段联合起来作为表的主键 本质还是一个主键)
create table t7 (id int,name varchar(6),phone int,primary key(id,phone));
-
auto_increment自增
注意auto_increment通常都是加在主键上的 不能给普通字段加
以后在创建表的id(数据的唯一标识id、uid、sid)字段的时候
id int primary key auto_increment#不能给非主键加自增 create table t9(id int auto_increment,name varchar(6),age int); # 报错:Incorrect table definition; there can be only one auto column and it must be defined as a key create table t8(id int primary key auto_increment,name varchar(6),age int); insert t8 values('rose',13); #报错:Column count doesn't match value count at row 1 insert t8(name,age) values('rose',13);
补充
删除表中数据后 主键的自增不会停止; 清空表数据并且重置主键
truncate t8;
表关系
外键就是用来帮助我们建立表与表之间关系的:foreign key
一对多
外键字段建在多的一方
多对多
自己开设第三张存储
一对一
建在任意一方都可以 但是推荐你建在查询频率较高的表中
1、一对多表关系 外键字段建在多的一方
2、在创建表的时候 一定要先建被关联表
3、在录入数据的时候 也必须先录入被关联表
-
表关系例子
create table grades(cid int primary key auto_increment,caption varchar(16) not null); create table teachers(tid int primary key auto_increment,tname varchar(6) not null); create table students( sid int primary key auto_increment, sname varchar(6) not null, gender enum('male','female') default 'male', class_id int, foreign key((class_id) references grades(cid) on update cascade on delete cascade); create table courses( cid int primary key, cname varchar(6), teacher_id int, foreign key(cid) references grades(cid) on update cascade on delete cascade, foreeign key(teacher_id) references teachers(tid) on update cascade on delete cascade ); create table results( rid int primary key auto_increment, student_id int, course_id int, number int not null, foreign key(student_id) references students(sid) on update cascade on delete cascade, foreign key(course_id) references courses(cid) on update cascade on delete cascade);
级联操作
on update cascade on delete cascade
- 复制表
1.不能复制主键,外键
2.指定复制create table t3 select * from t1;
create table t4 select * from t1 where id >3;
- 复制表
关键字查询
-
where
作用:是对整体数据的一个筛选操作
1. 精确查找
#1.查找uid是7的数据 select uid,username,email from pre_ucenter_members where uid >7; #2.查找uid在3和7之间的数据 select uid,username,email from pre_ucenter_members where uid >=3 and uid <=7; select uid,username,email from pre_ucenter_members where uid between 3 and 7; #3.查找regip是'127.0.0.1','113.4.146.14','27.189.221.197'的数据 select username from pre_ucenter_members where regip='127.0.0.1' or regip='113.4.146.14' or regip='27.189.221.197'; select username from pre_ucenter_members where regip in ('127.0.0.1','113.4.146.14','27.189.221.197'); #4.查看值为null select department from t11 where describle is null; #5.查找uid不在3和9之间的数据 select username,email from pre_ucenter_members where uid not between 3 and 9; #6.查找email不在'qianfan1@126.com','qianfan2@126.com','qianfan3@126.com'的数据 select username,email from pre_ucenter_members where email not in('qianfan1@126.com','qianfan2@126.com','qianfan3@126.com');
2. 模糊匹配
模糊查询 like % 匹配任意多个字符 _ 匹配任意单个字符
#1.查看email中含有qianfan字符的数据 select username from pre_ucenter_members where email like '%qianfan%'; #2.查看username中是五个字符的数据 select username,email from pre_ucenter_members where username like '_____';
-
group by
分组之后 最小可操作单位应该是组 还不再是组内的单个数据 没有严格模式: 返回的是分组之后 每个组的第一条数据 但是这不符合分组的规范:分组之后不应该考虑单个数据 而应该以组为操作单位(分组之后 没办法直接获取组内单个数据) 有严格模式:报错
1. 基本命令
#1.获取每个部门最大薪资 select post,max(salary) from emp group by post; #2.获取每个部门最低薪资,as作为别称,可加可不加 select post as '部门',min(salary) '最高薪资' from emp group by post; #3.获取各部门平均值 select post,avg(salary) from emp group by post; #4.获取各部门总值 select post,sum(salary) from emp group by post; #5.统计各部门人数 select post,count(name) from emp group by post; #可以 select post,count(id) from emp group by post; #可以 select post,count(post_comment) from emp group by post; #不可以,null
2. group_concat
group_concat不单单可以支持你获取分组之后的其他字段值 还支持拼接操作
#1.查询分组之后的部门名称和每个部门下所有的员工姓名 select post,group_concat(name) from emp group by post; #2.查询分组之后的部门名称和每个部门下所有的员工姓名和薪资 select post,group_concat(name,':',salary) from emp group by post;
3. 分组注意事项
关键字where和group by同时出现的时候group by必须在where的后面 where先对整体数据进行过滤之后再分组操作 where筛选条件不能使用聚合函数
# 报错:Invalid use of group function select name,age from emp where max(salary) >10000; #不分组 默认整体就是一组 select max(salary) from emp; #统计各部门年龄在30岁以上的员工平均薪资 select post,avg(salary) from emp where age>30 group by post;
-
having
having的语法根where是一致的 只不过having是在分组之后进行的过滤操作 即having是可以直接使用聚合函数的
#1.统计各部门年龄在30岁以上的员工平均工资并且保留平均薪资大于10000的部门 select post,avg(salary) from emp where age >30 group by post having avg(salary)>1000;
-
distinct
一定要注意 必须是完全一样的数据才可以去重!!!#会去重 select distinct age from emp; #不会去掉重复的年龄 select distinct id,age from emp;
-
order by
order by默认是升序asc,可以省略不写;也可以修改为降序:desc
#1.薪资升序排列 select * from emp order by salary; #2.查询薪资降序,相同薪资的年龄升序 select * from emp order by salary desc, age; #3.统计各部门年龄在10岁以上的员工平均工资并且保留平均薪资大于1000的部门,然后对平均工资降序排序 select post,avg(salary) from emp where age>10 group by post having avg(salary) > 1000 order by avg(salary) desc;
-
limit
#1.输出五条数据 select * from emp limit 5; #2.输出从1开始的五条数据 select * from emp limit 0,5; #3.输出从8开始的五条数据 select * from emp limit 7,5;
-
正则
正则是一门独立的语言 在python中如果你想使用正则需要借助于re模块 面试题 1.re模块中常用的方法 findall:分组优先展示 ^j.*(n|y)$ 不会展示所有正则表达式匹配到的内容 而仅仅展示括号内正则表达式匹配到的内容 match:从头匹配 search:从整体匹配 2.贪婪匹配与非贪婪匹配 正则表达式默认都是贪婪匹配的 将贪婪变成非贪婪只需要在正则表达式后面加? .* 贪婪 .*? 非贪婪
select * from emp where name regexp '^j.*(n|y)$';
多表操作
-
表查询
1.inner join,内连接只拼接两张表中公有的数据部分
select * from emp1 inner join dep on emp1.dep_id=dep.id;
2.left join,左连接
左表所有的数据都展示出来 没有对应的项就用NULL
select * from emp1 left join dep on emp1.dep_id=dep.id;
3.right join,右连接
右表所有的数据都展示出来 没有对应的项就用NULL
select * from emp1 right join dep on emp1.dep_id =dep_id;
4.union,全连接
左右两表所有的数据都展示出来select * from emp1 left join dep on emp1.dep_id=dep_id union select * from emp1 right join dep on emp1.dep_id=dep_id;
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· 百万级群聊的设计实践
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
· 永远不要相信用户的输入:从 SQL 注入攻防看输入验证的重要性