Mysql学习笔记

基本概念

数据库：

DataBase，简称DB：是按照一定格式存储数据的一些文件的组合。

顾名思义，存储数据的仓库，实际上就是一堆文件。这些文件中存储了具有特定格式的数据。

数据库管理系统：
DataBaseManagement，简称DBMS。

数据库管理系统是专门用来管理数据库中数据的，数据库管理系统可以对数据库当中的数据进行增删改查。

常见的数据库管理系统：MySQL、Oracle、MS SglServer、DB2、sybase等....

SOL：
是一个结构化查询语言。

程序员编写SOL语句，然后通过DBMS负责执行SOL语句，最终来完成数据库中数据的增删改查操作。

SQL是一种通用的标准语言，不只是可以在MySQL中使用，还可以在Oracle等其他数据库管理系统中使用。

三者之间的关系：DBMS  --- 执行 ---> SOL --- 操作 ---> DB 。

表：

数据库当中最基本的单元是表：table。

数据库当中是以表格的形式表示数据的。因为表比较直观。

任何一张表都有行和列：

        行（row）：被称为数据/记录。
        列（column）：被称为字段。（姓名字段、性别字段、年龄字段）

  
 #字段

  姓名   年龄   性别（列/字段）

------------------------
  小明   18     男    #数据

字段由字段名、数据类型、约束等属性组成。

字段名就是一个自己给字段起的一个名字。

数据类型：不同的数据类型需要使用不同的数据类型来定义，其中包括数字类型、时间和日期类型、字符串类型。

约束：为了防止数据表插入错误的数据，Mysql定义了一些维护数据库完整性的规则，即表的约束。（其实就是为了插入的数据达到自己想要的效果而设定的一些规则或者说限制条件）

Mysql的数据类型：

一、数字类型

1.整数类型

若要使用无符号数据类型，需要在数据类型右边加上unsigned关键字来修饰，例如：int unsigned 表示无符号int类型。

 数据类型右边使用小括号数字标注显示宽度，如上图int(10)、varchar(10)，表示显示宽度为为10。但是显示宽度与取值范围是无关的，若数值的位数小于显示宽度，则填充空格，若大于显示宽度，则不影响显示结果。

mysql>create table student(
    ->id int(3),
    -age int(3)
    -);

把表创建成功之后，分别给id、age添加数据1234、12
    id      age
--------------------
   1234     12     #数值的位数大于显示宽度时并不影响显示结果 如：1234
                   #数值的位数小于显示宽度时，会填充空格如：12

PS：表的主键推荐使用整数数据类型

2.浮点数类型

3.定点数类型

  decimal定点数类型通过decimal（M,D）设置位数和精度，其中M表示数字总位数（不包括“.”和“-”），最大值为65，默认值为10；D表示小数点后的位数，最大值为30，默认值为0。

4.BIT类型

BIT(位)类型用于存储二进制数据，语法为BIT（位），位表示位数，范围为1~64

二、时间和日期类型

1.year类型

（1）使用4位字符串或者数字表示都行

2.date类型

表示日期值，不包含时间部分：

create table my_date (d date)      #设置d字段的数据类型为date
insert into my_date values('2020-01-21'或者'20200121')   #插入日期数据
insert into my_date values('YY-MM-DD'或者'YYMMDD')
insert into my_date values(YY-MM-DD或者YYMMDD)       #这三种格式输入日期都行

PS：（1）通过"select current_date;"或者"select now();"可查看当前日期。

        （2）日期中的分隔符“-”可以用 .  ,  /  符号来代替。

3.time类型s

 三、字符串类型

表的约束

 什么是约束以及约束的作用：

    约束实际上就是表中数据的限制条件；为了防止数据表插入错误的数据，Mysql定义了一些维护数据库完整性的规则，即表的约束。（其实就是为了插入的数据达到自己想要的效果而设定的一些规则或者说限制条件）约束保证数据的完整性和一致性

    约束分为表级约束和列级约束

约束类型：

NOT NULL （非空约束）

PRIMARY KEY（主键约束）

UNIQUE KEY（唯一约束）

DEFAULT（默认约束）

FOREIGN（外键约束）

