查看数据库: show databases;
查看是否已经选定数据库: select database();
选择数据库: use databaseName;
创建数据库: create database databaseName;
删除数据库: drop database databaseName;
查看存储引擎: show engines
查询
查询语句: select * from tableName;
查询不同的列: select distinct id from tableName;
限制返回的条数: select * from tableName limit 5;
(如果需要定义开始的行数)
select * from tableName limit 6,5
排序
普通排序: select * from tableName order by id;
多个列排序: select * from tableName order by id,name;
(顺序是按照输出时列的顺序进行排序)
指定方向排序: select * from tableName order by id DESC ;
(DESC是降序排序,默认情况下是升序排序的)
指定方向多个列排序: select * from tableName order by id DESC ,name;
过滤
where字句: select * from tableName where name='test';
范围检查:select * from tableName where name between 5 and 6;
空值检查: select * from tableName where name is NULL;
AND字句: select * from tableName where id=1 and name='test';
OR字句: select * from tableName where id=1 or name='test';
(如果通知存在and和or字句,会优先执行and,所以必要时候可用括号对其进行限制)
IN字句: select * from tableName where id in (100,200);
NOT字句: select * from tableName where id not in (100,200);
LIKE字句:(%:任意数量的任意字符,_:单个任意字符)
select * from tableName where name like 'test%';
select * from tableName where name like 'test_';
正则
基本字符匹配: select * from tableName where name REGEXP '100 tom';
(REGEXP后跟的是正则表达式)
计算字段
拼接字符串: select concat(name,'(',id,')') from tableName;
算叔计算: select id ,name ,number ,price,num from tableName;
数据处理函数
RTrim()/LTrim():清楚左/右侧空白
soundex():返回串的soundex值
locate():找出串的一个字符
lower():全部转成小写
upper():全部转成大写
subString():返回子串的字符
left()/right():返回串的左/右边字符
日期处理函数
addDate():增加一个日期
addtime():增加一个时间
curDate():返回当前日期
curTime():返回当前时间
Date():返回日期部分
DateDiff():计算两个日期之差
Date_Add():日期运算
Date_Formate():格式化日期
Day():返回日期的天数部分
DayOfWeek():返回该日期是周几
Hour():返回小时
Minute():返回分钟
Month():返回小时
Now():返回当前时间
Second():返回秒
Time():返回时间
year():返回年份
聚集函数
AVG():返回某列的平均值
select AVG(number) as avg_number from tableName;
COUNT():返回某列的行数
select count(*) from tablieName;
select count(number) from tableName;
MAX():返回某列的最大值
select max(number) as min_number from tableName;
MIN():返回某列的最小值
select min(number) as min_number from tableName;
SUM():返回某列之和
select sum(number) as sum_number from tableName;
(以上这几个函数可以在同一sql中使用)
分组
普通分组:
select vend_id,count(*) as num_prods from tableName group by vend_id;
过滤分组:
select cust_id,count(*) as orders from tableName group by cust_id having count(*)>=2;
(having是对分组进行过滤,where是对查询进行过滤)
分组排序
select order_num,sum(quantity*item_price) as ordertotal from tableName
group by order_num having sum(quantity*item_price) >= 50
order by ordertotal;
子查询
select cust_id from orders where order_num in (select order_num from orderitems where prod_id = 'test2');
select cust_name,cust_state,(select count(*) from orders where orders.cust_id=customers.cust_id) as orders form customers order by cust_name;
联结
自联结:
select vend_name,prod_name,prod_price from vendors,products
where vendors.vend_id = products.vend_id order by vend_name,prod_name;
内联:
select vend_id,prod_name,prod_price from vendors inner join products
on vendors.vend_id = products.vend_id;
外联:
select customers.id,orders.order_num from customers left outer join orders
on customers.cust_id = orders.cust_id;
select customers.cust_id,orders.order_num from customers right outer join orders
on orders.cust_id = customers.cust_id;
插入
整行插入
insert into tableName values (1,'test','123');
insert into tableName (id,name,password) values (1,'test','123');
插入检索出的数据
insert into tableName1 (id,name,password)
更新数据:
update tableName set cust_email = 'elmer@fudd.com' where cust_id = 10005;
删除数据:
delete from tableName where cust_id = 10006;
创建表
create table tableName(
cust_id int not null auto_increment,
cust_name char(50) not null,
cust_address char(50) null,
cust_city char(50) null,
cust_check char(10) not null default 1,
primary ley(cust_id)
)engine = innoDB;
primary ley=>指定主键
auto_increment=>自增
default=>默认值
engine=>指定引擎
注:MySQL有三种引擎:
innoDB:可靠的事务引擎,不能全文搜索
memory:类似于innoDB,数据存放于内存中,相应比较快
MyIsAM:极高的性能引擎,支持全文搜索,单不支持事务处理
更新表
alter table tableName add vend_phone char(50);//增加字段
alter table tableName drop column vend_phone;//删除字段
删除表
drop table tableName;
重命名表
rename table tableName1 to tableName2;
视图
创建视图
create procedure productpring()
begin
select avg(prod_price) as priceaverage from products;
end;
调用存储过程
call productpring();
删除存储过程
drop procedure productpring();
带参数的存储过程
create procedure productpring(
out pl decimal(8,2),
out ph decimal(8,2),
out pa decimal(8,2))
begin
select Min(prod_price) into pl from products;
select Max(prod_price) into ph from products;
select Avg(prod_price) into pa from products;
end;
out=>指定相应参数,用于存放值返回给调用者
in=>传递给存储过程
变量
call productpring(@pricelow,@pricehight,@priceaverage);
select @pricelow;//查询返回值
触发器
创建触发器
create trigger newproduct after install on products
for each row select 'product added';
after each=>在插入语句成功后触发
for each=>对每一条出入语句都执行
select 'product added'=>在成功后触发显示'product added'
删除触发器
drop trigger newproduct ;