MySQL(Structured Query Language)基础篇

1. 创建数据库与数据库的操作

语句 说明
CREATE DATABASE database_name; 创建数据库
CREATE DATABASE IF NOT EXISTS database_name; 判断数据库是否存在,不存在则创建,存在则不创建
CREATE DATABASE database_name charset=utf8; 创建数据库,并制定字符集
show databases; 显示所有的数据库
USE database_name; 选择数据库
SELECT database; 显示当前的数据库
SHOW VARIABLES LIKE "character_set_database"; 查看当前数据库的编码格式
ALTER DATABASE database_name charset=utf8; 修改数据库的字符集
DROP DATABASE database_name; 删除数据库
DROP DATABASE IF NOT EXISTS database_name; 判断数据库是否存在后删除数据库。

2.数据库的储存引擎 和 数据类型

语句 说明
SHOW ENGINES; 查看所有的储存引擎
SHOW VARIABLES LIKE 'default_storage_engine'; 查看默认的SQL储存引擎
SET default_storage_engine=INNODB; 修改默认的额储存引擎
储存引擎的类型
数字数据类型
文本数据类型
日期的数据类型

3.数据库中的表的操作

CREATE TABLE IF NOT EXISTS table_name(id INT NOT NULL AUTO_INCREMENT,name CHAR(10) NOT NULL,PRIMARY KEY(id));
创建的表 ,设置数据类型,限制,AUTO_INCREMENT 设置自增长 ,PRIMARY KEY() 指定主键,主键是唯一的,是表中每一行的唯一标示

语句 说明
SHOW TABLES 查看表
SHOW CREATE TABLE table_name 查看表详细信息
DESC table_name 查看表的结构
DESC 数据表名 列名 查看某一列的信息
CREATE TABLE students( stu_id int NOT NULL AUTO_INCREMENT, birthday date NOT NULL, address varchar(50) NOT NULL *__DEFAULT “山西长治”__*, PRIMARY_KEY(stu_id) ); 设置默认值
ALTER ATBLE table_name ADD age int DEFAULT 0 添加新字段(列)
ALTER ATBLE students ADD( gender int default 0, phonenum int(11) ); 添加多个新字段,用逗号分隔
ALTER TABLE 表名 CHANGE 字段名 新字段名 数据类型; 修改字段名(列)
ALTER TABLE 表名 DROP 字段名; 删除字段名
ALTER TABLE 表名称 ALTER column 要修改的字段名 SET DEFAULT 修改为的默认值; 修改默认值
ALTER TABLE 表明 ALTER column 要删除的字段名 DROP DEFAULT; 删除默认值
重命名表
RENAME TABLE 数据表名1 To 数据表名2 1
ALTER TABLE oldtable RENAME AS newtable; 2
删除表
DROP TABLE [IF EXISTS] 数据表名; 删除表
复制表(结构)
CREATE TABEL IF NOT EXISTS 表名 LIKE 源表名; 复制表(复制表结构)

4. 表内容的操作

CREATE TABLE customers ( userid int NOT NULL AUTO_INCREMENT, name char(50) NOT NULL, address char(200) NOT NULL, email char(50) NOT NULL, PRIMARY KEY(userid) ); 创建一个表

