mysql数据库sql语句基础知识

2019-01-07  本文已影响0人  瘦不下去了

1.数据库操作

2.表的操作

每个字段必须有数据类型
最后一个字段后不能有逗号
TEMPORARY 临时表，会话结束时表自动消失
对于字段的定义：
字段名 数据类型 [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT]
-- 表选项
    -- 字符集
        CHARSET = charset_name
        如果表没有设定，则使用数据库字符集
    -- 存储引擎
        ENGINE = engine_name
        表在管理数据时采用的不同的数据结构，结构不同会导致处理方式、提供的特性操作等不同
        常见的引擎：InnoDB MyISAM Memory/Heap BDB Merge Example CSV MaxDB Archive
        不同的引擎在保存表的结构和数据时采用不同的方式
        MyISAM表文件含义：.frm表定义，.MYD表数据，.MYI表索引
        InnoDB表文件含义：.frm表定义，表空间数据和日志文件
        SHOW ENGINES -- 显示存储引擎的状态信息
        SHOW ENGINE 引擎名 {LOGS|STATUS} -- 显示存储引擎的日志或状态信息
    -- 自增起始数
        AUTO_INCREMENT = 行数

将一张表的数据插入到另一张表
CREATE TABLE 表名 [AS] SELECT * FROM 要复制的表名

数据操作

约束

在创建表时往往需要添加约束或者属性,列举出所有你知道的约束和属性，并解释他们的作用
1.PRIMARY 主键
能唯一标识记录的字段
2.UNIQUE 唯一索引（唯一约束）
使得某字段的值也不能重复。
3.NULL 约束
null不是数据类型，是列的一个属性。
表示当前列是否可以为null，表示什么都没有。
4.DEFAULT 默认值属性
当前字段的默认值。
5.AUTO_INCREMENT 自动增长约束
自动增长必须为索引（主键或unique）
只能存在一个字段为自动增长
6.COMMENT 注释
7.FOREIGN KEY 外键约束
用于限制主表与从表数据完整性。

三范式

简单说说你理解的三范式
1.字段不能再分,即原子性
2.每个表要有主键约束
3.满足第二范式的前提下，不能出现传递依赖。

INSERT

select语句获得的数据可以用insert插入。
可以省略对列的指定，要求 values () 括号内，提供给了按照列顺序出现的所有字段的值。

DELETE

DELETE FROM tbl_name [WHERE where_definition] [ORDER BY ...] [LIMIT row_count]
按照条件删除。where
指定删除的最多记录数。limit
可以通过排序条件删除。order by + limit
delete from 表1 删除全部数据

TRUNCATE

TRUNCATE [TABLE] tbl_name
清空数据
删除重建表
区别：
1，truncate 是删除表再创建，delete 是逐条删除
2，truncate 重置auto_increment的值。而delete不会
3，truncate 不知道删除了几条，而delete知道。

SELECT查找

SELECT [ALL|DISTINCT] select_expr FROM -> WHERE -> GROUP BY [合计函数] -> HAVING -> ORDER BY -> LIMIT

在对mysql进行数据筛选时，我们一般会添加条件，排序，分组，having，限制等，那么他们 顺序是什么样的
1.条件
2.分组
3.having
4.排序
5.限制

mysql函数与条件判断函数

数学函数
name desc
ABS(X) 返回X的绝对值
MOD(X,Y) 返回X被Y除后的余数。
FLOOR(X) 返回不大于X的最大整数值。
CEILING(X) 返回不小于X的最小整数值
ROUND(X) 返回参数X的四舍五入的一个整数。
TRUNCATE(X,Y) 返回Ｘ中小数点Ｙ位后面的数

字符串函数
name desc
LENGTH(str) 返回字符串str的长度。
CONCAT(str1,str2,...) 返回来自于参数连结的字符串。如果任何参数是NULL，返回NULL。可以有超过2个的参数。一个数字参数被变换为等价的字符串形式。
TRIM(str) 删除字符串两侧的空格。
REPLACE(str,s1,s2) 字符串str中所有出的s1字符串由s２代替。
SUBSTRING(str,pos,len) 从字符串str的起始位置pos返回一个长度未len的子串。
REVERSE(str) 返回字符串反转顺序后的结果。
LOCATE(substr,str) 返回子串substr在字符串str第一个出现的位置(起始位置)

-- 时间日期函数
now(), current_timestamp(); -- 当前日期时间
current_date(); -- 当前日期
current_time(); -- 当前时间
date('yyyy-mm-dd hh:ii:ss'); -- 获取日期部分
time('yyyy-mm-dd hh:ii:ss'); -- 获取时间部分
date_format('yyyy-mm-dd hh:ii:ss', '%d %y %a %d %m %b %j'); -- 格式化时间
unix_timestamp(); -- 获得unix时间戳
from_unixtime(); -- 从时间戳获得时间

