image
1 查看当前数据库
select database();
2 创建数据库
create database test;
设置UTF8格式
create database test1 default charset='utf8';
3 切换数据库
use mysql;
4 创建数据表
(1)第一种形式
mysql> create table pet (
name VARCHAR(20),
owner varchar(20),
species varchar(20),
sex varchar(10),
birth DATE,
death DATE);
(2)第二种形式
create table test (name VARCHAR(20),owner varchar(20),species varchar(20),death DATE);
5 查看数据表结构
desc test;
6 查看数据表所有内容
select * from test;
7 数据表中插入数据
insert into pet values ('k1','zt','cat','f','2001-04-01',NULL);
insert into pet values ('kkk','zt1','cat1','f1','2001-04-02',NULL);
insert into pet values ('kkk','zt1','cat3','f1','2001-04-02',NULL);
8 删除数据表中的数据
delete from pet where species='cat1';
9 修改数据表数据
update pet set name='zhangsan' where species='cat2';
10 mysql创建表格约束
(1)主键约束-primary约束
它可以确定一个表中的唯一记录，也就是给表格某个字段添加约束，就可以确定其唯一性且不为空
create table user (id int primary key,name varchar(20));
mysql> insert into user values(1,'kobe');
Query OK, 1 row affected (0.01 sec)
mysql> insert into user values(1,'kobe');
ERROR 1062 (23000): Duplicate entry '1' for key 'user.PRIMARY'
mysql> insert into user values(NULL,'kobe');
ERROR 1048 (23000): Column 'id' cannot be null
(2)主键约束-联合约束
只要两个主键值加起来不重复就可以
create table user2(id int,name varchar(20),password varchar(20),primary key(id,name));
insert into user2 values(1,'zhangsan','123');
insert into user2 values(2,'zhangsan','123');
insert into user2 values(2,'lisi','123');
insert into user2 values(NULL,'lisi','123');
mysql> insert into user2 values(NULL,'lisi','123');
ERROR 1048 (23000): Column 'id' cannot be null
(3)主键约束-自增约束
create table user3(id int primary key auto_increment,name varchar(20));**
insert into user3 (name) values('san');
(4)主键约束-忘记创建主键约束：建表后添加与删除
create table user4(id int,name varchar(20));
alter table user4 add primary key(id);
1 修改表结构，添加主键
alter table user4 add primary key(id);
2 修改表结构，删除主键
alter table user4 drop primary key;
3 使用modify修改字段，添加约束
alter table user4 modify id int primary key;
(5)主键约束-唯一约束
1 约束修饰字段的值不可以重复
create table user5(id int,name varchar(20));
2 添加唯一约束
alter table user5 add unique(name);
创建表user5进行测试
create table user5(id int,name varchar(20));
alter table user5 add unique(name);
insert into user5 values(1,'zhangsan');
insert into user5 values(1,'zhangsan');
第二种方式，表示两个键（id,name）组合在一起不重复就OK
create table user6(id int,name varchar(20),unique(id,name));
insert into user6 values(1,'zhangsan');
insert into user6 values(1,'zhangsan');
ERROR 1062 (23000): Duplicate entry '1-zhangsan' for key 'user6.id'
3 如何删除唯一约束
alter table user6 drop index name;
mysql> alter table user6 drop index id;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
4 modify添加唯一约束
alter table user6 modify name varchar(20) unique;
mysql> alter table user6 modify name varchar(20) unique;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
(6)约束-非空约束
create table user7(id int,name varchar(20) not null);
mysql> create table user7(id int,name varchar(20) not null);
Query OK, 0 rows affected (0.04 sec)
insert into user7 (id) values(1);
mysql> insert into user7 (id) values(1);
ERROR 1364 (HY000): Field 'name' doesn't have a default value
insert into user7 (id,name) values(1,'zhangsan');
insert into user7 values(1,'zhangsan');
mysql> insert into user7 (id,name) values(1,'zhangsan');
Query OK, 1 row affected (0.01 sec)
insert into user7 (name) values('lisi');
mysql> insert into user7 (name) values('lisi');
Query OK, 1 row affected (0.01 sec)
(7)约束-默认约束-default
当我们插入字段值时，没有传值时，就会使用默认值
create table user8(id int,name varchar(20),age int default 10);
插入数据
insert into user8 (id,name) values(1,'zhang_san');
insert into user8 values(1,'zhang_san',19);
(8)约束-外键约束-foreign_key
---涉及到两个表：主表、副表
---主表、副表
---班级
1 创建classes表
create table classes(id int primary key,name varchar(20));
2 创建students表
create table students(id int primary key,
name varchar(20),
class_id int,
foreign key(class_id) references classes(id));
3 classes表格插入数据
insert into classes(id,name) values(1,'一班');
insert into classes(id,name) values(2,'二班');
insert into classes(id,name) values(3,'三班');
insert into classes(id,name) values(4,'四班');
4 修改表格数据
mysql> update classes set name='fourth class' where id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
---学生
insert into students values(1001,'zhang-tian',1);
insert into students values(1002,'li-tian',2);
insert into students values(1003,'zhao-tian',3);
insert into students values(1004,'sun-tian',4);
insert into students values(1005,'error',5);
---1主表（父表）classes中的没有的数据值，在副表（子表）中不可使用
---2主表中的记录被副表引用时，是不可删除的
mysql> insert into students values(1005,'error',5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
(`python`.`students`, CONSTRAINT `students_ib` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))
11数据表设计-第一范式1NF
数据表中所有数据都是不可分割的原子值?
create table student2(id int primary key,name varchar(20),address varchar(30));
insert into student2 values(1,'kobe','lakes_eight_S+');
insert into student2 values(2,'james','miami_six_S+');
insert into student2 values(3,'jordan','bulls_twenty_S+');
update student2 set address='bulls_twenty_S+' where id='3';
字段值还可以继续拆分的，就不满足第一范式
create table student3(id int primary key, name varchar(20), team varchar(30), number varchar(20), grade varchar(20));
insert into student3 values(1,'kobe','lakers','8','S+');
insert into student3 values(2,'james','miami','6','S+');
insert into student3 values(3,'jordan','bulls','23','S+');
范式设计的越详细，对于某些实际操作可能会更好，但是不一定都是好处
12数据表设计-第二范式2NF
必须是满足第一范式的前提下，第二范式要求，除主键外的每一列都必须完全依赖主键
如果出现不完全依赖，则只可能发生在联合主键的情况下。
create table my_order(product_id int, customer_id int, product_name varchar(20), customer_name varchar(20), primary key(product_id,customer_id));
问题？
除主键以外的其他列，只依赖主键的部分字段
拆表
create table myorder(order_id int primary key, product_id int, customer_id int);
create table product(id int primary key, name varchar(20));
create table customer(id int primary key, name varchar(20));
分成三个表以后，就满足了第二范式
13数据表设计-第三范式3NF
必须先满足第二范式,除开主键列的其他列之间不能有传递依赖关系
create table myorder(order_id int primary key, product_id int, customer_id int, customer_phone varchar(20));
customer_phone不应该放在myorder表里面，需要放在customer里面，这样可以不重复
create table customer(id int primary key, name varchar(20), phone varchar(20));
网友评论