菜园小青虫

  博客园 :: 首页 :: 博问 :: 闪存 :: 新随笔 :: 联系 :: 订阅 订阅 :: 管理 ::

1、关系模型:把世界看作是由实体(Entiy)和联系(Relationship)组成的。所谓实体就是指在实现世界中客观存在并可相互区别的事物。

关系型数据库:是一种以表做为实体,以主键和外键做为表间联系的数据库结构。
主键:用一个唯一的标识符来标识每一行。(特点:不可以重复,不能为空)
外键:用来表达表和表之间的关联关系。

2、关系数据库中的三种关系:
一对一:一条主表记录对应一条从表记录,一条从表记录对应一条主表记录。
一对多:一条主表记录对应多条从表记录,一条从表记录对应一条主表记录。
多对多:一条主表记录对应多条从表记录,一条从表记录对应多条主表记录。(特征:必然有一个中间表)

3、SQL:是一种用于管理关系型数据库,并与数据库中的数据库进行通讯的计算机语言。
数据定义语言(DDL):创建、修改、删除数据库的内部数据结构。
数据查询语言(DQL):用于数据库中数据的查询。
数据操作语言(DML):用于数据库中数据的修改,包括添加、删除、修改等。
数据控制语言(DCL):控制数据库访问权限。

4、DELETE和TRUNCATE的区别:DELETE记录日志,意味着删除后的数据还可以恢复,但是效率低。TRUNCATE不会记录日志,删除后的数据不能恢复,但是效率高。

5、模糊查询:‘_’通配符表示任何单个字符。
‘%’通配符表示包含零个和多个任意字符。

聚合函数的分类:COUNT:统计行数量。
SUM:获取单个列的合计值。
AVG:计算某个列的平均值。
MAX:计算列的最大值。
MIN:计算列的最小值。
注:分组以后只能出现分组字段和聚合函数。

6、HAVING和WHERE的区别:二者都是过滤条件,WHERE运行在分组之前,因此不能执行任何聚合函数。HAVING运行在分组后,只能用做聚合函数的过滤。

7、SQL执行顺序:1、执行FROM
          2、WHERE 条件过滤
          3、GROUP BY 分组
          4、SELECT 投影列
          5、HAVING 条件过滤
          6、ORDER BY 排序

8、多列分组:分组可以实现多列分组,比如按班级和年龄分组,那么只有当班级和年龄一样的才分组。

9、如果子查询中返回结果为单行单列,那么外查询中,才可以使用=、<=、>= 比较运算符。
如果子查询中返回结果为多行单列,那么外查询中只能用in、not in。

10、数据库的完整性:为了防止垃圾数据的产生,从而影响数据库的执行效率。
可靠性+准确性=数据库的完整性

11、数据库完整性分类:①实体完整性(保证每一行数据是有效的)
②域完整性(保证每一列数据是有效的)
③引用完整性(保证引用的编号是有效的)
④用户自定义完整性(保证自定义规则)

12、实体完整性:表中的每一行数据如果与它所代表的实体完全一致。(保证每行所代表的实体能互相区别,不能存在两条一模一样的记录)
实现方法:①主键约束(Primary Key)主键列不能为空,也不能重复;
一个表只能有一个主键。
②唯一约束(Unique)唯一约束是指给定列的所有值必须唯一
,该列在表中每一行的值必须唯一。它和主键约束的区别在于
该列可以为空,并且可以在一张表中给多个列设置唯一约束。

13、域完整性:是指定列的输入有效性。
实现方法:①非空约束:Not Null(该列不允许插入null)
②默认约束:Default
③检查约束:Check (MySQL不支持)(设置只允许插入年龄大于18岁的数据:如s age INT check(s age>18))

引用完整性:从表外键出现的数据,必须在主表的主键列出现。

14、内联接和外联接的区别:
内联接只能查询两个表之间,有对应数据的记录;而外联接可以查询一个表中所有的记录,无论该记录是否有对应的数据。

15、删除主表记录:当主表记录有从表记录引用时,如果删除主表记录就意味着从表记录的外键无法引用主表的主键。这时如果有外键约束存在,那么会报违反外键约束异常,从而无法删除主表记录。

删除主表记录有两种方式:①外键置空:先将该主表记录对应的从表记录
,外键设置为null,再删除主表记录。
②级联删除:先删除该主表记录对应的从表记
录,再删除主表记录。

16、外键:指从表的某列与主表的某列存在依附关系。

外键约束:指在外键关联主键上强制加上一个约束,如果违反约束,则不允许该条数据的修改。

注:没有建立外键约束不等于没有外键。

 

-- 创建数据库
CREATE DATABASE 数据库名;

-- 删除数据库
DROP DATABASE 数据库名;