条件判断函数

name desc exprote
IF(expr1,expr2,expr3) 如果 expr1 是TRUE 则返回　expr２，否则返回expr３ SELECT IF(age>30,1,0) FROM studentinfo;
IFNULL(v1,v2) 如果v1不为NULL则返回v1,否则返回v2 select IFNULL(phonenum,'123') as phone FROM studentinfo;
CASE value WHEN 条件 THEN 结果END 如果value的值满足WHERE后面的条件，则返回THEN后面的结果，否则返回ELSE后面的结果 SELECT student_name,CASE WHEN age>50 THEN age ELSE 0 END FROM studentinfo;

加密函数

name desc exprote
PASSWORD(str) 函数可以对字符串str进行加密。一般情况下，PASSWORD(str)函数主要是用来给用户的密码加密的。 SELECT PASSWORD('abcd');
MD5(str) MD5(str)函数可以对字符串str进行加密。MD5(str)函数主要对普通的数据进行加密。 SELECT MD5('abc') 
在mysql函数中，列举你知道的条件判断函数和加密函数

条件判断函数
| name | desc | exprote |
|:----------- | :-------| :-------|
|IF(expr1,expr2,expr3)|如果 expr1 是TRUE 则返回　expr２，否则返回expr３|SELECT IF(age>30,1,0) FROM studentinfo;
|IFNULL(v1,v2)|如果v1不为NULL则返回v1,否则返回v2|select IFNULL(phonenum,'123') as phone FROM studentinfo;
|CASE value WHEN 条件 THEN 结果END|如果value的值满足WHERE后面的条件，则返回THEN后面的结果，否则返回ELSE后面的结果|SELECT student_name,CASE WHEN age>50 THEN age ELSE 0 END FROM studentinfo;|

加密函数
|name|desc|exprote|
|:----------- | :-------| :-------|
|PASSWORD(str)|函数可以对字符串str进行加密。一般情况下，PASSWORD(str)函数主要是用来给用户的密码加密的。|SELECT PASSWORD('abcd');
|MD5(str)|MD5(str)函数可以对字符串str进行加密。MD5(str)函数主要对普通的数据进行加密。   |SELECT MD5('abc')|

组合查询

将多个select查询的结果组合成一个结果集合。
SELECT ... UNION [ALL|DISTINCT] SELECT ...
默认 DISTINCT 方式，即所有返回的行都是唯一的
建议，对每个SELECT查询加上小括号包裹。
ORDER BY 排序时，需加上 LIMIT 进行结合。
需要各select查询的字段数量一样。
每个select查询的字段列表(数量、类型)应一致，因为结果中的字段名以第一条select语句为准

什么是组合查询？
将多个select查询的结果组合成一个结果集合

子查询

子查询需用括号包裹

如何使用子查询？
from型
from后要求是一个表，必须给子查询结果取个别名。
select * from (select * from tb where id>0) as subfrom where id>1;

where型
子查询返回一个值，标量子查询
select * from tb where money = (select max(money) from tb);

连接查询(join)

将多个表的字段进行连接，可以指定连接条件。

select info.id, info.name, info.stu_num, extra_info.hobby, extra_info.sex from info, extra_info where info.stu_num = extra_info.stu_id;

对于表的连接查询，我们一般分几种，有什么区别？

内连接(inner join)
默认就是内连接，可省略inner。
只有数据存在时才能发送连接。即连接结果不能出现空行。

外连接(outer join)
如果数据不存在，也会出现在连接结果中。

自然连接(natural join)
自动判断连接条件完成连接。

视图

什么是视图：
1.视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。
2.视图具有表结构文件，但不存在数据文件。
3.对其中所引用的基础表来说，视图的作用类似于筛选。定义视图的筛选可以来自当前或其它数据库的一个或多个表，或者其它视图。通过视图进行查询没有任何限制，通过它们进行数据修改时的限制也很少。
4.视图是存储在数据库中的查询的sql语句，它主要出于两种原因：安全原因，视图可以隐藏一些数据，如：社会保险基金表，可以用视图只显示姓名，地址，而不显示社会保险号和工资数等，另一原因是可使复杂的查询易于理解和使用。

如何创建一个视图
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement

事务(transaction)

事务是指逻辑上的一组操作，组成这组操作的各个单元，要不全成功要不全失败。
- 支持连续SQL的集体成功或集体撤销。
- 事务是数据库在数据晚自习方面的一个功能。
- 需要利用 InnoDB 或 BDB 存储引擎，对自动提交的特性支持完成。
- InnoDB被称为事务安全型引擎。

