数据库操作

2018-10-08 本文已影响0人 Snackk

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下公司。
数据库：

数据库服务器-：运行数据库管理软件

数据库管理软件：管理-数据库

数据库（DataBase，简称DB）：即文件夹，用来组织文件/表

表：即文件，用来存放多行内容/多条记录

分两大类：
　　关系型：如sqllite，db2，oracle，access，sql server，MySQL，注意：sql语句通用
　　非关系型：mongodb，redis，memcache

关系型数据库和非关系型数据库
- 关系型
  一条数据包含了一个事物的多条信息，这些信息之间是有关联性的
- 非关系型 :存取频繁的，并且要求效率高的，不突出数据之间关联的
  k-v

数据库系统的特点：

1 数据结构化（如上图odboy_stu）

2 数据共享，冗余度低，易扩充

3 数据独立性高

4 数据由DBMS统一管理和控制

  a：数据的安全性保护

  b：数据的完整性检查

  c：并发控制

  d：数据库恢复

初识MYSQL

启动mysql：net start mysql
关闭mysql：net stop mysql

SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统,SQL语言由IBM开发。SQL语言分为3种类型：

  1、DDL语句 数据库定义语言： 数据库、表、视图、索引、存储过程，例如CREATE DROP ALTER

  2、DML语句 数据库操纵语言： 插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT

  3、DCL语句 数据库控制语言： 例如控制用户的访问权限GRANT、REVOKE

默认用户登录：>mysql
使用root用户登录：>mysql -uroot -p
设置密码：> set password = password('123');
查看当前用户：> select user();

在输入sql语句的过程中如果想要放弃本条语句 \c

创建用户：
create user 'alex'@'192.168.16.*' identified by '123';

远程登录一个mysql服务:>mysql -ueva -p123 -h 192.168.16.39
新创建出来的用户eva没有使用数据库的权限

给新用户授权: grant
> grant select/all on '库.表' to '用户'@'ip地址';

创建用户并授权: grant
mysql> grant select/all on '库.表' to '用户'@'ip地址' identified by '密码';

查看所有的用户信息：
> select user,host,password from mysql.user;

mysql 新设置用户或更改密码后需用flush privileges刷新MySQL的系统权限相关表，否则会出现拒绝访问，还有一种方法，就是重新启动mysql服务器，来使新设置生效。----> flush privileges;

处理库

通配符 :_ %
% :可以匹配任何内容
_ :一个_匹配一个字符

创建库：
create database 数据库名 [charset utf-8];

查看库：show databases;

修改库：
alter database 数据库的名字你要修改的内容;

删除库：
drop database 数据库名;

创建表，处理表

#语法：
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);

#注意：
1. 在同一张表中，字段名是不能相同
2. 宽度和约束条件可选
3. 字段名和类型是必须的

先切换到文件夹下：use 库名;

增:
mysql> create table 表的名字 (
列1的名字数据类型，
列2的名字数据类型，
...
);

查：show tables;

查看表结构: > desc 表名; <=> describe 表名;
>show create table 表名 \G;（格式化查看）

删：drop table 表名;

改：
修改数据类型：alter table 表名 modify name char(3);
修改列名：alter table 表名 change name name1 char(2);
添加一列：alter table 表名 add 列名数据类型;

处理数据

插入数据：
insert into 表名 (指定你要插入数据的字段名和顺序id,name) values (1,'alex')

插入多个数据：insert into 表名 values(1,'egon1'),(2,'egon2'),(3,'egon3');

查询数据：
select 字段名 from 表名;
select id,name from 表名;
select * from 表名;

改：update t1 set name='sb' where id=2;

删：delete from t1 where id=1;

清空表：
delete from t1; #如果有自增id，新增的数据，仍然是以删除前的最后一样作为起始。
truncate table t1;数据量大，删除速度比上一条快，且直接从零开始

存储引擎

InnoDB 行级锁
支持事务、行级锁、外键
msyql 同时同一个人的同一个数据进行修改
同一张表中不同行的记录可以被同时修改
MyIsam 表级锁
MyISAM既不支持事务、也不支持外键、其优势是访问速度快，但是表级别的锁定限制了它在读写负载方面的性能，因此它经常应用于只读或者以读为主的数据场景
Memory
在内存中存储所有数据，应用于对非关键数据由快速查找的场景。Memory类型的表访问数据非常快，因为它的数据是存放在内存中的，并且默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失
BLACKHOLE
黑洞存储引擎，类似于 Unix 的 /dev/null，Archive 只接收但却并不保存数据。

常用存储引擎及适用场景

InnoDB

