增加列类型：
alter table table_name add table_name fieltype not null default ' ' after resume;
增改删列类型：add modify drop
修改表名rename table table_name to table_name（备注：修改库名是不行的）
RENAME TABLE `emp` TO `employee`
SELECT * FROM `employee`
修改表的字符集 alter table table_name character set charset
表的结构：desc table_name
CREATE TABLE `emp`(id INT ,`name` VARCHAr(32),sex CHAr(1),birthday DATE,entry_date DATETIME,job VARCHAr(32),
salary DOUBLE, `resume` TEXT)CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;
INSERT INTO `emp` VALUES(100,'小妖精','男','2000-11-11','2010-11-10 11:11:11','巡山的',
'3000','大王叫我来巡山')
SELECt * FROM `emp`;
ALTER TABLE `emp` ADD image VARCHAr(32)NOT NULL DEFAULT ''AFTER RESUME;
DESC `emp`
#删除列类型
ALTER TABLE `emp` DROp image
#类名修改
ALTER TABLE `employee` CHANGE `name` `user_name` VARCHAr(32);(没有to)
#增删改查数据命令
c[create]r[read]u[updata]d[delete]
values()中的字符要放' '里
alter是修改列类型 updata是修改对应列中数据
UPDATE `employee` SET `salary`=5000 WHERe `user_name` = '小妖精';
####%%%%%&&&&&列名用` `反单引号 列数据用' '单引号 屏蔽 关键字
#删除组数据delete语句
DELETE FROM `employee` WHERe `user_name`='小妖精'
delete 语句不能删除某行的那一列数据但是可以用update置空
UPDATE `employee` SET `sex`=' '(WHERe NAME=??)
（处理数据的都不用谢table_name前的table）
#select distinct * from 去重查询
SELECt DISTINCT english FROM `students`
distinct 是针对 English也就select后面的所有查询都相同才去重
#只查询指定列数据
SELECT `name` ,english FROM `students`
对查询的显示列取别名as
SELECt english as `count` FROM `students`
SELECt `name`,(english+math+chinese)AS `count` FROM `students`
#where运算
SELECt `name` FROM `students` WHERe english > 90;
SELECt `name` FROM `students` WHERe math > 60 AND id >4;
SELECt `name` FROM `students` WHERe (chinese+english+math)>200 AND math
#betweend num1 and num2 在num1~num2内闭区间
SELECt * FROM `students` WHERe english BETWEEN 70 AND 100
#查询等于几个确定的值
select * from `students` where english=78 or english=98
select * from `students` where english in(98,78)
#order by排序查询结果asc默认升序desc降序
SELECt * FROM `students` WHERe english >70 ORDER BY english DESC
SELECt * FROM `students` WHERe english >70 ORDER BY english ASC
#count合计函数返回查询结果行数
SELECt COUNT(*) AS `total people`FROM `studentS` WHERe `math`>82
count(列)会排除位null情况
#sum函数统计列数据相加总数
SELECt SUM(math)AS `math total score`FROM `students`
#avg平均分
SELECt AVG(math) FROM `students`
#max,min 最大分最小分
SELECt MAX(math),MIN(math) FROM `students`
ALTER TABLE `students` ADD `bumen` CHAr(8) AFTER chinese
UPDATe `students` SET bumen='教育' WHERe `name`='韩顺平';
UPDATE `students` SET bumen='三国' WHERe `name`='张飞';
UPDATE `students` SET bumen='水浒' WHERe `name`='宋江';
UPDATE `students` SET bumen='三国' WHERe `name`='关羽';
UPDATE `students` SET bumen='三国' WHERe `name`='赵云';
#演示group by+having
SELECT AVG(math),MAX(english) FROM `students` GROUP BY bumen ;
#字符串相关函数的演示
-- charset() 返回字符集
SELECt CHARSET(`name`) FROM `students`
-- concat(sting,sting2,..)连接字符串
SELECt CONCAt(`name`,'部门是',bumen) FROM `students`
-- instr(string,substring)返回substr在string中的位置
SELECt INSTR(`name`,'韩') FROM `students`：3
-- ucase()返回大写up
SELECt UCASE('aa') FROM `students`
-- lcase()返回小写low
SELECt LCASE('AA') FROM `students`
-- left(string,length)从左取length个字符的字符串
SELECt LEFt(`name`,2) FROM `students`
-- length()字符串长度
SELECt LENGTH(`name`) FROM `students`
-- replace(str,string,restring)在str中用restr替代string
SELECT REPLACE('654656','654','777') FROM `students`
-- strcmp(str1,str2)逐字比较str1与str2的大小-1就是str2大1就相反0就一样大
SELECt STRCMP('abc','acc') FROM `students`
-- substring(string,postion,length)从str的pos取length个字符
SELECt SUBSTRINg('abcd',2,1) FROM `students`:b
-- ltrim()去掉左空格，rtrim，trim去掉左右端空格
SELECt TRIm(' ds ') FROM `students`
dual亚元表,测试表
#将TEXT的第一个字母小写输出
SELECt CONCAt(LCASE(SUBSTRINg('TEXT',1,1)),SUBSTRINg('TEXT',2)) FROM DUAL
#数学相关函数
-- abs()绝对值
select abs(-10)from dual;
-- bin()转二进制
SELECt bin(10)FROM DUAL;
-- ceiling()向上取整
SELECt ceiling(1.1)FROM DUAL;
-- conv(num,from_base,to_base)将num从from_base进制转为to_base进制
SELECt conv(11,2,10)FROM DUAL;
-- floor()向下取整
SELECt floor(1.1)FROM DUAL;
-- format(number,decimal_places)保留decimal_places个小数位的number
SELECt format('1.12345',2)FROM DUAL;
-- hex()10转16进制
SELECt hex(10)FROM DUAL;
-- least(n1,n2,n3,nn)求最小数值
SELECt least(1,2,-1,0.8)FROM DUAL;
-- mod(a,b)a对b求余
SELECt mod(10,3)FROM DUAL;
-- rand(seed)返回0~1.0的随机数
SELECt rand()FROM DUAL;
放seed种子之后他就是固定的了随机值范围不变
SELECt RAND(5)FROM DUAL;