登录mysql数据库

​

注：文中mysql版本为8.0而并非5.7

使用命令行登录

在操作系统中打开命令行工具/或使用xshell等ssh登录

使用命令行登录数据库：根据所使用的数据库类型，输入相应的命令行登录命令。

mysql -u 用户名 -p密码 -P 3306 -h 数据库地址

-u 用户名 -p密码 -P端口 -h 数据库地址

登录成功：如果用户名和密码正确无误，命令行工具会显示登录成功，并允许用户进行数据库操作。

注意：输入密码时与p之间不要有空格，不然空格也会被视为密码的一部分，这通常会导致登录失败

使用客户端登录（如navicat等）

输入主机（本地连接默认用localhost）、端口（默认是3306）、用户名、密码后

点击左下角测试连接，连接成功后点击确定保存即可通过客户端访问mysql数据库。

数据库管理相关

创建数据库：

创建数据库是数据库管理的第一步。使用以下SQL语句创建一个新的数据库：

CREATE DATABASE database_name
[ character_set_collate ]
[ ENCRYPTION [=] { 'Y' | 'N' } ]
[ CHARACTER SET charset_name ]
[ COLLATE collation_name ]
[ SQL SECURITY { DEFINER | INVOKER } ]
[ COMMENT 'string' ];

database_name是新数据库的名称。

character_set_collate指定默认的字符集和校对规则。

ENCRYPTION 选项用于指定数据库文件是否应该被加密。

CHARACTER SET和COLLATE选项用于指定数据库的默认字符集和校对规则。

SQL SECURITY选项用于定义存储过程和函数的SQL安全特性。

COMMENT选项用于添加数据库的描述。

删除数据库

删除数据库是一个危险的操作，在生产正式环境请做好备份谨慎使用。使用以下SQL语句删除一个数据库：

DROP DATABASE database_name;

database_name是要删除的数据库的名称。

修改数据库

修改数据库通常涉及更改数据库的默认字符集、校对规则或其他属性。使用以下SQL语句修改数据库：

ALTER DATABASE database_name
[ character_set ]
[ collate ]
[ ENCRYPTION { 'Y' | 'N' } ]
[ UPGRADE DATA DIRECTORY NAME ]
[ SQL SECURITY { DEFINER | INVOKER } ]
[ COMMENT 'string' ];

character_set和collate选项用于更改数据库默认字符集和校对规则。

ENCRYPTION选项用于更改数据库文件的加密状态。

UPGRADE DATA DIRECTORY NAME选项用于更改数据库文件的存储路径。

SQL SECURITY和 COMMENT选项用于更改数据库的SQL安全特性和描述。

查看数据库列表

查看当前MySQL服务器上所有数据库的列表：

SHOW DATABASES

LIKE 'pattern | WHERE expr ;

LIKE 'pattern’用于过滤数据库名称。

WHERE expr用于使用条件表达式过滤数据库。

选择数据库

在执行数据库操作之前，通常需要选择一个数据库：

USE database_name;

database_name是要操作的数据库的名称。

用户和权限管理

数据库管理还包括用户账户和权限的管理。以下是一些基本的权限管理命令：

****

创建用户：
CREATE USER user_name [IDENTIFIED BY 'password'];
删除用户：
DROP USER user_name;
授予权限：
GRANT privileges ON database_name.* TO user_name
撤销权限：
REVOKE privileges ON database_name FROM user_name;
刷新权限：
FLUSH PRIVILEGES;

数据库维护

数据库维护包括检查和修复数据库、优化表和分析表等操作。以下是一些基本的维护命令：

检查表：
CHECK TABLE table_name;
修复表：
REPAIR TABLE table_name;
优化表：
OPTIMIZE TABLE table_name;
分析表：
ANALYZE TABLE table_name;

表管理相关

创建表结构：

CREATE TABLE table_name (column1 datatype1 [constraint1], column2 datatype2 [constraint2],... [ table_options ]);

table_name 是新表的名称。

column1, column2, … 是表中的列名。

datatype 是列的数据类型，如 INT, VARCHAR, DATE 等。

constraints 是列的约束条件，如 NOT NULL, PRIMARY KEY, FOREIGN KEY 等。

table_options 是表级别的选项，如字符集、存储引擎等。

查看表结构：

查看表的结构，包括列名、数据类型、是否可以为NULL、键信息等：

DESCRIBE table_name;
DESC table_name;

table_name 是表的名称。

修改表结构：

