标签：name 改查 actor user Mysql 增删 works id SELECT

Mysql 数据语言系列

Mysql 数据语言系列
一.数据定义语言 DDL
二. 数据操纵语言 DML
三. 数据控制语言 DCL
四. 数据查询类语句 DQL
五元数据
可能碰到的错误
- 1 忘记Mysql密码如何处理
- 2 错误2059 连接不上远程数据库
六.函数

一.数据定义语言 DDL

1 数据库规范

#官网
https://www.sqlstyle.guide/zh/

   创建库需要应该遵循包括但不限于以下规范:
        (1)数据库名称小写，尽量不要使用中文字符作为数据库名称;
        (2)数据库名称要与业务有关;
        (3)数据库名称不要以数字开头;
        (4)数据库名称不要太长;
        (5)不能使用保留字符串或者关键字(尽管我们可以在关键字加一个反引号强制创建出来，但这样做是没有多大意义的!);

2 DDL 语言使用

查询帮助
help data definition

查询帮助
help data definition

#01 创建
mysql> ? CREATE

#02 查询
mysql> ? SELECT
show create database oldboy ;	# 查看库属性
mysql> desc t15;				# 竖向展示表


#03 插入 默认是追加
mysql> ? INSERT

#04 更新
mysql> ? UPDATE		# 更新表
mysql> ? alter      # 修改库
? alter table      # 修改表



#05 删除
mysql> ? DELETE


-----------------------


#01 创建一个名为"oldboyedu_linux"的数据库
CREATE DATABASE IF NOT EXISTS oldboyedu_linux;

#02 创建一个名为"oldboyedu_numeric"的表
CREATE TABLE oldboyedu_numeric (my_tinyint TINYINT,my_smallint SMALLINT, my_mediumint MEDIUMINT,my_int INT,my_bigint BIGINT);

#03 往"oldboyedu_numeric"的表插入数据;
INSERT oldboyedu_numeric VALUES (1,2,3,4,5);

#04 查看"oldboyedu_numeric"表的所有内容;
SELECT * FROM oldboyedu_numeric;
     
#05 更新表某列数据;
UPDATE  oldboyedu_numeric SET my_bigint = 8;

#06 删除表某行数据;
DELETE FROM oldboyedu_numeric WHERE my_bigint=8;

#07 查看表格属性
SHOW CREATE TABLE t5;
select user () ;  		# 查看当前用户
show processlist ;		# 查看当前用户登入信息



#08 修改库属性
root@[oldboy]-> ALTER DATABASE oldboy CHARACTER SET utf8;  # 版本5.6
ALTER DATABASE oldboy CHARACTER SET utf8 READ ONLY  1 ; # 8.0 

解释下： 
CHARACTER SET utf8 修改默认字符集
READ ONLY  1  修改权限 1 只读 0 关闭只读；可读写 还有默认

查询帮助：? alter database  

CREATE TABLE t15 (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'id', name VARCHAR(30),ags int) CHARSET=utf8mb4  ;


-------客户端命令 ------------

c HELP CONTENTS

客户端命令 不需要加 ;
查询命令  ?
#01 修改前缀提示符
mysql>  prompt [\D\d] 

#02 执行当前系统命令 
mysql> system ls -l /tmp
total 0
srwxrwxrwx 1 mysql mysql 0 Sep  6 12:54 mysql.sock
drwx------ 2 root  root  6 Sep  6 11:20 vmware-root_1065-4248615061
drwx------ 2 root  root  6 Sep  6 11:18 vmware-root_965-4256676100

#03 更换客户端字符集
mysql> charset utf8

#04 设置语句分隔符。
mysql> delimiter @@

#05 使用$EDITOR编辑命令。
mysql> edit

#06 执行SQL脚本文件。将文件名作为参数。
mysql> source oldboy.sql		# 可以导入表数据 需要进入对应的库

2 创建视图

#01  SELECT id,name FROM t15 ;

解释下： 只看 id,name 字段


#01 创建表 
CREATE TABLE staff (
id int(11) PRIMARY KEY AUTO_INCREMENT COMMENT '员工编号',
name varchar(30) NOT NULL COMMENT '员工姓名',
birthday  DATETIME(0) COMMENT '出生日期',
gender enum('Male','Female') DEFAULT 'Male' COMMENT '性别',
address varchar(255) NOT NULL COMMENT '家庭住址',
mobile_number bigint UNIQUE KEY NOT NULL COMMENT '手机号码',
salary int NOT NULL COMMENT '薪资待遇',
departing tinyint NOT NULL DEFAULT 0 COMMENT '离职信息,0代表全职,1代表兼职,2代表离职',
remarks VARCHAR(255) COMMENT '备注信息'
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


#02 查看表结构:
DESC t1;

#03 插入数据
SELECT * from t1 ;

#04 查看数据
SELECT * FROM t1 ;


#05 创建视图
create table t3 select name,hobby from t1;
SHOW TABLES;


#06 插入数据测试
insert t3 (name,hobby) values ('B','C');

二. 数据操纵语言 DML

1 插入语法

#01 create创建表
create table t1 (id int, name varchar(255), hobby varchar(255) );
create table user03 select * from let ;

复制let表内容 到user03 ；user03如果没有会自动创建改表


#02 插入数据
INSERT t1 VALUES (1,'孙悟空','金吾邦');
INSERT t1 set id = 2, name='流川枫', hobby='樱桃小丸子' ;

INSERT t2 table t1;  # 把t1的数据 同步到t2 

注意：这是给t2表插入数据 插入的数据是t1的

INSERT t2 select * from t1 ;  # 给t2插入数据
select * from t1 ;

2 更新语法

#01 update更新表数据
mysql> update t2 set hobby = 'B' where name = '孙悟空' ORDER BY  limit 2;

解释下：name = 孙悟空的行 更新字段数据hobby =B 并且只改一行
where      过滤
set hobby  修改
ORDER BY   集群排序 默认是升序 A-Z  
limit 	   指定行 
DESC limit 降序  Z-A

#02 改变字段
alter table 表名 change 字段名称 新的字段名称 新的字段类型

3 删除语法

#01 删除语法 删除表数据
? delete 
delete from t2 ;					# 删除表数据 但是不会删除表结构 ；逐条删除 不会删除原始文件和改变高水位线
delete from t2 where id = (1); 		# 删除 id等于1的
delete from t2 where id IN (1,3); 	# 删除 id等于1和3
delete from t2 where id > 3 ;		# 删除大于3的
delete from t2 where id != 1 ;		# 删除id 不等于3的

#02 删除表和库
drop table 表名称 ;					# 删表 直接删除表（包括表结构数据）
drop database 库名称 				    # 删库


#03 清空表结构
create table user03 select * from let ;
truncate user03 ;		# 清空user03表数据；它会复制表结构 重新创建一个表 但是没有数据 速度快



#04 总结    高水位线(AUTO_INCREMENT=)
delete from t2 ;   # 删除表数据 逐条逻辑删除 但是不会删除表结构 ；不会删除文件和改变高水位线 inode不变 可以恢复
truncate user03;   # 清空user03表数据；它会复制表结构 重新创建一个表 高水位线归零 inode改变
drop table user03; # 删表 直接删除表（包括表结构数据）




cat>/root/sh/auth.sh<<EOF
#!/bin/bash
source /etc/profile
kill -9 $(lsof -i:3000 | grep LISTEN | awk '{print $2}')
sleep 5s
echo "auth kill"

nohup java -jar  -Dfile.encoding=UTF-8 -Xms128m -Xmx1g /home/subway/pigx-auth/pigx-auth.jar -java.tmp.dir=/data/upload_tmp >> /home/subway/pigx-auth/pigx-auth.log 2>&1 &
EOF


#!/bin/bash
source /etc/profile
kill -9 $(lsof -i:3000 | grep LISTEN | awk '{print $2}')
sleep 5s
echo "auth kill"

nohup java -jar  -Dfile.encoding=UTF-8 -Xms128m -Xmx1g /home/subway/pigx-auth/pigx-auth.jar -java.tmp.dir=/data/upload_tmp >> /home/subway/pigx-auth/pigx-auth.log 2>&1 &

4 伪删除

#03 伪删除表数据
create table let (
id int primary key auto_increment,
username varchar(255) not null comment '用户名',
password varchar(255) not null comment '用户密码',
telphone bigint not null comment '用户电话',
email varchar(255) not null comment '用户邮箱', 
deleted tinyint default 0 comment '改记录是否被删除 0表示未删除 1代表已经删除' );
show create table let ;

# 修改某列的字符集
# 往user表插入数据
insert let
(username,password,telphone,email) VALUES
('张宇宙',123,164646313,'[email protected]'),
('陆小凤',123,164646513,'[email protected]'),
('西门吹雪',123,164666313,'[email protected]');

# 更新数据
update let set deleted = '1' where id = '2' ;
select * from let ;
Wed Sep  8 09:42:31 2021oldboyedu_goods] select * from let ;
+----+--------------+----------+-----------+------------------+---------+
| id | username     | password | telphone  | email            | deleted |
+----+--------------+----------+-----------+------------------+---------+
|  2 | 陆小凤       | 123      | 164646513 | [email protected] |       1 |
|  3 | 西门吹雪     | 123      | 164666313 | [email protected] |       0 |
+----+--------------+----------+-----------+------------------+---------+

