MySQL总结

MySQL

一、基础语法

1、数据库服务的启动与登录

• DOS命令启动方式：net start mysql

2、控制台连接数据库：

• 登录格式1：

  mysql -u用户名 -p密码
  （u和p后面没有空格）
  
  mysql -u用户名 -p
  密码
  （p后面直接回车）
  

• 登录格式2：

  mysql -hip地址 -u用户名 -p密码
  例：mysql -h127.0.0.1 -uroot -proot
  

• 登录格式3：

  mysql --host=IP地址 --user=用户名 --password=密码
  例：mysql --host=127.0.0.1 --user=root --password=root
  

• 退出mysql：quit或者exit

3、SQL语句

• SQL：结构化查询语句
• 是一种所有关系型数据库的查询规范，不同的数据库都支持。
• 通用的数据库操作语言，可以用在不同的数据库中。

4、SQL语句分类：

• DDL：数据定义语言（建库、建表）；
• DML：数据操纵语言（对表中的数据进行增删改）；
• DQL：数据查询语言（对表中的查询操作）；
• DCL：数据控制语音（对用户权限的设置）；

5、MySQL语法：

• 每一条语句以分号结尾，如果在SQLyog中是不必添加的。
• SQL中不区分大小写，关键字中认为大和小写是一样的。
• 三种注释：
  • –空格：单行注释
  • /**/：多行注释
  • #：这时mysql特有的注释方式

二、DDL操作数据库/操作表结构

1、创建数据库：

-- 创建数据库
create database 数据库名;

-- 判断数据库是否存在，不存在则创建数据库
create database if not exists 数据库名;

-- 创建数据库并指定字符集
create database 数据库名 character set 字符集;

2、查看数据库

-- 查看所有数据库
show database;

-- 查看某个数据库的创建信息
show create database 数据库名;

3、修改数据库

-- 修改数据库默认字符集
alter database 数据库名 default character set 字符集;

4、删除数据库

-- 删除数据库
drop database 数据库名;

5、使用数据库

-- 查看正在使用的数据库
select database();

-- 使用或者切换数据库
user 数据库名;

---

---

6、创建表

-- 创建表格式
create table 表名(
    字段1 字段类型1,
    字段2 字段类型2
);

-- 创建student表 包含id，name，birthday字段
create table student(
	id int,
    name varchar(20),
    birthday date
);

7、查看表

-- 查看某个数据库中的所有表
show tables;

-- 查看表结构
desc 表名;

-- 查看创建表的SQL语句
show create table 表名;

-- 查看student的创建表的SQL语句
show create table student;

-- 快速创建一个表结构相同的表
create table 表名1 like 表名2; 

8、删除表

-- 直接删除表
drop table 表名;

-- 判断是否存在，如果存在则删除表
drop table if exists 表名;

9、修改表结构

-- 添加表列
alter table 表名 add 列名 类型;

-- 为学生表添加一个新的字段remark，类型为varchar(20)
alter table student add remark varchar(20);

10、修改列类型MODIFY

alter table 表名 modify 列名 新类型;

-- 将student表中的remark字段改成varchar(100);
alter table student modify remark varchar(100);

11、修改列名change

alert table change 旧列名 新列名 类型;

-- 将student表中的remark字段名改成intro，类型varchar(30);
alter table student change remark intro varchar(30); 

12、删除列drop

alter table 表名 drop 列名;

-- 删除student表中的字段intro
alter table student drop intro;

13、修改表名

rename table 表名 to 新表名;

-- 将学生表改名为student2;
rename table student to student2;

14、修改字符集

alter table 表名 character set 字符集;

-- 将student2表的编码修改成gbk
alter table student2 character set gbk;

三、DML操作表中数据

1、插入记录

-- insert into 表名 字段名 values 字段值
insert into 表名:表示往哪一张表中添加数据
(字段名1，字段名2，...):表示要给哪些字段设置值
values (值1，值2...):设置具体的值

2、插入全部字段

-- 所有的字段都写出来
insert into 表名 (字段1，字段2...) values (值1，值2...);

-- 不写字段名
insert into 表名 values (值1，值2...);

3、插入部分数据

insert into 表名 (字段1，字段2) values (值1，值2);
-- 没有添加数据的字段会使用null

---

---

---

4、更新表记录

update 表名 set 列名=值 [where 条件表达式]

update表名：需要修改的表
set 列名=值：修改的列值
where：符合条件的记录才更新

-- 你可以同时更新一个或者多个字段；可以在where子句中指定任何条件。

-- 不带条件修改数据
update 表名 set 字段名=值;-- 修改所有的行

-- 带条件修改数据
update 表名 set 字段名=值 where 字段名=值;

-- 将所有性别改成女
updata student set sex='女';

-- 将ID为2的学生性别改为男
update student set sex='男' where id=2;

