mysql统计结果大于0时返回true_mysql表查询练习

准备数据

create table book(

id int primary key auto_increment,

name varchar(16),

price int

);

create table author(

id int primary key auto_increment,

name varchar(16)

);

create table book_author(

id int primary key auto_increment,

book_id int,

author_id int

);

id

name

pirce

1

葵花宝典

88

2

九阴真经

78

3

辟邪剑谱

99

id

name

1

张全蛋

2

李诞

3

寡人

id

book_id

author_id

1

1

1

2

1

2

3

1

3

4

2

1

5

2

3

6

3

2

7

3

2

2、查看每本书的作者

SELECT

book.NAME,

GROUP_CONCAT( author.`name` )

FROM

book

INNER JOIN book_to_author ON book.id = book_to_author.book_id

INNER JOIN author ON author.id = author_id

GROUP BY

book.NAME;

3、查看每位作者都有哪些作品

SELECT

author.NAME,

group_concat( book.NAME )

FROM

author

INNER JOIN book_to_author ON author.id = book_to_author.author_id

INNER JOIN book ON book.id = book_to_author.book_id

GROUP BY

author.NAME;

4、利用pymysql操作数据库实现sql注入效果

# -*- coding: utf-8 -*-

import pymysql

# 实例化产生一个 Connect 连接对象

conn = pymysql.Connect(

host='127.0.0.1',

port=3306,

user='root',

password='root',

database='day41',

charset='utf8'

)

# 游标对象

name = input('>>>name:').strip()

pwd = input('>>>>pwd:').strip()

cursor = conn.cursor(pymysql.cursors.DictCursor)

sql = "select * from userinfo where name='%s' and password=%s" % (name, pwd)

print(sql)

res = cursor.execute(sql)

print(res)

if res:

print('登录成功')

else:

print('登录失败')

# >>>name:qzk' -- dasad

# >>>>pwd:

# select * from userinfo where name='qzk' -- dasad' and password=

# 1

# 登录成功

# >>>name:qzk' or 1=1 -- asd

# >>>>pwd:''

# select * from userinfo where name='qzk' or 1=1 -- asd' and password=''

# 5

# 登录成功

5、利用pymysql实现登陆注册功能

注册:用户名不能重复

登陆:展示对应的错误信息(用户名错误，密码错误)

# 实例化产生一个 Connect 连接对象

conn = pymysql.Connect(

host='127.0.0.1',

port=3306,

user='root',

password='root',

database='day41',

charset='utf8'

)

# 游标对象

name = input('>>>name:').strip()

pwd = input('>>>>pwd:').strip()

cursor = conn.cursor(pymysql.cursors.DictCursor)

sql = 'select * from userinfo where name=%s'

print(sql)

res = cursor.execute(sql,(name,))

print(res)

dic_user = cursor.fetchone()

print(dic_user)

if res:

if int(pwd) == dic_user['password']:

print('登录成功')

else:

print('密码错误')

else:

print('用户名不存在')

内容回顾

select distinct 字段1，字段2，。。。 from 表名

where分组之前的过滤条件

group by 分组条件

having分组之后过滤条件

order by 排序字段1 asc，排序字段2 desc

limit 5,5

as语法中给某个查询结果起别名的时候需要把查询语句中的分号去除

(select name,salary*12 as '年薪' from emp) as t1;

# 一个字段展示用户名和年龄

select concat(name,':',age) as info from emp;

# 字段为NAME和AGE，值为‘NAME:jason’,'AGE:18'

select concat("NAME:",name) as NAME,concat("AGE:",age) as AGE from emp;

# 如果拼接的符号是统一的可以用

select concat_ws(':',name,age,sex) as info from emp;

# 1.子查询相关

# 查询平均年轻在25岁以上的部门名

select name from dep

where id in

(select dep_id from emp group by dep_id having avg(age)>25);

select dep.name from emp inner join dep on emp.dep_id = dep.id

group by dep.name

having avg(age) > 25;

# exist(了解)

EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录，

而是返回一个真假值，True或False。

当返回True时，外层查询语句将进行查询

当返回值为False时，外层查询语句不进行查询。

select * from employee

where exists

(select id from department where id > 3);

select * from employee

where exists

(select id from department where id > 250);

Navicat使用

掌握：

#1. 测试+链接数据库

#2. 新建库

#3. 新建表，新增字段+类型+约束

#4. 设计表：外键

#5. 新建查询

#6. 建立表模型

#注意：

批量加注释：ctrl+？键

批量去注释：ctrl+shift+？键

练习题

导出的sql语句代码

/*

数据导入：

Navicat Premium Data Transfer

Source Server : localhost

Source Server Type : MySQL

Source Server Version : 50624

Source Host : localhost

Source Database : sqlexam

Target Server Type : MySQL

Target Server Version : 50624

File Encoding : utf-8

Date: 10/21/2016 06: