MySQL的搭设和基本的增删改操作

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存储引擎


新闻标题:MySQL的搭设和基本的增删改操作
网页URL:http://pwwzsj.com/article/pejccc.html