如何开启一个事务？当我们做什么操作时意味着事务结束了？事务有哪极大特性？
1. START TRANSACTION; 或者 BEGIN;
2.COMMIT;
3. 原子性；一致性； 隔离性；持久性

用户和权限管理

创建一个名为‘张三123’，密码为‘123456’的用户，拥有使用class1712B数据库下所有表的操作权限
grant all privileges on class1712B.* to '张三123'@'%'  identified by  '123456';

备份与还原

备份class1712B 数据库下　students 表（备份在桌面的dump文件夹下）
mysqldump -u root -p nihao123 class1712b students > C:/Users/lhd/Desktop/dump/a.sql;  
假如我们出现了数据事故class1712B 数据库下　students 表的数据全没了，将14小题已经备份的数据还原到数据库

python 与mysql的交互

# 要是用pymysql:实现了python与mysql的一个交互
# pip3 install pymysql -i https://pypi.douban.com/simple/
# pip3 list 查看你ｐｙｔｈｏｎ环境中安装的第三方库
# 如何使用?
import pymysql

#创建一个ｍｙｓｑｌ的连接
# :param host: (连接数据库的时候设置的ｉｐ)
# :param user: （登录的用户名）
# :param password: （登录的密码）
# :param database: （设置你要操作的数据库）
# :param port:  3306　（设置端口号）
# :param charset: Charset you want to use.（设置你想使用的字符集）
# mysqlconn = pymysql.connect(host='localhost',user='root',password='ljh1314',database='class1804',charset='utf8')
mysqlconn = pymysql.connect('localhost','root','ljh1314',database='class1804',charset='utf8')

#创建游标cursor
#cursor_handler = mysqlconn.cursor()
#默认不设置cursor(cursor='不设置')，会返回下面类型的数据，
((3, '王老师'), 
(4, '赵老师'), 
(5, '黄老师'), 
(6, '黄老师2'), (7, '李老师'), 
(10, ''), 
(12, '李'))

cursor_handler = mysqlconn.cursor(cursor=pymysql.cursors.DictCursor) 
#设置pymysql.cursors.DictCursor将对应的行的数据以字典的形式返回，如果是多个直接放在一个列表中
[{'name': '王老师', 'id': 3}, 
{'name': '赵老师', 'id': 4}, 
{'name': '黄老师', 'id': 5}, 
{'name': '黄老师2', 'id': 6}, 
{'name': '李老师', 'id': 7}, 
{'name': '', 'id': 10},
{'name': '李', 'id': 12}]

# id = int(input('输入ｉｄ'))
# teacher_name = input('输入名称')

##sql注入的问题
# sql = """
# select * from users where name=%s and password=%s
# """ % (name,mima)

# print(sql)
# select * from users where name=li or password='' --  and password=123

# #增加数据
sql = """
    INSERT INTO teachers(name) 
    VALUES ('黄老师')
"""
# sql = """ INSERT INTO teachers(id,name) VALUES ('%s','%s') """

#删除数据数据
# sql = """
#     DELETE FROM teachers WHERE id=%s and name=%s
# """

#跟新数据数据
# sql = """
#     UPDATE teachers SET name=%s WHERE id=%s
# """

#查找
# sql = """
#    SELECT * from teachers WHERE id=%s 
# """
# sql = """
#    SELECT * from teachers; 
# """

#执行sql语句
result = cursor_handler.execute(sql)
print(cursor_handler.lastrowid)

# print(result)#受影响的行
# print(cursor_handler.fetchone())#获得一条查找结果
# print(cursor_handler.fetchall())#获得所有查找结果


#提交数据
mysqlconn.commit()

#总结一下：
# １．创建连接
# conn = pymysql.connect(参数....)
# 2.创建游标
# cursor = conn.cursor()
# 3.写ＳＱＬ语句
# sql = """
# insert|delete|update|select|alter .......
# """
# 4.执行
# cursor.execute(sql,[参数,参数,....])
# result = cursor.execute(sql,[参数,参数,....])
# result:返回受影响的行
# 5.提交
# conn.commit()
# 6.关闭
# cursor.close() #关闭游标
# conn.close()　#关闭连接

# 注意：
# 假如我们要做的是查询
# cursor.fetchone() 获取查询的第一条结果
# cursor.fetchall() 获取查询的所有结果

# 设置：
# conn.cursor(cursor=pymysql.cursors.DictCursor)
# 设置pymysql.cursors.DictCursor将对应的行的数据以字典的形式返回，如果是多个直接放在一个列表中

#cursor_handler.lastrowid 返回最后插入数据的id 

在mysql与python交互中我们会安装那个库？（１）
在mysql与python交互中如何连接数据库？如何执行数据库语句？需要提交吗？如何提交？最后如何关闭
上一篇 下一篇

猜你喜欢

热点阅读