一 表的详细操作
1.修改表名 alter table 旧表名 rename 新表名； 2.修改表中的引擎与字符编码 alter table 表名 engine = "引擎名" charset ="编码名"； 3.复制表 #3.1只复制表结构以及约束条件 create table 新表名 like 旧表名； eg1: select * from t1; +----+------------+ | id | name | +----+------------+ | 0 | guer | +----+------------+ #================================================desc t1; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | NO | PRI | 0 | | | name | char(10) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ #================================================create table nt like t1; select * from nt; #为空表，Empty set (0.04 sec) desc t; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | NO | PRI | 0 | | | name | char(10) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ #================================================ #3.2只复制表的结构，约束不复制 eg2: create table nt1 select * from tt where 1=2; #（where 后面时False的条件均可） select * from nt1; #为空表 desc nt1; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | NO | | 0 | | | name | char(10) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ #================================================ #3.3复制结构+数据，不复制约束 create table 新表名 select * from 旧表名; eg3: create table nt2 select * from t1; select * from nt2; +----+------------+ | id | name | +----+------------+ | 0 | guer | +----+------------+ #================================================ desc nt2; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | NO | | 0 | | | name | char(10) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ #================================================ 4.清空表 truncate 表名； #注意：表被重置后，自增字段也被重置，即重头开始。
二 表中字段的详细操作
#创建一个有主键并且主键自增的表t2 create table t2( id int primary key auto_increment, x int, y int ); #插入数据 insert into t2(x,y) values (10,20),(100,200),(1000,2000); +----+------+------+ | id | x | y | +----+------+------+ | 1 | 10 | 20 | | 2 | 100 | 200 | | 3 | 1000 | 2000 | +----+------+------+ +-------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | x | int(11) | YES | | NULL | | | y | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+----------------+ #================================================ 1.修改字段信息 alter table 表名 modify 字段名 类型[(宽度) 约束]; alter table t2 modify x bigint default 0; # 模式不同, 涉及精度问题 +-------+------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | x | bigint(20) | YES | | 0 | | | y | int(11) | YES | | NULL | | +-------+------------+------+-----+---------+----------------+ #================================================ 2.修改字段名及信息 alter table 表名 change 旧字段名 新字段名 类型[(宽度) 约束]; alter table t2 change y c char(10) not null; # 模式不同, 涉及类型转换问题 +-------+------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | x | bigint(20) | YES | | 0 | | | c | char(10) | NO | | NULL | | +-------+------------+------+-----+---------+----------------+ +----+------+------------+ | id | x | c | +----+------+------------+ | 1 | 10 | 20 | | 2 | 100 | 200 | | 3 | 1000 | 2000 | +----+------+------------+ 3.添加字段名 # 末尾添加 alter table 表名 add 字段名 类型[(宽度) 约束], ..., add 字段名 类型[(宽度) 约束]; alter table t2 add age int, add gender enum("male", "female", "wasai") default "wasai"; # 首尾添加 alter table 表名 add 字段名 类型[(宽度) 约束] first; # 指定位添加：指定字段后 alter table 表名 add 字段名 类型[(宽度) 约束] after 旧字段名; alter table t2 add y int after x; +----+------+------+------+--------+ | id | x | y | age | gender | +----+------+------+------+--------+ | 1 | 10 | 20 | NULL | wasai | | 2 | 100 | 200 | NULL | wasai | | 3 | 1000 | 2000 | NULL | wasai | +----+------+------+------+--------+ +--------+-------------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | x | bigint(20) | YES | | 0 | | | y | int(11) | YES | | NULL | | | age | int(11) | YES | | NULL | | | gender | enum('male','female','wasai') | YES | | wasai | | +--------+-------------------------------+------+-----+---------+----------------+ #================================================ 4.删除字段名 alter table 表名 drop 字段名; alter table t2 drop y; +----+------+------+--------+ | id | x | age | gender | +----+------+------+--------+ | 1 | 10 | NULL | wasai | | 2 | 100 | NULL | wasai | | 3 | 1000 | NULL | wasai | +----+------+------+--------+ +--------+-------------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | x | bigint(20) | YES | | 0 | | | age | int(11) | YES | | NULL | | | gender | enum('male','female','wasai') | YES | | wasai | | +--------+-------------------------------+------+-----+---------+----------------+
三 特殊表（mysql.user）=> 用户管理
# 操作前提：登录root用户 1.重要字段 Host | User | Password root 用户查看当前所有的用户： select Host,User,Password from mysql.user; 2.新建用户 create user 用户名@主机名 identified by '密码'; # 正确 create user zero@localhost identified by 'zero'; +--------------------------------------------------------------+------------------+-------------------------------------------+ | Host | User | Password | +--------------------------------------------------------------+------------------+-------------------------------------------+ | localhost | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | | 127.0.0.1 | root | | | ::1 | root | | | localhost | | | | localhost | zero | *98F19B394B58FAC4B01A3D9037B93CA2A17199CF | +--------------------------------------------------------------+------------------+-------------------------------------------+ #================================================ 注：insert into mysql.user(Host,User,Password) values("主机名","用户名",password("密码")); #这种方法是错误 3.设置用户权限 grant 权限们 on 数据库名.表名 to 用户名@主机名 [with grant option]; grant create on db1.* to zero@localhost with grant option; +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | test | +--------------------+ #============================================= 注：权限有select,delete,update,insert,drop..., all代表所有权限 注：数据库名，表名可以用*替换，代表所有 注：设置权限时如果没有当前用户，会自动创建用户，提倡使用 重点: grant all on db1.* to owen@localhost identified by 'owen'; # (创建用户)设置权限 （推荐使用这个） 4.撤销权限 revoke 权限名 on 数据库名.表名 from 用户名@主机名; revoke delete on db1.* from owen@localhost; 5.修改密码 set password for 用户名@主机名 = password('新密码'); #在root用户下 set password for owen@localhost = password('123'); set password = password("新密码") #在当前用户修改自己的密码 6.删除用户 #在root用户下 drop user 用户名@主机名;
四 表关系
社会中存储需要可以构建成表的数据，它们形成的表，往往之间存储某种或某些社会关系，mysql数据库建立表结构就是社会中产生的各种数据，分门别类管理，但Mysql建立的（代码层次的）表之间，同样需要处理表与表之间的关系，形成了多对一|多对多|一对一 3种关系。
多对一
''' 案例：员工employees表 | 部门department表 建表规则： 先建立主表，再建立从表，在从表中设置主表的唯一字段(通常为主键)作为外键 建表语法： create table 主表( id int primary key auto_increment, ... ); create table dep( id int primary key auto_increment, name varchar(16), work varchar(16) ); create table 从表( id int primary key auto_increment, ... 主表_id int, # 只是在从表中起了一个名字, 该名字和主表主键对应,所有起了个见名知义的名字 foreign key(主表_id) references 主表(唯一字段名id) on update cascade on delete cascade ); create table emp( id int primary key auto_increment, name varchar(16), salary float, dep_id int, foreign key(dep_id) references dep(id) on update cascade # 设置级联 on delete cascade ); 插入记录规则： 先插入主表数据，再插入从表数据 insert into dep values(1, '市场部', '销售'), (2, '教学部', '授课'); insert into emp(name, salary, dep_id) values('egon', 3.0, 2),('yanghuhu', 2.0, 2),('sanjiang', 10.0, 1),('owen', 88888.0, 2),('liujie', 8.0, 1); 更新删除数据： 两表间相互影响，先从依赖数据入手，再进行更新删除操作 eg:1 删除主表dep中一个部门 delete from dep where id=1; => 从表emp中属于该部门的员工都被删除了 更新从表emp中一个员工的部门 update emp set dep_id=3 where name='egon'; <= 部门必须存在 insert into dep values(3, '管理部', '吃饭睡觉打豆豆, 明确团队方针'); '''
多对多
案例：作者author表 | 书book表 建表规则： 新建第三张表，通过两个外键形成多对多关系 建表语法： create table 表1( id int primary key auto_increment, ... ); create table book( id int primary key auto_increment, name varchar(16), price int ); create table 表2( id int primary key auto_increment, ... ); create table author( id int primary key auto_increment, name varchar(16) ); create table 关系表( id int primary key auto_increment, 表1_id int, 表2_id int, foreign key(表1_id) references 表1(id) on update cascade on delete cascade, foreign key(表2_id) references 表2(id) on update cascade on delete cascade ); create table book_author( id int primary key auto_increment, book_id int, author_id int, foreign key(book_id) references book(id) on update cascade on delete cascade, foreign key(author_id) references author(id) on update cascade on delete cascade );
一对一
案例：丈夫husband表 | 妻子wife表 建表规则： 未存放外键的表被依赖，称之为左表；存放外键的表示依赖表，称之为右表；先操作左边再操作右表 建表语法： create table 左表( id int primary key auto_increment, ... ); create table husband( id int primary key auto_increment, name varchar(16) ); create table 右表( id int primary key auto_increment, ... 左表_id int unique, # 一对一的外键需要唯一性 foreign key(左表_id) references 左表(id) on update cascade on delete cascade ); create table wife( id int primary key auto_increment, name varchar(16), husband_id int unique, # 一对一的外键需要唯一性 foreign key(husband_id) references husband(id) on update cascade on delete cascade );