【MYSQL】SQL基本操作

SQL基本操作

库操作(DDL)

对数据库的增删改查

基本语法

1. 创建数据库

Create database 数据库名[库选项];

库选项：用来约束数据库，分为两个选项

字符集设定：charset/character set 具体字符集.常用：GBK, UTF8

校对集设定：collate 具体校对集（数据比较的规则）（不用设置，与你设置的字符集自动匹配）

2. 查看数据库

查看所有数据库：show databases;

查看指定部分的数据库：模糊查询：show databases like ‘pattern’;

Pattern:匹配模式

%：匹配所有字符

_：匹配单个字符

如果数据库名中含‘_’，那么只用匹配时需要使用\_对下划线进行转义

查看数据库的创建语句：Show create database 数据库名字;

3. 更新数据库

数据库名字不可以修改

数据库的修改仅限库选项：字符集和校对集（校对集依赖字符集）

Alter database 数据库名字 charset/character set [=] 字符集;

4. 删除数据库

Drop database 数据库名;

注意：删除不可逆，谨慎操作！

表操作(DDL)

1. 新增表

普通方式：

create table [if not exists] 表名(

字段1 数据类型 [字段属性|约束] [索引] [注释],

……

字段n 数据类型 [字段属性|约束] [索引] [注释]

)[表类型] [字符集] [注释];

高级方式：

Create table 表名 like 数据库.表名;

-从已有表中复制，只复制结构，不复制数据

2.查看表

查看全部表：show tables;

模糊查找：show tables like ‘pattern’;

查看表创建语句：show create table 表名：

查看表结构：desc/describe/show columns from 表名;

3.修改表

    3.1 修改表本身

修改表名

A: rename table 旧表名to 新表名;

B: alter table 旧表名rename [to] 新表名;

修改表选项：

Alter table 表名表选项 [=] 值;

 

    3.2 修改字段

新增字段：alter table 表名 add [column] 字段名 数据类型 [列属性][位置];

位置：字段名可以存放表中的任意位置

First：第一个位置

After：在哪个字段之后：after 字段名;

修改字段：修改通常是修改属性或者数据类型

Alter table 表名 modify 字段名 数据类型 [类型][位置];

重命名字段：alter table 表名 change 旧字段 新字段名 数据类型[属性][位置];

删除字段：alter table 表名 drop 字段名;

4. 删除表

Drop table 表名1,表名2,…,表名n;

数据操作(DML)

1. 新增数据

全部字段插入数据

Insert into 表名values (值列表);

Insert into 表名 values (值列表1),(值列表2),…(值列表n);

CREATE TABLE 新表(SELECT 字段1，字段2....FROM 原表);

如果新表已存在，不能重复创建

非数值类型的值建议使用单引号包裹

部分字段插入数据

Insert into 表名(字段列表) values (),(),();

主键冲突：当主键存在冲突的时候（duplicate key），可以选择性的进行处理：更新和替换

Insert into 表名(字段列表) values (值列表) on duplicate key update 字段=新值;

Replace into 表名[(字段列表)] values (值列表);

蠕虫复制

定义：从已有的数据中去获取数据，然后将数据又进行新增操作：数据成倍的增加

语法：

Insert into 表名 select (字段列表) from 表名;

意义：

从已有表拷贝数据到新表

可以迅速的让表中的数据膨胀到一定的数据量级，测试表的压力及效率

 

2. 查看数据(DQL)

查看所有数据

基本语法

Select */字段列表 from 表名 [where 条件];

完整语法

