MySQL操作指令及查询语句

MySQL操作指令以及数据查询

一、操作表

1.数据定义语言（DDL）

（1）创建表

create table 表名(
    列名   列数据类型,
    .......

);

（2）给表中添加列

alter table 表名 add (列名 数据类型);

（3）修改列的数据类型

-- 修改列的数据类型
ALTER TABLE body MODIFY address CHAR(11);

（4）修改列名

-- 修改列名
ALTER TABLE body CHANGE address location VARCHAR(100);

（5）删除列

-- 删除列
ALTER TABLE body drop location;

（6）修改表名

-- 修改表名
ALTER TABLE body RENAME TO bd;

2.数据操作语言（DML）

（1）插入语句：向表中插入记录

insert into 表名(列1,列2,....) values(值1,值2,...)

注意：创建表格时设置主键自增

（2）修改表的记录

-- 修改表的记录，带条件的修改
UPDATE stu SET ssex = '女' WHERE sid =1005;

--修改多列数据
UPDATE stu SET ssex = '女' ,sage = 48 WHERE sid =1004;

（3）删除表格的记录

--删除一条记录
DELETE FROM stu WHERE sid=1005;

--删除表中所有的数据，但是表的结构还在
DELETE from stu;

3.数据查询语言（DQL）

（1）简单查询

select 列名1,列名2,...  from 表名;

--若要查询所有的列，使用*来查询
--查询所有列
SELECT * FROM stu;

展示效果：

（2）条件查询：

select 列1,列2,... from 表名  where 条件;

条件运算符:

• =、!=、<>、<、<=、>、>=；
• BETWEEN…AND；
• IN(set)；
• IS NULL；
• AND；
• OR；
• NOT；

-- 查询性别为女，并且年龄小于50的记录
SELECT * from stu where gender='female' and age<50;

-- 查询学号为S_1001，或者姓名为liSi的记录
SELECT * FROM stu WHERE sid='S_1001' OR sname = 'liSi'; 

-- 查询学号为S_1001,S_1002,S_1003的记录
SELECT * FROM stu WHERE sid in ('S_1001','S_1002','S_1003');

-- 查询学号不是S_1001,S_1002,S_1003的记录
SELECT * FROM stu WHERE sid NOT IN ('S_1001','S_1002','S_1003');

-- 查询年龄为空的记录
SELECT * from stu where age is null;

-- 查询年龄在20到40之间的记录
select * from stu WHERE age BETWEEN 20 and 40;
SELECT * FROM stu where age >=20 and age <=40;

（3）模糊查询：

select 列 from 表名  where 列  like 条件;

‘%’：表示0个或多个字符

‘_’：任意单个字符

示例：

-- 查询姓小的记录
select * from stu where sname like '小%';

-- 查询姓名中包含‘红’的记录
select * FROM stu where sname like '%红%';

-- 查询姓名是两个字并且最后一个字是'白'的记录
select * from stu where sname like '_白';

-- 查询姓名中第二个字母为‘i’的学生记录
select * from stu where sname like '_i%';

（4）字段查询

示例：

-- 查询emp表中的sal字段并去除重复记录
SELECT DISTINCT sal from emp;

-- 查看雇员的月薪与佣金之和
select *,sal+comm from emp;

-- 查询雇员的月薪与佣金之和，将佣金的NULL转换为0
select *,sal+IFNULL(comm,0) from emp;

-- 给列添加别名
select *,sal+IFNULL(comm,0) as '总工资' from emp;

（5）排序：对查询结果进行排序，关键字是order by;

select 列 from 表名 [where 条件] order by 列名  asc/desc;

示例：

-- 查询所有的学生记录，按年龄升序（降序）排序
SELECT * from stu ORDER BY age ASC;

-- 查询所有性别为男的学生记录，按年龄降序排序
select * from stu where gender = 'male' ORDER BY age desc;

-- 查询所有雇员，按月新降序排序，如果月薪相同时，按编号升序排序
SELECT * from emp ORDER BY sal desc, empno asc;

（6）聚合函数的使用

• count() ：统计指定列不为NULL的记录

-- 统计stu表中记录数
select count(*) as '总记录数' from stu;

-- 统计emp表中有佣金的记录数
select count(comm) as '有佣金' from emp;

