1.mysql的组成
1.1 mysql 数据库的服务器,适合和java一起使用
1.2 MySQL自带的客户端 command line client:命令行
登录:默认的用户名: root
密码:我们在安装时设置的(一定要记住)
常用命令:
create database 数据库名:新建数据库
drop database 数据库名:删除数据库
show databases: 显示当前服务器中所有的数据库
use test:切换到test数据库;
show tables:显示test数据库中所有的表
desc 表名: 显示表的结构
exit:退出
1.3 mysql的运算符
a.算术: + - * / mod()
b.关系: > >= < <= = !=(<>) between…and,like,in
c.逻辑: not and or
d.通配符: %:任意多个字符 _:任意一个字符 和like连用
1.4 navicat:第三方客户端,gui,简单,易用
2.创建表
2.1 创建表的语法
create table 表名
(
id int auto_increment primary key,
name varchar(50) not null,
pwd varchar(15) not null,
status int default(1)
)
auto_increment:自增
primary key:主键(唯一,非空)
not null:非空
default(1):默认约束
2.2 外键的写法( 外键就一定有个对应的主键,主键所在的表就是主表)
- create table questionInfo (
id int auto_increment primary key,
subjectId int not null references subjectInfo(id),–外键
remark varchar(500)
) - create table questionInfo(
id int auto_increment primary key,
subjectId int not null,–外键
remark varchar(500),
FOREIGN KEY(subjectId) REFERENCES subjectInfo(id)
) - create table questionInfo(
id int auto_increment primary key,
subjectId int not null,
remark varchar(500)
)
配置外键
alter table questionInfo
add constraint foreign key(subjectId) references SubjectInfo(id);
修改表questionInfo
添加约束 外键(外键) 联系 SubjectInfo主表(id主键
3.SQL的分类 - DDL:Data Define Language 数据定义语言
作用:搭建数据库对象(数据库,表,视图,用户,角色,权限,索引,函数过程,触发器等)
a.创建:create database|table|view|user|role… 对象名 […];
b.修改结构:alter database|table …
c.删除 :drop database|table 对象名 - DML: Data Manager Language 数据操作语言
作用:操作数据(表)
a.增:insert into 表…
b.修改:update 表…
c.删除:delete from 表 where …
d.查询:select…from 表 where… - DCL: Data Control Language 数据控制语言(数据库管理员)
作用:控制数据的访问权限
a.赋权限 root拥有所有权限 grant 权限|角色 to 用户;
b.回收权限:revoke 权限 from 用户; - TCL:Transaction Control Language 事务控制语言
a.提交事务:commit;
b.回滚事务:rollback;
c.设置保存点:savepoint;
4.DML(增删改查)
1. 查询
a.查询
1) 基本查询
select * from 表; 查询表的所有的行和列的数据
*:所有列 emp:取数据的地方(表|结果集中)
尽量少用:* : 效率低
select 列名|计算|常数 from 表名|视图|结果集
2) 加条件
where子句 条件:对数据的行的筛选
select xx from 表
[where 条件]
[order by xx [asc|desc]]
[group by xx having 子句]
注意:where 条件中的列必须出现在from后面的结果集(表|查询结果)中
in/not in
between..and
like
3) as关键字的使用
-- '男' as sex:表中不存在的数据
select ename,job,sal,'男' as sex from emp;
4) limit关键字的使用
limit:取结果集中前x行数据
select * from 表名 order by 列名 desc limit x;
limit x,y:从x行开始取,取y行数据,注意x:从0开始
select * from 表名 order by 列名 desc limit x,y;
5) 排序
order by 列1 排序规则,列2 排序规则
asc :默认:升序
desc:降序(从大到小)
字符,数字,日期都能做排序
order by 后面的列可以使用虚拟列(没有在from后面的结果中)
6) 分组
group by 列1,列2:越靠近group by 的优先级越高
group by + having
having :在分组数据上面做筛选
分组 & 聚合函数(统计函数)
注意:聚合函数不会统计null
7) 表联接(a.内联(自联接) b.外联)
将多个表的数据列合成一张表,一般是有主外键关系的表
内联:两个表都有的数据才会显示
语法:
select .,, from tab1 ,tab2
where tab1.主键 = tab2.外键
select .... from tab1 inner join tab2
on tab1.主键 = tab2.外键
外联:以谁为主,为主的表的数据都出来,从表没有的数据用null填充(左外联 & 右外联)
左外联:看左表 左表的数据都能显示(一般主建表),
右表展示与之对应的,如果没有与之对应的数据,用null填充
LEFT [outer] JOIN... on 关系列
在表联接中如果多个表有相同的列名,一定要用别名区分
select d.deptno,dname,loc,ename,sal
from dept(主表) d left join emp(从表) e
on d.deptno = e.deptno;
全连:内联+左外联+右外联:full join
交叉联:cross join
总结:表联接:把多张表联接成一张表,再做条件筛选,效率很低
8) 子查询
语法 : select .... from 表 where xx = (select xx from 表);
= :表示子查询中只有一个数据,
查询的数据在表1,但是条件数据在表2
查询的数据在同一张表,但是条件的数据不直接,需要进行计算/统计
DISTINCT :去重复
b.mysql--常用函数
1) 字符函数
LCASE(s)/LOWER(s): 将字符串 s 的所有字母变成小写字母
UCASE(s)/UPPER(s):将字符串转换为大写
LEFT(s,n):返回字符串 s 的前 n 个字符
RIGHT(s,n):返回字符串 s 的后 n 个字符
LPAD(s1,len,s2):在字符串 s1 的开始处填充字符串 s2,使字符串长度达到 len
RPAD(s1,len,s2):在字符串 s1 的结尾处添加字符串 s2,使字符串的长度达到 len
LTRIM(s):去掉字符串 s 开始处的空格
RTRIM(s):去掉字符串 s 结尾处的空格
TRIM(s):去掉字符串 s 开始和结尾处的空格
MID(s,n,len):从字符串 s 的 n 位置截取长度为 len 的子字符串,同 SUBSTRING(s,n,len)
SUBSTR(s, start, length):从字符串 s 的 start 位置截取长度为 length 的子字符串
REPEAT(s,n):将字符串 s 重复 n 次
REPLACE(s,s1,s2):将字符串 s2 替代字符串 s 中的字符串 s1
REVERSE(s): 将字符串s的顺序反过来
2) 日期函数
select curdate():返回当前日期
SELECT SYSDATE():返回当前日期和时间
SELECT CURRENT_DATE();返回当前日期
SELECT CURRENT_TIME();返回当前时间
SELECT NOW():返回当前日期和时间
SELECT CURTIME();返回当前时间
SELECT DATE("2017-06-15");从日期或日期时间表达式中提取日期值
SELECT SUBDATE('2011-11-11 11:11:11', 1):计算日期 d 是本年的第几天
SELECT MONTH('2011-11-11 11:11:11'):返回日期d中的月份值,1 到 12
3) 统计函数
sum():求总
avg():求平均
max():求最大
min():求最小
count():求个数
4) 数学函数
ABS(x):返回 x 的绝对值
CEIL(x):返回大于或等于 x 的最小整数
FLOOR(x):返回小于或等于 x 的最大整数
MOD(x,y):返回 x 除以 y 以后的余数
PI():返回圆周率(3.141593)
POW(x,y)/POWER(x,y): 返回 x 的 y 次方
RAND():返回 0 到 1 的随机数
ROUND(x):返回离 x 最近的整数
TRUNCATE(x,y):返回数值 x 保留到小数点后 y 位的值(与 ROUND 最大的区别是不会进行四舍五入)
5) 高级函数
USER():返回当前用户
IFNULL(v1,v2):如果 v1 的值不为 NULL,则返回 v1,否则返回 v2。
ISNULL(expression):判断表达式是否为 NULL
NULLIF(expr1, expr2):如果表达式 expr 成立,返回结果 v1;否则,返回结果 v2
CAST(x AS type):转换数据类型
2. 添加(一行的加)
注意点:
1).char,varchar,date,datetime 数据两边用'',sql不区分大小写,但是数据区分
2).和日期相关 '2000-09-32",获取系统的当前时间 currDate()
3).和外键:外键的数据一定从主表的主键中任选其一
2.1 insert into 表 values(): 给整个表添加数据,数据的数量和表一致,顺序和查询出来一致
insert inot 表(列名,列名,...) values():给表非空列加数据
2.2 一次添加多行
insert into 表[(列,...)]
select 值 ... union
select 值;
insert into 表
select ... from 源表 [where 条件];
2.3 表复制
create table 表
as select ... from 源表[ where 条件];
3. 修改 :修改一行或者多行的数据
select * from stuInfo;
注意:
1).尽量不要修改主键数据
2).一定注意修改时加where条件,全表修改(提前做备份)
3).尽量where条件选择主键(唯一)
4. 删除 :一次删除一行或多行
delete from 表 [where 条件]
注意:
1).创建外键关系时,删除和更新用restrict,不要用级联
2).有外键的主键不能删除,如果非得删除,必须先删外键表,再删除主键
3).使用条件时,尽量用主键