MySQL基本语句

MySQL基础语句

1.操作数据库（CRUD）

• C(Create):创建

   创建数据库：create databases;
   创建数据库：判断不存在，再创建：create database if not exists 数据库名称;
   创建数据库：并指定字符集：create database 数据库名称 character set 字符集合;
  

• R(Retrieve):查询

   查询所有数据库名称：show databases;
   查看某个数据库的字符集/查询某个数据库的创建语句：show create database 数据库名称;
  

• U(Update):修改

   修改数据库的字符集：alter database 数据库名称 character set 字符集名称;
  

• D(Delete):删除

   删除数据库：drop database 数据库名称;
   判断数据库存在，存在再删除：drop database if exists 数据库;
  

• 使用数据库

   查询当前正在使用的数据库名称：select database();
   使用数据库：use 数据库名称;
  

2.操作表

• C(Create):创建

   语法：create table 表名{
           列名1  数据类型1,
           列名2  数据类型2,
           ......
           列名n  数据类型n};
   数据类型：int  整数类型：age int
            double 小数类型：score double(4,1)   相当于3位整数部分、1位小数部分，一共4个数字的数
            date 日期，只包含年月日：yyyy-mm-dd
            datetime 日期，年月日时分秒：yyyy-mm-dd hh:mm:ss
            timestamp  : yyyy-mm-dd hh:mm:ss       不赋值或null，则自动默认为系统时间
            varchar  字符串：name varchar(20);   姓名最大20个字符
  

• R(Retrieve):查询

   查询某个数据库中所有的表名称：show tables;
   查询表结构：desc 表名;
  

• U(Update):修改

   修改表名：alter table 表名 rename to 新的 name;
   修改表的字符集：alter table 表名 character set 字符集名称;
   添加一列：alter table 表名 add 列名 数据类型;
   修改名称/类型：alter table 表名 change 列名 新列名 新数据类型;
                alter table 表名 modify 列名 新数据类型;
   删除列：alter table 表名 drop 列名;
  

• D(Delete):删除

   删除表名：drop table 表名;
   判断表存在，存在再删除：drop table if exists 表名;
  

DML：增删改表中数据

1.添加数据：

    语法：insert into表名(列名1,列名 2,...列名n) values(值1,值2,...值n);
    注意：
        1.列名和值要一一对应
        2.如果表名后，不定义列名，则默认给所有列添加值
        3.除了数字类型，其他类型需要使用引号（单双引号都可以）引起来

2.删除数据：

    语法：
    delete from 表名[where 条件]
    注意：
    1.如果不加条件，则删除表中所有记录
    2.如果要删除所有记录：delete table 表名;   不推荐使用，有多少条记录就会执行多少次删除操作
    truncate table 表名;    例:TRUNCATE table student; 推荐使用，效率更高。先删除表，然后再创建一张一样的表。

3.修改数据：

    语法：
        update 表名 set 列名1 = 值1,列名2 = 值2，...[where 条件];
    注意：1.如果不加任何条件，则会将表中所有记录全部修改

DQL:查询表中的记录

    select * from 表名;
    
    语法：
    select 
        字段列表
    from 
        表名列表
    where
        条件列表
    group by
        分组字段
    having
        分组之后的条件
    order by
        排序
    limit
        分页限定

2.基础查询

    1.多个字段的查询 
        select 字段名1，字段2...from 表名;
        查询所有字段：
        select * from 表名;
    2.去除重复
    
        distinct
    3.计算列
        *四则运算
        *ifnull(表达式1，表达式2)：null参与的计算，计算结果都为null
        表达式1：哪个字段需要判断是否为null

    4.起别名
        关键字：as（可省略）

3.条件查询

    1.where子句后面跟条件
    2.运算符：
    在数据库原理里：图

DQL：查询语句

1.排序查询

    语法：order by 子句
        order by 排序字段1 排序方式1， 排序字段2，排序方式2;
    排序方式：
        ASC: 升序（默认）
        DESC：降序   例：SELECT * FROM class ORDER BY `班号` DESC;
    注意：若有多个排序条件，则当前边的条件值一样时，才会判断第二条件

2.聚合函数：将一列数据作为一个整体，进行纵向运算

    1.count:计算个数   例：SELECT COUNT(*) AS '学生总数' FROM student;
        1.一般选择非空的列：主键
        2.count(*)
    2.max:计算最大值
    3.min：计算最小值
    4.sum：计算和
    5.avg：计算平均值
    
注意：聚合函数的计算，排除null
    解决方法：使用ifnull函数

3.分组查询：

    1.语法：group by 分组字段;
    注意：
        1.分组之后查询的字段：分组字段、聚合函数 
        2.where和having的区别：
            a.where在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来
            b.where后不可以跟聚合函数，having可以进行聚合函数的判断

4.分页查询

1.语法：
     limit 开始的索引，每页查询的条数
2.公式：开始的索引=（当前的页码-1）*每页显示的条数

题目：
1 建立数据库名为SCHOOL并激活当前新建的数据库

2 建表：在定义中声明

    1）每个表要有主键
    2）学生的性别只能取‘男’或者‘女’
    3）学生姓名不能为空
    4）表CLASS中建立外键系名，关联表DEPARTMENT中的系名
    5）系代号不能重复且不能为空

（1）学生表STUDENT（学号，姓名，性别，生源地，出生日期）

（2）班级表CLASS（班号，专业名，系名，入学年份）

（3）系DEPARTMENT（系号，系名）

3 插入如下数据

CALSS（101，软件，02，2003；
102，微电子，02，2004）

STUDENT（8101，张三，男，101，山东，1984；
8102，钱四，女，121，济南，1984；
8103，王玲，女，131，福建，1985；
8105，李飞，男，102，河北，1985）

