你的位置:首页 > 软件开发 > Java > mysql整理

mysql整理

发布时间:2017-11-02 19:00:10
一、建表(创建一个简单的用户权限关系表)  1.user(用户表)1 CREATE TABLE `user` (2 `username` varchar(255) NOT NULL,3 `password` varchar(255) DEFAULT NULL,4 `sex` va ...

mysql整理

一、建表(创建一个简单的用户权限关系表)

  1.user(用户表)

1 CREATE TABLE `user` (2 `username` varchar(255) NOT NULL,3 `password` varchar(255) DEFAULT NULL,4 `sex` varchar(255) NOT NULL,5 `email` varchar(255) NOT NULL,6 PRIMARY KEY (`username`),7 UNIQUE KEY `username` (`username`)8 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

   创建用户表,username(用户账号)设为unique唯一。

  2.role(角色表)

1 create table role(2  role_id int not null primary key,3  role_name varchar(255) not null unique, #角色名称4  role_description varchar(255) not null #角色描述5 );

  3.authority(权限表)

1 create table authority(2  authority_id int not null primary key ,3  authority_name varchar(255) not null unique,#权限名称4  authority_description varchar(255) not null #权限描述5 );

   修改表属性:(如有需要的话)

1 #将user表的password字段的类型改成varchar(255)2 alter table `user`3 change column password password varchar(255)

   删除表的字段:

1 #删除user表的user_id字段2 alter table `user`3 drop column user_id

  4.user_role(用户角色表)

 1 CREATE TABLE `user_role` ( 2 `ur_id` varchar(255) NOT NULL, 3 `username` varchar(255) NOT NULL, 4 `role_id` varchar(255) NOT NULL, 5 PRIMARY KEY (`ur_id`), 6 KEY `role_ids` (`role_id`), 7 KEY `username` (`username`), 8 CONSTRAINT `username` FOREIGN KEY (`username`) REFERENCES `user` (`username`) ON DELETE CASCADE ON UPDATE CASCADE,#创建外键约束,并且会根据主表的更新而更新 9 CONSTRAINT `role_ids` FOREIGN KEY (`role_id`) REFERENCES `role` (`role_id`) ON DELETE CASCADE ON UPDATE CASCADE10 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

   5.role_authority(角色权限表)

create table role_authority( ra_id varchar(255) not null primary key, authority_id varchar(255) not null, role_id varchar(255) not null, constraint authority_id foreign key(authority_id) references authority(authority_id) on update CASCADE on delete CASCADE, constraint role_id2 foreign key(role_id) references role(role_id) on update CASCADE on delete CASCADE);

   建完的ER图:

mysql整理

  主从关系:(从表数据会依赖于主表的数据)

  主表:userb表、role表、authority表

  从表:user_role表、role_authority表

 二、常用修改命令

  • ALTER TABLE:添加,修改,删除表的列,约束等表的定义。

    · 查看列:desc 表名;

    · 修改表名:alter table t_book rename to bbb;

    · 添加列:alter table 表名 add column 列名 varchar(30);

    · 删除列:alter table 表名 drop column 列名;

    · 修改列名MySQL: alter table bbb change nnnnn hh int;

    · 修改列名SQLServer:exec sp_rename't_student.name','nn','column';

    · 修改列名Oracle:lter table bbb rename column nnnnn to hh int;

    · 修改列属性:alter table t_book modify name varchar(22);

  • MySQL 查看约束,添加约束,删除约束 添加列,修改列,删除列

    · 查看表的字段信息:desc 表名;

    · 查看表的所有信息:show create table 表名;

    · 添加主键约束:alter table 表名 add constraint 主键 (形如:PK_表名) primary key 表名(主键字段);

    · 添加外键约束:alter table 从表 add constraint 外键(形如:FK_从表_主表) foreign key 从表(外键字段) references 主表(主键字段);

    · 删除主键约束:alter table 表名 drop primary key;

    · 删除外键约束:alter table 表名 drop foreign key 外键(区分大小写);

    · 修改表名:alter table t_book rename to bbb;

    · 添加列:alter table 表名 add column 列名 varchar(30);

    · 删除列:alter table 表名 drop column 列名;

    · 修改列名MySQL: alter table bbb change nnnnn hh int;

    · 修改列名SQLServer:exec sp_rename't_student.name','nn','column';

    · 修改列名Oracle:alter table bbb rename column nnnnn to hh int;

    · 修改列属性:alter table t_book modify name varchar(22);

 三、数据库索引

  1.关于索引的总结  2.一句话理解数据库索引:https://my.oschina.net/gooke/blog/678556

  3.并不是所有的表都适合创建索引,不适合索引的表具有如下特点:

    • 第一,对于那些在查询中很少使用或者参考的列不应该创建索引。这是因 为,既然这些列很少使用到,因此有索引或者无索引,并不能提高查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。
    • 第二,对于那 些只有很少数据值的列也不应该增加索引。这是因为,由于这些列的取值很少,例如人事表的性别列,在查询的结果中,结果集的数据行占了表中数据行的很大比例,即需要在表中搜索的数据行的比例很大。增加索引,并不能明显加快检索速度。 
    • 第三,对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为,这些列的数据量要么相当大,要么取值很少。 
    • 第四,当修改性能远远大于检索性能时,不应该创建索 引。这是因为,修改性能和检索性能是互相矛盾的。当增加索引时,会提高检索性能,但是会降低修改性能。当减少索引时,会提高修改性能,降低检索性能。因 此,当修改性能远远大于检索性能时,不应该创建索引。

  4.几种创建索引的方法:

    1、添加PRIMARY KEY(主键索引) 

     mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

    2、添加UNIQUE(唯一索引) 

     mysql>ALTER TABLE `table_name` ADD UNIQUE ( `column` )  

    3、添加INDEX(普通索引)

     mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

    4、添加FULLTEXT(全文索引)

     mysql>ALTER TABLE `table_name` ADD FULLTEXT ( `column`)  

    5、添加多列索引 

     mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

    6、删除索引

     mysql>drop index username on user  【username:索引名称,user:表名】

  

  

 

原标题:mysql整理

关键词:MYSQL

*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们: admin#shaoqun.com (#换成@)。

可能感兴趣文章

我的浏览记录