黑马程序员技术交流社区

标题: 数据库基本操作命令 [打印本页]

作者: 1049355629 时间: 2019-8-14 15:11
标题: 数据库基本操作命令
数据库数据库的基本概念： MySql DBMS = Database Management System DB的特点持久化数据库就是一个文件系统方便储存和管理提供统一的操作方式 DB2 IBM的数据库，银行系统常用 SQLite嵌入式小型数据库，手机端和浏览器常用 MySql阿里巴巴版 MariaDB——MySql原作者的另一个产品，底层和MySql一样两个cmd命令： services.msc 打开windows服务窗口 net start mysql 启动mysql服务 net stop mysql 停止mysql服务 netstat -aon显示端口列表 cmd窗口显示中文乱码问题
切换为UTF-8：chcp 65001
切换回GBK：chcp 936
登录数据库 mysql -uroot -proot （默认连接3306端口）如果-p之后不写密码，会提示输入密文密码，这样更安全
mysql -h127.0.0.1 -uroot -proot -P端口号 mysql --host=ip --user=root --password=root 两种连接外部数据库的方式 quit 退出数据库 exit；关闭cmd mysql软件安装目录 my.ini ——mysql的配置文件 mysql的数据目录结构数据库——文件夹表——文件数据——文件中的数据 SQL语句（Structured Query Language）定义了操作所有关系型数据库的规则。每一种数据库的操作方式存在不一样的地方，称为方言。语法：以多行或单行的形式书写，分号结尾可以用空格或缩进大小写不敏感，建议大写三种注释单行注释 --空格例如 show databases; -- 查询所有数据库 #（可以不加空格）多行注释 /* */ DDL 操作数据库和表 CRUD 操作数据库 Create create database db1; create database if not exists db1; -- 先判断再创建 create database db2 character set gbk; -- 创建时指定字符集 create database if not exists db3 character set gbk; -- 综合使用Rtrieve
show databases;show tables;
show create database mysql; -- 查看某个数据库的创建语句（包含字符集信息） Update alter database db1 character set utf8; -- 注意不加中划线 Delete drop database db1; drop database if exists db1; 使用数据库查询当前正在使用的数据库名称 select database(); use db1; 操作表 Create create table table1( 列名1 数据类型1，列名2 数据类型2， ... 列名3 数据类型3); 数据类型整数 int 小数 double(5,2) -- 最多5位，小数点后保留2位日期 date -- 只包含年月日，格式为 yyyy-MM-dd datatime -- 包含年月日时分秒,格式为 yyyy-MM-dd HH:ss:mmtimestamp -- 特点：如果不赋值，则默认使用添加记录时系统时间自动赋值字符串 varchar(20) -- 最大20个字符 create table stu like student; -- 克隆student表 Rtrieve
show tables; -- 查询某个数据库中所有表名称 desc table1; -- 查询某个表结构 description Delete drop table t1; drop table if exists t1; Update alter table t1 rename to t2; -- 修改表名 show create table t2;
alter table t1 character set gbk; -- 修改表的字符集修改列 alter table t1 add gender varchar(10); -- 添加一列 alter table t1 change gender sex varchar(10); -- 修改列名 alter table t1 modify sex varchar(20); -- 篡改列的类型 alter table t1 drop sex; -- 删除列 DML 增删改表中数据添加数据 insert into student(id,name,gender) values(1,'张无忌','男'); 注意事项：列名和值要一一对应，值类型也要对应如果不写列名，默认给所有字段添加值不想赋值的列，可以用NULL代替除了数字类型，其他类型需要使用引号（单双都行）日期使用 '1994-04-05'的格式赋值更新update student set age=19,set score=90 where id=1; -- 多个记录用逗号隔开删除数据 delete from student; -- 删除所有数据，效率低，一条一条地删除，但是有日志记录，有可能找回来 delete from student where id=1; -- 删除表中id为1的数据 truncate table student; -- 删除表，然后再创建一个一模一样的空表，效率更高，推荐使用，不过这种删除的更彻底，找不回来 DQL查询select * from student; select 字段列表 from 表明列表 where 条件列表 group by 分组字段 having 分组之后的条件 order by排序 limit 分页限定多个字段查询 select name,age,score from 表名; 去重select distinct address from student;
计算select name ,math,english,math+english from student; 如果算式中包含null，则结果也为null select name ,math,ifnull(english,0) ,math+english from student; 如果english为null，则替换成0 注意 IFNULL 方法不用来修饰原始列，而是用来修饰结果列起别名select name ,math,ifnull(english,0) ,math+english as 总分 from student; select name ,math,ifnull(english,0) ,math+english 总分 from student; 逻辑运算符 > < = <> != >= <= between ... and ... AND && OR || IN关键字 where math IN (88,90,99) -- math在其中模糊查询使用LIKE关键字 % 任意个任意字符 _ 单个任意字符判断空值IS NULL IS NOT NULL