用于事务处理应用程序，支持外键和行级锁。如果应用对事物的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包括很多更新和删除操作，那么InnoDB存储引擎是比较合适的。InnoDB除了有效的降低由删除和更新导致的锁定，还可以确保事务的完整提交和回滚，对于类似计费系统或者财务系统等对数据准确要求性比较高的系统都是合适的选择。

MyISAM

如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不高，那么可以选择这个存储引擎。

Memory

将所有的数据保存在内存中，在需要快速定位记录和其他类似数据的环境下，可以提供极快的访问。Memory的缺陷是对表的大小有限制，虽然数据库因为异常终止的话数据可以正常恢复，但是一旦数据库关闭，存储在内存中的数据都会丢失。

查看当前的默认存储引擎:

mysql> show variables like "default_storage_engine";

查询当前数据库支持的存储引擎

mysql> show engines \G;

在建表时指定

mysql> create table ai(id bigint(12),name varchar(200)) ENGINE=MyISAM; 

mysql> create table country(id int(4),cname varchar(50)) ENGINE=InnoDB;

使用alter table语句，修改一个已经存在的表的存储引擎。

mysql> alter table ai engine = innodb;

在配置文件中指定
在my.ini文件

[mysqld]
default-storage-engine=INNODB

MySQL架构总共四层分。

  首先，最上层的服务并不是MySQL独有的，大多数给予网络的客户端/服务器的工具或者服务都有类似的架构。比如：连接处理、授权认证、安全等。 
  
  第二层的架构包括大多数的MySQL的核心服务。包括：查询解析、分析、优化、缓存以及所有的内置函数（例如：日期、时间、数学和加密函数）。同时，所有的跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等。

  第三层包含了存储引擎。存储引擎负责MySQL中数据的存储和提取。服务器通过API和存储引擎进行通信。这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明化。存储引擎API包含十几个底层函数，用于执行“开始一个事务”等操作。但存储引擎一般不会去解析SQL（InnoDB会解析外键定义，因为其本身没有实现该功能），不同存储引擎之间也不会相互通信，而只是简单的响应上层的服务器请求。

  第四层包含了文件系统，所有的表结构和数据以及用户操作的日志最终还是以文件的形式存储在硬盘上。

数值类型

常用
int，float，decimal
整数：int（默认11位,有符号的\unsigned无符号的表示的数更大），bigint
小数：float（单精度），double（双精度），decimal（小数值）

int长度约束没有实际意义（默认11位）

精度测试：

float(255,30)
参数表示一共显示255位，小数部分占30位

范围，超出就会遵循四舍五入的规则：

mysql> create table t8 (f float(255,30),d double(255,30),d2 decimal(65,30));

日期和时间类型

data：年月日
time：时分秒
year：年份值

datatime：年月日时分秒

mysql> create table 表名 (列名 datetime);
mysql> insert into 表名 values ('2018-9-26 12:20:10');
mysql> insert into 表名 values ('2018/9/26 12+20+10');
mysql> insert into 表名 values ('20180926122010');
mysql> insert into 表名 values (20180926122010);

当前时间：mysql> insert into 表名 values (now(),now(),now());

timestamp：混合日期和时间值，时间戳

插入数据null，会自动插入当前时间的时间（默认不为空）

添加一列默认值是'0000-00-00 00:00:00'

mysql> alter table 表名 add 列名 timestamp;

手动修改新的列默认值为当前时间

mysql> alter table 表名 modify 列名 timestamp default current_timestamp;

字符串类型

CHAR 0-255字节定长字符串
定长浪费磁盘存取速度非常快

 这一列数据的长度变化小  手机号 ，身份证号 ，学号
频繁存取、对效率要求高
短数据

VARCHAR 0-65535 字节变长字符串
变长节省磁盘空间存取速度相对慢

这一列的数据长度变化大  描述信息
对效率要求相对小
相对长

mysql> create table t10 (c char(5),vc varchar(5));
mysql> insert into t10 values ('ab','ab');

1.插入ab，实际上存储中c占用5个字节，vc只占用2个字节，但是我们查询的额时候感知不到
2.因为char类型在查询的时候会默认去掉所有补全的空格
3.插入的数据超过了约束的范围，会截断数据
4.插入带有空格的数据，查询的时候能看到varchar字段是带空格显示的，char字段仍然在显示的时候去掉了空格

concat（）：

mysql> select concat(列名1,'需要拼接的内容'),concat(列名2,'需要拼接的内容') from 表名;

ENUM和SET类型

ENUM只允许从值集合中选取单个值
SET类型可以允许值集合中任意选择1或多个元素进行组合。

枚举 enum   单选
集合 set    多选

