数据库表的操作

一、数据库的搭建

如上篇文章

二、基础了解

show命令支持模糊匹配
show databases、show tables、
show databases like “····”
“%”、“_”通配符字符串

三、MySQL数据库表的操作

关系型数据库都是遵循SQL语法进行数据查询和管理的

3.1 SQL介绍

3.1.1 SQL的功能

结构化查询语言（Structured Query Lauguage），在关系型数据库上执行数据库操作、数据检索以及数据维护的标准语言；使用SQL语句，程序员和数据库管理员可以完成如下的任务：1.改变数据库的结构2.更改系统的安全设置3.增加用户对数据库或表的许可权限4.在数据库中检索需要信息5.对数据库的信息进行更新

3.1.2 SQL的分类

1.DDL(data definition language)：数据定义语言，定义对数据库对象（库、表、列、索引的操作）create、drop、alter、rename、truecate
2.DML(data manipulation language)：数据操作语言
3.DQL：数据查询语言
4.DCL：数据控制语言，定义对数据库】表、字段、用户的访问权限和安全级别。

3.1.3 SQL语句的规范

在数据库系统中，SQL语句不区分大小写(关键字建议用大写) 。但字符串常量区分大小写。
SQL语句可单行或多行书写，以“;”结尾。关键词不能跨多行或简写。用空格和缩进来提高语句的可读性。子句通常位于独立行，便于编辑，提高可读性。SELECT * FROM tb_table
注释：SQL标准：/**/。多行注释“-- ” 单行注释MySQL注释：“#” 

3.2 MySQL的数据库编码

MySQL的默认编码是Lain1的编码，Lain1支持西欧字符、希腊字符等。
在早期MySQL为了兼容像中文这种符号，提供了utf-8编码，第四个字节拿来存储表情包，所以不太建议使用utf-8，建议使用真正的utf8mb4
​
MySQL自带数据库： Information_schema:主要存储了系统中的一些数据库对象信息:如用户表信息、列信息、权限信息、字符集信息、分区信息等。(数据字典表)； performance_schema:主要存储数据库服务器的性能参数； mysql:存储了系统的用户权限信息及帮助信息。sys :5.7新增，之前版本需要手工导入。这个库是通过视图的形式把information_schema 和performance_schema结合起来，查询出更加令人容易理解的数据;test:系统自动创建的测试数据库，任何用户都可以使用。

3.3 数据库操作

3.3.1 create创建

CREATE DATABASE 数据库名称
​
####创建数据库同时指定编码####
create database db_name default charset="utf8mb4";
create database schOOl DEFAULT CHARACTER SET utf8mb4;
create database schOOl DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
​
####查询当前数据库是哪个数据库###
create database();
​
###查看创建的数据库###
show create database 数据库名称;

3.3.2 drop删除数据库

drop database 数据库名称;

3.3.3 查看数据库编码情况

show variables like "char%";

3.4数据表的操作

数据表是一种二维表格，类似于excel，用来存储真正的数据；

3.4.1 create创建表格

####语法结构####
create table  [if not exists] t_name(###定义表结构###字段名称1 类型 [约束条件]，字段名称2 类型 [约束条件],······####最后一个不能加逗号###字段名称N 类型 [约束条件]
)
/**创建一个用户表用户的姓名用户的年龄用户的性别用户的地址用户的电话
**/create table t_user(id int,name varchar(50),age int,gender char(5),address varchar(255),tel char(11))##查询数据
select * from t_user;
​
###插入数据
insert into t_user values(1,'肖金凤',18,'女','四川南充','322');
insert into t_user values('小辣椒',19,'女');

注意：查询SQL时，如果结果较多，查看不方便，则可以反转查询的结果，在SQL后面加上‘\g’。

3.4.2 数据类型

数据库在MySQL中，有三种主要的类型：文本、数字和日期/时间类型。

3.4.3 char和varchar的区别

char和varchar是数据库中用于存储字符串数据的两种数据类型，它们之间的主要区别如下：

1.存储长度：

• char：是一种固定长度的字符串类型。当你定义一个char类型字段时，你需要指定一个长度，例如char(10)。无论你存储的字符串实际长度如何，数据库都会为这个字段分配固定的存储空间（在这个例子中是10个字符的空间）。如果实际存储的字符串长度小于定义的长度，数据库会在字符串的末尾填充空格来达到指定的长度。

