数据库常用操作

mysql数据库中常用命令：

1. show columns from customers  --显示表列
2. Describe 是 show columns from customer 的快捷方式
3. Show status  查看服务器状态信息
4. Show create database 和 show create table  分别用来显示创建特定数据库或
5. Show grants 用来显示授予用户的安全权限
6. Show errors 和show warnings 用来显示服务器错误或警告

操作mysql表的用法：

1. Distinct 关键字去重
2. Select prod_name from products limit 5  -- limit  5 关键字 表示mysql返回的不多于5行，limit 5,5 表示mysql返回从第5 行开始的第5行 注意： limit 限制的第一行为0
3. Select prod_name from products order by prod_name  desc 排序  desc 指定降序排列
4. 与desc 相反的关键字是asc
5. 如果使用limit，必须为order by之后
6. 如果有where条件，order by在where之后 where 条件匹配的时候，默认不区分大小写
7. Where 条件中可以使用id<=10, >= ,<>不等于 != 也是不等于的用法
8. Between 5 and 10  检索价格在5到10之间 包括5 和10，范围从低值到高值。
9. Where prod_price is null 判断空值
10. And 和or注意： select name price from products where id=1002 or id=1003 and price>10要求结果是 id是1002或者1003的商品且价格大于10上语句的结果为 id是1002 的商品或者是id是1003且价格>10 出现这种情况的原因是and具有较高的优先级，解决办法是：select name price from products where （id=1002 or id=1003 ）and price>10IN 操作符指定条件范围  select name ，price from products  where id in(1002,1003)
11. NOT 否定它之后所跟的任何条件。
12. %百分号通配符，表示任意字符出现任意次数。但不能匹配空值
13. _ 下划线匹配单个任意字符

正则表达式匹配

1. Select name from products where name REGEXP '1000'  检索包含文本1000的行
2. Regexp '1000|2000' 表示匹配其中之一
3. Regexp '[123] Ton' 表示匹配几个限定字符其中的一个
4. Rxgexp '[^123]' 表示匹配除去这些字符以外的东西
5. Rxgexp '[^1-9]' 表示匹配1到9之间的数字
6. Regexp '\\.' 表示查找.字符
7. 为什么会用两个表示转义，应为mysql自己解释一个，正则表达式库解释另一个。
8. Regexp ‘\\([0-9] stricks?\\)’表示\\( 匹配（；stcick后的s表示s可选，因为？匹配他前面的任何字符的0次或1出现。
9. Like 和regexp的不同在于，like匹配整个串而regexp匹配子串
10. regexp可以使用定位符，^&表示开始和结束。

常用函数

1. 多数数据库管理系统用+或||来实现拼接，mysql用concat（）函数来实现
2. Select concat(name,'(',country,')') from vendors order by name
3. Select concat(Rtrim(name),'(',country,')') from vendors order by name --Rtime()函数表示去掉右边的所有空格相对的LTrim（）
4. As 关键字赋别名
5. Select id quantity price quantity*price as expand_price 可以使字段相乘
6. Select name，Upper（name） as name_upper from vendors  Upper()将文本转换为大写。

常用的文本处理函数

1. left（）返回串左边的字符
2. length（）返回串的长度
3. locate（）找出串的一个子串
4. lower（）将串转换为小写
5. Ltrim（）去掉串左边的空格
6. right（）返回串右边的字符
7. rtrim（）去掉串右边的空格
8. SubString（）返回子串的字符
9. Upper（）将串转换为大写

常用的日期和时间处理函数

1. addDate（）增加一个日期
2. addTime（）增加一个时间
3. curdate（）返回当前日期
4. Curtime （）返回当前时间
5. date（）返回日期时间的日期部分
6. datediff（）计算两个日期之差
7. Date_add() 增加一个日期
8. Date_Format（）返回一个格式化的日期或时间串
9. day（）返回一个日期的天数部分
10. dayOfweek（）返回的星期几
11. hour（）小时部分
12. minute（）分钟
13. month（）月份部分
14. Now（）当期日期和时间
15. Second（）秒部分
16. Time（）一个日期的时间部分
17. year（）年份部分
18. where日期过滤，格式必须为yyyy-mm-dd
19. Select id,num from orders where date='2005-09-01'
20. 因为date的数据类型为dateTime
21. 所以用where Date（date）=‘2005-09-01’ 会更靠谱些比较日期
22. 获取9月份的订单数据  Select id,num from orders where Date（date）between '2005-09-01' and '2005-09-30';
23. 方法二： Select id,num from orders where year（date）=2005 and month（date）=9

常用数值处理函数

1. abs（） 返回绝对值
2. mod（）返回除操作的余数

 

聚集函数

1. avg（） 返回平均值 忽略列值为null的行
2. count（）返回某列的行数 不管列值是否为null 都计算
3. max（）最大值 忽略列值为null的行
4. min（）最小值 忽略列值为null的行
5. sum（）某值之和 忽略列值为null的行
6. Select id，count（1）from  products group by id 分组 Group by子句必须出现在where 子句之后，order by 子句之前
7. where和having的区别：where过滤行，Having 过滤分组  where在数据分组前进行过滤，having在分组后进行过滤

其他操作

1.利用子查询进行过滤

Select name，contact from customers where id in（select id from orders where num in （select num from orderitems  where id=‘TNT2’））

2.作为计算字段使用子查询其中也用到相关子查询。

Select name state （select count（*） from orders where orders.id=customers.id）as

Orders from customers order by name;

内部联结

Select vend_name，prod_name,price from vendors inner join products on vendors.id=products.id

与下面的语句一样的效果：

Select vend_name，prod_name,price from vendors  products where vendors.id=products.id

自联结，自然联结，外部联结还包括没有关联的行；在使用outer join语法时，必须使用right，或left关键字指定包括其所有行的表

 

如果想从表中删除所有行，可使用truncate table 可以更快些删除表数据

Id int not null Auto_increment 自增

Name char（10） not null default “小明”；

mysql创建表后的右括号加上engine=innodb 表示选择innodb的数据处理引擎

innodb是一个可靠的事务处理引擎。

myIsam 是个性能极高的引擎，但不支持事务处理。

Alter table 更新表结构

Alter table vendors add vend_phone Char(20)

Alter table vendors drop column vend_phone;

定义外键

Alter table orderitems add constraint fk_orderitems_orders

Foreign key （order_num） reference orders（order_num）

删除表

 drop table customers

重命名表

Rename table cusomers to cs

视图是虚拟的表，视图只包含使用时动态检索数据的查询。

视图的操作：

create view ;

show create view viewname;

Drop view viewname;

create or replace view;

执行存储过程

执行存储过程的语句为call，call接受存储过程的名字以及需要传递给他的任意参数

Call productpricing(@pricelow,@pricehigh,@priceaverage)

创建存储过程：

Create procedure productpricing（）

Begin

Select avg（price） as priceaverage

From products

end；

删除存储过程

Drop procedure productpricing

Declare 在存储过程中申明局部变量

游标是一个存储在mysql服务器上的数据库查询，它不是一条select语句，而是被

被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

游标存在存储过程中

创建游标

Create procedure pro（）

Begin

Declare ordernumbers cursor

For

Select num from oreders

End ；

Start transaction 开启事务

Commit 提交事务（写或保存）

Rollback 回退（撤销）mysql语句

事务自动关闭

在事务处理块中放置占位符，这样，如果需要回退，可以回退到某个占位符

这些占位符称为保留点，创建占位符，使用savepoint 语句