通过看代码示例进行最快的学习理解和记忆
提示：以下是本篇文章正文内容，下面案例可供参考
代码如下（示例）：
insert into student values ('张三',18,'男',2023005774,'2005-02-26');
insert into student values ('李四',20,'女',2023005775,'2005-02-26');
update student set name = 'wangle',age = 19 where no = 2023005774;#修改
delete from student where no = 2023005775;#删
insert into student values ('李四',20,'女',2023005775,'2005-02-26');
select name,age,no from student;
insert into student values ('老张',20,'男',1,'2005-02-26'),
('李李',17,'女',2,'2005-02-26'),
('李王',19,'男',3,'2005-02-26'),
('李九',19,'女',4,'2005-02-26'),
('怀谦',21,'男',5,'2005-02-26'),
('忘川',25,'男',6,'2005-02-26'),
('烘炉',22,'女',7,'2005-02-26'),
('点穴',17,'女',8,'2005-02-26');
select name,age,no from student;#查
update student set no = 9 where no = 2023005775;#改
select name,age,no from student;#查
select *
from student;
#查……
select student.name as '学生姓名' from student;#查并取别名
select student.age as '学生年龄' from student;
select student.gender as '性别' from student;
select student.no as '学号' from student;
select *
from student;
select *
from student where age > 20;#where给个条件
select * from student where age between 20 and 21;#在……之间
select count(*) from student where age = 15;#计数
select avg(student.age) from student;#平均
select gender,count(*) from student group by gender;#分组查
select student.gender,avg(age) from student group by gender;
#改下生日
update student set birth = '2005-03-19' where no = '1';
update student set birth = '2005-02-27' where no = '2';
update student set birth = '2005-04-29' where no = '3';
update student set birth = '2005-05-21' where no = '4';
update student set birth = '2004-06-18' where no = '5';
update student set birth = '2005-07-11' where no = '6';
update student set birth = '2004-04-09' where no = '7';
update student set birth = '2004-03-19' where no = '8';
update student set birth = '2004-03-19' where no = '9';
select student.name,age,count(*) from student where age > 20 group by gender having count(*) > 1;#有点抽象
select *
from student order by student.age asc ;#升序
select *
from student order by age desc;#降序
select *
from student limit 0,3;#查，分页展示
select *
from student limit 3,3;#第二页
select * from student limit 6,3;#第三页
create user 'itcast'@'localhost' identified by '123456';#创用户
alter user 'itcast'@'localhost' identified with mysql_native_password by '1234';#改密码
drop user 'itcast'@'localhost';#删用户
create user 'test'@'%' identified by '123456';
show grants for 'test'@'%' ;#看权限
grant all on itcast.student to 'test'@'%';#给权限
revoke all on itcast.student from 'test'@'%';#取消权限
drop user 'test'@'%';
select concat('hello','world');
select lower('I HATE U');
select upper('i love u');
select lpad('01',5,'-');#左填充
select rpad('01',5,'-');#右填充
select trim(' z l x ');#只去除头部和尾部的空格
select substring('hello world',1,5);#切片
select ceil(1.5);#向上取整
select floor(1.5);
select mod(4,2);#取模
select rand();
select round(3.1415926,4);#保留小数
select curdate();
select curtime();
select now();
select year('2005-02-26');
select month('2005-02-26');
select adddate('2005-02-26',INTERVAL 2 month );
select datediff('2025-03-28','2005-04-04');
#流程
select
name,
(case when age > 20 then '大哥姐' else '小弟妹' end)
from student;
select
name,
(case age when 25 then '大哥' when 20 then '小弟' else '兄长' end)
from student;
#约束
create table user(
id int primary key auto_increment,#非空唯一，自动增长
name varchar(10) not null unique ,#不为空，唯一约束
age int check ( age > 0 and age < 120 ),
status char(1) default '1',#未指定时默认
gender char(1)
);
drop table user;
create table userr(
id int primary key auto_increment comment '主键', #auto_increment 表示：自动增长
name varchar(10) not null unique comment '姓名', #非空且唯一
age int check(age > 0 and age < 120),
gender char(1) comment '性别',
status char(1) default '1' comment '状态'
)comment '用户表';
insert into userr(name,age,status,gender) values ('Tom1',19,'1','男'),
('Tom2',25,'0','男');
insert into userr(name,age,status,gender) values ('Tom3',19,'1','男');
insert into userr(name,age,status,gender) values (null,19,'1','男');
#由于姓名为空，无法创建
insert into userr(name,age,status,gender) values ('Tom3',19,'1','男');
#由于名字我们规定有唯一性，所以当名字相同时会报错，但已经申请了主键，因此在接下面的命令中，添加用户的id将由5开始
insert into userr(name,age,status,gender) values ('Tom4',80,'1','男');
insert into userr(name,age,status,gender) values ('Tom5',-1,'1','男');
#如果是5.7的话可能因为版本问题check不会强制执行，这句可能不报错
insert into userr(name,age,status,gender) values ('Tom5',121,'1','男');
#两条年龄都会显示为无效值，无法添加
delete from userr where id = '6';
insert into userr(name,age,gender) values ('Tom5',120,'男');
#检测其status（状态）在不输入值时，是否为默认值1
drop table userr;
#创建部门表
create table dept01(
id int auto_increment comment 'ID' primary key,
name varchar(50) not null comment '部门名称'
)comment '部门表';
#向部门表添加数据
INSERT INTO dept01 (id, name) VALUES (1, '研发部'), (2, '市场部'),(3, '财务部'), (4,
'销售部'), (5, '总经办');
#创建员工表
create table emp01(
id int auto_increment comment 'ID' primary key,
name varchar(50) not null comment '姓名',
age int comment '年龄',
job varchar(20) comment '职位',
salary int comment '薪资',
entrydate date comment '入职时间',
managerid int comment '直属领导ID',
dept_id int comment '部门ID'
)comment '员工表';
#向员工表添加数据
INSERT INTO emp01 (id, name, age, job,salary, entrydate, managerid, dept_id)
VALUES
(1, '金庸', 66, '总裁',20000, '2000-01-01', null,5),(2, '张无忌', 20,
'项目经理',12500, '2005-12-05', 1,1),
(3, '杨逍', 33, '开发', 8400,'2000-11-03', 2,1),(4, '韦一笑', 48, '开
发',11000, '2002-02-05', 2,1),
(5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1),(6, '小昭', 19, '程
序员鼓励师',6600, '2004-10-12', 2,1);
alter table emp01 add constraint fk_emp01_dept01_id foreign key (dept_id) references dept01(id) ;
alter table emp01 drop foreign key fk_emp01_dept01_id;#添加外键，之后删除试试
alter table emp01 add constraint fk_emp01_dept01_id foreign key (dept_id) references dept01(id) on UPDATE cascade on delete cascade ;#cascade就是子表跟着改变
#这部分外键方面的学习建议大家去看https://yardon.blog.csdn.net/article/details/129979943?fromshare=blogdetail&sharetype=blogdetail&sharerId=129979943&sharerefer=PC&sharesource=logohjiji&sharefrom=from_link
#创建部门表
create table dept01(
id int auto_increment comment 'ID' primary key,
name varchar(50) not null comment '部门名称'
)comment '部门表';
#向部门表添加数据
INSERT INTO dept01 (id, name) VALUES (1, '研发部'), (2, '市场部'),(3, '财务部'), (4,
'销售部'), (5, '总经办');
#创建员工表
create table emp01(
id int auto_increment comment 'ID' primary key,
name varchar(50) not null comment '姓名',
age int comment '年龄',
job varchar(20) comment '职位',
salary int comment '薪资',
entrydate date comment '入职时间',
managerid int comment '直属领导ID',
dept_id int comment '部门ID'
)comment '员工表';
#向员工表添加数据
INSERT INTO emp01 (id, name, age, job,salary, entrydate, managerid, dept_id)
VALUES
(1, '金庸', 66, '总裁',20000, '2000-01-01', null,5),(2, '张无忌', 20,
'项目经理',12500, '2005-12-05', 1,1),
(3, '杨逍', 33, '开发', 8400,'2000-11-03', 2,1),(4, '韦一笑', 48, '开
发',11000, '2002-02-05', 2,1),
(5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1),(6, '小昭', 19, '程
序员鼓励师',6600, '2004-10-12', 2,1);
alter table emp01 add constraint fk_emp01_dept01_id foreign key (dept_id) references dept01(id) ;
alter table emp01 drop foreign key fk_emp01_dept01_id;
alter table emp01 add constraint fk_emp01_dept01_id foreign key (dept_id) references dept01(id) on UPDATE cascade on delete cascade ;
#恢复上面两表的数据继续
select job,dept01.name
from emp01,dept01;#多表联合查询，笛卡尔积
select emp01.name,dept01.name from emp01,dept01 where dept_id = dept01.id;#等值连接
#将dept_id中的某一个写成null然后 继续
select * from emp01 inner join dept01 on dept_id = dept01.id;#内连接，两表都满足这一条件的共同数据
select * from emp01 left join dept01 on dept_id = dept01.id;#左连接,A中所有和B中满足条件的数据
select *from emp01 left join dept01 on emp01.dept_id = dept01.id where dept01.id is null ;#还是左连接查询，加了条件就变成了,A中除去和B共同满足的数据剩下的数据
#右连接，B中所有和A中满足条件的
select *
from emp01 right join dept01 on dept01.id = dept_id ;
#联合查询，相当于并集
select * from emp01 left join dept01 on emp01.dept_id = dept01.id
union
select * from emp01 right join dept01 on emp01.dept_id = dept01.id;
#这里首先把我们的表最大工资的人改了，不要放在第一个
#这种方法里面的select只获得了最高的薪水,但没有对应姓名等信息，我们得到的还是表中第一个人的姓名和信息
#子表查询，就是将select 得到的一个表作为外面select查询所用的表
select t.name,t.salary from (select name,salary,max(salary) from emp01) as t;
select t.name,t.age from (select emp01.name,age,max(age) from emp01) as t;
#错误原因可以运行下面这两行，就明白了
select emp01.name,age,max(age) from emp01;
select name,salary,max(salary) from emp01;
#子表查询where,这样得到的才是正确的结果
select name,salary from emp01 where salary = (select max(salary) from emp01);
#any是任意一个的意思，我们下面找的就是工资比那些工资在6000-10000的这些人中的任意一个都大的人
#可以看到表中出现了8400工资的人，因为他就比一个6600 的人高，就是说any只要比任意一个高就行
select name,salary from emp01 where salary > any(select salary from emp01 where salary >= 6000.00 and salary <= 10000.00);
#all就是全部
select name,salary from emp01 where salary > all(select salary from emp01 where salary >= 6000.00 and salary <= 10000.00);
#in的用法
select * from emp01 where dept_id in (select id from dept01 where dept01.name in ('销售部','市场部'));
select * from emp01 where exists(select name from dept01 where name ='研发部') and age >20 and dept_id = (select id from dept01 where name ='研发部')
仅供参考
扫一扫
496
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
