MySQL基础

1.MySQL安装与卸载

1.1安装

1. 下载地址

   https://dev.mysql.com/downloads/mysql/，选择安装包下载

2. 解压到D盘并重命名mysql8.0.26，路径为 D:\mysql8.0.26，将该目录下的bin目录路径添加到环境变量中

3. 在 D:\mysql8.0.26找到my.ini或者my.ini。如果没有my.ini结尾的文件，直接新建my.ini文件即可，然后新建一个data目录，在my.ini文件中加入如下内容（记得如果路径发生改变则需要改动）：

   [mysqld]
   # 设置3306端口
   port=3306
   # 设置mysql的安装目录
   basedir=D:\mysql8.0.26
   # 设置mysql数据库的数据的存放目录
   datadir=D:\mysql8.0.26\data
   # 允许最大连接数
   max_connections=200
   # 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
   max_connect_errors=10
   # 服务端使用的字符集默认为UTF8
   character-set-server=utf8
   # 创建新表时将使用的默认存储引擎
   default-storage-engine=INNODB
   # 默认使用“mysql_native_password”插件认证
   default_authentication_plugin=mysql_native_password
   
   [mysql]
   # 设置mysql客户端默认字符集
   default-character-set=utf8
   
   [client]
   # 设置mysql客户端连接服务端时默认使用的端口
   port=3306
   default-character-set=utf8
   

4. 安装myl
   在mysql8.0.26\bin目录中打开 cmd，执行初始化数据库的指令mysqld --initialize --console ,会生成一个root用户，并且由一个初始密码

5. 安装服务
   在mysql8.0.26\bin目录中执行指令 mysqld --install 服务名，如mysqld --install mysql8 ，如果没有权限，则以管理员身份打开cmd，重新执行上述命令。

6. 安装完成之后就可以用
   net start mysql8 启动 MySQL 的服务了
   net stop mysql8 停止服务

7. 登录
   mysql -u root -p 然后输入密码

8. 修改账户密码：
   登录之后：alter user 'root'@'localhost' identified with mysql_native_password BY 'new_password'

1.2卸载

1. 使用管理员身份关闭mysql服务，net stop mysql8
2. 删除mysql服务，sc delete mysql8 或者 mysqld remove mysql8
3. 删除mysqlDB目录(my.ini指定的目录)

2.SQL语言

• SQL(Structure Query Language), 结构化查询语言, 是一种标准化的语言，允许对数据库执行如创建项目、查询内容、更新内容、删除条目等操作。各数据库厂商都支持ISO的SQL标准，另外各厂商在标准的基础之上做了自己的扩展。
• SQL语句分类：
  • DDL(Data Definition Language): 数据定义语言，用来定义数据库对象：库、表、列等
  • DML(Data Manipulation Language):数据库操作语言，用来定义数据库记录(数据)增删改
  • DCL(Data Control Language): 数据控制语言，用来定义访问权限和安全级别
  • DQL(Data Query Language):数据查询语言，用来查询记录
• SQL语句以;结尾
• mysql中的关键字不区分大小写

2.1 DDL数据定义语言

数据库操作：

create database database_name;	-- 创建数据库

show databases;	-- 查看所有数据库

alter database database_name character set utf-8;-- 修改数据库编码方式为utf-8

drop database database_name;	-- 删除数据库

select database();	-- 查看当前使用的数据库

use database_name;	-- 切换数据库

数据表操作：

-- 创建表
create table table_name(attr1 type1 [约束1], attr2 type2 [约束1], ...);		-- 约束可选
-- 例如
create table student(
    name varchar(5), 
    age int, 
    sex char(1)
);

-- 删除表
drop table_name;

-- 查看表
show tables;

-- 查询所有的数据
select * from table_name

-- 查看表的具体信息
desc table_name;	-- desc describe缩写

-- 查看表格的创建细节
show create table table_name;

-- 修改表的字符集
alter table table_name character set gbk;	-- 修改编码方式为gbk方式

-- 修改
-- 添加列, 即添加一个属性
alter table table_name add attr_name type_name;
-- 修改列
alter table table_name change old_attr new_attr new_type_name;
-- 删除列
alter table table_name drop attr_name
-- 修改表名
alter table table_name rename new_table_name

type-----------常用数据类型：
int：整型
double：浮点型，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为999.99；默认支持四舍五入
char：固定长度字符串类型；    char(10)    'aaa       '  占10位
varchar：可变长度字符串类型； varchar(10)  'aaa'  占3位
text：字符串类型，比如小说信息；
blob：字节类型，保存文件信息(视频，音频，图片)；
date：日期类型，格式为：yyyy-MM-dd；
time：时间类型，格式为：hh:mm:ss
timestamp：时间戳类型 yyyy-MM-dd hh:mm:ss  会自动赋值
datetime:日期时间类型 yyyy-MM-dd hh:mm:ss

2.2 DML数据库操作语言

