数据库跟表的增删改查

数据库包含 表\函数\存储过程创建数据库
CREATE DATABASE[ IF NOT EXISTS] 数据库名 数据库选项数据库选项：CHARACTER SET charset_nameCOLLATE collation_name
create database d1 default character set gbk;# 创建表
create table t1(age int, name varchar(10));
# 给上表增加列
alter table t1 add column addr varchar(50);
# 删除表
drop table t1;# 表的增删改查
create table t1(age int, name varchar(10));
insert into t1(age,name)  values(10,"丁丁"),(11,"泥泞"); # 插入数据
select * from t1; # 查数据
# 修改丁丁的age
update t1 set age=12 where name="丁丁"; 
select * from t1; 
# 查询泥泞数据
select * from t1 where name="泥泞"; 
# 删除
delete from t1 where name="丁丁";create table t1(age int, name varchar(10));
insert into t1(age,name)  values(10,"丁丁"),(11,"泥泞"); # 插入数据
select * from t1; # 查数据# 修改丁丁的age
update t1 set age=12 where name="丁丁"; select * from t1; # 查询泥泞数据
select * from t1 where name="泥泞"; # 删除
delete from t1 where name="丁丁";show databases;create table boy(name varchar(10), age int, addr varchar(50));
create table girl(name varchar(10), age int, addr varchar(50));insert into boy(name, age, addr) 
values
("张飞",32,"北京"),
("谢霆锋",30,"南京"),
("李四",25,"天井"),
("甲流",33,"酒精"),
("鲁北",24,"南海");select * from boy;insert into girl(name, age, addr) 
values
("泻立停",32,"北京"),
("佳美",30,"府东"),
("盼盼",23,"背黑锅"),
("欣欣",34,"安防"),
("玲玲",24,"安慰人");select * from boy;
select * from girl;# 单表查询
use d1; # 去重查询
select distinct age from boy; # 查询age在25-30之间的
insert into boy values("彪子",28,"韩国");
insert into boy values("鬼小四",26,"几盆");
select * from boy where age between 25 and 30; # 查询age 25和30的数据 固定范围
select * from boy where age in (25,30);# 查询name中, 有四的列出来, %通配符,
select * from boy where name like "%四%";
select * from boy where name like "%四";
select * from boy where name like "彪%";# 排序输出
select * from boy order by age;
# 降序
select * from boy order by age desc;# 显示前两个
select * from boy limit 2;# 查看表中字段
desc t1;# 统计age数量,  age,count(1)是表头
insert into boy values("王八那",26,"as");
insert into boy values("阿斯钢",26, "阿三哥");
select 
age,count(1) 
from boy 
group by age;# 多表查询 # 内连接   as后面跟的是查询出来的表头, on 后面是查询的条件
# 两张表的交集
select
a.`name` as "男名字", b.`name`as "女名字"
from
d1.boy a 
inner join d1.girl b on a.age=b.age;# 左连接, 以左边的a.age查询为主,
# 这里就不得不说下我们玩的DNF了, 创建一个账号,但是里面有很多角色,
# 左表为基础, 右表有就好了
insert into girl values("阿美美",32, "阿哥");
insert into girl values("慧海",24, "笔记");
select
a.`name` as "男名字",a.age as "男年龄", b.`name`as "女名字", b.age as "女年龄"
from
d1.boy a 
left join d1.girl b on a.age=b.age;# 右连接
insert into girl values("阿美美",32, "阿哥");
insert into girl values("慧海",24, "笔记");
select
a.`name` as "男名字",a.age as "男年龄", b.`name`as "女名字", b.age as "女年龄"
from
d1.boy a 
right join d1.girl b on a.age=b.age;# 系统函数
# 聚合函数
# 求表的sum, max min avg
select 
sum(age), max(age),min(age),avg(age)
from boy;# 字符串汇合
select 
name, CONCAT("角色名",name,age,addr) as concat
from boy;-- 将name字段的四换成一
select 
name, replace(name,'四', '一')
from boy;# 日期函数
select
now();# 加两天
select
DATE_ADD("2019-1-26",interval 2 DAY);select
DATE_SUB("2019-1-26",interval 2 DAY);

自定义函数

show function status;
delimiter $$
drop FUNCTION if EXISTS myAddFunc $$
create function myAddFunc(a int, b int)
returns int
begin return a+b
end $$
delimiter;