MySQL基础示例知识

MySQL数据类型与表操作

一、MySQL的基本认知

1、查看所有数据库

show databases;

2、看当前登录用户转为使用的库：

show database(),user(),now(),version();

3、查看建立数据库的语句代码

show create database d1;

Show create database mysql;

4、将数据库的字符集修改

alter database t character set utf8mb4;

二、创建数据库

1、创建数据库没有指定编码格式

create database d1;

指定编码格式

create database d2 default character set utf8;

如果不存在的数据库则建立数据库，不报错

create database if not exists ‘d3’charset utf8;

三、删除数据库

1、删除数据库

Drop database d1;

2、数据库不存在不报错

Drop database if exists ‘d2’;

Drop database d3;

3、drop、truncate、delete的区别

①drop(删除表)：删除内容和定义，删除整个表，

②truncate(清空表中数据)：删除内容、释放空间但不删除定义(保留表的数据结构)，只是清空表数据，不能删除行数据，要删就把表清空。

③delete(删除表中数据)：删除表中的行，【truncate与不带where的delete：之删除数据，不删除表结构】

四、查看字符集信息

1、查看支持MySQL的所有字符集

Show charset；

2、查看当前MySQL开发环境的相关字符集信息

Show variables like ‘char%’;

MySQL之外键、表关系及SQL查询关键字

一、外键 foreign key

多张表之间数据没有关系时，可以使用外键来给表与表之间添加关系

二、表与表之间建立关系

一对多：外键字段建在多的一方

多对多：需要单独开设第三张表存储两张表的关联数据(第三张表也可以不绑定关系)

一对一(或者表之间没有关系)：外键字段建在任何一方都可，但建议建在查询率较高的表中，

三、外键约束

1、在创建表的时候，需要先创建被关联表(没有外键字段的表)

2、在插入新数据时，应该先确保被关联表中已存在数据

3、在插入新数据时，外键字段只能填写被关联表中已存在的数据

4、在修改和删除被关联表中的数据时，无法直接操作，如果想要数据之间自动修改和删除，需要添加级联更新(on update cascade)或者级联删除（on delete cascade）。

四、操作关键字(增删改)

修改表名

      ALTER TABLE 表名 RENAME 新表名;

增加字段

      ALTER TABLE 表名 ADD 字段名  数据类型 [约束条件…],...;

      ALTER TABLE 表名 ADD 字段名  数据类型 [约束条件…]  FIRST;

      ALTER TABLE 表名 ADD 字段名  数据类型 [约束条件…]  AFTER 字段名;                      

删除字段

      ALTER TABLE 表名 DROP 字段名;

修改字段 

ALTER TABLE 表名 MODIFY  字段名 数据类型 [约束条件…];

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 数据类型 [约束条件…];

Modify只能改字段数据类型和约束条件，不能改字段名，但是change可以

五、查询关键字

1、Select与from：select控制的是查询表里面的哪些字段，from控制的是查询哪张表。

Select * from 表名；//查询所有字段

Select 字段1,字段2 from 表名；//查询指定字段

2、where(后面跟查询的条件)

示例：

1.查询id大于等于3小于等于6的数据

select * from emp where id >= 3 and id <= 6;

select * from emp where id between 3 and 6;

2.查询薪资是20000或者18000或者17000的数据

select * from emp where salary = 20000 or salary = 18000 or salary = 17000;

select * from emp where salary in (20000,18000,17000);

3.查询员工姓名中包含o字母的员工姓名与其薪资

select name,salary from emp where name like '%o%';

4.查询员工姓名是由四个字符组成的员工姓名与其薪资

select name,salary from emp where name like '____';//单引号中间为四个字符

select name,salary from emp where char_length(name) = 4;

5.查询id小于3或者大于6的数据

select * from emp where id not between 3 and 6;  //取反

6.查询薪资不在20000，18000，17000范围的数据

select * from emp where salary not in (20000,18000,17000);

7.查询岗位描述为空的员工名与岗位名（针对null不能用等号，只能用is）

select name,department_comment from emp where department_comment = NULL;  //不报错，但查询结果为空！

select name,department_comment from emp where department_comment is NULL;

模糊查询：没有明确的筛选条件

3、like

‘%’：匹配任意个数任意字符

‘_’：匹配单个任意字符

关于通配符匹配里的 % 相当于正则表达式里的 .* 表示匹配大于等于0个任意字符，所以  % 太 % 匹配的是 [大于等于0个任意字符]太[大于等于0个任意字符]，[东皇]太[一] 和 []太[乙真人]都符合；

而 _% 相当于正则表达式里的 .+ 表示匹配至少一个，即大于等于1个，所以  '_% 太 % 匹配的是 [大于等于1个字符]太[大于等于0个字符]，只有 [东皇]太[一] 符合。

示例：show variables like ‘%mode%’;筛选出含有mode字符的结果

4、group by(按照某个指定的条件将单个单个的个体分成一个个整体)

分组之后默认只能获取到分组的依据，其他数据不能直接获取，通常与聚合函数一起使用。

MySQL5.6版本针对group by需要设置sql_mode：only_full_group_by

常见的聚合函数：

Max//最大   Min//最小   Sum//求和   Count//计数   Avg//平均

示例：

Select * from emp group by department;//按部门分组

Select id,name,sex from emp group by department;//验证

获取每个部门的最高工资

以组为单位统计组内数据>>>>聚合查询(聚集到一起合成为一个结果)

select department,max(salary) from emp group by department;

字段取别名：

