注释
数据库创建与适用
创建表
修改表
插入
更新
删除
清空
查询
过滤
通配符
匹配不以A和B开头的任意文本
二、自测

注释

#
/**/
--

数据库创建与适用

create database test;
use test;

创建表

create table mytable(
    id INT NOT NULL AUTO_INCREMENT,
    col1 INT NOT NULL DEFAULT 1,
      col2 VARCHAR(45) NULL,
      col3 DATE NULL,
      PRIMARY KEY(`id`)
);

修改表

添加列

alter table mytable add newcol char(20);

删除列
```
alter table mytable drop column newcol;
```
删除表
```
drop table mytable;
```
插入

普通

insert into mytable(col1,col2) values(val1,val2);

插入检索数据

insert into mytable1(col1,col2) select col1,col2 from mytable2;

将检索内容插入一个新表

create table newtable as select * from mytable;

更新

update mytable
set col=val
where id=1;

删除

delete from mytable where id=1;

清空

truncate table mytable;

查询

distinct
- 作用于所有列
```
select distinct col1,col2 from mytable;
```
limit
- 起始行（从0开始）
- 总行数 ```sql select from mytable limit 5; select from mytable limit 0,5;

select * from mytable limit 2,3;

<a name="j5uEh"></a>
### <br />
<a name="TH3sA"></a>
### 排序
- asc
- desc
```sql
select * from mytable 
order by col1 desc,col2 asc;

过滤

=, <, >, <>, !=, <=, !>, >=, !<, between, is null
优先处理 and

通配符

%：匹配>=0个任意字符
_：匹配1个任意字符
[]：匹配集合内的字符，或连接
^：否定
在开头处匹配会非常慢 ```sql select * from mytable where col like ‘[^AB]%’;

匹配不以A和B开头的任意文本

<a name="fa9ID"></a>
### <br />
<a name="S2ju4"></a>
### 计算字段
- 取别名
```sql
select col1*col2 as alias
from mytable;

concat：连接时去掉空格填充

trim：去除首尾空格

select concat(trim(col1),'(',trim(col2),')') as concat_col
from mytable;

函数

avg：忽略NULL行
count：结合distinct，汇总不同的值
max
min

sum

select avg(distinct col1) as avg_col from mytable;

文本处理

left：LEFT(ARG,LENGTH)，返回ARG最左边的LENGTH个字符串，ARG可以是CHAR或BINARY STRING。
right：RIGHT(ARG,LENGTH) ，返回ARG最右边的LENGTH个字符串
lower：转换为小写字符
upper：转换为大写字符
ltrim：去除左边空格
rtrim：去除右边空格
length：长度
soundex：转换为语音值

分组

NULL的行单独分为一组

按指定的分组字段进行分组

select col,count(*) as num
from mytable
group by col;

分组后按指定字段进行排序

select col,count(*) as num
from mytable
group by col
order by num;

过滤分组

select col,count(*) as num
from mytable
where col>2
group by col
having num>=2;

子查询

子查询中只能返回一个字段的数据
可以将子查询的结果作为where语句的过滤条件
- 检索出客户的订单数量，子查询语句会对第一个查询检索出的每个客户执行一次
```
select cust_name,(select count(*) from orders where orders.cust_id = customers.cust_id) as orders_num
from customers
order by cust_name;
```
  连接
join … on …

内连接（等值连接）：inner join

select A.value,B.value
from tableA as A inner join tableB as B
on A.key = B.key;

自连接（与自身进行内连接）
- 子查询 ```sql select name from employee where department = (
select department from employee where name = “Jim”

);


   - 自连接
```sql
select e1.name
from employee as e1 inner join employee as e2
on e1.department = e2.department and e2.name = "Jim";

自然连接
- 把同名列通过等值测试连接起来，同名列可以有多个
- 自然连接自动连接所有同名列
```
select A.value, B.value from tableA as A natural join tableB as B;
```

外连接

保留了没有关联信息的那些行

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行
- 每个查询必须包含相同的列、表达式和聚集函数
- 默认去重
- 保留全部行：union all ```sql select col from mytable where col = 1

union

select col from mytable where col = 2;

<a name="Ot6st"></a>
### 
<a name="KTbSj"></a>
### 视图
- 视图是**虚表**，**本身不包含数据**，也就不能对其进行索引操作
- 优点
   - 简化sql操作
   - 设置访问权限，保证数据的安全性
```sql
create view myview as 
select concat(col1,col2) as concat_col,col3*col4 as compute_col
from mytable
where col5 = val;
create view myview as ...

存储过程

