mysql学习笔记5，mysql增删改查（DDL,DML）操作数据库，数据表，数据，三个维度

mysql学习笔记5

增删改查数据库

创建数据库

命名规则：表名不得超过30个字符，必须包含a-z，A-Z,0-9，_;数据库名与字段，表名不能包含空格，同一个软件中不能重名数据库，数据库中不能同名表，表中不能同名字段；

如何创建数据库

CREATE DATABASE 数据库名；
CREATE DATABESE 数据库名 CHARACTER SET 'utf-8';
(第2种指定字符集)
CREATE DATABASE IS NOT EXITSTS 数据库名；（如果不存在同名时创建成功，否则创创建数百但不返回错误）

管理数据库

SHOW DATABSAE ;(查看)
USE 数据库名（切换）
SHOW TABLES;(查看当前数据库中的表)
SELCET DATABASE() FROM DUAL;(查看当前数据库)
SHOW TABLES FROM 数据库名；（查看数据库下的表）

修改数据库

ALTER database 数据库名 CHARACTER SET ‘UTF-8’;(修改数据库的字符集)

删除数据库

DROP DATABASE 数据库名；

增删改查数据表

创建数据表

CREATE TABLE IF NOT EXISTS 表名（
字段名 数据类型，
id INT ,
name VARCHAR(15)
）；

查看表结构

DESC 	表名；

复制数据表

CREATE TABLE 表名 AS 
SELECT 字段 FROM 表名2；
（相当于将表21中的选定字段提取创建一个新表为表一）

修改表 ALTER

添加一个字段
ALTER TABLE 表名 ADD 字段  数据类型 (长度)FRIST/AFTER某字段；
修改一个字段
ALTER TABLE 表名 MODIFY 字段 数据类型（长度
重命名字段
ALTER TABLE 表名  CHANG 旧字段名 新字段名 数据类型（长度）
删除一个字段
ALTER TABLE 表名  DROP COLUMN 字段；

重命名表

RENAME TABLE 表名 TO 新表名；
ALTER TABLE 表名 RENAME TO 新表名；

删除表

DROP TABLE 表名；
（不能回滚，撤销）

清空表
区别于删除表，只删除数据，保留表结构

TRUNCATE TABLE 表名；

DCL

COMMIT
提交数据，一旦执行commit数据会永久保留在数据库中；(相当于保存操作)
ROLLBACK
操作可以回滚

COMMIT;(提交数据)
DELELTE FROM  表名；
ROLLBACK;（回滚数据）
此时查询已经清除的表示有内容的

数据处理

对某一条数据进行操作 增删改

添加数据

1.INSERT INTO 表名
VALUES (内容)；此时添加数据的数据类型顺序按照声明表中的数据类型顺序相同
2.INSERT INTO 表名（数据名）
VLAUES (内容)；此时不需要特别严格按照数据顺序进行添加
未指定的数据为null

每一条数据为一个括号（），多条数据需要用逗号隔开
将查询除的字段添加到表中

INSERT INTO 表名（字段名）
SELECT 字段 FROM 表名；
此时就讲查询除的字段，添加到新的表中（注意，字段数量必须相同，且数据类型相同）

更新数据（修改）

将数据表中现有的数据进行修改

UPDATE 表名 SET 数据修改（举例：name=qq） WHERE 过滤条件（ id=5）；
当不标明过滤条件数显批量修改
需要修改的数据使用逗号隔开实现多条数据同时修改

TABLE 与DELETE FROM (删除数据)

相同点：实现数据清除，保留表结构
不同点：TRUNCATE TABLE 一旦执行，不可以回滚
DELETE FROM 可以回滚

其他知识，案例总结

DDL与DML

DDL的操作一旦执行都不能回滚
DML 在声明其提交状态为flase时可以回滚，SET autcomit =FLASE

练习

1.创建一个数据库 test01_library

CREATE DATABASE IS NOT EXITES test01_library；