select department as ‘部门',max(salary) as ‘最高工资' from emp group by department;

4.1获取分组以外的数据字段，使用group_concat()

每个部门的员工姓名

select department,group_concat(name) from emp group by department;

每个部门的员工姓名,性别（支持自定义连接符）

select department,group_concat(name,'|',sex) from emp group by department;

4.2 concat：未分组时使用

select concat(name,sex) from emp;

select concat(name,'|',sex) from emp;

5、having过滤

Where与having区别：where是在分组之前对数据进行筛选；having是在分组之后对数据进行筛选。

示例：

筛选出员工年龄在30岁以上，且平均薪资在10000以上的部门

select department,avg(salary) from emp where age>30 group by department having avg(salary)>10000;

6、distinct去重

对有重复的展示数据进行去重操作 一定要是重复的数据

select distinct department from emp;//去重单个字段

select distinct id,age from emp;// 如去重多个字段必须满足去重的字段数据都重复才能去重，否则不去重

7、order by排序

select * from emp order by salary asc;//也可不写asc，默认升序排

select * from emp order by salary desc;//降序排

先按照age降序排，在年龄相同的情况下再按照薪资升序排

select * from emp order by age desc,salary asc;

统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行降序排序

select department,avg(salary) from emp where age>10 group by department having avg(salary)>1000 order by avg(salary) desc;

8、limit分页

限制展示条数

select * from emp limit 3;

查询工资最高的人的详细信息

select * from emp order by salary desc limit 1;

分页显示

select * from emp limit 0,5;//第一个参数表示起始位置，第二个参数表示的是条数，不是索引位置

select * from emp limit 5,5;

9、regexp正则

select * from emp where name regexp '^j.*(n|y)$';

10、exists存在

exists关键字表示是否存在，在使用时，内层查询语句不返回查询的记录，而是返回一个真假值，true或false：当返回true时，外层查询语句将进行查询；当返回false时，外层查询语句不进行查询。

示例：

select id,name from emp where exists (select id from emp where id > 3);//有结果

select id,name from emp where exists (select id from emp where id > 200);//内层查询无结果，所以外层查询语句不执行

MySQL之多表查询

一、多表查询

MySQL的注释方法：【#】注释 【–-】注释

查询思路：

子查询（将SQL语句的结果当作另外一条SQL语句的查询条件即分布操作）

连表操作(通过连接将需要使用的表拼接成一张大表，之后基于单表查询完成)

1、inner join：内连接，查询结果与表的顺序无关

1.1、cross join：交叉连接(笛卡尔积、交叉积、没有连接 no join)会把第一张表的每个值与第二张的每个值进行匹配，交叉连接是内连接的一种，可以把内连接看作是通过查询中的条件筛选掉某些结果数据行之后的交叉连接

1.2、相等连接：eg：boys.toy_id = toys.toy_id

1.3、不等连接：eg：boys.toy_id <> toys.toy_id

1.4、自然连接：只有在连接的列在两张表中的名称都相同时才会有用，自然连接就是自动识别相同列的相等连接

2、out join：外连接，查询与表的顺序有关

2.1、left join：左连接

2.2、right join：右连接

3、union：全连接

select * from student  where class='95031' union (select * from student  where  ssex='女');

union前面是一个结果集，后面是一个结果集，将两个数据结构相同的结果集进行连接

多表查询时，避免表字段重复，需要在字段名的前面加上表名限制，即使用【表名.字段名】来加以区分

-- inner join:只拼接两张表中共有的部分(有对应关系)

select * from emp inner join dep on emp.dep_id = dep.id;

-- left join:以左表为基准展示所有的内容，没有的用NULL填充

select * from emp left join dep on emp.dep_id = dep.id;

-- right join:以右表为基准展示所有的内容，没有的用NULL填充

select * from emp right join dep on emp.dep_id = dep.id;

-- union:左右表所有的数据都在 没有的用NULL填充

select * from emp left join dep on emp.dep_id = dep.id

union

select * from emp right join dep on emp.dep_id = dep.id;

二、SQL注入

产生原因：由于特殊符号的组合会产生特殊的效果，从而避免常规的逻辑。

解决方式：使用execute方法

三、常用的SQL函数

1、算术函数

 示例：

Select abs(-2),结果为2；

Select mod(101,3),结果为2；

Select round(37.25,1),结果为37.3

2、字符串函数

示例：

 Select concat(‘abc’,123),运行结果为abc123；

 Select length(‘你好’)，运行结果为6；

 Select char_length(‘你好’)，运行结果为2；

 Select lower(‘ABC’),运行结果为abc；

 Select upper(‘abc’)，运行结果为ABC；

 Select replace(‘fabcd’,‘abc’,123),运行结果为f123d;

 Select substring(‘fabcd’,1,3),运行结果为fab;

3、日期函数

4、转换函数

示例：

select cast(123.123 as int);运行结果会报错。

select cast(123.123 as decimal(8,2))，运行结果为 123.12。

select coalesce(null,1,2)，运行结果为 1。

cast 函数在转换数据类型的时候，不会四舍五入，如果原数值有小数，那么转换为整数类型的时候就会报错。不过你可以指定转化的小数类型，在 MySQL 和 SQL Server 中，你可以用decimal(a,b)来指定，其中 a 代表整数部分和小数部分加起来最大的位数，b 代表小数位数，比如DECIMAL(8,2)代表的是精度为 8 位（整数加小数位数最多为 8 位），小数位数为 2 位的数据类型。所以select cast(123.123 as decimal (8,2))的转换结果为 123.12。