MySQL 基础使用

最新推荐文章于 2021-07-13 15:37:13 发布

hopyGreat

最新推荐文章于 2021-07-13 15:37:13 发布

阅读量229

点赞数

分类专栏： mysql SQL

本文链接：https://blog.csdn.net/hopygreat/article/details/99705183

版权

mysql 同时被 2 个专栏收录

13 篇文章 0 订阅

订阅专栏

SQL

2 篇文章 0 订阅

订阅专栏

MySQL基础使用

一、关于MySQL支持的数据类型

1. 串数据类型

最常用的数据类型，用于存储字符型数据。有两种基本的串类型：定长串和变长串。

定长串存储长度固定的字符串数据，其长度在创建表时指定，如果存储数据不满定长会进行填充，但不允许存储数据长度超出。

变长串存储可变长度的字符串数据。部分变长类型具有最大定长，部分类型无最大定长。

变长数据串虽然灵活但需要底层的算法维护，因此其性能相对定长存在一定的损失。并且MySQL不允许对变长列进行索引，也会极大影响SQL的执行性能。

串数据类型
数据类型	说明
CHAR	1~255个字符的定长串。长度需在创建时指定，否则默认CHAR（1）
VARCHAR	长度可变，最多不超过255字节。创建时使用VARCHAR（n）指定最大长度
TEXT	最大长度为64K的变长文本
TINYTEXT	与TEXT相同，但最大长度为255字节
MEDIUMTEXT	与TEXT相同，但最大长度为16K
LONGTEXT	与TEXT相同，但最大长度为4GB
SET	接受最多64个串组成的一个预定义集合的零个或多个串
ENUM	接受最多64K个串组成的一个预定义集合的某个串

不管使用何种形式的串数据类型，串值都必须括在引号内，通常单引号更好。

2. 数值数据类型

MySQL支持多种数值数据类型，每种存储的数值具有不同的取值范围。支持取值范围越大的数据类型会消耗更多的存储空间。此外数据类型也分只支持保存整数的类型以及只支持浮点数（小数）的类型

所有数值数据类型（除BIT和BOOLEAN外）都可以以有符号和无符号的形式存在。默认情况为有符号，但如果明确存储无符号类型可使用UNSIGNED关键字，此时正数存储范围会扩大一倍。

数值数据类型
数据类型	说明
BIT	位字段，1~64位
BOOLEAN（或BOOL）	布尔标志，或者为0或者为1，主要用于开/关标志（似乎在MySQL中布尔类型会自动变成TINYINT）
TINYINT	整数值，支持 -128~127（如果为UNSIGNED，为0~255）的数
SMALLINT	整数值，支持 -32768～32767（如果是UNSIGNED，为0～65535）的数
MEDIUMINT	整数值，支持 -8388608～8388607（如果是UNSIGNED，为0～16777215）的数
INT（或INTEGER）	整数值，支持 -2147483648～2147483647（如果是UNSIGNED，为0～4294967295）的数
BIGINT	整数值，支持 -9223372036854775808～9223372036854775807（如果是UNSIGNED，为0～18446744073709551615）的数
FLOAT	单精度浮点值
DOUBLE	双精度浮点值
DECIMAL（或DEC）	精度可变的浮点值
REAL	4字节的浮点值

与串不一样，数值数据类型不应该出现在引号内。

3. 日期和时间数据类型

日期和时间数据类型
数据类型	说明
DATE	表示1000-01-01～9999-12-31的日期，格式为YYYY-MM-DD
TIME	表示一天内的时分秒时间，格式为HH:MM:SS
DATETIME	DATE和TIME的组合
TIMESTAMP	存储时间戳，功能和DATETIME相同（但范围较小）
YEAR	用2位数字表示，范围是70（1970年）～69（2069年），用4位数字表示，范围是1901年～2155年

4. 二进制数据类型

二进制数据类型可存储任何数据（甚至包括二进制信息），如图像、多媒体、字处理文档等。

二进制数据类型
数据类型	说明
BOLB	Blob最大长度为64 KB
TINYBLOB	Blob最大长度为255字节
MEDIUMBLOB	Blob最大长度为16 MB
LONGBLOB	Blob最大长度为4 GB

二、基础使用代码实例

-- 1. 创建数据库test，使用utf8编码，使用utf8大小写不敏感校验集。
create database if not exists test default charset utf8 collate utf8_general_ci;

-- 2. 创建数据表
create table `tb1` (
	`id` int not null auto_increment primary key,
	`name` varchar(20) not null,
	`gender` varchar(4),
	`sid` char(10) unique,
	check (`gender` in ('男', '女') and sid > 0)
);

