Msql浅析-基础命令(二)

2018-07-09 13:38:43来源:博客园 阅读 ()

新老客户大回馈,云服务器低至5折


篇幅简介

一、Msql数据类型

1、整型

tinyint,  占 1字节 ,有符号: -128~127,无符号位 :0~255

smallint, 占 2字节 ,有符号: -32768~32767无符号位 :0~65535

mediumint 占 3字节 ,有符号: -8388608~8388607,无符号位:0~16777215:

int, 占 4字节 ,有符号: -2147483648~2147483647,,无符号位 无符号位 :0~4 284967295

bigint, bigint,bigint, 占 8字节

bool  等价于 tinyint

2、浮点型

float([m[,d]])  占 4字节 ,1.17E-38~3.4E+3838~3.4E

double([m[,d]])  占 8字节

decimal([m[,d]])  以字符串形式表示的浮点数 

3、字符型

char([m]): :定长的字符 ,占用 m字节

varchar[(m)]::变长的字符 ,占用 m+1m+1 字节,大于 255 个字符:占用 m+2m+2

tinytext,255 个字符 (2 的 8次方 )

text,65535 个字符 (2 的 16 次方 )

mediumtext,16777215字符 (2 的 24 次方 )

longtext (2的 32 次方 )

enum(value,value,...)占 1/2个字节 最多可以有 65535 个成员 个成员

set(value,value,...) 占 1/2/3/4/8个字节,最多可以 有 64个成员

二、Mysql数据运算

1、逻辑运算 and or not

for example:

选择出 书籍价格 为(30,60,40,50)的记录

sql> select bName,publishing,price from books where price=30 or price=40 or price=50 or price=60; 


1

2、in 运算符

in 运算符用于 WHERE 表达式,以列表的形式支持多个选择,语法如下

where colunmm in (value1,value2,.......)

where colunmm not in (value1,value2,..........)

当in前面加上not时,表示与in相反,既不在结果中

sql> select bName,publishing,price from books where  price in (30,40,50,60)order by price asc;


2

3、算术运算符  >= | <=| <> |=

for example

找出价格小于70的记录

mysql> select bName,price from books where price <= 70;

 


3

4、模糊查询  like '%...%'

字段名 [not] like  '%......%'   通配符  任意多个字符

查询书中包含程序字样的记录

mysql> select bName,price from books where bName like '%程序%'


4

5、范围运算 [not] between .......and

查找价格不在30和60之间的书名和价格

mysql> select bName,price from books where price not between 30 and 60  order by price desc;


5

6、Mysql 子查询

select where条件中又出现select

查询类型为网络技术的图书

mysql> select bName,bTypeId from books where bTypeId=(select bTypeId from category where bTypeName='网络技术');

 


6

7、limit 限定显示的条目

LIMIT子句可以被用于强制 SELECT语句返回指定的记录数。 LIMIT 接受一个或两数字参。必 须是一个整数常量。如果给定两 个数,第一指定返 回记录行的偏移量,第二个参数返回记录行的最大数目。初始偏移量是 0( 而不是 1)。

语法 : select * from limit m,n

其中 m是指记录开始的 index indexindex,从 0开始,表示第一条记录,n是指从第 m+1 条开始,取 n。

查询books表中第2条到六行的记录

mysql>select * from books limit 1,6;


7

8、连接查询

以一个共同的字段,求两张表当中符合条件并集。 通过 共同字段把这两张表的共同字段把这两张表连 接起来。

常用的连接:

内连接:根据表中的共同字段进行匹配

外连接:现实某数据表的 全部记录和另外数据表中符合连接条件的记录。

外连接:左连接、右连接

内连接:for exmaple

create table student(sit int(4) primary key auto_increment,name varchar(40));

insert into student values(1,‘张三’),(2,‘李四’),(3,‘王五’),(4,‘mikel’);

create table teachers(sit int(4),id int(4) primary key auto_increment,score varchar(40));

insert into teachers values(1,1,‘1234’),(1,2,‘2345’),(3,3,‘2467’),(4,4,‘2134’);

select s.* ,t.* from student as s,teachers as t where s.sid=t.sid;


8

左连接: select 语句 a表 left[outer] join b 表  on 连接条件 ,a表是主,都显示。

b表是从,主表内容全都有,主表多出来的字段,从表没有的就显示 null,从表多出主表的字段不显示。

select * from student as s left join teachers as t on  s.sit=t.sit; 


9

右连接:select 语句 a表 right[outer] join b 表  on 连接条件 ,b表是主,都显示。

a表是从,主表内容全都有,主表多出来的字段,从表没有的就显示 null,从表多出主表的字段不显示。

select * from student as sright join teachers as t on  s.sit=t.sit;


10

三、聚合函数

1、sam() 求和

select sum (id+score) as g from teachers;

2、avg() 求平均值

select avg (id+score) as g from teachers;

