数据库学习笔记(1) MySQL数据库的基本用法

创建数据库

create database dbname default character set utf8 collate utf8_general_ci;

新建数据表

drop table if exists `school`; //如果数据表存在，删除
create table `school`(
	`id` int(10) not null primary key comment '学校id',
	`school_name` varchar(500) comment '学校名称',
	`adress` varchar(1000) comment '地址');

插入表数据

insert into school (id,school_name,adress) values (1,'ceshi','五星小学');

更新表数据

update school set adress='希望小学' where id =1;

删除表数据

--1.清除表数据，但是不删除表结构
truncate table school;
--2.删除数据，删除表结构
drop table school;
--3.删除部分(指定的行)，不删除表结构
delete from school where school.id=1;

查询表数据

1.模糊查询

--1.查询以a结尾的数据
select * from user where username like '%a';
--2.查询包含a的数据
select * from user where username like '%a%';
--3.查询以a开头的数据
select * from user where username like 'a%';
--4.查询2位数据且以a结尾的数据
select * from user where username like '_a';
--5.查询3位数且中间字母是a的数据
select * from user where username like '_a_';

2.LIMIT限制查询结果

--1.查询用户表中的前5条数据
select * from user limit 5;
--2.查询username为'meng'的第三条数据对应的地址信息
select u.address from user as u where u.username='meng' limit 2,1;  //从第二条开始往后查1位
--3.分页查询中limit的用法
select * from user limit (pageNum-1)*pageSize,pageSize; //pageSize为5就是1-5,6-10,11-15

3.查询数据排序

--1.根据创建时间对查询到的数据倒序排序(指定排序方向)
select * from user order by create_time desc;
--2.按多列进行排序(先按创建时间，再按用户等级排序，都采用正向排序)
select * from user order by create_time,level;

4.正则匹配查询

--1.查询user表中username包含'ton'的所有数据
select * from user where username REGEXP 'ton';
--2.查询user表中username包含'ton'或者'jack'的所有数据
select * from user where username REGEXP 'ton|jack';
--3.查询user表中username中包含'.'符号的所有数据(匹配特殊字符)
select * from user where username REGEXP '\.';

5.聚合函数查询

--1.查询商品的平均价格(avg()函数)
select avg(price) as avg_price from product; 
--2.查询目前所有的用户数量(count()函数)
select count(*) as user_count from user;
--3.查询商品中价格最贵的商品(max()函数)
select max(price) as max_price from product;
--4.查询商品中价格最低的商品(min()函数)
select min(price) as min_price from product;
--5.查询商品的价格总和(sum()函数)
select sum(price) as total_price from product;

6.查询数据去重

--1.查询所有的水果种类，不展示重复的种类
select distinct name from fruit;
--2.查询商品的平均价，如果有部分相同的价格，只取一个
select avg(distinct price) as avg_price from product where vendorid='1001';

7.按分组查询

--1.查询每个用户下单了多少次(需要group by分组)
select t.userid,count(*) as order_count from trans as t group by t.userid;

8.过滤分组(having)

--1.查询每个用户下单了多少次,并过滤出下单次数大于1次的数据(group by ,having)
select t.userid,count(*) as order_count from trans as t group by t.userid having count(*)>=2;
--2.where和having的区别
   2.1having支持where的所有操作符
   2.2where在数据分组前过滤，having在数据分组后进行过滤

9.使用子查询

--1.利用子查询过滤
(1) 检索包含物品TNT2的所有订单的编号。
(2) 检索具有前一步骤列出的订单编号的所有客户的ID。
(3) 检索前一步骤返回的所有客户ID的客户信息。
select userid from order wehre orderid in (select orderid from orderitems where productid='TNT2');
--2.作为计算字段使用子查询
(1) 从customers表中检索客户列表。
(2) 对于检索出的每个客户，统计其在orders表中的订单数目
首先写出第二个条件对应的语句
select count(*) as order_count from orders where uid='111'
然后就可以分析写出最终的语句
select username,userstatus,(select count(*) as order_count from orders where orders.uid=customers.uid) as order_count from customers order by username;

10.联合查询

表A记录如下：
aID　　　　　aNum
1　　　　　a20050111
2　　　　　a20050112
3　　　　　a20050113
4　　　　　a20050114
5　　　　　a20050115

表B记录如下:
bID　　　　　bName
1　　　　　2006032401
2　　　　　2006032402
3　　　　　2006032403
4　　　　　2006032404
8　　　　　2006032408

10.1等值联结查询

select * from A,B where A.aID=B.aID;

10.2内部联结

select * from A inner join B on A.aID=B.bID;

10.3外部联结

select * from A left join B on A.aID=B.bID;

select * from A  join B on A.aID=B.bID;

11.组合查询uinon

--union去重，union all不去重，但查询效率快
--1.单表组合查询(也可以用uinon all)
select id,name,address from student where math_count>60
union 
select id,name,address from student where id in (1001,1002);
--2.多表组合查询(也可以用uinon all)
select id,name,address from student where math_count>60
union 
select id,school_name as name,school_adress as adress from school where school_status='0';

12.查询当前天0点和下一天0点

SELECT TIMESTAMP(DATE_ADD(CURDATE(), INTERVAL - 0 DAY)) AS m_starttime
SELECT TIMESTAMP(DATE_ADD(CURDATE(), INTERVAL + 1 DAY))AS m_endtime;

导出导入表数据

--1.导出整个表数据(testmeng为数据库，user为数据表)
mysqldump -u root -p testmeng user >testmeng_user.sql
--2.导出整个数据库
mysqldump -u root -p testmeng > testmeng.sql

--3.导入表数据
mysql>source c:/desk/testmeng.sql