Sql语言（mysql为例）全解

一. SQL语言基本介绍

1.sql是什么

结构化查询语言，关系型数据库通用操作语言

2.能做什么

查询，操纵，定义，控制 关系型数据库

二.Sql语言运用

1.操作库

a.增 >创建数据库

CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [, create_specification] …]
其中：
create_specification:
[DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name
解释:创建数据库可以指定字符集和校对规则

b.删 > 删除数据库

DROP DATABASE [IF EXISTS] db_name

c.改> 修改数据库

ALTER DATABASE db_name [alter_specification [, alter_specification] …]
其中：
alter_specification: [DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name
解释：修改数据库的字符集或者校对规则

d.查> 查看数据库

1.显示数据库
SHOW DATABASES
2.显示数据库建表语句
SHOW CREATE DATABASE db_name

选择库

use db_name

2.操作表

a.增 >创建数据表

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,…)]
[table_options] [select_statement]
其中：

create_definition：

col_name type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT ‘string’] [reference_definition]
| [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,…)
| KEY [index_name] [index_type] (index_col_name,…)
| INDEX [index_name] [index_type] (index_col_name,…)
| [CONSTRAINT [symbol]] UNIQUE [INDEX]
[index_name] [index_type] (index_col_name,…)
| [FULLTEXT|SPATIAL] [INDEX] [index_name] (index_col_name,…)
| [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name,…) [reference_definition]
| CHECK (expr)
重复以上多行
table_options：
常用：
[DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]
解释： col_name type 名称跟类型 后还可跟约束 跟默认值 还可以指定比如联合主键 外键 索引等
如：

设置uid与tid同时为主键

CREATE TABLE applicant(
  uid INT NOT NULL,
  tid INT NOT NULL,
  PRIMARY KEY(uid,tid)
)

b.删 > 删除数据表

drop table tablename

c.改> 修改数据表

增加列

alter table tabname add (column datatype [default expr][, column datatype]…);

修改列

alter table tabname add (column datatype [default expr][, column datatype]…);

删除列

alter table tabname drop (column)

修改表名

alter table old_tabname to new_tabname

修改列名称

alter table tabname change [column] old_col_name new_col_name datatype

修改列顺序

alter table tabname modify col_name1 datatype after col_name2

修改表字符集

alter table tabname character set character_name

等等很多 具体看手册

d.查> 查看数据表

查看表结构

desc tabName

查看所有表

show tables

查看当前表建表语句

show create table tabName;

3.操作表记录

a.增 >插入表记录

insert into tabname [(column [,column…])] values (value [, value…]);
运用：
1.可以 省略全字段 然后顺次插入全字段数据值
2.可以指定几个列插入数据 注意not null的列必须要指定 注意按顺序对应插值
3.可以 插入多条 以逗号分割
注意：字符串与日期格式的数据要用单引号引起来

b.删 > 删除表记录

delete from tab_name [WHERE where_definition] （记录在日志中）
若不适用where字句将删除所有数据

或者摧毁重建（效率高）(但是不记录日志)
truncate table employee;

c.改> 修改表记录

UPDATE tab_name SET col_name1=expr1 [, col_name2=expr2 …] [WHERE where_definition]

d.查> 查询表记录

单表查询

1.基本查询
SELECT [DISTINCT] *|{column1, column2. column3…} FROM table;
2.where子句查询
Select *|列名 from tablename [WHERE where_definition]
在where_definition中可以用

• in
  in=（=any）；
• some(any)
  只要有一条数据满足条件，整个条件成立
• all
  对所有数据都满足条件，整个条件才成立

!!注意一般是某个属性与一列范围比较 多列会出现以下错误：

    Query : select * from scores where degree in (SELECT * from scores)
    Error Code : 1241
    Operand should contain 1 column(s)

3.查询后排序
SELECT column1, column2. column3… FROM table order by column asc|desc;
4.聚合函数查询
列名之前可以加：
count，sum，avg，max，min
5.分组查询
SELECT column1, column2. column3… FROM table group by column having …（having后通常跟聚合函数）
6.分组之后筛选
… group by having…

注意点！：
0：每个分组默认只显示第一条记录
select * from tablename group by col_name;
1：having后跟聚合函数
2：注意以下sql语句

错
SELECT * FROM scores GROUP BY cno HAVING degree<AVG(degree);
现在求平均数求的是分组后各自的平均数

错
SELECT * FROM scores GROUP BY cno HAVING degree=min(degree);
现在求平均数求的是分组后各组最低的分数

对（对每个学科（超过两个人考试的学科） 求平均分）
SELECT cno,avg（degree） FROM scores GROUP BY cno HAVING count(*)>2;
现在求数量求的是分组后各自分组的数量而不是总数量

分组顺序在having之前 having只能用于限制 满足条件的分组的显示 并且默认只显示 每个分组的第一条 如果 用having用来判断各组满足条件的记录 （如果默认显示的第一条记录满足条件 那么还会显示 如果 默认的第一条记录不满足 这一组的记录不会显示）
3.多字段分组
多个字段全部相同的分为一组

多表联合查询

1.多表设计（3种关系及外键设计）
一对一
一对多
多堆多
2.多表查询

https://blog.csdn.net/just_tigris/article/details/53616198

• 笛卡尔积查询 共n*m条数据
  select * from dept join emp；