3、max() 最大值

select max (id) as g from teachers;

4、min() 最小值

select min(id) as g from teachers;

5、substr(string,start,len) 截取

select substr(soucr,1,2) as g from teachers;

从start开始,截取len长度,start从1开始

concat(str1,str2,str3......................)字符串拼接,将多个字符串拼接在一起

select concat(id,score,sit) as g from teachers;

6、count() 统计计数 记录字段数据条数

select count(id) as g from teachers;

7、upper() 大写

select upper(name) as g from student;  #将字段name中英文全部变为大写,但不改变原值

8、lower() 小写

select lower(name) as g from student;  #将字段name中英文全部变为小写,但不改变原值

四、索引

mysql中索引是以文件形式存放的,对表进行增删改,会同步到索引,索引和表保持一致,常用在where 后字段查询就加索引。

优点:加快查询速度,减少查询时间

缺点:索引占据一定磁盘空间,会影响insert,delete,update执行时间

1、索引类型

普通索引:最基本索引,不具备唯一性

唯一索引:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一

主键索引:记录值唯一,主键字段很少被改动,不能为空,不能修改,可用于一个字段或者多个字段

全文索引:检索文本信息的, 针对较大的数据,生成全文索引查询速度快,但也很浪费时间和空间

组合索引:一个索引包含多个列

2、创建索引

普通索引:

# 创建普通索引

create table demo(id int(4),uName varchar(20),uPwd varchar(20),index (uPwd));

# 查看建表过程

show create table demo;

