mysql

基本操作
简介
术语
数据库的三大范式
第一范式
第二范式
第三范式
sql的特点：
书写规范
服务的开启和关闭
登录
查看当前应用的版本

基本操作
简介
如果一个网页是动态（后缀.jsp php shtml)的内容的话，数据库是必不可少的一个环节
mySQL是web应用中最好的和php是黄金搭档
LAMP（linux apache mysql php）
术语
DB： 存储了大量有组织数据的仓库（DATABASE)数据库是用来存储和管理数据的仓库
特点：
用于持久化存储数据
数据库就是一个文件系统
使用了统一的方式来操作数据库
DBMS： 数据库管理系统（DATABASE MANAGEMENT ）
常见的DBMS：mysql,Oracle,DB2（IBM出品，处理海量数据）,sqlserver（微软）
作用：检索数据，插入数据，更新数据，删除数据等、
又称为数据库软件，数据库产品，数据库
mysql就是数据库管理系统，通常我们所说的数据库，就是数据管理系统。
SQL ：结构化查询系统语言 structure query language
sql可以用来操作所有的关系型数据库，是一种通用的标准，每一种数据库操作不一样的地方，成为方言
用于和DBMS通信
SQL是几乎所有的数据库都支持的语言
简单， 并且有力
数据库的三大范式
第一范式
表的每一列都具有原子性，不可分解。
只要是关系型数据库，都自动满足第一范式。数据库表的每一列都是不可分割的
第二范式
第二范式是在第一范式的基础上建立起来的，即满足第二范式必须满足第一范式。第二范式要求数据表中每一个实例或行必须被唯一的区分。为实现区分通常需要我们设计一个主键来实现。
即满足第一范式的前提下，当存在多个主键的时候，才会发生不符合第二范式的情况。比如两个主键。任意一个字段都只依赖表中的一个字段。
如果遇到多个主键的时候，应该拆分
第三范式
第三范式，必须满足第二范式，除了主键列其他列之间不能有传递依赖。
sql的特点：
sql将数据放到表中，表再放到库中
每个数据库可以有多个表，每个表有一个自己的名字，用来标识自己，表名具有唯一性
表具有一些特性，这些特性决定了数据在表中如何存储，类似于java中“类”的设计
每个表都由一个或多个列组成，列又称为字段，每一列对应java中的“属性”
DBMS分为两类，

1. 基于共享文件系统的DBMS（access，excel）
2. 基于客户机-服务器的dbms（mysql,oracle,sqlserver)
   1. 服务器更重要，所以一般说的安装数据库就是安装服务端

书写规范
不区分大小写，但建议关键字大写，建议关键字大写，其他的小写

1. 可以多行操作，但是每条命令最好用分号结尾
2. 每条命令根据需要可以进行缩进或换行
3. 单行注释# —注释文字 /多行注释/

服务的开启和关闭
linux
[root@localhost ~]# systemctl start mysqld.service; [root@localhost ~]# systemctl stop mysqld.service;
win
net start mysql net stop mysql

登录
mysql -h 主机名(ip) -P 端口号 -u用户名 -p密码
连接远程的端口的时候，黄色部分不能省略，连接本地主机的时候可以省略
mysql -h localhost -P 3306 -u root -p
输入密码
删除数据库
drop database xxx； drop database xxx if exists xxx;
查看选中的是哪个数据库
mysql> select database();
切换操作的数据库，假设操作的是test；
use test;
显示选择的库中有哪些表
show tables;
当前选择的是哪个库
select database;
查看当前应用的版本
select version();

数据类型
如何设置有符号和无符号
int unsigned —无符号 int — 有符号：
zerofill为前面填充0
zerofill
枚举类型的，如果只能取固定值，那么可以用枚举不区分大小写
create table tab_c{ c1 enum(‘f’,’m’)— c1 这一列只能取 字符 ‘f’ 和 ‘m’ }
set 类型的 类似于enum 可以有多列
s1 set(‘a’,’b’,’c’,’d’)

数值型

如果插入的数字超出了范围，会插入一个临界值，并返回一个异常
日期型

文本

char 【0-255】较短的文本 不可变长度，节省时间 char存储较短类型的字符
varchar【0-65535】可变长度，节省空间 varchar存储较大的
text blob 较长的文本：

