一. DDL (数据定义语言)
1.create -- 创建xx定义
create database 数据库名;
create table 表名(列定义); (重点)
create table 表名(
列1名 类型 约束 auto_increment,
列2名 类型 约束,
...
);
注:
(1)数据类型分为:
- 整数类型: tinyint(1个字节), smallint(2个字节), int(4个字节), bigint(8个字节)
无符号数字 tinyint unsigned (0~255)
- 浮点类型: float, double
- 定点小数: decimal(总位数, 小数位数)
decimal(10, 2) 小数部分两位,整数部分最大8位
- 字符类型 : char(长度) , varchar(长度)
char(10) 表示最多存10个字符, 定长,效率高
varchar(10) 表示最多存储10个字符,变长例: "abc" "abc " 存储时,长度不足,用空格补齐
"abc" "abc" 存储时,根据实际长度存储,可以节省空间
- 日期类型: datetime , timestamp
(2)约束类型:
* not null 表示此列取值不能为空
* unique 表示此列的取值是唯一的 (一张表可以有多个唯一约束)
* primary key 同时起到了约束的效果, 唯一且非空(一张表只能定义一次)
* mysql 中没有 check约束
* foreign key 外键约束, 某一列的取值来自于另一张表(列必须是主键或唯一的)
(3)primary key 唯一主键
每张表只能有一个主键
主键的值必须是唯一,且非空的
组合主键:
primary key(列1, 列2 ...)
(4)foreign key 外键约束语法
[constraint 约束名] foreign key(本表列名) references 主表(列名)
例: 创建课程表
create table course(
cid int primary key,
cname varchar(20) not null,
tid int,
foreign key (tid) references teacher(tid) //本表中tid这一列的取值,要引用 teacher中的tid列
);
外键总是建在多的一方
(5)auto_increment 自增列,用来解决主键冲突问题
在主键列后加入:auto_increment
1 2 3 4 ....
因为id列由数据库维护,所以有了自增列后就不需要给id列赋值了
创建用户
create user 用户名 identified by '密码';
2.drop -- 删除xx定义
drop database 数据库名;
drop table 表名;
3.alter -- 修改xx定义
alter table 表 ... (添加列, 修改列, 删除列, 重命名列8.0才有)
alter user 用户
- 添加列
语法:alter table 表名 add 列名 数据类型;
例如:给student新增一个age列
alter table stduent add age tinyint unsigned;
- 修改列
语法:alter table 表名 modify 列名 新类型;
例如要修改列的长度定义原来varchar(10)
alter table student modify name varchar(20);
- 删除列
语法:alter table 表名 drop 列名;
- 重命名列
语法:alter table 表名 rename column 旧列名 to 新列名;
二. DML (数据操作语言)
1.insert
语法1: 插入一行
insert into 表名(列...) values(值...); //插入一行
语法2: 插入多行
insert into 表名(列...) values(值...), (值...), (值...), (值...); //插入多行
语法3:从表1查询,把查询结果插入表2
insert into 表2 select * from 表1;
如果两张表结构不一样,可以在select后加具体的列名,以便和新表的列相匹配
例如:
create table student3(
id int primary key,
name varchar(20)
);
insert into student3 select id,name from student;
2.load data
可以把外部文本文件的内容导入到数据库表中
语法:
load data infile '文件路径\\文件名字' into table 表名;
要让load data命令生效,必须修改设置my.ini文件:
[mysqld]
character-set-server=utf8mb4
secure-file-priv=
注:
(1)my.ini改动以后,必须重启mysql的服务
(2)其中secure-file-priv默认是null值,表示不允许加载文件
可以改为具体目录名,表示只能从这个目录加载文件
如果改为"",表示可以从任意目录加载文件
配置之后,可以看到secure-file-priv的值为""
例如:加载heroes.txt,把数据存入hero表中:
先创建hero表
create table hero(
id int primary key,
name varchar(10),
loc varchar(10),
sex char(1),
birth int,
death int,
power int
);
然后运行如下命令
load data infile 'e:\\heroes.txt' into table hero;
如果文件中的列分隔符是, 不是默认\t 键,需要用 fields TERMINATED BY来指定分隔符
load data infile 'e:\\person.txt' into table person fields TERMINATED BY ',';
3.source
source 文件路径/文件名
* 其文件内容必须是合法的sql语句
* 与load的区别:不用引号,建议使用/分隔路径
* 文件编码与操作系统编码一致(gbk)
4. update 更新
语法:
update 表名 set 列名=新值 where 条件;
例如:
update person set sex='男'; // 把所有记录性别都改为男
update person set sex='男' where id=1; // 只修改id=1的性别为男
5.delete 删除
语法:
delete from 表名; // 删除表中所有记录(危险操作)
delete from 表名 where 条件; // 删除满足条件的记录
6.select 查询
语法:
select 列名... from 表 where 条件 group by 分组条件 having 分组筛选条件 order by 排序条件 limit;
- where条件
= 等值匹配
!= 不等值匹配
> 大于
< 小于
>= 大于等于
<= 小于等于
(1)逻辑运算符组合多个条件
逻辑与(两个条件同时成立) and 例如:
select * from hero where sex='女' and loc='建业';
逻辑或(两个条件有一个成立,结果就是真) or 例如:
select * from hero where name='小乔' or id=200;
逻辑非 (条件取反) not
(2)列 between 值1 and 值2 等价于 列 >= 值1 and 列 <= 值2, 注意小值要在前面,包含边界的值
例如:
select * from hero where power between 85 and 90;
等价于
select * from hero where power >= 85 and power <=90;
(3)列 in (值1,值2,... 值n) 等价于 列=值1 or 列=值2 ... or 列=值n 注意值列表的长度
例如:
select * from hero where power in(85,90);
(4) like 模糊查询 其中匹配通配符 % 表示匹配0~多个任意字符
通配符 _ 表示匹配1个任意字符
例如:
select * from hero where name like'马%'; //查询所有姓名以马开头的hero
- 排序条件
排序条件:列名 升降序 如果升降序关键字省略,默认是asc
升序-> 由小到大 asc
降序-> 由大到小 desc
select * from hero order by power desc limit 10;
多列排序: 排序条件1, 排序条件2 ...
先按照条件1排序,条件1中取值相同的,再按照条件2排序
- 限制返回结果个数
limit m; // 最多返回m个结果
limit n,m; // 最多返回m个结果,n代表起始下标,下标从0开始
经常用来实现分页应用,假设每页10条
第一页 limit 0,10;
第二页 limit 10,10;
第三页 limit 20,10;
- 分组条件
select count(*),max(sal),min(sal),sum(sal),avg(sal),deptno from emp group by deptno;
count(*) 表示求每组的个数
max(列) 求最大值
min(列) 求最小值
sum(列) 求和
avg(列) 求平均值
分组之后,
* select子句中只能出现分组条件列和组函数,其他列不能出现在select中,
* order by 子句中只能出现分组条件列和组函数,其他列不能出现在order by中,
例如:
select deptno,max(sal),ename from emp group by deptno; // ename不符合刚才的规定
select deptno,max(sal) from emp order by ename; // 错误的
- having 也是过滤
where > group by > having > select > order by > limit // sql语句的执行顺序
select count(*), deptno from emp where count(*) >=5 group by deptno; // 因为where先执行,这时候还没有分组,不知道个数,错误
正确如下:
select count(*), deptno from emp group by deptno having count(*)>=5;
有时候筛选条件既可以写在where 上,也可以写在having (优先采用where)
select count(*), deptno from emp where deptno=10 or deptno=30 group by deptno;
select count(*), deptno from emp group by deptno having deptno=10 or deptno=30;
- 多列分组 (了解)
多个列取值都相同的分为一组
group by 列1,列2 ...
例如:
select count(*),deptno,job from emp group by job,deptno;
多列分组时,列的顺序不影响结果
- 多表结构和连接查询
select ... from 表1 inner join 表2 on 连接条件
where group by having order by limit;
例如:
select empno,ename,sal,emp.deptno,dept.deptno,dname,loc from emp inner join dept on emp.deptno = dept.deptno;
还可以给表起别名: 表1 表1别名
select empno,ename,sal,e.deptno,d.deptno,dname,loc from emp e inner join dept d on e.deptno = d.deptno;
- 几种连接查询
表1 inner join 表2 on 连接条件 (内连接:两张表的记录必须完全满足连接条件,才会出现在最后结果中)
表1 left outer join 表2 on 连接条件 (左外连接)
表1 right outer join 表2 on 连接条件 (右外连接)
(1)left outer join 位于连接左侧的表,不管是否连接到了记录,都会出现在结果中
符合连接条件的记录,和内连接效果一样
不符合连接条件的记录,对应另一张表的列都是null值
例如:
select empno, ename, e.deptno, d.deptno, d.dname, d.loc from emp e left outer join dept d on d.deptno=e.deptno;
(2)right outer join 位于连接右侧的表,不管是否连接到了记录,都会出现在结果中
outer可以省略
- 连接查询的等价写法
(1)内连接的等价写法 (重要)
select ... from 表1,表2 where 连接条件;
例如:
select e.empno,e.ename,e.deptno,d.deptno,d.dname from emp e, dept d where e.deptno=d.deptno;
(2)mysql 独有的 (了解)
select ... from 表1 inner|left join 表2 using(deptno); // 两张表的连接列名要相同
例如:
select e.empno,e.ename,e.deptno,d.deptno,d.dname from emp e inner join dept d using(deptno);
- 常用函数
select count(*) from emp; // 求整张表的行数
select max(sal) from emp; // 求整张表的工资最大值
mysql>help functions
(1)Bit Functions 位运算函数
(2)Comparison operators 比较运算符
(3)Control flow functions 流程控制
(4)Date and Time Functions 日期函数
year() 截取年份
month()
day() 截取日
date_add(日期 时间间隔); 其中时间间隔的语法:interval n 单位
例:select empno,ename,date_add(hiredate, interval 1 month ),hiredate from emp; 加一个月
select empno,ename,date_add(hiredate, interval 3 day ),hiredate from emp; 加3天
SELECT EXTRACT(DAY_MINUTE FROM '2009-07-02 13:02:03'); 提取日期中的从天到分钟的部分
select now() 获取当前时间
(5)Encryption Functions 加密
(6)Information Functions
(7)Logical operators 逻辑运算符
(8)Miscellaneous Functions 剩余的函数
(9)Numeric Functions 数学函数
mysql> help Numeric Functions
...
mysql> select RAND();//函数调用
rand() 生成一个从[0.0 ~ 1.0) 之间的随机小数, 小于1.0
floor() 舍去小数
round() 四舍五入
(10)String Functions 字符串函数
left(字符串,n) n代表从左边要截取的字符
mysql>select left('abcde',3);//abc
lower() 转小写
upper() 转大写
substr(字符串,下标,长度) 下标从1开始
mysql>help substr //查函数
length() 求字节个数
char_length 求字符串里字符的个数
求字符串长度的例子:select * from hero where char_length(name)=4;
三. DCL (数据控制语言)
grant 授权
revoke 回收权限
授权语法:
grant 权限 to 用户名;
例如:把查询test库中所有表的查询权限授权给user1
grant select on test.* to user1;
回收权限:
revoke 权限 from 用户名;
例如:回收之前分配的权限
revoke select on test.* from user1;