添加新列：
ALTER TABLE table_name
ADD column_name datatype;
修改列：
ALTER TABLE users MODIFY COLUMN email VARCHAR(500) NOT NULL;
删除列：
ALTER TABLE table_name
DROP COLUMN column_name;
修改字段类型：
ALTER TABLE table_name
MODIFY COLUMN column_name new_datatype;
重命名列：
ALTER TABLE table_name
CHANGE OLD_COLUMN_NAME NEW_COLUMN_NAME datatype;

添加主键：
ALTER TABLE table_name
ADD PRIMARY KEY (column_name);
删除主键：
ALTER TABLE table_name
DROP PRIMARY KEY;
重命名表：
RENAME TABLE old_table_name TO new_table_name;
复制表结构：
CREATE TABLE new_table_name LIKE existing_table_name;
复制表结构和数据：
CREATE TABLE new_table_name AS SELECT * FROM existing_table_name;

查看所有表：

SHOW TABLES;

删除表：

DROP TABLE users;

索引管理：

创建索引：
创建单列索引：
CREATE INDEX index_name ON table_name (column_name);
创建复合索引（多列索引）：
CREATE INDEX index_name ON table_name (column_name1, column_name2);

查看索引：
查看某个表的所有索引：
SHOW INDEXES FROM table_name;
查看数据库中所有索引：
SHOW INDEXES FROM database_name;


删除索引：
当索引不再需要时，使用DROP INDEX语句删除索引。
删除指定的索引：
DROP INDEX index_name ON table_name;
DROP INDEX必须指定表名（如 DROP INDEX index_name ON table_name），无法省略数据库名：
DROP INDEX index_name ON table_name;

修改索引：
mysql不支持直接修改索引，但可以通过删除再新建的方式来修改。

优化索引：
优化表的索引，回收未使用的空间，更新索引的统计信息：
对于有大量更新、删除操作的表，定期执行此命令可能有助于维护索引性能。
OPTIMIZE TABLE table_name;

分析索引：
分析表的索引和数据，收集关于索引选择性的信息：
ANALYZE TABLE table_name;


检查索引：
检查表的索引是否损坏：
CHECK TABLE table_name;

修复索引：
如果索引损坏，可以使用以下命令尝试修复：
REPAIR TABLE table_name;

约束管理：

创建约束
主键约束：
CREATE TABLE table_name (
    column_name1 INT NOT NULL,
    column_name2 VARCHAR(100),
    PRIMARY KEY (column_name1)
);

唯一约束：
CREATE TABLE table_name (
    column_name1 INT NOT NULL,
    column_name2 VARCHAR(100),
    UNIQUE (column_name2)
);

外键约束：
CREATE TABLE child_table (
    child_id INT,
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES parent_table(parent_id)
);

检查约束（MySQL 8.0及以上版本）：
CREATE TABLE table_name (
    column_name INT,
    CHECK (column_name > 0)
);


查看约束
查看表的约束：
SHOW CREATE TABLE table_name;

查看所有外键约束：
SELECT 
    CONSTRAINT_NAME, 
    TABLE_NAME, 
    COLUMN_NAME, 
    REFERENCED_TABLE_NAME, 
    REFERENCED_COLUMN_NAME 
FROM 
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE 
TABLE_NAME = 'table_name';

删除约束
删除主键约束：
ALTER TABLE table_name DROP PRIMARY KEY;

删除唯一约束：
ALTER TABLE table_name DROP INDEX unique_index_name;

删除外键约束：
ALTER TABLE child_table DROP FOREIGN KEY foreign_key_name;

删除检查约束（MySQL 8.0及以上版本）：
ALTER TABLE table_name DROP CHECK check_constraint_name;

修改约束
修改主键约束：
先删除旧的主键约束，然后添加新的主键约束：
ALTER TABLE table_name DROP PRIMARY KEY, ADD PRIMARY KEY (new_column_name);

修改外键约束：
先删除旧的外键约束，然后添加新的外键约束：
ALTER TABLE child_table DROP FOREIGN KEY foreign_key_name;
SHOW CREATE TABLE；
ALTER TABLE child_table ADD FOREIGN KEY (new_column_name) REFERENCES parent_table(parent_id);

添加约束
在已存在的表中添加约束：
添加唯一约束：
ALTER TABLE table_name ADD UNIQUE (column_name);

添加外键约束：
ALTER TABLE child_table ADD FOREIGN KEY (parent_id) REFERENCES parent_table(parent_id);

添加检查约束（MySQL 8.0及以上版本）：
ALTER TABLE table_name ADD CHECK (column_name > 0);

数据操作

详细说明数据的增删改查（CRUD）操作。
(1)创建（Create）
插入单行数据：
使用 SQL 的 INSERT 语句。例如，在一个名为“students”的表中插入一条学生记录，表中有“id”（整数类型，主键）、“name”（字符串类型）、“age”（整数类型）等字段。
INSERT INTO students (id, name, age) VALUES (1, '张三', 18);
解释：这条语句将一个学生的信息插入到“students”表中，指定了学生的编号为 1，姓名为“张三”，年龄为 18。

