show databases;
create database 数据库名 default character set utf8;
show create database 数据库名;
drop database 数据库名;
alter database 数据库名 default character set gbk;
show tables;
create table 表名(
字段名 字段类型,
字段名 字段类型,
字段名 字段类型
);
desc 表名;
drop table 表名;
alter table 表名 add (column) 新字段 新字段类型;
alter table 表名 drop (column) 字段名;
alter table 表名 modify (column) 字段名 新的字段类型;
alter table 表名 change 旧字段名 新字段名 新字段类型;
alter table 表名 rename (to) 新表名;
create table student(
id int,
NAME varchar(20),
gender varchar(2);
age int,
jsp int,
servlet int,
address varchar(10)
);
INSERT INTO student VALUES(1,'张三','男',20);
insert into student(id, NAME) VALUES (2,'李四');
UPDATE student SET gender='女';
UPDATE student SET gender='男' WHERE id=1;
UPDATE student SET gender='男', age=30 WHERE id=2;
DELETE FROM student;
TRUNCATE TABLE student;
SELECT * FROM student;
select id,NAME from student;
select id as '编号',NAME as '姓名' from student;
select id,NAME,gender,age,'java学习班' as '班级' from student;
select id,NAME,(servlet+jsp) as '总成绩' from student;
select distinct gender from student;
select distinct(gender) from student;
select distinct address from student;
select * from student where id=2 and NAME='李四';
select * from student where id=2 or NAME='张三';
select * from student where servlet>70;
select * from student where jsp>=75 and jsp<=90;
select * from student where jsp between 75 and 90;
select * from student where address is null or address=='';
select * from student where adress is not null and address <>'';
select * from student where NAME like '张%';
select * from student where NAME like '李_';
select SUM(servlet) as 'servlet的总成绩' from student;
select AVG(servlet) as 'servlet的平均分' from student;
select MAX(servlet) as '最高分' from student;
select count(*) from student;
select * from student limit 0, 2;
select * from student limit 2, 2;
select * from student limit 4, 2;
select * from student order by id asc;
select * from student order by servlet asc, jsp desc;
select gender, count(*) from student group by gender;
select gender count(*) from student where group by gender having count(*)>2;
create table employee(
id int primary key,
empName varchar(20),
deptId int
constraint employee_dept_fk foreign key(deptId) references dept(id) on update cascade on delete cascade
);
create table dept(
id int primary key;
deptName varvhar(20)
);
select empName,deptName from employee, dept;
select empName, deptName
from employee, dept
where employee.deptId=dept.id
select empName, deptName
from employee
inner join dept
on employee.deptId=dept.id
select e.empName, d.deptName
from employee as e
inner join dept as d
on e.deptId=d.id
select d.deptName, e.empName
from dept d
left outer join employee e
on d.id=e.deptId;
select e.empName,d.deptName
from employee e
right outer join dept d
on e.deptId=d.id;
select e.empName,b.empName
from employee e
left outer join employee b
on e.bossId=b.id
delimiter $
create procedure 存储过程名()
begin
select * from employee;
end $
call 存储过程名();
delimiter $
create procedure pro_findById(IN eID INT)
begin
select * from empolyee where id=eID;
end $
call pro_findById(4);
delimiter $
create procedure pro_testOut(OUT str varchar(20))
BEGIN
SET str='这是一个输出参数';
END $
call pro_testOut(@NAME)
select @NAME
drop procedure 存储过程名;
delimiter $
create procedure pro_testInOut(INOUT N INT)
begin
select n;
set n=500;
end $
call pro_testInOut(10);
select @n;
delimiter $
create procedure pro_testIf(IN num int, OUT str varchar(20))
begin
if num=1 then
set str='星期一';
elseif num=2 then
set str='星期二';
elseif num=3 then
set str='星期三';
else
set str='输入错误';
end if;
end $
call pro_testIf(1,@str)
delimiter $
create procedure pro_testWhile(IN num int, OUT result int)
begin
declare i int default 1;
declare vsun int default 0;
while i<num do
set vsum = vsum+i;
set i=i+1;
end while;
set result=vsum;
end $
create table log(
id int primary key auto_increment,
content varchar(100)
);
create trigger tri_empAdd after insert on employee for each row
insert into log(content) values('员工表插入了一条记录');
create trigger tri_empAdd after update on employee for each row
insert into log(content) values('员工表修改了一条记录');
create trigger tri_empAdd after delete on employee for each row
insert into log(content) values('员工表删除了一条记录');
SELECT * FROM USER
UPDATE USER SET PASSWORD=PASSWORD('123456') WHERE USER='root';
GRANT [SELECT|DELETE] ON 数据库名.表名 TO '账户名'@'localhost' IDENTIFIED BY '密码';
mysqldumn -u root -p 数据库名 > c:/bf.sql
mysql -u root -p 数据库名 < c:/bf.sql