MySQL服务启动
管理员打开cmd
- net start mysql :启动mysql的服务
- net stop mysql :关闭mysql的服务
MySQL登录
- mysql -uroot -p密码
- mysql -hip -uroot -p连接目标的密码
- mysql --host=ip --user=root --password=连接目标的密码
SQL注释
- 单行注释:–(一定要加空格)注释内容或者#注释内容(mysql特有)
- 多行注释:/* 注释 */
DDL:操作数据库和数据库中表的
用来定义数据库对象:数据库、表、列等。关键字:create,drop,alter等
1、操作数据库:CRUD
* C(Create)创建
creat database db1;
create database if not exists db1;
create database db3 character set gbk;
创建一个db4的数据库,判断是否存在,并制定字符集为gbk
create database if not exists db4 character set gbk;
* R(Retrieve)查询
查询所有数据库的名称:show databases
查看某个数据库的字符集:查询某个数据库的创建语句:show create database 数据库名称;
* U(Updata):修改
修改数据库的字符集:alter database 数据库名称 character set 字符集名称;
* D(Delete):删除
删除数据库:drop database 数据库名称;
drop database if exists db3;
* 使用数据库
查询当前正在使用的数据库名称:
select database();
use 数据库名称;
2、操作表
- 创建 C(Create)创建
- 语法
create table 表名(
列名1 数据类型1,
列名2 数据类型2,
…
列名n 数据类型n
);
** 注意:最后一个数据类型后面不加逗号,否则报错。
** 数据类型:- int类型:整数类型,age int,
- double小数类型:score double(5,2),小数最多有5位,小数点后保留两位。
- date日期类型:只包含年月日的类型,yyyy-MM-dd,
- datetime日期类型:包含年月日,时分秒:yyyy-MM-dd HH:mm:ss
- timestamp时间戳类型:包含年月日时分秒:yyyy-MM-dd HH:mm:ss 如果将来不给这个字段赋值,或赋值为null,则默认使用当前的系统时间,来自动赋值
- varchar字符串类型:name varchar(20):姓名最大20个字符
- 语法
-
创建表:
create table studet(
id int,
name varchar(20),
age int,
score double(5,2),
birthday date,
insert_time timestamp
);复制表:create table stu like student;
创建一个stu表和student表一样。
-
R(Retrieve)查询
查询数据库中所有表的名称:show tables;
查询表的字符集show create table 表名;
查询表结构:desc 表名; -
U(Updata):修改
*修改表名 alter table 表名 rename to 新表名; *修改表的字符集 alter table 表名 character set 字符集名称; *添加一列 alter table 表名 add 列名 数据类型; *既改列名也改类型 alter table 表名 change 旧列名 新列名 新数据结构; *只修改列的数据类型 alter table 表名 modify 列名 新数据类型; *删除列 alter table 表名 drop 列名;
-
D(Delete):删除
* drop table 表名;
* drop table if exists 表名;
DML:数据操作语言
用来对数据库中表的数据进行增删改。关键字:insert,delete,update等
- 添加数据:insert into 表名(列名1,列名2,…列名n) values(值1,值2,…值n);
注意事项:- 列名和值要一一对应
- 如果表名后,不定义列名,则默认给所有列添加值:insert into 表名 values (值1,值2,…值n);
- 除了数字类型,其他类型需要使用引号(单双都可以)引起来。
- 删除数据:delect from 表名 [where 条件]
如果不加条件,则删除表中所有数据。(哪怕是要删除表中所有数据也不建议使用,因为表中有多少条数据,就会执行多少次删除操作)
truncate table 表名;-- 删除表,然后再创建一个一模一样的空表(更推荐)。 - 修改数据:update 表名 set 列名1 = 值1,列名2 = 值2,…[where 条件];
注意:如果不加任何条件,则会将表中所有记录全部修改
DQL:数据查询语言
用来查询数据库中表的记录(数据)。关键字:select,where等
-
查询表中记录:select * from 表名;
* 语法: * select * 字段列表 * from * 表名列表(可单表可多表,多表则用逗号分隔) * where * 条件列表 * group by * 分组字段 * having * 分组之后的条件 * order by * 排序 * limit * 分页限定 1、多个字段的查询 select name,age,score from student; select后跟多个查询字段用逗号隔开 2、去除重复 select distinct 查询字段 from student;(在查询字段前写distinct,结果集完全一样才可以用distinct去重复,注意数据是否有空字符) 3、计算列 一般可以使用四则运算计算一些列的值(一般只会进行数值型的计算) 计算math_score和english_score 之和: select name , math_score , english_score , math_score + english_score from student; *注意:如果数据中有null参与运算,则计算结果就为null,可以使用IFNULL函数来解决 SELECT NAME , math_score , english_score , IFNULL(math_score,0) + IFNULL(english_score,0) FROM student1; 如果为null,则用0来替换 4、起别名 SELECT NAME , math_score , english_score , IFNULL(math_score,0) + IFNULL(english_score,0) AS 总分 FROM student1; as可省略 在字段后用as关键字后跟别名,也可以直接空格写别名
-
排序查询:order by 排序字段1 排序方式1 ,排序字段2 排序方式2…
排序方式:ASC:升序,默认的。DESC:降序。
注意:如果有多个排序条件,则当前边的条件值一样时,才会判断第二条件。 -
聚合函数:将一列数据作为整体,进行纵向计算。
- count:计算个数
select count(name) from student; - max:计算最大值
SELECT MAX(english_score) FROM student1; - min:计算最小值
SELECT MIN(english_score) FROM student1; - sum:计算和
- avg:计算平均值
SELECT AVG(english_score) FROM student1;
注意:聚合函数的计算,不包含null值。avg函数做平均值计算时,分母并非为总数,而是除去null后的总数
- count:计算个数
-
分组查询:
* 语法:group by 分组字段;
* 按照性别分组,分别查询男、女同学平均分
* SELECT gender,AVG(math_score) FROM student1 GROUP BY gender;
* 按性别分组,分别查询男女同学个数
* SELECT gender,COUNT(id),AVG(math_score) FROM student1 GROUP BY gender;
* 按性别分组查询男女同学个数以及平均分,同时要求低于70分的不参与分组
* SELECT gender , COUNT(id) , AVG(math_score) FROM student1 WHERE math_score > 70 GROUP BY gender;
* 按性别分组查询男女同学个数以及平均分,同时要求低于70分的不参与分组,还要求分组后!分组后人数要大于5人用having
* SELECT gender , COUNT(id) , AVG(math_score) FROM student1 WHERE math_score > 70 GROUP BY gender HAVING COUNT(id) > 5;
* 注意:
* 1、分组之后查询的字段:分组字段、聚合函数
* where和having 的区别:
* 1、where 在分组之前进行限定,如果不满足条件,则不参与分组。having在分组之后进行限定,如果不满足条件则不会被查询出来。
* 2、where后不可以跟聚合函数,having则可以进行聚合函数的判断。
IFNULL函数:
IFNULL(表达式1,表达式2)判断表达式1是否为null,如果为null则替换为表达式2
意义:null参与的运算最后结果都为null
IFNULL(english,0) -- 如果english值为null,则替换为0
-
分页查询
1. 语法:limit 开始的索引,每页查询的条数。 2. 开始索引计算公式:(当前页码-1)*每页显示条数 3. limit 是MySQL的“方言”,Oracle和SQL sever有其特有的方言。
-
条件查询
-
where子句后跟条件
-
运算符:> , < , <= , >= , = , <>(<>在sql中表示不等于,在mysql中也可使用!=)
-
BETWEEN … AND :在一个范围之内,如:between 100 and 200相当于条件在100到200之间,包含头和尾
-
IN(集合):集合表示多个值,使用逗号分隔
-
LIKE’张%':模糊查询
-
IS NULL:查询某一列为NULL的值,注意:不能写=null
-
SELECT * FROM student1 WHERE english_score IS NULL; -- 查询英语成绩为null的,如果是查询不为null的则是is not null
-
and或&&:与,SQL中建议使用前者,后者并不通用
-
or或||:或
-
not或!:非
- 模糊查询
- 占位符:_ 和 %
- 下划线 _ 代表任意单个字符,而%则代表之后或之前所有,多个任意字符
- – 查询所有姓王的学生
- SELECT * FROM student1 WHERE NAME LIKE ‘王%’ ORDER BY id ;
- 如果是使用
- SELECT * FROM student1 WHERE NAME LIKE ‘王%’ GROUP BY gender ORDER BY id ;
- 则报错,因为分组查询中,我们查询的字段只能为分组查询中的字段以及聚合函数。
- – 查询第二个字为伟的人
SELECT * FROM student1 WHERE NAME LIKE ‘_伟%’ ; - – 查询姓名为三个字的
SELECT * FROM student1 WHERE NAME LIKE ‘___’; - – 查询姓名中包含琳字的
SELECT * FROM student1 WHERE NAME LIKE ‘%琳%’;
DCL:数据控制语言
用来定义数据库的访问权限和安全级别,及创建用户。关键字:GRANT,REVOKE等
约束
1. 概念:对表中数据进行限定,保证数据的正确性、有效性和完整性。
2. 分类:主键约束:primary key,非空约束:not null,唯一约束:unique,外键约束:foreign key
非空约束:not null
1. 创建表时添加约束
CREATE TABLE stu(
id INT,
NAME VARCHAR (10) NOT NULL -- 非空约束
);
2. 创建表后,添加非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(10) NOT NULL;
3. 删除name的非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(10) ;
唯一约束:unique
1. 创建表时添加唯一约束
CREATE TABLE stu(
id INT ,
phone_number VARCHAR(20) UNIQUE-- 添加唯一约束
);
* 注意mysqll中,唯一约束限定的列的值可以有多个null
2. 删除唯一约束
-- alter table stu modify phone_number varchar(20); 跟上述非空约束不一样,如此修改并不能删除唯一约束
ALTER TABLE stu DROP INDEX phone_number;
应使用drop index删除
3. 在创建表后,添加唯一约束
ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;
* 注意,如果添加唯一约束的列的数据项中有重复数据则报错。
主键约束:primary key
-
注意: 1. 含义:非空且唯一 2. 一张表只能有一个字段为主键 3. 主键就是表中记录的唯一标识
-
1. 在创建表时给id添加主键 CREATE TABLE stu( id INT PRIMARY KEY, NAME VARCHAR(20) ); 2. 删除主键 ALTER TABLE stu MODIFY id INT;-- 错误写法,无法删除主键约束 正确写法:应使用DROP PRIMARY KEY关键字 ALTER TABLE stu DROP PRIMARY KEY; 3. 创建表后如何添加主键 ALTER TABLE stu MODIFY id INT PRIMARY KEY;
外键约束:foreign key
让表与表产生关系,从而保证数据的完整性
注意:外键值可以为null,但是不可以为不存在的外键值
一个表中的一个列被另一个不同的表中的列的直接引用,含有外键的表为“子表”,被外键引用的表被称为“父表” 外键级联(Cascade):当父表有更新或删除时,会自动更新或删除子表中的关联行 Set Null
在创建表时可以添加外键
CREATE TABLE 表名(
...
外键列
CONSTRAINT 外键名称 FOREIGN KEY (外键列名称) REFERENCES 主表名称(主表列名称)
);
-- 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名;
-- 在创建表后添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键列名称) REFERENCES 主表名称(主表列名称);
级联:Cascade
当主表被子表使用外键约束后,想要修改被约束的数据时会报错,我们可以先使子表中数据置为null,再将主表中数据修改,之后再将子表数据改过来即可。
步骤有些麻烦,所以我们需要采用级联操作简化操作
添加外键时,设置级联更新on update cascade、级联删除on delete cascade
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键列名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE;
- 如何理解级联:
假设现在要做删除操作,当在主表(母表/父表)要删除一个叫“张三”同学的相关信息,马上在子表中查找是否有“张三”这个记录,如果有,那么在子表中也会删除“张三”的对应记录 - 如何理解限制:
假设现在要做删除操作,当在主表要删除一个叫“张三”同学的相关信息,马上在子表中查找是否有“张三”这个记录,如果有,马上限制在主表中做删除操作。 - 如何理解忽略:
假设现在要做删除操作,当在主表要删除一个叫“张三”同学的相关信息,不需要查找子表里是否有“张三”,此时主表的删除与子表无关。
自动增长
如果某一列是数值类型的,使用auto_increment可以完成值的自动增长,一般与主键连在一起用。
* 在创建表时,添加主键约束,并且完成主键自增长
CREATE TABLE stu(
id INT PRIMARY KEY AUTO_INCREMENT,-- 给id添加主键并自增长
NAME VARCHAR(20)
);
INSERT INTO stu (id,NAME) VALUES (NULL,'wang')
-- null值不可以添加主键中,但是当我们设其为自动增长时,可以添加为null并且会自动增长
-- 当然也可以手动委派值
* 删除自动增长
ALTER TABLE stu MODIFY id INT;
* 添加自动增长
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT
数据库的设计
多表之间的关系
- 一对一
- 一对多(多对一)
- 多对多
- 实现关系
- 一对多(多对一)
- 如:部门和员工
- 实现方式:在多的一方建立外键,指向一的一方的主键。
- 多对多
- 如:学生表和课程表,一个学生可以选择多门课程,一个课程也可以被多个学生所选择
- 实现方式:多对多的表述关系需要借助一张中间表,至少得有两个字段,这两个字段作为第三张表的外键,分别指向另外两张表的主键。
- 一对一
- 实现方式:可以在任意一方添加唯一外键指向另一方的主键,也可以合成一张表
- 一对多(多对一)
-- 创建旅游线路分类表 tab_category
-- cid 旅游线路分类主键,自动增长
-- cname 旅游线路分类名称非空,唯一,字符串 100
CREATE TABLE tab_category(
cid INT PRIMARY KEY AUTO_INCREMENT,
cnamme VARCHAR(100) NOT NULL UNIQUE
);
-- 创建旅游线路表 tab_route
CREATE TABLE tab_route(
rid INT PRIMARY KEY AUTO_INCREMENT,
rname VARCHAR(100) NOT NULL UNIQUE,
price DOUBLE,
rdate DATE,
cid INT,
FOREIGN KEY (cid) REFERENCES tab_category(cid) -- 未指定外键名称
);
-- 创建用户表
CREATE TABLE tab_user(
uid INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(100) UNIQUE NOT NULL,
PASSWORD VARCHAR(30) NOT NULL,
NAME VARCHAR(100),
birthday DATE,
sex CHAR(1) DEFAULT '男',
telephone VARCHAR(11),
email VARCHAR(100)
);
-- 创建用户与线路之间的第三张中间表
CREATE TABLE tab_favorite(
rid INT,
uid INT,
DATE DATETIME,
-- 创建复合主键
PRIMARY KEY(rid,uid),
FOREIGN KEY (rid) REFERENCES tab_route(rid),
FOREIGN KEY (uid) REFERENCES tab_user(uid)
);
数据库设计的范式
概念:设计数据库时,需要遵循的一些规范
- 设计关系数据库时,遵从不同的规范要求,设计出合理的关系型数据库,这些不同的规范要求被称为不同的范式,各种范式呈递次规范,越高的范式数据库冗余越小。
- 关系数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)。
分类:
3. 第一范式:每一列都是不可分割的原子数据项
4. 第二范式:在1NF基础上,非码属性必须完全依赖于候选码(在1NF基础上消除非主属性对主码的部分函数依赖) 说人话:让所有的非主属性完全依赖于主码
几个概念:
1. 函数依赖:A-->B,如果通过A属性(属性组)的值,可以确定唯一B属性的值。则称B依赖于A
2. 完全函数依赖:A-->B,如果A是一个属性组,则B属性值的确定需要依赖于A属性组中所有属性的值
例如:(学号,课程名称)--> 分数
3. 部分函数依赖:A-->B,如果A是一个属性组,则B属性值的确定只需要依赖于A属性组中某一些值即可。
例如:(学号,课程名称)--> 姓名
4. 传递函数依赖: A-->B,B-->C,如果通过A属性(属性组)的值,可以唯一确定B属性的值,再通过B属性(属性组)的值可以唯一确定C属性的值,则称C传递函数依赖于A
例如:学号-->系名,系名-->系主任
5. 码:如果一张表中,一个属性或属性组,被其他所有属性所完全依赖,则称这个属性(属性组)为该表的码。
例如:该表中码为:(学号,课程名称)
* 主属性:码属性组中的所有属性
* 非主属性:除过码属性组中的所有属性
- 第三范式:在2NF基础上,任何非主属性不依赖于其他非主属性(在2NF基础上消除传递依赖)
设计数据库
按照第一范式重新设计
按照第二范式重新设计
按照第三范式重新设计
数据库备份和还原
命令行
-
语法:
1. 备份:mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
2. 还原:1. 登录数据库 2. 创建数据库 3. 使用数据库 4. 执行文件。source 文件路径