Bibles_MySQL笔记整理_SQL语句基础版_DDL&DML

sql Bible（缩略版）

• DML table的操作

  • 表结构的操作
    create
    desc
    rename
    alter
    add
    drop
    change

  • 表数据的操作
    select
    insert into
    values
    copy
    update
    set
    delete

  • 去重，排序，分页，模糊，条件，逻辑
    distinct
    as
    order by
    desc
    asc
    limit
    like
    where
    %
    _

    in and or

  • 聚合
    group by
    avg
    max
    min
    sum
    count

Truncated adj. 缩短了的；被删节的；切去顶端的

多表查询
join
on
子查询

union

一、SQL语句基本概念和分类

1. SQL：

结构化查询语句(structured query lanangue)
作用：实现和数据库实现交互的一些命令语句。
种类：

(1) DDL

数据定义语言 (表结构相关的操作，库的相关操作)
关键字：
create[创建] alter[修改] drop[删除]
作用：
定义数据的结构

(2) DML

数据操作语言(表中数据操作相关)
关键字：
insert[插入] select[查询] update[修改] delete[删除]
作用：
实现对表中的数据进行增删改查的操作(crud)

(3) DCL

数据控制语言(表中数据操作授权)
关键字：grant[授权] revoke[取消授权]
作用：实现表中数据访问权限的控制

(4) TCL

事务控制语言(事务相关的操作)
关键字：commit[提交事务] rollback[回滚事务]

二、DDL

数据库database的操作

登录
mysql -h127.0.0.1 -uroot -p

create database test;
use db;
drop database test;
show databases;

查看当前使用的数据库
SELECT database();

查看当前使用的数据库的下面的表
show tables;

1. 查看，创建，删除数据库(cmd操作)

显示数据库服务中所有的数据库：show databases;
使用某一个数据库：use 数据库名;
create database 数据库名;
注意：如果数据库存在，则报错
create database fund;
或 （如果存在不创建，不存在就会创建）
create database if not exists fund;

查看数据库中的所有的表：show tables;

drop database 数据库名;
注意：删除的数据库要存在，否则报错
drop database AI1809;
或 （如果存在就删除，不存在不删除）
drop database if exists AI1809;

2. 数据库的备份与还原※(cmd操作)

备份(在bin文件夹下)
mysqldump –h服务器地址 –u登录名 –p密码 数据库名 > 文件名

