数据库
是按照数据结构组织、存储和管理数据的仓库。本质是一个文件系统,数据按照特定的格式将数据存储起来,用户可以对数据库中的数据进行增加,修改,删除及查询操作。
分类
SQL分类
数据定义语言:简称DDL(Data Definition Language),用来定义数据库对象:数据库,表,列等。关键字:create,alter,drop等
数据操作语言:简称DML(Data Manipulation Language),用来对数据库中表的记录进行更新。关键字:insert,delete,update等
数据控制语言:简称DCL(Data Control Language),用来定义数据库的访问权限和安全级别,及创建用户。
数据查询语言:简称DQL(Data Query Language),用来查询数据库中表的记录。关键字:select,from,where等
数据表中的数据类型
整数类型 tinyInt 很小的整数
smallint 小的整数
mediumint 中等大小的整数
int(integer) 普通大小的整数
小数类型 float 单精度浮点数
double 双精度浮点数
decimal(m,d) 压缩严格的定点数
日期类型 year YYYY
time HH:MM:SS
date YYYY-MM-DD
datetime YYYY-MM-DD HH:MM:SS
timestamp YYYY-MM-DD HH:MM:SS文本、二进制类型 CHAR(M) M为0~255之间的整数
VARCHAR(M) M为0~65535之间的整数
TINYBLOB 允许长度0~255字节
BLOB 允许长度0~65535字节
MEDIUMBLOB 允许长度0~167772150字节
LONGBLOB 允许长度0~4294967295字节
TINYTEXT 允许长度0~255字节
TEXT 允许长度0~65535字节
MEDIUMTEXT 允许长度0~167772150字节
LONGTEXT 允许长度0~4294967295字节
VARBINARY(M)允许长度0~M个字节的变长字节字符串
BINARY(M) 允许长度0~M个字节的定长字节字符串
创建数据库
create database 数据库名
查看数据库MySQL服务器中的所有的数据库:
show databases;删除数据库
drop database 数据库名称;切换数据库:
use 数据库名;创建数据表格式
create table 表名(
字段名 类型(长度) 约束,
字段名 类型(长度) 约束
);哪些约束:
主键约束
非空约束
唯一约束
外键约束查看数据库中的所有表:
格式:show tables;
查看表结构:
格式:desc 表名;
例如:desc sort;修改表添加列
alter table 表名 add 列名 类型(长度) 约束;创建用户表:
需求:创建用户表,用户编号,姓名,用户的地址CREAT TABLE users ( uid INT, uname VARCHAR(20), uaddress VARCHAR(200) );
主键约束
主键是用于标识当前记录的字段。它的特点是非空,唯一。
在开发中一般情况下主键是不具备任何含义,只是用于标识当前记录。格式:
1.在创建表时创建主键,在字段后面加上 primary key.
create table tablename(
id int primary key,
…
) 2.在创建表时创建主键,在表创建的最后来指定主键
create table tablename(
id int,
…,
primary key(id)
) 3.删除主键:alter table 表名 drop primary key;
alter table sort drop primary key; 4.主键自动增长:一般主键是自增长的字段,不需要指定。
实现添加自增长语句,主键字段后加auto_increment
修改表结构
- 修改表添加列
alter table 表名 add 列名 类型(长度) 约束;
例如:
为分类表添加一个新的字段为 分类描述 varchar(20)
ALTER TABLE sort ADD new VARCHAR(20);- 修改表修改列的类型长度及约束
alter table 表名 modify 列名 类型(长度) 约束;
例如:
为分类表的分类名称字段进行修改,类型varchar(50) 添加约束 not null
ALTER TABLE sort MODIFY sname VARCHAR(50) NOT NULL;- 修改表修改列名
alter table 表名 change 旧列名 新列名 类型(长度) 约束;
例如:
为分类表的分类名称字段进行更换 更换为 snamesname varchar(30)
ALTER TABLE sort CHANGE sname snamename VARCHAR(30);- 修改表删除列
alter table 表名 drop 列名;
例如:
删除分类表中snamename这列
ALTER TABLE sort DROP snamename;- 修改表的字符集
alter table 表名 character set 字符集;
例如:
为分类表 tables 的编码表进行修改,修改成 utf8
ALTER TABLE tables CHARACTER SET utf8;数据表添加数据
insert into 表 (列名1,列名2,列名3…) values (值1,值2,值3…);
举例:
INSERT INTO product (id,pname,price) VALUES (1,‘笔记本’,5555.99);添加数据格式,不考虑主键
insert into 表名 (列名) values (值)举例:
INSERT INTO product (pname,price) VALUE(‘洗衣机’,800);删除数据
- 语法:
delete from 表名 [where 条件];数据的基本查询
查询指定字段信息
select 字段1,字段2,…from 表名;
例如:
select id,name from student;查询表中所有字段
select * from 表名;
例如:
select * from student;查询某个字段的值
select name from student;distinct用于去除重复记录
select distinct 字段 from 表名;
例如:
select distinct money from student;别名查询,使用的as关键字,as可以省略的
select * from 表名 as 别名;
或
select * from 表名 别名;条件查询(where)
一个字段的条件
select 字段 from 表名 where 条件;
复合查询 多个条件 and &&
select * from score where name=“joke” and age =33;查询空值
select * from score where age is null;模糊查询
查询变量中有没有storage_engine
show variables like “%storage_engine%”;
Variable_name Value default_storage_engine InnoDB ---------------------- ------ default_tmp_storage_engine InnoDB -------------------------- ------ disabled_storage_engines ------------------------ ---- internal_tmp_disk_storage_engine InnoDB -------------------------------- ------ 根据某个字段的名字模糊查询
select * from score where name like “%三%”;
占位符查询 占一位
select * from score where name like “_三”;
排序查询
通过order by语句,可以将查询出的结果进行排序。放置在select语句的最后。
SELECT * FROM 表名 ORDER BY 字段ASC ASC 升序 (默认) DESC 降序
聚合函数
count:统计指定列不为NULL的记录行数
sum:计算指定列的数值和,如果指定列
max:计算指定列的最大值
min
avg:计算指定列的平均值,如果指定列类型不是数值类型,那么计算结果为0
分组查询
group by字句对查询信息进行分组
SELECT 字段1,字段2… FROM 表名 GROUP BY 字段 HAVING 条件;
having与where的区别 * having是在分组后对数据进行过滤. * where是在分组前对数据进行过滤
多表查询
通过外键约束来联系多表之间的关系(引用外键后两张表就会存在主从关系)
添加外键
格式:
ALTER TABLE student ADD CONSTRAINT stu_tea foreign key(num) references teacher(num);
外键关联查询
格式:
select sname,sschool,tname,tschool from student s join teacher t on s.num = t.num;
外键查询加条件
select sname,sschool,tname,tschool from student s join teacher t on s.num = t.num where s.sid = 3;
表关联查询(内联查询,没有使用到外键)
select * from student s join teacher t where stu.sid= tea.num;
在删除外键关联的表时报错 原因是主键被引用到另一张表中
- alter table student drop foreign key stu_tea;
表的关联操作
一对多(一):
select * from studnet stu join teacher on stu.tid=teacher.tid where teacher.tid=3;
同一数据对应另外表的一个或多个数据
多对多:
需要第三张表作桥梁,抽取两表中共同int类型value作外键关联。
什么是事务
INOODB引擎的特点:1支持事务处理2.行级别的锁。3.外键
就是数据库的一组操作,一条或多条sql语句组成;
事务的特点
原子性:
一致性:
隔离性:
持久性:
mysql默认支持事务开启
查看事务是否自动提交 : show variables like “%autocommit%”;
事务的操作
开启(关闭)事务自动提交:set autocommit = 1;(关闭 = 0)
开启事务:start transaction;
执行
连接查询
笛卡尔查询(cross join 交叉查询)
select * from one join two;
+-----+-------+----------+------+------+-------+----------+ | oid | oname | oaddress | tid | tid | tname | taddress | +-----+-------+----------+------+------+-------+----------+ | 1 | 小爱 | a | 1 | 1 | a | a | | 2 | 小波 | b | 1 | 1 | a | a | | 3 | 小擦 | c | 2 | 1 | a | a | | 4 | 小大 | d | 2 | 1 | a | a | | 5 | 小一 | e | 3 | 1 | a | a | | 6 | ds | dsf | 3 | 1 | a | a | | 1 | 小爱 | a | 1 | 1 | b | b | | 2 | 小波 | b | 1 | 1 | b | b | | 3 | 小擦 | c | 2 | 1 | b | b | | 4 | 小大 | d | 2 | 1 | b | b | | 5 | 小一 | e | 3 | 1 | b | b | | 6 | ds | dsf | 3 | 1 | b | b | | 1 | 小爱 | a | 1 | 2 | c | c | | 2 | 小波 | b | 1 | 2 | c | c | | 3 | 小擦 | c | 2 | 2 | c | c | | 4 | 小大 | d | 2 | 2 | c | c | | 5 | 小一 | e | 3 | 2 | c | c | | 6 | ds | dsf | 3 | 2 | c | c | | 1 | 小爱 | a | 1 | 2 | d | d | | 2 | 小波 | b | 1 | 2 | d | d | | 3 | 小擦 | c | 2 | 2 | d | d | | 4 | 小大 | d | 2 | 2 | d | d | | 5 | 小一 | e | 3 | 2 | d | d | | 6 | ds | dsf | 3 | 2 | d | d | | 1 | 小爱 | a | 1 | 3 | f | f | | 2 | 小波 | b | 1 | 3 | f | f | | 3 | 小擦 | c | 2 | 3 | f | f | | 4 | 小大 | d | 2 | 3 | f | f | | 5 | 小一 | e | 3 | 3 | f | f | | 6 | ds | dsf | 3 | 3 | f | f | | 1 | 小爱 | a | 1 | 3 | g | g | | 2 | 小波 | b | 1 | 3 | g | g | | 3 | 小擦 | c | 2 | 3 | g | g | | 4 | 小大 | d | 2 | 3 | g | g | | 5 | 小一 | e | 3 | 3 | g | g | | 6 | ds | dsf | 3 | 3 | g | g | +-----+-------+----------+------+------+-------+----------+
内连接
select * from three join one on three.oid = one.oid;
+------+------+-----+-------+----------+------+ | oid | tid | oid | oname | oaddress | tid | +------+------+-----+-------+----------+------+ | 1 | 1 | 1 | 小爱 | a | 1 | | 1 | 2 | 1 | 小爱 | a | 1 | | 1 | 3 | 1 | 小爱 | a | 1 | | 1 | 4 | 1 | 小爱 | a | 1 | | 2 | 3 | 2 | 小波 | b | 1 | | 3 | 3 | 3 | 小擦 | c | 2 | +------+------+-----+-------+----------+------+
select * from three join one where three.oid = one.oid;(不看外键)
+------+------+-----+-------+----------+------+ | oid | tid | oid | oname | oaddress | tid | +------+------+-----+-------+----------+------+ | 1 | 1 | 1 | 小爱 | a | 1 | | 1 | 2 | 1 | 小爱 | a | 1 | | 1 | 3 | 1 | 小爱 | a | 1 | | 1 | 4 | 1 | 小爱 | a | 1 | | 2 | 3 | 2 | 小波 | b | 1 | | 3 | 3 | 3 | 小擦 | c | 2 | +------+------+-----+-------+----------+------+
外连接
左连接left join
左表为主基准查询右表相关数据
+-----+-------+----------+------+------+------+ | oid | oname | oaddress | tid | oid | tid | +-----+-------+----------+------+------+------+ | 1 | 小爱 | a | 1 | 1 | 1 | | 1 | 小爱 | a | 1 | 1 | 2 | | 1 | 小爱 | a | 1 | 1 | 3 | | 1 | 小爱 | a | 1 | 1 | 4 | | 2 | 小波 | b | 1 | 2 | 3 | | 3 | 小擦 | c | 2 | 3 | 3 | | 4 | 小大 | d | 2 | NULL | NULL | | 5 | 小一 | e | 3 | NULL | NULL | | 6 | ds | dsf | 3 | NULL | NULL | +-----+-------+----------+------+------+------+
右连接
select * from one right join three on three.oid = one.oid;
+------+-------+----------+------+------+------+ | oid | oname | oaddress | tid | oid | tid | +------+-------+----------+------+------+------+ | 1 | 小爱 | a | 1 | 1 | 1 | | 1 | 小爱 | a | 1 | 1 | 2 | | 1 | 小爱 | a | 1 | 1 | 3 | | 1 | 小爱 | a | 1 | 1 | 4 | | 2 | 小波 | b | 1 | 2 | 3 | | 3 | 小擦 | c | 2 | 3 | 3 | +------+-------+----------+------+------+------+
子查询(自查)
select * from (select * from one where oid>3) as ss where ss.oname =“小爱”;
复合查询(增加一些条件及排序)
select * from (select * from one where oid>3) as ss where ss.oid <=5 order by ss.oid desc;
+-----+-------+----------+------+ | oid | oname | oaddress | tid | +-----+-------+----------+------+ | 5 | 小一 | e | 3 | | 4 | 小大 | d | 2 | +-----+-------+----------+------+
rollback为事务回滚。