学习笔记：SQL语句-数据库、数据表操作(单表)

一、数据库操作

1.连接数据库

mysql [-h 服务器地址  -P 端口号] -u用户名 -p密码

如果是本机运行 服务器地址为127.0.0.0 端口号一般为3306这种情况可简写为

mysql -uroot -p

输入上述命令在输入数据库密码就好了。

注：可能遇到的错误

ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)  

表示数据库服务停止了 打开服务把mysql重新启动就好了

ERROR 1045 (28000): Access denied for user'root'@'localhost' (using password: YES)  

表示你的用户名和密码不对。

2.创建数据库

create database if not exists 数据库名;

if not exists可以不写 他主要是为了判断你创建这个数据库在这之前有没有另一个同名的。

3.查询数据库

show databases;

4.删除数据库

drop database 数据库名;

5.使用数据库

use 数据库名;

二、数据表操作

1.创建数据表

create table 表名(    
    字段名1  字段类型1(字段长度) [ comment  字段1注释 ],  
    字段名2  字段类型2(字段长度) [ comment  字段2注释 ],   
    ....    
    字段名n  字段类型n(字段长度) [ comment  字段n注释 ]
) [ comment  表注释 ];

例如

create table student(
     id int comment '学号',
     name varchar(30) comment '姓名',
     gender char(1) comment '性别', -- 性别,长度固定,采用char更合适
     age tinyint unsigned comment '年龄', -- 年龄,采用无符号的tinyint更合适
     birthday date comment '生日'
) comment '学生表';

对于数据库的数据类型，整理了一些常用的类型

分类	类型	描述
数值类型	tinyint	小整数，可用于年龄。
	int	大整数，常用。
	bigint	大整数
	float	浮点数类型
	double	浮点数类型
字符类型	varchar()	可变长度字符串，如果插入的长度小于定义长度时，插入多长就存多长
	char()	固定长度字符串，如果插入的长度小于定义长度，则可以用空格进行填充
日期类型	date	日期，格式：yyyy-MM-dd
	datetime	日期时间，格式：yyyy-MM-dd HH:mm:ss

数据约束：

约束用于对表中的数据进行进一步的限制，一般作用在表中的字段上，用于保证数据的正确性。

约束种类有：主键约束、唯一约束、非空约束、默认值、外键约束。

约束	描述	关键字
主键约束	主键是一行数据的唯一标识，要求非空且唯一	primary key(auto increment自增)
非空约束	限制该字段值不能为null	not null
唯一约束	保证字段的所有数据都是唯一、不重复的	unique
默认约束	保存数据时，如果未指定该字段值，则采用默认值	default
外键约束	让两张表的数据建立连接，保证数据的一致性和完整性	foreign key

那么怎么使用呢 例如

create table student(
     id int primary key comment '学号',
     name varchar(30) unique comment '姓名',
     gender char(1) default '男' comment '性别',
     age tinyint unsigned not null comment '年龄',
     birthday date comment '生日'
) comment '学生表';

2.查询数据表

查询当前数据库所有表：show tables

查询表结构：desc 表名

查询建表语句：show create table 表名

例如

-- 1. 查看当前库中的所有数据表
show tables;

-- 2. 查看student表的表结构
desc student;

-- 3. 查看student表的建表语句
show create table student;

3.修改数据表

添加字段：alter table 表名 add 字段名 类型(长度)

修改字段类型：alter table 表名 modify 字段名 新数据类型(长度)

修改字段名和字段类型：alter table 表名 change 旧字段名 新字段名 类型 (长度)

删除字段：alter table 表名 drop column 字段名

修改表名： rename table 表名 to 新表

例如

-- 1. 修改student表, 添加一列description 变长字符串类型，长度30
alter table student add description varchar(30);

-- 2. 修改student表 description列为定长字符串类型，长度40
alter table student modify description char(40);

-- 3. 修改student表 description列名为descr，变长字符串类型，长度20
alter table student change description descr varchar(20);

-- 4. 删除student表的descr列
alter table student drop column descr;

-- 5. 修改student表的名称为stu
rename table student to stu;

4.删除数据表

删除表：drop table [ if exists ] 表名

-- 删除stu表
drop table stu;

三、数据的增删改查

1.插入数据

指定字段添加数据：insert into 表名 (字段名1, 字段名2) values (值1, 值2)

全部字段添加数据：insert into 表名 values (值1, 值2, ...)

批量添加数据（指定字段）：insert into 表名 (字段名1, 字段名2) values (值1, 值2), (值1, 值2)

批量添加数据（全部字段）：insert into 表名 values (值1, 值2, ...), (值1, 值2, ...)

-- 1. 为 student 表的 name, gender, age, birthday 字段插入值
insert into student(name,gender,age,birthday) values ('张三','男',18,'2000-01-01');

-- 2. 为 student 表的所有字段插入值
insert into student values (null,'李四','男',20,'2011-01-01');

-- 3. 批量为 student 表的 name, gender, age, birthday 字段插入值
insert into student(name,gender,age,birthday) 
values ('jack','男',18,'2000-01-01'),
       ('lisa','女',18,'2002-01-01');

-- 4. 批量为 student 表的所有字段插入值
insert into student 
values (null,'王五','男',20,'2000-01-01'),
       (null,'赵六','男',20,'2001-01-01');