• varchar：是一种可变长度的字符串类型。定义varchar类型字段时，也需要指定一个最大长度，例如varchar(10)。但是，数据库只为实际存储的字符串长度分配空间，加上一个额外的字节（或两个字节，取决于具体的数据库实现和字符串长度）来记录字符串的长度。如果存储的字符串是空字符串，那么varchar字段几乎不占用任何空间。

2.性能：

• char：由于char类型的长度是固定的，所以在某些情况下，数据库可以更快地处理char类型的数据，因为存储和检索时不需要计算实际的长度。

• varchar：虽然varchar在存储空间上更为高效，但在处理时可能需要额外的计算来确定字符串的实际长度，这可能会稍微影响性能。

3.使用场景：

• char：适用于存储长度固定或几乎总是相同长度的字符串，例如邮政编码、电话区号等。

• varchar：适用于存储长度可变的字符串，例如姓名、地址等。

4.空间利用率：

• char：可能会浪费存储空间，因为即使实际数据没有达到定义的长度，也会占用全部的空间。

• varchar：空间利用率较高，只占用实际数据长度加上长度记录的空间。

5.字符串比较：

• char：在比较时，可能会考虑填充的空格，这可能会导致一些意外的结果。

• varchar：比较时通常不考虑填充的空格，更符合直观的字符串比较逻辑。

在选择使用`char`还是`varchar`时，应根据实际的数据特性和应用需求来决定。如果字符串长度固定或对性能有特别要求，可以考虑使用`char`。如果字符串长度变化较大，且希望节省存储空间，则应使用`varchar`。

\G:反转九十度

3.4.4 数据库的约束

创建数据库的时候，可以有多个约束。
存在如下一些数据库表的约束：默认值、主键、非空、外键、唯一检查约束

1.主键约束

• 主键功能上看相当于非空且唯一,一个表中只允许一个主键,主键是表中唯一确定一行数据的字段。

主键的值一般是没有意义的；
并且一般会加auto_increment（只加在主键后！） ---- 自增

2.唯一约束

作用：是保证该字段的值是唯一的；
#唯一性约束条件确保所在的字段或者字段组合不出现重复值;
#唯一性约束条件的字段允许出现一个NULL
#同一张表内可建多个唯一约束
#唯一约束可由多列组合而成
#建唯一约束时MySQL会为之建立对应的索引---也就是唯一索引。
#如果不给唯一约束起名，该唯一约束默认与列名相同。
​
#####第一种####
mysql> create table tb_student(-> id int primary key auto_increment,-> name varchar(18) unique --- 唯一约束-> );#####第二种#####
create table t_user(id int auto_increment,name varchar(18),primary key(id),unique(name));

3.默认值约束

####comment也可以跟在后面，也就是描述信息###
mysql> create table t_user(-> id int primary key auto_increment,-> name varchar(50) unique,-> age int-> );
Query OK, 0 rows affected (0.00 sec)
​
mysql> show tables;
+-----------------+
| Tables_in_db_ck |
+-----------------+
| db_hero         |
| t_user          |
+-----------------+
2 rows in set (0.00 sec)
​
mysql> insert into t_user values(1,"肖金凤",18);
Query OK, 1 row affected (0.01 sec)
​
mysql> select * from t_user;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | 肖金凤    |   18 |
+----+-----------+------+
1 row in set (0.00 sec)
​
​
#####加个默认值#####
mysql> create table t_user(-> id int primary key auto_increment,-> name varchar(50) unique,-> age int default 18    ---- 默认值约束-> );

4.非空约束

不允许字段的值为空
create table t_user(id int primary key auto_increment,name varchar(50) unique not null,age int default 18,password varchar(255) not null  --- 非空约束);

5.外键约束

• 是一个独立字段

外键指的是两张或者多张表之间关联关系的字段；
外键约束：是表的约束，是约束表在插入外键数据时能够正确地插入。
​
#外键是构建于一个表的两个字段或者两个表的两个字段之间的关系
#外键确保了相关的两个字段的两个关系：
子(从)表外键列的值必须在主表参照值的范围内，或者为空（也可以加非空约束，强制不允许为空）。
当主表的记录被子表参照时，主表记录不允许被删除。
外键参照的只能是主表主键或者唯一键，保证子表记录可以准确定位到被参照的记录。