插入多行数据：
可以一次性插入多条记录，使用多个 VALUES 子句。
INSERT INTO students (id, name, age) VALUES (2, '李四', 19), (3, '王五', 20);
解释：这条语句同时插入了两个学生的信息。
(2)读取（Read）
查询所有数据
使用 SELECT * FROM 表名; 可以查询表中的所有数据。
SELECT * FROM students;
解释：这条语句将返回“students”表中的所有学生记录。
查询特定字段数据
可以指定要查询的字段名称。
SELECT name, age FROM students;
解释：这条语句只查询“students”表中的学生姓名和年龄字段。
条件查询
使用 WHERE 子句添加查询条件。
SELECT * FROM students WHERE age > 18;
解释：这条语句查询“students”表中年龄大于 18 的学生记录。
排序查询
使用 ORDER BY 子句对查询结果进行排序。
SELECT * FROM students ORDER BY age DESC;
解释：这条语句查询“students”表中的所有学生记录，并按照年龄降序排列。
(3)更新（Update）
更新单个字段
使用 UPDATE 表名 SET 字段名=新值 WHERE 条件;
UPDATE students SET age = 20 WHERE id = 1;
解释：这条语句将“students”表中编号为 1 的学生年龄更新为 20。
更新多个字段
可以同时更新多个字段的值。
UPDATE students SET name = '张三新', age = 19 WHERE id = 1;
解释：这条语句将编号为 1 的学生姓名更新为“张三新”，年龄更新为 19。
(4)删除（Delete）
删除单行数据
使用 DELETE FROM 表名 WHERE 条件;
DELETE FROM students WHERE id = 1;
解释：这条语句删除“students”表中编号为 1 的学生记录。
删除所有数据
如果不指定条件，将删除表中的所有数据。
DELETE FROM students;
注意：InnoDB引擎下未提交的事务可通过回滚恢复，物理删除需通过备份。

sql语法指南

sql函数

Sql函数：
是对数据执行操作的预定义的代码块。MySQL 提供了多种内置函数，包括字符串函数、数值函数、日期和时间函数等。
(1)字符串函数：
CONCAT(str1, str2, ...)：将多个字符串连接在一起。
LOWER(str)：将字符串转换为小写。
UPPER(str)：将字符串转换为大写。
TRIM(str)：去除字符串两端的空格。
LENGTH(str)：返回字符串的长度。
LOCATE(substring ，string，start_position)：返回子字符串在字符串中的位置。
(2)数值函数：
ABS(number)：返回数值的绝对值。
ROUND(value, decimal_places)：将数值四舍五入到指定的小数位数。
CEILING(value)：返回大于或等于数值的最小整数。
FLOOR(value)：返回小于或等于数值的最大整数。
RAND（）：返回一个随机数。
SQRT（value）：返回一个数在平方根。
(3)日期和时间函数：
NOW()：返回当前的日期和时间。
CURDATE()：返回当前的日期。
CURTIME()：返回当前的时间。
YEAR（date）:从日期中提取年份。
DATEDIFF(date1, date2)：返回两个日期之间的天数差。
TIMESTAMPDIFF(unit, datetime1, datetime2)：返回两个日期/时间之间的差异。
(4)聚合函数：
COUNT(column)：返回列中非 NULL 值的数量。
SUM(column)：返回列中数值的总和。
AVG(column)：返回列中数值的平均值。
MIN(column)：返回列中的最小值。
MAX(column)：返回列中的最大值。

sql表达式

SQL 表达式是由一个或多个 SQL 元素组成的组合，用于在查询时执行计算。这些元素可以是列名、常量、函数，也可以是算术运算符、比较运算符和逻辑运算符。
(1)算术表达式：
由数值常量、列名和算术运算符（+、-、*、/、%等）组成。
(2)比较表达式：
由列名、常量、函数返回值和比较运算符（=、<、>、<=、>=、<> 等）组成。
(3)逻辑表达式：
由比较表达式、逻辑运算符（AND、OR、NOT 等）组成。
(4)字符串表达式：
由字符串常量、列名（代表字符串数据）和字符串运算符（如连接运算符 || 或函数 CONCAT () 等）组成。
(5)日期表达式：
由日期常量、列名（代表日期数据）和日期函数、运算符组成。
(6)位运算表达式
位运算表达式在处理二进制数据时使用：
	位与（&）
	位或（|）
	位异或（^）
	位非（~）
	位左移（<<）
	位右移（>>）