MySQL基础

MySQL的学习在于多实践,多上机敲,这样才容易记住语法格式

---

MySQL语句分类

1.Data Definition Language(DDL):数据定义语句,用于对数据库和表的管理,如:建库,建表,删库,删表,修改库,修改表。

2.Data Manipulation Language(DML):数据操纵语句,用于对表中数据记录的增删改。

3.Data Query Language(DQL):数据查询语句,用于对数据的查询操作

4.Data Control Language(DCL):数据控制语句,用于对用户的管理和权限管理,如:创建数据库的用户,管理权限,一般是运维或者数据库管理员操作

---

表关系

一对一: 外键设置成唯一,从表外键又是主键。

一对多:通过外键约束。

多对多:创建一张中间表,形成两个一对多

---

注释

• 单行: –
• 单行: #
• 多行: /* */

---

DDL:数据定义语句

• 数据库

#无判断条件创建数据库
create database database_name;
#判断是否存在,不存在创建数据库
create database if not exists database_name;
#创建数据库并指定字符集,例如指定为gbk,没有指定默认为utf-8
create database database_name character set gbk;
#查看所有数据库
show databases;
#查看某个数据库定义信息
show create database database_name;
#修改数据库字符集,例如改为utf8
alter database database_name character set utf8;
#删除数据库
drop database database_name;
#查看正在使用的数据库
select database();
#使用某个数据库
use database_name;

• 数据库常用数据类型(fieldType)

分类	类型名称	类型说明	对应java
微整型	tinyInt	8位二进制的整数(很小)	java.lang.Integer
小整型	smallint	16位二进制的小整数	java.lang.Integer
中整型	mediumint	24位二进制的中等长度整数	java.lang.Integer
整型	int	32位二进制的整数类型	int
整型	integer	32位二进制的整数类型	java.lang.Long
大整型	bigint	64位二进制的大整数(长整数)	java.math.BigInteger
位	bit		java.lang.Boolean
字符串	varchar		java.lang.String
字符	char		java.lang.String
字节	blob		java.lang.byte[]
文本	text		java.lang.String
单精度浮点	float	4个字节的单浮点数	java.lang.Float
双精度浮点	double	8个字节的双浮点数	java.lang.Double
小数值类型	decimal(m,n)	数值类型,m表示数值的长度,n表示小数的位数	java.math.BigDecimal
时间类型	time	只表示时间	java.sql.Time
日期类型	date	只表示日期	java.sql.Date
	year		java.sql.Date
日期和时间类型	dateTime	表示日期和时间:‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’	java.sql.Timestamp
日期和时间类型	timestamp	表示日期和时间: ‘1970-01-01 00:00:01’ 到 ‘2038-01-19 03:14:07’	java.sql.Timestamp

• 约束(constraint):防止不符合要求的数据添加到表中,保证表中数据的正确性

类型	说明
非空约束(not null)	表示被约束的字段内容不能为空,必须给定具体数据
唯一约束(unique)	表示具有唯一性,不可重复,但可以为null
主键约束(primary key)PK
外键约束(foreign key)FK
联合约束	表示表中的另一条记录,有两个字段或者两个以上字段相同,则会报错
默认值(default)	为空时默认值会代替null

• 表创建
  新手建表注意是在()内,不是{}内.每个以英文逗号 , 隔开

#创建表结构格式(字段名,数据类型,约束),多个字段用逗号隔开
create table table_name(
  fieldName_1  fieldType_1  constraint_1 ,
  fieldName_2  fieldType_2  constraint_2 ,
  fieldName_3  fieldType_3  constraint_3 ,
  ....
);
#无约束创建单表结构(可创建后添加约束)
create table table_name(
`id` int,
`name` varchar(20), -- ``可以不用,varchar(20)表示varchar长度为20
`sex` char(1),
`birthday` date
);

create table ptn(
p_id int primary key,
name varchar(20)
);

