计算机教程

当前位置:澳门娱乐场网址 > 计算机教程 > Msql浅析-基础命令(二)

Msql浅析-基础命令(二)

来源:http://www.ablakeforum.com 作者:澳门娱乐场网址 时间:2019-05-17 14:43

图片 1

MySQL基本知识及练习(5)

1.求一个班级数学平均分。
(1). select sum(math) / count(math) as 数学平均分
from student;

(2). select avg(math) as 数学平均分
from student;

(3).select avg(name) as 小明平均分
from student;//0

2.求一个班级总分平均分。
(1).select (sum(chinese) sum(math) sum(english)) / count(*)
from student;

(2).select avg(chinese math english)
from student;

3.求班级语文最高分和最低分。
select max(name),min(name)
from student;

drop table if exists teacher;
create table teacher(
id int,
name varchar(20),
birthday date
);
insert into teacher(id,name,birthday) values(1,'jack','2011-1-1');
insert into teacher(id,name,birthday) values(2,'marry','2011-2-2');
insert into teacher(id,name,birthday) values(3,'sisi','2011-3-3');

select max(birthday),min(birthday)
from teacher;

4.对订单表中商品归类后,显示每一类商品的总价
select product as 类别名,sum(price) as 商品类别总价
from orders
group by product;

5.查询购买了几类商品,并且每类总价大于100的商品
select product as 类别名,sum(price) as 商品类别总价
from orders
group by product
having sum(price) > 100;

6.where v.s. having区别:

where主要用于行过滤器
having主要用于类别过滤器,通常有having就一定出现group by,但有group by的地方,不一定出现having。hving可以说是针对结果集在进行查询的。

drop table if exists teacher;
create table teacher(
id int primary key auto_increment,
name varchar(20) not null unique,
birthday date
);
insert into teacher(name,birthday) values(NULL,'2011-1-1');
insert into teacher(name,birthday) values('marry','2011-2-2');
insert into teacher(id,name,birthday) values(3,'sisi','2011-3-3');

select max(birthday),min(birthday)
from teacher;

7.删除主键,主键在表中只有一个,要么是一列,要么是多列
alter table teacher drop primary key;

8.一对一关系(方案一):
drop table if exists card;
drop table if exists person;

create table person(
id int primary key auto_increment,
name varchar(20) not null
);

insert into person(name) values('jack');
insert into person(name) values('marry');

create table card(
id int primary key auto_increment,
location varchar(20) not null,
pid int,
constraint pid_FK foreign key(pid) references person(id)
);

insert into card(location,pid) values('BJ',1);
insert into card(location,pid) values('GZ',2);
insert into card(location,pid) values('CS',NULL);
insert into card(location,pid) values('NJ',3);//出错

//删除person表的某记录
delete from person where name = 'jack';

9.一对一关系(方案二):
drop table if exists card;
drop table if exists person;

create table person(
id int primary key auto_increment,
name varchar(20) not null
);
insert into person(name) values('jack');
insert into person(name) values('marry');

create table card(
id int primary key auto_increment,
location varchar(20) not null,
constraint id_FK foreign key(id) references person(id)
);
insert into card(location) values('BJ');
insert into card(location) values('GZ');
insert into card(location) values('CS');//出错
insert into card(location) values(NULL);

10.一对多/多对一关系:
drop table if exists employee;
drop table if exists department;

create table department(
id int primary key auto_increment,
name varchar(20) not null
);
insert into department(name) values('软件部');
insert into department(name) values('销售部');

create table employee(
id int primary key auto_increment,
name varchar(20) not null,
did int,
constraint did_FK foreign key(did) references department(id)
);
insert into employee(name,did) values('jack',1);
insert into employee(name,did) values('marry',1);

11.问题?查询"软件部"的所有员工(组合式)
select d.name as 部门名,e.name as 员工名
from department as d,employee as e
where d.name = '软件部';

思考:还有没有其它方法?

分解:
(1)select id from department where name='软件部';
(2)select name from employee where did = 1;
(总)嵌入式SQL

select name as 员工
from employee
where did = (
select id
from department
where name='软件部'
);

12.多对多关系:
drop table if exists middle;
drop table if exists student;
drop table if exists teacher;

create table if not exists student(
id int primary key auto_increment,
name varchar(20) not null
);
insert into student(name) values('jack');
insert into student(name) values('marry');

create table if not exists teacher(
id int primary key auto_increment,
name varchar(20) not null
);
insert into teacher(name) values('赵');
insert into teacher(name) values('蔡');

create table if not exists middle(
sid int,
tid int,
constraint sid_FK foreign key(sid) references student(id),
constraint tid_FK foreign key(tid) references teacher(id),
primary key(sid,tid)
);
insert into middle(sid,tid) values(1,1);
insert into middle(sid,tid) values(1,2);
insert into middle(sid,tid) values(2,1);
insert into middle(sid,tid) values(2,2);

13.问题?查询"赵"所教过的所有学员
select t.name as 老师, s.name as 学员
from teacher as t,student as s,middle as m
where t.name = '赵'and m.sid=s.id and m.tid=t.id;

14.模式:
select 列出需要显示的字段
from 列出所涉及到的所有表,建议写别名
where 业务条件 and 表关联条件

15.使用MySQL特有函数:
到年底还有几少天?
select datediff('2011-12-31',now());

16.截取字符串
select substring('mysql',1,2); //从1开始

17.保留小数点后2位(四舍五入)
select format(3.1415926535657989,3);

18.向下取整(截取)
select floor(3.14);
select floor(-3.14);
select floor(3.54);
select floor(-3.54);

