Mysql基础命令——案例实战

案例1：MySQL的安装和基本使用

安装需要设置编码为 UTF-8 ， 管理用户 root, 密码设置 rootroot，新建一个名为mydb的数据库。（Mysql安装链接： Mysql5.7安装详细步骤_luo_guibin的博客-CSDN博客_mysql5.7安装步骤 ）

b站链接： 大学生必备 ! 1 小时速成MySQL !_哔哩哔哩_bilibili

案例2：MySQL的数据类型

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

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

char和verchar的区别 ：

char(10)：“ABC       ”

verchar（10）：“ABC”

案例3：建表操作

语法：

-- 删除表 
DROP TABLE IF EXISTS 表名; 
-- 新建表 
create table 表名( 
    字段名 类型 约束（主键，非空，唯一，默认值）， 
    字段名 类型 约束（主键，非空，唯一，默认值），
 )编码，存储引擎；

 实例：

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 CHARSET=utf8;

 

 

案例4：插入、删除、更新

插入语句

INSERT INTO websites(name, url,alexa,sal,country ) VALUES 
    ('腾讯', 'https://www.qq.com', 18, 1000,'CN' ) ;

删除语句

delete from websites where id = 5;

更新语句

update websites set sal = null where id = 3

案例5：基本 select 查询语句

数据初始化：

INSERT INTO `websites` VALUES 
    (1, 'Google', 'https://www.google.cm/', '1', 2000,'USA'), 
    (2, '淘宝', 'https://www.taobao.com/', '13',2050, 'CN'), 
    (3, '菜鸟教程', 'http://www.runoob.com/', '4689',0.0001, 'CN'), 
    (4, '微博', 'http://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`) 
) ENGINE=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 （推荐使用的方式）

案例6. 分页查询

语法：

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

select * from websites limit 3 ; --从第0条(下标从0开始)开始查，即查询前三行

 案例7. distinct 关键字

去掉重复的关键字，只保留唯一。country里面有多个CN和USA，但是结果只显示country下有多少种。

实例：

select distinct country from websites

 

 

 案例8. where 语句

作为条件筛选 , 运算符 : > < >= <= <> != =

is null 或者 is not null ( 因为在 sql 语句中 null 和任何东西比较都是假/0，包括它本身)，所以当select * from websites where sal = null时会报错，因为条件sal = null，本身为假/0，无法查询。当where条件为真/1时，才会查询。

like in

实例：

select * from websites where sal >500

 案例9. 逻辑条件: and 、or（配合where使用）

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

-- 收入在 0 到 2000 之间 
select * from websites where sal >= 0 and sal <=2000 ; 

-- 和上面一样的，没事找事 
select * from websites where sal between 0 and 2000； 

 -- 收入小于5 或者没收入
select * from websites where sal < 5 or sal is null ;

 

案例10. order by 排序

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

默认情况是asc（升序），所以asc也可以省略。

 

案例11. like 和 通配符（模糊查询）

  like实例：%表示多个字符，_表示一个字符。

select * from websites where name like '%oo%';

案例12. in

匹配多个条件

select * from websites where country in ('USA','鸟国','CN');

 等价于

select * from websites where 
    country = 'USA' or country = '鸟国' or country = 'CN';

 

案例13. 别名

-- 此处tt是websites的缩写或者叫临时变量，websties=tt
select tt.name '网站名字' from websites tt

 

案例14. Group by 分组查询

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

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

-- having相当于where，因为分组的原因，不能使用where
select avg(sal) aa from websites where sal is not null group by country having aa > 1500

 

案例15. 子查询

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

案例16. 连接查询（重难点）

内连接，左外连接，右外连接，全连接

 

语法：

--著名的笛卡尔积，没什么意义的 
select name,count,date from websites w , access_log a ; 

-- 这是 1992 的语法 
select name,count,date from websites w , access_log a where w.id = a.site_id；

-- 这是 1999 年的语法，推荐使用 
select name,count,date from websites w inner join access_log a on w.id = a.site_id；

-- 把没有访问的网站也显示出来 -- 注意: inner 和 outer 是可以默认省略的。
select name,count,date from websites w left outer join access_log a on w.id = a.site_id；

实例：将websites里的id和access_log里的site_id相关联查询。

内连接：

select name,id,site_id,count,date from websites w join access_log a on w.id = a.site_id;

 

左外连接：

select name,id,site_id,count,date from websites w left join access_log a on w.id = a.site_id;

 

右外连接：

select name,id,site_id,count,date from websites w right join access_log a on w.id = a.site_id;

 

全连接：有union将左右外连接的语句拼接，就可以了。

select name,id,site_id,count,date from websites w left join access_log a on w.id = a.site_id 
union 
select name,id,site_id,count,date from websites w right join access_log a on w.id = a.site_id;

 

 

案例17. Null 处理l 函数

 实例：

select name,ifnull(count,0),ifnull(date,'') from websites w left outer join access_log a on w.id = a.site_id