mysql基础

一个完整的存储过程

1. 创建数据库

CREATE DATABASE demo;

2. 查看所有数据库

SHOW DATABASE;

初始数据库组成：
infomation——schema 是MYSQL系统自带的数据库，主要保存MySQL数据库服务器的系统信息，比如数据库的名称，数据表的名称、字段名称、存取权限、数据文件所在文件夹和系统使用的文件夹等等。
performance_schema是MYSQL系统自带的数据库，可以监控MYQSL的二个性能指标
sys数据库时MySQL系统自带的数据库，主要作用是，以一种更容易理解的方式展示MySQL数据库的各个性能指标，帮助系统管理员和开发人员监控MySQL的技术性能
mysql数据库保存了MySQL数据库服务器运行时需要的系统信息，比如数据文件夹，当前使用的字符集，约束检查信息等等
MySQL数据表由行和列组成，一行就是一条数据记录，每一条数据记录都被分成许多列，一列就叫一个字段，每个字段都需要定义数据类型，这个数据类型叫做字段类型

3.创建数据表

CREATE TABLE demo.test
（
  barcode test,
  goodsname text,
  price int
）;

两点注意：1.创建表时最好指明数据库。2.不要在最后一个字段后面加逗号","
查看表的结构 DESCRIBE demo.test
查看数据库中的表步骤：

1. 选择数据库 USE demo
2. 查询该数据库中所有表 SHOW TABLES

4. 如何设置主键

主键可以确保数据的唯一性，且减少数据错误
主键的三个特征：

• 必须唯一，不能重复；
• 不能是空；
• 必须可以唯一标识数据表中的记录。
  修改表的结构，增加一个主键字段：

ALTER TABLE demo.test;
ADD COLUMN itemnumber int PRIMARY KEY AUTO_INCREMENT;

解释： ALTER TABLE表示修改列
ADD COLUMN表示增加一列
itemnumber int表示字段名为itemnumber，字段类型时int
PRIMARY KEY表示这一列是主列
AUTO_INCREMENT表示每增加一条记录，这个值自动增加。

5.向表中加入一条数据

INSERT INTO demo.test
(barcode,goodsname,price)
VALUES('0001','本',3) 

6.计算字段合计函数

SELECT SUM(PRICE)
FROM demo.goodsmaster

字段

整数类型

image.png

浮点数类型和定点数类型

浮点数

MySql支持的浮点数类型为FLOAT(单精度)、DOUBLE(双精度)、REAL(默认是DOUBLE，想要变成FLOAT需要通过 SET sql_mode = "REAL_AS_FLOAT";)

image.png
浮点数类型的缺陷在于不精准
定点数类型：DECIMAL，是将整数部分和小数部分分别用十六进制保存，保证数值精准表达

文本类型

• CHAR(M):固定长度字符串，CHAR(M)类型必须预先定义字符串长度。
• VARCHAR(M):可变长度字符串。VARCHAR(M)需要预先知道字符串的最大长度，不能超过，保存时按实际字符串长度存储。
• TEXT：字符串。系统自动按照实际长度存储，不需要预先定义长度。
• ENUM：枚举类型：取值必须是在预先设定的一组字符串值范围之内的一个，必须要知道字符串所有可能的取值。
• SET：是一个字符串对象，取值必须是在预先设定的一组字符串值范围之内的0个或多个，也必须知道字符串所有可能的取值
  由于实际存储长度不确定，MySQL不允许TEXT类型的字段做主键，可以采用CHAR(M)或VARCHAR(M)

日期与时间类型

用的最多的是DATETIME，日期格式为YYYY-MM-DD HH:MM:SS,范围为1000-01-01 00:00:00~9999-12-31 23:59:59

约束

约束限定了表中数据应该满足的条件

1. 默认约束
   如定义默认值：

CREATE TABLE demo.importhread
(
importtype INT DEFAULT 1,
--加了默认值1
);

2. 主键约束
   同上面的设置主键
3. 非空约束
   表示字段不能为空，在添加数据时必须有值，否则系统报错
   4.唯一性约束
   表示这个字段的值不能重复，满足主键约束的字段，自动满足费控约束，但是满足唯一性约束的字段，则可以是空值

4. 自增约束：让MySQL自动给字段赋值，且保证不会重复；注意，1.只有整数类型的字段才可以定义自增约束，每增加一条数据，值自动增加1；2.可以给自增约束的字段赋值,MySQL会重置自增约束字段的自增基数，下次添加数据时自动以自增约束字段的最大值加1为新的字段值。

   
   用workbench设置字段自增约束方法.png

修改表

1. 复制表结构

CREATE TABLE demo.importheadhst
LIKE demo.importhread;

执行该句后一个跟demo.importhread有相同表结构的空表demo.importheadhst就创建出来了

2. 添加字段

ALTER TABLE demo.importheadhst ADD confirmer INT;

指定位置添加字段，比如在字段supplieid后添加一个字段suppliername，数据类型是TEXT

ALTER TABLE demo.importheadhst
ADD supplieid TEXT AFTER suppliername;

3. 修改字段
   将字段名“quantity”改为“importquantity”，并把字段类型改为DOUBLE

ALTER TABLE demo.importheadhst
CHANGE quantity importquantity DOUBLE;

