三、启动MySQL
文章目录
3.1.MySQL启动和停止
3.1.1.方式一:服务配置
- 此电脑-右击-管理-服务和应用程序-服务-MySQLyanyu安装配置时设置的服务名字,默认就是MySQL,此处我的服务名字是MySQLyanyu-双击-进行相应的配置
- 设置
- 经常使用,设置为自动
- 不经常使用,建议设置为手动
3.1.1.方式二:以管理员运行DOS命令
-
-
点击O搜索,输入cmd-命令提示符-右击-以管理员运行-输入:
- net stop mysqlyanyu服务名字+enter关闭MySQL服务
- net start mysqlyanyu+enter开启MySQL服务
-
Microsoft Windows [版本 10.0.17763.1577] (c) 2018 Microsoft Corporation。保留所有权利。 C:\Users\Administrator>net stop mysqlyanyu MySQLyanyu 服务正在停止. MySQLyanyu 服务已成功停止。 C:\Users\Administrator>net start mysqlyanyu MySQLyanyu 服务正在启动 ... MySQLyanyu 服务已经启动成功。 C:\Users\Administrator>
3.2.MySQL服务登录与退出
背景:MySQL相当于是一个仓库,MySQL服务的启动大门可用与停止大门不可用就是相当于仓库的大门,开启MySQL服务后,仓库大门便是可用,便可以进行刷卡进行身份验证MySQL的登录与退出
使用配置MySQL使设置的root密码,进行登录,我的MySQL目前是只有root一个用户,也是最高权限的用户
3.2.1.方式一:MySQL服务端
-
点击win键-
-MySQL-直接输入root密码
-
不建议使用,因 为该方式只能用于root用户,对于普通用户无法使用,目前我们只设置了root用户,但是以后在工作那样的实际操作环境中,肯定不会只有一个root用户
-
Enter password: **** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.5.15 MySQL Community Server (GPL) Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
3.2.2.方式二:以管理员DOS命令
- 以管理员身份进入DOS命令界面
- 输入以下MySQL命令:
- mysql -h localhost -P 3306 -u root -p
- 输入root密码
- 解释:
- -h 登录那台机器的MySQL服务端本次登录的是“本地主机”
- -P大写,代表的是端口号,未更改的话,默认端口号3306
- -u 用户名
- -p小写,也就是用户名的登录密码
- 可以在-p后面只接输入密码p与密码之间不可以有空格,但是不建议这样操作,存在暴露密码风险
- enter
- 输入用户密码(即我们这里的root密码)
- 退出;
- exti
ctrl
+c
Microsoft Windows [版本 10.0.17763.1577]
(c) 2018 Microsoft Corporation。保留所有权利。
C:\Users\Administrator>mysql -h localhost -P 3306 -u root -p
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.15 MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
若是登录本地主机而且端口号是3306,命令可以简写:
mysql -u root -p
C:\Users\Administrator>mysql -u root -p Enter password: **** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.5.15 MySQL Community Server (GPL) Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
3.2.3.登录失败
-
操作性失误
- 用户名和密码不匹配
- 登录命令写错
- 非管理员模式
- MySQL服务为开启
-
环境变量错误
- 环境变量未配置
- 找到MySQL的bin目录-点击
- 将该路径复制
- 此电脑-右击-属性-高级系统设置-环境变量-系统变量-双机path–新建-粘贴MySQL的bin路径-确定
- 环境变量紊乱
- 原因:path环境变量时从前往后查询,若是在MySQL的bin路径不是在最前面,就可能出现紊乱问题
- 此电脑-右击-属性-高级系统设置-环境变量-系统变量-双机path-编辑文本-全部复制-新建一个TXT文本-粘贴-将“C:\Program Files (x86)\MySQL\MySQL Server 5.5\bin;”剪切到最前面-
ctrl+a
+ctrl+c
(也就将MySQL路径剪切到了最前的,修改后的整个文档进行复制) - 此电脑-右击-属性-高级系统设置-环境变量-系统变量-双机path-编辑文本-删除-粘贴也就将MySQL路径剪切到了最前的,修改后的整个文档
- 环境变量未配置
3.3.MySQL常见3命令
登录MySQL
3.3.1.命令
命令 | 含义 |
---|---|
; or 0\g | 分割两个不同的命令 |
show databases; | 查看仓库里有哪些小仓库 |
use 库名; | 进入某个库 |
show tables; | 查看库里有什么表 |
select database() | 查看处于哪个库 |
create table 表名(指定id和name列以及类型); | 创建表 |
desc 表名; | 查看表的结构 |
select * from 表名; | 查看表的数据 |
insert into 表名 (id,name) values(列号,'内容'); | 向表插入数据,电脑重启后,再次连接到数据库,数据依然存在 |
update 表名 set name='jessi' where id=1; | 将表中id=3那列的数据更新为jessi |
delete from yanyu666 where id=3; | 删除“烟雨666”这个表id=3的数据 |
3.3.2.实例与命令详解
Microsoft Windows [版本 10.0.17763.1577]
(c) 2018 Microsoft Corporation。保留所有权利。
C:\Users\Administrator>mysql -h localhost -P3306 -u root -p
//登录MySQL服务端
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.5.15 MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases
//缺少英文分号
-> show databases;
//未在mysql>输入
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'show databases' at line 2
mysql> show databases;
//查看有哪些库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
/*
1.有四个自带的默认数据库
2.mysql用于保存用户信息
3.information_schema保存一些源数据
4.performance_schema收集性能参数
5.test一种测试数据库,里面没有表,是空的数据库,
可以在里面建表,也可以删除库、修改库,前面三个库不可以动
*/
mysql> use test;
//进入某个库
Database changed
mysql> show table;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql> show tables;
//查看库里有什么表
Empty set (0.00 sec)
//空表,说明test库确实是空库
/*处于test库如何查看mysql库里有什么表?
1.重复上面的,进入到MySQL库进行查看
2.以下命令
*/
mysql> show tables from mysql;
//就在test得库中,不出去,显示来自于
mysql的表
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
24 rows in set (0.03 sec)
mysql> select database();
//查看当前处于那个库?
+------------+
| database() |
+------------+
| test |
+------------+
1 row in set (0.00 sec)
//在test库中创建一个名字为yanyu666的表
mysql> create table yanyu666(
-> id int,
//设置id列类型为int
-> name varchar(20));
//设置name列为字符串类型
//注意在MySQL中,字符串用varchar
//不是java种的String
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| yanyu666 |
+----------------+
1 row in set (0.00 sec)
/*证明yanyu666的表创建成功*/
mysql>
mysql> desc yanyu666;
/*查看表的结构*/
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
/*
1.field 字段(列)有两个,id、name
2、type 列对应的类型,一个int varchar(字符串)
3、以及其它约束
*/
mysql> select * from yanyu666;
/*查看表里有什么数据,结果显然是没有数据,
yanyu666是我们新建的表*/
Empty set (0.00 sec)
mysql> insert into yanyu666 (id,name) values(1,'yanyu');
Query OK, 1 row affected (0.01 sec)
/*在yanyu666表中插入数据
insert into 表名 (id,name)
values(id号,‘内容’);
*/
mysql> insert into yanyu666 (id,name) values(3,'yanyu666');
Query OK, 1 row affected (0.01 sec)
mysql> select * from yanyu666;
+------+----------+
| id | name |
+------+----------+
| 1 | yanyu |
| 3 | yanyu666 |
+------+----------+
2 rows in set (0.00 sec)
mysql> update yanyu666 set name='jessi' where id=1;
/*
将表中id=3那列的数据更新为jessi
*/
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from yanyu666;
+------+----------+
| id | name |
+------+----------+
| 1 | jessi |
| 3 | yanyu666 |
+------+----------+
2 rows in set (0.00 sec)
mysql> delete from yanyu666 where id=3;
Query OK, 1 row affected (0.01 sec)
/*
删除“烟雨666”这个表id=3的数据
*/
mysql> select * from yanyu666;
+------+-------+
| id | name |
+------+-------+
| 1 | jessi |
+------+-------+
1 row in set (0.00 sec)
-+
| 1 | jessi |
| 3 | yanyu666 |
+------+----------+
2 rows in set (0.00 sec)
mysql> delete from yanyu666 where id=3;
Query OK, 1 row affected (0.01 sec)
/*
删除“烟雨666”这个表id=3的数据
*/
mysql> select * from yanyu666;
+------+-------+
| id | name |
+------+-------+
| 1 | jessi |
+------+-------+
1 row in set (0.00 sec)