薛国良 发布的文章

Python数据库操作(基础)

1. 关系数据库介绍

关系数据库是建立在关系数据模型基础上数据库。和普通文件相比,普通文件存储数据有以下缺点:

  • 当文件变大时,普通文件访问会很慢
  • 查找和修改文件部分内容很困难
  • 并发操作难于上锁
  • 缺乏足够的限制机制

关系数据库相对文件有以下优点:

  • 比文件访问速度快
  • 可以按照某种关系查找数据集合
  • 具有内置的并发管理
  • 随机访问数据
  • 有内置的管理权限

2. 起源

1970年,IBM的研究员,埃德加·弗兰克·科德博士发表了《大型共享数据库的关系模型》论文,在该论文的理论指导下,各种关系数据库被研发并应用于各行业。

3. 关系数据库系统产品列表

  • 甲骨文公司的Oracle
  • IBM的DB2
  • 微软的SqlServer、access
  • sybase公司的sybase(2009年SAP收购)
  • 开源数据库MySQL(2008年sun收购,2009年sun被Oracle收购)
  • 小型开源数据库SQLite
  • MariaDB

各种关系数据库之间,有互补和竞争关系。比如Oracle数据库非常昂贵,所以一些有钱又懒的组织,比如金融、银行首选Oracle。IBM的DB2和Oracle是竞争关系。

而MySQL开源免费,因此成为了互联网小公司首选,最后即使他们成长为大公司之后,由于在MySQL上积累雄厚,依旧成为首选。MariaDB是和MySQL是竞争关系。

使用微软体系技术的公司,最好使用SqlServer,单独的SqlServer并不是以上数据库系统的对手,但是在微软体系中,SqlServer还是最好用的。跟微软竞争,向来不是一个产品的事情。

小型数据库Sqlite常用于手机系统和嵌入式设备中,它是一个小巧而简练的数据库。在这个没有什么利润的角落,也有一个竞争对手叫做fastDB,我都快忘记它了。

4. Python程序,PyMySQL和数据库、数据库系统、MySQL命令行之间的关系

python和MySQL.png

我们要使用Python操作数据库,那么以上图中的角色是经常被使用的,他们之间的关系必须了解清楚。

  • 数据库:本质是一个文件,它按照特有的格式,保存数据
  • 数据库系统:本质是一个程序,它可以操作数据库,封装复杂的数据结构,并提供接口供外部使用
  • PyMySQL:本质是一个Python程序,它能够通过数据库系统操作数据库,并提供Python编程接口,供Python程序员使用
  • MySQL命令行:本质是一个程序,可以通过命令操作数据库,比如初始化和检查数据内容是否正确等等

5. 安装软件

  • MySQL系统(包括数据库系统和MySQL命令行),通过下载安装包安装即可
  • PyMySQL,通过pip命令安装即可。pip install pymysql

6. 数据库内部概念

  • 数据库:关系数据集合
  • 数据表:一个数据库由多个表组成
  • 字段:一个数据表由多个字段组成
  • 记录:表中的一个实体数据

7. 通过命令行操作数据库和数据表

一般来说,在应用程序运行之间,首先要在外部通过命令方式,先创建好数据库和数据表。

7.1 数据库操作

  • 查看数据库
    show databases;
  • 创建数据库
    create database 数据库名; //默认字符集是latin1(拉丁文,不支持中文)
    create database 数据库名 character set utf8; //创建一个数据库指定为utf8字符集
  • 删除数据库
    drop database 数据库名;
  • 查看当前数据库
    select database();
  • 切换数据库
    use 数据库名;

