mysql增删改查和基本函数

增加列类型：

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<chinese AND 
`name` LIKE '韩%';#'韩%'~以韩开头的字符串

#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;