MySQL基础

作者: 强某某 | 来源:发表于2020-01-10 13:57 被阅读0次

1. MySQL 基础
MySql数据库基础及IDE
2018-04-09 数据仓库技能要求
mysql基础
mysql索引，锁机制，主从同步，日志分析
MySQL基础语句及常见关键字使用总结
MySQL基础语句与其在Python中的使用
高性能的索引策略
MySQL学习笔记---基础概念和一些基础SQL语句（一）
mysql学习

MySQL执行流程

mysql执行步骤图.png

mysql执行细节.png

由上可知，数据库也有缓存，其中解析器是最耗费时间的步骤

MySQL的SQL语句

1.DDL : 定义数据库，数据表的结构，create drop alter

2.DML : 数据操纵语言， insert update delete

3.DCM : 数据控制语言，定义访问权限，取消访问权限，安全设置 grant

4.DQL ：数据查询语言, select(查询) from子句 where子句

数据库CRUD操作

首先登录数据库: mysql -uroot -p

创建数据库

create database java
-- 指定字符集
create database java character set utf8
-- 指定校对规则
create database java character set utf8 collate 校对规则

查看数据库

-- 查看所有数据库
show databases;
-- 查看数据库定义语句
show create database java

修改数据库

-- 修改数据库的字符集
alter database java character set utf8

删除数据库

drop database java;

其他数据库命令

-- 切换数据库
use java
-- 查看当前使用的数据库
select database();

表的CRUD操作

创建表

-- 列的约束
    主键约束: primary key
    唯一约束: unique
    非空约束: not null

-- 数据类型
    data : YYYY-MM-DD
    time : hh:mm:ss
    datatime: YYYY-MM-DD hh:mm:ss 默认null
    timestamp: YYYY-MM-DD hh:mm:ss 默认当前时间
create table student(
    sid int primary key,
    sname varchar(30),
    sex int,
    age int
) ;

查看表

-- 查看所有表
show tables;
-- 查看表的定义
show create table student;
-- 查看表结构
desc student;

修改表

-- 添加列
alter table student add chengji int not null;
-- 修改列(修改列sex的类型为varchar类型)
alter table student modify sex varchar(2);
-- 修改列名(修改列sex名为gender)
alter table student change sex gender varchar(2);
-- 删除列
alter table student drop gender;
-- 修改表名
rename table student to java;
-- 修改表的字符集
alter table student character set utf8;

清空表

truncate table 表名

delete from 表和truncate table 表区别？
delete from 表：遍历表记录，一条一条的删除

truncate table：将原表销毁，再创建一个同结构的新表。就清空表而言，这种方法效率高。

删除表

drop table student;

和delete from student不同，delete是清空数据，drop是表都删除

表数据的CRUD操作

插入数据

insert into student (name,age,sex) values ("qiang",20,"1");
insert into student values("zeng",30,"2");
-- 批量插入(成功一起成功，失败一起失败)
insert into student values("zeng",30,"2"),("zeng",30,"2");
-- 查看表中数据
select * from student;

删除记录

delete from student where name='qiang';
-- delete不指定条件会一条一条全部删除(数据量少的时候效率高)
delete from student;
-- truncate(DDL)删除(先删除表在重建表，数据量多的时候效率高)
truncate student;

更新表记录

-- 修改age=20的数据的sex为30 name为曾
update student set sex=30,name="曾" where age=20;

查询记录

select [distinct] [*] [列名,列名2] from 表名 [where 条件]
-- distinct 去重查询
select distinct * from student;
-- 别名查询 as关键字 可省略
select distinct sex s from student;
-- 运算查询
SELECT  *, age*2 AS ageall FROM student WHERE age=20;
-- 查询年纪在10-20
SELECT  * FROM student WHERE age>10 AND age<20;
SELECT  * FROM student WHERE age BETWEEN 10 AND 20;
-- like:模糊查询
    _ : 代表一个字符
    % : 代表是多个字符
SELECT * FROM student WHERE NAME like'_曾';
-- in 查询某个范围中获得的值
SELECT * FROM student WHERE age IN (20,30)
-- 排序查询(asc 升序,默认  desc 降序)
SELECT  * FROM student WHERE age ORDER BY age asc;

聚合函数

--注意：where之后不可以接聚合函数
-- 求和 sum()
SELECT SUM(age) FROM student WHERE age>1;
-- 求平均值avg()
SELECT AVG(age) FROM student WHERE age>1;
-- 统计数量
SELECT COUNT(*) FROM student;
-- 最大值/最小值 max()/min()
SELECT MAX(age) FROM student WHERE age>1;
--综合(查询所有年纪大于平均年纪的信息)
--之所以用子查询是因为where后面不可以使用聚合函数
SELECT * FROM student WHERE age> (SELECT AVG(age) FROM student);

