Mysql学习笔记(二)——表格及数据的插入

说在前面：以下命令都是用管理员权限打开CMD执行的，创建的表要按要求创建，后面查询的时候直接拿创建的表进行。这个笔记也是自己在学习过程总写的，难免有瑕疵，如果有问题可以直接指正。

1.Mysql常用指令

net start mysql

启动服务器

1
2
3

C:\WINDOWS\system32>net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。

net stop mysql

停止服务器

1
2
3

C:\WINDOWS\system32>net stop mysql
MySQL 服务正在停止.
MySQL 服务已成功停止。

mysql -u root -p

输入配置的密码即可进入后台系统

C:\WINDOWS\system32>mysql -u root -p
Enter password: *********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

show databases;

查看mysql系统中存在的数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| robocup3d          |
| school             |
| sys                |
+--------------------+
6 rows in set (0.19 sec)

mysql>

create database 数据库名字;

创建数据库

1 2	mysql> create database school; Query OK,1 row affected (0.00 sec)

use 数据库名字;

选择数据库，使用数据库时一定要先选择数据库

1
2
3

mysql> use school;
Database changed
mysql>

drop database 数据库名字;

删除指定的数据库

1	mysql> drop database school;

2.创建表格

CREATE TABLE <表名>

（<列名> <数据类型>[<列级完整性约束条件>],

[<列名> <数据类型>[<列级完整性约束条件>]] …[<表级完整性约束条件>]);
- 表名：所要定义的基本表的名字
- 列名：组成该表的各个属性（列）
- 列级完整性约束条件：涉及相应属性列的完整性约束条件
- 表级完整性约束条件：涉及一个或多个属性列的完整性约束条件
例：建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，姓名也不能重复。
1
2
3
4
5
6
CREATE TABLE Student
(Sno varCHAR(5) NOT NULL UNIQUE，
Sname varCHAR(20) UNIQUE，
Ssex varCHAR(1) ，
Sage INT，
Sdept varCHAR(15))；

A.数据类型

整数类型：

类型	字节数	范围
tinyint	1字节	0~255
smallint	2字节	0~2的16次方-1
mediumint	3字节	0~2的24次方-1
int	4字节	0~2的32次方-1
bigint	8字节	0~2的64次方-1

备注:整数类型比较简单,但是它的选择也比较多,开发时注意选择范围即可。

高精度型：(decimal和numeric)

decimal和numeric类型在MySQL中被视为相同的类型,可以用以保存精确的值,比如工资、商品单价等。
Numeric(10,2) 指字段是数字型,长度为10 小数为两位的
备注:通常价格、工资等字段使用这种类型
char和varchar类型:

char (N) 表示定长字符 N取值0~255 varchar(N) 表示变长字符 N取值0~2的16次方-1

char和varchar是最常用的两种字符类型,varchar更为常用有点类似字符数组
日期类型：

DateTime 8个子节
TimeStamp 4个子节
Date 3个子节
Year 1个子节
Time 1个子节
备注:TimeStamp和DateTime显示时间格式是相同的,显示范围不同. TimeStamp类型可以设置自动更新时间为当前时间。这也是建表时比较常用的一个类型。

B.完整性约束条件

常用完整性约束
主码约束： PRIMARY KEY
唯一性约束： UNIQUE
非空值约束： NOT NULL
参照完整性约束
PRIMARY KEY 与 UNIQUE 的区别?

PRIMARY KEY = UNIQUE + NOT NULL
主码就是要求唯一和非空，否则就不能区别其他行，主码为空就意味着这一行是冗余的。

创建学生选课表，其中Cno(课程号)为主码

1
2
3

create Table Course(Cno varCHAR(3), 
					Cname varCHAR(60), Cpno varCHAR(3), 
					Ccredit int not null, Primary key(Cno));

[例] 建立一个“学生选课”表SC，它由学号Sno、课程号Cno，修课成绩Grade组成，其中(Sno, Cno)为主码，Sno，Cno还分别为外码。

CREATE TABLE SC(Sno varCHAR(5), Cno varCHAR(3), 
				Grade int, Primary key (Sno, Cno), 
				FOREIGN KEY (Sno) REFERENCES Student(Sno), 
				FOREIGN KEY (Cno) REFERENCES Course(Cno));

3.查看表格

show create table sc;

可以查看已创建表的属性

