MySql-基础语法练习

mysql
定义DDL
操作DML
控制DCL
查询DQL
一，三范式
1.数据库表的每一列值都不可再分
2，满足第二范式必须先满足第一范式，如果表是单主键，每一列都必须依赖主键。如果是复合主键，除主键外的每一列都必须完全依赖主键，不能仅部分依赖
3，满足二才能满足三。除主键外的列必须直接依赖主键，不能间接依赖，也就是说非主键的列不能相互依赖。
二，数据类型
1，整数类型
TINYINT,SMALUNT ,MEDIUMINT,INT,BIGINT
2,浮点数和定点数
FLOAT,DOUBLE,DECIMAL(M,D),M表示数据长度，D表示小数点后长度
3，字符串类型
CHAR和VECHAR
4,文本类型
tinytext
TEXT
MEDIUMTEXT
LONGTEXT
5,日期时间类型
YEAR,DATE,TIME,DATETIME,TIMESTAMP
6,二进制类型（图片，PDF文档）
TINYBLOB
BLOB
MEDIUMBLOB
LONGBLOB

完整性约束

除了数据库对建表语句的约束，我们在对库、表、列命名时也要有一定得规范，虽然不一定报错，但是违背规范对于后续的开发和维护都可能造成影响。常见规范总结如下：
（1）采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线’‘组成，命名简洁明确，多个单词用下划线’'分隔
（2）全部小写或全部大写（推荐小写）
（3）禁止使用数据库关键字，如：time ，datetime，password等
（4）表和字段名称要容易理解，不应该取得太长（一般不超过三个英文单词）
（5）表的名称一般使用名词或者动宾短语例如：create_time
（6）用单数形式表示名称，例如，使用 student，而不是 students
（7）表和字段必须填写描述信息
（8）列命名时，不要重复表的名称如：student_name

三，数据库，数据表操作

create database 数据库名称；
create database db1；
show create database db1；
drop database db1；#删除数据库 

1.查看自己mysql中已有的库
答案：show databases;

2.在自己的mysql中创建一个school_system库
答案：create database school_system

3.进入到school_system库中
答案：use school_system

4.通过navice界面，查看school_system库是否存在

5.删除school_system库
答案：drop database school_system;

1.创建表：
create table 表名(
列名1 类型 [<完整性约束>],
列名1 类型 [<完整性约束>],
… … ) CHARSET = 指定字符集;
示例：

CREATE TABLE `student_demo` (
	`id`            INT (11)      NOT NULL     AUTO_INCREMENT                       COMMENT '主键',
	`name`          varchar (50)    NULL                                          COMMENT '姓名',
	`age`           INT (3)         NULL                                          COMMENT '年龄',
	`number`        INT (30)        NULL                                          COMMENT '学号',
	`class_id`      CHAR (36)       NULL                                          COMMENT '班级id',
	`sex`           INT (1)         NULL        default 1                         COMMENT '性别 0 男 1 女',
	`version`       INT (11)       not null                                       COMMENT '版本号',
	`create_time`  datetime        not null      default CURRENT_TIMESTAMP     COMMENT '创建时间',
	`update_time`  datetime        NULL          ON UPDATE CURRENT_TIMESTAMP    COMMENT '修改时间',
	PRIMARY KEY (`id`)
) CHARSET = utf8;

2.显示当前库所有表：show tables;
3.显示表创建语句：show creat table 表名 示例：show create table student_demo;
4.删除指定表：drop table [if exists] tab_name [,tab_name]… 示例：drop table if exists student_demo
DDL
1.修改表
语法：alter table 表名 action;
说明：action 可以是如下语句：
add 列名 column [first | after 列名]
说明：为表添加一列，如果没指定first或者after，则在列尾添加一列，否则在指定列添加新列
练习：为student表添加一列班级id命名为class_id，类型为varchar，长度为36
答案：alter table student add column class_id varchar(36) after name;
alter 列名 set default 默认值
说明：更改添加指定列默认值
练习：将student表的name列默认值设为“家人们”
答案：alter table student alter column name set default “家人们”;
alter 列名 drop default
说明：删除指定列默认值
练习：删除student表name列的默认值
答案：alter table student alter column name drop default;
change 旧列名 新列名 新列数据类型
说明：更改列类型和列名称
练习：student表age列修改为update_age，类型为int，长度为15
答案：alter table student change age update_age int(15);
为了保证列名规范，我们将此列再改回age：alter table student change update_age age int(15);
modify 列名 数据类型
说明：更改数据类型
练习：将student表update_version列类型修改为，varchar类型，长度为15
答案：alter table student modify age varchar(15);
drop 列名
说明：删除一列
练习：将student添加一test列，varchar类型，长度为200，成功后再删除此列
答案1：alter table student add column test varchar(200);
答案2：alter table student drop test;
add foreign key(列名) references 关联表名(列名)
说明：为表添加一个外键
练习：为student表的class_id设为外键，关联class_grade的id列
答案：alter table student add foreign key( class_id ) references class_grade(id);
drop foreign key 约束名
说明：删除指定外键约束
练习：将student表的class_id外键删除，已知该外键约束名为student_ibfk_1
答案：alter table student drop foreign key student_ibfk_1;
rename as 新表名
说明：更改表名
练习：将student表重命名为student_test,成功后改回student
答案1： alter table studen rename as student_test;
答案2： alter table student_test as student;

