11sql语句.docx
《11sql语句.docx》由会员分享，可在线阅读，更多相关《11sql语句.docx（21页珍藏版）》请在冰豆网上搜索。
11sql语句
数据库：
保存在硬盘上的文件
数据库产品：
采用一种优良的数据结构来保存数据，方便我们对数据进行查询和修改
sql语句：
用于操作数据库的语句
数据库database表table
创建create
查看show
修改alter
删除drop
所有对数据库和表单的操作就是上面六个单词的组合
//创建一个数据库
createdatabasemydb;
//删除一张表
droptablemytable;
一、数据库的操作
创建一个名称为mydb1的数据库。
createdatabasemydb1;
创建一个使用utf-8字符集的mydb2数据库。
createdatabasemydb2charactersetutf8;
创建一个使用utf-8字符集，并带校对规则的mydb3数据库。
createdatabasemydb3charactersetutf8collateutf8_general_ci;
查看所有的数据库
showdatabases;
查看数据库的创建语句
showcreatedatabasemydb2;
删除前面创建的mydb3数据库
dropdatabasemydb3;
dropdatabaseifexistsmydb3;如果存在才会删除
数据库的修改
数据库一旦创建名称无法修改，字符集和校对规则可以改
把mydb2的字符集修改为gb2312
alterdatabasemydb2charactersetgb2312;
备份数据库
//使用数据库
usemydb2;
//创建表
createtablea
（
namevarchar（20）
）;
//插入数据
insertintoa（name）values（'aaa'）;
insertintoa（name）values（'bbb'）;
备份数据库
mysqldump-uroot-prootmydb2>d:
\a.sql
删除mydb2
dropdatabasemydb2;
恢复数据库实际上是恢复数据库中的数据
创建数据库使用数据库
createdatabasemydb3;
usemydb3;
sourced:
\a.sql
实际上source命令用于执行一段sql脚本
二、表的操作
创建表
id整形
name字符型
gender字符型或bit型
brithday日期型
entry_date日期型
job字符型
Salary小数型
resume大文本型
createtableemployee
（
idint,
namevarchar（20）,
gendervarchar（6）,
birthdaydate,
entry_datedate,
jobvarchar（20）,
salaryfloat,
resumetext
）;
修改表
在上面员工表的基础上增加一个image列
altertableemployeeaddimageblob;
修改job列，使其长度为60
altertableemployeemodifyjobvarchar（60）;
删除gender列。
altertableemployeedropgender;
表名改为users。
renametableemployeetousers;
修改表的字符集为utf-8
altertableuserscharactersetutf8;
列名name修改为username
altertableuserschangecolumnnameusernamevarchar（40）;
查看所有的表
showtables;
查看表的创建语句
showcreatetableusers;
查看表的结构
descusers;
//删除表
droptableemployee;
三、表中数据的操作（重点）
crud：
createreadupdatedelete
sql语句
1.insert语句增加数据
employee.sql
createtableemployee
（
idint,
namevarchar（20）,
gendervarchar（10）,
birthdaydate,
salaryfloat,
entry_datedate,
resumetext
）;
插入三条记录
insertintoemployee（id,name,gender,birthday,salary,entry_date,resume）values（1,'zhangsan','male','1980-1-1',1000,'2000-3-16','goodboy'）;
insertintoemployee（id,name,gender,birthday,salary,entry_date,resume）values（2,'lisi','male','1983-1-1',1000,'2010-3-16','goodboy'）;
insertintoemployee（id,name,gender,birthday,salary,entry_date,resume）values（3,'xiaohong','female','1984-1-1',1000,'2008-3-16','goodgirl'）;
insertintoemployee（id,name,gender,birthday,salary,entry_date,resume）values（4,'王五','男','1983-1-1',1000,'2010-3-16','一个好男孩'）;
//查看数据库的所有编码
showvariableslike'character%';
character_set_client使用的客户端编码
character_set_results结果集的编码
设置的方式
setcharacter_set_client=gbk
setcharacter_set_results=gbk
//创建一张a表
createtablea
（
idint,
namevarchar（20）
）;
insertintoavalues（1,'aaa'）;
insertintoa（name）values（'bbbb'）;
inserta（id,name）values（2,'cccc'）,（3,'dddd'）;
规范的方式书写
insertintoa（id,name）values（6,'eeeee'）;
查看表中的数据
select*fromemployee;
2.update语句更新数据
将所有员工薪水修改为5000元。
updateemployeesetsalary=5000;
将姓名为’zhangsan’的员工薪水修改为3000元。
updateemployeesetsalary=3000wherename='zhangsan';
将姓名为’lisi’的员工薪水修改为4000元,gender改为female。
updateemployeesetsalary=4000,gender='female'wherename='lisi';
将xiaohong的薪水在原有基础上增加1000元。
updateemployeesetsalary=salary+1000wherename='xiaohong';
3.delete语句删除数据
删除表中name为’zhangsan’的记录。
deletefromemployeewherename='zhangsan';
删除表中所有记录。
deletefromemployee;
使用truncate删除表中记录。
删除表再创建表
truncateemployee;
4.select语句查询数据
student.sql
createtablestudent（
idint,
namevarchar（20）,
chinesefloat,
englishfloat,
mathfloat
）;
insertintostudent（id,name,chinese,english,math）values（1,'张小明',89,78,90）;
insertintostudent（id,name,chinese,english,math）values（2,'李进',67,53,95）;
insertintostudent（id,name,chinese,english,math）values（3,'王五',87,78,77）;
insertintostudent（id,name,chinese,english,math）values（4,'李一',88,98,92）;
insertintostudent（id,name,chinese,english,math）values（5,'李来财',82,84,67）;
insertintostudent（id,name,chinese,english,math）values（6,'张进宝',55,85,45）;
insertintostudent（id,name,chinese,english,math）values（7,'黄蓉',75,65,30）;
insertintostudent（id,name,chinese,english,math）values（8,null,75,65,30）;
查询表中所有学生的信息。
select*fromstudent;
查询表中所有学生的姓名和对应的英语成绩。
selectname,englishfromstudent;
过滤表中重复数据。
selectdistinctenglishfromstudent;
在所有学生分数上加10分特长分。
selectname,chinese+10aschinese,math+10,english+10fromstudent;
统计每个学生的总分。
selectname,chinese+math+englishfromstudent;
使用别名表示学生分数。
selectname,chinesec,englishefromstudent;
查询姓名为李一的学生成绩
select*fromstudentwherename='李一';
查询英语成绩大于90分的同学
select*fromstudentwhereenglish>90;
查询总分大于200分的所有同学
select*fromstudentwhereenglish+chinese+math>200;
查询英语分数在80－90之间的同学。
两头的值都包含
select*fromstudentwhereenglishbetween80and90;
查询数学分数为89,90,91的同学。
select*fromstudentwheremathin（89,90,30）;
查询所有姓李的学生成绩。
select*fromstudentwherenamelike'李%';
查询所有姓李的,名字是两个字的学生成绩。
select*fromstudentwherenamelike'李_';
查询数学分>80，语文分>80的同学。
select*fromstudentwheremath>80andchinese>80;
查询英语>80或者总分>200的同学
select*,chinese+english+mathassssfromstudentwhereenglish>80orchinese+english+math>200;
对数学成绩排序后输出。
select*fromstudentorderbymath;
对总分排序后输出，然后再按从高到低的顺序输出
select*,chinese+math+englishfromstudentorderbychinese+math+englishdesc;
对姓李的学生成绩排序输出
select*fromstudentwherenamelike'李%'orderbychinese;
合计函数--count
统计一个班级共有多少学生？
selectcount（*）fromstudent;
统计数学成绩大于90的学生有多少个？
selectcount（*）fromstudentwheremath>90;
统计总分大于230的人数有多少？
selectcount（*）fromstudentwheremath+chinese+english>230;
sum只对数值类型起作用
统计一个班级数学总成绩？
selectsum（math）fromstudent;
统计一个班级语文、英语、数学各科的总成绩
selectsum（math）,sum（chinese）,sum（english）fromstudent;
统计一个班级语文、英语、数学的成绩总和
selectsum（math+chinese+english）fromstudent;
统计一个班级语文成绩平均分
selectsum（chinese）/count（*）fromstudent;
selectsum（chinese）/count（chinese）fromstudent;
求一个班级数学平均分？
selectavg（chinese）fromstudent;
求一个班级总分平均分
selectavg（chinese+math+english）fromstudent;
求班级语文最高分和最低分
selectmax（chinese）fromstudent;
groupby子句用于分类查询
orders.sql
createtableorders（
idint,
productvarchar（20）,
pricefloat
）;
insertintoorders（id,product,price）values（1,'电视',900）;
insertintoorders（id,product,price）values（2,'洗衣机',100）;
insertintoorders（id,product,price）values（3,'洗衣粉',90）;
insertintoorders（id,product,price）values（4,'桔子',9）;
insertintoorders（id,product,price）values（5,'洗衣粉',90）;
对订单表中商品归类后，显示每一类商品的总价
//归类
select*fromordersgroupbyproduct;
selectproduct,sum（price）fromordersgroupbyproduct;
查询购买了几类商品，并且每类总价大于100的商品
selectproduct,sum（price）fromordersgroupbyproducthavingsum（price）>100;
四、表的约束
有的时候针对某个列，我们不希望别人插入任意的数据，这样会导致错误
例如插入null值，取数据的时候就会出错
例如有的列我们希望数据是有唯一性的（不允许重复）
notnull非空约束这一列不允许插入null值
createtablea
（
namevarchar（20）notnull
）;
insertintoa（name）values（'aaa'）;
insertintoa（name）values（null）;
//id需要唯一unique唯一约束限定了列的值不能重复
createtableb
（
idintnotnullunique,
namevarchar（20）
）;
insertintob（id,name）values（1,null）;
insertintob（id,name）values（2,'zhangsan'）;
insertintob（id,name）values（2,'lisi'）;
//只用唯一约束
createtablec
（
idintunique,
namevarchar（20）
）;
insertintoc（name）values（'aaa'）;
insertintoc（name）values（'bbb'）;
insertintoc（id,name）values（1,'ccc'）;
//通常来讲我们会根据id来查询某条记录id一般会加非空约束、唯一约束
//主键约束=非空约束+唯一约束
createtabled
（
idintprimarykey,
namevarchar（20）
）;
insertintod（name）values（'aaa'）;不行，因为非空
insertintod（id,name）values（1,'aaa'）;
insertintod（id,name）values（1,'bbb'）;不行，唯一要唯一
通常来讲如果主键定义为int我们为了方便会定义主键自定增长
createtablee
（
idintpr（参考下载：
）imarykeyauto_increment,
namevarchar（20）
）;
insertintoe（name）values（'aaa'）;
insertintoe（id,name）values（4,'bbb'）;
//声明联合主键
createtablef
（
firstnamevarchar（20）,
lastnamevarchar（20）,
primarykey（firstname,lastname）
）;
insertintof（firstname,lastname）values（'aaa','bbb'）;
insertintof（firstname,lastname）values（'aaa','ccc'）;
insertintof（firstname）values（'eee'）;
//增加主键约束
createtableg
（
idint,
namevarchar（20）
）;
//增加两条记录
insertintogvalues（1,'aaa'）;
//增加主键约束但是如果主键列有重复或者为空的情况，主键约束加不上
altertablegaddprimarykey（id）;
//删除主键约束
createtableh
（
idintprimarykey,
namevarchar（20）
）;
altertablehdropprimarykey;
夫妻关系系统
创建老公表
createtablehusband
（
idintprimarykeyauto_increment,
namevarchar（20）
）;
insertintohusband（name）values（'张三'）;
insertintohusband（name）values（'李四'）;
insertintohusband（id,name）values（3,'王五'）;
创建老婆表
createtablewife
（
idintprimarykeyauto_increment,
namevarchar（20）,
husbandidint
）;
insertintowife（name,husbandid）values（'小红',2）;
insertintowife（name,husbandid）values（'小兰',3）;
insertintowife（name,husbandid）values（'小黑',1）;
insertintowife（name,husbandid）values（'小黄',8）;
//小兰来找老公
select*fromhusbandwhereid=3;
//删除王五
deletefromhusbandwhereid=3;
//外键约束
某一列数据是参照另一张表的某一个列这是就需要加外键约束
好处：
外键列只能插入参照列存在的值，参照列被参照的值是不能删除的
createtablewife
（
idintprimarykeyauto_increment,
namevarchar（20）,
husbandidint,
constrainthusbandid_FKforeignkey（husbandid）referenceshusband（id）
）;
表的关系有三种情况
多对一
在多的一方建立外键
多对多
需要创建中间表描述关系
中间表有两个字段都是外键参照两个表的主键列，同时这两列又是联合主键
一对一
分清主从关系
在从的一方建立外键此时应将主键直接作为外键
多对一
创建部门表
createtabledepartment
（
idintprimarykeyauto_increment,
namevarchar（20）
）;
创建员工表
droptableifexistsemployee;
createtableemployee
（
idintprimarykeyauto_increment,
namevarchar（20）,
departmentidint,
constraintdepartmentid_FKforeignkey（departmentid）referencesdepartment（id）
）;
insertintodepartment（name
升级会员 