自学MySQL整理的常用SQL指令

熟悉指令后可进行简单的练习：https://www.cnblogs.com/zhoucs/p/13452776.html

数据库基本操作

--链接数据库

mysql -uroot -p 或mysql -uroot -pmysql

--退出数据库

exit/quit/ctrl+d

--查看所有数据库

show databases;

--显示当前时间

select now();

--显示当前数据库版本

select version();

--创建数据库

create database 数据库名 charset = utf8；(默认为latin1)

--使用数据库

use 数据库名；

--查看当前使用的数据库

show database();

--删除数据库

drop 数据库名；例：drop `xxx-01`或drop xxx01，因为特殊符号报错是，可尝试用tab键上方的特殊单引号

数据表的操作

--查看数据库中所有的表

show tables;

--创建表

--auto_increment 自动增长

--not null 不能为空

--primary key 主键

--default 默认值

--create table 数据表的名字 （字段 类型 约束[，字段 类型 约束]）；

--unique 唯一性约束

--foreign key() references 表（字段）

--CONSTRAINT 外键名称 FOREIGN KEY 外键列名称 REFERENCES 主表名称(主表列名称)

--创建学生表
create table student(
id int unsigned not null auto_increment primary key,
name varchar(30),
age tinyint unsigned default 0,
high decimal(5,2),
cls_id int unsigned,
gender enum("男","女")
);
--外键

create table scores(
-> id int not null auto_increment primary key,
-> subject enum("语文","数学","英语"),
-> stu_id int unsigned not null,
-> cls_id int unsigned not null,
-> score tinyint unsigned default 0,
-> foreign key(stu_id) references student(id),
-> foreign key(cls_id) references class(id)
-> );

--查看表的结构

desc 表名称；

--往表里插入数据(必须按照表中字段的数据插入)

insert into student values(1,”jack”,18,178.2,2,”男”);
insert into student(name) values("micheal");
insert into student(字段名) select ....语句；

--查看表中数据

select * from 表名称

--添加表中的字段

alter table 表名 add 列名 类型；

--在学生表中添加出生日期字段

alter table student add birthday date;

--修改字段：

重命名：alter table 表名 change 原名 新名 类型及约束；

--修改学生表中出生日期字段名称

alter table student change birthday birth date default “2000-01-01”;

不重命名：alter table 表名 modify 列名 类型及约束;

--修改学生表中出生日期类型及约束条件

alter table student modify birth datetime;

--删除字段

alter table 表名 drop 列名;

--删除表

drop table 表名

--查看表的创建语句

show create table 表名

--插入数据

insert into 表名 values(数据);

--部分插入数据

insert into 表名(列名，...) values (值1，...);

--多行插入

insert into 表名 (列1，...) values (值1，...),(值1，...);

insert into 表名 values(值...),(值...);

--学生表中插入学生信息
insert into student (name,age,gender) values ("貂蝉",16,"女"),("小乔",16,"女");
insert into student values (default,"貂蝉",16,150,default,"女",default),(null,"小乔",16,150,null,"女",”1940-09-09”);

--修改数据

update 表名 set 列名=“值” where 条件；

--修改学号1的学生的出生日期
update student set birth=”2002-02-07” where id=1;

--删除数据

1.物理删除（一般情况不会使用）

delete from 表名 where 条件；

2.逻辑删除

--用一个字段（is_delete字段 bit类型 来表示这条信息是否已经不能再使用了

alter table 表名 add is_delete bit default 0;(0表示不删，1表示删除)

--在学生表中添加字段is_delete，默认为0，未删除状态
update student set is_delete=1 where name=”jack”;
select * from student where is_delete=0;

--查询数据

1. 查询所有列：select * from 表名;
2. 指定条件查询： select * from 表名 where 条件；
3. 查询指定列：select 列1 ，列2 from 表名
4. 使用as为列或表指定别名：select 列1 as 命名1，列2 as 命名2 from 表名
5. 消除重复行：distinct 字段
6. 给表重新命名后必须使用：select s.name,s.age from student as s;
7. 比较运算符：=，>,<,!=,>=,<=
8. 逻辑运算符：and or not
9. 返回指定行数：limit start,count
10. 返回NULL：SELECT IFNULL(NULL, "RUNOOB");如果第一个为null，则返回第二个
11. 例如：查找排第二的数，没有就返回NULL：
    select IFNULL((select distinct Salary from Employee order by salary desc limit 1,1),null)as SecondHighestSalary ;

--查询年龄18-28岁的学生
select * from student where age>18 and age<28;

--查询不在18-28岁的学生
select * from student where not（age>18 and age<28);

　　1.模糊查询：like ,rlike（正则表达式）

like:%替换一个或者多个；_替换一个；

--查询以“小”开头的学生
select * from student where name like ”小%”;

--查询姓名包含“小”的学生
select * from student where name like “%小%”;

--查询两个字的名字
select name from student where name like”__”;

--查询至少两个字的名字
select name from student where name like”__%”;

rlike:正则表达式

--查询以周开始的姓名
select name from student where name rlike”周.*”;

--查询以周开始,伦结尾的姓名
select name from student where name rlike”周.伦$”;

 

