SQL基础语法概括

一、SQL基础语句

1. DDL 操作数据库数据库、表、列

• 操作类

-- 查询所有数据库
show database;
-- 创建数据库
create database daName;
create database if not exists dbName;
-- 删除数据库
drop database daName;
drop database if exists dbName;
-- 使用数据库
select database(); #查询当前使用的数据库
use dbName; #使用数据库
-- 查询数据库表
show tables; #查询当前数据库下所有表
desc daName; #查询表结构

• 创建表

create table tb_users (
    id int,
    username varcahr(20),
    password varchar(32)
);
# 注意：最后一个字段不加逗号！

2. DML 操作表中数据-增删改查

-- 给指定列添加数据
insert into tableName(name,age) values('小明',18);
-- 给全部列添加数据
insert into tableName values('小明',18,'广州',...);
-- 批量添加数据
insert into tableName(name,age) values('小明',18)，('小聪',19),...;
insert into tableName values('小明',18,'广州',...),('小聪',19,'广州',...),...;

3. DQL 对表中数据进行查询

• 基础查询 from

-- 查询多个字段
select name,age from dbName;
select * from dbName;
-- 去除重复记录
select distinct city from dbName;
-- 起别名
select city as 城市 from dbName;
select city 城市 from dbName；#可省略as

• 条件查询 where

-- 语法
select name from dbName where 条件列表;
-- 条件
> < >= <= = !=
between ... and ...
in(...) -- 多选一
like 占位符 -- _ 单个任意字符匹配；%多个任意字符匹配
is null
is not null
and 或 &&
or 或 ||
not 或 ！

• 排序查询 order by

-- 语法
select name from dbName order by 排序字段名1 [排序方式1],[排序方式2],[排序方式3];
-- 排序方式
ASC 默认，升序
DESC 降序

• 分组查询 group  by

-- 聚合函数 (null值不参与所有聚合函数运算)
count(列名) -- 统计数量，选不为null列
max(列名)
min(列名)
sum(列名)
avg(列名)
 
-- 聚合函数语法
select 聚合函数名(列名) from 表名;
 
-- 分组查询 （分组后，查询的字段为聚合函数和分组字段，查询其他无意义）
select sex, avg(age), stuNum from studentTable [where 分组前条件限定] group by sex [having 分组后过滤];
 
-- 例子：查询男同学和女同学各自的数学平均分和各自人数，要求：分数低于70的不参与分组，分组后人数大于2
select sex, avg(math), count(*) from stu where math > 70 group by sex having count(*) > 2;
 
-- 执行顺序： where > 聚合函数 > having

• 分页查询 limit

-- 1.从0开始查询，查询3条数据
select * from stu limito，3;
 
-- 2.每页显示3条数据，查询第1页数据
select * from stu limito，3;
 
-- 3.每页显示3条数据，查询第2页数据
select * from stu limit 3，3;
 
--4.每页显示3条数据，查询第3页数据
select * from stu limit 6，3;
 
-- 公式：
起始索引=(当前页码-1)*每页显示的条数

4. 数据类型

5. 约束

非空NOT NULL、唯一UNIQUE、主键PRIMARY KEY、默认DEFAULT、检查CHECK、外键FOREIGN KEY

二、数据库设计—多表关系实现

1. 数据库关系

• 一对一
• 一对多
• 多对多

2. 一对多关系实现

• 实现方式：在“多”的一方建立外键，指向“一”的一方的主键

3. 多对多关系实现

• 实现方式：建立中间表，中间表至少包含两个外键，分别关联两方的主键

 三、多表查询

1. 连接查询

• 内连接

（1）隐式内连接

select 字段列表 from 表1，表2... where 条件;
select stu.name, stu.age, class.className from stu, class where stu.class_id = class.stu_id;
select t1.name, t1.age, t2.className from stu t1, class t2 where stu.class_id = class.stu_id; -- 起别名

（2）显示内连接

select 字段列表 from 表1 [inner] join 表2 on 条件;
select * from stu join class on stu.class_id = class.stu_id; -- inner可省略

• 外连接

（1）左外连接

select 字段列表 from 表1 left [outer] join 表2 on 条件;

（2）右外连接

select 字段列表 from 表1 right [outer] join 表2 on 条件;

2. 子查询

• 单行单列 （= != > <）

select 字段列表 from 表 where 字段名 = (子查询);
select * from stu where age >= (select age from stu where stu_id = 1);

• 多行单列 （in）

select 字段列表 from 表 where 字段名 in (子查询);
select * from stu where city_id in (select city_id from city where cityName = '广州' or '深圳');

• 多行多列 （虚拟表）

select 字段列表 from (子查询) where 条件;
select * from (select * from stu where birthday > '2000-01-01') reTableName, city where reTableName.city_id = city.id;

 四、事务

1. 事务四大特性

• 原子性(Atomicity): 事务是不可分割的最小操作单位，要么同时成功，要么同时失败
• 一致性(Consistency): 事务完成时，必须使所有的数据都保持一致状态
• 隔离性(lsolation): 多个事务之间，操作的可见性
• 持久性(Durability): 事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

2. 事务流程

-- 开启事务
start transaction;
--或者 begin;
...
...
-- 提交事务,mysql有默认自动提交功能，其他则需手动
commit;
...
...
-- 回滚事务
rollback;