mysql增删改查常用操作总结（纯干货-基本够用）

F6

整数 ：4个

bit (1-64)

tinyint (-128~127)

smallint(-32768~32767)

int(-2147483648~2147483647)

小数：3个

float

double

decimal

时间日期： 4个

data(日期存储范围为：1000-01-01~9999-12-31)

datetime(日期和时间的组合：1000-01-01 00:00:00~9999-12-31 23:23:23)

timestamp(时间戳，转换成本地时间再存)

time(范围-838:59:59~838:59:59)

字符串：

char(M) M固定长度，范围（0~255）（长度不够后面空代替）

varchar(M) M变长 （多长就是多长，效率比char低）

text 长文本

mysql中没有boolean，我们可以使用tinyint标识：0 false 1 true

F6

（1）show tables; (展示所有存在的表)

（2）desc user;（刷新展示当前表）

（3）show create table user; （展示表）

表结构操作：

//创建表

create table test2(id int(10),name varchar(255));

插入数据

insert into test2 values(10,"zhangsan");

3.修改列类型

alter table user modify userage varchar(5);

4.增加列

alter table user add userpwd varchar(20);

5.删除列

alter table user drop userage;

6.列改名

alter table user change userpwd password varchar(20);

7.更改表名

alter table user rename users;

rename table users to user;

 

8.删除数据（连表一起删除）

drop table test1;

 

9.删除表中全部数据 (删除表中数据-表结构不删除)

truncate table test1;

 

10.删除表中数据（一条一条删-效率低）

delete from user;

用delete删除 数据，然后添加。可以看到添加之后id标识不连续。（说明delete删除不释放空间）

 

truncate 与delete 比较：

truncate table 在功能上与不带 WHERE 子句的 delete语句相同：二者均删除表中的全部行。

truncate 比 delete速度快，且使用的系统和事务日志资源少。

truncate 操作后的表比Delete操作后的表要快得多。

当表被清空后表和表的索引讲重新设置成初始大小，而delete则不能。

 

约束：

约束的含义都很重要 操作时只有建表时构建约束最重要

1.非空约束

建表时加： create table pers(name varchar(20) not null ,age int(5));

删除：alter table pers modify name varchar(20);

建表后加：alter table pers modify name varchar(20) not null;

 

2.唯一约束

建表时加：create table pers1(name varchar(20) unique ,age int(5));

删除： alter table pers1 drop index name;

建表后加： alter table pers1 add constraint unique(name);

给多列增加唯一约束： alter table pers1 add constraint unique(name,age);

 

3.主键(用来唯一标识当前行数据的列)约束 唯一，非空，一张表只有一个主键

建表时加：create table pers2 (name varchar(20) primary key,age int(5));

删除： alter table pers2 drop primary key;

建表后加：alter table pers2 add constraint primary key(name);

 

联合主键:create table pers3 (name varchar(20),age int(5),primary key(name,age));

两列都不许为null 两列不允许同时重复

 

4.自动增长:一般给数字列加，并且只能给主键列加

 

建表时加：create table 举个栗子 (id int(5) primary key auto_increment,name varchar(20));

删除：alter table 举个栗子 modify id int(5);

建表后加： alter table 举个栗子 modify id int(5) auto_increment;

 

5.区分大小写（注意window系统不区分大小写，liunx系统区分大小写）

create table users1 (name varchar(5) binary primary key,age int(3));

删除: alter table users1 modify name varchar(5)

 

增删改操作：

1.插入数据

insert into user(username,password) values("xx2","654321");

insert into user values("xx1","123456");

insert into user(username) values("xx3");

 

2.删除全部数据

delete from user;（查询方式删除）

truncate table user;（直接删除）

drop table user ;（连表一起删除）

删除指定行数据：

delete from users1 where name='XX1';

3.修改数据

update 举个栗子 set name="qqq";

update 举个栗子 set name="xx2" where id=2;

4.给定列初始值

