Mysql数据库（2）

古城微笑少年丶 2022-05-18 06:22 161阅读 0赞

**1.表约束**

约束是一种限制，通过退表的数据做出限制，来确保表中数据的完整性，唯一性。

![70][]

*   default     默认约束

创建表若只添加某个或某几个字段数据，其他字段便默认赋空（NULL），可添加默认值约束来指定默认值。

例：create table tb(     id int,     name varchar(20),     age int default 18 );

![70 1][]

alter table tb modify age int 20; \#为某张表添加默认值约束

alter table tb modify age int; \#删除default

*  not null 非空约束

添加非空约束后，insert时必须添加该字段

例：CREATE TABLE tb1(      id int,      name varchar(20) NOT NOLL );

![70 2][]

![70 3][]

alter table tb1 modify id int not null; \#添加非空约束

alter table tb1 modify id int ; \#删除非空约束

*  unique key 唯一约束

确保字段下的值唯一，不能有重复

例：CREATE TABLE tb2(      id int UNIQUE KEY,      name varchar(20) );

![70 4][]

![70 5][]

alter table tb2   modify name varchar(20) unique；\#添加唯一约束

alter table tb2   modify name varchar(20); \#删除唯一约束

*  primary key  主键约束

可以唯一标识一条数据，查询时一般通过主键定位一条数据，每张表里面只能有一个主键。

主键特性： 非空且唯一。当表里没有主键的时，第一个出现的非空且为唯一的列，被当成主键。

例：create table tb4(     id int primary key,     name varchar(20) not null );

![70 6][]

![70 7][]

alter table tb3   add primary key(id); \#添加主键约束

alter table tb4  drop primary key;  \#删除主键约束

*  auto\_increment 自增长

自动编号，一般与主键组合使用。一个表若无主键可设，则可加一列自增使其作为主键。

一个表里面只有一个自增默认情况下，起始值为1，每次的增量为1。

例：create table tb3(     id int primary key auto\_increment,     name varchar(20) )auto\_increment =100;

![70 8][]

alter table tb3   modify id int auto\_increment; \#添加自增

alter table tb3 modify id int; \#删除自增

*  foreign key  外键约束

保持数据一致性，完整性实现一对多关系。

外键必须关联到键上面去，一般情况是，关联到另一张表的主键

