MySQL常用操作分栏整理（一）

MySQL常用操作分栏整理（一）

1.数据库的基本操作

1. 启动数据库：net start mysql
2. 客户端登录服务器：mysql -u root -p 远程登录服务器：mysql -h ip地址 -u root -p
3. 展示服务器中有哪些数据库：show databases;
4. 创建一个新的数据库：create database pgbd/数据库名; 查看刚创建好的数据库：show create database pgbd/数据库名;
5. 指定要操作的数据库：use pgbd/数据库名;
6. 查看当前数据库有哪些数据表：show tables;
7. 删除某个数据库：drop database pgbd/数据库名;
8. 退出服务器连接：exit;

2.数据表的基本操作

(show数据库-create数据库-show数据库-use数据库-create表-查看表-查看表结构）

操作

1. 创建信息表： create table 表名(字段 属性 约束);

2. 每类数据的数据类型：
   (1)字符串 char（固定长度，设置固定长度之后，不足用空格补齐（电话号码，身份证号等）），varchar（20）（可变长度，小于等于固定长度均可），blob或text（二进制大数据，图片和音频）
   (2)数值 int，float(m,n）m为几位数据，m为小数位占几位 ,double(m,n)，
   (3)日期 date.(年月日) time（小时分秒）datetime（SS格式）/timestamp（时间戳）(年月日和小时分秒都有)
   注意！！！一般情况：凡是整数都用int（不限定字符），放不下用bigint，小数用float和double（有损失）十分精确的表示，精度十分高用decimal。

3. 对数据进行约束：
   (1)主键约束（primary key，具有唯一性，不能为空），
   (2)非空约束（NOT NULL），唯一性约束（unique 唯一性，可以为空），
   (3)默认约束（default 默认的值，指定某个字段的默认值，若新插入的记录没有为该字段赋值，则为默认值）
   (4)外键约束(foreign key(引用的字段) references 被引用的表(被引用的字段)，两者的数据类型必须一样，引用中插入值时，被引用的表必须有该值，删除时，先删除引用字段的值，再删除被引用的字段。)
   约束有两种方法：（1）直接在数据后加约束（empid int primary key,），（2）在最后加约束，适用于加多个约束（primary key(depid, deptname)）

4. 删除数据表：drop table employees/表名;

5. 查看表结构:desc employees;

6. 向表中插入数据: insert into 表名 values(字段值列表);

7. 批量插入数据：insert into 表名 values(字段值列表), (字段值列表), (字段值列表);

8. 查询表中的记录：select 字段列表 from 表名;

9. 查看创建表的语句：show create table employees\G;(G为垂直显示)

10. 将查询结果保存到另外一张表中，有两种方式：
    （1） 先创建一张新数据表，然后使用insert into …
    select …
    （2） 直接使用create table … select …

11. 加载外部数据文件到数据表中： load data local
    infile ‘文件路径’ into table products fields terminated by
    ‘分割形式’; （分割形式为,或者\t）文件路径中不能包含中文，且为左斜杠。
    一般情况导入外部文件到mysql可能会出现错误。
    (mysql报错：ERROR 1148 (42000): The
    used command is not allowed with this MySQL version
    解决方法：
    show global variables like ‘local_infile’;（查看local_infile是否打开）
    set global local_infile=‘ON’;（打开local_infile）
    mysql --local-infile=1 -uroot -p（重新启动数据库）
    .csv文件要以utf8格式编码保存（数据中有中文内容）

12. 修改表中的数据：（1）update 表名 set X 条件;
    (2) 处理null值 对于null值只能使用is null，is not null，不能使用=

13. 删除表中的记录：delete from 表名 条件; 不加条件为全部删除

14. 修改数据表的结构：alter table 操作
    （1）修改表名 alter table 表名 rename新表名;
    （2）修改表中字段的数据格式 alter table 表名 modify 字段 数据格式;
    （3）添加新的字段 alter table 表名 add 字段 数据格式;
    （4）修改字段在表中的位置 alter table 表名 add 字段 数据格式 first;
    （5）删除表中的字段：alter table 表名 drop 字段;

案例

（1） 员工信息表分析：employees
列：工号 姓名 性别 年龄 部门 薪水
列：empid empname gender age dept salary
（2） 部门信息表分析：departments
列：部门id 部门名称 所在城市
列：depid deptname location

创建员工信息表：
create table employees(
empid int primary key,
empname varchar(20) not null,
gender char(1) default ‘F’,
age int,
deptid int,
salary float(8,2),
foreign key(deptid) references
departments(depid)
);

创建部门信息表： create table departments(
depid int,
deptname varchar(100) unique,
location varchar(50),
primary key(depid)
);
插入数据：
insert into departments values(101,‘技术研发部’,‘北京’);
insert into departments values(102,‘市场部’,‘北京’);
insert into departments values(103,‘财务部’,null); 不知道值就填写null;
insert into departments(depid,deptname) values(104,‘公共关系部’);
很多不知道，从departments中加括号，填写需要填充的字符，其他为null。
insert into departments values(105,null,null);
insert into employees values(301,‘李四’,‘M’,22,102,13000);
insert into employees values(302,‘张三’,‘M’,22,103,12000);
insert into employees(empid,empname) values(303,‘张三2’);
批量插入：
insert into employees values(304,‘李四’,‘F’,27,102,13600), (305,‘张三’,‘M’,22,101,12500),
(306,‘张三2’,‘M’,22,104,14000);
查询表中数据：
select depid,deptname,location from departments;
select * from departments; * 表示全部显示
in 用来查询满足指定范围内的条件的记录
如：从fruits中查询s_id为101和102的记录：select *
from fruits where s_id in(101,102);
查看创建表的语句
show create table departments;
将查询结果保存到另外一张表：
（1）Create table emp2(
empid int,
empname varchar(20),
salary float(8,2)
);
insert into emp2 select empid,empname,salary from employees;
(2) create table emp3 select empid,empname,salary from employees;
(3) create table emp4 select empid as id ,empname as name,salary as
salary from employees; 为新创建的表设置别名 a as b
加载外部数据文件到数据表中：
load data local infile ‘C:/Users/Administrator/Desktop/p.csv’ into table products fields terminated by ‘,’;
load data local infile ‘C:/Users/Administrator/Desktop/p.txt’ into table products ;不用fields terminated by 默认为用tab键分开
load data local infile ‘C:/Users/Administrator/Desktop/p.txt’ into table products fields terminated by ‘\t’;
修改表中的数据：
将月薪低于5000的员工的工资上调10%
update employees set salary=salary1.1 where salary<5000;
将月薪低于5000的员工的工资上调10%，并调动到101部门
update employees set salary=salary1.1,deptid=101 where salary<5000;
将products中所有商品打八折
update products set price=price*0.8;
将products中所有未标明价格的商品，定价为10元
update products set price=10 where price is null;
删除表中的记录：
将products 中商品名未知的那些记录删除
delete from products where pname is null;
将月薪超过13000的员工辞退
delete from employees where salary>13000;
将月薪超过12500的员工或者女性员工辞退
delete from employees where salary>12500 or gender=‘F’;
将公司不属于103部门的男员工都删除
delete from employees where deptid !=102 and gender=‘M’;
删除所有数据
delete from employees
修改数据表的结构：
修改表名
alter table departments rename dep;
修改表中字段的数据格式
alter table employees modify empname varchar(100);
添加新的字段
alter table dep add aaa varchar(100);
修改字段在表中的位置
alter table dep add sss varchar(100) first;
删除表中的字段
alter table dep drop sss;

3.单表查询的基本操作

（单表查询是指从一张数据表中查询出所需要的数据 – 在select语句中可以使用星号”*”通配符查询所有字段）

操作

1. 查询指定记录（=,!=,<，>,>=,<=,between…and…）select 字段 from 表名 where 条件
2. 带in关键字的查询 （ in操作符用来查询满足指定范围内的条件的记录）select 字段 from 表名 where 字段 in 条件
3. 带between…and的范围查询 （between and用来查询某个范围内的值，包含端点）select 字段 from 表名 where 字段between 下限 and 上限
4. 带like的字符匹配查询(模糊查询) SQL语句中可以和like一起使用的通配符有’%‘和‘_ ’
   百分号通配符’%’，匹配任意长度的字符，甚至包括零字符，’_'匹配一个字符。select 字段 from 表名 where 字段 like “条件”
5. 空值比较: is null 或者 is not null – null不同于空字符串。空值一般表示数据未知、不适用等。在select语句中使用is null子句，可以查询某字段内容为空的记录。
   select 字段 from 表名 where 字段 is null;select 字段,is null(字段,别名) from 表名;
6. 多条件查询: and or。可以使用and接连两个甚至多个查询条件。and的优先级要高于or的优先级。
   select 字段 from 表名 where （字段+条件 or 字段+条件） and字段+条件
7. 查询结果不重复 ：在select查询时，可以使用distinct关键字告诉MySQL消除重复的记录，关键字distinct，放在去重的字段前,count()统计满足条件的值
   select distinct 字段 from 表名;select count(distinct 字段) as 统计结果的名字 from 表名;
8. 对查询结果排序 ：使用order by子句对查询结果排序，ordery by永远是最后一个句子 。
   select 字段列表 from 表名 where 过滤条件 order by 字段 [desc];
9. 分组查询：分组查询是对数据按照某个或多个字段进行分组 。MySQL使用group by关键字对数据进行分组。group by关键字通常和集合函数一起使用。
   select 字段 集合函数 from 表名 group by 字段;
10. 分组查询-排序 ：对分组查询出来的结果再排序（将查询和分组的功能结合起来即可）
    例子：–找出每个供应商提供的水果的种类，并排序 ：
    select s_id, count() as ‘商品种类’ from fruits group by s_id order by ‘商品种类’;
    或者
    select s_id, count() as ‘商品种类’ from fruits group by s_id order by count(*);
11. 分组查询-过滤 ：group by可以和having一起限定显示记录所需满足的条件，只有满足条件的分组才会被显示。
    select 字段 集合函数 from 表名 group by 字段 having 条件;
    例子：
    – 查询出所供商品种类超过一种的那些供货商的信息
    select s_id, count() ,group_concat(f_name) from fruits group by s_id having count()>1;
12. 分组查询-统计总和：在group by子句中可以使用with rollup。使用with rollup关键字之后，在所有查询出的分组记录之后会增加一 条记录，该记录计算查询出的所有记录的总和，即统计记录数量。
    select 字段 集合函数 from 表名 group by 字段 with rollup;
    例子：
    –根据供应商id对fruits表中的数据进行分组，并显示记录数量
    select s_id,count(*) ‘商品种类’ from fruits group by s_id with rollup;

限制查询结果的数量: 关键字limit。只有MySQL数据库支持limit关键字。
select * from 表格 limit 偏移量,行数量
14. 使用limit实现分页查询算法：– 假设page代表查询第几页，pageSize代表每页多少条记录，起始索引：(page-1)*pageSize
例子：
select 字段from 表名 limits (page-1)*pageSize, pageSize;
查询第3页的数据记录，每页显示5条记录
select * from fruits limits10, 5;
15. case when语句：用于计算条件列表并返回多个可能结果表达式之一。
select 字段,
case
when条件 then 结果
when条件 then 结果
else 结果
end
as 别名
from 表名;

案例

– 创建fruits表
create
table fruits(
f_id char(10) not null,
s_id int not null,
f_name char(255) not null,
f_price decimal(8,2) not null,
primary key(f_id)
);
– 插入样本数据
insert into fruits(f_id,s_id,f_name,f_price)
values(‘a1’,101,‘apple’,5.2),
(‘b1’,101,‘blackberry’,10.2),
(‘bs1’,102,‘orange’,11.2),
(‘bs2’,105,‘melon’,8.2),
(‘t1’,102,‘banana’,10.3),
(‘t2’,102,‘grape’,5.3),
(‘o2’,103,‘coconut’,9.2),
(‘c0’,101,‘cherry’,3.2),
(‘a2’,103,‘apricot’,25.2),
(‘l2’,104,‘lemon’,6.4),
(‘b2’,104,‘berry’,7.6),
(‘m1’,106,‘mango’,15.6),
(‘m2’,105,‘xbabay’,2.6),
(‘t4’,107,‘xbababa’,3.6),
(‘b5’,107,‘xxxx’,3.6);
查询指定记录：
–从fruits表中查询价格为10.2元的水果的名称
select f_name,f_price from fruits where f_price=10.2;
–查询fruits表中价格小于10的水果的名称
select f_name,f_price from fruits where f_price<10;
带in关键字的查询：
–从fruits表中查询所有s_id不等于101也不等于102的记录
select s_id,f_name,f_price from fruits where s_id not in(101,102);
带between…and的范围查询：
–从fruits表中查询价格在2.00元到10.20元之间的水果名称和价格
select f_name,f_price from fruits where f_price between 2.00 and 10.2
带like的字符匹配查询：
–从fruits表中查询所有以’b’字母开头的水果
select * from fruits where f_name like ‘b%’;
下划线通配符’_’，只匹配任意单个的字符
–在fruits表中,查询以字母’y’结尾并且’y’前面只有4个字母的记录
select f_id,f_name from fruits where f_name like ‘____y’;
多条件查询:
–查询s_ic=101或者102，且f_price大于5，并且f_name=‘apple’的水果价格和名称
select f_id, f_name, f_price from fruits where (s_id=101 or
s_id=102) and f_price>5 and f_name=‘apple’;
查询结果不重复：
例子：–从fruits表中查询有多少个供应商
select count(distinct s_id) as 供应商数量 from fruits;
查询结果排序：
查看所有101供应商的商品按价格排序
select * from fruits where s_id=101 order by f_price desc ;
分组查询：
–找出每个供应商提供的水果的种类
select s_id,count(*) as ‘number’ from fruits group by s_id;
concat（）函数 将字符串拼接
–找出每个供应商提供的水果的种类名称
–-根据s_id对fruit表中的数据进行分组，使用group_concat()函数将每 个分组中各个字段的值显示出来
select s_id, group_concat(f_name) as ‘商品种类’ from fruits group by s_id;
限制查询结果的数量：
–显示前4个水果信息
select * from fruits limit 4;
–从第2个商品开始，显示4个水果信息
select * from fruits limit 1,4;
case when 语句：
– 假设水果价格超过10元,显示为’贵’;小于5元,显示’便宜’;其余是’正常’
select f_name,
case
when f_price>10 then ‘贵’
when f_price<5 then ‘便宜’
else ‘正常’
end
as ‘价格说明’
from fruits;