mysql数据库语句知识点使用总结

1.order by使用

      order by 子句默认使用升序。要说明排序顺序，我们可以用desc表示降序，或者用asc表示升序

      例如：按salary的降序列出整个instructor关系，如果有几位教师的工资相同，就将他们按姓名升序排列

select *
from instructor
order by salary desc,name asc;

 

2.sum(),count()函数的使用

 

2.1 sum函数的使用

（1）sum()函数里面的参数是列名的时候，是计算列名的值的相加，而不是有值项的总数。
（2）sum(条件表达式)，如果记录满足条件表达式就加1，统计满足条件的行数

 

2.2 count函数的使用

（1）COUNT()函数里面的参数是列名的的时候,那么会计算有值项的次数。（NULL 不计入， 但是''值计入）
（2）COUNT(*)可以计算出行数，包括null
（3）COUNT（1）也可以计算出行数，1在这里代表一行
（4）COUNT(column)对特定的列的值具有的行数进行计算，不包含NULL值
（5）COUNT(条件表达式)，不管记录是否满足条件表达式，只要非NULL就加1

例如：有一个student表，里面有下面的数据

问题：查询两门以及两门以上不及格者的平均成绩

  查询条件

SELECT `name`,
AVG(score) FROM student GROUP BY `name`
HAVING SUM(score < 60) >= 2

 

2.聚合函数、group by和having子句

聚合函数一般有sum(),count(),avg()等，使用聚合函数时，必须有一个分组标志字段，例如group by分组；过滤分组采用having子句完成。

group by 子句也可以和where条件语句结合在一起使用。当结合在一起时，where在前，group by 在后。即先对select xx from xx的记录集合用where进行筛选，然后再使用group by 对筛选后的结果进行分组 ，使用having字句对分组后的结果进行筛选。

3.注意having和where用法的区别

  having只能用在group by之后，对分组后的结果进行筛选(即使用having的前4102提条件是分组)。

  having子句必须于group by 子句同时使用，不能单独使用，且是先group by XXX 再having XXX，用group by不一有having（它只是一个筛选条件用的）

  where肯定在group by 之前。

  where后的条件表达式里不允许使用聚合函数，而having可以。

4.当一个查询语句同时出现了where,group by,having,order by的时候，执行顺序和编写顺序是：

执行where xx对全表数据做筛选，返回第1个结果集。

针对第1个结果集使用group by分组，返回第2个结果集。

针对第2个结果集中的每1组数据执行select xx，有几组就执行几次，返回第3个结果集。

针对第3个结集执行having xx进行筛选，返回第4个结果集。

针对第4个结果集排序。

5.sql语句中，通过charindex如果能够找到对应的字符串，则返回该字符串位置，否则返回0，在数据库中，字符串的索引从1开始，字符串在1位置则返回1而不是0；

例如：查询显示雇员的姓名和姓名中是否含有字母A的信息

select ename, case  charindex(‘A‘,ename)
when 1 then ‘字符A在首位‘
when len(ename) then
‘字符A在末位‘
when 0 then ‘没有字符A‘
else ‘字符A在中间‘
end 名称类别   from emp;

6.数据库修改语句

change 用来修改字段名字以及类型

modify 用来修改字段类型

alter column ... set  用来修改字段数据

例如：修改表test_tbl字段a的缺省值为1000，可以使用SQL语句

ALTER TABLE test_tbl ALTER a SET DEFAULT 1000;

7.左连接 ，右连接，内连接和全外连接的4者区别

left join （左连接）：返回包括左表中的所有记录和右表中连接字段相等的记录。

right join （右连接）：返回包括右表中的所有记录和左表中连接字段相等的记录。

inner join （等值连接或者叫内连接）：只返回两个表中连接字段相等的行。

full join （全外连接）：返回左右表中所有的记录和左右表中连接字段相等的记录。

例如有两张表A表，B表

　A表　　　　　　　　　　
　　id　 name　　
　　1　　小王
　　2　　小李
　　3　　小刘
　B表
　　id　　a_id　　job
　　1　　  2　　　老师
　　2　　  4　　　程序员

 

内连接：（只有2张表匹配的行才能显示）

select a.name,b.job from A a inner join B b on a.id=b.A_id
　　只能得到一条记录
　　小李　　老师

左连接：（左边的表不加限制）

select a.name,b.job from A a  left join B b on a.id=b.A_id
 
　　三条记录
 
　　小王　　null
 
　　小李　　老师
 
　　小刘　　null

右连接：（右边的表不加限制）

