
一、表结构与记录 create table students( id int auto_increment primary key, `name` varchar(200), grade int, course varchar(200) ); insert into students(name, grade, course) values("张三", 60, "数学"); insert into students(name, grade, course) values("李四", 70, "数学"); insert into students(name, grade, course) values("王五", 80, "数学"); insert into students(name, grade, course) values("赵六", 90, "数学"); insert into students(name, grade, course) values("孙七", 85, "数学"); insert into students(name, grade, course) values("周八", 75, "数学"); insert into students(name, grade, course) values("吴九", 65, "数学"); insert into students(name, grade, course) values("郑十", 55, "数学"); insert into students(name, grade, course) values("张三", 78, "英语"); insert into students(name, grade, course) values("李四", 89, "英语"); insert into students(name, grade, course) values("王五", 52, "英语"); insert into students(name, grade, course) values("赵六", 86, "英语"); insert into students(name, grade, course) values("孙七", 35, "英语"); insert into students(name, grade, course) values("周八", 85, "英语"); insert into students(name, grade, course) values("吴九", 85, "英语"); insert into students(name, grade, course) values("郑十", 61, "英语"); insert into students(name, grade, course) values("王五", 62, "语文"); insert into students(name, grade, course) values("赵六", 83, "语文"); insert into students(name, grade, course) values("孙七", 77, "语文"); insert into students(name, grade, course) values("周八", 100, "语文"); insert into students(name, grade, course) values("吴九", 92, "语文"); insert into students(name, grade, course) values("郑十", 100, "语文"); 二、查询每个学生成绩最高的记录,包括 id、姓名、课程名、成绩 1. 网上一般直接用max或min函数来求 select `name`, course, max(grade) as max_grade from students group by `name`; name course max_grade 吴九 数学 92 周八 数学 100 孙七 数学 85 张三 数学 78 李四 数学 89 王五 数学 80 赵六 数学 90 郑十 数学 100 这样有一个问题,就是group by默认是取每个分组的第一条记录,所以max_grade虽然计算的是该组中最大的成绩,但是course并不是这个成绩对应的实际的课程名,这样就得到了不一致的数据。比如说吴九最高分92是语文,但是查询出来的结果是数学。如果要查询是字段是分组的字段以及最值的字段,那么这个方法是可行的,但是一旦要查询其他的信息,这个方法就错了。 2. 正确语句 select s1.`name`, s1.course, s1.grade from students s1, (select `name`, max(grade) as max_grade from students group by `name`) as s2 where s1.name=s2.name and s1.grade=s2.max_grade; name course grade 王五 数学 80 赵六 数学 90 孙七 数学 85 张三 英语 78 李四 英语 89 周八 语文 100 吴九 语文 92 郑十 语文 100 先用子查询查询出每组的最值以及分组的字段信息,然后再用where条件进行筛选,这样就可以查询出正确的结果了。 三、查询分组里面的前N条记录----查询每门科目中,成绩为前两名的学生姓名与成绩 1. 成绩相同并且排名在前可以并列 select s1.course, s1.name, s1.grade from students s1 where (select count(distinct s2.grade) from students s2 where s1.course=s2.course and s2.grade>s1.grade) < 2 /*当整张表中相同课程里成绩高于s1的人数小于2时,证明s1是前两名,加distinct是为了防止前面有并列的人*/ order by s1.course; cours name grade 数学 赵六 90 数学 孙七 85 英语 李四 89 英语 赵六 86 语文 周八 100 语文 吴九 92 语文 郑十 100 在语文中,一共查询出来了三个人,是因为周八跟郑十是并列第一,所以第一名有两个人,第二名就是吴九了,也就是成绩可以并列。如果在子查询中没有加distinct,那么查询出来的结果就没有吴九,因为语文成绩比它高的已经有两个人了,分别是第一第二名。 2. 成绩相同的不能并列,只取其中一个 select s1.course, s1.name, s1.grade from (select * from students group by grade,course) as s1 where (select count(distinct s2.grade) from students s2 where s1.course=s2.course and s2.grade>s1.grade) < 2 order by s1.course; course name grade 数学 孙七 85 数学 赵六 90 英语 李四 89 英语 赵六 86 语文 周八 100 语文 吴九 92 与上面的区别在于对s1进行了筛选,也就是对查询出的记录按照(grade,course)进行了分组,相当于grade与course相同的记录只查询出来一条。 3. 同组去重,grade与course相同的只取一条记录 方法一select distinct grade, course from students; 方法二select * from students group by grade,course order by course;
一、内外连接 内连接: 只连接匹配的行 左外连接: 包含左边表的全部行(不管右边的表中是否存在与它们匹配的行),以及右边表中全部匹配的行 右外连接: 包含右边表的全部行(不管左边的表中是否存在与它们匹配的行),以及左边表中全部匹配的行 全外连接: 包含左、右两个表的全部行,不管另外一边的表中是否存在与它们匹配的行。 交叉连接: 生成笛卡尔积-它不使用任何匹配或者选取条件,而是直接将一个数据源中的每个行与另一个数据源的每个行都一一匹配 drop table users; drop table address; create table users( id int primary key auto_increment, `name` varchar(30) ); create table address( id int primary key auto_increment, uid int, city varchar(30) # foreign key(uid) references users(id) ); insert into users (`name`) values("张三"); insert into users (`name`) values("李四"); insert into users (`name`) values("王五"); insert into address (`uid`,`city`) values(1,"上海"); insert into address (`uid`,`city`) values(2,"北京"); insert into address (`uid`,`city`) values(5,"武汉"); select `name`,city from users inner join address on users.id=address.uid;#内连接 select `name`,city from users left join address on users.id=address.uid;#左外连接 select `name`,city from users right join address on users.id=address.uid;#右外连接 select `name`,city from users full join address;#全连接 select `name`,city from users cross join address;#交叉连接,类似于笛卡儿积 1.内连接(inner join)——左右两边都匹配才连接 select `name`,city from users inner join address on users.id=address.uid; 2.左外连接(left join)——左边的表全部都保存下来,右边可以为空 select `name`,city from users left join address on users.id=address.uid; 3.右外连接(right join)——右边的表全部都保存下来,左边可以为空 select `name`,city from users right join address on users.id=address.uid; 4.全连接与交叉连接——两个都是与笛卡儿积类似,都两两相连 二、数据库事务的四大特性(ACID) 1.原子性(Atomicity) 原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生。 2.一致性(Consistency) 如果事务执行之前数据库是一个完整的状态,那么事务结束后,无论事务是否执行成功,数据库仍然是一个完整的状态。数据库的完整状态:当一个数据库中的所有的数据都符合数据库中所定义的所有约束,此时可以称数据库是一个完整的状态。拿转账来说,假设用户A和用户B两者的钱加起来一共是5000,那么不管A和B之间如何转账,转几次账,事务结束后两个用户的钱相加起来应该还得是5000,这就是事务的一致性。 3.隔离型(Isolation) 多个用户并发访问数据库时,一个用户的事务不能被其他用户的事务所干扰,多个并发事务之间数据要相互隔离。 4.持久性(Durability) 指一个事务一旦被提交,他对数据库的影响是永久性的。 三、事务级别 1.不同事务级别可能带来的问题 脏读脏数据所指的就是未提交的数据。也就是说,一个事务正在对一条记录做修改,在这个事务完成并提交之前,这条数据是处于待定状态的(可能提交也可能回滚),这时,第二个事务来读取这条没有提交的数据,并据此做进一步的处理,就会产生未提交的数据依赖关系。这种现象被称为脏读。 salary=1000 T1 T2 read(salary)——1000 write(salary+2000)——3000 read(salary)——3000 rollback 不可重复读一个事务先后读取同一条记录,而事务在两次读取之间该数据被其它事务所修改,则两次读取的数据不同,我们称之为不可重复读。 salary=1000 T1 T2 read(salary)——1000 read(salary)——1000 write(salary+2000)——3000 commit read(salary)——3000 幻读一个事务按相同的查询条件重新读取以前检索过的数据,却发现其他事务插入了满足其查询条件的新数据,这种现象就称为幻读。 T1 T2 select * from users where age between 10 and 30; insert into users(name,age) values("Bob",15); select * from users where age between 10 and 30; 脏读与不可重复读的区别 脏读是读取了未提交的数据。一个事务正在对一条记录做修改,在这个事务完成并提交之前,这条数据是处于待定状态的(可能提交也可能回滚),这时,第二个事务来读取这条没有提交的数据,并据此做进一步的处理,就会产生未提交的数据依赖关系。这种现象被称为脏读。 不可重复读是在两次读取之间有其它事务对数据做了操作。 不可重复读与幻读的区别 不可重复读的重点是修改,同样的条件, 你读取过的数据, 再次读取出来发现值不一样了。 幻读的重点在于新增或者删除 (数据条数变化)。同样的条件, 第1次和第2次读出来的记录数不一样。 2.事务的隔离级别 √: 可能出现 ×: 不会出现 脏读 不可重复读 幻读 Read uncommitted(读未提交) √ √ √ Read committed(读提交) × √ √ Repeatable read(重复读) × × √ Serializable(序列化) × × × 四、三大范式 第一范式(1NF):数据表中的每一列(每个字段)必须是不可拆分的最小单元,也就是确保每一列的原子性; 第二范式在第一范式的基础上更进一层,第二范式需要确保数据库表中每一列都和主键相关,而不能只与主键的某一部分相关(主要针对联合主键而言)。也就是说在一个数据库表中,一个表中只能保存一种数据,不可以把多种数据保存在同一张数据库表中。 第三范式需要确保数据不能存在传递关系,即每个属性都跟主键有直接关系而不是间接关系。 简而言之:第1范式:每个表中都有1列,并且该列是不可拆分的最小单元第2范式:1张表只描述一件事情。如包含用户-订单-商品信息表,应该拆分为3个表。第3范式:用外键做表的关联。比如: Student表(学号,姓名,年龄,性别,所在院校,院校地址,院校电话) 就存在上述关系:学号--> 所在院校 --> (院校地址,院校电话) 这样的表结构,我们应该拆开来:(学号,姓名,年龄,性别,所在院校)--(所在院校,院校地址,院校电话)参考链接:https://www.cnblogs.com/1906859953Lucas/p/8299959.html 五、五大约束 1.primary KEY:设置主键约束;2.UNIQUE:设置唯一性约束,不能有重复值;3.DEFAULT 默认值约束,height DOUBLE(3,2)DEFAULT 1.2 # height不输入是默认为1,24.NOT NULL:设置非空约束,该字段不能为空;5.FOREIGN key :设置外键约束。 六、常考语法 假设orders表中有如下数据:{ cust_id: "abc123", ord_date: ISODate("2012-11-02T17:04:11.102Z"), status: 'A', price: 50, items: [ { sku: "xxx", qty: 25, price: 1 }, { sku: "yyy", qty: 25, price: 1 } ]} 1.统计orders表中记录数 db.orders.aggregate( [ { $group: { _id: null, count: { $sum: 1 } } } ] ) 类似mysql:SELECT COUNT(*) AS count FROM orders 2.计算orders表中所有记录的price之和 db.orders.aggregate( [ { $group: { _id: null, total: { $sum: "$price" } } } ] ) 类似mysql:SELECT SUM(price) AS total FROM orders 3.计算cust_id相同的所有记录的price之和 db.orders.aggregate( [ { $group: { _id: "$cust_id", total: { $sum: "$price" } } } ] ) 类似mysql:SELECT cust_id,SUM(price) AS total FROM orders GROUP BY cust_id 4.对(cust_id,ord_date)进行分组,并计算每组里面的price之和 db.orders.aggregate( [ { $group: { _id: { cust_id: "$cust_id", ord_date: { month: { $month: "$ord_date" }, day: { $dayOfMonth: "$ord_date" }, year: { $year: "$ord_date"} } }, total: { $sum: "$price" } } } ] ) 类似mysql:SELECT cust_id,ord_date,SUM(price) AS total FROM orders GROUP BY cust_id,ord_date 5.当cust_id相同的记录数大于1时,查询出该cust_id及其对应的记录数 db.orders.aggregate( [ { $group: { _id: "$cust_id", count: { $sum: 1 } } }, { $match: { count: { $gt: 1 } } } ] ) 类似mysql:SELECT cust_id,count(*) FROM orders GROUP BY cust_id HAVING count(*) > 1 6.对(cust_id,ord_date)进行分组,并计算每组里面的price之和,返回price之和大于250时的cust_id,ord_date以及price之和 db.orders.aggregate( [ { $group: { _id: { cust_id: "$cust_id", ord_date: { month: { $month: "$ord_date" }, day: { $dayOfMonth: "$ord_date" }, year: { $year: "$ord_date"} } }, total: { $sum: "$price" } } }, { $match: { total: { $gt: 250 } } } ] ) 类似mysql:SELECT cust_id,ord_date,SUM(price) AS total FROM orders GROUP BY cust_id,ord_date HAVING total > 250 7.计算status='A',且cust_id相同的记录的price之和 db.orders.aggregate( [ { $match: { status: 'A' } }, { $group: { _id: "$cust_id", total: { $sum: "$price" } } } ] ) 类似mysql:SELECT cust_id,SUM(price) as total FROM orders WHERE status = 'A' GROUP BY cust_id 8.计算status='A',且cust_id相同的记录的price之和,并且只返回price之和大于250的记录 db.orders.aggregate( [ { $match: { status: 'A' } }, { $group: { _id: "$cust_id", total: { $sum: "$price" } } }, { $match: { total: { $gt: 250 } } } ] ) 类似mysql:SELECT cust_id,SUM(price) as total FROM orders WHERE status = 'A' GROUP BY cust_id HAVING total > 250 9.对于每个唯一的cust_id,将与orders相关联的相应订单项order_lineitem的qty字段进行总计 db.orders.aggregate( [ { $unwind: "$items" }, { $group: { _id: "$cust_id", qty: { $sum: "$items.qty" } } } ] ) 类似mysql:SELECT cust_id,SUM(li.qty) as qty FROM orders o,order_lineitem li WHERE li.order_id = o.id GROUP BY cust_id 10.统计(cust_id,ord_date)分组的数量 db.orders.aggregate( [ { $group: { _id: { cust_id: "$cust_id", ord_date: { month: { $month: "$ord_date" }, day: { $dayOfMonth: "$ord_date" }, year: { $year: "$ord_date"} } } } }, { $group: { _id: null, count: { $sum: 1 } } } ] ) 类似mysql:SELECT COUNT(*) FROM (SELECT cust_id, ord_date FROM orders GROUP BY cust_id, ord_date) as DerivedTable 参考链接:https://www.cnblogs.com/ilikeballs/p/4341383.htmlhttps://blog.csdn.net/qq_33862644/article/details/79692652https://www.cnblogs.com/zhoujie/p/mongo1.html
1. 定义 定义一个列表的方法有三种: a = ['zhangsan', 18, 'male'] # 第一种,直接用[] b = list(['zhangsan', 18, 'male']) # 第二种,用list函数,也可以写成b = list([1, 2, 3]) b = a # 第三种,用一个列表初始化另一个列表 c = a + b # 与第三种类似,可以用两个列表相加来初始化另一个列表 print(c)# ['zhangsan', 18, 'male', 'zhangsan', 18, 'male'] 需要注意的是第三种方法的赋值是浅拷贝,所以在修改数据时可能会出现问题:: a = ['zhangsan', 18, 'male', 'handsome', 100] b = a #浅拷贝 print(a,b)# a=['zhangsan', 18, 'male', 'handsome', 100] b=['zhangsan', 18, 'male', 'handsome', 100] b[0]='lisi' print(a,b)# a=['lisi', 18, 'male', 'handsome', 100] b=['lisi', 18, 'male', 'handsome', 100] 可以看到,当修改了b里面的第一个元素的值以后,a的值也被修改了,因为a与b共用同一块内存空间,解决办法就是使用深拷贝,使用深拷贝时要引入copy模块: import copy a = ['zhangsan', 18, 'male', 'handsome', 100] b = copy.deepcopy(a) #深拷贝 print(a,b)# a=['zhangsan', 18, 'male', 'handsome', 100] b=['zhangsan', 18, 'male', 'handsome', 100] b[0]='lisi' print(a,b)# a=['zhangsan', 18, 'male', 'handsome', 100] b=['lisi', 18, 'male', 'handsome', 100] list中的变量可以是不同的数据类型,类似与C++中的数组,下标是从1开始,但是这个数组更灵活一些,可以存储各种数据类型的数据。 2.查 list的查找可以通过各种切片操作来实现,切片一个有三个参数,第一个参数是起始下标(包括该值),第二个是终止下标(不包括该值),第三个是步长(默认为1): a = ['zhangsan', 18, 'male', 'handsome', 100] # 切片方法 print(a[0]) # 取出下标为0的元素,zhangsan # print(a[10]) # 下标越界,会报错 print(a[1:3]) # 左边包括,右边不包括,[18, 'male'] print(a[1:-1]) # -1表示最后一个元素,由于右边不包括故不取最后一个值,即取到倒数第二个值,[18, 'male', 'handsome'] # 省略值表示全取 print(a[:]) # 从第一个开始取到最后一个,['zhangsan', 18, 'male', 'handsome', 100] print(a[1:]) # 取到最后一个值,[18, 'male', 'handsome', 100] print(a[:-1]) # 从第一个开始取到倒数第二个,['zhangsan', 18, 'male', 'handsome'] # 步长为负表示从右往左开始取值,第一个元素是起始下标(从左往右看时的下标) print(a[1:-1:1])# 从左到右数一下,从下标为1开始到最后一个元素(不包括)结束,[18, 'male', 'handsome'] print(a[1::2]) # 从左到右数两下,[18, 'handsome'] print(a[3::-2]) # 从右到左数两下,下标为3的元素是handsome,故['handsome', 18] print(a[2::-1]) # 从右到左数一下,下标为2的元素是male,故['male', 18, 'zhangsan'] 3.增 像列表添加元素主要有两个方法,append与insert: a = ['zhangsan', 18, 'male', 'handsome', 100] a.append('aaa') # append中的参数放的是要插入的值,默认插到列表的最后面,若append的是字典,会直接将整个字典插入到列表的表尾 print(a) # ['zhangsan', 18, 'male', 'handsome', 100, 'aaa'] a.insert(1,"bbb")# 第一个参数是插入的位置,第二个参数是插入的值 print(a) # ['zhangsan', 'bbb', 18, 'male', 'handsome', 100, 'aaa'] 还有一个是对列表的扩展,使用的是extend方法,extend方法的参数不能是单个的值: a = ['zhangsan', 18, 'male', 'handsome', 100] b = ['aaa','bbb'] a.extend(b) # 将b的内容加在a的后面,extend的参数不能是单个的值 print(a) # ['zhangsan', 18, 'male', 'handsome', 100, 'aaa', 'bbb'] b = {'zhang':11,'li':22} a.extend(b) # 将b的内容加在a的后面,如果参数是字典的话,只会将键加入列表中 print(a) # ['zhangsan', 18, 'male', 'handsome', 100, 'aaa', 'bbb', 'zhang', 'li'] 4.删 删除列表中的元素共有4种方法: a = ['zhangsan', 18, 'male', 'handsome', 100] a.remove('zhangsan') # remove中放的是要删除的内容 或者 a.remove(a[0]) print(a) # [18, 'male', 'handsome', 100] b = a.pop(1) # pop中放的是索引值,并返回删掉的值 若不指定就默认删除最后一个 print(a) # [18, 'handsome', 100] print(b) # male del a[0] #del既可以删除某个固定的内容,也可以删除整个列表 print(a) # ['handsome', 100] a.clear() # clear将整个列表置为空表 print(a) # [] del a # print(a) # 当a被删除以后再输出会报错 5.修改 修改与查是类似的,只需要将元素重新赋值即可: a = ['zhangsan', 18, 'male', 'handsome', 100] a[0]='lisi' 6.常见函数 count:统计某个元素出现次数 a = [1,2,3,4,3,5,2,3].count(3) print(a) # 3 index:获取某个元素第一次出现的下标 a = [1,2,3,4,3,5,2,3] print(a.index(3)) # 2 print(a.index(0)) # 元素不存在时会报错 reverse:逆序 a = [1,2,3,4,3,5,2,3] a.reverse() print(a) # [3, 2, 5, 3, 4, 3, 2, 1] sort与sorted:排序,默认为升序,sort是直接对原列表进行排序,sorted是返回排序后得到的新列表 a = [1,2,3,4,3,5,2,3] a.sort(reverse=True) #reverse默认为False即升序,reverse=True:降序 print(a) # [5, 4, 3, 3, 3, 2, 2, 1] b = sorted(a) # 默认为升序 print(b) # [1, 2, 2, 3, 3, 3, 4, 5] b = sorted(a, reverse=True) print(b) # [5, 4, 3, 3, 3, 2, 2, 1] 记录自己的python学习之路,错误的地方欢迎指正~~
一、数据类型初识 -数字,包括int(用%d输出),float(用%f输出)age=18-布尔,只有两种取值,True或Falseflag=True-字符串,在python中,字符串是一个类name='张三'-列表,列表里面的内容可以是不同的数据类型l=['张三',18,'李四']-元组,元组和列表的区别在于元组里面的内容不能重复s=(1,2,3,4)-字典,冒号前面的称为键(key),后面的称为值(value)d={'张三':18,'李四':19} 二、变量的定义与输入输出 1.变量的定义 python中的变量定义很简单,不需要像C/JAVA等定义变量类型,也不以分号表示结尾,直接用等号赋值即可: name='张三' age=18 2.变量的输入 使用input函数,input输入默认的是string也就是字符串类型,如果要输入其它类型比如说int类型的数字,需要进行强制类型转换 age= input("age:") if age.isdigit() : # 判断是否为数字 salary = int(salary) # 将string转换为int类型 因为输入的时候可能会输入空格,如果想跳过空格,可以用strip()函数age= input("age:").strip() 3.变量的输出 python的输出是采用格式化输出的方式,如果是字符串类型的变量可以直接输出,并且字符串与其它类型的变量相加会自动将其它类型的变量转换成字符串。 sexb='boy' sexg='girl' print("I am an happy %s" %(sexb)) # I am an happy boy print("I am an happy %s" %(sexg)) # I am an happy girl 或者直接输出: sexb='boy' print("I am an happy",sexb ) # I am an happy boy print("I am an happy"+sexb) # I am an happyboy 需要注意的是,用逗号分隔时会自动加一个空格,用'+'连接时不会加空格,并且python里面的print会自动换行。要想去掉换行就用一个end参数: print("123",end="") # 默认end='\n',即回车换行,现在是以空串结尾 print("456") 三、注释、多行输出及语法 1.注释 python的单行注释用#,多行注释用'''要注释的东西''',在pycharm中注释的快捷键为ctrl+?,若多行注释就选中要注释的代码块,然后按ctrl+? #name="张三" ''' name="张三" age=18 ''' 2.多行输出 多行输出用'''要输出的东西''' menu_bank = ''' 1. aaa 2. bbb 3. ccc 4. ddd''' print(menu) 3.语法 python是通过缩进来检测是否为一个语句块,语句块是以冒号开始,用空格的缩进来表示其语句块内部的语句。 s='b' if 'a'==s:#冒号表示开始 return True#缩进表示if内部的语句 return False python中单引号与双引号的作用相同,用这两种引号括起来的都是字符串。 四、循环 1.for循环 for循环是指依次遍历每一个可迭代对象,可以是列表、元组等: for i in range(5):#这个是指在[0,4]进行循环 if i==3: break# 跳出这层for循环 print(i) n=5 for i in range(n):#也是对[0,4]进行遍历 if i==3: continue# 不继续往下执行,直接执行下一个迭代,即令i=4 print(i) else:# 只有当for循环正常执行结束时才会调用,如果有break或者return等就不会执行 print("正常执行完for循环") 2.while循环 while循环与for类似,只是while只有循环条件,而for除了循环条件还有循环变量: i=0 while i<5: print(i) i+=1 else:# 只有当while循环正常执行结束时才会调用,如果有break或者return等就不会执行 print("正常执行完while循环") 在python中没有i++,i--,++i,--i等。 记录自己的python学习之路,错误的地方欢迎指正~~