mysql> create table 表名 (name varchar(20),sex enum('male','female'),hobby set('抽烟','喝酒','烫头','翻车'));

如果插入的数据不在枚举或者集合范围内，数据无法插入表

mysql> insert into 表名 values ('alex',不在的项,不在的项);

向集合中插入数据，自动去重

mysql> insert into 表名 values ('alex','female','抽烟,抽烟,烫头');

约束（防止不符合规范的数据进入数据库）

NOT NULL ：非空约束，指定某列不能为空； 
UNIQUE : 唯一约束，指定某列或者几列组合不能重复
PRIMARY KEY ：主键，指定该列的值可以唯一地标识该列记录
FOREIGN KEY ：外键，指定该行记录从属于主表中的一条记录，主要用于参照完整性

not null ：非空约束，指定某列不能为空；

mysql> create table t12 (id int not null);
default

我们约束某一列不为空，如果这一列中经常有重复的内容，就需要我们频繁的插入，这样会给我们的操作带来新的负担，于是就出现了默认值的概念。

例子：

mysql> create table t13 (id1 int not null,id2 int not null default 222);

unique

  方法一：
  create table department1(
  id int,
  name varchar(20) unique,
  comment varchar(100)
  );
  方法二：
  create table department2(
  id int,
  name varchar(20),
  comment varchar(100),
  unique(name)
  );

not null 和unique的结合

mysql> create table t1(id int not null unique);

联合唯一(不能同时相同)

create table service(
id int primary key auto_increment,
name varchar(20),
host varchar(15) not null,
port int not null,
unique(host,port) 
);

insert into service values
(1,'nginx','192.168.0.10',80),
(2,'haproxy','192.168.0.20',80),
(3,'sql','192.168.0.20',90),
(4,'mysql','192.168.0.30',3306)
;

主键：

主键必须唯一，主键值非空；可以是单一字段，也可以是多字段组合。称为组合键 (Composite Key),也可以叫联合主键。

1.如果我们没有指定主键，那么第一个非空唯一的字段将会被设置成主键
2.指定主键之后其他的非空 + 唯一约束都不会再成为主键

方法一：not null+unique
create table department1(
id int not null unique, #主键
name varchar(20) not null unique,
comment varchar(100)
);

方法二：在某一个字段后用primary key
create table department2(
id int primary key, #主键
name varchar(20),
comment varchar(100)
);

方法三：在所有字段后单独定义primary key
create table department3(
id int,
name varchar(20),
comment varchar(100),
primary key(id);

=======多列做主键=======
create table service(
ip varchar(15),
port char(5),
service_name varchar(10) not null,
primary key(ip,port)
);

AUTO_INCREMENT：只有数字类型才能设置自增

约束字段为自动增长，被约束的字段必须同时被key约束

不指定id，则自动增长
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
);

对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长

mysql> delete from student;# 清空表
Query OK, 4 rows affected (0.00 sec)

mysql> select * from student;
Empty set (0.00 sec)

应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它

mysql> truncate student;

offset偏移量 ****

#在创建完表后，修改自增字段的起始值
mysql> create table student(
    -> id int primary key auto_increment,
    -> name varchar(20),
    -> sex enum('male','female') default 'male'
    -> );

mysql> alter table student auto_increment=3;

可以查出存储引擎，下一个的自增量

mysql> show create table student;
.......
ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

FOREIKEY（外键） Innodb存储引擎中才有外键

解决字段的重复存储

   解决方法： 我们完全可以定义一个部门表 然后让员工信息表关联该表，如何关联，即foreign key

创建部门表
设置字段为unique唯一字段时，设置该字段为外键才会成功（或者主键）

#mysql> create table department (id int primary key auto_increment,dep_name varchar(20) not null);
#Query OK, 0 rows affected (0.16 sec)

创建员工表外键关联部门表中的id字段

#mysql> create table staff (sid int primary key auto_increment,sname varchar(20) not null,dep_id int, foreign key(dep_id) references department(id));
#Query OK, 0 rows affected (0.21 sec)

表类型必须是innodb存储引擎，且被关联的字段，即references指定的另外一个表的字段，必须保证唯一（只有另一个表中设置了unique的字段才能作为本表的外键）

create table department(
id int primary key,
name varchar(20) not null
)engine=innodb;

dpt_id外键，关联父表（department主键id），同步更新，同步删除

create table employee(
id int primary key,
name varchar(20) not null,
dpt_id int,
foreign key(dpt_id)
references department(id)
on delete cascade  # 连级删除
on update cascade # 连级更新
)engine=innodb;

1.删父表department，子表employee中对应的记录跟着删
2.更新父表department，子表employee中对应的记录跟着改
3.如果一个表中的字段作为外键对另一个表提供服务，那么默认不能直接删除外表中正在使用的数据（没有设置on delete cascade连级删除，on update cascade连级更新）