7.2 数据表操作

  • 创建数据表
    create table 表名(字段名 字段类型,字段名 字段类型,...);
    create table 表名(字段名 字段类型,字段名 字段类型,...) character set utf8;
  • 删除数据表
    drop table 表名;
  • 修改数据表
    1. 修改表名:
      rename table 原表名 to 新表名;
    2. 增加字段:
      alter table 表名 add column 字段名 字段类型; //column 可省略
    3. 修改字段类型:
      alter table 表名 modify column 字段名 新字段类型;
    4. 修改字段名和字段类型:
      alter table 表名 change column 原字段名 新字段名 新字段类型;
    5. 删除字段:
      alter table 表名 drop column 字段名;
    6. 修改表的字符集
      alter table 表名 character set utf8;
  • 显示数据库中的表
    show tables;
  • 显示数据表的结构
    desc 表名;
    describe 表名;
  • 查看创建表时的语句
    show create table 表名;

7.3 字段类型

  • 整数类型
    tinyint 1字节
    smallint 2字节
    int或integer 4字节
    bigint 8字节
    float(m,n) 4字节 //不指定m,n有可能无法通过where条件查询
    double 8字节
    decimal(m,d) m>d?m+2:d+2 m代表数据的宽度,d代表小数宽度
  • 字符串类型
    char 定长字符串
    varchar 变长字符串
    text 长文本数据
    blob 二进制长文本数据
  • 日期和时间类型
    date 4字节 yyyy-mm-dd 日期
    time 3字节 hh:mm:ss 时间
    year 1字节 yyyy 年份
    datetime 8字节 yyyy-mm-dd hh:mm:ss 混合日期时间
    timestamp 4字节 yyyy-mm-dd hh:mm:ss 混合日期时间 如果未明确赋值或赋值为null,则自动使用当前系统时间
  • 复合类型
    enum 只能使用集合中的一个值或null
    set 只能使用集合中的多个值或null

7.4 案例

创建一个数据库:dberp
创建一个表:temployee,字段如下:

  • fid: int
  • fname: varchar
  • fage: int
  • fedu: int
  • fbirthday: date

打开MySQL命令行程序,输入以下命令:

create database dberp;
use dberp
create table temployee (fid int, fname varchar(16), fsex tinyint, fedu int, fbirthday date);

检查操作结果:

show databases;
show tables;

8. 操作数据表

8.1 SQL语句

SQL 是用于访问和处理数据库的标准的计算机语言。之前学习的create databasecreate table都数据SQL语句的一部分。

如果想要操作表中的数据,需要学习更多的SQL语句。

8.2 操作数据(增、删、改、查)

  • 插入数据
    insert into 表名(字段名,字段名,...) values(字段值,字段值,...); //如果为所有字段插入数据可以不指定字段名
  • 删除数据
    delete from 表名; //删除表中所有数据
    delete from 表名 where 字段名 = ?; //根据条件删除
  • 修改数据
    update 表名 set 字段名 = ?; //修改单个字段
    update 表名 set 字段名 = ?,字段名=?; //修改多个字段
    update 表名 set 字段名 = ? where 字段名 = ?; //根据条件修改
  • 查询数据
    1. 查询所有数据
      select * from 表名;
    2. 查询指定字段
      select 字段名,字段名 from 表名;
    3. 根据条件查询
      select 字段名 from 表名 where 字段名 = ?;
    4. 条件查询运算符
      select * from 表名 where 字段名 > 3; // =,>,>=,<,<=
      select * from 表名 where 字段名 <> 3; // <> 不等于
      select * from 表名 where 字段名 between 3 and 5; //区间
      select * from 表名 where 字段名 is null; //空值
      select * from 表名 where 字段名 is not null; //非空值
      select * from 表名 where 字段名 = ? and 字段名 = ?; //与
      select * from 表名 where 字段名 = ? or 字段名 = ?; //或
    5. 排序查询
      select * from 表名 where 查询条件 order by 字段名 asc; // asc 表示升序排列,可省略
      select * from 表名 where 查询条件 order by 字段名 desc; // desc 表示降序排列
    6. top N 问题
      select * from 表名 limit 0,N; //第0行取N行的数据

8.3 案例

以下是创建职工表的命令

create database dberp;
use dberp
create table temployee (fid int, fname varchar(16), fsex tinyint, fedu int, fbirthday date);