非空约束（not  null）：

创建表，给字段添加非空约束（创建用户表，用户名不能为空），如果没有插入name字段的数据，就会报错。

  使用 not null 约束的字段不能为null值，必须给定具体数值

mysql> create table t_user(
    -> id int(10),
    -> name varchar(32) not null
    -> );
Query OK, 0 rows affected (0.08 sec)

主键约束(primary key)

表设计时一定要有主键

主键约束与“not null unique”区别
给某个字段添加主键约束之后，该字段不能重复也不能为空，效果和"not null 、unique"约束相同，但是本质不同。主键约束除了可以做到"not null、unique"之外，还会默认添加"索引——index"

按主键约束的字段数量分类

无论是单一主键还是复合主键，一张表主键约束只能有一个(约束只能有一个，但可以作用到好几个字段)

创建双主键：

mysql> create table t_user(
    -> id int(10),
    -> name varchar(32) not null,
    -> email varchar(128) unique,
    -> primary key(id,name)      #创建双主键
    -> );
Query OK, 0 rows affected (0.05 sec)

唯一性约束

unique约束的字段，具有唯一性，不可重复，但可以为null

创建约束——列级约束

mysql> create table t_user(
    -> id int(10),
    -> name varchar(32) not null,
    -> email varchar(128) unique      #列级约束
    -> );
Query OK, 0 rows affected (0.03 sec)

表级约束

mysql> create table t_user(
    -> id int(10),
    -> name varchar(32) not null,
    -> email varchar(128），
	-> unique(email)       ##表级约束
    -> );

使用表级约束，给多个字段联合约束

联合约束，表示两个或以上的字段同时与另一条记录相等，则报错

mysql> create table t_user(
    -> id int(10),
    -> name varchar(32) not null,
    -> email varchar(128),
    -> unique(name,email)
    -> );
Query OK, 0 rows affected (0.01 sec)

插入第一条数据：
mysql> insert into t_user(id,name,email) values(1,'xxx','qq.com');
Query OK, 1 row affected (0.05 sec)    #没问题，不会报错
 
插入第二条数据：
mysql> insert into t_user(id,name,email) values(2,'mmm','qq.com');
Query OK, 1 row affected (0.05 sec)    #也没问题，不会报错 插入第二条数据如果是与联合字段中的一 
                                        条相同或者另一条相同，也是可以的

插入第三条数据：
mysql> insert into t_user(id,name,email) values(3,'mmm','qq.com');
ERROR 1062 (23000): Duplicate entry 'mmm-qq.com' for key 'name'      #此时报错，两条数据同时相同时，则不行。
                                                  

表级约束可以给约束起名字(方便以后通过这个名字来删除这个约束)

mysql> create table t_user(
    -> id int(10),
    -> name varchar(32) not null,
    -> email varchar(128),
    -> constraint t_user_email_unique unique(email)
    -> );
Query OK, 0 rows affected (0.06 sec)      #constraint是约束关键字，t_user_email_unique自己取的名字

自动增长（auto_increment）

  在MySQL数据库提供了一个自增的数字，专门用来自动生成主键值，主键值不用用户维护，自动生成，自增数从1开始，以1递增(auto_increment)

使用自动增长（auto_increment）时，需要注意的：

（1）一个表只能有一个自动增长值，该字段的数据类型必须是整数类型，且必须为键，如unique key、primary key。

（2）若为自动增长字段插入null、0、default 或者插入时省略该字段，则该字段会使用自动增长值；若插入的是一个具体值，则不会使用自动增长值。

（3）自动增长值从1开始自增，每次加1。若插入的值大于自动增长值，则下次会自动使用最大值加1；若插入的值小于自动增长值，则不会对自动增长值产生影响。

（4）使用delete 删除记录时，自动增长值时不会减小或填报空缺。

mysql> create table t_user(
    -> id int(10) primary key auto_increment,
    -> name varchar(32) not null
    -> );
Query OK, 0 rows affected (0.03 sec)

插入两行数据：
mysql> insert into t_user(name) values('jay');
Query OK, 1 row affected (0.04 sec)

mysql> insert into t_user(name) values('man');
Query OK, 1 row affected (0.00 sec)


mysql> select * from t_user;
+----+------+
| id | name |
+----+------+
|  1 | jay  |
|  2 | man  |
+----+------+
2 rows in set (0.00 sec)

默认约束（default）

语法：字段名 数据类型 default 默认值；

需要注意的是，blob、text

mysql>create table my_default(
    ->name varchar(20)
    ->age int unsigned default 18   #默认值为18
    ->);
  
desc my_default;
 输出结果：
name  null
age   18    #默认约束，默认值为18

外键约束(foreign key)FK

什么是外键

若有两个表A、B，id是A的主键，而B中也有id字段，则id就是表B的外键，外键约束主要用来维护两个表之间数据的一致性。（A为基本表，B为信息表），只能是表级定义。

• 单一外键：给一个字段添加外键约束
• 复合外键：给多个字段联合添加一个外键约束

注意问题：

一张表可以有多个外键字段（与主键不同）

主表必须定义主键

外键中列的数目必须和主表的主键中列的数目相同。
外键中列的数据类型必须和主表主键中对应列的数据类型相同。

外表和主表使用的引擎必须是InnoDB

主表必须已经存在于数据库中，或者是当前正在创建的表 (如果是后一种情况，则主表与从表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性)
主键不能包含空值，但允许在外键中出现空值。(只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的)

 在创建表是添加外键

 添加外键约束的语法格式

->[constraint <外键名>] FOREIGN KEY 字段名 [，字段名2，…] #constraint<外键名>是可选的，这个的意 
                                                       思就是给你建立的外键起一个名字，方便你操 
                                                       作
references <主表名> 主键列1 [，主键列2，…]
->on delete restrict on update cascade   #利用 on delete 指定从表此关联字段含有数据时，拒绝主表 
                                          执行操作，利用on update 设置主表执行更新操作时，从表 
                                          的相关字段也执行更新操作。 

其实基本语法就是：foreign key +外表字段名+ references +主表字段名

示例

##创建一个表
mysql> CREATE TABLE tb_dept1
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(22) NOT NULL,
    -> location VARCHAR(50)
    -> );

