MySQL的一些简单SQL语句

一、SQL
SQL:Structure Query Language:结构化查询语言
专门用在操作关系型数据库的语言
任何关系型数据库都支持SQL,但是每门数据库都有细微的差别
SQL语句分类：
DDL(*)（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等；
DML()（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）；增删改
DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别；
DQL(*)（Data Query Language）：数据查询语言，用来查询记录（数据）。查询（select）
注意：SQL语句必须以;结尾
** MySQL数据类型：
INT 数字类型(4个字节)
SMALLINT 数字(2个字节)
BIGINT 数字(4个字节)
DATE 日期
TIME 时间
DATETIME 日期时间
VARCHAR(长度) 字符串
TEXT 文本
BLOB 二进制
FLOAT 浮点型
DOUBLE 双精度

二、SQL语句
sql语句不区分大小写
2.1DDL数据定义语句
包含的关键字：
2.1 CREATE：新建
可以新建数据库、表
2.1.1创建数据库格式：

CREATE database [ if NOT EXISTS] 名称 [CHARACTER SET 编码格式] [COLLATE 比较方式];

创建一个编码格式为utf8的数据库

create database db_study01 ;//默认编码格式
create database db_study02 character set utf8 collate utf8_general_ci;//创建数据库编码格式utf8比

较方式对应的
2.1.2创建表的格式：

CREATE table [ if NOT EXISTS] 名称 (字段名称 数据类型,……) [CHARACTER SET 编码格式] [COLLATE 比较方式];

创建一张user表

create table user (id int,name varchar(20),age int);

2.2ALTER:修改
可以修改数据库的编码和表的字段
7.2.1修改数据库格式：ALTER database 名称 [character set 编码][cllate 比较格式];
修改数据库mydb2的编码格式为gbk

alter database mydb2 character set utf-8; 

2.2.2修改数据库表的格式：

ALTER table 名称 [ADD|CHANGE|DROP|MODIFY] 字段名称 [新名称] [数据类型]

为user表新增一个字段password 字符串32

alter table user add password varchar(32);
alter table user add test varchar(32);

为user表中的name字段重命名为username

alter table user change name username varchar(20);

删除user表中的test字段

alter table user drop test;

修改user表中username字段的长度为30

alter table user modify username varchar(30);

修改user的表名为user1

rename table user to user1;

2.3DROP删除
可以删除数据库或表
2.3.1删除数据库格式：

DROP database [ if EXISTS] 名称;

2.3.2删除数据表格式：

DROP table 名称;

删除数据库mydb4

drop database if exists mydb4;

删除test3表

drop table if exists test3;

DML语句：主要是对表中的数据进行新增、修改、删除操作

2.4INSERT新增语句
格式：INSERT INTO 表名[(字段名称)] values(值1,值2);
为user表新增数据

insert into user (id,username,password) values(2,'贾金山','8888');
insert into user (id,username,password,flag) values(5,'战旗','838',1);
insert into user (id,username,password,flag) values(6,'战旗','838',1);
insert into user (id,username,password,flag) values(7,'战旗','838',1);
insert into user (id,username,password,flag) values(8,'战旗','838',1);

//简写形式

insert into user values(4,'曹怡',"123456");
insert into user(username,id,password) values('王星',5,"123");

2.5UPDATE修改语句
格式：UPDATE 表名 SET 字段名称=值[多个字段] [where 条件]
修改id为2的用户名字为金星

update user set username='金星' where id=2;

修改id为3的用户的名字和密码

update user set username='网警',password="654321" where id=3;

修改user中所有的密码为123456

update user set password='123456';

2.6DELETE删除语句
删除表中的数据
格式：DELETE FROM 表名 [where 条件];
删除user中名称为王星的人

delete from user where username='王星';

开发中的删除：
1、真删除
直接使用delete语句删除指定的数据
2、假删除
使用update 修该指定字段
为user表新增一字段，flag标记是否有效，0有效1删除

alter table user add flag int;
update user set flag=0;
select * from user where flag=0;
update user set flag=1 where id=4;

DQL语句：数据查询语句，关键字：select

2.7SELECT查询语句
主要用来查询数据
格式：SELECT *|字段名称|函数 from 表名 [where 条件] [order by 字段名称] [group by 字段名称][having 条件][limit 限制]
2.7.1
*在查询语句中表示当前表的所有字段(顺序为建表时字段顺序)
2.7.2where条件：
常用的条件符合：
1、=、<、>、!=、<>、<=、>=
2、and:并且，同时满足
3、or:或者，满足其一
4、in (值,……):对应的字段的值再()里，一般不连续的多个值
5、between A and B:在A和B之间,[A,B]
6、IS NULL:为空
7、NOT:不
8、子查询
查询user表中所有信息

select * from user;

等价于

select id,username,password,flag from user;

查询有效的名字

select username from user where flag=0;

查询id大于1小于6的人

select * from user where id<6 and id>1;

等价

select * from user where id between 2 and 5;

查询id为4或者id为8

select * from user where id=4 or id=8;

查询id不为6的人

select * from user where id!=6;

等价