现在需要在职工表中

  • 增加三个职工
fid=3113, fname=tom, fsex=1, fedu=16, fbirthday='1990-02-14'
fid=3114, fname=jack, fsex=1, fedu=19, fbirthday='1989-04-01'
fid=3115, fname=rose, fsex=0, fedu=16, fbirthday='1985-12-28'

插入命令为:

insert into temployee (fid, fname, fsex, fedu, fbirthday) values(3113, 'tom', 1, 16, '1990-02-14');

其余类推。

  • 删除一个员工名为jack
  • 修改员工名字为tom的学历
  • 查询学历大于等于16的员工

9. 在程序中操作数据库

如果使用Python程序访问数据库,那么第一件事需要安装开发包PyMySQL,然后写一个程序来操作数据库,步骤如下:

  • 连接数据库
  • 获得数据库游标
  • 执行SQL语句
  • 如果是查询语句,获得SQL语句返回的数据进行处理
  • 提交数据
  • 关闭数据

9.1 插入数据

import pymysql
import pymysql.cursors

conn = pymysql.connect(user='root', password='123456', db='dberp')
cursor = conn.cursor()

id = input('id:')
name = input('name:')
sex = input('sex:')
edu = input('edu:')
birthday = input('birthday:')

sql = "insert into temployee(fid, fname, fsex, fedu, fbirthday) " \
      "values (%d, '%s', %d, %d, '%s')" \
      % (int(id), name, int(sex), int(edu), birthday)

cursor.execute(sql)
conn.commit()

conn.close()

9.2 查询数据

import pymysql
import pymysql.cursors

conn = pymysql.connect(user='root', password='123456', db='dberp')
cursor = conn.cursor()

sql = "select * from temployee"

cursor.execute(sql)

result = cursor.fetchone()
print(result)
result = cursor.fetchone()
print(result)

conn.close()

# cursor.fetchall()
# cursor.rowcount()

MySQL

MySQL 文档

1. MySQL 数据库的安装

sudo apt install mysql-server-5.7
sudo apt install mysql-client-5.7
sudo apt install libmysqlclient-dev

2. MySQL 服务的启动和停止

sudo service mysql start;
sudo service mysql stop;
sudo service mysql status;
sudo service mysql restart;

3. MySQL 数据库的登录和退出

mysql -u用户名 -p密码
eg. mysql -uroot -p205205
exit

4. 对数据库的操作

  • 查看数据库
    show databases;
    show create database 数据库名; //查看创建数据库时的语句
  • 创建数据库
    create database 数据库名; //默认字符集是latin1(拉丁文,不支持中文)
    create database 数据库名 character set utf8; //创建一个数据库指定为utf8字符集
  • 删除数据库
    drop database 数据库名;
  • 查看当前数据库
    select database();
  • 切换数据库
    use 数据库名;
  • 修改数据库的字符集
    alter database 数据库名 character set utf8;

5. 对用户的操作

  • 查看用户
    select host,user from mysql.user;
  • 创建用户
    create user '用户名'@'允许连接的主机IP' identified by '密码';
    eg.
    create user 'test'@'%' identified by 'test';
    create user 'test'@'127.0.0.1' identified by 'test';
    create user 'test'@'localhost' identified by 'test';
  • 删除用户
    drop user '用户名'@'允许连接的主机IP';
    delete from mysql.user where user = '用户名' and host = '允许连接的主机IP';
  • 修改用户密码
    update mysql.user set password = password('新密码') where user = '用户名' and host = '允许连接的主机IP'; //mysql 5.7已废弃 password 字段,改为 authentication_string
  • 设置用户权限
    grant 权限 on 数据库.* to '用户名'@'允许连接的主机IP'; //需要用户存在
    grant 权限 on 数据库.* to '用户名'@'允许连接的主机IP' identified by '密码'; //自动创建一个用户
    eg.
    grant insert,delete,update,select on test.* to 'test'@'localhost';
    grant all privileges on test.* to 'test'@'localhost'; //为test用户设置test数据库的所有权限
  • 刷新权限表
    flush privileges;

