- 登录数据库
mysql -u username -p password
1 增
1.0 创建数据库
mysql>create database test_database character set utf8 collate utf8_general_ci;
- 结果
Query OK, 1 row affected (0.06 sec)
1.2 创建数据表
# 进入数据库
mysql>use test_database;
# 创建表info
mysql>create table if not exists `info`(
-> `id` int unsigned auto_increment,
-> `name` varchar(100) not null,
-> `address` varchar(100) not null,
-> primary key(`id`)
-> )engine=Innodb default charset=utf8;
- 结果
Query OK, 0 rows affected (0.44 sec)
- 添加表数据
mysql>insert into info
-> (name, address)
->values
->("xiaohong", "hegang");
- 增加表字段
1.3 新增用户
mysql>create user 'test'@'%' identified by '123456';
| 序号 | 参数 | 说明 |
|---|---|---|
| 1 | test | 用户名 |
| 2 | % | 主机host,若允许所有其他主机连接,使用%,指定主机则使用具体IP |
| 3 | 123456 | 用户密码 |
1.4 新增授权
mysql>grant all on *.* to 'test'@'%'
| 序号 | 参数 | 说明 |
|---|---|---|
| 1 | all | 所有授权,其他为select,insert,update |
| 2 | . | 允许操作的数据库和数据表,左第一个*为数据库名,第二个*为数据表名,若为*则表示所有库和表 |
| 3 | test | 授权的用户 |
1.5 新增数据库同步功能
mysql>grant select,replication slave, repliaction client on *.* to 'test'@'%';
# 授权生效
flush privileges;
| 序号 | 参数 | 说明 |
|---|---|---|
| 1 | replication slave | slave授权 |
| 2 | replication client | slave客户端授权 |
| 3 | . | 允许操作的数据库和数据表,左第一个*为数据库名,第二个*为数据表名,若为*则表示所有库和表 |
| 4 | test | 授权的用户 |
| 5 | % | 所有主机可连接mysql |
1.6 执行sql文件
mysql -u root -p
mysql>source /path/file_name.sql
1.7 数据表新增字段
- CMD
alter table table_name add item_name format null_or_not comment '描述';
- Usage
alter table school add area INT not null comment '区域';
2 删
2.1 删除数据库
- 清空表，数据按删除前的最大id递增
drop database database_name;
- 清空表，并重建表，数据从1开始
delete from table_name;
2.2 删除表
drop table info;
2.3 清空表内容
truncate testTables;
- 清除含有外键的表内容
# 解除外键约束
set foreign_key_checks=0;
# 清空内容
truncate testTables;
# 恢复外键
set foreign_key_checks=1;
2.4 删除某条信息
mysql>delete from table_name where id=1
2.5 删除某个字段
- CMD
alter table table_name drop property_name;
- Usage
alter table userinfos drop email;
3 改
3.1 插入数据
mysql> insert into info
-> (name, address)
-> values
-> ("小三三", "沈阳");
- 结果
Query OK, 1 row affected (0.09 sec)
3.2 更新行数据
update table_name set item="test" where id=2;
3.3 修改用户密码
mysql>set password for 'test'@'%' = password("123456");
3.4 修改数据库事务隔离级别
- 未提交读
set session transaction isolation level read uncommitted;
- 提交读
set session transaction isolation level read committed;
- 可重复读(MySQL默认)
set session transaction isolation level repeatable read
- 串行化
set session transaction isolation level serializable;
-
修改数据库名称
-
修改表名称
3.5 修改表字段名称
- CMD
alter table table_name change older_property newer_property;
- Usage
alter table userinfos change email emails;
3.6 修改表字段类型
- CMD
alter table table_name modify property format(length);
- Usage
alter table userinfos modify emails VARCHAR(50);
4 查
4.1 查询数据库引擎
mysql>show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
4.2 查询表结构
# 进入数据库test_database
mysql>use test_database;
# 查看表info结构
mysql>desc info;
- 结果
desc info;
+---------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(100) | NO | | NULL | |
| address | varchar(100) | NO | | NULL | |
+---------+------------------+------+-----+---------+----------------+
4.3 查看表编码方式
mysql>show create table knowledge;
- 结果
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| knowledge | CREATE TABLE `knowledge` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`question` varchar(320) NOT NULL,
`answer` varchar(320) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `question` (`question`),
UNIQUE KEY `answer` (`answer`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
4.4 单表查询
4.4.1 查询所有数据
mysql> select * from info;
- 结果
+----+-----------+---------+
| id | name | address |
+----+-----------+---------+
| 1 | 小三三 | 沈阳 |
+----+-----------+---------+
1 row in set (0.00 sec)
4.4.2 排序查询
mysql> select * from info order by id;
4.4.3 分段查询
mysql> select * from info limit offset,num;
其中,offset为偏移量(索引),从第offset行开始查询(从0开始记录行),num为查询的数量.
4.5 多表查询
mysql>select * from table_a, table_b;
4.6 统计查询
统计满足条件的数据行数,忽略值为null的行.
mysql>select count(*) from info;
- 结果
+----------+
| count(*) |
+----------+
| 3 |
+----------+
1 row in set (0.00 sec)
4.7 查看binlog
mysql> show binlog events in 'mysql-binlog.000020';
- 结果
+---------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-binlog.000020 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.27-0ubuntu0.18.04.1-log, Binlog ver: 4 |
| mysql-binlog.000020 | 123 | Previous_gtids | 1 | 154 | |
| mysql-binlog.000020 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-binlog.000020 | 219 | Query | 1 | 383 | create database test_database character set utf8 collate utf8_general_ci |
| mysql-binlog.000020 | 383 | Anonymous_Gtid | 1 | 448 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-binlog.000020 | 448 | Query | 1 | 725 | use `test_database`; create table if not exists `info`(
`id` int unsigned auto_increment,
`name` varchar(100) not null,
`address` varchar(100) not null,
primary key(`id`)
)engine=Innodb default charset=utf8 |
| mysql-binlog.000020 | 725 | Anonymous_Gtid | 1 | 790 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-binlog.000020 | 790 | Query | 1 | 871 | BEGIN |
| mysql-binlog.000020 | 871 | Table_map | 1 | 933 | table_id: 109 (test_database.info) |
| mysql-binlog.000020 | 933 | Write_rows | 1 | 992 | table_id: 109 flags: STMT_END_F |
| mysql-binlog.000020 | 992 | Xid | 1 | 1023 | COMMIT /* xid=32 */ |
| mysql-binlog.000020 | 1023 | Anonymous_Gtid | 1 | 1088 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-binlog.000020 | 1088 | Query | 1 | 1169 | BEGIN |
| mysql-binlog.000020 | 1169 | Table_map | 1 | 1231 | table_id: 109 (test_database.info) |
| mysql-binlog.000020 | 1231 | Write_rows | 1 | 1287 | table_id: 109 flags: STMT_END_F |
| mysql-binlog.000020 | 1287 | Xid | 1 | 1318 | COMMIT /* xid=34 */ |
| mysql-binlog.000020 | 1318 | Anonymous_Gtid | 1 | 1383 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-binlog.000020 | 1383 | Query | 1 | 1464 | BEGIN |
| mysql-binlog.000020 | 1464 | Table_map | 1 | 1526 | table_id: 109 (test_database.info) |
| mysql-binlog.000020 | 1526 | Write_rows | 1 | 1582 | table_id: 109 flags: STMT_END_F |
| mysql-binlog.000020 | 1582 | Xid | 1 | 1613 | COMMIT /* xid=36 */ |
+---------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
21 rows in set (0.00 sec)
4.8 查看主服务状态
mysql> show master status;
- 结果
+---------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+-------------------+
| mysql-binlog.000020 | 1613 | | | |
+---------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
4.9 查询用户
mysql>select user,host from mysql.user;
- 结果
+------------------+-----------+
| user | host |
+------------------+-----------+
| sync_user | % |
| debian-sys-maint | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| phpmyadmin | localhost |
| root | localhost |
+------------------+-----------+
6 rows in set (0.12 sec)
4.10 查看事务隔离级别
mysql>select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
4.11 数据拼接
MySQL引擎InnoDB数据按列存储，搜索时，返回的数据为二维表，行列形式，若需要合并多个列的数据，需要使用concat_ws()
- 语法
select concat_ws(separator, property-1, property-2, ..., property-n) from table;
- separator
分隔符 - property-1
字段1 - property-n
字段n
select concat_ws(',',name,uid) as name_id from study.users;
mysql> select concat_ws(',', name, uid) from users;
+---------------------------+
| concat_ws(',', name, uid) |
+---------------------------+
| xiaxoa,123456 |
| xiaxoa,123456 |
| xiaxoa,123456 |
| 小测试,123456 |
+---------------------------+
5 登录服务器数据库
mysql -h x.x.x.x -P xxxx -u root -p
# -h 为主机IP
# -P 为数据库端口
# -u 为用户名
# -p 为数据库密码
6 查询数据库用户
select distinct concat('User:''',user,'''@''',host,''';') AS query FROM mysql.user;
# 查询结果
+--------------------------------------+
| query |
+--------------------------------------+
| User:'root'@'%'; |
| User:'copm_spider'@'localhost'; |
| User:'debian-sys-maint'@'localhost'; |
| User:'mysql.session'@'localhost'; |
| User:'mysql.sys'@'localhost'; |
+--------------------------------------+
select user, host from mysql.user;
7 数据库运行状态
- 运行状态
service mysql status
或
/etc/init.d/mysql status
# 结果
mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: en
Active: active (running) since Wed 2018-11-07 11:28:52 CST; 1 weeks 1 days ag
Main PID: 858 (mysqld)
Tasks: 31 (limit: 4915)
CGroup: /system.slice/mysql.service
└─858 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid
11月 07 11:28:33 xdq systemd[1]: Starting MySQL Community Server...
11月 07 11:28:52 xdq systemd[1]: Started MySQL Community Server.
- 启动
sudo /etc/init.d/mysql start
- 停止
- 重启
- 查看日志
- 支持中文设置
8 端口查询
show variables like "port";
mysql> show global variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 3306 |
+---------------+-------+
1 row in set (0.33 sec)
11 远程连接mysql
注释或删除:bind-address
sudo vim /etc/mysql/mysql.conf.d
[参考文献]
[1]https://www.cnblogs.com/walter371/p/4113196.html
[2]https://www.cnblogs.com/skyWings/p/5952795.html
[3]https://blog.csdn.net/a18373279153/article/details/78604174
[4]https://www.jb51.net/article/51413.htm
[5]https://blog.csdn.net/hang916/article/details/79444994
[6]https://blog.csdn.net/qq_37996815/article/details/78934536
[7]https://www.cnblogs.com/sos-blue/p/6852945.html
[8]https://www.cnblogs.com/shihaiming/p/11044740.html
[9]https://www.cnblogs.com/fwqblogs/p/6641287.html
[10]https://www.cnblogs.com/xbxxf/p/9174028.html
112
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
