增删改查等
创建数据库
避免数据库已经存在而报错,可以使用if not exists
避免后续添加的表数据中有中文时报错,可以指定字符集character set utf8mb4
create database if not exists 数据库名 character set utf8mb4 collate utf8mb4_general_ci;
查看所有数据库
show databases;
删除数据库
会删除数据库及其所有数据,操作不可逆
避免数据库不存在而报错,可以使用if exists
drop database if exists 数据库名;
进入数据库
use 数据库名;
在连接数据库时也可以直接指定数据库
mysql -uroot -p -D数据库名
创建表格
create table 表名(列1 数据类型1,列2 数据类型2,...);
查看所有表格
show tables;
删除表格
会删除表格及其所有数据,操作不可逆
避免表格不存在而报错,可以使用if exists
drop table if exists 表名;
查看表结构
desc 表名;
添加列
alter table 表名 add 列名 数据类型;
修改列
alter table 表名 modify 列名 新数据类型;
删除列
alter table 表名 drop 列名;
增加表数据
insert into 表名(列名1,列名2,...) values(值1,值2,...);
查询表数据
select * from 表名;
可加where 条件
指定条件
修改表数据
update 表名 set 列名1=值1,列名2=值2...;
可加where 条件
指定条件
删除表数据
delete from 表名;
可加where 条件
指定条件
子句
指定条件where
过滤结果,只返回满足条件的数据
select * from 表名 where 条件;
update 表名 set 列名=值 where 条件;
delete from 表名 where 条件;
操作符可在 where 子句中使用,见后面操作符
查询结果分组group by
根据一个或多个列对结果集分组,一般结合sum,count,avg等聚合函数使用
select 列名 from 表名 group by 列名;
查询结果筛选having
一般和gruop by配合使用。where只可以对表中数据进行筛选
select 列名 from 表名 group by 列名 having 限制条件;
例:查找最低成绩大于90的科目:select c_name from score group by c_name having min(grade)>80;
查询结果排序order by
默认升序
降序desc:
select * from 表名 order by 列名 desc;
升序asc:
select * from 表名 order by 列名 asc;
限制结果条数limit
select * from 表名 limit 起始位置,显示多少条;
函数
聚合函数
最大值max
select max(列名) from 表名;
最小值min
select min(列名) from 表名;
平均值avg
select avg(列名) from 表名;
求和sum
select sum(列名) from 表名;
计数count
select count(列名) from 表名;
运算符
比较运算符
比较运算符 | 描述 |
---|---|
between and | 选取介于两个值之间的数据 |
like | 模糊匹配指定模式的数据 |
in | 匹配表达式中任一值的数据 |
is null | 判断列值是否为空 |
= | 等于 |
> | 大于 |
< | 小于 |
>= | 大于等于 |
<= | 小于等于 |
<> | 不等于 |
between and
选取介于两个值之间的数据,包含边界值,可以是数值、文本或者日期
select * from 表名 where 列名 between A and B;
例:查询在1988和1990之间出生的学生:
select * from student where birth between 1988 and 1990;
like
模糊匹配指定模式的数据
%代替0或多个字符
_代替一个字符
[字符列]匹配字符列中的任何单一字符(要用rlike或者regexp)
[^字符列]不在字符列中的任何单一字符(要用rlike或者regexp)
select * from 表名 where 列名 like '%匹配的文字%';
例:查询地址中包含昌平的学生:
select * from student where address like '%昌平%';
例:查询地址以北或者福开头的学生:
select * from student where address rlike '^[北福]';
例:查询地址以湖北省开头但第四个字不是衡的学生:
select * from student where address rlike '湖南省[^衡]';
in
匹配表达式中任一值的数据
select * from 表名 where 列名 in(值1,值2,...)
例:查询专业在中文系和英语系的学生:
select * from student where department in('中文系','英语系');
is null
判断列值是否为空
select * from 表名 where 列名 is null;
逻辑运算符
逻辑运算符 | 描述 |
---|---|
and | 并且,多个条件要都成立 |
or | 或者,多个条件中只要有一个成立 |
not | 否定一个布尔表达式的值 |
and
并且,多个条件要都成立
select * from 表名 where 列名='列值' and 列名='列值';
例:查询在中文系的男学生:
select * from student where sex='男' and department='中文系';
or
或者,多个条件中只要有一个成立
select * from 表名 where 列名='列值' or 列名='列值';
例:查询在中文系的学生或者男学生:
select * from student where sex='男' or department='中文系';
not
否定一个布尔表达式的值,可以和其他语句条件和运算符配合使用
not null:创建表时,设定某列不允许空值
create table student (id INT(10) NOT NULL UNIQUE PRIMARY KEY ,name VARCHAR(20) NOT NULL ,sex VARCHAR(4) ,birth YEAR ,department VARCHAR(20) ,address VARCHAR(50));
not in
not like
not between and
not and
not or
算术运算符
算术 | 描述 |
---|---|
+ | 加 |
- | 减 |
* | 乘 |
/ | 除 |
% | 模 |
数据类型
文本、数字和日期/时间类型
其他
指定别名as
为列名和表名指定别名
select 表别名.列名 from 表名 表别名;
例:查询计算机成绩低于95的学生姓名和成绩:
select st.name,sc.grade from student as st join score as sc on st.id=sc.stu_id where sc.c_name='计算机' and sc.grade<95;
过滤重复值distinct
去掉重复值
select distinct 列名 from 表名;
连接join
将多个表中的数据连接在一起,根据两个或多个表之间的关联关系,将它们的行合并在一起,形成一个新的结果集
select 列名 from 表名1 join 表名2 on 表名1.匹配的列名1=表名2.匹配的列名2;
join
表中有至少一个匹配,则返回行,inner join与join是一样的
select * from student as st join score as sc on st.id=sc.stu_id;
left join
即使右表中没有匹配,也从左表返回所有的行
select * from student as st left join score as sc on st.id=sc.stu_id;
right join
即使左表中没有匹配,也从右表返回所有的行
select * from student as st right join score as sc on st.id=sc.stu_id;
连接union
连接两个以上的select语句的结果集合,去除重复的行,每个select语句的列数和对应位置的数据类型必须一样
select 列名1,列名2,... from 表名1 where 条件1 union select 列名1,列名2,... from 表名2 where 条件2;
例:将两表中的计算机系的学生筛选出来,并按照id升序:
select * from new_test where department='计算机系' union select * from student where department='计算机系' order by id;
不去掉重复值可以使用union all
约束
创建或者修改表时对列/值进行约束
约束 | 描述 |
---|---|
NOT NULL | 非空约束,不接受空值 |
UNIQUE | 唯一约束,一个表中可以有多个 |
PRIMARY KEY | 主键约束,必须唯一,不能有空值,一个表中只能有一个 |
FOREIGN KEY | 外键约束,外键列指向另一个表的主键,防止非法数据插入外键列 |
DEFAULT | 默认值,新增数据没值时插入默认值 |
例:创建一个学生表,id不能为空,主键,唯一,专业默认其他系:
create table student2 (id INT(10) NOT NULL UNIQUE PRIMARY KEY ,name VARCHAR(20) NOT NULL ,sex VARCHAR(4) ,birth YEAR ,department VARCHAR(20) DEFAULT '其他系',address VARCHAR(50));
新增数据时没有给department赋值,默认其他系:
insert into student2(id,name,sex,birth,address)values(0001,'张老大', '男',1985, '北京市海淀区');
复制表
create table new_test like student;
insert into new_test select * from student;