DDL数据库操作SQL语句

一、数据库操作

查看所有的数据库

show databases;

查看指定的数据库

show create database 数据库名;

进入某个数据库

use 数据库名;

创建数据库SQL（create）

create database [if not exists] 数据库名称[数据库配置选项]

选项含义

if not exists 不存在时才创建

character set utf8 设置数据库字符集为utf-8

collate utf8_general_ci 设置校对集，排序编码格式，忽略大小写

sql示例:
```
create database if not exists lanouDB character set utf8 collate utf8_general_ci;
```
删除数据库 drop

drop database [if exists] 数据库名称;

示例：
```
drop database if exists lanouDB;
```

选项	含义
if not exists	不存在时才创建
character set utf8	设置数据库字符集为utf-8
collate utf8_general_ci	设置校对集，排序编码格式，忽略大小写

二、数据表操作 table

   ##### 1、创建数据表 create

create table [if not exists] 表名(列定义1，列定义2，列定义3...) [表选项];

每列定义时可以配置的值

选项	含义	用法
列名	相当于key	`stu`
数据类型和数据长度	类型（长度）	int(3) varchar(100)
是否为空	该列数据是否可以为空	not null //不写带可以为空
校验集	设置字符串比对规则	collate utf8_general_ci 忽略大小写
注释	对列的说明文字	comment '注释性的文字'
主键设置	设置唯一确定一行数据的列	primary key
主键自增	A_I	auto_increment/不写表示不自增

示例：

create table if not exists stu(
    `stu_id`   int(4) not null collate utf8_general_ci comment '学生ID' primary key,
    `stu_name` varchar(20) not null collate utf8_general_ci comment '学生姓名'
)engine=InnoDB default charset=utf8;

2、查看表信息

show tables;  //查看所有的表
show create table 表名称; //查看指定表信息

3、修改表 alter

新增列

alter table 表名称 add(列定义1，列定义2，列定义3);

示例：

alter table stu add(
   `stu_age` int(3) not null collate utf8_general_ci comment '年龄',
   `stu_sex` int(1) not null collate utf8_general_ci comment '性别',
   `stu_phone` varchar(11) not null collate utf8_general_ci comment '手机号'
);

修改列定义

alter table 表名 modify 列名 新的列定义

示例：

alter table stu modify `stu_sex` varchar(20) not null collate utf8_general_ci comment '性别说明';

列更改

alter table 表名 change 要改的列 新列  新列定义

示例：

alter table stu change `stu_sex`
`stu_hobby` varchar(30) not null collate utf8_general_ci comment '爱好';

删除列

alter table 表名 drop 列名

示例：

alter table stu drop `stu_hobby`;

4. 对表重命名

rename table 原表名 to 新表名

示例

rename table stu to lanou_stu;

5、删除表 drop

drop table [if exists] 表名;

示例

drop table if exists lanou_stu;

DDL 语句总结

database

show databases

show create database

create database

drop database

table

show tables

show create table

create table if not exists

列操作

alter table 表名 add(列定义)

rename table oldname to newname

drop table 表名

DML

inset into 表名（keylist） values (valueslist)

                         values(按照表定义顺序写值)

DML 数据库处理语句

DML语句主要是对数据表table中每一行数进行操作，整个语句分为以下4种

insert into 向数据表种添加一行数据
delete 删除数据表种一行或多行数据
update 修改数据表中一行或多行数据
select 查询一个结果集

插入数据

insert into 表名（列名1，列名2,列名3..） values (列1值，列2值，列3值..);

示例：

insert into stu(`stu_id`,`stu_name`,`stu_age`,`stu_sex`,`stu_phone`)values(1001,"张三",18,1,"18187657890");

insert into stu values(1003,"张三",18,1,"18187657890");
//这种写法虽然省略了 key，但是values书写顺序必须与列定义顺序保持一致

修改数据

update 表名 set  `列名1`=值1,`列名2`=值2  where 更新条件

示例：

//修改一行
update stu set `stu_name`='李四',`stu_age`=28 where `stu_id`=1001;
//修改多行
update stu set `stu_phone`='110' where `stu_name`='张三';
//修改所有行数据
update stu set `stu_sex`=0 where 1;
update stu set `stu_sex`=0 //省略不写

删除数据

delete from 表名 where 删除条件

示例：

//删除一行
delete from stu where `stu_id`=1001;
//删除多行
delete from stu where `stu_age`<18;
//删除所有
delete from stu where 1;
delete from stu ;