demo | CREATE TABLE `demo` (

  `id` int(4) DEFAULT NULL,

  `uName` varchar(20) DEFAULT NULL,

  `uPwd` varchar(20) DEFAULT NULL,

  KEY `uPwd` (`uPwd`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

唯一索引:字段值只允许出现一次,可以有空值

# 创建唯一索引

create table demo1(id int(4),uName varchar(20),uPwd varchar(20),unique index (uName));

# 查看建表过程

show create table demo1;

demo1 | CREATE TABLE `demo1` (

  `id` int(4) DEFAULT NULL,

  `uName` varchar(20) DEFAULT NULL,

  `uPwd` varchar(20) DEFAULT NULL,

  UNIQUE KEY `uName` (`uName`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

主键索引:字段记录值唯一,字段很少被修改,一般主键约束为auto_increment或者not null unique,不能为空,不能重复。

# 创建主键索引

create table demo2(id int(4) auto_increment primary key,uName varchar(20),uPwd varchar(20));

# 查看建表语句

demo2 | CREATE TABLE `demo2` (

  `id` int(4) NOT NULL AUTO_INCREMENT,

  `uName` varchar(20) DEFAULT NULL,

  `uPwd` varchar(20) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

全文索引:提高全文检索效率,解决模糊查询

# 创建全文索引

create table demo3(id int(4),uName varchar(20),uPwd varchar(20),fulltext(uName,uPwd));

# 查看建表语句

| demo3 | CREATE TABLE `demo3` (

  `id` int(4) DEFAULT NULL,

  `uName` varchar(20) DEFAULT NULL,

  `uPwd` varchar(20) DEFAULT NULL,

  FULLTEXT KEY `uName` (`uName`,`uPwd`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

五、外键约束

外键约束:foreign key 表与表之间的一种约定关系,由于这种关系存在,让表与表之间的数据更加具有完整性,更加具有关联性。

创建外键约束

创建user主表

create table user1(id int(11)auto_increment primary key,name varchar(50),sex int(1));

插入数据

insert into user1(name,sex)values("mikel",4),("plyx",6);

创建order外键表

create table `order`(order_id int(11)auto_increment primary key,u_id int(11),username varchar(50),monery int(11),foreign key(u_id) references user1(id)  on delete cascade on update cascade )engine=innodb);

插入数据

INSERT INTO `order` (order_id,u_id,username,monery)values(1,1,'mikel',2345),(2,2,'plyx',3456)

测试级联删除

delete from user1 where id=1

查看order表记录


12

测试级联更新

update  user1 set id=5 where id=2


13

测试数据完整性

在order表中插入一条u_id为6的记录

insert into `orser` (u_id)values(6);

Cannot add or update a child row: a foreign key constraint fails (`school`.`order`, CONSTRAINT `order_ibfk_1` FOREIGN KEY (`u_id`) REFERENCES `user1` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

user1中不存在id为6的记录,现在添加一条id为6的记录

insert into user1(id)values(6);


14

可以看到数据已经插入进去了

视图

是一张虚拟表,由 select select select语句指定的数据结构和数据,不生成真实文件

create view mikel as select * from school.books;

select  * from mikel;


15

六、存储过程

存储过程用来封装mysql代码,相当于函数,一次编译,生成二进制文件,永久有效,提高效率。

1、定义存储过程

create procedure 过程名(参数1,参数2,.............)

begin

      sql语句

end

2、调用存储过程

call 过程名(参数1,参数2,...................)

example:定义一个存储过程查看books表中所有数据

    1. 修改sql默认执行符号

    delimiter //

   create  procedure seebooks();

   begin

         select * from sctudent.books;

    end //

    call seebooks() //


16

3、存储过程参数传递

in 传入参数 int 赋值

IN输入参数:表示调用者向过程传入值(传入值可以是字面量或变量)

OUT输出参数:表示过程向调用者传出值(可以返回多个值)(传出值只能是变量)

INOUT输入输出参数:既表示调用者向过程传入值,又表示过程向调用者传出值(值只能是变量)

create procedure seebook(in b int)

begin

  select * from school.books where bId=b;

end //

call seebook(4)


16

out --------------传出参数

select into 在过程中赋值传给变量,并查看变量值

create procedure seebook2(out b varchar(100))

begin

  select bName into b  from school.books where bId=4;

end //


17

过程内的变量使用方法

声明变量名称,类型,declare 过程内的变量没有@

赋值 set 变量名=(select 语句)

create procedure seebook3()

begin 

        declare str varchar(100);

        set str=(select bName from school.books where bId=20);

        select str;

end//

call seebook3() //


18

1、触发器

与数据表有关,当表出现(增,删,改,查)时,自动执行其特定的操作

语法:create trigger 触发器名称 触发器时机 触发器动作 on 表名 for each row

触发器名称:自定义

触发器时机:after/before   之后/之前

触发器动作:insert  update  delete

创建触发器:

 create trigger delstudent after delete on grade for each now

delete from student where sid='4';

delete from grade where sid=4;

mysql> select sid from student where sid=4;

Empty set

查看是否还有sid=4的值,可以发现已经被删除

2、事务

单个逻辑单元执行的一系列操作,通过将一组操作组成一个,执行的时要么全部成功,要么全部失败,使程序更可靠,简化错误恢复。

MySQL 事务主要用于处理操作量大,复杂度高的数据。比如说,在人员管理系统中,你删除一个人员,你即需要删除人员的基本资料,也要删除和该人员相关的信息,如信箱,文章等等,这样,这些数据库操作语句就构成一个事务!

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

事务处理可以用来维护数据库的完整性,保证成批的 SQL 语句要么全部执行,要么全部不执行。事务用来管理 insert,update,delete 语句。

MYSQL 事务处理主要有两种方法:

1、用 BEGIN, ROLLBACK, COMMIT来实现

BEGIN 开始一个事务

ROLLBACK 事务回滚

COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

SET AUTOCOMMIT=0 禁止自动提交

SET AUTOCOMMIT=1 开启自动提交

创建事务

begin;

update books set bName="plyx" where bId=1;

update books set bName="plyx" where bId=2;

commit//

查看记录,已经修改了

select * from books;


19

七、mysql数据结构

主配置文件  my.cnf

数据目录:/var/lib/mysql

进程通信sock文件 :/var/lib/mysql/mysql.sock

错误日志文件

[mysqld_safe]

log-error=/var/log/mysqld.log

进程PID文件:pid-file=/var/run/mysqld/mysqld.pid

二进制文件:log-bin=mysql-bin.log

八.常见的存储引擎介绍

myisam :

特性: 1、不支持事务,不支持外键,宕机时会破坏表

            2、使用较小的内存和磁盘空间,访问速度快

            3、基于表的锁,表级锁

            4、mysql 只缓存index索引, 数据由OS缓存

适用场景:日志系统,门户网站,低并发。

Innodb:

特性:1、具有提交,回滚,崩溃恢复能力的事务安全存储引擎

           2、支持自动增长列,支持外键约束

           3、占用更多的磁盘空间以保留数据和索引

           4、不支持全文索引

适用场景:需要事务应用,高并发,自动恢复,轻快基于主键操作

MEMORY:

特性:1、Memory存储引擎使用存在于内存中的内容来创建表。

           2、每个memory表只实际对应一个磁盘文件,格式是.frm。memory类型的表访问非常的快,因为它的数据是放在内存中的,并且默认使用HASH索引,但是一旦服务关闭,表中的数据就会丢失掉。 

          3、MEMORY存储引擎的表可以选择使用BTREE索引或者HASH索引。

九、思考与总结

到此主要介绍,mysql一些使用技巧,包括数据类型,查询方法,存储过程,外键约束,索引。触发器,事务,还包含一些存储引擎介绍,到此基础部分结束,还有后面的分享将会陆续推出,敬请期待!


总结

我是MIkel Pan,云计算爱好者,定期更新生活感悟,心灵进化者就在MIkel Pan,喜欢我就来找我吧!

博客园地址:http://www.cnblogs.com/plyx/

简书地址:https://www.jianshu.com/u/5986765934f4

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:升级MySQL5.7,开发不得不注意的坑

下一篇:mysql中列属性