还原： [还原前题要新建一个数据库]
(一定在bin文件夹下）
mysql –h主机 –u登录名 –p密码 数据库名 < 文件名

在MYSQL编辑状态下还源[无盘符，只有mysql>]

创建数据库就是创建文件夹：【盖房子】
在数据库中创建表[创建文件] 【做几个小屋】
向数据表插入数据 [住人]

三、 DML

表table的操作

总结：表结构修改

通过
create, drop, add(change), modify, drop
这几个关键字实现

能用modify实现的都能用change实现
一般修改表名的时候用change
其他的修改建议用modify

1.查看，创建表

创建表
create table 表名(字段名1 数据类型1, 字段名2 数据类型2, …)
注意：
选中一个数据库，如果数据库中已经存在该表，则报错
create table student_info(num int, name varchar(10), age int);
或（库中没有该表则创建，否则不创建）
create table if not exists student_info(num int, name varchar(10), age int);

查看表结构
desc 表名;
desc student_info;

更改表的名字
rename table 旧表名 to 新表名;
rename table student_info to stu_info;
注意：
varchar:动态字符串 varchar(len) len最大取255
说明-当数据长度小于len的时候就存数据的长度，大于len存len个

注意：
表命名规则

后缀根据类型加
_tbl
_index

字段名如果是中文，加引号就是字符串了，需要加的 ``

2.删除表

drop table 表名;
注意：如果表不存在，则报错
drop table student_info;
或（如果表存在就删除，不存在不删除）
drop table if exists student_info;

3.修改表结构 alter add

添加新的字段–phone （追加新字段）
alter table 表名 add 字段名 字段类型;
alter table student_info add phone int;
或（把新增字段作为首字段）
alter table 表名 add 字段名 字段类型 first;
alter table student_info add qq int first;
或（在任意位置新增字段） 注：没有before
alter table 表名 add 字段名 字段类型 after 字段名;
alter table student_info add wx varchar(10) after age;

字段（纵）记录（横）

4. 修改字段的名字&类型（语法一样）

修改名字
修改student_info的phone字段名为tel
alter table 表名 change 旧字段名 新字段名 新类型 [新约束];
语法说明：新类型跟原类型一样，则仅是修改字段名。但新类型一定要有。约束是可选的。
alter table student_info change phone tel int;

修改类型
修改tel字段的类型为varchar类型
alter table 表名 change 旧字段名 新字段名 新类型 [新约束];
语法说明：保证新旧字段名一样，仅更改类型即可
alter table student_info change tel tel varchar(11);
或 语法：alter table 表名 modify 字段名 新类型 [新约束];
alter table student_info modify tel int;

6. 修改表的字段顺序

把num字段和qq字段的顺序进行交换
alter table 表名 modify 字段名1 字段1类型 after 字段名2;
语法说明：表示把字段1修改到字段2的后面
alter table student_info modify qq int after num;
或 （报错不行）
alter table student_info change qq int after num;
或 （保留被修改的字段名字，类型，约束不变，仅更改字段顺序）
alter table student_info change qq qq int after wx;

备注：

• table关键字别掉了，知识desc不用加table
• 标位字段不用加类型

7. 删除字段

alter table student_info drop wx;

DML II（操作表中的数据的）

查询语句(select)

实现对表的全查询
select * from stu_info;

指定字段查询
查询stu_info表所有学生的num name age
select num,name,age from stu_info;

查询部分记录(有条件查询)
查询年龄在20岁以上的所有学生的信息
select * from stu_info where age>=20;
查询年龄在20岁以下的所有学生的名字和年龄信息
select name,age from stu_info where age<20;
查询年龄在19到21之间的所有学生的信息
select * from stu_info where age>=19 and age<=21;
select * from stu_info where age between 19 and 21;
查询学生qq是5673,4562,8976的学生的名字，年龄，qq信息
select name,age,qq from stu_info where qq in(5673,4562,8976);
select name,age,qq from stu_info where qq=5673 or qq=4562 or qq=8976;

备注：
能增加中文字段 可以，怎么做，好处，坏处
字段名如果是中文，加引号就是字符串了，需要加的 ``

插入语句(insert)

往bank表中新增一个用户
insert into 表名 values(字段1的值，字段2的值，…);
注意：插入完整字段数据的时候，要保证数据的顺序和字段的顺序一致
字符串要用单引号或者双引号引起来
insert into bank values(4, ‘rose’, ‘678’, 1000);

往bank表中新增一个用户，不考虑用户id
insert into 表名(字段1, 字段2, …) values(字段1的值, 字段2的值, …)
注意：当插入的新记录的数据个数跟字段个数不一样的时候，用该语法，指明值和字段的对应关系。
insert into bank(name, pwd, money) values(‘nancy’,‘890’,0);
insert into bank(money, pwd, name) values(100, ‘123’,‘jerry’);

往表中批量添加多条记录
指定记录的每个字段的数据（顺序跟表中结构一致）
insert into bank values(7,‘tjim’,‘567’,2000),(8,‘tlucy’,‘789’, 500);
指定记录部分字段的数据（顺序跟字段列表一致）
insert into bank(name, pwd, money) values(‘trose’,‘123456’,200),(‘ttom’,‘567123’,1000);

把一张表中的数据批量添加到另一张表中(表的数据的复制)
把bank表中的数据复制到bank_copy表中
创建bank_copy表，让他的结构跟bank表一致。
insert into bank_copy select * from bank;

把bank表中的数据复制到bank_copy1表中
创建bank_copy1表，让他的结构跟bank表比少了money列。
insert into bank_copy1 select id, name, pwd from bank;

把bank表中的数据复制到bank_copy2表中
创建bank_copy2表，让他的结构跟bank表比多了age列。
insert into bank_copy2(id, name, pwd, money) select * from bank;

更新语句(update)

修改一个字段的值
把bank表中的money字段的值都改为0
update 表名 set 字段=新的数据;
update bank set money=0;

修改多个字段的值
把bank表中的name字段都修改为’jim’，pwd字段都修改为’123’
update 表名 set 字段1=新的数据, 字段2=新的数据, …;
update bank set name=‘jim’, pwd=‘123’;

修改部分记录的某些字段的值
update 表名 set 字段1=新数据, 字段2=新数据,… where 条件;
注意：一个条件可能选中多条记录，也可能只有一条记录
update bank set name=‘tom’, pwd=‘456’,money=1000 where id=7;

删除语句(delete)

删除一条完整的记录

删除一张表的所有的记录
delete from 表名;
delete from bank_copy2;

删除一张表中的部分记录
删除bank_copy3表中的name为’jim’的记录
delete from 表名 where 条件;
delete from bank_copy3 where name=‘jim’;

注意：
删除记录以后，自增的主键在新添加新记录的时候，不会用已经被用过的值。
但是你可以自己修改为已经用过的值。

查询结果去重

在要去重的字段的前面加上distinct关键字即可
查看stu_info中学生的年龄构成
select distinct age from stu_info;

定义别名

给表达式，结果集中的列名，表名，整个结果集起别名
定义别名的时候是通过as关键字实现，as是可以省略的

给表达式定义别名

select 1+1 as sum; # 结果集中以sum作为列名
select 1+1 sum; # 结果集中以sum作为列名

给结果集中的列名定义别名

查看stu_info表中所有的学生长大一岁后的年龄,并且显示学生名字。
select name, age+1 as upage from stu_info;

给表定义别名(常见于多表查询时)

把stu_info的名字定义别名为s
查询表中的所有的学生的名字和年龄
select s.name,s.age from stu_info as s;

给某一个结果集定义别名，用于子查询中

排序查询

升序asc[默认值] 降序desc)

