mysql增删改查 表格_MySQL数据库如何创建表格以及增删改查

对表格结构的修改

-- 创建表 (创建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