解释下： 这里的1代表用户已经删除 但是数据还在我们这里 只是用户看不到

5 数据恢复

#01 首先DELETE FROM student, DROP TABLE student, TRUNCATE TABLE student这三条SQL语句均能删除全表数据，但他们的确是存在一定的差异，这也是不争的事实。

#02 DELETE FROM student:
        (1)是逻辑上的删除，并没有真正从磁盘上删除，只是在存储层面打标记，磁盘空间不立即释放，高水位线(HWM)不会降低;
        (2)当数据量较大时，操作会很慢，因为他要将全表进行扫描，而后对每行打标记改行被删除，理论上这种删除通过一定的手段是可以恢复数据的;

#03 DROP TABLE student:
        (1)将表结构(元数据)和数据行物理层次删除;
        (2)该删除操作是不可恢复的，因为已经在物理层上删除数据，想要恢复也只能通过备份来进行恢复了;

#04 TRUNCATE TABLE student:
        (1)清空表中所有的数据页，物理层次删除全表数据，磁盘空间立即释放，高水位线(HWM)会初始化为0;
        (2)其实TRUNCATE我们可以理解为将原表的表结构复制并创建了一张新表，新数据直接写入到新建的表中，他不会去逐行删除之前的数据(之前的原始数据也不会有相应的引用，从而被MySQL进行垃圾回收);


#05 数据恢复手段
(1)常规方法：都可以通过备份和日志进行数据的恢复;
(2)灵活方法：DELETE可以通过翻转日志(这需要开启binlog日志);
(3)当然，也可以通过"延迟从库"进行恢复;

6 案例

#01 插入语法:
	INSERT  t1 VALUES  (1,'孙悟空','蟠桃');
	INSERT  t1 SET id = 2, name='猪八戒', hobby='嫦娥';
	INSERT  t1 TABLE student;
	INSERT  t1  SELECT * from student;

#02 更新语法
UPDATE t1 SET name = '齐天大圣' WHERE id = 1;
UPDATE t1 SET name = '美猴王' WHERE id = 1  LIMIT 2;
UPDATE t1 SET name = '花果山水帘洞齐天大圣孙悟空' WHERE id = 1 ORDER BY hobby LIMIT 2;


#03 删除语法
DELETE  FROM student WHERE id = 6;
DELETE  FROM t1 WHERE id IN (3,4);
DELETE  FROM t1 WHERE id > 3;
DELETE  FROM t1 WHERE id != 1;
DELETE  FROM t1 WHERE id = 1 LIMIT 2;
DELETE  FROM T11 WHERE id = 1 ORDER BY hobby LIMIT 2;
DELETE  FROM T12 WHERE id = 1 ORDER BY hobby  DESC LIMIT 2;





CREATE TABLE user (
	id INT PRIMARY KEY AUTO_INCREMENT,
	username VARCHAR(255) NOT NULL,
	password VARCHAR(255) NOT NULL,
	telphone BIGINT NOT NULL,
	email VARCHAR(255) NOT NULL
) DEFAULT CHARACTER SET utf8mb4;


#04 修改某列的字符集
ALTER TABLE user CHANGE username username2021 CHARACTER SET latin1;

ALTER TABLE user CHANGE username2021 username VARCHAR(255) NOT NULL;

#05 往user表插入数据
INSERT user 
	(username,password,telphone,email) 
VALUES 
	('刘岩','666',13111112222,'[email protected]'),
	('叶子奇','888',13111110000,'[email protected]'),
	('杨向前','999',13111111111,'[email protected]');
	
	
CREATE TABLE user (
	id INT PRIMARY KEY AUTO_INCREMENT,
	username VARCHAR(255)  NOT NULL COMMENT '用户名',
	password VARCHAR(255) NOT NULL COMMENT '用户密码',
	telphone BIGINT NOT NULL COMMENT '电话号码',
	email VARCHAR(255) NOT NULL COMMENT '用户邮箱',
	deleted TINYINT DEFAULT 0 COMMENT '标记该条记录是否删除,0表示未删除,1表示已删除'
) DEFAULT CHARACTER SET utf8mb4;


GRANT ALL ON oldboyedu_goods.* TO admin;
GRANT Insert(id,username,password,telphone,email),Select(id,username,password,telphone,email),Update(username,password) ON oldboyedu_linux.user TO admin;
GRANT Insert(id,username,password,telphone,email),Select,Update(username,password) ON oldboyedu_linux.user TO admin;




INSERT user (username,password,telphone,email) VALUES
	('张宇宙','123',13366667777,'[email protected]'),
	('李彤','456',13366668888,'[email protected]'),
	('张伟','123456',13366669999,'[email protected]');

REVOKE ALL ON oldboyedu_goods.*  FROM admin;

三. 数据控制语言 DCL

1 概念

 即"数据控制语言"，简称"DCL"。

    是用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。
  
    有些学术派将DCL中的事物相关的操作归纳为事务控制语言(Transaction Control Language，简称"TCL")，关键字：COMMIT、ROLLBACK、SAVEPOINT。

2 用户管理

#01 创建用户
? CREATE USER
create user admin ;
create user admin@'10.0.0./24' ;
create user oldboy@'mysql108.oldboyedu.com' IDENTIFIED BY '123' ;
create user oldboyedu@'10.0.0.106' IDENTIFIED BY '123' ;
create user oldboyedu@'10.0.0.%' IDENTIFIED BY '123' ;
create user oldboyedu@'%' IDENTIFIED BY '123' ;


解释下：创建 '用户'@'授权登入的主机 可以域名和网段IP' IDENTIFIED BY '密码'

#02 使用其它主机连接数据库
[root@mysql106 ~]# mysql -u oldboyedu -p123 -h 10.0.0.107 -P 3306
[root@mysql108 ~]# mysql -u oldboy -p123 -h 10.0.0.107 -P 3306

解释下：-u 指定用户 -p 密码 -h 登入的主机ip -P 端口
注意： mysql5.7和5.6 创建用户可以互通 但是8.0不可以 因为插件版本不同


#03 删除用户
drop user oldboy@'mysql106.oldboy.com' ;


#04 修改用户
1）本地主机修改密码
alter user oldboyedu@'10.0.0.106' IDENTIFIED BY '123456' ;
2）远程修改密码
SET PASSWORD FOR 'oldboyedu'@'10.0.0.106' = PASSWORD('oldboyedu');
3）远程重设密码
alter user admin@'10.0.0.108' PASSWORD EXPIRE;




#05 查看用户
SELECT user,host,authentication_string from mysql.user;
SHOW GRANTS FOR oldboy@'mysql108.oldboyedu.com' ;  # 查看用户属性 
show grants for oldboyedu@'mysql106.oldboyedu.com' ;

DESC mysql.user;  # 查看表结构
select user,host,Password from mysql.user;   # 5.6版本查看 mysql库user表 user,host,Password 列
select user,host,plugin from mysql.user;	 # 查看 mysql库user表 user,host,plugin 列（字段）


SELECT user,host,authentication_string from mysql.user; # 相当于5.6版本 mysql库user表 里Password列

3 权限管理

帮助：? GRANT;

#01  # 查看授权信息 
SHOW PRIVILEGES; 			# 查看所有权限								#查看权限
SHOW GRANTS FOR oldboy@'mysql108.oldboyedu.com' ; 
SELECT * FROM mysql.user WHERE user = 'admin'\G
show grants for admin ;			# 查看admin用户的权限


#02 给用户授权
grant all on oldboyedu_goods.* to admin;	# 给用户授权库

解释下：grant 权限 库名称.表名称 to 用户;



grant Insert(id,username,password,telphone,email),Select(id,username,password,telphone,email),Update(id,username,password,telphone,email)ON oldboyedu_goods.user02 to admin;
grant Insert(id,username,password,telphone,email),Select,Update(id,username,password,telphone,email) ON oldboyedu_goods.user02 to admin;

解释下：给用户精准授权 Insert,Select,Update 某库某表(oldboyedu_goods.user02)某列(id,username,password,telphone,email)


