mysql学习笔记

发表于 | 分类于 | | 浏览 次
字数统计: 2,326 | 阅读时长 ≈ 10

mysql数据库

MySQL是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

mysql数据库安装

安装mysql数据库，5.6版本开始的数据库，会在~/.mysql_secret生成随机密码
使用随机密码串登录mysql后，需要修改root密码才能进行操作

1
2
3
4
cat /root/.mysql_secret 
# The random password set for the root user at Thu Sep  8 10:35:16 2016 (local time): zUtb3keu
mysql -uroot -pzUtb3keu
mysql> set password for root@"localhost"=password("123456");  #修改root用户的密码

mysql数据库存储过程

mysql库操作

mysql库名命名规则：

  1. 库名可以使用数字、字母和下划线，但不允许纯纯数字
  2. 库名必须具有唯一性
  3. 库名区分大小写
  4. 库名不能使用特殊字符和sql关键字
  • 查看已有的库 show databases;
  • 创建新库 create database newdata;
  • 切换库 use database newdata;
  • 显示当前所在库 select database();
  • 删除已有库 drop database newdata;
  • 显示当前库已有的表 show tables; 需要进入库才能操作哦

mysql表操作

  • 新建表
    create table 表名(字段1 类型，字段2 类型，字段3 类型);

  • 查看表中的内容
    select 字段1,字段2,字段3 from 表名;

  • 查看表结构
    desc 表名;

  • 向表中插入数据
    insert into 表名 values(字段类表);

  • 删除表记录
    delect * from 表名;

  • 删除表
    drop table 表名;

mysql数据类型

关键字 长度 范围有符号 范围无符号 功能
char 255字节 * * 字符类型
varchar 65532字节 * * 变长字符
blob/text 大于65532 * * 大文本类型
tinyint 1字节 -128~127 0~255 微整型
smallint 2字节 -32768~32767 0~65535 小整型
mediumint 3字节 -2^23~2^23-1 0~2^24-1 中整型
int 4字节 -2^31~2^31-1 0~2^32-1 大整型
bigint 8字节 -2^63~2^63-1 0~2^64-1 极大整型
float 4字节 * * 单精度浮点类型
double 8字节 * * 双精度浮点类型
year * * * 年(YYYY)
date * * * 月日(YYYYMMDD)
time * * * 时间(HHMMSS)
datetime * * * 年月日时分秒(YYYYMMDDHHMMSS)
timestamp * * * 年月日时分秒(YYYYMMDDHHMMSS)

注：

datetime和timestamp类型的区别是，如果不给datetime赋值则默认为null，而如果不给timestamp赋值则使用当前系统时间复制

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> create table table_time(time1 datetime,time2 timestamp);
mysql> insert into table_time values(now(),now());
mysql> insert into table_time(time1)values(19911205);
mysql> insert into table_time(time2)values(19920206);
mysql> select * from table_time;
+---------------------+---------------------+
| time1               | time2               |
+---------------------+---------------------+
| 2016-09-08 16:45:24 | 2016-09-08 16:45:24 |
| 1991-12-05 00:00:00 | 2016-09-08 16:45:49 |
| NULL                | 1992-02-06 00:00:00 |
+---------------------+---------------------+
3 rows in set (0.00 sec)

mysql时间函数

函数 作用
now() 获取当前时间
year() 截取给定时间中的年分
month() 截取给定时间中的月份
date() 截取给定时间中的月日
day() 截取给定时间中的日期
time() 截取给定时间中的时间

mysql枚举和列表

mysql中有两个选择型数据类型:枚举和列表
枚举类型只能在给定的值中选择一个，而列表类型可以在给定的值中选择多个。但这两种类型有一个共同点，只能在给定的值中选择。

  • 枚举 enum()
  • 列表 set()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysql> create table t6(
    -> name char(10),
    -> birthday datetime,
    -> sex enum("boy","girl","other"),
    -> likes set("reading","sleeping","football")
    -> );
mysql> desc t6;
+----------+--------------------------------------+------+-----+---------+-------+
| Field    | Type                                 | Null | Key | Default | Extra |
+----------+--------------------------------------+------+-----+---------+-------+
| name     | char(10)                             | YES  |     | NULL    |       |
| birthday | datetime                             | YES  |     | NULL    |       |
| sex      | enum('boy','girl','other')           | YES  |     | NULL    |       |
| likes    | set('reading','sleeping','football') | YES  |     | NULL    |       |
+----------+--------------------------------------+------+-----+---------+-------
mysql> insert into t6 values("mazhen",now(),"girl","sleeping,football");
mysql> select * from t6;
+--------+---------------------+------+-------------------+
| name   | birthday            | sex  | likes             |
+--------+---------------------+------+-------------------+
| mazhen | 2016-09-08 16:59:56 | girl | sleeping,football |
+--------+---------------------+------+-------------------+

mysql约束条件

not null 不允许字段为空
default 给字段设置默认值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
mysql> create table t7(
    -> name char(10) not null,
    -> age int(5) not null default 20,
    -> sex enum("boy","girl","no") not null default "boy"
    -> );