DEPARTMENT（02，计算机；
03，数学；
04，化学）

4 完成以下的查询功能

（1）通过对学生信息表的查询，查询出所有学生的学号，姓名和性别

    SELECT `学号`, `姓名`,`性别`FROM student;

（2）通过对学生信息表的查询，查询来自福建的学生的记录

    SELECT `学号`, `姓名`,`性别`FROM student WHERE `生源地`='福建';

（3）在查询语句中，查询出学生姓名并计算出学生的年龄大小

    SELECT `姓名`,(YEAR(NOW())-出生日期) AS '年龄' FROM student;

（4）用模糊查询出姓钱的女生记录

    SELECT *FROM student WHERE `性别`='女'AND`姓名`LIKE'钱%';

（5）在查询语句中使用聚合函数计算出所有学生总数起别名为学生人数

    SELECT COUNT(*) AS '学生人数' FROM student;

（6）查询所有班级的信息，并用班级号进行降序排序

    SELECT * FROM class ORDER BY `班号` DESC;

（7）更新学号为8103的这位学生的姓名为张玲

    UPDATE student SET `姓名` = '张玲'WHERE `学号`=8103;

（8）查找出学号在8101到8103的学生信息

SELECT * FROM student WHERE `学号`>=8101 AND `学号`<=8103;
SELECT * FROM student WHERE `学号` BETWEEN 8101 and 8103;

（9）删除姓名为李飞的学生记录

    DELETE  FROM student WHERE `姓名` = '李飞';

（10）删除数据表STUDENT

    drop table if exists student;

约束

概念：对表中的数据进行限定，保证数据的正确性、有效性和完整性
分类：

1.主键约束：primary key
2.非空约束：not null
3.唯一约束：unique
4.外键约束：foreign key

非空约束：not null

1.创建表时添加约束
    create table stu(
    id int,
    name VARCHAR(20) not null 
    );
2.创建表后，添加非空约束

    ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;
3.删除name的非空约束

    ALTER table stu MODIFY NAME VARCHAR(20);

唯一约束：unique，值不能重复

创建表时，添加唯一约束
create table stu(
id int,
phone_number VARCHAR(2) UNIQUE     --添加了唯一约束
);
注意MySQL中，唯一约束限定的列的值可以有 多个null

删除唯一约束
 ALTER TABLE stu MODIFY phone_number VARCHAR (20);
ALTER TABLE stu DROP INDEX phone_number;

在创建表后，添加唯一约束
ALTER TABLE stu MODIFY phone_number VARCHAR(2) UNIQUE;

主键约束：primary key

    1.注意：
        -含义：非空且唯一
        -一张表只能有一个字段为主键
        -主键就是表中记录唯一标识
    
    2.在创建表时，添加主键约束
        create table stu(
        id int primary key,  -- 给id添加主键约束
        name varchar(20)
        );
    3.删除主键
        -- 错误ALTER TABLE stu MODIFY id INT;
        ALTER TABLE stu DROP PRIMARY KEY;

   4.创建完表后，添加主键
        ALTER TABLE stu MODIFY id INT PRIMARY KEY;
    5.自动增长
       概念：如果某一列是数值类型的，使用auto_increment可以来完成值的自动增长
    create table stu(
        id int primary key auto_increment, 
        --给id添加主键约束
        name varchar(20)
    );

约束

概念：对表中的数据进行限定，保证数据的正确性、有效性和完整性
分类：
1.主键约束：primary key
2.非空约束：not null
3.唯一约束：unique
4.外键约束：foreign key

非空约束：not null

1.创建表时添加约束

create TABLE stu1(
 id1 int,
 name VARCHAR(20) NOT NULL
);

2.创建表后，添加非空约束

ALTER table stu1 MODIFY name VARCHAR(20) not null;

3.删除name的非空约束

ALTER table stu1 MODIFY name VARCHAR(20);

唯一约束：unique

1.添加唯一约束

create TABLE stu1(
 id1 int,
 phone_number VARCHAR(20) UNIQUE
);

2.删除唯一约束

ALTER TABLE stu1 DROP INDEX phone_number;

3.在创建表后添加唯一约束

ALTER TABLE stu1 MODIFY phone_number VARCHAR(20) UNIQUE;

主键约束：primary key

1.注意：

含义：非空且唯一
 一张表只能有一个字段为主键
主键就是表中记录的唯一标识

2.在创建表时，添加主键约束

create table stu(
id int primary key,-- 给id添加主键约束
name varchar(20)
);

3.删除主键

ALTER TABLE stu1 drop PRIMARY KEY; 

4.创建完表后，添加主键

alter table stu1 MODIFY id int primary key;

5.自动增长：

1.概念：如果某一列是数值类型的，使用auto_increment可以来完成值的自动增长
2.在创造表时，添加主键约束，并且完成主键自增长
        create table stu1(
        id int primary key auto_increment,  -- 给id添加主键约束
        name varchar(20)
        );
3.删除自动增长
        alter table stu1 MODIFY id int;
4. 添加自动增长
        alter table stu1 modify id int auto_increment;

外键约束：foreign key

1.在创建表时，可以添加外键
       语法：
        create table 表名(
        ...
        外键列
        constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
        )
        
2. 删除外键
		ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

3. 创建表之后，添加外键
		ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
	
	
4. 级联操作
		1. 添加级联操作
			语法：ALTER TABLE 表名 ADD CONSTRAINT 外键名称 
					FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE  ;
		2. 分类：
			1. 级联更新：ON UPDATE CASCADE 
			2. 级联删除：ON DELETE CASCADE 

数据库的设计

1.多表之间的关系

    1.一对一：

2.数据库设计的范式