多条语句可以同时执行

insert into stu values(1001,'lilei',12,0,'123132');
insert into stu values(1002,'lilei',12,0,'123132');
insert into stu values(1003,'lilei',12,0,'123132');
insert into stu values(1004,'lilei',12,0,'123132');
insert into stu values(1005,'lilei',12,0,'123132');
insert into stu values(1006,'lilei',12,0,'123132');
insert into stu values(1007,'lilei',12,0,'123132');
insert into stu values(1008,'lilei',12,0,'123132');
insert into stu values(1009,'lilei',12,0,'123132');
insert into stu values(1010,'lilei',12,0,'123132');
update stu set `stu_name`='hanmeimei' where `stu_id`=1002;
delete from stu where `stu_id`=1005;

sql语句1;sql语句2;sql语句3;.....

查询语句 select

通过sql语句从数据表中检索出一个结果集：依然一张表

用数据描述结果集 :

[
    {
      key1: value1,
      key2: value2
    },  //一条数据
    {},  //2条数据
    {},  //3条数据
]

一、基本查询语句

一般查询

select  `字段1`,`字段2`,`字段3` from  表名;

示例：

//查询学生 姓名 年龄  所属班级 在 学生  表中
select `c_id`,`s_name`,`s_age` from lo_stu;
//查询所有的学生信息
select * from lo_stu;

条件查询

select  `字段1`,`字段2`,`字段3` from  表名 where 条件表达式;

示例：

//查询所有c001班学生的信息
select * from lo_stu where `c_id`='c001';
//条件表达式
 变量     字段 key
 运算符   关系运算符  逻辑运算符
 数据     查询value值

运算符

运算符	含义	用法	查询结果
=	全等比较	`c_id`='c001'	查询所有c001班学生信息
<> !=	不等于	`c_id`<>'c001'	查询除了c001班以为外其他班级学生的信息
< , <=, >,>=	关系	`s_age` < 18	查询年龄小于18岁的学生信息
is null	判断是空	`c_id` is null	查询未分班的学生信息
is not null	判断不为空	`c_id` is not null	查询已分班的学生信息
between...and...	是否在某个区间	`s_age` between 18 and 20	查询年龄在18-20岁之间的学生信息
not	逻辑非	not `c_id`='c001'	查询非c001班学生信息
or	逻辑或	`c_id` = 'c001' or `c_id`= 'c002'	查询c001班和c002班学生信息
and	逻辑与	`c_id`='c001' and `s_age`<20	查询c001班20岁以下的学生信息
like	模糊匹配	`s_name` like '张%' <br /> '%张' ：以张符号结尾<br />'%张%李'：<br />%百分号表示。任意数量的任意符号	表示查询所有的张姓学生信息
()	优先级控制	`s_age` < (2 + 18)	-------

练习：

1、查询所有c002班18岁到20岁之间姓张的同学，年龄，性别，姓名

select  `s_age`,`s_sex`,`s_name` from lo_stu where (`c_id`='c002') and (`s_age` between 18 and 20) and `s_name` like '张%';

2、查询所有未分班的学生姓名

数据在哪一列，select 后就跟那些字段 from 字段所在表 where 条件表达式变量：字段运算符值

二、进阶查询语句

1、排序（是给一条完整查询语句之后加一些标识符，来对查询结果进行操作，跟原数据表没有关系）

升序

select 字段 from 表名 where 条件 order by 字段 asc（升序）desc（降序）

示例：查询c002班学生信息，并按照年龄升序

select 
     * 
from 
     lo_stu 
where 
    `c_id`='c002' 
order by `s_age` asc;

2、限定查询limit

  ```sql
  select 字段 from 表 where 条件 limit 起始数据下标,length
  ```

示例：

查询 25岁以下 学生信息，取里面的前3组数
select * from lo_stu where `s_age` < 25 limit 0,3;

查询c002班年龄最大前两名学生 姓名
select `s_name`,`s_age` from lo_stu where `c_id`='c002' order by `s_age` desc limit 0,2;

3、聚合函数统计查询

select sum(`s_age`) as sumage  from lo_stu; //算列和
select 
   avg(`s_age`) as avgage,   //列平均值
   max(`s_age`) as maxage,   //列最大值
   min(`s_age`) as minage,   //列最小值
   count(`s_id`) as totalCount //列总数据量
