MySQL数据库-DAY03

修改表格整体结构

alter改变表的结构

添加列

altertable 表名 add 列名 数据类型;

 

删除列

altertable 表名 drop column 列名;

 

添加列，并设定默认值：

altertable 表名 add 列名 数据类型 default 默认值;

 

修改表中某列的数据类型

altertable 表名 modify column 列名 数据类型;

例子：

altertable orders modify column amount float;

 

        微观修改表中的元素    宏观修改表的整体结构

添加    insert                alter table...add...

删除    delete                alter table...drop column...

修改    update                alter table...modify column...

 

修改表名

altertable 表名 rename 新的表名;

 

修改列名

altertable 表名 change 旧名字 新名字 数据类型;

 

对表添加唯一限制

altertable 表名 add constraint MyUniqueConstraint unique(列名1,列名2...，列名N);

 

对表取消唯一限制

altertable 表名 drop index MyUniqueConstraint;

 

对表添加主键限制

altertable 表名 add primary key(列名1，列名2，...,列名n);

 

对表取消主键限制

altertable 表名 drop primary key;

 

对表添加外键限制

altertable 表名

addforeign key(列名1，列名2，...,列名N)

references另一个表的表名(列名1，列名2，...,列名N);

 

例子：

altertable orders

addforeign key(c_id)

referencescustomers(id);

 

对表取消外键限制

altertable 表名 drop foreign key 自动生成的外键限制名;

【注意】

1.用showcreate table查看系统自动生成的限制名

2.是限制名，不是外键名

 

altertable orders drop foreign key orders_ibfk_1;

 

altertable customers drop primary key;

 

对表添加非空限制

altertable 表名 modify 列名 数据类型 not null;

 

对表取消非空限制

altertable customers modify name varchar(20) default null;

insertinto customers values (9,null,null,null,null,null);

 

truncate table

保留表头，也即保留表的格式，只删除表中存放的数据内容

 

truncate table 表名;

【注意】

1.truncatetable 与 delete table 效果一样

2.droptable不仅删除表中的数据，连表的骨架也一起删除，需要用create table 和 insert into table重新建立表

 

------------------------------------------------------------------------------

子查询

【注意】

1.必须用括号包起来

2.order by 不能用于子查询，可以用groupby

3.子查询返回多行的情况，只能用多数据操作符，比如in

4.between操作符不能连接子查询；但是，可以在子查询里面使用between

 

第一类：select语句中嵌套select语句

select列名或者表达式 from 表名

where表达式 比较操作符 (子查询语句);

 

1.返回单值的子查询

select* from customers

wherecustomers.id = (select c_id from orders where o_id = 107);

 

2.返回多值的子查询

select* from customers

wherecustomers.id in (4,5);

 

selectname from customers

whereid in (select c_id from orders where o_name = 'ipad');

 

 

查看顾客的消费大于7000元，同时地址是北京或者上海的顾客信息

提示：in,and,or

 

select* from customers

wherecustomers.id in

(selectc_id from orders where amount > 7000)

and(address = 'Beijing' or address = 'Shanghai')

 

 

练习题：

查看买过价值1万元以上的ipad的客户信息

select* from customers

whereid in (select c_id from orders where o_name = 'ipad' and amount > 10000);

 

子查询与聚合函数配套练习

思考题：

选择消费最少的顾客的信息

select* from customers

whereid =

(selectc_id from orders where amount =

(selectmin(amount) from orders));

 

打印出orders表中amount的最小值

selectmin(amount) from orders; #200

练习题：

列出customers表中,全体顾客的平均工资

selectavg(salary) from customers;   #6587.5

 

 

哪些顾客的c_id，其消费额度amount大于 全体顾客的平均工资

selectc_id from orders

whereamount > (select avg(salary) from customers); #1,3,4,7,5

 

列出customers表中，消费额度大于 全体顾客的平均工资，这样的顾客的全部信息

select* from customers

whereid in

(selectc_id from orders

whereamount > (select avg(salary) from customers));

 

 

练习题*：

列出工资高于平均消费额的客户的总个数

提示：count(*)

selectcount(*) from customers

wheresalary > (select avg(amount) from orders);

 

group by ... having...

having语句嵌套子查询

 

思考题：

1.查询customers表中，各个城市对应的平均工资

selectaddress, avg(salary) from customers

groupby address;

 

2.查询customers表中，address以Sh开头的顾客信息

select* from customers

whereaddress like 'Sh%';

 

3.查询customers表中，城市对应的平均工资大于5000，并且以Sh开头的城市信息

selectaddress,avg(salary) from customers

groupby address

havingavg(salary) > 5000 and address like 'Sh%';

 

selectaddress,avg(salary) from customers

groupby address

havingavg(salary) > 5000 and address in ('Shenzhen','Shanghai');

 

 

第二类：insert语句中嵌套select语句

insertinto 表名 (列名1，列名2,...,列名n)

select列名1，列名2,...,列名n from 表名2