-- 3. 外键约束
create table `tb2` (
	`kid` int,
	`fid` int,
	primary key (`kid`),
	foreign key (`fid`) references tb1(id)
);

-- 4. 多主键，外键约束
create table `tb3` (
	`kid1` int,
	`kid2` int,
	`fid1` int,
	`fid2` int,
	primary key (kid1, kid2),
	-- 可命名约束 constraint pk_id primary key (kid1, kid2)
	foreign key (`fid1`) references tb1(id),
	foreign key (`fid2`) references tb2(kid)
);

-- 5. mysql 没有bool类型
create table `tb4` (
	`id` int primary key,
	`ok` boolean
);

---------------
-- 1. 
drop database if exists school;
create database school;
use school;
create table student(
	sid int auto_increment primary key,
	name varchar(20) not null unique,
	gender varchar(4) not null,
	dept varchar(20),
	check (gender in ("男", "女") and age > 0)
);
create table course(
	cid int auto_increment primary key,
	name varchar(20) not null unique,
	prev int,
	credit int
);
create table sc(
	sid int,
	cid int,
	grade int,
	primary key (sid, cid),
	foreign key (sid) references student(sid),
	foreign key (cid) references course(cid)
);
insert into student values(2019002, 'ab', '男', 'IOT');
insert into student values(2019001, 'bc', '男', 'CS');
insert into student values(2018001, 'cd', '男', 'CS');
insert into student values(2018002, 'de', '男', 'LE');
insert into student values(2018003, 'ef', '男', 'EE');
insert into student values(2018004, 'fg', '男', 'EE');
insert into student values(2018005, 'gh', '女', 'CE');
insert into student values(2018006, 'hi', '男', 'EE');

insert into course values(1, '数据库', 5, 4);
insert into course values(2, '数学', NULl, 2);
insert into course values(3, '机器学习', 2, 4);
insert into course values(4, '操作系统', 5, 3);
insert into course values(5, '数据结构', 6, 4);
insert into course values(6, 'python', NULL, 2);
insert into course values(7, '中特', NULL, 6);
insert into course values(8, 'DB_MS', 1, 2);
insert into course values(9, 'DATA\\BASE', 1, 2);

insert into sc values(2018001, 1, 92);
insert into sc values(2018001, 2, 85);
insert into sc values(2018002, 6, 88);
insert into sc values(2018002, 7, NULL);
insert into sc values(2018003, 2, 90);
insert into sc values(2018004, 6, 95);
insert into sc values(2018005, 4, 82);
insert into sc values(2018005, 7, 92);
---------------

-- 6. 基本查询
-- 查询所有信息
select * from student;
-- select中含有 +,-,*,/算术
select sid, cid, grade*2 from sc; 
-- distinct all
select sid from sc;
select all sid from sc;
select distinct sid from sc;
-- count
-- 统计有多少条选课记录
select count(sid) as 选课名单数 from sc;
-- 统计有多少个人选课
select count(distinct(sid)) as 选课人数 from sc;
-- =,>,<,>=,!=,<>, not
select * from sc where grade > 90;
select * from sc where not grade > 90;
select * from sc where grade <> 92; -- 因为NULL，进行=,!=返回值都是false
select * from sc where grade is null or grade != 92;
-- between x and y, not between x and y  上下界都包括
select * from sc where grade between 80 and 90;
-- 统计 cs和ee系的学生
select * from student where dept in ('cs', 'ee');
-- binary 强调大小写
select * from student where binary dept in ('cs', 'ee');
-- like, not like  %通配多位 _通配1位  escape \
-- 2018开头的学号
select * from student where sid like '2018%';
-- 名字中有`数据`
select * from course where name like '%数据%';
-- 三个字且以数开头
select * from course where name like '数据_';
-- 课程名中有_
select * from course where name like '%\_%';
-- 课程名中有\
select * from course where name like '%\\\\%';
-- count, sum, avg, max, min 除count都跳过空值
-- 统计所有课程得分的均分
select avg(grade) from sc;
-- where中不能出现聚合函数
-- select * from sc where max(grade)>80;
-- group by
-- 统计每个人的均分
select sid,avg(grade) from sc group by sid; -- 想想这里能不能加cid，为什么
-- 统计选过两门及以上课程的学生
select cid from sc group by cid having count(*)>=2;
-- 选课课程号为2的学生按成绩降序，学号升序排列
select sid,grade from sc where cid='2' order by grade desc, sid asc;

-- 连接查询
-- 所有选课信息
select * from student,sc where student.sid=sc.sid;
-- 先修课信息
select f.name as 课程名, s.name as 先修课 from course f, course s where f.prev = s.cid;

-- 统计cd的课程号为7的课程得分
select grade from sc, student where student.sid = sc.sid and student.name="cd" and cid=7;
select grade from sc inner join student on sc.sid = student.sid where student.name="cd" and cid=7;

