SQL语法

SQL语法

SELECT、INSERT、UPDATE 和 DELETE

基本术语：

• 事务（transaction）指一组 SQL 语句；
• 回退（rollback）指撤销指定 SQL 语句的过程；
• 提交（commit）指将未存储的 SQL 语句结果写入数据库表；
• 保留点（savepoint）指事务处理中设置的临时占位符（placeholder），你可以对它发布回退（与回退整个事务处理不同）。

不能回退 SELECT 语句，回退 SELECT 语句也没意义；也不能回退 CREATE 和 DROP 语句。

MySQL 的事务提交默认是隐式提交，每执行一条语句就把这条语句当成一个事务然后进行提交。当出现 START TRANSACTION 语句时，会关闭隐式提交；当 COMMIT 或 ROLLBACK 语句执行后，事务会自动关闭，重新恢复隐式提交。

设置 autocommit 为 0 可以取消自动提交；autocommit 标记是针对每个连接而不是针对服务器的。

如果没有设置保留点，ROLLBACK 会回退到 START TRANSACTION 语句处；如果设置了保留点，并且在 ROLLBACK 中指定该保留点，则会回退到该保留点。

• 创建、修改、删除

## 注释
SELECT *
FROM mytable; -- 注释
/* 注释1
   注释2 */

CREATE DATABASE test;
USE test;

CREATE TABLE mytable (
  # int 类型，不为空，自增
  id INT NOT NULL AUTO_INCREMENT,
  # int 类型，不可为空，默认值为 1，不为空
  col1 INT NOT NULL DEFAULT 1,
  # 变长字符串类型，最长为 45 个字符，可以为空
  col2 VARCHAR(45) NULL,
  # 日期类型，可为空
  col3 DATE NULL,
  # 设置主键为 id
  PRIMARY KEY (`id`));
  
  ---------------------------修改
  alter table mytable add col char(20)
  alter table mytable drop column col
  drop table mytable

  ----------------------------插入
  insert into mytable(col1,col2) values(val1,val2)
  insert into mytable(col1,col2) select col1,col2 from mytable2
  create table newtable as select * from mytable

  ------------------------------更新
  update mytable set col = val where id = 1;
  
 --------------------------------删除
 #使用更新和删除操作时一定要用 WHERE 子句，不然会把整张表的数据都破坏。可以先用 SELECT 语句进行测试，防止错误删除
 	delete from mytable where id = 1
 	truncate table mytable

• 查询1

  #相同值只会出现一次，所有列的值都相同才相同
  select distinct col1,col2 from mytable 
  
  #限制返回的行数，limit 起始行，结束行
  select * from mytable limit 5;
  select * from mytable limit 2,3;
  select * from mytable limit 0,5;
  
  #排序asc desc
  select * from mytable order by col1 desc,col2 asc;
  
  #过滤{= < >} ，{<>!=(不等于)}，between ,is null
  select * from mytable where col is null;
  /*
  1.应该注意到，NULL 与 0、空字符串都不同。
  2.AND 和 OR 用于连接多个过滤条件。优先处理 AND，当一个过滤表达式涉及到多个 AND 和 OR 时，可以使用 () 来决定优先级，使得优先级关系更清晰。
  3.IN 操作符用于匹配一组值，其后也可以接一个 SELECT 子句，从而匹配子查询得到的一组值。
  4.NOT 操作符用于否定一个条件。*/
  
  #通配符 like [] % _,不要滥用会影响速度
  select * from mytable where col like '[^AB]%';
  
  #select as 计算字段也就是表达式时用取别名
  SELECT CONCAT(TRIM(col1), '(', TRIM(col2), ')') AS concat_col FROM mytable;
  
  #函数 avg()列的平均值，count()列的行数，max() min()列的最大值和最小值，sum()返回某列值之和
  from avg(distict col1) as avg_col from mytable
  

• 查询2

  #分组group by 把具有相同数据值的行放在同一组中，可对同一分组数据使用汇总函数如求分组数据的平均值；指定分组字段除了能按字段进行分组，也会自动按该字段进行排序
  #where - group by - oder by，where过滤行，having过滤分组，先过滤行再过滤分组
  #null行单独分为一组，
  select col,count(*) as num from mytable group by col;
  select col,count(*) as num from mytable where col > 2 group by col having num >=2;
  
  #略：子查询，可用连接代替
  - 子查询必须在圆括号中
  - 子查询的select子句只能油一列，除非主查询有多个列，用于与子查询选中的列相比较
  - 子查询不能用order by，可以用groupy by代替
  select * from mytable1 where col1 in(select col2 from mytable2);
  select cust_name , (select count(*) from orders where orders.cust_id = customers.cust_id) as oders_num from custommers order by cust_name;#查询客户的订单数量，子查询语句会对第一个查询检索出的每个客户执行一次
  

• 查询3 -连接，组合查询，视图

  -关于on、where、having的区别：on是最先执行，where次之，having最后。
  on是在生成中间的临时表时起作用的，在两个表联接时才用on，where，having是生成临时表之后对连接表再进行过滤所以在一个表的时候，就剩下where跟having比较

  ---inner/left/right join on(注意这里是on不要用where)
  1 内连接
  select A.value, B.value from tablea as A inner join tableb as B on A.key = B.key;#相当于select A.value, B.value from tablea as A , tableb as B where A.key = B.key
  2 自连接-内连接的一种，连接的表是自身
  如：一张员工表，包含员工姓名和员工所属部门，要找出与 Jim 处在同一部门的所有员工姓名。
  select e1.name from employee as e1 inner join employee as e2 on e1.department = e2.department and e2.name = 'jim';
  #相当于select name from employee where department = (select department from employee where name = 'jim');
  3外连接，右连接不常用，用left join 以左表为基准取并集从结果集查找，用having 代替where（注意这里on和having的区别
  
  -检索所有顾客的订单信息，包括还没有订单信息的顾客。
  select Customers.cust_id,Customer.cust_name, Orders.order_id from Customers left outer join orders on Customers.cust_id = orders.cust_id;
  
  ----组合查询
  用union组合两个查询，如果第一个查询返回 M 行，第二个查询返回 N 行，那么组合查询的结果一般为 M+N 行
  SELECT col FROM mytable WHERE col = 1
  UNION
  SELECT col FROM mytable WHERE col =2