化繁为简的MySQL数据库

MySQL数据库

数据库（MySQL.com下载）

查看系统所有数据库：show databases;

创建数据库：create database 数据库名 charset=utf8；

切换到指定数据库：use 数据库名

查看当前数据库：select database();

展示创建数据库的语句：show create database 数据库名;

删除数据库：drop database 数据库名;

创建数据表

展示当前数据库中所有表：show tables;

创建一个表结构：create table 表名 (id int unsigned auto_increment primary key not null,name varchar(32)，其他字段和属性); 

增（0 或者null表示占位  实际以默认或者自动增长的值  为准）

全列插入和表结构的字段一致。例：insert into students values(0,'小李',150),(null, '小王', 180);

指定插入：insert into students (age, name) values(182, '小王');

删

1、物理删除

delete from students where id = 7;

删除多行，可以使用in：delete from classmate where id in (2,4,5,6,7,8)；

2、逻辑删除-本质上是 更新数据的标记的操作

alter table students add isDelete bit default 0;

update students set isDelete = 1 where id = 1;

改

例：update students set age =0 where name='小王';

查（基本）

显示全部信息select * from students;

指定字段查询select name,age from students;

字段显示别名，例：select name as '尊姓大名', age as '高寿' from students;

表起别名，例：select s.name,s.age from students s;

查（提高）

条件筛选：><=、不等于<>或者！=

使用枚举查询，enum是一个类，gender=enum（）则是创建一个对象，而对象的返回值是内存地址，因此不能使用。枚举（enum）本质数字构成的集合  从1开始 ，可以直接用gender=1,来代表男性——select * from classmate where gender=1;

模糊匹配

%匹配任意多个字符:select * from students where name like '周%';

_匹配1个任意字符:select * from students where name like '周_';

查询 姓名中含有小 的信息:select * from students where name like '%小%';

in () 判断值是否在集合中存在

between 下限 and 上限

查询 height字段没有填写的记录，此处不能使用'='，必须使用is：select * from students where height is NULL;

排序（关键词order by ——ASC 升序<默认>  DESC 降序）

select * from students order by height desc,age asc;

聚合函数

统计数量count（）——select count(*) from students;

最大值select max(height) from students;

最小值select min(age) from students where gender = 1;

求和sum、求平均avg、保留小数位数round

分组

以性别分组 显示性别 及其对应分组的平均年龄 及其 分组内每个成员的年龄——select gender, avg(age),group_concat(age) from students group by gender;

分组后过滤having——select gender, avg(age),group_concat(age) from students group by gender having count(*)>2;

分组后汇总with rollup——select gender,count(*) from students group by gender with rollup;

分页

第n页：limit (n-1)*每页数，每页数

例：第3页的数据 每页是3条select * from students limit 6, 3;——limit 开始位置是0

连接查询——（笛卡尔积运算）

内连接——使用on过滤条件即两个表中重复的字段select * from 表1 inner join 表2 on 表1.重复字段 = 表2.重复字段;

左连接—— select * from 表1 left join 表2 on 表1.重复字段 = 表2.重复字段;

可以重命名—例： select * from hero as h left join gongfu as g on h.gongfuid = g.id;

右连接——select * from 表1 right join 表2 on 表1.重复字段 = 表2.重复字段;

自连接—一个表即当表1又当表2,例：左表中的市所属的省id即pid 需要等于右表的省份id即aid    （select * from areas city join areas pro on city.pid = pro.aid  where pro.atitle = '山东省'; ）语句中有重命名

子查询

标量子查询

查询出高于平均年龄的学生的信息select * from students where age > (select avg(age) from students);

列子查询（结果是一个集合）—关键字in—格式：主查询where条件in（列子查询）

例：查询已经上课的学生信息select * from students where cls_id in (select id from classes);

行子查询（结果是一个元组）

例：查询出班级上 年龄最大 和 身高最高的信息select * from students where (age,height) = (select max(age),max(height) from students);

展示创建表的语句：show create table students;

查看表结构：desc 表名;