select a.name,b.job from A a  right join B b on a.id=b.A_id
 
　　两条记录
 
　　小李　　老师
 
　　null　　程序员

全外连接：(左右2张表都不加限制）

select a.name,b.job from A a  full join B b on a.id=b.A_id
 
　　四条数据
 
　　小王　　null
 
　　小李　　老师
 
　　小刘　　null
 
　　null　　程序员

注：当子表元素大于1时，不能用=,要用in

例如,用in查询职业为老师的人姓名

select name from A where id in (select a_id from B where job = '老师')

8.case when的使用

例如：比赛结果result表内容如下：

Date                        Win
2020-05-12               胜 
2020-05-12               负 
2020-05-15               胜 
2020-05-15               负

如果要生成下列结果, 写出语句
比赛日期            胜     负
2020-05-12          1      1
2020-05-15          1      1

select Date As 比赛日期, 
SUM(case when Win='胜' then 1 else 0 end) 胜, 
SUM(case when Win='负' then 1 else 0 end) 负 
from result 
group by Date

 

9.mysql索引

一、mysql目前主要有以下几种索引

1.普通索引

2.唯一索引

3.主键索引

4.组合索引

5.全文索引

二、语句

CREATE TABLE table_name[col_name data type]
[unique|fulltext][index|key][index_name](col_name[length])[asc|desc]

1.unique|fulltext为可选参数，分别表示唯一索引、全文索引
2.index和key为同义词，两者作用相同，用来指定创建索引
3.col_name为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择
4.index_name指定索引的名称，为可选参数，如果不指定，默认col_name为索引值
5.length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度
6.asc或desc指定升序或降序的索引值存储

三、索引类型

1.普通索引
是最基本的索引，它没有任何限制。它有以下几种创建方式：
（1）直接创建索引

CREATE INDEX index_name ON table(column(length))

（2）修改表结构的方式添加索引

ALTER TABLE table_name ADD INDEX index_name ON (column(length))

（3）创建表的时候同时创建索引

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    INDEX index_name (title(length))
)

（4）删除索引

DROP INDEX index_name ON table

2.唯一索引
与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：
（1）创建唯一索引

CREATE UNIQUE INDEX indexName ON table(column(length))

（2）修改表结构

ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

（3）创建表的时候直接指定

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    UNIQUE indexName (title(length))
);

3.主键索引
是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引：

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) NOT NULL ,
    PRIMARY KEY (`id`)
);

4.组合索引
指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合

ALTER TABLE `table` ADD INDEX name_city_age (name,city,age); 

5.全文索引
主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。
（1）创建表的适合添加全文索引

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    FULLTEXT (content)
);

（2）修改表结构添加全文索引

ALTER TABLE article ADD FULLTEXT index_content(content)

（3）直接创建索引

CREATE FULLTEXT INDEX index_content ON article(content)

四、缺点

1.虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update和delete。因为更新表时，不仅要保存数据，还要保存一下索引文件。
2.建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会增长很快。
索引只是提高效率的一个因素，如果有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

五、注意事项

