Mysql基本操作
全局操作
查询Mysql服务
"此电脑->管理->服务"找到Mysql
也可以打开cmd,输入“services.msc”
查看配置文件
打开MySQL的配置文件my.ini
,一般位于MySQL的安装目录下
C:\Program Files\MySQL\MySQL Server 8.0\my.ini
可以查看和修改里面的配置,在配置文件修改的配置是永久的,不过新的配置需要重启Mysql服务后才能生效。
启动和关闭服务
用管理员身份打开cmd命令窗口(必须是以关闭员身份,否则操作失败)
启动服务命令:net start mysql80(以服务名为准)
关闭服务命令:net stop mysql80
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SiHDwApi-1680940757210)(C:\Users\谢功庆\AppData\Roaming\Typora\typora-user-images\image-20230407221729725.png)]
连接(登录)Mysql
cmd命令:mysql -uroot -p
(这里一定是mysql,root用户,-p是密码)
全局配置
全局配置查看命令(需要先登录MySQL)
查看所有全局配置:SHOW VARIABLES
;
查看指定配置信息:SHOW VARIABLES LIKE '%setting%'
//setting为指定配置
这里我们以时区为例,查看、修改
show variables like'%time_zone'
;//查看时区
set global time_zone = '+8:00'
;//修改时区,不过并非永久的
在配置文件my.ini里修改时区为东八区,这样才是永久的
default_time_zone = '+8:00'
(更多全局配置->百度,chat)
Database操作
增
-
创建数据库
创建数据库的基本语法:CREATE DATABASE dB_name; 但是每次创建的时候最好判断一下是否已有这个数据库
所以语句改为:`CREATE DATABASE IF NOT EXISTS db_name;``
如果要顺便设置字符集可以在后面:
CREATE DATABASE IF NOT EXISTS db_name DEFAULT CHARACTER SET UTF8;
-
使用数据库
USE db_name;切换数据库
删
【小伙子,删库跑路是要坐牢的,不要太皮】
除非极特殊情况,否则禁止使用该命令:DROP DATABASE db_name;
改
-
修改库的配置
自行百度
查
-
显示数据库的信息
使用以下 SQL 语句查看数据库的大小、版本、字符集等信息
-
SUM(data_length + index_length) / 1024 / 1024 AS `Size (MB)`, VERSION() AS `Server Version`, DEFAULT_CHARACTER_SET_NAME AS `Character Set`, DEFAULT_COLLATION_NAME AS `Collation` -
-
-
查看数据库
展示所有数据库:SHOW DATABASES;
查看正在使用的数据库:SELECT DATABASE();
查看创建数据库的命令(此命令可以查看到数据库的一些配置):SHOW CREATE DATABASE db_Name;
表的操作
增
-
创建表
CREATE TABLE IF NOT EXISTS table_Name(col1 type,col2 type,col3 type…);
-
添加字段值
INSECT INTO table_name(col1,col2,col3…)//如果是所有字段,那么直接写表名即可
values (value1,value2,value3…);
如果是添加多行那么:
values
(value1,value2,value3…),
(value1,value2,value3…),
…
-
设置表
可以在创建表的时候添加约束条件,例如添加主键 primary key 外键 foreign key 唯一值 unique,非空值:nounull,
检查值 check(但是Mysql这个没用,不能检查,也就是说实际上没用),默认值 default
也可以在创建完表后在设置约束条件,使用ALTER TABLE语句在已经创建的表上添加约束条件。语法格式如下:
1.主键约束 添加:alter table table_name add primary key (字段) 添加B:alter table table_name modify 列名 数据类型 primary key; 删除:alter table table_name drop primary key 2.非空约束 添加:alter table table_name modify 列名 数据类型 not null 删除:alter table table_name modify 列名 数据类型 null 3.唯一约束 添加:alter table table_name add unique 约束名(字段)-- 同上,也有方式B 删除:alter table table_name drop key 约束名 4.自动增长 添加:alter table table_name modify 列名 int auto_increment 删除:alter table table_name modify 列名 int 5.外键约束 添加:alter table table_name add constraint 约束名 foreign key(外键列) references 主键表(主键列) 删除: 第一步:删除外键 alter table table_name drop foreign key 约束名 第二步:删除索引 alter table table_name drop index 索引名 [^1]: 约束名和索引名一样 6.默认值 添加:alter table table_name alter 列名 set default '值' 删除:alter table table_name alter 列名 drop default
删
-
删表
drop table if exists table_name;
-
删除表的所有信息
truncate table table_name;
//注意这个操作不能回滚,要想可以回滚用delete(推荐)delete from table_name;
-
删除指定字段
在delete后面加where语句判断即可
delete from table_name where expression;
改
-
修改表名
ALTER TABLE old_Name RENAME new_Name;
-
修改表的信息
- 修改表结构 我们可以使用
ALTER TABLE
语句修改表结构,常见的操作包括添加、删除、修改列、修改列属性等。例如,我们可以添加一列age
到表student
中:
ALTER TABLE student ADD COLUMN age INT(11) AFTER name;
上述语句将在表
student
中添加一列age
,数据类型为INT
,长度为11
,插入在name
列之后。- 修改表数据 我们可以使用
UPDATE
语句修改表中的数据,常见的操作包括修改单行数据、批量修改数据等。例如,我们可以将表student
中id
为1
的学生的年龄修改为18
:
UPDATE student SET age = 18 WHERE id = 1;
上述语句将修改表
student
中id
为1
的学生的年龄为18
。- 修改表名 我们可以使用
RENAME TABLE
语句修改表名。例如,我们可以将表student
修改为user
:
RENAME TABLE student TO user;
上述语句将修改表
student
的名字为user
。- 修改表的存储引擎 我们可以使用
ALTER TABLE
语句修改表的存储引擎。例如,我们可以将表user
的存储引擎从 MyISAM 修改为 InnoDB:
ALTER TABLE user ENGINE=InnoDB;
- 修改表结构 我们可以使用
查
-
单表综合查询
下面是一个单表的综合查询案例,包含了多种查询方式和 SQL 关键字,旨在尽可能地复杂: 假设我们有一个
books
表,包含以下列:id
、title
、author
、price
、publish_date
、publisher
、category
。我们需要查询出price
最高的 5 本书,并按照publish_date
的升序排序。同时,我们需要计算出这 5 本书的平均价格和总价格,并按照category
进行分组统计。 查询语句如下:SELECT id, title, author, price, publish_date, publisher, category FROM books WHERE price IN ( SELECT price FROM books ORDER BY price DESC LIMIT 5 ) ORDER BY publish_date ASC GROUP BY category WITH ROLLUP;
上述查询语句将返回 5 本价格最高的书籍,并按照
publish_date
的升序排序。同时,它还计算出了这 5 本书的平均价格和总价格,并按照category
进行了分组统计。最后,使用WITH ROLLUP
将这些统计结果进行了一次汇总。综合查询语句的执行顺序是:
-
FROM:查询语句中的 FROM 关键字指定要查询的表。 WHERE:查询语句中的 WHERE 关键字指定查询的条件。 GROUP BY:查询语句中的 GROUP BY 关键字按照指定的列进行分组统计。 HAVING:查询语句中的 HAVING 关键字对分组结果进行过滤。 SELECT:查询语句中的 SELECT 关键字选择要查询的列,可以使用聚合函数计算结果。 DISTINCT:查询语句中的 DISTINCT 关键字去除查询结果中的重复值。 ORDER BY:查询语句中的 ORDER BY 关键字指定查询结果的排序方式。 LIMIT:查询语句中的 LIMIT 关键字限制查询结果的数量。 -- 根据上述执行顺序,可以得出以下代码的执行顺序: -- 执行子查询,查询出价格最高的前 5 本书的价格。在 books 表中,WHERE 子句筛选出价格在子查询中查询出的价格范围内的书籍。根据 publish_date 列进行升序排序。按照 category 分组统计。计算每个分组的平均价格、总价格。使用 WITH ROLLUP 在分组统计的基础上汇总统计。选择 id、title、author、price、publish_date、publisher、category 列。 -- 返回查询结果。 注意,实际执行顺序可能会受到数据库优化器的影响,因此不一定完全按照上述顺序执行。但是,了解 SQL 语句的执行顺序可以帮助我们更好地理解和优化查询语句。
-
-
多表查询
-
案例
假设有两张表,一张是顾客信息表(customers),另一张是订单信息表(orders)。它们的结构如下所示:
- CREATE TABLE customers ( customer_id INT PRIMARY KEY, name VARCHAR(50), email VARCHAR(50), phone VARCHAR(20) ); CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT, order_date DATE, total_amount DECIMAL(10, 2), FOREIGN KEY (customer_id) REFERENCES customers (customer_id) );
其中,customers表存储了顾客的基本信息,包括customer_id、name、email和phone四个字段;orders表存储了订单的信息,包括order_id、customer_id、order_date和total_amount四个字段,其中customer_id为外键,关联到customers表的customer_id字段。 现在我们需要查询所有订单的详细信息及对应的顾客信息,包括订单号、订单日期、订单总金额、顾客姓名、顾客邮箱和顾客电话。这个查询可以使用内联、外联和自联三种方式来实现,具体如下所示
内联
-
内联查询是指将两张表的所有匹配行连结起来,只返回符合条件的数据行。内联查询可以使用INNER JOIN关键字来实现。以下是一个内联查询的示例:
SELECT orders.order_id, orders.order_date, orders.total_amount, customers.name, customers.email, customers.phone FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id;
这个查询语句中,使用INNER JOIN将orders表和customers表关联起来,关联条件为orders表的customer_id等于customers表的customer_id。查询结果将返回所有匹配的订单信息和对应的顾客信息。
-
-
外联
外联查询是指将两张表的所有行(包括不匹配的行)连结起来,返回符合条件的数据行和空值行。外联查询可以使用LEFT JOIN或RIGHT JOIN关键字来实现。以下是一个左外联查询的示例:
SELECT orders.order_id, orders.order_date, orders.total_amount, customers.name, customers.email, customers.phone FROM orders LEFT JOIN customers ON orders.customer_id = customers.customer_id;
这个查询语句中,使用LEFT JOIN将orders表和customers表左外联起来,关联条件为orders表的customer_id等于customers表的customer_id。查询结果将返回所有订单信息和对应的顾客信息,如果订单信息没有对应的顾客信息,则顾客信息的字段将为NULL。
-
自联
自联查询是指将同一张表的不同行连结起来,用于查询具有父子关系的数据。自联查询可以使用AS关键字来实现。以下是一个自联查询的示例:
SELECT c1.name, c1.email, c1.phone, c2.name, c2.email, c2.phone FROM customers c1, customers c2 WHERE c1.customer_id = 1 AND c2.customer_id = 2;
这个查询语句中,将customers表自联两次,分别用c1和c2来表示,通过WHERE子句指定查询条件,即c1的customer_id等于1,c2的customer_id等于2。查询结果将返回两个顾客的姓名、邮箱和电话号码。
其他查询
-
除了内联、外联和自联查询之外,MySQL中还有一些其他常用的多表查询方式,包括子查询、UNION查询和交叉连接查询等。 这里再举几个例子:
- 子查询: 子查询是指将一个查询语句嵌套在另一个查询语句中,用于查询符合某些条件的数据。以下是一个子查询的示例:
SELECT *
FROM orders
WHERE customer_id IN (
SELECT customer_id
FROM customers
WHERE name LIKE 'J%'
);
这个查询语句中,外部查询语句查询orders表中所有顾客姓名以J开头的订单信息,内部查询语句查询customers表中所有姓名以J开头的顾客的customer_id。查询结果将返回所有符合条件的订单信息。 2. UNION查询: UNION查询是指将两个或多个查询语句的结果集合并成一个结果集,用于查询不同的表或相同的表的不同列。以下是一个UNION查询的示例:
SELECT name, email
FROM customers
UNION
SELECT 'Admin', 'admin@example.com'
FROM dual;
这个查询语句中,将customers表中所有顾客的姓名和邮箱查询出来,并与一个包含固定值的查询结果合并。需要注意的是,UNION查询要求两个查询的列数和数据类型必须相同,否则会出现错误。 3. 交叉连接查询: 交叉连接查询是指将两张表的所有行组合起来,返回所有可能的组合结果。交叉连接查询可以使用CROSS JOIN关键字来实现。以下是一个交叉连接查询的示例:
SELECT customers.name, orders.order_id
FROM customers
CROSS JOIN orders;
这个查询语句中,将customers表和orders表进行交叉连接,查询结果将返回所有顾客姓名和订单号的组合情况。需要注意的是,交叉连接查询可能会产生大量的结果行,应该谨慎使用。 总之,MySQL中有多种多表查询方式,开发人员可以根据具体的查询需求选择合适的方式来实现。