Mysql操作
- 登陆
mysql -u用户名 -p密码
- 添加数据库
create database 数据库库名;
- 删除数据库
drop database [if exists] 数据库库名;
- 查看已有数据库
show databases;
- 选择\进入数据库
use 数据库库名;
- 查询数据库中的表
show tables;
- 展示数据库引擎
show variables like 'storage engine%';
- 数据库的备份【cmd方法】
mysqldump -u用户名 -p 数据库名 表名 > D:\table.sql
表的操作
-
创建表
-
create table 表名 ( 字段名 数据类型(长度) [属性] ); # 案例 CREATE TABLE 表名( name VARCHAR(20) NOT NULL );
-
-
删除表
drop table 表名;
-
更改表名
alter table 旧表名 rename 新表名;
-
查看表结构
desc 表名;
-
查看创建的数据库属性
show create database 数据库库名;
-
在表中插入数据
insert into 表名 value (数据1,数据2,...);
:插入一行数据insert into 表名 values (数据1,数据2,...),(数据1,数据2,...);
:插入多行数据insert into 表名 (字段名1,字段名2,...) value (数据1,数据2,...);
:插入一行指定列的数据
-
修改数据库编码方式
alter database 数据库库名 default character set utf8 collate utf8_bin;
表中数据的查询
-
查询表中的所有数据
select * from 表名;
-
查询表中指定列的数据
select 列名 from 表名;
-
查询某列中数据为 x 的记录
select * from 表名 where 列名 = x;
-
查询在 IN 范围的数据【当该列的记录中包含任意x,则条件成立,输出该记录】
select * from 表名 where 列名 in(x1,x2,...);
-
查询在x1~x2之间的数据【大于等于"x1",小于等于"x2"】
select * from 表名 where 列名 between x1 and x2;
-
查询、去重复信息【distinct】
select distinct 列名 from 表名;
-
模糊、关键字查询
select * from 表名 where 列名 like '%xx';
- %:表示0个或多个字符
- _:表示单个字符
-
and 并且/ or 或者【and 优先级大于 or】
-
select * from 表名 where 列名1 = xx and/or 列名2 = xx;
-
查询数据并给字段加别名
select 列名 别名 from 表名;
select 列名 as 别名 from 表名;
-
统计总数 count【总记录数】
select count(列名) from 表名;
-
统计种类 distinct【不同数据】
select count(distinct 列名) from 表名;
-
查询 总和/最大值/最小值/平均值【sum/max/min/avg】
select sum(列名) from 表名;
-
排序 order by【默认由小到大 ASC , 由大到小 DESC】
select * from 表名 order by 列名 ASC;
-
分组查询 group by【单独使用只查询每个分组的第一条记录】
- 单独使用:
select * from 表名 group by 列名
- 联合使用:
select count(*) from 表名 group by 列名;
- 单独使用:
-
Having(Where)【与where区别是having后可跟聚合函数 sum…,要和group by一起使用】
Having sum(...)<1300;
-
拼接查询结果字符串 group_count(字段名)
-
select group_count(字段名) from 表名 group by 字段名; #案例 SELECT COUNT(*) 在校老师数量,GROUP_CONCAT(tname) 老师名字 FROM teacher WHERE comments = '在校';
-
在校老师数量 老师名字 3 张三,李四,王五
-
-
在记录的最后一行添加一条汇总记录 with rollup【GROUP BY WITH ROLLUP】
-
SELECT department,pos,AVG(salary) FROM employee GROUP BY department,pos; +------+------+-----------+ | department | pos | AVG(salary) | +------+------+-----------+ | 01 | 01 | 1500.0000 | | 01 | 02 | 1950.0000 | | 02 | 01 | 1500.0000 | | 02 | 02 | 2450.0000 | | 03 | 01 | 2500.0000 | | 03 | 02 | 2550.0000 | +------+------+-----------+ SELECT department,pos,AVG(salary) FROM employee GROUP BY department,pos WITH ROLLUP; +------+------+-----------+ | department | pos | avg(salary) | +------+------+-----------+ | 01 | 01 | 1500.0000 | | 01 | 02 | 1950.0000 | | 01 | NULL | 1725.0000 | | 02 | 01 | 1500.0000 | | 02 | 02 | 2450.0000 | | 02 | NULL | 2133.3333 | | 03 | 01 | 2500.0000 | | 03 | 02 | 2550.0000 | | 03 | NULL | 2533.3333 | | NULL | NULL | 2090.0000 | +------+------+-----------+
-
-
显示前几条记录
limit 偏移量(开始下标),待取记录数;
-
显示第几页记录【以一页10条记录为例】
select * from 表名 limit 0,10;
多表查询
- 交叉连接【返回笛卡尔积】
select * from 表1 cross join 表2;
- 内连接【使用比较运算符对两个表中的数据进行比较,列出与连接条件匹配的数据行,组成新纪录】
select 查询字段 from 表1 [inner] join 表2 on 表1.关键字段 = 表2.关键字段;
- 自连接【自己连接自己】
- 外连接【符合条件的和一些不符合条件的都显示出来】
- 左连接【显示左表中的所有记录和右表中的符合条件的记录】
- 右连接【显示左表中的符合条件的记录和右表中的所有记录】
select 所查字段 from 表1 [left/right/outer] join 表2 on 表1.关系字段 = 表2.关系字段 where 条件;
表中记录更新
- 更新记录【所有列的记录都会变】
update 表名 set 列名1 = 新值, 列名2 = 新值;
- 更新某列数据为 x 的记录
update 表名 set 列名 = 新值 where 某列名 = x;
表中记录删除
- 删除表中记录
delete from 表名 where 字段名 = 要删除行的字段值;
表字段操作
- 修改表里的 字段名和类型
alter table 表名 change 旧字段名 新字段名 新数据类型;
- 修改表里字段的 数据类型
alter table 表名 modify 字段名 新数据类型;
- 在表的最后添加一个字段
alter talbe 表名 add 字段名 数据类型;
- 在表中 XX 字段后添加一个字段
alter table 表名 add 新字段名 新数据类型 [约束条件] first/(+after 旧字段名);
- 修改字段排列顺序
alter table 表名 modify 字段名1 数据类型 first/(after 字段2);
表的约束
- 主键【primary key】能够在表中唯一的指定行。
- 自增【auto_increment】不能独自存在,必须和主键或唯一存在。【先删自增,再删主键】
- 非空【not null】
- 唯一【unique】
- 默认【default】
- 删除非空约束
alter table 表名 modify 列名 数据类型 null;
- 删除自增
alter table 表名 modify 列名 数据类型;
- 删除主键
alter table 表名 modify 列名 数据类型 not null;
alter table 表名 drop primary key;
- 删除默认值
alter table 表名 alter 列名 drop default;
- 删除唯一
alter table 表名 drop key 约束名;
- 约束名可以自定义,如果没有定义,默认为列名
- 设置外键
constraint fk_id foreign key (本表字段) references 引用表(引用的列);
- 删除外键
alter table 表名 drop foreign key 外键名;
alter table 表名 drop key fk_id;
- 添加外键
alter table 表名 add constraint fk_id foreign key (本表字段) references 引用表(引用的列);
- 外键的注意事项:外键可以为 null,可以有多个外键,外键链接的字段必须唯一,外键的引擎必须为 Innodb,一定是先加引用表再加子表
视图
-
创建视图
create view 视图名 as SELECT语句;
- 视图名:指定视图的名称。该名称在数据库中必须是唯一的,不能与其他表或视图同名。
- SELECT语句:指定创建视图的 SELECT 语句,可用于查询多个基础表或源视图。
-
删除视图
drop view 视图名
-
查看视图的字段信息
desc 视图名;
describe 视图名;
-
查看视图的详细信息
show create view 视图名;
-
修改视图
alter view 视图名 as SELECT语句;
create or replace view 视图名 as SELECT语句;
索引
- 普通索引:INDEX
- 唯一性索引:UNIQUE
- 主键索引:PRIMARY KEY
- 空间索引
- 全文索引:FULLTEXT
-
创建索引
-
#【建表时】 create table 表名( 列名x1 varchar(20), index 索引名(列名x1) ); #【建表后】 create index 索引名 on 表名(列名);
-
-
修改索引
alter table 数据库名 add index 索引名(列名);
-
删除索引
drop index 索引名 on 表名;
-
查看索引
show index from 表名;
事务
- 开始事务:
BEGIN;
或START TRANSACTION;
- 提交事务:
COMMIT;
- 回滚(撤销)事务:
ROLLBACK;
注意事项
- 事务尽可能简短
- 事务中访问的数据量尽量最少
- 查询数据时尽量不要使用事务
- 在事务处理过程中尽量不要出现等待用户输入的操作