数据库、MySql知识点

最新推荐文章于 2024-08-15 01:26:58 发布

准时~

最新推荐文章于 2024-08-15 01:26:58 发布

阅读量244

点赞数

分类专栏：数据库 java 文章标签：数据库 MySql 数据库实列及面试题

本文链接：https://blog.csdn.net/longlong_agoDe/article/details/98740883

版权

java 同时被 2 个专栏收录

8 篇文章 0 订阅

订阅专栏

数据库

1 篇文章 0 订阅

订阅专栏

1.SQL分类

DDL （数据定义语言） 数据定义语言 - Data Definition Language 用来定义数据库的对象，如数据表、视图、索引等
DML （数据操纵语言） 数据处理语言 - Data Manipulation Language 在数据库表中更新，增加和删除记录如 update， insert， delete
DCL （数据控制语言） 数据控制语言 – Data Control Language 指用于设置用户权限和控制事务语句如grant，revoke，if…else，while，begin transaction
DQL （数据查询语言） 数据查询语言 – Data Query Language select

2.练习：

创建一个名称为mydb1的数据库。
create database mysql1;
创建一个使用utf8字符集的mydb2数据库。
create database mysql2 character set utf8;
创建一个使用utf8字符集，并带校对规则的mydb3数据库。
create database mysql3 character set utf8 collate utf8_bin;
—补充：每次创建一个数据库在数据存放目录中生成一个文件夹，每个文件夹中存在 db.opt 存放默认字符集和校对规则。
删除数据库:语法：drop database +数据库名称;
修改数据库编码集:修改mydb2字符集为gbk :alter database mysql2 character set gbk;
切换当前使用数据库:语法：use 数据库名
查看当前使用的数据库:语法：select database();
—所有数据库相关操作语句都属于DDL 语句。

3.数据表的创建

**

> - 创建一个员工表 CREATE TABLE employee(
>     NAME VARCHAR(32),
>     PASSWORD VARCHAR(32),
>     age INT,
>     birthday DATE );

查看表结构 desc 表名;

-- 创建一张有约束的表
CREATE TABLE employee2(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(32) UNIQUE NOT NULL,
    PASSWORD VARCHAR(32) NOT NULL,
    age INT NOT NULL,
    birthday DATE
);

4.数据表结构修改

 在employee2表上增加salary列。
ALTER TABLE employee2 ADD salary INT NOT NULL;
-- 修改birthday列不能为null
ALTER TABLE employee2 MODIFY birthday DATE NOT NULL;
-- 修改列名name为username
ALTER TABLE employee2 CHANGE NAME username VARCHAR(32) UNIQUE NOT NULL;
-- 修改username列的长度为60
ALTER TABLE employee2 MODIFY username VARCHAR(60) UNIQUE NOT NULL;
-- 删除age列
ALTER TABLE employee2 DROP age;
-- 将employee2 的表名给person
RENAME TABLE employee2 TO person;

5.数据表删除

语法：drop table 表名;

6.查看数据表

desc 表名;
查看表结构 show tables ;
查看当前库内所有表名 show create table 表名;
查看建表语句和字符集

—所有数据表结构操作语句都是 DDL

7.表中数据增删改查

给表中插入数据 INSERT INTO student (id , sage , sname , gender , score ,
birthday) VALUES(NULL ,23 , ‘秋香’ , ‘女’ , 99.99 , ‘2017-12-01’);
– 如果给表的每一列都插入数据，可以省略列名 INSERT INTO student VALUES ( NULL ,33 , ‘石榴’ , ‘女’ , 10.01 , ‘1900-12-01’ );
– 如果只给表中指定的某些列插入数据，这时必须写列名 INSERT INTO student(sname , gender , score ) VALUES (‘石榴’ , ‘女’ , 10.01); 修改语法: update 表名 set 列名=值,列名=值…
where条件语句; 如果没有where条件语句，默认修改所有行数据删除语法：delete from 表名 where条件语句 ;

8.面试题

**
使用delete删除表中所有记录和使用truncate table删除表中所有记录有何不同？
truncate 删除数据，过程先将整个表删除，再重新创建。
delete 删除数据，逐行删除记录
truncate 效率要好于 delete
truncate 属于DDL ，delete 属于DML
事务管理只能对DML 有效，被事务管理SQL语句可以回滚到SQL执行前状态
insert update delete 属于DML 语句

9.格式：使用 as 关键字给表、列等起别名。

