MySQL操作实例
修改数据库表结构
alter table 表名 执行动作；mysql安装教程
执行动作：
- 添加新字段（add）
- 删除已有字段（drop）
- 修改字段类型（modify）
- 修改字段名（change）
语法格式
添加新字段
add 添加新字段
add 字段名 类型(宽度)；
add 字段名 类型(宽度) 约束条件 ；
add 字段名 类型(宽度) 约束条件 first；
add 字段名 类型(宽度) 约束条件 after 字段名；
eg：
alter table t1 add class char(7) default "nsd1609" first,add tel char(11) ,add sex enum ("boy","girl") default "boy" after name;
删除字段
drop 删除字段
drop 字段名
eg：
alter table t1 drop name，drop sex;
修改字段类型
modify 修改字段类型
不能与字段已经存储的数据冲突
modify 字段名 类型(宽度) 约束条件；
eg：
mysql> alter table t1
-> modify
-> sex enum("boy","girl","no") not null default "no";
修改字段名
change 修改字段名
change 原字段名 新字段名 类型(宽度) 约束条件；
eg：
alter table t1 change tel iphone char(11);
修改表名
alter table 原表名 rename [to] 新表名；
eg:
alter table t1 rename t111;
mysql索引
概述
索引:相当于 "书的目录"
- 索引的优点
- 加快查询记录的速度.
- 索引的缺点
- 会减慢写的速度( insert update delete ).
- 占用物理存储空间.
在表里建索引 设置在字段上
索引类型
- 普通索引 index
- 唯一索引 unique
- 主键 primary key
- 外键 foreign key
- 全文索引 fulltext
使用索引
- 查看
- 创建
- 使用规则
- 删除
查看索引
- desc 表名； ---> 显示结果中的Key列即是索引值
- show index from 表名\G；
Table: user
Column_name: Host
Key_name: PRIMARY //即是索引值
Index_type: BTREE //共有三种：BTREE（二叉树），B+tree，hash
index普通索引的使用规则
- 一个表中可以有多个INDEX字段
- 字段的值允许有重复，且可以赋NULL值
- 经常把做查询条件的字段设置为INDEX字段
- INDEX字段的KEY标志是MUL
创建普通索引
1.在已有表里创建index字段
create index 索引名 on 表名(字段名);
create index sex on t111(sex);
2.建表时创建index字段
create table 表名 （
字段名列表，
index(字段名)，index(字段名)
）;
删除普通索引
drop index 索引名 on 表名；
drop index sex on t24;
primary key主键的使用规则
- 一个表中只能有一个primary key字段
- 对应的字段值不允许有重复，且不允许赋NULL值
- 如果有多个字段都作为PRIMARY KEY，称为复合主键，必须一起创建。
- 主键字段的KEY标志是PRI
- 通常与 AUTO_INCREMENT 连用
- 经常把表中能够唯一标识记录的字段设置为主键字段[记录编号字段]
建表时创建主键字段
create table t25( name char(10), age int(2), primary key(name));
create table t26( name char(10) primary key, age int(2));
删除主键
alter table 表名 drop primary key;
在已有表里创建主键
alter table 表名 add primary key(字段名）；
复合主键的使用
多个字段一起做主键是复合主键 必须一起创建。
字段的值不允许同时相同。
建表时创建：
create table t29(host char(10),db char(10),user char(10),primary key(host,db,user));
对已有的表进行添加：
alter table t29 add primary key(host,user,db);
通常和aUTO_INCREMENT 连用，实现字段值的字段增长
经常把表中能够唯一标识记录的字段设置为主键字段[记录编号字段]
唯一索引 unique
- 字段的值可以为Null 但不可以重复
- 一个表里可以有多个unique字段
- 标志 UNI
一般使用于：姓名，身份证，考试证，护照，驾驶证
建表时创建：
create table t29(
name char(10),
stu_id char(9),
age int(2),
unique(stu_id)
);
在已有表里创建unique字段
create unique index 索引名 on 表名（字段名）;
create unique index stu_id on t29(stu_id);
外键(foreign key)
功能:
让当前表某个字段的值，在另一个表某个字段值的范围内选择。
使用规则:
- 表的存储引擎必须是innodb
- 字段的数据类型要匹配
- 被参考的字段必须是key 中的一种 (primary key)
create table jfb(
jfb_id int(2) primary key auto_increment,
name char(10),
pay float(7,2)
)engine=innodb;
create table bjb(
bjb_id int(2),
name char(10),
foreign key(bjb_id) references jfb(jfb_id) on update cascade on delete cascade
)engine=innodb;
删除外键
show create table 表名；
alter table 表名 drop foreign key 外键名；
alter table bjb drop foreign key bjb_ibfk_1;
mysql存储引擎
概述
存储引擎：
表的处理器，是mysql数据库服务软件自动程序，不同处理器有不同的功能和数据存储方式。
基本操作
- 查看数据库服务支持哪些存储引擎：
show engines;
InnoDB DEFAULT
- 修改mysql数据库服务默认使用的存储引擎:
vim /etc/my.cnf
[mysqld]
default-storage-engine=myisam
service mysql restart
- 建表时指定表使用的存储引擎
create table t31(name char(10))engine=memory;
- 修改表使用的存储引擎
alter table 表名 engine=存储引擎名；
eg:
alter table t31 engine=innodb;
-
查看表使用的存储引擎
show create table 表名； -
工作中使用哪种存储引擎？
- myisam
- innodb
-
myisam的特点
- 独享表空间
- t1.frm 表结构
- t1.MYD 表记录
- t1.MYI 表索引
-
innodb的特点
- 支持行级锁
- 支持外键 、 事务 、事务回滚
- 共享表空间
- t3.frm 表结构
- t3.ibd 表记录+表索引
事务
事务：一次sql操作从开始到结束的过程。
事务回滚：执行一次事务，只要执行过程中，任何一步执行失败，就恢复之前所有的sql操作。
事务日志文件记录对所有inondb存储引擎的表执行过的sql命令。
ibdata1 记录sql命令产生的数据信息
ib_logfile0----|
|---> 记录SQL 命令
ib_logfile1----|
锁机制
锁机制是为了解决客户端的并发访问冲突问题。
锁粒度： 表级锁 行级锁 页级锁
锁类型：
- 读锁 （共享锁） select * from t1;
- 写锁 (互斥锁 排它锁)
建表时如何决定表使用的存储引擎：
执行写操作多的表适合使用inondb存储引擎，这样并发访问大。
执行读操作多的表适合使用myisam存储引擎.
实例操作（前面基本描述的具体实现）
//进入mysql数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| game_db |
| mysql |
| performance_schema |
| test |
| user_db |
+--------------------+
6 rows in set (0.00 sec)
mysql> use user_db;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-------------------+
| Tables_in_user_db |
+-------------------+
| user_list |
+-------------------+
1 row in set (0.00 sec)
mysql> desc user_list;
+-----------+-------------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------------------------------+------+-----+---------+----------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| u_name | char