sql语句集合

#创建数据库
create database mydata_db;
#查看数据库中的数据表
show tables;

#1.创建数据库表
#格式：
#create table 表名称(
#列名1 数据类型 [约束条件],
#列名2 数据类型 [约束条件],
#列名3 数据类型 [约束条件],
#列名4 数据类型 [约束条件],
#…
#列名n 数据类型 [约束条件],
#);
#注意：1.数据库表列的数据类型
#数据库表列的数据库类型就是用来规定这个列中可以保存什么类型的数据。
#常见的数据库表的数据类型：
#数值型【数字类型】：tinyint、smallint、int、digint、float、double
#字符串型: varchar 、char
#逻辑性:bit [0 false/ 1 true]
#日期型:date[日期]、time[时间]、datatime[日期+时间]
#大数据类型：blob[大量的字节数据] 、 text[大量的字符数据][不常用，因为查询太慢]
#假如我们创建了一个保存书籍的数据库表，这个中有一列叫内容，它的数据类型text,
#那么我们就可以给这个表的内容列保存一本书的具体内容，当我们查询这个数据库表的时候，
#就会被保存内容的数据列，拉低数据库表的查询速度。
#2.约束条件–限制添加到数据库表中的具体数据值。
#常见的约束
#1.主键约束–primary key 【唯一标识数据库表中的一条记录，不会重复，而且是唯一的】
#2.自动增长 ：auto_increment 【控制数据列的数据值自动增加】
#例如：第一行第一个数据列的值是1，那么我们不需要为这个列添加数据值，那么的第二行数据的
#第一列就是2，第三行第一列就是3…
#通常情况下自动增长与主键约束一起修饰数据库表的第一列
#所以往往数据表的第一列都是主键且自动增长。
#有些数据库不支持自动增长约束【例如：Oracle数据不支持自动增长约束】
#3.唯一约束–unique 【表示这个列的数据不能重复】
#4.非空约束–not null 【表示这个列的数据值不能有null】
#我们就可以创建一张数据库表啦
#例如：创建一个学生表
create table t_student(
stuid int primary key auto_increment,
stuname varchar(20) not null,
stuage int unique,
stuheight double,
stusex bit,
studay datetime
);
#创建一个学生表【t_student】
#第一列 stuid ，int类型 ，这一列是主键，还是自动增长
#第二列 stuname , varchar(20)，表示是一个字符串，字符的长度是20，超过就会出错
#使用not null 修饰，就是不能出现null值
#第三列 stuage int类型，有一个唯一约束，这个列的值不能重复
#第四列 stuheight ，double类型小数
#第五列 stusex bit [0 / 1]0表示男，1表示女
#第六列 studay datetime 时间日期型数据【yyyy-MM-dd HH:mm:ss】

#2.删除数据库表
#drop table 表名称;
drop table t_student;

#3.修改数据库表的表结构
#为数据库表重命名，修改数据表中的某一列，修改某一列的数据类型…
#3.1 为数据库表增加一个新列
#alter table 表名称 add 新列名 列的数据类型;
#例如：为上面的学生表增加一个保存地址的列
alter table t_student add stuaddress varchar(30);
#3.2修改某一个列的数据类型的长度
#alter table 表名称 modify 被修改的列 列的数据类型;
alter table t_student modify stuaddress varchar(60);
#3.3修改某一个列的名称
#alter table 表名称 change 旧列名 新列名 新列的数据类型;
alter table t_student change stuaddress address varchar(30);
#3.4 删除某一个列
#alter table 表名称 drop 被删除的列名;
alter table t_student drop address;
#3.5 修改数据库表的名称
#rename table 旧表名 to 新表名;
rename table t_student to student;

#4 查看表结构
#就是查看这个有几个列，每一列叫什么名字，是什么数据类型的，使用了什么约束
#desc 表名称;
desc student;

#5 insert 语句向表中插入数据。

intsert into 表名称[(列名1,列名2…列名n)] values(数据值1,数据值2…数据值n);

