MySQL学习(一)---数据库的概念/基本操作命令/数据表的增删改/外键

MySQL学习(一)—数据库的概念/基本操作命令/数据表的增删改/外键

01 概念

数据库相关概念

• DB：DataBase数据库（一个数据“仓库”，能够保存并安全管理数据。数据有组织，可共享。）
  • SQL ：关系型数据库 （关系型数据库通过外键关联来建立表与表之间的关系）
  • NOSQL ：Not Only SQL 非关系型数据库 （非关系型数据库通常指数据以对象的形式存储在数据库中，而对象之间的关系通过每个对象自身的属性来决定）
• DMMS : DataBase Management System 数据库管理系统 （科学组织和存储数据，高效地获取和维护数据）
• MySQL是现在流行的开源，免费的关系型数据库（由瑞典MySQL AB公司开发，后被SUN公司收购，现在属于ORACLE公司）
• SQLyog ：可手动操作，管理MySQL数据库的图形化软件工具
  • SQLyog的使用：
    • 连接本地MySQL数据库（打开SQLyog–>文件—>新连接—>新建—>填写连接信息–>测试连接–>测试成功后连接）
    • 新建数据库（root@localhost右键创建数据库–>右键数据库新建数据库表–>添加字段）
    • 在历史记录中可以看到相对应的数据库操作语句
    • 在询问窗口可以通过操作语句创建数据库
• MySQL的数据表的类型有MyISAM , InnoDB, HEAP , BOB , CSV等，常见的为MyISAM , InnoDB。
  • MyISAM:不支持事务处理，数据行锁定，外键约束。支持全文索引，表空间较小。
  • InnoDB：支持事务处理，数据行锁定，外键约束。不支持全文索引，表空间较大，约为MyISAM的两倍。

---

02 基本操作命令

• 注意事项：1.不区分大小写 （数据库排序规则设置为utf8_general_ci校对不区分大小写）

​ **2.语句以;结尾 **

​ 3.数据库名或表名通常用反引号`` 引出，用于区别数据库中的关键字

---

• 基本操作命令(命令行)

mysql -uroot -p******;//登陆MySQL -u后为用户名，这里以root为例 ******为用户密码
show databases;//查看所有数据库
use database_name;//选择数据库
SELECT * FROM database_name.table_name;//查询数据库下的表单信息
show tables;//显示数据库mysql中所有的表
flush privileges;//刷新数据库
drop database database_name;//删除数据库
exit;//退出MySQL

• 创建数据库操作命令（SQLyog工具下）

#在询问窗口输入（MySQL注释用#声明）
#创建数据库
CREATE DATABASE `database_name`;

#创建表
CREATE TABLE IF NOT EXISTS `table_name`（
#创建列字段
`字段名1` 列类型 数据字段属性 COMMENT '字段1的注释',
......
`字段名n` 列类型 数据字段属性 COMMENT '字段n的注释',

）ENGINE=InnoDB DEFAULT CHARSET=utf8;

---

• 常见错误：
  • 创建表单filed用``引出，注意此处的符号不是单引号，而是tab键上方的符号。
  • default、comment等加入的内容用单引号引出。

---

03 数据库的增删改

• 列类型：规定数据库中该列存放的数据类型
  • 常用数值类型：tinyint（非常小的数据，1字节）int(标准整数，4字节) double（双精度浮点数，8字节）
  • 常用字符串类型：char（固定字符串，检索快，费空间）varchar（可变字符串）text（文本串）
  • 常用日期和时间型数据类型：DATETIME(YY-MM-DD hh:mm:ss)
  • NULL值：没有值（不要用NULL 进行算术运算，结果仍为NULL）
• 数据字段属性
  • UnSigned:声明该数据列不允许负数
  • ZEROFILL:声明不足位数的用0来填充，如INT（5），5则为00005
  • Auto_InCrement：声明该列值是自动增长的，每添加一条数据，自动在上一个记录数上默认加1
  • NULL 和 NOT NULL :
  • 默认为NULL , 即没有插入该列的数值
  • 如果设置为NOT NULL , 则该列必须有值
  • DEFAULT :声明默认值，如，性别字段,默认为"男" , 否则为 “女” ; 若无指定该列的值 , 则默认值为"男"的值

---

• 增添数据

#INSERT INTO命令
INSERT INTO Table_name(字段1,字段2,字段3,...) VALUES('值1','值2','值3')
# 添加一行数据时'字段1,字段2...'该部分可省略 , 但添加的值务必与表结构,数据列,顺序相对应,且数量一致.