修改表结构

1. 修改表名
      ALTER TABLE 表名 
                      RENAME 新表名;

2. 增加字段
      ALTER TABLE 表名
                      ADD 字段名  数据类型 [完整性约束条件…],
                      ADD 字段名  数据类型 [完整性约束条件…];

3. 删除字段
      ALTER TABLE 表名 
                      DROP 字段名;

4. 修改字段
      ALTER TABLE 表名 
                      MODIFY  字段名 数据类型 [完整性约束条件…];
      ALTER TABLE 表名 
                      CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
      ALTER TABLE 表名 
                      CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];

修改字段排列顺序/在增加的时候指定字段位置

ALTER TABLE 表名
                 ADD 字段名  数据类型 [完整性约束条件…]  FIRST;
ALTER TABLE 表名
                 ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;
ALTER TABLE 表名
                 CHANGE 字段名  旧字段名 新字段名 新数据类型 [完整性约束条件…]  FIRST;
ALTER TABLE 表名
                 MODIFY 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;
                 
# 修改sex列的位置
mysql> alter table staff modify sex enum('male','female') after sname;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc staff;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(4)                | YES  |     | NULL    |       |
| sname | varchar(20)           | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
| age   | int(3)                | YES  |     | NULL    |       |
| phone | bigint(11)            | YES  |     | NULL    |       |
| job   | varchar(11)           | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

# 创建自增id主键
mysql> alter table staff modify id int(4) primary key auto_increment;
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> desc staff;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type                  | Null | Key | Default | Extra          |
+-------+-----------------------+------+-----+---------+----------------+
| id    | int(4)                | NO   | PRI | NULL    | auto_increment |
| sname | varchar(20)           | YES  |     | NULL    |                |
| sex   | enum('male','female') | YES  |     | NULL    |                |
| age   | int(3)                | YES  |     | NULL    |                |
| phone | bigint(11)            | YES  |     | NULL    |                |
| job   | varchar(11)           | YES  |     | NULL    |                |
+-------+-----------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

# 删除主键，可以看到删除一个自增主键会报错
mysql> alter table staff drop primary key;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

# 需要先去掉主键的自增约束，然后再删除主键约束
mysql> alter table staff modify id int(11);
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> desc staff;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | NO   | PRI | 0       |       |
| sname | varchar(20)           | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
| age   | int(3)                | YES  |     | NULL    |       |
| phone | bigint(11)            | YES  |     | NULL    |       |
| job   | varchar(11)           | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

mysql> alter table staff drop primary key;
Query OK, 4 rows affected (0.06 sec)
Records: 4  Duplicates: 0  Warnings: 0

# 添加联合主键
mysql> alter table staff add primary key (sname,age);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 删除主键
mysql> alter table staff drop primary key;
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