1、DQL
排序order by 排序字段排序方式 [,排序字段排序方式] 排序方式 ASC 升序（默认） DESC 降序聚合函数将一列数据作为一个整体，进行纵向运算 count -- 计算个数 max -- 计算最大值
min -- 计算最小值 sum -- 求和 avg -- 计算平均值案例select sum(name) from student3; 注意，聚合函数的运算会忽略NULL值解决方式 ①避免计算包含null的列（选择主键计算） ①select count(ifnull(english,0)) from student3; ②select count(*) from student3; -- 只要有数据就会计算分组计算查询 group by 分组字段注意，1 分组之后查询的字段：分组字段或聚合函数，其他字段没意义select sex,avg(math) from student3 group by sex;-- 按照性别分组，分别求男、女平均分 select sex,avg(math), count(id) from student3 group by sex;-- 按照性别分组，分别求男、女平均分分组计算前的条件限定 select sex,avg(math) from student3 where math>70 group by sex; --不满足条件不参与分组分组计算后的条件限定 select sex,avg(math) from student3 group by sex having count(id)>2; 关键字having 注意：where不可以跟聚合函数，having可以跟聚合函数分页查询 limit 起始索引, 条目数量公式：起始索引 = （当前页码-1）* 每页条目数量注意，分页操作的limit语法是方言，不同的数据库系统有不同的分页语法
2、约束
——对表中数据进行限定，保证数据的正确性、有效性、完整性 1、主键约束 primary key
——非空且唯一一张表只能有一个主键主键是表中记录唯一表示关键字 primary key 注意，删除时使用 alter table student drop primary key 不需要写列名，因为一个表只有一个主键添加约束时，不能有重复元素和null值自动增长如果某一列是数值类型，使用auto_increment关键字完成自动增长语法： primary key auto_increment 删除时，直接不写注意，只能有一个字段被设置成自增 2、非空约束 not null ①创建表时添加约束关键字 not null ②创建后修改表的字段时添加约束删除约束时，直接不写约束就行 3、唯一约束 unique 关键字 unique 注意，添加约束时，不能有重复元素 mysql中唯一约束的值可以有多条null 注意，删除的时候需要特别语句 drop index 字段名 alter table student drop index phone; 4、外键约束 foreign key ——在一张表中记录另一张表的主键信息吗，减少冗余 [constrant 外键名] foreign key (外键列名) references 主表 (列名) 注意，上述外键名只是索引名，要先添加外键列再添加外键约束
数据冗余拆分表删除外键约束 alter table employee drop foreign key 外键名创建后再添加外键约束 alter table employee add constrant 外键名 foreign key (外键列名) references 主表 (列名) 注意，外键列不一定是主键，只要是唯一属性的都可以作为外键外键可以为null，但不能是主表不存在的值学习如何用工具操作级联操作级联更新需要在添加外键的同时，设置级联 on update cascade 级联删除（危险操作！慎用！） on delete cascade
3、多表关系
1、一对一人 - 身份证如何实现？任意一方设置外键关联对方的主键，并且让其外键唯一 2、一对多（多对一）重要部门 - 员工 3、多对多学生 - 课程（至少出现三张表）需要设计学生-课程关系表关系表建议设置联合主键
4、范式
——设计数据库时需要遵循的规范
第次规范第一范式、第二范式、第三范式冗余度随级别升高而减少第一范式(1NF)：每一列都是不可分割的原子数据项第二范式 (2NF)：在1F的基础上，消除非主属性对码的部分依赖（不完全依赖）函数依赖 A -> B 如果通过A能确定B，则成B依赖于A 学号 -> 姓名姓名依赖于学号属性组(学号,课程) -> 分数完全函数依赖 A -> B 如果A是一个属性组，则B的属性值确定完全依赖与A属性组不完全依赖 A -> B 如果A是一个属性组，则B的属性值确定只依赖于A的部分属性传递函数依赖 A -> B && B-> C => A -> C 称：C 传递依赖于A 学号 -> 系别系别 -> 系主任码：在一张表中，一个属性或属性组被其他所有属性所依赖属性组（学号+课程名称）为一个码第三范式 (3NF)：消除传递依赖
5、数据库备份还原
数据库管理员的工作 1、命令行备份： mysqldump -u用户名 -p密码数据库名称> 保存的路径（以sql的形式保存）还原： 2、图形化工具

欢迎光临黑马程序员技术交流社区 (http://bbs.itheima.com/)

黑马程序员IT技术论坛 X3.2