language mysql_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 表名称;

从表中删除所有的行

修改(更新)数据

修改(更新)指定行

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 * 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 条件１ and 条件２;

eg：

SELECT name,adress FROM customers WHERE userid<=10015 AND adress='北京市';

OR操作符

一般格式：

SELECT 列,列 FROM students WHERE 条件１ OR 条件２;

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模块)

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;