使用该用户登入并插入数据测试
INSERT user02 (id,username,password,telphone,email)
VALUES (3,'陆十三','123456','1324164','[email protected]');


#03 回收权限
? REVOKE;
REVOKE all on oldboyedu_goods.* from admin;

4 角色管理

? create role
https://dev.mysql.com/doc/refman/8.0/en/roles.html

#01 创建角色
CREATE ROLE 'app_developer', 'app_read', 'app_write';
greate

#02 给角色授权权限
GRANT ALL ON oldboyedu_goods.* TO 'app_developer';
GRANT SELECT ON oldboyedu_goods.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON oldboyedu_goods.* TO 'app_write';


#03 创建用户
CREATE USER 'dev1' IDENTIFIED BY '123';
CREATE USER 'read_user1' IDENTIFIED BY '123';
CREATE USER 'read_user2' IDENTIFIED BY '123';
CREATE USER 'rw_user1' IDENTIFIED BY '123';

#04 给用户绑定角色
GRANT 'app_developer' TO 'dev1';
GRANT 'app_read' TO 'read_user1', 'read_user2';
GRANT 'app_read', 'app_write' TO 'rw_user1';


#05 检查权限
SHOW GRANTS FOR 'dev1';
SHOW GRANTS FOR 'dev1' USING 'app_developer';
SHOW GRANTS FOR 'read_user1' USING 'app_read';
SHOW GRANTS FOR 'rw_user1' USING 'app_read', 'app_write';


#06 激活角色
SELECT CURRENT_ROLE();
SET DEFAULT ROLE ALL TO
  'dev1',
  'read_user1',
  'read_user2',
  'rw_user1';

#07 测试权限
[root@mysql107 ~]# mysql -u read_user1 -p123 -h 10.0.0.108
[root@mysql107 ~]# mysql -u dev1 -p123 -h 10.0.0.108
mysql> use oldboyedu_goods ;
mysql> drop table user02 ;

#08 回收角色
1)用户和角色取消绑定
? revoke 
REVOKE role FROM user; 					#格式
REVOKE app_write FROM rw_user1 ;		#解绑rw用户和app_write角色的绑定
revoke app_developer from dev1 ;
mysql> SHOW GRANTS FOR 'rw_user1';		#检查权限

解释下：revoke 角色 from 用户 ;

2)取消角色的权限
REVOKE INSERT, UPDATE, DELETE ON oldboyedu_goods.* FROM 'app_write';
SHOW GRANTS FOR 'app_write';


3)恢复角色
GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write';


4)删除角色
DROP ROLE 'app_read', 'app_write';
drop role app_developer ;

解释下：drop role 角色1,角色2

四. 数据查询类语句 DQL

1 语法格式 SELECT


#01 show使用方式
show processlist ;					#查看当前用户登入信息

#02 select使用方式
select user ();						#查看当前登入用户
SELECT CURRENT_ROLE();				#当前登入角色 （8.0）
select database () ;				#查看当前位置 在那个库
select count(*) from tab_name;		#显示表的行数
SELECT NOW();						#显示当前系统时间
SELECT @@PORT;						#显示端口


#03 查看字符串拼接的函数-CONCAT()
SELECT CONCAT("hello world!");			
SELECT user,host FROM mysql.user;
SELECT CONCAT(user,"@",host) AS "完整用户名" FROM mysql.user;
select concat(user,'@',host) from mysql.user;


#04 查看当前数据库的版本-VERSION()
SELECT VERSION();


#05 SELECT语句进行算术运算
SELECT  3 * 10;
SELECT  3 * 10 + 8 * 2;


#06 查看变量
show variables like '%dir%' ;

解释下 查询 *dir* 的变量

#07 对指定列去重-DISTINCT
SELECT DISTINCT(CountryCode) FROM city;
SELECT DISTINCT(producer) FROM luxury ;

解释下：指定(列) from 表中 去重

#08 查看MySQL支持的函数类型及帮助信息
HELP Functions				#
HELP  String Functions
? upper
select ascii ('A');

2 单表查询 SELECT FROM

#01 导入测试数据 并查看表结构
desc country ;							#查看表字段
world ]-> select count(*) from  city ;	#查看行数
show create table city\G				#查看表属性


#02 查看指定字段内容 并显示3行
select Name,District,Population from city limit 3;

#03 AS 别名
[Thu Sep  9 11:35:48 2021 world ]-> select Name AS '城市名称',Population AS '城市人口' from city limit 3;
+--------------+--------------+
| 城市名称     | 城市人口     |
+--------------+--------------+
| Kabul        |      1780000 |
| Qandahar     |       237500 |
| Herat        |       186800 |
+--------------+--------------+

解释下：selct 字段 AS '别名' 表 即可

#这里可以简写
[Thu Sep  9 11:35:19 2021 world ]-> select Name '城市名称',Population'城市人口' from city limit 3;
+--------------+--------------+
| 城市名称     | 城市人口     |
+--------------+--------------+
| Kabul        |      1780000 |
| Qandahar     |       237500 |
| Herat        |       186800 |
+--------------+--------------+


#03 查看指定行
create table t1 select * from food limit 25 ;	#创建表
mysql> select * from food limit 50,10;			#偏移50 显示10条
mysql> select * from food limit 20,2;			#偏移20 显示2条 从第20条开始 显示2条
+----+--------------------------+-------+---------------------------------------+--------------+-----------+
| id | name                     | price | item                                  | brand        | producer  |
+----+--------------------------+-------+---------------------------------------+--------------+-----------+
| 21 | 三只松鼠每日坚果         |   139 | https://item.jd.com/100000499657.html | 三只松鼠     | 刘志宇    |
| 22 | 坚果大礼包               |   109 | https://item.jd.com/5903062.html      | 洽洽         | 刘志宇    |
+----+--------------------------+-------+---------------------------------------+--------------+-----------+

解释下：从第几行开始展示 到第几行结束

3 单表查询 SELECT WHERE


##语法格式
WHERE							#匹配
AND 							#并且
OR  							#或者
IN								#匹配等于(多个字段)
LIKE 'CH%' 						#模糊匹配 以CH开头的 数据
BETWEEN .10.AND.5.  			#匹配区间内容  10到5之间的数据

#01 查询属于中国的城市
SELECT * FROM city WHERE CountryCode = 'CHN' ;

解释下：查询 'city表' 字段 = 'CHN' 的所有内容

#02 查询属于中国的城市人口 并大于500万的城市
SELECT * FROM city  WHERE CountryCode = 'CHN' AND Population >5000000;

解释下：查询 'city表' 字段 = 'CHN' 并且(AND)

#03 查询中国和美国的城市
SELECT * FROM city WHERE CountryCode = 'CHN' OR CountryCode = 'USA'
SELECT * FROM city WHERE CountryCode IN ('CHN','USA')


#04 查询中国或美国的城市信息 并且人数超过800万
SELECT * FROM city WHERE CountryCode IN ('CHN','USA') AND Population >8000000;
SELECT * FROM city WHERE (CountryCode = 'CHN' OR CountryCode = 'USA') AND Population >8000000;


#05 查询中国或者美国的城市 并且人数在500万和8000万之间
SELECT * FROM city WHERE (CountryCode = 'CHN' OR CountryCode = 'USA') AND 5000000 < Population AND Population <8000000;

SELECT * FROM city WHERE CountryCode IN('CHN', 'USA') AND Population BETWEEN 5000000 AND 8000000 ;

解释下：  匹配city表 字段等于('CHN', 'USA') 并且 在5000000-8000000之间的


#06 查询city中，国家代号是头的城市信息
SELECT * FROM city WHERE CountryCode LIKE 'CH%' ;

4 单表查询函数使用

#语法格式
GROUP BY 	  #结合聚合函数，根据一个或多个列对结果集进行分组

#聚合函数
? Aggregate Functions and Modifiers 


------函数使用------

#01 统计每个国家城市的个数
SELECT CountryCode,COUNT(*) FROM city GROUP BY CountryCode;

解释下：count(*) 计算和 给city库 countrycode字段分组 并且计算和

#02 统计中国每个省的城市个数
SELECT District,COUNT(*) FROM city WHERE CountryCode= 'CHN' GROUP BY District;

#03 统计每个国家的总人口
SELECT CountryCode,sum(Population) FROM city GROUP BY CountryCode;

#04 统计中国每个省的总人口
SELECT District,sum(Population) FROM city WHERE CountryCode= 'CHN' GROUP BY District;


#05 统计中国每个城市个数及城市名称列表和人口
SELECT
	District,
	sum( Population ),
	COUNT( ID ),
	GROUP_CONCAT( NAME ) 
FROM
	city 
WHERE
	CountryCode = 'CHN' 
GROUP BY
	District