| Table | Create Table
+-------+-----------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------+
| sc    | CREATE TABLE `sc` (
  `Sno` varchar(5) NOT NULL,
  `Cno` varchar(3) NOT NULL,
  `Grade` int(11) DEFAULT NULL,
  PRIMARY KEY (`Sno`,`Cno`),
  KEY `Cno` (`Cno`),
  CONSTRAINT `sc_ibfk_1` FOREIGN KEY (`Sno`) REFERENCES `student` (`Sno`),
  CONSTRAINT `sc_ibfk_2` FOREIGN KEY (`Cno`) REFERENCES `course` (`Cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

show tables;

查看当前数据库中的所有表

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| course           |
| sc               |
| student          |
+------------------+
4 rows in set (0.00 sec)

mysql>

describe sc;

使用describe（可以缩写为desc）命令查看表的结构

mysql> describe sc;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| Sno   | varchar(5) | NO   | PRI | NULL    |       |
| Cno   | varchar(3) | NO   | PRI | NULL    |       |
| Grade | int(11)    | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>

4.修改表格

ALTER TABLE <表名>
[ ADD <新列名> <数据类型> [ 完整性约束 ] ]
[ DROP <完整性约束名> ]
[ MODIFY <列名> <数据类型> ]；

<表名>：要修改的基本表
ADD子句：增加新列和新的完整性约束条件
DROP子句：删除指定的完整性约束条件
MODIFY子句：用于修改列名和数据类型

例: 向Student表增加“入学时间”列，其数据类型为日期型。

ALTER TABLE Student ADD Scome DATE；

不论基本表中原来是否已有数据，新增加的列一律为空值。
删除属性列
例：ALTER TABLE Student Drop Scome；
例: 将年龄的数据类型改为半字长整数。

ALTER TABLE Student MODIFY Sage SMALLINT；

注：修改原有的列定义有可能会破坏已有数据

例: 删除学生姓名必须取唯一值的约束。

ALTER TABLE Student DROP key Sname；
alter table Student add unique key sname(sname);

+---------+-------------------------------------
------------------------------------------------
-------------------+
| student | CREATE TABLE `student` (
  `Sno` varchar(5) NOT NULL,
  `Sname` varchar(20) DEFAULT NULL,
  `Ssex` varchar(1) DEFAULT NULL,
  `Sage` smallint(6) DEFAULT NULL,
  `Sdept` varchar(15) DEFAULT NULL,
  UNIQUE KEY `Sno` (`Sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+-------------------------------------
------------------------------------------------
-------------------+

5.插入元组

语句格式
INSERT

 INTO <表名> [(<属性列1>[，<属性列2 >…)]
 VALUES (<常量1> [，<常量2>]    …           )

功能
将新元组插入指定表中

INTO子句
属性列的顺序可与表定义中的顺序不一致
没有指定属性列
指定部分属性列

VALUES子句
提供的值必须与INTO子句匹配
值的个数
值的类型

例: 将一个新学生元组（学号：95010；姓名：陈冬；性别：男；所在系：IS；年龄：18岁）插入到Student表中。
1
2
3
mysql>INSERT
INTO Student (Sno，Sname，Ssex，Sdept，Sage)
VALUES ('95010'，'陈冬'，'男'，'IS'，18)；
例: 将学生张成民的信息插入到Student表中。
1
2
3
mysql>INSERT
INTO Student
VALUES ('95011','张成民','男',18,'CS');
可以不写属性元组，但是直接写VALUES的话一定要和表的属性顺序保持一致

例: 插入一条选课记录(‘95011’，’1’)。

INSERT
    INTO SC(Sno，Cno)
    VALUES ('95011' ， '1')；
#RDBMS将在新插入记录的Grade列上自动地赋空值。
#或者：
INSERT
    INTO SC             
    VALUES (' 95011 '，' 1 '，NULL)；

此时显示插入失败，原因是在SC这张表中，（SNO，CNO）是主码，但是Cno和Sno又是外码（Course和student的主码），由于Cno在course那张表中未定义课程信息，故这里出现就是错误的数据，所以这就要求，外码所在的那张表中必须要有值。

下面用同样的方法添加下面的student表和course表和Sc表

select * from student;

可以查看表中所有的信息

+-------+--------------+------+------+-------+
| Sno   | Sname        | Ssex | Sage | Sdept |
+-------+--------------+------+------+-------+
| 95001 | 张三         | 男   |   20 | CS    |
| 95002 | 李四         | 男   |   21 | IS    |
| 95003 | 王五         | 男   |   18 | MA    |
| 95004 | 马六         | 女   |   19 | CS    |
| 95005 | 苏三         | 女   |   19 | IS    |
| 95006 | 刘七         | 女   |   18 | IS    |
| 95007 | 刘三姐       | 女   |   22 | IS    |
| 95008 | 欧阳锋       | 男   |   23 | MA    |
| 95009 | 欧阳大侠     | 男   |   22 | MA    |
+-------+--------------+------+------+-------+

mysql> select * from course;
+-----+-----------+------+---------+
| Cno | Cname     | Cpno | Ccredit |
+-----+-----------+------+---------+
| 1   | MATH      | NULL |       6 |
| 2   | DB_DESIGN | 3    |       2 |
| 3   | P_DESIGN  | 4    |       3 |
| 4   | OS        | NULL |       2 |
+-----+-----------+------+---------+
4 rows in set (0.00 sec)

mysql> select * from sc;
+-------+-----+-------+
| Sno   | Cno | Grade |
+-------+-----+-------+
| 95001 | 1   |    92 |
| 95001 | 2   |    94 |
| 95001 | 3   |    90 |
| 95001 | 4   |    97 |
| 95002 | 2   |    90 |
| 95002 | 3   |    80 |
| 95003 | 2   |  NULL |
| 95004 | 3   |  NULL |
| 95004 | 4   |    87 |
| 95005 | 1   |    90 |
| 95005 | 2   |    98 |
| 95005 | 3   |    90 |
| 95005 | 4   |    89 |
+-------+-----+-------+
14 rows in set (0.00 sec)

6.修改数据

语句格式
UPDATE <表名>
SET <列名>=<表达式>[，<列名>=<表达式>]…
[WHERE <条件>]；

功能
修改指定表中满足WHERE子句条件的元组
- SET子句
  指定修改方式
  要修改的列
  修改后取值
- WHERE子句
  指定要修改的元组
  缺省表示要修改表中的所有元组

例: 将学生95002的年龄改为22岁（修改单个元组的值）

UPDATE  Student
         SET Sage=22            #将年龄改为22  
         WHERE  Sno=' 95002 '； #找出学号为95002的那一行

例：将所有学生的年龄增加1岁（修改多个元组的值）

1
2
3

UPDATE Student
         SET Sage= Sage+1；   # 这里缺省where代表对所有元组操作

7.删除数据

语句格式
```
DELETE
FROM     <表名>
[WHERE <条件>]；
```
功能
删除指定表中满足WHERE子句条件的元组
WHERE子句
指定要删除的元组
缺省表示要删除表中的全部元组，表的定义仍在字典中

下面的删除例子可以不用执行，因为后面还要用到这三张表，知道怎么写的就行。

例：删除学号为95009的学生记录。

DELETE
         FROM Student
         WHERE Sno= '95009'；

例：删除所有的学生选课记录。

1
2
3

DELETE
        FROM SC；             #where为空，所以默认是整个表的元组

在执行修改语句时会检查修改或删除操作是否破坏表上已定义的完整性规则
实体完整性
参照完整性
用户定义的完整性
NOT NULL约束
UNIQUE约束
值域约束

这时把前面插入报错的SC那个信息再重新插入，就会发现插入成功，原因是course中已经有了课程信息。

INSERT
    INTO SC             
    VALUES (' 95011 '，' 1 '，NULL)；

此时再将student表中的学号为’95011’改为’95022’

mysql> update student set sno='95022' where sno='95011';

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`school`.`sc`, CONSTRAINT `sc_ibfk_1
` FOREIGN KEY (`Sno`) REFERENCES `student` (`Sno`))

mysql>

发现报错，原因是在student表中修改主码，会影响到SC选课表中的Sno，这个修改会导致SC表中的那一行无意义，所以不能修改。

这时尝试删除student中的’95011’的学生信息

mysql> delete from student where sno='95011';

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`school`.`sc`, CONSTRAINT `sc_ibfk_1
` FOREIGN KEY (`Sno`) REFERENCES `student` (`Sno`))

mysql>

发现依然报错，原因和上面类似，如果删除这个信息，会导致sc表中的数据无意义，就因为sno在sc那张表中是外码。

本次笔记就讲了表格和数据的创建，后面会更新笔记，在创建的这三张表上进行查询操作，所以创建的数据库不要删！如果觉本篇笔记总结还不错的话，请点个赞，以鼓励我更上一层楼！