-- 创建表
CREATE TABLE 表名(
-- PRIMARY KEY 表示id列为主键列不能为空,也不能重复
-- AUTO_INCREMENT将该列设置为自动增长列,由DBMS分配该列的值
id INT PRIMARY KEY AUTO_INCREMENT,
-- VARCHAR(20)该列最多只能存放20个字符
-- 姓名
studentName VARCHAR(20),
-- 成绩
grade INT,
-- 生日
birthday DATE,
-- 电话
tel VARCHAR(20),
-- 班级
className VARCHAR(20)
);

-- 删除表
DROP TABLE 表名;

-- 添加表记录
INSERT INTO 表名(studentName,grade,birthday,tel,className)
VALUES('张三',90,"1990-01-02",'13049586689','J156');

 


-- 查询所有记录
SELECT * FROM 表名;

-- 删除记录
DELETE FROM t_student WHERE id=2;
-- 删除id大于5并且小于10的记录
DELETE FROM t_student WHERE id>5 AND id<10;

-- 修改张三的成绩为100,电话为15200930940
UPDATE t_student SET grade=100,tel='15200930940' WHERE studentName='张三';

-- J156学生的成绩加二分
UPDATE t_student SET grade=grade+2 WHERE className='J156';

-- 查询姓名中带张的学生
SELECT * FROM t_student WHERE studentName LIKE "%张%";

-- 查询90后的学生
SELECT * FROM t_student WHERE birthday>+'1990-1-01' AND birthday<'2000-01-01';


CREATE TABLE t_student(
-- id为列名,int为该列的类型
-- primary key 定义该列为主键列(不能为空,不能重复)
-- AUTO_INCREMENT 设置该列为自动增长列,该列的数值由DBMS分配
id INT PRIMARY KEY AUTO_INCREMENT, -- 编号
studentName VARCHAR(20), -- 姓名
sex ENUM('男','女'), -- 枚举类型,表示该列的值只能取'男'或'女'
brithday DATE, -- 生日
grade INT, -- 成绩
tel VARCHAR(20), -- 电话
className VARCHAR(20) -- 班级名称
);

-- 添加列
ALTER TABLE t_student ADD address VARCHAR(20);

-- 删除列
ALTER TABLE t_student DROP COLUMN address;

-- 修改列
ALTER TABLE t_student CHANGE className cname VARCHAR(30);

-- DEFAULT 如果该列没有填值,那么以'男'插入
-- 如果该列填充了值,就以填充值作为插入内容
ALTER TABLE t_student CHANGE sex sex ENUM('男','女') DEFAULT '男';

-- 批量添加
INSERT INTO t_student(studentName,sex,brithday,grade,tel,className)
VALUES('李二狗','男','1987-12-23',80,'12233333333','J157'),
('王凤姐','女','1987-09-25',56,'19999999999','J156'),
('张毛豆','男','1987-10-05',98,'18656565656','J157');

-- 清除所有记录
TRUNCATE t_student;

-- 查询所有学生的姓名和性别(效率高)
SELECT studentName,sex FROM t_student;

-- *表示所有的列(效率低)
SELECT * FROM t_student;

-- 查询有几个班
-- DISTINCT表示去除重复记录
SELECT DISTINCT className FROM t_student;

-- 返回限定行(查询前三条信息,其中0为起始记录数(从0开始),3表示显示几条记录)
SELECT * FROM t_student LIMIT 0,3;

-- 查询所有J156的学生
SELECT * FROM t_student WHERE className='J156';

-- 查询90后所有的女生
SELECT * FROM t_student WHERE brithday>='1990-01-01' AND brithday<'2000-01-01' AND sex='女';

-- BETWEEN一个范围搜索,大于等于下限,并且小于等于上限
SELECT *FROM t_student WHERE brithday BETWEEN '1990-01-01' AND '2000-01-01';

-- 查询李四和张天师两个学生的信息
SELECT * FROM t_student WHERE studentName='李四' OR studentName='张天师';
SELECT * FROM t_student WHERE studentName IN('李四','张天师');

-- 查询所有姓李的人
SELECT * FROM t_student WHERE studentName LIKE '李%';

-- 查询姓李,名字只有两个字的学生
SELECT * FROM t_student WHERE studentName LIKE '李_';

-- 查询没有电话的学生
SELECT * FROM t_student WHERE tel IS NULL;

-- 按成绩排序
SELECT * FROM t_student ORDER BY grade ASC; -- 默认为升序 ASC可以不加
SELECT * FROM t_student ORDER BY grade DESC; -- 降序

-- 按班级降序排序,如果班级一样,再按成绩排序
SELECT * FROM t_student ORDER BY className DESC ,grade DESC;

-- ORDER BY RAND()乱序(随机排序)
SELECT * FROM t_student ORDER BY RAND();

