MySQL基础

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)