select 列名 as 别名 from 表名; （在对列起别名时，as可以省略。）
-- 成绩在80 到100 之间
SELECT * FROM student WHERE score >= 80 AND score <= 100;
SELECT * FROM student WHERE score >= 80 && score <= 100;
SELECT * FROM student WHERE score BETWEEN 80 AND 100;
-- 查询年龄为18，23，25的同学信息  列表 可以使用in
SELECT * FROM student WHERE sage IN(18,23,25);
SELECT * FROM student WHERE sage = 18 OR sage = 23 OR sage = 25;
SELECT * FROM student WHERE sage = 18 || sage = 23 || sage = 25;
-- 查询所有姓赵的学生信息  这种查询称为模糊查询  需要使用like关键字
-- like后面的 % 和 _ 都是占位符，% 可以代表任意个字符， _ 一个下划线就表示一个字符
SELECT * FROM student WHERE sname LIKE '赵%';  -- 找出姓赵的
SELECT * FROM student WHERE sname LIKE '赵_';  -- 找出姓赵，姓名只能有2个字
SELECT * FROM student WHERE sname LIKE '%赵%'; -- 找出姓名中有赵字的
SELECT * FROM student WHERE sage > 23 AND score > 80;
在SQL中不能使用=null，或者<>null ，判断是否为null 需要使用 is null， 不是null，需要使用 is  not  null
-- 没有生日信息的学生
SELECT * FROM student WHERE birthday = NULL;
SELECT * FROM student WHERE birthday IS NULL;
-- 查询生日有数据的
SELECT * FROM student WHERE birthday IS NOT NULL;

10.select * from 表名 order by 列名 asc|desc ; asc是升序排列，desc是降序排列

练习：
1、统计一个班级共有多少学生？
– count函数的使用 count(列名)
SELECT COUNT() FROM student;
2、统计成绩大于80的学生有多少个？
– 成绩大于80的学生，学生的个数
SELECT COUNT() FROM student WHERE score > 80;
练习：
1、统计一个班级成绩和
– sum函数，求和
– 统计成绩和值
SELECT SUM(sname) FROM student ;
2、分别统计年龄和成绩的和
SELECT SUM(sage) , SUM(score) FROM student;
3、统计年龄和成绩和值
SELECT SUM(sage + score) FROM student;

SELECT SUM(sage) + SUM(score) FROM student;
– 使用mysql数据库自身的ifnull函数来避免有null的计算
---------SELECT SUM(IFNULL(sage , 0) + IFNULL(score , 0 ) ) FROM student;
---------上述的ifnull(sage,0) 意思就是当某个学生的sage为null时，给其设置值为零。

11.-- 计算平均分

SELECT SUM(score) / COUNT(score) FROM student;
SELECT AVG(score) FROM student;

12.-- 表中的最高分和最低分

SELECT MAX(score) , MIN(score) FROM student;
-- 最大年龄和最小年龄
SELECT MAX(sage) , MIN(sage) FROM student;

13.-- 统计每类商品的个数(数量、商品的单价)

SELECT product , COUNT(product) , price FROM orders GROUP BY product;
-- 最终需要查询出商品的总价大于100
SELECT product , COUNT(product) , price FROM orders GROUP BY product WHERE SUM(price) > 100;

14.面试题目

where 和 having 条件语句的区别？
where 是在分组前进行条件过滤，having 是在分组后进行条件过滤
使用where地方都可以用 having替换，但是having可以使用分组函数，而where后不可以用分组函数
select语句的小节：
S-F-W-G-H-O 组合 select … from … where … group by… having… order by … ; 顺序不能改变
解析顺序： from - where - group by - having – order by

15.在mysql的安装目录的bin目录下有mysqldump命令，可以完成对数据库的备份。

语法：mysqldump -u 用户名 -p 数据库名 > 磁盘SQL文件路径还原数据库使用的是mysql安装目录下的mysql命令
语法：mysql -u 用户名 -p 数据库名 < 磁盘SQL文件路径
还原数据库的时候，是不会创建数据库的，只会还原数据库中的表和数据，因此需要在还原数据库之前，手动的创建数据库。

16.使用关联查询，获取两张表中的数据：

SELECT * FROM goods,price;
可以在查询的后面添加条件：
SELECT * FROM goods , price WHERE goods.id = price.id;

17.内连接查询

在连接查询中添加条件，筛选出符合条件的数据，而其他的数据重复或者无效数据被过滤掉。
内连接查询出来的数据是最有价值的，并且数据肯定是多张表一定存在的。内连接有两种书写方式：
1、Inner join书写相关条件
2、使用where关键字书写条件。

	语法：select * from 表名 inner join 表名 on 条件