-- 查询所有的学生,并显示该学生是否合格,if语句(条件,满足输出,不满足输出) 三目运算符
SELECT s.*,IF(grade>=60,'合格','不合格') info FROM t_student s;

-- 查询所有的学生,并给学生成绩一个评价:80以上为优秀,60-80为中等,60以下为差,null为缺考
SELECT s.*,(CASE WHEN grade>=80 THEN '优秀'
WHEN grade>=60 AND grade<80 THEN '中等'
WHEN grade IS NULL THEN '缺考'
ELSE '差' END) info
FROM t_student s;

-- J156学生成绩加三分,J157学生加一分
UPDATE t_student SET grade=grade+(IF(className='J156',3,1));
UPDATE t_student SET grade=grade+(CASE WHEN className='J156' THEN 3
WHEN className='J157' THEN 1 END);

-- 查询成绩最高学生的信息
SELECT * FROM t_student ORDER BY grade DESC LIMIT 0,1;

-- 查询学生人数,COUNT(列名)只统计非空列,COUNT(*)统计所有数据
SELECT COUNT(*) '学生人数',COUNT(grade) '参考人数' FROM t_student;

-- 统计总分和平均分,AVG只统计非空列
SELECT SUM(grade) '总分',AVG(grade) '平均分' ,SUM(grade)/COUNT(*) FROM t_student;

-- 统计最高分和最低分
SELECT MAX(grade) '最高分',MIN(grade) '最低分' FROM t_student;

-- 统计每个班的学生人数
SELECT className,COUNT(*) '人数' FROM t_student GROUP BY className;

-- 统计每个班的总分、最高分、最低分
SELECT className,SUM(grade) '总分', MAX(grade) '最高分',MIN(grade) '最低分' FROM t_student GROUP BY className;

-- 查询总分大于320的班级
SELECT className,SUM(grade) '总分' FROM t_student GROUP BY className HAVING SUM(grade)>320;

-- 查询学生人数在5个以上的班级
SELECT className,COUNT(*) '总人数' FROM t_student GROUP BY className HAVING COUNT(*)>=5;

-- 统计所有学生中80分以上的学生有多少人,60-80分的学生有多少人,60以下多少人,缺考多少人
SELECT COUNT(IF(grade<60,1,NULL)) '60以下',
COUNT(IF(grade>=60 AND grade<80,1,NULL)) '60到80',
COUNT(IF(grade>=80,1,NULL)) '80以上' ,
COUNT(IF(grade IS NULL,1,NULL)) '缺考'
FROM t_student;


-- 查询比李四更好的学生
-- 子查询:在一个查询语句中,再嵌入一个查询
SELECT * FROM t_student WHERE grade>
(SELECT grade FROM t_student WHERE studentName='李四');

-- 查询J157学生中,哪些学生成绩高于J156最高成绩
SELECT * FROM t_student WHERE className='J157' AND grade>
(SELECT MAX(grade) FROM t_student WHERE className='J156');

-- 查询每个班成绩最好的学生信息
SELECT * FROM t_student s WHERE grade IN
(SELECT MAX(grade) FROM t_student s1 WHERE s.className=s1.className GROUP BY className);

-- 查询男生成绩最好的三个学生,以及女生成绩最好的三个学生信息
-- UNION把查询结果连接
(SELECT * FROM t_student s1 WHERE sex='男' ORDER BY grade DESC LIMIT 0,3)
UNION
(SELECT * FROM t_student s2 WHERE sex='女' ORDER BY grade DESC LIMIT 0,3);

-- 添加外键约束。强行要求从表外键必须引用主表的主键。
-- 否则从表记录插入会失败
ALTER TABLE t_bike ADD CONSTRAINT fk_1 FOREIGN KEY(userId)
REFERENCES t_user(id);

-- 删除外键约束
ALTER TABLE t_bike DROP FOREIGN KEY fk_1;

-- 查询所有自行车,以及该自行车的主人姓名(内连接)
SELECT b.*,u.userName FROM t_bike b JOIN t_user u
ON b.userId=u.id;

-- 左外连接,LEFT包含左边表的所有数据
SELECT b.*,IF(u.userName IS NULL,'没有主人',u.userName) FROM t_bike b LEFT JOIN t_user u
ON b.userId=u.id;

-- 查询所有用户,并显示该用户拥有自行车的数量,以及价值
SELECT u.*,COUNT(b.bikeType),SUM(b.price) FROM t_bike b RIGHT JOIN t_user u
ON b.userId=u.id GROUP BY u.id;

-- 外键置空
UPDATE t_bike SET userId=NULL WHERE userId=5;
DELETE FROM t_user WHERE id=5;

-- 级联删除
DELETE FROM t_bike WHERE userId=6;
DELETE FROM t_user WHERE id=6;

posted on 2018-04-29 21:44  菜园小青虫  阅读(152)  评论(0编辑  收藏  举报