语句 说明
INSERT INTO customers values(10010,'李同学','北京市朝阳区','1234567@qq.com') 插入完整的行(表中的所有字段必须全部插入)
SELECT * FROM customers; 查询以插入的内容
INSERT INTO customers(userid,name,adress,email) values('10011','黄同学','北京市通州区','1234789@qq.com') 根据列插入内容(没有设置默认值和设置为空的字段,必须放在字段中)
INSERT INTO customers(userid,name,adress,email) values ('10011','黄同学','北京市通州区','1234789@qq.com'), ('10012','黄同学','北京市通州区','1234789@qq.com'), ('10013','黄同学','北京市通州区','1234789@qq.com'); 多行插入(用“,”逗号隔开)
将一个表的内容插入到另一个表中
INSERT INTO 表名称1 (字段名…) SELECT 字段名… from 表名称2 将表2的数据内容插入表1中(插入的内容必须一一的对应)
INSERT INTO customer1 (userid,name,address,email) SELECT userid,name,address,email FROM customer; `
删除数据
1) DELETE FROM customers WHERE userid=10010; 从表中删除指定的行(where 后面指定要删除的行)
2)DELETE FROM 表名称; 从表中删除所有的行

修改(更新)数据

  1. 修改(更新)指定行
    UPDATE 表名 SET 字段名=修改为的内容 WHERE 指定的主键;
    eg: UPDATE customers SET email='1234@sina.com' WHERE userid=10010;
    2)修改对应字段的所有值:
    UPDATE customers SET gender=1;
    3)修改(更新)多个列`
    UPDATE customers SET email='123@sian.com',name='刘先生' WHERE userid=10013;

5.mySQL检索数据

语句 说明
SELECT 列名 FROM 表名; 检索单个列
SELECT DISTINCT 列名 FROM 表名; 检索不同的列
SELECT * FROM 表名; 检索全部内容
SELECT 列名,列名,…,列名 FROM 表名; 检索多个列
SELECT 表名.列名 FROM 表名; 完全限制表名
SELECT 表名.列 FROM 数据库.表; 完全限制表名(可以选择不先选择数据库)

6.表数据的查询where(过滤)

语句 说明
where 的句子的一般形式: SELECT 字段名 FROM 表名 WHERE 条件;
= 等于 select name from customers where name='李逵';
<> 不等于 select name from customers where name<>'李逵';
!= 不等于 select name from customers where name!='李逵';
< 小于 (一般情况下后面会跟数字) select name from customers where name<'李逵'; select name from customers where name<'李逵';
<= 小于等于 select * from customers where userid<=10014;
> 大于 select * from customers where userid>10014;
>= 大于等于 select * from customers where userid>=10014;
BETWEEN .. AND ..在指定的两个值之间 select name from customers where userid between 10010 and 10014; 注意:在使用BETWEEN时,必须指定两个值,所需范围的低端值和高端值。这两个值必须用AND关键字分隔。BETWEEN匹配范围中所有的值,包括指定的开始值和结束值.
空值检查(IS NULL)
一般格式 SELECT 列 FROM students WHERE 列 IS NULL;
例子 SELECT name from customers WHERE age is NULL;
AND 操作符
一般格式: SELECT 列,列 FROM students WHERE 条件1 and 条件2;
eg: SELECT name,adress FROM customers WHERE userid<=10015 AND adress='北京市';
OR操作符
一般格式: SELECT 列,列 FROM students WHERE 条件1 OR 条件2;
eg: SELECT * FROM customers WHERE userid<100 or age >19;
IN操作符 相当于多个or连在一起,包含其中的一个即可
一般格式: SELECT 列 FROM 表名 WHERE 列 IN(列值,列值);
eg: SELECT name FROM customers WHERE userid IN(10011,10013);
NOT操作符 否定它之后所跟的任何条件(可以和其他的进行配合)
eg: SELECT name FROM customers WHERE userid NOT IN(10011,10013);

7.通配符查询(过滤)

符号 说明
%(百分号) %表示任何字符出现任意次数
% SELECT name FROM students WHERE name like “李%”
_(下划线) 下划线只匹配任意的单个字符
_ SELECT name FROM customers WHERE name LIKE "李_";

通配符搜索的处理一般要比前面讨论的其他搜索所花时间更长,所以一般不是很推荐使用通配符

8.正则表达式

正则表达式的匹配规则如下:

正则表达式的匹配

python中使用正则表达式(导入python下的re模块)

  1. match函数处理
    match 方法用于查找字符串的头部(也可以指定起始位置),它是一次匹配,只要找到了一个匹配的结果就返回,而不是查找所有匹配的结果。它的一般使用形式如下:
    当匹配成功时,返回一个 Match 对象,如果没有匹配上,则返回 None。
