mysql基础

增

insert into 表名(field1,field2,field3...) values(value1,value2,value3...),(value11,value22,value33...);

 

改

update 表名 set field1=new_value,field2=new_value where 条件

注意:

a.完全可以更新一个字段或者多个字段
b.where相当于Python中的if语句
c.可以指定任何条件到where子句中
d.如果没有where子句，则默认所有的行都被同时更新为指定的操作

 

删

delete from 表名 where 条件

注意： a.如果where子句没有指定，则默认将表中的数据全部删除 b.可以指定任何条件在where子句中

#删除表中的数据的方法有delete,truncate, 其中TRUNCATE TABLE用于删除表中的所有行，而不记录单个行删除操作。TRUNCATE TABLE 与没有 WHERE 子句的 DELETE 语句类似；但是，TRUNCATE TABLE 速度更快，使用的系统资源和事务日志资源更少
​
#Truncate是一个能够快速清空资料表内所有资料的SQL语法。并且能针对具有自动递增值的字段，做计数重置归零重新计算的作用

注意： delete: 删除表中的数据，表结构还在，删除之后的数据可以找回, 不会重置自动递增的数据; 例如(id int auto_increment primary key) truncate: 不但删除表中的数据，还drop掉了表结构，删除的数据是不能找回的，执行速度比delete快,会重置自动递增的数据.

 

查

1.基础查询

select * from student;

2.条件查询

主要结合where的使用

between...and:介于。。和。。。之间

and：逻辑与

or：逻辑或

in：类似于Python中的成员运算符

not in:

is:类似于Python中的身份运算符

is not:

3.模糊查询

where子句中=表示精准查询

like 通配符：一般情况下结合where子句使用

通配符：

_:匹配任意一个字符
​
%：匹配0~n个字符【n大于等于1】

例: select * from student where name like '_c%';

4.字段控制查询

as:起别名，用法 ：select 字段 as 别名

ifnull:将null转换为其他数据，用法，ifnull(字段名，其他数据)

distinct:去除重复记录

distinct 字段名: 根据指定字段去重 ;  
​
对于需要返回stu表中2列以上的结果时会有歧义,  比如:
​
例1: select distinct id, name from stu;
​
实际上返回的是 id与name同时不重复的结果,也就是DISTINCT同时作用了两个字段，必须得id与name都相同时才会去重.
​
例2: select id,distinct name from stu;

这里id和name会发生冲突, 去了重的name对应的id有好几个,不知道显示哪个id,会报错;

order by : 排序,指定数据返回顺序

asc：ascending,升序
​
desc:descending，降序

用法：select from 表 order by xxx

#1.查询所有的记录，按照年龄升序排序 select * from student order by age asc;

#2.查询所有学生记录，按照年龄降序排序，如果年龄相等，则按照编号进行升序排序 select * from student order by age desc,id asc;

聚合函数

1.count():统计指定列不为null的记录行数

count(*)  它返回检索行的数目(对行数目进行计数)， 不论其是否包含 NULL值。
​
count(column_name)  对列中不为null的行进行计数, 若找不到匹配字段的项，则count()返回 0 ;(所以分组后,指定匹配列的项全为null时,返回0)
​
count(distinct 字段名)  返回不同的非NULL值数目。

2.sum():计算指定列的数值和

例: select sum(age) from student;

3.求指定列中的最大值和最小值

max():

min():

例: select max(age),min(age) from student;

4.avg():求平均数

例: select avg(age) from student;

总结: 查询关键字的书写顺序：select 聚合函数 from where order by

5.分组查询

group by：分组查询

having：有...,表示条件，类似于where的用法

例: select deptno,sum(sal) from emp group by deptno having sum(sal)>7000;

having和where的区别:

a.二者都表示对数据执行条件
​
b.having是在分组之后对数据进行过滤
​
    where是在分组之前对数据进行过滤
​
c.having后面可以使用聚合函数
​
        where后面不可以使用聚合函数

 

6.分页查询

limit m n：从下标m开始查询,查询n行; 下标从0开始

例: select * from emp limit 0,4; 查询4行记录，起始行从0开始

 

总结：

**查询语句书写顺序：select----》from---》where---》group by-----》having-----》order by----->limit**
​
**查询语句的执行顺序：from----》where-----》group by----》having----》select-----》order by----》limit**

 

7.多表查询

表与表之间的关系

一对一

通过嵌套的方式

一对多【多对一】

添加外键

多对多

单独创建一张新的表

连接查询

1.内连接

特点：查询结果必须满足条件

select s.stuid,s.stuname,c.score,c.courseid from student s join score c on s.stuid=c.stuid;

等价写法:

select s.stuid,s.stuname,c.score,c.courseid from student s,score c where s.stuid=c.stuid;

2.外连接

特点：以其中一个表作为参照连接另外一个表

参照表数据全部显示, 另一个表没有对应相同字段的显示为null

左外连接: left join on

右外连接：right join on

 

8.子查询

a.子查询出现的位置：

    from后
​
    where子句的后面，作为条件的一部分被查询

b.当子查询出现在where后面作为条件时，可以使用关键字：any、all

c.子查询结果集的形式

    单行单列
​
    单行多列
    多行多列
​
    多行单列

 

 

数据库的备份和恢复

备份

生成SQL脚本，导出数据

命令：mysqldump -u root -p 数据库名>生成sql脚本的路径

注意：可以不需要登录数据库

演示: rock@rockrong:~$ mysqldump -u root -p mydb1>/home/rock/Desktop/mydb1.sql Enter password:

 

恢复

执行sql脚本，恢复数据

命令: source sql脚本存放路径

前提：必须先创建数据库【空的】

注意：需要先登录数据库，然后进入指定的数据库，执行sql脚本

演示: source /home/rock/Desktop/mydb1.sql;

事务使用

执行语句	说明
start transaction	执行开始事务命令后,下面将进入事务模式.
update、delete、insert 操作	在事务执行后,这些操作只在内存状态下进行,而不是在物理状态下
commit	在事务执行完成后,确认执行无误且成功,就可以使用commit把内存中执行的结果,提交到物理内存中
rollback	如果在事务执行过程中,发生了错误,则可以使用rollback命令回滚到上一个事务操作

 

python操作mysql

import pymysql

1.连接mysql

con = pymysql.connect('localhost','root','password','mydb')

2.创建游标对象,可以执行sql语句

cursor = con.cursor()

3.写sql语句

sql=''

4.执行sql语句

cursor.execute(sql) 执行sql

con.commit() 提交事务 (增删改需要提交, 查询不需要)

查询数据:

1. res = cursor.fetchone() 每次执行获取查询的下一条数据

2. res = cursor.fetchall() 获取查询的所有数据

3. res = cursor.fetchmany(3) 获取查询的前3条数据

4. cursor.rowcount() 总的数据条数

5.关闭游标对象和mysql连接

cursor.close() 关闭游标

con.close() 关闭连接