使用索引时，有以下一些技巧和注意事项：
1.索引不会包含有null值的列
只要列中包含有null值都将不会被包含在索引中，复合索引中只要有一列含有null值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为null。
2.使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个char(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
3.索引列排序
查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
4.like语句操作
一般情况下不推荐使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
5.不要在列上进行运算
这将导致索引失效而进行全表扫描，例如

SELECT * FROM table_name WHERE YEAR(column_name)<2017;

6.不使用not in和<>操作

10.mysql优化

一、sql语句优化

（1）使用limit对查询结果的记录进行限定
（2）避免select *，将需要查找的字段列出来
（3）使用连接（join）来代替子查询
（4）拆分大的delete或insert语句

二、选择合适的数据类型

（1）使用可存下数据的最小的数据类型，整型 < date,time < char,varchar < blob
（2）使用简单的数据类型，整型比字符处理开销更小，因为字符串的比较更复杂。如，int类型存储时间类型，bigint类型转ip函数
（3）使用合理的字段属性长度，固定长度的表会更快。使用enum、char而不是varchar
（4）尽可能使用not null定义字段
（5）尽量少用text，非用不可最好分表

三、选择合适的索引列

（1）查询频繁的列，在where，group by，order by，on从句中出现的列
（2）where条件中<，<=，=，>，>=，between，in，以及like 字符串+通配符（%）出现的列
（3）长度小的列，索引字段越小越好，因为数据库的存储单位是页，一页中能存下的数据越多越好
（4）离散度大（不同的值多）的列，放在联合索引前面。查看离散度，通过统计不同的列值来实现，count越大，离散程度越高：

mysql> SELECT COUNT(DISTINCT column_name) FROM table_name;

11 .操作数据库

操作数据库 ---->  操作数据库中的表  ---->  操作数据库中表的数据

MySQL不区分大小写

11.1 操作数据库

1.创建数据库

CREATE DATABASE IF NOT EXISTS mo

2.删除数据库

DROP DATABASE IF EXISTS mo

3.使用数据库

use 'mo'

4.查看数据库

show databases;

11.2 数据库的列类型

数值

• tinyint 十分小的数据 1个字节
• smallint 较小的数据 2个字节
• mediumint 中等大小 3个字节
• int 标准的整数 4个字节（常用）
• bigint 较大的数据 8个字节
• float 浮点数 4个字节
• double 浮点数 8个字节 （精度问题）
• decimal 字符串形式的浮点数,金融计算的时候，一般用

字符串

• char 字符串固定大小 0-255
• varchar 可变字符串 0-65535（常用）
• tinytext 微型文本 2^8-1
• text 文本串 2^16-1 (保存大文本)

时间日期

java.util.Date

• date YYYY-MM-DD，日期
• time HH:mm:ss 时间格式
• datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式
• timestamp 时间戳 1970.1.1到现在的毫秒数
• year 年份表示

null

• 没有值，未知
• 注意，不要使用null进行运算，结果为null

11.3 数据库的字段类型

unsigened:

• 无符号的整数

• 声明该列不能声明负数

zerofill:

• 0填充的
• 10的长度 1 – 0000000001 不足位数用0 填充

自增：

• 通常理解为自增，自动在上一条记录的基础上+1
• 通常用来设计唯一的主键 index,必须是整数类似
• 可以自定义设置主键自增的起始值和步长

非空 NULL not Null

• 假设设置为 not null，如何不给他赋值，就会报错

• NULL 如果不填写，默认为NULL

默认：

• 设置默认的值！

 

11.4 数据表的类型

-- 关于数据库引擎
/*
INNODB 默认使用
MYISAM 早些年使用


*/

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间的大小	较小	较大，约为MYISAM的两倍

常规使用操作：

• MYISAM 节约空间，速度较快，
• INNODB 安全性高，事务处理，多表多用户操作

在物理空间存在的位置

所有的数据库文件都存在data目录下，一个文件夹就对应一个数据库

本质还是文件的存储

MySQL 引擎在物理文件上的区别

• innoDB 在数据库表中，只有一个*.frm文件，以及上级目录下的ibdata1文件
• MYISAM 对应的文件
  • *.frm - 表结构的定义文件
  • *. MYD -数据文件
  • *.MYI 索引文件

设置数据库字符集编码

CHARTSET=UTF8

不设置的话，会是mysql默认的字符集编码-（不支持中文）

可以在my.ini中配置默认的编码

character-set-server=utf8

11.5 将一张表的查询结果关联一张虚拟表，把结果插入到另一张表中

INSERT INTO [boss_door_record] 
([record_id], [person_no], [remark], [cross_time], [picture_file], [record_type], [event_type], [card_type], [card_no], [person_name], [mobile])
select newId() record_id, u.suserid person_no, '人脸识别开门' remark, attend.datetime cross_time,  null picture_file, '1' record_type, 104 event_type, 169 card_type, '' card_no, u.username person_name, u.phone mobile
from boss_information_gathering info
join (select 'AM' as [option], '2021-03-08 08:55:00' datetime
union all select 'AM' as [option], '2021-03-08 12:05:00' datetime
union all select 'PM' as [option], '2021-03-08 12:55:00' datetime
union all select 'PM' as [option], '2021-03-08 18:05:00' datetime) attend on info.[OPTION] = attend.[OPTION]
join pcmc_user u on info.usercode = u.usercode
where info.[option] is not null

11.6 update关联另外的表语句

UPDATE p 
SET p.ss_date = isnull( ( SELECT report_date FROM pm_paperdoc d WHERE p.contract_cd = d.contract_code AND report_type = 'YS' AND grading = 'F' ), p.ss_date ),
p.ss_date_end = DATEADD(
	DAY,
	payments_duration,
	isnull( ( SELECT report_date FROM pm_paperdoc d WHERE p.contract_cd = d.contract_code AND report_type = 'YS' AND grading = 'F' ), p.ss_date_end ) 
) 
from cnt_payments p
WHERE
	p.is_zz_date = '1' 
	AND p.contract_cd = 'slsz-sc-19-443-028'