mysql数据库

SQL:结构化查询语句（Structured Query Language），一般应用于mysql、oracle等数据库，
sql语句属于数据库中标准的的查询语句。

SQL可以支持的命令类型：
DML:数据操作语言(Data Manipulation Language)，用于修改或检索数据 insert delete update select
DDL:数据定义语言(Data Definition Language)，用于创建、修改、删除数据库及数据表的语言 create alter drop
DCL：数据控制语言(Data Control Language)，设置或回收权限 grant 授权 revoke 回收权限
TCL：事物控制语言（Transaction Control Language） commit rollback savepoint

一、基本命令语句
1、启动服务器(以管理员身份运行cmd)
格式： net start 服务器名称
例子： net start mysql57
（在cmd命令下，所有命令没有 ; , 单词不能写错，语句中的单词间的空格要存在）
（手动开启mysql服务器： 管理->服务->MYSQL57->点击启动）
2、停止服务器(以管理员身份运行cmd)
格式： net stop 服务器名称
例子： net stop mysql57

3、链接mysql本地数据库 （前提：mysql数据库为启动状态）
格式： mysql -u 用户名 -p
例子： mysql -u root -p
（当链接成功后会提示输入密码）
当见到 mysql> 时，代表进入mysql服务器，需要写sql语句。

4、查看版本
（注：sql语句以分号结束，sql语句要在mysql下执行）
例子：select version();

5、链接远程数据库
格式： mysql -h ip地址 -u 用户名 -p
例子： mysql -h 10.0.121.160 -u root -p
（输入root的密码）
（mysql默认只允许本地连接）

5.1、设置允许远程连接
输入命令 ： mysql -u root -p
输入密码
mysql> use mysql;
mysql> update user set host=’%’ where user=‘root’;
mysql> flush privileges;
(flush privileges; 刷新数据库)

6、退出数据库
格式：quit 或 exit
例子：quit

二、数据库的操作(必须进入mysql下)
1、查看mysql下有哪些数据库
格式：show databases;
例子：show databases;

2、创建数据库
格式： create database 数据库名称 charset=utf8;
(charset=utf8 : 数据库的编码格式，默认编码格式为gbk，不支持汉字，如果想要支持汉字
存储或读取，设置编码为utf8)
(数据库及数据表的命名格式：英文字母及数字及下划线的组合，不能以数字开头，不区分大小写)
例子： create database persons charset=utf8;

3、删除数据库
（删除数据库：会把当前数据库下的表单一起删除，慎用）
格式：drop database 数据库名;
例子：drop database abc;

4、使用数据库（打开文件夹）（前提:当前数据库要存在）
格式：use 数据库名;
例子：use students;
（数据表单要求在某一个数据库下创建）

5、查看当前正在使用的数据库
格式：select database();

三、表单的操作
1、查看当前数据库下有哪些表单
格式：show tables;
例子：show tables;

2、创建表单
2.1、创建一个全新的表单
格式：create table 表单名称(列名及列的类型和约束等)
例子：create table stu(id int auto_increment primary key, name varchar(50) unique not null,age int not null, address varchar(100));

2.2、根据已经存在的表单创建表单
(只是将旧的表单中的格式复制一遍，数据是不复制的)
格式：create table 新的表单名称 like 旧的表单;
例子：create table per like stu;

2.3、根据已经存在的表单创建表单
（数据会复制，但字段的约束不会被复制）
格式：create table 新的表单名称 as 旧表单中的全部或部分数据;
例子：create table person as select * from stu;

SQL的数据类型：
int 整型 （11）
bigint 长整型
float 浮点型

char 字符型 固定长度的字符型 最多放置255个字符
varchar 字符型 可变长度的字符型 最多放置255个字符
char与varchar的不同点： ‘abc’
char(16): 直接分配16个字符的空间，就算字符串长度不够16个字符，依旧是16个字符空间。
varchar(16): 根据字符的多少随时分配字符空间，但最多可存放16个字符。
小括号中的数字：为字符串的最大长度,取值范围:1-255

