MySQL学习基础知识2
1、基础语句
查
select(* | 字段名 | 四则运算 | 聚合函数) from 表名称;
加上as取别名 as可省略
如:select name, (math+english)/2 total from stu;
增
insert into 表名(字段, ..) values(值, ....),(值, .....)
可以从另一张表中拿数据,insert into t1(user, pwd) select user,pwd from user;
删
delete from 表名 where 条件;
delete from 表名;
truncate table 表名;(清空表。删除全表,然后重新建立一个新的)
改
update 表名 set 字段名称= 新的值 where 条件;
2、筛选条件
select完整的语法:
select [distinct] (* | 字段 | 四则运算 | 聚合函数) from 表名 where , group by, having, order by, limit
强调: 书写顺序必须按照上面的顺序,
执行顺序却是下面的:from, where, group by, having, order by, limit, distinct
distinct,去除重复,在最后执行,去除查询后,表中重复的数据
where 查询条件,
= > < >= <= != <>
and not or
in in(1,2,3)
between .. and ..在...之间
like 模糊匹配,'%': 表示多个字符,'_': 一个任意字符
group by
在5.7版本以前,经过如下设置:
select @@sql_mode;
set global sql_mde = "上面的结果+ONLY_FULL_GROUP_BY"
得到的结果:分组后只能查看分组后的字段和聚合函数得到的结果
分组可以有多个分组依据,select dep, job from emp group by dep,job;
聚合函数:(只能在group by后面才能执行)
sum,avg,max,min,count(count会忽略Noll值)
group_concat(), 分组后拼接(可以用来显示不在分组依据的字段)
concat(), 拼接,select concat('姓名',name,'性别', sex) from t1;、
concat_ws(), select concat_ws(':', name, sex) from t1;
having: 用于分组后的筛选
order by 排序,asc升序,desc降序。可以多个值排序,默认为asc
select * from emp order by salary desc, id asc; 先按salary降序,然后id升序
limit 控制显示记录的条数
limit a, b;从a开始数b个显示
limit x; 不写就是默认从0开始数x个记录。
正则表达式 regexp
select * from where name regexp ".*ba$";
3、连表查询
1. 笛卡尔积查询
把两个表中的所有数据已笛卡尔积的形式建立连接。
select * from emp, dept where dept_id = dept.id;
2. 内链接查询
(inner) join on
查询两边表中完全匹配条件的信息
select * from dept (inner) join emp on dept_id = dept.id;
当只使用join没有筛选on时,效果和笛卡尔积查询一样。
3. 左外连接查询
lift join ...on ...
左表无论是否匹配,都全部显示,右边只显示成功的
select * from dept left join emp on dept_id = dept.id;
4. 右外连接查询
right join ... on..
5. 全外连接查询
左右两边 无论是否匹配都要显示
mysql不支持 全外连接 oracle支持 full join
替代方法:mysql 可以通过union合并查询,来合并左连接和右连接的查询结果
合并两个查询结果 并且去除重复的
select *from dept left join emp on dept_id = dept.id
union
select *from dept right join emp on dept_id = dept.id;
合并但不去除重复
select *from dept left join emp on dept_id = dept.id
union all
select *from dept right join emp on dept_id = dept.id;
注意:union语句 必须使用在两个字段数量相同的情况下
三表查询(多对多)套路:
1. 先把三个表全部连接一起, select * from stu join tsr join tea
2. 用on来筛选正确关系 on stu.id = tsr.s_id and tea.id = tsr.t_id
3. 通过where 添加额外的调价 where tea.name = "egon";
4、子查询
指的时当一个查询语句作为另一个查询语句的条件时,该查询语句就称为子查询。
exists 关键字查询,exists 后面跟子查询,如果子查询有结果为True,没有结果为False
还可以使用: in, not in, any, all, not exists,=, >, <, !=
5、用户管理
在mysql下,各种表存放各种权限,db,user,columns_priv,tables_priv
查看user;用户表
select * from user\G;(字段太多,使用\G)
创建用户,只有在root用户下
create user 用户名@主机名称 identified by '123';
主机名称:'%'可以代替所有,还可以使用'192.168.1.%’
分配权限
grant all on *.* to 用户名@'localhost' identified by '123';
all:增删改查所有权限,不包括grant
*.*:表示任何数据库 任何表,第一个*是库,第二个*是表
例:grant all on day45.* to tom@'%' identified by '123';
权限可以精确到字段(但不能精确的某一行,需要视图)
grant select(id) on day45.dept to tom@"%" identified by '123';
可以将权限授其他用户
grant all on *.* to tom@% identified by '123' with grant option;
收回权限,revoke all privileges [column] on db.table from user@'host';
删除用户,drop user@'host';
刷新权限,在修改之后执行。flush privileges;
6、pymysql的基本操作
import pymysql
try:
conn = pymysql.connect(host='127.0.0.1',
port=,
user='root',
password='',
database='db1')
print("连接服务器成功!")
#.获取游标对象
# DicCursor使读出的额结果是字符串类型,不写就是元组类型
cursor = conn.cursor(pymsql.cursors.DicCursor)
#.执行sql语句
count = cursor.execute("select *from empt")
print("结果数量: %s" % count) # 提取结果
# print(cursor.fetchall())
# print(cursor.fetchone())
# print(cursor.fetchmany()) # 移动游标位置 相对当前位置
cursor.scroll(,"relative")
cursor.scroll(-, "relative")
print(cursor.fetchone()) # 移动游标位置 使用绝对位置
cursor.scroll(, "absolute")
print(cursor.fetchone()) print(cursor.fetchall())
# 注意 游标移动到末尾后无法在读取到数据 若需重复读取数据,需要使用scroll来移动游标 except Exception as e:
print("连接服务器失败.....")
print(type(e),e)
finally:
if cursor:
cursor.close()
print("关闭游标")
if conn:
conn.close()
print("关闭链接")
#增
#sql = "insert into user values(null,%s,%s,%s)"
#count = cursor.execute(sql,("tom","man",""))
# 也可以使用字典
# sql = "insert into user values(null,%{a}s,%{b}s,%{c}s)
# count = cursor.execute(sql,{'a': 'tom','b': 'man', 'c': ''}
# 用来批量添加数据,可提高效率
#count = cursor.executemany(sql, [("周芷若","woman",""), ("赵敏","woman","")]) #删
# count = cursor.execute("delete from user where id = 1") #改
count = cursor.execute("update user set name = '刘大炮' where id = 1") if count:
print("执行成功!")
else:
print("执行失败!") # 获取最新的id, 在查时返回的是None,只有修改时才返回的是id
# print(cursor.lastrowid) #pymsql对于数据的增删改默认不会生效,必须调用commit().
conn.commit()
#或者创建链接对象时指定为自动提交
conn = pymysql.connect(host="127.0.0.1",port=,user="root",password="",db="day46",autocommit=True)
使用pymysql进行增删改
7、sql注入攻击
用户在输入数据时,按照sql的语法,编写了一些带有攻击目的的sql语句,并插入到原始语句中。
count = cursor.execute("select *from user where name = '%s' and password = '%s'" % (user,password))
if count:
print("登录成功!")
else:
print("登录失败!")
sql注入攻击:
尝试在用户名中输入以下内容,密码随意
jerry' — ass
或者连用户名都不用写
' or 1 = 1 -- asaa
注入攻击
# 解决办法:
、客户端发sql语句发给服务端后,服务端进行re判断。
、使用pymsql处理,pymsql可以自己处理拼接工作。
sql = "select *from user where name = %s and password = %s"
print(sql)
count = cursor.execute(sql,(user,password)) # 参数交给模块
if count:
print("登录成功!")
else:
print("登录失败!")
解决办法
最新文章
- ABP源码分析四:Configuration
- QT
- select 1 from dual 中的1表示的含义
- 北斗/GPS
- 详细介绍java中的数据结构
- Windows API 文件处理
- Python 网页投票信息抓取
- JQuery 对 Select option 的操作---转载
- DNS添加/修改/查询/删除A记录
- 在Linux系统下安装大于mysql5.5版本的数据库
- IOS 中runtime 不可变数组__NSArray0 和__NSArrayI
- No resource found that matches the given name &#39;android:WindowTitle&#39;
- ios新开发语言swift 新手教程
- Intellij IDEA 15 如何同时打开多个项目
- Android自定义View(三、深入解析控件测量onMeasure)
- libevent 实现的socket 通信 server以及解决找不到动态库的方法
- Virtual box中Ubuntu虚拟机磁盘碎片整理和空间清理方法
- latex中使用定理、证明、缩进
- 网络通信协议tcp,udp区别
- ②泡茶看<;数据结构>;,喜欢看源码-栈ADT