MySQL
文章目录
安装MySQL
-
我的本机是macos,所以我安装的是msc版本的mysql.
下载页面的链接为https://dev.mysql.com/downloads/mysql
-
在安装之后打开终端,输入
vim ~/.bash_profile-
在打开页面中,按 i 建盘进入输入模式并添加下边的文本
export PATH=$PATH:/usr/local/mysql/bin alias mysqlstart='sudo /usr/local/mysql/support-files/mysql.server start' alias mysqlstop='sudo /usr/local/mysql/support-files/mysql.server stop'-
编辑完成后先按 esc 键,在输入 :wq 后回车退出编辑页面,并使用下边的命令使其生效
source ~/.bash_profile
-
-
-
经过上边的修改后,我们在终端,输入mysqlstart就是启动mysql服务,输入mysqlstop就是关闭mysql服务.
-
如果在设置后,仍然出现zsh: command not found: mysql的提示,就在终端中输入.
cd /usr/local/bin/ sudo ln -fs /usr/local/mysql/bin/mysql mysql
MySQL终端操作
- 查看所有的数据库.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
-
创建数据库
mysql> create database test; -
选择数据库
mysql> use test; Database changed -
打印数据库中的所有数据表
mysql> show tables; Empty set (0.01 sec) -
创建数据表
mysql> create table user(name varchar(20),password varchar(20),sex char(1)); -
查看数据表的详情信息
mysql> desc user; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | name | varchar(20) | YES | | NULL | | | password | varchar(20) | YES | | NULL | | | sex | char(1) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+
MySQL的数据类型
数值类型
日期类型
字符类型
表数据的增删改查
查
mysql> select * from user;
+--------+----------+------+
| name | password | sex |
+--------+----------+------+
| 李华 | 12345678 | 1 |
增
mysql> insert into user values ('周铭','abc1234','2');
mysql> insert into user values ('王洁','edu.esc','1');
mysql> select * from user;
+--------+----------+------+
| name | password | sex |
+--------+----------+------+
| 李华 | 12345678 | 1 |
| 周铭 | abc1234 | 2 |
| 王洁 | edu.esc | 1 |
+--------+----------+------+
删
mysql> select * from user;
+--------+----------+------+
| name | password | sex |
+--------+----------+------+
| 李华 | 12345678 | 1 |
| 周铭 | abc1234 | 2 |
| 王洁 | edu.esc | 1 |
| 小张 | abc1234 | 2 |
| 小名 | abc1234 | 3 |
| 小王 | abc1234 | 2 |
+--------+----------+------+
mysql> delete from user where name = '小王';
mysql> select * from user;
+--------+----------+------+
| name | password | sex |
+--------+----------+------+
| 李华 | 12345678 | 1 |
| 周铭 | abc1234 | 2 |
| 王洁 | edu.esc | 1 |
| 小张 | abc1234 | 2 |
| 小名 | abc1234 | 3 |
+--------+----------+------+
改
mysql> select * from user;
+--------+----------+------+
| name | password | sex |
+--------+----------+------+
| 李华 | 12345678 | 1 |
| 周铭 | abc1234 | 2 |
| 王洁 | edu.esc | 1 |
| 小张 | abc1234 | 2 |
+--------+----------+------+
mysql> update user set password = 'china' where name = '小张';
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from user;
+--------+----------+------+
| name | password | sex |
+--------+----------+------+
| 李华 | 12345678 | 1 |
| 周铭 | abc1234 | 2 |
| 王洁 | edu.esc | 1 |
| 小张 | china | 2 |
+--------+----------+------+
MySQL的建表约束
主键约束
-
它能够唯一确定表内的一条记录,也就是我们通过对某个字段添加约束,就可以使得该字段不重复且不为空.
mysql> create table user1( -> id int primary key, -> name varchar(20) -> ); mysql> insert into user1 values(1,'小王'); mysql> insert into user1 values(1,'小张'); ERROR 1062 (23000): Duplicate entry '1' for key 'user1.PRIMARY' mysql> insert into user1 values(null,'小梦'); ERROR 1048 (23000): Column 'id' cannot be null -
复合主键是指只要使用了复合主键的字段都不同时与已有的数据完全相同就可以添加成功.
mysql> create table user2(id int,name varchar(20),primary key(id,name)); mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | user | | user1 | | user2 | +----------------+ mysql> insert into user2 values(1,'小王'); mysql> insert into user2 values(1,'小明'); mysql> insert into user2 values(1,'小王'); ERROR 1062 (23000): Duplicate entry '1-小王' for key 'user2.PRIMARY' -
在建表后添加或删除主键
-
添加主键
- 通过add添加
mysql> desc user4; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ mysql> alter table user4 add primary key(id); Records: 0 Duplicates: 0 Warnings: 0 mysql> desc user4; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+-
通过modify添加
mysql> desc user4; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | NO | | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ mysql> alter table user4 modify id int primary key; Records: 0 Duplicates: 0 Warnings: 0 mysql> desc user4; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+
-
删除主键
mysql> desc user4; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ mysql> alter table user4 drop primary key; Records: 0 Duplicates: 0 Warnings: 0 mysql> desc user4; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | NO | | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+
-
自增约束
- 主键自增,在添加数据的时候不需要添加id主键,自增字段会根据数据条数自增.
mysql> create table user3(id int primary key auto_increment,name varchar(20));
mysql> insert into user3(name) values ('小王');
mysql> insert into user3(name) values ('小王');
mysql> insert into user3(name) values ('小王');
mysql> select * from user3;
+----+--------+
| id | name |
+----+--------+
| 1 | 小王 |
| 2 | 小王 |
| 3 | 小王 |
+----+--------+
唯一约束
-
约束修饰的字段的值不能重复
mysql> create table user5(id int,name varchar(20)); mysql> alter table user5 add unique (name); Records: 0 Duplicates: 0 Warnings: 0 mysql> desc user5; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | name | varchar(20) | YES | UNI | NULL | | +-------+-------------+------+-----+---------+-------+ mysql> insert into user5 values(1,'小王'); mysql> insert into user5 values(2,'小王'); ERROR 1062 (23000): Duplicate entry '小王' for key 'user5.name' mysql> insert into user5 values(2,'小张'); mysql> desc user5; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | name | varchar(20) | YES | UNI | NULL | | +-------+-------------+------+-----+---------+-------+ -
在建表后添加或删除唯一约束
-
添加
-
add
mysql> desc user5; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | name | varchar(20) | YES | UNI | NULL | | +-------+-------------+------+-----+---------+-------+ mysql> alter table user5 add unique(name); Records: 0 Duplicates: 0 Warnings: 0 mysql> desc user5; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | name | varchar(20) | YES | UNI | NULL | | +-------+-------------+------+-----+---------+-------+ -
modify
mysql> desc user5; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ mysql> alter table user5 modify name varchar(20) unique; Records: 0 Duplicates: 0 Warnings: 0 mysql> desc user5; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | name | varchar(20) | YES | UNI | NULL | | +-------+-------------+------+-----+---------+-------+
-
-
删除
-
drop
mysql> desc user5; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | name | varchar(20) | YES | UNI | NULL | | +-------+-------------+------+-----+---------+-------+ mysql> alter table user5 drop index name; Records: 0 Duplicates: 0 Warnings: 0 mysql> desc user5; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+
-
-
非空约束
-
修饰的字段不能为空
mysql> create table user6(id int,name varchar(20) not null); mysql> desc user6; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | name | varchar(20) | NO | | NULL | | +-------+-------------+------+-----+---------+-------+ mysql> insert into user6 values(,'霓虹'); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ','霓虹')' at line 1 mysql> mysql> select * from user6; +------+--------+ | id | name | +------+--------+ | 1 | 霓虹 | +------+--------+
默认约束
-
就是我们插入字段值时,如果没有传值,就会使用默认值
mysql> create table user7(id int,name varchar(20) default '老王'); mysql> desc user7; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | name | varchar(20) | YES | | 老王 | | +-------+-------------+------+-----+---------+-------+ mysql> insert into user7(id) values(1); mysql> insert into user7(id) values(2); mysql> insert into user7 values(2,'小王'); mysql> select * from user7; +------+--------+ | id | name | +------+--------+ | 1 | 老王 | | 2 | 老王 | | 2 | 小王 | +------+--------+
外键约束
-
设计到两个表 : 父表,字表(主表,副表)
mysql> create table classes(id int primary key,name varchar(20)); mysql> create table students(id int primary key,name varchar(20),class_id int,foreign key(class_id) references classes(id)); mysql> desc classes; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ mysql> desc students; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | id | int | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | | class_id | int | YES | MUL | NULL | | +----------+-------------+------+-----+---------+-------+ mysql> select * from classes; +----+--------+ | id | name | +----+--------+ | 1 | 一班 | | 2 | 二班 | | 3 | 三班 | | 4 | 四班 | +----+--------+ mysql> select * from students; +------+--------+----------+ | id | name | class_id | +------+--------+----------+ | 1001 | 张三 | 1 | | 1002 | 张三 | 2 | | 1003 | 张三 | 3 | | 1004 | 张三 | 4 | +------+--------+----------+ -
如果插入班级为5的数据会报错,即在students表中的外键必须在classes的主键中存在.
mysql> insert into students values(1001,'张三',5); ERROR 1062 (23000): Duplicate entry '1001' for key 'students.PRIMARY' -
删除问题,如果我们直接删除class中的班级数据,数据库会报错,因为该数据正在被students中的数据作为外键,想要删除此数据就需要先删除或修改students中引用该主键的数据
mysql> delete * from classes where id = 1; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* from classes where id = 1' at line 1
MySQL的范式
第一范式(1NF)
-
数据表中的所有字段都是不可分割的原子集.(字段值还可以继续拆分的就不满足第一范式)
-
如下边所示,address中的地址值,可以拆分为国家,省份,城市,具体住址.那么下边的表就不满足第一范式.
+------+-----------------------------------------+ | id | address | +------+-----------------------------------------+ | 1 | 中国河北廊坊市中信大厦122号 | | 2 | 中国河北廊坊市光临大厦22号 | | 3 | 中国河北廊坊市移动大厦333号 | +------+-----------------------------------------+
第二范式(2NF)
-
必须是满足第一范式的前提下,第二范式要求,除主键外的每一字段都必须完全依赖于主键.
-
如果要出现不完全依赖,只可能发生在联合主键的情况下.
- 如下图所示 product_name只依赖于proudct_id,而customer_name只依赖于customer_id,不满足第二范式的要求
create table orders( product_id int, customer_id int, product_name varchar(20), customer_name varchar(20), primary key(product_id,customer_id) );-
如果想要使其满足第二范式就需要进行拆表,拆分后的表就满足了第二范式的要求
create table orders( order_id int primary key, product_id int, customter_id int );create table products( id int primary key, name varchar(20) );create table customers( id int primary key, name varchar(20) );
第三范式(3NF)
-
必须先满足第二范式,除主键外的其他列之间不能有传递依赖关系
- 如下表所以,customer_id可以查询到customer_phone,即customer_phone和customer_id存在传递依赖,两者都不是主键,
create table orders( order_id int primary key, product_id int, customter_id int, customer_phone varchar(20) );-
如果想要使其满足第三范式,则将customer_phone存放到customer表中
create table orders( order_id int primary key, product_id int, customter_id int, );create table customers( id int primary key, name varchar(20), customer_phone varchar(20) );
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