对一系列sql操作的批处理
优点
- 代码封装，保证安全性
- 代码复用
- 预先编译性能高
自定义分隔符
参数：int、out、inout
变量赋值：select into 语句
每次只能给一个变量赋值 ```sql delimiter // create procedure myprocedure(out ret int) begin
```
declare y int;
```
select sum(col1) from mytable int y; select y*y into ret; end //

delimiter ;

call myprocedure(@ret); select @ret;

<a name="iS5o9"></a>
### 
<a name="ymYtK"></a>
### 游标
- 在存储过程中使用游标，对一个结果集进行**移动遍历**
- 需求：对数据集中的**任意行进行浏览和修改**
- 四个步骤
   - **声明游标**
   - **打开游标**
   - **取出数据**
   - **关闭游标**
```sql
delimiter //
create procedure myprocedure(out ret int)
    begin
      declare done boolean default 0;
    declare mycursort cursor for 
    select col1 from mytable;

二、自测

##
/**/
create table aaa;
 use aaa;
# 自增(AUTO_INCREMENT)、默认值(DEFAULT 1)、非空(NOT NULL)、主键(PRIMARY KEY(`id`))、设置引擎(ENGINE=)、编码方式（DEFAULT CHARSET=）
create table mytable(
   id INT NOT NULL AUTO_INCREMENT,
   sex INT NOT NULL DEFAULT 1,
   name VARCHAR(45),
   dt DATE,
   PRIMARY KEY(`id`))
 ENGINE=MyISAM,DEFAULT CHARSET=utf8;
create table mytable(
   id INT NOT NULL AUTO_INCREMENT,
   sex INT NOT NULL DEFAULT 1,
   name VARCHAR(45),
   dt DATE,
   PRIMARY KEY(`id`))
 ENGINE=INNODB,DEFAULT CHARSET=UTF8;
# 添加列
ALTER TABLE mytable ADD newcol VARCHAR(50);
ALTER TABLE mytable ADD newcol VARCHAR(50);
# 删除列
ALTER TABLE mytable DROP COLUMN sex;
ALTER TABLE mytable DROP COLUMN sex;
ALTER TABLE mytable DROP COLUMN sex;
# 修改字段类型（MODIFY）
ALTER TABLE mytable MODIFY COLUMN name VARCHAR(100) DEFAULT NOT NULL COMMENT '姓名';
ALTER TABLE mytable MODIFY sex VARCHAR(100) DEFAULT '女' COMMENT '性别';
# 修改字段名（CHANGE）
ALTER TABLE mytable CHANGE name nick_name VARCHAR(100) DEFAULT NULL COMMENT '性别';
# 修改表名(RENAME TO)
ALTER TABLE mytable RENAME TO newtable;
# 修改表注视
ALTER TABLE 表名 COMMENT '新注释';
# 在指定位置加入新列（字段）
ALTER TABLE mytable ADD mobile varchar(50) NOT NULL AFTER name;
# 删除字段
ALTER TABLE mytable DROP COLUMN name;
DROP TABLE mytable;
INSERT INTO mytable(col1,col2) VALUES(val1,val2);
INSERT INTO mytable1(col1,col2) SELECT col1,col2 FROM mytable2;
INSERT INTO mytable1(col1,col2) SELECT col1,col2 FROM mytable2;
CREATE TABLE newtable AS SELECT * FROM mytable;
CREATE TABLE tt as SELECT * from mytable;
# 更新
UPDATE mytable SET age = 10 WHERE id=1
UPDATE mytable SET age=10 WHERE id=2
# 删除
DELETE FROM mytable WHERE id=1;
DELETE FROM mytable WHERE id=1;
# 清空表
TRUNCATE TABLE mytable;
# 查询
SELECT DISTINCT col1,col2 FROM mytable;
SELECT DISTINCT col1,col2 FROM mytable;
SELECT * FROM mytable LIMIT 5;
SELECT * FROM mytable LIMIT 0,5;
SELECT * FROM mytable LIMIT 2,3;
# 排序
SELECT * FROM mytable order by age desc,weight asc;
# 过滤
select * from mytable where col is NULL;
select * from mytable where age = 18;
-- <> != <= !> BETWEEN A AND B
select * from mytable where age in (18,19,20);
# 通配符（用在开头匹配会非常慢）
%：匹配>=0个任意字符
_：匹配1个任意字符
[]：匹配集合内的字符（字符用或者连接）
^：否定
LIKE
# 计算字段
AS：取别名
CONCAT：连接两个字段
TRIM：去除空格
select concat(trim(col1),'(',trim(col2),')') as concat_col from mytable;
# 函数
AVG() -- 忽略NULL行）！！
COUNT()
MAX()
MIN()
SUM()
select col,count(*) as num from mytable group by col;
select col,count(*) as num from mytable group by col;
select A,count(B) from mytable group by col order by xx;
select col,count(*) as num from mytable where col>2 group by col having num>=2;
select col,count(*) as num from mytable where col>2 group by col having num>=2;
# 子查询
select * from mytable where col1 in (select col2 from table2);
select cust_name,(select count(*) from Orders where Orders.cust_id = Customers.cust_id) as orders_num from Customers ORDER BY cust_name;
# 连接（可以替代子查询）
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 A,B on A.id=B.id;
-- 自连接
select A.name from employee as A inner join employee as B on(!!) A.department=B.department and B.name="Jim";
select e1.name from employee as e1 inner join employee as e2 on e1.department=d2.department and e2.name="Jim";
-- 外连接
select Customers.cust_id,Customers.cust_name,Orders.order_id from Customers left outer join Orders on Customers.cust_id = Orders.cust_id;
# 组合查询（默认去重、只能包含一个order by）
select * from mytable where col=1 union select * from mytable where col=2;
# 视图（虚拟的表，本身不包含数据，无法进行索引操作）
create view myview as
select concat(col1,col2) as concat_col from mytable where col=val;
# 存储过程（自定义分隔符，预先编译，类似函数的作用，变量赋值）
delimiter //
create procedure myprocedure(out ret int)
    begin
      declare y int;
    select sum(col1) from mytable into y;
    select y*y into ret;
  end //