#有约束创建表(创建时约束)
create table table_name(
`id` int primary key auto_increment, --主键约束+自增长
`name` varchar(10) not null, -- 非空约束
`address` varchar(30) default '广东', --默认约束
`email` varchar(20) unique -- 唯一约束
`f_key` int not null, -- 外键约束,需要先创建一张主表,否则无法创建外键
foreign key(f_key) references ptn(p_id) -- p_id:主表中的主键 , ptn:表示主表表名(primary table name)
);
#表结构后创建约束
-- 1.主键约束
###添加
alter table table_name add primary key(fieldName);
###删除
alter table table_name drop primary key;
-- 2.非空约束
###添加非空
alter table table_name modify fieldName fieldType not null;
###删除非空
alter table table_name modify fieldName fieldType null;
-- 3.唯一约束
###添加唯一
alter table table_name add unique constraint constraint_name(fieldName);
###删除唯一
alter table table_name drop key fieldName;
-- 4.自动增长
###添加自动增长
alter table table_name modify fieldName fieldType auto_increment;
###删除自增长
alter table table_name modify fieldName fieldType
-- 5.外键约束
###添加外键:
alter table table_name add constraint constraint_name foreign key(fieldName);
###删除外键:
alter table table_name drop foreign key constraint_name;

#查看某个数据库中所有表
##使用use 指定数据库
use database_name;
##使用show
show tables;
#查看某个表的表结构
desc table_name;
#查看创建表的sql语句
show create table table_name;
#创建相同的表结构(类似于复制,只复制结构不复制记录的数据)
create table new_table_name like old_table_name;
#直接删除表
drop table table_name;
#判断表是否存在再删除表
drop table if exists table_name;

#修改表结构
##添加表字段(列),使用关键字 add
alter table table_name add fieldName fieldType constraint_type;
##修改字段类型,使用关键字 modify
alter table table_name modify fieldName new_fieldType constraint_type;
##修改字段和类型,使用关键字 change
alter table table_name change old_fieldName new_fieldName new_fieldType;
##删除字段,使用关键字 drop
alter table table_name drop fieldName;
##修改表名(注意mysql中没有直接修改库名的语句)
rename table old_table_name to new_table_name;

---

比较运算符

<=, >=, <, >, =, != ,<>不等于

---

逻辑运算符

逻辑运算符	说明
and或&&	全真为真
or或 II	有真为真
not或 !	取反

---

MySQL通配符

通配符	说明
%	匹配多个字符
_ 下划线	匹配一个字符

---

DML:数据操纵语句

-- 插入一条记录
##val要与其对应的数据类型相匹配
insert into table_name value(val1,val2,val3,....);
#插入多条记录
insert into table_name (fieldName1,fieldName2,....)values(val1,val2,....)
###如碰到时间类型可以使用 now() ,插入当前时间,包含了日期和时间
###自增长,或有默认,或没有非空约束的可以选择忽略填入(没有填的默认为null)

-- 更新表记录(修改表记录)
##修改所有行的记录 , 会修改指定字段下的所有值
update table_name set fieldName = value;
##例如: 将student表中的所有性别修改为男
update student set sex = '男';

##修改符合要求的数据 , 会根据where后条件修改指定字段的值
update table_name set fieldName = value where fieldName = value;
##例如: 将student表中id为2的性别改为男
update student set sex = '男' where id = 2;

-- 删除表记录
##删除表中所有记录 : 不会删除结构只删记录
delete from table_name;
##删除了所有表记录,不同的是,同时删除了表结构,然后又重新创建了一张相同的表结构
##如同(drop table table_name;) + (create table table_name(...);)的结果
truncate table_name; 
##删除表中指定记录:
delete from table_name where fieldName = val;

---

DQL : 数据查询语句

-- 单表
##查询表中所有数据(无条件)
select * from table_name; -- * 表示当前表的所有字段, 可以改成指定字段,前提是表中存在的字段

#as 定义别名的关键字,可以省略as不写,如下:
select name as 姓名 from table_name; 
select name 姓名 from table_name;

#清除重复值
select distinct fieldName1,fieldName,... from table_name;

• 进行运算

select fieldName1+fieldName2 from table_name;
select fieldName+value from table_name;
#例如:
select * from student;
## 查询姓名、数学，将数学每个减10分
select name,math from student;
## 只影响查询结果
select name,math-10 from student;
## 查询所有列与math + english的和并使用别名”总成绩”
select *,(math+english) 总成绩 from student;

• 比较运算符

##查询math分数大于80分的学生
select * from student3 where math > 80;
##查询english分数小于或等于78分的学生
select * from student3 where english <=78;
##查询age等于20岁的学生
select * from student3 where age = 20;
## 查询age不等于20岁的学生，注：不等于有两种写法
select * from student3 where age <> 20;
select * from student3 where age != 20;

• 范围查询 between min and max

