mysql内连接部门平均值_MySQL

数据库MySQL

SQL语句

databaseSQL语句说明create database 数据库名;创建数据库

show databases;查看所有数据库

show create database 数据库名;查看某个数据库

drop database 数据库名;删除某个数据库

use 数据库名;使用某个数据库

select database();查看当前正在使用的数据库

tableSQL说明create table 表名(

字段名1 字段类型 字段属性,

字段名2 字段类型 字段属性

.. );建表

show tables;查看所有表

desc 表名;查看某个表

show create table 表名;查看某个表(更详细)

drop table 表名;删除某个表

alter table 表名 add 列名 字段类型 字段属性修改表-添加字段

alter table 表名 modify 列名 字段类型 字段属性修改表-修改某个字段

alter table 表名 change 旧列名 新列名 字段类型 字段属性修改表-修改某个字段

alter table 表名 drop 列名;修改表-删除字段

rename table 表名 to 新表名修改表名

alter table 表名 character set 字符集修改表字符集

##增删改查

###增SQL说明INSERT INTO 表名 (列名 1,列名 2,列名 3…) VALUES(值 1,值 2,值 3…)表中插入一条数据

insert into 表 values (值 1,值 2,值 3…)表中插入一条数据

insert into 表 values (值 1,值 2,值 3…),(值1，值2，值3)…表中插入多条数据

INSERT INTO 表名(字段列表) SELECT 字段列表 FROM 表名w从一个表取出数据插入到另一个表

###改SQL说明UPDATE 表名 SET 字段名=值,字段名=值 …修改所有数据

UPDATE 表名 SET 字段名=值,字段名=值 … WHERE 条件修改满足条件的数据

###删SQL说明DELETE FROM 表名删除所有数据

DELETE FROM 表名 WHERE 条件删除满足条件的数据

TRUNCATE 表名删除表中所有的数据，重置自增ID

###查

select语句1

2

3

4

5

6

7SELECT 字段 [AS] 别名 ....

FROM 表名 [AS] 别名

[WHERE 条件

GROUP BY 字段名

HAVING 过滤条件

ORDER BY 字段名 DESC|ASC

LIMIT 起始记录,记录数]

说明：SELECT :查询字段

SELECT * FROM 表1:查询表1中的所有数据

FROM：表名

WHERE：查询条件

SELECT * FROM 表1 WHERE 查询条件:查询表1中满足查询条件的数据

GROUP BY：根据字段分组

SELECT 分组数据 FROM 表1 GROUP BY 分组数据 将表1按照选择的分组数据进行分组

HAVING：过滤条件

ORDER BY：排序字段，DESC：降序，ASC：升序,rand()随机排序

SELECT * FROM 表1 ORDER BY 排序字段 DESC(ASC)将表1按照排序字段降序(升序)进行排序

LIMIT：限制数量

SELECT * FROM 表1 LIMIT num1,num2在表1中的下标为num1开始取num2个数据

AS：起别名

having和where的区别

区别：where 和 having 的区别？where：在聚合函数之前执行

having：在聚合函数之后执行。

去重

distinct对数据进行去重distinct要写在所有字段之前

取出来的字段都相同才算重复

SELECT DISTINCT genter FROM student

聚合函数

根据字段分组之后，我们就可以调用聚合函数对每个组中的记录进行数据统计

聚合函数：Max:取最大的值

min：取最小的值

sum:求和

avg：取平均值

count：计算记录数

说明：如果不分组，会对表中所有记录进行统计，如果分组就对每组中的记录进行统计。

表间关系

一对一

一张表中的一条记录对应另一张表中的一条记录

当一张表中字段非常多时，会影响表的查询速度，为了提高查询速度，我们可以把一张表拆分成两张表，常用的字段放到一张表，不用常用的字段放到另一张表中，这两张表中的记录就是一对一的关系。[垂直分表]

一对多

一个表中的数据对应另一个表中的多条记录

多对多

