abstract：-- 查看表结构DESC `user`-- 增加数据INSERT INTO `user`(`name`,`sex`,`age`,`email`,`password`,`status`)VALUES('Jerry','0','15','Jerry@qq.com',SHA1(123123),0)-- 批量增加
-- 查看表结构
DESC `user`
-- 增加数据
INSERT INTO `user`(`name`,`sex`,`age`,`email`,`password`,`status`)VALUES('Jerry','0','15','Jerry@qq.com',SHA1(123123),0)
-- 批量增加(1)
INSERT `user` (`name`,`sex`,`age`,`email`,`password`,`status`,`create_time`)VALUES('Amy', 1, 18, 'Amy@qq.com', SHA1(123123), 0, 1556801071), ('Tom', 0, 28, 'Tom@qq.com', SHA1(123123), 0, 1556801071), ('Jhon', 1, 25, 'Jhon@qq.com', SHA1(123123), 0, 1556801071), ('Jack', 1, 19, 'Jack@qq.com', SHA1(123123), 0, 1556801071)
-- 更新语句
UPDATE `user` SET `status` = 1 WHERE `name` = 'Tom'; -- 更新语句必须带WHERE条件 UPDATE `user` SET `status` = 0,`create_time` = 1556801222 WHERE `id` = 1;
-- 查询语句
SELECT * FROM `user` -- 查询表中所有数据 SELECT `name`,`email`,`status` FROM `user` -- 指定表中字段查询 SELECT `name`,`status` FROM `user` WHERE `status` = 1 -- 指定条件查询 SELECT * FROM `user` where `id` >= 1 AND `id` <= 3 -- 比较查询 SELECT * FROM `user` where `id` >= 1 ORDER BY `id` DESC -- 降序查询 SELECT * FROM `user` where `id` >= 1 ORDER BY `id` ASC LIMIT 0,3 -- 指定返回多少条数据 SELECT count(*) as `total` FROM `user` -- 返回记录总数 SELECT CONCAT(`name`,' : ',`email`) as `lists` FROM `user` -- 字段合并查询1 SELECT CONCAT_WS(' : ',`name`,`email`) as `lists` FROM `user` -- 字段合并查询2
-- 删除语句
DELETE FROM `user` WHERE `status` = 1 -- 删除语句必须带WHERE条件
-- 重置id字段的顺序
ALTER TABLE `user` DROP `id` -- 删除id字段 ALTER TABLE `user` ADD `id` int(11) PRIMARY KEY AUTO_INCREMENT FIRST -- 新增id字段并将字段设置为第一列 ALTER TABLE `user` ADD `position` VARCHAR(50) NOT NULL COMMENT '位置显示表' ALTER TABLE `user` CHANGE `position` `left` VARCHAR(20) NOT NULL COMMENT '左侧菜单' -- 重命名列名
Correcting teacher：查无此人Correction time：2019-05-05 10:19:46
Teacher's summary：完成的不错。sql语句最多的就是增删查改。继续加油。