#between min_number and max_number:直到一个范围区间查找
select * from table_name where fieldName between min_number and max_number;
#例如:
-- 查询english成绩大于等于77，且小于等于87的学生
select * from student where english between 77 and 87;
select * from student where english >=77 and english<=87;
select * from student where english<=87 and english >=77;

• in关键字

## in关键字 : 只要符合in()括号中的条件就会显示
select fieldName from table_name where fieldName in(val1,val2,val3,...);
-- 例如:
-- 查询id是1或3或5的学生
select * from student3 where id in(1,3,5);

-- 查询id不是1或3或5的学生
select * from student3 where id not in (1,3,5);

• like关键字

#作用于字符串模糊查找
select * from table_name where fieldName like '字符串';
#可以结合通配符 _ 或 % 使用

--例子:
## 查询姓马的学生
select * from student where name like '马';
## 相当于
select * from student where name = '马';
## 模糊查询必须使用通配符
select * from student where name like '马%';
## 查询姓名中包含'德'字的学生
select * from student where name like '%德%';
## 查询姓马，且姓名有2个字的学生
select * from student where name like '马_';

• 查询为空的列(is null)

#查询为空的
select * from table_name where fieldName is null;
#查询不为空的
select * from table_name where fieldName is not null;
#ifnull(fieldName,defaultValue):如果这一字段有值,则显示值,为null时显示后面默认值
select fieldName1,ifnull(fieldName,dafaultValue) from table_name;

-排序

升序	asc
降序	desc

#关键字....order by ... asc/desc
#单列排序
select * from table_name order by fieldName asc/desc
#例:
## 查询所有数据,使用年龄降序排序
select * from student order by age desc;

#多例排序
#例子:
##查询所有数据大于20岁的学生,在年龄降序排序的基础上，如果年龄相同再以数学成绩降序排序
SELECT * FROM student WHERE age > 20 ORDER BY age DESC, math DESC;

• 聚合函数

sql中的聚合函数	作用
count()	统计满足条件的记录数
sum()	求和
avg()	求平均值
max()	取最大值
min	取最小值

#格式
select 聚合函数(列名或*) 别名 from table_name;

• 分组查询(group by)

关键字	功能
where	先过滤,再进行分组, 且注意 where 后面不能再使用聚合函数
having	先分组(用再group by 后面),再过滤,后面可以使用聚合函数

#对表中的所有数据进行分组,默认是返回每组的第一行数据,而having可以对分组后的结果再进行过滤
select * from table_name group by fieldName having 条件;

#只会返回分组后的第一条数据
select * from table_name group by fieldName;

• 分页(limit):限制返回的数据行数

#limit 起始行 返回多少行 : 数据的记录从0开始算3条记录数
select * from table_name limit 0,3;

#不能乱写的先后顺序
select * from 表
where 条件
group by 分组列
having 过滤条件
order by 排序列
limit 起始行 , 返回行数;

• 多表查询

-- 内连接
#隐式内连接
select fieldName from left_table , right_table where condition;
#显示内连接
select fieldName from left_table inner join right_table on condition;
-- 外连接
#左外连接
select fieldName from left_table left join right_table on condition;
#右外连接
select fieldName from left_table right join right_table on condition;

---

DCL:数据控制语句

运维和数据库管理员必备

-- 创建新用户
create user '用户名'@'主机名' identified by '密码';
-- 例子:
#创建只能在本地登录的用户
create user 'user1'@'127.0.0.1' IDENTIFIED by '123456';
create user 'user2'@'localhost' IDENTIFIED by '123456';
#创建可以在远程电脑上登录的用户
create user 'user3'@'%' IDENTIFIED by '123456';

-- 给用户分配权限
grant 权限1,权限2,.... on 数据库名.表名 to '用户名'@'主机名';
# 把stu数据库本身的权限都给用户user3
grant create,alter,drop,insert,update,delete,select on stu.* to 'user3'@'%';
grant all no stu.* to 'user3'@'%'; -- 表示stu库下所有表,如果是 *.* 就是所有的库所有的表
flush privileges;--刷新权限

--撤销权限
revoke 权限1,权限2,... no 数据库.表名 from '用户名'@'主机名';

-- 查看权限
show grants for '用户名'@'主机名';

--删除用户
drop user '用户名'@'主机名';

--更改管理员密码 , 不能登录修改,只能在cmd中修改
mysqladmin -u root -p 旧密码 新密码

-- 修改普通用户自己的密码,要先登录
set password for '用户名'@'主机名' = password(新密码);

--修改当前登录用户的密码
set password =  password(新密码);