删除sex:alter table employee drop cpolumn sex;
修改name字段:alter table employee change column name username varchar(10);
改变usename字段长度:alter table employee change username username varchar(60);
改变usename字段类型:alter table employee change username username var(6);
添加主键约束:alter table employee add constraint pk_id primary key(id);
修改表名:rename table employee to user;
表的字符集:alter table user character set utf8;
添加索引:alter table user add index intind(id);
查询:select * from tstudent;
查多字段:select sid,name from tsdudent;
查字段改为中文:select sid as "学号", name as "姓名" from tstudent;
查询满足条件:select *from tcourse where chours=100;
select *from tcourse where chours<100;
选择查询:select *from tcourse where chours between 50 and 100;
select *from tcourse where (chours>=50) and (chours<=100);
select *from tcourse where chours in(100,72);
select *from tcourse where (chours=100) or (chours=72);
匹配查询:select *from tcourse where cname like"%m%";
排序默认asc升序:select *from tcourse order by chours;
desc降序: select *from tcourse order by chours desc;
统计记录:select count(*) from tcourse;
统计大于90:select count(*) from tcourse where chours>90;
课程总数:select sum(chours) from tcourse where cterm;
中文名:select sum(chours) as "学时",sum(credit) as "学分" from tcourse where cterm=1;
平均值AVG:select avg(chours) from tcours where cterm=1;
max与min:select max(chours),min(chours) from tcourse;
select addtime("02:30:20","01:00:01");
当前日期:select current_date();
select current_time
时间戳:select current_timestamp();
日期:select date("2010-01-01 16:00:01");
加时间:select birthday,date_add(birthday,interval 2 year) from tstudent;
select birthday,date_add(birthday,interval 2 day) from tstudent;
select birthday,date_add(birthday,interval 2 month) from tstudent;
select birthday,date_add(birthday,interval 2 week) from tstudent;
减时间:select birthday,date_sub(birthday,interval 2 year) from tstudent;
select birthday,date_sub(birthday,interval 2 day) from tstudent;
时间差:select datediff("1990-02-01","1990-01-01");
返回当前:select now(); select year/month/date(now());
字符串函数-->字符集:select charset("hello world");
select charset(name) from tstudent;
字符连接:select concat("hello","world");
转换大写:select ucase("hello");
转换小写:select lcase("WORLD");
字符长度:select length("hello");
在字符的位置:select instr("helloworld","wo");
左取字符:select left("hello world",5);
替换:select replace("hello world","lo","dd");
比较字符:select strcmp("aca","abb");
取字符:select substring("hello world",5,6);//从str的position开始,取length个字符
去空格:select rtrim(" hello world ");//右端
select ltrim(" hello world");//左端
select trim(" hello world ");//前后
select concat(trim("hello world"),"dd");
select concat("dd",trim("hello world"));
数学函数:
绝对值:select abs(-2);
转换进制:select conv(11,8,10);
向下取整:select floor(16.8);
保留小数倍数
:select format(16.2438,2);
随机数:select rand();
分组求和:select cterm,sum(chours) from tcourse group by cterm;
select cterm,sum(chours)from tcourse group by chours having sum(chours)>120;