#注意事项：
#1.插入的数据应与列的数据类型相同。
#例如：student表中stuage列它的数据类型是int，那么我们在给这个列添加数据的时候只能是整数。
#2.数据的大小应在列的规定范围内
#例如：student表中stuname列的数据长度是20，那么我们在添加数据的时候就要将数据值保持在20个数据以内
#3.在values中列出的数据位置必须与被加入的列的排列位置相对应
#4.字符和日期型数据应包含在单引号中。
#5.可以插入空值但是处过not null 约束修饰的列
#5.1 向表中的所有列添加数据
#insert into 表名称 values(数据值1,数据2,…数据值n);
#如果某一个列没有数据值，使用null代替，不能空出。
insert into student values(null,‘zhangsan’,23,null,0,‘2020-03-31’);

insert into student values(null,‘zhangsan’,23,168.5,0,‘2020-01-31’);
insert into student values(null,‘lisi’,24,148.5,1,‘2020-02-23’);
insert into student values(null,‘wangwu’,25,158.5,0,‘2020-03-31’);
insert into student values(null,‘java’,26,198.5,1,‘2020-04-13’);
insert into student values(null,‘android’,27,188.5,0,‘2020-05-16’);
insert into student values(null,‘iphone’,28,178.5,1,‘2020-06-25’);

#5.2 向表中指定的列条件数据
#insert into 表名称(列名1,列名2…列名n) values(数据值1,数据2,…数据值n);
insert into student(stuname,stuage,stusex) values(‘lisi’,24,true);

#6 使用 update语句修改表中数据。
#update 表名称 set 列名1=新的数据值1,列名2=新的数据值2…列名n=新的数据值n where 修改条件;
#set 列名1=新的数据值1,列名2=新的数据值2…列名n=新的数据值n 修改被修改的列以及该列对应的数据值；

where 修改条件; 如果没有修改条件，那么所有行的数据都会被修改成相同的值

#例如：修改studnt表中stuname为lisi的这一行记录的stuheight值为168.5
update student set stuheight=168.5 where stuname=‘lisi’;
#例如：修改studnet表中的stuname为张三或者stuid为2的数据记录的stuheight为178.5，何studay为’2020-03-31’
update student set stuheight=178.5,studay=‘2020-03-31’ where stuname=‘zhangsan’ or stuid=2;
#例如：修改student表中的stuname为张三并且stuage为23的数据记录的stusex值为1/false
update student set stusex=1 where stuname=‘zhangsan’ and stuage=23;
#为修改语句条件“where 条件”，实际上是为了准确的获取到被修改的数据记录
#或者–or [可以有多个] 只要有一个修改条件符合要求就执行修改
#并且–and [可以有多个] 所用的修改条件都符合要求才执行修改

#7.使用 delete语句删除表中数据。
#删除表我们使用“drop table 表名称”，他会删除表中的所有数据记录，以及表结构。
#删除表中的数据记录，我们使用delete语句，它只删除数据表中的数据记录，保留表结构。
#delete from 表名称 where 删除条件；
#例如：删除studnet表中stuname为张三的数据记录
delete from student where stuname=‘zhangsan’;
#例如：删除studnet表中stuname为lisi后者stuid为1的数据记录
delete from student where stuname=‘lisi’ or stuid=1;
#例如：删除student表中stuname为iphone,并且stuage为28的数据记录
delete from student where stuname=‘iphone’ and stuage=28;

