SQL语句的分类:
DDL: 数据定义语言
create / drop / alter
DML:数据操作语句
insert / delete /update / truncate
DQL: 数据查询语言:
select / show
CRUD
一、 数据库操作:
增:
创建数据库: create database db1;
删:
删除数据库:drop database db1;
改:
修改数据库字符集:alter database db1 default character set gbk;
查:
查询所有数据库:show databases;
查看数据库默认字符集:show create database db1;
选择数据库:use db1;
二、 表操作:
增:
创建表:create table student(id int,name varchar(20),age int);
数据约束
1. 默认值:
Create table student(
Id int,
Name varchar(20),
Address varchar(20) default ‘北京’
);
2. 非空:
Create table student(
Id int,
Name varchar(20),
Gender varchar(2) not null
);
3. 唯一:
Create table student(
Id int unique,
Name varchar(20)
);
4. 主键:
Create table student(
Id int primary key, -- 非空+唯一
Name varchar(20)
);
5. 自增长:
Create table student(
Id int(4) zerofill primary key auto_increment, -- 自增长,从0开始s
Name varchar(20)
);
可以影响自增长约束的删除:truncate table student;
6. 外键:
主表:部门表
从表:员工表
Create table dept(
Id int primary key,
deptName varchar(20)
);
Create table employee(
Id int primary key,
empName varchar(20),
deptId int,
constraint employee_dept_fk foreign key(deptId) references dept(id)
);
7. 级联修改:
Create table dept(
Id int primary key,
deptName varchar(20)
);
Create table employee(
Id int primary key,
empName varchar(20),
deptid int
constraint emplee_dept_fk foreign key(deptid) references dept(id) on update cascade on delete cascade
);
删:
删除表:drop table student;
可以影响自增长约束的删除:truncate table student;
改:
表操作:
修改表名称:alter table student rename to students;
字段操作:
添加字段:alter table student add gender varchar(2);
删除字段:alter table student drop gender;
修改字段类型:alter table student modify name varchar(10);
修改字段名称:alter table student change name stuName(20);
查:
查看所有表:show tables;
查看指定表结构:desc student;
三、数据操作:
增:
增加数据:insert into student values(1,’张三’,’男’,20);
Insert into student(id,name) values(2,’李四’);
删:
删除数据:delete from student;
delete from student where id=1;
truncate table student; -- 不能带条件删除
改:
修改数据:update student set gender=’女’;
update student set gender=’男’ where id=1;
update student set gender=’男’,age=18 where id=2;
查:
查询所有列:
select * from student;
查询指定列:
select id,name,gender from student;
查询时添加常量列:
select id,name,gender,age,’默认值’ as ‘添加的常量列名’ from student;
查询时合并列:
select id,name,(math+chinese) as ‘总成绩’ from student;
查询时取出重复记录:
select distinct gender from student;
条件查询:
select * from student where id=2 and name=’张三’;
select * from student where math>90 or chinese<80;
select * from student where math<>70;
select * from student where math between 75 and 90; -- 包前包后;
select * from student where address is null;
select * from student where address is not null or and address<>’’;
select * from student where name like ‘张_’;--模糊查询 张X
select * from student where name like’李%’;-- 李XXXX…
聚合查询:
select sum(math) as ‘math总成绩’ from student;
select avg(math) as ‘math平均分’ from student;
select max(math) as ‘math最高分’ from student;
select min(math) as ‘math最低分’ from student;
select count(*) from student; -- 统计共有多少学生字段;
select count(id) from student; -- 推荐
分页查询:
select * from student limit 0,n; -- 第一页 每页n条
select * from student limit n,n; -- 第二页
select * from student limit 2n,n; -- 第三页
查询排序:
Select * from student order by id asc;
Select * from student order by id; --正序
Select * from student order by id desc; -- 反序
Select * from student order by math asc,chinese desc; -- 优先级排序;
分组查询:
Select gender,count(id) from student group by gender;
分组后筛选:
Select gender,count(*) from student group by gender having count(*)>2;
关联查询(多表查询)
1. 内连接查询:
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 empName,deptName
From employee e
Inner join dept d
On e.deptId=d.id;
2. 左外连接查询(左表的数据一定会完成显示)
Select d.deptName,e.empName
From dept d –deptName一定会显示
Left outer join employee e
On d.id=e.deptId;
3. 右外链接查询(右表的数据一定会完成显示)
Select d.depaName,e.empName
From dept d
Right outer join employee e -- empName一定会显示
On d.Id=e.deptId;
4. 自连接查询
Select e.empName,b.empName
From employee e
Left outer join employee b
On e.bossId=b.Id;
三、 存储过程:
存储过程—带有逻辑的sql语言 (执行效率快,但是移植性差)
1. 带有输入参数的存储过程
DELIMITER $
CREATE PROCEDURE pro_findById(IN eid INT) -- IN: 输入参数
BEGIN
SELECT * FROM employee WHERE id=eid;
END $
CALL pro_findById(4);
DROP PROCEDURE pro_findById; -- 删除存储过程。注意:后面不带括号
2. 带有输出参数的存储过程
DELIMITER $
CREATE PROCEDURE pro_testOut(OUT str VARCHAR(20)) -- OUT:输出参数
BEGIN
SET str='helljava'; -- 给参数赋值
END $
CALL pro_testOut(@NAME);
SELECT @NAME;
3. 带有输入输出参数的存储过程
DELIMITER $
CREATE PROCEDURE pro_testInOut(INOUT n INT) -- INOUT: 输入输出参数
BEGIN
-- 查看变量
SELECT n;
SET n =500;
END $
SET @n=10;
CALL pro_testInOut(@n);
SELECT @n;
4. 带有条件判断的存储过程
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(4,@str);
SELECT @str;
5. 带有循环功能的存储过程
DELIMITER $
CREATE PROCEDURE pro_testWhile(IN num INT,OUT result INT)
BEGIN
-- 定义一个局部变量
DECLARE i INT DEFAULT 1;
DECLARE vsum INT DEFAULT 0;
WHILE i<=num DO
SET vsum = vsum+i;
SET i=i+1;
END WHILE; --这儿的分号不能忘记
SET result=vsum;
END $
CALL pro_testWhile(100,@result);
SELECT @result;
6. 使用查询的结果赋值给变量(INTO)
DELIMITER $
CREATE PROCEDURE pro_findById2(IN eid INT,OUT vname VARCHAR(20) )
BEGIN
SELECT empName INTO vname FROM employee WHERE id=eid;
END $
CALL pro_findById2(1,@NAME);
SELECT @NAME;
SQL语句学习笔记
最新推荐文章于 2024-07-22 22:13:26 发布