MySQL8数据库安装及SQL语句详解

mysql8数据库安装

一、windows 环境下安装

a、下载 mysql

下载地址

select operating system:
microsoft windows

快捷下载：mysql-8.0.22-winx64.zip

b、解压并配置mysql环境变量

mysql_home:
c:\mysql\mysql-8.0.22-winx64

c、在解压根目录创建my.ini配置文件

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

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4

[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
# 设置mysql客户端连接服务端时默认使用的字符集
default-character-set=utf8mb4

d、安装 mysql (以下操作必须是管理员身份)

• 初始化mysql

mysqld --defaults-file=c:\mysql\mysql-8.0.22-winx64\my.ini --initialize --console

注意：复制保存 mysql初始化密码 fvdpg:bm9pak

• 安装mysql服务

mysqld --install mysql8

• 启动mysql服务

net start mysql8

e、登录、修改密码

• 登录 mysql

mysql -u账号 -p密码

使用上面方式无法登录的解决方案

1、停止 mysql8 net stop mysql8

2、无密码启动 mysqld --console --skip-grant-tables --shared-memory

3、前面窗口不能关闭，再开启一个新的窗口进行无密码登录 mysql -u root -p

4、清空密码 update mysql.user set authentication_string='' where user='root' and host='localhost;'

5、刷新权限 plush privileges;

6、重新启动 mysql 服务，再以无密码登录 mysql

• 登录后使用mysql修改密码

alter user root@localhost identified by '123456';

• 开启远程访问

create user 'root' @'%' identified by '123456'; -- 这一步执行失败也没关系

grant all on *.* to 'root' @'%';

# alter user 'root'@'%' identified with mysql_native_password by '123456';

flush privileges;

二、linux 环境下安装

a、下载 mysql

下载地址

select operating system:
source code

select os version:
generic linux (architecture independent)

快捷下载：mysql-8.0.22.tar.gz

b、把下载的 mysql 压缩包上传到 linux 服务器

c、解压mysql-8.0.22.tar.gz

tar -zxvf mysql-8.0.22.tar.gz

d、把解压后的文件移动到 /usr/local 目录下

mv mysql-8.0.22 /usr/local/mysql

e、添加mysql组合用户 （默认会添加，没有添加就手动添加）

groupadd mysql
useradd -r -g mysql mysql

f、进入 /usr/local/mysql 目录，修改相关权限

cd /usr/local/mysql
chown -r mysql:mysql ./

g、mysql初始化操作，记录临时密码

cd /usr/local/mysql/bin
./mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

注意：复制保存 mysql初始化密码 fvdpg:bm9pak

h、创建mysql配置文件 /etc/my.cnf

cd /etc
vi my.cnf

my.cnf

[mysqld]
port = 3306
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
max_connections=200
max_connect_errors=10
character-set-server=utf8mb4
default-storage-engine=innodb
default_authentication_plugin=mysql_native_password

[mysql]
default-character-set=utf8mb4

[client]
port=3306
default-character-set=utf8mb4

i、启动mysql服务

cd /usr/local/mysql/support-files
./mysql.server start

j、通过临时密码登录mysql并修改密码

cd /usr/local/mysql/bin
./mysql -u root -p生成的临时密码 
alter user 'root' @'localhost' identified by '123456';

k、开启远程访问

create user 'root' @'%' identified by '123456';  -- 这一步执行失败也没关系

grant all on *.* to 'root' @'%';

flush privileges;

mysql 数据库操作

数据库操作

创建数据库

create database db_name default character set utf8mb4 collate utf8mb4_general_ci;

查询数据库

-- 查询所有数据库
show databases;
-- 查询数据库建表时的sql脚本
show create database db_name;

删除数据库

drop database db_name;

修改数据库

-- 修改数据库的字符编码和排序方式
alter database db_name default character set utf8 collate utf8_general_ci;

选择数据库

use db_name;

设置操作的编码格式

set names utf8;

表操作

创建表

create table tb_name (建表的字段、类型、长度、约束、默认、注释)

约束

• 非空 not null
• 非负 unsigned
• 主键 primary key
• 自增 auto_increment
• 默认 default
• 注释 comment

-- 数据库存在就删除
drop database if exists testdb;
-- 创建数据库的操作
create database if not exists testdb;
-- 使用数据库
use testdb;
-- 数据表存在就删除
drop table if exists testdb;
-- 创建表的操作
create table if not exists tb_test 
( 
	test_id integer ( 10 ), 
	test_name varchar ( 50 ) 
);

-- 使用数据库
use testdb;
-- 数据表存在就删除
drop table if exists testdb;
-- 创建表的操作
create table if not exists tb_test 
( 
	test_id integer (10) auto_increment primary key comment '测试id', 
	test_name varchar (50) not null comment '测试名称',
	test_password varchar(20) not null default '123456' comment '测试密码'
);

常用类型

• 极小整形 tiyint 1个字节，无符号最大值 256 (2^8 -1)，正负 -128 ~ 127 (-2^7 -1 ~ 2^7 -1)
• 小整形 smallint 2个字节，无符号最大值 65535 (2^16 - 1)，正负 -32768 ~ 32767 (-2^15 - 1 ~ 2^15 - 1)
• 中整形 mediumint 3个字节，无符号最大值 16777215 (2^24 - 1)，正负 (-2^23-1 ~ 2^23-1)
• 整形 int 4个字节，无符号最大值 2^32 -1，正负 (-2^31-1 ~ 2^31-1)
• 长整形 bigint 8个字节，无符号最大值 2^64 - 1, 正负 (-2^63-1 ~ 2^63-1)
• 单精度 float 4个字节 float [(m,d)] -3.4e+38~3.4e+38( 约 )
• 双精度 double 8个字节 double [(m,d)] -1.79e+308~1.79e+308( 约 )
• 小数值 decimal m>d ? m+2 : d+2 个字节 decimal [(m,d)] 注：m 为长度， d 为小数
• 定长字符串char 最大保存255个字节，如果值没有达到给定的长度，使用空格补充
• 变长字符串varchar 最大保存255个字节，用多大长度占多大长度
• 极小文本 tinytext 最大长度255个字节(2^8-1)
• 中文本 mediumtext 最大长度 16777215 个字节(2^24-1)
• 文本 text 最大长度65535个字节(2^16-1)
• 长文本 longtext 最大长度4294967295个字节 (2^32-1)
• 日期 date 日期(yyyy-mm-dd)
• 时间 time 时间(hh:mm:ss)
• 日期时间 datetime 日期与时间组合(yyyy-mm-dd hh:mm:ss)
• 时间戳 timestamp yyyymmddhhmmss
• 年份 year 年份yyyy

-- 创建表的操作
create table if not exists tb_user
( 
	user_id int(11) auto_increment primary key comment '用户id', 
	user_name varchar (30) not null comment '用户名称',
	user_birthday date comment '用户生日',
	user_gender char(3) comment '用户性别',
	user_status tinyint(1) not null comment '用户状态',
	user_height decimal(4,1) not null comment '用户身高',
    user_desc text comment '用户简介'
);

表字段索引

• 主键索引：alter table table_name add primary key (column)，用于唯一标识一条记录
• 唯一索引：alter table table_name add unique (column) 往往不是为了提高访问速度，而是为了避免数据出现重复
• 普通索引：alter table table_name add index index_name (column)，唯一任务是加快对数据的访问速度
• 全文索引：alter table table_name add fulltext index_name (column1, column2) ，仅可用于 myisam 表，针对较大的数据，生成全文索引很耗时好空间
• 联合索引：alter table table_name add index index_name (column1, column2, column3) ，为了更多的提高mysql效率

# 删除主键索引
alter table `table_name` drop primary key

# 删除唯一索引
alter table `table_name` drop index unique_index_name;
alter table `table_name` drop index cloumn;

# 删除普通索引
alter table `table_name` drop index index_name;

# 删除全文索引
alter table `table_name` drop index fulltext_index_name;
alter table `table_name` drop index cloumn;

修改表

字段添加

# alter table tb_name add 字段 字段类型 非空约束 默认值 注释
alter table tb_name add address varchar ( 100 ) not null default comment '用户地址';

字段类型修改

# alter table tb_name modify 字段 新的字段类型 非空约束 默认值 注释
alter table tb_name modify address varchar ( 50 ) not null default comment '用户地址';

字段名称类型修改

# alter table tb_name modify 旧的字段 新的字段 新的字段类型 非空约束 默认值 注释
alter table tb_name change address addr varchar ( 50 ) not null default comment '用户地址';

字段类型查询

desc tb_name;

字段删除

# alter table tb_name drop 字段
alter table tb_name drop addr;

表名修改

# alter table 旧表名 rename to 新表名
alter table tb_name rename to tb_name1

表引擎修改

# alter table tb_name engine = 新引擎
alter table tb_name engine = myisam;

删除表

# drop table 表名
drop table tb_name;
# 如果表存在就删除
drop table if exists tb_name;

查询表

# 查询所有表
show tables;
# 查询建表时的脚本
show create table tb_name;

mysql dml 操作

新增数据

# insert into 表名 (字段名：字段1,字段2,...字段n) values (值1,值2,...值n);

# 全表插入
insert into `tb_user`(`user_name`, `user_birthday`, `user_gender`, `user_status`, `user_height`, `user_desc`) values ('曾小贤', '2020-11-22', '男', 1, 174.5, '好男人就是我，我就是好男人曾小贤');

# 指定列插入，前提是其他列没有非空的约束
insert into `tb_user`(`user_name`, `user_birthday`, `user_gender`, `user_status`, `user_height`) values ('胡小梅', '2020-11-22', '女', 1, 174.5);

修改数据

# update 表名 set 字段1=新值1,字段2=新值2,...字段n=新值n where 条件
update `tb_user` set user_birthday='1995-10-20' where user_id=2;
update `tb_user` set user_birthday='1995-10-20', user_status = 2 where user_id=2;

update `tb_user` set user_status = 1 where user_id > 1;

update `tb_user` set user_status = 1;

删除数据

# delete from 表名 where 条件
delete from `tb_user` where user_id=2;

查询数据

# select 字段1,字段2,...字段n from 表名 where 条件

# 不带条件查询
select * from tb_user;
select user_id,user_name from tb_user;

# 带条件查询 (比较运算 >, <, >=, <=, !=, <>, =)
select * from tb_user where user_id > 1;

# 带逻辑条件查询 (and,or)
select * from tb_user where user_status = 1 and user_id > 1;
select * from tb_user where user_id = 1 or user_name = '胡小梅';

# 模糊查询 (like %%)
select * from tb_user where user_name like '曾%';
select * from tb_user where user_name like '%闲';
select * from tb_user where user_name like '%小%';

# 范围查询
select * from tb_user where tb_status in (0,1,2);

# 聚合函数
-- count(field)
select count(user_id) 用户数量 from tb_user;
-- sum(field)
select sum(user_height) 总身高 from tb_user;
-- avg(field)
select avg(user_height) 平均身高 from tb_user;

...

# 分组查询
-- group by 统计男女的平均身高: group by 查询中出现的字段必须是 group by 后面的字段
select user_gender as 性别,avg(user_height) 平均身高 from tb_user group by user_gender;

select user_status,user_gender as 性别,avg(user_height) 平均身高 from tb_user group by user_gender,user_status;

select user_gender as 性别,avg(user_height) 平均身高,sum(user_height),count(user_id) 用户数量 from tb_user group by user_gender;

# 排序查询
-- order by 默认是 asc 升序, desc 降序; order by 是放在 group by 之后的
select * from tb_user order by user_id asc;

select * from tb_user order by user_id desc;

select * from tb_user where user_id < 10 order by user_id desc;

select * from tb_user where user_id < 10 order by user_id,user_status desc;

select user_gender as 性别,avg(user_height) 平均身高,sum(user_height),count(user_id) 用户数量 from tb_user group by user_gender order by 用户数量;

# 创建分数表
create table `tb_score` (
  `id` int(11) not null primary key auto_increment,
  `stu_id` int(11) not null,
  `cou_id` int(11) not null,
  `score` decimal(4,1) not null
);

-- 插入测试数据 
insert into tb_score (`stu_id`, `cou_id`, `score`) values(1,1,89.0);
insert into tb_score (`stu_id`, `cou_id`, `score`) values(1,2,78.0);
insert into tb_score (`stu_id`, `cou_id`, `score`) values(1,3,94.0);
insert into tb_score (`stu_id`, `cou_id`, `score`) values(1,4,77.0);
insert into tb_score (`stu_id`, `cou_id`, `score`) values(1,5,99.0);
insert into tb_score (`stu_id`, `cou_id`, `score`) values(3,1,90.0);
insert into tb_score (`stu_id`, `cou_id`, `score`) values(3,2,88.0);
insert into tb_score (`stu_id`, `cou_id`, `score`) values(3,3,69.0);
insert into tb_score (`stu_id`, `cou_id`, `score`) values(3,4,83.0);
insert into tb_score (`stu_id`, `cou_id`, `score`) values(3,5,92.0);
insert into tb_score (`stu_id`, `cou_id`, `score`) values(2,1,77.0);
insert into tb_score (`stu_id`, `cou_id`, `score`) values(2,2,84.0);
insert into tb_score (`stu_id`, `cou_id`, `score`) values(2,3,91.0);
insert into tb_score (`stu_id`, `cou_id`, `score`) values(2,4,80.0);
insert into tb_score (`stu_id`, `cou_id`, `score`) values(2,5,99.0);

# 分页查询
-- 查询科目id为1的最高成绩
select max(score) from tb_score where course_id = 1;
select * from tb_score where course_id = 1 limit 1;
-- 查询课程id为4的前五名成绩信息
select * from tb_score where course_id = 4 order by score limit 5;
-- limit 分页, 起始值是 0： (pageindex - 1) * pagesize, pagesize
select * from tb_score limit 0,10
select * from tb_score limit 10,10
select * from tb_score limit 20,10

到此这篇关于mysql8数据库安装及sql语句详解的文章就介绍到这了。希望对大家的学习有所帮助，也希望大家多多支持。