MySQL 1-4Chpters

CH01 : MySQL服务与MySQL登录

1.1 连接 断开 登录

net start mysql--连接
net stop mysql--断开
mysql -u root -p--登录

然后输入密码：（自己设置的）出现这样的界面就表示你已经进入MySQL啦

1.2 数据库信息的查看与指令

输入 \h 显示帮助信息

CH02 : 数据库和表的基本操作

2.1 创建和查看数据库

1.创建/删除/查看数据库

create database 数据库名称;
drop database 数据库名称;
show create database 数据库名称;

create database db_demo;//创建数据库
drop database db_demo;//删除数据库
show create database db_demo;//查看数据库

2.修改数据库信息

现在db_demo的编码方式为latin1 我们可以通过

alter database 数据库名称 default character set 编码方式 collate 编码方式_bin;

alter database db_demo default character set gbk collate gbk_bin;

先修改数据库的编码方式再查看一下数据库

2.2 数据类型

数据类型包括：整数类型、浮点数类型、定点数类型、日期和时间类型、字符串类型和二进制类型。

1.整数类型

数据类型	字节数	无符号数的取值范围	有符号数的取值范围
TINYINT	1	0~255	-128~127
SMALLINT	2	0~65 535	-32 768~32 767
MEDIUMINT	3	0~16 777 215	-8 388 608~8 388 607
INT	4	0~4 294 967 295	-2 147 483 648~2 147 483 647
BIGINT	5	0~18 446 744 073 709 551 615	-9 223 372 036 854 775 808~9 223 372 036 854 775 807

2.浮点数类型和定点数类型

3.日期和时间类型

4字符串和二进制类型

数据类型	类型说明
CHAR	用于表示固定长度的字符串
VARCHAR	用于表示可变长度的字符串
BINARY	用于表示固定长度的二进制数据
VARBINARY	用于表示可变长度的二进制数据
BLOB	用于表示二进制大数据
TEXT	用于表示大文本数据
ENUM	表示枚举类型 只能存储一个枚举字符串值
SET	表示字符串对象 可以有零或多个值
BIT	表示位字段类型

2.3数据表的基本操作

1.创建数据表

create table 表名(
    字段名 1,数据类型[完整性约束条件],
    字段名 2,数据类型[完整性约束条件],
    …
    字段名 3,数据类型[完整性约束条件]
);

create table tb_grade(
	id int(11),
	name varchar(20),
	grade float
);

然后输入show tables;查看是否创建成功

2.查看数据表（show create table/describe）

①使用 show create table 语句查看数据表

show create table 表名;

show create table tb_grade;

输入show create table tb_grade\G可以使上面的更加美观

②使用 describe 语句查看数据表

describe 表名; //可以简写成 desc 表名;

describe tb_grade;

3.修改数据表

①修改表名

alter table 旧表名 rename [to] 新表名;

alter table tb_grade rename grade;

②修改字段名

alter table 表名 change 旧字段名 新字段名 新数据类型;

alter table grade change name username varchar(20);

③修改字段的数据类型

alter table 表名 modify 字段名 数据类型;

alter table grade modify id int(20);

④添加字段

alter table 表名 add 新字段名 数据类型
    [ 约束条件 ] [ first | after 已存在字段名 ]

alter table grade add age int(10);

⑤删除字段

alter table 表名 drop 字段名;

alter table grade drop age;

⑥修改字段的排列位置

alter table 表名 modify 字段名 1 数据类型 first | after 字段名2;
//“字段名1”是指修改位置的字段 “数据类型”是字段1的数据类型 “first”为可选参数 指的是将字段1修改为表的第一个字段 “after 字段名2 ”是将字段1插入到字段名2的后面

alter table grade modify username varchar(20) first;

4.删除数据表

drop table 表名;

drop table grade;

2.4 表的约束

约束条件	说明
PRIMARY KEY	主键约束 用于唯一标识对应的记录
FOREIGN KEY	外键约束
NOT NULL	非空约束
UNIQUE	唯一性约束
DEFAULT	默认值约束 用于设置字段的默认值

1.主键约束

① 单字段主键

字段名 数据类型 primary key;

create table example01(
	id int primary key,
	name varchar(20),
	grade float
);

② 多字段主键

primary key(字段名1，字段名2,…字段名n)

create table example02(
	std_id int,
	course_id int,
	grade float,
	primary key(std_id,course_id)
);

TIPS:每个数据表中最多只能有一个主键约束 定义为primary key的字段不能有重复且不能为NULL

2.非空约束

字段名 数据类型 not null;

create table example03(
	id int primary key,
	name varchar(20) not null,
	grade float
);

3.唯一约束

字段名 数据类型 unique;

create table example04(
	id int primary key,
	stu_id int unique,
	name varchar(20) not null,
);

4.默认约束

字段名 数据类型 default 默认值;

create table example05(
	id int primary key auto_increment,
	stu_id int unique,
	grade float default 0
);

2.5 设置表的字段值自动增加