添加 order by子句，对于结果集进行排序

查询stu_info表中所有学生的姓名和年龄，按年龄进行排序(降序)
select name,age from stu_info order by age desc;

查询stu_info表中所有学生的姓名和年龄，按年龄进行排序，如果年龄相同在按照名字排序
select name,age from stu_info order by age,name;

查询stu_info表中所有学生的姓名和年龄，按年龄进行升序排，如果年龄相同在按照名字降序排
select name,age from stu_info order by age asc,name desc;

默认是升序

限制查询(分页查询)

添加limit子句。语法：limit 起始索引,记录个数
注：索引值从0开始。当起始索引为0的时候，可以省略。
查询stu_info表中的前3条记录的所有信息
select * from stu_info limit 0,3;
起始索引为0，可以省略
select * from stu_info limit 3;

注意：
mysql 使用order by排序的时候不起作用，原因是order by的字段是varchar类型的， 直接在字段后加上’+0’ 则转化为int类型，

例如: select * from ORDER BY 字段+0 即可

排序和分页查询的综合应用

查询出stu_info表中年龄最大的学生的名字和年龄以及学号信息
select num,name,age from stu_info order by age desc limit 0,1;
查询出stu_info表中年龄最小的学生的名字和年龄以及学号信息
select num,name,age from stu_info order by age asc limit 0,1;

模糊查询

进行查询条件值不完整

like
通过 关键字实现模糊查询

%
作为占位符，表示0到多个字符
_
作为占位符，一个下划线代表一个字符

查询stu_info表中名字含有’张’的学生信息
以下的sql等同于where name = ‘张三’;
select * from stu_info where name like ‘张三’;

查询stu_info表中名字含有’张’的学生信息
以下sql表示以’张’为开头，表示：张*** 或 张
select * from stu_info where name like ‘张%’;

查询stu_info表中名字含有’张’的学生信息
select * from stu_info where name like ‘%张%’;

查询stu_info表中名字的第二个字符是’o’的学生的信息
select * from stu_info where name like ‘_o%’;

查询stu_info表中名字的第三个字符是’m’的学生的信息
select * from stu_info where name like ‘__m%’;

# 修改表的结构=======================================

# 增加字段，追加后面，头，任意位置
alter table student add object varchar(10);
alter table student add first varchar(10) first;
alter table student add everyw varchar(10) after high;

# 修改字段名
alter table student change high height decimal(5,2);

# 修改字段的类型
alter table student change height height int;

desc student;

# 或
alter table student modify height decimal(5,2);

# 修改字段的顺序

alter table student change height height decimal(5,2) after gender;

# 或
alter table student modify height decimal(5,2) after everyw;

# 删除字段(不用加类型)
alter table student drop object;