MySql数据库零散补充知识点

最新推荐文章于 2024-10-14 21:48:23 发布

风雨断肠草

最新推荐文章于 2024-10-14 21:48:23 发布

阅读量320

点赞数 1

CC 4.0 BY-SA版权

文章标签：初学者的零散知识点笔记

本文链接：https://blog.youkuaiyun.com/weixin_42271016/article/details/80834347

1、创建表格时

create table test(
   id int(4) zerofill -- zerofill 零填充
);

2、timestamp

create table test(
    id int,
    data1 timestamp
);

timestamp为时间戳，建表时在最后添加一个timestamp字段，每更新一条记录时，系统会自动加上该记录更新时的时间

3、主键自增长

CREATE TABLE test(
	    id INT PRIMARY KEY AUTO_INCREMENT,-- 主键自增
	    NAME VARCHAR(20) ,
	    gender VARCHAR(2) DEFAULT '男'
);

4、truncate

删除带有自增的字段数据后，再次插入数据时会按照删除之前的数据排序自增，即会出现自增数据跳跃的情况，可用truncate来删除数据，这样可避免自增跳跃，但是会删除整张表格的所有数据且无法恢复

5、外键约束

CREATE TABLE empe(
    id INT PRIMARY KEY AUTO_INCREMENT,-- 主键自增
    NAME VARCHAR(20) ,
    deptID int,
    constraint empe_dept_fk foreign key empe(deptID) references dept(id) -- 外键约束
);
CREATE TABLE dept(
    id INT PRIMARY KEY AUTO_INCREMENT,-- 主键自增
    depte varchar(20)
);

外键约束：constraint+外键名+foreign key+从表(外键约束字段名)+references+主表(参考字段名)

constraint empe_dept_fk foreign key empe(deptID) references dept(id)

此处外键约束作用：在给empe表增加数据时，deptID字段的值必须在dept表中id字段的取值范围内，否则报错

6、多表等值联查

假设有三张表 ax,bx,cx(字段随意)

select a.name,b.age,c.sex from ax a join bx b on a.id = b.id join cx c on b.name = c.id；

7、级联

级联删除：on delete cascade

级联修改：on update cascade

作用：操作主表的参考字段数据时，从表的外键字段数据自动随之改变

8、数据库设计三大范式

1）要求表的每一个字段，必须是独立的不可分割的单元

2）在第一范式的基础上，要求表的除主键以外的其他字段都要和主键有依赖关系。

例如创建员工表，表中除主键以外，其他所有字段应当都与员工直接相关

3）在第二范式的基础上，要求除主键以外，其他所有字段只能和主键有直接决定的依赖关系

以上三种范式最少满足前两个范式要求，第三范式则根据实际需要，分表可以节省储存空间降低冗余，但同时也会降低相应的查询效率，因此在实际设计中，则要根据需求来对存储空间和查询效率进行取舍

9、MySql数据库三种变量

1）全局变量

mysql内置的变量，随着数据库的启动，全局变量即开始存在内存中，当数据库关闭时，全局变量才会消失。

查看某个全局变量：select @@变量名

修改某个全局变量：set @@变量名 = 值

2）会话变量

会话变量只在某次登录的会话中有效，退出连接之后，会话变量就失效

查看某个会话变量：select @变量名

修改某个会话变量：set @变量名 = 值

3）局部变量

局部变量在存储过程中定义的变量。存储过程结束，局部变量失效

查看某个局部变量：select 变量名

修改某个局部变量：set 变量名 = 值

定义某个局部变量：declare 变量名数据类型 default 初始值

一般使用会话变量和局部变量

10、MySql权限问题

1）mysql 数据库：存放mysql配置信息，包括用户信息

use mysql;

2）用户表

select * from user;

3）加密函数（md5算法-单向加密）

select password('root');

4）修改用户密码

update user set password =password('新密码') where user = 'root';

5) 分配权限给不同的用户

grant 功能 on 库名.表名 to '用户名'@'localhost' identified by '密码' ;

例如分配一个查询功能给新的用户 hei 密码为 123，只能查询数据库中的student表

grant select on hf2.student to 'hei'@'localhost' identified by '123';

6）取消已分配的权限

revoke select on hf2.studnet from 'hei'@'localhost' identified by '123';

7）设置让其他人可以访问本地数据库

若不设置此项，则数据库只能本地localhost连接，其他人连接则报错

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

11、备份与恢复

数据库的备份与恢复必须在进入数据库之前进行，在dos窗口中执行相应命令

备份

mysqldump -u root -p 数据库名 > d:/数据库名.sql

恢复

mysql -u root -p 数据库名>d:/数据库名.sql

12、关于命令窗口中文显示乱码的问题

中文乱码一般是因为命令窗口显示中文时所采用的字符集与中文本身采用的字符集不匹配，中文本身采用的字符集是gb2312，而数据库默认的是utf8，所以需要将数据库默认字符集改为gb2312。

1、先查询当前数据库里所采用的字符集（此步骤可省略。最好查询一下，确认是否是上述原因导致），命令如下：

show variables like '%char%'; 可查询出很多功能对应的字符集

2、显示如下图，其中标记的位置 character_set_results 则是当前数据库显示中文所用的字符集，一般为utf8，需要将其设置为gb2312，如果直接在dos窗口修改（修改命令：set character_set_results=gb2312;），退出窗口则失效，下次登录仍需修改，这里推荐修改配置文件my.ini(在mysql的安装文件夹里) ，可永久解决中文乱码。

3、如下改变：

找到mysql安装文件夹，并找到配置文件my.ini，用记事本打开

4、将下面的默认字符集utf8 改为gb2312 ，保存即可，进入数据库再次查询，可正常显示

13、多表联删

实例：下面四张表相互关联t_project，t_project_space，t_project_type，t_project_materils
       根据 t_project 表中的user_id字段的值 来删除对应的所有关联表的数据
DELETE p,ps,pt,pm FROM `t_project` p LEFT OUTER JOIN `t_project_space` ps ON p.`id` =         
     ps.`pid`	
	 LEFT OUTER JOIN `t_project_type` pt ON pt.`sid` = ps.`id`
	 LEFT OUTER JOIN `t_project_materils` pm ON pm.`tid` = pt.`id`
	  WHERE p.`user_id` = 'Youth'