字段名 数据类型 auto_increment;

create table example06(
	id int primary key auto_increment,
	stu_id int unique,
	grade float default 0
);

CH03:添加、更新与删除数据

3.1 添加数据

3.1.1 为表中所有字段添加数据

①insert 语句中指定所有字段名

在此之前 先建立一个

insert into 表名 (字段名1,字段名2,···) values(值1,值2,···)

insert into student(id,name,grade) values(1,'zhangsan',98.5);

insert into student(name,grade,id) values('lisi',95,2);

② insert语句中不指定字段名

insert into 表名 values( 值1, 值2,…);

insert into student values(3,'wangwu',61.5);

3.1.2 为表的指定字段添加数据

insert into 表名(字段1,字段2,…) values(值1,值2,…)

insert into student(id,name) values(4,'zhaoliu');
insert into student(id,grade) values(5,97);//不成功 提示name字段不能为空
insert into student(grade,name) values(55,'sunbin');

insert 语句的其他写法

insert into 表名 set 字段名1=值1[,字段名2=值2,…]

insert into student set id=5,name='boya',grade=99;

3.1.3 同时添加多条记录

insert into 表名[(字段名1,字段名2,…)]
  values(值1,值2,…),(值1,值2,…),
  …
  (值1,值2);

insert into student values
	(6,'lilei',99),
	(7,'hanmeimei',100),
	(8,'poly',40.5);
insert into student (id,name) values(9,'liubei'),(10,'guanyu'),(11,'zhangfei');

3.2 更新数据

update 表名 set 字段名1=值1[,字段名2=值2,…] [where 条件表达式]

update更新 （部分/全部）数据

--部分
update student set name='caocao',grade=50 where id=1;--修改id=1的记录
update student set grade=100 where id<4;--将id<4的记录的grade值更新为100
--全部
update student set grade=80;--所有记录的grade都更新为80

3.3 删除数据

delete from 表名 [where 条件表达式]

delete 删除（部分 / 全部）数据

--部分
delete from student where id=11;
delete from student where id=11;//再次删除 显示Empty set
delete from student where id>5;//删除 id>5 的记录
--全部
delete from student;//删除全部数据

多学一招 使用关键词truncate删除表中数据

truncate [table] 表名

truncate table student;

Tips:truncate & delete

（1）delete是DML语句，truncate是DDL语句
（2）delete后面可以跟where子句，通过指定 where子句中的条件表达式只删除满足条件的部分记录，而truncate只能删除表中所有记录。
（3）使用truncate语句删除表中数据之后，再次向表中添加记录时，自动增加字段的默认初始值重新从1开始，而使用delete语句则继续从删除时该字段的最大值加1

CH04:单表查询

4.1 简单查询

4.1.1 select语句

select [distinct] * | 字段名 1 ,字段名 2 ,字段名 3 ,…
    from 表名
    [where条件表达式1]
    [group by 字段名 [having 条件表达式2]]
    [order by 字段名 [asc|desc]]
    [limit [offset] 记录数]

（1）select[distinct] * | 字段名1,字段名2,字段名3,…

(*)星号通配符表示表中所有字段与“字段名1,字段名2,字段名3,…”为互斥关系，任选其一。“distinct”可选，用于剔除查询结果中的重复数据。

（2）from 表名

表示从指定的表中查询数据

（3）where 条件表达式1

可选参数，用于指定查询条件

（4）group by 字段名 [having 条件表达式2]

group by是可选参数，用于将查询结果按照指定字段进行分组， having 也是可选参数，用于对分组后的结果进行过滤

（5）order by 字段名 [asc|desc]

order by 是可选参数 用于将查询结果按照指定字段进行排序，排序方式有asc和desc控制，asc升序，desc降序，若不指定，则默认升序排列

（6）limit [offset] 记录数

limit 可选参数 用于限制查询结果的数量，第一个参数offset表示偏移量，如果偏移量为0，则从查询结果的第一条开始，偏移量为1则从查询结果的第二条记录开始 offset可选 默认0，第二个参数“记录数”表示返回查询记录的条数

4.1.2 查询(所有/指定)字段

//查询所有字段
select 字段名1,字段名2,…from 表名; //字段名之间可以互相交换
select * from 表名; //使用（*）通配符
//查询指定字段
select 字段名1,字段名2,…from 表名;//此处的字段名为指定的字段名

--查询所有字段
select id,name,grade from student;
select * from student;
--查询指定字段
select name,grade from student;

4.2 按条件查询

4.2.1 带关系运算符的查询

select 字段名1,字段名2, … from 表名 where 条件表达式;

select id, name from student where id=4;//查询id=4的同学的姓名
select id, grade from student where id=4;//查询id=4的同学的成绩
select name grade from student where grade>80;//查询grade>80的同学的姓名

4.2.2 带in关键词的查询

select * | 字段名1,字段名2,… from 表名 where 字段名 [not] in (元素1,元素2,…)