一张表中的一条记录在另外一张表中可以匹配到多条记录，反过来也一样。

联合查询

将多个查询使用union和union all将结果合并到一起，合并时字段数量不变union：合并时去重

union all合并时不去重

连接查询

将多张表连到一起进行查询

内连接

内连接：inner join从一张表中取出所有的记录去和另外一张表中匹配，能够匹配就保留，匹配不了就删除

语法：表1 inner join 表2 on 匹配条件

SELECT a.* b.* FROM mother a INNER JOIN children b on(where) a.id=b.mother_id

外连接

外连接分为两种，左连接和右连接左连接：左表为主表

右连接，右表为主表

内连接和外连接的区别：当一条记录关联不到另一表时，内连接中关联不上就取不出来，外连接中主表中的数据都能取出来

语法：表1 LEFT JOIN 表2 ON 匹配条件 表1为主表

​ 表1 RIGHT JOIN 表2 ON 匹配条件 表2为主表

交叉连接

自然连接

子查询

子查询的位置

位置where 子查询：子查询出现在 where 的位置上，用子查询的结果做为条件

from 子查询：子查询出现在 from 的位置上，用子查询的结果做为一张表

select 子查询：出现在 select 后面列的位置上，用子查询的结果做为一列

where子查询子查询返回一行一列时：可以使用 > 、>=、 、any、all 运算符。

子查询返回多行一列时：使用 in 、not in 运算符。

子查询返回一行多列时：使用 () = 运算符。

exists 和 not exists 子查询：子查询是否返回结果，有结果为真，没有结果为假。

from子查询

将一个子查询的结果当成一个表来查询

注意：在from子查询时需要为子查询起别名，否则会报错

select子查询

在 select 的字段上使用子查询

表引擎

InnoDB引擎事务：支持

锁的粒度：行级

外键：支持

故障恢复：支持

MyISAM引擎事务：不支持

锁的粒度：表级

外键：不支持

设置表引擎1

2

3

4CREATE TABLE 表名

{

字段....

}engine=表引擎

修改表引擎1alter table 表名 engine=引擎名;

数据的导入1mysql>source SQL文件路径

索引(数据库优化)

索引是提高数据库性能最有效的方法之一

BTREE和HASH索引

索引从它的实现原理来分，可以分为：BTREE 和 HASH 两种。HASH：性能虽高，但是只支持 =、IN、<> 查询，而且不能避免排序，不能避免全表扫描等问题。

BTREE：支持范围查询、可以避免排序、可以避免全表扫描等。

所以我们更加需要 BTREE 类型的索引，大多数引擎都支持 BTREE 索引，只有少数引擎支持 HASH 索引：

索引的类型索引类型说明主键索引 ( primary key )加快查询速度，索引字段的值不能重复，不能为空，一个表中只能有一个主键

唯一索引 ( unique )加快查询速度，索引字段的值不能重复

普通索引 ( indexkey )加快查询速度 ，没有其它功能，使用最多

全文索引 ( fulltext )对大文本进行 分词 索引，但是 MySQL 默认的全文索引对中文支持不好，所以基本不用，如果工作中就要对中文处理，使用第三方软件：Sphinx、lucence -> solr -> ElasticSearch(流行)

使用索引

索引是添加在表中的字段上的

索引会加快查询速度，但同时也会降低添加，修改，删除等写操作的速度，所以索引也有弊端，不要盲目使用索引。

一般会在以下字段创建索引：外键字段

where中的字段

order by中的字段

索引应该建在选择性高的字段上

索引操作指令说明show index from 表名查看索引

alter table 表名 add primary key (字段名)添加主键索引

alter table 表名 add unique 索引名称 (字段名)添加唯一索引

alter table 表名 add index 索引名称 (字段名)添加普通索引

drop index 索引名称 on 表名删除普通/唯一索引

alter table 表名 drop primary key删除主键索引

索引无效情况模糊查询以%开头时

or连接无索引字段时

分析一条SQL语句：在select前使用desc