from 
   lo_stu;
 聚合函数操作的依然是结果集
 select 
   avg(`s_age`) as avgage,   
   max(`s_age`) as maxage,   
   min(`s_age`) as minage,   
   count(`s_id`) as totalCount 
from 
   lo_stu
where
   `s_age` < 20;

3-2：分组查询统计 group by 字段

select 聚合函数（统计的字段）,分组字段 from 表 group by 分组字段

示例：

//统计每个班的人数 count
select count(`s_id`) as renshu,`c_id` from lo_stu where `c_id` is not null group by `c_id`;
//统计每个班的年龄平均值

3-3 having子句，对分组统计结果集进行条件筛选

分组查询语句  having 条件表达式

示例：

查询 统计平均年龄大于18岁的班级
select avg(`s_age`) as avage,`c_id` from lo_stu group by `c_id` having `avage` > 18;

多表联查

多表查询，要查询的数据不在同一张表中，而是分布在不同表的数据列中，需要将包含有被查数据的所有表联合起来，组合成一新的集合查询后返回

基本多表查询

select table1.字段1,table2.字段2 from table1,table2  where 联合查询条件

示例：

查询学生的 名称 年龄  班级名称
select 
   lo_stu.`s_name`,
   lo_stu.`s_age`,
   lo_class.`c_name`
from
   lo_stu,
   lo_class
where
   lo_stu.`c_id` = lo_class.`c_id`;
 查询学生的 名称 年龄  班级名称 18岁以下 
 
 select 
   lo_stu.`s_name`,
   lo_stu.`s_age`,
   lo_class.`c_name`
from
   lo_stu,
   lo_class
where
   lo_stu.`c_id` = lo_class.`c_id`
and 
   lo_stu.`s_age` < 18;

连接查询 table1 join table2 on 连接条件

连接查询，是将多张表，先进行两两合并，最后对和并的结果按照单表的查询方式进行查询

select 合并后表中的字段 from (表1 join 表2 on 连接条件) where 查询条件

示例：

select 
   lo_stu.`s_name`,
   lo_class.`c_name`,
   lo_major.`m_name`
from
    lo_stu
join
    lo_class
on
    lo_stu.`c_id` = lo_class.`c_id`
join
   lo_major
on
   lo_class.`m_id` = lo_major.`m_id`
where 
   lo_stu.`s_age` > 18 order by lo_stu.`s_age` desc;

左/右外连接查询 table1 left/右 join table2 on 条件

//查询  班级名 和学科名
    select
       lo_class.`c_name`,
       lo_major.`m_name`
    from
       lo_class
    right 
    join
       lo_major
     on
       lo_class.`m_id` = lo_major.`m_id`
     where 1;

左连接：保证第一张表的数据完整性，是以第一张表为主表，将第二张表向第一张合并，合并过程，第一张表有，第二张没有，就补NULL，第一张表没有的，第二张表如果有就丢弃

右连接，保证第二张表的数据完整性，以第二张表为主表

   select  原表字段名 as 结果集显示名称

子查询语句

从结果集中进行查询的语句，称为子查询
结果集可以作为一张新表被继续使用查询语句，查询语句中所有可以写表位置，都可以写成子查询语句，但必须通过 as 给查询结果集 命名。  从结果集中查询时，要使用结果集名.字段的方式书写。

select 
   c002.`s_id`,
   c002.`s_name`,
   c002.`s_age`
from
    (select `s_id`,`s_name`,`s_age` from lo_stu     where `c_id`='c002') as c002
where
   c002.`s_age` > 18;

in/ not in
子查询语句也可以写在where子句后面，作为另一个查询语句的查询条件。运算符使用字段 in 子查询结果集

查询当前已经开设的学科种类
select * from lo_major where `m_id` in (select `m_id` from lo_class);

其他语句

1、查询结果去重distinct 字段名

select distinct `s_name`,`s_sex`,`s_age` from lo_stu;

用法：select distinct 用来对查询后的结果集，进行过滤，将结果集每列数据完全相同的行，进行去重，只保留一行

2、as重命名

as 重命名结果集字段

select `s_name` as '姓名',`s_age` as '年龄' from  lo_stu;

as 对查询结果集命名

select 
   c001.* 
from 
  (select * from lo_stu) as c001

as 对数据表起别名

select  x.`s_id`,x.`s_name` from lo_stu as x;

3、查询列运算

     ```sql
     select `s_age` from lo_stu;
     select (`s_age` + 5) as n_age from lo_stu;
     select concat("lanou",`s_name`) as n from lo_stu;
     ```

sql语句