数据库基础

2020-03-25

数据库基础，本文将讲述一下基本的 MySQL 基本语法以及遇到的一些问题。

tips

  • 解决Host '192.168.1.75' is not allowed to connect to this MySQL server
use mysql;
update user set host='%' where user='root';
FLUSH PRIVILEGES;

%是通配符

show variables like 'gen%'

+----------------------------------+---------------------------+
| Variable_name                    | Value                     |
+----------------------------------+---------------------------+
| general_log                      | OFF                       |
| general_log_file                 | /var/lib/mysql/debian.log |
| generated_random_password_length | 20                        |
+----------------------------------+---------------------------+

创建数据库,并使用数据库

create database soft;
use soft;

创建表

create table stu(
    sno varchar(20),
    sname varchar(20),
    ssex char(4),
    sage smallint
);

show tables; 使用前需选中

+---------------+
| Tables_in_stu |
+---------------+
| stu           |
+---------------+

详细的查询表

desc stu;
show create table stu;

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sno   | varchar(20) | YES  |     | NULL    |       |
| sname | varchar(20) | YES  |     | NULL    |       |
| ssex  | char(4)     | YES  |     | NULL    |       |
| sage  | smallint    | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

修改表的属性

  • 添加字段 alter table stu add sco int after ssex
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sno   | varchar(20) | YES  |     | NULL    |       |
| sname | varchar(20) | YES  |     | NULL    |       |
| ssex  | varchar(4)  | YES  |     | NULL    |       |
| sco   | int         | YES  |     | NULL    |       |
| sage  | smallint    | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
  • 修改字段 alter table stu modify sco float;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sno   | varchar(20) | YES  |     | NULL    |       |
| sname | varchar(20) | YES  |     | NULL    |       |
| ssex  | varchar(4)  | YES  |     | NULL    |       |
| sco   | float       | YES  |     | NULL    |       |
| sage  | smallint    | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
  • 重命名字段 alter table stu change ssex sex varchar(4);
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sno   | varchar(20) | YES  |     | NULL    |       |
| sname | varchar(20) | YES  |     | NULL    |       |
| sex   | varchar(4)  | YES  |     | NULL    |       |
| sco   | float       | YES  |     | NULL    |       |
| sage  | smallint    | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
  • 删除字段 alter table stu drop sco;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sno   | varchar(20) | YES  |     | NULL    |       |
| sname | varchar(20) | YES  |     | NULL    |       |
| sex   | varchar(4)  | YES  |     | NULL    |       |
| sage  | smallint    | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
  • 对表重命名 alter table stu rename stuinfo;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sno   | varchar(20) | YES  |     | NULL    |       |
| sname | varchar(20) | YES  |     | NULL    |       |
| sex   | varchar(4)  | YES  |     | NULL    |       |
| sage  | smallint    | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

复制表

  • 复制表的结构和索引 create table if not exists stu like stuinfo;
+---------------+
| Tables_in_stu |
+---------------+
| stu           |
| stuinfo       |
+---------------+

mysql> desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sno   | varchar(20) | YES  |     | NULL    |       |
| sname | varchar(20) | YES  |     | NULL    |       |
| sex   | varchar(4)  | YES  |     | NULL    |       |
| sage  | smallint    | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
  • 复制表中的数据 create table stunew as select sno, sname from stu;
+---------------+
| Tables_in_stu |
+---------------+
| stu           |
| stuinfo       |
| stunew        |
+---------------+

mysql> desc stunew;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sno   | varchar(20) | YES  |     | NULL    |       |
| sname | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

删除表

  • drop table if exists stunew;
+---------------+
| Tables_in_stu |
+---------------+
| stu           |
| stuinfo       |
+---------------+

设置默认字段

  • 设置默认字段alter table stu alter sex set default '男'
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sno   | varchar(20) | YES  |     | NULL    |       |
| sname | varchar(20) | YES  |     | NULL    |       |
| sex   | varchar(4)  | YES  |     | 男      |       |
| sage  | smallint    | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
  • 删除默认字段 alter table stu alter sex drop default
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sno   | varchar(20) | NO   | PRI | NULL    |       |
| sname | varchar(20) | YES  |     | NULL    |       |
| sex   | varchar(4)  | YES  |     | NULL    |       |
| sage  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

