查看版本号和当前日期
# 直接登录MySQL，能看到版本信息
[root@mysql ~]# mysql -uroot -p'sc123456'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.37 MySQL Community Server (GPL)
Copyright (c) 2000, 2022, 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.
root@(none) 00:24 mysql>
root@(none) 16:31 mysql>select version(),current_date;
+-----------+--------------+
| version() | current_date |
+-----------+--------------+
| 5.7.41 | 2023-04-18 |
+-----------+--------------+
1 row in set (0.00 sec)
root@song 10:22 mysql>select now();
+---------------------+
| now() |
+---------------------+
| 2023-04-19 10:22:39 |
+---------------------+
1 row in set (0.00 sec)查看当前使用的库和数据库服务的基本信息
root@song 10:15 mysql>select database();
+------------+
| database() |
+------------+
| song |
+------------+
1 row in set (0.00 sec)
root@song 11:45 mysql>status
--------------
mysql Ver 14.14 Distrib 5.7.41, for linux-glibc2.12 (x86_64) using EditLine wrapper
Connection id: 17
Current database: song
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.41 MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8mb4
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /data/mysql/mysql.sock
Uptime: 23 hours 4 min 53 sec
Threads: 3 Questions: 623 Slow queries: 0 Opens: 208 Flush tables: 1 Open tables: 182 Queries per second avg: 0.007
--------------简单的计算器
root@(none) 16:38 mysql>select sin(pi()/4),(4+1)*5;
+--------------------+---------+
| sin(pi()/4) | (4+1)*5 |
+--------------------+---------+
| 0.7071067811865475 | 25 |
+--------------------+---------+
1 row in set (0.00 sec)查看所有的库
root@(none) 18:57 mysql>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
# 创建库
root@(none) 18:58 mysql>create database sanchuang;
Query OK, 1 row affected (0.00 sec)
root@(none) 18:58 mysql>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sanchuang |
| sys |
+--------------------+
5 rows in set (0.00 sec)
# 使用库
root@(none) 18:58 mysql>use sanchuang;
Database changed
root@sanchuang 18:58 mysql>show tables;
Empty set (0.00 sec)
# 创建表
root@sanchuang 18:58 mysql>create table t1(id int,name varchar(20));
Query OK, 0 rows affected (0.00 sec)
root@sanchuang 18:59 mysql>show tables;
+---------------------+
| Tables_in_sanchuang |
+---------------------+
| t1 |
+---------------------+
1 row in set (0.00 sec)
# 查看表的信息
root@sanchuang 18:59 mysql>desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
root@sanchuang 19:01 mysql>库本质上是一个文件夹
表本质上是一个文件
[root@mysql mysql]# cd sc
[root@mysql sc]# ls
db.opt student_info.frm student_info.ibddb.opt database option 数据库选项 --》告诉我们这个库使用的字符集是什么
student_info.frm --》表结构文件 frame 框架
student_info.ibd --》表的数据和索引 innodeb data --》mysql默认使用的存储引擎是innodb：将mysql内存里的数据存放到磁盘，将磁盘里的数据读取到内存
索引：Index 也是数据，描述数据的数据，告诉我们数据存放在哪里（帮助我们快速的查询到数据，提升查询的效率）
存储引擎捆绑到表上的
root@sc 17:19 mysql>create table wei(id int,name varchar(10)) engine=myisam;
wei.frm --》表结构文件 frame 框架
wei.MYD --》myisam存储引擎存放数据的 data
wei.MYI --》myisam存储引擎存放索引 index
[root@mysql sc]# cat db.opt
default-character-set=utf8 # 默认字符集
default-collation=utf8_general_ci # 默认字符集对应的校对规则：排序的时候使用
查看所有的引擎
root@sanchuang 19:05 mysql>show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)查看创建t1表的过程
root@sanchuang 11:59 mysql>show create table t1;
+-------+-------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)查看所有连接的用户
root@(none) 12:12 mysql>show processlist;
+----+-------+---------------------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------+---------------------+------+---------+------+----------+------------------+
| 3 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 5 | hanwl | 192.168.102.1:60430 | NULL | Sleep | 34 | | NULL |
| 6 | hanwl | 192.168.102.1:60431 | NULL | Sleep | 35 | | NULL |
+----+-------+---------------------+------+---------+------+----------+------------------+
3 rows in set (0.00 sec)
root@(none) 12:20 mysql>查看user表的详细信息
root@mysql 12:24 mysql>desc user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
root@mysql 12:25 mysql>select user,host from user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| hanwl | % |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)
root@mysql 12:26 mysql>在mysql里使用Linux命令
root@(none) 13:15 mysql>system ls /data/mysql;
auto.cnf ib_buffer_pool mysql performance_schema server-key.pem
ca-key.pem ibdata1 mysql.err private_key.pem sys
ca.pem ib_logfile0 mysql.pid public_key.pem
client-cert.pem ib_logfile1 mysql.sock sanchuang
client-key.pem ibtmp1 mysql.sock.lock server-cert.pem查看帮助文档
root@song 10:32 mysql>help create table
Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options]
[partition_options]
[IGNORE | REPLACE]
[AS] query_expression
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }
create_definition: {
col_name column_definition
| {INDEX | KEY} [index_name] [index_type] (key_part,...)
[index_option] ...
| {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name] (key_part,...)
[index_option] ...
| [CONSTRAINT [symbol]] PRIMARY KEY
[index_type] (key_part,...)
[index_option] ...
| [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
[index_name] [index_type] (key_part,...)
[index_option] ...
| [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (col_name,...)
reference_definition
| CHECK (expr)
}
column_definition: {
data_type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
[COMMENT 'string']
[COLLATE collation_name]
[COLUMN_FORMAT {FIXED | DYNAMIC | DEFAULT}]
[STORAGE {DISK | MEMORY}]
[reference_definition]
| data_type
[COLLATE collation_name]
[GENERATED ALWAYS] AS (expr)
[VIRTUAL | STORED] [NOT NULL | NULL]
[UNIQUE [KEY]] [[PRIMARY] KEY]
[COMMENT 'string']
[reference_definition]
}
data_type:
(see https://dev.mysql.com/doc/refman/5.7/en/data-types.html)
key_part:
col_name [(length)] [ASC | DESC]
index_type:
USING {BTREE | HASH}
index_option: {
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
| COMMENT 'string'
}
reference_definition:
REFERENCES tbl_name (key_part,...)
[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
table_options:
table_option [[,] table_option] ...
table_option: {
AUTO_INCREMENT [=] value
| AVG_ROW_LENGTH [=] value
| [DEFAULT] CHARACTER SET [=] charset_name
| CHECKSUM [=] {0 | 1}
| [DEFAULT] COLLATE [=] collation_name
| COMMENT [=] 'string'
| COMPRESSION [=] {'ZLIB' | 'LZ4' | 'NONE'}
| CONNECTION [=] 'connect_string'
| {DATA | INDEX} DIRECTORY [=] 'absolute path to directory'
| DELAY_KEY_WRITE [=] {0 | 1}
| ENCRYPTION [=] {'Y' | 'N'}
| ENGINE [=] engine_name
| INSERT_METHOD [=] { NO | FIRST | LAST }
| KEY_BLOCK_SIZE [=] value
| MAX_ROWS [=] value
| MIN_ROWS [=] value
| PACK_KEYS [=] {0 | 1 | DEFAULT}
| PASSWORD [=] 'string'
| ROW_FORMAT [=] {DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT}
| STATS_AUTO_RECALC [=] {DEFAULT | 0 | 1}
| STATS_PERSISTENT [=] {DEFAULT | 0 | 1}
| STATS_SAMPLE_PAGES [=] value
| TABLESPACE tablespace_name [STORAGE {DISK | MEMORY}]
| UNION [=] (tbl_name[,tbl_name]...)
}
partition_options:
PARTITION BY
{ [LINEAR] HASH(expr)
| [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list)
| RANGE{(expr) | COLUMNS(column_list)}
| LIST{(expr) | COLUMNS(column_list)} }
[PARTITIONS num]
[SUBPARTITION BY
{ [LINEAR] HASH(expr)
| [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list) }
[SUBPARTITIONS num]
]
[(partition_definition [, partition_definition] ...)]
partition_definition:
PARTITION partition_name
[VALUES
{LESS THAN {(expr | value_list) | MAXVALUE}
|
IN (value_list)}]
[[STORAGE] ENGINE [=] engine_name]
[COMMENT [=] 'string' ]
[DATA DIRECTORY [=] 'data_dir']
[INDEX DIRECTORY [=] 'index_dir']
[MAX_ROWS [=] max_number_of_rows]
[MIN_ROWS [=] min_number_of_rows]
[TABLESPACE [=] tablespace_name]
[(subpartition_definition [, subpartition_definition] ...)]
subpartition_definition:
SUBPARTITION logical_name
[[STORAGE] ENGINE [=] engine_name]
[COMMENT [=] 'string' ]
[DATA DIRECTORY [=] 'data_dir']
[INDEX DIRECTORY [=] 'index_dir']
[MAX_ROWS [=] max_number_of_rows]
[MIN_ROWS [=] min_number_of_rows]
[TABLESPACE [=] tablespace_name]
query_expression:
SELECT ... (Some valid select or union statement)
CREATE TABLE creates a table with the given name. You must have the
CREATE privilege for the table.
By default, tables are created in the default database, using the
InnoDB storage engine. An error occurs if the table exists, if there is
no default database, or if the database does not exist.
MySQL has no limit on the number of tables. The underlying file system
may have a limit on the number of files that represent tables.
Individual storage engines may impose engine-specific constraints.
InnoDB permits up to 4 billion tables.
For information about the physical representation of a table, see
https://dev.mysql.com/doc/refman/5.7/en/create-table-files.html.
URL: https://dev.mysql.com/doc/refman/5.7/en/create-table.html
mysqld_safe 父进程，mysqld_safe 管理mysqld
mysqld 子进程，mysqld是工作进程
mysql 客户端进程
安装pstree
yum install psmisc -y
[root@mysql mysql]# pstree -p
systemd(1)─┬─NetworkManager(6069)─┬─{NetworkManager}(6155)
│ └─{NetworkManager}(6170)
├─VGAuthService(6076)
├─auditd(5318)───{auditd}(5319)
├─chronyd(6058)
├─containerd(6657)─┬─{containerd}(6918)
│ ├─{containerd}(6927)
│ ├─{containerd}(6931)
│ ├─{containerd}(6933)
│ ├─{containerd}(6934)
│ ├─{containerd}(7020)
│ ├─{containerd}(7021)
│ └─{containerd}(17706)
├─crond(6134)
├─dbus-daemon(6053)
├─dockerd(7023)─┬─{dockerd}(7070)
│ ├─{dockerd}(7071)
│ ├─{dockerd}(7072)
│ ├─{dockerd}(7073)
│ ├─{dockerd}(7074)
│ ├─{dockerd}(7161)
│ └─{dockerd}(7339)
├─gssproxy(6094)─┬─{gssproxy}(6115)
│ ├─{gssproxy}(6116)
│ ├─{gssproxy}(6117)
│ ├─{gssproxy}(6118)
│ └─{gssproxy}(6119)
├─irqbalance(6085)
├─login(6669)───bash(16872)
├─lvmetad(3157)
├─master(7094)─┬─pickup(17710)
│ └─qmgr(7107)
├─mysqld_safe(6663)───mysqld(6996)─┬─{mysqld}(7226)
│ ├─{mysqld}(7328)
│ ├─{mysqld}(7329)
│ ├─{mysqld}(7330)
│ ├─{mysqld}(7331)
│ ├─{mysqld}(7332)
│ ├─{mysqld}(7333)
│ ├─{mysqld}(7334)
│ ├─{mysqld}(7335)
│ ├─{mysqld}(7336)
│ ├─{mysqld}(7337)
│ ├─{mysqld}(7341)
│ ├─{mysqld}(7561)
│ ├─{mysqld}(7562)
│ ├─{mysqld}(7563)
│ ├─{mysqld}(7564)
│ ├─{mysqld}(7569)
│ ├─{mysqld}(7570)
│ ├─{mysqld}(7571)
│ ├─{mysqld}(7572)
│ ├─{mysqld}(7610)
│ ├─{mysqld}(7611)
│ ├─{mysqld}(7612)
│ ├─{mysqld}(7619)
│ ├─{mysqld}(7668)
│ ├─{mysqld}(7669)
│ ├─{mysqld}(7712)
│ ├─{mysqld}(7713)
│ ├─{mysqld}(17345)
│ └─{mysqld}(17516)
├─polkitd(6082)─┬─{polkitd}(6141)
│ ├─{polkitd}(6158)
│ ├─{polkitd}(6159)
│ ├─{polkitd}(6160)
│ ├─{polkitd}(6161)
│ └─{polkitd}(6179)
├─rpc.idmapd(5324)
├─rpc.mountd(6691)
├─rpc.statd(6652)
├─rpcbind(6052)
├─rsyslogd(6644)─┬─{rsyslogd}(6676)
│ └─{rsyslogd}(6677)
├─sshd(6633)─┬─sshd(17474)───bash(17476)───mysql(17737)
│ └─sshd(17493)───bash(17495)─┬─ping(17626)
│ └─pstree(17738)
├─systemd-journal(3134)
├─systemd-logind(6087)
├─systemd-udevd(3171)
├─tuned(6635)─┬─{tuned}(7246)
│ ├─{tuned}(7247)
│ ├─{tuned}(7251)
│ └─{tuned}(7310)
└─vmtoolsd(6083)───{vmtoolsd}(6194)
shell里操作MySQL
[root@mysql ~]# mysql -uroot -p'Sanchuang1234#' -e "show databases;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| chen |
| hunan |
| mysql |
| performance_schema |
| song |
| sys |
+--------------------+
[root@mysql ~]# mysql -uroot -p'Sanchuang1234#' -e "show databases;use hunan;select user,host from user2;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| chen |
| hunan |
| mysql |
| performance_schema |
| song |
| sys |
+--------------------+
+---------------+-----------+
| user | host |
+---------------+-----------+
| hanwl | % |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
[root@mysql ~]# mysql -uroot -p'Sanchuang1234#' -e "show databases;use hunan;select user,host from user2;"|grep "han"
mysql: [Warning] Using a password on the command line interface can be insecure.
hanwl %
[root@mysql ~]# mysql -uroot -p'Sanchuang1234#' -e "show databases;use hunan;select user,host from user2;" 2>/dev/null|grep "han"
hanwl %
[root@mysql ~]# mysql -uroot -p'Sanchuang1234#' -e "show databases;use hunan;select user,host from user2;" 2>/dev/null|grep "han" |awk '{print $1}'
hanwl
[root@mysql mysql]# cat test_mysql.sh
#!/bin/bash
mysql -uroot -p'Sanchuang1234#' <<EOF
create database li;
use li;
create table t1(id int);
insert into t1(id) values(1),(2),(3);
exit
EOF
echo $?
echo "插入数据成功"
[root@master mysql]# bash test_mysql.sh
mysql: [Warning] Using a password on the command line interface can be insecure.
0
插入数据成功
root@hunan 17:54 mysql>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| chen |
| hunan |
| li |
| mysql |
| performance_schema |
| song |
| sys |
+--------------------+
8 rows in set (0.00 sec)
扫一扫
1441
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
