【数据库-初阶-速成】Mysql案例实战教程

一、MySQL的安装和基本使用

略

二、Mysql的数据类型

• MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串（字符）类型。

• 备注：char和varchar一定要指定长度，float会自动提升为double、timestamp是时间的混合类型，理论上可以存储时间格式和时间戳。

类型	用途
int	整型，相当于java的int
bight	整型，相当于java的long
float	浮点型
double	浮点型
datetime	日期类型
timestamp	日期类型（可存储时间戳）
char	定长字符
varchar	不定长字符
text	大文本，用于存储很长的字符内容
blob	字节数据类型，存储图片、音频等文件

三、建表操作

• 语法

--删除表

DROP TABLE IF EXISTS 表名

--新建表

create table 表名(

字段名 类型 约束(主键、非空、唯一、默认值)，

字段名 类型 约束(主键、非空、唯一、默认值)，

)编码，存储引擎；

在SQL中，我们有如下约束：

• NOT NULL--指示某列不能存储NULL值。

• UNIQUE-保证某列的每行必须有唯一的值。

• PRIMARY KEY-NOT NULL和UNIQUE的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。

• FOREIGN KEY-保证一个表中的数据匹配另一个表中的值的参照完整性。

• CHECK-保证列中的值符合指定的条件。

• DEFAULT-规定没有给列赋值时的默认值。

• 实例

DROP TABLE IF EXISTS 'websites';
 ​
 CREATE TABLE 'websites' (
 ​
 id int(11) NOT NULL AUTO_INCREMENT,
 ​
 name char(20) NOT NULL DEFAULT '' COMMENT '站点名称',
 ​
 url varchar(255) NOT NULL DEFAULT '',
 ​
 alexa int(11) NOT NULL DEFAULT '0' COMMENT 'Alexa排名',
 ​
 sal double COMMENT '广告收入'，
 ​
 country char(10) NOT NULL DEFAULT '' COMMENT '国家',
 ​
 PRIMARY KEY(id)
 ​
 )ENGINE=InnoDB DEFAULT CHARSE=utf8;

四、插入、删除、更新

• 插入语句

INSERT INTO websites(name,url,alexa,sal,country) VALUES ('腾讯','腾讯网',18,1000,'CN');

• 删除语句

delete from websites where id=5;

• 更新语句

update websites set sal =null where id=3

五、基本select查询语句

• 初始化数据

 DROP TABLE IF EXISTS 'websites';
 ​
 CREATE TABLE 'websites' (
 ​
 id int(11) NOT NULL AUTO_INCREMENT,
 ​
 name char(20) NOT NULL DEFAULT '' COMMENT '站点名称',
 ​
 url varchar(255) NOT NULL DEFAULT '',
 ​
 alexa int(11) NOT NULL DEFAULT '0' COMMENT 'Alexa排名',
 ​
 sal double COMMENT '广告收入'，
 ​
 country char(10) NOT NULL DEFAULT '' COMMENT '国家',
 ​
 PRIMARY KEY(id)
 ​
 )ENGINE=InnoDB DEFAULT CHARSE=utf8;
 ​
 ​
 ​
 INSERT INTO 'websites' VALUES
 ​
 (1,'Google','https://www.google.cm/','1',2000,'USA'),
 ​
 (2,'淘宝','https://www.taobao.com/','13',2050,'CN'),
 ​
 (3,'菜鸟教程','https://www.runoob.com/','4689',0.0001,'CN'),
 ​
 (4,'微博','https://weibo.com/','20',50,'CN'),
 ​
 (5,'Facebook','https://www.facebook.com/','3',500,'USA');
 ​
 ​
 ​
 CREATE TABLE IF NOT EXISTS 'access_log' (
 ​
 'aid' int(11) NOT NULL AUTO_INCREMENT,
 ​
 'site_id' int(11) NOT NULL DEFAULT '0' COMMENT '网站id',
 ​
 'count' int(11) NOT NULL DEFAULT '0' COMMENT'访问次数',
 ​
 'date' date NOT NULL,
 ​
 PRIMARY KEY('aid')
 ​
 )ENGINGE=InnoDB DEFAULT CHARSET=utf8;
 ​
 ​
 INSERT INTO 'access_log' ('aid','site_id','count','date') VALUES
 ​
 (1,1,45,'2016-05-10'),
 ​
 (2,3,100,'2016-05-13'),
 ​
 (3,1,230,'2016-05-14'),
 ​
 (4,2,10,'2016-05-14'),
 ​
 (5,5,205,'2016-05-14'),
 ​
 (6,4,13,'2016-05-15'),
 ​
 (7,3,220,'2016-05-15'),
 ​
 (8,5,545,'2016-05-16'),
 ​
 (9,3,201,'2016-05-17'),
 ​
 (10,88,9999,'2016-09-09');