2.创建表books的表结构

CREATE TABLE IS NOT EXITES books（id INT,name VARCHAR(50),authors CARCHAR(100),price FLOAT,pubdate YEAR,note VARCHAR(100),num INT）;

3.向表中插入数据

1.不指定字段名称
INSERT INTO books VALUES(1,'Tal of AAA',Dickes,23,1995,novel,11);
2.指定数据名称
INSERT INTO books（id，name，authors，price，pubdate，note，num）VALUES(2，EammaT，35,1993，joke，22)；
3.同时插入多条记录
INSERT INTO books（id，name，authors，price，pubdate，note，num）VALUES（3，Story，jane，40,2001，novel，0），
（4，LOVE DAY,George，20,2005，novel，30），
（5，old，honore，30,2010，law，0），
（6，the，向，30,1999，medicine，40），
（7，rose，richard，28,2008，cartoon，28）；

4.将小说（novel）价格都增加5

UPDATE books SET price=price+5 WHERE note=novel;

5.将名称为EammaT的书价格修改为40，将说明改为drama

UPDATE books DET price=40，note=drama WHERE name=EammaT；

6.删除库存为0的记录

DELETE FROM books 
WHERE num=0 ；

7.统计书名中包含字母为a的书

CELECT NAME FROM books WHERE name LIKE ‘%a%’；

8.统计书名中包含字母a的书的库存总量

CELECT SUM(num),COUNT(*)  FROM books WHERE name LIKE '%a%';

9.找出novel 类型的书按照价格降序排序

CELECT name，price FROM books WHERE note=novel OEDER BY price DESC；

10.查询图书的信息，按照库存存量进行排序，库存相等按照note升序排列

SELECT name，num，note FROM books OEDER BY num DESC，note ASC;

11.按照note分类统计数的数量

SELECT note，COUNT(*) FROM books GROUP BY note ；

12.按照note分类统计书的数量,显示大于30的

SELECT note，SUM(num) FROM books GROUP BY note HAVING SUM(num)>30; 

13.查询所有图书，每页5本，显示第二页

SELECT * FROM books 
LIMIT 5,5;

14.按照note 分类计算统计书的库存量，显示最多的

SELECT note，COUNT(*) FROM books GROUP BY note DESC LIMIT 0,1；

15.查询书名中字符数大于5的书不包含空格

SELECT NAME,CHAR_LENGTH(REPLACE(name,' ','')) FROM books  WHERE  CHAR_LENGTH(REPLACE(name,' ',''))>5;

16.查询书名和类型

SELECT NAME,note,CASE note WHEN  'novel' THEN ' ELSE '其他' END '类型'小说'FROM books；

17.查询书名，库存，其中num超过30的显示滞销，大于0小于10，显示畅销，为0的显示无货

SELECT NAME，num，CASE  WHEN num>30 THEN '滞销' WHEN num>0AND num <10 THEN '畅销' WHEN num=0 THEN '无货' END '销量' FROM books；

18.统计每一种note的库存量，并合计

SELECT IFNULL(note,'总量'），SUM(num) FROM books GROUP BY note WITH ROLLUP；

19.统计每种note的数量并合计

SELECT IFNULL(note,'总量'），AS note,COUNT(*) FROM books GROUP BY note WITH ROLLUP；

20.统计库存量前三种的图书

SELECT * FROM books OEDER BY num DESC LIMIT 0,3;

21.最早出版的图书

SELECT * FROM books OEDER BY pubdate ASC LIMIT 0,1；

22.找出novel中价格最高的书

SELECT * FROM books WHERE note =novel OEDER BY price DESC LIMIT 0,1;

23.找出书名中字数最多的书，不含空格

SELECT NAME,CHAR_LENGTH(REPLACE(name,' ','')) FROM books  ORDER BY  CHAR_LENGTH(REPLACE(name,' ','')) DESC LIMIT 0,1;

剑谱最终页，无爱自然神