数据库之表关系
表的详细操作
1.修改表名
create table db1.t1 (
id int primary key auto_increment,
name char,
age int)
use db1;
alter table t1 rename zt;
alter table zt rename t1;
2.修改表字段属性
alter table t1 modify name char(16) not full default "无名氏";
3.修改表引擎
alter table t1 engine myisam charset gbk;
4.修改表字段名
alter table t1 change name usr char(16);
5.复制表
①复制结构，约束条件，不复制数据
insert t1 (usr,age) values ("zb",18),("egon",28),("alex",38);
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| usr | varchar(16) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
----------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`usr` varchar(16) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
create table t2 like t1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| usr | varchar(16) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
mysql> select * from t2;
Empty set (0.00 sec)
| t2 | CREATE TABLE `t2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`usr` varchar(16) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
②复制结构和数据，不复制约束条件
create table t3 select * from t1;
-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | 0 | |
| usr | varchar(16) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
③不复制数据，约束条件，仅复制结构
create table t4 select * from t1 where i<0;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | 0 | |
| usr | varchar(16) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
5.添加字段名
①在字段尾添加字段
alter table t1 add sex enum("male","female") not null default "male";
②在字段首添加字段
alter table t1 add gender char(16) first;
③在某个字段后添加字段
alter table t1 add salary float after age int;#字段 必须加类型
6.删除字段名
alter table t1 drop gender;
7.清空表
truncate t1
会将表重置，自增字段重置
特殊表（mysql.user）=>用户管理
操作前提：登陆root用户
1.重要字段
Host，User，Password
use mysql;
select Host ,User,Password from user;
Host | User | Password |
+-----------+------+-------------------------------------------+
| localhost | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| 127.0.0.1 | root | |
| ::1 | root | |
| localhost | | |
最后一个为 游客
2.新建用户
①create user 用户名@主机名 identified by "密码"
create user zb@localhost identified by "zb";
C:\WINDOWS\system32>mysql -uzb -pzb(登陆不能加分号)
--------------+
| user() |
+--------------+
| zb@localhost |
+--------------+
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
啥权限也没有
3.设置用户权限
grant 权限 on 数据库名.表名 to 用户名@主机名[with grant option];
mysql> grant select,create,alter on db1.* to zb@localhost identified by "123";
ERROR 1044 (42000): Access denied for user 'zb'@'localhost' to database 'db1'
权限有select delete update insert drop ... all代表所有权限
mysql> grant all on db1.* to zb@localhost;
mysql> drop database db1;
Query OK, 4 rows affected (0.04 sec)
设置权限时如果没有当前用户，会自动创建用户，提倡使用
重点:grant all on db1.* to owen@localhost identified by "owen";
grant all on *.* to robert@localhost identified by "robert";
| User | Host | Password |
+--------+-----------+-------------------------------------------+
| robert | localhost | *A14C02465C2ED43BDB89ACC6C7213C1D00617758 |
4.撤销权限
revoke 权限名 on 数据库名.表名 from 用户名@主机名;
revoke all on *.* from robert@localhost;
5.修改密码
用户：
未登陆前设置密码
C:\WINDOWS\system32>mysqladmin -uzb -p123 password "1234"
mysqladmin -uroot -p旧密码 password "新密码"
登陆后修改密码
set password = password("新密码");
管理员：
set password for owen@localhost = password("123");
6.删除用户
drop user 用户名@主机名
mysql> drop user robert@localhost;
Query OK, 0 rows affected (0.00 sec)
mysql> select User,Host,Password from mysql.user;
+------+-----------+-------------------------------------------+
| User | Host | Password |
+------+-----------+-------------------------------------------+
| root | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| zb | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
表关系
多对一
a 表中有多个数据 共同使用 b表的一个数据
而b表的数据只对应一个a表的数据
则 b 表为主表，a表为从表
先建主表，再建从表,在从表中设置主表的唯一字段（通常为主键）作为外键
use db1;
create table school (
id int primary key auto_increment,
name char(16)
);
create table student(
id int primary key auto_increment,
name char(16),
school_id int,
foreign key(school_id) references school(id)
on update cascade#设置级联
on delete cascade);
先添加主表，后添加从表
mysql> insert school (name) values("bj"),("sh");
insert student (name,school_id)values("zb",1),("egon",2),("alex",1);
#？如果设置忘记级联应该怎么办？
#主表没办法被清空，删除
#当存在id 1,2将id1改为3，后面就不能添加3
#手动添加id 4 ，下次是5
#手动添加id 7，下次是8
加主键
alter table student modify id int primary key auto_increment;
加外键
alter table student add constraint foreign key (school_id) references school(id) on update cascade on delete cascade;
删除外键
有外键时，主表不能被先删除
先删除从表，再删除主表
多对多
将两表的关系放入关系表
两表随便哪个先建
create table student(
id int primary key auto_increment,
name char(16),
core int);
create table teacher(
id int primary key auto_increment,
name char(16) );
create table stu_tec(
id int primary key auto_increment,
stu_id int,
tec_id int,
foreign key (stu_id) references student(id)
on update cascade
on delete cascade,
foreign key(tec_id)references teacher(id)
on update cascade
on delete cascade);
一对一
```python
create table seat(
id int primary key auto_increment,
price int);
create table passenger(
id int primary key auto_increment,
seat_id int unique,
foreign key (seat_id)references seat(id));