Mysql-CURD

常用登陆参数

# 常用参数:
-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 |
+----+----------+-------------+--------------+---------------------+------------+-----------+------------+

 

posted @ 2022-03-23 13:59  总要做些什么  阅读(64)  评论(0编辑  收藏  举报