-- 吧ID为3的学生，年龄改为26，address改为北京
update student set age=26,address='北京' where id=3;

5、删除记录

delete from 表名 [where 条件表达式]
-- 注意：如果没有指定where子句，mysql表中的所有记录将被删除

-- 不带条件删除数据
delete from 表名;

-- 带条件删除数据
delete from 表名 where 字段名=值;

-- 使用truncate删除表中所有数据
truncate table 表名;

• truncate和delete的区别？

  truncate相当于删除表的结构，再创建一张表。

-- 删除id为1的记录
delete from student where id=1;

-- 删除表中所有数据
delete from student;

---

---

---

四、DQL查询表中数据

1、查询不会对数据库中的数据进行修改，只是一种显示数据的方式

select 列名 from 表名 where 条件表达式

-- select命令可以读取一行或者多行数据。
-- 你可以使用*来代替其他字段，select语句会返回表的所有字段的数据。
-- 你可以使用where语句来包含任何条件。

2、简单查询

-- 查询表所有的列和行的数据
select * from 表名;

-- 查询指定列
select 字段1,字段2,字段3,...from 表名;

3、指定列的别名进行查询

• 使用别名的好处：显示的时候使用新的名字，并不修改表的结构。

-- 对列指定别名
select 字段名1 as 别名，字段2 as 别名...from 表名;

-- 对列和表同时指定别名
select 字段名1 as 别名，字段2 as 别名...from 表名 as 别名;

-- 使用别名
select name as 姓名,age as 年龄 from student;

-- 表使用别名
select st.name as 姓名,age as 年龄 from student as st;

-- 使用别名的原因：用于多表查询操作

4、清除重复值

select distinct 字段名 from 表名;

-- 查询学生来自什么地方
select address from student;

-- 去掉重复记录
select distinct address from student;

5、查询结果参与运算

-- 某列数据和固定值运算
select 列名1+固定值 from 表名;

-- 某列数据和其他数据参与运算
select 列名1+列名2 from 表名;
-- 参与运算必须是数值类型

select * from student;

-- 给所有的数学加5分
select math+5 from student;
-- 查询math和English的和
select math+English from student;

6、条件查询

• 如果没有条件查询，则每一次查询所有的行。实际应用中，一般要指定查询条件面对记录进行顾虑。

-- 语法
select 字段名 from 表名 where 条件;
-- 流程：取出表中的每条数据，满足条件的记录就返回，不满足条件的记录不返回。

-- 查询math分数大于80的学生
select * from student where math>80;

-- 查询English分数小于80的学生
select * from student where English<80;

-- 查询age=20的学生
select * from student where age=20;

-- 查询age不等于20的学生
select * from student where age<>20;
select * from student where age!=20;

-- 查询age大于35且性别为男的学生
select * from student where age>35 and sex ='男';

-- 查询age大于35或性别为男的学生
select * from student where age>35 or sex ='男';

-- 查询id是1或3或5的学生
select * from student where id=3 or id=5 or id=1;

-- in关键字
select 字段名 from 表名 where 字段 in(数据1，数据2...);
-- in 里面的每个数据都会作为一次条件，只要满足条件的就会显示

-- 查询id是1或3或5的学生
select * from student where id in(1,3,5);

-- 查询id不是1或3或5的学生
select * from student where id not in(1,3,5);

-- 范围查询
between 值1 and 值2;
-- 表示从值1到值2范围，包头又包尾

-- 查询English成绩大于等于75且小于等于90的学生
select * from student where English between 75 and 90;

-- like关键字，表示模糊查询
select * from 表名 where 字段名 like 通配符字符串;
-- mysql通配符  %匹配任意多个字符串  _匹配一个字符

-- 查询姓马的学生
select * from student where name like '马%';

-- 查询姓名中包含'德'字的学生
select * from student where name like '%德%';

-- 查询姓马，且名字只有两个字的学生
select * from student where name like '马_';

---

---

---

7、排序

• 通过order by子句，可以将查询出的结果进行排序（排序只是显示方式，不会影响数据库中的数据的顺序）

select 字段名 from 表名 where 字段=2 order by 字段名 [ASC][DESC]
-- ASC是默认的升序/DESC是降序

-- 单列排序：只按某一字段进行排序

-- 查询所有数据，使用年龄降序排序
select * from student order by age desc;

-- 组合排序：同时对多个字段进行排序
select 字段名 from 表名 where 字段=值 order by 字段1[asc][desc],字段2[asc][desc];

-- 查询所有数据，在年龄降序的基础上，如果年龄相同再以数学成绩升序排序
select * from student order by age desc,math asc;

8、聚合函数

• max(列名)：求这一列的最大值
• min(列名)：求这一列的最小值
• avg(列名)：求这一列的平均值
• count(列名)：统计这一列有多少记录
• sum(列名)：对这一列求总和、