#示例
#插入单个数据，主键不自增
INSERT INTO height(`studentNo`,`height`) VALUES (1000,178);
#可以省略字段名
INSERT INTO height VALUES (1000,178);
#插入多个数据，主键不自增
INSERT INTO height(`studentNo`,`height`) VALUES (1001,188),(1002,170),(1003,178),(1004,158);
#插入单个数据，主键自增
INSERT INTO height (`tall`) VALUES (178);
#插入多个数据，主键自增
INSERT INTO height (`tall`) VALUES (178),(180),(168),(190);

• 删除数据表

# DROP TABLE [IF EXISTS] 表名 
# IF EXISTS判断是否存在该数据表,如果删除不存在的数据表会抛出错误

# DELETE FROM tabel_name WHERE condition;
# condition为筛选条件,如不指定则删除该表的所有列数据

# TRUNCATE [TABLE] table_name;
# 用于完全清空数据表，但表结构，索引，约束等不变

#举例
#使用delete删除height表中studentNo=1004的数据，其中studentNo属性为自增
DELETE FROM height WHERE studentNo=1004; 
#删除后，使用INSERT添加数据
INSERT INTO height(`height`) VALUES (178);
#执行结果：添加的该条数据的studentNo不再是1004，而是1005
#结论：使用delete删除数据，自增当前值依然从原来基础上进行,会记录日志.

#使用truncate删除height数据表
TRUNCATE TABLE height;
#删除后，使用INSERT添加数据
INSERT INTO height(`height`) VALUES (178);
#执行结果：添加的该条数据的studentNo不再是1004，而是1
#结论：使用truncate删除数据,自增当前值会恢复到初始值重新开始;不会记录日志.

• 修改数据表

#修改表名 :ALTER TABLE 旧表名 RENAME AS 新表名
#添加字段 :ALTER TABLE 表名 ADD字段名 列属性[属性]
#修改字段 :ALTER TABLE 表名 MODIFY 字段名 列类型[属性]   （适用于不改变列名只改变列属性的情况）
#		  ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[属性]
#删除字段 :ALTER TABLE 表名 DROP 字段名

#示例
#修改列字段studenNo的属性，不修改列字段名称
ALTER TABLE height MODIFY `studentNo` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号';
#修改列字段studenNo的属性，修改列字段名称
ALTER TABLE height CHANGE `height` `tall` INT(3) NOT NULL COMMENT '学号';

#修改数据表中某一行的某个列值
# UPDATE table_name SET column_name=value [,column_name2=value2,...] WHERE condition;
#总结：column_name 为要更改的数据列，value 为修改后的数据，可以为变量，具体指表达式或者嵌套的SELECT结果。condition 为筛选条件,如不指定则修改该表的所有列数据。

#示例
#修改studentNo=1002的学生的身高
UPDATE height SET `height`=190 WHERE studentNo=1002;

---

04 外键

• 概念：各个相关联的表是依靠公共关键字进行关联的。如果公共关键字在一个数据表的关系中是PRIMARY KEY，即主键，那么这个公共关键字被称为另一个表的关系中的外键(foreign key)。以这个外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。
• 作用：保持数据一致性，完整性，主要目的是控制存储在外键表中的数据,约束。 使两张表形成关联，外键只能引用外表中的列的值或使用空值。
• 外键的创建

# 创建外键的方式一：创建从表的同时创建外键

# 示例 
# 先创建要被外键关联的主表
CREATE TABLE `grade` (
`gradeid` INT(2) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '年级id',
`grade` varchar(20) NOT NULL COMMENT '年级'
) ENGINE=INNODB DEFAULT CHARSET=utf8;

# 再创建从表
CREATE TABLE `student` (
  `studentNo` INT(4) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号'
  `studentname` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `sex` TINYINT(1) DEFAULT '1' COMMENT '性别',
  `gradeid` INT(10) DEFAULT NULL COMMENT '年级',
  KEY `FK_gradeid` (`gradeid`),
  CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8


# 创建外键的方式二：数据表创建完后，修改从表属性，添加外键，关联主表
ALTER TABLE student
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);


#关联外键之后，就可以进行联合查询
SELECT a.StudentNo,a.StudentName,b.grade 
FROM student AS a,grade AS b
WHERE a.gradeid=b.gradeid;

• 外键的删除

# 删除具有主外键关系的表时 , 要先删从表 , 后删主表
# 示例
ALTER TABLE student DROP FOREIGN KEY FK_studentNo;
ALTER TABLE student DROP INDEX FK_gradeid;

---