MySQL笔记(一)、安装MySQL和基础知识

一、安装mysql

按照网上的教程，但是走了一些坑，记录下来。
基本过程：

1. cd /d D:\Software\mysql-8.0.20-winx64\bin
2. mysql --install #安装服务
3. mysqld --initialize --user=root --console #按my.ini初始化
4. net start mysql
5. mysql -u root -p #按初始密码登陆
6. alter user ‘root’ @ ‘localhost’ identified by “123”;
   不写这句，其他的命令都不让执行。

会出现的一些问题：

• 用initial那里生成的初始密码登陆，登陆失败。按网上的办法，在ini的mysql后面好像加了什么东西就可以空密码登陆，但试了之后报了无法连接到localhost的错误。
• 解决方案：删掉整个data文件夹，再执行一遍initial，再用初始密码登陆就可以了。其他一开始出现的奇奇怪怪的错误，这么弄了一遍后好像都消失了。

二、单表增删查改等

练习网站：http://xuesql.cn/

1、select

格式：
select xxx from xxx where xxx;
例子：
select id,title,'美国'as country from movies; //新加了一列，值全为"美国"

2、where

• 比较运算符：=；>；<；>=；<=；!=；<> 表示：不等于

• 逻辑运算：And:与；Or:或；Not:非
  优先级：( ) > not > and > or

• 特殊条件
  1）空值判断： is null

  select * from movies where title is null;

  2）在x和y之间的值：between x and y；范围包含x和y的值
  3）值为(a,b,c,…)中的一个：in (a,b,c)；
  4）模糊查询：like，配合 % 与_

   select * from movies where title like 'M%';

  • % 表示多个字值，_ 下划线表示一个字符
  • M% : 为能配符，正则表达式，表示的意思为模糊查询信息为 M 开头的。
  • %M% ：表示查询包含M的所有内容。
  • %M_ : 表示查询以M在倒数第二位的所有内容。

3、distinct, order, limit（过滤和排序）

(1) distinct
删去director这个属性中，重复的行

select distinct director from movies;

(2) order by

1.按多列排列，默认升序asc
select xxx from xxx order by col1,col2,... 
2.desc 或者 asc 只对它紧跟着的第一个列名有效，其他的仍然是默认的升序
  order by A,B          A和B都是默认按升序排列
  order by A desc,B     A 降序，B 升序排列
  order by A ,B desc    A 升序，B 降序排列

(3) limit

偏移 x ，则从第 x+1 行选起，选后面的 nums 行数据
select xxx from xxx limit nums offset 第x行

4、having

HAVING 子句可以让我们筛选分组后的各组数据。
比如说，我们想要查找总访问量大于 200 的网站，并且 alexa 排名小于 300，用where先小于300，然后分组，然后having再大于200。having就相当于是在order by之后使用的where。

SELECT Websites.name, SUM(access_log.count) AS nums FROM Websites
INNER JOIN access_log
ON Websites.id=access_log.site_id
WHERE Websites.alexa < 300 
GROUP BY Websites.name
HAVING SUM(access_log.count) > 200;

5、insert into

用于向表中插入新记录。
没有指定要插入数据的列名，则需要列出插入行的每一列数据:

INSERT INTO table_name
VALUES (value1,value2,value3,...);

或者指定哪几列插入新值，其他的列就用默认值来填充，主键id自动更新加1。

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

6、delete

drop、delete、truncate 都是删除表的内容。

drop删除表，并释放空间，将表删除的一干二净。drop test;
delete仅删除表test内的所有内容，保留表的定义，不释放空间。delete from test;
truncate 删除表test里的内容，并释放空间，但不删除表的定义，表的结构还在。truncate test;

删除指定的行：

DELETE FROM Websites
WHERE name='Facebook' AND country='USA';

删除所有的行：

DELETE FROM table_name;
或
DELETE * FROM table_name;

7、update

更新指定的行里的内容：

UPDATE Websites 
SET alexa='5000', country='USA' //更新该行的这两列
WHERE name='淘宝网'; //淘宝网这一行