-- 查询薪水大于等于2500的员工人数
select count(sal) from emp where sal >=2500;

• max/min ：查询列的最大值/最小值

-- 查询最高的薪水
SELECT MAX(sal) as '最高薪水' from emp;

• sum(列名)：求和使用

-- 查询所有员工的薪水之和
SELECT sum(sal) as '薪水总和' from emp;

• avg(列名)：求平均值

-- 求所有员工的平均工资
select avg(sal) as '平均工资' from emp;

（7）分组查询：使用 group by 列名

-- 查询每个部门的部门编号和每个部门的工资总和
select deptno as '部门编号',sum(sal) as '工资总和' from emp GROUP BY deptno;

-- 查询每个部门的部门编号以及每个部门的部门人数
SELECT deptno as '部门编号' ,count(*) as '部门人数' from emp GROUP BY deptno;

-- 查询每个部门的部门编号以及每个部门工资大于1500的人数
SELECT deptno as '部门编号',count(*) as '人数'from emp where sal>1500 GROUP BY deptno;

（8）having字句：

where与having的区别：

​ WHERE是对分组前记录的条件，如果某行记录没有满足WHERE子句的条件，那么这行记录不会参加分组。

​ HAVING是对分组后数据的进行筛选，必须和group by结合使用，不能单独使用。

-- 查询工资总和大于9000的部门编号以及工资和
SELECT deptno ,sum(sal) from emp GROUP BY deptno HAVING sum(sal)>9000;

（9）limit的使用：用来限定查询结果的起始行和查询行数。（MySQL独有的语句）

select 列 from 表名 [where 条件] limit 起始行索引,查询的行数

二、多表连接查询

1. 内连接

内连接仅选出两张表中互相匹配的记录

-- staff表和dept表的内连接
SELECT staff.name,deptno.deptname from staff,deptno where staff.name = deptno.name;

2.外连接：既包括两张表匹配的记录，也包括不匹配的记录

**左连接：**包含左边表中所有的记录，右边表中没有匹配的记录显示为NULL。

-- staff和deptno进行左连接
select staff.name ,deptno.deptname from staff left join deptno on staff.name = deptno.name;

右连接：包含右边表中所有的记录，左边表中没有匹配的记录显示为NULL。

-- staff和deptno进行右连接
SELECT staff.name,deptno.deptname from staff right join deptno on staff.name = deptno.name;

3.笛卡尔积：笛卡尔积，又叫cross join，是SQL中两表连接的一种方式。 假如A表中的数据为m行，B表中的数据有n行，那么A和B做笛卡尔积，结果为m*n行

三、子查询

是多表连接查询的一种实现方式。在一个select语句的from字句或者where字句中嵌套了另一个select语句。外层的select语句是主查询where或者select中的语句是子查询。

1.where字句中的子查询：子查询返回的值作为主查询的查询条件

--查询部门编号不在部门表中的员工记录
select * from emp where deptno not in (select deptno from dept);

--查询部门编号不在部门表中的员工记录
select * from emp where deptno  in (select deptno from dept);

-- 查询部门编号小于5，并且工资大于1500的员工记录
select * from emp where deptno<(SELECT deptno from dept where dname = 'mark') and sal>1500;

2.from字句中的子查询：子查询返回的是一张临时的表。主查询从该临时表中查询出满足条件的记录

示例：

-- 在部门表与员工表的内连接中查询部门表中的部门编号，部门名称，部门地址
SELECT d.deptno,d.dname,d.loc 
from dept d INNER JOIN(
SELECT deptno,count(empno) as '员工数',avg(sal) as '平均工资' from emp GROUP BY deptno
) employee;

四、表与表之间的关系（实体之间的关系）

1.一对一（1:1）：一个身份证对应一个人。只需要在表中添加一个字段即可。

2.一对多（1:n）：部门与员工之间的关系（一个部门有多个员工）。设计表格时”一“端不动，”多“端添加一个字段用于关联”一“端，两张表形成主外键的关联。

3.多对多（m:n）：学生与课程之间的关系（一个学生要学习多门课程，一门课程被多个学生学习）。设计时会生成第三张表，将多对多的关系转化为一对多的关系。