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右键创建数据库–>右键数据库新建数据库表–>添加字段)
- 在历史记录中可以看到相对应的数据库操作语句
- 在询问窗口可以通过操作语句创建数据库
- SQLyog的使用:
- 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;