对表格结构的修改
-- 创建表 (创建user表)
CREATE TABLE `user` (`id` int NOT NULL AUTO_INCREMENT ,
`name` varchar(10) COLLATE utf8_general_ci NOT NULL ,
`age` decimal(6,3) NULL ,
`img` blob NULL ,
PRIMARY KEY (`id`)
)
DEFAULT CHARACTER SET =utf8
;
desc user;
-- 修改列ALTER TABLE `user`
MODIFY COLUMN `name` varchar(20),
MODIFY COLUMN `age` DECIMAL(6,2);
-- 修改列名 dept之前 deptId修改后
ALTER TABLE `user`
CHANGE dept deptId VARCHAR(10);
-- 增加列
ALTER TABLE USER
ADD COLUMN `sexId` CHAR(1) NOT NULL;
-- 删除列ALTER TABLE USER
DROP COLUMN `sexId`;
-- 删除表结构drop TABLE user1;
对表格数据的操作(增删改查)
-- 返回当前时间
SELECT CURDATE() from DUAL
select * from `user`
-- 插入数据 int型不用加引号,字符串加引号
insert into `user`
VALUES(DEFAULT,"嘻嘻",6.6,NULL);
-- 插入数据 可以是个表
INSERT INTO `user1`
SELECT * from `user`
-- 修改数据 <>表示不等于
UPDATE `user`
SET `name`="张三",
`age`=3.5
WHERE id=14;-- != > < =
UPDATE `user`
SET `name`="张洒",
`age`=5.5
WHERE (`name`="张三" AND `age`=3.5) OR `age`=4.5
SELECT * FROM `user`
-- 删除数据】
DELETE FROM `user`
WHERE id=1
-- 用于清空整个表
TRUNCATE TABLE `user` where id=13
-- 复制一个table 没有主键 不自动增长
CREATE TABLE `user1`
SELECT * FROM `user`
-- 排序
SELECT * FROM `user`
WHERE 1=1
ORDER BY age DESC-- 放在where条件的后面 排序ESC顺序 DESC 倒叙 age根据
SELECT id,name,age,img,ASCII(name) FROM `user`
WHERE 1=1
ORDER BY age DESC,name DESC
-- 查找排序 ASCII升序 降序
SELECT * ,ASCII(name) FROM `user`
WHERE 1=1
ORDER BY age DESC,name ASC
-- 去重复数据
SELECT DISTINCT name,age,img FROM `user`
-- 范围查询 in 、 not in
SELECT * from `user`
where name in("琪琪","皮皮");
SELECT * from `user`
where name not in("琪琪","皮皮");
SELECT * from `user`
where age BETWEEN 3.5 and 6.6
-- 模糊查询 %人一多的字符 _代表一个字符
SELECT * from `user`
WHERE `NAME` LIKE '张%'
-- 查询带 三 的
SELECT * from `user`
WHERE `NAME` LIKE '%三%'
-- 子查询
SELECT * FROM dept
SELECT
id,name,age,img,
( SELECT deptDesc FROM dept
where deptId=`user`.deptId) as dept-- as 别名
FROM `user`
SELECT
id,name,age,img,
( SELECT deptDesc FROM dept
where deptId=`user`.deptId) -- as 别名
FROM `user`
WHERE ( SELECT deptDesc FROM dept
where deptId=`user`.deptId) LIKE '人%'
-- 查找 出IT部
SELECT
id,name,age,img,
( SELECT deptDesc FROM dept
where deptId=`user`.deptId) -- as 别名
FROM `user`
WHERE deptId=( SELECT deptId FROM dept
where deptDesc LIKE 'IT%')
-- 查找出人事部和IT部 使用范围查找 in 词语IN是=ANY的别名
SELECT
id,name,age,img,
( SELECT deptDesc FROM dept
where deptId=`user`.deptId) -- as 别名
FROM `user`
WHERE deptId =ANY ( (SELECT deptId FROM dept -- in 等同于=ANY
where deptDesc LIKE 'IT%' or deptDesc LIKE '人事%'))
-- FROM子句中的子查询 把结果当做一个表 再起一个别名
SELECT * FROM
(SELECT
id,name,age,img,
( SELECT deptDesc FROM dept
where deptId=`user`.deptId) -- as 别名
FROM `user`
WHERE deptId =ANY ( (SELECT deptId FROM dept -- in 等同于=ANY
where deptDesc LIKE 'IT%' or deptDesc LIKE '人事%')) )as temp
-- 限制行索引 查取最大的两个
SELECT * FROM user ORDER BY age DESC LIMIT 0,2