MySQL 语句命令的使用 第二讲

MySQL 语句命令的使用 第二讲

操作数据的查询

1． distince的使用：是过滤重复记录，

如果DISTINCT后面跟多列, 是过滤掉多列合并之后的重复

如果在创建表时，为防止重复记录，可以在创建表的时候创建索引，指定唯一值----- uniqune 例如：

查询employee表中所有人的薪水

Select distinct username, salary

from user;

.2. 列名表达式

表达式只是显示时起作用, 不会改变数据库中的值，例如：

查询所有学生的总分

select name , chinese+math+english

from student;

3. 给列表起别名 a s

注：起别名时AS可以省略

不会改变数据库中的值

例子：

查询所有学生总分, 别名为总分

select name '姓名', chinese+math+english as '总分'

from student;

4．where语句的使用

（1）查询英语分数在80-90分之间的

select name,english

from student

where english>=80 and english<=90;

（2）查询语文分数为81,82,83的学生

select name,english

from student

where english in(80,90,82);

或者是：

select nane,English

from student where English=80 or enhlish=90 or english=90;

（3）查询所有姓张的学生的成绩

select name,english,math,Chinese

from student

where name like '张%';

（4）查询除了姓张和姓李的学生总分

select name,english,math,chinese

from student

where name not like '张%'

and name not like '李%';

或者是：

select name,english,math,chinese

from student

where name like '张%'

or name like '李%';

5. 对查询的数据进行排序------------order by

注：order by指定排序的列名可以是表中的列名, 也可以是SELECT语句后面起的别名

ASC为升序, DESC为降序

order by 应在查询语句的结尾

例子：

（1）对数学成绩排序后输出

select name,math

from student

order by math;

（2）查询总分, 从高到低显示

select name '姓名' , chinese+math+english '总分'

from student

order by 总分 desc;

（3）选择所有姓张的学生的英语成绩, 并从高到低排序

select name , english

from student

where name like '张%'

order by english desc;

（4）查询学生成绩, 按照语文从高到低排序, 如果语文相同, 按照英语从高到低排序

select * from student

order by chinese desc , english desc;

6. count函数

count(列名)的方式是统计指定列中有多少条记录, 不包括值为NULL的

count(*)则是统计表中有多少条数据

count(distince 列名) 统计不重复的记录数

如果加上where子句, 则是统计满足条件的记录

例子：

（1）统计student表中有多少条记录

select count(*) from student;

（2）统计学生语文成绩大于80的有多少人

select count(*) from student

where chinese>80;

（3）统计总分大于250的有多少人

select count(*) from student

where english+math+chinese>250;

（4）统计参加英语考试的有多少人

select count(english) from student;

7. sum函数

计算指定列中所有记录的和, 如果有WHERE子句则计算满足条件的记录

例子：

（1）计算所有学生的数学成绩总和

select sum(math) from student;

（2）显示所有学生的语文成绩总和, 数学成绩总和, 英语成绩总和

select sum(chinese),sum(math),sum(english) from student;

（3）计算所有学生的分数总和

select sum(chinese)+sum(math)+sum(english) from student;

（4）统计英语平均分

select sum(english)/count(*) from student;

select sum(english)/count(english) from student;

8．Avg函数

说明：计算指定列的平均值, 如果有WHERE子句, 则计算满足条件的记录

AVG()统计平均数不包含NULL值

例子：

（1）计算英语平均分

select avg(english) from student;

（2）计算总分平均分, MySQL不支持组函数嵌套使用.

select sum(english+math+chinese)/count(*) from student;

9．分组-----group by

例子：

（1）对订单表归类, 显示购买过哪些商品

select product from orders group by product;

select distinct product from orders;

（2）对订单表归类, 显示购买过哪些商品, 并显示每种购买了几个, 以及总价

select product,count(product),sum(price)

from orders group by product;

（3）查询总价大于5000的商品有哪几类

select product,count(product),sum(price) sum_price from orders group by product having sum_price>5000;

10．时间函数的使用

注意date, datetime, timestamp (1971-2037年之间)之间的区别

1． Timestamp会随着系统的不同而改变

2． Datetime 不会随着系统的不同而改变

ADDTIME(原时间, 增加值) 在某个时间上增加一段时间

select addtime('18:23:01', '01:01:01');

select addtime(now(),'3:0:0'); now(),获取当前的时间

CURRENT_DATE() 当前日期

select current_date();

CURRENT_TIME() 当前时间

select current_time();

CURRENT_TIMESTAMP() 当前时间戳

select current_timestamp();

DATE(时间) 返回制定时间的日期部分

select date('2011-02-14 18:00:00');

DATE_ADD(日期,INTERVAL 增加值 类型) 在指定日期上对某个字段增加

select date_add('2011-02-14 23:00:00', interval 10 month); interval是增加的关键字

DATE_SUB(日期,INTERVAL 减少值 类型) 在指定日期上对某个字段减少

select date_sub('2011-02-14 23:00:00', interval 1 year);

DATEDIFF(日期1, 日期2) 计算两个日期之间的差值

select datediff('2000-02-14', '2001-02-14');

