sql语句
-
SQL通用语法
- SQL语句可以单行或多行书写，以分号（“;”）结尾。
- SQL语句可以使用空格/缩进来增强语句的可读性。
- MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
- 注释：
- 单行注释：--注释内容 或 # 注释内容（MySQL特有）
- 多行注释：/*注释内容*/
-
SQL分类
| 分类 | 全称 | 说明 |
|---|---|---|
| DDL | Data Definition Language | 数据定义语言，用来定义数据库对象（数据库，表，字段） |
| DML | Data Manipulation Language | 数据操作语言，用来对数据库表中的数据进行增删改 |
| DQL | Data Query Language | 数据查询语言，用来查询数据库中表的记录 |
| DCL | Data Control Language | 数据控制语言，用来创建数据库用户、控制数据库的访问权限 |
-
DDL-Data Definition Language-数据定义语言-数据库操作
-
查询
查询所有数据库
SHOW DATABASES;查询当前数据库
SELECT DATABASE(); -
创建
CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则]; -
删除
DROP DATABASE[IF EXISTS]数据库名; -
使用
USE 数据库名;
-
-
DDL-表操作-查询
-
查询当前数据库所有表
SHOW TABLES; -
查询表结构
DESC 表名; -
查询指定表的建表语句
SHOW CREATE TABLE 表名; -
DDL-表操作-创建
-
DDL-表创建-修改
-
添加字段
ALTER TABLE 表名 ADD 字段名 类型（长度） [COMMENT 注释] [约束]; -
修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型（长度）; -
修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型（长度） [COMMENT 注释] [约束]; -
删除字段
ALTER TABLE 表名 DROP 字段名; -
修改表名
ALTER TABLE 表名 RENAME TO 新表名; -
删除表
DROP TABLE [IF EXISTS] 表名; -
删除指定表，并重新创建该表
TRUNCATE TABLE 表名;
-
-
DML-Data Manipulation Language-数据操作语言
-
添加数据
-
给指定字段添加数据
INSERT INTO 表名(字段名1，字段名2,...) VALUES(值1，值2，...); -
给全部字段添加数据
INSERT INTO 表名 VALUES(值1，值2，...); -
批量添加数据
INSERT INTO 表名(字段名1,字段名2,...) VALUES (值1,值2,...),(值1,值2,...),(值1,值2,...);INSERT INTO 表名 VALUES（）(值1,值2,...),(值1,值2,...),(值1,值2,...); -
注意：
- 插入数据时，指定的字段顺序需要与值得顺序是一一对应得。
- 字符串和日期型数据应该包含在引号中。
- 插入得数据大小，应该在字段的规定范围内
-
-
DML-修改数据
-
修改数据
UPDATE 表名 SET 字段名1 = 值1,字段名2 = 值2 , ...[WHERE 条件]； -
注意：
修改语句得条件可以有，也可以没有，如果没有，则会修改整张表的所有数据。
-
-
DML-删除数据
-
删除数据
DELETE FROM 表名 [WHERE 条件] -
注意：
- DELETE 语句的条件可有可无，如果没有条件，则会删除整张表的所有数据。
- DELETE 语句不能删除某一个字段的值(可以只用UPDATE)。
-
-
DQL -Data Query Language-数据查询语言
-
DQL-基本查询
-
查询多个字段
SELECT 字段1,字段2,字段3... FROM 表名；SELECT * FROM 表名; -
设置别名
SELECT 字段1 [AS 别名1],字段2 [AS 别名2]...FROM 表名； -
去除重复记录
SELECT DISTINCT 字段列表 FROM 表名; -
DQL-条件查询
-
语法
SELECT 字段列表 FROM 表名 WHERE 条件列表; -
条件
-
DQL-聚合函数
-
将一列数据作为一个整体，进行纵向计算
SELECT 聚合函数(字段列表) FROM 表名； -
注意：NULL值不参与所有聚合函数运算
-
DQL-分组查询
-
语法
SELECT 字段列表 FROM 表名 [WHERE 表名] GROUP BY 分组字段名 [HAVING 分组后过滤条件]; -
where和having区别
-
执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组;而having是分组之后对结果进行过滤
-
判断条件不同：where不能对聚合函数进行判断，而having可以
-
注意：
-
执行顺序：where > 聚合函数 > having
-
分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。
-
DQL-排序查询
-
语法
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2;ASC:升序（默认） DESC：降序
-
注意：
-
如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。
-
DQL-分页查询
-
语法
SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数; -
注意
-
起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数
-
分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT
-
如果查询的是第一页数据，起始索引可以忽略，直接简写为limit10。
-
-
练习
-
查询年龄为20,21,22,23岁的女性员工信息
select * from emp where gender = ’女‘ and age in(20,21,22,23); -
查询性别为 男，并且年龄在 20 - 40 岁（含）以内的姓名为三个字的员工。
select * from emp where gender = '男' and age between 20 and 40 and name like '___' -
统计员工表中，年龄小于60岁的，男性员工和女性员工人数
select gender, count(*) from emp where age < 60 group by gender; -
查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果按年龄升序排序，若相同按入职时间倒序排序
select name,age from emp where age <=35 order by age asc, entrydate desc; -
查询性别为男，且年龄在20-40岁（含）以内的前5个员工信息，对查询的结果按年龄升序排序，年龄相同按入职时间升序排序。
select * from emp where gender = '男' and age between 20 and 40 order by age asc,entrydate asc limit 5; -
DQL-执行顺序
-
DCL-Data Control Language-数据控制语言，用来管理数据库用户、控制数据库的访问权限。
-
DCL-管理用户
-
1 查询用户
USE mysql； SELECT * FROM user； -
2 创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码'； -
3 修改用户密码
ALTER USER '用户名’@‘主机名' IDENTIFIED WITH mysql_native_pawwword BY '新密码'； -
4 删除用户
DROP USER '用户名'@’主机名‘; -
注意：
-
主机名可以使用%统配。
-
这类SQL开发人员操作少，主要是DBA（Database Administrator 数据库管理员）使用。
-
-
DCL-权限控制
-
1.查询控制
SHOW GRANTS FOR '用户名'@’主机名‘; -
2 授权权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@’主机名‘; -
3 撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@’主机名‘; -
注意：
-
多个权限之间，使用逗号分隔
-
授权时，数据库名和表名可以用*进行通配，代表所有。
！！！！！！
！！！！！！
！！！！！！
-
多表查询
-
连接查询-内连接
-
内连接查询语法（查询两个表交集）
- 隐式内连接
SELECT 字段列表 FROM 表1，表2 WHERE 条件...；- 显式内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件...； -
外连接查询语法（查询交集和中间的部分）
- 左外连接
SELECT 字段列表 FROM 表1 LERT [OUTER] JOIN 表2 ON 条件...;- 右外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件...; -
自连接（可以是内连接查询，也可以是外连接查询）
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...; -
联合查询--union，union all(把多次查询的结果合并起来，形成一个新的查询结果集)
SELECT 字段列表 FROM 表A... UNION [ALL] #去掉ALL可以去重 SELECT 字段列表 FROM 表B...;- 注意：
- 联合查询的多张表的列数必须保持一致，字段列表也需要保持一致。
- union all会将全部的数据直接合并在一起，union会对合并后的数据去重。
-
子查询--SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。
SELECT * FROM T1 WHERE column1 = (SELECT column1 FROM T2);子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个。
-
标量子查询(子查询返回的结果是单个值（数字、字符串、日期等）,最简单的形式)
-
常见的操作符：= <> > >= < <=
-
列子查询(子查询返回的结果是一列(可以是多行))
-
常见的操作符：IN 、NOT IN 、 ANY 、 SOME 、 ALL
-
行子查询(子查询返回的结果是一行(可以是多列))
-
常见的操作符：=、<> 、 IN 、 NOT IN
-
表子查询(子查询返回的结果是多行多列)
-
常见的操作符：IN
-
！！！！！！
！！！！！！
！！！！！！
-
练习
-
1、查询员工的姓名、年龄、职位、部门信息（隐式内连接）（表：emp，dept）
select e.name , e.age , e.job , d.name from emp e , dept d where e.dept_id = d.id;
- 2、查询年龄小于30岁的员工的姓名、年龄、职位、部门信息（显示内连接）（表：emp，dept）
select e.name , e.age , e.job , d.name from emp e inner join dept d on e.dept_id = d.id age < 30;
- 3、查询拥有员工的部门ID、部门名称
select distinct d.id ,d.name from emp e , dept d where e.dept_id = d.id;
- 4、查询所有年龄大于40岁的员工，及其归属的部门名称；如果员工没有分配部门，也需要展示出来
select e.*, d.name from emp e left join dept d on e.dept_id = d.id where e.age > 40;
- 5、查询所有员工的工资等级
select e.* ,s.grade from emp e,salgrade s where e.salary >= s.losal and e.salary <= s.hisal;
可用between and替换为
select e.* ,s.grade from emp e,salgrade s where e.salary between s.losal and s.hisal;
- 6、查询“研发部”所有员工信息及工资等级（表：emp , salgrade , dept）
select e.* , s.grade from emp e , dept d,salgrade s where e.dept_id = d.id and (e.salary between s.losal and s.hisal ) and d.name = '研发部';
- 7、查询“研发部”员工的平均薪资（表：emp , dept）
select avg(e.salary) from emp e ,dept d where e.dept_id = d.id and d.name = '研发部'；
- 8、查询工资比"灭绝"高的员工信息
select * from emp where salary > (select salary from emp where name = '灭绝');
- 9、查询比平均薪资高的员工信息
select * from emp where salary > (select avg(salary) from emp);
- 10、查询低于本部门平均工资的员工信息
select * from emp e2 where e2.salary < (select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept._id);
- 11、查询所有的部门信息，并统计部门的员工人数
select d.id , d.name , (select count(*) from emp where dept_id = 1) '人数' from dept d;
- 12、查询所有学生的选课情况，展示除学生名称，学号，课程名称
select s.name , s.no ,c.name from student s, student_course sc , course c where s.id = sc.studentid and sc.courseid = c.di;
浙公网安备 33010602011771号