6. 对数据表的操作

  • 创建数据表
    create table 表名(字段名 字段类型,字段名 字段类型,...);
    create table 表名(字段名 字段类型,字段名 字段类型,...) character set utf8;
  • 删除数据表
    drop table 表名;
  • 修改数据表
    1. 修改表名:
      rename table 原表名 to 新表名;
    2. 增加字段:
      alter table 表名 add column 字段名 字段类型; //column 可省略
    3. 修改字段类型:
      alter table 表名 modify column 字段名 新字段类型;
    4. 修改字段名和字段类型:
      alter table 表名 change column 原字段名 新字段名 新字段类型;
    5. 删除字段:
      alter table 表名 drop column 字段名;
    6. 修改表的字符集
      alter table 表名 character set utf8;
  • 显示数据库中的表
    show tables;
  • 显示数据表的结构
    desc 表名;
    describe 表名;
  • 查看创建表时的语句
    show create table 表名;

7. 数据类型

  • 整数类型
    tinyint 1字节
    smallint 2字节
    int或integer 4字节
    bigint 8字节
    float(m,n) 4字节 //不指定m,n有可能无法通过where条件查询
    double 8字节
    decimal(m,d) m>d?m+2:d+2 m代表数据的宽度,d代表小数宽度
  • 字符串类型
    char 定长字符串
    varchar 变长字符串
    text 长文本数据
    blob 二进制长文本数据
  • 日期和时间类型
    date 4字节 yyyy-mm-dd 日期
    time 3字节 hh:mm:ss 时间
    year 1字节 yyyy 年份
    datetime 8字节 yyyy-mm-dd hh:mm:ss 混合日期时间
    timestamp 4字节 yyyy-mm-dd hh:mm:ss 混合日期时间 如果未明确赋值或赋值为null,则自动使用当前系统时间
  • 复合类型
    enum 只能使用集合中的一个值或null
    set 只能使用集合中的多个值或null

