2023年6月27日发(作者：)

sql完成对数据库表数据的CRUD操作插⼊数据:insert into 表名(列名1,列名2,列名3) values (值1,值2,值3);例⼦:insert into student(sid,sname,sex,age) values (1,'zhangsan',0,21);[写列名] 简单写法: insert into student values(2,'lisi',1,32);[不加列名]注:简单写法,如果插⼊式全列名的数据,表名后⾯的列名可以省略.id不要重复,否则会报以下错误:插⼊其中⼏列:insert into 数据库表名(列名1,列名2) values (值1,值2);[对的]例⼦:insert into student(sid,sname) values (3,'wangwu');[对的]insert into 数据库表名 (sid,sname) values (值1,值2)[错的]注:如果是插⼊部分列的话,列名不能省略.批量插⼊:insert into 数据库表名 (列名1,列名2,列名3) values (值1,值2,值3),(值1,值2,值3),(值1,值2,值3);例⼦:insert into student(sid,sname,sex,age) values (1,'zhangsan',0,21),(2,'zhangsan',0,21),(3,'zhangsan',0,21);注:单条插⼊与批量插⼊的效率:批量插⼊的效率更⾼.假设需插⼊10条数据,单条插⼊:插⼊10次即可.批量插⼊:⼀次性插⼊10条即可,但,如果,中间有数据错了,接下来未插⼊的数据就不会执⾏了.查看表中数据:select * from 表名;例⼦:select * from student;删除记录:delete from 表名 [where 条件];例⼦:delete from student where id = 2;delete from 表名;如果没有指定条件,会将表中数据⼀条⼀条全部删除掉.例⼦:delete from student;⾯试问题:请说⼀下,delete 删除数据和truncate 删除数据有什么差别?delete:是⼀个关键字,属于DML,⼀条⼀条删除表中的数据.truncate:属于DDL,先删除表在重建表.关于哪条执⾏效率⾼:具体要看表中的数据量 如果数据⽐较少,delete⽐较⾼效. 如果数据⽐较多,truncate⽐较⾼效.更新表记录:update 表名 set 列名=列的值,列名2=列的值2 [where 条件];例⼦:将sid为5的名字改为李四.如果参数是字符串,⽇期,要加上单引号,如果是数字,直接写即可.update student set sname='李四' where id=5;[可⾏]update student set sname='李四',sex=0;[可⾏,不加where条件会将表中sex列的值都改为0]查询记录:select [distinct] [ * ] [列名1,列名2] from 表名 [where条件];distinct:去处重复数据select:选择显⽰哪些列的内容商品分类:⼿机数码,鞋靴箱包1.分类ID2.分类名称3.分类描述create table cayegory(cid int primary key auto_increment,cname vatchar(30),cdesc varchar(50));insert into category values (null,'⼿机数码','全都是数据数码,⼿机,电脑');insert into category values (null,'鞋靴箱包','全都是鞋靴箱包,男鞋,⼥鞋');insert into category values (null,'美酒美⾷','全都是美酒美⾷,葡萄酒,⽕锅');insert into category values (null,'零⾷','全都都是零⾷,⽠⼦,花⽣');select * from category;select cname,cdesc from category;所有商品:1.商品id2.商品名称3.商品价格4.⽣产⽇期5.商品分类id商品和商品分类关系:所属关系create table product(pid int primary key auto_increment,pname varchar(20),price double,pdate timestamp,cno int);insert into product values(null,'⼩⽶',998,null,1);insert into product values(null,'⾏李箱',200,null,2);insert into product values(null,'葡萄酒',666,null,3);insert into product values(null,'⽠⼦',10,null,4);简单查询:查询所有商品:select * from product;查询商品名称和商品价格:select pname,price from product;别名查询 as关键字 as关键字可省略.表列名:select , from product p;(主要是⽤在多表查询)[不使⽤as关键字]select , from product as p;[使⽤as关键字]列别名:select pname as 商品名称,price as 商品价格 from product;select pname,price from product;select pname as 商品名称,price as 商品价格 from product;[使⽤as关键字]select pname 商品名称,price 商品价格 from product;[不使⽤as关键字]去掉重复的值: 查询商品所有的价格: select price from product; select distinct price from product;[使⽤distinct关键字去重]select运算查询:[仅仅在查询结果上做了运算 + - * / ] select *,price*1.5 from product; select *,price*1.5 as 折后价 from product; select *,price*0.8 from product;//打⼋折条件查询[where关键字]:where关键字:指定条件,确定要操作的记录.查询商品价格>60元的所有商品信息:select * from product where price > 60;where后的条件写法:关系运算符:> >= < <= = != <><>:不等于 标准sql语法!=:不等于 ⾮标准sql语法[sqlserver]查询商品价格不等于88的所有商品价格select * from product where price <> 88;select * from product where price != 88;查询商品价格在10到100之间:select * from product where price > 10 and price < 100;between ...关键字:select * from product where price between 10 and 100;逻辑运算 :and ,or ,not查询商品价格⼩于35或者商品价格⼤于900:select * from product where price < 35 or price >900;like:模糊查询:-:代表⼀个字符%:代表的是多个字符查询出名字中带有饼的所有商品:使⽤% '%饼%'select * from product where pname like '%饼%';查询第⼆个名字是熊的所有商品:使⽤_ '_熊%'select * from product where pname like '_熊%';in在某个范围内获得值:查询出商品分类ID在1,4,5⾥⾯的所有商品:select * from product where cno in(1,4,5);排序查询:order by 关键字asc:ascend 升序 [默认的排序⽅式]desc:descend 降序查询所有商品,按照价格进⾏排序select * from product order by price;[结果默认是升序]查询所有的商品,按照价格降序排序(asc-升序,desc-降序)select * from product order by price desc;查询名称有 ⼩ 的商品,按照价格降序排序: 1.查询出名称有 ⼩ 的商品select * from product where pname like '%⼩%'; 2.进⾏排序,得出结果select * from product where pname like '%⼩%' order by asc;聚合函数:sum():求和avg():求平均值count():统计数量max():最⼤值min():最⼩值1.获得所有商品价格的总和 select sum(price) from product;2.获得所有商品的平均价格select avg(price) from product;3.获得所有商品的个数select count(*) from product;注:where条件后⾯不能接聚合函数,否则会报错查询商品价格⼤于平均价格的所有商品1.查出所有商品select * from product;2.条件 ⼤于平均价格select avg(price) from product;3.两个结果合在⼀块(⼦查询)select * from product > (select avg(price) from product);[对的]select * from product where price > avg(price);[错的]分组:group by1.根据cno字段分组,分组后统计商品的个数select cno,count(*) from product group by cno;2.根据cno分组,分组统计每组商品的平均价格 并且商品平均价格 >601.根据cno分组,分组统计每组商品的平均价格.select cno,avg(price) from product group by cno;2.加条件 商品平均价格 >60select cno,avg(price) from product group by cno having avg(price) > 60;having 关键字 可以接聚合函数 出现在分组之后where关键字 不可接聚合函数 出现在分组之前sql的编写顺序:----S...F....W...G...H...Oselect ...der by执⾏顺序:F ... W...G...H...S...Ofrom ... where ...group by ... having ... select ... order bysql练习:-------------------------------------------------------------------------数据库操作sql练习⼀、数据库的创建： 1、创建⼀个名称为mydb1的数据库 create database mydb1; 2、创建⼀个使⽤utf8字符集的mydb2数据库。 create database mydb2 character set utf8; create database mydb2 character set utf8; 3、创建⼀个使⽤utf8字符集，并带⽐较规则的mydb3数据库。 create database mydb3 character set utf8 collate utf8_general_ci;⼆、数据库的修改： 修改mydb2字符集为gbk; alter database mydb2 character set gbk;三、数据库的删除： 删除数据库mydb3。 drop database mydb3;四、数据库查看： 查看所有数据库。 show databases; 查看数据库mydb1的字符集 show create database mydb1;-----------------------------------------------数据库中表操作的sql练习⼀、创建表 1、创建⼀张员⼯表employee 字段 类型 id 整形 name 字符型 gender 字符型 birthday ⽇期型 entry_date ⽇期型 job 字符型 salary ⼩数型 resume ⽂本 2、创建⼀张员⼯表employee2 字段 类型 id 整形 name 字符型 gender 字符型 birthday ⽇期型 entry_date ⽇期型 job 字符型 salary ⼩数型 resume ⽂本 要求：把id 设置成主键，并且⾃动增长。name不允许为空。 create table employee (id int primary key auto_increment, name varchar(20) not null, gender varchar(10), birthday date, entry_date date, job varchar(30), salary double, resume text );⼆、删除表 1、删除employee2表 drop table employee;三、数据表的结构的修改： 1、在上⾯员⼯表的基本上增加⼀个image列。 alter table employee add image varchr(20); 2、修改job列，使其长度为60。 alter table employee modify job varchar(60); 3、删除gender列。 alter table employee drop gender; 4、表名改为user。 rename table employee to user; 5、修改表的字符集为utf8 alter table user character set utf8; 6、列名name修改为username alter table user change name username varchar(20) not null;四、查看表结构四、查看表结构 1、查看数据库内的所有表 show tables; 2、查看employee的建表语句 show create table employee; 3、查看employee的表结构 desc employee;----------------------------------------------------表记录的操作⼀、插⼊语句 ---insert 1、向employee中插⼊三个员⼯信息，要求员⼯姓名分别是zs,ls,wangwu⼆、更新语句 ---update 1、将所有员⼯薪⽔修改为5000元。 update employee set salary=5000 ; 2、将姓名为’zs’的员⼯薪⽔修改为3000元。 update employee set salary=3000 where name='zs'; 3、将姓名为’ls’的员⼯薪⽔修改为4000元,job改为ccc。 update employee set salary=3000,job='ccc' where name='ls'; 4、将wangwu的薪⽔在原有基础上增加1000元。 update employee set salary=salary+1000 where name='wangwu';