分组查询(group by)

-- having 关键字 可以接聚合函数 出现在分组之后
-- where  关键字 不可以接聚合函数，出现在分组之前
-- 查询age大于1并且分组信息中sex为2的信息
SELECT * FROM student WHERE age>1 GROUP BY age HAVING sex='2';

列属性

列属性——是否为空(null | not null)

null：可以为空

not null：不可以为空

思考题

学员姓名允许为空吗? 非空
家庭地址允许为空吗? 非空
电子邮件信息允许为空吗? 可以为空
考试成绩允许为空吗? 可以为空

列属性——默认值（default）

1、如果一个字段没有插入值，可以默认插入一个指定的值。

2、default关键字用来插入默认值

mysql> create table t16(
   -> id int unsigned,
   -> addr varchar(20) not null default '地址不详'
   -> );
Query OK, 0 rows affected (0.06 sec)

mysql> insert into t16 values (1,'北京'),(2,default);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t16;
+------+----------+
| id   | addr     |
+------+----------+
|    1 | 北京         |
|    2 | 地址不详        |
+------+----------+
2 rows in set (0.00 sec)

列属性——自动增长（auto_increment）

1、字段的值从1开始，每次递增1，特点就在字段中的数据不可能重复，适合为记录生成唯一的id

2、自动增长都是无符号整数。

3、在MySQL中，auto_increment必须是主键。但是主键不一定是自动增长的。

4、如果要给自动增长列插入数据，使用null关键字。

5、自动增长列上的数据被删除，默认情况下此记录的编号不再使用。

列属性——主键（primary key）

主键：唯一标识表中记录的一个或一组列

主键的特点：不能重复，不能为空

一个表只能有一个主键，==主键可以有多个字段组成==。

主键的作用：

1、保证数据完整性

2、加快查询速度

添加主键

方法一：创建表的时候添加主键

