MySQL基础
MySQL常用命令
查看数据库常用命令
show databases; 查看所有数据库
show tables; 查看数据库中的表
use 数据库; 切换数据库
select 表名 from 数据库; 从数据库中查看表
select 表名 from 数据库 where 条件; 从数据库筛选符合条件的表
select now(); 查看时间
select database(); 查看当前所在数据库
select @@datadir; 查看数据路径
select @@basedir; 查看数据库安装路径
select @@version_compile_os; 查看安装系统
# 查看数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
# 切换数据库
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
# 查看数据库中的表
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.00 sec)
# 从msyql数据库中的user表中查看数据
mysql> select user,host from mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| root | % |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)
# 加上where语句
mysql> select user,host from mysql.user where host='%';
+------+------+
| user | host |
+------+------+
| root | % |
+------+------+
1 row in set (0.00 sec)
# 查看时间
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2023-02-27 09:12:41 |
+---------------------+
1 row in set (0.00 sec)
# 查看当前所在数据库
mysql> select database();
+------------+
| database() |
+------------+
| mysql |
+------------+
1 row in set (0.00 sec)
# 查看数据路径
mysql> select @@datadir;
+-----------------+
| @@datadir |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
1 row in set (0.00 sec)
# 查看mysql安装路径
mysql> select @@basedir;
+-----------+
| @@basedir |
+-----------+
| /usr/ |
+-----------+
1 row in set (0.00 sec)
# 查看安装系统
mysql> select @@version_compile_os;
+----------------------+
| @@version_compile_os |
+----------------------+
| Linux |
+----------------------+
1 row in set (0.00 sec)
创建、删除、修改数据库的常用命令
create database 数据库名 charset 编码名; 创建数据库
create table 表名(数据名 数据类型); 创建表
drop table 表名; 删除表
alter table 表名 add 数据名 数据类型 添加字段
insert into 表名 values(数据) 插入数据
# 创建数据库
mysql> create database test charset utf8mb4;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
# 创建表
mysql> create table t1(id int);
Query OK, 0 rows affected (0.03 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
+----------------+
1 row in set (0.00 sec)
# 删除表
mysql> drop table t1;
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
Empty set (0.00 sec)
# 查看表
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
+----------------+
1 row in set (0.00 sec)
# 在表中添加字段
mysql> alter table t1 add name varchar(32);
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(32) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
# 向表中插入数据
mysql> insert into t1 values(1,"alice"),(2,"jack"),(3,"alan");
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+------+-------+
| id | name |
+------+-------+
| 1 | alice |
| 2 | jack |
| 3 | alan |
+------+-------+
3 rows in set (0.00 sec)