数据库的基本操作

MySQL数据操作： DML

========================================================

在MySQL管理软件中，可以通过SQL语句中的DML语言来实现数据的操作，包括

1. 使用INSERT实现数据的插入
2. UPDATE实现数据的更新
3. 使用DELETE实现数据的删除
4. 使用SELECT查询数据以及。

插入数据INSERT

1.插入完整数据（顺序插入）
语法一：

INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);

语法二：

INSERT INTO 表名 VALUES (值1,值2,值3…值n);

2.指定字段插入数据
语法：

INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);

3.插入多条记录
语法：

INSERT INTO 表名 VALUES  
(值1,值2,值3…值n),  
(值1,值2,值3…值n),  
(值1,值2,值3…值n);

4.插入查询结果
语法：

INSERT INTO 表名(字段1,字段2,字段3…字段n)  
SELECT (字段1,字段2,字段3…字段n) FROM 表2  
WHERE …;

更新数据UPDATE

语法：

UPDATE 表名 SET  
字段1=值1,  
字段2=值2,  
WHERE CONDITION;

示例：

UPDATE mysql.user SET password='123'  where user='root' ;

删除数据DELETE

语法：

DELETE FROM 表名   WHERE CONITION;

示例：

DELETE FROM student  WHERE id=3;

删除全部

DELETE FROM student
truncate table student;

区别:
delete: DML操作, 是逻辑性质删除,逐行进行删除,速度慢.
truncate: DDL操作,对与表段中的数据页进行清空,速度快.

伪删除：用update来替代delete，最终保证业务中查不到（select）即可

1.添加状态列

ALTER TABLE student ADD state TINYINT NOT NULL DEFAULT 1 ;
SELECT * FROM student;

2.UPDATE 替代 DELETE

UPDATE student SET state=0 WHERE id=6;

3.业务语句查询

SELECT * FROM student WHERE state=1;

DQL应用(select )

单独使用

-- select @@xxx 查看系统参数
SELECT @@port;
SELECT @@basedir;
SELECT @@datadir;
SELECT @@socket;
SELECT @@server_id;

select函数

SELECT NOW();
SELECT DATABASE();
SELECT USER();
SELECT CONCAT("hello world");
SELECT CONCAT(USER,"@",HOST) FROM mysql.user;
SELECT GROUP_CONCAT(USER,"@",HOST) FROM mysql.user;
https:

单表查询的语法

SELECT 字段1,字段2... FROM 表名
                  WHERE 条件
                  GROUP BY field
                  HAVING 筛选
                  ORDER BY field
                  LIMIT 限制条数

单表查询练习

创建表并录入数据

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) 
);

CREATE  TABLE  score (
id  INT(10)  NOT NULL  UNIQUE  PRIMARY KEY  AUTO_INCREMENT ,
stu_id  INT(10)  NOT NULL ,
c_name  VARCHAR(20) ,
grade  INT(10)
);

INSERT INTO student VALUES( 901,'张老大', '男',1985,'计算机系', '北京市海淀区');
INSERT INTO student VALUES( 902,'张老二', '男',1986,'中文系', '北京市昌平区');
INSERT INTO student VALUES( 903,'张三', '女',1990,'中文系', '湖南省永州市');
INSERT INTO student VALUES( 904,'李四', '男',1990,'英语系', '辽宁省阜新市');
INSERT INTO student VALUES( 905,'王五', '女',1991,'英语系', '福建省厦门市');
INSERT INTO student VALUES( 906,'王六', '男',1988,'计算机系', '湖南省衡阳市');

INSERT INTO score VALUES(NULL,901, '计算机',98);
INSERT INTO score VALUES(NULL,901, '英语', 80);
INSERT INTO score VALUES(NULL,902, '计算机',65);
INSERT INTO score VALUES(NULL,902, '中文',88);
INSERT INTO score VALUES(NULL,903, '中文',95);
INSERT INTO score VALUES(NULL,904, '计算机',70);
INSERT INTO score VALUES(NULL,904, '英语',92);
INSERT INTO score VALUES(NULL,905, '英语',94);
INSERT INTO score VALUES(N ULL,906, '计算机',90);
INSERT INTO score VALUES(NULL,906, '英语',85);

单表子句from

SELECT * from student;

查询名字和所在系

select name,department from student;

where比较查询

SELECT * from student WHERE id>904;

where等值查询

SELECT * from student WHERE id=904;

where逻辑运算  and or 

SELECT * from student WHERE id>903 AND department='英语系';

where 模糊查询
注意%不能放在前面因为不走索引

SELECT * from student WHERE `name` LIKE '张%';

where配合in 使用 
其他相关  NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字

SELECT * from student WHERE department in ('计算机系','英语系');

where配合between and使用

SELECT * from student WHERE id BETWEEN 901 and 904;

group by聚合函数

常用聚合函数

**max()**      ：最大值
**min()**      ：最小值
**avg()**      ：平均值
**sum()**      ：总和
**count()**    ：个数
group_concat() : 列转行

