智警杯数据库学习（1）

CentOS中安装MySQL数据库

检测系统是否自带安装 MySQL

首先检查是否自带mysql

rpm -qa | grep mysql

如果有删除

rpm -e mysq

未安装，开始安装

进入software目录，解压安装包mysql5.7.25

cd /root/software
tar -xvf mysql-5.7.25-1.el7.x86_64.rpm-bundle.tar

安装组件rpm -ivh

在安装组件是存在一定的先后顺序，还是按照以下顺序来安装避免浪费时间

rpm -ivh mysql-community-common-5.7.25-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.25-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-compat-5.7.25-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-5.7.25-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-5.7.25-1.el7.x86_64.rpm

组件的包就在software下面，分别是common，libs，libs-5，client，server，找到去用rpm -ivh安装

初始化mysql数据库

# 不使用root用户，密码为空
/usr/sbin/mysqld --initialize-insecure --user=mysql

后台启动服务

/usr/sbin/mysqld --user=mysql &

免密登录

如果出现免密登录报错的情况应该是在安装组件的时候顺序不对，而且可以在初始化数据库的语句后面加“&”

mysql -uroot

修改mysql登陆密码(教学改为：123456)

use mysql
update mysql.user set authentication_string=password('123456') where user='root';
flush privileges;

创建用户

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;

执行 flush privileges命令立即生效

flush privileges;

查询数据库的用户

SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;

数据库与数据表的管理

mysql语句

创建数据库

create database name;

如果存在数据库就会显示数据库已存在

创建相同数据库，使用if not exixts，防止报错

查看所有数据库

show databases;

有些是默认创建的数据库

查看指定数据库

show database create name;

修改数据库

核心的关键字是alter，alter database用于更改数据库全局特性，用户必须具有数据库 修改权限，才可以使用alter database修改数据库。语法后面的默认字符集以及默认校对规则是可选项，不是必选项。

alter database db_name character set new_charset;

alter database为修改数据库的固定语法格式。 db_name为要修改的数据库名称。 character set表示修改的是数据库的字符集。 new_charset为新的字符集名称。

删除数据库

　语法关键是drop，if exists也是可选项，如果不加该语句，删除不存在的数据库就会报错。与if not exists语句正好相反，同样也是具有避免抛错的作用。

drop database name;

创建表

create table student(
id INT(8),
name VARCHAR(20),
gender VARCHAR(1),
age INT(2)
);

查看student表的基本结构

desc student;

查看student表的详细结构

show create table student;

修改表名

将student表的名称修改为sxt_student

alter table student rename to sxt_student;

alter table student rename sxt_student;

修改之后通过查询所有表来判断是否修改成功，查询表结构和之前是否完全相同

修改一个字段的数据类型

将sxt_student表中的“id”字段的数据类型由int(8)修改为int(10)

alter table sxt_student modify id int(10);

使用desc语句查看表的基本结构，验证数据类型是否修改成功

desc sxt_student;

修改多个字段的数据类型

将sxt_student表中的“id”字段的数据类型修改为int(20)，将“name”字段的数据类型修改为varchar(10)

alter table sxt_student modify id int(20), modify name varchar(10);

使用desc语句查看表的基本结构，验证数据类型是否修改成功

desc sxt_student;

修改字段名

alter table 表名 change 字段名 改为什么

将sxt_student表中“gender”的字段名修改为“sex”

alter table sxt_student change gender sex varchar(1);

使用desc语句查看表的基本结构，验证字段名是否修改成功

增加字段

alter table 表名 add 添加内容 位置or类型

在sxt_student表的最后位置添加一个名为“score”的字段，数据类型为float

alter table sxt_student add score float;

在sxt_student表的第一个位置添加一个名为“phone”，数据类型为varchar(11)的字段

alter table sxt_student add phone varchar(11) first;

之后使用desc语句查看表的基本结构，验证字段是否增加成功

修改字段的排列位置

将“id”字段移动到表的第一个位置。

alter table sxt_student modify id int(20) first;

删除字段

 alter table 表名 drop 删除字段

alter table sxt_student drop phone;

删除使用desc语句查看表的基本结构，是否删除成功

删除表

drop table 表名

使用show tables语句查看数据库中所有的表,检查表是否删除成功

数据库基本语句

插入数据

插入数据的四种方式：为所有字段插入数据，为指定字段插入数据，同时插入多条数据，插入查询结果

为所有字段插入数据

insert [into] table_name [(column_name1, column_name2, ···)] values|value (value1, value2, ···);

AUTO_INCREMENT 表示该字段的值会自动递增，每次插入新记录时，数据库会自动生成一个唯一的整数值。

PRIMARY KEY 是主键约束，表示 stu_id 是表的主键，唯一标识表中的每一行记录。

DEFAULT是设置默认值为‘nan’

unique是唯一性约束，代表这个表中只能存在一个这个数据，不能出现重复的数据

为指定字段插入数据

insert [into] table_name (column_name1, column_name2, ···) values|value (value1, value2, ···);

column_name1和column_name2分别指定添加数据的字段名。 value1和value2分别表示column_name1字段和column_name2字段的值。 在此需要注意的是，value值要和指定字段的顺序、数据类型相对应，即value1对应column_name1字段，value2对应column_name2字段。

同时插入多条数据

MySQL中提供了同时插入多条数据的SQL语句，其可以实现为所有字段或指定字段同时插入多条数据

