*博客主页——小杰同学要加油的博客主页
*欢迎关注点赞收藏留言
*刚刚学习,如## 标题果有不对的地方,请大佬斧正!
文章目录
-----------------------------------------------------------------------------------------------------
1.对表的操作
-----------------------------------------------------------------------------------------------------
2.修改表列ALTER操作
简单介绍:
相关代码:
ALTER TABLE t7 (添加列)
ADD image VARCHAR(25)
NOT NULL DEFAULT ''
AFTER RESUME
ALTER TABLE t7(改变列)
MODIFY job VARCHAR(100)
NOT NULL DEFAULT ''
ALTER TABLE t7(删除列)
DROP sex
修改表名:RENAME TABLE t7 TO t71
修改表的配置信息:ALTER TABLE t71 CHARACTER SET utf8
修改列:ALTER TABLE t71
CHANGE `name` `name_wangjie` VARCHAR(64)
NOT NULL DEFAULT ''
其中:
1.NOT NULL 表明该列数据不可以为空
2. DEFAULT ’ ’ 列数据默认的是‘’
3. AFTER RESUME 添加的列在RESUME 后面
-----------------------------------------------------------------------------------------------------
3.INSERT添加表数据操作
简单介绍:
相关代码:
INSERT INTO t71(`id`,`name_wangjie`,`sex`,`birthday`,`job`,`salary`,`resume`)
VALUES(2008114132,'wangjie','男','2002-09-20','程序员',20000.4,'本科毕业');
:
-----------------------------------------------------------------------------------------------------
4.UPDATE修改表数据操作
简单介绍:
相关代码:
UPDATE t71 SET salary = 6000
[WHERE id = 123](只修改id=123的数据)
;
-----------------------------------------------------------------------------------------------------
5.DELETE对表数据的操作
简单介绍:
相关代码:
DELETE FROM t71
WHERE id = 20081141;
DELETE FROM T71(删除表中所有数据)
:
-----------------------------------------------------------------------------------------------------
6.SELECT操作(重点)
简单介绍
相关代码:
CREATE TABLE student(
id INT NOT NULL DEFAULT 1,
`name` VARCHAR(20) NOT NULL DEFAULT '',
`chinese` FLOAT NOT NULL DEFAULT 0.0,
`english` FLOAT NOT NULL DEFAULT 0.0,
`math` FLOAT NOT NULL DEFAULT 0.0);
-- drop table student
INSERT INTO student(id,`name`,`chinese`,`english`,`math`)
VALUES(1,'wangjie',80,80,80);
INSERT INTO student(id,`name`,`chinese`,`english`,`math`)
VALUES(2,'xiaogu',90,90,90);
INSERT INTO student(id,`name`,`chinese`,`english`,`math`)
VALUES(3,'xiaxueyang',70,70,70);
INSERT INTO student(id,`name`,`chinese`,`english`,`math`)
VALUES(4,'xiaowang',60,60,60);
SELECT *FROM student
结果演示:
SELECT english FROM student
结果演示:
SELECT DISTINCT english FROM student
小练习:
SELECT `name`,chinese+english+math FROM student
SELECT `name`,chinese+english+math+10 FROM student
SELECT `name`,(chinese+english+math+10) AS total_score FROM student
WHERE子句中运算符
1.SELECT * FROM student WHERE `name`= 'wangjie'
2.SELECT *FROM student WHERE english>80
3.SELECT *FROM student WHERE (chinese+english+math)>240
4.SELECT *FROM student WHERE (chinese+english+math)>240 AND `name` LIKE 'xiao%'
SQL通配符
使用ORDER BY排序查找结果
SELECT *FROM student ORDER BY math(默认是升序ASC)
SELECT *FROM student ORDER BY math DESC(降序)
SELECT `name`,(chinese+english+math) AS total_score FROM student ORDER BY total_score
以上PPT内容截取韩顺平老师的PPT!
如果本篇博客对你有帮助的话,点个赞呗!让博主有动力去继续创作下去!