NOW() 当前时间

select now();

YEAR|MONTH|DATE|HOUR|MINUTE|SECOND(时间) 获取指定时间的某个字段

select year('2011-02-14 23:00:00');

select hour('2011-02-14 23:00:00');

11．字符串函数

CHARSET(字符串) 返回字符串字符集

select charset(name) from student;

CONCAT(字符串1[, 字符串2]... ) 连接多个字符串

select concat('aaa', 'bbb', 'ccc');

INSTR(字符串, 子字符串) 查找子字符串出现位置, 注意序号从1开始

select instr('abc', 'a');

UCASE(字符串) 将字符串转为大写

select ucase('aBc');

LCASE(字符串) 将字符串转为小写

select lcase('aBc');

LEFT(字符串, 长度) 从字符串左边取指定长度个字符

select left('aBc',2);

LENGTH(字符串) 计算字符串长度

select length('aBc');

REPLACE(字符串, 搜索字符串, 替换字符串) 将字符串中指定字符串替换为其他字符串

select replace('abbcbbd', 'bb', 'ee');

STRCMP(字符串1, 字符串2) 逐个字符比较两个字符串, 如果是包含关系, 则返回长度差值 0 1 -1

select strcmp('abcc', 'abde');

select strcmp('abc', 'ab');

SUBSTRING(字符串, 开始坐标[, 个数]) 从字符串中截取

select substring('abcdef', 3);

select substring('abcdef', 3, 2);

LTRIM(字符串) 去掉左边空白

select ltrim(' abc ');

select concat('--', ltrim(' abc '), '--');

RTRIM(字符串) 去掉右边空白

select concat('--', rtrim(' abc '), '--');

TRIM(字符串) 去掉左右两边空白

select concat('--', trim(' abc '), '--');

12 数学函数

ABS(数字) 求绝对值

select abs(10);

select abs(-10);

BIN(十进制数) 将十进制转换为二进制

select bin(5);

HEX(十进制数) 将十进制转换为十六进制

select hex(10);

CONV(数字, 原进制, 目标进制) 转换进制

select conv(12, 10, 16);

select conv(12, 10, 2);

select conv(12, 16, 2);

CEILING(小数) 向上取整

select ceiling(3.4);

FLOOR(小数) 向下取整

select floor(3.4);

ROUND(小数) 四舍五入

select round(3.4);

select round(3.5);

FORMAT(小数, 保留位数) 保留小数位

select format(3.1415926, 2);

LEAST(值,值[,值]...) 取最小值

select least(1,2,3,4);

select least('a', 'b', 'c', 'd');

GREATEST(值,值[,值]...) 取最大值

select greatest(1,2,3,4);

select greatest('a', 'b', 'c', 'd');

MOD(数字, 数字) 取余

select mod(3,2);

select 3%2;

RAND() 生成随机数, 14位小数, 0 <= n <= 1

select rand();

13．唯一性约束

unique约束的字段在整张表中唯一, 不可重复, 不包括多个NULL

创建表时设置唯一

create table test (

id int,

name varchar(20) unique

);

删除唯一约束

show create table test; 发现唯一索引名叫name

alter table test

drop index name;

添加唯一约束

alter table test

change name name varchar(20) unique;

14．非空的约束

not null约束的字段不能为空

创建表时设置非空

create table test1 (

id int,

name varchar(20) not null

);

删除非空约束

alter table test1 change name name varchar(20);

添加非空约束

alter table test1 change name name varchar(20) not null;

15．主键约束 primary key

通常我们在设计表的时候需要给每一条记录一个独有的标识, 我们就用主键来约束这个标识.

primary key用来标识一个字段, 这个字段是非空且唯一的.

创建表时设置主键

create table test2(

id int primary key,

name varchar(20)

);

删除主键

alter table test2 drop primary key;

在制定列上添加主键

alter table test2 change id id int primary key;

alter table test2 add primary key(id);

设置主键自动增长

create table test3(

id int primary key auto_increment,

name varchar(20)

);

删除自增长

alter table test3 change id id int;

设置自增长

alter table test3 change id id int auto_increment;

UUID主键

128位的2进制, 32位16进制加上4个-

java.util.UUID.randomUUID().toString()

3c2372a4-da2a-4470-b17a-f2e50ac79636

16．外键约束 foreign key

foreign key约束某一列的值是参照另外一列

创建表时添加外键约束

create table husband(

id int primary key,

name varchar(20) not null

);

create table wife(

id int primary key,

name varchar(20) not null,

husband_id int,

constraint husband_id_fk foreign key(husband_id) references husband(id)

);

wife表的husband_id的值必须是husband表中的id

被外键引用的记录不能删除, 如果想要删除某条被引用的记录, 需要找到引用这条记录的记录, 解除关联

被外键引用的表不能删除, 如果想要删除被引用的表, 需要删除所有引用此表的外键

删除外键约束

alter table wife drop foreign key husband_id_fk;

添加外键约束

alter table wife add constraint husband_id_fk foreign key(husband_id) references husband(id)

17．表的设计

表的设计分为多对一、一对多、多对多

在设置外键时，多的设为外键