mysql知识点

最新推荐文章于 2024-08-28 12:19:08 发布

@沉住气

最新推荐文章于 2024-08-28 12:19:08 发布

阅读量153

点赞数

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/m0_51044974/article/details/129585028

版权

文章详细介绍了MySQL5.6和8.0的安装步骤，包括配置文件设置、服务安装与启动。接着讲解了数据库的基本操作，如创建、删除数据库，以及时间格式处理。还涵盖了CRUD操作、聚合函数和表级约束，最后提到了视图、存储过程和函数的使用。

摘要由CSDN通过智能技术生成

Mysql安装

mysql 5.6.xx安装

下载mysql 官网 MySQL

进入官网后点击 downloads

在该页面中找到 MySQL Community (GPL) Downloads

点击mysql community server

点击Archives历史版本

选择自己需要下载的版本即可

现在完成后解压到指定目录即可

在bin 同级下创建一个my.ini文件，一下是内容

[mysqld]
#设置时区为东八区，此项设置后，在连接MySQL的时候可以不用每次都手动设置时区
#select @@global.time_zone,@@session.time_zone
default-time-zone = '+08:00'
#default-time_zone = '+8:00'
# 允许执行load data local infile 'c:/wx.txt';
local-infile=1
# 允许select * from tablename into outfile 'c:/d.txt'
secure_file_priv=''
# 设置3306端口
port=3306
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用mysql_native_password caching_sha2_password”插件认证
default_authentication_plugin=mysql_native_password
# 解决导入脚本时funtion报错
log_bin_trust_function_creators=1
# 格式化日期时，显示中文的日期 星期五
lc_time_names=zh_CN
#lc_messages_dir=./share/english
#lc_messages=en_US
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
# 允许执行load data local infile 'c:/wx.txt'
local-infile=1
[client]
# 设置mysql客户端连接服务端时默认使用的端口 mysql\bin>mysql 直接登录
# mysql>set password = ''; 取消密码
# mysql -hlocalhost -uroot -P3306
port=3306
user=root
password=
default-character-set=utf8

然后进入解压后的bin目录 --- 进入cmd窗口

# 安装服务 m5 是服务的名称
mysqld install mysql5.6  
# 启动服务 
net start mysql5.6
# 登录 
mysql -uroot
# 连接mysql后修改密码为 root
set password = 'root';

# 到这里mysql5.6.xx就安装成功了

执行完后可以在服务中看到服务名称就是上面install的时候后面指定的名称

mysql 8.0.xx安装

mysql 8.0.xx 的安装就和5.6.xx的差不太多

解压后进入bin目录 --- 接下来进入cmd窗口

5.6.xx的都是直接 mysqld --install

8.0.xx的需要进行初始化

步骤就是以管理员的身份运行cmd窗口进入到 bin 目录下

执行：

# 初始化数据库位置
mysqld --initialize-insecure
# 注册服务
mysqld --install mysql8.0
# 启动数据库
net start mysql8.0
# 连接数据库
mysql
# 修改本机数据库密码
set password = 'root';

数据库

库操作

-- 显示所有数据库
show databases;

#数据库操作
CREATE DATABASE dddd;
USE dddd;

DROP DATABASE dddd;

#查看当前数据库，当前角色，当前数据库版本，当前时间
SELECT DATABASE(),USER(),VERSION(),NOW();

#查看当前数据库的创建语句
SHOW CREATE DATABASE newbee_mall_db;
SHOW CREATE DATABASE mysql;

#创建一个beiyou2023数据库
CREATE DATABASE beiyou2023;
#使用beiyou2023数据库
USE beiyou2023;
#查看当前使用的数据库
SELECT DATABASE();

# exists 表示创建数据库或者表的时候存在的时候再次创建相同
# 的数据库或者表名时不报错，如果存在就不再执行后面的语句。
# 一般都是 if not exixts 一起出现的。

# 创建数据库的时候设置一个字符编码
# 关键字 ：character set \ charset \ default character  后面都是加上字符编码。
#create database if not exists 'exists' character set utf8;
#create database if not exists 'exists' charset utf8;
#create database if not exists 'exists' default character set gbk;

# 删除数据库 关键字：drop

#drop database dddd;
#drop database if exists 'exists';

#查看mysql支持的所有字符集信息
SHOW CHARSET;
SHOW CHARSET LIKE 'utf%';
SHOW CHARACTER SET;
# 查看当前数据库开发环境的相关字符集信息
SHOW VARIABLES LIKE '%dir%';
SHOW VARIABLES LIKE '%char%';

# 新版数据库和旧版数据库user表的区别