# 创建主键id
mysql> alter table staff add primary key (id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc staff;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | NO   | PRI | 0       |       |
| sname | varchar(20)           | NO   |     |         |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
| age   | int(3)                | NO   |     | 0       |       |
| phone | bigint(11)            | YES  |     | NULL    |       |
| job   | varchar(11)           | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

# 为主键添加自增属性
mysql> alter table staff modify id int(4) auto_increment;
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> desc staff;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type                  | Null | Key | Default | Extra          |
+-------+-----------------------+------+-----+---------+----------------+
| id    | int(4)                | NO   | PRI | NULL    | auto_increment |
| sname | varchar(20)           | NO   |     |         |                |
| sex   | enum('male','female') | YES  |     | NULL    |                |
| age   | int(3)                | NO   |     | 0       |                |
| phone | bigint(11)            | YES  |     | NULL    |                |
| job   | varchar(11)           | YES  |     | NULL    |                |
+-------+-----------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

主键和外键的区别：
主键：一张表只能有一个主键,主键非空且唯一
外键：一张表可以有多个外键,可以重复

总结：

约束

not null
    default
unique
    auto_increment
    unique (字段1，字段2，...)
primary key
    primary key(字段1，字段2，...)
foreign key 本表中的字段
    references 外表名(外表的unique字段)
    on delete cascade
    on update cascade

表的增删改查

drop table 表名
create table 表名 (
    列名 数据类型【(长度) 其他约束条件】，
    列名 数据类型【(长度) 其他约束条件】，
    列名 数据类型【(长度) 其他约束条件】，
    ...
)
alter table 表名
                 rename   新表名
                 drop    列名
                 add     新列名 数据类型【(长度) 其他约束条件】 【after (字段名)/first】
                 modify  列名 (新)数据类型【((新)长度) (新)其他约束条件】 【after (字段名)/first】
                 change   列名 新列名 (新)数据类型【((新)长度) (新)其他约束条件】 【after (字段名)/first】
查
desc 表名；
show create table 表名 \G;

多表结构的创建与分析：
多对一：关联方式：foreign key

create table press(
id int primary key auto_increment,
name varchar(20)
);

create table book(
id int primary key auto_increment,
name varchar(20),
press_id int not null,
foreign key(press_id) references press(id)
on delete cascade
on update cascade
);

多对多：关联方式：foreign key+一张新的表
（多对多：一个作者可以写多本书，一本书也可以有多个作者，双向的一对多，即多对多，3张表-->作者表,书名表，对应关系表）

create table author(
id int primary key auto_increment,
name varchar(20)
);

    create table book(
id int primary key auto_increment,
name varchar(20)
);

这张表就存放作者表与书表的关系，即查询二者的关系查这表就可以了

create table author2book(
id int not null unique auto_increment,
author_id int not null,
book_id int not null,
constraint fk_author foreign key(author_id) references author(id)
on delete cascade
on update cascade,
constraint fk_book foreign key(book_id) references book(id)
on delete cascade
on update cascade,
primary key(author_id,book_id)
);

一对一：（外键字段设置成unique即可）
关联方式：foreign key+unique

create table customer(
    -> id int primary key auto_increment,
    -> name varchar(20) not null,
    -> qq varchar(10) not null,
    -> phone char(16) not null
    -> );

create table student(
    -> id int primary key auto_increment,
    -> class_name varchar(20) not null,
    -> customer_id int unique, #该字段一定要是唯一的
    -> foreign key(customer_id) references customer(id) #外键的字段一定要保证unique
    -> on delete cascade
    -> on update cascade
    -> );

SQL语言分为3种类型（CDM）：

  1、DCL语句 数据库控制语言： 例如控制用户的访问权限GRANT、REVOKE

2、DDL语句 数据库定义语言： 数据库、表、视图、索引、存储过程，例如CREATE DROP ALTER

  3、DML语句 数据库操纵语言： 插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT

MySQL数据操作： DML

插入数据insert

1. 插入完整数据（顺序插入）
    语法一：
    INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);

    语法二：
    INSERT INTO 表名 VALUES (值1,值2,值3…值n);

2. 指定字段插入数据
    语法：
    INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);

3. 插入多条记录
    语法：
    INSERT INTO 表名 VALUES
        (值1,值2,值3…值n),
        (值1,值2,值3…值n),
        (值1,值2,值3…值n);

4. 插入查询结果
    语法：
    INSERT INTO 表名(字段1,字段2,字段3…字段n) 
                    SELECT (字段1,字段2,字段3…字段n) FROM 表2
                    WHERE …;
mysql> insert into 表1(sname) select name from 表2 where id < 3;
mysql> insert into mysql.user(user,host,password) values('root','localhost',password('123456')),('root','127.0.0.1',null);

更新数据update

语法：
    UPDATE 表名 SET
        字段1=值1,
        字段2=值2,
        WHERE CONDITION;

示例：
    UPDATE mysql.user SET password=password(‘123’) 
        where user=’root’ and host=’localhost’;

密文形式：

mysql> update mysql.user set password = password('123') where user = 'egon1';

明文形式：

mysql> update mysql.user set password = '123' where user = 'egon2';

删除数据delete

语法：
    DELETE FROM 表名 
        WHERE 条件;

示例：
    DELETE FROM mysql.user 
        WHERE password=’’;

练习：

删除除从本地登录的root用户以外的所有用户

mysql> delete from mysql.user where user != 'root' or host not in ('localhost','127.0.0.1');

单表查询：

单表查询语法

SELECT 字段1,字段2... FROM 表名
                     WHERE 条件
                     GROUP BY field
                     HAVING 筛选
                     ORDER BY field
                     LIMIT 限制条数

from：
1.找到表:from

where：
2.拿着where指定的约束条件，去文件/表中取出一条条记录

group by：
3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组

having：
4.将分组的结果进行having过滤

select
5.执行select

distinct
6.去重

order by
7.将结果按条件排序：order by

limit
8.限制结果的显示条数

语法：

#简单查询
    SELECT id,emp_name,sex,age,hire_date,post,post_comment,salary,office,depart_id 
    FROM employee;

    SELECT * FROM employee;

    SELECT emp_name,salary FROM employee;

#避免重复DISTINCT
    SELECT DISTINCT post FROM employee;    

