SQLyog 打开mysql数据库 sql语句打开数据库

SQL语句

操作数据库(DDL)

连接数据库

mysql -u用户名 -p

enter password：密码

退出数据库

exit

quit

**
**

查看已经创建的数据库

SHOW DATABASES;

注意

• mysql不严格区分大小写
• 分号作为结束符，必须写

**
**

查看数据库版本

select version();

**
**

创建数据库

CREATE DATABASE 数据库名;

CREATE DATABASE 数据库名 charset=utf8;

注意

• charset：指定编码为utf8

查看创建数据库的命令

SHOW CREATE DATABASE 数据库名;

查看当前使用的数据库

SELECT DATABASE();

使用数据库

USE 数据库名;

删除数据库

DROP DATABASE 数据库名;

练习

• 创建数据库：Logic_edu
• 查看已创建的数据库
• 查看当前使用的数据库是否是Logic_edu
• 若不是，则切换使用Logic_edu数据库
• 删除Logic_edu数据库

注意

• SQL语句不区分大小写
• 每行必须以英文状态下分号作为结束
• 当数据库名称有特殊字符时，使用``包括。如：Logic_edu

操作数据表

查看当前数据库中所有表

SHOW TABLES;

创建表

CREATE TABLE 数据表名(字段 类型 约束[, 字段 类型 约束])

需求：

• 创建表名为mytable1，字段id为int并且字段name为varchar(30)
• 创建表名为mytable2，指定id为主键并且字段name为varchar(30)

查看表的创建语句

SHOW CREATE TABLE 数据表名;

**
**

查看表描述信息

DESC 数据表名;

**
**

练习