修改表结构（相当于对字段的修改，而非表内容）：alter table

添加一个字段—add：alter table students add age tinyint unsigned not null;

修改字段名称、类型、约束—change：例：alter table students change birth（需要修改的字段） birthday（修改后字段） date not null（修改后字段的属性）;

只修改字段类型和约束  不改名—modify：例：alter table students modify birthday（需要修改的字段） datetime not null（修改后属性）;

删除字段—drop：例：alter table students drop birthday（需要删除的字段）;

和pycharm联用

导入模块：import pymysql

创建连接conn = pymysql.connect(host='127.0.0.1',port=3306, user='root',password='mysql'，db='python_test_1',charset='utf8'）

获取游标cur = conn.cursor()

书写sql语句

执行 数据库操作row_count = cur.execute(sql)

提交修改conn.commit()

先关游标  cur.close() 

再关连接  conn.close()

参数化：可以有效防止sql注入—全部使用%s占位

在execute中传入参数的列表，例：age = input('请输入年龄：')

gender = input('请输入性别：')                  

sql = "select * from students where age>%s and gender= %s;"

row_count = cur.execute(sql,[age,gender])

MySQL高级

视图——视图就是一条SELECT语句执行后返回的结果集

创建视图（建议以V-开头），例：create view v_hebei_areas as select pro.atitle as patitle, c.id as cid, c.atitle as catitle, c.pid as cpid from areas as c join areas as pro on c.pid=pro.id where pro.atitle='河北省';

查看视图：show tables;

使用视图：select * from v_hebei_areas;

删除视图：drop view v_hebei_areas;

事务——原子性、一致性、隔离性、持久性

开启事务：begin

提交事务：commit

回滚事务：rollback

索引

查看索引：show index from 表名;

创建索引：create index 索引名称 on 表名(字段名称(长度));

删除索引：drop index 索引名称 on 表名;

开启时间监测：set profiling=1;

查看执行时间：show profiles;

账户管理——所有用户及权限信息存储在mysql数据库的user表中

查看user表的结构：desc user;

查看所有用户：select *或者（host,user,authentication_string） from user;分号前使用\G显示全部内容

创建账户&授权：grant 权限列表 on 数据库.* to '用户名'@'访问主机' identified by '密码';

修改已有用户的权限，例：grant select, create, update on students to 'xiaoyu'@'localhost' with grant option;

刷新：flush privileges;

修改已有用户的密码，例：update user set authentication_string=password('123456')where user='xiaoyu';

查看用户有哪些权限，例：show grants for 'xiaoyu'@'localhost'

删除一个用户，例：drop user 'xiaoyu'@'localhost';

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 206,602评论 6赞 481
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 88,442评论 2赞 382
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 152,878评论 0赞 344
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 55,306评论 1赞 279
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 64,330评论 5赞 373
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 49,071评论 1赞 285
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,382评论 3赞 400
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 37,006评论 0赞 259
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 43,512评论 1赞 300
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,965评论 2赞 325
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,094评论 1赞 333
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,732评论 4赞 323
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,283评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,286评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,512评论 1赞 262
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 45,536评论 2赞 354
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,828评论 2赞 345

推荐阅读更多精彩内容

  • 数据库性能优化详解
    转 # https://www.cnblogs.com/easypass/archive/2010/12/ 08/...
    吕品㗊阅读 9,698评论 0赞 44
  • 做你能做到的善良
    阅读原文 墨霖 很多年以前，我一直都听信着正能量，觉得人该活的善良。只有善良，才能得到你该得到的，和想得到的。因此...
    心里有只猫阅读 422评论 0赞 1
  • 最后的幻想
    心，再也不会疼了 就像干涸了千年的大地 再也挤不出一丝水分 也再供养不起一颗草 比荒芜更荒芜 ………… 风熄灭了最...
    端木丹阳阅读 220评论 0赞 0
  • 2018-03-05
    ECO生态币，FLC环保币，零投资，高受益，
    金掌柜阅读 156评论 0赞 0