数据库和表格查询
-- 列出所有数据库
SHOW DATABASES;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set
-- 查看某一个数据库里所有的表
USE mysql;
SHOW TABLES;
-- 或者
SHOW TABLES FROM mysql;
mysql> use mysql;
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
SELECT 特殊应用
-- select
SELECT NOW(); -- 查看时间docker run要指定时区
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2023-10-22 13:12:31 |
+---------------------+
1 row in set
-- 查看当前选择(use)的是哪个库
select database();
mysql> select database();
+------------+
| database() |
+------------+
| mysql |
+------------+
1 row in set
-- 版本
select version();
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.26 |
+-----------+
1 row in set
-- 查看当前登录数据库的用户
select user();
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set
-- 查看数据路径
select @@datadir;
-- 查看 mysql 安装路径
select @@basedir;
-- 查看 mysql 安装在什么系统上
select @@version_compile_os;
SELECT 查询数据
-- SELECT 查询关键字
select * from mysql.user; -- *代表查询所有字段
select user,host from mysql.user; -- 查询 user 和 host 字段
mysql> select * from mysql.user;
+-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked |
+-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| localhost | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | N | 2019-05-06 13:47:14 | NULL | N |
| localhost | mysql.session | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N | 2019-05-06 13:42:05 | NULL | Y |
| localhost | mysql.sys | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N | 2019-05-06 13:42:05 | NULL | Y |
+-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
3 rows in set
WHERE 条件（AND 和 OR）
select user,host from mysql.user where user = 'root';
UNION 联合查询
SELECT USER,HOST FROM mysql.user UNION SELECT * FROM test.t1; -- 合并查询结果
SELECT * FROM test.t1 UNION SELECT 1,2; -- 判断字段数量
数据库操作
创建库
CREATE DATABASE test CHARSET utf8mb4;
创建表
USE test; -- 切换数据库
CREATE TABLE t1(id INT); -- 创建表,添加 id 字段
DROP TABLE t1; -- 删除表
ALTER TABLE t1 ADD name VARCHAR(32); -- 添加 name 字段
DESC t1; -- 查询字段信息
插入数据
INSERT INTO t1 VALUES (1,'张三'),(2,'李四'),(3,'王五');
SELECT * FROM t1;
SCHEMATA 表
show databases;
-- 的结果来自于 information_schema.schemata
select schema_name from information_schema.schemata;
TABLES 和 COLUMNS 表
SELECT DATABASE(); -- 返回当前选择的库
SELECT * FROM information_schema.tables WHERE table_schema = 'test';
SELECT * FROM information_schema.columns WHERE table_schema = 'test';
mysql> SELECT * FROM information_schema.tables WHERE table_schema = 'test';
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------------+
| def | test | t1 | BASE TABLE | MyISAM | 10 | Dynamic | 11 | 20 | 220 | 281474976710655 | 1024 | 0 | NULL | 2023-10-23 20:51:33 | 2023-10-23 21:01:30 | NULL | utf8mb4_general_ci | NULL | | |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------------+
1 row in set
mysql> SELECT * FROM information_schema.columns WHERE table_schema = 'test';
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+--------------------+-------------+------------+-------+---------------------------------+----------------+-----------------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME | COLUMN_TYPE | COLUMN_KEY | EXTRA | PRIVILEGES | COLUMN_COMMENT | GENERATION_EXPRESSION |
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+--------------------+-------------+------------+-------+---------------------------------+----------------+-----------------------+
| def | test | t1 | id | 1 | NULL | YES | int | NULL | NULL | 10 | 0 | NULL | NULL | NULL | int(11) | | | select,insert,update,references | | |
| def | test | t1 | name | 2 | NULL | YES | varchar | 32 | 128 | NULL | NULL | NULL | utf8mb4 | utf8mb4_general_ci | varchar(32) | | | select,insert,update,references | | |
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+--------------------+-------------+------------+-------+---------------------------------+----------------+-----------------------+
2 rows in set
浙公网安备 33010602011771号