MySQl库、表、数据的增删改查等操作

---

前言

一、MySQl数据库

	mysql版本8.1.0

二、库操作-database

-- 列出所有数据库
show databases;
show schemas;
-- 列出ss名称开头的数据库
show databases like 'ss%';
-- 通过查看information_schema.schemata过滤核心数据库，只显示用户数据库
select schema_name from information_schema.schemata where schema_name not in ('information_schema','performance_schema','sys','mysql');

-- 建立数据库
create database ded1;
create database if not exists ded2 default character set utf8;

-- 使用数据库
use ded1;

-- 删除数据库，没有库报错
drop database ded1;
-- 如果存在删除数据库,否则不报错
drop database if exists ded1;

-- 查看建立数据库语句
show create database ded2;

三、表操作-table

表的约束

1.primary key 主键 （一个表只能一个主键，主键列不能为null,不能重复）“primary key(id,name) 复合主键”
2.auto_increment 自增（一般用于主键）
3.unique 唯一（约束后这一列不能有重复）
4.not null 非空
5.default ‘男’ 默认值（会有一个初始值例如’男’）
6.comment '姓别' 提示信息
7.unsigned 无符号（没有负数，修饰数字类型后会增大数字类型正数范围例如tinyint从-127~128变成 0~255）
8.zerofill 补零（id int(3) unsigned zerofill auto_increment primary key comment '主键'）001、002…

数据类型

1.数字类型(整数浮点数)

1.整数： tinyint smallint mediumint int bigint（int(3) unsigned zerofill 三位补零）
2.浮点数：float double decimal(p,s) numeric(p,s)（decimal(5,2)代表三位整数两位小数）
3.boolean 使用的是 tinyint unsigined（一般来说1代表T,0代表F）

2.字符串（定长，变长）

1.char(11) 定长（一般使用位数固定的数字，例如学号、电话、银行卡号等）
2.varchar(255) 变长（一般使用地址、姓名等位数不定的字符串）
3.longtext 变长（一般不用 4GB字符 超大）

3.日期时间

1.date yyyy-MM-dd
2.datetime与timestamp yyyy-MM-dd hh:mm:ss
3.year yyyy
4.time hh:mm:ss

 	-- 建立时间（当前时间戳）
 	create_time datetime default current_timestamp,
 	-- 修改时间（修改时的时间戳）
    update_time datetime default current_timestamp on update current_timestamp

4.复合类型

1.set() 集合类型 （set('英语','数学','语文','代数')内容不能重复，只能填写set集合里面的内容且可多选)
2.enum() 枚举类型（enum('男','女')内容不能重复，只能填写enum枚举里面的内容且单选 ）
3.bit 布尔类型 （bit 只能存1和0、或者true和false、或者null）

5.json

https://www.json.org/json-zh.html

6.二进制类型

1.建立表 create table

create table numeral(
    id int(3) unsigned auto_increment primary key ,
    name varchar(50) not null unique comment '姓名',
    birthday date not null default '2001-12-08',
    create_time datetime default current_timestamp,
    update_time datetime default current_timestamp on update current_timestamp
    )engine Innodb default charset utf8;
    -- engine设置引擎 charset字符集编码
    
-- 将t表从book数据库复制到books数据库中
create table book.her like books.t; -- 复制books.t表的结构到her(会自动建立her表)
insert into book.her select * from books.t; -- 复制books.t表的内容

2.删除表 drop table

-- 删除表，没有表报错
drop table t;
-- 如果存在删除表,否则不报错
drop table if exists t;
-- 当前在ded数据库上，删除fre数据库中的表t
drop table if exists fre.t;
-- 删除一堆表
drop table if exists t1,t2,t3,t4,t5;

3.修改表 alter table

-- 修改表名
alter table olds rename news;
rename table olds to news;
-- 移动表olds从db2库移动当前数据库为new表
alter table dd2.olds rename news;
rename table dd2.olds to news;

-- modify代表修改字段(修改约束、数据类型和精度)
alter table numeral modify id int(3) unsigned zerofill auto_increment comment '主键';
-- add代表增加字段（列） after代表位置在name后   decimal(5,2)代表三位整数两位小数
alter table numeral add results decimal(5,2) unsigned ;
alter table numeral modify results decimal(5,2) after name; -- first添加到首列
-- char定长字符串（学号、手机号等）
alter table numeral add mobile_phone char(11) after results;
-- 修改字段名（列名）
alter table numeral rename column name to names;
-- 删除字段(删除一列)
alter table numeral drop birthday;

4.查看表 select table

-- 查看当前库下的所有表
show tables;
-- 查看db库下的所有表
show tables from db;
-- 查看建立表语句
show create table numeral;
-- 查看表结构
desc numeral;
describe numeral;
-- 查看lxl库下ss表的结构
show columns from lxl.ss;

四、增删改查insert、delete、update、select

1.insert （replace）数据插入

1.insert into 表名 values(值...),(值...),(值...);
2.insert into 表名(字段名...) values(值...);
3.insert into 表名 set 字段名1=’‘,字段名2=’‘...;
replace与insert同理（replace into 表名 values(值...),(值...),(值...);）

2.delete 数据删除

1.delete from 表名;
2.delete 表名 from where 条件;
3.truecate 表名; 直截断数据，留下表结构，自增主键会自动恢复，速度快，不能加条件，不会触发删除触发器。

3.update 数据修改

1.update 表名 set 字段名='新值';
2.update 表名 set 字段名='新值' where 条件;

4.select 数据查询

1.select * from 表名;
2.select 字段名,字段名,字段名... from 表名;

查询条件 = > < >= <= != and和&& or和|| not和!

-- 查询id=1并且以红字结尾的数据
select * from student where id = 1 && name like '%红';
-- 查询name不等于小红的数据
select * from student where name !='小红';
select * from student where not name ='小红';
-- 查询name=小红的数据和age=18的数据,两者并无关系
select * from student where  name ='小红' or age='18';

查询条件 is null 与is not null

select * from student where age is null;-- 查询age为null的
select * from student where age is not null;-- 查询age不为null的
select * from student where not age is null;-- 查询age为null的

查询条件 like '% _'

-- 查询‘小’字开头的数据
select * from student where name like '小%';
-- 查询‘亮’字结尾的数据
select * from student where name like '%亮';
-- 查询不含‘静’字的数据
select * from student where name not like '%静%';
-- 查询‘王’字开头并且三位的数据
select * from student where name like '王__';

查询条件 regexp

-- 查询含有数字的数据
select * from student where name regexp '[0-9]+';
-- 查询含有数字开头的数据
select * from student where name regexp '[0-9]+$';
-- 查询含有数字结尾的数据
select * from student where name regexp '^[0-9]';
-- 查询只有两个汉字的数据
select * from student where name regexp '^[\\u4e00-\\u9fa5]{2}$'

查询条件 between x and y not between x and y in() not in()

-- 查询age在15-19范围的数据
select * from student where age between 15 and 19;
-- 查询age不在15-19范围的数据
select * from student where age not between 15 and 19;
-- 查询id=1,3,5,11,19的数据
select * from student where id in (1,3,5,11,19);
-- 查询id!=1,3,5,11,19的数据
select * from student where id not in (1,3,5,11,19);