mysql基础

一.创建表

create table mytable(

id int not null auto_increment,
col varchar(45) not null ,
col2 Date null,
primary key('id')
);

二.修改表

​ 1.添加列

alter table mytable
add col varchar(20); //这里的col是新增加的列名

​ 2.删除列

alter table mytable
drop column col;

​ 3.删除表

drop table mytable

三.插入表

1.普通插入

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

2.插入检索出来的数据

insert into mytable(col1,col2 )
select col1,col2 from mytable;

四.更新表

update mytable set col='java' where id=1;

五.删除一行

delete from mytable where id=1;

删除所有行

TRUNCATE TABLE mytable;

六.查询

Distinct

select distinct col1,col2 from mytable

这里面只有所有列都相同才算相同，否则也会返回

Limit

select * from mytable limit 5//返回前五行
select * from mytable limit 0,5//返回1，2，3，4，5行

七.排序

可以对一列或者多列指定排序

select * from mytable order by col desc,col2 asc;

八.通配符

% 匹配>=0个任意字符
_ 匹配==1个任意字符
[]匹配集合内的字符

select * from mytable where col like '[^ab]%'

九.计算字段

在数据库服务器完成数据的转换和格式化往往比客户端快的多，并且数据格式化之后网络的流量更小，计算字段可以使用as来取列 别名，否则输出的时候就是计算的表达式

十.函数

1.汇总

AVG：返回某列的平均值

COUNT：返回某列的行数

MAX（）：返回某列的最大值

MIN（）：返回某列的最小值

SUM（）：返回某列值之和

select avg（distinct col） as avg_col from mytable

2.文本处理

left（）：返回左边的字符

right（）：返回右边的字符

lower（）：准换为小写字符

upper（）：转换为大写字符

LTRIM（）：去除左边的空格

RTRIM（）：去除右边的空格

LENGTH（）：长度

3.日期和时间处理

adddate（）：增加一个日期（天，周）

addtime（）：增加一个时间（时，分）

curdate（）：返回当前日期

curtime（）：返回当前时间

date（）：返回当前日期和时间部分

now（）：返回当前日期和时间

4.数值处理

sin（）：正弦

abs（）：绝对值

sqrt（）：平方根

mod（）：余数

exp（）：指数

rand（）：随机数

十一.分组

group by自动按照分组字段进行排序，order by按照汇总字段来进行排序

where 过滤行

having 过滤分组

十二.子查询

子查询只能返回一个字段的数据，可以将子查询的结果作为where语句的过滤条件

十三.连接

1.内连接

inner join 也可以使用普通查询并在where中将两个要连接的列用等值方法连接起来

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.自连接

自连接可以看成内连接的一种，只是连接的表是本身而已

子查询版本

select name from employee where department=(
select department from employee where name='jim')

内连接版本

select e1.name from employee as e1 inner join employee as e2 on e1.department=e2.department and 
e2.name='jim'

3.自然连接

自然连接把同名列通过等值测试连接起来，通名列可以有多个

select A.value,B.value from tablea as A natural join 
tableb as B

4.外连接

外连接保留了没有关联的行，分为左外连接，右外连接以及全外连接。

左外连接就是保留左边没有关联的行

eg：检索所有顾客的订单信息，包括还没有订单信息的顾客

select C.cust_id,O.order_num from Customers as C left outer join Orders as O on C.cust_id=O.cust_id

十四.组合查询

使用union来组合两个查询，第一个返回m行，第二个返回n行，那么组合查询一般就返回m+n行。

每个查询必须包括相同的列，表达式和聚集函数

默认会去除相同的行，如果需要保留相同的行，那么使用union all

只能包含一个order by子句，并且放在语句的最后

select col from mytable  where col=1 union
select col from mytable where col=2

十五.视图

视图 是虚拟的表，本身不包含数据，也不能对其索引操作

视图具有以下好处：

• 简化复杂的sql操作，比如复杂的连接
• 只使用实际表的一部分数据
• 通过只给用户访问视图的权限，保证数据的安全性
• 更改数据格式和表示

CREATE VIEW myview AS
SELECT Concat(col1, col2) AS concat_col, col3*col4 AS compute_col
FROM mytable
WHERE col5 = val;

十六.存储过程可以看做是一系列sql操作的批处理

好处：

• 代码封装，保证了一定的安全性
• 代码复用
• 预先编译，具有很高的性能

包含in ,out和inout三种参数

参数in的使用(代表输入，意思说你的参数要传到存过过程的过程里面去)

参数out的使用(代表往外输出)

参数inout的使用(既能输入一个值又能传出来一个值)

给变量赋值都需要使用select into语句，每次只能给一个变量赋值，不支持集合的操作

命令行中创建存储过程需要自定义分隔符，因为命令行由; 为结束符，而存储过程中也包含了分号，因此会错误把这部分分号当成结束符，造成语法错误。

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 ;

十六.游标

在存储过程中使用游标可以对一个结果集进行移动遍历

十七.逐步

初始化会在某个表执行以下语句而自动执行delete ，insert，update

十八.事务管理

基本术语

• 事务：指一个sql语句
• 回退：指取消指定sql语句的过程
• 提交：指将为储存的sql语句写入数据库表
• 保留点：指事务处理中设置的临时占位符，可以对它发布回退

不能回退select语句，也不能回退create和drop语句

mysql的事务提交默认为隐式提交，当出现start transaction语句，会关闭隐式提交，当commit或roll back语句执行后，事务会关闭，重新恢复隐式提交，设置auto commit为0可以取消自动提交，

如果没有设置保留点，roll back会回退到start transaction语句处，如果设置了保留点，并且在roll back中设置了保留点，那么就会回退到roll back上

START TRANSACTION
// ...
SAVEPOINT delete1
// ...
ROLLBACK TO delete1
// ...
COMMIT