select 聚合函数(列名) from 表名;

-- 查询学生总数
select count(id) as 总人数 from student;
select count(*) as 总人数 from student;

-- 我们发现对于null的记录不会统计，如果我们需要把null统计进去，可以使用ifnull(列名，默认值)

-- 查询id字段，如果为null。使用0代替
select ifnull(id,0) from student;

-- 我们可以使用ifnull()函数，如果记录为null，就给个默认值，这样统计的数据就不会遗漏
select count(ifnull(id,0)) from student;

-- 查询年龄大于20的总数
select count(ifnull(id,0)) from student where age>20;

-- 查询数学成绩总分
select sum(math) 总分 from student;

-- 查询数学成绩平均分
select avg(math) 平均分 from student;

-- 查询数学成绩最高分
select max(math) 最高分 from student;

-- 查询数学成绩最低分
select min(math) 最低分 from student;

8、分组

• 分组查询是指使用group by 语句对查询信息进行分组，相同数据为一组

select 字段1，字段2 from 表名 group by 分组字段 [having 条件]

-- 按性别分组，求男生和女生数学的平均分
select sex,avg(math) from student group by sex;
-- 注意：当我们使用某个字段分组时，在查询的时候也需要将这个字段查询出来，否则看不到数据属于哪一组。

-- 查询男女各多少人，查询所有数据，按性别分组，统计每组人数
select sex count(*) from student group by sex;

-- 查询年龄大于25岁的人，按性别分组，统计每组人数

select sex count(*) from student where age>25 group by sex;

-- 查询年龄大于25岁的人，按性别分组，统计每组人数，并值只显示性别人数大于2的数据
select sex count(*) from student where age>25 group by sex having count(*)>2;

9、where和having的区别？

• where子句：对查询结果进行分组前，将不符合where条件的行去掉，即再分组前过滤数据，即先过滤再分组；where后面不可以使用聚合函数。
• having子句：是筛选满足条件的组，即在分组之后过滤数据，即先分组再过滤；having后面可以使用聚合函数。

10、limit语句：

• limit是限制的意思，所以limit的作用就是限制查询记录的条数。

  select * 字段列表 [as 别名] from 表名 [where子句][group by子句][having子句][order by子句][limit子句];
  

limit语法格式：limit offset,length;
-- offset：起始行数，从0开始计数，如果省略，默认就是0
-- length：返回的行数

-- 查询学生表中数据，从第三条开始显示，显示六条
select * from student limit 2,6;

• 使用场景：比如我们登陆京东和淘宝，返回商品信息可能有几万条，不是一次全部显示出来，是一页显示固定的条数。

-- 假如我们每页显示5条记录的方式来分页
select * from student limit 5;

五、数据库表的约束

1、约束的作用：

• 对标中数据进行限制，保证数据的正确性、有效性和完整性。一个表如果添加了约束，不正确的数据将无法插入到表中。约束在创建表的时候加比较合适。

2、约束的种类：

• 主键：primary key
• 唯一：unique
• 非空：not null
• 外键：foreign key
• 检查约束：check（mysql不支持）

3、主键约束

• 用来标识数据库中的每一条数据。
• 通常不用业务字段作为主键，单独给每一张表设计一个id的字段，把id作为主键。主键是给数据库和程序使用的，不是给最终的客户使用的，所以主键没有任何含义和关系，只要不重复，非空就行。
• 主键关键字：primary key
• 特点：唯一、非空

-- 创建主键的方式
-- 在创建表的时候给字段添加主键
字段名 字段类型 primary key
-- 在已有的表中添加主键
alter table 表名 add primary key(字段名);

-- 创建表学生表st5，包含字段(id,name,age)将id作为主键
create table st5(
	id int primary key,
    name varchar(20),
    age int
);

-- 删除主键
-- 删除st5表中的主键
alter table st5 drop primary key;

-- 添加主键
alter table st5 add primary key;

• 主键自增：主键如果让我们自己添加很有可能重复，我们通常希望在每次插入新纪录时，数据库自动生成主键字段的值。auto_increment表示自动增长（字段类型必须是整数类型）

-- 插入数据
insert into st(name,age) values('小乔'，18);
-- 另一种写法
insert into st(name，age) values(null,'周瑜',35);

-- 修改自增长的默认起始值
-- 创建表时指定起始值
create table st(
	id int primary key auto_increment,
    name varchar(20),
    age int
)auto_increment=1000;

-- 创建好以后修改起始值
alter table st auto_increment =2000;

• delete和truncate对自增长的影响
  • delete：删除所有记录之后，自增长没有影响
  • truncate：删除所有记录之后，自增长重新开始

4、唯一约束

• 就是表中某一列不能出现重复的值
• 基本格式：字段名 字段类型 unique