mysql的增删改查以及pymysql的使用

mysql的启动和停止

linux环境

启动:service mysql start

停止:service mysql stop

重启:service mysql restart

windows环境

启动:net start mysql

停止:net stop mysql

客户端登陆mysql

语法 mysql -u用户名 -p密码 -hip -P端口号

示例:mysql -uroot -p123456 -h127.0.0.1 -P3306

关于用户的命令

注:命令中的%和*都是通配符的意思,表示所有

查看当前用户:select user();

设置当前用户密码:set password = password("123456");  #123456是新密码

创建一个用户:

　　语法:create user "用户名"@“登陆的ip” identified by "密码";

　　示例:create user "zhangsan"@"192.168.1.%" identified by "123456" ; # 给192.168.1.X网段用户设置一个用户,只要是该网段的电脑都可以远程登陆数据库

查看权限:

　　语法:show grants for “用户名”@"ip";

　　示例:show grants for "root"@"127.0.0.1";

授予权限:

　　语法 grant 权限 on 数据库名.表名 to "用户名"@“ip” identified by "密码"

　　示例:grant all on *.* to "root"@"127.0.0.2" identified by "123456";

　　数据库中的权限all,select,insert, update, delete, create, drop, reload, shutdown, process, file, references, index, alter, show databases, super, create temporary tables, lock tables, execute, replication slave, replication client, create view, show view, create routine, alter routine, create user, event, trigger, create tablespace

刷新权限:flush privileges;

移除权限:

 　　语法:revoke 权限名 on 数据库.数据表 from “用户名”@"ip";

　　示例:revoke delete on *.* from "root"@"127.0.0.1";

删除用户:

　　语法:drop user "用户名"@"ip";

　　示例:drop user "root"@"127.0.0.1";

数据库的操作

使用数据库: user mydatabase  # mydatabase是数据库名

新增数据库:

　　语法:create database 数据库名 charset 编码

　　示例:create database user charset utf8mb4;

查看数据库

　　查看所有数据库:show databases;

　　查看建库语句:show database create user;  # user是数据库名字

修改数据库:alter database user charset gbk;  # 将数据库名为user的编码改为gkb

删除数据库: drop database user;

数据表的操作

要对数据表进行操作,就必须先数据数据库

新增数据库

　　语法:create table 数据表名(字段名1 类型 约束1 约束2,字段名2 类型 约束1 约束2,字段名3 类型 约束1 约束2,); # 类型必须是mysql中有的类型,约束是对字段的约束,后文有介绍

　　示例:create table t1(id int primary key,name varchar(32) not null unique);

查看数据表

　　查看所有数据表:show tables;

　　查看建表语句:show create table t1;  #t1是要查看数据表

修改数据表:

　　语法:alter table 数据表名 操作类型  修改的属性 修改值 # 对于常用类型个一个实例

　　示例1:alter table t1 midify name char(32) ;  #修改字段name的数据类型

　　示例2:alter table t1 change name studentname varchar(32);  # 修改字段name的名字和数据类型

　　示例3:alter table t1 add age int; # 新增一个字段

　　示例4:alter table t1 drop column age;  # 删除一个字段

　　示例5:alter table t1 rename t2;  # 修改数据表的名字  

删除数据表:drop table t1;

数据的操作

新增数据: 

　　语法:insert into 数据表(字段名1,字段名2,字段名3 .....) values(数据1,数据2,数据3...),(数据1,数据2,数据3...),(数据1,数据2,数据3...);  # 字段名的位置没有要求,只要与数据对应即可,可以同时插入多条数据,用元组分开即可,如果所有字段都会插入值,且数据位置与表中的顺序一致,可以省略字段名的书写

　　完整示例: insert into t1(id,name) values(1,"张三"),(2,"李四"),(3,"王五"); # 由于插入数据是所有字段且顺序与建表一致,所以可以简写

　　简写:insert into t1 values(1,"张三"),(2,"李四"),(3,"王五");

查询数据库:

　　语法: select 查询字段 from 数据表 where 条件 group by 分组字段  having 分组后过滤条件 order by 排序字段 limit 查询条数;

　　示例:selece id from t1 where age >18 group by id having id>10 order by -id limit 3 ; #  排序-id表示按照id降序排序

　　简便示例:select id,name from t1 where id>2;  # 这种简便查询可以满足大部分单表查询了

修改数据库

　　语法:update 数据表名 set 字段名=字段值 where 条件;

　　示例:update t1 set name = "赵四" where id = 3;

删除数据:

　　语法: delete from 数据表名 where 满足删除的条件;

　　　　  truncate table 数据表   # 删除整个数据表的数据;

　　示例:delete from t1 where id=1;

　　　　　truncate t1;

数据库的常用类型

tinyint 1个字节 有符号(-128 ~ 127) 无符号 (0 ~ 255) 小整型值

int 4个字节 有符号(-21亿 ~ 21亿) 无符号(0 ~42亿) 大整型值 , 精度更高

float(255,30) 单精度

double(255,30) 双精度

decimal(65,30) 一般用于表达金钱类型,是使用字符串的形式保存的小数

char(11) 定长:固定开辟11个长度的空间 (手机号,身份号) 速度快

varchar(11) 变长:最大开辟字符长度为11的空间 (文章评论 5~255 个评论字数) 相较于char速度慢

text 这种类型专门用于存储文章,论文,小说.

enum 和 set 的数据类型,必须从中进行挑选,enum 枚举 从一组数据中选一个(性别),set 集合 从一组数据中选多个,自动去重

 数据库中的约束

unsigned:无符号整型

not null : 非空

default:设置默认值

unique:唯一

primary key: 主键,一张表只能有一个

auto_increment;自增加一

foreign key 外键,用来关联其他表

zerofill;存储数据不足设置长度,用0b填充

 数据查询的使用

where中查询条件

判断:<,>,<=,>=,!=,<>拼接:not,and,or

区间:between small and big在small和big之间的值，in [值1,值2......]在中括号中的值

模糊查询:  like  "%a_%" %代表任意长度任意字符，_代表长度为1的任意字符

group by分组查询

分组查询可以使用聚合函数,常用有

计算数量:count(字段名)

最值:max(字段名),min(字段名)

均值:avg(字段名)

和:sum(字段名)

 python使用数据库

1.创建连接

　　语法:对象 = pymysql.connect(host="ip",port=端口号,user="用户名",password="密码",database="数据库名“,charset="编码")

　　示例:conn= pymysql.connect(host="127.0.0.1",port=3306,user="root",password="12345",database="user“,charset="utf8mb4")

2.创建游标对象:

　　cursor = conn.cursor()

3.写sql语句放到一个变量中:

　　sql = "select * from t1" 

4.执行语句:

　　res = cursor.execute(sql)

5.查看数据或提交数据

　　res = fetchall()

　　conn.commit()

6.关闭连接

　　cursor.close()

　　conn.close()

 7.回滚:

　　conn.roll_back()