mysql> desc t7;
+-------+-------------------------+------+-----+---------+-------+
| Field | Type                    | Null | Key | Default | Extra |
+-------+-------------------------+------+-----+---------+-------+
| name  | char(10)                | NO   |     | NULL    |       |
| age   | int(5)                  | NO   |     | 20      |       |
| sex   | enum('boy','girl','no') | NO   |     | boy     |       |
+-------+-------------------------+------+-----+---------+-------+
mysql> insert into t7(name) value("mz");
mysql> select * from t7;
+------+-----+-----+
| name | age | sex |
+------+-----+-----+
| mz   |  20 | boy |
+------+-----+-----+
mysql> insert into t7 values(null,94,girl);
ERROR 1054 (42S22): Unknown column 'girl' in 'field list'
mysql> insert into t7 values("ababa",94,"girl");
mysql> select * from t7;
+-------+-----+------+
| name  | age | sex  |
+-------+-----+------+
| mz    |  20 | boy  |
| ababa |  94 | girl |
+-------+-----+------+

修改表结构

alter table 表名 执行动作

  • 添加新字段
    alter table 表名 add 字段名 类型(宽度) 约束条件;
    添加新字段的时候可以使用first使添加的新字段在所有字段之前，或者使用after 已有字段名使添加的字段在指定字段之后。
  • 删除已有字段
    alter table 表名 drop 字段名;
  • 修改字段类型（修改后的类型若与已有数据冲突则不允许修改）
    alter table 表名 modify 字段名 类型(宽度) 约束条件;
  • 修改字段名
    alter table 表名 change 字段名 类型(宽度);
  • 删除字段
    alter table 表名 drop 字段名;
    注：

    alter的操作可以写在一行sql语句。每个动作之前使用,分割

mysql索引

mysql索引的作用，可以加快数据库查询的速度

mysql索引类型

  • 普通索引 index
  • 唯一索引 unqiue
  • 全文索引 fulltext
  • 主键 primary key
  • 外建 foreign key
    在使用中最常用的索引类型有：index、pirmary key和forgign key

mysql索引有

  • brtee(二叉数)算法 <—-最常用的算法>
  • hash算法
  • b+tree算法

索引操作

  • 查看索引
    show index from 表名;

  • 创建索引

    • 建表时创建索引
      create table 表名(字段列表,index(字段名),index(字段名));
    • 给已有表中的字段设置索引
      create index 索引名 on 表名(字段名);
  • 删除索引
    drop index 索引名 on 表名;

mysql主键

在mysql中主键primary key规则

一个表中只能有一个主键
主键的值不能为空且不可重复
表中如果需要多个字段设置为主键叫复合主键，复合主键必须一起创建字段
主键通常和auto_increment连用

注：创建主键时，单主键可以在字段名后面加primary key关键字约束，而符合主键必须在最后使用primary key(字段1,字段2)创建。

  • 创建主键
    • 创建表时创建主键
      create table 表名(字段表,primary key(字段名));
      create table 表名(字段名1 类型 primary key,字段名2 类型,....);
    • 在已有的表中添加主键
      alter table 表名 add primary key(字段名);

  • 删除主键
    alter table 表名 drop primary key;

  • 创建复合主键

    只要符复主键字段的值不同是重复就可以赋值
    crate table 表名(字段表,primary key(字段1,字段2));

  • 删除符合主键

    复合主键删除方式和普通主键删除方式一致。只是，删除符合主键，会把所有主键删除。

唯一索引unique

一个表中可以有多个unique字段，字段值不可以重复但可以赋空值。

  • 创建唯一索引
    crteat table 表名(字段表,unique(字段名1),unique(字段名2));
  • 添加唯一索引
    create unique index 索引名 on 表名(字段名);
  • 删除唯一索引
    alter table 表名 drop index 索引名;

mysql外键

mysql外键foreign key使用规则

表的存储引擎必须是innodb
字段类型必须一致
主表的被参考字段必须是索引的一种

外键的作用：

保证数据的一致性

  • 创建外键

    1
    2
    3
    4
    5
    6
    7
    8
    create table 表名(
    id int primary key,
    name varchar(10) not null,
    tel char(11) unique,
    bm_id int,
    index(name),
    foregin key(bm_id) referneces 父表(bm_id) on update cascade on delete cascade;
    )engine=innodb;

  • 删除外键

    外键约束的情况下，父表不能被删除，并且父表中被关联的字段也不能删除

    1
    alter table 表名 drop foregin key 外键名;

mysql存储引擎

  • 查看已有表的存储引擎
    show create table 表名;

  • 设置建表时的存储引擎和字符集

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    Create Table: CREATE TABLE tb17 (
      id int(11) NOT NULL,
      name varchar(10) DEFAULT NULL,
      tel char(11) NOT NULL,
      sex enum('boy','girl') DEFAULT NULL,
      PRIMARY KEY (id),
      UNIQUE KEY tel (tel),
      index(name)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    #ENGINE为存储引擎 CHARSET设置字符集

  • 修改存储引擎
    alter table 表名 engine=引擎名

  • 查看mysql支持的存储引擎

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    mysql> show engines;
    +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
    | Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
    +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
    | PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
    | CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
    | MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
    | BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
    | MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
    | MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
    | ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
    | InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
    | FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
    +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
    9 rows in set (0.00 sec)

  • 修改mysql默认存储引擎

    1
    2
    3
    vim /etc/my.cnf
    #在{mysqld}中添加
    default-storage-engine=存储引擎

mysql存储引擎特点

在日常工作中，最常用的是myisam和innodb。
myisam特点:

  1. 独享表空间
  2. 表名.frm用于存储表结构
  3. 表名.myd用于存储表记录
  4. 表名.myi用于存储表索引
  5. 支持表级锁

innodb特点:

  1. 共享表空间
  2. 表名.frm用于存储表结构
  3. 表名.idb用户存储表记录和表索引
  4. 支持行级锁
0%