MySQL入门到放弃

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));

推荐阅读更多精彩内容