添加主键

create table stuinfo(
    sno varchar(20) not null,
    sname varchar(20),
    sex varchar(4),
    sage varchar(4),
    primary key(sno)
);

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sno   | varchar(20) | NO   | PRI | NULL    |       |
| sname | varchar(20) | YES  |     | NULL    |       |
| sex   | varchar(4)  | YES  |     | NULL    |       |
| sage  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

添加外键

  • 表级约束(在创建之后来修改)
    • on delet 和 on update 后面有很多参数可选
[on delete {cascade| restrict |set null | no action} ]
[on update {cascade| restrict |set null | no action} ]

alter table sc add foreign key(sno) references stu(sno)
on update restrict
on delet restrict;

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sno   | varchar(20) | NO   | PRI | NULL    |       |
| cno   | varchar(20) | NO   | PRI | NULL    |       |
| grade | int         | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
  • 列级约束
    • 在字段的后面加一个 references
create table sc (
  sno varchar(20) not null references stu(sno),
  cno varchar(20) not null,
  grade int default 0,
  primary key(sno, cno)
);

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sno   | varchar(20) | NO   | PRI | NULL    |       |
| cno   | varchar(20) | NO   | PRI | NULL    |       |
| grade | int         | YES  |     | 0       |       |
+-------+-------------+------+-----+---------+-------+
  • 完整性约束
create table sc(
  sno varchar(20) not null,
  cno varchar(20) not null,
  grade int not null default 0,
  primary key(sno, cno),
  constraint sc_student_fk foreign key(sno) references stu(sno)
);