8. 对数据的操作

  • 插入数据
    insert into 表名(字段名,字段名,...) values(字段值,字段值,...); //如果为所有字段插入数据可以不指定字段名

  • 删除数据
    delete from 表名; //删除表中所有数据
    delete from 表名 where 字段名 = ?; //根据条件删除
    truncate 表名; //这种方式效率高

  • 修改数据
    update 表名 set 字段名 = ?; //修改单个字段
    update 表名 set 字段名 = ?,字段名=?; //修改多个字段
    update 表名 set 字段名 = ? where 字段名 = ?; //根据条件修改

  • 查询数据

    1. 查询所有数据
      select * from 表名;
    2. 查询指定字段
      select 字段名,字段名 from 表名;
    3. 根据条件查询
      select 字段名 from 表名 where 字段名 = ?;

    1. 字段别名
      select 字段名 as 别名 from 表名; //as 可以省略
    2. 去掉重复数据
      select distinct 字段名 from 表名; //当指定多个字段时,多个字段必须全部匹配才会成功
    3. 滤空修正
      select ifnull(字段名,替换值) from 表名;
    4. 使用算术表达式
      select 字段名 + 字段名 as '别名' from 表名;
    5. 字符串拼接
      select concat(str1,str2,...) as '别名' from 表名;
    6. 常用函数 min,max,avg,count
      select min(字段名) from 表名;

    1. 条件查询运算符
      select * from 表名 where 字段名 > 3; // =,>,>=,<,<=
      select * from 表名 where 字段名 <> 3; // <> 不等于
      select * from 表名 where 字段名 between 3 and 5; //区间
      select * from 表名 where 字段名 is null; //空值
      select * from 表名 where 字段名 is not null; //非空值
      select * from 表名 where 字段名 = ? and 字段名 = ?; //与
      select * from 表名 where 字段名 = ? or 字段名 = ?; //或

    2. 模糊查询
      select * from 表名 where 字段名 like '%xxx'; // % 表示任意个数的任意字符
      select * from 表名 where 字段名 like '_xxx'; // _ 表示单个的任意字符
      select * from 表名 where 字段名 rlike '[0-9]abc'; // [] 表示单个字符的取值范围
      select * from 表名 where 字段名 rlike '[^0-9]abc';// [^] 表示单个字符的非取值范围
      select * from 表名 where 字段名 like '%\%%'; // \ 表示转义,查询字段中包含%的数据

    3. 分组查询
      select * from 表名 where 查询条件 group by 字段名 having 查询条件;

    4. 排序查询
      select * from 表名 where 查询条件 order by 字段名 asc; // asc 表示升序排列,可省略
      select * from 表名 where 查询条件 order by 字段名 desc; // desc 表示降序排列

    5. 嵌套查询
      select * from 表名 where 字段名 = (select 字段名 from 表名 where 字段名=?);

      • 当子查询返回的值只有一个时,才可以直接使用 =,>,< 等运算符;
      • 当子查询返回的值是一个结果集时,需要使用 in,not in,any,some,all 等操作符;
      • all : 匹配结果集中的所有结果, 例如 > all 表示比所有结果都大;
      • any : 匹配结果信中任意一个结果, 例如 < any 表示只要比其中任意一个小就符合条件;
      • in : 查询的数据在子查询的结果集中,相当于 = any;
      • not in : 查询的数据不在子查询结果集中,相当于 <> all;
      • some : 匹配结果集中的部分结果,基本等同于 any,不常用;
      • 子查询通常只用于返回单个字段的结果集,如果需要匹配多个字段,可以使用多表联查(后面讲);
        eg.
        select * from 表名 where 字段名 > all (select 字段名 from 表名);
    6. 多表联查

      • 数据表的别名
        select * from 表名 as 别名; // as 可省略
      • 交叉连接(笛卡尔积)
        //显示两张表的乘积
        select * from 表1 cross join 表2 on 连接条件 where 查询条件; //cross join on 可省略
        select * from 表1,表2 where 查询条件; //默认就是交叉连接,如果 where 条件中有等值判断则称为等值连接
      • 内连接
        //显示两张表的交集
        select * from 表1 inner join 表2 on 连接条件 where 查询条件; //inner 可省略
      • 左外连接
        //左表显示全集,右表显示交集
        select * from 表1 left outer join 表2 on 连接条件 where 查询条件; //outer 可省略
      • 右外连接
        //左表显示交集,右表显示全集
        select * from 表1 right outer join 表2 on 连接条件 where 查询条件; //outer 可省略
      • 联合查询
        //显示两张表的并集,要求两张表的查询字段名必须保持一致
        select id,name from 表1 union select id,name from 表2;
      • 全外连接(MySQL不支持)
        //显示两张表的并集
        select * from 表1 full outer join 表2 on 连接条件 where 查询条件; //outer 可省略
        //可以通过左外连接和右外连接的联合查询来实现
        select * from 表1 left join 表2 on 连接条件 where 查询条件 union select * from 表1 right join 表2 on 连接条件 where 查询条件;

    7. 常用字符串函数

      • upper 和 ucase
        //把所有字符转换为大写字母
        select upper(name) from 表名;
      • lower 和 lcase
        //把所有字符转换为小写字母
        select lcase(name) from 表名;
      • replace(str, from_str, to_str)
        //把str中的from_str替换为to_str
        select replace(字段名,替换前的值,替换后的值) from 表名;
      • repeat(str,count)
        //返回str重复count次的字符串
        select repeat('abc',2) from 表名; // abcabc
      • reverse(str)
        //逆序字符串
        select reverse('abc') from 表名; // cba
      • insert(str,pos,len,newstr)
        //把str中pos位置开始长度为len的字符串替换为newstr
        select insert('abcdef',2,3,'hhh'); // ahhhef
      • substring(str from pos)
        //从str中的pos位置开始返回一个新字符串
        select substring('abcdef',3); // cdef
      • substring_index(str,delim,count)
        //返回str中第count次出现的delim之前的所有字符,如果count为负数,则从右向左
        select substring_index('abacadae','a',3); // abac
      • ltrim(str)
        //去除字符串左边的空格
        select ltrim(' abc');
      • rtrim(str)
        //去除字符串右边的空格
        select rtrim('abc ');
      • trim(str)
        //去除字符串左右两边的空格
        select trim(' abc ');
      • mid(str,pos,len)
        //从str中的pos位置开始返回len个长度的字符串
        select mid('abcdef',2,3); // bcd
      • lpad(str,len,padstr)
        //在str左边填充padstr直到str的长度为len
        select lpad('abc',8,'de'); // dededabc
      • rpad(str,len,padstr)
        //在str右边填充padstr直到str的长度为len
        select rpad('abc',8,'de'); // abcdeded
      • left(str,len)
        //返回str左边的len个字符
        select left('abcd',2); // ab
      • right(str,len)
        //返回str右边的len个字符
        select right('abcd',2); // cd
      • position(substr in str)
        //返回substr在str中第一次出现的位置
        select position('c' in 'abcdc'); // 3
      • length(str)
        //返回字符串的长度
        select length('abcd'); // 4
      • concat(str1,str2,...)
        //合并字符串
        select concat('abc','def','gh'); // abcdefgh
    8. 日期时间函数

      • dayofweek(date)
        //返回date是星期几,1代表星期日,2代表星期一...
        select dayofweek('2017-04-09');
      • weekday(date)
        //返回date是星期几,0代表星期一,1代表星期二...
        select weekday('2017-04-09');
      • dayname(date)
        //返回date是星期几(按英文名返回)
        select dayname('2017-04-09');
      • dayofmonth(date)
        //返回date是一个月中的第几天(范围1-31)
        select dayofmonth('2017-04-09');
      • dayofyear(date)
        //返回date是一年中的第几天(范围1-366)
        select dayofyear('2017-04-09');
      • month(date)
        //返回date中的月份数值
        select month('2017-04-09');
      • monthname(date)
        //返回date是几月(按英文名返回)
        select monthname('2017-04-09');
      • quarter(date)
        //返回date是一年中的第几个季度
        select quarter('2017-04-09');
      • week(date,first)
        //返回date是一年中的第几周(first默认值是0,表示周日是一周的开始,取值为1表示周一是一周的开始)
        select week('2017-04-09');
        select week('2017-04-09',1);
      • year(date)
        //返回date的年份
        select year('2017-04-09');
      • hour(time)
        //返回time的小时数
        select hour('18:06:53');
      • minute(time)
        //返回time的分钟数
        select minute('18:06:53');
      • second(time)
        //返回time的秒数
        select second('18:06:53');
      • period_add(p,n)
        //增加n个月到时期p并返回(p的格式为yymm或yyyymm)
        select period_add(201702,2);
      • period_diff(p1,p2)
        //返回在时期p1和p2之间的月数(p1,p2的格式为yymm或yyyymm)
        select period_diff(201605,201704);
      • date_format(date,format)
        //根据format字符串格式化date
        select date_format('2017-04-09','%d-%m-%y');
      • time_format(time,format)
        //根据format字符串格式化time
        select time_format('12:22:33','%s-%i-%h');
      • curdate() 和 current_date()
        //以'yyyy-mm-dd'或yyyymmdd的格式返回当前日期值
        select curdate();
        select current_date();
      • curtime() 和 current_time()
        //以'hh:mm:ss'或hhmmss格式返回当前时间值
        select curtime();
        select current_date();
      • now(),sysdate(),current_timestamp()
        //以'yyyy-mm-dd hh:mm:ss'或yyyymmddhhmmss格式返回当前日期时间
        select now();
        select sysdate();
        select current_timestamp();
      • unix_timestamp()
        //返回一个unix时间戳(从'1970-01-01 00:00:00'开始到当前时间的秒数)
        select unix_timestamp();
      • sec_to_time(seconds)
        //把秒数seconds转化为时间time
        select sec_to_time(3666);
      • time_to_sec(time)
        //把时间time转化为秒数seconds
        select time_to_sec('01:01:06');
    9. top N 问题
      select * from 表名 limit 0,N; //第0行取N行的数据

    10. 对查询出的数据进行筛选替换 case ... when ... then ... else ... end
      select case sex when 1 then '男' when 2 then '女' else '人妖' end from 表名 //此方式仅限等于条件
      select case when 字段条件 then '要显示的值' end from 表名 //此方式条件更多