#8 查询语句
#8.1查询所有列数据
#select * from 表名称;
select * from student;
#8.2 查询指定列的数据
#select 列名1，列名2，…列名n from 表名称;
select stuname,stuage,stusex from student;
#8.3 有条件的查询
#select * from 表名称 where 查询条件
#select 列名1，列名2，…列名n from 表名称 where 查询条件;
#例如：查询stuname为张三的数据记录
select * from student where stuname=‘zhangsan’;
#例如：查询stuid为4或者stuage为25的数据记录
select * from student where stuid=4 or stuage=25;
#例如：查询stuname为java并且stuage为25的数据记录
select * from student where stuname=‘java’ and stuage=25;
#Mysql数据库的查询动作
#查询操作是数据库操作中最复杂动作，以为查询的时候使用的查询条比较繁琐。
#创建一个测试用的数据库
drop database mydata_db;
create database mydata_db;
#查看数据库中的数据表
show tables;
#创建一个测试用的数据表
create table t_person(
perid int primary key auto_increment,
pername varchar(20),
persex bit,
perage int,
peraddress varchar(30),
perday datetime
);
#向数据库库表中添加一些测试数据
insert into t_person values(null,‘张三’,0,20,‘西安’,‘2001-04-01’);
insert into t_person values(null,‘李四’,1,21,‘北京’,‘2002-05-02’);
insert into t_person values(null,‘王五’,0,22,‘上海’,‘2003-06-03’);
insert into t_person values(null,‘赵六’,1,23,‘天津’,‘2004-07-04’);
insert into t_person values(null,‘张三1’,0,24,‘深圳’,‘2005-08-01’);
insert into t_person values(null,‘李四2’,1,25,‘广州’,‘2006-09-02’);
insert into t_person values(null,‘王五3’,0,26,‘珠海’,‘2007-10-03’);
insert into t_person values(null,‘赵六4’,1,27,‘云南’,‘2008-11-04’);
insert into t_person values(null,‘zhangsan’,0,20,‘西安’,‘2001-04-01’);
insert into t_person values(null,‘lisi’,1,21,‘北京’,‘2002-05-02’);
insert into t_person values(null,‘wangwu’,0,22,‘上海’,‘2003-06-03’);
insert into t_person values(null,‘zhaoliu’,1,23,‘天津’,‘2004-07-04’);
insert into t_person values(null,‘zhangsan1’,0,24,‘深圳’,‘2005-08-01’);
insert into t_person values(null,‘lisi2’,1,25,‘广州’,‘2006-09-02’);
insert into t_person values(null,‘wangwu3’,0,26,‘珠海’,‘2007-10-03’);
insert into t_person values(null,‘zhaoliu4’,1,27,‘云南’,‘2008-11-04’);
#数据库表的查询语句
#1.查询所有数据
select * from t_person;

#2 查询指定列的数据
select pername,persex,perage from t_person;

#3.有别名的查询
#别名–为了区分不同数据中的名称相同的表，名称相同列
#例如：第一个数据【database1】,有一张student表，第二个数据库【database2】,也有一个student表
#那么我们在查询的时候，就需要去区分一下不同数据中名称相同的表，这时可以使用别名。
#查询所有数据，为数据库表创建别名
select * from t_person as 个人信息表;
#查询指定列的数据,为数据库表和被查询的列添加别名
select pername as 姓名,perage as 年龄,peraddress as 地址 from t_person as 个人信息表;
#由于创建别名都是使用 “as 别名”，所以这个语法中的as是可以省略
select pername 姓名,perage 年龄,peraddress 地址 from t_person 个人信息表;
#数据库表的别名可以引用在列的名称前面，用于表示这个列是数据这张表
select 个人信息表.pername 姓名,个人信息表.perage 年龄,个人信息表.peraddress 地址 from t_person 个人信息表;