• 创建 students 表(id、name、age、high、gender、cls_id)
• 创建 classes 表(id、name）

添加表字段

ALTER TABLE 数据表名 ADD 字段 类型;

修改表字段

ALTER TABLE 数据表名 MODIFY 字段 类型; – 不重命名

ALTER TABLE

数据表名

CHANGE

原字段名 新字段名

类型及约束; – 将字段重命名

删除表字段

ALTER TABLE 数据表名 DROP 字段;

练习

• 添加字段

• 字段名称：birthday
• 字段类型：DATETIME

• 修改字段

• 字段类型：DATE

• 修改字段

• 字段名称：birth
• 字段类型：DATE

• 删除该字段

操作数据

新增数据

整行插入

INSERT INTO 数据表名 VALUES(值1,值2,值3…);

指定列中插入数据

INSERT INTO tabname (字段1, 字段2,…) VALUES (值1,值2,…);

指定列中插入多条数据

INSERT INTO 数据表名 (字段1, 字段2,…) VALUES (值1,值2,…),(值1,值2,…);

修改数据

UPDATE 数据表名 SET 字段1=新值，字段2=新值 [WHERE 条件];

删除数据

DELETE FROM 数据表名 [WHERE 条件];

查询数据

查询整个表数据

SELECT * FROM 数据表名;

查询****指定字段数据

SELECT 字段1,字段2 FROM 数据表名;

查询指定字段数据，并给字段起别名

SELECT 字段1 as 别名,字段2 as 别名 FROM 数据表名;

查询指定字段并去重

SELECT DISTINCT 字段1 FROM 数据表名;

where子句

where子句通常结合增删改查使用，用于做筛选的条件。

比如，查询当id=1的数据

select * from Student where id=1

不仅如此，经常结合运算符使用。

比较运算符

运算符	描述
=	等于
>	大于
>=	大于等于
<	小于
<=	小于等于
!=或<>	不等于

逻辑运算符

运算符	描述
a and b	当同时满足a,b时，则为True
a or b	当满足a或b其中一个时，则为True
not a	否定

模糊查询

like关键字用来进行模糊查询，并且结合**%以及_**使用。

• % 表示任意多个任意字符
• _ 表示一个任意字符

范围查询

关键字	描述
in	表示在一个非连续的范围内
between…and…	表示在一个连续的范围内

空判断

关键字	描述
is null	判断空
is not null	判断非空

常用聚合函数

count 总数

• 求students总人数
• 求男性的人数
• 女性的人数

max() 最大值

• 查询最大的年龄
• 女性最大的id

min() 最小值

• 查询未删除的学生的最小编号

sum 求和

• 查询男生年龄和

avg 平均值

• 查询未删除女生的年龄的平均值
• 计算男性的平均年龄，保留2位小数

分组与分组之后的筛选

分组

在数据库中，通过 group by 将查询结果按照1个或多个字段进行分组，字段值相同的为一组。

select … from students group by 需要分组字段;

group_concat(…)

我们通过group_concat(…)查看每组的详细信息

**
**

分组后的筛选

排序

order by 字段 默认升序

order by asc 字段 指定升序

order by desc 字段 指定降序

限制

limit start,count

• start 为偏移量，默认起始0
• count 为条数

注意：

• limit 不能写数学公式
• limit只能写在末尾

表连接

当查询结果的列来源于多张表时，需要将多张表连接成一个大的数据集，再选择合适的列返回mysql。这时我们就需要使用表连接。

分类：

• 内连接
• 外连接

• 左连接
• 右连接

内连接

内连接仅选出两张表中互相匹配的记录

select * from 表1 inner join 表2 on 表1.列 = 表2.列;

左连接

查询的结果为两个表匹配到的数据，左表持有的数据，对于右表中不存的数据使用null填充

select * from 表1 left join 表2 on 表1.列 = 表2.列;

右连接

查询结果为两个表匹配到的数据，右表持有的数据，对于左表中不存在的数据使用null填充。

select * from 表1 right join 表2 on 表1.列 = 表2.列;

子查询

某些情况下，当进行查询的时候，需要的条件是另外一个 select 语句的结果，这个时候，就要用到子查询。

自关联

可以简单的理解为自己与自己进行连接查询。

比如：

一张 areas 表里面有省市区，我们需要在通过这一张表查询某省对应的所有市。

SELECT * FROM areas AS p INNER JOIN areas AS c ON p.`id`=c.`pid` HAVING p.name="湖南";

外键

外键介绍

MySQL的外键约束(FOREIGN KEY)是表的一个特殊字段。对于两个具有关联关系的表而言，相关联字段中的主键所在表就是主表(父表)，外键所在的表就是从表(子表)。

注意：

• 主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。

创建表时设置外键约束

语法：

[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…] REFERENCES <主表名> 主键列1 [，主键列2，…]

实现：

• 班级表

• 学生表

-- 创建班级表
create table classes(
    id int(4) not null primary key,
    name varchar(36)
);

-- 创建学生表
create table student(
    sid int(4) not null primary key,
    sname varchar(30),
    cid int(4) not null
);

在创建 student表时将其 cid 设置为外键。

注意：

• 从表的外键关联必须是主表的主键，并且主键与外键的类型必须保持一致。

在修改表时添加外键约束

alter table 表名 add constraint 外键名字 foreign key(外键字段名) references 外表表名(主键字段名);

注意：

• 主表需存在时创建从表

删除外键约束

ALTER TABLE student DROP FOREIGN KEY fk_cid;

MySQL与Python交互

准备数据

• 创建数据表

-- 创建一个商品 goods 数据表
CREATE TABLE goods(
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
    NAME VARCHAR(150) NOT NULL,
    cate_name VARCHAR(40) NOT NULL,
    brand_name VARCHAR(40) NOT NULL,
    price DECIMAL(10,3) NOT NULL DEFAULT 0,
    is_show TINYINT NOT NULL DEFAULT 1,
    is_saleoff TINYINT NOT NULL DEFAULT 0
);

• 插入数据

-- 插入数据
INSERT INTO goods VALUES(0,'r510vc 15.6英寸笔记本','笔记本','华硕','3399',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'y400n 14.0英寸笔记本电脑','笔记本','联想','4999',DEFAULT,DEFAULT);
INSERT INTO goods VALUES(0,'g150th 15.6英寸游戏本','游戏本','雷神','8499',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'x550cc 15.6英寸笔记本','笔记本','华硕','2799',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'x240 超极本','超级本','联想','4880',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'u330p 13.3英寸超极本','超级本','联想','4299',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'svp13226scb 触控超极本','超级本','索尼','7999',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'ipad mini 7.9英寸平板电脑','平板电脑','苹果','1998',DEFAULT,DEFAULT);
INSERT INTO goods VALUES(0,'ipad air 9.7英寸平板电脑','平板电脑','苹果','3388',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'ipad mini 配备 retina 显示屏','平板电脑','苹果','2788',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'ideacentre c340 20英寸一体电脑 ','台式机','联想','3499',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'vostro 3800-r1206 台式电脑','台式机','戴尔','2899',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'imac me086ch/a 21.5英寸一体电脑','台式机','苹果','9188',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'at7-7414lp 台式电脑 linux ）','台式机','宏碁','3699',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'z220sff f4f06pa工作站','服务器/工作站','惠普','4288',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'poweredge ii服务器','服务器/工作站','戴尔','5388',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'mac pro专业级台式电脑','服务器/工作站','苹果','28888',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'hmz-t3w 头戴显示设备','笔记本配件','索尼','6999',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'x3250 m4机架式服务器','服务器/工作站','ibm','6888',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT);

分表

分表是因为当一张表的数据量比较多时，但是我们只需要查询其中的某个字段数据，就会导致查询效率降低，所以此处我们需要使用到分表。当然后面会细讲。

• 商品分类表

• 创建 商品分类 表

create table goods_cates(
    id int unsigned primary key auto_increment not null,
    name varchar(40) not null
);

• 将 商品表 中的 商品种类 查询并插入到 商品分类表 里

insert into goods_cates (name) select cate_name from goods group by cate_name;

• 将 商品表 中的 商品种类 更改为 商品表分类表 对应的 id

update goods as g inner join goods_cates as c on g.cate_name=c.name set g.cate_name=c.id;

安装pymysql

在Windows操作系统上安装

Python3:pip install pymysql

Python2:pip install MySQLdb

Ubuntu安装：https://www.jianshu.com/p/d84cdb5e6273

Python操作MySQL步骤

Connection 对象

用于建立与数据库的连接

创建对象：调用 connect() 方法

conn=connect(参数列表)

"""
参数host：连接的mysql主机，如果本机是'localhost'
参数port：连接的mysql主机的端口，默认是3306
参数database：数据库的名称
参数user：连接的用户名
参数password：连接的密码
参数charset：通信采用的编码方式，推荐使用utf8
"""

import pymysql

con = pymysql.connect(host = 'localhost',port=3306,database='python-01',user='root',password = 'root',charset = 'utf8')

from pymysql import *

conn = connect(host = 'localhost',port=3306,database='python-01',user='root',password = 'root',charset = 'utf8')

对象的方法

• close()关闭连接
• commit()提交
• cursor()返回Cursor对象,用于执行sql语句并获得结果

Cursor对象

• 用于执行sql语句,使用频度最高的语句为select、insert、update、delete
• 获取Cursor对象:调用Connection对象的cursor()方法

cs1=conn.cursor()

对象的方法

• close()关闭 先关闭游标,在关闭链接
• execute(operation [, parameters ])执行语句，返回受影响的行数，主要用于执行insert、update、delete语句，也可以执行create、alter、drop等语句
• fetchone()执行查询语句时，获取查询结果集的第一个行数据，返回一个元组
• fetchall()执行查询时，获取结果集的所有行，一行构成一个元组，再将这些元组装入一个元组返回

使用Python连接数据库

from pymysql import *

try:
    conn = connect(
        host = "localhost",
        port = 3306,
        user = "root",
        passwd = "root",
        db = 'logic_web',
        charset = 'utf8'
    )
    cursor = conn.cursor()
    cursor.execute('select * from users_banner')    
    result = cursor.fetchone()
    cursor.close()
    conn.close()     
except Exception as e:
    print("Error %d:%s"%(e.args[0],e.args[1]))

ction对象的cursor()方法

cs1=conn.cursor()

对象的方法

• close()关闭 先关闭游标,在关闭链接
• execute(operation [, parameters ])执行语句，返回受影响的行数，主要用于执行insert、update、delete语句，也可以执行create、alter、drop等语句
• fetchone()执行查询语句时，获取查询结果集的第一个行数据，返回一个元组
• fetchall()执行查询时，获取结果集的所有行，一行构成一个元组，再将这些元组装入一个元组返回

使用Python连接数据库

from pymysql import *

try:
    conn = connect(
        host = "localhost",
        port = 3306,
        user = "root",
        passwd = "root",
        db = 'logic_web',
        charset = 'utf8'
    )
    cursor = conn.cursor()
    cursor.execute('select * from users_banner')    
    result = cursor.fetchone()
    cursor.close()
    conn.close()     
except Exception as e:
    print("Error %d:%s"%(e.args[0],e.args[1]))