select[select 选项] 字段列表[字段别名]/* from 数据源 [where条件子句] [group by子句] [having 子句] [order by子句][limit 子句];

select选项：select 对查出来的结果的处理方式

all：默认的，保留所有的结果

distinct：去重，将查出结果的重复项去除（所有字段都相同）

字段别名：

当数据进行查询出来的时候，有时候名字不一定就满足需求(多表查询的时候，会有同名字段)，需要对字段名进行重命名：别名

语法：字段名 [as] 别名;

数据源：

数据的来源，关系型数据库的来源都是数据表：本质上只要保证数据类似二维表最终都可以作为数据源

数据源的分类：多表数据源，查询语句

单表数据源：select * from 表名;

多表数据源：select * from 表名1,表名2...;

从一张表中取出一条记录，去另一张表总匹配所有记录，而且全部保留：（记录数和字段数），将这种结果称为：笛卡尔积（交叉连接），其并没有什么用处，所以避免使用

查询语句（子查询）：select * from (select语句) as 别名;

因为数据应该是一张表，所以给别名

where子句：

用来判断数据，筛选数据（在磁盘读取时就进行筛选了）

where子句的返回结果：0或者1,0代表false，1代表true

判断条件：

比较运算符：>,<,>=,<=,!=,<>,=,like ,between and ,in/not in ,is null/is not null

逻辑运算符：&&（and），||（or），!（not）

where原理：where是唯一一个直接从磁盘获取数据的时候就开始判断的条件：从磁盘取出一条记录，开始进行where判断，判断的结果如果处理，就保存到内存，如果失败就直接放弃

group by:分组

根据某个字段进行分组（相同的放一组，不同的分到不同的组）

基本语法：group by字段名 [asc||desc];

asc:字段升序排列；desc：字段降序排列

 

分组的意义是为了统计数据（按组统计：按分组字段进行数据统计）

SQL提供了一系列统计函数

Count():统计分组后的记录数，每一组有多少条记录

括号里面如果放字段的话（null不被统计）

Max():统计每组中的最大值

Min():统计最小值

Avg():统计平均值

Sum():统计和

多字段分组排序group by 字段1，字段2…;

对分组的结果的某个字段进行字符串链接的函数：group_concat(字段)

回溯统计：with rollup：任何一个分组后都会有一个小组，最后根据当前分组的字段向上级分组进行汇报统计，这就是回溯统计，回溯统计的时候会将分组字段置空

Having子句

与where子句一样，进行条件判断

区别：where是针对磁盘数据进行判断：进入到内存之后，会进行分组操作，分组结果需要使用having来处理

Having能做where能做的几乎所有事情，但是where却不能做having能做的很多事情

1. 分组统计的结果或者说统计函数都只有having能够使用
2. Having能够使用字段别名，where不能，别名是在内存中才有的

 

Order by子句

排序，根据某个字段进行升序或者降序排序，依赖校对集

基本语法：

Order by 字段名 [asc|desc]; -- asc:默认，升序；desc：降序

多字段排序

Limit子句

2种使用方式

1. 只用来限定长度（数据量）：limit 数据量;
2. 限制起始位置，限制数据量：limit 起始位置,数据量;

连接查询

定义：将多张表进行记录的连接（按照某个制定的条件进行数据拼接）；

最终结果是：记录数有可能变化，字段数一定会增加

连接查询的意义：在用户查看数据的时候，需要显示的数据来自多张表

连接查询：join，使用方式：左表 join 右表

左表：在join关键字左边的表

右表：在join关键字右表的表

连接查询分类

交叉连接

Cross join，从一张表中循环取出每条记录，每条记录都去另外一张表进行匹配，匹配一定保留，而连接本身字段会增加，最终形成的结果叫：笛卡尔积

基本语法：左表 cross join 右表; ===========from 左表,右表;

笛卡尔积没有意义，应该尽量避免

内连接

[inner] join：从左表中取出每一条记录，去右表中与所有的记录进行匹配，匹配必须是某个添加在左表中与右表中相同最终才会保留结果，否则不保留

基本语法：

左表 [inner] join 右表 on 左表.字段=右表.字段;

On表示连接条件：条件字段就是代表相同的业务含义

内连接还可以使用where代替on关键字（where没有on效率高）

注意：由于不同表的字段名可能相同，所以可以使用别名

字段别名以及表别名的使用：在查询数据的时候，不同表有同名字段，这个时候需要加上表名才能区分，而表名太长，通常可以使用别名

语法： 字段名/表名 [as] 别名

外连接

Outer join，以某张表为主，取出里面的所有记录，然后每条与另外一张表进行连接：不管能不能匹配上条件，最终都会保留

能匹配，正确保留，不能匹配，其他表的字段都置空NULL

 

外连接的分类，以哪张表为主，有主表

Left join：左外连接（左连接），以左表为主表

Right join：右外连接（右连接），以右表为主表

基本语法：左表 left/right join 右表 on 左表.字段 = 右表.字段;

自然连接

Natural join，就是自动匹配连接条件：系统以字段名作为匹配条件（同字段名就作为条件，如果多个字段同名，那么就都作为匹配条件）

语法：

自然内连接：左表 natural join 右表;

自然外连接： 左表 natural left/right join 右表;

联合查询

将多次查询（多条select语句），在记录上进行拼接（字段不会增加）

基本语法：

多条select语句构成，每一条select语句获取的字段数必须严格一致（但是字段类型无关）

Select语句：

Select语句1 union [union 选项] select 语句2 union ….union 语句n；

Union 选项：与select选项一样有两个

All：保留所有（不管重复）

Distinct（默认使用）：去重（整个重复），

联合查询的意义

1. 查询同一张表，但是需求不同，如查询学生信息，男生身高升序，女生身高降序
2. 多表查询：多张表的结构是完全一样的，保存的数据（结构）也是一样的

Order by 的使用

1. 联合查询中，order by不能直接使用，需要对查询语句使用括号
2. 搭配limit 使用，limit使用限定的最大数即可

子查询

分类：

按位置分类：子查询在外部查询中出现的位置

From子查询：子查询跟在from之后

Where子查询：子查询跟在where之后

询：子查询出现在exists里面

按结果分：根据子查询得到的数据进行分类

标量子查询：子查询得到的结果是一行一列

列子查询：子查询得到的结果是一列多行

行子查询：子查询得到的结果是多列一行（多行多列）

Select * from 表名 where (字段列表)=(select语句);

上面几个出现的位置都是在where之后

表子查询：子查询得到的结果是多行多列（出现的位置是在from之后）

Exists/not exists查询

此查询用于判断某些条件是否满足（跨表），exists是接在where之后，exists返回结果只有0和1

语法：select … from 表名 where exists(子查询);

1. 更新数据

Update 表名 set 字段 = 值 [where 条件]; -- 建议都有where：不然就是更新全部

 

2. 删除数据

删除是不可逆的，谨慎操作

Delete from 表名 [where 条件]

 

 

字符集问题

显示当前所有字符集：show character set;

查找当前使用的字符集：show variables like 'character_set%';

设置字符集：set names gbk;

修改数据库字符集 ：alter database 数据库名 default character set 'utf8';

– character_set_server：默认的内部操作字符集

– character_set_client：客户端来源数据使用的字符集

– character_set_connection：连接层字符集

– character_set_results：查询结果字符集

– character_set_database：当前选中数据库的默认字符集

– character_set_system：系统元数据(字段名等)字符集