create table stus (name varchar(20) default 'zhangjun',age int(3) default 20,qq1 varchar(20),qq2 int(3));

 

查询：

1.查看若干列

select name,pwd from users;

2.取别名：

select name '姓名',pwd from users;

 

3.合并列显示

 

select concat(name,pwd) '姓名+密码' from users;

 

select concat(name,"++",pwd) '姓名+密码' from users;

 

4.只查看若干行：

select * from users where age>=23;

select * from users where age<>23;

select * from users where name >'lishuo1';

 

5.可以结合算术表达式使用

select age*10 from users;（查出来每一个乘以10）;

 

6.查找并去重

select distinct * from users;

select distinct name from users;

7.查找指定区间的数据

select * from users2 where salary>=500 AND salary<=3000;

select * from users2 where salary between 500 and 3000;

 

select * from users2 where salary in (1000,1500);

 

8.查null值

select * from users2 where salary is null;

 

9.模糊查询

查询名字中带a的人

select * from users2 where name like '%a%';

查询名字以a开头的人

select * from users2 where name like 'a%';

查询名字长度为2并且以a结尾的人

select * from users2 where name like binary '%_a';

10.与或非

select * from worker where sex='man' and salary >1000;

select * from worker where salary >1000 and sex ='man' or sex='woman' and dept='技术部';

select * from worker where dept is not null;

select * from worker where salary not between 1000 and 5000;

select * from worker where salary not in(1000,6000);

 

11.对查询结果排序

select * from worker where salary between 1000 and 5000 order by salary;

select * from worker where salary between 1000 and 5000 order by salary desc

(倒序，从大到小排序)

select name '姓名',salary ,salary/10 '税金' from worker where salary>3000 order by salary;

（常用函数）查询2：

1.虚拟表

select LOWER('ABCD') from dual;

2.查询日期时间

select curdate(),curtime(),now() from dual;

+------------+-----------+---------------------+

| curdate() | curtime() | now() |

+------------+-----------+---------------------+

| 2015-08-14 | 14:56:59 | 2015-08-14 14:56:59 |

+------------+-----------+---------------------+

 

select year(now()),hour(now()),minute(now()),monthname(now()) from dual;

 

3.查看数据库及用户信息

select database(),version(),user() from dual;

+------------+-----------+----------------+

| database() | version() | user() |

+------------+-----------+----------------+

| szt_13 | 5.5.39 | root@localhost |

+------------+-----------+----------------+

 

4.加密

select password('123456'),md5('123456') from dual;

 

5.聚合函数，求总和，均值，最大，最小

select count(salary) from worker; //计数的聚合函数

select count(*) from worker; //计数的聚合函数

 

select avg(salary) from worker; avg值

 

select sum(salary),max(salary),min(salary) from worker;

 

6,分组

查询每个部门的总工资：

select dept, sum(salary) from worker group by dept;

 

出现在SELECT列表中的字段，要么出现在组合函数里，要么出现在GROUP BY 子句中

 

7.分组后限定：

分组前限定用where,分组后限定用having

 

select dept, sum(salary) from worker group by dept having sum(salary)>=3000

 

select dept, sum(salary) from worker where sex='man' group by dept ;

 

select dept, sum(salary) from worker where sex='man' group by dept having sum(salary)>=1000 ;

 

8.limit限定：

只看前5行数据

select * from worker limit 5;

6-8行数据

select * from worker limit 5,3;

+----+------+--------+-------+--------+

| id | name | dept | sex | salary |

+----+------+--------+-------+--------+

| 6 | ffff | 保卫处 | man | 1000 |

| 7 | ggg | 保卫处 | woman | 3000 |

| 8 | kkk | 公关部 | man | 5000 |

+----+------+--------+-------+--------+

 

索引和视图：

二叉树索引，哈希索引

1.创建

create view myv1 as select empname from emp e1 where salary =(select max(salary) from emp e2 where e2.deptid=e1.deptid);

 

create view v1 as SELECT * FROM `table1` WHERE id=2 AND name="李四";

