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 $