import re
使用match方法进行匹配操作
result = re.match(正则表达式,要匹配的字符串)
如果上一步匹配到数据的话,可以使用group方法来提取数据
result.group()  提取match函数得到的值

2..search函数的处理
earch 方法用于查找字符串的任何位置,它也是一次匹配,只要找到了一个匹配的结果就返回,而不是查找所有匹配的结果,它的一般使用形式如下:
当匹配成功时,返回一个 Match 对象,如果没有匹配上,则返回 None。

3..findall函数的处理
上面的 match 和 search 方法都是一次匹配,只要找到了一个匹配的结果就返回。然而,在大多数时候,我们需要搜索整个字符串,获得所有匹配的结果。
findall 方法的使用形式如下:
findall 以列表形式返回全部能匹配的子串,如果没有匹配,则返回一个空列表。
4.sub函数的处理
sub方法用于替换
ret = re.sub(r"\d+", '998', "python = 997") ,要替换的内容,替换为,被替换的内容
5.split函数的处理
根据匹配进行切割字符串,并返回一个列表
ret = re.split(',','你是一头猪吗?Are you a pig,please tell me.'); 用来切割的字符,要被切割的语句
结果:["你是一头猪吗?Are you a pig:","please tell me."]
6.finditer函数的处理
finditer方法的行为跟 findall 的行为类似,也是搜索整个字符串,获得所有匹配的结果。但它返回一个顺序访问每一个匹配结果(Match 对象)的迭代

贪婪和非贪婪

Python里数量词默认是贪婪的(在少数语言里也可能是默认非贪婪),总是尝试匹配尽可能多的字符;
非贪婪则相反,总是尝试匹配尽可能少的字符。
*? 重复任意次,但尽可能少重复
+? 重复1次或更多次,但尽可能少重复
?? 重复0次或1次,但尽可能少重复

9.mysql的正则匹配

select * from studentinfo where student_name regexp '^牛';
SELECT name,email FROM studentinfo WHERE email REGEXP '.'; python中匹配一些特殊的字符的时候,需要用到转义字符“\”,将对应的符号转换成原来的意思。

10.排序和限制查询

1)单列排序(并指定方向)
升序排列:

SELECT age,name FROM customers ORDER BY age ASC;

降序排列:

SELECT age,name FROM customers ORDER BY age DESC;

2)多列排序(并指定方向)

SELECT name,adress,age FROM customers ORDER BY age DESC,name ASC;
SELECT * FROM studentinfo WHERE gender=1 ORDER BY age; 查询出性别为1的并按照年龄升序排列
SELECT * FROM studentinfo WHERE gender=0 ORDER BY age DESC; 查询出性别为1的并按照年龄j降序排列

限制查询(可以指定开始的位置和显示的条数)
指定返回的行数

SELECT name FROM customers LIMIT 5;

指定从第几行起,返回多少行

SELECT name FROM badboy LIMIT 5,5; 指定开始位置是5(不包括5),返回5行 ``````无头有尾````
SELECT name FROM customers LIMIT 0,1; 注意: 行0 检索出来的第一行为行0而不是行1。

分页:
已知:每页显示m条数据,当前显示第n页求第n页的数据
SELECT * FROM sudents WHERE is_delete=0 LIMIT (n-1)*m,m

使用ORDER BY和LIMIT的组合

SELECT name,age FROM customers ORDER BY age DESC LIMIT 1; 取最大值
SELECT name,age FROM customers ORDER BY age LIMIT 1; => SELECT name,age FROM customers ORDER BY age ASC LIMIT 1; 取最小值

LIMIT 和其他的语句进行配合使用的时候,要放在语句的末尾,作为最后的限制。

11. 聚合函数(汇总函数)

AVG()函数

AVG()通过对表中行数计数并计算特定列值之和,求得该列的平均值。 AVG()可用来返回所有列的平均值,也可以用来返回特定列或行的平均值。