#通过四则运算查询
    SELECT emp_name, salary*12 FROM employee;
    SELECT emp_name, salary*12 AS Annual_salary FROM employee;
    SELECT emp_name, salary*12 Annual_salary FROM employee;

#定义显示格式
   CONCAT() 函数用于连接字符串
   SELECT CONCAT('姓名: ',emp_name,'  年薪: ', salary*12)  AS Annual_salary 
   FROM employee;

   CONCAT_WS() 第一个参数为分隔符
   SELECT CONCAT_WS(':',emp_name,salary*12)  AS Annual_salary 
   FROM employee;

   结合CASE语句：
   SELECT
       (
           CASE
           WHEN emp_name = 'jingliyang' THEN
               emp_name
           WHEN emp_name = 'alex' THEN
               CONCAT(emp_name,'_BIGSB')
           ELSE
               concat(emp_name, 'SB')
           END
       ) as new_name
   FROM
       emp;

where约束

where字句中可以使用：

1. 比较运算符：> < >= <= <> !=
2. between 80 and 100 值在10到20之间
3. in(80,90,100) 值是10或20或30
4. like 'egon%'
    %表示任意多字符
    _表示一个字符 
5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not

1:单条件查询

SELECT emp_name FROM employee
    WHERE post='sale';

2:多条件查询

SELECT emp_name,salary FROM employee
    WHERE post='teacher' AND salary>10000;

3:关键字BETWEEN AND

SELECT emp_name,salary FROM employee 
    WHERE salary BETWEEN 10000 AND 20000;

SELECT emp_name,salary FROM employee 
    WHERE salary NOT BETWEEN 10000 AND 20000;

4:关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS)

SELECT emp_name,post_comment FROM employee 
    WHERE post_comment IS NULL;

SELECT emp_name,post_comment FROM employee 
    WHERE post_comment IS NOT NULL;
    
SELECT emp_name,post_comment FROM employee 
    WHERE post_comment=''; 注意''是空字符串，不是null
ps：
    执行
    update employee set post_comment='' where id=2;
    再用上条查看，就会有结果了

5:关键字IN集合查询

SELECT emp_name,salary FROM employee 
    WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ;

SELECT emp_name,salary FROM employee 
    WHERE salary IN (3000,3500,4000,9000) ;

SELECT emp_name,salary FROM employee 
    WHERE salary NOT IN (3000,3500,4000,9000) ;

6:关键字LIKE模糊查询

通配符’%’
SELECT * FROM employee 
        WHERE emp_name LIKE 'eg%';

通配符’_’
SELECT * FROM employee 
        WHERE emp_name LIKE 'al__';

不为NULL

is not null

group by（分组）：
强调：

如果我们用unique的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义
多条记录之间的某个字段值相同，该字段通常用来作为分组的依据

获取组内的其他相关信息，需要借助函数

mysql>select 列名,avg(列名) from group by post

聚合函数

count(列名):计数，计算一个分组内成员个数
max（列名）:计算该分组内的最大值
min（列名）：计算该分组内的最小值
sum（列名）：计算该分组内的总值
avg（x）： ---.> 求x列里的平均值，根据group by得到的分组
group_concat(列名)：根据分组得到的组来提取列里面的成员

HAVING过滤：

where过滤的是行
having过滤的是组
执行优先级从高到低：where > group by > having

#1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。
#2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数

例子：查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资

方法1：mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary) <20000;

方法2：mysql> select post,avg(salary) from employee group by post having avg(salary) between 10000 and 20000;

ORDER BY 查询排序：
按单列排序

SELECT * FROM employee ORDER BY salary;
SELECT * FROM employee ORDER BY salary ASC;（默认从小到大）
SELECT * FROM employee ORDER BY salary DESC;（从大到小）

按多列排序:用‘，’隔开

先按照age排序，如果年纪相同，则按照薪资排序
    SELECT * from employee
        ORDER BY age,
        salary DESC;

数据库不切分大小写！！！

LIMIT 限制查询的记录数：
示例：

SELECT * FROM employee ORDER BY salary DESC 
    LIMIT 3; #默认初始位置为0 ， 即LIMIT 0，3;
    
SELECT * FROM employee ORDER BY salary DESC
LIMIT 5,5; #从第5开始，第5条不要，即先查询出第6条，然后包含这一条在内往后查5条

使用正则表达式查询：

查看所有员工中名字是jin开头，n或者g结果的员工信息

select * from employee where emp_name regexp '^jin.*[gn]$';

小结：对字符串匹配的方式

WHERE emp_name = 'egon';
WHERE emp_name LIKE 'yua%';
WHERE emp_name REGEXP 'on$';

多表连接查询：

重点：链接语法

