select 5种子句
where 、针对表中的列发挥作用,查询数据
group、分组查询 ,把行按字段分组
select id,sum(dd*ss) from table_num group by id;
having、针对查询结果中的列发挥作用,筛选数据
selectprice from shop where id=3 having price>200;
orderby、排序功能 默认升序asc 降序 desc
orderbyitem 1,item2 asc/desc
limit[offset][N] select * from stu where stuid limit 5,10;
having与where异同点
where针对表中的列发挥作用,查询数据
having针对查询结果中的列发挥作用,筛选数据
创建create,修改alter,删除drop 数据库和表,以及解决显示中文乱码问题
mysql 创建 数据库时指定编码
GBK: create database test2 DEFAULT CHARACTER SETgbk COLLATE gbk_chinese_ci;
UTF8: CREATE DATABASE `test2` DEFAULT CHARACTERSET utf8 COLLATE utf8_general_ci
删除:
drop database xue_xiao;
创建新表
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
删除新表:drop tabletabname
添加主键:Alter table tabname add primary key(col)
删除主键:Alter table tabname drop primary key(col)
创建索引:create [unique] index idxname on tabname(col….)
删除索引:drop index idxname
创建视图:create view viewname as select statement
删除视图:drop view viewname
增加列:alter table xue_sheng ADD nian_ling int;
删除列:alter table xue_sheng DROP nian_ling;
修改列: alert table xue_shengmotify nianling varchar(20);
增加列:alter table xue_sheng ADD nian_ling int;
删除列:alter table xue_sheng DROP nian_ling;
修改列: alert table xue_shengmotify nianling varchar(20);