text:大文本类型,可存放65535个字符

blob：二进制类型，图片、音频–>转为二进制存储到数据库中

date : 日期类型 格式：yyyy-MM-DD 2018-07-02
time : 时间类型 格式：HH:mm:SS 14:45:34
datetime : 日期及时间的组合 ：yyyy-MM-DD HH:mm:SS 2018-07-02 14:45:34

SQL约束：
约束：对插入的数据进行各种限制,例如：年龄不能为空，名字不能重复
约束最好在创建表单字段时直接加入，将字段的声明确定；约束可以后期添加，但
尽量少用。
约束类型：
not null：非空约束，数据不能为空
auto_increment：自增约束，不能为空，不能重复，int类型, 默认从1开始自增
(设置了自增约束的字段我们开发人员可以不管这列的数据)
primary key：主键约束，不能为空(主键可以设置多个)
unique:唯一约束,值不能重复
default : 默认约束，可以设置默认值
foreign key：外键约束

3、删除表单(慎用)
格式：drop table 表单名称;
例子：drop table stu;

4、查看表单结构
格式：desc 表单名称;
例子：desc stu;

5、查看创建表单的语句
格式：show create table 表单名称;
例子：show create table stu;

CREATE TABLE stu (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(50) NOT NULL,
age int(11) NOT NULL,
address varchar(100) DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

ENGINE=InnoDB :数据库引擎 5.5以后默认innodb引擎
innodb:优点：支持外键,支持大量的update，
缺点：不适用于大量的select
myisam：优点:适用于大量的select
缺点：不支持外键

create table stu(id int auto_increment primary key, name varchar(50) unique
not null, age int not null, address varchar(100));

create table stu(id int auto_increment primary key, name varchar(50) unique
not null, age int not null, address varchar(100) default ‘北京’);

6、重命名表单
格式：rename table 原表单名称 to 新表单名称;
例子：rename table person to anim;

7、修改表单的操作
格式：alter table 表单名 add|drop|change|modify 字段名 类型 约束;
7.1、添加新字段
格式：alter table 表单名 add 字段名 类型 约束;
例子：alter table per add phone int(11) unique not null;
7.2、删除字段
格式：alter table 表单名 drop 字段名;
例子：alter table per drop phone;
7.3、修改或更新字段
格式：alter table 表单名 modify 字段名 新的类型 新的约束;
例子：alter table per modify age varchar(3);
7.4、更改字段
格式：alter table 表单名 change 旧字段名 新字段名 新的类型 新的约束;
例子：alter table per change age sex int;

四、数据的操作
1、增
1.1、全列插入
格式： insert into 表单名称 values(字段对应的值);
例子： insert into stu values(0,‘A’,12,‘北京’);
注：目前id为自增约束，在全列插入数据时，我们开发人员可以不考虑该字段的值，系统会默认从1开始增加，但我们需要写0，用于占位使用。
注：传入的值要与字段的顺序一致。
注：当字段为唯一unique约束时，传入的值不能重复，如果重复，添加失败，但自增的值会被占用。
注：自增约束的值如果被使用过了，就算后期进行增加删除时，只要使用的数值，不会重复使用。每次新增的数据的自增字段都会在最后一个值的基础上+1。
注：全列插入时，值的个数要与字段的个数必须一致。

1.2、缺省插入
注：约束为not null的字段必须插入值。
格式：insert into 表单名称(字段1, 字段2…) values(值1, 值2…);
例子： insert into stu(id, name, age, address) values(0, ‘D’, 17, ‘杭州’);
新增数据成功：原因：address为default约束。
insert into stu(id, name, age) values(0, ‘E’, 18);
新增数据成功：原因：address为default约束。id为自增约束。
insert into stu(name, age) values(‘F’, 19);
新增数据失败：原因：age为非空约束，插入数据时必须传值。
insert into stu(name) values(‘G’);
1.3、插入多条数据
格式：insert into 表单名称 values(值1, 值2…),(值1, 值2…),…;
insert into 表单名称(字段1,字段2…) values(值1, 值2…),(值1, 值2…),…;
例子：insert into stu values(0, ‘G’,12,‘苏州’),(0,‘H’,34,‘南京’);
insert into stu(name,age) values(“I”,12),(“J”,34);

2、删
格式：delete from 表单名称 where 条件;
例子： delete from stu where id=2;
注：执行删除时，where条件一定要写，如果不写where，会将整个表单的数据都删除。
delete from stu;

3、改
格式：update 表单名称 set key1=value1,key2=value2… where 条件;
例子： update stu set address=‘北极’ where id=2;
update stu set age=56,address=‘天津’ where name=‘J’;
注：更改数据时，where后面的条件一定要写，如果写了，更改符合条件的那条数据，
如果不写where条件，所有的数据都会更改(慎用)。
update stu set address=‘北极’;

4、查
4.1、全列及全部数据的查找
格式：select * from 表单名称;
例子：select * from stu;

5、表单的设计
淘宝 ----> 购物记录 （客户端删除）(数据库中未删除)
create table stu(id int auto_increment primary key, name varchar(50) unique
not null, age int not null, address varchar(100), isDelete int default 1);
更新数据 isDelete=0

五、SQL中的基本函数
1、字符函数
dual:虚构的表，但mysql认可该虚构表
1.1、将字符转为小写 lower()
例子：select lower(“ABC”) from dual;
1.2、将字符转为大写 upper()
例子：select upper(“abc”) from dual;
1.3、获取字符长度 length()
例子：select length(“abc”) from dual;
2、数学函数
2.1、绝对值 abs()
例子：select abs(-10) from dual;
2.2、四舍五入 round()
例子：select round(1.4) from dual;
select round(2.5) from dual;
select round(1.5) from dual;
select round(1.5325325, 2) from dual;
2.3、开方 sqrt()
例子：select sqrt(9) from dual;

六、查
1、查找所有数据
格式： select * from 表单;
* : 查询所有列
格式： select 字段1,字段2… from 表单;
如果加了字段:查询部分列
格式： select 字段1 as 字符串1,字段2 as 字符串2… from 表单;
as: 如果字段后面加了as，给字段起了一个别名，结果集中会展示别名的字符串。
注：当别名为汉字时，使用引号引起来。
注：from 后面写表单名称，代表从该表单中查询的数据。

例子： select * from stu;
select name from stu;
select name,age from stu;
select name as ‘姓名’, age as ‘年龄’ from stu;
select name as ‘n1’, age as ‘a1’ from stu;
select name as n1, age as a1 from stu;

2、按条件查找
格式： select * from 表单 where 条件;
* : 查询所有列
格式： select 字段1,字段2… from 表单 where 条件;
如果加了字段:查询部分列
格式： select 字段1 as 字符串1,字段2 as 字符串2… from 表单 where 条件;
as: 如果字段后面加了as，给字段起了一个别名，结果集中会展示别名的字符串。
注：当别名为汉字时，使用引号引起来。
注：from 后面写表单名称，代表从该表单中查询的数据。
注：where 后面为条件

例子： select * from stu where address=‘北京’;
select name from stu where address=‘北京’;
select name,age from stu where address=‘北京’;
select name as ‘姓名’, age as ‘年龄’ from stu where address=‘北京’;

七、where 条件语句
1、比较运算符
等于 ：=
大于：>
小于：<
大于等于：>=
小于等于：<=
不等于：!= 或 <>
例子： select * from stu where id=12;
select * from stu where id>13;
select * from stu where id<13;
select * from stu where id<=13;
select * from stu where id>=13;
select * from stu where id!=12;
select * from stu where id<>12;

2、逻辑运算符
与 and 必须满足and的所有条件
或 or 满足or的任意一个条件即可
非 not 不满足not后面的条件
格式： where 条件1 and 条件2
where 条件1 or 条件2
where not 条件1
12 -17
例子： select * from stu where id<16 and id>13; 14 15
select * from stu where id>16 or id<13; 12 17
select * from stu where id<16 or id>13; 12-17
select * from stu where not id<16; 16 17

3、逻辑运算符联合使用
例子： select * from stu where id<16 and id>13 or id<15;
select * from stu where id>13 or id<16 and id>13;
优先级 ： not > and > or
小括号的优先级最高
注：优先级不明确时，使用小括号提升优先级，根据小括号中的条件再去做逻辑判断。
例子： select * from stu where id<16 and id>13 or id<15; 12-15
select * from stu where id<16 and (id>13 or id<15); 12-15
select * from stu where (id<16 and id>13) or id<15;
select * from stu where not id>14 and id<17; 12 13 14
select * from stu where not (id>14 and id<17);
select * from stu where id>14 and (not id<17); 17

4、范围查找
4.1、in 集合 ： 非连续性集合
格式：where 字段 in (集合)
注：集合中的数据类型可以是任意类型
例子：select * from stu where id in (10,11,12,16,18); 12 16
select * from stu where id in (‘15’,‘16’);
4.2、between…and…: 连续性集合
格式：where 字段 between start and stop
注：闭区间
注：使用数字或字母，不要用汉字
例子： select * from stu where id between 14 and 16;

5、空值与非空值判断
5.1、空值 is null
格式：where 字段 is null
例子：select * from stu where address is null;

5.2、非空 is not null
格式：where 字段 is not null
例子：select * from stu where address is not null;

注：空字符串与null不一样，null是未传值或值为null；空字符串不属于null类型，只是该字符串字符个数为0。

create table animal(id int auto_increment primary key, name varchar(50)
not null, sex int default 0,age int not null);
insert into animal values(0, “温大大”, 0, 12);
insert into animal values(0, “温大”, 1, 67);
insert into animal values(0, “帅帅温大大”, 0, 10);
insert into animal values(0, “老温大大”, 1, 23);
insert into animal values(0, “总理温”, 0, 12);
insert into animal values(0, “总温”, 0, 12);
insert into animal values(0, “总公司”, 1, 45);
insert into animal values(0, “司小闹”, 1, 87);
insert into animal values(0, “温”, 1, 87);

6、模糊查询
6.1、% : 任意多个字符
格式：where 字段 like ‘字符%’
%字符 字符%字符 %字符%
例子：select * from animal where name like ‘温%’;
select * from animal where name like ‘%温’;
select * from animal where name like ‘%温%’;
6.2、_ : 任意一个字符 已经固定字符串的长度
格式：where 字段 like ‘字符_’
例子：select * from animal where name like ‘温_’;
select * from animal where name like ‘温__’;
select * from animal where name like ‘温_’;
select * from animal where name like ‘温’;

八、消除重复行 distinct
(一般查找某一个字段的不同值及该值的个数时使用)
格式：select distinct 字段 from stu where 条件;
例子：select distinct age from stu;
select distinct age from stu where id>16;

九、SQL的聚合函数
1、count(字段/*) 统计结果集的数据条数
2、max(字段) 返回当前字段的最大值
3、min(字段) 返回当前字段的最小值
4、sum(字段) 返回当前字段的结果集中的和
5、avg(字段) 返回当前字段的结果集中的平均值

例子：
查询animal中所有个体的个数
select count(*) from animal;
查询animal所有个体的年龄的和
select sum(age) from animal;
查询animal所有雄性个体的年龄的和
select sum(age) from animal where sex=0;
查询animal所有雌性个体的年龄的最大值
select max(age) from animal where sex=1;
查询animal所有雌性个体的年龄的平均值
select avg(age) from animal where sex=1;

十、分组查询 group by
(按字段分组,将该字段的相同值放为一组)
1、分组后的数据个数与原数据个数可能不一致，一般都是对分组后的数据进行聚合运算。
格式：select 字段 from 表单 where 条件 group by 字段名称;
(where 条件 ： 可以省略)
例子：查看当前动物有什么性别？
select sex from animal group by sex;
查看当前动物有什么性别，每种性别各有几个个体？
select sex,count() from animal group by sex;
判断年龄大于50的性别为1的个体个数有几个？
select count() from animal where age>50 and sex=1;
判断年龄大于50的不同性别的个体个数有几个？
select sex,count(*) from animal where age>50 group by sex;

2、查询条件 having （与group by合用）
having:对分组后的内容再进行筛选
格式：select 字段 from 表单 where 条件 group by 字段名称 having 条件;
(where 条件 ： 可以省略)
例子：判断当前雄性个体的个数
select count() from animal where sex=0;
select count() from animal group by sex having sex=0;

where 与 having 的区别：
where 条件: 先执行条件，再进行查询 (对整个表单进行查询)。
where是对from后面的表单进行查询。
having 条件：是再group by已经分组后的结果上查询 （对查询结束后的数据再进行查询）
having：是对group by后面的数据进行查询。

十一、排序 order by
格式：select 字段 from 表单 where 条件 order by 字段名称1 asc|desc, 字段名称2 asc|desc,…;
(where 条件 ： 可以省略)
说明：以字段1开始排序，如果字段1有相同的数据，按字段2排序…
asc：升序 默认升序排序 desc：降序
例子：
按年龄排序
select * from animal order by age;
select * from animal order by age asc;
select * from animal order by age desc;
select * from animal order by age desc, id desc;

十二、分页 limit
格式：select 字段/* from 表单 where 条件 limit 起始值,数据条数;
(where 条件 ： 可以省略)
下标从0开始
例子：
select * from animal limit 0,4;
分页效果:
select * from animal limit 0,4;
select * from animal limit 4,4;
select * from animal limit 8,4;

十三、关联查询(多表查询)
创建表单及插入值
create table b1(id int, name varchar(16));
create table b2(num int, passwd varchar(16));
insert into b1 values(2,“A”),(3,“B”),(4,“C”);
insert into b2 values(1,“a”),(2,“b”),(3,“c”),(4,“d”),(5,“e”);
1、左链接 left join
格式： select 字段 from 表单1 left join 表单2 on 表单1.字段=表单2.字段;
说明： 结果集的个数以表单1为基准，如果符合on后面的条件展示在结果集中，表单2的个数如果不够，用null补全。
例子：select * from b1 left join b2 on b1.id=b2.num;
select * from b2 left join b1 on b1.id=b2.num;

2、右链接 right join
格式：select 字段 from 表单1 right join 表单2 on 表单1.字段=表单2.字段;
说明：结果集的个数以表单2为基准，如果符合on后面的条件展示在结果集中，表单1的个数如果不够，用null补全。
例子：select * from b1 right join b2 on b1.id=b2.num;
select * from b2 right join b1 on b1.id=b2.num;

3、内链接 inner join
格式：select 字段 from 表单1 inner join 表单2 on 表单1.字段=表单2.字段;
说明：结果集的个数以表单1与表单2中字符合on后面的条件的个数为基准。
例子：select * from b1 inner join b2 on b1.id=b2.num;
select * from b2 inner join b1 on b1.id=b2.num;

4、多表查询时的字段设置
表单名称.字段名
select b1.id,b2.passwd from b1 inner join b2 on b1.id=b2.num;

十四、以表单的结果为查询范围(多表查询)
select * from b2 where num in (2,3,4);
以b1的id字段的查询结果作为b2的查询条件范围
select * from b2 where num in (select id from b1);

如果将select的查询结果作为查询范围，将select的结果加个别名 as
select * from (select * from animal where age>40) as An;

十五、表单设计
1、一对一 （一个表）
一个id—一个name–一个sex
2、一对多 (多个表)
班级(1807)–学生(多名)
3、多对多 (多个表)
选修课（多个） — 学生(多个)

一个学生 — A B C
A — 多名学生

十六、外键(多表)
主键：表单中每条数据的唯一标识，值不能重复，不能为null。
注：主键的值不建议修改及删除。
外键：另一个表的主键，外键的值可以重复,可以为null。
外键对本个表单来说，就是一个普通的字段，只是该字段与其他表有些许联系。

外键的创建及使用
格式：foreign key(外键名) references 关联的表单(字段名)
1、一对多
a、创建表单
表1 ： 班级管理表
create table class(id int auto_increment primary key, name varchar(20) not null);
表2 ： 学员管理表
将student表中的classid字段与class表单中的id字段关联起来。
classid为外键。
create table student(id int auto_increment primary key,name varchar(20) not null,
classid int not null, foreign key(classid) references class(id));

b、添加数据
表1 id： 1 2 3 4
insert into class values(0,‘java’),(0,‘python’),(0,‘php’),(0,‘go’);
表2
insert into student values(0,‘妞妞’, 1);
insert into student values(0,‘牛’, 1);
insert into student values(0,‘妹妹’, 2);
insert into student values(0,‘大头’, 4);

当关联的外键的值不存在时，增加数据失败

insert into student values(0,‘弟弟’, 5);

c、查询数据

打印所有学员信息信息及课程名称

select student.id,student.name,student.classid, class.name from class,student
where student.classid=class.id;

select student.id as ‘学号’,student.name,student.classid, class.name as ‘学科’ from class,student
where student.classid=class.id;

打印所有学习java的学员信息

select student.id,student.name,student.classid, class.name from class,student
where student.classid=class.id and class.name=‘java’;

2、多对多
一个学员 — 课程（多） id 主键
一个课程 ---- 学员(多) id 主键
选修课程总概表
xx (sid,cid) 两个字段都是外键
a、创建表单
表1 ： 学员表
create table xueyuan(id int auto_increment primary key,name varchar(50)
not null);
表2： 课程表
create table kecheng(id int auto_increment primary key,name varchar(50)
not null);
表3： 选修课程表
create table xuanxiu(xid int not null, kid int not null, foreign key(xid)
references xueyuan(id), foreign key(kid) references kecheng(id));

b、添加数据
表1：
insert into xueyuan values(0,‘温温’),(0,‘木木’),(0,‘果果’);
表2：
insert into kecheng values(0,‘Python’),(0,‘Html’),(0,‘C++’),(0,‘Java’);
表3：
第一个值： 1 2 3 第二个值：1 2 3 4
insert into xuanxiu values(1,1);
insert into xuanxiu values(1,2);
insert into xuanxiu values(2,4);
insert into xuanxiu values(3,3);
insert into xuanxiu values(3,4);

新增失败:学员的值小于等于3

insert into xuanxiu values(4,1);

新增失败:课程的值小于等于4

insert into xuanxiu values(3,5);

c、查询数据
1)、查询所有的学员及学员选修的课程记录(学员名字–课程名字)
select xueyuan.id,xueyuan.name, kecheng.id,kecheng.name from xueyuan,kecheng,
xuanxiu where xueyuan.id=xuanxiu.xid and kecheng.id=xuanxiu.kid;
2)、查询温温学员选修的课程记录(学员名字–课程名字)
select xueyuan.name as ‘姓名’,kecheng.name as ‘课程’ from xueyuan,kecheng,
xuanxiu where xueyuan.id=xuanxiu.xid and kecheng.id=xuanxiu.kid and
xueyuan.name=‘温温’;
3)、查询选修课程C++的学员记录(学员名字–课程名字)
select xueyuan.name as ‘姓名’,kecheng.name as ‘课程’ from xueyuan,kecheng,
xuanxiu where xueyuan.id=xuanxiu.xid and kecheng.id=xuanxiu.kid and
kecheng.name=‘C++’;