SELECT 字段列表 FROM 表1 INNER|LEFT|RIGHT JOIN 表2 ON 表1.字段 = 表2.字段;

1 交叉连接：不适用任何匹配条件。生成笛卡尔积

mysql> select * from 表1,表2;#暴力拼接

2 内连接：只连接匹配的行

select * from 表1 inner join 表2 on 表1.外键字段 = 表2.字段

内连接 inner join...on...
只有两个表中互相匹配的项才会被匹配

mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee inner join department on employee.dep_id=department.id; 

#上述sql等同于
mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee,department where employee.dep_id=department.id;

3 外链接之左连接：优先显示左表全部记录

#以左表为准，即找出所有员工信息，当然包括没有部门的员工
#本质就是：在内连接的基础上增加左边有右边没有的结果

mysql> select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id;

4 外链接之右连接：优先显示右表全部记录

#以右表为准，即找出所有部门信息，包括没有员工的部门
#本质就是：在内连接的基础上增加右边有左边没有的结果
mysql> select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id;

5 全外连接：显示左右两个表全部记录

全外连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
#注意：mysql不支持全外连接 full JOIN
#强调：mysql可以使用此种方式间接实现全外连接
select * from employee left join department on employee.dep_id = department.id
union
select * from employee right join department on employee.dep_id = department.id
;

注意 union与union all的区别：union会去掉相同的纪录

符合条件连接查询：

#示例1：以内连接的方式查询employee和department表，并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门
select employee.name,department.name from employee inner join department
    on employee.dep_id = department.id
    where age > 25;

#示例2：以内连接的方式查询employee和department表，并且以age字段的升序方式显示
select employee.id,employee.name,employee.age,department.name from employee,department
    where employee.dep_id = department.id
    and age > 25
    order by age asc;
    
#同上：select employee.name,department.name from employee inner join department
on employee.dep_id = department.id order by employee.age asc;

子查询：

#1：子查询是将一个查询语句嵌套在另一个查询语句中。
#2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
#3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
#4：还可以包含比较运算符：= 、 !=、> 、<等

1 带IN关键字的子查询

#查询平均年龄在25岁以上的部门名
select id,name from department
    where id in 
        (select dep_id from employee group by dep_id having avg(age) > 25);

#查看技术部员工姓名
select name from employee
    where dep_id in 
        (select id from department where name='技术');

#查看不足1人的部门名(子查询得到的是有人的部门id)
select name from department where id not in (select distinct dep_id from employee);

2 带比较运算符的子查询

#比较运算符：=、!=、>、>=、<、<=
#查询大于所有人平均年龄的员工名与年龄
mysql> select name,age from emp where age > (select avg(age) from emp);

#查询大于部门内平均年龄的员工名、年龄
select t1.name,t1.age from emp t1
inner join 
(select dep_id,avg(age) avg_age from emp group by dep_id) t2
on t1.dep_id = t2.dep_id
where t1.age > t2.avg_age;

3 带EXISTS关键字的子查询

EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。
而是返回一个真假值。True或False
当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询

# true就执行
#department表中存在dept_id=203，Ture
mysql> select * from employee
    ->     where exists
    ->         (select id from department where id=200);
    
    # false就不执行
    #department表中存在dept_id=205，False
mysql> select * from employee
    ->     where exists
    ->         (select id from department where id=204);
Empty set (0.00 sec)

练习：查询每个部门最新入职的那位员工

答案一为正确答案，答案二中的limit 1有问题（每个部门可能有>1个为同一时间入职的新员工），我只是想用该例子来说明可以在select后使用子查询

#方法1
SELECT
    *
FROM
    emp AS t1
INNER JOIN (
    SELECT
        post,
        max(hire_date) max_date
    FROM
        emp
    GROUP BY
        post
) AS t2 ON t1.post = t2.post
WHERE
    t1.hire_date = t2.max_date;
    
    #方法2
    select employee2.name,employee2.hire_date,t2.post
from employee2
inner join (
    select post,max(hire_date) max_hire from employee2 group by post
    ) t2
on employee2.post = t2.post and  employee2.hire_date = t2.max_hire;

 #方法2.1
select (
select t2.name from emp as t2 where t2.post=t1.post order by hire_date desc limit 1
)
from emp as t1 group by post;

创建表的完整结构：

create table 表名（列名 类型 [约束]，列名2 类型 [约束]，列名3 类型 [约束]，）engine=innodb default charset=utf8

Mysql插入语句之value与values区别：

插入单行时，使用VALUES比较快
插入多行时，用VALUE比较快
得出的结论，应该在插入单行的时候使用VALUES，在插入多行的时候使用VALUE

数据库offset偏移量问题......

Pymysql模块