创建数据表 tb_emp6，并在表 tb_emp6 上创建外键约束，让它的键 deptId 作为外键关联到表 tb_dept1 的主键 id，SQL 语句和运行结果如下所示。
mysql> CREATE TABLE tb_emp6
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT,
    -> CONSTRAINT fk_emp_dept1
    -> FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
    -> );

在修改表时，添加外键

->ALTER TABLE <数据表名> ADD CONSTRAINT <外键名>  #就是比之前的多了这么一个修改的语句，
                       alter table +要添加外表的表名+add constraint +自己给这个外键约束起的名字

FOREIGN KEY(<外表列名>) REFERENCES <主表名> (<列名>);

mysql> ALTER TABLE tb_emp2
    -> ADD CONSTRAINT fk_tb_dept1
    -> FOREIGN KEY(deptId)
    -> REFERENCES tb_dept1(id);

mysql> SHOW CREATE TABLE tb_emp2\G
*************************** 1. row ***************************
       Table: tb_emp2
Create Table: CREATE TABLE `tb_emp2` (
  `id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  `deptId` int(11) DEFAULT NULL,
  `salary` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_tb_dept1` (`deptId`),
  CONSTRAINT `fk_tb_dept1` FOREIGN KEY (`deptId`) REFERENCES `tb_dept1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312

删除外键约束

当一个表中不需要外键约束时，就需要从表中将其删除。外键一旦删除，就会解除主表和从表间的关联关系。

删除外键约束的语法格式如下所示：

ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>; #这个表名就是外表名，外键约束名是自己给起的名 
                                                  字

SQL语句

SQL语句类型

DQL：
        数据查询语言（凡是带有 select 关键字的都是查询语句）

    DML：
        数据操作语言（凡是对表当中的数据进行增删改的都是DML）
        insert 增
        delete 删
        update 改
        这个主要是操作表中的数据data。

    DDL：
        数据定义语言(凡是带有create、drop、alter的都是DDL)
        DDL主要操作的是表的结构。不是表中的数据。（如新增数据库、新增表格）
        create：新建，等同于增
        drop：删除
        alter：修改
        这个增删改和DML不同，这个主要是对表结构进行操作。

    TCL：
        事务控制语言。
        包括：
                事务提交：commit;
                事务回滚：rollback;

    DCL：
        是数据控制语言。
        例如：授权grant、撤销权限revoke....

MYSQL常用命令语句

操作数据库的语句

查看MySQL有多少个数据库：show databases;

 ##MySQL默认自带四个数据库

选择使用哪个数据库：use xxx;

mysql> use test;
    Database changed  #使用名字为test的数据库

创建一个新的数据库：create database xxx; 或 create database if not exists;

                                    #为了避免数据库已存在的错误，可在数据库名字前加上if no exists，表示创建的数据库不存在时执行创建操作，否则忽略此操作。

mysql> create database if not exists yjx;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| student            |
| test               |
| y_schooldb         |
| yjx                |
+--------------------+
8 rows in set (0.00 sec)

查看数据库里有多少个表：show tables;     (首先要先进入一个数据库)

mysql> use student;
Database changed
mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| information       |
+-------------------+
1 row in set (0.00 sec)

 查看自己当前使用的是哪个数据库：select database();

mysql> select database();
+------------+
| database() |
+------------+
| student    |
+------------+
1 row in set (0.00 sec)

删除数据库：drop database 数据库名字；

查看自己的MySQL版本：select version();

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.5-m8  |
+-----------+
1 row in set (0.00 sec)

操作数据表的语句

查看表中的所有内容：select * from 表名；

mysql> select * from student;
+-------+--------+------+------+---------+
| id    | name   | sex  | age  | classNo |
+-------+--------+------+------+---------+
| 21225 | 小明   |    2 |   18 |       4 |
+-------+--------+------+------+---------+
1 row in set (0.01 sec)

查看数据表的字段信息：desc 表名;

#（desc是describe的缩写，可 describe 表名）

mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | NO   | PRI | NULL    |       |
| name    | varchar(50) | YES  |     | NULL    |       |
| sex     | int(11)     | YES  |     | NULL    |       |
| age     | int(11)     | YES  |     | NULL    |       |
| classNo | int(11)     | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

查看数据表的创建语句：show create table 表名；

mysql> show create table student;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                 |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `id` int(11) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `sex` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `classNo` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

查看数据表结构：show [ full ] columns from 数据表名 [ from 数据库名];

#可选项full表示显示详细内容，在不添加的情况下查询结果与desc的结果相同，在添加full选项时此语句不仅可以查看到desc语句查看的信息，还可以查看到字段的权限、comment字段的注释信息

#可选项from 数据库名表示可以查看任意数据库下的数据表结构信息，即不用先进入到那个数据库，可以直接查询。

mysql> show full columns from student;
+---------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field   | Type        | Collation       | Null | Key | Default | Extra | Privileges                      | Comment |
+---------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| id      | int(11)     | NULL            | NO   | PRI | NULL    |       | select,insert,update,references |         |
| name    | varchar(50) | utf8_general_ci | YES  |     | NULL    |       | select,insert,update,references |         |
| sex     | int(11)     | NULL            | YES  |     | NULL    |       | select,insert,update,references |         |
| age     | int(11)     | NULL            | YES  |     | NULL    |       | select,insert,update,references |         |
| classNo | int(11)     | NULL            | YES  |     | NULL    |       | select,insert,update,references |         |
+---------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
5 rows in set (0.01 sec)

修改数据表名：alter table 旧表名 rename [to/as] 新表名；

                             或rename table 旧表名 to 新表名;[,旧表名2 to 新表名2］...

#使用语法1时，可以直接使用renamed或者在其后添加to/as

而使用renamed时必须使用to,此语法可以同时修改多个数据表的名称

mysql> alter table student rename students;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| comment_list   |
| goodstudent    |
| my_default     |
| my_goods       |
| my_int         |
| my_primary     |
| students       |
| user           |
| yourdata       |
+----------------+
9 rows in set (0.00 sec)

删除数据表：drop table [if exists] 数据表名1[数据表名2]...；

修改表结构（字段）语句：alter table...

修改字段名：alter table  数据表名 change 旧字段名 新字段名 字段类型；

mysql> alter table students change sex see varchar(10);
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

修改字段类型：alter table 数据表名 modify 字段名 新类型;

mysql> alter table students modify id int(10); #修改显示宽度也是修改类型了
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table students modify id varchar(10);
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

修改字段的位置：alter table 数据表名 modify 字段名1 数据类型 [first/after 字段名2]；

#first 表示将字段名1的字段调整为数据表的第一个字段位置

#after 表示将字段名1的字段插入到字段名2的后面

mysql> alter table students modify id varchar(10) after name;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> desc students;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(50) | YES  |     | NULL    |       |
| id      | varchar(10) | NO   | PRI |         |       |
| see     | varchar(10) | YES  |     | NULL    |       |
| age     | int(11)     | YES  |     | NULL    |       |
| classNo | int(11)     | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

删除字段：alter table 数据表名 drop 字段名；

mysql> alter table students drop calssNo;
ERROR 1091 (42000): Can't DROP 'calssNo'; check that column/key exists
mysql> alter table students drop age;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> desc students;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(50) | YES  |     | NULL    |       |
| id      | varchar(10) | NO   | PRI |         |       |
| see     | varchar(10) | YES  |     | NULL    |       |
| classNo | int(11)     | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

数据操作

添加数据：insert 数据表名...

为所有字段添加数据：insert 数据表名 values (值1，值2...)；

#多个值之间用逗号隔开，其顺序跟表的字段顺序一样的

mysql> select * from students;
+--------+-------+------+---------+
| name   | id    | see  | classNo |
+--------+-------+------+---------+
| 小明   | 21225 | 2    |       4 |
+--------+-------+------+---------+
1 row in set (0.00 sec)
mysql> insert students values (
    -> '小红',21223,3,4);
Query OK, 1 row affected (0.00 sec)

mysql> select * from students;
+--------+-------+------+---------+
| name   | id    | see  | classNo |
+--------+-------+------+---------+
| 小红   | 21223 | 3    |       4 |
| 小明   | 21225 | 2    |       4 |
+--------+-------+------+---------+
2 rows in set (0.00 sec)

为部分字段添加数据：insert 数据表名 （字段名1，字段名2） values （值1，值2）；

一次添加多行数据：insert 数据表名 values （值1，值2），（值3，值4）；

查询数据

查询表中全部数据：select * from 数据表名；

#星号查询的缺点：
        1、效率低。（程序会先将*号转换为所有字段的名字）
        2、可读性差。
在实际开发中不建议。

查询表中部分字段：select (字段名1，字段名2...) from 数据表名；

简单的条件查询：select * from 数据表名 where 字段名=值； 

给查询的列起别名：字段 as 别名；

  mysql> select deptno,dname as deptname from dept; #这里是将dname改为deptname
      +--------+------------+
      | deptno | deptname   |
      +--------+------------+
      |     10 | ACCOUNTING |
      |     20 | RESEARCH   |
      |     30 | SALES      |
      |     40 | OPERATIONS |
      +--------+------------+

as是可以省略的：select deptno,dname deptname from dept;   #直接用空格隔开，此语句与上句作用一样的

注意：只是将显示的查询结果中的dname显示为deptname，原表该字段名还是叫：dname。

     as是可以省略的

当你想改的别名有空格或者有中文的时候，需要加单引号或者双引号把别名括起来

mysql> select deptno,dname dept name from dept;    //程序报错！！！

mysql> select deptno,dname 'dept name' from dept;     //加单引号便没问题

mysql> select deptno,dname "dept name" from dept;     //加双引号便没问题

修改数据：update 数据表名 set 字段名1=值1....[where 条件表达式]；

删除数据：delete from 数据表名 [条件表达式]； 

注意

mysql是不见“;”不执行，“;”表示结束！

可回车换行继续输入代码。

可以通过 \c 来结束未完成的SQL语句。（或Ctrl+c）

在所有的数据库当中，字符串统一使用单引号括起来，单引号是标准，双引号在oracle数据库中用不了，但是在mysql中可以使用。为了语句的通用，尽量使用单引号。

　　

　　

---