1.对数据库的CRUD操作
创建数据库:create database student; 创建名为student的数据库。
查看数据库:show databases; 查看所有数据库。
删除数据库:drop database student; 删除名为student的数据库。
切换数据库:use database student; 进入名为student的数据库。
2.对数据库表的CRUD操作
创建学生表:
create table student(
id int not null PRIMARY KEY unique auto_increment,
name varchar(40),
sex varchar(40),
age int
)
包括三种约束:非空约束、主键约束和唯一性约束。
查看指定表结构:desc student;
删除指定表:drop student;
查看所有数据库中所有表:show tables;
3.对表中记录的CRUD操作
向表中插入数据:
insert into student values(1,'Tom','man',20);
查看表中所有记录:
select * from student;
修改表中记录:
update student set age=21 where id=1;
删除表中记录:
delete from student where age=21;
删除表中所有记录:
delete from student;
条件查找:
设置别名:select id as ID,name as NAME,sex as SEX,age as AGE from student;(别名操作不影响数据库)
年龄加1:select id,name,sex,age+1 from student;
查询年龄为21岁的学生姓名:select name from student where age=21;
查询年龄大于21岁的学生信息:select * from student where age>21;
查询年龄为21,22,23的学生信息:select * from student where age in(21,22,23);查询属性等于括号中的值的记录
查询姓名中包含‘T’的学生信息:select * from student where name like ‘%T%’;
查询年龄大于20岁的男性学生信息:select * from student where sex='man' and age>20;
查询年龄大于25或者id小于3的学生信息;select * from student where age>25 or id<3;
查询所有学生信息按照年龄升序排序:select * from student ORDER BY age ASC; (ASC升序,DESC降序)
查询student表中的记录数:SELECT count(*) from student;
查询student表中年龄大于23岁的记录数:SELECT count(*) from student where age>23;
查询student表中学生年龄和:SELECT sum(age) from student;
查询student表中学生平均年龄:SELECT sum(age)/count(*) from student;或者SELECT avg(age) from student;
查询student表中学生最大年龄和最小年龄:SELECT max(age),min(age) from student;
查询student表中男性学生与女性学生的人数:SELECT sex,count(sex) from student GROUP BY sex;
查询student表中男性学生人数:SELECT sex,count(sex) from student GROUP BY sex having sex='man';
4.sql语句里面关键字顺序
(insert/select/update/delete/drop)->from->where->group by-> having->order by...
5.mysql中的limit关键字:
查询student表中前2条数据:select * from sudent limit 2;
查询student表中第1条记录后的2条记录:select * from sudent limit 1,2;