MySQL ----- SQL语法

最新推荐文章于 2023-05-04 09:01:48 发布

滚去学习……

最新推荐文章于 2023-05-04 09:01:48 发布

阅读量291

点赞数

分类专栏：数据库文章标签：数据库 mysql

本文链接：https://blog.csdn.net/qq_45668041/article/details/119863898

版权

数据库专栏收录该内容

5 篇文章 0 订阅

订阅专栏

4. DDL

数据定义语言, 用于操作DB、TB、column等对象.

4.1 数据库

create | drop database DBname; 增删数据库
show databases; 显示所有数据库
use DBname; 切换数据库
status; 查看字符集,服务器,连接数等信息

4.2 表

create table TBname(列类型约束,...);
drop table TBname;
alter table TBname RENAME TO Newname; 表重命名
show tables [in DBname]; 查看指定数据库的所有表
show create table TBname \G; 查看表的完整创建语句(引擎,字符集), \G表示旋转90度纵向显示
desc TBname; 查看表的结构

4.3 列

可通过 first 或 after 来指定列的位置

alter table TBname ADD COLUMN Cname type; 增加列
alter table TBname DROP COLUMN Cname; 删除列
alter table TBname CHANGE COLUMN Cname Newname type; 重命名列
alter table TBname MODIFY COLUMN Cname type; 修改列的类型
change 和 modify 的区别: 前者还可修改字段名但必须写两次字段名

5. DML

数据操纵语句，用于操作表记录及其完整性.

5.1 增

INSERT INTO TBname(field,...) values(v1,...),(v2,...); 插入多条记录
可用 子查询 进行批量插入

5.2 删

DELETE from TBname [where condition]; 删除指定结果集

5.3 改

UPDATE TBname SET f1=v1,... [where condition]; 对指定结果集更新
可同时更改多个表格的数据: UPDATE T1,T2 set T1.f1=v1, T2.f1=v2 [where];

5.4 查

SELECT f1,... from TBname; 查询结果集的f1字段, * 表示所有字段
- 可以取别名显示: select name AS username ...
distinct

相同的记录只显示其一
- select DISTINCT name from user; 输出不重复的记录
where

首次对表格记录条件过滤
- select * from TBname where 条件; 通过条件过滤
- 条件中可用的运算符: > < >= <= != or and not等
limit

限制输出记录的位置和行数
- select * from TBname limit off_start,row_count; 限制输出记录的偏移位置(默认0)和行数
order by

控制输出记录的顺序
- select name,age from user ORDER BY age DESC, height ASC; 先对age降序排列, 在对height升序排列, 默认升序
group by

对记录按某字段进行分类
- select 聚合字段,聚合函数 from user GROUP BY 聚合字段 [WITH ROLLUP]; 分类聚合并汇总
- 常用聚合函数: sum() count(1) avg() max() min()
- select age, max(height) from user group by age 查询不同年龄中的最高身高
- select age, max(height) from user group by age with rollup 再做一次归并, 即所有年龄中的最大身高
- having: 对聚合后的结果再进行过滤
  - select age, max(height) as max from user group by age HAVING max>170; 过滤聚合后的结果集记录
union

多个结果集合并为一个结果集, 列数必须一样
- select age from user UNION select name from user; 默认给每个结果集distinct
- select age from user UNION ALL select name from user; 不去除重复记录
- mysql只支持并集运算, SQLserver还支持交差运算

join…on

对多个表进行整合输出显示, 如:

# infor
+--------+------+----------+--------+
| name   | age  | address  | height |
+--------+------+----------+--------+
| acvals |   21 | Jiangxi  |    180 |
| chen   |   18 | ji'an    |    175 |
| js     |   17 | chinese  |    100 |
| esma   |   15 | jiahua   |    170 |
| cjh    |   18 | nanchang |    175 |
+--------+------+----------+--------+
# user
+----------+---------+----------------------+
| username | userage | useremail            |
+----------+---------+----------------------+
| acvals   |      21 | 2625632146@qq.com    |
| cjh      |      18 | cjh262563214@163.com |
| chen     |      20 | chenacvals@gmail.com |
+----------+---------+----------------------+

内连接(默认)

先进行on条件判断(字段类型必须相同), 再进行笛卡尔积运算(右表拼接到左表记录后)
- select * from infor JOIN user ON infor.name = user.username;