select * from user where id<>6;

查询id为2或5或6的人

select * from user where id=2 or id=5 or id=6;

等价

select * from user where id in (2,5,6);

2.8 show语句
查看当前的所有数据库：

show databases;

查看指定数据库的创建语句

show create database 数据库名称;

查看数据库的编码格式

show variables like 'character%';

查看mySQL的支持的编码

show character set;

查看对应编码的比较集

show collation;

查看数据库的所有表名

show tables;

查看数据库中建表语句

show create table 表名;

三、where 条件：
=<><>!=>=
or:或者
and：并且
between A and B：在A和B之间
in (值)：在……内部
is null：为null
not:不

select的标准格式：select *|字段列表|聚合函数 from 表名 [where 条件] [group by 字段] [having 字段][order by 字段 ASC|DESC][limit 起始索引,数量]]

四、模糊查询
like:模糊查询的关键字
_:1个字符
%:任意个字符
1.1以XX开头
格式：select *|字段 from 表名 where 字段 like 'XX%';
查询商品表中名字以华为开头的

select * from goods where name like '华为%';

查询商品表中名字以华为开头的且后面就一个字符的商品

select * from goods where name like '华为_' ;

1.2以XX结尾
格式：select *|字段 from 表名 where 字段 like '%XX';
查询商品表中价格以9结尾的

select * from goods where price like '%9';

查询商品表中价格为三位数且以9结尾

select * from goods where price like '__9';

等价于

select * from goods where price like '%9' and price >99 and price <1000;

1.3包含XX
格式：select *|字段 from 表名 where 字段 like '%XX%';
查询商品表中名字包含o的商品
select * from goods where name like ‘%o%’;

五、字段控制查询
1查询结果去重
distinct:对查询结果去重
//查询goods表中所有数据并去除重复的

select DISTINCT * from goods;

2对null值转换
ifnull(字段名称 , 待转换的值)：标记某个字段为null的话转换为指定的数据参与运算
查询goods表中id和price的和

select id+price from goods;

null值和任何类型的数据运算结果都是null

select id+ifnull(price,10000) from goods;

3别名
可以为运算的结果起别名： as 别名
也可以为表起别名，as可以省略
查询goods表中id和价格的和，要求价格为null则作为10000处理

select id+ifnull(price,10000) as total from goods;

等价于

select id+ifnull(price,10000) total from goods;

查询goods表名价格小于100的商品

select * from goods where price <100;

等价于

select * from goods where goods.price <100;

等价于

select * from goods g where g.price <100;

六、排序
order by 字段 排序方式,……
排序方式：ASC:升序(默认排序)，DESC:降序
查询goods表中按照价格升序排列

select * from goods order by price;

等价于

select * from goods order by price ASC;

查询goods表中按照价格降序排列

select * from goods order by price DESC;

查询goods表中按照价格降序排列,如果价格相同按照id进行升序排列

select * from goods order by price DESC, id asc;

七、聚合函数
COUNT:计数，计算指定列不为null的数量
MAX:计算指定列的最大值
MIN:计算指定列的最小值
SUM:计算指定列的总和
AVG:计算指定列的平均值

查询goods表中的数据的数量
//查询goods表中price不为null的数据行

select count(price) from goods;

//查询goods表中id不为null的数据行

select count(id) from goods;

//查询goods表中的数据行

select count(1) from goods;

查询goods表中所有price的总和

select sum(price) from goods;

查询goods表中价格最高和和最低的商品

select MAX(price) maxp,min(price) minp from goods;

查询goods表中的评价价格

select avg(price) from goods;

八、分组

group by 字段名称 [having 条件]

查询学生表中男女的数量

select sex,count(no) from student group by sex;

查询学生表中男女的平均年龄

select sex,avg(age) from student group by sex;

查询每个年龄的学生的数量

select age,count(no) from student group by age;

查询学生表中每个年龄的人数并且，人数等于的1

select age,count(no) ct from student group by age having ct=1;

where和having的区别
where:用在表名后，进行条件的筛选，主要用在分组前
having：用在group by之后，主要对分组的数据进行筛选

九、limit分页
格式：limit 行索引,数量：从指定的行索引开始查询，查询指定的数量
查询学生表中前三条数据

select * from student limit 0,3;

查询学生表中第三条到底五条的学生

select * from student order by no limit 2,3;

分页：每页显示2条数据，请查询第三页的数据

select * from student order by no limit 4,2;

//分页算法
int count=2;int page=3;

select * from student order by no limit (page-1)*count,count;

查询的顺序：from-->where--->group by --->having--->order by --->limit

十、数据完整性
约束条件：
主键、唯一约束、自增、不为null、默认值、外键等
10.1主键：唯一标记，不可为null，不能重复
任何表必须有主键
添加主键的方式：
1、create table (字段 primary key)
2、create table (字段,primary key(字段名称,字段名称))
3、alter table 表名 add primary key(字段名称);
//delete from student where no =(select no from student limit 5,1);
delete from student where no=17000506;
//添加主键为no
alter table student add primary key(no);
create table grade(id int,name varchar(20),primary key(id));
10.2unique:唯一约束 ，不能重复，可以为null(多个)

