SQL语言:
1.创建表
DROP TABLE if EXISTS stuents;
CREATE TABLE stuents (
id int unsigned PRIMARY key AUTHORIZATION
name VARCHAR ( 10 ),
age INT unsigned,
height DECIMAL(5,2)
)
2.删除表
DROP TABLE stuents (如果表存在则删除,如果不存在报错)
DROP TABLE if EXISTS stuents
3.增加数据
给所有的字段设置数据,字段必须一一对应(0,NUll,DEFAULT为默认数据)
INSERT into stuents VALUES (NULL,'我',20, 23.99)
INSERT into stuents VALUES (0,'我',20, 23.99)
INSERT into stuents VALUES (DEFAULT,'我',20, 23.99)
给指定的字段设置数据
INSERT into stuents (name) VALUES('我')
INSERT into stuents (name,age) VALUES('我',10)
插入多条数据
写多条插入语句
INSERT into stuents VALUES
(0,'我',20, 23.99),
(2,'我',20, 23.99),
(3,'我',20, 23.99),
(4,'我',20, 23.99)
INSERT into stuents (name) VALUES('我');
INSERT into stuents (name) VALUES('我');
INSERT into stuents (name) VALUES('我')
INSERT into stuents (name) VALUES('我'),('你')('他')
4.删除数据
DELETE FROM students
DELETE FROM students WHERE id=20
逻辑删除:设置一个字段表示删除或者非删除
1.设计表,给表添加一个字段isdelete,1代表删除,0没有删除
2.把所有的数据isdelete=0
3.要删除某一条数据时,更新它的isdelete为1
4.当要查询数据时,只查询isdelete为0的数据
update studetns set isdelete = 0
UPDATE students set isdelete=1 WHERE id=10
SELECT * from students isdelete=0
5.修改数据
UPDATE students set age=20;
UPDATE students set name='狄仁杰',age=20 WHERE id=20
6.添加一个字段
alert table student isdelete int
7.查询数据
7.1查询基本用法
查询
select * from students
select name,age from students
给字段起别名
select name as 姓名,age as 年龄 from students
给表起别名
select a.name,a.age from students as a
查询学生的性别有哪几种,去重,针对一类
select DISTINCT sex from students
去重
select distinct sex,class from students
消除重复行
select distinct * from students
7.2 比较运算符
查询小乔的年龄
select age from students where name='小乔'
查询20岁以下的学生
select * from students where age<20
查询北京不等于北京
select *from students where hometown!='北京'
7.3逻辑运算
and:
select * from students where age<20 and sex='女'
or:
select * from students where class='1班' or sex='女'
not:
select * from students where not hometown='天津'
7.4模糊查询
任意字符
SELECT * FROM students WHERE name like '孙%'
任意一个字符
SELECT * FROM students WHERE name like '孙_'
查询以乔结尾的学生
SELECT * FROM students WHERE name like'%乔'
查询姓名含白的学生
SELECT * FROM students WHERE name like'%白%'
7.5范围查询
家乡是北京上海广东的学生
SELECT * FROM students WHERE hometown in('北京','上海','广东')
年龄是18到20的学生,小值在前
SELECT * FROM students WHERE age>=18 and age<=20
SELECT * FROM students WHERE age BETWEEN 18 and 20
7.6空判断
查询没有填写身份证的学生
SELECT * FROM students WHERE card is null
查询没有填写身份证为空字符串的学生
SELECT * FROM students WHERE card=''
插入学习默认名字为NULL
insert into students(studentno,name) VALUES(20,NULL)
7.7排序
查询所有学生的信息,按年龄从小到达排
SELECT * from students ORDER BY age (desc 从大到小)
查询所有学生的信息,按年龄从小到达排,年龄相同时,学号按从小到大排
SELECT * from students ORDER BY age,studentNo
按姓名中文排序
SELECT * from students ORDER BY CONVERT(name using
gbk)
7.8 聚合函数
SELECT COUNT(*)from students
SELECT COUNT(name) from students
求女生中的最大值
SELECT MAX(age) FROM sutdents WHERE sex='女'
求女生中的最小值
SELECT min(age) FROM sutdents WHERE sex='女'
求和
SELECT SUM(age) from students WHERE hometown='北京'
求平均年龄
SELECT avg(age) from students WHERE sex='女'
7.9分组
SELECT sex,count(*) from students GROUP BY sex
SELECT age,count(*) from students GROUP BY age
7.10分组后过滤
分组后过滤
SELECT class,AVG(age),max(age),min(age) from studetns GROUP BY class
每班男女个数
SELECT class,sex,COUNT(*) from students GROUP BY class,sex
查询男生总人数
SELECT * FROM students WHERE sex = '男'
SELECT sex,COUNT(*) from students GROUP BY sex HAVING sex='男'
查询1班除外的其他班级的平均年龄,最大年龄,最小年龄
SELECT class,AVG(age),max(age),MIN(age),COUNT(*) from students WHERE class!='1班'
GROUP BY class
SELECT class,AVG(age),max(age),MIN(age),COUNT(*) from students GROUP BY class HAVING
class!='1班'
7.11分页查询
分页查询
查询前3行学生信息
select * from student limit 0,3
7.12连接查询(多表查询)-等值查询
查询学生信息及学生成绩
select * from students as stu,scores as sco where stu.studentNo=sco.studentNo
7.13 连接查询-内连接
连接查询-内连接
select * from students INNER JOIN scores on students.studentno=scores.studentno
7.14 去重查询
去重查询
select distinct xing, ming from B
7.15 case函数
--简单case函数
case sex
when '1' then '男'
when '2' then '女’
else '其他' end
--case搜索函数
case when sex = '1' then '男'
when sex = '2' then '女'
else '其他' end
