数据库基础01-SQL基础语法

数据库查询语言（Structured Query Language）

数据库查询语言:
DDL（data definition language） -数据定义语言，建库建表
DML （data manipulate language ） -数据操作语言，增删改
DQL （data query language） -数据查询语言

1.DDL（data definition language）

1.库操作

1.查看数据库
show databases

2.查看数据库创造过程
SHOW CREATE DATABASE

3.创建数据库
CREATE DATABASE 数据库名

4.查看数据库
SHOW SCHEMAS;

5.修改数据库（只能修改数据的默认字符编码）
ALTER DATABASE DB CHASET GBK;

6.删除库
drop database db1;

2.表操作

1.查看表
show tables

2.查看所有列
SHOW COLUMNS FROM tablename

3.查找表创建过程
SHOW CREATE TABLE

4.查看用户权限
SHOW GRANTS;

5.创建表
create  table tb1（
id int,
name varchar(20)
）

6.删除表
drop table tb

7.删除重建
truncate table tb

单表修改操作

1.修改表名
 rename table tb1 to tb2

 2.增加字段
  Alter table tb2
  add col varchar first;
                 -first 表示加到最前面
                 -after 表示加到某个字段之后
 
 3.修改字段名
  alert table tb1
  change col1 col2 varcha4(100) 

 4.修改字段类型
  alert table tb1
  modify col decimal(3,2)    
      
 5.修改字段位置
  alert table tb
  modify col varchar(100) first;
  
  6.删除字段
  alter table tb2
  drop column  col1;

1.3创建表取值约束（对字段取值范围进行约束）

详细语法
非空约束（NOT NULL）

1.添加非空约束
create table tb2(
  col varchar(10) not null
)

alter table tb
modify col varchar(10) not null

2.取消非空约束
alter table tb
modify col varchar(10);

alter table tb
modify col varchar(10) null;

主键约束（primary key）

1.添加主键
creat table tb(
 id int primary key ;
)
creat table tb(
 id int  ;
 name varchar(20);
 primary key (id,name);
)

2.删除及修改主键
alter table tb
add primary key(id);

alter table tb
drop primary key(id);

自增（一般用域主键自增）

creat table tb(
 id int primary key auto_increment ;
)

外键约束

1.创建外键
create table tb1(
 id int,
 foreign key (x) reference tb(id) 
）

2.修改删除外键
alter table tb
drop foreign key(id) reference tb1(id)

2.DML （data manipulate language ）数据操作语言

2.1 insert

1.插入单行数据和插入多行数据

insert int tb (col1,col2,col3) values(val1,val2,val2);
insert int tb (col1,col2,col3) values(val1,val2,val2);
insert int tb (col1,col2,col3) values(val1,val2,val2);

批量插入： 

insert int tb (col1,col2,col3) values(val1,val2,val2),(val1,val2,val2),(val1,val2,val2);


2. 插入其他表的数据
insert into  tb（col1,col2） select col1,col2 from tb2

insert into tb1 select * from tb

2.2 update

update table set col=1

2.3 delete

delete from table

全部删除建议使用truncate table ,因为delete 的原理时逐条删除数据；truncate table是删除表再新建表

3.DQL （data query language） -数据查询语言

3.1 简单查询

select col from tb

select * from tb

3.2 过滤及排序

select 子句的执行顺序

• select
• from
• where
• grop by
• having
• order by
• limit

WHERE子句的操作符

3.3 查询字段处理

字符串处理函数

char_length(‘a中‘) - 字符数
length(‘‘) - 字节数
concat(‘a‘,‘b‘,‘cde‘,‘fff‘) -连接字符串
locate(‘abc‘, ‘-abc--abc-‘) - 返回第一个子串的位置，从1开始，找不到返回0
insert(‘abcdefghijkl‘,2, 11, ‘---‘) - 用子串取代从2位置开始的11个字符
lower(‘AdFfLJf‘) - 变为小写
upper(‘AdFfLJf‘) - 变为大写
left(‘AdFfLJf‘,3) - 返回最左边的三个字符
right(‘AdFfLJf‘,3) - 返回最右边的三个字符
trim(‘  a  bc   ‘) - 去除两端空格
substring(‘abcdefghijklmn‘, 3, 6) - 从3位置开始的6个字符
REPLACE(‘Hello MySql‘,‘My‘,‘Your‘) - 子串替换

数字处理函数

Ceil(-3.14)- 向上取整  ，不能制定位数
floor(3.94) - 舍掉小数，向下取整
format(391.536, 2) - 数字格式化为字符串,第二个参数是小数点后位数
round(673.4974, 2) - 第二个参数，正数表示，四舍五入到小数点后两位，负数的话表示向上进位置，也即百位以上进位
TRUNCATE(234,31, 1) - 舍去至小数点后1位
abs() -取绝对值

日期处理函数

NOW()   返回当前的日期和时间
CURDATE()   返回当前的日期
CURTIME()   返回当前的时间

加密函数

md5()  单向加密，无法解密

sha() 相比md5()更加安全

Null函数

IFNULL(数据1,数据2) - 数据1是null返回数据2；不是null返回数据1
 coalesce(数据1,数据2,......) - 从左向右第一个不是null的数据

分页查询

limit a,b  a开始行数，b表示查询行数

相对效率比较低

多行函数(多行数据交给函数处理，产生一个计算结果)

• 多行函数会忽略null值
• 不能直接与普通字段一起查询
• 一般和group by合用

count()   计数，数量，默认null值忽略，但是count（*）的时候null值不忽略
   sum()     求和，可以多列上进行计算
   avg()     求平均数，只能处理单列，多列要用多个avg()函数
   max()     求最大值
   min()     求最小值

3.4 子查询

条件子查询

行内视图和select字段子查询

行内视图
select ... from (select ... from...) as tb
select ... from tb where col in (select col1 from tb2 where )
select ... from tb where id = (select id from tb2 where name=‘zhangsan‘)

字段子查询
select col,(select col1 from tb1 where tb1.id = tb.id) from tb as tb

3.5 多表联查

• 自联结
• 将一张表看成两张表
• 同一张表的主键和外键进行关联

select tb1.col,tb2.col2 
    from tb tb1,tb tb2
   where tb1.id = tb2.refe_id

• 自然联结
  • 选取两个表中全匹配的数据，舍弃不匹配的数据。由于自然连接会丢弃与其他表没匹配的数据，所以查询的数据可能造成数据信息丢失

select ...
  from tb 
  [inner] join tb1 on tb.id = tb1.id

• 外联结
  • left [out] join 为左边的表，筛选出所有符合 连接条件的数据
  • right [out] join 为右边的表，筛选出所有符合连接条件的数据

3.6 联合查询

• 在单个查询中多个表关联查询出相似的数据
• 对单个表执行相似的操作，按单个表返回数据
  关键字UNION
• 必须两个以上select语句
• 每个查询都必须包含相同的列；
• UNION会自动去除重复的行
• 使用UNION ALL 不去除重复的行
• UNION 不允许多个ORDER BY语句，只能在最后使用