--------------------
#语法格式
GROUP BY 	  #结合聚合函数，根据一个或多个列对结果集进行分组
COUNT(*) 	  #统计相加
#聚合函数
? Aggregate Functions and Modifiers 

MIN				  #最小值
MAX				  #最大值
AVG(字段)			#平均值
COUNT(*) 	  	  #统计相加

SELECT AVG()   AS '衣服的平均价格'FROM clothing ;

#01 练习题
-- 分析出7个商品的最贵的,最便宜,平均价格的产品?
-- 分析各组商品价格大于500的产品有多少件?
-- 分析各组商品价格大于10000产品列出其名称;
-- 分析各组商品价格大于5万小于10万的商品并查看前5个商品名称;
id 				#存储商品id
name 			#商品名称
price   		#商品价格
item    		#商品浏览地址
brand			#商品的品牌
producer		#作者


#02 答案
-- 分析出7个商品的最贵的,最便宜,平均价格的产品?
SELECT type,MIN(price) '最便宜价格',MAX(price)'最贵价格',AVG(price)'平均价格' FROM t3 GROUP BY type ; 

-- 分析各组商品价格大于500的产品有多少件?
SELECT COUNT(*) '价格大于500总个数' FROM t3 WHERE price >500 ;

-- 分析各组商品价格大于10000产品列出其名称;
SELECT name '商品价格大于10000产品' FROM t3 WHERE price >10000 GROUP BY name ;

-- 分析各组商品价格大于5万小于10万的商品并查看前5个商品名称;

SELECT * FROM t3 WHERE price BETWEEN 50000 AND 100000 LIMIT 5 ;

-- 分析各组商品的最贵的,最便宜,平均价格的产品并排序显示Top3;
-- 分析各组商品价格大于500的产品有多少件,降序输出,显示Top10;
-- 分析各组商品价格大于10000产品列出其名称,并降序输出,显示Top8;
-- 分析各组商品价格大于5万小于10万的商品并降序输出15个商品名称?

-----

SELECT type,MAX(price) '最大',MIN(price),AVG(price) FROM t1 GROUP BY type ORDER BY MAX(price) DESC LIMIT 3;
SELECT type,MAX(price),MIN(price) '最小',AVG(price) FROM t1 GROUP BY type ORDER BY MIN(price) LIMIT 3;
SELECT type,MAX(price),MIN(price),AVG(price) '平均' FROM t1 GROUP BY type ORDER BY AVG(price) DESC LIMIT 3;


SELECT type,price,name'大于500块得产品名称' FROM t3 WHERE price > 500 ORDER BY price DESC limit 10;

SELECT type,name '大于10000块得产品名称',price  FROM t1 WHERE price > 10000 ORDER BY price DESC LIMIT 8;

SELECT type,name,price '5w-10w得商品' FROM t1 WHERE price >= 50000 and price <= 100000 ORDER BY price DESC limit 15;

SELECT type,name,price '5w-10w得商品' FROM t1 WHERE  price BETWEEN 50000 and 100000 ORDER BY price DESC limit 15;


--
SELECT * FROM t1;
-- 分析出7个商品的最贵的,最便宜,平均价格的产品?
SELECT type,MAX(price),MIN(price),AVG(price) FROM t1 GROUP BY type;

-- 分析各组商品价格大于500的产品有多少件?
SELECT type,COUNT(name) FROM t1 WHERE price > 500 GROUP BY type;
SELECT type,COUNT(type) FROM t1 WHERE price > 500 GROUP BY type;

-- 分析各组商品价格大于10000产品列出其名称;
SELECT type,GROUP_CONCAT(name) FROM t1 WHERE price > 10000 GROUP BY type;
SELECT type,name,price FROM t1 WHERE price > 10000;

-- 分析各组商品价格大于5万小于10万的商品并查看前5个商品名称;
SELECT type,name,price FROM t1 WHERE price BETWEEN 50000 AND 100000 limit 5;

#升级
-- 分析各组商品的最贵的,最便宜,平均价格的产品并排序显示Top3;
SELECT type,MAX(price),MIN(price),AVG(price) FROM t1 GROUP BY type ORDER BY MAX(price) DESC LIMIT 3;
SELECT type,MAX(price),MIN(price),AVG(price) FROM t1 GROUP BY type ORDER BY MIN(price) LIMIT 3;
SELECT type,MAX(price),MIN(price),AVG(price) FROM t1 GROUP BY type ORDER BY AVG(price) DESC LIMIT 3;

-- 分析各组商品价格大于500的产品有多少件,降序输出,显示Top10;
SELECT type,COUNT(name) FROM t1 WHERE price >500 GROUP BY type ORDER BY COUNT(name) DESC LIMIT 10;
SELECT type,COUNT(*) c FROM t1 WHERE price >500 GROUP BY type ORDER BY c DESC LIMIT 10;

-- 分析各组商品价格大于10000产品列出其名称,并降序输出,显示Top8;
SELECT type,name,price FROM t1 WHERE price > 10000 ORDER BY price DESC LIMIT 8;

-- 分析各组商品价格大于5万小于10万的商品并降序输出15个商品名称?
SELECT type,name,price FROM t1 WHERE price BETWEEN 50000 AND 100000 ORDER BY price DESC LIMIT 15;




--------------------------------------------------

#01 统计中国每个省的总人数在800万以上的省份，

SELECT District,sum(Population) FROM city WHERE CountryCode= 'CHN' GROUP BY District HAVING sum(Population) > 8000000;

SELECT District,sum(Population) 'P' FROM city WHERE CountryCode= 'CHN' GROUP BY District HAVING P > 8000000;

#02 统计中国每个省的总人数在800万到1000万之间的的省份，
SELECT District,sum(Population) 'P' FROM city WHERE CountryCode= 'CHN' GROUP BY District HAVING P BETWEEN 8000000 and 10000000 ; 


#03 统计中国每个省的总人数在800万到1000万之间的的省份，并且按照总人口数从小到大排序升序输出
SELECT District,sum(Population) 'P' FROM city WHERE CountryCode= 'CHN' GROUP BY District HAVING P BETWEEN 8000000 and 10000000 ORDER BY P ASC ; --默认就是升序 ASC


#04 统计中国每个省的总人数在800万到1000万之间的的省份，并且按照总人口数从小到大排序降序输出
SELECT District,sum(Population) 'P' FROM city WHERE CountryCode= 'CHN' GROUP BY District HAVING P BETWEEN 8000000 and 10000000 ORDER BY P DESC ; --默认就是升序 ASC


#05 统计中国每个省的总人数在800万以上的省份，并且按照总人口数从大到小排序输出，但只显示前3名。
SELECT District,sum(Population) 'P' FROM city WHERE CountryCode= 'CHN' GROUP BY District HAVING P BETWEEN 8000000 and 10000000 ORDER BY P ASC LIMIT 3 ; 


#06 统计中国每个省的总人数在800万以上的省份，并且按照总人口数从大到小排序输出，但只显示前4-5名。
SELECT District,sum(Population) 'P' FROM city WHERE CountryCode= 'CHN' GROUP BY District HAVING P > 8000000 ORDER BY P ASC LIMIT 3,2 ;

5 单库查询系列

-------------------------------------

#01 统计每个库，所有表的名称及个数
SELECT
	TABLE_SCHEMA '数据库名称',
	COUNT( TABLE_NAME ) '表个数',
	GROUP_CONCAT( TABLE_NAME ) '表名称'
FROM
	information_schema.TABLES 
GROUP BY
	TABLE_SCHEMA;

#02 统计每个库的占用空间总大小
SELECT
	TABLE_SCHEMA '数据库',
	SUM( AVG_ROW_LENGTH * TABLE_ROWS + INDEX_LENGTH ) / 1024 / 1024 '表占的大小MB'
FROM
	information_schema.TABLES 
GROUP BY
	TABLE_SCHEMA;

#03 
SELECT
	TABLE_SCHEMA,
	SUM( DATA_LENGTH ) / 1024 / 1024 
FROM
	information_schema.TABLES 
GROUP BY
	TABLE_SCHEMA;

6 多表合并

##项目要求 合并七张表格 并且添加个字段tyep 

# 查询各组的价格平均值
SELECT AVG(price) AS '衣服的平均价格' FROM clothing;  
SELECT AVG(price) AS '化妆品的平均价格' FROM cosmetics;
SELECT AVG(price) AS '药品的平均价格' FROM drugs;
SELECT AVG(price) AS '电子产品的平均价格' FROM electronic;
SELECT AVG(price) AS '食物的平均价格' FROM food;
SELECT AVG(price) AS '奢侈品的平均价格' FROM luxury;
SELECT AVG(price) AS '玩具的平均价格' FROM toys;