-- 内外连接区别  --外连接会列出不满足条件的元组
select * from sc inner join student on sc.sid = student.sid;
select * from sc left outer join student on sc.sid = student.sid;
select * from sc right outer join student on sc.sid = student.sid;

-- 嵌套查询
-- 选了中特的学生
select * from student, sc, course where student.sid=sc.sid and sc.cid=course.cid and course.name="中特";
select * from student where sid in (
	select sid from sc inner join course on sc.cid = course.cid
	where course.name="中特"
);

-- all some any exists
-- 均分最高的学生
select sid, avg(grade) from sc group by sid order by avg(grade) desc limit 0,1;
select sid, avg(grade) from sc group by sid having avg(grade) >= all (
	select avg(grade) from sc group by sid
);


-- 7. 插入数据
insert into student (name, gender, dept) values('小明', '男', 'CS');
-- 一次插入多条数据除了分开写多条 如果列相同可以简写成(并且这种方式效率会比前者更高)
insert into student (name, gender, dept) values('小明', '男', 'CS'), values('小刚', '男', 'EE');
-- 需要手动create table(该语句不会自动创建)
insert into csstudent select * from student where dept='cs';
-- 降低insert的优先级别 因为insert操作可能比较耗时而且优先级别可能不如select操作
-- low_priority 同样适用于update和delete
insert low_priority inot ...

-- 8. 修改数据
update student set dept='CS' where name='fg';
-- 同时修改多个值
update student set dept='EE', gender='女' where name='fg'
-- 不能在同一个表同时查询修改
-- update sc set grade=(select avg(grade) from sc where cid = '7');
-- update操作是一组完整的事务 要么都成功要么都不成功
-- 如果在更新的过程中一行或者多行出现错误 则整个update操作会被退回
-- 如果希望发生错误也继续执行 可使用IGNORE关键字
update IGNORE sc...
-- 删除某个值 可将其设置为NULL(如果该列可以为NULL)
update sc set dept=NULL where name='fg';

-- 9. 删除数据
delete from student where name='小明'; -- 有外键约束删不了
delete from sc where grade is null;
-- 如果希望从表中删除所有行 不要使用delete操作 可以使用TRUNCATE TABLE语句 
-- 它完成相同的工作 但速度更快(TRUNCATE实际是删除原来的表并重建一个表)

-- 一些UPDATE和DELETE应该遵循的习惯
-- 1. 除非确实打算更新和删除每一行 否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句
-- 2. 保证每个表都有主键 尽可能像WHERE子句那样使用它(可以指定各主键,多个值或值的范围)
-- 3. 在对UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进行测试
--    保证它过滤的是正确的记录 以防编写的WHERE子句不正确
-- 4. 使用强制实施引用完整性的数据库 这样MySQL将不允许删除具有与其他表相关联的数据的行

-- 10. 修改表结构
-- update是用来更新表中数据的关键字 而alter是用来修改数据表的结构的关键字
-- 在student表中添加一列联系方式 添加时必须说明数据类型
alter table student add phone char(20);
-- 删除一列
alter table student drop column phone;
-- 定义外键
alter table table1_name add constraint fk_name foreign key (talbl1_column_name) references table2_name (table2_colummn_name)
-- 重命名表
rename table table_name1 to table_name2;

-- 11. 简单存储过程
-- MySQL支持IN(传递给存储过程)、OUT(从存储过程传出，如这里所用)
-- 和INOUT(对存储过程传入和传出)类型的参数

-- 定义存储过程
DELIMITER //
CREATE PROCEDURE demo(IN cnt int)
BEGIN
	select * from sc order by grade desc limit 0, cnt;
END //
DELIMITER ;

-- 调用存储过程
call demo(2);

-- 定义存储过程 携带输出参数
DELIMITER //
CREATE PROCEDURE demo2(IN c_num INT, OUT max_grade INT)
BEGIN
	SELECT MAX(grade) into max_grade from sc where cid=c_num;
END //
DELIMITER ;

-- 所有MySQL变量都必须以@开始
call demo2(2, @mgrade);
select @mgrade;

-- 删除存储过程 如果指定的存储过程不存在 的drop procedure会产生一个错误
-- 可以使用 if exists 进行判断防止产生错误
drop procedure demo;
drop procedure if exists demo;

-- 12. 简单触发器 
-- NEW和OLD记录了触发触发器那一行的数据
DELIMITER //
CREATE TRIGGER trig BEFORE DELETE
ON student for EACH ROW
BEGIN
	DELETE FROM sc where sid = OLD.sid;
END //
DELIMITER ;

delete from student where name='hi';
call demo(-1);