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 COLUMN`sexId`;
-
- 删除表结构
drop table`USER1`;
select CURDATE() from DUAL;
DESC newtable
select*from `newtable`
-- 插⼊数据
INSERT INTO`newtable`VALUES(DEFAULT,"张3张",8.5,NULL);
-- 插⼊⼀个表的数据
INSERT  INTO user1
select*from `newtable`
-- 修改数据
-
- 有条件
UPDATE `newtable`
set `name`="李we",
age=5.3
WHERE  name="张三"
AND age=4.5  ;-- != = <>
select*from `newtable`
WHERE  (name="张三"and age=3.5)
OR age=3.5  ;
-- 删除表数据内容
DELETE FROM `USER1`
bigdecimal striptrailingzerosWHERE  ID=1
select*from `newtable`
WHERE  ID="1"
--  性能好效率⾼清空表
TRUNCATE TABLE`newtable`
-- 复制表但是没有主键递增
CREATE TABLE `user1`
select*from `newtable`
-- 排序
select*from `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 FROM`newtable`
-- 范围查询  in notin
select*from `newtable`
WHERE name not in("张3","张");
-- 范围查询 BETWEEN
SELECT*from`newtable`
WHERE age BETWEEN 3.5 and 4.6
-- 模糊查询 %代表任意多个字符 _⼀个字符
SELECT*FROM`newtable`
WHERE `name` LIKE'%张%'
-- ⼦查询
SELECT /*INDEX*/*from
(SELECT *,
-- id,name,age,img,
( SELECT deptDesc FROM dept
WHERE deptId=`user1`.deptId)as dept
from`user1`
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 '财务')
SELECT*FROM `user1`
-- 限制索引前两个
SELECT*FROM`user1` 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
floor英语-- 分钟加减
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 '周五'
mysql面试题大全
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
display none影响布局吗
-- ⼀些数学表达式
SELECT COUNT(*) FROM`user1`
SELECT sum(age)FROM `user1`;java原地删除数组元素
SELECT AVG(age)FROM `user1`;
SELECT MIN(age)FROM `user1`;
SELECT MAX(age)FROM `user1`;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。