MySQL命令
-u ：后面是用户id
//#客户端登录：mysql -h localhost -u root -p
C:\Users\EDY>mysql -h localhost -u root -p
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.34-log MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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>
//#查询版本号：select version();
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.34-log |
+------------+
1 row in set (0.01 sec)
3）查看基本配置信息
status # 查看基本配置信息
//# 查看基本配置信息：status
mysql> status
--------------
mysql Ver 14.14 Distrib 5.7.34, for Win64 (x86_64)
Connection id: 3
Current database:
Current user: root@localhost
SSL: Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
Using delimiter: ;
Server version: 5.7.34-log MySQL Community Server (GPL)
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: gbk
Conn. characterset: gbk
TCP port: 3306
Uptime: 7 hours 23 min 47 sec
Threads: 1 Questions: 26 Slow queries: 0 Opens: 111 Flush tables: 1 Open tables: 104 Queries per second avg: 0.000
--------------
4）查看当前时间
select now(); # 查看当前时间
//# 查看当前时间：select now();
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2022-03-03 18:04:54 |
+---------------------+
1 row in set (0.00 sec)
5）查看连接数
show variables like '%connections%'; # 查看连接数
//# 查看连接数show variables like '%connections%';
mysql> show variables like '%connections%';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| max_connections | 151 |
| max_user_connections | 0 |
+----------------------+-------+
2 rows in set, 1 warning (0.01 sec)
6）查询超时的关键字
show variables like '%timeout%'; # 查询超时的关键字
//# 查询超时的关键字:show variables like '%timeout%';
//connect_timeout:链接
//net_read_timeout：读
//net_write_timeout：写
//需要关注的是链接、读和写超时三部分
mysql> show variables like '%timeout%';
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| have_statement_timeout | YES |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 60 |
| wait_timeout | 28800 |
+-----------------------------+----------+
13 rows in set, 1 warning (0.01 sec)二、管理命令
1、
//# 创建数据库：create database 数据库名称;
mysql> create database anruo;
Query OK, 1 row affected (0.01 sec)2）查看已有数据库
show databases； # 查看已有数据库
//# 查看已有数据库：show databases；
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| anruo |
| indext |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
3）查询数据库在服务器的储存路径
show variables like '%datadir%'; # 查询数据库在服务器的储存路径
//# 查询数据库在服务器的储存路径:show variables like '%datadir%';
mysql> show variables like '%datadir%';
+---------------+---------------------------------------------+
| Variable_name | Value |
+---------------+---------------------------------------------+
| datadir | C:\ProgramData\MySQL\MySQL Server 5.7\Data\ |
+---------------+---------------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql>4）进入数据库
use databaseName # 进入数据库
//# 进入数据库：use 数据库名称
mysql> use anruo
Database changed
5）验证是否进入到那个数据库
select database(); # 验证是否进入到那个数据库
//# 查询连接到那个数据库:select database();
mysql> select database();
+------------+
| database() |
+------------+
| anruo |
+------------+
1 row in set (0.00 sec)
6）查看MySQL数据库所有文件
查看MySQL数据库的所有文件，包括隐藏文件的方法为：西安查询到数据库的储存路径，如上图，然后在本地进入到上述目录下，进入c盘，复制路径进行搜索即可进入到上述目录，然后就可以查看到所有的文件；
7）删除数据库
drop database 数据库名； # 删除数据库
//# 查询连接到那个数据库：select database();
mysql> drop database anruo;
Query OK, 0 rows affected (0.01 sec)
2、表格命令
1）创建表
create table 表的名称(表的内容，填写方式：字段 数据类型)； #创建表
//创建数据库
mysql> create database anruo;
Query OK, 1 row affected (0.00 sec)
//进入指定数据库
mysql> use anruo
Database changed
//创建表格
mysql> create table wugen(
-> name varchar(10),
-> age int,
-> sex varchar(5),
-> salary float,
-> tal int,
-> birthday datetime
-> );
Query OK, 0 rows affected (0.04 sec)
//查看指定表格
mysql> desc wugen;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| name | varchar(10) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| sex | varchar(5) | YES | | NULL | |
| salary | float | YES | | NULL | |
| tal | int(11) | YES | | NULL | |
| birthday | datetime | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
情景一：字段约束
在数据库中创建表时，字段的约束条件：
（1）primary key 表示主键，主要是为了排序，就是该字段的内容是唯一的；
（2）default 是指默认值，当该字段没有写入内容时，写入指定的内容；
（3）not null 是指不能为空；
（4）unique key 是指不能重复，具有唯一约束性，与primary的区别就是排序没有primary强大）；
如下所示：
//创建表格，带入约束条件，即：
//primary key表示主键，主要是为了排序，就是该字段的内容是唯一的；
//default是指默认值，当该字段没有写入内容时，写入指定的内容；
//not null是指不能为空；
//unique key 是指不能重复，具有唯一约束性，与primary的区别就是排序没有primary强大）；
mysql> create table anruo(
-> id int primary key,
-> name varchar(10) unique key,
-> age int not null,
-> sex varchar(5) default "girl"
-> );
Query OK, 0 rows affected (0.04 sec)
//查看当前表格
mysql> desc anruo;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(10) | YES | UNI | NULL | |
| age | int(11) | NO | | NULL | |
| sex | varchar(5) | YES | | girl | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql>情景二：自增字段
auto_increment # 自增
//创建自增表格
mysql> create table intal(
-> id int primary key auto_increment,
-> name varchar(10)
-> );
Query OK, 0 rows affected (0.03 sec)
//查看当前表格
mysql> desc intal;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
//插入多条语句
mysql> insert into intal values(01,"zl");
Query OK, 1 row affected (0.01 sec)
//查看当前表格
mysql> select * from intal;
+----+------+
| id | name |
+----+------+
| 1 | zl |
+----+------+
1 row in set (0.00 sec)
//插入增值“name”
mysql> insert into intal(name) values("zhangli");
Query OK, 1 row affected (0.00 sec)
mysql> select * from intal;
+----+---------+
| id | name |
+----+---------+
| 1 | zl |
| 2 | zhangli |
+----+---------+
2 rows in set (0.00 sec)
2）查看表格
查看表格内容
//查看所有表格
mysql> show tables;
+-----------------+
| Tables_in_anruo |
+-----------------+
| anruo |
| intal |
| wugen |
+-----------------+
3 rows in set (0.00 sec)
//查看当前表格
mysql> desc intal;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
//插入多条语句
mysql> insert into intal values(01,"zl");
Query OK, 1 row affected (0.01 sec)
//查看当前表格
mysql> select * from intal;
+----+------+
| id | name |
+----+------+
| 1 | zl |
+----+------+
1 row in set (0.00 sec)
3、获得表的SQL脚本
show create table info \G; # 获得表的SQL脚本
//# 获得表的SQL脚本:show create table info \G;
mysql> show create table anruo \G;
*************************** 1. row ***************************
Table: anruo
Create Table: CREATE TABLE `anruo` (
`id` int(11) NOT NULL,
`name` varchar(10) DEFAULT NULL,
`age` int(11) NOT NULL,
`sex` varchar(5) DEFAULT 'girl',
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>4、
1）INSERT（插入）
insert into user values # 插入多条语句
insert into userImfo select * from user； # 批量插入表格