desc select * from 表名

联合索引

联合索引：将多个字段上的索引合并成一个索引(联合索引比普通索引快)

分开索引1

2

3

4

5

6

7

8

9create table xxx(

...

a int unsigned not null,

b int unsigned not null,

c int unsigned not null,

key a(a),

key b(b),

key c(c)

);

联合索引1

2

3

4

5

6

7create table xxx(

...

a int unsigned not null,

b int unsigned not null,

c int unsigned not null,

key a_b_c(a,b,c)

);

联合索引的限制：创建联合索引时，字段的顺序非常重要，因为有最左边字段能使用上索引。比如：1

2

3

4

5

6SELECT * FROM xxx WHERE a=12;

SELECT * FROM xxx WHERE b=12; -- 索引不生效

SELECT * FROM xxx WHERE a=12 AND b=32;

SELECT * FROM xxx WHERE b=32 AND c=32; -- 索引不生效

SELECT * FROM xxx WHERE a=12 AND b=32 AND c=32;

SELECT * FROM xxx WHERE a=32 AND c=32;

前缀索引

对于大文本字段，我们可以使用前缀索引，这样可以避免整个文本索引，只需要为前几个字符建索引即可

添加前缀索引语法1ALTER TABLE table_name ADD KEY(column_name(prefix_length)); --prefix_length为限制的字符长度

系统函数

数学函数函数名称作用ABS求绝对值

SQRT求二次方根

CEIL和CEILING返回不小于参数的最小整数，

FLOOR向下取整

RAND随机数

SIGN返回参数的符号

POW和POWER所传参数的次方的结果值

字符串函数函数名称作用LENGTH计算字符串的长度，返回字符串的长度

CONCAT合并字符串函数，返回结果为连接参数产生的字符串，参数可以是一个或多个

INSERT替换字符串函数

LOWER将字符串中韩的字母转化为小写

UPPER将字符串中的字母转化为大写

LEFT从左侧字符截取，返回字符串左边的若干个字符

RIGHT从右侧字符截取，返回字符串右边的若干个字符

TRIM删除字符串左右两侧的空格

REPLACE字符串替换函数，返回替换后的新字符串

SUBSTRING截取字符串，返回从指定位置开始的指定长度的字符串

REVERSE字符串反转函数，返回和原始字符串相反的字符串

时间函数函数名称作用CURDATE和CURRENT_DATE返回当前系统的日期值

CURTIME和CURRENT_TIME返回当前系统的时间值

NOW和SYSDATE返回当前系统的日期和时间值

UNIX_TIMESTAMP获取UNIX时间戳为基础的无符号整数

FROM_UNIXTIME将UNIX时间戳转化为时间格式，与UNIX_TIMESTAMP互为反函数

MONTH获取指定日期中的月份

MONTHNAME获取指定日期中月份因问名称

DAYNAME获取指定对应星期几的英文名称

DAYOFWEEK获取指定日期对应的一周的索引位置值

WEEK获取指定日期的一年中的第几周，返回值的范围为0～52或者1～53

DAYOFYEAR获取指定日期是一年中的第几天，返回的范围是1～366

DAYOFMONTH获取指定日期是一个月中的第几天，返回值是1～31

YEAR获取年份，返回值：1970～2069

TIME_TO_SEC将时间参数转换为秒

SEC_TO_TIME将秒数转化为时间

ADDTIME时间加法运算，向日期添加指定时间间隔

SUBTIME时间减法运算，在原始时间上减去特定时间

DATEDIFF获取两个日期之间的间隔，返回参数1减去参数2的值

DATE_FORMAT格式化指定日期，根据参数返回指定格式的值

WEEKDAY获取指定日期在一周内的对应的工作日索引

聚合函数函数名称作用MAX查询指定列的最大值

MIN查询指定列的 最小值

COUNT统计查询结果的行数

SUM求和，返回指定列的总和

AVG求平均值 ，返回指定列数的平均值