View -> Tool Windows -> Database -> + -> Data Source -> Mysql -> 安装需要依赖的组件，在设置相应值，然后就能使用Pycharm连接数据库了。

pip —V 查看当前pip版本
pip list 查看当前python解释器安装的第三方包和版本

SQL注入问题
    1. 什么是SQL注入？
        用户输入的内容有恶意的SQL语句，后端拿到用户输入的内容不做检测直接做字符串拼接，得到一个和预期不一致的SQL语句        
    比如：输入带有注释   “ ‘-- “  的sql注入

    2. 如何解决SQL注入？
        对用户输入的内容做检测
        pymysql内置了这种检测，我们只需要让pymysql帮我们拼接sql语句
        ret = cursor.execute(sql, [name, pwd])  # 让pymysql模块帮我们拼接sql语句，执行SQL语句

pymysql格式：

增

# 1.导入模块
import pymysql
# 2. 连接数据库,得到一个连接
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123456',
    database='db',
    charset='utf8' # 是utf8 而非utf-8
)
# 3. 获取光标
cursor = conn.cursor()

# 4. 得到SQL语句
sql = "insert into 表名(列名1, 列名2) values (%s,%s);"  # 按照pymysql模块的写法定义好占位符
# 5. 使用光标对象执行SQL语句
cursor.execute(sql, ['何青松', '456'])  # 让pymysql模块帮我们拼接sql语句，执行SQL语句
# 6.涉及操作数据库的 一定要提交
conn.commit()
# 7.关闭
cursor.close()
conn.close()

删

import pymysql
conn = pymysql.connect(
    host = '127.0.0.1',
    port = 3306,
    user = 'root',
    password = '123456',
    database = 'db',
    charset = 'utf8'
)

cursor = conn.cursor()
sql = "delete from a2 where name = %s;"
cursor.execute(sql,['何青松'])
conn.commit()
cursor.close()
conn.close()

改

import pymysql
conn = pymysql.connect(
    host = '127.0.0.1',
    port = 3306,
    user = 'root',
    password = '123456',
    database = 'db',
    charset = 'utf8'
)

cursor = conn.cursor()
sql = "update a2 set name = %s where id = %s;"
cursor.execute(sql,['刘陆洋',4])
conn.commit()
cursor.close()
conn.close()

查

import pymysql
conn = pymysql.connect(
    host = '127.0.0.1',
    port = 3306,
    user = 'root',
    password = '123456',
    database = 'db',
    charset = 'utf8'
)

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 以字典形式显示,默认是以元祖显示
sql = "select * from a2;"
cursor.execute(sql)
ret = cursor.fetchall()
print(ret)
cursor.scroll(0,mode='absolute')  # 绝对光标移动,移动到绝对位置
ret = cursor.fetchmany(3)
print(ret)
cursor.scroll(-3,mode='relative')  # 相对光标移动,移动到相对位置,负数向上移动,正数向下移动
ret = cursor.fetchone()
print(ret)
ret = cursor.fetchone()
print(ret)
cursor.close()
conn.close()

获取插入数据的ID(关联操作时会用到)
last_id = cursor.lastrowid # conn.commit()之后
批量执行
cursor.executemany(）
使用 rollback() 回滚取消操作。
conn.rollback()

import pymysql
conn = pymysql.connect(
    host = '127.0.0.1',
    port = 3306,
    user = 'root',
    password = '123456',
    database = 'db',
    charset = 'utf8'
)

cursor = conn.cursor()
sql = "insert into a2 (name) values (%s);"
data = ['小兵','张嘎','小于','旺财']
l = []
try:
    cursor.executemany(sql,data)
    # 提交事务
    conn.commit()
    id  = cursor.lastrowid  # 提交之后，获取刚插入的数据的ID
    l.append(id)

except Exception as e:
    conn.rollback()
print(l)
cursor.close()
conn.close()

仅利用返回结果是否有值判断登录

import pymysql
# 1. 获取用户输入
name = input('请输入用户名：')
pwd = input('请输入密码：')

# 判断用户名和密码是否正确
# 去数据库查询一下 用户输入的用户名和密码是否正确
# 1. 连接数据库,得到一个连接
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    database='day43',
    charset='utf8'
)
# 2. 获取光标
cursor = conn.cursor()
# 3. 执行SQL语句
# 3.1 得到SQL语句
sql = "select * from userinfo where username='%s' and password='%s';" % (name, pwd)
print(sql)
# 3.2 使用光标对象执行SQL语句
ret = cursor.execute(sql)
# 关闭
cursor.close()
conn.close()
# 4 得到结果
if ret:
    print('登陆成功')
else:
    print('登录失败')