MySQL——数据库 ——简单程序代码大全

CREATE TABLE NewTable (
id int NOT NULL AUTO_INCREMENT ,
name varchar(10) CHARACTER SET utf8 NOT NULL ,
age decimal(6,4) NOT NULL ,
img blob NULL ,
PRIMARY KEY (id)
)
DEFAULT CHARACTER SET=utf8
DESC NewTable;
– 修改表元素
ALTER TABLE NewTable
MODIFY COLUMN name varchar(30),
MODIFY COLUMN name DECIMAL(6,3);
– 修改列名
ALTER TABLE user1
CHANGE dept deptId VARCHAR(10)
– 添加行
ALTER TABLE NewTable
ADD COLUMN sexId CHAR(1) not NULL;
– 删除行
ALTER TABLE NewTable
DROP COLUMNsexId;
– 删除表结构
drop tableUSER1;
select CURDATE() from DUAL;
DESC newtable
select*from newtable

– 插入数据
INSERT INTOnewtableVALUES(DEFAULT,“张3张”,8.5,NULL);
– 插入一个表的数据
INSERT INTO user1
selectfrom newtable
– 修改数据
– 有条件
UPDATE newtable
set name=“李we”,
age=5.3
WHERE name=“张三”
AND age=4.5 ;-- != = <>
selectfrom newtable
WHERE (name="张三"and age=3.5)
OR age=3.5 ;
– 删除表数据内容
DELETE FROM USER1
WHERE ID=1
selectfrom newtable
WHERE ID=“1”
– 性能好效率高 清空表
TRUNCATE TABLEnewtable
– 复制表 但是没有主键递增
CREATE TABLE user1
selectfrom newtable
– 排序
selectfrom newtable
WHERE 1=1
ORDER BY age DESC , id DESC
– 查询排序 desc降序 asc升序
SELECT ,ASCII(NAME) FROM newtable WHERE 1=1
ORDER BY age DESC , id DESC
– 去除重复数据
SELECT DISTINCT NAME ,age,img FROMnewtable
– 范围查询 in notin
selectfrom newtable
WHERE name not in(“张3”,“张”);
– 范围查询 BETWEEN
SELECTfromnewtable
WHERE age BETWEEN 3.5 and 4.6
– 模糊查询 %代表任意多个字符 _一个字符
SELECT*FROMnewtable
WHERE name LIKE’%张%’
– 子查询
SELECT /INDEX/from
(SELECT ,
– id,name,age,img,
( SELECT deptDesc FROM dept
WHERE deptId=user1.deptId)as dept
fromuser1
WHERE deptId =any( SELECT deptId FROM dept – in 等同于 = any
where deptDesc like ‘人事%’ or deptDesc like ‘财务%’))as zyff
– or deptId=( SELECT deptId FROM dept
– where deptDesc like ‘财务’)
SELECTFROM user1
– 限制索引前两个
SELECTFROMuser1 ORDER BY age DESC LIMIT 0,2

–CASE WHEN
SELECT*, CASE WHEN age<5THEN “儿童” ELSE “少年” END AS part from user1

– 汉字长度
SELECT*,LENGTH(deptDesc) FROM dept
– 连接字符串
SELECT CONCAT(deptId,":",deptDesc)d FROM dept
– 3,0插入字符串 3,3替换3位
SELECT INSERT(‘Quadratic’, 3, 0, ‘What’);
– 截取字符串
SELECT left(‘12345’,5);
SELECT RIGHT(‘2135435’,5);
– 1,5从第几位到 后几位 -3倒数后几位 MID与SUBSTRING同义词
SELECT SUBSTRING(‘012345678’,1,5);
SELECT MID(‘012345678’,1,5);
– 查找字符串 2.从第6位开始查找
SELECT LOCATE(‘bar’,‘footbarbar’)
SELECT LOCATE(‘bar’,‘footbarbar’,6)
– 1.转换成小写 2.转换成大写
SELECT LOWER(‘adsadQWEQ1232’)
SELECT UPPER(‘asdasdASDASD1231’)
– 去除开头空格 LTRIM去除左空格RTRIM右空格
SELECT TRIM(’ asdf dfasd ‘)
SELECT RTRIM(’ asdf dfasd ‘)
SELECT LTRIM(’ asdf dfasd ‘)
– 替换字符串
SELECT REPLACE(’ df sdf sdaf dsf ‘,’ ‘,’’);
– 重复3次
SELECT REPEAT(‘w’,4)
SELECT REPEAT(deptDesc,4) FROM dept
– 字符串反转
SELECT REVERSE(deptDesc)FROM dept
– 数值函数
SELECT POW(2,3)
SELECT RAND()
SELECT FLOOR(7+(RAND()*6))
– 取随机数据
SELECT *FROM user1
WHERE 1
ORDER BY RAND() LIMIT 3;
– 四舍五入
SELECT ROUND(-1.5)
SELECT FLOOR(1.5)
SELECT ROUND(5.553454,3)
– 当前日期
SELECT CURDATE()
SELECT CURRENT_DATE()
SELECT CURRENT_TIME()
SELECT CURRENT_TIMESTAMP()
SELECT YEAR(CURDATE())
SELECT MONTH(CURDATE())
SELECT YEARWEEK(CURDATE())
SELECT YEARWEEK(‘2016&01+23’)
– 设置日期
SELECT ADDDATE(CURDATE(),-50)
– 有问题
SELECT CURDATE()-30
– 分钟加减
SELECT ADDTIME(CURRENT_TIME(),’-01:00:00’);
SELECT DATE_ADD(CURRENT_TIMESTAMP(),INTERVAL ‘-1 1’ DAY_HOUR);
– 相差天数
SELECT DATEDIFF(CURRENT_DATE(),‘20170125’)
– 格式化日期
SELECT DATE_FORMAT(‘1997-10-04 22:23:00’, ‘%H:%i:%s’);
– 判断周几
SELECT
case WHEN
DATE_FORMAT(CURDATE(),’%w’)=0
THEN ‘周日’
WHEN
DATE_FORMAT(CURDATE(),’%w’)=1
THEN ‘周一’
WHEN
DATE_FORMAT(CURDATE(),’%w’)=2
THEN ‘周二’
WHEN
DATE_FORMAT(CURDATE(),’%w’)=3
THEN ‘周三’
WHEN
DATE_FORMAT(CURDATE(),’%w’)=4
THEN ‘周四’
WHEN
DATE_FORMAT(CURDATE(),’%w’)=5
THEN ‘周五’
WHEN
DATE_FORMAT(CURDATE(),’%w’)=6
THEN ‘周六’
end weekNo
– 当前月份的第几天
SELECT DAYOFMONTH(CURDATE());
– 当前月第几周
SELECT DAYOFWEEK(CURDATE());
– 当前年第几天
SELECT DAYOFYEAR(CURDATE())
SELECT EXTRACT(DAY_MINUTE FROM CURRENT_TIMESTAMP());
– 当前月份的最后一天
SELECT LAST_DAY(CURDATE())
SELECT SYSDATE()
– 1.name 2.date
INSERT INTO ttt
VALUES(‘zz’,SYSDATE())
SELECT *FROM ttt

– 一些数学表达式
SELECT COUNT(*) FROMuser1

SELECT sum(age)FROM user1;
SELECT AVG(age)FROM user1;
SELECT MIN(age)FROM user1;
SELECT MAX(age)FROM user1;