如果只想改变字段类型而不改变字段名称：

ALTER TABLE demo.importheadhst
MODIFY importquantity DECIMAL(10,3)

总结常用的创建表SQL语句

CREATE TABLE
(
字段名 字段类型 PRIMARY KEY
);
CREATE TABLE
(
字段名 字段类型 NOT NULL
);
CREATE TABLE
(
字段名 字段类型 UNIQUE
);
CREATE TABLE
(
字段名 字段类型 DEFAULT 值
);
CREATE TABLE
(
字段名 字段类型 AUTO_INCREAMENT
);
-- 在一个已经存在的表基础上，创建一个新表
CREATE TABLE demo.importheadhst LIKE demo.importhread;
-- 修改表的相关语句
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 数据类型;
ALTER TABLE 表名 ADD COLUMN 字段名 字段类型 FIRST|AFTER 字段名;
ALTER TABLE 表名 MODIFY 字段名 字段类型 FIRST|AFTER 字段名;
ALTER TABLE 表名 MODIFY 字段名 字段类型 FIRST;
alter table user rename as userinfo;

记录：在重命名时报错1046，这是因为需要在执行这一句之前先双击选择对应的数据库再进行操作

添加数据

INSERT INTO 表名 [(字段名 [,字段名] ...)] VALUES (值的列表);
--方括号[]表示里面的内容可写可不写

可以部分插入一条数据记录，前提是没有赋值的字段需要让MySQL知道如何处理，比如可以为空，有默认值，或是有自增约束等，否则会报错

插入查询结果

INSERT INTO 表名 （字段名）
SELECT 字段名或值
FROM 表名
WHERE 条件

删除数据

DELETE FROM 表名
WHERE 条件

修改数据

UPDATE 表名
SET 字段名=值
WHERE 条件

注意，不要修改主键字段的值

查询数据

SELECT *|字段列表
FROM 数据源
WHERE 条件
GROUP BY 字段
HAVING 条件
ORDER BY 字段
LIMIT 起始点

• SELECT：查询关键字。“*”是一个通配符，表示要查询表中所有字段
• WHERE:表示查询条件。
• GROUP BY：作用是告诉MySQL，查询结果要如何分组
• HAVING:用于筛选查询结果，与WHERE类似
• FROM 关键字表示查询的数据源，这个数据源可以是一张数据表，也可以是一个查询结果，如：

SELECT a.goodname,a.price
FROM (
  SELECT *
  FROM demo.goodmaster
) AS a;
-- FROM后面的子查询必须要起别名

• ORDER BY 作用是告诉MySQL查询结果如何排序。ASC表示升序，DESC表示降序

SELECT *
FROM demo.goodsmaster
ORDER BY barcode ASC,price DESC;
--先按barcode的升序排序，相同barcode时按price的降序排序

• LIMIT：只显示部分查询结果

SELECT *
FROM demo.goodsmaster
LIMIT 1,2;

这里的“LIMIT 1,2”中，“1”表示起始位置，MySQL 中，起始位置的起点是 0，1 表示从第 2 条记录开始；“2”表示 2 条数据。因此，“LIMIT 1,2”就表示从第 2 条数据开始，显示 2 条数据，也就是显示了第 2、3 条数据。

• 如果我们把查询的结果插入到表中时，导致主键约束或者唯一性约束被破坏了，就可以用“ON DUPLICATE”关键字进行处理。这个关键字的作用是，告诉 MySQL，如果遇到重复的数据，该如何处理。

INSERT INTO demo.goodsmaster
SELECT *
FROM demo.goodsmaster1 as a
ON DUPLICATE KEY UPDATE barcode = a.barcode,goodsname=a.goodsname;

上面这串语句实现将demo.goodsmaster1表中的数据插入到demo.goodsmaster中，如果遇到重复商品编号(barcode)，就用master1表中的编码和商品名(goodsname)替换master表中的编码和商品名

-- 一：Mysql数据库作业--表的操作：
-- 练习题：
-- 1） 创建用户表（user） #反引号，ESC下面的这个键
--  字段      类型  长度
--  uid     int
--  uname   varchar 20
--  password    varchar 20
--  birthday    date
create table `user`(  
    uid int,
    uname varchar(20),
    `password` varchar(20),
    birthday date
);
-- 2） 向用户表中追加字段email,varchar类型，长度20
alter table `user` add email varchar(20);
desc `user`;

-- 3） 向用户表最前面添加字段status,int类型
alter table `user` add `status` int first;

-- 4） 向用户表password字段后面添加sage,int类型
alter table `user` add sage int after `password`;

-- 5） 修改sage的名称为age  modify,change
alter table `user` change sage age int; 

-- 6） 修改password字段的长度为50
alter table `user` change `password` `password` varchar(50);
alter table `user` modify `password` varchar(50);

-- 7） 修改status到email之后的位置
alter table `user` modify `status` int after email;
alter table `user` change `status` `status` int after email;

-- 8） 删除字段status
alter table `user` drop `status`;

-- 9） 修改表的名称为userinfo
rename table `user` to userinfo;

-- 10） 清空数据库表
truncate table userinfo;
delete from userinfo;  #dml 

-- 11） 销毁数据库表user
drop table userinfo;