MySQL 构建MySQL服务器---->CPU，内存，硬盘（存储） 一、安装MySQL yum -y install perl-Data-Dumper perl-JSON perl-Time-HiRes //安装依赖文件 mysql-5.7.17-1.el7.x86_64.rpm-bundle.tar mysql-community-client-5.7.17-1.el7.x86_64.rpm mysql-community-common-5.7.17-1.el7.x86_64.rpm mysql-community-devel-5.7.17-1.el7.x86_64.rpm mysql-community-embedded-5.7.17-1.el7.x86_64.rpm mysql-community-embedded-compat-5.7.17-1.el7.x86_64.rpm mysql-community-embedded-devel-5.7.17-1.el7.x86_64.rpm mysql-community-libs-5.7.17-1.el7.x86_64.rpm mysql-community-libs-compat-5.7.17-1.el7.x86_64.rpm mysql-community-minimal-debuginfo-5.7.17-1.el7.x86_64.rpm mysql-community-server-5.7.17-1.el7.x86_64.rpm mysql-community-test-5.7.17-1.el7.x86_64.rpm rpm -Uvh mysql-community-*.rpm //源码包安装 rpm -qa |grep -i mysql //查看安装玩后状态 查看配置文件 ls /etc/my.cnf 启动服务 systemctl status mysqld systemctl status mysqld ps -C mysqld netstat -utnalp |grep :3306
数据目录 ls /var/lib/mysql/
grep mysql /etc/passwd 查看所有者所属组 修改表
修改MySQL密码 #ls /var/log/mysqld.log #grep password /var/log/mysqld.log查看本地数据库的初始化密码 #rpm -qf /usr/bin/mysql #mysql -hlocalhost -uroot -p‘密码’ mysql> set global validate_password_policy=0; //修改密码只验证长度 Query OK, 0 rows affected (0.00 sec) mysql> set global validate_password_length=6; //修改密码长度为6 Query OK, 0 rows affected (0.00 sec) mysql> alter user user() identified by "123456" //修改登陆密码
使用永久配置文件 vim /etc/my.cnf validate_password_policy=0 validate_password_length=6
把数据存储到数据库服务器上的过程 1、连接数据库服务器 客户端自己提供连接工具（图形 命令行） --命令行使用mysql 2、创建库（文件夹） 创建库 --库名可用数字，字母，下划线 --不能是纯数字，关键词，特殊符号 create database 库名； 查看已有库 show databases； 删除库 drop database 库名； 切换库 use 库名； 查看库里已有的表 show tables； //表，相当于系统文件 查看当前所在的库 select database（）；
3、建表（文件） create table 库名.表名（ 字段名 字符类型， 字段名 数值类型， ...... name char（10） ...... age int ）； 插入表记录 insert into 库名.表名 values（值列表）；
查看表结构 describe 表名 查看表记录 select * from 库名.表名 删除表记录 delete from 库名.表名 删除表 drop table
mysql数据类型 支持的数据类型有那些？ --数值型：体重、身高、成绩、工资 --字符型：姓名、工作单位、通信地址 --枚举型：兴趣爱好、性别 --日期时间型：出生日期、注册时间
数值类型：整型、浮点型 根据存储数值的范围整型类型为: tinyint smallint mediumint int bigint unsigned 无符号
浮点型：根据存储数值的范围分为 单精度（n，m） 双精度（n，m） n表示总位数 m表示小数位的位数 pay float（5，2） 最大999.99 最小-999.99
mysql> create table t1(id tinyint unsigned zerofill); Query OK, 0 rows affected (0.40 sec) mysql> desc t1; +-------+------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------------------+------+-----+---------+-------+ | id | tinyint(3) unsigned zerofill | YES | | NULL | | +-------+------------------------------+------+-----+---------+-------+ 1 row in set (0.00 sec)
mysql> create table t2(pay float(7,2)); Query OK, 0 rows affected (0.46 sec)
mysql> desc t2; +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | pay | float(7,2) | YES | | NULL | | +-------+------------+------+-----+---------+-------+ 1 row in set (0.00 sec)
mysql> create table t4( -> age float(7,2), -> high float(3,2) -> ); Query OK, 0 rows affected (0.36 sec)
mysql> desc t4; +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | age | float(7,2) | YES | | NULL | | | high | float(3,2) | YES | | NULL | | +-------+------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) insert into t4 values（11211，1.82）; ########################################################### 字符类型 --定长:char(字符数) 最大长度255字符 不够指定字符数时再右边用空格补齐 字符数超出时，无法写入数据 --varchar（字符数） 按数据实际大小分配存储空间 字符数超出时，无法写入数据 --大文本类型：text/blob 字符数大与65535存储时使用 mysql> create table t8( -> name char(10), -> class char(7), -> address char(15), -> mail varchar(30) -> ); mysql> desc t8; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | name | char(10) | YES | | NULL | | | class | char(7) | YES | | NULL | | | address | char(15) | YES | | NULL | | | mail | varchar(30) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> insert into t8 values("jim","nsd1709","beijing","123456@qq.com") Query OK, 1 row affected (0.04 sec)
mysql> select * from t8; +------+---------+---------+---------------+ | name | class | address | mail | +------+---------+---------+---------------+ | jim | nsd1709 | beijing | 123456@qq.com | +------+---------+---------+---------------+ 1 row in set (0.00 sec) #################################################################### 日期时间类型： 年 year YYYY 2017 日期 date YYYYMMDD 20171220 时间 time HHMMSS 155302 日期时间： datetime YYYYMMDDHHMMSS timestamp YYYYMMDDHHMMSS
mysql> create table t9( -> name char(10), -> age tinyint, -> s_year year, -> uptime time, -> birthday date, -> party datetime -> ); Query OK, 0 rows affected (0.37 sec)
mysql> desc t9; +----------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+------------+------+-----+---------+-------+ | name | char(10) | YES | | NULL | | | age | tinyint(4) | YES | | NULL | | | s_year | year(4) | YES | | NULL | | | uptime | time | YES | | NULL | | | birthday | date | YES | | NULL | | | party | datetime | YES | | NULL | | +----------+------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) mysql> insert into t9 values("Tom",24,1992,073000,19920221122020,20180131122100); Query OK, 1 row affected, 1 warning (0.04 sec) mysql> select * from t9; +------+------+--------+----------+------------+---------------------+ | name | age | s_year | uptime | birthday | party | +------+------+--------+----------+------------+---------------------+ | Tom | 24 | 1992 | 07:30:00 | 1992-02-21 | 2018-01-31 12:21:00 | +------+------+--------+----------+------------+---------------------+ 1 row in set (0.00 sec) #################################################### 时间函数 now（） 获取调用次函数时的系统日期时间 sysdate（） 执行时动态获得系统日期时间 sleep（N） 休眠N秒 curdate（） 获得当前的系统日期 curtime（） 获得当前的系统时刻 month（） 获得指定时间中的月份 date（） 获得指定时间中的日期 time（） 获取指定时间中的时刻
mysql> select * from t9; +-------+------+--------+----------+------------+---------------------+ | name | age | s_year | uptime | birthday | party | +-------+------+--------+----------+------------+---------------------+ | Tom | 24 | 1992 | 07:30:00 | 1992-02-21 | 2018-01-31 12:21:00 | | Jerry | 25 | 1991 | 06:50:55 | 1991-08-19 | 2018-01-31 12:21:00 | +-------+------+--------+----------+------------+---------------------+ 2 rows in set (0.00 sec) mysql> insert into t9 values("kenji",19,year(now()),time(now()),date(now()),now()); Query OK, 1 row affected (0.04 sec) mysql> select * from t9; +-------+------+--------+----------+------------+---------------------+ | name | age | s_year | uptime | birthday | party | +-------+------+--------+----------+------------+---------------------+ | Tom | 24 | 1992 | 07:30:00 | 1992-02-21 | 2018-01-31 12:21:00 | | Jerry | 25 | 1991 | 06:50:55 | 1991-08-19 | 2018-01-31 12:21:00 | | kenji | 19 | 2017 | 03:55:12 | 2017-12-20 | 2017-12-20 03:55:12 | +-------+------+--------+----------+------------+---------------------+ 3 rows in set (0.00 sec) ########################################################### 枚举类型：字段的值只能在列表的范围内选择 字段名 enum（值列表） 只能选择一个值,在赋值时可用数字选择。 字段名 set（值列表） 多选
mysql> create table t12( name char(10), sex enum("boy","girl"), yourlikes set("book","film","game","study") ); Query OK, 0 rows affected (0.43 sec)
mysql> desc t12; +-----------+-----------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-----------------------------------+------+-----+---------+-------+ | name | char(10) | YES | | NULL | | | sex | enum('boy','girl') | YES | | NULL | | | yourlikes | set('book','film','game','study') | YES | | NULL | | +-----------+-----------------------------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> insert into t12 values("ZhouMing","boy","book,film"); Query OK, 1 row affected (0.04 sec)
mysql> select * from t12; +----------+------+-----------+ | name | sex | yourlikes | +----------+------+-----------+ | ZhouMing | boy | book,film | +----------+------+-----------+ 1 row in set (0.00 sec) ############################################################## 约束条件:作用限制赋值 --Null 允许为空，默认设置 --NO NULL 不允许为空 Key 索引类型 Default 设置默认值，缺省为NULL
mysql> create table t13( name char(10) not null, sex enum('man','woman') not null default "man", age tinyint not null default 23 ); Query OK, 0 rows affected (0.37 sec)
mysql> desc t13; +-------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------------+------+-----+---------+-------+ | name | char(10) | NO | | NULL | | | sex | enum('man','woman') | NO | | man | | | age | tinyint(4) | NO | | 23 | | +-------+---------------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
mysql> insert into t13(name) values("chihiro"); Query OK, 1 row affected (0.04 sec)
mysql> select * from t13; +---------+-----+-----+ | name | sex | age | +---------+-----+-----+ | chihiro | man | 23 | +---------+-----+-----+ 1 row in set (0.00 sec) ####################################################### 修改表结构 mysql> alter table 表名 执行动作；
添加新字段 -add 字段（宽度）约束条件； -add 字段（宽度）约束条件 first； //添加至表的最前面 -add 字段（宽度）约束条件 after 字段名； // 添加至指定字段名的后面
删除字段 -drop 字段名；
修改字段类型 -modify 字段 类型（宽度） 约束条件； //不可修改为与已有值冲突的类型
修改字段名 -change 源字段名 新字段名 类型（宽度） 约束条件；
修改表名 alter table 源表名 rename 新表名
MySQL
数字类型的宽度是显示宽度，不能够限制给字段赋值的大小，大小由类型决定。
mysql> create table t21( -> name char(5), -> age int(2) -> ); mysql> insert into t21 values("coco",1992); mysql> select * from t21; +------+------+ | name | age | +------+------+ | coco | 1992 | +------+------+ 1 row in set (0.00 sec) 宽度不能限制字段大小，类型决定。可节约存储空间，age（2）宽度是2，但赋值能大于2
mysql> create table t24(id int(2) zerofill,age int(5) zerofill); 设定宽度，不够用0填充。（zerofii用0补位） mysql> insert into t24 values(7,7); mysql> select * from t24; +------+-------+ | id | age | +------+-------+ | 07 | 00007 | +------+-------+ 1 row in set (0.00 sec)
################################################################# 一、mysql键值（限制如何给字段赋值） 1.1 普通索引index 1.1.1 什么是索引？ 类似与“书的目录”树型目录结构 eg：500页----->目录信息1-20----->正文21-500
1.1.2索引的优点 加快查询的速度
1.1.2索引的缺点 减慢写的速度（insert update delete） 占用物理存储空间
1.1.3使用普通索引index -索引使用规则 字段的值允许重复，可以赋NULL值 INDEX字段的KEY标志是MUL
-查看索引 desc 表名； show index from 表名； //查看索引信息的具体值 创建索引 默认使用的索引类型：BTREE（二叉树） 1-10 1-5 6-10 hash B+Tree create index 索引名 on 表名（字段名）； 1）建表时创建索引 mysql> create table t25( -> name char(10), -> age int, -> sex enum("boy","girl"), -> index(sex), -> index(name) -> ); mysql> desc t25; +-------+--------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------------+------+-----+---------+-------+ | name | char(10) | YES | MUL | NULL | | | age | int(11) | YES | | NULL | | | sex | enum('boy','girl') | YES | MUL | NULL | | +-------+--------------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> create index name on t21(name); //一般习惯将索引名与字段名相同 Query OK, 0 rows affected (0.42 sec) 2）在已有表创建索引 mysql> desc t21; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | name | char(5) | YES | | NULL | | | age | int(2) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> create index name on t21(name); Query OK, 0 rows affected (0.42 sec) ysql> desc t21; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | name | char(5) | YES | MUL | NULL | | | age | int(2) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
mysql> show index from t21\G *************************** 1. row *************************** Table: t21 Non_unique: 1 Key_name: name Seq_in_index: 1 Column_name: name Collation: A Cardinality: 1 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: 1 row in set (0.00 sec)
删除 drop index 索引名 on 表名； ########################################################### primary key主键 注意事项 -一个表中只能有一个primary key字段 -对应的字段值不允许有重复，且不允许赋NULL值 -如果有多个字段都作为PRIMARY KEY，称为复合主键，必须一起创建 -主键字段的KEY标志是PRI 通常与AUTO INCREMENT连用 -经常把表中能够唯一标识记录的字段设置为主键字段【记录编号字段】 1)已有表设主键 mysql> drop index name on t25; mysql> desc t25; +-------+--------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------------+------+-----+---------+-------+ | name | char(10) | YES | | NULL | | | age | int(11) | YES | | NULL | | | sex | enum('boy','girl') | YES | MUL | NULL | | +-------+--------------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> select * from t25; Empty set (0.00 sec) mysql> alter table t25 add primary key(name); //只能有一个主键 Query OK, 0 rows affected (0.47 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table t25 drop primary key; //删除主键 2）新建表，设主键 mysql> create table t26( -> name char(10), -> age int, -> likes set("a","b","c"), -> primary key(name) -> ); mysql> desc t26; +-------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+-------+ | name | char(10) | NO | PRI | NULL | | | age | int(11) | YES | | NULL | | | likes | set('a','b','c') | YES | | NULL | | +-------+------------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) 建表时上例主建加在中间也可以 eg：name char（10） primary key， 效果与t26相同 3）复合主键：多个字段一起做主键，字段不允许同时重复 mysql> create table t28( cip char(15), port smallint, status enum("allow","deny") default "deny", primary key(cip,port) ); Query OK, 0 rows affected (0.31 sec)
mysql> desc t28; +--------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+----------------------+------+-----+---------+-------+ | cip | char(15) | NO | PRI | NULL | | | port | smallint(6) | NO | PRI | NULL | | | status | enum('allow','deny') | YES | | deny | | +--------+----------------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> alter table t28 drop primary key; //删除主键 mysql> alter table t28 add primary key(cip,port); //添加主键
4）与auto_increment 连用 字段值自动增长 eg： id name age class jim 21 1709 让id字段的值自动增长 +1 条件：主键并且是数字 mysql> create table t29( -> id int(2) zerofill primary key auto_increment, -> name char(10), -> class char(10), -> index(name) -> ); Query OK, 0 rows affected (0.22 sec)
mysql> desc t29;
+-------+--------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------------------+------+-----+---------+----------------+
| id | int(2) unsigned zerofill | NO | PRI | NULL | auto_increment |
| name | char(10) | YES | MUL | NULL | |
| class | char(10) | YES | | NULL | |
+-------+--------------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> insert into t29(name,class) values("tom","1709");
mysql> insert into t29(name,class) values("jerry","1709");
mysql> insert into t29 values(9,"jack","1709"); //可自己赋值id，但id属于主键，不能同名
mysql> insert into t29(name,class) values("rose","1709"); //自动增长会选择数字最大的值进行自动增长，之前设置id=9，再开启自动增长则为10
mysql> select * from t29;
+----+-------+-------+
| id | name | class |
+----+-------+-------+
| 01 | tom | 1709 |
| 02 | jerry | 1709 |
| 09 | jack | 1709 |
| 10 | rose | 1709 |
+----+-------+-------+
4 rows in set (0.00 sec)
mysql> alter table t29 drop primary key; //无法删除主键，因为id设置为auto_increment自动增长，该命令必须是主键才可设立。
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql> alter table t29 modify id int; //修改字段类型，取消自动增长
mysql> alter table t29 drop primary key; //删除主键成功
Query OK, 4 rows affected (1.00 sec)
##########################################################
UNIQUE唯一索引
唯一索引不可赋相同值 ，可以为NULL
1）建表的时候指定UNIQUE字段
mysql> create table t211( stu_id char(9), name char(10), sex enum("boy","girl"), unique(stu_id) ); 指定学号为唯一索引
mysql> desc t211;
+--------+--------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------------+------+-----+---------+-------+
| stu_id | char(9) | YES | UNI | NULL | |
| name | char(10) | YES | | NULL | |
| sex | enum('boy','girl') | YES | | NULL | |
+--------+--------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
#########################################################
mysql> create table t212( stu_id char(9) not NULL, name char(10), sex enum("boy","girl"), unique(stu_id) );
Query OK, 0 rows affected (0.26 sec)
//指定stu_id为唯一索引，但不允许它为空值，则描述信息显示stu_id为RPI，但实际上主键不存在也无法删除
mysql> desc t212;
+--------+--------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------------+------+-----+---------+-------+
| stu_id | char(9) | NO | PRI | NULL | |
| name | char(10) | YES | | NULL | |
| sex | enum('boy','girl') | YES | | NULL | |
+--------+--------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
#######################################
练习
mysql> desc stuinfo
+-------+-------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+-------------------+-----------------------------+
| name | varchar(15) | YES | | NULL | |
| class | char(7) | YES | | NULL | |
| party | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-------------+------+-----+-------------------+-----------------------------+
3 rows in set (0.00 sec)
alter table stuinfo add stu_id char(7) first
create ubique index stu_id on stuinfo(name)
alter table stuinfo add id int(2) zerofill primary key auto_increment;
###############################################################
外键
foreign key（字段名） references 表名（字段名）on uptate cascade on delete cascade 同步更新，同步删除
作用：限制给字段赋值的，值必须在指定表中指定字段值的范围里选择
mysql> create table jfb(
-> id int(2) primary key auto_increment,
-> name char(10),
-> pay float(7,2)
-> )engine=innodb;
建立参照表
insert into jfb(name,pay)values("tom",20000),("lucy",20000);
Query OK, 2 rows affected (0.07 sec)
mysql> create table xsb( num int(2), name char(10), class char(9), foreign key(num) references jfb(id) on update cascade on delete cascade )engine=innodb;
同步修改 update 表名 set 字段名=值 where 条件； //条件就是原有的（字段名=值） 同步删除 delete from 表名 where 条件； 被参考的表不能随意删除
删除外键字段 show create table 表名； alter table 表名 drop foreign key 外键名；
eg：
本案例要求熟悉MySQL索引的类型及操作方法，主要练习以下任务： 普通索引、唯一索引、主键索引的创建/删除 自增主键索引的创建/删除 建立员工表yg、工资表gz，设置外键实现同步更新与同步删除 实现此案例需要按照如下步骤进行。 步骤一：索引的创建与删除
创建表的时候指定INDEX索引字段 创建库home： mysql> create database home; Query OK, 1 row affected (0.00 sec) 允许有多个INDEX索引字段。比如，以下操作在home库中创建了tea4表，将其中的id、name作为索引字段： mysql> USE home; Database changed mysql> CREATE TABLE tea4( -> id char(6) NOT NULL, -> name varchar(6) NOT NULL, -> age int(3) NOT NULL, -> gender ENUM('boy','girl') DEFAULT 'boy', -> INDEX(id),INDEX(name) -> ); Query OK, 0 rows affected (0.59 sec) 查看新建tea4表的字段结构，可以发现两个非空索引字段的KEY标志为MUL： mysql> DESC tea4; +--------+--------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------------+------+-----+---------+-------+ | id | char(6) | NO | MUL | NULL | | | name | varchar(6) | NO | MUL | NULL | | | age | int(3) | NO | | NULL | | | gender | enum('boy','girl') | YES | | boy | | +--------+--------------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) 2）删除现有表的某个INDEX索引字段 比如，删除tea4表中名称为named的INDEX索引字段： mysql> drop INDEX name ON tea4; //删除name字段的索引 Query OK, 0 rows affected (0.18 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC tea4; //确认删除结果 +--------+--------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------------+------+-----+---------+-------+ | id | char(6) | NO | MUL | NULL | | | name | varchar(6) | NO | | NULL | | | age | int(3) | NO | | NULL | | | gender | enum('boy','girl') | YES | | boy | | +--------+--------------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) 3）在已有的某个表中设置INDEX索引字段 比如，针对tea4表的age字段建立索引，名称为 nianling： mysql> CREATE INDEX nianling ON tea4(age); //针对指定字段创建索引 Query OK, 0 rows affected (0.62 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC tea4; //确认创建结果 +--------+--------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------------+------+-----+---------+-------+ | id | char(6) | NO | MUL | NULL | | | name | varchar(6) | NO | | NULL | | | age | int(3) | NO | MUL | NULL | | | gender | enum('boy','girl') | YES | | boy | | +--------+--------------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) 4）查看指定表的索引信息 使用SHOW INDEX 指令： mysql> SHOW INDEX FROM tea4\G *************************** 1. row *************************** Table: tea4 Non_unique: 1 Key_name: id Seq_in_index: 1 Column_name: id Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE //使用B树算法 Comment: Index_comment: *************************** 2. row *************************** Table: tea4 Non_unique: 1 Key_name: nianling //索引名称 Seq_in_index: 1 Column_name: age //字段名称 Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: 2 rows in set (0.00 sec) 5）创建表的时候指定UNIQUE索引字段 UNIQUE表示唯一性的意思，同一个表中可以有多个字段具有唯一性。 比如，创建tea5表，将id、name字段建立设置UNIQUE索引，age字段设置INDEX索引： mysql> CREATE TABLE tea5( -> id char(6), -> name varchar(4) NOT NULL, -> age int(3) NOT NULL, -> UNIQUE(id),UNIQUE(name),INDEX(age) -> ); Query OK, 0 rows affected (0.30 sec) 查看新建tea5表的字段结构，可发现UNIQUE字段的KEY标志为UNI；另外，由于字段name必须满足“NOT NULL”的非空约束，所以将其设置为UNIQUE后会自动变成了PRIMARY KEY主键字段： mysql> DESC tea5; //确认设置结果 +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | id | char(6) | YES | UNI | NULL | | | name | varchar(4) | NO | PRI | NULL | | | age | int(3) | NO | MUL | NULL | | +-------+------------+------+-----+---------+-------+ 3 rows in set (0.03 sec) 6）删除UNIQUE索引、在已有的表中设置UNIQUE索引字段 先删除tea5表name字段的唯一索引（与删除INDEX索引的方法相同）： mysql> DROP INDEX name ON tea5; //清除UNIQUE索引 Query OK, 0 rows affected (0.97 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC tea5; //确认删除结果 +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | id | char(6) | YES | UNI | NULL | | | name | varchar(4) | NO | | NULL | | | age | int(3) | NO | MUL | NULL | | +-------+------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) 重新为tea5表的name字段建立UNIQUE索引，并确认结果： mysql> CREATE UNIQUE INDEX name ON tea5(name); //建立UNIQUE索引 Query OK, 0 rows affected (0.47 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC tea5; //确认设置结果 +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | id | char(6) | YES | UNI | NULL | | | name | varchar(4) | NO | PRI | NULL | | | age | int(3) | NO | MUL | NULL | | +-------+------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) 7）建表时设置PRIMARY KEY主键索引 主键索引实际上在前面已经接触过了，建表的时候可以直接指定。如果表内一开始没有主键字段，则新设置的非空UNIQUE字段相当于具有PRIMARY KEY主键约束。 每个表中的主键字段只能有一个。 建表的时候，可以直接在某个字段的“约束条件”部分指定PRIMARY KEY；也可以在最后指定PRIMARY KEY(某个字段名)。比如： mysql> CREATE TABLE biao01( -> id int(4) PRIMARY KEY, //直接在字段定义时约束 -> name varchar(8) -> ); Query OK, 0 rows affected (0.19 sec) 或者： mysql> CREATE TABLE biao02( -> id int(4), -> name varchar(8), -> PRIMARY KEY(id) //所有字段定义完，最后指定 -> ); Query OK, 0 rows affected (0.17 sec) 在建表的时候，如果主键字段为int类型，还可以为其设置AUTO_INCREMENT自增属性，这样当添加新的表记录时，此字段的值会自动从1开始逐个增加，无需手动指定。比如，新建一个tea6表，将id列作为自增的主键字段： mysql> CREATE TABLE tea6( -> id int(4) AUTO_INCREMENT, -> name varchar(4) NOT NULL, -> age int(2) NOT NULL, -> PRIMARY KEY(id) -> ); Query OK, 0 rows affected (0.29 sec) 8）删除现有表的PRIMARY KEY主键索引 如果要移除某个表的PRIMARY KEY约束，需要通过ALTER TABLE指令修改。比如，以下操作将清除biao01表的主键索引。 清除前（主键为id）： mysql> DESC biao01; +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | id | int(4) | NO | PRI | NULL | | | name | varchar(8) | YES | | NULL | | +-------+------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) 清除操作： mysql> ALTER TABLE biao01 DROP PRIMARY KEY; Query OK, 0 rows affected (0.49 sec) Records: 0 Duplicates: 0 Warnings: 0 清除后（无主键）： mysql> DESC biao01; +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | id | int(4) | NO | | NULL | | | name | varchar(8) | YES | | NULL | | +-------+------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) 当尝试删除tea6表的主键时，会出现异常： mysql> ALTER TABLE tea6 DROP PRIMARY KEY; ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key 这是因为tea6表的主键字段id具有AUTO_INCREMNET自增属性，提示这种字段必须作为主键存在，因此若要清除此主键必须先清除自增属性——修改id列的字段定义： mysql> ALTER TABLE tea6 MODIFY id int(4) NOT NULL; Query OK, 0 rows affected (0.75 sec) Records: 0 Duplicates: 0 Warnings: 0 然后再清除主键属性就OK了： mysql> ALTER TABLE tea6 DROP PRIMARY KEY; //清除主键 Query OK, 0 rows affected (0.39 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc tea6; //确认清除结果 +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | id | int(4) | NO | | NULL | | | name | varchar(4) | NO | | NULL | | | age | int(2) | NO | | NULL | | +-------+------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) 9）为现有表添加PRIMARY KEY主键索引 重新为tea6表指定主键字段，仍然使用id列： mysql> ALTER TABLE tea6 ADD PRIMARY KEY(id); //设置主键字段 Query OK, 0 rows affected (0.35 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC tea6; //确认设置结果 +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | id | int(4) | NO | PRI | NULL | | | name | varchar(4) | NO | | NULL | | | age | int(2) | NO | | NULL | | +-------+------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) 步骤二：创建数据库并设置外键实现同步更新与同步删除
根据实验任务要求，两个表格的字段结构如表-1、表-2所示。
1）创建yg表，用来记录员工工号、姓名
其中yg_id列作为主键，并设置自增属性
mysql> CREATE TABLE yg(
-> yg_id int(4) AUTO_INCREMENT,
-> name char(16) NOT NULL,
-> PRIMARY KEY(yg_id)
-> );
Query OK, 0 rows affected (0.15 sec)
2）创建gz表，用来记录员工的工资信息
其中gz_id需要参考员工工号，即gz表的gz_id字段设为外键，将yg表的yg_id字段作为参考键：
mysql> CREATE TABLE gz(
-> gz_id int(4) NOT NULL,
-> name char(16) NOT NULL,
-> gz float(7,2) NOT NULL DEFAULT 0,
-> INDEX(name),
-> FOREIGN KEY(gz_id) REFERENCES yg(yg_id)
-> ON UPDATE CASCADE ON DELETE CASCADE
-> );
Query OK, 0 rows affected (0.23 sec)
3）为yg表添加2条员工信息记录
因yg_id有AUTO_INCREMENT属性，会自动填充，所以只要为name列赋值就可以了。
插入表记录可使用INSERT指令，这里先执行下列操作，具体在下一章学习：
mysql> INSERT INTO yg(name) VALUES('Jerry'),('Tom');
Query OK, 2 rows affected (0.16 sec)
Records: 2 Duplicates: 0 Warnings: 0
确认yg表的数据记录：
mysql> SELECT * FROM yg;
+-------+-------+
| yg_id | name |
+-------+-------+
| 1 | Jerry |
| 2 | Tom |
+-------+-------+
2 rows in set (0.00 sec)
4）为gz表添加2条工资信息记录
同上，数据参考图-2，插入相应的工资记录（gz_id字段未指定默认值，也未设置自增属性，所以需要手动赋值）：
mysql> INSERT INTO gz(gz_id,name,gz)
-> VALUES(1,'Jerry',12000),(2,'Tom',8000)
-> ;
Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0
确认gz表的数据记录：
mysql> SELECT * FROM gz;
+-------+-------+----------+
| gz_id | name | gz |
+-------+-------+----------+
| 1 | Jerry | 12000.00 |
| 2 | Tom | 8000.00 |
+-------+-------+----------+
2 rows in set (0.05 sec)
5）验证表记录的UPDATE更新联动
将yg表中Jerry用户的yg_id修改为1234：
mysql> update yg SET yg_id=1234 WHERE name='Jerry';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
确认修改结果：
mysql> SELECT * FROM yg;
+-------+-------+
| yg_id | name |
+-------+-------+
| 2 | Tom |
| 1234 | Jerry |
+-------+-------+
2 rows in set (0.00 sec)
同时也会发现，gz表中Jerry用户的gz_id也跟着变了：
mysql> SELECT * FROM gz;
+-------+-------+----------+
| gz_id | name | gz |
+-------+-------+----------+
| 1234 | Jerry | 12000.00 |
| 2 | Tom | 8000.00 |
+-------+-------+----------+
2 rows in set (0.00 sec)
6）验证表记录的DELETE删除联动
删除yg表中用户Jerry的记录：
mysql> DELETE FROM yg WHERE name='Jerry';
Query OK, 1 row affected (0.05 sec)
确认删除结果：
mysql> SELECT * FROM yg;
+-------+------+
| yg_id | name |
+-------+------+
| 2 | Tom |
+-------+------+
1 row in set (0.00 sec)
查看gz表中的变化（Jerry的记录也没了）：
mysql> SELECT * FROM gz;
+-------+------+---------+
| gz_id | name | gz |
+-------+------+---------+
| 2 | Tom | 8000.00 |
+-------+------+---------+
1 row in set (0.00 sec)
7）删除指定表的外键约束
先通过SHOW指令获取表格的外键约束名称：
mysql> SHOW CREATE TABLE gz\G
*************************** 1. row ***************************
Table: gz
Create Table: CREATE TABLE gz (
gz_id int(4) NOT NULL,
name char(16) NOT NULL,
gz float(7,2) NOT NULL DEFAULT '0.00',
KEY name (name),
KEY gz_id (gz_id),
CONSTRAINT gz_ibfk_1 FOREIGN KEY (gz_id) REFERENCES yg (yg_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
其中gz_ibfk_1即删除外键约束时要用到的名称。
删除操作：
mysql> ALTER TABLE gz DROP FOREIGN KEY gz_ibfk_1;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
确认删除结果：
mysql> SHOW CREATE TABLE gz\G
*************************** 1. row ***************************
Table: gz
Create Table: CREATE TABLE gz (
gz_id int(4) NOT NULL,
name char(16) NOT NULL,
gz float(7,2) NOT NULL DEFAULT '0.00',
KEY name (name),
KEY gz_id (gz_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
##################################################
MySQL存储引擎
mysql服务体系结构：（8个功能模块） 连接池 sql接口 分析器 优化器 查询缓存 存储引擎 文件系统 查看数据库服务支持的存储引擎 mysql> show engines;
设置默认存储引擎 vim /etc/my.cnf [mysqld] ... ... default-storage-engine=InnoDB 常用的存储引擎为InnoDB和MyISAM
create table 表名（约束条件） ENGINE=存储引擎名
常用存储引擎的特点 MyISAM 表.MYI 索引信息 表.MYD 数据 表.frm 表结构 支持表级锁（锁一张表） 不支持事务 事务回滚
InnoDB存储引擎
表.idb 索引信息+数据
表.frm 表结构
支持行级锁（只给当前被访问的行加锁）
支持事务 事务回滚
MySQL锁机制 锁粒度：表级锁、行级锁、页级锁 锁类型：读锁（select）和写锁（insert delete update）
事务：访问数据从开始到结束的过程 事务回滚：一次访问过程中，任意一步执行失败，都会恢复所有操作 事务的特性：一致性 原子性 隔离性 事务日志文件：记录对innodb存储引擎的表执行过的操作 工作中如何决定表使用的存储引擎 接收写操作多的表适合使用innodb存储引擎 接收读操作多的表适合使用myisam存储引擎