# mysql 5.6及以下版本使用password作为令牌	PASSWORD 和 authentication_string字段的替换
SELECT HOST,USER,PASSWORD,PLUGIN FROM mysql.`user`;
#
SELECT HOST,USER,authentication_string,PLUGIN FROM mysql.`user`;

# 表的约束的两种方式
#方式一
DROP TABLE tt1;

CREATE TABLE IF NOT EXISTS tt1(
	id INT PRIMARY KEY
	);
#方拾二
CREATE TABLE tt2(
	id INT NOT NULL,
	PRIMARY KEY(id)
);

SELECT UUID_SHORT(),UUID();
# 
INSERT INTO tt1(id) VALUES(1);

SELECT * FROM tt1;

SHOW TABLES;

CREATE TABLE IF NOT EXISTS t_user(
	t_id INT(3) AUTO_INCREMENT NOT NULL COMMENT '学生id',
	t_name VARCHAR(30) NOT NULL DEFAULT '' COMMENT '学生姓名',
	t_birthday DATETIME NOT NULL DEFAULT NOW() COMMENT '生日',
	PRIMARY KEY(t_id)
);

SELECT DATABASE();

数据库的时间格式


#数据库中的时间格式

USE beiyou2023;

CREATE TABLE IF NOT EXISTS t_time(
	id CHAR(36) COMMENT '',
	t_name VARCHAR(10) NOT NULL COMMENT '',
	birth DATE COMMENT '',
	onlinetime DATETIME DEFAULT NULL COMMENT '',
	creat_time DATETIME DEFAULT NULL COMMENT '',
	update_time DATETIME DEFAULT NULL COMMENT '',
	PRIMARY KEY(id)
)ENGINE = INNODB CHARSET = utf8 COMMENT '';