• DML是对表中的数据进行增、删、改的操作，主要包括insert、update、delete等操作

• 在mysql中，字符串类型和日期类型都要用单引号括起来；

• 空值为 null

1. insert

   • 插入单行

   insert into table_name(attr1, attr2, attr3) values(val1, val2, val3);

   • 当给所有列值添加数据时，attr可以忽略，但要注意val的值要与列的顺序(表创建时的顺序)一致，不能给错；

   insert into table_name values(val1, val2, val3);

   • 同时添加多行，每行值用逗号隔开即可，不要忘记以分号结尾；

     insert into table_name(attr1, attr2, attr3)

     values(val11, val21, val31),

     (val12, val22, val32),

     (val13, val23, val33)

     ...... ;

2. update

   运算符：

   （1）算数运算符：+ = * / %

   （2）逻辑运算符：and or not

   （3）关系运算符：> >= < <= != =(等于) <>(不等于)

   （4）赋值运算符：=

   修改特定列属性的所有值

   update table_name set attr1=val1, attr2=val2;

   根据特定条件修改，要修改的列属性逗号隔开，条件先加where，然后用and连接；

   update table_name set attr1=val1, attr2=val2, attr3=val3 where attr=val and attr2=val2;

   eg：update student set name='David', age=age+5, sex='男' where name='张三' and age=8;

3. delete & truncate

   delete from table_name where attr=val;	-- 删除符合条件的行
   
   delete from table_name;	-- 删除表中所有数据，但表格还在，空表
   
   truncate table table_name; -- 
   

   delete和truncate的区别：

   • delete 删除表中的数据，表结构还在; 删除后的数据可以找回
   • truncate 删除是把表直接 drop 掉，然后再创建一个同样的新表。
   • truncate 删除的数据不能找回。执行速度比 delete 快。

2.3 DCL数据控制语言

1. 创建用户

   管理员用户登陆才可以创建用户；

   -- 创建指定ip用户，
   create user user_name@ip_addr identified by 'password' ;
   -- eg:本地用户登录 localhost ip=127.0.0.1
   ate user test@localhost identified by '123456';
   
   -- 创建
   create user user_name@ip_addr identified by 'password';
   -- eg: 创建一个客户端登陆用户
   create user dashagua@10.45.133.67 identified by 'hahaha';
   
   -- 创建所有人ip可以登录的用户
   create user user_name@'%' identified by 'password';
   
   

   查看用户：

   select user, host, authentication_string from mysql.user;
select user();

2. 用户授权

   -- 将数据库中表的某些权限授给特定用户 grant ... on ... to ...
   grant 权限1，权限2，权限3 on database_name.table_name to 'user_name'@'ip_addr';
   -- eg：将test数据库中所有表的select, update, delete, add权限授给本地的test用户
   grant select, update, delete, add on test.* 'test'@localhost;
   
   -- 将所有数据库中的所有表的所有权限授给某用户，超级管理员
   grant all on *.* to 'user_name'@'ip_addr'
   

3. 查询权限

   show grants for 'user_name'@'ip_addr'
   
   -- eg
   show grants for 'root'@'%';
   show grantd for 'test'@localhost;
   

4. 撤销权限

   -- 与授权的语法类似 revoke ... on ... from ...
   revoke 权限1, 权限2, 权限3 data_name.table_name from 'user_name'@'ip_addr';
   

5. 删除用户

   drop user 'user_name'@'ip_addr';
   

2.4 DQL 查询语句

DQL:数据查询语言

• 数据库执行DQL语句不会改变数据库的数据，而是让数据库发送结果给客户端；

• 查询返回的是一张虚拟表；

• 查询的关键字select

• 语法： select attr from table_name where ... group by ... having ... order by ...

1. 简单查询语句：

   select attr1, attr2, attr3 from table_name;
   select * from table_name;
   

2. 条件查询：

   在查询时给出where关键字，在where子句中可以使用如下运算符及关键字；

   =, !=, <>, <, <=, >, >=; between ... and ; in () ; is null/ is not null ; and ; or ; not ;

   select * from table_name where sex='female' and age<30;
   
   select * from table_name where id=1001 or name="张三";
   
   select * from table_name where age=16 or age=17 or age=18;
   -- 等价于 
   select * from table_name where age in (16, 17, 18);
   
   select * from table_name where age not in (16, 17, 18);
   
   select * from table_name where age>=16 and age<=20;
   -- 等价于
   select * from table_name where age between 16 and 20;
   
   select * from table_name where age is null;
   
   select * from table_name where sex!='男';
   select * from table_name where sex<>'男';
   select * from table_name where not sex='男';
   
   select * from table_name where not age is null;
   select * from table_name where age is not null;
   