19.取随机值
select format(rand(),2);

20.取1-6之间的随机整数值
select floor(rand()*6) 1;

21.MySQL扩展知识:
查MySQL文档,利用MySQL的函数:随机产生'a'-'z'之间的随机字符。
随机产生'a'-'z'之间的随机字符
(1)查询'a'-'z'对应的Unicode值
select ascii('a');//97
select ascii('z');//122

(2)产生97-122之间的随机整数
select floor(rand()*26) 97;

(3)产生97-122对应的字符
select char(floor(rand()*26) 97);

22.查MySQL文档,利用MySQL的函数:对密码'123456'进行MD5加密。
select md5('123456');

drop table user;
create table user(
id int primary key auto_increment,
name varchar(20),
gender varchar(6),
salary float
);
insert into user(name,gender,salary) values('jack','male',4000);
insert into user(name,gender,salary) values('marry','female',5000);
insert into user(name,gender,salary) values('jim','male',6000);
insert into user(name,gender,salary) values('tom','male',7000);
insert into user(name,gender,salary) values('soso','female',NULL);
insert into user(name,gender,salary) values('haha','female',3500);
insert into user(name,gender,salary) values('hehe','female',4500);
select * from user;

23.MySQL特有流程控制函数:
1) if(value,第一值,第二值);
value为真,取第一值,否则取第二值
将5000元(含)以上的员工标识为"高薪",否则标识为"起薪"
类似于Java中的三目运算符

select if(salary>=5000,'高薪','起薪')
from user;

2) ifnull(value1,value2)
value1为NULL,用value2替代
将薪水为NULL的员工标识为"无薪"

select name as 员工,ifnull(salary,'无薪') as 薪水情况
from user;

3) case when [value] then [result1] else [result2] end;
当value表达式的值为true时,取result1的值,否则取result2的值(if...else...)
将5000元(含)以上的员工标识为"高薪",否则标识为"起薪"

select
case when salary>=5000 then '高薪'
else '起薪' end
from user;

4) case [express] when [value1] then [result1] when [value2] then [result2] else [result3] end;
当express满足value1时,取result1的值,满足value2时,取result2的值,否则取result3的值(switch...case..)
将7000元的员工标识为"高薪",6000元的员工标识为"中薪",5000元则标识为"起薪",否则标识为"低薪"

select
case salary
when 7000 then '高薪'
when 6000 then '中薪'
when 5000 then '起薪'
else '低薪' end
from user;

25.查询相同性别的员工总人数>2的工资总和,并按工资总和降序排列
select count(*) as 员人数,gender as 性别,sum(salary) as 工资和
from user
group by gender
having count(*)>2
order by sum(salary) desc;

26.将性别为男的员工工资-1000,性别为女的员工工资 1000,在一条SQL上完成
select if(gender='female',salary 1000,salary-1000) as 工资 from user;  

27.常用函数举例

select now();
select year(now());
select month(now());
select day(now());
select floor(datediff(now(),‘1999-01-01’)/365);//间隔年
select format(rand(),2);
select floor(rand()*5) 1;[1-5]随机值
select length(trim(' jack '));
select strcmp('a','w');

总结:

1 .关系的完整性

(1)实体(行)完整性:每条记录有一个唯一标识符,通常用无任何业务含义的字段表示
(2)参照完整性:一张(A)表的某个字段必须引用另一张(B)表的某个字段值,而且B表 的字段必须先存在。
(3)域(列)完整性:域即单元数据,域中的数值必须符合一定的规则,例如字段的值域、字 段的类型等的约束。

2 键的概念
(1)主键:只有唯一字段
(2)组合主键:由多个字段组合起来,形成唯一字段
(3)外键:针对多张表之间的关联

3 主键的特点
(1)主键不能重复
(2)主键不能为NULL
(3)auto_increment是MySQL特有的,默认从1开始,该ID值与表同生亡
(4)多人项目中,通常使用UUID来生成唯一的主键值,便于多个合并数据时依然保持实体完整性

4 唯一约束的特点
(1)非NULL值不能重复
(2)可以插入多个NULL值
(3)'NULL'空串和NULL是不同的概念

5 非空约束特点
(1)不能插入NULL值
(2)主键约束=非NULL约束 唯一约束

6 外健特点
(1)外键值必须来源于所引用别一个表主键值,或NULL

7 关联关系
(1)一对一(外健根业务有关)
(2)一对多或多对一(外键放置在多方)

(3)多对多(外健放置在关联表中,即将一个多对多拆分成二个一对多关系)

8.常用函数:
(1).日期函数:

图片 2

2.数学函数:

图片 3

3.字符串函数图片 4

http://www.bkjia.com/Mysql/862004.htmlwww.bkjia.comtruehttp://www.bkjia.com/Mysql/862004.htmlTechArticleMySQL基本知识及练习(5) 1.求一个班级数学平均分。 (1). select sum(math) / count(math) as 数学平均分 from student; (2). select avg(math) as 数学平均分 from...

篇幅简介

一、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; 

图片 5

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;

图片 6

2

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

for example

找出价格小于70的记录

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

 

图片 7

3

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

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

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

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

图片 8

4

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

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

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

图片 9

5

6、Mysql 子查询

select where条件中又出现select

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

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

 

图片 10

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;

图片 11

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;

图片 12

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; 

图片 13

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;

图片 14

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;

本文由澳门娱乐场网址发布于计算机教程,转载请注明出处:Msql浅析-基础命令(二)

关键词:

上一篇:Javascript闭包(Closure)详解

下一篇:没有了