-- 注意    
	1. 插入数据时，指定的字段顺序需要与值的顺序是一一对应的
	2. 字符串和日期型数据应该包含在引号中

2.删除数据

删除数据：delete from 表名 [ where 条件 ]

-- 1. 删除 student 表中ID为1的学生
delete from student where id = 1;

-- 2. 删除 student 表中的所有学生
delete from student;

-- 注意
	删除语句中如果不加条件，则将所有数据都会被删除！	

3.修改数据

修改数据：update 表名 set 字段名1 = 值1 , 字段名2 = 值2 , .... [ where 条件 ]

--将 student 表的ID为1的学生 姓名name字段更新为'一一'
update student set name = '一一' where id = 1;

4.查询数据

 (1)基本查询

查询指定字段：select 字段1, 字段2, 字段3 from 表名

查询所有字段：select * from 表名

设置别名：select 字段1 [ as 别名1 ] , 字段2 [ as 别名2 ] from 表名

去除重复记录：select distinct 字段列表 from 表名

-- 1. 查询指定字段 name,age 并返回
select name,age from student;

-- 2. 查询返回所有字段
select * from student;

-- 3. 查询所有学生的 name,age, 并起别名(姓名、年龄)  --- as 关键字可以省略
select name as '姓名' ,entrydate as '年龄' from student;
select name  '姓名' ,entrydate '年龄' from student;

-- 4. 查询学生有哪几种年龄(不要重复)
select distinct age from student;

（2）条件查询

条件查询：select 字段列表 from 表名 where 条件列表

比较运算符	功能
>	大于
>=	大于等于
<	小于
<=	小于等于
==	等于
<> 或 !=	不等于
is null	是null
between ... and ...	在某个范围之内(含最小、最大值)
in(...)	在in之后的列表中的值，多选一
like 占位符	模糊匹配(_匹配单个字符, %匹配任意个字符)

逻辑运算符	功能
and	并且 (多个条件同时成立)
or	或者 (多个条件任意一个成立)
!	非 , 不是

--1. 查询 姓名 为 张三 的学生
select * from student where name = '张三';

--2. 查询在 id小于等于5 的学生信息
select * from student where id <=5;

--3. 查询 id<=5 并且 age=20  的学生信息
select * from student where id <=5 and age = 20;

--4. 查询生日 在 '2000-01-01' (包含) 到 '2010-01-01'(包含) 之间的学生信息
select * from student where birthday between '2000-01-01' and '2010-01-01';

--5 查询年龄是 18, 19, 20 的学生信息
select * from student where age = 18 or age = 19 or age = 20;
select * from student where age in (18,19,20);

--6. 查询姓 '张' 的学生信息
select * from student where name like '张%';

--7. 查询姓名中包含 '三' 的学生信息
select * from student where name like '%三%';

--8. 查询姓'张'，并且姓名为三个字的学生信息
select * from student where name like '张__';

四、聚合函数

聚合函数: 将一列数据作为一个整体，进行纵向计算，语法为: select 聚合函数(字段名) from 表名

函数	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

-- 1. 统计该班级学生数量
select count(*) from student;
select count(id) from student; -- 不包含空值的列
select count(1) from student; -- 任意数字

-- 2. 统计该班级出生最早的学生
select min(birthday) from student;

-- 3. 统计该班级出生最晚的学生
select max(birthday) from student;

-- 4. 统计该班级学生年龄的平均值
select avg(age) from student;

-- 5. 统计该班级学生的ID之和
select sum(id) from student;

五、分组过滤

分组过滤： select 分组字段，聚合函数() from 表名  group by 分组字段名  having 分组后过滤条件

-- 1. 根据性别分组， 统计、男生和女生的数量
select gender,count(*) from student group by gender;


-- 2. 先查询出生时间在 '2015-01-01' (包含) 以后的学生，并对结果根据性别分组
select gender,count(*) from student where birthday >= '2015-01-01' group by gender;

-- 3. 先查询出生时间在 '2015-01-01' (包含) 以后的学生，并对结果根据性别分组，获取数量大于等于2的性别
select gender,count(*) from student where birthday >= '2015-01-01' group by gender having count(*) > 2;

六、排序

排序: select 字段列表 from 表名 order by 字段1 排序方式1，字段2 排序方式2

ASC：升序（默认值）

DESC：降序

-- 1. 根据出生时间，对学生进行降序排序
select * from student order by birthday desc;

-- 2. 根据出生时间，对学生进行升序排序
select * from student order by birthday asc;

-- 3. 根据出生时间对学生进行升序排序，出生时间相同再按照ID进行降序排序
select * from student order by birthday asc, id desc;

七、分页

分页: select 字段列表 from 表名 limit 起始索引, 查询记录数

注：起始索引 = （查询页码 - 1）* 每页显示记录数。比如要查第三页，那它的起始索引就是（3-1）*5=10

-- 1. 查询第1页学生数据, 每页展示5条记录
select * from student limit 0,5;

-- 2. 查询第2页学生数据, 每页展示5条记录
select * from student limit 5,5;

-- 3. 查询第3页学生数据, 每页展示5条记录
select * from student limit 10,5;

-- 4. 查询第4页学生数据, 每页展示5条记录
select * from student limit 15,5;

总结 

select 集合函数() from 表 [where 条件]  [group by 分组]  [having 过滤] [order by 排序] [limit 截取]

以上是我学习到的内容，有什么不对的欢迎指正！