mysql的数据库、表、字段、数据操作sql

---

一、数据库操作

1、查看所有数据库

show databases;
1

2、查看建库sql

show create database db1;
1

3、创建数据库

create database db1;
create database `ry-cloud`;
create database tt character set =utf8mb4 collate = utf8mb4_general_ci;
-- 5.7左右版本：utf8mb4_general_ci
1
2
3
4

4、修改数据库

-- 字符集
alter database newdb3 charset utf8mb4;

-- 排序规则
alter database yuncms  collate=utf8mb4_general_ci;
1
2
3
4
5

5、删除数据库

drop database `ri-cloud`;
1

6、使用数据库

use db1;
1

7、退出数据库

exit;
1

8、查看数据库版本

select version()
1

• 登录数据库会显示
  • Server version: 5.7.17 MySQL Community Server (GPL)

二、表操作

1、查看所有表

show tables;
1

2、查看建表sql

show create table 表名;
-- 有引擎和字符集信息
1
2

3、查看表详情

desc 表名;
1

4、创建表

create table 表名 (字段1名 字段1类型, 字段2名 字段2类型,...);

-- 创建表添加其它信息
create table 表名 (字段...) comment '用户表' engine=innodb character set=utf8mb4 collate=utf8mb4_general_ci row_format=Dynamic;

/*
comment：表注释
engine：引擎：innodb-支持数据库的复杂操作，包括外键、事务等（默认）；myisam-只支持数据基础的增删改查操作
character set：字符集
collate：排序规则
row_format：行格式
*/
1
2
3
4
5
6
7
8
9
10
11
12

5、删除表

drop table person;
1

6、删除表并创建新表

truncate table t2;
1

7、修改表名

rename table 原名 to 新名;
1

8、修改表信息

alter table t_user comment ' ';
alter table 表名 engine=myisam/innodb character set=gbk/utf8;
1
2

三、字段操作

1、添加表字段

alter table t1 add age int comment' ' /first/after xxx(不写添加到最后);
1

2、删除表字段

alter table 表名 drop 字段名;
1

3、修改字段类型、位置

-- 不会清空该字段的值

-- 添加到第一行
alter table 表名 modify 字段名 新类型  comment ' ' first;

-- 添加到哪个字段后面
alter table 表名 modify 字段名 新类型  comment ' ' after xxx;
1
2
3
4
5
6
7

4）修改字段名称、类型

-- 不会清空该字段的值

alter table 表名 change 原字段名 新字段名 新类型 comment ' ';
1
2
3

四、数据操作

1、插入数据

1. 全字段插入

-- 单条
-- 插入json数据-jsonObject
insert into hero values(1,'Tom','{"name": "kimi", "psd": "123"}');

-- 插入json数据-jsonArray
insert into hero values(1,'Tom',
'[{"name": "kimi", "psd": "123"},{"name": "rafa", "psd": "123"}]');	//json数组

-- 批量插入
insert into hero values(5,'唐僧','男'),(6,'悟空','男')

-- 插入NULL值
insert into hero values(null,null,'男')
1
2
3
4
5
6
7
8
9
10
11
12
13

2. 指定字段插入

insert into hero (name,gender) values('张飞','男');
1

3. 插入数据后获取自增主键
   • @@identity可以跟在一条insert语句或多条insert语句后面，用户记录生成的自增主键
     • 如果未插入成功，返回 NULL
     • 如果插入成功1行，则返回这条insert的自增主键值
     • 如果插入成功多行，则返回最后一条insert的自增主键值
   • 菜单表，3个字段分别为：id、名称、parent_id

   INSERT INTO `sys_menu` VALUES (null, '试题管理', 0);
   -- 定义全局变量@question = 插入试题管理目录的自增主键值
   select @question := @@identity;
   
   -- parent_id使用@question
   INSERT INTO `sys_menu` VALUES (null, '试题分类管理', @question);
   -- 定义全局变量@qust_category = 插入试题分类管理菜单的自增主键值
   select @qust_category := @@identity;
   
   -- parent_id使用@qust_category
   INSERT INTO `sys_menu` VALUES (null, '试题分类-详情', @qust_category);
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11

2、查询数据

• select 字段1,字段2 from 表名;

  • *：所有字段

• 查询json类型数据

  select
  json_value, -- json类型字段
  json_value->'$.url' url -- 查询json字段中的url属性，带双引号
  json_value->>'$.url' url  -- 查询json字段中的url属性，不带双引号
  from ld_web_result_xray
  1
  2
  3
  4
  5

• select也可以不是表

-- 系统日期
select now();

-- 自定义字段值
select 'kimi' name;
1
2
3
4
5

3、修改数据

• update 表名 set gender=‘女’ where 修改条件;

  • 不加where修改条件就是改表中的所有的员工

• 举例

  • 修改id小于5的性别为女

  update hero set gender='女',age=18  where id<5;
  1

  • 修改为NULL值数据

  update hero set gender=null
  1

4、删除数据

delete from 表名 where 删除条件;
-- 不加where删除条件就是删除表中所有数据
1
2