SELECT * FROM goods INNER JOIN price ON goods.id = price.id;
	语法：select * from 表名,表名, where 条件
SELECT * FROM goods , price WHERE goods.id = price.id;

18.外连接查询

左外连接：使用左边的表去查询右边的表，不管右边表有没有结果，都会显示左边表的全部记录。语法：select * from 表名 left
Outer join 表名 on 条件。例如：查询所有商品的价格，使用左外连接，不管商品有没有价格，商品信息都会被查询出来。
SELECT * FROM goods LEFT OUTER JOIN price ON goods.id = price.id;
右外连接：使用右边的表去查询左边的表，不管左边的表有没有结果，都会显示右边表的全部记录。语法：select * from 表名
right outer join 表名 on 条件。例如：查询每个价格对应的商品。不管有的价格有没有对应的商品，价格都会查询出来。
SELECT * FROM goods RIGHT OUTER JOIN price ON goods.id = price.id;
全外连接：将左连接、右连接的结果和在一起。并去掉重复数据。语法：select * from 表名 full outer join 表名
on 条件。 SELECT * FROM goods FULL OUTER JOIN price ON goods.id =
price.id; 全连接mysql数据库不支持，可以使用union 关键字将左、右连接合并到一起：语法：select * from
表名 left outer join 表名 on 条件 union select * from 表名 right outer
join 表名 on 条件 union 关键字后面可以跟着all 关键字： union：将多个查询的结果合并，会去除重复的记录。

union all ：将多个查询的结果合并，但不去除重复记录

SELECT * FROM goods LEFT OUTER JOIN price ON goods.id = price.id
UNION ALL
SELECT * FROM goods RIGHT OUTER JOIN price ON  goods.id = price.id;

19.in和exists关键字

in：需要指定一个列表，找出列表中所有符合条件的数据
SELECT * FROM student WHERE id IN( 2, 8 );
SELECT NAME FROM student WHERE id IN( SELECT student_id FROM studentcource WHERE score < 60 );
	exists：主要判断存在，用在查询中的时候，主查询和子查询需要有条件进行关联，否则exists查询出来的数据就会是主查询中的所有数据（子查询有结果，exists 得到的就true，where条件就成立）。
SELECT * FROM student WHERE EXISTS( SELECT student_id FROM studentcource WHERE score < 60 );

20.any、some和all关键字

any和some：它们本质没有区别，只是在sql语法历史版本演变中出现的不同的关键字而已。因此在书写SQL时，两个随便使用。（sql92、sql99）
any和some主要强调的是多个条件的一部分。可以使用min函数代替。any(1,2,3) 等价于 min(1,2,3)

all：主要强调多个条件中的所有。可以使用max函数代替。all(1,2,3) 等价于 max(1,2,3)

/*
查询获得最高分的学生学号:
学号在student和studentcource表中都有。成绩在studentcource表中。
就直接在studentcource表中查学号就可以了。
*/

-- 找到最高分
SELECT MAX(score) FROM studentcource ;
-- 从表中查询出那个成绩等于最高分，然后得到它的id
SELECT student_id FROM studentcource WHERE score = (SELECT MAX(score) FROM studentcource );
-- 上面的sql可以使用 all 代替
SELECT score FROM studentcource;
-- 只要查询出的成绩大于等于上面的查询列表即可
SELECT student_id FROM studentcource WHERE score >= ALL(SELECT score FROM studentcource );
/*
  查询编号为2课程比编号为1课程成绩高的所有学号，不考虑是不是同一个学生
  
  先找出编号为1的课程的最低分。只要比这个最低分高，并且课程编号为2，就是我们要找的数据
*/
SELECT MIN(score) FROM studentcource WHERE cource_id = 1;

SELECT student_id  FROM studentcource WHERE cource_id = 2 AND 
score > (SELECT MIN(score) FROM studentcource WHERE cource_id = 1);

-- 上面的sql可以使用some 或 any 代替
SELECT student_id  FROM studentcource WHERE cource_id = 2 AND score > SOME(SELECT score FROM studentcource WHERE cource_id = 1);

-- 根据上面的结果查询学生姓名
SELECT * FROM student WHERE id IN(SELECT student_id  FROM studentcource WHERE cource_id = 2 AND 
score > (SELECT MIN(score) FROM studentcource WHERE cource_id = 1));

21.多表查询

查询成绩大于平均分的的同学的学号和姓名
/*
1、成绩和学号都在选课表中
2、需要计算平均成绩，avg函数
3、条件是比平均成绩大
4、结果是符合条件的学号，查询出姓名
*/

