1、终端操作MySQL数据库
1.1如何登陆数据库服务器：
C:Usersusername>mysql -uroot -proot
1.2如何查询数据库服务器中的数据库
mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || sys || test1 |+--------------------+5 rows in set (0.03 sec)1.3如何选中某一个数据库进行操作
mysql> use test1Database changed1.4SQL语句中的查问
mysql> select * from person;+-----------+------+| name | sex |+-----------+------+| 张三 | 男 || 李四 | 女 || 王麻子 | 男 |+-----------+------+3 rows in set (0.00 sec)mysql> select * from person where name="张三";+--------+------+| name | sex |+--------+------+| 张三 | 男 |+--------+------+1 row in set (0.00 sec)1.5如何退出数据库服务器
mysql> exit;Bye 2、如何创立数据库数据表
2.1如何在数据库服务器中创立咱们的数据库
mysql> create database test2;Query OK, 1 row affected (0.05 sec)mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || sys || test1 || test2 |+--------------------+6 rows in set (0.00 sec)mysql> use test2;Database changed2.2如何查看某个数据库中的所有数据表
mysql> show tables;Empty set (0.00 sec)2.3如何创立一个数据表
mysql> create table pet( -> name VARCHAR(20), -> owner VARCHAR(20), -> species VARCHAR(20), -> sex CHAR(1), -> birth DATE, -> death DATE);Query OK, 0 rows affected (0.09 sec)创立了一个宠物数据表
查看数据表是否创立胜利
mysql> show tables;+-----------------+| Tables_in_test2 |+-----------------+| pet |+-----------------+1 row in set (0.00 sec)2.4查看创立好的数据表的构造
mysql> describe pet;+---------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+---------+-------------+------+-----+---------+-------+| name | varchar(20) | YES | | NULL | || owner | varchar(20) | YES | | NULL | || species | varchar(20) | YES | | NULL | || sex | char(1) | YES | | NULL | || birth | date | YES | | NULL | || death | date | YES | | NULL | |+---------+-------------+------+-----+---------+-------+6 rows in set (0.00 sec)3、insert减少数据记录
3.1查看数据表中的记录
mysql> select * from pet;Empty set (0.00 sec)3.2如何往数据表中增加数据记录
mysql> insert into pet -> values('Tom','LiMing','hamster','f','2020-11-11',null);Query OK, 1 row affected (0.01 sec)再一次查问
mysql> select * from pet;+------+--------+---------+------+------------+-------+| name | owner | species | sex | birth | death |+------+--------+---------+------+------------+-------+| Tom | LiMing | hamster | f | 2020-11-11 | NULL |+------+--------+---------+------+------------+-------+1 row in set (0.00 sec)mysql> insert into pet -> values('旺财','周星星','狗','公','2020-11-11',null);Query OK, 1 row affected (0.01 sec)查看
mysql> select * from pet;+--------+-----------+---------+------+------------+-------+| name | owner | species | sex | birth | death |+--------+-----------+---------+------+------------+-------+| Tom | LiMing | hamster | f | 2020-11-11 | NULL || 旺财 | 周星星 | 狗 | 公 | 2020-11-11 | NULL |+--------+-----------+---------+------+------------+-------+2 rows in set (0.00 sec)3.3MySQL罕用数据类型
MySQL反对多种类型，大抵能够分为三类：数值、日期/工夫和字符串(字符)类型。
3.3.1数值类型
如果数值超出数值类型的范畴就会报错：
mysql> create table testType( -> number tinyint);Query OK, 0 rows affected (0.10 sec)mysql> describe testType;+--------+---------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+---------+------+-----+---------+-------+| number | tinyint | YES | | NULL | |+--------+---------+------+-----+---------+-------+1 row in set (0.00 sec)mysql> insert into testType -> values(128);ERROR 1264 (22003): Out of range value for column 'number' at row 13.3.2日期和工夫类型
3.3.3字符串类型
3.3.4数据类型如何抉择
日期：依照日期格局
数值和字符串：依照大小
4、数据记录增删查改操作
4.1插入：
mysql> insert into pet values('Buffy','Job','cat','m','2020-11-10',null);Query OK, 1 row affected (0.01 sec)mysql> insert into pet values('Slim','邓紫棋','dog','f','2019-11-11',null);Query OK, 1 row affected (0.01 sec)mysql> insert into pet -> values('Fang','成龙','cat','m','2018-11-11','2020-10-10');Query OK, 1 row affected (0.01 sec)mysql> select * from pet;+--------+-----------+---------+------+------------+------------+| name | owner | species | sex | birth | death |+--------+-----------+---------+------+------------+------------+| Tom | LiMing | hamster | f | 2020-11-11 | NULL || 旺财 | 周星星 | 狗 | 公 | 2020-11-11 | NULL || Buffy | Job | cat | m | 2020-11-10 | NULL || Slim | 邓紫棋 | dog | f | 2019-11-11 | NULL || Fang | 成龙 | cat | m | 2018-11-11 | 2020-10-10 |+--------+-----------+---------+------+------------+------------+5 rows in set (0.00 sec)4.2如何删除数据
mysql> delete from pet where name='Tom';Query OK, 1 row affected (0.01 sec)再次查看
mysql> select * from pet;+--------+-----------+---------+------+------------+------------+| name | owner | species | sex | birth | death |+--------+-----------+---------+------+------------+------------+| 旺财 | 周星星 | 狗 | 公 | 2020-11-11 | NULL || Buffy | Job | cat | m | 2020-11-10 | NULL || Slim | 邓紫棋 | dog | f | 2019-11-11 | NULL || Fang | 成龙 | cat | m | 2018-11-11 | 2020-10-10 |+--------+-----------+---------+------+------------+------------+4 rows in set (0.00 sec)4.3如何批改数据
mysql> update pet set name='旺财财' where owner='周星星';Query OK, 1 row affected (0.01 sec)再次查看：
mysql> select * from pet;+-----------+-----------+---------+------+------------+------------+| name | owner | species | sex | birth | death |+-----------+-----------+---------+------+------------+------------+| 旺财财 | 周星星 | 狗 | 公 | 2020-11-11 | NULL || Buffy | Job | cat | m | 2020-11-10 | NULL || Slim | 邓紫棋 | dog | f | 2019-11-11 | NULL || Fang | 成龙 | cat | m | 2018-11-11 | 2020-10-10 |+-----------+-----------+---------+------+------------+------------+4 rows in set (0.00 sec)4.4总结：数据记录常见操作
减少：insert
删除：delete
批改：update
查问：select
5、MySQL建表束缚
5.1主键束缚 primary key
它可能惟一确定一张表中的一条记录，也就是咱们通过给某个字段增加束缚，就能够使得该字段不反复且不为空。
mysql> create table user( -> id int primary key, -> name varchar(20));Query OK, 0 rows affected (0.08 sec)mysql> describe user;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int | NO | PRI | NULL | || name | varchar(20) | YES | | NULL | |+-------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)再次插入id=1的记录
mysql> insert into user values(1,'李四');ERROR 1062 (23000): Duplicate entry '1' for key 'user.PRIMARY'id为空的时候
mysql> insert into user values(null,'李四');ERROR 1048 (23000): Column 'id' cannot be null5.2联结主键
mysql> create table user2( -> id int, -> name varchar(20), -> password varchar(20), -> primary key(id,name) -> );Query OK, 0 rows affected (0.06 sec)插入记录
mysql> insert into user2 values(1,'zhangsan','123');Query OK, 1 row affected (0.01 sec)mysql> insert into user2 values(1,'zhangsan','234');ERROR 1062 (23000): Duplicate entry '1-zhangsan' for key 'user2.PRIMARY'mysql> insert into user2 values(2,'zhangsan','234');Query OK, 1 row affected (0.01 sec)联结主键只有主键字段之间加起来不反复就能够，但任何一个主键字段都不能够为空。
5.3自增束缚auto_increment
mysql> create table user3( -> id int primary key auto_increment, -> name varchar(20) -> );Query OK, 0 rows affected (0.05 sec)插入数据
mysql> insert into user3 (name) values('zhangsan');Query OK, 1 row affected (0.01 sec)查看mysql> select * from user3;+----+----------+| id | name |+----+----------+| 1 | zhangsan |+----+----------+1 row in set (0.00 sec)自增束缚和主键束缚搭配在一起应用时，它会主动给咱们管去控值，例如：主动减少id。
5.4如果创立表的时候遗记创立主键束缚怎么办？
mysql> create table user4( -> id int, -> name varchar(20));Query OK, 0 rows affected (0.06 sec)mysql> desc user4;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int | YES | | NULL | || name | varchar(20) | YES | | NULL | |+-------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)批改表构造，减少主键束缚 alter ... add
mysql> alter table user4 add primary key(id);Query OK, 0 rows affected (0.12 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc user4;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int | NO | PRI | NULL | || name | varchar(20) | YES | | NULL | |+-------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)如何删除呢？alter ...drop
mysql> alter table user4 drop primary key;Query OK, 0 rows affected (0.10 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc user4;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int | NO | | NULL | || name | varchar(20) | YES | | NULL | |+-------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)应用modify，批改字段，增加束缚
mysql> alter table user4 modify id int primary key;Query OK, 0 rows affected (0.08 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> describe user4;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int | NO | PRI | NULL | || name | varchar(20) | YES | | NULL | |+-------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)5.5惟一束缚
束缚润饰的字段的值不能够反复。
mysql> create table user5( -> id int, -> name varchar(20));Query OK, 0 rows affected (0.06 sec)mysql> desc user5;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int | YES | | NULL | || name | varchar(20) | YES | | NULL | |+-------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)mysql> alter table user5 add unique(name);Query OK, 0 rows affected (0.04 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc user5;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int | YES | | NULL | || name | varchar(20) | YES | UNI | NULL | |+-------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)mysql> insert into user5 values(1,'zhangsan');Query OK, 1 row affected (0.03 sec)mysql> insert into user5 values(1,'zhangsan');ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'user5.name'name=lisi不能反复
mysql> insert into user5 values(1,'lisi');Query OK, 1 row affected (0.01 sec)mysql> create table user6( -> id int, -> name varchar(20), -> unique(name));Query OK, 0 rows affected (0.07 sec)能够对id和name都增加惟一束缚，这时候只有id+name不反复就能够增加记录。
mysql> create table user7( -> id int, -> name varchar(20), -> unique(id,name));Query OK, 0 rows affected (0.06 sec)mysql> insert into user7 values(1,'zhangsan');Query OK, 1 row affected (0.01 sec)mysql> insert into user7 values(2,'zhangsan');Query OK, 1 row affected (0.04 sec)mysql> select * from user7;+------+----------+| id | name |+------+----------+| 1 | zhangsan || 2 | zhangsan |+------+----------+2 rows in set (0.00 sec)如何删除惟一束缚
mysql> alter table user6 drop index name;Query OK, 0 rows affected (0.03 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc user6;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int | YES | | NULL | || name | varchar(20) | YES | | NULL | |+-------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)通过modify增加惟一束缚
mysql> alter table user6 modify name varchar(20) unique;Query OK, 0 rows affected (0.03 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc user6;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int | YES | | NULL | || name | varchar(20) | YES | UNI | NULL | |+-------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)总结：
- 建表的时候增加束缚
- 能够用alter... add增加
- 能够用alter... modify增加束缚
- 删除用alter...drop
5.6非空束缚
润饰的字段不能为空null
mysql> create table user8( -> id int, -> name varchar(20) not null);Query OK, 0 rows affected (0.05 sec)mysql> desc user8;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int | YES | | NULL | || name | varchar(20) | NO | | NULL | |+-------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)测试：如果name为空
mysql> insert into user8 (id)values(1);ERROR 1364 (HY000): Field 'name' doesn't have a default value正确增加不为空时
mysql> insert into user8 values(1,'lisi');Query OK, 1 row affected (0.01 sec)mysql> select *from user8;+------+------+| id | name |+------+------+| 1 | lisi |+------+------+1 row in set (0.00 sec)如果只传name是能够的
mysql> insert into user8 (name)values('zhangsan');Query OK, 1 row affected (0.01 sec)mysql> select *from user8;+------+----------+| id | name |+------+----------+| 1 | lisi || NULL | zhangsan |+------+----------+2 rows in set (0.00 sec)同样能够用alter ... add，alter ... modify来增加惟一束缚。
5.7默认束缚default
就是当咱们插入字段值的时候，如果没有传值，就会应用默认值。
mysql> create table user9( -> id int, -> name varchar(20), -> age int default 10 -> );Query OK, 0 rows affected (0.06 sec)查看表构造
mysql> desc user9;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int | YES | | NULL | || name | varchar(20) | YES | | NULL | || age | int | YES | | 10 | |+-------+-------------+------+-----+---------+-------+3 rows in set (0.00 sec)插入值时直插入id和name那么age应用默认值10，如果age也插入值那么就应用插入的值不应用默认值。
mysql> insert into user9(id,name) values (1,'lisi');Query OK, 1 row affected (0.01 sec)mysql> select *from user9;+------+------+------+| id | name | age |+------+------+------+| 1 | lisi | 10 |+------+------+------+1 row in set (0.00 sec)5.8 外键束缚
波及到两个表：父表，子表。
班级表：
mysql> create table classes( -> id int primary key, -> name varchar(20) -> );Query OK, 0 rows affected (0.05 sec)mysql> desc classes;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int | NO | PRI | NULL | || name | varchar(20) | YES | | NULL | |+-------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)插入值
mysql> insert into classes values(1,'yiban');Query OK, 1 row affected (0.01 sec)mysql> insert into classes values(2,'erban');Query OK, 1 row affected (0.03 sec)mysql> insert into classes values(3,'sanban');Query OK, 1 row affected (0.01 sec)mysql> select *from classes;+----+--------+| id | name |+----+--------+| 1 | yiban || 2 | erban || 3 | sanban |+----+--------+3 rows in set (0.00 sec)学生表：
mysql> create table students( -> id int primary key, -> name varchar(20), -> class_id int, -> foreign key(class_id) references classes(id));Query OK, 0 rows affected (0.06 sec)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 | |+----------+-------------+------+-----+---------+-------+3 rows in set (0.00 sec)插入值
mysql> insert into students values(1001,'zhangsan',1);Query OK, 1 row affected (0.01 sec)mysql> insert into students values(1002,'zhangsan',2);Query OK, 1 row affected (0.01 sec)mysql> insert into students values(1003,'zhangsan',3);Query OK, 1 row affected (0.04 sec)mysql> select *from students;+------+----------+----------+| id | name | class_id |+------+----------+----------+| 1001 | zhangsan | 1 || 1002 | zhangsan | 2 || 1003 | zhangsan | 3 |+------+----------+----------+3 rows in set (0.00 sec)父表classes中没有的数据值，是不能够在子表students中应用的。
父表中的记录被子表援用，是不能够被删除的。
mysql> delete from classes where id=3;ERROR 1451 (23000): Cannot delete or update a parent row:a foreign key constraint fails (`test2`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))数据库三大范式
- 第一范式 1NF
数据库表中的所有字段都是不可分割的原子值。
mysql> create table student( -> id int primary key, -> name varchar(20), -> address varchar(30) -> );Query OK, 0 rows affected (0.12 sec)mysql> insert into student values(1,'zhangsan','甘肃会宁');Query OK, 1 row affected (0.02 sec)mysql> insert into student values(2,'lisi','甘肃会宁');Query OK, 1 row affected (0.01 sec)mysql> insert into student values(3,'laowang','甘肃会宁');Query OK, 1 row affected (0.01 sec)mysql> select * from student;+----+----------+--------------+| id | name | address |+----+----------+--------------+| 1 | zhangsan | 甘肃会宁 || 2 | lisi | 甘肃会宁 || 3 | laowang | 甘肃会宁 |+----+----------+--------------+3 rows in set (0.00 sec)address字段值还能够持续拆分的就不满足第一范式。
create table student2( id int primary key, name varchar(20), country varchar(30), privence varchar(30), city varchar(30), details varchar(30) );insert into student2 values(1,'张三','中国','甘肃','兰州','安宁区安定街道30号');
insert into student2 values(2,'李四','中国','甘肃','兰州','安宁区安定街道34号');
insert into student2 values(3,'王五','中国','甘肃','兰州','安宁区安定街道80号');
mysql> select * from student2;+----+--------+---------+----------+--------+----------------------------+| id | name | country | privence | city | details |+----+--------+---------+----------+--------+----------------------------+| 1 | 张三 | 中国 | 甘肃 | 兰州 | 安宁区安定街道30号 || 2 | 李四 | 中国 | 甘肃 | 兰州 | 安宁区安定街道34号 || 3 | 王五 | 中国 | 甘肃 | 兰州 | 安宁区安定街道80号 |+----+--------+---------+----------+--------+----------------------------+3 rows in set (0.00 sec)范式：设计的越具体，对于某些实际操作可能更好，但不肯定都有益处。个别要结合实际状况设计。
- 第二范式 2NF
第二范式必须是满足第一范式的前提下，要求，除主键外的每一列都必须齐全依赖于主键。
如果呈现不齐全依赖，只可能产生在联结主键的状况下。
create table myolder( product_id int, customer_id int, product_name varchar(30), customer_name varchar(30), primary key(product_id,customer_id));**问题：
除主键外的其余列，只依赖于主键的局部字段。
不满足第二范式
解决办法是拆表。**
create table myolder( older_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));分成三个表之后就满足第二范式的设计！！！
- 第三范式 3NF
必须先满足第二范式， 除开主键列的其余列之间不能有传递依赖关系。
例如：
create table myolder( older_id int primary key, product_id int, customer_id int, customer_phone varchar(15));在这个表中，咱们能够看到customer_phone和customer_id之间存在依赖关系，这样就不满足第三范式的要求；解决办法是：咱们把customer_phone放到customer表中。如下：
create table customer( id int primary key, name varchar(20), phone varchar(15));查问练习题：
1.数据筹备：
学生表student
学号：姓名：性别：出生日期：所在班级；
create table student ( sno varchar(20) primary key, sname varchar(20) not null, ssex varchar(10) not null, sbirthday datetime, class varchar(10));老师表teacher
老师编号，老师名字，老师性别，出生日期，职称，所在部门。
create table teacher( tno varchar(20) primary key, tname varchar(20) not null, tsex varchar(10) not null, tbirthday datetime, prof varchar(20), depart varchar(20) not null);课程表course
课程号，课程名称，老师编号
create table course( cno varchar(20) primary key, cname varchar(20) not null, tno varchar(20) not null, foreign key(tno) references teacher(tno));成绩表score
学号，课程号，问题
create table score( sno varchar(20) not null, cno varchar(20) not null, degree decimal, foreign key(sno) references student(sno), foreign key(cno) references course(cno), primary key(sno,cno));增加数据：
insert into student values('101', '曾华','男','1977-09-01','95033');insert into student values('102','匡明','男','1975-10-02', '95031');insert into student values('103','王丽','女','1976-01-23', '95033');insert into student values('104', '李军','男','1976-02-20','95033');insert into student values('105','王芳','女','1975-02-10', '95031');insert into student values('106','陆君','男','1974-06-03','95031');insert into student values('107','王尼玛','男','1974-06-03','95031');insert into student values('108','张全蛋','男','1974-06-03','95031');insert into student values('109','赵铁柱','男','1974-06-03','95031');insert into teacher values('804','李诚','男','1958-12-02', '副教授','计算机系');insert into teacher values('856','张旭','男','1969-03-12','讲师','电子工程系');insert into teacher values('825','王萍','女','1972-05-05','助教','计算机系');insert into teacher values('831','刘冰','女','1977-08-14','助教','电子工程系');insert into course values('3-105','计算机导论', '825');insert into course values('3-245','操作系统','804');insert into course values('6-166','数字电路','856');insert into course values('9-888','高等数学','831');insert into score values('103','3-105','92');insert into score values('103','3-245','86');insert into score values('103','6-166','85');insert into score values('105','3-105','88');insert into score values('105','3-245','75');insert into score values('105','6-166','79');insert into score values('109','3-105','76');insert into score values('109','3-245','68');insert into score values('109','6-166','81');