MySQL数据库操作-EW帮帮网

SQL语言主要由以下几部分组成。
>DDL(DataDefinitionLanguage,数据定义语言):用来建立多数据库、数据库对象和定义字段,如CREATE、ALTER、DROP。
>DML(Data Manipulation Language,数据操纵语言):用来插入、除和修改数据库中的数据,如INSERT、UPDATE、DELETE。
>DQL(DataQuery Language,数据查询语言):用来查询数据库中的数据,如SELECT。
>DCL (Data Control Language,数据控制语言):用来控制数据库组件的存取许可、存取权限等,如COMMIT、ROLLBACK、GRANT、REVOKE20

二：MySQL库操作

1.系统数据库

MySQL安装后会自动创建几个系统数据库，用于存储系统信息和元数据。常见的系统数据库包括：

information_schema：存储数据库的元数据，如表、列、索引等信息。
mysql：存储用户权限、日志等系统信息。
performance_schema：用于监控MySQL服务器的性能。
sys：提供了一种更简单的方式来查看performance_schema中的数据。

2.数据库操作

2.1创建数据库

使用CREATE DATABASE语句可以创建一个新的数据库。语法如下：

CREATE DATABASE database_name;

例如，创建一个名为mydb的数据库：

CREATE DATABASE mydb;

2.2数据库命名规则

数据库名称应遵循以下规则：

名称可以由字母、数字、下划线和美元符号组成。
名称不能以数字开头。
名称不能包含特殊字符或空格。
名称长度通常不超过64个字符。

2.3选择数据库

使用USE语句可以选择一个数据库作为当前操作的数据库。语法如下：

USE database_name;

例如，选择mydb数据库：

USE mydb;

2.4查看数据库

使用SHOW DATABASES语句可以查看当前MySQL服务器上的所有数据库。语法如下：

SHOW DATABASES;

2.5删除数据库

使用DROP DATABASE语句可以删除一个数据库。语法如下：

DROP DATABASE database_name;

例如，删除mydb数据库：

DROP DATABASE mydb;

三：MySQL表操作

1.表介绍

表是数据库中存储数据的基本单位，由行和列组成。每一行代表一条记录，每一列代表一个字段。表的结构由字段名、数据类型和约束条件定义。

2.查看表

使用SHOW TABLES语句可以查看当前数据库中的所有表。语法如下：

在操作之前,需要先使用USE语句切换到所使用的数据库,就像要查看一个文件夹里面有多少文件,

要先进入这个文件夹。

USE mysql;
Database changed
SHOW TABLES;

3.创建表

3.1语法

使用CREATE TABLE语句可以创建一个新表。语法如下：


mysql>create table表名(
字段名1类型[(宽度)约束条件],
字段名2类型[(宽度)
约束条件],
字段名3类型[(宽度)
约束条件]
) :
#注意:
1.在同一张表中,字段名是不能相同
2.宽度和约束条件可选
3.字段名和类型是必须的,字段的类型宽度和约束条件是可选项
4.表中最后一个字段不要加逗号

3.2类型介绍

表内存放的数据有不同的类型,类似于使用excel存储数据时也需要设置的数据类型,每种数据类型都有自己的宽度,但宽度是可选的,7不设置宽度时,会使用字段的默认宽度。

MySQL支持多种数据类型，主要包括以下几类：

数值类型：如INT、DOUBLE、DECIMAL等。
日期类型：如DATE、DATETIME、TIMESTAMP等。
字符串类型：如VARCHAR、CHAR、TEXT等。

3.3约束条件

约束条件与数据类型的宽度一样,都是可选参数,类似于使用excel存储数据时,可以利用excel的公式限制员工ID列:禁止重复值,且不能为空

约束条件用于限制表中数据的规则，常见的约束条件包括：

PRIMARY KEY：定义主键，唯一标识表中的每一行。
NOT NULL：字段不能为空。
UNIQUE：字段值必须唯一。
FOREIGN KEY：定义外键，用于关联其他表。
DEFAULT：为字段设置默认值。

3.4创建表示例

创建一个名为users的表，包含id、name和email字段：

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE
);

4.查看表结构

使用DESCRIBE语句可以查看表的结构。语法如下：

DESCRIBE table_name;

例如，查看users表的结构：

DESCRIBE users;

5.修改表

5.1修改表名

使用RENAME TABLE语句可以修改表名。语法如下：

