MySQL操作汇总2——数据操纵语言（DML）+数据定义语言（DDL）

第一部分：数据操纵语言（DML语言）

插入：insert
修改：update修改数据，alter修改
删除：delete

一、插入语句：

insert into 表名(l列名(可不加)，…) values(值1，…),(…，…)(…，…);
——支持插入多行
【例】：

INSERT INTO beauty
VALUE(13,'鹿晗','女','1990-4-23','120',NULL,2),
(15,'关晓彤','女','1992-5-23','100',NULL,2)

——支持子查询

二、修改语句：

（1）修改单表的记录：
语法：
update 表名
set 列=新值，…
where 筛选条件
【案例】：修改姓唐的美女的电话号码

UPDATE beauty SET phone ='13888888889'
WHERE NAME LIKE '唐%';

（2）修改多表的记录

三、删除语句：

（1）方式一：delete

1）单表的删除：

delete from 表名 where 筛选条件

【案例一】：删除手机尾号为9的美女信息

DELETE FROM beauty WHERE phone LIKE '%9'

【案例二】删除第一行：

DELETE FROM beauty LIMIT 1；

2）多表的删除：

SQL92语法：
delete 表1的别名，表2的别名
from 表1 别名，表2 别名
where 连接条件
and 筛选条件

SQL99语法：
delete 表1的别名，表2的别名
inner|left|right join 表2 别名 on 连接条件
where 筛选条件;

【案例一】删除张无忌的女朋友的信息

DELETE b 
FROM beauty b
INNER JOIN boys bo ON b.boyfriend_id = bo.id
WHERE bo.`boyName`='张无忌';

【案例二】删除黄晓明和他老婆的信息

DELETE b,bo
FROM beauty b
INNER JOIN boys bo ON b.`boyfriend_id`=bo.`id`
WHERE bo.`boyName`='黄晓明';

（2）方式二：truncate

语法：truncate table 表名

【面试题】二者的区别：

1）truncate删除，效率高一点；
2）如果用delete删除，再插入数据，自增长列的值从断点开始，而truncate删除后，再插入数据，自增长列的值从1开始；
3）truncate删除没有返回值，delete删除有返回值；
4）delete可以添加筛选条件，truncate不可以。
5）truncate删除不能回滚，delete删除可以回滚——事务

【返回值验证】

第二部分：数据定义语言（DDL语言）

四、库的管理

1、库的创建

CREATE DATABASE books;

CREATE DATABASE IF NOT EXISTS books;

2、库的修改

（1）修改库名

RENAME DATABASE books TO 新库名;

——现有版本一般不这样修改，不安全，会丢失数据！
可以直接从文件夹里的MySQL Server里的data中进行修改库名。

（2）更改库的字符集

ALTER DATABASE books CHARACTER SET gbk;

3、库的删除：drop

DROP DATABASE IF EXISTS books;

五、表的管理

（1）创建表

1.在数据库服务器中创建数据库：

mysql>create database test;
-Query OK, 1 row affected (0.47 sec)

2.显示数据库：

mysql>show databases;

3.选中某个数据库：

mysql> use test;
-Database changed

4.显示该数据库中所有数据表：

mysql> show tables;
Empty set (0.00 sec)

5.创建数据表：

mysql> create table pet(
-> name varchar(20),
-> owner varchar(20),
-> sex char(1),
-> species varchar(20),
-> age int);
Query OK, 0 rows affected (0.42 sec)

6.再次查看数据表：show tables;

1 row in set (0.00 sec)

7.查看数据表具体内容（describe）

mysql> describe pet;

8.向数据表中添加数据记录（insert）

mysql> insert into pet
-> value(‘Puluto’,‘Miki’,‘m’,‘Dog’,5);
->value(‘旺柴’,‘周星星’，‘m’,‘狗’，‘3’);
Query OK, 1 row affected (0.02 sec)

9.查看数据表中的记录（select）

mysql> select * from pet;

2 rows in set (0.00 sec)

10.修改数据（update）

mysql> update pet set name=‘旺财’ where owner=‘周星星’;
Query OK, 1 row affected (0.11 sec)

再次查看：

11.删除数据（delete）
mysql> delete from pet where name=‘Puluto’;
Query OK, 1 row affected (0.06 sec)

（2）修改列名：alter

【案例】修改品种：special，这个列的列名

DESC pet;

ALTER TABLE pet CHANGE COLUMN species spe INT(11);#change column

（3）添加新列：add

语法：alter table 表名 add column 列名 类型 【first | after 字段名】（不支持last）

ALTER TABLE pet ADD COLUMN newCol VARCHAR(20) AFTER sex;#插入到指定列之后

ALTER TABLE pet ADD COLUMN color VARCHAR(20);

（4）删除列：drop

ALTER TABLE pet DROP COLUMN color;

（5）修改表名：alter

ALTER TABLE pet RENAME TO pets;

六、表的删除

DROP TABLE pets;

七、表的复制

1、复制表的结构

CREATE TABLE copy LIKE pets;

SELECT * FROM copy;

——仅仅能够复制表的结构！

2、复制表的结构+数据

CREATE TABLE copy2
SELECT * FROM pets;#pets中的数据一起复制

SELECT * FROM copy2;

3、只复制部分数据

CREATE TABLE copy3
SELECT NAME,sex,age
FROM pets
WHERE NAME='大黄';

SELECT * FROM copy3;

4、仅仅复制某些字段

（1）带数据：

CREATE TABLE copy6
SELECT OWNER,species
FROM pets;

（2）不带数据：

CREATE TABLE copy5
SELECT OWNER,species
FROM pets
WHERE 0;#为false,即便是所有数据都不满足
SELECT * FROM copy5;