JavaWeb(mysql数据库语句)

目录

1.创建数据库表

2.mysql基本语句

3.高级查询

---

1.创建数据库表

-- 创建数据表的代码
#创建表的代码只能执行一次
#auto_increment表示自动递增
#primary key表示设置主键
create table student(
	id int(10) not null auto_increment,
	`name` varchar(20) not null,
	sex varchar(10) not null,
	age int(10),
	`subject` varchar(20),
	primary key(id)
);

2.mysql基本语句

#加;允许两条及以上语句同时运行，不加只能运行单行语句
#查询语句使用select
# *表示查询表中所有的列
select * from student;

#查询表中指定的几个列，列名之技安用逗号分割
select id,`name`,sex from student;

#查询语句后面加where叫做条件查询语句
select * from student where id = 1;

#新增语句insert后面数据顺序是表中显示的顺序
#default默认
insert into teacher value(default,'张老师','男',35,'java',1);
insert into teacher(id,t_name,t_age,t_subject,s_id) value(default,'王老师',35,'python',1);

#一条新增语句添加多条数据values
insert into teacher(id,t_sex,t_name,t_age,t_subject,s_id) values
(default,'男','杨老师',34,'java',3),
(default,'女','李老师',29,'MySQL',4),
(default,'男','周老师',38,'c++',6),
(default,'女','高老师',30,'G',8),
(default,'女','林老师',41,'JavaScript',2);

#修改语句update
#语句后面不加where表示修改的是表中所有人的该列数据
update student set age=age+1;

#加where条件，表示就修改id为3这个人的信息
update student set age=age+1,name='王二麻子' where id=3;

#删除表中的数据
#不加条件的删除表示的是清空表中的数据
delete from student;

#删除符合条件的id为7的数据
delete from student where id=7;

#使用指令删除数据库或数据表
#drop database 数据库名字
#drop table 数据表名字

3.高级查询

#排序order by 排序的列 asc
#asc表示升序排序 desc表示降序
select * from student order by age asc,id desc;
#重命名，查询过程中，临时重命名，as重命名的时候可以省略
select id as 编号,name as 姓名,sex 性别 from student;

#查询指定行数的数据limit 3 中3表示行数
select * from student limit 3;
#查询从第几行开始，查询几条数据limit1,3
#1表示从第几行开始，表示行数的索引，索引从0开始
#3表示查询几行数据
select * from student limit 1,3;

#模糊查询like _表示一个字符，%表示0到多个
select * from student where name like "王_";
select * from student where name like "王%";
#包含王字的同学
select * from student where name like "%王%";
#模糊查询between and 闭区间
select * from student where age between 18 and 20;
#模糊查询in范围查询  括号内数据是隐含=
select * from student where age in(16,20,21,22,25,28,30);
#模糊查询null 查询字段内容是否为空
select * from teacher where t_sex is null;
select * from teacher where t_sex is not null;

#聚合函数
#count统计表中有多少条数据
select count(*) from student;
#sum 统计表中所有人的年龄总和
select sum(age) from student;
#avg 统计表中所有人的平均年龄
select avg(age) from student;
#max 统计表中所有人的最大年龄
select max(age) from student;
#min 统计表中所有人的最小年龄
select min(age) from student;

#分组查询group by 分组
select max(age),sex from student group by sex;
#having 加分组的条件  每组中条数大于等于2的显示出来
select age from student group by age having count(age)>=2;

#子查询
#第一步select * from student where name="张三";
#第二步select age from student where name="张三";
#第三步也就是最终结果select * from student where age >(select age from student where name="张三");
select * from student where age >(select age from student where name="张三");
#比李四年龄大 比赵六年龄小 要的是开区间 between and是闭区间
select * from student where age >
	(select age from student where name="李四")and age <
		(select age from student where name="赵六");
#与张三李四年龄相等的同学信息
select * from student where age =
	(select age from student where name="张三")or age =
		(select age from student where name="李四")
#in范围查询
select * from student where age in
	(select age from student where name="张三" or name="李四")
	
#多表联合查询
#内联结
select * from student inner join teacher on student.id=teacher.s_id;
#外联结
#左联结 以左侧表为准 左侧表数据全部显示
select * from student left join teacher on student.id=teacher.s_id;
#右联结 以右侧表为准 右侧表数据全部显示
select * from student right join teacher on student.id=teacher.s_id;
#交叉联结
select * from student inner join teacher