三、删除语句 ---delete 1、删除表中名称为’zs’的记录。 delete from employee where name='ls'; 2、删除表中所有记录。 delete from employee;四、查询语句 ---select create table exam( id int primary key auto_increment, name varchar(20) not null, chinese double, math double, english double ); insert into exam values(null,'关⽻',85,76,70); insert into exam values(null,'张飞',70,75,70); insert into exam values(null,'赵云',90,65,95); insert into exam values(null,'刘备',97,50,50); insert into exam values(null,'曹操',90,89,80); insert into exam values(null,'司马懿',90,67,65); 练习： 1、查询表中所有学⽣的信息。 select * from exam; 2、查询表中所有学⽣的姓名和对应的英语成绩。 select name,english from exam; 3、过滤表中重复数据。

4、在所有学⽣分数上加10分特长分。 5、统计每个学⽣的总分。 select *,chinese+math+english from exam; 6、使⽤别名表⽰学⽣分数。 select *,chinese+math+english as 总分 from exam; -----使⽤where⼦句 7、查询姓名为刘备的学⽣成绩 select * from exam where name='刘备'; 8、查询英语成绩⼤于90分的同学 select * from exam where english>90; 9、查询总分⼤于200分的所有同学 select * from exam where chinese+math+english>200; 10、查询英语分数在 80－90之间的同学。 select * from exam where english between 80 and 90; select * from exam where english>=80 and english<=90; 11、查询数学分数为89,75,91的同学。 select * from exam where math=89 or math=75 or math=91; select * from exam where math in(89,75,91); select * from exam where math in(89,75,91); 12、查询所有姓刘的学⽣成绩。 select * from exam where name like '刘%'; 13、查询所有姓刘两个字的学⽣成绩。 select * from exam where name like '刘_'; 14、查询数学分>80并且语⽂分>80的同学。 select * from exam where math>80 and chinese>80; 15、查询数学分>80 或者 语⽂分>80的同学。 select * from exam where math>80 or chinese>80; ------使⽤order by 排序 16、对数学成绩排序后输出。 select * from exam order by math; 17、对总分排序按从⾼到低的顺序输出 select *,chinese+math+english as 总分 from exam order by 总分 desc; select *,chinese+math+english as 总分 from exam order by chinese+math+ english desc; 18、对姓刘的学⽣成绩排序输出 select * from exam where name like '刘%' order by math desc; ------使⽤count（函数） 19、统计⼀个班级共有多少学⽣？ select count(id) from exam; 20、统计数学成绩⼤于或等于90的学⽣有多少个？ select count(math) from exam where math>=90; 21、统计总分⼤于250的⼈数有多少？ select count(id) from exam where chinese+math+english>250; -------使⽤sum函数 22、统计⼀个班级数学总成绩？ selcet * from exam; 23、统计⼀个班级语⽂、英语、数学各科的总成绩 select sum(chinese),sum(math),sum(english) from exam; 24、统计⼀个班级语⽂、英语、数学的成绩总和

select sum(chinese)+sum(math)+sum(english) from exam; 25、统计⼀个班级语⽂成绩平均分 select sum(chinese)/count(id) from exam; --------使⽤avg函数 26、求⼀个班级数学平均分？ select avg(math) from exam; 27、求⼀个班级总分平均分 select avg(ifnull(chinese,0))+avg(ifnull(math,0))+avg(ifnull(english,0)) from exam; -------使⽤max，min函数 28、求班级最⾼分和最低分（数值范围在统计中特别有⽤） select max(ifnull(chinese,0)+ifnull(math,0)+ifnull(english,0)) from exam; select min(ifnull(chinese,0)+ifnull(math,0)+ifnull(english,0)) from exam;

create table orders( id int, product varchar(20), price float ); insert into orders(id,product,price) values(1,'电视',900); insert into orders(id,product,price) values(2,'洗⾐机',100); insert into orders(id,product,price) values(3,'洗⾐粉',90); insert into orders(id,product,price) values(4,'桔⼦',9); insert into orders(id,product,price) values(5,'洗⾐粉',90); 29、查询购买了⼏类商品，并且每类总价⼤于100的商品 select product,sum(price) from orders where price>100 group by product;