#4.条件查询
#4.1 比较运算符的查询条件【> < >= <= == <>】
#例如：查询年龄大于25的用户信息
select * from t_person where perage > 25;
#例如：查询年龄不等于25的用户信息
select * from t_person where perage <> 25;
#例如：查询年龄小于25的用户信息
select * from t_person where perage < 25;
#4.2 between min and max [查询某一个区间的数据值]
#例如：查询年龄在25到27之间的用户信息
select * from t_person where perage between 25 and 27;
#例如：查询时间日期在2006-01-01 到 2008-12-31 之间的用户信息
select * from t_person where perday between ‘2006-01-01’ and ‘2008-12-31’;
#4.3 in(数组)【查询包含在这个数组中的数据】
#例如:查询用户编号在【1,3,5,7】中的用户信息
select * from t_person where perid in(1,3,5,7,13,28);
#4.4 模糊查询 【like ‘zhang%’】
#例如：查询姓张的用户信息
select * from t_person where pername like ‘张%’;
#例如：查询名字叫“四”的用户信息
select * from t_person where pername like ‘%四’;
#例如：查询一个姓名中包含“an”字符串的用户信息
select * from t_person where pername like ‘%an%’;
#4.5 is null 查询某一列数据为null的数据记录
#例如：查询一个perday列为null的数据记录
update t_person set perday=null where perid=16;
select * from t_person where perday is null;
#4.6 and [逻辑与]所有查询条件为true的时候查询
#例如：查询perid=1并且perage=25并且peraddress=‘西安’
select * from t_person where perid=1 and perage=25 and peraddress=‘西安’;
#4.7 or [逻辑或]某一个查询条件为true的时候查询
#例如：查询perid=1或者perage=25或者peraddress=‘西安’
select * from t_person where perid=1 or perage=25 or peraddress=‘西安’;
#4.7 not [逻辑非]
select * from t_person where not (perage>25);

#5 order by 子句排序查询结果。
#两种情况【asc 升序】 【desc 降序】
#例如：查询的所有数据根据年龄升序排列
select * from t_person order by perage asc;
#例如：查询的所有数据根据年龄将序排列
select * from t_person order by perage desc;

#6 SQL聚合函数
#1.count(列名) 返回某一列，行的总数[总记录数]
#例如：t_person表中的总记录数
select count(*) as 总记录 from t_person;
#例如：t_person表中perday列对应的总记录数
select count(perday) as perday总记录 from t_person;
#2.sum 统计某一列的数据和
#例如:统计t_person中的年龄总和
select sum(perage) as 年龄总和 from t_person;
#3.avg 统计某一列的平均值
#例如：统计t_person中的年龄的平均值
select avg(perage) as 年龄的平均值 from t_person;
#4.max 统计某一列的最大值
#例如：统计t_person中的年龄的最大值
select max(perage) as 年龄的最大值 from t_person;
#5.min 统计某一列的最小值
#例如：统计t_person中的年龄的最小值
select min(perage) as 年龄的最小值 from t_person;

#7 分页查询
#需要使用"limit 参数1 , 参数2"
#参数1–每页数据的起始值 (当前页码 - 1)* 每页记录数
#参数2–每页记录数
#t_person表中共有16天记录，我们要将这16条记录分页显示，每页5条记录
#第一页
select * from t_person limit 0,5;
#第二页
select * from t_person limit 5,5;
#第三页
select * from t_person limit 10,5;
#第四页
select * from t_person limit 15,5;

#8 group by子句分组查询
#例如：将t_person表中的记录按照年龄分组，统计每一组有几个人
select perage as 年龄, count(perage) as 每组人数 from t_person group by perage;
#例如：将t_person表中的记录按照性别分组，统计每一组有几个人
select persex as 性别, count(persex) as 每组人数 from t_person group by persex;

#以上的查询就属于基本查询操作
#----------------------------------------------------------------------------------

#Mysql的高级查询部分
#多表设计–表与表之间的关系
#1.一对一
#2.一对多
#3.多对多
#我们如果需要作出多表设计，需要使用外键
#定义外键约束

foreign key(外键列) references 表(表中的某一个列)

#1.一对一 【外键关联结构】
#例如:一个用户对应一个身份证信息
#创建一个保存用户信息的数据库表
create table t_user(
uid int primary key auto_increment,
uname varchar(20),
uaddress varchar(30)
);
#创建一个保存身份证信息的数据库表
create table t_card(
cid int primary key auto_increment,
cnumber varchar(20),
corg varchar(30),
#创建一个外键列
#一对一的时候需要唯一约束
userid int unique,
#定义外键约束
foreign key(userid) references t_user(uid)
);
#添加测试数据
insert into t_user values(null,‘zhangsan’,‘西安’);
insert into t_user values(null,‘lisi’,‘天水’);
insert into t_card values(null,‘123456789012345678’,‘西安.长安区’,1);
insert into t_card values(null,‘222222222222222222’,‘甘肃.天水’,2);

