SQL基本部分:
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;
修改表结构:
修改列类型(长度) : alter table 表名 modify ...
添加一个新列 : alter table 表名 add ...
修改列名称 : alter table 表名 change ...
删除列 : alter table 表名 drop 列名
修改表名 : 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<100 ; 向数据表存入age值,必须小于100
* 完整性约束有5类
----------------------------------------------------------
多表设计
数据表与数据表之间关系三种:实体之间关系 多对多、一对多、一对一
多对多案例:项目和程序员
一个项目可以由多个程序员参与
一个程序员可以参与多个项目开发
建表原则:多对多关系,必须引入第三张数据表,同时引入另两张实体表主键作为外键
一对多案例:老师与课程
一个老师可以教授多门课程
一门课程只能有一个老师教授
建表原则:一对多关系,在多的一方添加一方 主键作为外键
一对一关系:班级与班长关系
一个班只能有一个班长
一个班长只能负责一个班
* 该关系比较少见
建表原则:一对一关系,可以在任何一方添加 另一方主键作为外键
建表练习:
设计学生成绩管理系统数据表
1、每个教师可以教多门课程
2、每门课程可以由多个学生选修
3、每个学生可以选修多门课程
4、学生选修课程要有成绩
关系表表名,通常用两个实体表表名组合而成!
-------------------------------------------------------------------------------
笛卡尔积
当两个数据表进行关联查询时,用第一张数据表每一条记录去匹配第二张数据表每一条记录。
第一张表10条数据
第二张表20条数据
使用笛卡尔积 结果 10*20 = 200 条记录
在实际开发中,获得笛卡尔积中有意义的记录 ? ---- 连接查询
内连接
外连接
内连接 : 将两张表相同意义字段连接起来
select * from A,B where A.A_ID = B.A_ID; 条件 A表中A_ID与 B表中 A_ID 相等匹配
* 返回结果一定是两个表都存在信息 , 最有意义的信息,如果第一张表记录在第二张表找不到匹配信息,不显示,第二张表记录在第一张表无匹配信息,不显示
第一张表10条数据
第二张表20条数据
内连接 结果 <= 10条
语法:select * from a inner join b on A.A_ID = B.A_ID;
简化:select * from a,b where A.A_ID = B.A_ID;
外连接:左外连接、右外连接、全外连接
左外连接 :用第一张表每条记录去匹配第二张表对应记录,无论是否找到匹配信息,都显示第一张表匹配结果
例如:每个水果价格 ? 没有价格水果也要显示
select * from a left outer join b on A.A_ID = B.A_ID ;
第一张表10条数据
第二张表20条数据
左外连接 --- 10条
右外连接:从第二张表找第一张表匹配记录,无论是否找到,第二张表所有记录都显示
select * from a right outer join b on A.A_ID = B.A_ID ;
第一张表10条数据
第二张表20条数据
右外连接 --- 20条
全外连接:左外连接与右外连接 结果和 ---- 排除重复数据
select * from a full outer join b on A.A_ID = B.A_ID ; ----- MySQL 不支持
使用union关键字实现全外连接效果
select * from A left outer join B on A.A_ID = B.A_ID
union
select * from A right outer join B on A.A_ID = B.A_ID;
------------------------------------------------------------------------------------
关联子查询:将第一个查询结果 ,作为第二个查询条件
查询student表中年龄最大学员的信息
select * from student where age = (select max(age) from student);
等价于
select max(age) from student; ----- 25
select * from student where age = 25; ----- 学生信息
IN/EXISTS 当前查询记录在子查询结果中存在
查询所有成绩小于60分的同学名称
查询studentcource表成绩小于60 所有记录
select student_id from studentcource where score < 60; --- 小于60分学生学号 2,8
再根据id去查询学生表,得知学生姓名
select * from student where id in(2,8);
select * from student where id in(select student_id from studentcource where score < 60);
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='语文';
+----+------+------------+------------+-----------+-------+
| id | name | teacher_id | student_id | cource_id | score |
+----+------+------------+------------+-----------+-------+
| 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='数学';
+----+------+------------+------------+-----------+-------+
| id | name | teacher_id | student_id | cource_id | score |
+----+------+------------+------------+-----------+-------+
| 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 id,name from student where id not in (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 id from cource where name='语文' or name='数学';
* 学过语文的学生 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”的同学所学相同的同学的学号和姓名
查询和小李同学学习的课程完全相同的其他同学学号和姓名
查询各科成绩最高和最低的分
查询学生信息和平均成绩
查询上海和北京学生数量
查询不及格的学生信息和课程信息
查询每门功成绩最好的前两名
统计每门课程的学生选修人数(超过两人的进行统计)
把成绩表中“关羽”老师教的课的成绩都更改为此课程的平均成绩
相关推荐
│ day1-不同存储引擎建表语句.sql │ day1_MySQL架构与SQL执行流程-笔记.pdf │ day1_MySQL架构与SQL执行流程-课件.pdf │ day1_MySQL架构与SQL执行流程.mp4 │ day2-相关SQL.sql │ day2_MySQL索引深入剖析-笔记....
SQL> select next_day('18-5月-2001','星期五') next_day from dual; NEXT_DAY ---------- 25-5月 -01 41.SYSDATE 用来得到系统的当前日期 SQL> select to_char(sysdate,'dd-mm-yyyy day') from...
UPDATE ot_tgbz set update_day=if(update_day is null,date,update_day); 2、更新整张表,如果某个字段大于2,那么把他们都更新成2; UPDATE ot_user set xingyun_num=if(xingyun_num > 2,2,xingyun_num); 3、...
sql语句含义:结构化查询语言。客户端跟服务端通信的“特殊”语言 关系型数据库核心元素:数据库、数据表、记录、字段 客户端链接服务端 数据完整性 客户端Navicat使用 MySQL数据类型:数值型、decimal浮点...
9、关于SQL语句的分类? 10、导入一下提前准备好的数据 11、关于导入的这几张表? 12、不看表中的数据,只看表的结构,有一个命令:desc 表名; 13、简单查询:select a, b as c from d; 14、条件查询: select 字段1,...
FROM `ler_items` WHERE DATE_FORMAT(postTime,’%Y-%m’)=’2013-03′注意:日期一定要用”,否则没有效果其它的一些关于mysql日期查找语句mysql> select date_format(DATE_SUB(CURDATE(), INTERVAL 7 DAY),’%y%m%...
移动开发小白树懒,在努力的学习mysql基本语法.通用语法.狂练sql的select语句为将来打下扎实的基础.
文章目录数据库和SQL概述数据库的好处数据库的概念SQL原因概述数据库的特点安装与使用MySQL产品的特点MySql数据库的安装启动和停止MySQL服务MySQL服务端的登录和退出MySql数据库的使用MySQL的常见命令MySQL语法规范...
Day03回顾 1、SQL查询 1、group by 1、select后字段名没有在group by之后出现,则必须对该字段进行聚合处理(聚合函数) ## 记 :先分组,再聚合 2、having 1、过滤由group by语句返回的记录集 2、where只能操作表...
通过单独或调用存储过程使用,在某一特定的时间点,触发相关的SQL语句或存储过程。 首先删除2天以前记录的SQL语句(webserver_monitormemory为表名,time为时间字段): delete From webserver_monitormemory where ...
字符串类型的字段值必须用单引号括起来, 例如: ‘GOOD DAY’如果字段值里包含单引号’ 需要进行字符串转换, 我们把它替换成两个单引号”. 字符串类型的字段值超过定义的长度会出错, 最好在插入前进行长度校验. 日期...
多数DBMS不需要在单条SQL语句后加分号,但特定的DBMS可能必须在单条SQL语句后加分号。 SQL语句的最后一句要以 “;”号结束 二、写子句顺序 Select column,group_function From table [Where condition] ...
/*站站查询:从枣庄站到北京站的所有列车(两种不同方式的SQL语句)*/ Select T1.* From Train T1, Train T2, Train T3 Where T2.Station='枣庄' and T3.Station='北京' and T2.S_No Select * From Train Where ...
导语 今天在写 SQL 的时候,遇到一个问题...首先先说明一个概念,MySQL 语句执行的顺序,并不是按照 SQL 语句的顺序。下面是示例 SQL SELECT DISTINCT < select_list > FROM < left> JOIN < right_table
mysql 获取昨天日期、今天日期、明天日期以及前一个小时和后一个小时的时间php、mysql查询当天,查询本周,查询本月的数据实例(字段是时间戳)mysql中获取一天、一周、一月时间数据的各种sql语句写法mysql 数据库取前后...
├─(41) 02 python s3 day46 sql规范.avi ├─(42) 03 python s3 day46 数据库操作DDL.avi ├─(43) 04 python s3 day46 mysql的数据类型.avi ├─(44) 05 python s3 day46 数据表操作.avi ├─(45) 06 python s3 ...
day11: where条件之比较运算、逻辑运算 模糊查询(like) % 表示任意多个字符 _ 表示一个任意字符 范围查询(between and 、in) ... 限制记录limit:限制取出记录的数量,要写在SQL语句的最后 标准的SQL书写格式
10.6 外键约束 create table employee ( id int primary key auto_increment, name varchar(50) not null, deptName varchar(50) ); -- 这里会导致数据冗余问题。并且存在资源的浪费 insert into employee(name, ...
今天 select * from 表名 where to_days(时间字段名) = to_days(now()); 昨天(包括昨天和今天的数据) ...SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名) 近30天 SE
(首先将数据库中表清空,随后输入以下操作,要严格按照语句输入,不然会报错) 方式一、在创建表的时候,增加约束(比较麻烦,也比较复杂) -- 定义年级表: CREATE TABLE `grade`( `gradeid` INT(10) NOT NULL ...