# 合并表
## A:建表
CREATE TABLE `t1` (
  `id` int PRIMARY KEY AUTO_INCREMENT COMMENT '商品的ID',
  `name` varchar(255) NOT NULL COMMENT '商品名称',
  `price` float NOT NULL COMMENT '商品的价格',
  `items` varchar(255) NOT NULL COMMENT '商品浏览地址',
  `brand` varchar(255) NOT NULL COMMENT '商品的品牌',
  `producer` varchar(255) NOT NULL COMMENT 'who',
	type VARCHAR(255)  NOT NULL DEFAULT ''
) 

## 查询待合并的几张表
-- 合并服装表(clothing)--->120条记录
INSERT t1 
    (name,price,items,brand,producer) 
SELECT name,price,item,brand,producer FROM clothing;

-- UPDATE t1 SET type = 'clothing' WHERE id <= 120;
UPDATE t1 SET type = 'clothing' WHERE type = '';

-- 合并化妆品表(cosmetics) ---> 130条记录
INSERT t1 
    (name,price,items,brand,producer) 
SELECT name,price,item,brand,producer FROM cosmetics;

-- UPDATE t1 SET type = 'cosmetics' WHERE id > 120;
UPDATE t1 SET type = 'cosmetics' WHERE type = '';

-- 合并药品表(drugs) ---> 344条记录
INSERT t1 
    (name,price,items,brand,producer) 
SELECT name,price,item,brand,producer FROM drugs;

-- UPDATE t1 SET type = 'drugs' WHERE id > 250;
UPDATE t1 SET type = 'drugs' WHERE type = '';

-- 合并电子产品表(electronic) ---> 120条记录
INSERT t1 
    (name,price,items,brand,producer) 
SELECT name,price,item,brand,producer FROM electronic;

-- UPDATE t1 SET type = 'electronic' WHERE id > 594;
UPDATE t1 SET type = 'electronic' WHERE type = '';

-- 合并食物表(food)
INSERT t1 
    (name,price,items,brand,producer) 
SELECT name,price,item,brand,producer FROM food;

-- UPDATE t1 SET type = 'food' WHERE id > 714;
UPDATE t1 SET type = 'food' WHERE type = '';

-- 合并奢侈品表(luxury)
INSERT t1 
    (name,price,items,brand,producer) 
SELECT name,price,item,brand,producer FROM luxury;

-- UPDATE t1 SET type = 'luxury' WHERE id > 814;
UPDATE t1 SET type = 'luxury' WHERE type = '';

-- 合并玩具表(toys)
INSERT t1 
    (name,price,items,brand,producer) 
SELECT name,price,items,brand,producer FROM toys;

-- UPDATE t1 SET type = 'toys' WHERE id > 954;
UPDATE t1 SET type = 'toys' WHERE type = '';

-- 检查各组的数据条目数是否正确!
SELECT type,COUNT(*) FROM t1 GROUP BY type;


注意：基于id过滤 不行 因为AUTO_INCREMENT 并不是连续的

一个开发同事 让我帮忙合并表 后来我基于id过滤表格 添加字段发现 AUTO_INCREMENT 并不是准确的顺序 会有变化 解决 匹配为空 然后单表过滤添加



##方法2 

#01 更改字段属性 因为两个表字段属性不一致 环境一致可以忽略 这个也可以放在后面
alter table electronic drop id;
alter table electronic add id int unsigned primary key auto_increment first;
alter table toys drop id;
alter table toys add id int unsigned primary key auto_increment first;




#02 添加字段type
alter table food add type varchar(30) default 'food';
alter table clothing add type varchar(30) default 'clothing';
alter table luxury add type varchar(30) default 'luxury';
alter table drugs add type varchar(30) default 'drugs';
alter table cosmetics add type varchar(30) default 'cosmetics';
alter table electronic add type varchar(30) default 'electronic';
alter table toys add type varchar(30) default 'toys';


#03 新建表格
create table t3 select * from clothing union all 
select * from cosmetics union all
select * from food union all 
select * from luxury union all 
select * from drugs union all 
select * from toys union all 
select * from electronic ;

				
select type,count(*) from t2  group by type ;
select count(*) from t2 ;						# 显示表的行数

7 多表查询

1）内连接

###查看每个演员都查看了那些作品

#01 分析需求：
#02 源数据在哪：需要用到那些表？---> actor works
#03 找关联关系：开始写sql

-- 
#01 检查张宇宙查看的电影和评分

SELECT
	u.`name` '影评人',
	u.score '评分',
	w.`name` '作品' 
FROM
	user u
	JOIN works w ON u.works_id = w.actor_id 
WHERE
	u.`name` = '张宇宙';
	
#02 检查张森看过的作品和数量

#03 查询评价过周星驰作品的用户姓名
## user works actor 
## user.works_id works.actor_id  actor.id 


SELECT
	u.`name` '影评人',
	u.score '评分',
	a.`name` '主演',
	w.`name` '作品' 
FROM
	`user` u
	JOIN works w
	JOIN actor a ON u.works_id = w.id 
	AND w.actor_id = a.id 
WHERE
	a.`name` = '周星驰';


--- 显示周星驰电影被评论的次数

SELECT
	a.`name` '主演',
	w.`name` '电影名称',
	GROUP_CONCAT( u.`name` ) '观影人数',
	COUNT(*) '点播次数' 
FROM
	`user` u
	JOIN works w
	JOIN actor a ON u.works_id = w.id 
	AND w.actor_id = a.id 
WHERE
	a.`name` = '周星驰' 
GROUP BY
	w.`name`;

#04 查询周星驰各个作品的平均分数 并排序

SELECT
	w.`name` '电影名称',
	a.`name` '主演',
	AVG(u.score) '平均分',
	COUNT(*) '点播次数' 
FROM
	`user` u
	JOIN works w
	JOIN actor a ON u.works_id = w.id 
	AND w.actor_id = a.id 
WHERE
	a.`name` = '周星驰' 
GROUP BY
	w.`name` ORDER BY AVG(u.score) DESC  ;


#05 每位演员各个作品的平均分,并按平均分降序排序
# 作品名称 演员 平均分

SELECT
	w.`name` '电影名称',
	a.`name` '主演',
	AVG( u.score ) '平均分'
	
FROM
	`user` u
	JOIN works w
	JOIN actor a ON u.works_id = w.id 
	AND w.actor_id = a.id 
GROUP BY
	w.`name` ,
	a.`name`
ORDER BY
	AVG( u.score ) DESC ;
	

#06 查询给周星驰作品的评分低于8分的用户姓名

	
SELECT
	a.`name` '主演',
	w.`name` '电影名称',
	GROUP_CONCAT(u.`name`,u.score),
  AVG(u.score) '场均评分'
FROM
	`user` u
	JOIN works w
	JOIN actor a ON u.works_id = w.id 
	AND w.actor_id = a.id 
WHERE
	a.`name` = '周星驰' AND u.score < 8
GROUP BY
	w.`name`;


#07 查询所有演员评分低于8分的作品及用户姓名
# 演员名称 作品集 

SELECT
	w.`name` '作品名称',
	GROUP_CONCAT( u.id, u.`name`, '@', u.score ) 
FROM
	`user` u
	JOIN ( works w, actor a ) ON u.works_id=w.actor_id
	AND w.actor_id = a.id 
	WHERE
	u.score < 8
GROUP BY
	作品名称;


#08 查询所有演员平均评分大于7分的作品

SELECT
  a.`name` '主演',
	w.`name` '电影名称',
	AVG(u.score) '平均分'
	
FROM
	`user` u
	JOIN works w
	JOIN actor a ON u.works_id = w.id 
	AND w.actor_id = a.id 
GROUP BY 
 w.`name`,
 a.`name`
 HAVING
 平均分 > 7
 ORDER BY 平均分 DESC ;


--
# 课堂练习:
## 查询所有用户在姓名、选课数和给出的总评分,并显示前10;
#09 用户姓名 评论作品数 总评分 前十

SELECT
	u.`name` '姓名',
	COUNT( u.`name` ) '评论次数',
	sum( u.score ) '总评分' 
FROM
	`user` u
	JOIN works w
	JOIN actor a ON u.works_id = w.id 
	AND w.actor_id = a.id 
GROUP BY
	u.`name` 
ORDER BY
	总评分 DESC 
	LIMIT 10;

##10 查询各个作品成绩最高和最低的分：以如下形式显示：作品名称，最高分，最低分
## 作品名称 最高分 最低分

SELECT
	w.`name` '作品名称',
	MIN( u.score ) '最低分',
	MAX( u.score ) '最高分' 
FROM
	`user` u
	JOIN works w
	JOIN actor a ON u.works_id = w.id 
	AND w.actor_id = a.id 