where条件;

 

练习题*：

使用嵌套查询，把customer表中消费额度大于8000元的客户的全部消息，存放在customers2表中

 

truncatetable customers2;

 

insertinto customers2

selectid,name,age,address,salary from customers

whereid in

(selectc_id from orders where amount > 8000);

 

 

 

第三类：delete语句中嵌套select语句

deletefrom 表名1

where列名 操作符

（select 列名 from 表名2where 条件）;

 

【注意】

delete 与 update语句嵌套子查询的时候，子查询中涉及的表不同于主句中的表

 

练习题：

把customers2 中，将salary小于customers表中平均工资的顾客信息删除

 

deletefrom customers2

wheresalary <

(selectavg(salary) from customers);

 

 

 

第四类：update语句中嵌套select语句

update表名1

set列名 操作符 新的值

where列名 操作符

(select列名 from 表名2 where 条件);

 

练习题：

1.先把customers2表中数据清空

提示：truncate

 

truncatetable customers2;

 

2.把customers表中的数据复制到customers2中

提示：insert..select..

 

insertinto customers2

selectid,name,age,address,salary from customers;

 

3.把customers2表中，年龄大于customers表中平均年龄的顾客的工资翻倍

提示：update..select..

 

updatecustomers2

setsalary = salary * 2

whereage > (select avg(age) from customers);

 

聚合函数

练习题：

求出customers表中一共有多少顾客，其工资大于所有人的平均工资

 

selectcount(*) from customers

wheresalary > (select avg(salary) from customers);

 

mid(列名，起始位置，长度)

substring(列名，起始位置，长度)

 

例子：

selectmid(name,1,3) from customers;

【注意】

1：从起始位置为1开始

3：打印出三个字母的长度

第一个字母的位置是1，不是0

 

思考题：

用substring函数，打印出orders表中o_name列，从第3个字母开始，打印2个字母长度

selectsubstring(o_name,3,2) from orders;

 

SQL Scalar函数:基于输入值，返回一个单一的值

ucase()：转化为大写字母

lcase():转化为小写字母

 

例子：

selectucase(name) from customers;

selectlcase(name) from customers;

 

length():长度

selectname,length(name) from customers;

lengthb()长度，常用于有汉字的语句，查看字段的长度。

 

round():对某个数值字段进行指定小数位数的四舍五入

selectround(salary,1) from customers;

 

concat()：合并两个或多个字符串，成为一个字符串

selectconcat(o_name,' ',amount) from orders;

 

replace():搜索和替换一个字符串中的子字符串

update表名

set列名 = replace(列名，需要被替换的子字符串，用来代替的新的子字符串)

where条件;

 

例子：

updateorders set o_name = replace(o_name,'i','x');

 

 

trim():去除字符串中不想要的空字符

ltrim():left左边

rtrim():right右边

 

例子：

selecttrim('  Hi,Wei   ');

selectltrim('  Hi,Wei   ');

selectrtrim('  Hi,Wei   ');

 

 

format():格式化某个字段的显示方式

format(要格式化的数字,需要保留的小数点位置);

 

例子：

selectformat(12345.6789, 2);

 

SQL日期和时间函数

curdate():返回当前日期

2018-07-13

查看数据库当前时间

Selectnow();

2018-07-13 09:40:57

例子：

对比如下两种写法的区别

selectcurdate();

2018-07-13

selectcurdate() + 0;

20180713

selectdate(now());

2018-07-13

datediff: date difference计算出两个日期之间相差的天数

selectdatediff('2015-05-23','2017-11-23');

 

date_add():添加一个时间值

date_add(起始日期，interval表达式 单位)】

 

例子：

selectdate_add('1999-12-31 23:59:59',interval 1 second) result;

 

selectdate_add('1999-12-31 23:59:59',interval 1 day) result;

 

 

date_sub():减少一个时间值

selectdate_sub('2016-07-04',interval 1 day) result;

 

str_to_date():将字符串转为日期类型

selectstr_to_date('21,5,2013','%d,%m,%Y');

 

%Y年，注意一定是大写的Y

%m月

%d日

%H小时

%i分钟

%Ttime时间

%W星期几

 

date_format()调整日期显示的格式

selectdate_format('2017-12-25 23:59:59', '%Y,%m,%d');

selectdate_format('2017-12-25 23:59:59', '%T');

selectdate_format('2017-12-25 23:59:59', '%W');

selectdate_format('2017-12-25 23:59:59', '%H:%i');

 

 

练习题：

1.求出customers表中，地址为北京的顾客的总收入

selectsum(salary) from customers where address = "beijing";

 

2.选出customers表中，工资大于所有顾客平均工资的人，并且以大写字母显示姓名，以小写字母显示地址

selectucase(name),lcase(address) from customers

wheresalary > (select avg(salary) from customers);

 

3.列出customers表中的工资（只取正数部分），工资的位数

selectround(salary,0),length(salary) from customers;