MySQL实践

2023-01-19
Author 孙新斌
~6.38K words

基础知识

MySQL是一个开源的关系型数据库管理系统（RDBMS），它广泛用于Web应用程序的后台数据存储。

MySQL实操

查看初始密码

通过管理员命令进入.\MySQL\MySQL Server 8.0\bin文件夹下，使用命令查看密码：

1	mysqld --initialize --console

运行服务器：

1	mysqld --console

重置密码：

1	ALTER USER 'your_user'@'localhost' IDENTIFIED BY 'your_new_password';

创建用户：

1	CREATE USER 'your_user'@'localhost' IDENTIFIED BY 'yourpassword';

给予该用户全部权限：

1	GRANT ALL PRIVILEGES ON . TO 'your_user'@'localhost' WITH GRANT OPTION;

使用VSCode中的插件进行连接登陆服务器

SQL常用语句

# 创建数据库
CREATE DATABASE test;
# 创建和管理表
USE test;
CREATE TABLE T1 (
 ID INT1, 
 NAME VARCHAR(100),
 LEVEL INT,
 EXP INT,
 GOLD DECIMAL(10,2)
)
DESC t1;
ALTER TABLE t1 MODIFY COLUMN name VARBINARY(200);
DESC t1;
ALTER TABLE t1 RENAME COLUMN name TO nick_name;
DESC t1;
ALTER TABLE t1 ADD COLUMN last_login DATETIME;
ALTER TABLE t1 DROP COLUMN last_login;
DROP TABLE t1;
# 数据的增删改查
INSERT into t1 (id,name) VALUES (1,'zhangsan');
SELECT * from t1;
INSERT into t1 (id,name) VALUES (2,'lisi'),(3,'wangwu');
ALTER TABLE t1 MODIFY LEVEL int DEFAULT 1;
INSERT INTO t1 (id,name) VALUES (4,'zhaoliu');
SELECT * FROM t1;
UPDATE t1 SET level = 1 WHERE name = 'wangwu';
UPDATE t1 SET level = 1 ;
SELECT * FROM t1;
UPDATE t1 SET exp=0,gold=0;
SELECT * FROM t1;
DELETE FROM t1 WHERE gold = 0;

数据的导入方法

安装好MySQL之后，进入MySQL的命令行界面并按照下面的步骤来创建一个数据库：

打开终端，输入如下命令来登录MySQL：

1	mysql -u your_username -p

将your_username替换为你的MySQL用户名，然后会提示你输入MySQL密码。

登录成功后，你应该会看到MySQL的提示符：

mysql>

输入如下命令来创建一个名为game的数据库：

1	CREATE DATABASE game;

你可以通过如下命令来确认数据库是否创建成功：

1	SHOW DATABASES;

退出MySQL的命令行界面：

exit;

在终端中执行如下命令来导入数据：

1	mysql -u root -p game < game.sql

常用语句

USE test;
SELECT * FROM player WHERE level > 1; 
SELECT * FROM player WHERE level > 1 AND level < 5; 
SELECT * FROM player WHERE exp > 1 AND exp < 5; 
SELECT * FROM player WHERE level > 1 AND level < 5 OR exp > 1 AND exp < 5; 

SELECT * FROM player WHERE level > 1 AND (level < 5 OR exp > 1) AND exp < 5;
SELECT * FROM player WHERE level IN (1,3,5);
SELECT * FROM player WHERE level BETWEEN 1 AND 10;
SELECT * FROM player WHERE level>=1 AND level<=10;
SELECT * FROM player WHERE level NOT BETWEEN 1 AND 10;
SELECT * FROM player WHERE name LIKE '王%';
SELECT * FROM player WHERE name LIKE '%王%';
SELECT * FROM player WHERE name LIKE '王_';
SELECT * FROM player WHERE name LIKE '王__';
SELECT * FROM player WHERE name REGEXP '王.$';#正则表达式
SELECT * FROM player WHERE name REGEXP '[王张]';
SELECT * FROM player WHERE name REGEXP '王|张';
# 空值
SELECT * FROM player WHERE email is NULL;
SELECT * FROM player WHERE email is NULL or email = '';
SELECT * FROM player ORDER BY level DESC;
SELECT * FROM player ORDER BY level DESC, exp ASC;
SELECT * FROM player ORDER BY 5 DESC;
# 聚合函数 AVG COUNT MAX MIN SUM
SELECT COUNT(*) FROM player;
SELECT AVG(level) FROM player;
SELECT sex,count(*) FROM player GROUP BY sex;
SELECT level,count(level) FROM player GROUP BY level;
SELECT level,count(level) FROM player GROUP BY level HAVING COUNT(level)>4;
SELECT level,count(level) FROM player GROUP BY level HAVING COUNT(level)>4 ORDER BY COUNT(level) DESC;
SELECT SUBSTR(name,1,1),count(substr(name,1,1)) FROM player 
GROUP BY SUBSTR(name,1,1) 
HAVING COUNT(SUBSTR(name,1,1))>=5
ORDER BY COUNT(SUBSTR(name,1,1)) DESC
LIMIT 3,3;
SELECT SUBSTR(name,1,1),count(substr(name,1,1)) FROM player 
GROUP BY SUBSTR(name,1,1) 
ORDER BY COUNT(SUBSTR(name,1,1)) DESC
LIMIT 3,3;
SELECT DISTINCT sex FROM player;
SELECT * FROM player WHERE level BETWEEN 1 AND 3
UNION # ALL
SELECT * FROM player WHERE exp BETWEEN 1 AND 3;
SELECT * FROM player WHERE level BETWEEN 1 AND 3
EXCEPT
SELECT * FROM player WHERE exp BETWEEN 1 AND 3;