• 内连接查询
  两边都有的对应数据的连接
  select * from dept inner join emp on dept.id = emp.dept_id;
• 外连接查询
  (1)左外连接查询:在内连接的基础上增加上左边表有而有边表没有的记录
  select * from dept left join emp on dept.id = emp.dept_id;
  (2)右外连接查询:在内连接的基础上增加上右边表有而左边表没有的记录
  select * from dept right join emp on dept.id = emp.dept_id;
  (3)全外连接查询:在内连接的基础上增加左边表有而右边表没有的记录和右边表有而左边表没有的记录

（mysql不支持全外连接）可以用（select * from dept left join emp on dept.id = emp.dept_id
union select * from dept right join emp on dept.id = emp.dept_id;模拟）

三.Sql注意点

几个难点

分组后排序？

分组后显示前几条记录？

https://blog.csdn.net/u011575570/article/details/49953495

1.sql语言几个关键字

1.like 模糊查询
2.limit 限制显示数据量
用法：limit [OFFSET],N
N是取多少条，OFFSET是偏移量，数据下标从0开始
取前15条记录：
select * from tablename order by orderfield desc/asc limit 0,15
3.between and 和 or（between and 包头 包尾）
4.if null（english,0） 选择english列 如果 值为null 则显示为0
5.union与union all

https://blog.csdn.net/yuxin6866/article/details/52190772

union all 记录重复不会去重

union 会去重

6.各种mysql内置函数
如：YEAR（） 日期取年份
NOW（）

2.！！！sql查询语句的执行顺序

(1)  FROM <left_table>
(2)  ON <join_condition>
(3)  <join_type> JOIN <right_table>
(4)  WHERE <where_condition>
(5)  GROUP BY <group_by_list>
(6)  HAVING <having_condition>
(7)  SELECT 
(8)  DISTINCT <select_list>
(9)  ORDER BY <order_by_condition>
(10) LIMIT <limit_number>

带着关键词的执行顺序就可以理解了，sql中的列别名为什么可以在order by中使用，而不能在where条件和group by中使用了

3.参考手册中sql语法的阅读

在语法介绍中，方括号（“[”和“]”）用于指明可选字或子句。例如，在下面的语句中，IF EXISTS是可选的：

DROP TABLE [IF EXISTS] tbl_name
当某一语法成分由多个可选项组成时，可选项应用竖线“|”分开。当可能选择一组选择中的某一成员时，可选项将列在方括号（“[”和“]”）中。

TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str)
当必须选择一组选择中的某一成员时，可选项将列在大括号（“{”和“}”）中。

{DESCRIBE | DESC} tbl_name [col_name | wild]
省略号（…）表明省略了语句的某一选择，通常是为了提供复杂语法的简短表述。例如，INSERT … SELECT是后跟SLECT语句的INSERT语句的简短形式。

省略号还能指明语句的前部分语法元素可重复。在下面的示例中，可给定多个reset_option值，第1个值后每一个可由逗号分开：

RESET reset_option [,reset_option] …

4.Mysql具体数据类型

a.字符串类型

1,char
2.varchar

b.数值类型

1.整型

• tinyint
• smallint
• int
• bigint

2.浮点型

• float
• double

c.大数据类型

• BLOB（大二进制 最大4GB 可以存 图片 音频 视频 ）
• Text（Mysql 其他数据库是 CLOB）

d.逻辑型

1.BIT

e.日期型

1.DATE 2018-6-16
2.TIME 19:19:19
3.DATETIME 2018-6-16 19:19:19（ 没有默认值 8字节）
4.TIMESTAMP 2018-6-16 19:19:19(可以有默认值 自动更新为当前时间 4字节)

5.字段约束

a.主键约束

https://blog.csdn.net/hunter_2016/article/details/71106676

b.唯一性约束

可以为null 且可以多次插入null 因为mysql中null！=null

c.非空约束

d.外键约束（a表主键在b表上 b表这一列称为b表的外键）

唯一标识其他表中的一条记录，用来通知数据库两张表字段之间的对应关系, 并让数据库帮我们维护这样关系的键就叫做外键

1.作用
确保数据库数据的完整性和一致性
2.添加外键

• 建表时添加
• foreign key(ordersid) references orders(id)
• 修改添加
• alter table book add [constraint FK_BOOK] foreign key(pubid)
  references pub_com(id) [on delete restrict] [on update restrict];

3.删除外键
alter table 表名 drop foreign key 外键名
4.外键取值规则：空值或参照的主键值。

(1)插入非空值时，如果主键表中没有这个值，则不能插入。

(2)更新时，不能改为主键表中没有的值。

(3)删除主键表记录时，你可以在建外键时选定外键记录一起级联删除还是拒绝删除。

(4)更新主键记录时，同样有级联更新和拒绝执行的选择。

6.mysql乱码解决

a.查看字符集

show variables like ‘character’;

b.临时修改

set names 某字符集

c.持久修改：

修改 mysql/my.ini， 将default-character-set=gbk，修改服务器默认认为客户端的字符集编码

四.sql练习题

https://blog.csdn.net/qq_38061534/article/details/86628141

注意：笔试题中 多表查询时 默认满足数据规范（如外键约束）

五.sql优化

https://www.cnblogs.com/yunfeifei/p/3850440.html