RENAME TABLE old_table_name TO new_table_name;

例如，将users表重命名为customers：

RENAME TABLE users TO customers;

5.2增加字段

使用ALTER TABLE语句可以增加字段。语法如下：

ALTER TABLE表名ADD字段名
数据类型
[完整性约束条件...],ADD字段名
数据类型[完整性约束条件...];
ALTER TABLE表名ADD字段名
数据类型[完整性约束条件...] FIRST;
ALTER TABLE表名ADD字段名
数据类型[完整性约束条件...] AFTER字段名;

例如:

mysql> ALTER TABLE t1 ADD class int(3) not null;
Query OK, 0 rows affected, 1 warning (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 1

5.3删除字段

使用ALTER TABLE语句可以删除字段。语法如下：

ALTER TABLE table_name DROP COLUMN column_name;

例如:

mysql> alter table t2 drop class;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

5.4修改字段

ALTER TABLE表名 MODIFY字段名 数据类型[完整性约束条件...];
ALTER TABLE表名CHANGE 旧字段名 新字段名 旧数据类型[完整性约束条
件...];
ALTER TABLE表名CHANGE 旧字段名新字段名 新敬据类型[完整性约束条
件...];

例如:

alter table t2 modify name char(50);
Query OK, O rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table t2 change name user_name varchar(50);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

6.复制表

只复制表结构,不复制表中数据

mysql> create table t3 like t2;
Query OK, O rows affected (0.01 sec)

复制表结构+记录(key不会复制:主键、外键和索引)

mysql> create table t4 select * from t2;
Query OK, O rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

例如，复制users表到users_backup：

CREATE TABLE users_backup AS SELECT * FROM users;

7.删除表

删除数据库中的表,需要指定"数据库名.表名"作为参数女;若只指定表名参数,则需先通过执行"USE"语句切换到目标数据库

mysql> drop table t2;
Query OK, O rows affected (0.01 sec)
mysql> drop table dbl.t3;
Query OK, 0 rows affected (0.01 sec)

使用DROP TABLE语句可以删除表。语法如下：

DROP TABLE table_name;

例如，删除users表：

DROP TABLE users;

四：MySQL数据操作

1.介绍

在MySQL管理软件中,可以通过SQL语句中的DML语言来实现数据的操作,包括使用INSERT实现数据的插入、使用UPDATE实现数据的更新、使用DELETE实现数据的删除、使用SELECT查询数据。

创建示例表:

mysql>usedbl; #进入dbl库
mysql> create table tl(
-> id int,
->name varchar(50),
->age int (3),
->sex enum(')
->male', female')
-> ):
mysql> show tables;

2.插入数据INSERT

INSERTINTO语句:用于向表中插入新的数据记录,语句格式如下所示插入数据后可使用mysql>select*from tl;

验证顺序插入:
INSERT INTO表名VALUES(值1,值3...值 3...值 );

mysql> INSERT INTO t1 VALUES (1, "zhangsan", 18, "male");
Query OK, I row affected (0.00 sec)

指定字段插入数据:
INSERT INTO表名(字段1,字段2,字段3...字段n) VALUES6(值1,值2,值3...值

mysql>INSERT INTO tl(id, name,age, sex) VALUES (2, "lisi", 18, "female");
Query OK, I row affected (0.00 sec)

插入多条记录:
INSERT INTO表名VALUES(值1,值3...值3...值 n),(值1,值2,值3...值n),(值1,值2,值3...值n);

mysql>INSERT INTO ti VALUES (3, "wangwu", 28, "male"), (4, "zhaoliu", 30, "female");
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0

3.删除数据INSERT

使用DELETE语句可以删除表中的数据。语法如下：

DELETE FROM table_name WHERE condition;

例如，删除users表中id为1的记录：

DELETE FROM users WHERE id = 1;

4.更新数据INSERT

使用UPDATE语句可以更新表中的数据。语法如下：

UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

例如，更新users表中id为1的记录的name字段：

UPDATE users SET name = 'Jane Doe' WHERE id = 1;

1.单表查询

1.1单表查询语法

SELECT column1, column2 FROM table_name WHERE condition;

1.2关键字执行的优先级

WHERE、GROUP BY、HAVING、ORDER BY、LIMIT。

1.3准备数据

INSERT INTO users (username, email) VALUES ('alice', 'alice@example.com'), ('bob', 'bob@example.com');

1.4简单查询

SELECT * FROM users;

1.5where条件

（1）比较运算符：>、<、>=、<=、!=

SELECT * FROM users WHERE id > 1;

（2）between and:在什么之间

SELECT * FROM users WHERE id BETWEEN 1 AND 3;

（3）in:集合查询

SELECT * FROM users WHERE id IN (1, 2);

（4）like:像,模糊匹配 _:任意单个字符 %:任意多个字符

SELECT * FROM users WHERE username LIKE 'j%';

（5）逻辑运算符:andornot

SELECT * FROM users WHERE id > 1 AND username = 'john_doe';

1.6group by 分组

(1)什么是分组?为什么要分组?

分组用于将数据按某一列或多列进行分组，通常与聚合函数一起使用。

(2)聚合函数:count （） sum （） avg （） max （） min（）

SELECT COUNT(*) FROM users;

1.7having过滤

SELECT username, COUNT(*) FROM users GROUP BY username HAVING COUNT(*) > 1;

1.8order by 排序

SELECT * FROM users ORDER BY username ASC;

1.9limit限制结果条目

SELECT * FROM users LIMIT 2;

1.10正则匹配

SELECT * FROM users WHERE username REGEXP '^j';

2.多表查询

2.1子查询

SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

2.2多表连接查询

1.内连接（只连接匹配的行）

SELECT users.username, orders.order_id FROM users INNER JOIN orders ON users.id = orders.user_id;

2.左连接（优先显示左表全部记录）

SELECT users.username, orders.order_id FROM users LEFT JOIN orders ON users.id = orders.user_id;

3.右连接（优先显示右表全部记录）

SELECT users.username, orders.order_id FROM users RIGHT JOIN orders ON users.id = orders.user_id;

五：MySQL数据库用户授权

1.创建用户

使用CREATE USER语句创建用户。例如：

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

2.授权操作

授权语法:
GRANT权限列表ON数据库名.表名T0用户名@来源地址
>权限列表:用于列出授权使用的各种数据库操作,以逗号:进行分隔,如"select, insert,
update"。使用"all"表示所有权限,可授权执行任何操作。
>数据库名.表名:用于指定授权操作的数据库和表的名称,其中可以使用通配符"*"。例如,使用"auth.*"表示授权操作的对象为auth

执行以下操作可以为数据库用户'xiaoqi'@localhost',设置对test数据库中的所有表具有查询权限
GRANT SELECT ON test.* TO 'xiaoqi'@'localhost'
切换到其他Shell终端,以用户xiaoqi的身份连接数据库。在已授权的数据库上操作将被允许,否则将被拒绝。例如,允许用户xiaoqi查询test数据库中表的数据记录,但禁止查询其他数据库中的表的认记录。showdatabases只能看到被授权的库

在企业服务器的应用中,数据库与网站服务器有时候是相互独立的。因此在MySQL服务器中,应根据实际情况创建新的用户授权,允许受权用户从网站服务器访问数据库。通常的做法是,创建一个或几个网站专用的数女据库,并授予所有权限,限制访问的来源IP地址。
例如,执行以下操作可以新建bdqn数据库,并授权从IP地址为
192.168.4.19的主机连接,用户名为"dbuser",密码为"pwd@123",允许在bdqn数据库中执行所有操作

mysql> CREATE DATABASE bdqn;
Query OK, I row affected (0.03 sec
mysql> CREATE USER 'dbuser'@'192.168.4.19' IDENTIFIEDBY' pwd@123' :
Query OK, O rows affected (0.01 sec)
mysql> GRANT all ON bdqn.*TO'dbuser' @'192.168.4.19' 
Query OK, O rows affected (0.00 sec)

3.查看权限

使用SHOW GRANTS语句可以查看用户的权限。语法如下：

SHOW GRANTS FOR 'username'@'host';

例如，查看testuser用户的权限：

SHOW GRANTS FOR 'testuser'@'localhost';

4.撤销权限

使用REVOKE语句可以撤销用户的权限。语法如下：

REVOKE privileges ON database_name.table_name FROM 'username'@'host';

例如，撤销testuser用户对mydb数据库的所有权限：

REVOKE ALL PRIVILEGES ON mydb.* FROM 'testuser'@'localhost';

MySQL数据库操作

一：MySQL数据库介绍