MySQL自学之基础知识

MySQL数据库

数据库基本知识

数据库基本概念

数据库就是存放用户数据的地方，而我们常常把数据库和数据管理系统称为数据库。

数据库管理系统

数据库管理系统是管理数据库的一个软件，它充当所有数据的知识库，并对其存储、安全、一致性、并发操作、恢复和访问负责。

数据库基本安装

步骤一：双击打开数据库.EXE文件

步骤二：选择自定义安装

步骤三：修改安装路径以及数据库存储路径

步骤四：选择数据库使用编码

步骤五：选择自动配置环境变量

步骤六：如果未自动配置环境变量，则需要手动添加

DOS环境下的操作

• 开启MySQL服务：【net start MySQL】
• 关闭MySQL服务：【net stop MySQL】

关系数据库基本概念

• 数据表：是关系数据库最基本的数据存储单元。
• 记录：数据表中的每一行被称为一条记录。
• 字段：数据表中的每一列被称为一个字段。

MySQL数据库语句基础

MySQL基本命令

数据库查看

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| textone            |
+--------------------+
5 rows in set (0.00 sec)

数据表查看

mysql> show tables;
+-------------------+
| Tables_in_textone |
+-------------------+
| dept              |
| emp               |
| text              |
+-------------------+
3 rows in set (0.00 sec)

数据表使用

use  emp;

查看表结构

mysql> desc emp;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| eid    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20) | YES  |     | NULL    |                |
| birs   | date        | YES  |     | NULL    |                |
| sal    | int(11)     | YES  |     | NULL    |                |
| deptno | int(11)     | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

查看表定义

