数据库概述SQL基本语法

基本概念

数据库DB

database简称DB: 存储数据的仓库，是以某种结构存储数据的文件。指长期保存在计算机的存储设备上，按照一定规则组织起来，可以被用户或应用共享的数据集合。

数据库管理系统DBMS

用于创建，维护，使用数据库的一种大型软件系统。比如MySQL, Oracle， SQL server等等。

使用

MySQL服务的开启

方式1：右键任务栏->任务管理器->服务->MySQL80->右键即可启动服务
方式2：CMD命令行操作：小黑窗里面输入：net start/stop MySQL80来启动或关闭服务。

MySQL的访问方式

方式1：外部访问命令 mysql -h主机名 -P端口号 -u用户名 -p密码
如果是访问本机的数据库，可以省略为mysql -u用户名 -p密码

方式2：MySQL安装自带的命令行
直接输入账号的密码即可登录，默认是主机是本机，默认端口号是3306，默认账号是root.

方式3：图形化窗口，Navicat, SQLyog, MySQLWorkBench等工具。

数据类型

数值类型:
int : tinyint、smallint、mediumint、int
decimal(5, 2): 表示范围为-999.99~999.99
字符串类型:
char： 定长字符串类型，默认长度为1
varchar(50)：变长字符串类型
text: 不支持默认值
日期类型:
datatime: 存储的时间不会随着时区变化。
timestamp:日期时间戳，到2038年就不能用了。
json类型

update products
set properties = '{
	"dimension" : [1,2,3],
    "weight" : 10,
    "manufacturer" : {"name" : "sony"}
}'
WHERE product_id = 1;

select 
	product_id, 
	json_extract(properties, '$.weight') as weight
from products
where product_id = 1;

SQL语言

分类

• DDL数据定义语言
• DML数据操作语言
• DQL数据查询语言
• DCL数据控制语言

定义库

-- 查询所有的库
show databases;
-- 创建库
create database db1;
-- 查询数据库的字符集
show create database;
-- 指定字符集和校对规则
create database mydb2 character set gbk;
-- 修改字符集
alter database mydb2 character set utf8;
-- 删除数据
drop database mydb2;
--切换库
use db1;

定义表

-- 创建表
create table student (id int, name varchar(50), age int, gender char);
-- 查看所有表
show tables;
show create table student; -- 查看完成的创建语句
-- 查看表结构
desc student;
-- 修改表名
alter table student rename to stu;
-- 修改字段名
alter table stu change id sid int;
-- 修改数据类型
alter table stu modify gender char(2);
-- 新增字段
alter table stu add score double(5, 2);
-- 删除字段, 这里不需要添加字段类型
alter table stu drop score;
-- 修改字段的顺序
alter table stu modify gender char(2) after name;
-- 删除表
drop table stu;

DML数据操作

• insert添加数据

-- 全字段插入
insert into emp 
value(101,'tom','男'，12000, '1999-02-23', 'boss');
-- 指定字段插入
insert into emp(id, name)
value(102, 'jack');
-- 批量插入
insert into emp(id, name) 
values(103, 'rose'),(104, 'pert');

• update更新数据

-- 将所有行的该字段数据修改为一个值
update emp set salary = salary + 15000;

--指定某一条数据
update emp set salary = 19000 
where name = 'jack';

update emp salary = 1000, gender='女'
where name = 'rose';

update emp set birthday = '2000-01-01'
where id = 104;

• delete删除数据

delete from emp where name = 'jack';

-- 删除全部数据，逐条删除，删除后可以恢复，主键还是继续累加的
delete from emp;

-- 删除表后，全新创建一个空的新表，删除后无法恢复
truncate emp;

delete和truncate的区别：

• 删除全部数据，逐条删除，删除后可以恢复，主键还是继续累加
• 删除表后，全新创建一个空的新表，删除后无法恢复

DQL数据查询语句

select 关键字的作用：用于运算，执行函数，查询数据。

• 基本查询

  • select * from emp;: 查询emp的所有数据
  • select name, salary from emp; 查询指定字段的数据
  • select distinct salary from emp; 去除重复数据
  • select name, salary + 1000 as new_salary from emp;起别名

• 多个条件复合查询

  • select * from emp where id = 1 or id = 2;
  • select * from emp where eid in (1, 3, 5);集合条件查询

• 区间条件查询

  • select * from emp where salary >= 8000 and salary <= 15000;
  • select * from emp where salary between 9000 and 15000;

• 带有NULL的查询

  • select * from emp where salary is null;
  • select * from emp where salary is not null;
  • select name, IFNULL(salary, 0) + 1000 from emp; 把null值作为0处理。

• 模糊查询：使用like关键字，不要用等号

  • 下划线 _ 表示任意一个字符
  • 百分号 % 表示任意 多个字符

• 排序：关键字order by 字段名，默认是ASC升序排序，DESC是降序。

  • select * from emp order by salary desc; 按照降序排列
  • select * from emp order by id asc; asc可以省略
  • select * from emp order by salary asc, id desc;多个条件排序

• 聚合函数

  • select count(*) from t_employee where commission_pct is not null;统计函数
  • select * from t_employee where salary * (1 + IFNULL(commission_pct, 0)) > 15000; 如果值可能为空的话，要使用IFNULL()方法设置默认值，否则NULL和其他数字计算还是NULL.
  • select SUM(salary), SUM(commission_pct * salary) from t_employee;计算工资总和
  • select SUM(salary * (IFNULL(commission_pct, 0) + 1)) from t_employee;计算带佣金的工资总和
  • select max(birthday), min(birthday) from t_employee;查询年纪最小和最大的员工

• 分组查询

  • select did, count(*) from t_employee group by did;查询部门变化和每个部门的人数
  • select did, sum(salary) from t_employee group by did having SUM(salary) > 40000;查询工资总和大于40000的部门编号及工资和。
  • select did, SUM(salary) sm from t_employee where gender = '女' group by did having sm > 20000;查询部门女员工工资总和大于20000的部门编号和工资总和。

• Limit m, n 关键字

  • m 表示查询的起始索引，n表示需要查询的记录数
  • select * from emp limit 0, 5;查询前5条记录
  • select * from emp limit (x-1) * n , n; 查询第x页的记录,每页有n条记录。