delimiter ;
delimiter //
create procedure myprocedure(out ret int)
    begin
      declare y int;
    select sum(col1) from mytable into y;
    select y*y into ret;
  end //
delimiter ;
call myprocedure(@ret);
select @ret;
# 游标
-- 主要用于交互式应用，其中用户需要对数据集中的任意行进行浏览和修改。
-- 步骤：声明游标；打开游标；取出数据；关闭游标
delimiter //
create procedure myprocedure(out ret int)
    begin
          declare done boolean default 0;
      #声明游标
      declare mycursor cursor for
          select col1 from mytable;
      # 定义continue handler，当 sqlstate '02000'这个条件出现时，执行set done=1
      declare continue handler for sqlstate '02000' set done = 1;
      #打开游标
      open mycursor;
      #取出数据
      repeat 
          fetch mycursor into ret;
        select ret;
      until done end repeat;
      #关闭游标
      close mycursor;
  end //
delimiter ;
# 触发器
-- 在某个表执行以下语句时自动执行：DELETE\INSERT\UPDATE
-- 必须指定在语句执行之前还是之后自动执行
    -- 之前执行，用于数据验证和净化：before
  -- 之后执行，用于审计跟踪，将修改记录到另一张表中：after
create trigger mytrigger after insert on mytable for each row select NEW.col into @result;
select @result; -- 获取结果
-- insert触发器：包含一个名为NEW的虚拟表
-- delete触发器：包含一个名为OLD的虚拟表，只读
-- update触发器：包含一个名为NEW和一个名为OLD的虚拟表，其中NEW是可以被修改的，OLD是只读的
-- MYSQL不允许在触发器中调用存储过程
# 事务
-- 基本术语
    -- 事务（transaction）：一组SQL语句
  -- 回退（rollback）：撤销指定SQL语句
  -- 提交（commit）：将未存储的SQL语句结果写入数据库表
  -- 保留点（savepoint）：事务处理中设置的临时占位符（placeholder），可以对它发布回退。
-- 不能回退select、create、drop语句
-- mysql事务提交默认是隐式提交，每执行一条语句就把这条语句当成一个事务然后提交。
    -- 当出现start transacion语句时，会关闭隐式提交；
  -- 当commit或rollback语句执行后，事务会自动关闭，重新恢复隐式提交。
  -- 如果没有设置保留点，rollback会回推到start transaction语句处
  -- 如果设置了保留点，并且在rollback中指定该保留点，则会回退到该保留点处。
start transaction
savepoint delete1
 rollback to delete1
 commit
 # 字符集
 -- 字符集：字母和符号的集合
 -- 给表指定字符集
 create table mytable(
   col varchar(10) character set latin collate latin1_general_ci
 ) default character set hebrew collate hebrew_general_ci;
 -- 排序、分组时校对
 select * from mytable order by col collate latin1_general_ci;
 # 权限管理
 -- mysql账户信息保存在mysql数据库中
 use mysql;
 select user from user;
 -- 创建账户
 create user myuser identified by 'mypassword';
 -- 修改账户名
 rename user myuser to newuser;
 -- 删除账户
 drop user myuser;
 -- 查看权限
 show grants for myuser;
 -- 授予权限
 grant select,insert on mydatabase.* to myuser;
     -- 账户：username@host
  -- 默认主机名：username@%
 -- 删除权限
     -- 整个服务器：grant all、revoke all
  -- 整个数据库：on database.*
  -- 特定表：on database.table
  -- 特定列
  -- 特定存储过程
revoke select, insert on mydatabase.* from myuser;
-- 更改密码
set password for myuser=Password('new_password');

MySQL学习笔记

SQL语法

注释