• 增加数据的INSERT语句:关键字INSERT,主要功能是 给数据库表增加记录。
• 修改数据的UPDATE语句:关键字是UPDATE,主要功能是 修改数据库表中已有的记录。可 以根据条件去做修改。
• 删除数据的DELETE语句 :关键字DELETE,主要功能室删除 数据库表中已有的记录。可以依 照条件去做修改。
• 查询数据的SELECT语句:用途是查询数据库数据,如SELECT语句。是SQL语句 中最核心、最重要的语句,也是使用频率最高的语句。其中,可以根据表的结构和关系分为单表查询和多 表联查。
• Python对数据库的查询
• Python对数据库的添加
• Python对数据库的修改
• Python对数据库的删除
CREATE TABLE `student` ( `stu_id` int(11) NOT NULL AUTO_INCREMENT, `stu_name` varchar(20) NOT NULL, `stu_sex` varchar(2) DEFAULT NULL, `stu_birthday` datetime DEFAULT NULL, `stu_phone` char(11) DEFAULT NULL, PRIMARY KEY (`stu_id`), UNIQUE KEY `stu_phone` (`stu_phone`) )
INSERT INTO `student` VALUES ('1', '哈哈', '男', '2021-06-02 15:23:58', '18888888888'); INSERT INTO `student` VALUES ('2', '小王', '男', '2021-06-01 15:24:26', '19999999999'); INSERT INTO `student` VALUES ('3', '小张', '女', '2021-06-02 15:24:45', '17777777777'); INSERT INTO `student` VALUES ('4', '大白', '男', '2021-06-09 15:25:06', '18888888889'); INSERT INTO `student` VALUES ('5', '小明', '女', '1989-07-07 15:25:25', '16666666666');
update new_stu2 set stu_phone = '33333333333' where stu_name = '李四'; update new_stu2 set stu_phone = '44444444444' where stu_id >=102 and stu_id <=104; update new_stu2 set stu_phone = '66666666666' where stu_id between 102 and 104; update new_stu2 set stu_phone = '66777766666' where stu_id != 100;
delete from student delete from student where stu_name = '王五' delete from student where stu_id >= 109 insert into student(stu_name) values('哈哈');
-- 男女同学 最高分,最低分,总分,平均分,人数 select stu_sex,max(stu_score),min(stu_score),sum(stu_score),avg(stu_score), count(1) from student group by stu_sex -- 查询 -- * 号的含义 student 这张表所有的字段 select * from student; -- 选择特定字段进行查询 select stu_birthday,stu_name,stu_sex from student -- 给查询字段起别名 select stu_birthday as '学生生日', stu_name '学生姓名',stu_sex sex from student -- DISTINCT 关键词 select distinct stu_sex,stu_birthday from student -- 查询条件 select * from student where stu_sex = '男' select * from student where stu_id > 3 -- 找到出生日期大于1990-1-1学生 select * from student where stu_birthday < '1990-1-1' -- 模糊查询 select * from student where stu_name like '%白%' select * from student where stu_name like '小%' -- % 任意多的任意字符 -- _ 一个任意字符 select * from student where stu_name like '小__' -- 编号 2,4,6,8,10 学生查询 in 关键词 select * from student where stu_id in (2,4,6,8,10,100,100000,100000000)
import pymysql # 链接数据库 db = pymysql.connect(host='192.168.44.142',user='root',password='123456',db='yanandaxue',port=3306) print(db) # 获取游标 cur = db.cursor() # 编写sql语句 sql = 'select * from student' # 执行sql语句 cur.execute(sql) # 获取到执行结果 -- 虚拟表 rs = cur.fetchall() # 遍历 结果-- 虚拟表 for row in rs: sid = row[0] sname = row[1] sex = row[2] birthday = row[3] phone = row[4] score = row[5] print(sid,sname,sex,birthday,phone,score) # 释放资源 db.close()
欢迎大家加我微信学习讨论