-- 平均成绩
SELECT AVG(score) FROM studentcource;
-- 条件
SELECT student_id FROM studentcource WHERE score > (SELECT AVG(score) FROM studentcource);
-- 根据id到学生表中查询学生的姓名
SELECT NAME , id FROM student WHERE id IN(SELECT student_id FROM studentcource WHERE score > (SELECT AVG(score) FROM studentcource));
-- 查询所有同学的学号、姓名、选课数、总成绩

/*
学号，姓名 student表中。
选课、学号、成绩在选课表中。

选课数：一个学生选了几门课。按照学生的学号分组，统计课程的id
学生的总成绩：按照学号分组，统计成绩

SELECT student_id , COUNT(cource_id) , SUM(score) FROM studentcource GROUP BY student_id;

SELECT student.* , temp.* FROM student , 
 (SELECT student_id , COUNT(cource_id) , SUM(score) FROM studentcource GROUP BY student_id) AS temp
 WHERE student.id = temp.student_id;

=============查询学过李彦宏老师所教课的同学的学号、姓名
分析：和上面的题目操作基本一致，只需要将not in 改为in即可。
– 查询老师讲授课程的id
SELECT cource.id FROM teacher , cource WHERE teacher.id = cource.teacher_id AND teacher.name = ‘李彦宏’;
– 查询学过指定老师课程的学生id
SELECT studentcource.student_id FROM studentcource , (SELECT cource.id AS cid FROM teacher , cource WHERE teacher.id = cource.teacher_id AND teacher.name = ‘李彦宏’
) AS temp WHERE studentcource.cource_id = temp.cid;
– 查询学生的姓名和id
SELECT student.id , student.name FROM student , (SELECT studentcource.student_id AS sid FROM studentcource , (SELECT cource.id AS cid FROM teacher , cource WHERE teacher.id = cource.teacher_id AND teacher.name = ‘李彦宏’
) AS temp WHERE studentcource.cource_id = temp.cid) AS temp2 WHERE student.id = temp2.sid;
==============查询没有学三门课以上的同学的学号、姓名
– 查询没有学三门课以上的同学的学号、姓名
/*
按照学生的学号分组，然后统计学过的课程数，条件课程数小于3
*/
SELECT student_id FROM studentcource GROUP BY student_id HAVING COUNT(cource_id) < 3;

SELECT * FROM student WHERE id IN(SELECT student_id FROM studentcource GROUP BY student_id HAVING COUNT(cource_id) < 3);

============查询各科成绩最高和最低的分
分析：需要按照课程id分组，查询出最高分和最低分。
– 查询成绩最高分和最低分，按照课程id分组
SELECT MAX(score) , MIN(score) FROM studentcource GROUP BY cource_id;

============查询学生信息和平均成绩
– 按照学号分组，计算学生的平均成绩
– 查询学生的平均成绩
SELECT student_id , AVG(score) FROM studentcource GROUP BY student_id;

SELECT student.* , temp.ss FROM student , (SELECT student_id , AVG(score) AS ss FROM studentcource GROUP BY student_id) AS temp WHERE student.id = temp.student_id;
=============查询陕西和北京学生数量
分析：按照城市进行分组统计
– 查询陕西和北京学生数量
SELECT COUNT(city) FROM student WHERE city = ‘陕西’ OR city = ‘北京’ group by city;
===========查询不及格的学生信息和课程信息
– 查询不及格的学生信息和课程信息
SELECT * FROM studentcource WHERE score < 60;
SELECT student.* , cource.* FROM student , (SELECT * FROM studentcource WHERE score < 60) AS temp , cource
WHERE student.id = temp.student_id AND temp.cource_id = cource.id;
===========统计每门课程的学生选修人数（超过两人的进行统计）
分析：根据课程id分组，统计出人数。书写条件，过滤出超人2人。
– 根据课程分组，统计
SELECT COUNT(student_id) , cource_id FROM studentcource GROUP BY cource_id ;
– 添加过滤条件，找出选课数大于2的信息
SELECT COUNT(student_id) , cource_id FROM studentcource GROUP BY cource_id HAVING COUNT(student_id) > 2;

准时~

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
数据库、MySql知识点

**1.SQL分类**DDL （数据定义语言）数据定义语言 - Data Definition Language 用来定义数据库的对象，如数据表、视图、索引等DML （数据操纵语言）数据处理语言 - Data Manipulation Language 在数据库表中更新，增加和删除记录如 update， insert， deleteDCL （数据控制语言）数据控制语言 – D...
复制链接

扫一扫

专栏目录