子查询

SELECT AVG(level) FROM player;
SELECT * FROM player WHERE level > (SELECT AVG(level) FROM player);
SELECT level,round((SELECT AVG(level) FROM player)) as average,
level - ROUND((SELECT AVG(level) FROM player)) as diff
FROM player;
CREATE TABLE new_player AS
SELECT * FROM player WHERE level < 5;
select * from new_player;
select * from player where level between 6 and 10;
CREATE TABLE new_player SELECT * FROM player WHERE level < 5;
select * from new_player;
SELECT EXISTS(select * FROM player WHERE level > 10)

表关联

DESC equip
SELECT * FROM player
INNER JOIN equip
ON player.id = equip.player_id;
SELECT * FROM player
LEFT JOIN equip
ON player.id = equip.player_id;
SELECT * FROM player
RIGHT JOIN equip
ON player.id = equip.player_id;

索引

创建索引：

创建表时添加索引：

sqlCopy codeCREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    INDEX index_name (column1, column2)
);

或者在已存在的表上添加索引：

1 2	sqlCopy code CREATE INDEX index_name ON table_name (column1, column2);

查看表的索引：

1 2	sqlCopy code SHOW INDEX FROM table_name;

删除索引：

1 2	sqlCopy code DROP INDEX index_name ON table_name;

唯一索引：

创建唯一索引：

1 2	sqlCopy code CREATE UNIQUE INDEX index_name ON table_name (column1, column2);

在创建表时指定唯一索引：

sqlCopy codeCREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    UNIQUE INDEX index_name (column1, column2)
);

全文索引（仅适用于MyISAM引擎）：

创建全文索引：

1 2	sqlCopy code CREATE FULLTEXT INDEX index_name ON table_name (column1, column2);

删除表的所有索引：

1 2	sqlCopy code ALTER TABLE table_name DROP INDEX ALL;

优化表（重新建立索引）：

1 2	sqlCopy code OPTIMIZE TABLE table_name;

查看索引的统计信息：

1 2	sqlCopy code SHOW INDEX STATISTICS FROM table_name;

视图

创建视图：

sqlCopy codeCREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

查看所有视图：

1 2	sqlCopy code SHOW FULL TABLES WHERE Table_type = 'VIEW';

查看视图的定义：

1 2	sqlCopy code SHOW CREATE VIEW view_name;

更新视图：

sqlCopy codeCREATE OR REPLACE VIEW view_name AS
SELECT new_column1, new_column2, ...
FROM new_table_name
WHERE new_condition;

删除视图：
1
2
sqlCopy code
DROP VIEW view_name;
使用视图进行查询：
1
2
sqlCopy code
SELECT * FROM view_name;
更新视图基表： 视图可以被用来更新其基表，前提是视图的选择列表中仅包含一个基表的列，而且这些列没有使用聚合函数、DISTINCT、GROUP BY、HAVING子句或 UNION。
1
2
3
4
5
sqlCopy codeCREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition
WITH CHECK OPTION;
上述的 WITH CHECK OPTION 语句确保只有满足视图定义的条件的行才能被插入或更新。

修改视图的权限：

1 2	sqlCopy code GRANT SELECT ON database_name.view_name TO 'user'@'host';

Neo Sun