DQL查询

  1. 当gruop by出错时 进行以下操作
  3. 查询mysql安装文件：whereis mysql
  4. 编辑/etc/my.cnf文件，加入如下参数，
  5. sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
  7. 设置远程访问权限
  8. mysql -uroot -p        #登录
  9. use mysql;    #进入数据库
  10. GRANT ALL ON *.* TO 'root'@'%';    #配置远程访问权限
  11. update user set host = '%' where user = 'root'    #或是这样
  12. flush privileges;    #刷新
  14. 修改密码
  15. alter user 'root'@'%' identified with mysql_native_password by 'password';
  1. 1、查询名字中含有"金"字的学生信息
  2. select * from 学生表 where Sname like ‘%金%’
  4. 2、查询并统计同龄学生人数
  5. select Sage,count（Sid）from 学生表 group by Sage
  7. 3、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
  8. select Cid,avg(score) from 成绩 group by cid order by avg(Score),cid desc
  10. 4、按平均成绩从高到低显示所有学生的平均成绩
  11. select Sid,avg(Score) from 成绩 group by Sid order by avg(Score) desc
  13. 5、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分
  14. select sc.Cid 课程 ID, c.Cname 课程名, max(sc.Score) 最高分,min(sc.Score) 最低分,avg(sc.Score) 平均分 from 成绩表 sc left join 课程表 c on sc.cid=c.cid
  15. group by sc.cid
  17. 6、按各科平均成绩从低到高顺序排序
  18. select Cid, avg(Score) from 成绩表 group by Cid order by avg(Score) desc
  20. 7、查询学生的总成绩并进行排名
  21. select Sid 学号,sum(Score) 总分 from 成绩表 group by Sid order by sum(Score) desc
  23. 8、检索至少选修两门课程的学生学号
  24. select Sid from 成绩表 group by Sid having count(Cid) > 1;
  26. 9、查询每门课程被选修的学生数
  27. select Cid,count(Cid) from 成绩表 group by Cid
  29. 10、查询不及格的课程，并按课程号从大到小排列
  30. select * from 成绩表 group by Cid having Score < 60 order by Cid desc
  32. 11、查询出只选修了一门课程的全部学生的学号和姓名
  33. select Sid,Sname from 学生表 where Sid in(select Sid from 成绩表 group by Sid having count(Sid) = 1)
  35. 12、查询课程编号为"01"且课程成绩在 60 分以上的学生的学号和姓名
  36. select Sid,Sname from 学生表 where Sid in(select Sid from 成绩表 where Cid = 'c01’and Score>=60)
  38. 13、查询“01”课程比“02”课程成绩高的所有学生的学号
  39. select sc1.sid from 成绩表 sc1, 成绩表 sc2 where sc1.sid = sc2.sid and sc1.cid = ‘c01’ and sc2.cid = ‘c02’ and sc1.Score > sc2.Score
  41. 14、查询平均成绩大于 60 分的同学的学号和平均成绩
  42. select Sid, avg(Score) from 成绩表 group by Sid having avg(Score) > 60
  44. 15、查询姓“李”的老师的个数
  45. select count(*) from 老师表 where Tname like ‘李%’
  47. 16、查询没学过“何倩文”老师课的同学的学号、姓名
  48. select Sid, Sname from 学生表 where Sid not in(select Sid from 成绩表 where Cid =(select Cid from 课程表 where Cteacher = ‘何倩文’))
  50. 17、查询学过编号“01”并且也学过编号“02”课程的同学的学号、姓名
  51. select Sid,Sname from 学生 where Sid in(select sc1.Sid from 成绩 sc1,成绩 sc2 wheresc1.Sid=sc2.Sidand sc1.Cid = 'c01’and sc2.Cid = ‘c02’)
  53. 18、查询没学过"李征辉"老师讲授的任一门课程的学生姓名
  54. select Sid,Sname from 学生 where Sid not in(select distinct(Sid) from 成绩 where Cid in (select Cid from 课程 where Cteacher = ‘李争辉’)）
  56. 19、查询所有同学的学号、姓名、选课数、总成绩
  57. select sc.Sid, s.Sname, count(sc.Cid), sum(Score) from 成绩 sc left join 学生 s on sc.Sid = s.Sid group by sc.Sid
  59. 20、查询选修“李征辉”老师所授课程的学生中，成绩最高的学生姓名及其成绩
  60. select s.Sname, max(sc.Score) from 学生表 s left join 成绩表 sc on s.Sid=sc.Sid where sc.Cid in (select Cid from 课程表 where Cteacher = ‘李征辉’)
  62. 21、查询不同老师所教不同课程平均分从高到低显示
  63. select c.Cteacher,c.Cname, avg(sc.Cscore) from 成绩表 sc left join 课程表 c on sc.Cid=c.Cid group by sc.Cid
  65. 22、检索"01"课程分数小于 60，按分数降序排列的学生信息
  66. select * from 学生表 s left join 成绩表 sc on s.Sid=sc.Sid where sc.Score < 60 and sc.Sid = 'c01’order by sc.Score desc
  68. 23、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
  69. select s.Sid, s.Sname, avg(sc.Score) from 学生 s left join 成绩 sc on s.Sid = sc.Sid where s.Sid in(select Sid from 成绩 sc where Score < 60 group by Sid having count(Sid) > 2)

常用函数

数字函数

  1. #取绝对值
  2. select abs(-10);
  4. #向上取整
  5. select ceil(1.1);
  6. select ceil(1.8);
  8. #向下取整
  9. select floor(1.9);
  11. #求最大值
  12. select greatest(10,2,54,13454,67,6,4);
  14. #求最小值
  15. select least(1,4,32,23,4,3,4,54,3,6334);
  17. #取模
  18. select mod(100,33);
  20. #计算次方
  21. select pow(2,6);
  23. #计算π
  24. select pi();
  26. #0-100随机数
  27. select round(rand() * 10);
  29. #四舍五入
  30. select round(5.55);
  32. #四舍五入并保留小数
  33. select round(1.2353654,3);
  35. #保留小数但不四舍五入
  36. select truncate(1.123213,5);
  38. # 生成随机验证码
  39. select lpad(round(rand()*100000,0),5,0)

字符函数

  1. # 拼接函数
  2. concat()
  3. # 小写变大写
  4. lower()
  5. # 大写变小写
  6. upper()
  7. # 左填充
  8. lpad()
  9. # 右填充
  10. rpad()
  11. # 去除空格
  12. trim()
  13. # 截取
  14. substring()

日期函数

  1. # 当前日期
  2. curdate()
  3. # 当前时间
  4. curtime()
  5. # 当前日期时间
  6. now()
  7. # 当前日期的年
  8. year(curdate())
  9. # 当前日期的月
  10. month(curdate())
  11. # 当前日期的日
  12. day(curdate())
  13. # 日期时间到指定计算计算
  14. datediff(curdate(),"2002-10-3");
  15. # 时间添加
  16. date_add(now(),interval 70 year)
  17. # 计算入职时间并排列
  18. select name,datediff(now(),entrydate) a from emp order by a desc;

流程函数

  1. # 根据对错输出
  2. select if(true, 'ok', 'error');
  3. select ifnull('Ok','dede');
  4. # 根据分数评定成绩
  5. select 
  6.  id,
  7.  name,
  8.  (case when math>=85 then '优秀' when math>=60 then '集合' else '不及格' end) 数学,
  9.  (case when english>=85 then '优秀' when english>=60 then '集合' else '不及格' end) 英语,
  10.  (case when chinese>=85 then '优秀' when chinese>=60 then '集合' else '不及格' end) 语文
  11. from score;

主键约束

主键

  1. # 创建主键
  2. create table emp(
  3.     eid int primary key,
  4.     name varchar(20)
  5. );
  6. # 添加多列主键
  7. create table emp(
  8.     eid int,
  9.     name varchar(20),
  10.     age int,
  11.     primary key(eid,name)
  12. );
  13. # 后期添加主键
  14. alter table emp add primary key (sid);
  15. # 删除主键
  16. alter table emp drop primary key (sid);

自增长约束

  1. create table test(
  2.     id int primary key auto_increment,
  3.     name varchar(20)
  4. );
  5. ●  delete数据之后自动增长从断点开始 
  6. ●  truncate数据之后自动增长从默认起始值开始

非空约束

  1. # 创建时设置
  2. create table test(
  3.     id int primary key auto_increment,
  4.     name varchar(20),
  5.     age int not null
  6. );
  7. # 后期修改
  8. alter table test modify age not null;
  9. # 后期删除
  10. alter table test modify age;

唯一约束

  1. # 创建时设置
  2. create table test(
  3.     id int primary key auto_increment,
  4.     name varchar(20),
  5.     age int not null,
  6.     idcard int unique
  7. );
  8. # 后期添加
  9. alter table test add constraint idcard unique(id);
  10. # 删除约束
  11. alter table test drop index idcard;

默认约束

  1. # 创建默认约束
  2. create table users5(
  3.     id int,
  4.     name varchar(20),
  5.     address varchar(20) default '北京'
  6. );
  7. # 添加默认约束
  8. alter table user5 modify address varchar(20) deault '北京';
  9. # 删除默认约束
  10. alter table user5 modify column address varchar(20) default null;

检查约束

  1. # 创建检查约束
  2. create table users5(
  3.     id int,
  4.     name varchar(20),
  5.     address varchar(20) default '北京'，
  6.     age int check (age > 0 && age < 120)
  7. );

外键约束

  1. #创建主表:
  2. create database mydb3;
  3. use mydb3;
  4. create table if not exists dept(
  5.     depton varchar(20) primary key,    #部门号
  6.     name varchar(20)    #部门名字
  7. );
  8. #创建从表
  9. create table if not EXISTS emp(
  10.     eid varchar(20) PRIMARY KEY,    #员工编号
  11.     ename varchar(20),        #员工名字
  12.     age int,        #员工年龄
  13.     dept_id varchar(20),        #员工所属部门
  14.     CONSTRAINT emp_fk foreign key(dept_id) REFERENCES dept (depton)
  15.     #创建外键约束
  16. );
  17. CONSTRAINT emp_fk：    给外键取名
  19. foreign key(dept_id)：    设置外键列
  21. references dept(depton)：    设置依赖列
  22. # 添加外键
  23. alter table test constraint wj_test_dept foreige key dept_id references dept(depton);
  24. # 删除外键
  25. alter table test drop foreign key wj_test_dept;

外键约束的 删除/更新 行为

  1. restrict    默认
  2. cascade        当删除/更新时，其他外键子表也会更新
  3. set null    当删除时，子表则会变为null，但子表要设置允许取null
  5. alter table emp add constraint wj_test_dept foreige key dept_id references dept(depton) on update cascade on delete cascade;
  6. alter table emp add constraint wj_test_dept foreige key dept_id references dept(depton) on update set null on delete set null;