MySql数据库基础

一，数据库

1. 创建数据库

方法一：直接创建

create database 数据库名;

方法二：创建数据库并指定字符集

create database 数据库名 character set 字符集;

方法三：判断数据库是否已经存在，不存在则创建数据库（推荐使用）；如果MySQL中已经存在相关数据库，则可忽略此创建语句，不在创建数据库

create database if not exists 数据库名;

注意：DATABASE不能改名。一些可视化工具可以改名，他是创建新库，把所有表复制过来，再删旧库完成的。

2. 管理数据库

查看当前连接中的数据库都有哪些

show databases;

切换数据库

use 数据库名;

查看当前数据库中存在的数据表

show tables;

查看指定数据库下存在哪些数据表

show tables form 数据库名;

查看当前使用的数据库

select database() from dual;
#简写
select database();

3. 修改数据库

更改数据库字符集

alter database 数据库名 character set 字符集(如：gbk、utf8等);

查看数据库结构

show create database 数据库名;

4. 删除数据库

#方法一
drop database 数据库名;
#方法二（推荐使用，如果数据库存在，则删除成功，不存在则自动结束）
drop database if exists 数据库名;

二，MySQL数据类型

类型	类型举例
整数类型	TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT
浮点类型	FLOAT、DOUBLE
定点数类型	DECIMAL
位类型	BIT
日期时间类型	YEAR、TIME、DATE、DATETIME、TIMESTAMP
文本字符串类型	CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
枚举类型	ENUM
集合类型	SET
二进制类型	BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB
JSON类型	JSON对象、JSON数组
空间数据类型	单值：GEOMETRY、POINT、LINESTRING、POLYGON 集合：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION

三，数据表

1. 创建数据表

#方法一
create table 表名(
  id int,
  name varchar(15)
);
#方法二（推荐）
create table if not exists 表名(
  id int,
  name varchar(15)
);
#方法三（基于现有表,同时导入现有表数据，字段类型与现有表字段类型一致，查询语句中字段别名或者字段名就是创建表的字段名）
create table 新建表名
as
select 字段1，字段2,...
from 现有表名 where 判断条件;

2. 查看表结构

desc 表名;

3. 查询创建表的语句

show create table 表名;

4. 修改表

#添加一个字段
#方法一：数据表中追加字段
alter table 表名
add 字段名 字段类型 默认值 约束;
#方法二：字段添加到指定字段前面
alter table 表名
add 字段名 字段类型 默认值 约束 指定字段名 first;
#方法三：字段添加到指定字段后面
alter table 表名
add 字段名 字段类型 默认值 约束 指定字段名;

#修改一个字段（类型数据类型，默认值，约束）
#修改数据类型
alter table 表名
modify 字段名 字段类型;
#修改默认值
alter table 表名
modify 字段名 字段类型 default 默认值;

#重命名字段
alter table 表名
change 要重命名的字段名 重命名之后的字段名 数据类型;

#删除一个字段
alter table 表名
drop column 字段名;

5. 重命名表

#方法一：使用rename
rename table 新表名
to 旧表名

#方法二
alter table 旧表名
rename to 新表名;

6. 删除表

#方法一
drop table 表名;
#方法二
drop table if exists 表名;

7. 清空表

#删除表中所有数据
#方法一
truncate table 表名;
#方法二
delete from 表名;

四，DCL中的commit和rollback的使用

commit：

提交数据，一旦执行commit，则数据就被永久的保存在了数据库中，不可回滚。

rollback：

回滚数据，一旦执行rollback，则可实现数据回滚，回滚到最近一次commit之后。

五，对比truncate table和delete from

相同点：

都可以实现对表中数据的删除，同时保留表结构

不同点：

truncate table 一单执行此操作，表中数据全部清除，不支持回滚。

delete from 一单执行此操作，表中数据可以全部清除(不带where)，支持回滚。

说明：truncate table 比delete from速度要快，且使用的系统和事务日志资源少，但truncate table 无事务器不触发触发器（trigger），有可能造成事故，故不建议在开发代码中使用此语句。

六，浅谈DML，DDL，DCL

一，DML

• DML（data manipulation language）数据操纵语言：

就是我们最经常用到的 SELECT、UPDATE、INSERT、DELETE。 主要用来对数据库的数据进行一些操作。

二，DDL

• DDL（data definition language）数据库定义语言：

其实就是我们在创建表的时候用到的一些sql，比如说：CREATE、ALTER、DROP等。DDL主要是用在定义或改变表的结构，数据类型，表之间的链接和约束等初始化工作上。