mysql> show create table emp;
+-------+-----------------------------------------------
| Table | Create Table
+-------+-----------------------------------------------
| emp   | CREATE TABLE `emp` (
  `eid` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `birs` date DEFAULT NULL,
  `sal` int(11) DEFAULT NULL,
  `deptno` int(11) DEFAULT NULL,
  PRIMARY KEY (`eid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=gbk |
+-------+-----------------------------------------------
1 row in set (0.00 sec)

常见数据库对象

数据库命名规则

• 必须以字母开头
• 可包括数字和三个特殊字符（# _ $）
• 不要使用MySQL的保留字
• 同一Schema(数据库对象)下的对象不能同名

DDL语句

列类型

MySQL支持多种列类型

• 数值类型
• 日期类型
• 字符串类型

创建数据库表

语法：create table 表名 （列名 列类型，列名 列类型）；

删除数据库表

语法：drop table 表名；

增加列字段语句

语法：alter table 表名 add（字段名 字段类型，字段名 字段类型）；

修改列字段语句

语法：alter table 表名 modify 字段名 字段类型；

删除列字段语句

语法：alter table 表名 drop 字段名 ；

重命名列名

语法：alter table 表名 change 旧字段 新字段 字段类型；

重命名表名

语法1：alter table 表名 rename 新表名；
语法2：rename table 表名 to 新表名；

truncate 命令

删除表中所有的数据，但会保留表结构，不可选择性删除。
语法：truncate 表名；

约束

数据库约束

非空约束

创建语法：create table 表名（列字段 列类型 not null）;

mysql> create table test(id int not null);
Query OK, 0 rows affected (0.22 sec)

mysql> desc test;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

删除语法：alter table 表名 modify 列字段 列类型 ;

mysql> alter table test modify id int;
Query OK, 0 rows affected (0.33 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

唯一约束

• 唯一性约束条件确保所在的字段或者字段组合不出现重复值
• 唯一约束条件的字段允许出现多个NULL
• 同一张表内可创建多个唯一约束
• 唯一约束可由多列组合而成
• 创建唯一约束时，系统会创建对应的索引
• 如果不给唯一约束起名，该唯一约束默认与列名相同
  创建语法：create table 表名（列字段 列类型 unique）;

mysql> create table test(id int unique);
Query OK, 0 rows affected (0.07 sec)

mysql> desc test;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  | UNI | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

删除语法：alter table 表名 drop index 约束名;

mysql> alter table test drop index id;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

主键约束

主键约束相当于非空约束和唯一约束。
一个表中最多只有一个主键
创建语法：create table 表名（列字段 列类型 primary key）;
修改语法：alter table 表名 modify 列字段 列类型 primary key ;

mysql> alter table test modify id int primary key;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

删除语法：alter table 表名 drop primary key;

mysql> alter table test drop primary key;
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

在创建主键时可为主键设置自增长功能。
语法格式：create table 表名（列字段 列类型 primary key auto_increment）;

外键约束

用于保证两个数据表之间的参照完整性。
创建语法：create table 表名（列字段 列类型 foreign key（外键列名）references 主表名（参照列））;
修改语法：alter table 表名 add foreign key（外键列名）references 主表名（参照列）;

mysql> alter table ttt add foreign key(eid) references test(id);
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc ttt;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| eid   | int(11) | YES  | MUL | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

删除约束：alter table 表名 drop foreign key 约束名;

mysql> alter table ttt drop foreign key ttt_ibfk_1;
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

删除索引：alter table 表名 drop index 索引名;

mysql> alter table ttt drop index eid;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc ttt;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| eid   | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

定义约束名称：create table 表名（列字段 列类型 constraint 约束名 foreign key（外键列名）references 主表名（参照列））;

组合索引

创建table时：

mysql>create table user(id int primary key auto_increment, name varchar(20),age varchar(5),
		index name_age_id(name,age));

后续新增时：

mysql>create index name_age_id on user(name, age);

DML语句

插入语句

• 语法1：insert into 表名（字段名，字段名）values（值，值）；
• 语法2：insert into 表名 values（所有字段的值）；
• 语法3：insert into 表名 values（所有字段的值），（所有字段的值）；

mysql> insert into emp values(1,'zs','2018-11-22',2000,1),(2,'ls','2018-1-22',2200,2);
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from emp;
+-----+------+------------+------+--------+
| eid | name | birs       | sal  | deptno |
+-----+------+------------+------+--------+
|   1 | zs   | 2018-11-22 | 2000 |      1 |
|   2 | ls   | 2018-01-22 | 2200 |      2 |
+-----+------+------------+------+--------+
2 rows in set (0.00 sec)

修改语句

• 语法1：update 表名 set 修改的字段名=新值；
• 语法2：update 表名 set 修改的字段名=新值 where 条件；

mysql> update emp set sal = 4000 where eid =2;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from emp;
+-----+------+------------+------+--------+
| eid | name | birs       | sal  | deptno |
+-----+------+------------+------+--------+
|   1 | zs   | 2018-11-22 | 2000 |      1 |
|   2 | ls   | 2018-01-22 | 4000 |      2 |
+-----+------+------------+------+--------+
2 rows in set (0.00 sec)

删除语句

• 语法：delete from 表名 where 条件；

mysql> delete from emp  where eid =2;
Query OK, 1 row affected (0.04 sec)

mysql> select * from emp;
+-----+------+------------+------+--------+
| eid | name | birs       | sal  | deptno |
+-----+------+------------+------+--------+
|   1 | zs   | 2018-11-22 | 2000 |      1 |
+-----+------+------------+------+--------+
1 row in set (0.00 sec)

DQL语句

查询语句

查询语法：select {* [列字段]} from 表名；
起别名语法：select {* [列字段]} as 别名 from 表名；
带条件语法：select {* [列字段]} as 别名 from 表名 where 条件；

运算符

比较运算符


between 运算符
select 列表名 from 表名 where 工资 between 低值 and 高值；
in（list） 运算符
select 列表名 from 表名 where 部门 in （部门表）；
like 运算符
%表示零个或者多个字符
_表示一个字符
select 列表名 from 表名 where 名字 like‘l%’；
is null 运算符
select 列表名 from 表名 where 名字 is null；
逻辑运算符

NOT优先级大于AND优先级大于OR优先级
limit语法
select 列表名 from 表名 where id limit 起始索引，[记录数]；
order by语法
升序语法：select 列表名 from 表名 order by 字段名 [asc];
降序语法：select 列表名 from 表名 order by 字段名 desc;
索引
创建语法：create index 索引名 on 表名（字段名）；
删除语法：drop index 索引名 on 表名；

函数

字符串函数

数值函数

日期函数

流程函数

其他函数

聚合函数

group by 子句

语法：select 列表名 from 表名 group by 列字段；

Having 子句

语法：select 列表名 from 表名 group by 列字段 Having 判断条件；

REGEXP正则语句

# 匹配以w|n|t开头的
select * from user where name REGEXP "^[wnt]"
# 匹配a-zA-Z
select * from user where name REGEXP "[a-zA-Z]+"

多表连接

内连接

等值连接
语法：select 列表名 from 表名1，表名2 where 等值判断；
非等值连接
语法：select 列表名 from 表名1，表名2 where 工资 between 表2底限 and 表2高限；

外连接

左外连接
语法：select 列表名 from 表名1 left join 表名2 on 等值判断；
右外连接
语法：select 列表名 from 表名1 right join 表名2 on 等值判断；

子查询

语法：select 列表名 from 表名 where 工资大于（select 平均工资 from 表名）；

事务

事务的特点：

1. 原子性：要么执行要么不执行。
2. 一致性：事务完成时，所有数据保持一致。
3. 隔离性：一个事务在修改时，其他人不能对此事务进行修改。
4. 持久性：事务对数据库的修改是持久有效的。
   常用命令：

• 关闭自动提交事务：set autocommit = 0;
• 开启自动提交事务：set autocommit = 1;
• 手动提交事务：commit（）；
• 设置回滚点：savepoint 回滚点名字；
• 回滚至回滚点： rollback to 回滚点名字；
• 全部回滚：rollback ；

MySQL数据库与Python联用

模块安装

Python中需要先下载第三方库函数。

C:\Users\Administrator>pip install -U pymysql
Collecting pymysql

数据库与Python连接

连接数据库

Python执行数据库DML语言

#coding = utf-8
'''
    数据库DML语句
'''
#定义第三方模块
import pymysql
#连接数据库
'''
    connect函数参数
    @host:主机地址
    @port:端口号
    @user:账号
    @passwd:密码
    @db:数据库名
    @charset:连接编码(可省略)
'''
con = pymysql.connect(host='localhost',port= 3306,user='root',passwd='root',db='textone')
# 获取游标对象（作用执行SQL语句）
cur = con.cursor()
#执行SQL数据(DML语句)
try:
    cur.execute('insert into dept values(4,"企管部")')
    # 事务提交
    con.commit()
    print('事务提交成功')
except:
    print('事务提交失败')
    con.rollback()
    print('回滚成功')
#关闭连接
cur.close()
con.close()

结果：

Python执行数据库DQL语言

#coding = utf-8
'''
    数据库DQL语句
'''
#定义第三方模块
import pymysql
#连接数据库
'''
    connect函数参数
    @host:主机地址
    @port:端口号
    @user:账号
    @passwd:密码
    @db:数据库名
    @charset:连接编码(可省略)
'''
con = pymysql.connect(host='localhost',port= 3306,user='root',passwd='root',db='textone')
# 获取游标对象（作用执行SQL语句）
cur = con.cursor()
#执行SQL数据(DML语句)
try:
    cur.execute('select eid,name,sal,ename from emp,dept where emp.deptno = dept.deptno')
    #显示第一条数据
    print(cur.fetchone())
    # 显示所有数据
    print(cur.fetchall())
    #指定获取的记录数
    print(cur.fetchmany(3))
    #显示所有数据
    lenth =cur.fetchall()
    for i in range(len(lenth)):
        print(lenth[i])
    print('查询成功')
except:
    print('查询成功失败')
#关闭连接
cur.close()
con.close()

cur.fetchone()、cur.fetchall()、cur.fetchmany(3)不能联用，各自会抢占数据表资源，使得后者不能查询成功