MySQL——数据库 ——增删改查简单程序代码

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`
WHERE  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