SELECT AVG(age) as avg_age FROM customers;
SELECT AVG(age) as avg_age FROM customers WHERE adress LIKE '北%';

COUNT()函数

COUNT()函数进行计数。可利用COUNT()确定表中行的数目或符合特定条件的行的数目。
COUNT()函数有两种使用方式。 使用COUNT(*)对表中行的数目进行计数, 不管表列中包含的是空值( NULL)还是非空值。

SELECT COUNT(*) FROM customers;

使用COUNT(column)对特定列中具有值的行进行计数,忽略 NULL值。 SELECT COUNT(name) FROM customers;

MAX()函数

MAX()返回指定列中的最大值。 MAX()要求指定列名,如下所示
SELECT MAX(age) FROM customers;

MIN()函数

MIN()的功能正好与MAX()功能相反,它返回指定列的最小值.如下所示
SELECT MIN(age) FROM customers;

SUM()函数

SUM()用来返回指定列值的和(总计)。如下所示
SELECT SUM(age) as sum_age from customers;

聚集不同值

下面的例子使用AVG()函数返回同学的平均价格。 它与上面的SELECT语句相同,但使用了DISTINCT参数,因此平均值只考虑各个不同的年龄:
SELECT AVG(DISTINCT age) from customers;

12.mysql的分组查询

group by + 集合函数

SELECT COUNT(*) AS totalnum,age from customers group by age; AS 是给前面的集合函数返回的时候进行了重新的命名

group by + group_concat()
  • group_concat(字段名)可以作为一个输出字段来使用,
  • 表示分组之后,根据分组结果,使用group_concat()来放置每一组的某字段的值的集合

SELECT gender,group_concat(student_name) FROM studentinfo GROUP BY gender;

group_concat()

group by + with rollup

with rollup的作用是:在最后新增一行,来记录当前列里所有记录的总和

SELECT gender,count(*) FROM studentinfo GROUP BY gender with rollup;

HAVING

HAVING非常类似于WHERE。事实上,目前为止所学过的所有类型的WHERE子句都可以用HAVING来替代。唯一的差别是WHERE过滤行,而HAVING过滤分组。

  • having 条件表达式:用来分组查询后指定一些条件来输出查询结果
  • having作用和where类似,但having只能用于group by

SELECT COUNT(),age,adress FROM customers GROUP BY age,adress HAVING COUNT() >= 2;

SELECT COUNT(*),age,adress FROM customers GROUP BY age,adress HAVING age >= 2;

SELECT COUNT(*),age,adress FROM customers GROUP BY age,adress HAVING age >= 2 and adress LIKE '%搜索关键字%';

HAVING和WHERE的差别 这里有另一种理解方法,WHERE在数据分组前进行过滤,HAVING在数据分组后进行过滤。这是一个重要的区别, WHERE排除的行不包括在分组中。这可能会改变计算值,从而影响HAVING子句中基于这些值过滤掉的分组

分组和排序

SELECT COUNT(*),age,phonenum FROM studentinfo WHERE gender=1 GROUP BY age,phonenum HAVING phonenum is null ORDER BY age DESC; ;

分组和排序、限定查询

SELECT count(*),age,phonenum FROM studentinfo WHERE gender=1 GROUP BY age,phonenum HAVING phonenum is null ORDER BY age ASC LIMIT 2;

SELECT count(*),age,phonenum FROM studentinfo WHERE gender=1 GROUP BY age,phonenum HAVING phonenum is null ORDER BY age ASC LIMIT 2,2;

©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 211,817评论 6 492
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 90,329评论 3 385
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 157,354评论 0 348
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 56,498评论 1 284
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 65,600评论 6 386
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 49,829评论 1 290
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 38,979评论 3 408
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 37,722评论 0 266
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 44,189评论 1 303
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,519评论 2 327
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,654评论 1 340
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 34,329评论 4 330
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 39,940评论 3 313
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,762评论 0 21
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,993评论 1 266
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 46,382评论 2 360
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 43,543评论 2 349

推荐阅读更多精彩内容