9. 索引

  • 普通索引
    create index 索引名 on 表名(字段名(索引长度));
    alter table 表名 add index 索引名 (字段名(索引长度));
    create table 表名(字段名 字段类型,字段名 字段类型,index 索引名 (字段名(索引长度));
  • 唯一索引
    create unique index 索引名 on 表名(字段名(索引长度));
    alter table 表名 add unique 索引名 (字段名(索引长度));
    create table 表名(字段名 字段类型,字段名 字段类型,unique 索引名 (字段名(索引长度));
  • 全文索引
    //只支持 MyISAM 引擎
    create fulltext index 索引名 on 表名(字段名);
    alter table 表名 add fulltext 索引名(字段名);
    create table 表名(字段名 字段类型,字段名 字段类型,fulltext (字段名);
  • 组合索引
    create index 索引名 on 表名(字段名(索引长度),字段名(索引长度),...);
    alter table 表名 add index 索引名 (字段名(索引长度),字段名(索引长度),...;
    create table 表名(字段名 字段类型,字段名 字段类型,index 索引名 (字段名(索引长度),字段名(索引长度));
  • 查看索引
    show index from 表名;
  • 删除索引
    alter table 表名 drop index 索引名;

10. 约束

  • 主键约束
    create table 表名(字段名 字段类型 primary key,字段 字段类型,...);
    //一个表只能有一个主键,这个主键可以由一列或多列组成
    create table 表名(字段1 字段类型,字段2 字段类型,primary key(字段1,字段2);
  • 唯一键约束
    create table 表名(字段名 字段类型 unique,字段名 字段类型,...);
  • 外键约束
    create table 表1(字段1 字段类型,字段2 字段类型,foreign key(字段1) references 表2(字段名),...);
  • 非空约束
    create table 表名(字段名 字段类型 not null,字段名 字段类型,...);
  • 默认值约束
    create table 表名(字段名 字段类型 default 默认值,字段名 字段类型,...);
  • check约束(MySQL 不支持)
    create table 表名(字段1 字段类型,字段2 字段类型,check(字段1 > 30),...);

11. 事务

begin; 或者 start transaction;
要执行的操作;
commit;rollback;

12. 触发器

因为触发器中间需要用到分号,而分号又代表语句结束,所以需要先把结束符设置为别的符号
delimiter && //先把结束符设置为 &&

create trigger 触发器名
{before|after}
insert|update|delete}
on 表名
for each row //行触发器,MySQL不支持语句触发器
begin
触发器执行的操作 //如果要获取表中的数据当作条件,insert时用NEW,delete时用OLD.字段,update时都可以
end;

delimiter ; //把结束符设置回分号

13. 存储过程

  • 创建存储过程
    create procedure 存储过程名(参数)
    begin
    要执行的操作
    end;

eg:
create procedure func(IN a int, IN b int, OUT c int)
begin
set c=a+b;
end;

  • 调用存储过程
    call 存储过程名();
    eg:
    call func(3,5,@c);
    select @c;

  • 删除存储过程
    drop procedure 存储过程名;

14. 视图

create view 视图名
as
查询的SQL语句

15. 数据库的导入导出

  • 导入
    source xxx.sql;
  • 导出
    mysqldump -u root -p 数据库名 表名 > xxx.sql;