# 查看建表语句
SHOW CREATE TABLE t_time;
# 建表语句
CREATE TABLE `t_time` (
  `id` CHAR(36) NOT NULL ,
  `t_name` VARCHAR(10) NOT NULL,
  `birth` DATE DEFAULT NULL,
  `onlinetime` DATETIME DEFAULT NULL,
  `creat_time` DATETIME DEFAULT NULL,
  `update_time` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

# 插入一些数据

INSERT INTO t_time(id,t_name) VALUES (UUID(),'Tom');
INSERT INTO t_time(id,t_name) VALUES (UUID(),'Jack');
INSERT INTO t_time(id,t_name) VALUES (UUID(),'Anay');
INSERT INTO t_time(id,t_name) VALUES (UUID(),'Mary');
INSERT INTO t_time(id,t_name) VALUES (UUID(),'Lunch');
INSERT INTO t_time(id,t_name) VALUES (UUID(),'Math');
SELECT * FROM t_time;

# 修改当前时间
UPDATE t_time 
SET birth = NOW() 
WHERE t_name = 'Tom';

SELECT * FROM t_time;




SELECT UUID() AS uid,LENGTH(UUID()) AS len;

CREATE TABLE IF EXISTS t_name(
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	age SET('高等数学','大学英语','线性代数','离散数学'),
	gender ENUM('男','女','保密')
);

CRUD


# 创建数据库
CREATE DATABASE beiyou2023;

# 使用数据库
USE beiyou2023;

# 创建表
CREATE TABLE t_user(
	id INT(4) AUTO_INCREMENT NOT NULL COMMENT '用户id',
	t_name VARCHAR(12),
	PRIMARY KEY(id)
)ENGINE = INNODB CHARSET = utf8;

# unsigned 无符号（int 类型的数据没有负数）
# zerofill 填充0 ，表示在字段的数据类型中需要填写的宽度，如果不够就在前面补0
CREATE TABLE IF NOT EXISTS t_text(
	id INT(4) UNSIGNED ZEROFILL 
);

SHOW TABLES;


#创建一个用户表，使用enum 和 set 关键字
CREATE TABLE USER(
id INT UNSIGNED AUTO_INCREMENT,
NAME VARCHAR(30),
gender ENUM('男','女','保密') DEFAULT '男', /*gender字段数据只能填写 男 女 保密 
其中一个*/
depart SET('高等数学','英语','线性代数','离散数学'),/* 可以不填写，填写必须是集合
中的元素，可是任意个 */
/* '英语,英语,英语' */
PRIMARY KEY(id)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

# 显示当前数据库中的所有表
SHOW TABLES;

# 删除user 表
DROP TABLE USER;

# 如果存在user 表就删除，如果不存在就不删除
DROP TABLE IF EXISTS USER;

# --------------改变表-----------------
SHOW TABLES;

DESC t_text;
# 修改表名
ALTER TABLE t_text RENAME TO t_update;
DESC t_update;

# 添加字段
# after 关键字可以将需要添加的字段指定的插入到摸个字段的后面。
# first 关键字可以将添加的字段放在该表中的第一个位置
ALTER TABLE t_update ADD COLUMN id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY;
ALTER TABLE t_update ADD sname VARCHAR(15) NOT NULL COMMENT '姓名';
ALTER TABLE t_update ADD brith DATETIME DEFAULT (NOW()) COMMENT '出生日期';
ALTER TABLE t_update ADD gender ENUM('男','女') DEFAULT '男' AFTER sname;
ALTER TABLE t_update ADD address VARCHAR(15) FIRST;
ALTER TABLE t_update ADD save VARCHAR(16) DEFAULT '保存' AFTER brith; 

# 修改列的类型 和 位置
# modify 可以修改字段的类型和位置
ALTER TABLE t_update MODIFY address CHAR(5) AFTER save;
ALTER TABLE t_update RENAME address TO addr;

DESC t_update; 

SELECT * FROM t_update;



# -------------CRUD----------------
SHOW TABLES;	# t_update
DESC t_update;
-- 添加语句 insert into 
INSERT INTO t_update(id,sname,gender,brith,save,address,tid) 
VALUES(1,'Tom','男',NOW(),'yes','郑州',00000001);

INSERT INTO t_update(id,sname,gender,brith,save,address,tid) 
VALUES(2,'Tom1','女',NOW(),'no','郑州',00000002);

INSERT INTO t_update(id,sname,gender,brith,save,address,tid) 
VALUES(3,'Tom2','男',NOW(),'yes','郑州',00000003);

INSERT INTO t_update(id,sname,gender,brith,save,address,tid) 
VALUES(4,'Tom3','男',NOW(),'yes','郑州',00000004);

INSERT INTO t_update(id,sname,gender,brith,save,address,tid) 
VALUES(5,'Tom3','男',NOW(),'yes','郑州',00000005);

INSERT INTO t_update(id,sname,gender,brith,save,address,tid) 
VALUES(6,'1234','男',NOW(),'yes','郑州',00000006);


SELECT * FROM t_update WHERE sname REGEXP '[0-9]+$';

# 列别名，表别名
SELECT id uid,sname setName,gender gen
FROM t_update updated
WHERE updated.save = 'no';

# 计算列的行数
SELECT COUNT(*) FROM t_update;

# 查询条件
SELECT * FROM t_update WHERE gender = '女';

# 分组
SELECT * FROM t_update GROUP BY save HAVING address = '郑州';

SELECT * FROM t_update LIMIT 4,2;


/*
FROM子句-> ON ->(LEFT/RIGHT) JOIN->WHERE子句->GROUP BY子句->HAVING子句->
SELECT子句->DISTINCT->ORDER BY子句->LIMIT子句->最终结果 
*/
# rand() 排序号
# -------------聚合函数--------------------
/*	
	参数一：字符串 
	参数二：分隔符
	参数三：去第几个分隔符之前的部分
*/

SELECT DATABASE();

# 创建一个学生信息表
CREATE TABLE IF NOT EXISTS ann_function(
	id INT UNSIGNED AUTO_INCREMENT COMMENT '学生ID',
	NAME VARCHAR(12) NOT NULL COMMENT '学生姓名',
	scope INT COMMENT '学生分数',
	collega VARCHAR(13) COMMENT '学院',
	PRIMARY KEY(id)
);

INSERT INTO ann_function VALUES (02233001,'张三',82,'信息工程学院');

INSERT INTO ann_function VALUES (02233002,'李四',82,'信息工程学院');

SELECT SUBSTRING_INDEX('www.aphlios2000@163.com','.',2);

# rand() 取一个[0-1]的随机数，round()四舍五入，floor()向下取整，ceil()向上取整
SELECT RAND(),ROUND(1.7),ROUND(1.5),ROUND(1.4),FLOOR(1.1),FLOOR(1.9),CEIL(1.1),CEIL(1.6);

SELECT REPEAT('*',ROUND(RAND()*9+1)) AS t_repeat;

聚合函数


# 聚合函数的使用
SELECT DATABASE();

# 创建一个学生信息表
CREATE TABLE IF NOT EXISTS ann_function(
	id INT UNSIGNED AUTO_INCREMENT COMMENT '学生ID',
	NAME VARCHAR(12) NOT NULL COMMENT '学生姓名',
	scope INT COMMENT '学生分数',
	collega VARCHAR(13) COMMENT '学院',
	PRIMARY KEY(id)
);

USE beiyou2023;

INSERT INTO ann_function VALUES (02233001,'张三',82,'信息工程学院');

INSERT INTO ann_function VALUES (02233007,'李四',74,'信息工程学院')
				,(02233003,'王五',92,'英语学院')
				,(02233004,'周二',42,'英语学院')
				,(02233005,'李世源',56,'会计学院')
				,(02233006,'李星云',88,'信息工程学院');
# 查询语句
SELECT * FROM ann_function;
# 分组
SELECT collega 学院,COUNT(*) 人数,AVG(scope) 最高分 FROM ann_function GROUP BY collega;

# Having 过滤(一般用聚合函数)
SELECT collega 学院,COUNT(*) 人数,AVG(scope) 最高分 
FROM ann_function
GROUP BY collega 
HAVING AVG(scope) > 58 AND COUNT(*) = 2;

# 查看当前表中的所有索引
SHOW INDEX FROM ann_function;

SELECT * FROM ann_function;




SHOW DATABASES;
SELECT DATABASE();

# 创建一个索引
ALTER TABLE ann_function ADD FULLTEXT INDEX

表级约束

# 建表语句的约束
# unique 唯一约束
# unique 唯一约束可以不写，如果写的话就，该字段表示的元素不能重复

# not null 约束也可以不写，如果写了，对应的字段就必须要填写内容

# default 默认值约束，如果该字段表示的元素没有填写，就是用默认值
	# 填写了就是用用户自己填写的内容。

# 增加语句
#单行插入：insert into 表名(字段列表) value(字段值);
#多行插入：insert into 表名(字段列表) values(字段值),(字段值),(字段值),....;
# replace 和 replace into 用法和insert 一样 。
# replace 在插入的时候如果有主键的存在，就是修改数据。
 
 # unsigned 无符号 , auto_increment 主键自增 , primary key 主键标识
 # comment 字段说明， default 字段默认值。
 CREATE TABLE IF NOT EXISTS book(
	id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY 
 );
 
 # select 语句中的关键字
 /*
SELECT ..... 
FROM .... (left / right)表名 join 表名 on 连接查询条件
WHERE ....
GROUP BY ..
HAVING ....
ORDER BY ASC(默认升序) \ DESC(降序)
LIMIT ...

其中的having 必须出现在 group by 后面

having 和 where 的区别 
where在select语句开始的时候过滤数据，
having 是在分组之后过滤数据。
where 过滤在前 , having 过滤在后。 
 */
 
 # 查看表存储引擎
 SHOW ENGINES;
 
 
 # unsigned 无符号的

视图、存储过程、函数

SHOW DATABASES;

USE beiyou2023;
-- 查询
SELECT * FROM t_user;
DESC t_user;

CREATE TABLE IF NOT EXISTS teacher(
    t_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY ,
    t_name VARCHAR(12) NOT NULL UNIQUE,
    t_age TINYINT NOT NULL DEFAULT 18
);

SHOW TABLES;

CREATE TABLE IF NOT EXISTS student (
    s_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY ,
    s_name VARCHAR(12) NOT NULL UNIQUE,
    s_age TINYINT NOT NULL DEFAULT 18,
    t_id INT UNSIGNED,
    /*
    设置外键
        constraint  关键字
        t_idASs_id  外键名称
        foreign key() 需要创建外键的字段
    */
    CONSTRAINT fk_cjcl FOREIGN KEY(t_id) REFERENCES teacher(t_id)
);

DESC teacher;

INSERT INTO teacher(t_name, t_age) VALUES ('Tom',23),('Jack',19),('Mary',32),('Role',21),('Aphlios',23);

SELECT * FROM teacher;

DESC student;

INSERT INTO student(s_name, s_age, t_id) VALUES
                                                ('lisi',11,1),
                                                ('zhangsan',12,2),
                                                ('wangwu',13,3),
                                                ('xiaore',14,4);
SELECT * FROM student;

SELECT s.s_name ,s.s_age ,t.t_name,t.t_age FROM student s RIGHT JOIN teacher t ON s.t_id = t.t_id;

-- 创建视图
CREATE VIEW vo_tea AS
    SELECT s.s_name,s.s_age,t.t_name,t.t_age FROM student s RIGHT JOIN teacher t ON s.t_id = t.t_id;
-- 查看视图

SELECT `TABLE_NAME`
FROM `INFORMATION_SCHEMA`.`TABLES`
WHERE `TABLE_SCHEMA` = 'beiyou2023' AND `TABLE_TYPE` = 'vo_%';

SHOW FULL TABLES FROM `beiyou2023` WHERE table_type = 'BASE TABLE';




SHOW DATABASES;

SHOW TABLES;

-- 创建存储过程
DELIMITER @
CREATE PROCEDURE p_stuadd()
BEGIN
    SELECT * FROM t_user;
END @
DELIMITER ;

-- 调用存储过程
CALL p_stuadd();
SELECT USER(),DATABASE();

DELIMITER $$
CREATE PROCEDURE p_count(s INT)
BEGIN
    SELECT s*s;

END $$
DELIMITER ;
-- 调用存储过程
CALL p_count(12);
# 查看当前数据库所有的存储过程
SHOW PROCEDURE STATUS WHERE db = DATABASE();

-- 创建函数
DELIMITER &
CREATE FUNCTION f_count(i INT UNSIGNED)
RETURNS INT UNSIGNED
BEGIN
    RETURN i*i;
END &
DELIMITER ;
-- 调用函数
SELECT f_count(3);

-- 定义一个有返回值的字符串
DELIMITER @
CREATE FUNCTION f_number()
RETURNS VARCHAR(50)
BEGIN
    DECLARE t VARCHAR(50);
    SET t = '你好';
    RETURN t;
END @
DELIMITER ;

-- 一个成绩输出的函数
DELIMITER $
CREATE FUNCTION f_level(t INT UNSIGNED)
RETURNS VARCHAR(50)
BEGIN
    DECLARE s VARCHAR(50) DEFAULT '';
    IF t>90 THEN
        SET s ='优秀';
    ELSEIF t>80 THEN
        SET s ='良好';
    ELSEIF t>70 THEN
        SET s ='还不错';
    ELSEIF t>60 THEN
        SET s ='及格';
    ELSE
        SET s = '补考';
        /* end if 表示使用 判断语句结束 */
    END IF;
        RETURN s;
END $
DELIMITER ;
-- 调用
SELECT f_level(75);


-- 将名字输出 带* 的格式 ，手机号也是输出 带星的格式
DELIMITER @@
CREATE FUNCTION f_fmask(str VARCHAR(50))
RETURNS VARCHAR(50)
BEGIN
    DECLARE s VARCHAR(50) DEFAULT '';
    IF str REGEXP '^[0-9]{11}$' THEN
        SET s = INSERT(str,4,4,'****');
    ELSEIF LENGTH(str) = 2 AND LENGTH(str) >= 3 THEN
        SET s = INSERT(str,2,1,'*');
    END IF;
    RETURN s;
END @@
DELIMITER ;
-- 删除指定名称的函数
DROP FUNCTION IF EXISTS f_fmask;
-- 调用指定函数
SELECT f_fmask('12345678907'),f_fmask('张三丰');
-- 调用函数
SELECT f_number();


-- 查看消息历史
DELIMITER !!
CREATE FUNCTION f_dataTime(t DATETIME)
RETURNS VARCHAR(50)
BEGIN
    DECLARE d1 DATETIME DEFAULT '';
    DECLARE d2 DATETIME DEFAULT '';
    DECLARE str VARCHAR(50) DEFAULT '';
    SET d1 = UNIX_TIMESTAMP();
    SET d2 = UNIX_TIMESTAMP(t);
    IF d1 - d2 < 60 THEN
        SET str ='刚刚';
    ELSEIF d1 - d2 < 3600 THEN
        SET str = CONCAT(ROUND((d1-d2) / 60),'分钟前');
    ELSEIF d1-d2<(3600*24) THEN
        SET str = CONCAT(ROUND((d1-d2) / 3600),'小时前');
    ELSE
        SET str = FROM_UNIXTIME(d2);
    END IF;
    RETURN str;
END !!
DELIMITER ;

-- 查看当前数据库中的所有函数
SHOW FUNCTION STATUS WHERE db = DATABASE();
-- 查看当前数据库中的所有存储过程
SHOW PROCEDURE STATUS WHERE db = DATABASE();

SHOW TABLES;
--  触发器可以有 before/after   insert / update / delete
DELIMITER @
CREATE TRIGGER t1 AFTER INSERT ON student FOR EACH ROW
    BEGIN
        IF NEW.s_id > 0 THEN
            INSERT INTO t_user VALUE (NEW.s_id,NEW.s_name);
        END IF;
    END @
DELIMITER ;

-- 显示所有的触发器
SHOW TRIGGERS ;
-- 根据触发器名称删除指定触发器
DROP TRIGGER t1;

SELECT * FROM student;
INSERT INTO student(s_name, s_age, t_id) VALUES ('陈++',13,1);
DESC student;

SELECT * FROM t_user;
DESC t_user;