统计每个系的学生人数

SELECT department, COUNT(id) from student GROUP BY department;

having使用

统计每个系的学生人数 ，只打印人数大于1个的

SELECT department, COUNT(id) from student GROUP BY department HAVING COUNT(id) >1;

order by  排序  desc从大到小

`SELECT * from student WHERE `name` LIKE '张%' ORDER BY birth DESC;

limmit 显示几行

select name,department from student LIMIT 3; 
select name,department from student LIMIT 3,5;

联合查询- union all

说明:一般情况下,我们会将 IN 或者 OR 语句 改写成 UNION ALL,来提高性能
UNION 去重复
UNION ALL 不去重复

SELECT * from student WHERE department='计算机系'
UNION ALL
SELECT * from student WHERE department='英语系';

多表连接查询

#重点：外链接语法

SELECT 字段列表
    FROM 表1 INNERLEFTRIGHT JOIN 表2
    ON 表1.字段 = 表2.字段;

1 交叉连接：不适用任何匹配条件。生成笛卡尔积

2 内连接：只连接匹配的行

3 外链接之左连接：优先显示左表全部记录

4 外链接之右连接：优先显示右表全部记录

5 全外连接：显示左右两个表全部记录

查询张三的所有成绩

select student.name,score.c_name,score.grade from student join score on student.id=score.stu_id where name="张三";

使用别名

select a.name as '姓名',b.c\_name as '系',b.grade as '成绩' from student as a join score as b on a.id=b.stu_id where a.name='张三';

查询每个人的平均成绩

select student.name , avg(score.grade) from student join score on
student.id=score.stu_id GROUP BY student.name;

查询每个人的平均成绩并按成绩由高到低排序

select student.name , avg(score.grade) from student join score on
student.id=score.stu_id GROUP BY student.name ORDER BY AVG(score.grade) DESC;

子查询

1：子查询是将一个查询语句嵌套在另一个查询语句中。

2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。

3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字

4：还可以包含比较运算符：= 、 !=、> 、<等

information_schema.tables视图

DESC information_schema.TABLES
TABLE_SCHEMA    ---->库名
TABLE_NAME      ---->表名
ENGINE          ---->引擎
TABLE_ROWS      ---->表的行数
AVG_ROW_LENGTH  ---->表中行的平均行（字节）
INDEX_LENGTH    ---->索引的占用空间大小（字节）

查询整个数据库中所有库和所对应的表信息

SELECT table_schema,GROUP_CONCAT(table_name)
FROM  information_schema.tables
GROUP BY table_schema;

统计所有库下的表个数

SELECT table_schema,COUNT(table\_name)
FROM information_schema.TABLES
GROUP BY table_schema

查询所有innodb引擎的表所在的库

SELECT table_schema,table_name,ENGINE FROM information_schema.`TABLES`
WHERE ENGINE='innodb';

统计school库下每张表所占的磁盘空间

mysql> SELECT table_name,CONCAT((TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024," KB") AS size_KB
 -> FROM information_schema.tables WHERE TABLE_SCHEMA='school';

统计所有数据库的总的磁盘占用

SELECT
TABLE_SCHEMA,
CONCAT(SUM(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024," KB") AS Total_KB
FROM information_schema.tables
GROUP BY table_schema;

生成整个数据库下的所有表的单独备份语句

my.cnf 需要添加如下配置

secure_file_priv=/tmp/

模板语句：

mysqldump -uroot -p123 school score >/tmp/school_score.sql
SELECT CONCAT("mysqldump -uroot -p123456 ",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql" )
FROM information_schema.tables
WHERE table_schema NOT IN('information_schema','performance_schema','sys')
INTO OUTFILE '/tmp/bak.sh' ;

show 命令

show  databases;                          #查看所有数据库
show tables;                                          #查看当前库的所有表
SHOW TABLES FROM                        #查看某个指定库下的表
show create database world                #查看建库语句
show create table world.city                #查看建表语句
show  grants for  root@'localhost'       #查看用户的权限信息
show  charset；                                   #查看字符集
show collation                                      #查看校对规则
show processlist;                                  #查看数据库连接情况
show index from                                 #表的索引情况
show status                                         #数据库状态查看
SHOW STATUS LIKE '%lock%';         #模糊查询数据库某些状态
SHOW VARIABLES                             #查看所有配置信息
SHOW variables LIKE '%lock%';          #查看部分配置信息
show engines                                       #查看支持的所有的存储引擎
show engine innodb status\G               #查看InnoDB引擎相关的状态信息
show binary logs                                    #列举所有的二进制日志
show master status                                 #查看数据库的日志位置信息
show binlog evnets in                             #查看二进制日志事件
show slave status \G                             #查看从库状态
SHOW RELAYLOG EVENTS               #查看从库relaylog事件信息
desc  (show colums from city)               #查看表的列定义信息
http://dev.mysql.com/doc/refman/5.7/en/show.html