常用登陆参数
# 常用参数：
-h：--host=name 服务器名称
-D：--database=name 打开指定数据库
-p：--password[=name] 密码
-u：--user=name 用户名
-P：--port=# 端口号
-V：--version 输出版本信息
--prompt=name 设置提示符
# 常用命令：
mysql -u root -p # 默认登陆本机部署的数据库
mysql -u root -p -h 127.0.0.1 -P 3306 # 指定数据地址并指明端口
数据库操作
# 创建库
mysql> create database test;
Query OK, 1 row affected (0.17 sec)
# 查看库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
| zabbix |
+--------------------+
6 rows in set (0.00 sec)
# 删除库
mysql> drop database test;
Query OK, 0 rows affected (0.04 sec)
# 修改库
# 举例：修改test数据库的字符编码
mysql> alter database test character set 'gbk';
Query OK, 1 row affected (0.01 sec)
# 查看数据仓库的编码
mysql> show create database test;
+----------+-------------------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------------------------------------+
| test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET gbk */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+-------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
# 切换数据库
mysql> use test;
Database changed
# 查看当前数据库
mysql> select database();
+------------+
| database() |
+------------+
| test |
+------------+
1 row in set (0.00 sec)
Mysql数据类型
数值类型
日期时间类型
字符串类型
数据表CURD
创建数据表
语法：
CREATE TABLE table_name (column_name column_type);
例：
CREATE TABLE `user` (
`id` int(11) NOT NULL,
`username` varchar(50) NOT NULL COMMENT '用户名',
`password` varchar(80) NOT NULL COMMENT '密码',
`describe` text COMMENT '描述',
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated_at` datetime COMMENT '修改时间',
`is_banned` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否禁用',
`is_deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否逻辑删除',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# 查询当前数据库所有数据表
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| user |
+----------------+
1 row in set (0.01 sec)
# 查询表结构
mysql> desc user;
+------------+-------------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+-------------------+-------------------+
| id | int | NO | PRI | NULL | |
| username | varchar(50) | NO | | NULL | |
| password | varchar(80) | NO | | NULL | |
| describe | text | YES | | NULL | |
| created_at | datetime | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| updated_at | datetime | YES | | NULL | |
| is_banned | tinyint(1) | NO | | 0 | |
| is_deleted | tinyint(1) | NO | | 0 | |
+------------+-------------+------+-----+-------------------+-------------------+
8 rows in set (0.00 sec)
修改表结构
语法：
ALTER TABLE <表名> [修改选项]
# 增加字段
语法：ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] [FIRST|AFTER 已存在的字段名]
例：
mysql> alter table user add iphone int(11) AFTER `password`;
Query OK, 0 rows affected, 1 warning (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 1
mysql> desc user;
+------------+-------------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+-------------------+-------------------+
| id | int | NO | PRI | NULL | |
| username | varchar(50) | NO | | NULL | |
| password | varchar(80) | NO | | NULL | |
| iphone | int | YES | | NULL | |
| describe | text | YES | | NULL | |
| created_at | datetime | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| updated_at | datetime | YES | | NULL | |
| is_banned | tinyint(1) | NO | | 0 | |
| is_deleted | tinyint(1) | NO | | 0 | |
+------------+-------------+------+-----+-------------------+-------------------+
9 rows in set (0.01 sec)
# 修改字段
语法：ALTER TABLE <表名> MODIFY <字段名> <数据类型>
例：
mysql> alter table user modify iphone varchar(11);
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user;
+------------+-------------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+-------------------+-------------------+
| id | int | NO | PRI | NULL | |
| username | varchar(50) | NO | | NULL | |
| password | varchar(80) | NO | | NULL | |
| iphone | varchar(11) | YES | | NULL | |
| describe | text | YES | | NULL | |
| created_at | datetime | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| updated_at | datetime | YES | | NULL | |
| is_banned | tinyint(1) | NO | | 0 | |
| is_deleted | tinyint(1) | NO | | 0 | |
+------------+-------------+------+-----+-------------------+-------------------+
9 rows in set (0.00 sec)
# 删除字段
语法：ALTER TABLE <表名> DROP <字段名>；
例：
mysql> alter table user drop iphone;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user;
+------------+-------------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+-------------------+-------------------+
| id | int | NO | PRI | NULL | |
| username | varchar(50) | NO | | NULL | |
| password | varchar(80) | NO | | NULL | |
| describe | text | YES | | NULL | |
| created_at | datetime | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| updated_at | datetime | YES | | NULL | |
| is_banned | tinyint(1) | NO | | 0 | |
| is_deleted | tinyint(1) | NO | | 0 | |
+------------+-------------+------+-----+-------------------+-------------------+
8 rows in set (0.00 sec)
# 修改字段名
语法：ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>；
例：
mysql> alter table user change `describe` `desc` text COMMENT '描述';
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user;
+------------+-------------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+-------------------+-------------------+
| id | int | NO | PRI | NULL | |
| username | varchar(50) | NO | | NULL | |
| password | varchar(80) | NO | | NULL | |
| desc | text | YES | | NULL | |
| created_at | datetime | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| updated_at | datetime | YES | | NULL | |
| is_banned | tinyint(1) | NO | | 0 | |
| is_deleted | tinyint(1) | NO | | 0 | |
+------------+-------------+------+-----+-------------------+-------------------+
8 rows in set (0.00 sec)
# 修改表名称
语法：ALTER TABLE <旧表名> RENAME [TO] <新表名>；
例：
mysql> alter table `user` rename to `users`;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| users |
+----------------+
1 row in set (0.00 sec)
添加表数据
语法1：
INSERT INTO table_name ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN );
例：
mysql> insert into `users` (`id`, `username`, `password`, `desc`) values (1, 'zabbix', 'pwd0', 'zabbix用户');
Query OK, 1 row affected (0.00 sec)
mysql> insert into `users` (`id`, `username`, `password`, `is_deleted`) values (2, 'xiaoming', 'pwdxiaoming', 1);
Query OK, 1 row affected (0.00 sec)
语法2：
INSERT INTO table_name VALUES ( value1, value2,...valueN );
mysql> insert into `users` values (3, 'xiaoli', 'pwdxiaoli', '小丽用户', '2022-03-23 10:40:35', '2022-03-23 10:40:35', 0, 0);
Query OK, 1 row affected (0.00 sec)
注意：两种方式的语法都可以执行，语法2注意写全
查看表数据
语法：select 去重选项 字段列表 [as 字段别名] from 数据源 [where子句] [group by 子句] [having子句] [order by 子句] [limit子句];
# 查看users表所有数据
mysql> select * from users;
+----+----------+-------------+--------------+---------------------+---------------------+-----------+------------+
| id | username | password | desc | created_at | updated_at | is_banned | is_deleted |
+----+----------+-------------+--------------+---------------------+---------------------+-----------+------------+
| 1 | zabbix | pwd0 | zabbix用户 | 2022-03-23 11:23:15 | NULL | 0 | 0 |
| 2 | xiaoming | pwdxiaoming | NULL | 2022-03-23 11:25:13 | NULL | 0 | 1 |
| 3 | xiaoli | pwdxiaoli | 小丽用户 | 2022-03-23 10:40:35 | 2022-03-23 10:40:35 | 0 | 0 |
+----+----------+-------------+--------------+---------------------+---------------------+-----------+------------+
# 查询全部的某几个字段
mysql> select username, password from users;
+----------+-------------+
| username | password |
+----------+-------------+
| zabbix | pwd0 |
| xiaoming | pwdxiaoming |
| xiaoli | pwdxiaoli |
+----------+-------------+
3 rows in set (0.00 sec)
# distinct 去重
mysql> select distinct is_deleted from users;
+------------+
| is_deleted |
+------------+
| 0 |
| 1 |
+------------+
2 rows in set (0.00 sec)
注意：多字段用,间隔开
# where语句
# where语句
基于值：
where 字段 =值 ；查找出对应字段等于对应值的记录。（相似的，<是小于对应值,<=是小于等于对应值,>是大于对应值,>=是大于等于对应值,!=是不等于），例如：where name = 'lilei'
like:where 字段 like 值 ;功能与 = 相似 ，但可以使用模糊匹配来查找结果。例如：where name like 'li%'
基于值的范围：
in: where 字段 in 范围;查找出对应字段的值在所指定范围的记录。例如：where age in (18,19,20)
not in : where 字段 not in 范围;查找出对应字段的值不在所指定范围的记录。例如：where age not in (18,19,20)
between x and y :where 字段 between x and y;查找出对应字段的值在闭区间[x,y]范围的记录。例如：where age between 18 and 20。
条件复合：
or ： where 条件1 or 条件2… ; 查找出符合条件1或符合条件2的记录。
and: where 条件1 and 条件2… ; 查找出符合条件1并且符合条件2的记录。
not : where not 条件1 ;查找出不符合条件的所有记录。
&&的功能与and相同；||与or功能类似，!与not 功能类似。
例
mysql> select * from users where username='zabbix';
+----+----------+----------+--------------+---------------------+------------+-----------+------------+
| id | username | password | desc | created_at | updated_at | is_banned | is_deleted |
+----+----------+----------+--------------+---------------------+------------+-----------+------------+
| 1 | zabbix | pwd0 | zabbix用户 | 2022-03-23 11:23:15 | NULL | 0 | 0 |
+----+----------+----------+--------------+---------------------+------------+-----------+------------+
mysql> select * from users where id>1;
+----+----------+-------------+--------------+---------------------+---------------------+-----------+------------+
| id | username | password | desc | created_at | updated_at | is_banned | is_deleted |
+----+----------+-------------+--------------+---------------------+---------------------+-----------+------------+
| 2 | xiaoming | pwdxiaoming | NULL | 2022-03-23 11:25:13 | NULL | 0 | 1 |
| 3 | xiaoli | pwdxiaoli | 小丽用户 | 2022-03-23 10:40:35 | 2022-03-23 10:40:35 | 0 | 0 |
+----+----------+-------------+--------------+---------------------+---------------------+-----------+------------+
mysql> select * from users where is_banned=0 and is_deleted=1;
+----+----------+-------------+------+---------------------+------------+-----------+------------+
| id | username | password | desc | created_at | updated_at | is_banned | is_deleted |
+----+----------+-------------+------+---------------------+------------+-----------+------------+
| 2 | xiaoming | pwdxiaoming | NULL | 2022-03-23 11:25:13 | NULL | 0 | 1 |
+----+----------+-------------+------+---------------------+------------+-----------+------------+
# SQL语句查询复杂性太多了，再次就不详细叙述了...
# 想要继续了解的同学，参考：https://www.cnblogs.com/progor/p/8786133.html
# 继续学习group by、order by、limit、having等句式语法
修改表数据
语法：UPDATE <表名> SET 字段1=值1[,..字段n=值n] [WHERE 子句] [ORDER BY 子句] [LIMIT 子句]
例：
mysql> update users set username='zabbix1' where username='zabbix';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select username from users;
+----------+
| username |
+----------+
| zabbix1 |
| xiaoming |
| xiaoli |
+----------+
mysql> update users set is_banned=1,is_deleted=0 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from users;
+----+----------+-------------+--------------+---------------------+---------------------+-----------+------------+
| id | username | password | desc | created_at | updated_at | is_banned | is_deleted |
+----+----------+-------------+--------------+---------------------+---------------------+-----------+------------+
| 1 | zabbix1 | pwd0 | zabbix用户 | 2022-03-23 11:23:15 | NULL | 0 | 0 |
| 2 | xiaoming | pwdxiaoming | NULL | 2022-03-23 11:25:13 | NULL | 1 | 0 |
| 3 | xiaoli | pwdxiaoli | 小丽用户 | 2022-03-23 10:40:35 | 2022-03-23 10:40:35 | 0 | 0 |
+----+----------+-------------+--------------+---------------------+---------------------+-----------+------------+
删除表数据
语法：DELETE FROM table_name [WHERE Clause]
mysql> delete from users where id=3;
Query OK, 1 row affected (0.01 sec)
mysql> select * from users;
+----+----------+-------------+--------------+---------------------+------------+-----------+------------+
| id | username | password | desc | created_at | updated_at | is_banned | is_deleted |
+----+----------+-------------+--------------+---------------------+------------+-----------+------------+
| 1 | zabbix1 | pwd0 | zabbix用户 | 2022-03-23 11:23:15 | NULL | 0 | 0 |
| 2 | xiaoming | pwdxiaoming | NULL | 2022-03-23 11:25:13 | NULL | 1 | 0 |
+----+----------+-------------+--------------+---------------------+------------+-----------+------------+
浙公网安备 33010602011771号