GROUP BY
	w.`name` 
ORDER BY
	最低分;

##12 统计各位演员的作品平均分数大于等于8分案例

SELECT
	w.`name` '作品',
	a.`name` '演员名称',
	AVG( u.score ) '平均分' 
FROM
	`user` u
	JOIN works w
	JOIN actor a ON u.works_id = w.id 
	AND w.actor_id = a.id 
GROUP BY
	w.`name`,
	a.`name` 
HAVING
	平均分 >= 8 
ORDER BY
	平均分 DESC;

##13 统计作品的评分:优秀(8.5分以上),良好(7-8.5),一般(6-7),不及格(5分以下)的用户列表


SELECT
	u.`name` '用户',
	AVG( u.score ) '优秀平均分数' 
FROM
	`user` u 
GROUP BY
	u.`name`,
	u.id
	HAVING
	优秀平均分数 > 8.5 ;

SELECT
	u.`name` '用户',
	AVG( u.score ) '良好平均分数' 
FROM
	`user` u 
GROUP BY
	u.`name`,
	u.id
	HAVING 
	良好平均分数 BETWEEN 7 AND 8.5 ;


SELECT
	u.`name` '用户',
	AVG( u.score ) '一般平均分数' 
FROM
	`user` u 
GROUP BY
	u.`name`,
	u.id
	HAVING 
	一般平均分数 BETWEEN 7 AND 6 ;


SELECT
	u.`name` '用户',
	AVG( u.score ) '不及格' 
FROM
	`user` u 
GROUP BY
	u.`name`,
	u.id
	HAVING 
	不及格 < 5 ;


--case 语句写法


SELECT
 u.`name` '用户名',
GROUP_CONCAT(CASE WHEN u.score > 8.5 THEN u.score END) AS 优秀,
GROUP_CONCAT(CASE WHEN u.score > 7 AND u.score < 8.5 THEN u.score END) AS 良好,
GROUP_CONCAT(CASE WHEN u.score > 6 AND u.score < 7 THEN u.score END) AS 一般,
GROUP_CONCAT(CASE WHEN u.score < 5 THEN u.score END) AS 不及格
FROM 
  `user` u
GROUP BY
  u.`name`,
	u.id;



##14 查询各个作品被评分的用户数
## 作品 用户数

SELECT
	w.`name` '作品名称',
	GROUP_CONCAT( u.`name` ) '用户',
	COUNT( u.`name` ) '用户数' 
FROM
	`user` u
	JOIN works w
	JOIN actor a ON u.works_id = w.id 
	AND w.actor_id = a.id 
GROUP BY
	w.`name`,
	a.`name` 
ORDER BY
	用户数 DESC 
	LIMIT 5;




##15 查询出评论了25个作品以上的全部用户的姓名

SELECT
	u.`name` '姓名',
	COUNT( * ) '评论的次数' 
FROM
	`user` u 
GROUP BY
	u.`name`,
	u.id 
HAVING
	评论的次数 > 25 
ORDER BY
	评论的次数 DESC;



##16 查询平均评分大于8.5的所有用户的学号、姓名和平均评分

SELECT
	u.`name` '姓名',
	AVG(u.score) '平均分'
FROM
	`user` u 
GROUP BY
	u.`name`,
	u.id 
HAVING 
 平均分 > 8.5 ORDER BY 平均分 DESC ;


	


-----------写法2）
# 查看每个演员都有哪些作品
## 1.分析需求: 略.
## 2.源数据在哪: 需要用到哪些表? ---> actor,works
## 3.找关联关系: 开始写SQL
SELECT
	* 
FROM
	actor
	JOIN works ON actor.id = works.actor_id;
	
## 4.完善需求
SELECT
	actor.`name`,
	GROUP_CONCAT( works.`name` ) 
FROM
	actor 
  works ON actor.id = works.actor_id 
GROUP BY
	actor.`name`;


## 5.开始完善,优化
SELECT
	a.`name` AS '姓名',
	GROUP_CONCAT( w.`name` ) AS '作品' 
FROM
	actor AS a
	RIGHT JOIN works AS w ON a.id < w.actor_id 
GROUP BY
	a.`name`;
	
	
	
# 统计自己评论的作品,要求只显示"姓名,作品名称,评分"三列
SELECT
	`user`.NAME '姓名',
	works.`name` '作品名称',
	`user`.score '评分' ,
	COUNT(*)
FROM
	`user`
	JOIN works ON `user`.works_id = works.id 
WHERE
	`user`.`name` = '张森' 
	AND `user`.id = 7;
	

# 2.统计用户"张森"评价过的所有作品
## 1.分析需求: 略.
## 2.源数据在哪: 需要用到哪些表? ---> `user`,works
## 3.找关联关系:	(可以生成一个临时表!)
##		works.id = `user`.works_id
## 4.开始单表查询
## 5.优化SQL
SELECT
	u.id AS '组名称',
	u.`name` AS '姓名',
	GROUP_CONCAT( w.`name` ) AS '评论作品' 
FROM
	`user` AS u
	JOIN works AS w ON w.id = u.works_id 
GROUP BY
	u.id,
	u.`name` 
HAVING
	u.`name` = '张森';

SELECT 
	user.id,
	user.`name`,
	GROUP_CONCAT( works.`name` ),
	COUNT(*)
FROM
	user JOIN works ON user.works_id = works.id 
WHERE
	user.name = '张森' 
GROUP BY
	user.id


# 3.查询评价过周星驰作品的用户姓名及用户的个数
## 1.分析需求: 略.
## 2.源数据在哪: 需要用到哪些表? ---> `user`,works,actor
## 3.找关联关系:	(可以生成一个临时表!)
##		works.id = `user`.works_id
##		actor.id = works.actor_id
## 4.开始单表查询
## 5.优化SQL
SELECT
	actor.`name` AS '主演',
	works.`name` AS '作品',
	GROUP_CONCAT( `user`.`name` ) AS '观众',
	COUNT( * ) AS '观看次数'
FROM
	`user`
	JOIN ( works, actor ) ON works.id = `user`.works_id 
	AND actor.id = works.actor_id 
WHERE
	actor.`name` = '周星驰' 
GROUP BY
	works.`name`;


# 4.查询周星驰各个作品的平均分数
## 1.分析需求: 略.
## 2.源数据在哪: 需要用到哪些表? ---> `user`,works,actor
## 3.找关联关系:	(可以生成一个临时表!)
##		works.id = `user`.works_id
##		actor.id = works.actor_id
## 4.开始单表查询
## 5.优化SQL
SELECT
	actor.`name` AS '主演',
	works.`name` AS '作品',
	AVG( `user`.score ) AS '平均分数'
FROM
	`user`
	JOIN ( works, actor ) ON works.id = `user`.works_id 
	AND actor.id = works.actor_id 
WHERE
	actor.`name` = '周星驰' 
GROUP BY
	works.`name`;


# 5.每位演员各个作品的平均分,并按平均分降序排序
## 1.分析需求: 略.
## 2.源数据在哪: 需要用到哪些表? ---> `user`,works,actor
## 3.找关联关系:	(可以生成一个临时表!)
##		works.id = `user`.works_id
##		actor.id = works.actor_id
## 4.开始单表查询
## 5.优化SQL
SELECT
	actor.`name` AS '主演',
	works.`name` AS '作品',
	AVG( `user`.score ) AS '平均分数'
FROM
	`user`
	JOIN ( works, actor ) ON works.id = `user`.works_id 
	AND actor.id = works.actor_id 
GROUP BY
	works.`name`,actor.`name`
ORDER BY
	平均分数 DESC;


# 6.查询给所有作品的评分低于5分的用户姓名
## 1.分析需求: 略.
## 2.源数据在哪: 需要用到哪些表? ---> `user`,works,actor
## 3.找关联关系:	(可以生成一个临时表!)
##		works.id = `user`.works_id
##		actor.id = works.actor_id
## 4.开始单表查询
## 5.优化SQL
SELECT
	works.`name` AS '作品名称',
	GROUP_CONCAT( `user`.id,'@',`user`.`name`,'@', `user`.score ,'|||') 
FROM
	`user`
	JOIN ( works, actor ) ON works.id = `user`.works_id 
	AND actor.id = works.actor_id 
WHERE
	 `user`.score < 5 
GROUP BY
	作品名称

# 7.查询所有演员平均评分大于7分的作品
## 1.分析需求: 略.
## 2.源数据在哪: 需要用到哪些表? ---> `user`,works,actor
## 3.找关联关系:	(可以生成一个临时表!)
##		works.id = `user`.works_id
##		actor.id = works.actor_id
## 4.开始单表查询
## 5.优化SQL
SELECT
	actor.`name` AS '主演',
	works.`name` AS '作品名称',
	AVG( `user`.score ) AS '平均评分'
