前记：关系型数据库通过外键关联来建立表与表之间的关系，非关系型数据库通常指数据以对象的形式存储在数据库中，而对象之间的关系通过每个对象自身的属性来决定。
一、数据库操作
登录数据库
mysql -u root -p
退出数据库
quit 或 exit
查看数据库版本
mysql -V #未登录使用
mysql --version #未登录使用
select version() #登录使用
显示数据库
show databases;
show schemas;
select database();
创建数据库
create database tyut charset=utf8/utf8mb4;
修改数据库：
唯一能够修改编码 alter database tyut charset =utf8mb4;
删除数据库
drop database tyut;
二、表的操作
创建表
create table student(
student_id bigint(10) not null,
name varchar(20) default null,
state char(1) default null,
primary key(student_id)
);
显示表
show tables;
修改表的名字
rename table student
删除表
drop table student;
三、字段的操作
操作字段的关键字 alter
给表添加字段
alter table student add(class varchar(20));
修改字段名
alter table student change 原字段名 新字段名 类型;
alter table student change weight w int(10);
修改字段类型
alter table student modify weight double;
删除字段
alter table student drop a3;
约束
作用：保证数据的完整性和一致性
分类: PRIMARY KEY (PK)，标识该字段为该表的主键，可以唯一的标识记录
FOREIGN KEY (FK)，标识该字段为该表的外键
NOT NULL，标识该字段不能为空
UNIQUE KEY (UK)，标识该字段的值是唯一的
AUTO_INCREMENT， 标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT， 为该字段设置默认值
primary key
方法一：在某一个字段后用primary key
create table test{id int primary key};
方法二：not null+unique
创建表时未指定主键，会找不为空且唯一的字段作为主键
create table test(id int null unique);
foreign key
建立表之间的关系
create table student(
``id int primary key,
``name char(``255``),
``age ``int
);
# 关联的表
create table ``class``(
``id int primary key,
``name char(``255``),
``stu_id ``int``,
``foreign key(stu_id) references student(``id``)
``on delete cascade ``# 删除同步
``on update cascade ``# 修改同步
);
四、语句操作
查询语句
select * from scores;
插入语句
①sert into scores(id,name,sex,math,english,chinese,group_name,review)
values(1,"刘峰","男",98,121,135,"傅航组","此次考试刘峰成绩优秀");
②sert into scores(name,sex,math,english,chinese,group_name,review)
values("刘峰","男",98,121,135,"傅航组","此次考试刘峰成绩优秀");
③insert into scores values(id,"刘峰","男",98,121,135,"傅航组","此次考试刘峰成绩优秀");
修改语句
update scores set sex="女" where id =14;
五、运算符、条件查询、函数、分组、排序、通配符
查询所有学生数据成绩>80的学生信息
select * from scores where math>80;
查询所有语文成绩在125,105,135
select * from scores where chinese in(125,105,135);
查询所有信息，包含数学语文英语的成绩总和
select *,(math+chinese+english) count from scores;
查询所有信息，包含数学语文英语的成绩总和,总和>354
select *,(math+chinese+english) count from scores where (math+chinese+english) >336;
查询刘开头的学生信息
select * from scores where name like "刘_";
select * from scores where name like "刘%";
加号的作用
select 100+90 两个数据都是数值，做加法运算
select "100"+90 两个数据一个是数值字符串，一个是数值，做加法运算
select "liu"+90 两个数据一个是非数值字符串，一个是数值，则将非数值字符串转换为0，再做加法运算
select null+90 null和任何数据相加，结果都是null
常用函数：
去重： select distinct department_id from employees;
拼接字符：select concat(first_name,',',last_name) all_name from employees;
判断函数：select salary,ifnull(commission_pct,0)from employees limit 10;
查询工资在10000-20000之间的员工姓名、工资和奖金
SELECT first_name,salary,commission_pct,salary*IFNULL(commission_pct,0) FROM employees
where salary BETWEEN 10000 and 20000
查询排序：order by
查询成绩，从高到低排序 desc
select * from scores order by math desc;
查询按入职先后且员工编号>=90的员工名和入职日期
SELECT first_name, hiredate FROM employees WHERE employee_id >=90 ORDER BY hiredate asc\
查询员工名和年薪,按年薪从高到低排序
SELECT first_name,salary12(1+IFNULL(commission_pct,0)) year_salary FROM employees ORDER BY year_salary desc
查询员工名和年薪,按姓名长度从高到低排序
SELECT first_name,length(first_name) ,salary12(1+IFNULL(commission_pct,0)) year_salary FROM employees ORDER BY length(first_name) desc
查询员工信息,先按工资升序,再按员工编号降序
select employee_id,first_name,last_name,salary from employees order by salary,employee_id desc
聚合函数：分组函数，主要用于统计(count sum avg max min)
注意：avg sum 在统计时候，null会忽略，count max min 不会忽略null
查询所有员工总数
SELECT count(*) FROM employees
查所有人的工资总和
SELECT sum(salary) FROM employees
查所有人的平均
SELECT avg(salary) FROM employees
查所有人的最高
SELECT max(salary) FROM employees
分组group by
查询每个部门的id和部门的平均工资，按照平均工资的升序排序
select department_id,avg(salary) avg_salary from employees where department_id is not null GROUP BY department_id ORDER BY avg_salary asc
分组条件：having
查询员工个数>2的部门
select department_id,count(*) emp_total from employees
where department_id is not null GROUP BY department_id having emp_total>2
注意：在使用查询条件、分组、分组条件having、排序时候，顺序是where条件 分组 分组条件 排序
六、多表联查
连接查询：又称为多表查询--查字段来自多个表
交叉连接 -- 笛卡尔积
内连接：等值内连接 非等值内连接 自连接
外连接：左外连接 右外连接 全连接
(59条消息) Mysql中的七种常用查询连接详解_爱吃面的猫的博客-CSDN博客_mysql连接查询的几种方式
七、事务管理
什么是事务
官网解释是：事务是由一些列对系统中数据进行访问与更新的操作所组成的一个程序执行单元。
就是一条或者几条SQL语言对数据库进行操作的程序的总和，只有成功与失败，不会只运行其中几条。
执行步骤
start transaction/begin //开启事务
commit //提交事务
rollback //事务回滚
事务的特性
原子性
一致性
隔离性
持久性
事务的隔离级别
read uncommitted -- 此级别产生 脏读，不可重复读，幻读
read committed -- 此级别产生 不可重复读，幻读，解决脏读
repeatable read -- 此级别产生 幻读，解决脏读，不可重复读
serializable -- 解决脏读，不可重复读，幻读
八、索引
- 按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。
- 按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引） 。
- 按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。
- 按「字段个数」分类：单列索引、联合索引。
索引概述
数据库索引是用于提高数据库表的数据访问速度的。有的数据库也使用哈希作为索引的数据结构，但主流的RDBMS都是把平衡树当做数据表默认的索引数据结构的。
索引特点
避免进行数据库全表的扫描，大多数情况，只需要扫描较少的索引页和数据页，而不是查询所有数据页。对于非聚集索引，有时不需要访问数据页即可得到数据。聚集索引可以避免数据插入操作，集中于表的最后一个数据页面。
聚集索引
我们平时建表的时候都会为表加上主键， 在某些关系数据库中， 如果建表时不指定主键，数据库会拒绝建表的语句执行。如果给表上了主键，那么表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构，也就是上面说的「平衡树」结构，换句话说，就是整个表就变成了一个索引。没错， 再说一遍， 整个表变成了一个索引，也就是所谓的「聚集索引」。
这就是为什么一个表只能有一个主键， 一个表只能有一个「聚集索引」，因为主键的作用就是把「表」的数据格式转换成「索引（平衡树）」的格式放置。
非聚集索引
非聚集索引和聚集索引一样， 同样是采用平衡树作为索引的数据结构。索引树结构中各节点的值来自于表中的索引字段， 假如给user表的name字段加上索引 ， 那么索引就是由name字段中的值构成，在数据改变时， DBMS需要一直维护索引结构的正确性。如果给表中多个字段加上索引 ， 那么就会出现多个独立的索引结构，每个索引（非聚集索引）互相之间不存在关联。
每次给字段建一个新索引， 字段中的数据就会被复制一份出来， 用于生成索引。 因此， 给表添加索引，会增加表的体积， 占用磁盘存储空间。
非聚集索引和聚集索引的区别
非聚集索引和聚集索引的区别在于，通过聚集索引可以查到需要查找的数据， 而通过非聚集索引可以查到记录对应的主键值 ，再使用主键的值通过聚集索引查找到需要的数据， 不管以任何方式查询表， 最终都会利用主键通过聚集索引来定位到数据， 聚集索引（主键）是通往真实数据所在的唯一路径。
key 与 index
mysql的key和index多少有点令人迷惑，单独的key和其它关键词结合的key(如：primary key)实际表示的意义是不同，这实际上考察对数据库体系结构的了解的。
-
key 是数据库的物理结构，它包含两层意义和作用:
- 约束（偏重于约束和规范数据库的结构完整性），
- 索引（辅助查询用的）。
-
index 是数据库的物理结构，它只是辅助查询的，它创建时会在另外的表空间（mysql中的innodb表空间）以一个类似目录的结构存储。 因此，索引只是索引，它不会去约束索引的字段的行为（那是key要做的事情）。
SQL23
题目：运营商想要计算一些参加了答题的不同学校、不同难度的用户平均答题量，请你写SQL取出相应数据
用户信息表：user_profile
| id | device_id | gender | age | university | gpa | active_days_within_30 | question_cnt | answer_cnt |
|---|---|---|---|---|---|---|---|---|
| 1 | 2138 | male | 21 | 北京大学 | 3.4 | 7 | 2 | 12 |
| 2 | 3214 | male | NULL | 复旦大学 | 4 | 15 | 5 | 25 |
| 3 | 6543 | female | 20 | 北京大学 | 3.2 | 12 | 3 | 30 |
| 4 | 2315 | female | 23 | 浙江大学 | 3.6 | 5 | 1 | 2 |
| 5 | 5432 | male | 25 | 山东大学 | 3.8 | 20 | 15 | 70 |
| 6 | 2131 | male | 28 | 山东大学 | 3.3 | 15 | 7 | 13 |
| 7 | 4321 | male | 28 | 复旦大学 | 3.6 | 9 | 6 | 52 |
第一行表示:id为1的用户的常用信息为使用的设备id为2138，性别为男，年龄21岁，北京大学，gpa为3.4，在过去的30天里面活跃了7天，发帖数量为2，回答数量为12
最后一行表示:id为7的用户的常用信息为使用的设备id为4321，性别为男，年龄28岁，复旦大学，gpa为3.6，在过去的30天里面活跃了9天，发帖数量为6，回答数量为52\
题库练习明细表：question_practice_detail
| id | device_id | question_id | result |
|---|---|---|---|
| 1 | 2138 | 111 | wrong |
| 2 | 3214 | 112 | wrong |
| 3 | 3214 | 113 | wrong |
| 4 | 6534 | 111 | right |
| 5 | 2315 | 115 | right |
| 6 | 2315 | 116 | right |
| 7 | 2315 | 117 | wrong |
| 8 | 5432 | 117 | wrong |
| 9 | 5432 | 112 | wrong |
| 10 | 2131 | 113 | right |
| 11 | 5432 | 113 | wrong |
| 12 | 2315 | 115 | right |
| 13 | 2315 | 116 | right |
| 14 | 2315 | 117 | wrong |
| 15 | 5432 | 117 | wrong |
| 16 | 5432 | 112 | wrong |
| 17 | 2131 | 113 | right |
| 18 | 5432 | 113 | wrong |
| 19 | 2315 | 117 | wrong |
| 20 | 5432 | 117 | wrong |
| 21 | 5432 | 112 | wrong |
| 22 | 2131 | 113 | right |
| 23 | 5432 | 113 | wrong |
第一行表示:id为1的用户的常用信息为使用的设备id为2138，在question_id为111的题目上，回答错误
最后一行表示:id为23的用户的常用信息为使用的设备id为5432，在question_id为113的题目上，回答错误
表：question_detail
| id | question_id | difficult_level |
|---|---|---|
| 1 | 111 | hard |
| 2 | 112 | medium |
| 3 | 113 | easy |
| 4 | 115 | easy |
| 5 | 116 | medium |
| 6 | 117 | easy |
第一行表示: 题目id为111的难度为hard
最后一行表示: 题目id为117的难度为easy
请你写一个SQL查询，计算不同学校、不同难度的用户平均答题量，根据示例，你的查询应返回以下结果(结果在小数点位数保留4位，4位之后四舍五入)：
| university | difficult_level | avg_answer_cnt |
|---|---|---|
| 北京大学 | hard | 1.0000 |
| 复旦大学 | easy | 1.0000 |
| 复旦大学 | medium | 1.0000 |
| 山东大学 | easy | 4.5000 |
| 山东大学 | medium | 3.0000 |
| 浙江大学 | easy | 5.0000 |
| 浙江大学 | medium | 2.0000 |
解释：
第一行：北京大学有设备id为2138，6543这2个用户，这2个用户在question_practice_detail表下都只有一条答题记录，且答题题目是111，从question_detail可以知道这个题目是hard，故 北京大学的用户答题为hard的题目平均答题为2/2=1.0000
第二行，第三行：复旦大学有设备id为3214，4321这2个用户，但是在question_practice_detail表只有1个用户(device_id=3214有答题，device_id=4321没有答题，不计入后续计算)有2条答题记录，且答题题目是112，113各1个，从question_detail可以知道题目难度分别是medium和easy，故 复旦大学的用户答题为easy, medium的题目平均答题量都为1(easy=1或medium=1) /1 (device_id=3214)=1.0000
第四行，第五行：山东大学有设备id为5432和2131这2个用户，这2个用户总共在question_practice_detail表下有12条答题记录，且答题题目是112，113，117，且数目分别为3，6，3，从question_detail可以知道题目难度分别为medium,easy,easy，所以，easy共有9个，故easy的题目平均答题量= 9(easy=9)/2 (device_id=3214 or device_id=5432) =4.5000，medium共有3个，medium的答题只有device_id=5432的用户，故medium的题目平均答题量= 3(medium=9)/1 ( device_id=5432) =3.0000
select
university,
difficult_level,
round(count(qpd.question_id) / count(distinct qpd.device_id), 4) as avg_answer_cnt
from question_practice_detail as qpd
left join user_profile as up on up.device_id=qpd.device_id
left join question_detail as qd on qd.question_id=qpd.question_id
group by university, difficult_level
