mysql video tutorial column explains the MySQL8 database installation tutorial in detail.
Free recommendation: mysql video tutorial
1. Windows environment Install
A. Download MySQL
Select Operating System:Microsoft Windows
Quick download: mysql-8.0.22-winx64.zip
B. Unzip and configure the MySQL environment variables
MYSQL_HOME: C:\MySQL\mysql-8.0.22-winx64
C. Create the my.ini
configuration file in the root directory of the decompression
[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. Install MySQL (The following operations must be performed as an administrator)
- Initialize MySQL
mysqld --defaults-file=C:\MySQL\mysql-8.0.22-winx64\my.ini --initialize --console
Note: Copy and save the MySQL initialization passwordfVdpg:bM9pAk
- Install MySQL service
mysqld --install mysql8
- Start MySQL service
net start mysql8
E, log in, change password
- Login to MySQL
mysql -u账号 -p密码
Solution for failing to log in using the above method
1. Stop mysql8
net stop mysql8
2. Passwordless startup
mysqld --console --skip-grant-tables --shared-memory
3. The previous window cannot be closed, and then open a new window. Login without password
mysql -u root -p
4. Clear password
update mysql.user set authentication_string='' where user='root' and host='localhost;'
5. Refresh privilege
plush privilege;
6. Restart the mysql service, and then log in to mysql without password
- Use MySQL to change the password after logging in
ALTER USER root@localhost IDENTIFIED BY '123456';
- Enable remote access
CREATE USER 'root' @'%' IDENTIFIED BY '123456'; -- 这一步执行失败也没关系 GRANT ALL ON *.* TO 'root' @'%'; # alter user 'root'@'%' identified with mysql_native_password by '123456'; FLUSH privilege;
2. Installation in Linux environment
A. Download MySQL
Select Operating System:Source Code
Select OS Version:Generic Linux (Architecture Independent)
Quick download: mysql-8.0.22.tar.gz
B. Upload the downloaded MySQL compressed package to the Linux server
C. Unzip mysql-8.0.22 .tar.gz
tar -zxvf mysql-8.0.22.tar.gz
D. Move the decompressed file to the /usr/local directory
mv mysql-8.0.22 /usr/local/mysql
E. Add the MySQL combined user (it will be added by default, if not added, Manually add)
groupadd mysql useradd -r -g mysql mysql
F. Enter the /usr/local/mysql
directory and modify the relevant permissions
cd /usr/local/mysql chown -R mysql:mysql ./
G. MySQL initialization operation and record the temporary password
cd /usr/local/mysql/bin ./mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
Note: Copy and save the MySQL initialization passwordfVdpg:bM9pAk
H, create the MySQL configuration file/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. Start the MySQL service
cd /usr/local/mysql/support-files ./mysql.server start
J. Log in to MySQL through the temporary password and change the password
cd /usr/local/mysql/bin ./mysql -u root -p生成的临时密码 ALTER USER 'root' @'localhost' IDENTIFIED BY '123456';
K, enable remote access
CREATE USER 'root' @'%' IDENTIFIED BY '123456'; -- 这一步执行失败也没关系 GRANT ALL ON *.* TO 'root' @'%'; FLUSH privilege;
MySQL database operation
Database operation
Create database
CREATE DATABASE db_name DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
Query database
-- 查询所有数据库 SHOW DATABASES; -- 查询数据库建表时的sql脚本 SHOW CREATE DATABASE db_name;
Delete database
DROP DATABASE db_name;
Modify database
-- 修改数据库的字符编码和排序方式 ALTER DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Select database
USE db_name;
Set the encoding format of the operation
SET NAMES utf8;
Table operation
Create table
CREATE TABLE tb_name (field to create table , type, length, constraint, default, comment)
Constraint
- Non-null
NOT NULL
- Non-negative
UNSIGNED
- Primary key
PRIMARY KEY
- Auto-increment
AUTO_INCREMENT
- Default
DEFAULT
- Comments
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 '测试密码' );
Common types
- 极小整形
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 <pre class="brush:php;toolbar:false"># 创建分数表 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
The above is the detailed content of MySQL8 database installation tutorial. For more information, please follow other related articles on the PHP Chinese website!

MySQLdiffersfromotherSQLdialectsinsyntaxforLIMIT,auto-increment,stringcomparison,subqueries,andperformanceanalysis.1)MySQLusesLIMIT,whileSQLServerusesTOPandOracleusesROWNUM.2)MySQL'sAUTO_INCREMENTcontrastswithPostgreSQL'sSERIALandOracle'ssequenceandt

MySQL partitioning improves performance and simplifies maintenance. 1) Divide large tables into small pieces by specific criteria (such as date ranges), 2) physically divide data into independent files, 3) MySQL can focus on related partitions when querying, 4) Query optimizer can skip unrelated partitions, 5) Choosing the right partition strategy and maintaining it regularly is key.

How to grant and revoke permissions in MySQL? 1. Use the GRANT statement to grant permissions, such as GRANTALLPRIVILEGESONdatabase_name.TO'username'@'host'; 2. Use the REVOKE statement to revoke permissions, such as REVOKEALLPRIVILEGESONdatabase_name.FROM'username'@'host' to ensure timely communication of permission changes.

InnoDB is suitable for applications that require transaction support and high concurrency, while MyISAM is suitable for applications that require more reads and less writes. 1.InnoDB supports transaction and bank-level locks, suitable for e-commerce and banking systems. 2.MyISAM provides fast read and indexing, suitable for blogging and content management systems.

There are four main JOIN types in MySQL: INNERJOIN, LEFTJOIN, RIGHTJOIN and FULLOUTERJOIN. 1.INNERJOIN returns all rows in the two tables that meet the JOIN conditions. 2.LEFTJOIN returns all rows in the left table, even if there are no matching rows in the right table. 3. RIGHTJOIN is contrary to LEFTJOIN and returns all rows in the right table. 4.FULLOUTERJOIN returns all rows in the two tables that meet or do not meet JOIN conditions.

MySQLoffersvariousstorageengines,eachsuitedfordifferentusecases:1)InnoDBisidealforapplicationsneedingACIDcomplianceandhighconcurrency,supportingtransactionsandforeignkeys.2)MyISAMisbestforread-heavyworkloads,lackingtransactionsupport.3)Memoryengineis

Common security vulnerabilities in MySQL include SQL injection, weak passwords, improper permission configuration, and unupdated software. 1. SQL injection can be prevented by using preprocessing statements. 2. Weak passwords can be avoided by forcibly using strong password strategies. 3. Improper permission configuration can be resolved through regular review and adjustment of user permissions. 4. Unupdated software can be patched by regularly checking and updating the MySQL version.

Identifying slow queries in MySQL can be achieved by enabling slow query logs and setting thresholds. 1. Enable slow query logs and set thresholds. 2. View and analyze slow query log files, and use tools such as mysqldumpslow or pt-query-digest for in-depth analysis. 3. Optimizing slow queries can be achieved through index optimization, query rewriting and avoiding the use of SELECT*.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

mPDF
mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

ZendStudio 13.5.1 Mac
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

MantisBT
Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

SecLists
SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.