三，DCL

• DCL（Data Control Language）数据库控制语言：

是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。这个比较少用到。

七，DDL和DML说明

DDL操作一但执行，不可回滚。SET autocommit = FALSE对DDL操作失效（因为在执行DDL操作之后，一定会执行一次commit，而commit操作不受SET autocommit = FALSE影响）。

DML操作一旦执行，默认情况下不可回滚，但是，如果在执行DML之前，执行了SET autocommit = FALSE，则执行的DML操作，就可以回滚。

八，增删改

一，增

#方法一
insert into 表名 values(值1,值2...);
#方法二
insert into 表名(字段1,字段2...) values(值1,值2...);
#方法三
insert into 表名(字段1,字段2...) values(值1,值2...),(值1,值2...);
#方法四
insert into 表名(字段1,字段2...) select 查询的字段1,查询的字段2... from 被查询的表名;

二，删

delete from 表名 where 条件

三，改

update 表名 set 字段名 = ‘’ where 条件

九，MySQL8新特性：计算列

计算列：简单来说就是某一列的值是通过别的列计算得来的。例如：a列值为1，b列值为2，c列不需要手动，定义a+b的结果值为c的值，那么c就是计算列，是通过别的列计算得到的。

在MySQL8.0中，create table和alter atble都支持中增加计算列。

#以create table为例
create table 表名(
  a int,
  b int,
  c int generated always as (a+b) virtual
);

十，约束

约束是一种限制，通过对表的行或列的数据做出限制，来保证表中数据的完整性，一致性。

MySQL常用约束：

约束类型	主键约束	外键约束	唯一约束	非空约束	自增约束	默认值约束
关键字	primary key	foreign key	unique	not null	auto_increment	default

一，主键约束 primary key

主键约束相当于唯一约束 + 非空约束的组合，主键约束列不能重复也不能为空。

当创建主键约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。

#添加主键约束
#方法一
#基本模式
create table 表名(
    id int primary key,
    name varchar(20)
);
#组合模式
create table 表名(
    id int,
    name varchar(20),
    age int,
    primary key(id,name)
);
#方法二
alter table 表名 add primary key(字段名1,字段名2...);
#修改主键约束
alter table 表名 modify 字段名 数据类型 primary key;
#删除主键约束
alter table 表名 drop primary key;

二，外键约束 foreign key

外键约束是保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。

#添加外键约束
#基本模式
#方法一
#主表
create table 表1(
    id int primary key,
    name varchar(20)
);
#副表
create table 表2(
    id int,
    name varchar(20),
    foreign key(id) references 表1(id)
);
#方法二
alter table 表名 add constraint 外键约束名 foreign key(列名) references 引用外键表(列名);

# 多列外键组合，必须用表级别约束语法
# 主表
create table 表1(
id int,
name varchar(20),
age int,
primary key(name,number)
);

# 副表
create table 表2(
id int,
classes_name varchar(20),
classes_age int,
/*表级别联合外键*/
foreign key(classes_name,classes_age) references 表1(name,age);
);

#删除外键约束
alter table 表名 drop foreign key 外键名;

#修改外键数据--（先删除再添加）

三，唯一约束 unique

唯一约束是指定table的列或列组合不能重复，保证数据的唯一性。

唯一约束不允许出现重复的值，但是可以多个为null。

在创建唯一约束时，如果不给唯一约束名称，就默认和列名相同。

#添加唯一约束
#方法一
#创建表时设置,表示name、password不能重复
create table 表名(
    id int not null ,
    name varchar(20),
    password varchar(10),
    unique(name,password)
);

#方法二
ALTER TABLE 表名 ADD unique 约束名 ( `列名1`,`列名2`)

#修改唯一约束
alter table 表名 modify 字段名 数据类型 unique;

#删除唯一约束
alter table 表名 drop index 约束名;

四，非空约束 not null ，默认值约束 default和自动约束 auto_increment

非空约束用于确保当前列的值不为空值，非空约束只能出现在表对象的列上。

#创建table表，ID 为非空且自增约束，name 为非空约束 且默认值为abc
create table temp(
    id int not null auto_increment,
    name varchar(255) not null default  'abc',
    sex char null
);

#增加非空约束
alter table 表名
modify 字段名 数据类型 not null;

#取消非空约束
alter table 表名 modify 字段名 数据类型 null;

#取消非空约束，增加默认值
alter table 表名 modify 字段名 数据类型 default 默认值 null;