###在创建表的同时，将外键约束添加上
###首先保证班级表创建成功
#插入正确的数据
create table grade(id int auto_increment,name varchar(50) unique,primary key(id))
insert into grade(name) value("english"),("python"),("biochemistry"),("network");   

1.外键如何设计？

表的关联：1:1  ---->  外键可以设计在任意一方，不会产生数据冗余1:N  ---->  外键设计在多方N:N  ---->  创建第三张表，来专门描述两张表的关联关系

2.如何约束外键，保证不出错

需要添加外键约束：foreign key1.在创建数据库表的时候添加外键2.通过alter指令修改[CONSTRAINT] foreign key(字段) references 表(字段) [on delete RESTRICT | cacade | set null | xxx]
​
#内连接(只查存在关联的数据)
如果两张或多张关联关系表查询时，不指定他们的关联关系，则会出现笛卡尔积现象
select * from t_stu,t_classes;
select * from t_stu cross join t_Classes;
​
select * from t_stus /(inner)/ join t_classes on(t_stus.class.id=t_classes.id)
​
#外连接:如果要将这些没关联关系的数据查询出来，则需要使用外连接
select * from t_stus right outer join t_classes on(t_stus.class_id=t_classes.id)
​
#自连接:只能查询出存在关联关系的数据，如果不存在关联关系，就不行
​
# 注意：mysql不支持全连接查询  full  join
# 但是SQL存在联合查询  union 、union all
​
# 注意：联合查询，必须保证查询的多条SQL返回的结果 结构必须一致，所以联合查询常见于查询一张表

6.检查约束

检查约束在MySQL8之前是不生效的，也不报错！！！

create table t_stu(id int primary key auto_increment,name varchar(50) not null unique,age int check(Age >= 18),#gender char(2) check(gender in ("男","女"))gender enum("男","女"));

3.4.5 修改表的结构

修改列的类型：alter table 表名 modify 列名 列类型;
​
增加列：alter table 表名 add 列名 列类型;
​
删除列:alter table 表名 drop 列名;列改名:alter table 表名 change 旧列名 新列名 列类型;更改表名:① alter table 表名 rename 新表名;② rename table 表名 to 新表名;

在SQL中，提供了动态修改表结构的SQL功能。

创建表与之前的表相似

3.4.6复制表的结构

####第一种####
在create后面加一个like
#该种方式会讲原表的主键复制过来！
​
####第二种 select后面的*也可以选择为确定的列####
在create table后面跟一个select * from tableName
#该种方式不会将原表的主键这些复制过来！！

如果有主键限制，复制多行的数据会怎么样？没有主键限制的话，会直接都复制；

3.5 MySQL用户授权

3.5.1 密码策略

1.MySQL5.7为root用户随机生成了一个密码，打印在error_log中，关于error_log的位置；如果安装的是RPM包，
则默认是 /var/log/mysqld.log

开启用户的远程登陆：

MySQL官方是禁止root组账户远程登陆的！

3.5.2 远程登陆解决方案：

首先开启远程登录，必须保证你登陆的账号是存在高级权限的，一般是root。
登录MySQL之后访问MySQL数据库。
###案例###
use mysql
select host,user from user;
等价于
select host,user from mysql.user;
​
#修改
将localhost修改为%，或者改成需要的IP地址，再或者进行动态匹配。
update user set host="192.168.1.1%" where user="xjf";
update user set host="%" where user="xjf";
​
#此时修改成功，但是要让权限生效就要重启服务！ --- 不建议！！！！
#如果不行重启服务，可以刷新权限！
flush privileges;

3.5.3 用户创建和授权

grant create alter select等都需要权限的。

MySQL8之前，MySQL的grant命令非常强大，是集授权、创建用户、修改密码、修改用户、远程登录等等一些列功能与一体的命令。

grant all ON 表名.* TO 登录人名字@'%' IDENTIFIED BY '123456';

1.创建用户

create user 'zs'@'%' identified by 'zs';
%:是指允许远程登录

2.给创建的用户授权

#查看是哪个用户登录？
select user()
​
grant all on db_chengke.* to zs@'%';
grant all on *.* to dba@localhost; --- 给予最高的权限！