Linux 之MySQL数据库（二）
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Linux 之MySQL数据库（二），小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含9646字，纯文字阅读大概需要14分钟。
内容图文
MySQL数据库的五大约束
- NOT NULL ：非空约束，指定某列不能为空；
#建表时指定 create table t2(id int(10) not null,name varchar(5)); #已存在的表增加约束 alter table t2 modify name varchar(5) not null; #可以设置默认值，即为非空 alter table t2 constraint test_id default (‘xxx’) for stuname; #取消 alter table t2 modify name varchar(5); - UNIQUE : 唯一约束，指定某列或者几列组合不能重复
#创建的时候制定 create table t3(userid int(10) unique,name varchar(10)); #已存在的表增加 alter table t3 add constraint t3_id unique(id) #关键字增加唯一约束 alter table test4 add unique(id,name,age) - PRIMARY KEY ：主键，指定该列的值可以唯一地标识该列记录
#建表时添加 create table test1(user_id int(10) primary key,name varchar(10)); #删除约束 alter table test1 drop primary key; #以多列组合创立主键 create table t4 (id int,name varchar(255),primary key(id,name)); #已存在的表增加主键 alter table stu add constraint id primary key (id) - FOREIGN KEY ：外键，指定该行记录从属于主表中的一条记录，主要用于参照完整性
#创建表的时候 CREATE TABLE `students` ( `StuID` int(10) unsigned NOT NULL AUTO_INCREMENT, `Name` varchar(50) NOT NULL, PRIMARY KEY (`StuID`), KEY `hello_fk` (`Name`), CONSTRAINT `hello_fk` FOREIGN KEY (`Name`) REFERENCES `classes` (`Name`) ) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8 #已存在的表增加 alter table students add constraint mage_stu_class_fk foreign key(classid) references classes(classid); - CHECK ：检查，指定一个布尔表达式，用于指定对应的值必须满足该表达式（mysql不支持check约束）
MySQL数据库的函数
内置函数
- 字符串型
#upper转换为大写输出 MariaDB [hellodb]> select upper(name) from teachers; #lower转换为小写输出 MariaDB [hellodb]> select lower(name) from teachers; #insert替换函数(列名，从第几位开始，替换几位，替换的内容) MariaDB [hellodb]> select name,insert(phone,4,4,'****') phone from students; #substr从第5位开始取，往后一共取3位 MariaDB [hellodb]> select substr(name,5,3) phone from students; #length显示字段的长度 MariaDB [hellodb]> select length(name) phone from students; #CONCAT(s1,s2...sn) 字符串 s1,s2 等多个字符串合并为一个字符串 MariaDB [hellodb]> SELECT CONCAT("hello ", "mariadb ", "mysql ", "orcale") AS ConcatenatedString; #FIELD(s,s1,s2...) 返回第一个字符串 s 在字符串列表(s1,s2...)中的位置 MariaDB [hellodb]> SELECT FIELD("c", "a", "b", "c", "d", "e"); #LEFT(s,n) 返回字符串 s 的前 n 个字符 MariaDB [hellodb]> SELECT LEFT('abcde',2); #REPEAT(s,n) 将字符串 s 重复 n 次 MariaDB [hellodb]> SELECT REPEAT('mariadb ',3); #SUBSTRING(s, start, length) 从字符串 s 的 start 位置截取长度为 length 的子字符串 MariaDB [hellodb]> SELECT SUBSTRING("mariadb", 2, 3) AS ExtractString; - 日期函数
#显示当前的时间 MariaDB [hellodb]> select now(); #DATEDIFF(d1,d2) 计算日期 d1->d2 之间相隔的天数 MariaDB [hellodb]> SELECT DATEDIFF('2001-03-01','2001-02-02'); #DATE_FORMAT(d,f) 按表达式 f的要求显示日期 d MariaDB [hellodb]> SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r'); #DAY(d) 返回日期值 d 的日期部分 MariaDB [hellodb]> SELECT DAY("2017-06-15"); #DAYNAME(d) 返回日期 d 是星期几 MariaDB [hellodb]> SELECT DAYNAME('2011-11-11 11:11:11'); #DAYOFMONTH(d) 计算日期 d 是本月的第几天 MariaDB [hellodb]> SELECT DAYOFMONTH('2011-11-11 11:11:11'); #WEEK(d) 计算日期 d 是本年的第几个星期 MariaDB [hellodb]> SELECT WEEK('2011-11-11 11:11:11'); - 数字函数
#取绝对值 MariaDB [hellodb]> select abs(-20); #取模 MariaDB [hellodb]> select mod(11,3); #取不小于X的最小整数 MariaDB [hellodb]> select ceil(9.2); #取不大于X的最大整数 MariaDB [hellodb]> select floor(3.6); #n DIV m 整除，n 为被除数，m 为除数 MariaDB [hellodb]> SELECT 10 DIV 5; #GREATEST(expr1, expr2, expr3, ...) 返回列表中的最大值 MariaDB [hellodb]> SELECT GREATEST(3, 12, 34, 8, 25); MariaDB [hellodb]> SELECT GREATEST("mysql", "mariadb", "linux");自定义函数
#查看所有的函数数列 MariaDB [hellodb]> show function status\G; #创建无参的函数（在centos7支持，6不支持） MariaDB [test]> CREATE FUNCTION simple() RETURNS VARCHAR(20) RETURN "Hello World!"; #调用函数 MariaDB [test]> select simple(); #查看指定自定义函数的定义 MariaDB [mysql]> show create function simple\G; #创建带参的函数 MariaDB [test]> delimiter // #为了方便书写把结束符重新定义 MariaDB [test]> create function addtwo(x int unsigned,y int unsigned) returns int begin declare a,b int unsigned; set a=x,b=y; return a+b; end// #删除自定义函数 MariaDB [test]> drop function simpleFun;
MySQL数据库的视图
#创建视图
MariaDB [hellodb]> create view viewname as select * from teachers;
#查询指定视图
MariaDB [hellodb]> select * from viewname;
#查看所有的视图信息
select * from information_schema.views\G;
#查看视图的结构
desc viewname
#删除视图
MariaDB [hellodb]> drop view viewname;不支持做视图的语句
- select 子句中的distninct
- select 子句中的包含函数
- select 子句中包含group by
- select 子句中包含union
使用视图的好处
- 使用视图，可以定制用户数据，聚焦特定的数据。
例如：有张表里面有好多个属性：编号、学号、姓名、性别，生日、手机。。。等等，而且这张表会被经常被使用，但是你只想使用表里的两三个属性，这个时候你可以把你需要的属性建立成视图，从视图里查询即可。 - 使用视图，可以简化数据操作。
例如：你要经常性的写多表连接，嵌套查询，这些语句很长，每次书写很费时间，这个时候你就可以把这些长句子写进视图，下次直接在这个试图查询即可 - 使用视图，基表中的数据就有了一定的安全性。
例如：视图实际上是虚拟的，真实不存在的，假如银行的数据表，不可能给员工把真正表给他们，而是做一个原表的视图，这样既保证了员工的误操作带来的损失，又可以防止对数据表恶意修改 - 可以合并分离的数据，创建分区视图。
例如：可以把多条数据用union合成一个视图
MySQL数据库的索引
使用索引的优点：
- 所有的MySql列类型(字段类型)都可以被索引，也就是可以给任意字段设置索引
- 大大加快数据的查询速度
使用索引的缺点：
- 创索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加
- 索引也需要占空间，我们知道数据表中的数据也会有最大上线设置的，如果我们有大量的索引，索引文件可能会比数据文件更快达到上线值
- 当对表中的数据进行增、删除、修改时，索引也需要动态的维护，降低了数据的维护速度
索引的使用原则：
- 对经常更新的表就避免对其进行过多的索引，对经常用于查询的字段应该创建索引
- 数据量小的表最好不要使用索引，因为由于数据较少，可能查询全部数据花费的时间比遍历索引的时间还要短，索引就可能不会产生优化效果
- 在一同值少的列上(字段上)不要建立索引，比如在学生表的"性别"字段上只有男，女两个不同值。相反的，在一个字段上不同值较多可是建立索引
#创建索引 MariaDB [m33student]> create index age_index on student(phone); #查看索引 MariaDB [m33student]> show indexes from student\G; #创建唯一索引 create table ti( id int not null, name char(30) not null, unique index uniqidx(id) );#对id字段使用了索引，并且索引名字为UniqIdx #创建普通索引 create table book( bookid int not null, bookname varchar(255) not null, authors varchar(255) not null, info varchar(255) null , comment varchar(255) null, year YEAR not null, index(year) #对属性年创建索引 ); #修改表结构(添加索引) MariaDB [hellodb]> alter table teachers add index index_name(name); #删除索引 MariaDB [hellodb]> drop index [indexn_ame] on teachers; #显示索引信息 MariaDB [hellodb]> show index from teachers\G;
MySQL数据库的触发器
触发器是一个特殊的存储过程，不同的是存储过程要用来调用，而触发器不需要调用，也不需要手工启动，只要当一个预定义的事件发生的时候，就会被MYSQL自动调用。
简单格式：
create trigger trigger_name
trigger_time
trigger_event on table_name
trigger_stmt
for each row(行级监视，mysql固定写法，oracle不同)
begin
sql语句集........（触发器执行动作，分号结尾）
end;trigger_name：触发器的名称
trigger_time：{ BEFORE | AFTER }，表示在事件之前或之后触发
trigger event:：{ INSERT |UPDATE | DELETE }，触发的具体事件
trigger_stmt：触发器程序体，可以是一句SQL语句，或者用 BEGIN 和 END 包含的多条语句。
实验：创建一个触发器，制定一张表，对表增加记录的时候count加1，则反之
#创建student_info表
MariaDB [hellodb]> create table student_info(stu_id int(11) primary key auto_increment,stu_name varchar(255) default null);
#创建一张计数表
MariaDB [hellodb]> create table student_count( student_count int(11) default 0);
#使计数表置为0
MariaDB [hellodb]> insert into student_count values (0);
#创建触发器，给student_info表增加记录，计数表加一
MariaDB [hellodb]> create trigger trigger_student_count_insert after insert on student_info for each row update student_count set student_count=student_count+1;
#创建触发器，给student_info表删除记录，计数表减一
MariaDB [hellodb]> create trigger trigger_student_count_delete after delete on student_info for each row update student_count set student_count=student_count-1;
#开始测试，添加记录
MariaDB [hellodb]> insert into student_info (stu_id,stu_name) values (123,'abc');
#会看到计数表为1
MariaDB [hellodb]> select * from student_count;
#开始测试，删除记录
MariaDB [hellodb]> delete from student_info where stu_id=123;
#会看到计数表为0
MariaDB [hellodb]> select * from student_count;
MySQL数据库的用户管理
- 创建用户
MariaDB [hellodb]> create user name@host identified by 'passwd'; #创建全部权限的用户 MariaDB [hellodb]> grant all on *.* to name@host identified by 'passwd'; - 修改密码
#第一种直接利用password函数修改 MariaDB [hellodb]>set password for name@'host'=password("newpasswd"); #第二种更新用户表 MariaDB [hellodb]> update mysql.user password=password("passwd") where host ='localhost' #第三种关闭MySQL密码验证功能，然后第一种第二种 /etc/my.cnf 关闭密码认证 skip_grant_tables - 用户授权
#给用户授权使用库，表，视图 MariaDB [hellodb]> grant all on database.(database|table|view) to name@host ; #给用户授权使用命令select，insert MariaDB [hellodb]> grant select,insert on database.(database|table|view) to name@host; #给用户授权使用表或视图里的属性 MariaDB [hellodb]> grant select(属性) on database.(database|table|view) to name@host; - 查看授权内容
MariaDB [hellodb]>show grant for name@host; - 回收授权
MariaDB [hellodb]>revoke commod|all on database.(database|table|view) from name@host;
内容总结
以上是互联网集市为您收集整理的Linux 之MySQL数据库（二）全部内容，希望文章能够帮你解决Linux 之MySQL数据库（二）所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】