执行没有 WHERE 子句的 UPDATE 要慎重，再慎重。如果上面的代码没有where，它会将 Websites 表中所有数据的 alexa 改为 5000，country 改为 USA。在 MySQL 中可以通过设置 sql_safe_updates 这个自带的参数来解决，当该参数开启的情况下，你必须在update 语句后携带 where 条件，否则就会报错。

set sql_safe_updates=1; 表示开启该参数

三、多表查询

1、join

使用join连接多个表，连接的结果可是由select语句指定的列组成的新表。
左连接与右连接的左右指的是以两张表中的哪一张为基准。

INNER JOIN：取两表交集，如果表中有至少一个匹配，则返回行
FULL JOIN： 取两表并集，只要其中一个表中存在匹配，则返回行
LEFT JOIN： 即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行

例1：

//employees和buildings 连起来，on是匹配条件
select * from employees
left join buildings on employees.building=buildings.building_name
where building is not null; //注意是is not null 而非not is null

例2：

//电影每分钟的总销量(国内和国外销量的和)
select title,(Domestic_sales+International_sales)/length_minutes as value
from movies
left join boxoffice on movies.id=boxoffice.movie_id 
where director like 'John%'
order by value desc limit 3;//按价值排序，选最高的前三名

例3：

select id,title,(Domestic_sales+International_sales) as total
from movies
left join boxoffice on movies.id=boxoffice.movie_id 
order by length(title) desc limit 3;//最长的前3名

//（select xxx)整个语句也可以作为一个值参与计算，
//这个where选择的是最长的标题
或者where length(title)=(select max(length(title)) from movies)

2、表达式

在SQL中可以用col_name（列名）的地方，都可以用表达式来指定对属性进行一定的计算或处理，然后加一个AS，起个别名
常用的统计函数有：

fuction	description
COUNT(*), COUNT(column)	计数，COUNT(*) 统计数据行数，COUNT(column) 统计column非NULL的行数.
MIN(column)	找column最小的一行
MAX(column)	找column最大的一行
AVG(column)	对column所有行取平均值
SUM(column)	对column所有行求和

3、union

合并两个或多个 SELECT 语句的结果集。
UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。

1.连接两个表的查询结果集，重复的行不显示
select country from websites union select country from apps;
2.连接俩个个表的查询结果集，显示重复的行
select country from websites union all select country from apps order by country;
3.通过where条件查询的结果，连接连个表的结果集，并根据名字排序。
select country,name from websites where country = 'CN' union all 
select country,app_name from apps where country='CN' order by name; 
使用UNION命令时需要注意，只能在最后使用一个ORDER BY命令，是将两个查询结果合在一起之后，再进行排序。绝对不能写两个ORDER BY命令。
另外，在使用ORDER BY排序时，注意两个结果的别名保持一致，使用别名排序很方便。当然也可以使用列数。

例1：
对employees 这个表按员工角色role进行分组，然后对每一类role，统计分配了building的数量，和没有building员工的数量。

//这里用count(building)，直接排除了计算null的行，统计的都是有房子的员工。还加了一列Exist
select role,count(building) as nums,'yes' as Exist from employees group by role
union
//用where筛出来了无房子的员工，这时，用任何一个非building的变量都可以统计数量
select role,count(name),'no' from employees where building is null group by role

例2：
先按role分组，然后每一类角色下，雇佣年份从0-8划分为3组，统计不同组的员工个数

select role,count(*),'0-2'  // role下，0-2这一组
from employees where Years_employed between 0 and 2 group by role
union 
select role,count(*),'3-5' 
from employees where Years_employed between 3 and 5 group by role
union
select role,count(*),'6-8' 
from employees where Years_employed between 6 and 8 group by role

4、查询的执行顺序

SELECT DISTINCT column, AGG_FUNC(column_or_expression), …
FROM mytable
    JOIN another_table
      ON mytable.column = another_table.column
    WHERE constraint_expression
    GROUP BY column
    HAVING constraint_expression
    ORDER BY column ASC/DESC
    LIMIT count OFFSET COUNT;

1. from和join on：定表
2. where：筛选哪些column，此处还不能用as别名
3. group by：分小组
4. having：对分完后的组二次筛选
5. select：确定表后，决定展示哪些数据
6. distinct：对select的数据排重
7. order by：一切尘埃落定后，来排个序
8. limit和offset：截取部分行