MySQL学习第二天

第一部分给已经存在的表添加、删除约束1、使用alter命名去添加约束DDL添加主键alter table 表名 add primary key(列名1列名2)添加外键alter table 表名 add foreign key列名 references 主表列名添加唯一约束alter table 表名 add unique列添加非空约束alter table 表名 modify 列名 数据类型长度 not null添加默认值alter table 表名 modify column 列名 set default 默认值2、使用alter删除命令DDL获取约束信息 show create table 表名删除主键alter table 表名 drop primary key删除外键alter table 表名 drop foreign key 约束名删除唯一alter table 表名 drop index 约束名删除默认值alter table 表名 alter column 列名 drop default3、主键的自动增加一般在主键上id加上auto_increment,第一个auto_increment作用是设置主键自增这种情况下默认从1开始CREATE TABLE student ( sid INT primary key auto_increment,*/设置主键自增*/ sname VARCHAR(100), sage INT, ssex CHAR(3), snativeplace VARCHAR(100), smajor VARCHAR(100), sclass VARCHAR(100), snative VARCHAR(100) );第二个auto_increment表示从哪个数字开始CREATE TABLE student ( sid INT primary key auto_increment,*/设置主键自增*/ sname VARCHAR(100), sage INT, ssex CHAR(3), snativeplace VARCHAR(100), smajor VARCHAR(100), sclass VARCHAR(100), snative VARCHAR(100) )auto_increment 10001;4、comment可以在设计表中添加注释字段说明信息comment注释一般写在约束的后面CREATE TABLE student ( sid INT primary key comment用户id, sname VARCHAR(100), sage INT, ssex CHAR(3), snativeplace VARCHAR(100), smajor VARCHAR(100), sclass VARCHAR(100), snative VARCHAR(100) );5、DQL数据库表数据检索语句查询5.1基础查询语句全部没有指定列名语法select * from 表名;select*from studentselect*from markselect*from teacherselect*from course5.2查询指定字段列语法select 列1列2......from 表名查询学生的姓名、籍贯、民族select sname,snativeplace,snative from student;5.3条件查询语法select 列1列2 from 表 where 条件条件的写法①直接和某个值比较可以使用我们的比较运算符连接字段和值大于等于小于不等于小于等于大于等于比如sage20 sclass1班 cmark90②查询某个范围内的条件使用between and连接字段和值比如sage between 18 and 20年龄在18-20岁之间③null值作为条件不能直接使用比较运算符需要使用is null 或者 is not null比如sname is null不能用snamenull④已知部分条件的值需要做模糊查询like写法sname like _三_ sname like %小%通配符下划线_和百分号%是用来配合like做模糊查询的通配符下划线_表示匹配任意一个字符百分号%表示匹配任意多个字符特殊情况比如说我要找名字中带有s的人如果是%s%的话可能会出现名字的开头和末尾都有s如果%s的话只有名字的末尾有s如果s%的话只有名字的开头有s⑤多个条件的写法可以使用and 和 or去连接多个条件and表示并且的关系多个条件必须都满足才能查到数or表示或者的关系多个条件只要有一个满足就能查到数据如果同一个字段使用or连接也可以使用in代替in的使用-----查询来自江苏或者福建的女生的信息select*from student where ssex女 and (snativeplace江苏or snativeplace福建);select*from student where ssex女 and snativeplace in(江苏,福建);⑥给条件取反的用法 not可以在其他的比较运算符条件的前面加上not表示取反结果比如where not sage20 表示年龄不大于20----查询所有年龄超过20岁的学生姓名、班级 select snamesclass from student where sage20 -----查询所有年龄在20-22之间的学生信息 ①select * from student where sage between 20 and 22 ②select * from student where sage 20 and sage 22 -----查询员工表中有奖金的岗位有哪些 select *from emp where comm is not null -----查询员工表中没有奖金的岗位有哪些 select *from emp where comm is null ------查询返回所有姓徐的学生信息 select*from student where sname like 徐_ select*from student where sname like 徐% ----查询来自江苏地区的男生信息 select from student where ssex男 and snativeplace江苏 -----查询来自江苏地区的男生信息 SELECT * FROM student WHERE ssex 男 and snativeplace 江苏; -----查询来自上海或者福建地区的学生信息 SELECT * FROM student WHERE snativeplace 上海 or snativepl -----查询来自江苏或者福建的女生的信息 select*from student where ssex女 and (snativeplace江苏or snativeplace福建); select*from student where ssex女 and snativeplace in(江苏,福建);5.4数据去重distinct将查询结果中的重复数据去掉语法select distinct 列 from 表5.5查询数据排序select xx from 表名 where 条件order by字段 asc/descasc表示将结果升序排序desc表示将结果降序排序5.6分页查询select xx from 表名 where 条件order by字段 asc/desclimit 值1值2简略版limit 值1值2值1数据起始行表示数据从第几行开始显示值2每页显示几条数据显示第m页数据每页显示n条如果值1为0可以省略不写select*from 表名 limit m*n-n , n----查询1班的学生都来自哪些地区地区不重复 select distinct snativeplace from student where sclass1班 ----查询所有的学生按照年龄排序 select*from student order by sage ----查询返回第一页每一页展示五条数据 select*from student limit 0,5 ----查询返回第一页每二页展示五条数据 select*from student limit 5,51查询返回 1 班的女生中年龄最大的 2 个女生信息 select*from student where ssex女and sclass1班 order by sage desc limit 2;6、数据库中的函数6.1函数是什么函数是数据库系统把一些需要执行的功能封装起来放到一个函数的块中然后给这段代码取一个名字称为函数名通过调用函数完成这个函数对应的功能的调用执行。6.2函数的格式函数的格式函数名参数1参数2调用函数的写法select 函数名值1值2from 表6.3函数的分类①字符串函数②数字操作函数③日期操作函数6.3.1字符串函数----转大写 upper、转小写lowerselect upperhello select lowerHELLO;----把所有的员工名字专为小写 select lowerename from emp----字节数lengthstr 、字符串长度char_lengthstrselect length中国char_length中国----求员工中哪些员工名字长度是5 select*from emp where char_lengthename5----字符串截图 substringstrpos或substring_indexstrdelim,countsubstring_indexstr ,-1取空格后面substring_indexstr ,1取空格前面select substringhello2从第二位开始截取 select substringhello23从第二位开始截取截取三个字符 substring_indexhello ,1;从指定的分隔位置截取取空格的前面------字符串拼接 concatstr1str2......select concathello,world,java;---------helloworldjava-----字符串查找 instrstrsubstrselect instrhelloworld,o;-----查找o出现的位置------字符串替换 replacestrfrom_str,to_strselect replacehelloworld,o,x;-----把o替换成x------消除字符串前后的空格 trim重要的代码函数可以嵌套在所有的姓名的前面拼接上Dear返回返回后员工的首字母大写其他字母小写 select concat(Dear,substring(ename,1,1),LOWER(substring(ename,2)))from emp;6.3.2数学操作函数 select 函数 向上取整 ceilx向下取整floorx四舍五入 roundxdd表示保留几位小数没有d则不保留小数位截取 truncatexd截断数值保留d个小数位随机数 rand---0-1之间随机小数求余 modnm---整除后余下的值求1-10之间的随机整数 select ceilrand*106.3.3日期操作函数当前时间now、sysdate、current_date()、current_time()返回日期dateexpr 括号内的expr表示将来可以存的一个值返回时间timeexprselect datenowdatehiredatefrom empselect timenowtimehiredatefrom empyeardate、monthdate、daydate 年月日里面的date可以用代表日期的列名hourtime、minutetime、secondtime 时分秒date_add(data,interval expr unit) 根据单位给日期加上指定的值date_sub(data,interval expr unit) 根据单位给日期减去指定的值获取十个月后的日期 select date_add(now,interval 10 month) 获取十天前的日期 select date_sub(now,interval 10 day) last_day(date)表示获取某个日期的当月最后一天的日期select last_day(now返回当前时间的所在月份的最后一天7月31日select last_dayhiredatefrom emp计算两个日期的时间差 timestampdiffunitdatetime_expr1,datetime_expr2select timestampdiffyear2000-20-10,now();返回周几 dayofweekselectdayofweeknow-----周日1 周一2select weekofyearnow---返回年份的第n周-- 获取员工表中哪些员工是入职当月前5天入职的 select*FROM emp where day(hiredate)5; -- 获取员工表中哪些员工是入职当月后5天入职的 select*from emp where TIMESTAMPDIFF(DAY,hiredate,LAST_DAY(hiredate))5; -- 获取员工表中哪些员工是5月份入职的 select*from emp where month(hiredate)5; -- 编写sql查询出上周五的日期 SELECT DATE_SUB(now(),interval DAYOFWEEK(now())1 day);6.3.4其他函数条件函数 if ifexpr1expr2expr3如果expr1成立就返回expr2的值否则就返回expr3的值select if 32,true,false;-----返回学生分数表中超过90分的学生分数并把其设为优秀select *ifcmark90,优秀,普通from mark where cmark90;null值操作函数null值和任何数值计算完成结果还是null---计算每个人的总工资salcomm select *salcomm from emp----奖金为null结果也为null使用函数ifnullexpr1expr2如果expr1为null那么整个函数结果就是expr2不为null就返回expr1-- 计算每个人的总工资 comm奖金如果为null就设为0 select * , sal ifnullcomm,0) fromemp; select *, sal if(comm is null,0,comm) from emp;6.3.5分组函数聚合函数分组函数在计算的时候默认会把符合条件的数据分成一个组分组函数一般不和普通字段出现在select后面除非使用group by语句sum求和max最大值min最小值avg()平均值count数量----求1班学生年龄之和 select sumsage from student where sclass1班----求1班学生平均年龄最大年龄最小年龄和数量 select avgsagemaxsageminsagecountsage from student where sclass1班count数量count函数的特点使用count函数求数量的时候可以使用count字段或者count*或者count1如果字段中存在null值使用count字段或者count*返回结果会不同因为默认情况下分组函数不会统计null----获取有奖金的员工数量 select countcommcount*count1from emp6.3.6group by语句 手动分组语句group by可以让我们自己决定把那些数据分为一个组后统计数据group by后面跟上要分组的字段自动会将这个字段值相同的行分为一个组group by使用特点当普通字段和分组函数同时出现在select后面这个时候普通字段也需要出现在group by后面----分班级去统计学生的平均年龄数量最大年龄 select sclass avgsagecount1maxsage from student group by sclass ----分班级去统计男生的平均年龄数量最大年龄 select sclassssexavgsagecount1maxsage from student where ssex男 group by sclass ----分班级分性别去统计学生的平均年龄数量最大年龄 select sclassssexavgsagecount1maxsage from student where ssex男 group by sclassssex6.3.7 having语句having语句 过滤分组数据语句-- 分组函数的结果不能使用where去比较-- 把分数函数的结果去做条件比较要使用having而不是where-- 注意 having后面条件的写法格式和where后面条件写法格式基本相同--having一般只用来过滤分组函数其他的条件都写where后面-- 哪个班级的平均年龄超过了20岁SELECT sclass,avg(sage)FROM studentgroup by sclasshaving avg(sage) 20 ;6.3.8小总结执行语句的执行顺序select 5from 1where 2group by 3having 4order by 6第二部分