DML
insert [into] <表名> [列名] values <值列表>
说明：向指定表插入数据，如果表名后面没写字段名，则默认是向所有的字段添加值，另外字符串 值应该用‘ ’或“ ”引号括起来
练习：向student表中插入自己的姓名和年龄，年龄可以虚报…
答案：insert into student(name,age) values(“吴彦祖启明分祖”,20);
注意事项:每次插入一行数据，不可能只插入半行或者几列数据，因此，插入的数据是否有效将按照整行的完整性的要求来检验；
案例：insert into student (name,age) values (“凡凡”),这是错误的
insert [into] <表名> [列名] values (<列名值>), (<列名值>), (<列名值>)……
说明：像指定表同时插入多条数据
练习：向student表中插入两条自己的姓名和年龄，年龄允许虚报第二次…
答案：insert student (name,age)
values (‘小明’,20),
(‘小明’,20);
insert into <表名> (列名) (select <列名> from <源表名> where <查询条件>)
说明：将源表名查出的数据插入到指定表中
update <表名> set <列名 = 更新值> [where <更新条件>]
说明：where子句是判断语句，用来设定条件，限制只更新匹配的行,如果不带where子句，则更新所 有行数据。
练习1：将student表中所有学生的年龄改为21
答案1：update student set age = 21
练习2：根据名字，将student表自己的数据年龄改为21
答案2：update student set age = 21 where name = “小明”;
delete from <表名> [where <删除条件>]
说明：此语句删除表中的行，如果不带where子句，则删除整个表中的记录，但是表不被删除。
练习：将student表中所有年龄小于18的数据删除
答案：delete from student where age<18;
注意:
在执行删除命令前，一定要备份数据，如果要删除数据量小，可以先执行查询操作，检查好查询结果后，在查询的基础修改或使用in语句上执行删除，避免数据误删。如：select * from student where age <18;
检查没问题后 delete from student where age<18;

select <列名|*> from <表名> [where <查询条件表达式>] [order by <排序的列名>[asc|desc]]

说明：

from子句：指定查询数据的表
where子句：查询数据的过滤条件
group by子句：对匹配where子句的查询结果进行分组
having子句：对分组后的结果进行条件限制
order by子句：对查询结果结果进行排序，后面跟desc降序或asc升序（默认）。　
limit子句：对查询的显示结果限制数目
distinct：在select后，对数据去重
as：(可省略) 放在指定表后，对该表取别名，放在指定查询列后，查询结果中该列取别名，
练习1 :查询全体学生的年龄与姓名。
答案：select age,name from studnet;
练习2 :查询全体学生的详细（所有列）记录。
答案：select id,name,age,create_time from student;或select * from student;
练习3 :查询所有学生的姓名，重名只显示一条。
答案：select distinct name from student;
练习4 :查询全体学生的学号与姓名，用中文显示列名。
答案：select age as 年龄,name as 姓名 from student;
练习5 :查询全体学生的年龄与姓名，给表设置别名。
答案：select s.age,s.name from student as s;
练习6 :查询年龄在20以下的学生的姓名。
答案：select name from student where age<20；
练习7 :查询全体学生的姓名、年龄，要求按照年龄升序排列。
答案：select name,age from student order by age asc;
练习8 :查询年龄最小的前3个学生的姓名和年龄
答案：select name,age from student order by age asc limit 3
SQL提供的统计函数称为集函数
主要的集函数：
记数函数： count(列名) 计算元素的个数
求和函数： sum(列名) 对某一列的值求和，但属性必须是整型
计算平均值：avg(列名)对某一列的值计算平均值
求最大值： max(列名) 找出某一列的最大值
求最小值： min(列名) 找出某一列的最小值