-- 	,GROUP_CONCAT( `user`.id, '@', `user`.`name`, '@', `user`.score, '|||' ) 
FROM
	`user`
	JOIN ( works, actor ) ON works.id = `user`.works_id 
	AND actor.id = works.actor_id 
GROUP BY
	works.`name`,
	actor.`name` 
HAVING
	平均评分 > 7
ORDER BY
	平均评分 DESC
LIMIT
	3;

---彪哥书写
# 课堂练习:
## 查询所有用户在姓名、评论作品数和给出的总评分,并显示前10;

SELECT name,count(works_id),SUM(score) FROM `user` GROUP BY user.name,user.id ORDER BY  SUM(score) DESC LIMIT 10;


## 查询各个作品成绩最高和最低的分：以如下形式显示：作品名称，最高分，最低分

select w.`name`,max(u.score),min(u.score) from  user as u JOIN works as w JOIN actor as a on u.works_id=w.id and w.actor_id = a.id GROUP BY w.`name`;

## 统计各位演员的作品分数大于等于8分案例

select a.name,w.name,u.score from  user as u JOIN works as w JOIN actor as a on u.works_id=w.id and w.actor_id = a.id where u.score>8 ;

## 统计作品的评分:优秀(8.5分以上),良好(7-8.5),一般(6-7),不及格(5分以下)的用户列表

select 
  a.name '演员',w.name '作品',GROUP_CONCAT(u.name,'-',u.score) '评价人(分数"优秀")'
from  
  user as u 
  JOIN 
	  works as w 
  JOIN 
		actor as a 
	on 
	  u.works_id=w.id 
		and 
		w.actor_id = a.id 
where 
     u.score>8.5 
GROUP BY 
     w.name,a.name;
	 

## 查询各个作品被评分的用户数

select 
  w.name '作品',COUNT(u.name) '用户数'
from  
  user as u 
  JOIN 
	  works as w 
  JOIN 
		actor as a 
	on 
	  u.works_id=w.id 
		and 
		w.actor_id = a.id 
GROUP BY 
     w.name,a.name;

## 查询出评论了25个作品以上的全部用户的姓名

select 
  u.name '用户',COUNT(u.name) '用户数'
from  
  user as u 
  JOIN 
	  works as w 
  JOIN 
		actor as a 
	on 
	  u.works_id=w.id 
		and 
		w.actor_id = a.id 
GROUP BY 
    u.name
HAVING  
    COUNT(u.name) > 25;
	
	

## 查询平均评分大于8.5的所有用户的姓名和平均评分

select 
  u.name '用户',AVG(u.score)  '平均评分'
from  
  user as u 
  JOIN 
	  works as w 
  JOIN 
		actor as a 
	on 
	  u.works_id=w.id 
		and 
		w.actor_id = a.id 
GROUP BY 
    u.name
HAVING  
    AVG(u.score) >8.5
;




--------------------项目2-----------------------------------
------------------------------------------------------------


## 使用内连接查询人口数少于1000的城市的国家,人数最少的前5个!
## 1.分析需求: 略.
## 2.源数据在哪: 需要用到哪些表? ---> city,country
## 3.找关联关系:	(可以生成一个临时表!)
##		city.CountryCode = country.Code
## 4.开始单表查询
## 5.优化SQL



#01 使用内连接查询人口少于1000 的国家
SELECT * FROM city ci JOIN country co on ci.CountryCode = co.`Code`


#02 使用内连接查询人口少于1000 的国家 并且取出前十
SELECT
	ci.`Name` AS '城市',
	ci.`Population` AS '人口数量',
	co.`Name` '国家' 
FROM
	city ci
	JOIN country co ON ci.CountryCode = co.`Code` 
WHERE
	ci.Population < 1000 
ORDER BY
	人口数量 DESC
	LIMIT 10;

2）外连接

#01 概念
LEFT JOIN		# 左表显示所有数据,右表只显示满足条件的数据
RIGHT JOIN		# 右表显示所有数据,左表只显示满足条件的数据


#02 左连接
SELECT
	city.NAME AS '城市',
	country.NAME AS '国家',
	city.Population AS '人口' 
FROM
	city
	LEFT JOIN country ON city.countrycode = country.CODE 
	AND city.Population < 100 
ORDER BY
	city.Population DESC;

#03 右连接
SELECT
	city.NAME AS '城市',
	country.NAME AS '国家',
	city.Population AS '人口' 
FROM
	city
	RIGHT JOIN country ON city.countrycode = country.CODE 
	AND city.Population < 100;

五元数据

1 概述

    在数据库中存储数据的基本逻辑单元是表，我们通常可以往表中插入数据，修改数据，删除数据和查询数据。那么问题来了，基于表存储数据，那么表本身的信息存储在哪里呢？

    一张表包含了数据字典，数据行记录，索引，数据库状态，权限，日志等信息。除了数据行记录和索引外，其他的都可以理解为和元数据相关的信息，我们可以直接在information_schema数据库中进行查询元数据信息。

    数据字典：
        含义:
            即表中定义的字段信息。
        存储路径:
            数据存储在安装数据库实例配置的数据目录(即"datadir")下。
            对于MyISAM存储引擎而言: 表在对应数据库目录下以"表名.frm"命名(如:"mysql数据库的user.frm")。
            对于MySQL 8.0版本以前，InnoDB存储引擎而言: 表在对应数据库目录下以"表名.frm"命名(如:"world数据库的city.frm")和idbdata1中存储。

    数据行记录:
        含义:
            存储的真实数据。
        存储路径:
            数据存储在安装数据库实例配置的数据目录(即"datadir")下。
            对于MyISAM存储引擎而言: 表在对应数据库目录下以"表名.MYD"命名(如:"mysql数据库的user.MYD")。
            对于MySQL 8.0版本以前，InnoDB存储引擎而言: 表在对应数据库目录下以"表名.ibd"命名(如:"world数据库的city.ibd")。

    索引:
        含义:
            暂时可以先理解为一本书的目录，用于加快查询的。
        存储路径:
            数据存储在安装数据库实例配置的数据目录(即"datadir")下。
            对于MyISAM存储引擎而言: 表在对应数据库目录下以"表名.MYI"命名(如:"mysql数据库的user.MYI")。
            对于MySQL 8.0版本以前，InnoDB存储引擎而言: 表在对应数据库目录下以"表名.ibd"命名(如:"world数据库的city.ibd")。

    数据库状态:
        含义:
            也叫元数据信息信息，它存储了当前数据库实例的一些状态信息，用于运行MySQL实例本身的配置信息。
        存储路径:
            存储元数据信息的库都是MySQL内置的数据库，如:"information_schema，mysql，performance_schema，sys"

    权限:
        含义:
            顾名思义，就是存储MySQL用户的权限相关的表信息。
        存储路径:
            对应存储权限的表基本上都在mysql数据库中，比如:"columns_priv","db","tables_priv","user"等等都是跟权限有关的表哟~

    日志:
        含义:
            顾名思义，就是存储MySQL日志信息。
        存储路径:
            对应的有专门的日志文件，比如有用于存储MySQL错误日志的文件，也有对应二进制日志的文件，后面在主从复制会提及这些内容。

2 查询information_schema 数据库

##01 概述
  每次数据库启动，会自动在内存中生成"information_schema"数据库，该数据库用于生成查询MySQL部分元数据信息视图。换句话说，"information_schema"压根就不落地，它一直在内存中存储，因此我们去磁盘上是看不到对应的数据库目录的。
  
#02 查询所有字段信息
DESC information_schema.tables;

#03 常用的字段
TABLE_SCHEMA:		存储表所在的库。
TABLE_NAME:			存储表的名称。
ENGINE:				存储表的引擎。
TABLE_ROWS:			存储表的行数。
AVG_ROW_LENGTH:		平均行长度。
DATA_LENGTH:		表所占用的存储空间大小。
INDEX_LENGTH:		表的索引占用空间大小。
DATA_FREE:		1	表中是否有碎片。



#04 统计每个库，所有表的名称及个数
SELECT
	TABLE_SCHEMA '数据库名称',
	COUNT( TABLE_NAME ) '表个数',
	GROUP_CONCAT( TABLE_NAME ) '表名称'
FROM
	information_schema.TABLES 
GROUP BY
	TABLE_SCHEMA;

#05 统计每个库的占用空间总大小
SELECT
	TABLE_SCHEMA '数据库',
	SUM( AVG_ROW_LENGTH * TABLE_ROWS + INDEX_LENGTH ) / 1024 / 1024 '表占的大小MB'
FROM
	information_schema.TABLES 