+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                   |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sc    | CREATE TABLE `sc` (
  `sno` varchar(20) NOT NULL,
  `cno` varchar(20) NOT NULL,
  `grade` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`sno`,`cno`),
  CONSTRAINT `sc_stundent_fk` FOREIGN KEY (`sno`) REFERENCES `stu` (`sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

删除约束

  • 删除外键 alter table sc drop foreign key sc_stundent_fk;
  • 删除主键 alter table sc drop primary key;
  • 删除非空 alter table sc modify sno varchar(20) null
  • 删除默认值 alter table sc drop default
  • 删除唯一约束 alter table sc modify sno varchar(20)

表的增加

  • insert stu values('2018110801','张三', '男', 20);
+------------+--------+------+------+
| sno        | sname  | ssex | sage |
+------------+--------+------+------+
| 2018110801 | 张三   | 男   |   20 |
+------------+--------+------+------+
  • insert stu(sname, sno, sage, ssex) values('李四', '2018110802', 23, '女');
+------------+--------+------+------+
| sno        | sname  | ssex | sage |
+------------+--------+------+------+
| 2018110801 | 张三   | 男   |   20 |
| 2018110802 | 李四   | 女   |   23 |
+------------+--------+------+------+
  • insert stu values('2018110803', '王二麻', '男', 25), ('2018110804', '李二狗', '女', 28);
+------------+-----------+------+------+
| sno        | sname     | ssex | sage |
+------------+-----------+------+------+
| 2018110801 | 张三      | 男   |   20 |
| 2018110802 | 李四      | 女   |   23 |
| 2018110803 | 王二麻    | 男   |   25 |
| 2018110804 | 李二狗    | 女   |   28 |
+------------+-----------+------+------+

表的改动

  • update stu set sname='张三三' where sno = 2018110801;
+------------+-----------+------+------+
| sno        | sname     | ssex | sage |
+------------+-----------+------+------+
| 2018110801 | 张三三    | 男   |   20 |
| 2018110802 | 李四      | 女   |   23 |
| 2018110803 | 王二麻    | 男   |   25 |
| 2018110804 | 李二狗    | 女   |   28 |
+------------+-----------+------+------+

表的删除

  • delete from stu; 清空表的数据,但保留数据表的结构
  • truncate stu; 清空表的数据,保留数据表的结构

delete，drop，truncate 都有删除表的作用，区别在于：

1、delete 和 truncate 仅仅删除表数据，drop 连表数据和表结构一起删除，打个比方，delete 是单杀，truncate 是团灭，drop 是把电脑摔了。

2、delete 是 DML 语句，操作完以后如果没有不想提交事务还可以回滚，truncate 和 drop 是 DDL 语句，操作完马上生效，不能回滚，打个比方，delete 是发微信说分手，后悔还可以撤回，truncate 和 drop 是直接扇耳光说滚，不能反悔。

3、执行的速度上，drop>truncate>delete，打个比方，drop 是神舟火箭，truncate 是和谐号动车，delete 是自行车。

表的查询

  • select * from stu; 查询所有信息
+------------+-----------+------+------+
| sno        | sname     | ssex | sage |
+------------+-----------+------+------+
| 2018110801 | 张三      | 男   |   20 |
| 2018110802 | 李四      | 女   |   23 |
| 2018110803 | 王二麻    | 男   |   25 |
| 2018110804 | 李二狗    | 女   |   28 |
+------------+-----------+------+------+
  • select sno, sname from stu; 查询固定信息
+------------+-----------+
| sno        | sname     |
+------------+-----------+
| 2018110801 | 张三      |
| 2018110802 | 李四      |
| 2018110803 | 王二麻    |
| 2018110804 | 李二狗    |
+------------+-----------+
ANDORINNOT INBETWEEN
与或在不在范围

用条件来查询

  • select * from stu where sage > 20 and sage < 25;
+------------+--------+------+------+
| sno        | sname  | ssex | sage |
+------------+--------+------+------+
| 2018110802 | 李四   | 女   |   23 |
+------------+--------+------+------+
  • select * from stu where sage between 20 and 25; 包括边界
+------------+-----------+------+------+
| sno        | sname     | ssex | sage |
+------------+-----------+------+------+
| 2018110801 | 张三      | 男   |   20 |
| 2018110802 | 李四      | 女   |   23 |
| 2018110803 | 王二麻    | 男   |   25 |
+------------+-----------+------+------+
  • select * from stu where ssex in ('男');
+------------+-----------+------+------+
| sno        | sname     | ssex | sage |
+------------+-----------+------+------+
| 2018110801 | 张三      | 男   |   20 |
| 2018110803 | 王二麻    | 男   |   25 |
+------------+-----------+------+------+
  • select * from stu where ssex not in('男');
+------------+-----------+------+------+
| sno        | sname     | ssex | sage |
+------------+-----------+------+------+
| 2018110802 | 李四      | 女   |   23 |
| 2018110804 | 李二狗    | 女   |   28 |
+------------+-----------+------+------+

通配符

%-
匹配任意多个字符匹配一个任意字符
  • select * from stu where sno like '2018110_0_';
+------------+-----------+------+------+
| sno        | sname     | ssex | sage |
+------------+-----------+------+------+
| 2018110801 | 张三      | 男   |   20 |
| 2018110802 | 李四      | 女   |   23 |
| 2018110803 | 王二麻    | 男   |   25 |
| 2018110804 | 李二狗    | 女   |   28 |
+------------+-----------+------+------+
  • select * from stu where sno like '201811%';
+------------+-----------+------+------+
| sno        | sname     | ssex | sage |
+------------+-----------+------+------+
| 2018110801 | 张三      | 男   |   20 |
| 2018110802 | 李四      | 女   |   23 |
| 2018110803 | 王二麻    | 男   |   25 |
| 2018110804 | 李二狗    | 女   |   28 |
+------------+-----------+------+------+

对结果排序

ASCDESC
升序降序
  • select * from stu order by sage asc;
+------------+-----------+------+------+
| sno        | sname     | ssex | sage |
+------------+-----------+------+------+
| 2018110801 | 张三      | 男   |   20 |
| 2018110802 | 李四      | 女   |   23 |
| 2018110803 | 王二麻    | 男   |   25 |
| 2018110804 | 李二狗    | 女   |   28 |
+------------+-----------+------+------+
  • select * from stu order by sage desc;
+------------+-----------+------+------+
| sno        | sname     | ssex | sage |
+------------+-----------+------+------+
| 2018110804 | 李二狗    | 女   |   28 |
| 2018110803 | 王二麻    | 男   |   25 |
| 2018110802 | 李四      | 女   |   23 |
| 2018110801 | 张三      | 男   |   20 |
+------------+-----------+------+------+

SQL 内置函数

函数名COUNTSUMAVGMAXMIN
作用计数求和求平均值最大值最小值

其中 COUNT 函数可用于任何数据类型(因为它只是计数)，而另 4 个函数都只能对数字类数据类型做计算。

  • select min(sage), max(sage), sum(sage), avg(sage) from stu;
+-----------+-----------+-----------+-----------+
| min(sage) | max(sage) | sum(sage) | avg(sage) |
+-----------+-----------+-----------+-----------+
|        20 |        28 |        96 |   24.0000 |
+-----------+-----------+-----------+-----------+
  • select min(sage) as min, max(sage) as max, avg(sage) as avg, sum(sage) as sum from stu;
+------+------+---------+------+
| min  | max  | avg     | sum  |
+------+------+---------+------+
|   20 |   28 | 24.0000 |   96 |
+------+------+---------+------+

子查询

  • select * from sc where sno in (select sno from stu where sname like '_二麻');
+------------+--------------+-------+
| sno        | cno          | grade |
+------------+--------------+-------+
| 2018110803 | 大学物理     |    28 |
| 2018110803 | 大学英语     |    48 |
+------------+--------------+-------+

子查询还可以扩展到 3 层、4 层或更多层。

连接查询

  • select * from stu, sc; 两张表笛卡尔积
+------------+-----------+------+------+------------+--------------+-------+
| sno        | sname     | ssex | sage | sno        | cno          | grade |
+------------+-----------+------+------+------------+--------------+-------+
| 2018110804 | 李二狗    | 女   |   28 | 2018110801 | 大学物理     |    97 |
| 2018110803 | 王二麻    | 男   |   25 | 2018110801 | 大学物理     |    97 |
| 2018110802 | 李四      | 女   |   23 | 2018110801 | 大学物理     |    97 |
| 2018110801 | 张三      | 男   |   20 | 2018110801 | 大学物理     |    97 |
| 2018110804 | 李二狗    | 女   |   28 | 2018110802 | 大学物理     |    68 |
| 2018110803 | 王二麻    | 男   |   25 | 2018110802 | 大学物理     |    68 |
| 2018110802 | 李四      | 女   |   23 | 2018110802 | 大学物理     |    68 |
| 2018110801 | 张三      | 男   |   20 | 2018110802 | 大学物理     |    68 |
| 2018110804 | 李二狗    | 女   |   28 | 2018110803 | 大学物理     |    28 |
| 2018110803 | 王二麻    | 男   |   25 | 2018110803 | 大学物理     |    28 |
| 2018110802 | 李四      | 女   |   23 | 2018110803 | 大学物理     |    28 |
| 2018110801 | 张三      | 男   |   20 | 2018110803 | 大学物理     |    28 |
| 2018110804 | 李二狗    | 女   |   28 | 2018110804 | 大学物理     |    58 |
| 2018110803 | 王二麻    | 男   |   25 | 2018110804 | 大学物理     |    58 |
| 2018110802 | 李四      | 女   |   23 | 2018110804 | 大学物理     |    58 |
| 2018110801 | 张三      | 男   |   20 | 2018110804 | 大学物理     |    58 |
| 2018110804 | 李二狗    | 女   |   28 | 2018110801 | 大学英语     |    91 |
| 2018110803 | 王二麻    | 男   |   25 | 2018110801 | 大学英语     |    91 |
| 2018110802 | 李四      | 女   |   23 | 2018110801 | 大学英语     |    91 |
| 2018110801 | 张三      | 男   |   20 | 2018110801 | 大学英语     |    91 |
| 2018110804 | 李二狗    | 女   |   28 | 2018110802 | 大学英语     |    98 |
| 2018110803 | 王二麻    | 男   |   25 | 2018110802 | 大学英语     |    98 |
| 2018110802 | 李四      | 女   |   23 | 2018110802 | 大学英语     |    98 |
| 2018110801 | 张三      | 男   |   20 | 2018110802 | 大学英语     |    98 |
| 2018110804 | 李二狗    | 女   |   28 | 2018110803 | 大学英语     |    48 |
| 2018110803 | 王二麻    | 男   |   25 | 2018110803 | 大学英语     |    48 |
| 2018110802 | 李四      | 女   |   23 | 2018110803 | 大学英语     |    48 |
| 2018110801 | 张三      | 男   |   20 | 2018110803 | 大学英语     |    48 |
| 2018110804 | 李二狗    | 女   |   28 | 2018110804 | 大学英语     |    18 |
| 2018110803 | 王二麻    | 男   |   25 | 2018110804 | 大学英语     |    18 |
| 2018110802 | 李四      | 女   |   23 | 2018110804 | 大学英语     |    18 |
| 2018110801 | 张三      | 男   |   20 | 2018110804 | 大学英语     |    18 |
+------------+-----------+------+------+------------+--------------+-------+
  • select * from stu, sc where stu.sno = sc.sno;
+------------+-----------+------+------+------------+--------------+-------+
| sno        | sname     | ssex | sage | sno        | cno          | grade |
+------------+-----------+------+------+------------+--------------+-------+
| 2018110801 | 张三      | 男   |   20 | 2018110801 | 大学物理     |    97 |
| 2018110802 | 李四      | 女   |   23 | 2018110802 | 大学物理     |    68 |
| 2018110803 | 王二麻    | 男   |   25 | 2018110803 | 大学物理     |    28 |
| 2018110804 | 李二狗    | 女   |   28 | 2018110804 | 大学物理     |    58 |
| 2018110801 | 张三      | 男   |   20 | 2018110801 | 大学英语     |    91 |
| 2018110802 | 李四      | 女   |   23 | 2018110802 | 大学英语     |    98 |
| 2018110803 | 王二麻    | 男   |   25 | 2018110803 | 大学英语     |    48 |
| 2018110804 | 李二狗    | 女   |   28 | 2018110804 | 大学英语     |    18 |
+------------+-----------+------+------+------------+--------------+-------+
  • select * from stu join sc on stu.sno = sc.sno; 使用 join to 达到上一步一样的步骤
+------------+-----------+------+------+------------+--------------+-------+
| sno        | sname     | ssex | sage | sno        | cno          | grade |
+-------男   |   20 | 2018110801 | 大学英语     |    91 |
| 2018110802 | 李四      | 女   |   23 | 2018110802 | 大学英语     |    98 |
| 2018110803 | 王二麻    | 男   |   25 | 2018110803 | 大学英语     |    48 |
| 2018110804 | 李二狗    | 女   |   28 | 2018110804 | 大学英语     |    18 |
+------------+-----------+------+------+------------+--------------+-------+

创建索引

普通索引

普通索引是最基本的索引, 没有任何限制.他有以下几种方式创建

  • create index indexname on tablename(username(length)); 基本方式
  • alter table tablename add index indexname(columName); 添加索引
  • create table tablename( id varchar(20) primary key, name varchar(10) not null, index index_name (name(10)) ); 创建表时直接创建

删除索引

drop index [indexname] on mytable; alter table tablename drop index c

唯一索引

它与普通索引差不多,只不过唯一索引必须值唯一,但允许有空值,如果是组合索引,则值必须唯一.创建方式和上面的差不多

  • create unique index indexname on mytable(username(length));
  • alter table tablename add unique index [indexname] (username(length))
  • create table tablename( id varchar(20) primary key, name varchar(10) not null, unique index index_name (name(10)) );

显示索引

show index from tablename

视图

创建视图

  • create view view_stu as select * from stu;

删除视图

  • drop view if exists view_stu;

视图定以

  • desc view_stu

修改视图

  • create or replace view view_stu as select sno, sname from stu;
  • alter view view_stu as select sno, sname from stu;
mysql

超星学习通签到

利用Caddy方向代理Rocket.Chat