sql介绍
结构化查询语句,是一种特殊目的的编程语言。
Sql分类
- DDL数据库定义语言,针对库,表进行创建,修改,删除
- DML数据库操纵语言,针对表数据进行增删改查操作
- DQL数据库查询语言,针对数据进行查询操作
- DCL数据库权限控制语言
- DTL数据库事务语言
基本操作(除查询)
- 查看数据库
show databases;
- 创建数据库
create database cz_school character set utf8;
- 使用数据库
use cz_school;
- 删除数据库
drop database cz_school;
- 创建表
create table student(
sid int(11) primary key auto_increment,
sname varchar(20) not null,
sex int(1) not null,
age int(3) not null
);
- 展示数据表
desc student;
- 变换表名
alter table student rename students;
- 在表中增加一列“地址”
alter table students add address varchar(100);
- 改变表中一列的属性
alter table students change address address varchar(200);
- 删除表中一项
alter table students drop address;
- 删除表
drop table students;
- 增加数据
insert into student(sname,sex,age)values("詹闲",0,19);
- 增加多个数据(逗号分隔)
insert into student(sname,sex,age)values("呵呵",0,19),("哈哈",0,19);
- 修改数据
update student set age=15 where sname='呵呵';
- 修改多个数据
update student set age=17,sex=1 where sname='呵呵';
- 删除数据
delete from student where sname='呵呵';
数据库数据查询
1. 基础查询:
查询学生表中全部信息select * from student ;
查询学生表中姓名,年龄的信息select sname,age from student;
查询学生表中姓名的信息,并取别名select sname as 学生姓名 from student;
取别名select sname 学生姓名 from student;
2. 条件查询:
运算符:
(2)等于= select * from student where sid=6;
(3)不等于<>,!= select *from student where sid!=6;
(4)小于< select *from student where age<20;
(5)小于等于<= select *from student where age<=20;
(6)大于> select *from student where age>18;
(7)大于等于>= select *from student where age>=18;
(8)两值之间 between… and…
select *from student where age>=18 and age<=20;
select * from student where age between 18 and 20;
(9)查空is null
select * FROM student WHERE age IS NULL;
(10)查非空is not null
select * FROM student WHERE age IS NOT NULL;
(11)并且and ,&&
select * FROM student WHERE age=19 AND sex=0;
(12)或者or,||
select * FROM student WHERE age=19 or age=20;
(13)包含in
select * FROM student WHERE age IN(19,18,20);
(14)不包含 not in
select * FROM student WHERE age NOT IN(19,18,20);
(15)模糊查询 l使用like;通配符_,%
查询姓哈的学生:
select * FROM student WHERE sname like '哈_';
(_只有一个字符)
select * FROM student WHERE sname like '哈%';
查询名字都有得的学生
select * FROM student WHERE sname like '%得%';
3. 排序
查询所有学生信息,按学生年龄升序排列
select * FROM student ORDER BY age;
(默认升序)
select * FROM student ORDER BY age ASC;
查询所有学生信息,按学生年龄降序排列
select * FROM student ORDER BY age DESC;
4. 聚合函数
max() select MAX(age) AS 最大年龄 FROM student ;
min() select MIN(age) AS 最小年龄 FROM student ;
avg() select avg(age) AS 平均年龄 FROM student ;
sum() select sum(age) AS 总年龄 FROM student ;
count() select count(*) AS 总人数 FROM student ;
5. 分组(筛选)
查询当前学生表都有哪些年龄的学生
select * FROM student GROUP BY age;
查询当前学生表除19岁都有哪些年龄的学生
select * FROM student GROUP BY age HAVING age!=19;
6. 分页
查询当前学生中年龄最大的前三个人的信息(LIMIT 后面只有一个数据,则表示取几条)
select * FROM student ORDER BY age DESC LIMIT 3;
查询第二页学生的信息(每页3条数据)(LIMIT 后面有两个数据,则前面一个是索引,后面是几条数据)
select * FROM student LIMIT 3,3;
7. 子查询(嵌套查询)
查询年龄比哈哈大的学生select *FROM student WHERE
age>(select age FROM student WHERE sname='哈哈');
8.多表查询
表关系:一对一,一对多,多对一,多对多
(添加一个教师表)
CREATE TABLE class(
cid INT(11) PRIMARY KEY auto_increment,
cname VARCHAR (20) NOT NULL
);
外键
(在学生表中加如教师表的主键)ALTER table student add cid INT(11);
(设置外键)ALTER table student add CONSTRAINT fk_student_class FOREIGN KEY (cid) REFERENCES class(cid);
内连接
查询所有学生的名字和他们班级的名称
方法1:SELECT sname,cname FROM student INNER JOIN class ON student.cid=class.cid
;
方法2:SELECT sname,cname FROM student,class WHERE student.cid=class.cid;
外连接:
(1)左外连接
SELECT sname,cname FROM student LEFT JOIN class ON student.cid=class.cid;
(2)右外连接
SELECT sname,cname FROM class RIGHT JOIN student ON student.cid=class.cid;