sql -1-基础

SELECT COUNT(birthdate) FROM people; birthdate 非空的个数

SELECT COUNT(dinstict birthdate) FROM people;  birthdate 非空且不重复的个数

mysql 万能命名规则：标识符由字母、数字或下划线(_)组成, 且第一个字符必须是字母或下划线。

mysql 的关键字最好是实用大写书写，这样可以和其他的查询字区别开。

mysql三大数据类型：

• 数字类型
  • 整数: tinyint、smallint、mediumint、int、bigint
  • 浮点数: float、double、real、decimal
• 日期和时间: date、time、datetime、timestamp、year
• 字符串类型
  • 字符串: char、varchar
  • 文本: tinytext、text、mediumtext、longtext
  • 二进制(可用来存储图片、音乐等): tinyblob、blob、mediumblob、longblob

类型的详细说明

创建数据库：

create database 数据库名 [其他选项];
create database samp_db character set gbk; 
character set gbk: 便于在命令提示符下显示中文

创建表：

create table 表名称(列声明);

以创建 students 表为例, 表中将存放 学号(id)、姓名(name)、性别(sex)、年龄(age)、联系电话(tel) 这些内容:

create table students（
		id int unsigned not null auto_increment primary key,
		name char(8) not null,
		sex char(4) not null,
		age tinyint unsigned not null,
		tel char(13) null default "-"
	);

插入数据到表中：

insert [into] 表名 [(列名1, 列名2, 列名3, ...)] values (值1, 值2, 值3, ...);

insert into students values(NULL, "王刚", "男", 20, "13811371377");

有时我们只需要插入部分数据, 或者不按照列的顺序进行插入, 可以使用这样的形式进行插入:

insert into students (name, sex, age) values("孙丽华", "女", 21);

查询条件：

Where 的一些用法：

=   相同

<> 不相同

<  小于

>  大于

<=   >=

Where 的使用必须是在 from 之后

select title, release_year from films where 

(release_year>=1990 and release_year<2000) 

and (language = 'French' or language='Spanish')

and (gross > 2000000);

每一个条件必须要指定特定的column

SELECT title

FROM films

WHERE release_year

BETWEEN 1994 AND 2000;

BETWEEN 关键字包括 1994年和2000年

SELECT name

FROM kids

WHERE age IN (2, 4, 6, 8, 10);

In 关键字可以防止你写更多更大的or语句

Like 模式匹配,   not like 匹配不是和模式匹配的情况

% 匹配0个1个或者多个字符

_ 匹配1个字符

select name from people where name LIKE '_r%';

选择人名字的第二个字符是r 的人名字

一些集合函数可以在sql中使用

SELECT MAX(budget) FROM films;

还有的函数有：sum， avg 等

select avg(gross) from films where title like 'A%';

集合函数和where 配合使用

使用别名(临时名称)

SELECT MAX(budget), MAX(duration) FROM films;

以上的并不会给带来多少便利，这样的结果是 max 1222， max 222

如果这种计算很多，你并不知道哪个是哪个的结果，

SELECT MAX(budget) AS max_budget, MAX(duration) AS max_duration FROM films;

如果使用这种写法，那么输出会是 

max_budget：1222， max_duration  222

SQL assumes that if you divide an integer by an integer, you want to get an integer back.

排序：

ORDER BY  默认的是 升序(asc)，如果要降序(desc)

SELECT title FROM films ORDER BY release_year DESC;

## Get the title of films released in 2000 or 2012, in the order they were released

select title from films where release_year = 2000 or release_year = 2012 order by release_year

多个条件的排序，第一个为准，当第一个多个相同大小的时候，第二个排序，依次… 

SELECT birthdate, name FROM people ORDER BY birthdate, name;

更新表中的数据：

update 表名称 set 列名称=新值 where 更新条件;
update students set tel=default where id=5;
update students set age=age+1;
update students set name="张伟鹏", age=19 where tel="13288097888";

删除表中的数据：

delete from 表名称 where 删除条件;
delete from students where id=2;
delete from students where age<20;
delete from students; 删除表中所有数据

对于创建后标的修改(开发中数据库的migration 实用的较多)

alter table 语句用于创建后对表的修改 ，

添加列：

alter table 表名 add column 列名 列数据类型 [after 插入位置];
在表的最后追加列：
address: alter table students add column address char(60);

修改列属性：

alter table 表名 change 列名称 列新名称 新数据类型;
alter table `students ` modify column `tel` varchar(255) default null;

删除列：

alter table 表名 drop column 列名称;
alter table students drop column birthday;

重命名：

alter table 表名 change 新表名;
alter table `students ` change `task_status` `status` varchar(255);

删除整张表/数据库：

drop database 数据库名;
drop table workmates;

