1.show databases;//查询数据库服务器上的数据库
2.show database xsgl;//查询数据库服务器上xsgl数据库
3.create database xsgl;//创建数据库xsgl,使用默认编码集和校验规则
4.alter database xsgl default character set gbk collate gbk_bin;
修改数据库xsgl编码集为gbk,校验规则为gbk_bin;
5.drop database xsgl;删除数据库xsgl。
6.show table 表名;显示数据库表。
7.create table student(sno char(6) not null, name char(8),sex char(2) default 'M',birthday datetime,sdep char(20));创建表;
先使用数据库 use 数据库名字。
8.descibe 表名;查看数据表;
9.alter table student add memo varchar(200);在student表最后增加字段为memo,数据类型为varchar,长度为200;
10.在student表的所在系sdept字表后边添加一个字段native_place,数据类型为char;
alter table student add native_place char(30) after sdept;
11.alter table student change 老名字 新名字 char(200); 修改student表中名字为info,类型为char(200)
12.alter table student modify info varchar(100);只修改数据类型。
放后边 after sdept;
13.只改表名
alter table 旧表名 rename 新表名;
14.删表
drop table 表名;
15.查看某个数据库里的所有表
show tables;
16.把表中的字符集修改为utf8;
alter database 表名 character set utf8;
——————————————————————————————————————————————————————————
约束:
定义了必须维护数据一致性和正确性的规则。
5种类型:唯一 默认,主键,外键,检查(在MySQL中实现不了)
1.定义约束
可在定义表的时候定义约束
也可以修改已经创建好了的表结构,添加约束
alter add[constraint 约束名字]约束类型;
2.删除约束
alter constraint 约束名字;
3.主键约束
主键:知道主键就知道任何一个字段的值,主键只是唯一的,没重复值,没有空值。
给已经创建好了的表,添加主键
语法:
create table 表名(字段一 类型(??) primary key, 字段2....);
alter table 表名 add primary key(字段名)
定义联合主键
create table 表名(字段数据类型,.....,primary key(表名1,表名2));
删除主键
alter table 表名 drop primary key;
4.唯一约束
要求没重复值,可有一个空值,可设置多个。
语法:
添加唯一约束
alter table 表名 add unique (字段名);
5.默认约束
可以在创建表时给某个字段定义默认约束
create table 表名(字段1数据类型[约束][是否为空],.....default'默认值',...);
添加默认约束
alter table 表名 alter 字段名 set default;
删除默认约束
alter table 表名 alter 字段名 drop default;
6.外键约束
可以使两个表产生关系
语法:
在创建表的时候创建
外键
create table 表名(字段名类型......, [constraint 外键名字]foreign key(做外键的名字(字表))reference 参照表名(参照表字段名(附表必须为主键或唯一约束)));
例如:
create table sc(sno char(6),cno char(6),score decimal(4,1),primary key(sno,cno),foreign key(sno) references student(sno));
在已经建好的表中添加外键约束
alter table 表名 add constraint 约束名 foreign key(外键字段名)references 参照表名(同上);
—————————————————————————————
添加值
insert into 表名(字段名) values('...','....');
insert into 表名(字段名) values('...','....',default,null);
添加多条值在一个表中:
insert into 表名 values('...','..'.....),('...','...' ...),
查看表字段的值
select * from 表名;
自动增长列:
语法:
create table emp(eid int primary key auto_increment,ename char(20));
创建表emp,字段类型为整数,做主键,自动增长;ename为字符类型,长度为20个字符
向表中插入语法为:
insert into 表名(字段名) values (值1,.....);
修改数据:
update 表名 set 字段名='.......' where 字段名='唯一约束';
replace into 表名 values('','','',.....);//影响两行等于把这段数据删了,在从新insert;
删除数据:
delete from 表名 where 字段名(删除哪行)='....';//有记录,可以恢复
truncate table 表名;//彻底删除
—————————————————————————————
查询数据:
查询部分数据:
select 字段名1,字段名2,... from 表名;
如果查询表中所有字段的值,可以用*匹配,替代所有的字段名字
select * from 表名;
select * from 表名 where 字段名='...';
顺序: not and or
select * from 表名 where 字段 between .. and ..;
select * from 表名 where 字段 ...and ..;
select * from 表名 where 字段 ...or ..;//既满足前者,有满足后者
select * from 表名 where 字段 in('','',..);;//既满足前者,有满足后者,数据多的话用这个
select * from 表名 where 字段 is null;
select * from sc where score is not null;
模糊查询:
%多个字符 _ 一个字符
select * from 表名 where sname like '....';
排序:
select * from 表名 order by 字段名;
升序不用写,降序为desc;
select * from 表名 order by 字段名,字段名 desc;
limit:主要用于限制被select 语句返回的行数
格式为:
select * from 表名 order by 字段名,limit 0,?;
select 字段名 from 表名;
消除重复行 distinct 去除查询结果中重复行
select distinct 字段名 from 表名;
—————————————————————————————
常用聚合函数: 可加where 约束;
now 例如:
select sno标题1,sname 标题2,year(now())-year(birthday)标题3 from st
udent;
查询总行数
count 例如:
select count(*)from student;
求平均值:
select avg(字段名) from 表名 ;
求最高分:
select max(字段名) from 表名;
求最低分:
select min(字段名) from 表名;
分组;group by
select sex count (*) from student group by sex;
having放到group by后边
例如:
select cno, max(score) 最高分 from sc group by cno having count(sno)>3 ;
数学函数:
select ceilling();向上取整
select floor();向下取整
select round();四舍五入
select truncate(,);强行截取小数点后的几位数
select reverse();反转字符串
—————————————————————————————
多表查询:
连接方式:
全连接:select 表名1.字段名,.....from 表名1,表名2 where 表名1.字段名=表名2.字段名;
例如:
select student.sno 学号,sname 姓名,cname 课程名,score 成绩 from student,course,sc where student.sno=sc.sno and https://www.doczj.com/doc/8714153244.html,o=https://www.doczj.com/doc/8714153244.html,o;
表名字太长可使用别名,格式:表名 新名字
join连接:
内连接:通过共享列连接,未指明条件时默认内连接
select 表名.字段名,.....from 表名1 (inner) join 表名2 on 表名1.字段名=表名2.字段名;
例如:
select student.sno,sname,cname,score from student
join sc on student.sno=sc.sno
join course on https://www.doczj.com/doc/8714153244.html,o=https://www.doczj.com/doc/8714153244.html,o;
外连接:
显示包含来自一个表中所有的行和来自另一个表中匹配的结果集;
左外连接:可以查出null,student做左表
select 表名1.字段名,.....from 表名1 left join 表名2 on 表名1.字段名=表名2.字段名;
右外连接:查询右表中所有记录和左表中所有记录
select 表名1.字段名,.....from 表名2 right join 表名1 on 表名2.字段名=表名1.字段名;
自连接:先后无所谓
必须指定两个不同的别名.
格式:
select 字段名 from 表名 别名1 join 表名 别名2 where 别名1.字段名=别名2.字段名;
——————————————————————————*******************************
子查询:嵌套查询
外层的select叫外层查询,内层的叫子查询;
格式:select * from 表名 where 字段名=(select 字段名 from 表名 where 字段名);
例如:
select * from student where sdept=
(select sdept from studentstudent where sname='贾东');
多个值用in:
例如:
select sname from student where sno in(select sno from sc where cno='210203');
(not) exists:判断结果集是否为空,(相关子查询);
格式:
select 字段名 form 表名 where exists(select * from sc where 表名1.字段名= 表名2.字段名);
例如:
select sname from student where exists(select * from sc where cno='210203');
select sname from student where sno in
(select sno from sc group by sno having count(*)=(select count(*)from course));
____________________________________________________________________________索引:
创
建索引:
index 关键字
普通索引:index
唯一索引:unique
主键索引:不要为null
全文索引:fulltext 只能在char varchar text类型中创建
只有MyISAM表类型支持全文索引
格式 :crete index 索引名字 on 表名(字段名字);
创建唯一索引: create unique index 索引名字 on 表名(字段名字);
查询索引格式: show index from 表名;
添加索引:alter table 表名 add index 索引名(字段名);
删除索引: drop index 索引名 on 表名;
删除索引: alter table 表名 drop index 索引名;
____________________________________________________________________________________视图:虚拟的表
create view
merge:合并语句可以多个select合并结果;
temptable:创建一个虚拟表,select可以在里边查;
undefined:默认算法,倾向于merge
创建视图格式: create [algorithm=merge|temptable|undefined] view 视图名 as select * from 表名(条件);
替换视图格式: create or replace view 视图名(可加汉字名字)as select * from 表名 where 字段名的条件;
查询视图: desc 视图名;
修改视图:alter view 视图名字 as select * from 表名(where条件语句);
修改时检查:with check option;满足条件的才能添加
不能修改视图中导出列的值,可以修改非导出列的值;
删除视图:与删除表相似(有内建外键不可以随意删)
drop view 视图名字;
_______________________________________________________________
查询系统变量:
select @@ version;
select current_data;
select current_timestamp;
查询用户变量:(前面有一个@符号,如果没有赋初始值默认为null)
使用set语句:例如:
set@x=100;//定义变量
select@x;//查询变量的值
select@y:=200;//定义变量,赋初始值,并显示初始值
set@x=(select sdept from student where sno='99109');//定义变量x
select * from student where sdept=@x;//用变量x;
算数运算符:
select 1+2,2*3;
case语句:
select sno,cno,
case
when score>=90 then'优秀'
when score>=80 then'良好'
when score>=70 then'中等'
when score>=60 then'及格'
else '不及格'
end
from sc;
_________________________________________________________________
delimiter 定义符号结束
例如: delimiter #
select * from student#
定义变量;
declare x int;
赋值: set x=100;
定义存储过程;
delimiter ##
create procedure stuinfo(out name char(5),out dept char(20))//形参
begin
select sname,sdept into name,sdept from student where sno='99107';//实参
end ##
delimiter ;
调用存储过程:
call stuinfo(@name,@dept);
select @name,@dept;
调用if语句;
delimiter ##
create procedure stuinfo(in score int,out result varchar(20))//形参
begin
if(score>=60)then set result='及格';
else set result='不及格';
end if;
end ##
delimiter ;
调用存储过程:
call stuinfo(70,@result);
select @result;
___________________________________________________________________________循环:
用while
循环求1+2+3...+n的和:
delimiter ##
create procedure dosum(in n int,out sum int)
begin
declare i int default 1;
set sum=0;
while(i<=n)do
set sum=sum+i;
set i=i+1;
end while;
end ##
delimiter ;
用repeat循环求1+2+3...+n的和:
delimiter ##
create procedure dosum(in n int,out sum int)
begin
declare i int default 1;
set sum=0;
repeat
set sum=sum+i;
set i=i+1;
until i>n;
end repeat;
end ##
delimiter ;
用Loop循环求1+2+3...+n的和:
delimiter ##
create procedure dosum(in n int,out sum int)
begin
declare i int default 1;
set sum=0;
mylabel:loop
set sum=sum+i;
set i=i+1;
if (i>n) then
leave mylabel;
end if;
end loop mylabel;
end ##
delimiter ;
删除存储过程:
drop procedure 名字;
查看存储过程:
show procedure status like '名字';
________________________________________________________________________________
事务:
原子性;一致性;隔离性;持久性;
隔离级别:
1.序列化:隔离级别最高,但很少用,俩个事物都提交
2.可重复读:最常用
3.提交读:能看到其他事务添加的记录
4.未提交读:安全性差
set transaction isolation level .....;//设置事务隔离级别:
start transaction;//启动事务
commit;//提交
________________________________________________________________________________
备份与恢复:
在DOS里边执行
mysqldump –u用户名 –p密码 数据库名>生成的脚本文件路径
恢复以前要先建好数据库
_______________________________________________________________________________
创建新用户,用户名user123,密码:password
并赋予用户select学生xsgl中student 的权限
grant select on xsgl.student to 'user123'@'localhost' identified by'password';
__________________________________________________________________________________
数据表设计
一对多关系:每一个实体定义为一个表,实体属性定义为表的字段,为了体现一对多的关系,在多方定义一个字段作为快捷键,这个外键字段是一方的主键字段。
多对多的关系:每一个实体定义为一个表,实体属性定义为表的字段,为了体现多对多的关系定义为一个表,联系属性定义为表的字段,两个表的主键做联系表的联合主键。
选修课:
http://192.168.0.133/