create table test1(id int unique );

10.3auto_increment:自动增长，每次递增1,一般主键使用

create table test2(id int primary key auto_increment,msg varchar(20) unique);

10.4not null:不为null

create table test3(id int not null);

10.5default:默认值

create table test4(id int primary key auto_increment,psw varchar(6) not null default '123456');
insert into test4(psw) values(default);
insert into test4(psw) values(null);

10.6FOREIGN KEY ：外键
外键：一个表中的某个字段的值必须为另张表中的主键的值，这时我们可以使用外键约束
作用：当新增数据时，如果当前的外键的值在另张表的主键的值不存在，就报错
CONSTRAINT 外键名称(外键表中的名称) foreign key(当前表中的外键) references 另张表名称(主键名称)
外键的添加方式:

1、create table 表名(字段,外键,CONSTRAINT 外键名称(外键表中的名称) foreign key(当前表中的外键) references 另张表名称(主键名称))；

2、ALTER TABLE 表名ADD CONSTRAINT 外键名称FOREIGN KEY(当前表中的外键) REFERENCES 另张表名称(主键名称))；

create table student (sid int primary key auto_increment,name varchar(20),

十一、多表关系：
8.1一对一
2表数据一一对应
8.2一对多
2表数据，存在一对多
8.3多对多
2表数据，存在多对多的关系
九、三大范式
阐述如何创建表并定义之间联系
9.1第一大范式：
确定列的原子性
就是每列的内容各不相同，要划分为最小单元
9.2第二大范式
确保有主键
主要用于区分每条数据，并定位
9.3第三大范式
表与表之间通过主键关联
主键唯一
功能单一原则：表描述的或存储的数据单一
关联通过主键

十二、多表查询
主要用来查询多张表
1、合并结果集
UNION ：合并2个查询的结果，要求2个查询的列的个数和列的数据类型必须相同，会消除重复的数据
UNION ALL:合并2个查询的结果，要求2个查询的列的个数和列的数据类型必须相同，不会消除重复的数据
2、内部连接查询
inner join on：内部连接
格式：select * from 表1 inner join 表2 on 表1字段=表2字段
查询2个表的数据，内部连接的形式
select * from mytest1 inner join mytest2 on mytest1.id=mytest2.id2;
3、外部连接查询
左外联：left outer join on
格式：select * from 表1 left join 表2 on 表1字段=表2字段
左联就是表1为主表，表一数据都有，表2有就显示，没有就是显示为null
右外联：right outer join on
格式：select * from 表1 left join 表2 on 表1字段=表2字段
右联就是表2为主表，表二数据都有，表1有就显示，没有就显示为null
4、连接查询
查询2个表中所有数据的组合结果

select * from mytest1,mytest2;

产生的结果集：笛卡尔积
结果的数量：表1的数量*表2的数量

十三、杂谈
1、索引：
提高查询的效率
通过索引快速进行查询，速度会提高
1.1创建索引

CREATE INDEX indexName ON mytable(username(length)); 

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

1.2创建唯一索引

CREATE UNIQUE INDEX indexName ON mytable(username(length)) 

1.3修改索引（添加索引）

ALTER table tableName ADD INDEX indexName(columnName)

2、视图：
为比较复杂的结果集创建一个虚拟的表
格式：create view 名称 as select * from 表名；

CREATE VIEW 视图名(列1，列2...) AS SELECT (列1，列2...) FROM ...;

修改视图

CREATE OR REPLACE VIEW 视图名 AS SELECT [...] FROM [...];

查看已有视图

SHOW TABLES [like...];（可以使用模糊查找）

3、触发器：
一张表操作时，会对另外的表页进行操作
格式：delimiter $修改当前的结束符为$(默认的结束;)
创建触发器

create trigger 触发器名称
before|after insert|delete|update
on 表A for each row 
begin
要执行的sql语句
insert into 表B(列1， 列2， ...) VALUES(NEW.列1， NEW.列2， ...);
 end

删除指定的触发器：

DROP trigger T_TRIGGER_UPDATE

4、快速备份新表
create table 新表名称 select * from 旧表
其他数据库：
select * into 新表名 from 旧表;但是MySQL不支持

MySQL:
数据库重点内容：
一、七大SQL语句
1、CREATE
2、ALTER
3、DROP
4、INSERT
5、UPDATE
6、DELETE
7、SELECT
where\group by \having\order by \limit
二、约束条件
主键、唯一、非null、default、自增、外键
三、多表查询：
内联、外联、合并
四、聚合函数
AVG\MAX\MIN\SUM\COUNT
根据个人情况进行记忆：
1、内置函数
now\rand\floor\substring\ifnull
2、索引
索引提高操作数据库的效率
3、触发器
为某张表加自动操作

数据库的备份和恢复
3.1数据的备份
在cmd中，输入：mysqldump -u 用户名 -p 要备份的数据库 >路径/文件名称.sql
3.2数据的恢复
需要进入到Mysql
创建数据库
并使用数据库
source 要恢复的sql文件的路径;