+--------+------+----------+--------+----------+---------+----------------------+
| name   | age  | address  | height | username | userage | useremail            |
+--------+------+----------+--------+----------+---------+----------------------+
| acvals |   21 | Jiangxi  |    180 | acvals   |      21 | 2625632146@qq.com    |
| chen   |   18 | ji'an    |    175 | cjh      |      18 | cjh262563214@163.com |
| cjh    |   18 | nanchang |    175 | cjh      |      18 | cjh262563214@163.com |
+--------+------+----------+--------+----------+---------+----------------------+

外连接

用于两个表, 部分不匹配的记录也保留
左外连接, 保留左表全部记录, 右表不匹配的记录填充null值
- select * from infor LEFT JOIN user ON infor.name = user.username;

+--------+------+----------+--------+----------+---------+----------------------+
| name   | age  | address  | height | username | userage | useremail            |
+--------+------+----------+--------+----------+---------+----------------------+
| acvals |   21 | Jiangxi  |    180 | acvals   |      21 | 2625632146@qq.com    |
| chen   |   18 | ji'an    |    175 | cjh      |      18 | cjh262563214@163.com |
| cjh    |   18 | nanchang |    175 | cjh      |      18 | cjh262563214@163.com |
| js     |   17 | chinese  |    100 | NULL     |    NULL | NULL                 |
| esma   |   15 | jiahua   |    170 | NULL     |    NULL | NULL                 |
+--------+------+----------+--------+----------+---------+----------------------+

右外连接, 保留右表全部记录, 左表不匹配的记录填充null
- select * from infor RIGHT JOIN user ON infor.name = user.username;

+--------+------+----------+--------+----------+---------+----------------------+
| name   | age  | address  | height | username | userage | useremail            |
+--------+------+----------+--------+----------+---------+----------------------+
| acvals |   21 | Jiangxi  |    180 | acvals   |      21 | 2625632146@qq.com    |
| chen   |   18 | ji'an    |    175 | cjh      |      18 | cjh262563214@163.com |
| cjh    |   18 | nanchang |    175 | cjh      |      18 | cjh262563214@163.com |
| NULL   | NULL | NULL     |   NULL | chen     |      20 | chenacvals@gmail.com |
+--------+------+----------+--------+----------+---------+----------------------+

全连接

mysql不支持全连接(Oracle,SQLserver支持full join), 但可以通过union左右外连接来实现

mysql> select * from infor left join user on infor.age = user.userage
    -> union
    -> select * from infor right join user on infor.age = user.userage;
+--------+------+----------+--------+----------+---------+----------------------+
| name   | age  | address  | height | username | userage | useremail            |
+--------+------+----------+--------+----------+---------+----------------------+
| acvals |   21 | Jiangxi  |    180 | acvals   |      21 | 2625632146@qq.com    |
| chen   |   18 | ji'an    |    175 | cjh      |      18 | cjh262563214@163.com |
| cjh    |   18 | nanchang |    175 | cjh      |      18 | cjh262563214@163.com |
| js     |   17 | chinese  |    100 | NULL     |    NULL | NULL                 |
| esma   |   15 | jiahua   |    170 | NULL     |    NULL | NULL                 |
| NULL   | NULL | NULL     |   NULL | chen     |      20 | chenacvals@gmail.com |
+--------+------+----------+--------+----------+---------+----------------------+

交叉连接

select * from infor ACROSS JOIN user; 所有infor*user组合的所有记录

子查询

一个select语句中嵌入另一个select/insert语句, 子查询返回单个值则可用: > = < 等, 返回多个值则可用集合比较运算符: EXISTS IN等
- 无关子查询, 子查询和父查询无关
  
  先执行子查询, 结果返回给父查询使用,查询infor中与cjh的同龄人信息:
  
  select * from infor where age = (select userage from user where username="cjh");
- 相关子查询, 子查询的where引用了父查询的表
  
  父查询每执行一次, 立刻传数据子给查询执行, 子查询结果和父查询结果匹配则返回, 直到父查询结束.
  - select * from infor as item1 where height >= (select avg(height) from user as item2 where item1.age = item2.userage);
  - 查询infor中与user中身高身高的人: select * from infor as item1 where EXISTS (select userage from user as item2 where item1.height=item2.height);
- 批量数据插入
  - insert into infor(age, height) (select userage,height from user where userage>=20);

6. DCL

滚去学习……

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL ----- SQL语法

4. DDL数据定义语言, 用于操作DB、TB、column等对象.4.1 数据库create | drop database DBname; 增删数据库show databases; 显示所有数据库use DBname; 切换数据库status; 查看字符集,服务器,连接数等信息4.2 表create table TBname(列类型约束,...);drop table TBname;alter table TBname RENAME TO Newname; 表重命名sho
复制链接

扫一扫