php复习 第十三天 mysql基础1

1.DDL（Data Definition Language）数据库定义语言DDL不需要commit.CREATEALTERDROPTRUNCATECOMMENTRENAME2.DML（Data Manipulation Language）数据操纵语言DML需要commit.SELECTINSERTUPDATEDELETEMERGECALLEXPLAIN PLANLOCK TABLEshow databases; -->查看当前所有数据库create database t1;-->创建数据库use t1;-->使用数据库show tables;-->查看数据表信息创建数据表：create table user(id int auto_increment not null primary key ,username varchar(32) not null,password varchar(32) not null);查看当前数据库中的数据表：show tables;查看数据表结构:describe user;查看数据表内信息：select * from user;查看数据库创建语句：show create table username;Create Table: CREATE TABLE `user` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `username` varchar(32) NOT NULL,  `password` varchar(32) NOT NULL,  PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=gbk删除一个数据库：drop database t2;删除一个数据表：drop table user2;增加一条数据:insert into user(username) values('test');删除一条数据:delete from user where id = 2;修改一条数据：update user set username = 'test' where id = 1;修改字段类型：alter table user modify column id tinyint not null auto_increment;修改字段名称：alter table user CHANGE id iddd int;增加表字段：alter table user add address varchar(32);删除表字段：alter table user drop salary1;排序：select * from user order by id asc; //由低到高select * from user order by id desc;//由高到底排序限制：// limit 1;表示只看一条。// limit 0,1；索引为0开始计算,取1条数据。select * from user order by id desc limit 0,1;//从第0条开始，查看一条聚合相关操作：//sumselect sum(salary) from user;//countselect count(*) from user;//查询记录总数//maxselect max(salary) from user;//最大值//minselect min(salary) from user;//最小值//avgselect avg(salary) from user;//平均值//分组函数 group by#group by 有一个原则,就是 select 后面的所有列中,没有使用聚合函数的列,必须出现在 group by 后面//查询各个部门的员工工资之和。select department,sum(salary) from user group by department;//with rollup 分组后汇总select department,sum(salary) from user group by department with rollup;//having 聚合后过滤 与where不同。where必须在聚合之前。//查询工资发放超过5000的部门与工资。select department,sum(salary) from user group by department having sum(salary) > 5000;