mysql基础 一

-- 创建数据库

-- 语法：CREATE DATABASE 数据库名;

CREATE database dbstudent;

-- 创建表

-- 语法：create table 表名(列名1 类型,列名2 类型,列名3 类型......);

create table student(id int,name1 varchar(50),pass VARCHAR(50),sex VARCHAR(50),age int,class int);

-- 查询表结构:desc 表名;

desc student;

-- 增加数据

-- 语法：insert into 表名 values(值1,值2......);

insert into student VALUES(10,'two dog','122222','man',15,5);

-- 查询数据

-- 语法：select 列名 from 表名;

select * from student;

select name1,age,class from student;

-- 修改数据

-- 语法：update 表名 set 新值 where 旧值； 新值或旧值通过条件(表达式)来找到的

update student set name1='blue flower' where id=3;

-- 删除数据

-- 语法：delete from 表名 where 条件；

delete from student where id=3;

-- 带有条件的语句 WHERE

-- 未成年的用户详细信息

select * from student where age<18;

-- 年龄小于25的妹子叫啥名字

select * from student;

SELECT * from student where age<25 and sex='woman'

SELECT name1 from student where age<25 and sex='woman'

-- 排序 order BY asc:升序（默认可以不写） desc：降序

select * from student;

select * from student ORDER BY age desc;

-- 范围值1 between

select * from student where age BETWEEN 15 and 31;

-- 范围值2 limit

select * from student;

select * from student limit 0,6;

-- 模糊查询 like

select * from student where name1 like 'm%';

select * from student where name1 like '%e';

select * from student where name1 like '%i%';

-- 聚合函数 sum() avg() count() max() min()

select count(id) from student where sex='woman';

select avg(age) from student;

select * from student;