聚类

GROUP BY

SELECT sex, count(*) FROM employees GROUP BY sex; count(*) 统计每一个group 中的sex的个数

select release_year, max(budget) from films group by release_year order by release_year 

select country, sum(budget) from films group by country；
select country,release_year,min(gross) from films group by country,release_year order by country, release_year

GROUP BY 聚合函数和 WHERE 不能同时使用(groupby 之后不能在使用where)，如果在order by 之后需要使用过滤的一些操作，

那么你需要使用 HAVING , HAVING 和where 有相同的功能，HAVING后面跟着的是 条件

-- 返回1990年后的每一年的平均 budge, gross, 且平均budge 大于60 million 刀

select release_year, avg(budget) as avg_budget, avg(gross) as avg_gross from films where release_year > 1990 group by release_year having avg(budget) > 60000000 order by avg(gross) desc

select country, avg(budget) as avg_budget,avg(gross) as avg_gross from films group by country having count(films) > 10 order by country limit 5

Join

以上目前接触到的都是对于单张表的操作，但是有时候会遇到多个表的操作，需要将多个表结合,

第一张表：

第二张表：

films表的id 对应这reviews 表的film_id 根据这个字段可以将两张表join

SELECT title, imdb_score FROM films JOIN reviews ON films.id = reviews.film_id;

Inner join ：以共同存在的键为基础，取出这些共同存在键的内容将其合并，不存在共同键的行不在结果表中，合并会产生新的

表

select * from cities inner join countries on cities.country_code = countries.code

On 的意思是指定键去join

select countries.name as country, cities.name as city, countries.region from cities

inner join countries

on cities.country_code = countries.code

不仅在columns 中使用 别名，在表中也可以使用别名

SELECT c.code AS country_code, c.name, e.year, e.inflation_rate

FROM countries AS c

inner JOIN economies as e

ON c.code = e.code;inn

多个条件，多张表join

select c.code, c.name, c.region, e.year, p.fertility_rate, e.unemployment_rate from countries as c

inner join populations as p

on c.code = p.country_code

INNER JOIN economies AS e

on c.code = e.code and p.year = e.year

使用using(id) 代替 on c.code = e.code and p.year = e.year 合并表

SELECT c.name AS country, continent, l.name AS language, l.official

FROM countries AS c inner JOIN languages AS l using(code);

自我join，自己和自己join

SELECT p1.country AS country1, p2.country AS country2, p1.continent

FROM prime_ministers AS p1

INNER JOIN prime_ministers AS p2

ON p1.continent = p2.continent AND p1.country <> p2.country

LIMIT 13;

使用 when else 语句，(if else 语句)

SELECT name, continent, indep_year,

    CASE WHEN indep_year < 1900 THEN 'before 1900'

         WHEN indep_year <= 1930 THEN 'between 1900 and 1930'

         ELSE 'after 1930' END

         AS indep_year_group

FROM states

ORDER BY indep_year_group;

在使用上面所谈到的self join 的时候，当共同条件有多个的时候， 假如有以下情况,那么在join 的时候

id = 1， year = 2019

id = 1,    year  = 2018

那么在 self join 的时候就会出现4条记录

id = 1， year_1 = 2019,   year_2  = 2018

id = 1,    year_1  = 2019 , year_1  = 2019

id = 1，  year_1 = 2018,   year_2  = 2018

id = 1,    year_2  = 2018,  year_1  = 2019

这个时候就需要多添加一条语句，选出你需要的这两列的关系的那些数据

SELECT p1.country_code, 

       p1.size AS size2010,

       p2.size AS size2015

FROM populations AS p1

inner JOIN populations AS p2

ON  p1.country_code = p2.country_code

    and p1.year = p2.year - 5

使用 CASE 和 WHEN, THEN, ELSE, 及 END 定义新字段

SELECT name, continent, code, surface_area,
        -- first case
    CASE WHEN surface_area > 2000000 THEN 'large'
        -- second case
        WHEN surface_area > 350000 THEN 'medium'
        -- else clause + end
        ELSE 'small' END
        AS geosize_group
FROM countries;

产生一个新值，且这个值在不同条件下设置不同的值

into 关键字，设置select 的字段，存在into 指定的表名中

SELECT country_code, size,
    CASE WHEN size > 50000000 THEN 'large'
        WHEN size > 1000000 THEN 'medium'
        ELSE 'small' END
        AS popsize_group
        
into pop_plus
FROM populations
WHERE year = 2015;

select * from pop_plus
这里就可以将之前存储的字段都去出来

LEFT join RIGHT Join, FULL join 这三个都称之为是 OUTER join

LEFT join：会有几种特殊情况

RIGHT Join 和 LEFT Join 相同的情况，会有相似的处理方式，