Mysql数据库操作

---

一、数据库的分类

关系型数据库：（SQL）

Mysql，Oracle，Sql Server…等类型数据库

通过表和表之间，行和列之间的关系进行数据存储，学员信息表，考勤表…

非关系型数据库：(NoSQL)

• Redis, MongDB
• 非关系数据库，对象存储，通过对象的自身的属性决定。

二、CMD操作数据库

安装SQLyog

命令提示符 CMD

-- 登录数据库
   Mysql -uroot -proot  --有时候不好使
-- 第二种方式
   mysql -u root -p 
   输入密码：root

// 修改用户密码
   UPdate mysql.user set authentication_string=password(`123456`)where user=`root` and Host = `localhost`; 

 --刷新权限
   flush privileges;

-- 退出连接
   exit; 

查看数据库、表

	CREATE DATABASE [IF NOT EXISTS] westos;   
--[IF NOT EXISTS] 如果不存在，添加的好处：新建重复数据库就不会报错，只是警告。
	create database 库名

--查看所有的数据库
	show databases;  

-- 查看数据库中所有的表
	show tables; 

-- 显示数据库中指定表的信息
	describe student; 
	
2、删除数据库
--[IF EXISTS] 如果存在
	DROP DATABASE [IF EXISTS] westos;   

3、使用数据库
--如果表名、字段名是关键字特殊字符，就需要带``，就不会高亮显示。
	USE `school`

4、查看数据库
	SHOW DATABASES --查看所有的数据库

Mysql约束

（1）主键约束：主键唯一且不为空；一张表中只能存在一个主键  
  	id INT(10)PRIMARY KEY,
  	CONSTRAINT emp_id PRIMARY KEY(id); （emp_id是主键名）
  -- 复合主键——可以定义多行  语法同（2）

（2）默认值约束    若某行无定义值，将会使用默认值
 --无赋值就是默认值
 	id INT(10) default '10' 
 
（3）唯一性约束：该列唯一不能出现重复数据
   建表时：
    id INT(10)UNIQUE;
   建表后：
    alter table 表名 add constraint 约束名称 约束类型（列名）

（4）外键约束：要求不能引用一个不存在的实体；删除的时候先删从表，在删主表；
 	CONSTRAINT emp_fk FOREIGN KEY(被约束列名) REFERENCES employee(参考列名);      
    // 删除有外键关键的表，必须先删除引用的表（从表），再删除自己（主表）
              
（5）非空约束,插入值不能为空.
	id INT(10)NOT NULL;

字段属性设置

Unsigned：

• 无符号的整数
• 声明了该列不能声明为负数

zerofill:

• 0填充的
• 不足的位数，使用0来填充，int（3），5 —> 005

Null not null (非空)

• 假设设置为 not null , 如果不给它赋值，就会报错。

数据库操作

--1.创建数据库
create database if not exists 数据库名

-- 2.删除数据库
drop database if exists stu;
drop database 数据库名

-- 3.使用数据库`student`   " `` "Tab键上面的.
use `abb`;  
use `数据库名` 

-- 4.查看创建数据库的SQL语句
show create database school;

表操作

增加 表

show create table student    -- 查看创建student表的SQL语句
desc student; -- 显示表信息

--建表格式
create table if not exists `student`(
    `id` int(4) not null auto_increment comment'学号',
    `name` varchar(30) not null default '三毛' comment'姓名',
    `birthday` datetime default null comment '出生日期',
    `address` varchar(20) default null comment '家庭住址',
    primary key(`id`) 
) engine=innodb default charset=utf8;
-- 建表语句 Mysql 8.0 +
CREATE TABLE `lianlian_save_derive` (
  `id` bigint not null auto_increment comment'学号',
  `name` varchar(30) not null default '三毛' comment'姓名',
  `device_uid` varchar(50) NOT NULL COMMENT '设备SN',
  `product_key` varchar(64) NOT NULL COMMENT '设备Key',
  `is_deleted` tinyint(1) default '0' comment '逻辑删除(0:正常，1:删除)',
  `create_time` datetime default current_timestamp comment '创建时间',
  `update_by` varchar(64) default '' comment '更新者',
  `updated_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP comment '更新时间',
  `remark` varchar(255) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=94175 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='学生表';

-- 添加数据
insert into student values
(5,'李四','2020-01-01','西安'),
(6,'李四','2020-01-02','湖北');

--  []中内容可写可不写
-- create table [if not exists] `表名`(
     `id` int(4) [not null] [auto_increment] [注释],
     `列名`列类型  [属性]     [索引]    [注释],
      ......
     `列名`列类型  [属性]     [索引]    [注释],
     `列名`列类型  [属性]     [索引]    [注释],                 
--) [表类型][字符集设置][注释]

增加字段（列）

-- 增加表的字段 (列)
alter table stu add sex varchar(2);
alter table 表名 add 列名 列属性（类型）

修改 表

-- 修改表名
alter table student2 rename as student;

alter table 旧表名 rename as 新表名

-- 修改表的字段 (修改约束) 
alter table student modify age int(2);

alter table 表名 modify 列名 列属性（类型）

-- 修改表的字段 (字段重名名)
alter table stu change sex age int(3);
alter table 表名 change 旧名字 新名字 列属性（类型）

删除 表

-- 删除表的字段
alter table stu drop sex;
alter table 表名 drop 列名

-- 删除表（如果表存在再删除）  if exists 如果表存在就删除（好处报错 变警告）
drop table if exists stu;  
drop table if exists 表名

三、SQL语句操作

添加数据 (Insert)

insert into 表名(字段1，字段2，字段3) values
('值1'),('值2'),'值3'....)

注意事项：

-- 增加语句?
	INSERT INTO 表名[(字段1,字段2,字段3,...)] VALUES('值1','值2','值3')
-- 结论:'字段1,字段2...'该部分可省略 , 但添加的值务必与表结构,数据列,顺序相对应,且数量一致.

-- 一次插入多条数据
INSERT INTO grade(gradename) VALUES ('大三'),('大四');

删除数据 (Delete)

-- 删除数据
delete FROM grade WHERE gradeid = 5

-- 删除表所有数据  
truncate grade
TRUNCATE [TABLE] 表名;

注意：区别于DELETE命令

• 相同 : 都能删除数据 , 不删除表结构 , 但TRUNCATE速度更快
• 不同 :
• • 使用TRUNCATE TABLE 重新设置AUTO_INCREMENT计数器
  • 使用TRUNCATE TABLE不会对事务有影响 （事务后面会说）

修改数据 (Update)

UPDATE 表名 SET column_name=value [,column_name2=value2,...] [WHERE 条件];

--将id为2,3的性别改为'女',姓名改为'222'
update student set sex='女' , `name`='222' where id=2 or id=3;

普通查询 （Select）

-- 查询所有学生信息
SELECT * FROM student;
-- 查询指定列(学号 , 姓名)
select 返回查询值 from student where 条件;