(因为一个表只存一类信息。用外键来做参照，保证数据的一致性，可以减少数据冗余）

（比如一个学生成绩表，一个学生信息表，学生信息表的学号为主键，学生成绩表的学号关联到信息表的学号，保持数据一致。）

例：create table a( a\_id int primary key auto\_increment, a\_name varchar(20) not null );

create table b (b\_id int auto\_increment,  b\_name varchar(20),

constraint 外键名字 FOREIGN KEY (b\_id) REFERENCES a(a\_id) );

** B表中的b\_id 字段，只能添加 a\_id中 已有的数据。**

**            A表中a\_id  被参照的数据,不能被修改和删除**

alter table b     -> add constraint AB\_foreign foreign key(b\_id) references  a(a\_id); \#增加外键

alter table  b drop foreign key AB\_foregin; \#删除外键

**2.表结构修改**

表描述：desc tb\_name;

添加字段（列）：alter table tanle\_name add \[column\] name varchar(10);

删除列：alter table tb\_name drop \[column\] name;

修改字段类型：alter table tb\_name modify name char(10);

修改字段名称：alter table tb\_name change name new\_name char(10);

修改表名：alter table tb\_name rename to new\_name;

**3.子查询和连接查询**

直接上例子：

以上有三个表

*  寻找张三的成绩

首先成绩表中并没有张三，只有编号，需要先从学生表找到张三的标号后再去成绩表寻找成绩

寻找张三编号：select number from student where name='张三';

寻找张三成绩：select \*from grade where student\_number='?'

此时的'？'便可用第一条查询的语句代替，进行查询嵌套，也就是子查询

即：select \*from grade where student\_number=（select number from student where name='张三'）;

*   内连接

![70 9][]

我们希望得到一个这样的一个完整的成绩表，此时需要联立三个表，student表和grade的number联立，

grade的subject\_number和subjects的number又可以联立

语法：select ... from 左表 join 右表 on 左表**.**字段=右表**.**字段  \#on表示连接条件

若没有on连接条件，则会进行交叉连接，如笛卡尔一班一一对应连接，左表n条数据，右表m条，则总m\*n条

则上表可通过以下语句查询得到：

select name, title, grade from  students

join grades

on students.number = grades.student\_number

join subjects

on grades.subject\_number=subjects.number;

**4.编码/用户/权限**

*  编码

mysql的配置文件路径：/etc/mysql/mysql.conf.d/mysqld.cnf

![70 10][]

用vim打开：vim /etc/mysql/mysql.conf.d/mysqld.cnf

创建数据库自定义编码：CREATET DATABASE db\_name     CHARACTER SET gbk;

创建表自定义编码：CREATE TABLE tb\_name (     id INT ) CHARSET utf8;

此时则显示mysql的编码格式

*  远程访问数据库(vim打开配置文件)

第一步：修改监听IP

bind-address: 127.0.0.1 -> bind-address:0.0.0.0

![70 11][]

第二步：给用户添加远程访问权限

update user set host ='%' where user='root';

![70 12][]

此语句查看用户和相应权限，localhost指的是只能本地访问，%则可远程访问

修改root权限为%即可

![70 13][]

第三步：重启数据库(需要输入密码)

sudo service mysql restart

访问别人的mysql：

mysql -h 192.168.137.0 -u root -psfmaskmds

中间的ip是你要访问的数据库的ip，账号密码为远程数据库的账号密码

![70 14][]

*   修改密码：set password=password('123456'); \#修改的是当前用户的密码
 *   用户权限

Root账号权限太大，一般只在管理数据库的时候，一般项目都是创建一个用户去操作

创建用户：CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

用户授权:grant 权限 on 数据库.\*  to 用户名@登录主机 identified by "密码";(root用户下才能授权！！！)

取消用户授权： revoke privilege ON databasename.tablename from 'username'@'%';

查看用户权限：show grants for 'username'@'localhost'；

权限列表： Alterl:修改已经存在的表

Create：建立新的数据库或表

select：查找记录

Update:修改记录

Insert:增加表的记录

Delete:删表的记录

Drop 删除数据库或表

USAGE:只允许登陆

ALL:运行做任何事情

例：创建新用户，用户名sakura，密码qwe123，权限localhost（或者%），赋予select,update,delete,insert权限。

![70 15][]

![70 16][]

![70 17][]

此时创建数据库报错，无权限。

退出，登录root账号

![70 18][]

ok，可以了，不过create权限还没授予，老哥们可自行操作。

[70]: /images/20220518/2afe3562ef374d619bd3dfad13608e52.png
[70 1]: /images/20220518/d4817baf5f3b4a78bc9ad6e0c6b13480.png
[70 2]: /images/20220518/0c25ce75f8c44374ba8604a2c175df5b.png
[70 3]: /images/20220518/e28c4a43e6ce4bb0a454c59bb99ac26f.png
[70 4]: /images/20220518/8eb74ef929f44d4c9cd30f4c46ff1b14.png
[70 5]: /images/20220518/7b7370da8001478e85c53e07ba19a291.png
[70 6]: /images/20220518/16b768b559014c11aaa3757d7621634e.png
[70 7]: /images/20220518/c01d6416fa324580bd3f092858ac3459.png
[70 8]: /images/20220518/fc56dcc084634777add1680e467f6ad0.png
[70 9]: /images/20220518/e57c32e2637a41ebb15f6f34159ed632.png
[70 10]: /images/20220518/47272de206bc456fafd48996e9f5a335.png
[70 11]: /images/20220518/21a8ad60a1ae4b8d85a5627522517595.png
[70 12]: /images/20220518/3e22db72978f42d6845c3fe993e1a136.png
[70 13]: /images/20220518/bd25e1439eff4c3781f35aea36681405.png
[70 14]: /images/20220518/dbfb4bca8b81496f9d42ee694e30a9d2.png
[70 15]: /images/20220518/adab6fda2315413caca14cd62adf1804.png
[70 16]: /images/20220518/610bfe6d53cb4f06b6d57bc0be26caf6.png
[70 17]: /images/20220518/3a338b4cef094961b7e13cc0b81bf164.png
[70 18]: /images/20220518/eba972d5440c4fa99faa8c12c2e6bab5.png