数据表的修改
创建表
CREATE TABLE stuinfo( id INT PRIMARY KEY, #主键 stuName VARCHAR(20) NOT NULL ,# 非空约束 gender CHAR(1), seat INT UNIQUE,# 唯一约束 age INT DEFAULT 18,# 默认约束 majorId INT, foreign key(majorId) REFERENCES major(id) #外键约束 ) CREATE TABLE major( id INT PRIMARY KEY , majorname VARCHAR(20) UNIQUE )

约束
保证数据的准确性和一致性
not null — 用于保证该字段不能为空 default — 默认约束 ，用于保证该字段有默认值 primary key ： 主键约束 用于保证该字段有唯一值 主键非空 如学号 unique 该字段的值具有唯一性，可以为空 比如座位号 foreign key — 外键约束 用于限制两个表的关系，保证该字段的 — 值必须来自主表的关联列 — 在从表添加外键约束，用于引用主表中某列的值
外键约束
ALTER TABLE stuinfo ADD FOREIGN KEY(id) REFERENCES major(id)
外键约束

• 要求在从表设置外键关系
• 从表的外键列的类型和主表的关联列的类型要求一致或兼容，内容无要求
• 主表的关联列必须是一个key
• 删除时不能先删除主表 | | 位置 | 支持的数据类型 | | —- | —- | —- | | 列级约束 | 列的后面 | 语法都支持，但外键没有效果 | | 表级约束 | 所有列的下面 | 默认和非空不支持，其他都支持 |

修改表时添加约束(同修改列的类型是一样的）
添加非空约束
ALTER TABLE major MODIFY COLUMN majorname VARCHAR(20) NOT NULL

如果忘记了，如何给xxx添加自增约束？
alter table xxx add primary key(id);
如何给xxx删除主键约束？
alter table xxx drop primary key(id);
联合主键，每个主键都不可以为空，只要有一个不同就行。
create table user2( id int , name varchar(20), password varchar(20), primary key (id,name));

自增约束
标识列必须和key搭配
一个表至多有一个标识列
标识列的类型只能是数值型
可以设置步长和起始值
mysql> create table user6( -> id int primary key auto_increment, -> name varchar(20));
插入值的时候，如果自增约束的字段为null，就可以自己增加
insert into user6 values(null.’hans)
怎么设置自增步长
set auto_increament_increament = 3
怎么设置起始位置
需要修改起始位置编号的，赋值。其余的为null
ALTER TABLE major MODIFY COLUMN majorname VARCHAR(20) PRIMARY KEY auto_increment
唯一约束
约束修饰的字段的值不可重复
创建时添加唯一约束
mysql> create table user5( -> id int, -> name varchar(20) unique);
创建后添加唯一约束
mysql> alter table user3 add unique(name);

默认约束，default
如果我们插入字段的时候，如果没有传值，就会使用默认值
外键约束，涉及两个表
mysql > create table classes( -> id int primary key, -> name varchar(20));
mysql> create table students( -> id int primary key, -> name varchar(20), -> class_id int, -> foreign key(class_id) references classes(id));
主表中没有的数据值，在副表中是不可以使用的。
主表中的记录被副表使用的时候，是不能够删除的
查看表的结构 desc
describe stui;
修改表名xxx 为yyy
alter table xxx rename to yyy
给数据表添加一列
mysql> alter table DateType add column tFLOAT float;
删除数据表的一列
mysql> alter table DateType drop column tDECIMAL;
修改列名和列的信息
alter table student change column StuId Sid int(15) not null auto_increment primary key;

数据操作
更新数据 update
update stui set name = ‘hs’ where id = 123; update stui set字段 = 值 where 筛选条件
添加数据 insert
insert into stui(id,name) values(123,’am’); insert into stui values(123,’am’); — 这种方法要匹配对应的值i —一次插入多行 insert into student(score,age,name) values(123.2,21,”fdfa”)， values（3234.2,231,”df2a”) values(3.2,233,”3321”) values(124.2,239,”ek”);—

删除 delete
delete from stui where name = ‘hs’; — 可以搭配limit 来执行 delete from stui where name = ‘hs’ limit 2;
复制表的结构或数据
create table 表名 like 旧表； — 仅仅复制结构
复制表的结构+数据
create table 表名 select 查询列表 from 旧的表 where 筛选条件

数据查询
mysql查询练习
查询的格式
select 查询列表 from 表1 别名 inner/left/right/.. join 表2 on 连接条件 where 筛选 group by 分组 having 筛选 order by 排序列表 limit 排序列表
字段，表达式，函数，常量，表都可以起别名
在实际开发中，不使用 ’ ‘ 可以提高阅读性和性能
select from products order by prod_price; select prod_name,prod_id,prod_price from products order by prod_price; select prod_name,prod_id,prod_price from products order by 2,3;— 从第二行第三行排序，只能从选中的列排序 select from products order by prod_price limit 3 offset 2; — 从第二个开始。往后截取三个 select prod_name,prod_id,prod_price from products order by prod_price desc; — 按照降序排列
获取特定的元素
select from orderitems where quantity = 100;

比较符号

and 操作符和 or 和not
select from orders where cust_id = 1000000001 AND order_num = 20005; — 筛选出order中cust_id = 1000000001并且 order_num = 20005的行
select from orders where cust_id = 1000000001 or order_num = 20005; — 筛选出order中cust_id = 1000000001或者order_num = 20005的行

and 和 or 联合使用 ，需要加括号
select * from orders where (cust_id = 1000000001 or cust_id = 1000000003) and order_num >100;

用 in 来指定范围
select * from products where vend_id in (‘dll01’,’brs01’);

like关键字 只能作用于字符串
通配符开销比较大，不要过度使用通配符，尽量使用其他方法代替。
使用通配符%
%可以用来代表任意长度（0个也可）的字符串。或者字符。可以放在开头，中间，或者结尾
mysql> select vend_id from products where vend_id like ‘dll%’; +————-+ | vend_id | +————-+ | DLL01 | | DLL01 | | DLL01 | | DLL01 | +————-+ mysql> select vend_id from products where vend_id like ‘%l%’; +————-+ | vend_id | +————-+ | DLL01 | | DLL01 | | DLL01 | | DLL01 |

使用通配符_ 只匹配对应位置的一个字符
mysql> select from products where vend_id like ‘_RS01’; +————-+————-+——————————+——————+———————————————————————-+ | prod_id | vend_id | prod_name | prod_price | prod_desc | +————-+————-+——————————+——————+———————————————————————-+ | BR01 | BRS01 | 8 inch teddy bear | 5.99 | 8 inch teddy bear, comes with cap and jacket | | BR02 | BRS01 | 12 inch teddy bear | 8.99 | 12 inch teddy bear, comes with cap and jacket | | BR03 | BRS01 | 18 inch teddy bear | 11.99 | 18 inch teddy bear, comes with cap and jacket | +————-+————-+——————————+——————+———————————————————————-+
使用 as 来为列起别名
SELECT first_name AS 名 FROM employees;
*使用 distinct 去掉重复的
select distinct job_id from employees;— 查询员工都有干什么工作的
使用concat 来拼接多行数据
SELECT CONCAT(first_name,” “,last_name) AS 姓名 FROM employees;

外连接
查询一个表中有，另一个表中没有的记录
特点：

1. 外连接的查询结果为主表中的所有记录
2. 如果从表中没有匹配的，则显示null
3. 一般用于查询交集中剩余的不匹配的行，
4. 外连接查询结果 = 内连接结果 + 主表中有而从表没有的记录
5. 左外连接 left join 左边的是主表 ，返回左边表的全部记录
6. 右外连接 right join 右边的是主表，返回右边表的全部记录
7. 左外和右外交换两个表的顺序，可以实现同样的效果
8. ~~全外连接 = ~~ 内连接结果 + 表1中有而从表2没有的记录 + 表2中有而从表1没有的记录
9. 主表列不为空，从表列为空

联合查询
格式
查询语句一
union
查询语句二
。。。
要求，多条查询语句的查询列数是一致的，并且顺序一致
union关键字默认去重
union all 是包含重复项的
union 联合，将多条查询语句的结果组合成一个
查询部门编号>90 或邮箱包含 a 的员工信息
SELECT FROM employees WHERE email LIKE ‘%a%’ UNION SELECT FROM employees WHERE department_id>90

delete VS truncate
1、命令类型 delete是数据操作语言（DML）命令；而truncate是数据定义语言（DDL）命令。 2、功能 delete命令根据指定的SQL语句从表中删除单个，多个或所有记录；而truncate命令从数据库中删除所有记录和表结构。 3、Where子句 delete命令支持WHERE子句，可以使用带有DELETE的where子句来过滤和删除特定记录；而，truncate命令不支持WHERE子句。 4、锁定 delete命令采用行级锁定，表中的每一行都被锁定以进行删除；truncate命令采用表级锁定，锁定了整个表以删除所有记录。 5、索引视图 delete命令可以与索引视图一起使用；而，truncate命令不能与索引视图一起使用。 6、执行速度 由于elete命令维护日志，因此速度很慢。但是，由于truncate命令在事务日志中维护最少的日志记录，因此执行速度更快。 7、表结构 delete命令不会影响表结构，而truncate命令会从数据库中删除表结构。 8、事务空间 delete命令比truncate命令