mysq基础笔记(sql语句)

sdxh0506

　　1、数据库操作相关SQL ---- database
　　创建数据库 create database 数据库名称; ------ 在sql后通过 character set 指定数据库本身字符集，如果没有指定将服务器默认
　　* 服务器默认字符集 mysql安装目录/my.ini [mysqld] default-character-set
　　查看当前有哪些数据库 show databases;
　　修改数据库（修改数据库字符集） 数据库字符集存放mysql安装目录/data/数据库文件夹/db.opt
　　alter database 数据库名称 character set 字符集;
　　* collate 校对方式 ----- 用于数据库排序； 每一种字符集都存在一种默认校对方式（可以不用修改）
　　删除数据库 drop database 数据库名称;
　　切换数据库（设置当前使用数据库） use 数据库名称;
　　* select database(); 查看当前使用数据库
　　2、数据表操作相关SQL ---- table表结构
　　创建数据表 create table 表名(列名 类型(长度) 约束,列名 类型(长度) 约束... ) ----- 在创建表之前必须指定数据库
　　* 查看当前有哪些数据表 show tables;
　　查看数据表表结构 desc table;
　　修改表结构：

　　修改列类型（长度） :>
　　添加一个新列 :>
　　修改列名称 :>
　　删除列 :>　　修改表名 : rename table 旧表名 to 新表名
　　* table 在创建时 character set 指定表字符集，如果没有指定采用数据库默认字符集
　　删除表 drop table 表名;
　　3、数据记录增删改查  insert update delete select
　　数据记录插入 insert into 表名(列,...)  values(值,...);
　　* 值的顺序和列顺序一致，个数一致 ， 在开发中经常省略列名，值按照表结构所有字段进行设值
　　数据查看 select * from 表名;
　　数据记录修改 update 表名 set 列名=值,列名= 值 where 条件语句
　　数据记录删除 delete from 表名 where 语句
　　* truncate 与 delete区别 ？ truncate删除表，重新创建, delete from 逐行删除----- 性能truncate好于 delete，delete被事务控制，删除后回滚取消删除，truncate不可恢复
　　select 语句
　　S - F - W - G - H - O
　　select ... from ... where ... group by ... having ... order by ... ; 顺序固定的
　　1、from 指定查询数据表
　　2、where 前置过滤条件 --- 将表数据过滤掉一部分
　　3、group by 对where 过滤后数据进行分组
　　4、having 对分组后结果添加条件过滤
　　5、select 指定检索哪些字段
　　6、order by 对检索结果排序
　　4、数据库备份和恢复
　　备份命令: mysqldump -u 用户名 -p 数据库名 > sql脚本位置 (回车输入密码)
　　恢复命令: mysql -u 用户名 -p 数据库名 < sql脚本位置 (回车输入密码 )
　　* 在mysql连接后，通过source 进行数据库恢复 source sql脚本位置
　　5、数据库完整性约束 ----- 保证数据表中记录完整性
　　主键约束 primary key : 用来指定数据表数据记录的唯一标识
　　唯一约束 unique : 该字段取值唯一
　　非空约束 not null ; 该字段值不能为null
　　外键约束 foreign key : 当两个数据表存在关联时，添加外键约束，外键约束引用另一张表主键
　　条件约束 check : mysql不支持 Oracle支持 check age　　exists实现上面in 语句效果
　　select name from student where exists (select * from studentcource where score < 60 and student.id = studentcource.student_id);
　　select * from studentcource,student where score < 60 and student.id = studentcource.student_id;
　　select name from student where exists (select * from studentcource where score < 60 and student.id = studentcource.student_id);
　　* 在实际开发中 exists比 in效率要高
　　ANY、SOME、ALL 用法
　　SOME和ANY作用相同的  ----- 一些 >any(1,2,3) 大于任何一个都可以 等价于 >min
　　ALL ---- 所有  >all(1,2,3) 必须同时大于三个值   等价于 >max
　　查询获得最高分的学生学号
　　select max(score) from studentcource; 最高学分
　　select student_id from studentcource where score = (select max(score) from studentcource);
　　* 自我比较
　　select student_id from studentcource where score >=all(select score from studentcource);
　　查询编号2课程比编号1课程成绩高所有学号
　　select score from studentcource where cource_id = 2 and score > any(select score from studentcource where cource_id = 1);
　　select score from studentcource where cource_id = 2; 课程2所有成绩
　　select score from studentcource where cource_id = 1; 课程1所有成绩
　　使用union将两个查询结果合并，union 排重重复数据 union all 不会排重重复数据
　　* 合并时列名必须一致
　　------------------------------------------------------------------------------------------------------
　　查询语文课程比数学课程成绩高的所有学生的学号
　　mysql> select * from cource,studentcource where cource.id = studentcource.cource
　　_id and cource.name='语文';
　　+----+------+------------+------------+-----------+-------+