select id, grade, name from student where id in (1,2,3);//查询id in 1,2,3 中
select id, grade, name from student where id not in (1,2,3);//查询id not in 1,2,3 中

4.2.3 带between and关键词的查询

select * | 字段名1,字段名2,… from 表名 where 字段名 [not] between 值1 and 值2;

select id, name from student where id between 2 and 5;//查询id 2~5（左闭右闭）
select id, name from student where id not between 2 and 5;//与上面相反

4.2.4 空值查询

select * | 字段名1,字段名2,… from 表名 where 字段名 is [not] null

select id,name,grade from student where id is null;
select id,name,grade from student where id is not null;

4.2.5 带distinct关键字查询(可以去重)

select distinct 字段名 from 表名;

select distinct id from student;

distinct 关键字作用于多个字段

select distinct 字段名1,字段名2,… from 表名;

select distinct name, grade from student;
--只会显示name 和 grade 同时不相同的记录 有一个相同照样显示。

4.2.6 带like关键字的查询（’%’ , ‘_’ 通配符）

select * | {字段名1,字段名2,…} from 表名 where 字段名 [ not ] like ‘匹配字符串’;
//可以是（%）和（_）统配字符串

1.百分号%通配符

通配符	查询
%e	ate,apple
c%	cut,cat,count
s%g	sing,song,saying
%e%	ex,apple,student

2.下划线_通配符

通配符	查询
a_e	ate,ave
ca_	cat,cab
s_ _g	song,sing

Tips:如果要匹配字符串中的百分号和下划线，则在前加上‘\’进行转义

select name from student where name like "s%";
select name from student where name like "w__ong";
select name from student where name not like "s%";

4.2.7 带 and / or 关键字的多条件查询

select * | {字段名1,字段名2,…} from 表名 where 条件表达式1 and 条件表达式2 [… and 条件表达式n ];
select * | {字段名1,字段名2,…} from 表名 where 条件表达式1 or 条件表达式2 [… or条件表达式n ];

select id, name, grade from student where id<5 and grade>60;
select id, name, grade from student where id in (1,2,3,4,5) and name like '%g' and grade>60;
select id, name, grade from student where name like '_____g' or grade>60;

--or 和 and 一起使用
select name,grade,gender from student where gender="女" or gender="男" and grade=100;
-- and的优先级大于or的优先级 先执行and操作 再执行or操作

4.3 高级查询

4.3.1 聚合函数

函数名称	作用
count()	返回某列的行数
sum()	返回某列值的和
avg()	返回某列的平均值
max()	返回某列的最大值
min()	返回某列的最小值

//count()
select count(*) from 表名;
//sum()
select sum(字段名) from 表名;
//avg()
select avg(字段名) from 表名;
//max()
select max(字段名) from 表名;
//min()
select min(字段名) from 表名;

select count(*) from student;
select sum(grade) from student;
select avg(grade) from student;
select max(grade) from student;
select min(grade) from student;

4.3.2 对查询结果排序

select 字段名1, 字段名2,… from 表名 order by 字段名1 [asc|desc] , 字段名2 [asc|desc]…

select * from student grade asc;//grade 按照升序排列
select * from student gender asc,grade desc;//先按照gender升序 gender相同的 按照grade降序排列 注意NULL默认为最小值

4.3.3 分组查询

select 字段名1,字段名2,… from 表名 group by 字段名1,字段名2,…[having 条件表达式];

1. 单独使用group by 分组

select * from student group by gender;

select * from student group by gender;
--每个分组只显示一条记录 意义并不大 group by一般与聚合函数一起使用

2.group by 与 聚合函数一起使用

select count(*),gender from student group by gender;--返回gender每个的个数
--	count(*)  | gender
--	----------------
--		1	  | NULL
-- 	 	2     | 女
--  	5     | 男

3. group by 和 having 关键字一起使用

select sum(grade), gender from student group by gender having sum(grade)<300;
--sum(grade)	| gender
------------------------
--	90			| NULL
--	154			| 女

4.3.4 使用 limit 限制查询结果的个数

select 字段名1,字段名2,… from 表名 limit [offset,] 记录数

select * from student limit 4;--查询student表中的前四条记录
select * from student order by grade desc limit 4,10;
--将student表中的记录按照grade降序排序 显示第5位~第5+10位的记录

4.3.5 常用函数

函数	作用
abs(x)	return a>0?a:-a
mod(x,y)	return x%y
sign(x)	符号 return 1,0,-1
length(str)	return len(str)
concat(a1,a2…)	return a1+a2+…
md5(str)	return md5(str)
encode(str,pwd_str)	使用pwd作为密码加密str
decode(str,pwd_str)	使用pwd作为密码解密str

4.4 为表和字段取别名

4.4.1 为表取别名

select * from 表名 [as] 别名;

select * from student as s where s.gender = "女";
--student的别名为s s.gender是指student表上的gender字段

4.4.2 为字段取别名

select 字段名 [as] 别名 [,字段名, [as] 别名, …] from 表名;

select name as stu_name,gender stu_gender from student;