MYSQL的数据库基本操作

1、数据库的数据类型
整型：int bit
小数：decimal【浮点数decimal（5,2）111.11】
字符串：varchar char(8) [‘ab’ ‘ab ’ char的例子] (0-255)
时间： date ‘2018-09-03’
time ‘11:11:11’
datetime ‘2018-09-03 11:11:11’
timestamp 时间戳:格林威治时间1970年01月01日00时00分00秒(北京时间1970年01月01日08时00分00秒)起至现在的总秒数
大文本存储：text （0-65535）字符数大于4000
枚举类型：

2、约束
主键：primary key 物理存储顺序
非空：not null 不允许为空
唯一：unique 不允许重复
默认值：default 默认值，如果填写，以填写的值为准
外键：foreign key
3、实现一个实例
3、1设计数据库
create database waterDB（库名） charset=utf8;
3、2使用的数据库
use waterBD（库名）;
3、3创建一张新表
create table customer（表的名字）(
id int primary key auto_increment not null,
name varchar(10) not null,
password varchar(10) not null,
gender enum(‘boy’,’girl’,’secret’),
active int default 0
)
create table address(
id int primary key auto_increment not null,
add_name varchar(10) default ‘杭州’,
cus_id int not null
);
3、4查看数据库里的表
show tables;
3.5查看表的结构
desc customer（表名）
3.6删库操作
drop database customer;
3.7增加字段
alter table customer add email varchar(20) not null;
alter table customer add is_delete varchar(20) not null default 0;

3.8修改字段
alter table customer change name user_name varchar(20) not null;
3.9删除字段
alter table customer drop email;
3.10 删除表
drop table customer

4、数据CRUD–初级
4.0 增加数据
insert into customer values(0,’老王’,’123456’,’boy’,0);
insert into customer values(0,’王丑丑’,’123456’,’girl’,1);
insert into address values(0,0,1);
insert into address values(0,’杭州’,3);
4.1 查询语句
select * from customer;
select name as ‘姓名’,gender from customer;
4.2 按列插入数据
insert into customer(user_name,password) values(‘老张’,’123456’);
4.3 按列插入多行数据
insert into customer(user_name,password,email) values(‘老王’,’123456’,’12@qq.com’),(‘老李’,’123456’,’23@qq.com’);
4.4 修改数据
update customer set email=’123@qq.com’ where id=3;

4.5 删除数据
delete from customer where id=1;
4.6 逻辑删除
update customer set is_delete=1 where id=5;
select * from customer where is_delete=0;

5、数据CRUD–中级（条件）

5.2 比较运算符的问题
select * from customer where id > 7;
select * from customer where id !=7;
select * from customer where id <>7;(> >= < <= != <>)

5.3逻辑运算符
select * from customer where id > 7 and user_name=’老王’;（and or not）

5.4 模糊查询
select * from customer where user_name like ‘王%’;
%代表匹配任意的多个字符
select * from customer where user_name like ‘王%’ or user_name like ‘_王%’ or user_name like ‘%王’;

5.5 范围查询
不连续范围查询
select * from customer where id in(3,7,9,100);
连续范围
select * from customer where id between 3 and 9;(包括序号3和9)

5.6 null
select * from customer where gender is null;

5.7 not null
select * from customer where gender is not null and user_name like ‘%王’;

5.8 顺序的问题
括号 > not >比较运算符 > 逻辑运算符

5.9 排序的问题
desc降序 asc升序
select * from customer order by user_name desc ,id desc;
语句顺序的问题，如果第一个条件出现同值的情况，按照第二个语句来进行排序

6、数据CRUD–高级（查询结果）
6.10 删除重复行
select distinct user_name from customer;
6.1聚合函数
sum() max() min() avg() count()

    select avg(id) from customer where id >7 ;
    select sum(id)/count(id) as '均值' from customer where id >7 ;
    select count(id) from customer where id >7 ;

6.2 分组的问题
select gender from customer group by gender;
select gender,group_concat(user_name) from customer group by gender;
select gender,group_concat(user_name) from customer group by gender having id>3;(x)
select gender,group_concat(user_name,id) from customer group by gender having group_concat(id)>3;

having 和 where 功能是一样的
select * from customer having id>3;

select gender,avg(id) from customer group by gender;
6.3 分页的问题（解决数据量大的问题）
select * from customer limit 0,5;

7、完整版语句
select distinct * from customer
where 条件
group by 字段
having 条件
order by limit start,count
8.SQL注入的问题
配合execute,列表化参数，或者正则匹配

9.连接查询
内连接查询
select * from (customer inner join address) on id=cus_id;
select * from customer inner join address on customer.id=address.cus_id;
左连接查询
select * from customer left join address on customer.id=address.cus_id;
右连接查询
select * from customer as a right join address as b on a.id=b.cus_id;

10.子查询
select * from customer where id >(select avg(id) from customer);
select * from customer where id in (select cus_id from address);