mysql> create table t17(
    -> id varchar(5) primary key,   # 创建主键
    -> name varchar(10) not null
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t17 values ('s2531','tom'),('s2532','berry');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t17;
+-------+-------+
| id    | name  |
+-------+-------+
| s2531 | tom   |
| s2532 | berry |
+-------+-------+
2 rows in set (0.00 sec)

# 如果插入主键相同数据会报错
mysql> insert into t17 values ('s2531','tom');
ERROR 1062 (23000): Duplicate entry 's2531' for key 'PRIMARY'

# 主键不能插入null值
mysql> insert into t17 values (null,'tom');
ERROR 1048 (23000): Column 'id' cannot be null

方法二：创建表的时候添加主键

mysql> create table t18(
    -> id int,
    -> name varchar(10),
    -> primary key(id)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> desc t18;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | 0       |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

方法三：更改表的时候添加主键

mysql> create table t20(
    -> id int,
    -> name varchar(10)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> alter table t20 add primary key (id);   # 更改表添加主键
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t20;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | 0       |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

列属性——唯一键

特点：

1、不能重复，可以为空

2、一个表可以有多个唯一键

作用：

1、保证数据不能重复。保证数据完整性

2、加快数据访问

添加唯一键

方法一：创建表的时候添加唯一键

mysql> create table t22(
   -> id int primary key,
   -> name varchar(20) unique,    #通过unique添加唯一键
   -> addr varchar(100) unique
   -> );
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t22 values (1,'tom','上海');
Query OK, 1 row affected (0.05 sec)

mysql> insert into t22 values (2,'tom','北京');    #  name重复了，报错
ERROR 1062 (23000): Duplicate entry 'tom' for key 'name'
mysql> insert into t22 values (2,'berry','上海');   # addr重复了 
ERROR 1062 (23000): Duplicate entry '上海' for key 'addr'

还有一种方法

mysql> create table t26(
    -> id int,
    -> name varchar(20),
    -> addr varchar(20),
    -> primary key(id),
    -> unique (name),     # 添加唯一键
    -> unique (addr)
    -> );
Query OK, 0 rows affected (0.06 sec)

方法二：修改表的时候添加唯一键

mysql> create table t23(
    -> id int primary key,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> alter table t23 add unique (name);    #  添加一个唯一键
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

一次添加多个唯一键

mysql> create table t24(
    -> id int primary key,
    -> name varchar(20),
    -> addr varchar(20)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> alter table t24 add unique(name),add unique(addr);  
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

添加组合唯一键

mysql> create table t25(
    -> id int primary key,
    -> name varchar(20),
    -> addr varchar(20)
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> alter table t25 add unique(name,addr);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

查看唯一键

mysql> show create table t26\G
*************************** 1. row ***************************
      Table: t26
Create Table: CREATE TABLE `t26` (
 `id` int(11) NOT NULL DEFAULT '0',
 `name` varchar(20) DEFAULT NULL,
 `addr` varchar(20) DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `name` (`name`),      # 唯一键
 UNIQUE KEY `addr` (`addr`)       # 唯一键
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> show create table t25\G
*************************** 1. row ***************************
      Table: t25
Create Table: CREATE TABLE `t25` (
 `id` int(11) NOT NULL,
 `name` varchar(20) DEFAULT NULL,
 `addr` varchar(20) DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `name` (`name`,`addr`)    # 组合唯一键
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

添加唯一键，给唯一键取名

mysql> create table t27(
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> alter table t27 add unique UQ_name(name);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t27\G
*************************** 1. row ***************************
       Table: t27
Create Table: CREATE TABLE `t27` (
  `name` varchar(20) DEFAULT NULL,
  UNIQUE KEY `UQ_name` (`name`)    # 唯一键的名字是UQ_name
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

删除唯一键

通过唯一键的名字来删除唯一键

语法：alter table 表名 drop index 唯一键名称

问题：主键和唯一键的区别？

1、主键不能重复，不能为空，唯一键不能重复，可以为空

2、主键只有一个，唯一键可以有多个。

数据完整性介绍

数据没有对错，如果数据不完整就是没用的数据(例如无唯一标识针对实体完整性，例如没有数据针对域完整性)，就表明失去数据完整性

TIM截图20190724094040.png

保证实体完整性

1、主键约束

2、唯一约束

3、自动增长列

保证域完整性

1、数据类型约束

2、非空约束

3、默认值约束

保证引用完整性

外键约束：从表中的公共字段是主表的外键

引用完整性

主表和从表

两个表建立关系（两个表只要有公共字段就有关系），一个表称为主表，一个表称为从表。

外键约束可以实现：

1、主表中没有的从表中不允许插入

2、从表中有的主表中不允许删除

3、不能更改主表中的值而导致从表中的记录孤立存在。

4、先删除从表，再删除主表

外键（foreign key）

1、外键：从表中的公共字段，公共字段的名字可以不一样，但是数据类型必须一样。

2、外键约束用来保证引用完整性

添加外键

方法一：创建表的时候添加外键

create table stuinfo(
   stuno char(4) primary key,
   name varchar(10) not null
);

create table stumarks(
   stuid char(4) primary key,
   score tinyint unsigned,
   foreign key (stuid) references stuinfo(stuno)
);

方法二：修改表的时候添加外键

mysql> create table stuinfo(
    ->  stuno char(4) primary key,
    ->  name varchar(10) not null
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> create table stumarks(
    ->  stuid char(4) primary key,
    ->  score tinyint unsigned
    -> );
Query OK, 0 rows affected (0.06 sec)

语法：  alter table 从表 add foreign key (从表的公共字段) references 主表(公共字段)

mysql> alter table stumarks add foreign key (stuid) references stuinfo(stuno);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

脚下留心：要创建外键必须是innodb引擎，myisam不支持外键约束

查看外键

show create table student\G

删除外键

通过外键的名字删除外键

语法：alter table 表名 drop foreign key 外键名

例题

mysql> alter table stumarks drop foreign key stumarks_ibfk_1;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

外键操作

1、严格操作（前面讲的是严格操作）

2、置空操作（set null）：如果主表记录删除或更新，从表置空

3、级联操作（cascade）：如果主表记录删除或更新，从表级联

一般来说：主表删除的时候，从表置空操作，主表更新的时候，从表级联操作。

语法：foreign key(外键) references 主表(关键字段)[主表删除是的动作][主表更新时候的动作]

例题

mysql> create table stuinfo(
    -> stuno char(4) primary key,
    -> name varchar(10) not null
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> create table stumarks(
    -> stuid int auto_increment primary key,
    -> stuno char(4) ,
    -> score tinyint unsigned,
    ->  foreign key (stuno) references stuinfo(stuno) on delete set null on update cascade
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> insert into stuinfo values ('s101','tom');
Query OK, 1 row affected (0.00 sec)

mysql> insert into stumarks values (null,'s101',88);
Query OK, 1 row affected (0.00 sec)

mysql> select * from stuinfo;
+-------+------+
| stuno | name |
+-------+------+
| s101  | tom  |
+-------+------+
1 row in set (0.00 sec)

mysql> update stuinfo set stuno='s102' where stuno='s101';   # 更新时级联
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from stumarks;
+-------+-------+-------+
| stuid | stuno | score |
+-------+-------+-------+
|     1 | s102  |    88 |
+-------+-------+-------+
1 row in set (0.00 sec)

mysql> delete from stuinfo where stuno='s102';    # 删除时置空
Query OK, 1 row affected (0.02 sec)

mysql> select * from stumarks;
+-------+-------+-------+
| stuid | stuno | score |
+-------+-------+-------+
|     1 | NULL  |    88 |
+-------+-------+-------+
1 row in set (0.00 sec)