#2.一对多
#例如:一个班级中可以有多个学生
#创建一个班级表
create table t_class(
cid int primary key auto_increment,
cname varchar(20),
ccode varchar(20)
);
#创建一个学生表
create table t_student(
stuid int primary key auto_increment,
stuname varchar(20),
stuage int,
stuaddress varchar(30),
#创建一个保存班级的外键列
classid int,
#定义外键约束
foreign key(classid) references t_class(cid)
);
#添加测试数据
insert into t_class values(null,‘javaEE班’,‘20191119’);
insert into t_class values(null,‘前端班’,‘20191015’);
insert into t_student values(null,‘zhangsan’,23,‘西安’,1);
insert into t_student values(null,‘lisi’,24,‘西安’,2);
insert into t_student values(null,‘李四’,23,‘西安’,1);
insert into t_student values(null,‘张三’,24,‘西安’,2);

#3.多对多
#例如：一个项目组可以有多个角色，一个角色可以属于多个项目组
#多对多的表结构设计需要专门的一张表来保存关系
#3张表
#创建一个项目组信息的组表
create table t_group(
gid int primary key auto_increment,
gname varchar(10),
ginfo varchar(20)
);
#创建一个角色信息的角色表
create table t_role(
rid int primary key auto_increment,
rname varchar(10),
rinfo varchar(20)
);
#创建一个需要专门的一张表来保存多对多的关系表
create table t_group_role(
id int primary key auto_increment,
#创建保存项目组id的外键
groupid int,
#创建一个保存角色id的外键
roleid int,
#定义外键约束
foreign key(groupid) references t_group(gid),
foreign key(roleid) references t_role(rid)
);
#添加测试数据
insert into t_group values(null,‘java项目组’,‘java程序开发’);
insert into t_group values(null,‘C++项目组’,‘C++程序开发’);
insert into t_role values(null,‘程序员’,‘编写程序’);
insert into t_role values(null,‘测试员’,‘测试程序’);
insert into t_group_role values(null,1,1);
insert into t_group_role values(null,1,2);
insert into t_group_role values(null,2,1);
insert into t_group_role values(null,2,2);

#Mysql的高级查询部分
#通过一个查询语句来获取不同表中的数据信息
#子查询—就是在一个大的查询语句中，又包含了一个小的查询语句
#例如：查询t_Person中年龄大于平均年龄的数据记录
select * from t_person where perage > (select avg(perage) from t_person);
#连接查询
#1.内连接查询 【inner join】
#2.左外连接查询 【left outer join/left join】
#3.右外连接查询 【right outer join/right join】
#内连接与左连接和右连接查询的区别
#内连接：显示左边右边共有的
#左连接：左边有的，右边没有的为null
#右连接：左边没有的，右边有的为null
#格式
#select [col1,col2…coln来自多张表【最好使用别名】]
#from table1
#inner join/
#left outer join/left join/
#right outer join/right join
#table2
#on table1.col = table2.col
#where 查询条件

#内连接：显示左边右边共有的
#例如：通过用户名为"zhangsan"的用户信息和用户的身份证信息
select 用户表.uid,用户表.uname,用户表.uaddress,
身份证表.cid,身份证表.cnumber,身份证表.corg
from t_user as 用户表
inner join
t_card as 身份证表
on 用户表.uid = 身份证表.userid
where 用户表.uname=‘zhangsan’;

#左连接
#例如：查询姓名为“zhangsan”的这个学生对应的班级信息
select 学生表.stuname,学生表.stuage,学生表.stuaddress,
班级表.cname,班级表.ccode
from t_student as 学生表
left outer join
t_class as 班级表
on 学生表.classid = 班级表.cid
where 学生表.stuname=‘zhangsan’;

#右连接
#例如：查询班级编号是“‘20191015’”的班级信息和所有的学生信息
select 班级表.cname,班级表.ccode,
学生表.stuname,学生表.stuage,学生表.stuaddress
from t_class as 班级表
right outer join
t_student as 学生表
on 班级表.cid= 学生表.classid
where 班级表.ccode=‘20191015’;