练习1 :查询成绩总条数
答案：select count(*) from report;
练习2 :查询语文的平均成绩。
答案：select avg(grade) from report where course=“语文”;
练习3 :查询数学的最高分和最低分
答案：select max(grade) as 最高分 ,min(grade) as 最低分 from report where course=“数学”;
练习4 :查询每个学生的平均成绩。
答案：select student_id,avg(grade) as 平均成绩 from report group by student_id;
练习5 :查询平均成绩在62分以上的学生。
select student_id,avg(grade) as 平均成绩 from report group by student_id having avg(grade)>62;
在WHERE子句中使用谓词 :

BETWEEN    AND    			：在两数之间
NOT   BETWEEN    AND　	：不在两数之间
IN <值表>					：是否在特定的集合里（枚举）
NOT IN <值表>　				：不在特点集合里
LIKE								：是否匹配于一个模式，匹配值要结合%使用
										举例： %小→阿小  小%→小青   %小%→赵小六、小青   
IS NULL							：为空的
IS NOT NULL（不为空的）	：不为空
<>								: 不等于
练习1 :查询年龄在20-23岁（包括20岁和23岁）之间的学生的姓名年龄 。
答案：select name,age from student where age between 20 and 23;
练习2 :查询年龄不在20-23岁之间的学生的姓名年龄 。
答案： select name,age from student where age not between 20 and 23;
练习3 :查询名字中包含“小”的学生姓名和年龄。
答案： select name,age from student where name like "%小%";
练习5 :查询年龄包含于15、29和1000这三个数中学生的姓名和年龄。
答案：select name,age from student where age in (15,29,1000);
 在查询学生成绩的时候，每次显示的都是学员的id，因为该表中只存储了studnet_id；

实际上最好显示学员的姓名，而姓名存储在学员信息表；如何同时从这两个表中取得数据
连接查询
同时涉及多个表的查询称为连接查询
用来连接两个表的条件称为连接条件
内连接(INNER JOIN)
外连接
——左外联结 (LEFT JOIN)
——右外联结 (RIGHT JOIN)
外连接与内连接的区别
内连接操作只输出满足连接条件的元素
外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出

思考：如果查询成绩表时，想显示学生的姓名而不是studet_id怎么办？
方案：使用内连接关联查询学生表和成绩报告表，如两个表列数过多，可并分别指定要显示哪个表的哪个列如：学生姓名、科目和成绩等
示例：select student.name, report.course, report.grade
from student
inner join report
on report.student_id = student.id;
用as简化： select s.name, r.course, r.grade
from student s
inner join report r
on r.student_id = s.id;

思考：如何查询所有学生，如果有成绩就显示出来？
方案：使用外连接，以学生表为连接主体
方式：select s.name, r.course, r.grade 

from student s left join report r
on r.student_id = s.id;

思考：如果以成绩表为连接主体结果是什么样？
验证：select s.name, r.course, r.grade
from report r left join student s
on r.student_id = s.id;

思考：右关联查询和左关联查询什么区别？
验证：select s.name, r.course, r.grade
from report r right join student s
on r.student_id = s.id;
经过验证可以证实，左关联和右关联只是决定以哪个表为主表，主表会显示自己的全部符合条件的数据
什么是子查询？
将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为子查询。
思考：查询所有参加考试了的学生姓名
方案：在成绩表中有数据的学生即可认为参加了考试
验证：select name from student where id in (select student_id from report);

索引
在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。 目前大多数的数据库采用的都是B+Tree作为索引结构

mysql怎么创建索引？
1.primary key （主键索引）
alter table table_name add primary key ( column )
2.unique (唯一索引)
alter table table_name add unique (column )
3.index (普通索引)
alter table table_name add index index_name ( column )
4.多列索引
alter table table_name add index index_name ( column1, … )
5.建表时创建索引
create table name_list (
id int(10) not null default ‘0’,
name varchar(20) character set utf8 default null,
primary key (id),
key name_index (name)
) engine=innodb default charset=utf8mb4;
练习 :将report中的student_id设置为索引列
答案：alter table report add index index_st_id (student_id);
索引创建的原则
1.最左前缀匹配原则
2.=和in可以乱序
3.尽量选择区分度高的列作为索引，区分度的公式是count(distinct col)/count(*)
4.索引列不能参与计算
6.选择唯一性索引
7.为经常需要排序、分组和联合操作的字段建立索引
8.为常作为查询条件的字段建立索引
9.限制索引的数目
10.尽量使用数据量少的索引
11.尽量使用前缀来索引
12.删除不再使用或者很少使用的索引

数据库设计原则——三范式
什么是范式：数据库设计会直接影响存储性能、操作数据是否便捷等。所以需要建立科学的设计规范，在关系型数据库中这些规范就可以称为范式。范式是我们在设计数据库结构过程中所要遵循的规则和指导方法。