3. 模糊查询

   • 关键字 like

   • 语法：attr like '表达式' 表达式必须是字符串，故需要用单引号

   • 通配符：
     (1) _ 下划线，代表任意 1 个字符；

     (2) % 百分号，代表任意 0-n 个字符；

   select * from table_name where name like '_';
   select * from table_name where name like '__';
   select * from table_name where name like '___';
   
   select * from table_name where name like '___p';
   
   select * from table_name where name like 'z%';
   
   select * from table_name where name like '_i%';
   
   select * from table_name where name like '%b%';
   

4. 字段控制查询

   (1) 去除重复记录

   ​ 当查询特定属性（列）的值时，出现两行或两行以上的数据相同，那么此时为重复记录，去出重复记录，需要使用 distinct

   select distinct attr from table_name;
   

   (2) 查看两列之和

   ​ 当两列的属性都是数值类型时，可以做算术运算，如果的多列中有一个属性不是数值类，那么做运算时会报错；

   select attr1+attr2 from table_name;
   

   ​ 如果有值为null的情况(null与任何值相加还是null)，可以使用IFNULL()：

   -- attr2 如果为null，用0作为替代
   select attr1+IFNULL(attr2, 0) from table_name;
   

   (3) 给列名添加别名

   ​ 上述 attr1+IFNULL(attr2, 0) 会使得列名很复杂，可以给列名取别名

   -- 将列的输出名称改为new_name,其中as可以省略
   select attr1+IFNULL(attr2, 0) as new_name from table_name;
   

5. 排序

   (1) 单列排序

   语法： order by attr asc/desc asc升序排列(default)，desc降序排列；

   -- 根据attr属性的升序排列整个表
   select * from stu order by attr asc;
   
   -- 根据attr属性的降序排列整个表
   select * from stu order by attr desc;
   

   (2) 多列排序

   语法：order by attr1 asc/desc, attr2 asc/desc;

   -- 根据attr属性的升序排列整个表, attr1属性相同时，按照attr2的降序排列
   select * from stu order by attr1 asc, attr2 desc;
   

   ​

6. 聚合函数

   聚合函数用来做纵向运算的函数：

   • count(attr)：统计列中值不为null的行数，输出的是行数；

   • max(attr)：列中的最大值；如果指定列时字符串类型，则按字符串排序规则

   • min(attr)：列中的最小值；如果指定列时字符串类型，则按字符串排序规则

   • sum(attr)：列所有值的和；如果指定列类型非数值类型，计算结果为0；

   • avg(attr)：列中所有值的平均值，如果指定列类型非数值类型，计算结果为0；

/*
count();
*/
-- 统计表中的行数(记录数), 输出一个值, 值为表中的非空行数(有一个属性非空即视为非空行)
select count(*) as cnt from table_name;
-- 统计attr属性非空的行数
select count(attr) from table_name;

-- 统计薪水大于15000的个数
select count(*) from table_name where salary>15000;
-- 输出一行值，分别统计attr1不为空的个数和attr2不为空的个数；
select count(attr1), count(attr2)  from table_name;

/*
sum();
*/
-- 统计列的和
select sum(attr) from table_name;
select sum(attr1), sum(attr2) from table_name;
select sum(attr1 + ifnull(attr2)) from tbale_name;

/*
avg();
*/
-- 统计平均值
select avg(attr) from table_name;
select avg(attr1), avg(attr2) from table_name;

/*
max(), min();
*/
select max(attr1) as max_attr1, min(attr2) min_attr2 from table_name;

7. 分组查询

   • 关键字：group by, having
   • 如果查询语句中有分组操作，那么select后面只能接被分组的列名和聚合函数

 -- 分别统计不同年龄的人数，输出多行值(age列有多少个不同的值就输出多少行)
 select age, count(*) from table_name group by age;
 
 -- 查询各班级的班名并统计各班的人数
 select class, count(*) from table_name group by class;
 
 /*
 多列分组
 */
 -- 查询各班级的班名并统计各班的男女生人数, 即班级相同时进一步根据性别分组
 select class,sex count(*) from table_name group by class, sex;
 
 -- 查询各班级的班名并统计各班的女生人数
 select class, count(*) from table_name where sex='girl' group by class;
 
 -- 查询平均分大于85分的班级，并统计这些班级的总人数
 select class, count(*) from table_name group by class having avg(score) > 85;

having 和 where的区别：

• having是在分组后对数据进行过滤, where是在分组前对数据进行过滤；
• having后面可以使用分组函数(统计函数)，where后面不可以使用分组函数。
• WHERE是对分组前记录的条件，如果某行记录没有满足WHERE子句的条件，那么这行记录不会参加分组；而HAVING是对分组后数据的约束。

​

8. limit

   -- 查询5行数据，从第一行开始到第五行
   select * from table_name limit 0, 5;
   -- 查询10行数据，从第3行开始到第13行
   select * from table_name limit 3, 10;
   

如果一夜记录为8条，希望查看第9页记录应该如何查询？

limit 用变量代替具体数值；

​

​

​

• 查询语句书写顺序：select – from- where- groupby- having- order by-limit
• 查询语句执行顺序：from - where -group by -having - select - order by-limit