GROUP BY
	TABLE_SCHEMA;

SELECT
	TABLE_SCHEMA,
	SUM( DATA_LENGTH ) / 1024 / 1024 
FROM
	information_schema.TABLES 
GROUP BY
	TABLE_SCHEMA;


#06 查询非系统数据库中(即"information_schema","mysql","performance_schema","sys"这四个数据库被排除在外)，即业务数据库中所包含的非InnoDB的表
mysql> SELECT
    ->     TABLE_SCHEMA,TABLE_NAME
    -> FROM
    ->     information_schema.tables
    -> WHERE
    ->     ENGINE != 'InnoDB' 
    -> AND 
    ->     TABLE_SCHEMA NOT IN ("information_schema","mysql","performance_schema","sys");

#07 查询业务数据库中所包含的非InnoDB的表转换为InnoDB
        mysql> SELECT
            ->     CONCAT("ALTER TABLE ", TABLE_SCHEMA, ".", TABLE_NAME, " ENGINE=InnoDB;")
            -> FROM
            ->     information_schema.tables
            -> WHERE
            ->     ENGINE != 'InnoDB' 
            -> AND 
            ->     TABLE_SCHEMA NOT IN ("information_schema","mysql","performance_schema","sys");

3 通过show查询元数据

#01 查看数据库信息
show databases ;

#02 查看当前数据库下的表信息
show tables;

#03 查看创建某个数据库时使用的SQL语句
show create database world\G
show create database world;

#04 查看创建某个表时使用的SQL语句
show create table t3\G
show create table t3

#05 查看某个数据库下的所有表
show tables from oldboyedu_movie ;

#06 查询所有用户的连接情况
SHOW PROCESSLIST ;
SHOW FULL PROCESSLIST;		详细显示

#07 查看字符集
SHOW CHARSET;

#08 查看校对规则
SHOW COLLATION;

#09 查看MySQL支持的存储引擎
SHOW ENGINES;

#10 查看MySQL支持的权限信息
SHOW PRIVILEGES;

#11 查看某个用户的权限信息
SELECT user,host FROM mysql.user;
SHOW GRANTS FOR 'root'@'localhost';


#12 查看MySQL实例的变量信息
SHOW VARIABLES;
SHOW VARIABLES LIKE '%trx%';  # 进行模糊查询;

#13 查看某张表的索引信息
SHOW INDEX FROM student;

#14 查询INNODB引擎状态
 SHOW ENGINE INNODB STATUS\G

#15 查看数据库状态信息
SHOW STATUS\G
SHOW STATUS LIKE '%Threads%';  # 模糊查询来查看某个变量

#16 查看所有数据库参数
SHOW VARIABLES ;
SHOW VARIABLES LIKE '%warning%'; # 模糊查询来过滤某个某些字段信息

#17 查看所有二进制日志文件
SHOW BINARY LOGS;

#18 查看二进制日志事件
SHOW BINLOG EVENTS;

#19 查询主库二进制的位置点信息
SHOW MASTER STATUS;

#20 看从库状态信息(
HOW SLAVE STATUS;

可能碰到的错误

1 忘记Mysql密码如何处理

#01 关闭数据库
[root@mysql107 ~]# systemctl stop mysqld.service 
[root@mysql107 ~]# service  mysqld stop


#02 启动数据库
[root@mysql107 ~]# mysqld_safe --help
[root@mysql107 ~]# mysqld_safe --skip-grant-tables --skip-networking &
[root@mysql107 ~]# service mysqld start --skip-grant-tables --skip-networking

--skip-grant-tables: 跳过连接池的授权表验证。
--skip-networking: 跳过连接池的TCP/IP连接MySQL功能，也就是只能本地通过socket套接字来连接MySQL数据库，否则其它用户可以远程访问服务器并基于其它你们用户来直接登录数据库(因为缺少验证功能)。

注意；需要指定mysql安装目录和数据目录
[root@mysql107 ~]# cat .my.cnf
[mysqld]
user=mysql
basedir=/oldboyedu/softwares/mysql57
datadir=/oldboyedu/data/mysql57

或者执行这个命令 service mysqld start --basedir=/oldboyedu/softwares/mysql57 --datadir=/oldboyedu/data/mysql57 --skip-grant-tables --skip-networking


#02 版本限制            
MySQL 8.0+可以不使用"--skip-networking"参数，但是MySQL 5.7及其以下版本请务必添加该属性，避免监听相应的端口。


#03 重置密码
FLUSH PRIVILEGES;												# 手动加载磁盘的授权表到内存中
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123'); 	# MySQL 5.6修改方式
ALTER USER 'root'@'localhost' IDENTIFIED BY 'oldboyedu';		# MySQL 5.7+ 修改方式	
		
#04 重启数据库到正常模式
[[email protected] ~]# service mysqld restart

2 错误2059 连接不上远程数据库

问题：msyql 8.0创建的用户 5.7和5.6远程无法登入

#01 查看库结构
desc mysql.user;

#02 原因：因为插件版本不一样
Mysql 5.7及以下版本使用的插件：mysql_native_password
Mysql 8.0：caching_sha2_password 

#03 解决问题
方案1）
按照插件 caching_sha2_password 相关的插件支持即可 
方案2）
修改用户的认证策略为"mysql_native_password"
ALTER USER admin IDENTIFIED WITH  mysql_native_password  BY '123'
select user,host,plugin from mysql.user;		# 检查plugin库
+------------------+---------------------+-----------------------+
| user             | host                | plugin                |
+------------------+---------------------+-----------------------+
| admin            | %                   | mysql_native_password |
| mysql.infoschema | localhost           | caching_sha2_password |
| mysql.session    | localhost           | caching_sha2_password |
| mysql.sys        | localhost           | caching_sha2_password |
| root             | localhost           | caching_sha2_password |
| oldboy           | mysql107.oldboy.com | caching_sha2_password |
+------------------+---------------------+-----------------------+

六.函数

#01 聚合函数
? Aggregate Functions and Modifiers 

#01 查询各组的价格平均值
SELECT AVG(price) AS '衣服的平均价格'FROM clothing ;


GROUP BY 	  #结合聚合函数，根据一个或多个列对结果集进行分组
COUNT(*) 	  #统计行数
#聚合函数
? Aggregate Functions and Modifiers 

MIN(字段)			   		最小值
MAX(字段)				  	最大值
AVG(字段)			  		平均值
COUNT(*) 	  	   		  统计行数相加
ORDER BY(字段)ASC		    升序 默认就是升序
ORDER BY(字段)DESC		降序
LIMIT 3,2				  从第三行开始偏移 显示2行
HAVING					  GROUP BY 后面的二次过滤
sum(字段)					 总和
BETWEEN 50 and 10		  匹配 50 - 10 之间的数据

标签：name,改查,actor,user,Mysql,增删,works,id,SELECT
From： https://www.cnblogs.com/saas-open/p/17580218.html

Mysql 增删改查语言系列

Mysql 数据语言系列

一.数据定义语言 DDL

1 数据库规范

2 DDL 语言使用

2 创建视图

二. 数据操纵语言 DML

1 插入语法

2 更新语法

3 删除语法

4 伪删除

5 数据恢复

6 案例

三. 数据控制语言 DCL

1 概念

2 用户管理

3 权限管理

4 角色管理

四. 数据查询类语句 DQL

1 语法格式 SELECT

2 单表查询 SELECT FROM

3 单表查询 SELECT WHERE

4 单表查询函数使用

5 单库查询系列

6 多表合并

7 多表查询

1）内连接

2）外连接

五元数据

1 概述

2 查询information_schema 数据库

3 通过show查询元数据

可能碰到的错误

1 忘记Mysql密码如何处理

2 错误2059 连接不上远程数据库

六.函数

相关文章

赞助商

阅读排行

Mysql 增删改查语言系列

Mysql 数据语言系列

一.数据定义语言 DDL

1 数据库规范

2 DDL 语言使用

2 创建视图

二. 数据操纵语言 DML

1 插入语法

2 更新语法

3 删除语法

4 伪删除

5 数据恢复

6 案例

三. 数据控制语言 DCL

1 概念

2 用户管理

3 权限管理

4 角色管理

四. 数据查询类语句 DQL

1 语法格式 SELECT

2 单表查询 SELECT FROM

3 单表查询 SELECT WHERE

4 单表查询 函数使用

5 单库查询系列

6 多表合并

7 多表查询

1）内连接

2）外连接

五 元数据

1 概述

2 查询information_schema 数据库

3 通过show查询元数据

可能碰到的错误

1 忘记Mysql密码如何处理

2 错误2059 连接不上远程数据库

六.函数

相关文章

赞助商

阅读排行

4 单表查询函数使用

五元数据