• 查询语句

-- 实际开发中尽量不要使用*作为查询
 ​
 -- select * from websites
 ​
 -- 推荐使用的方式
 ​
 select id,name,url,alexa,sal,country from websites

六、分页查询

MySQL的分页是最优雅

select * from websites limit 2,3;-- 从第2条（下标从0开始）开始查，查3条数据
 ​
select * from websites limit 3;-- 从第0条（下标从0开始）开始查，查第3条数据

七、distinct关键字

DISTINCT 关键词用于返回唯一不同的值(去除重复的字段)。

select distinct country from websites

八、where语句

作为条件筛选，运算符：> < > = <= >= !=

is null is not null (判断空值为什么不用sal=null？因为在sql语句中nul和任何东西比较都是假，包括它本身)

like in

select * from websites where sal>500

九、逻辑条件：and、or

 select * from websites where sal >=0 and sal<=2000;-- 收入在0-2000之间
 ​
 select * from websites where sal between 0 and 2000；-- 和上面一样的意思
 ​
 select * from websites where sal<5 or is null;-- 收入小于5或者没收入

注：null的条件判断用is null或is not null

十、order by

排序：默认情况下是升序，asc可以省略。

 select * from websites order by sal asc,alexa desc ;-- 先根据sal升序排列，再根据Alexa降序

十一、like和通配符

• like模糊查询

• 通配符

  • %：0个或多个字符

  • _：1个字符

select * from websites where name like '%o%'-- 有o的字符
 ​
select * from websites where name like '_o%' -- 第二个字符为o

十二、in

匹配多个条件

 select * from websites where country in('USA','鸟国','CN');
 ​
 -- 等价于select * from websites where country = 'USA' or '鸟国' or 'CN';

十三、别名

 select tt.name '网站名字' from websites tt

websites tt -- 给当前建立的表websites起一个别名tt方便后面查询

十四、Group by 分组查询

注意：分组时候的筛选用having

常见的几个组函数：max() min() avg() count() sum()

 select avg(sal) 'avg' from websites where sal is not null group by country having avg>1500

查找按照 国家分组 的 平均工资大于1500 的平均工资

十五、子查询

把查询的结果当做一个表来使用**

*十六、连接查询

select name，count，date from websites w,access_log a;-- 著名的笛卡尔积，无意义
 ​
 select name，count，date from websites w,access_log a where w.id =a.site_id; -- 这是1992的语法
 ​
 select name，count，date from websites w left outer join access_log a on w.id = a.site_id; -- 把没有访问的网站也显示出来
 ​
 -- 注意：inner和outer是可以默认省略的

• 查询每个网站的每天的访问量，显示出：名称 访问量 日期

--                                                                                                                   关联条件      筛选价值

-- select name ,a.count,a.date from websites w,access_log a where w.id=a.site_id and sal>2000 -- 过时，不推荐

-- 推荐方式：
select name,count,a.date from websites w left join access_log a ​
on w.id=a.site_id where sal>2000

 -- 1999改进如下 ​
 -- 实际开发中推荐这种写法
 -- select * from websites w join access_log a on w.id = a.site_id where sal>2000
 ​
 select * from websites w left join access_log a
 on w.id = a.site_id --关联条件
 where sal>2000 --筛选条件

 -- 全连接
 select * from websites w left join access_log a
 on w.id = a.site_id 
 where sal>2000
 union
 select * from websites w right join access_log a
 on w.id = a.site_id
 where sal>2000

左外连接：以左表为主，另一个表就根据主表来匹配（主表有多少条数据，最终显示多少条数据）