　　|>　　+----+------+------------+------------+-----------+-------+
　　|  1 | 语文 |          1 |          1 |         1 |    80 |
　　|  1 | 语文 |          1 |          3 |         1 |    71 |
　　|  1 | 语文 |          1 |          5 |         1 |    60 |
　　|  1 | 语文 |          1 |          6 |         1 |    76 |
　　|  1 | 语文 |          1 |         10 |         1 |    77 |
　　+----+------+------------+------------+-----------+-------+
　　5 rows in set (0.02 sec)
　　mysql> select * from cource,studentcource where cource.id = studentcource.cource
　　_id and cource.name='数学';
　　+----+------+------------+------------+-----------+-------+

　　|>　　+----+------+------------+------------+-----------+-------+
　　|  2 | 数学 |          1 |          1 |         2 |    90 |
　　|  2 | 数学 |          1 |          2 |         2 |    53 |
　　|  2 | 数学 |          1 |          3 |         2 |    70 |
　　|  2 | 数学 |          1 |          4 |         2 |    90 |
　　|  2 | 数学 |          1 |          5 |         2 |    70 |
　　|  2 | 数学 |          1 |          6 |         2 |    88 |
　　|  2 | 数学 |          1 |          8 |         2 |    71 |
　　|  2 | 数学 |          1 |          9 |         2 |    88 |
　　|  2 | 数学 |          1 |         10 |         2 |    76 |
　　+----+------+------------+------------+-----------+-------+
　　9 rows in set (0.00 sec)
　　select t1.student_id,t1.score 语文,t2.score 数学 from (select * from cource,studentcource where cource.id = studentcource.cource_id and cource.name='语文') t1,(select * from cource,studentcource where cource.id = studentcource.cource_id and cource.name='数学') t2 where t1.student_id = t2.student_id and t1.score > t2.score;
　　查询平均成绩大于70分的同学的学号和平均成绩
　　* 按人取平均成绩 ------ 分组
　　select student_id,avg(score) from studentcource group by student_id having avg(score)>70;
　　* 打印学生姓名
　　select student.name,t.avgscore from student,(select student_id,avg(score) avgscore from studentcource group by student_id having avg(score)>70) t where student.id = t.student_id;
　　查询所有同学的学号、姓名、选课数、总成绩
　　*学生信息：select * from student;
　　*选课数、总成绩 select student_id,count(*),sum(score) from studentcource group by student_id;
　　select student.id 学号,student.name 姓名,t.courcenum 选课数, t.sumscore 总成绩 from student,(select student_id,count(*) courcenum,sum(score) sumscore from studentcource group by student_id) t where student.id = t.student_id;
　　查询没学过关羽老师课的同学的学号、姓名
　　* 关羽老师教什么课 select cource.id from teacher,cource where teacher.id = cource.teacher_id and teacher.name='关羽';
　　* 选过关羽老师课 select distinct student_id from studentcource where cource_id in (select cource.id from teacher,cource where teacher.id = cource.teacher_id and teacher.name='关羽');

　　select>　　查询学过语文并且也学过数学课程的同学的学号、姓名

　　* 语文和数据 课程编号  select>　　* 学过语文的学生 select * from cource,studentcource where cource.id = studentcource.cource_id and cource.name='语文';
　　* 学过数学的学生 select * from cource,studentcource where cource.id = studentcource.cource_id and cource.name='数学';
　　select t1.student_id  from (select * from cource,studentcource where cource.id = studentcource.cource_id and cource.name='语文') t1, (select * from cource,studentcource where cource.id = studentcource.cource_id and cource.name='数学') t2 where t1.student_id = t2.student_id ;
　　select student.id,student.name from student,(select t1.student_id  from (select * from cource,studentcource where cource.id = studentcource.cource_id and cource.name='语文') t1, (select * from cource,studentcource where cource.id = studentcource.cource_id and cource.name='数学') t2 where t1.student_id = t2.student_id) t where student.id = t.student_id;
　　查询学过赵云老师所教的所有课的同学的学号、姓名
　　查询没有学三门课以上的同学的学号、姓名
　　查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名
　　查询和小李同学学习的课程完全相同的其他同学学号和姓名
　　查询各科成绩最高和最低的分
　　查询学生信息和平均成绩
　　查询上海和北京学生数量
　　查询不及格的学生信息和课程信息
　　查询每门功成绩最好的前两名
　　统计每门课程的学生选修人数（超过两人的进行统计）
　　把成绩表中“关羽”老师教的课的成绩都更改为此课程的平均成绩