　　2.范围查询：

不在某个范围 not in:select * from student not in (10,12,36);

在某个连续的范围内between and

--查询年龄在18-30岁的学生信息
select * from student where age between 18 and 30;

不在持续范围内 not between and

--查询年龄在18-30岁的学生信息
select * from student where age not between 18 and 30；

3.空判断 is null；is not  null

排序

--order by 字段：asc 升序  desc 降序

--按年龄升序排序
select * from student where age between 18 and 28 order by age asc;

--order by 字段,字段

--查询18-20岁学生信息，按年龄升序排序，年龄相同的按id降序显示
select * from student where age between 18 and 28 order by age asc，id desc;

聚合函数 聚合函数不能和其他字段一起用

--总数 count：

--求女生人数
select count(*) as 女生人数 from student where gender=2;

--最大值 max：

--求学生最大年龄
select max(age) from student;

--最小值 min：

--求女生最低身高
select min(high) from student where gender=2;

--求和 sum:

--求所有学生身高总和
select sum(high) from student;

--平均值 avg:

--求所有学生身高平均值
select avg(high) from student;

--计算平均值 sum()/count()

--计算所有学生平均年龄
select sum(age)/count(*) from student;

--四舍五入 round(a,b) a为原数字，b位小数位数

--求所有学生平均年龄，并保留2位小数
select round(sum(age)/count(*)，2） from student;

分组

--group by

--按照性别分组，查询所有的性别
select gender ,count(*) from student group by gender;

 

--group_concat(字段) 可以查看分组中的所有成员

--查看同性别学生的姓名
select gender，group_concat(name,,”:”,age) from student group by gender;

 

--Having:对查询结果进行判断

--查询平均年龄超过20的性别，姓名。Having avg（age）>20
select gender,group_concat(name),avg(age) from student group by gender Having avg(age)>20;

 

Where 对表中的数据进行判断

--查询每种性别中的人数多于2个的信息
select gender,group_concat(name) from student group by gender Having count(*)>2;

 

分页 limit 必须放在最后面写

--limit start, count：limit（（第n页-1）*每页个数，每页个数）

限制查询出来的数据个数

select * from student  limit 5;

查询前5个数据

--查询前5个学生信息
select * from student limit 0,5;

 

--每页显示2个，显示3页：
select * from student limit 4,2;

--查询所有女生的信息，按身高降序排序，只显示两个
select * from student where gender=2 order by high desc limit 0,2;

 

连接查询

查询结果的列来源于多张表时，需要将多张表连接成一个大的数据库，再选择合适的列返回。

1.内连接查询：取多表的交集

--查询有对应班级信息的学生信息及班级名称
select s.*,c.name from student as s  inner join class as c on s.cls_id=c.cls_id;

 

2.右连接查询：和左连接查询相似，一般使用左连接查询

3.左连接查询：用左边表中的记录去右边表中去取，没取到的返回null

例：select c.cls_id,s.name from student as s left join class as c on s.cls_id=c.cls_id;

--查询没有对应班级信息的学生（对查询结果进行判断用Having）
select * form student as s left join class as c on s.cls_id=c.cls_id Having c.id is null; 

 

自关联：常用多个等级管理关系，如省市县

将网上下载的sql文件插入到现有的表格中：source 表.sql

 

--创建china 表格，包含中国所有的省和市。
create table china（
id tinyint unsigned not null primary key,
name varchar(30) not null,
p_id tinyint unsigned default null
）;

--往表里插入数据，如江苏省
insert into china values (320000,”江苏省”,default）；

--输出江苏省的所有市
select province.name,city.name from china as province inner join china as city on province.id = city.p_id having province.name=”江苏省”;

子查询：一个select 中嵌套另一个select

--查询最高的男生信息
select * from student where high = (select max(high) from student where gender = 1);

--查询江苏省的所有市
select * from china where p_id=(select id from china where name="江苏省");

 

数据库设计

三范式：数据库设计的前三个规范，不满足规范就拆表

第一范式 1NF：强调列的原子性，即列不能再分成其他几列；

第二范式 2NF：1.表必须有主键；2.没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分；假设主键有多个字段，其他字段只依赖于主键中的其中一部分

第三范式 3NF: 非主键列必须直接依赖于主键，不能存在传递依赖。如字段A依赖于字段B，字段B依赖于主键。

 

E-R模型

E表示entry，实体，设计实体就像定义一个类一样，指定从哪些方面描述对象，一个实体转换为数据库中的一个表；

R表示relationship，关系，关系描述两个实体之间对应规则，关系的类型包括一对一、一对多、多对多；

关系也是一种数据，需要通过一个字段存储在表中；

实体A对实体B为1对1，则在表A或表B中创建一个字段，存储另一个表的主键值；

实体A对实体B为多对多：新建一张表C（聚合表），这个表只有两个字段，一个用来存储A的主键值，一个用来存储B的主键值

 

逻辑删除

l 对于重要数据，并不希望物理删除，一旦删除，数据无法找回

l 删除方案：设置isdelete列，类型为bit，表示逻辑删除，默认值为0

l 对于非重要数据，可以进行物理删除

l 数据的重要性，要根据实际开发决定