为所有字段同时插入多条数据

insert [into] table_name [(column_name1, column_name2, ···)]
values|value (value11, value21, ···),
(value12, value22, ···),
···;

为指定字段同时插入多条数据

insert [into] table_name (column_name1, column_name2, ···)
values|value (value11, value21, ···),
(value12, value22, ···),
···;

插入查询结果

MySQL中还可以通过insert语句将从一张表中查询到的结果直接插入到另一张表中，这样就间接地实现了数据的复制功能

insert [into] table_name1(column_list1)
select column_list2 from table_name2 where where_condition;

通过select查询之后的结果放入到新表中，相当于是把旧表复制到新表中，类似子查询

更新指定记录

更新指定记录的前提是根据条件找到指定的记录，SQL语句需要结合使用update和where语句

update table_name
set column_name1 = value1[, column_name2 = value2, ···]
where where_condition;

将原表中zhangsan的名字和email更新为新值

更新全部记录

在SQL语句基础上去掉where子句即可

update table_name set column_name1 = value1[, column_name2 = value2, ···];

将原表中所有人的年龄更新为20

删除指定记录

根据条件找到指定的记录，需要结合使用delete和where语句

delete from table_name where where_condition;

删除全部记录

在上述的语句中去掉where

delete from table_name;

使用truncate语句删除数据

truncate [table] table_name;

删除学号大于6的

所有字段的查询

select column_name1, column_name2, ··· , column_namen from table_name;

或

select * from table_name;

查询所有的字段可以使用select来指定所有的字段名，也可以用*通配符来匹配所有的字段名

指定字段的查询

select column_name1, column_name2, ··· from table_name;

select指定查询的字段就行

去除重复记录的查询

使用distinct去除重复部分

select distinct column_name1, column_name2, ··· from table_name;

使用算术运算符的查询

使用字段别名的查询

用as来命名字段的别名

select column_name1 [as] othername1, column_name2 [as] othername2, ··· from table_name;

设置数据显示格式的查询

用concat函数设计数据显示的格式

数据库进阶查询

实训

数据库支持中文字符

Set character_set_database='utf8';
Set character_set_server='utf8';

创建数据库并且插入数据之后

一

统计每个学生的成绩总分

select a+b+c as new name from table_name

二

1.将表中姓名和班级连起来，格式为：张飞（2）;

2.指定分隔符(-)对数据中姓名、语数外进行连接。

第一问是要把姓名和班级连起来，就要改变数据的显示格式，用concat来实现

CONCAT 函数用于将多个字符串连接在一起

第二问指定分隔符连接

GROUP_CONCAT 函数可以用来将分组后的数据进行连接，并可以指定分隔符

聚合函数

1.计算各班级学生数学平均成绩

2.计算1班中语文最高分

第一题用聚合函数求平均分，再使用group by分组查询，因为涉及各班级的平均分

第二题计算一班语文最高分

分组 group by

1.计算各班级学生数学平均成绩

各班级平均分，分组查询，求数学平均成绩

2.计算数学平均分80以上的班级

Having

having的使用

过滤聚合结果：在数据分组后，根据聚合结果进行筛选。

复杂条件过滤：在聚合函数结果的基础上添加复杂条件。

对特定分组进行进一步分析：在分组后，进一步筛选需要关注的特定分组。

having和where的区别

WHERE 子句用于过滤原始数据，在数据被分组之前进行过滤。

HAVING 子句用于过滤分组后的数据，通常和聚合函数一起使用。

case when检索函数

CASE WHEN 是 SQL 中用于实现条件逻辑的表达式，允许根据条件对数据进行分类、计算或选择。它类似于编程语言中的条件语句，如 if-else。

CASE 关键字开始 CASE WHEN 表达式。

每个 WHEN 子句后面跟随一个条件 (condition) 和一个结果 (result)。

可以有多个 WHEN 子句，每个条件可以是复杂的逻辑表达式。

ELSE 子句是可选的，用于指定所有条件都不满足时的默认结果 (else_result)。

END 结束 CASE WHEN 表达式。

1.对英语成绩进行划分，>90为优秀，<=90 and >=60为良好，<60为不及格。

2.对英语成绩进行划分，>90为优秀，<60为不及格，null为缺考，其他为良好。

3.对英语成绩进行评估，统计有多少学生成绩为优秀

视图

视图（View）是一种虚拟的表，它是基于 SQL 查询结果定义的命名查询。视图并不存储实际的数据，而是根据定义的查询从一个或多个表中获取数据。通过视图，可以将复杂的查询结果保存为一个可重复使用的对象，简化了数据访问和提高了安全性

创建视图名为‘班级人数’，按照班级分组，进行各班人数计数。

create view 视图名称(<视图列名1>,<视图列名2>,......) as <select 查询语句>;

子查询

将一个查询语句包含在另一个查询语句中，那么这个查询语句就是子查询语句。可以认为是将定义视图的select语句直接用于from子句中

哪些学生的数学成绩比“张飞”的高？

关联子查询

关联子查询（Correlated Subquery）是一种特殊的子查询形式，在执行过程中与外部查询相关联。与普通的子查询不同，关联子查询的每一次执行都依赖于外部查询的当前行。这种关联关系使得子查询的执行次数通常比外部查询的行数多，因此可能会对性能产生影响