2.查看

show tables;

 

3.删除

drop view myv1;

 

4.修改

alter view myv1 as select empname,salary,sex from emp;

5.视图和原表的关系

原表中数据发生变化，则视图数据变化，反之亦然

注意向视图中插入数据的操作并不总是能执行

6.查看索引

 

哈希索引 BTree索引

 

show index from emp;

 

7.添加索引：如果有某一列经常作为查询的依据，那么我们可以给该列加上一个索引

create index in1 on emp (salary);

create index ind1 on user (name);

主键列默认会有索引 唯一约束列默认会有索引

 

8.删除索引

alter table emp drop index empname;

drop index in1 on emp;

 

只有Where后面的第一个约束的索引是生效的，其他的是不生效的

select * from user where username='dahuang' and userpwd='123456'

总结：

创建索引和删除索引：

create index ind1 on user(name);

drop index ind1 on user;

创建视图和删除视图：

create view v1 as SELECT * FROM `user` WHERE id=2 AND name="李四";

drop VIEW v1;

 

触发器：

触发器：trigger 监视某种情况并出发某个动作

 

当订单表增加一项时，仓储表发生对应变化：

---总是鼠标会减掉2个

delimiter $

create trigger tg after insert on sale for each row

begin

update goods set goodsNum=goodsNum-2 where goodsId=2;

end$

 

drop trigger tg; //删除触发器

订单下多少，就减掉多少

delimiter $

create trigger tg2 after insert on sale for each row

begin

update goods set goodsNum=goodsNum-new.salegoodsNum where goodsId=new.salegoodsid;

end$

 

 

删除订单数据，仓储数据也恢复

delimiter $

create trigger tg3 after delete on sale for each row

begin

update goods set goodsNum=goodsNum+old.salegoodsNum where goodsId=old.salegoodsid;

end$

 

修改订单数据，仓储数据也修改

delimiter $

create trigger tg4 after update on sale for each row

begin

update goods set goodsNum=goodsNum+(old.salegoodsNum-new.salegoodsNum) where goodsId=old.salegoodsid;

end$

 

 

 

before after区别演示：

delimiter $

create trigger tg5 before insert on sale for each row

begin

declare max integer;

select goodsnum into max from goods where goodsid=new.salegoodsid;

 

if new.salegoodsnum>max then

set new.salegoodsnum=max;

end if;

 

update goods set goodsnum=goodsnum-new.salegoodsnum where goodsid=new.salegoodsid;

end$

 

转出数据库sql文件：

点击数据库右键——》转储sql文件——》结构和数据

导入sql文件：

新建一个数据库（编码utf-8）

点击数据库右键——》运行sql文件——》

 

导出sql文件---->执行sql

 

外键：

外键约束: foreign key

B表要出现A表中的数据，那么B表中的数据就不能超出A表中的数据范畴

 

B表中的引用数据的这一列，我们就加一个外键约束即可，此列取值，全都在A表的主键中

 

外键：就是另一张表的主键数据

 

外键列数据可以重复，也可以是null，就一个要求，不超出数据来源表的数据范畴即可

重点掌握！！

 

 

 

附表加外键约束：

 

 

 

 

存储过程和函数：

 

create procedure p3(num int,choice varchar(1))

begin

if choice='y' then

select * from emp where salary>num;

else

select * from emp where salary<num;

end if;

end$

 

call p3(5000,'y')$

 

create procedure p2(num int)

begin

select * from emp where salary>num;

end$

 

传入一个int值，求该值到1的和

create procedure p5(num int)

begin

declare temp int;

declare total int;

set temp=1;

set total=0;

while temp<=num do

set total=total+temp;

set temp=temp+1;

end while;

select total from dual;

end $

 

//函数 有返回值 存储过程 没有返回值

create function p5(num int) returns int

begin

declare temp int;

declare total int;

set temp=1;

set total=0;

while temp<=num do

set total=total+temp;

set temp=temp+1;

end while;

return total;

end $