网站首页 文章专栏 mysql基础语法

mysql基础语法

编辑时间:2018-05-25 16:03:08 作者:苹果 浏览量:427


    前言:收集整理了一些mysql的基本操作,共mysql小白参考学习


1、大小写不敏感(内容除外)

2、命令可以写在一行 也可以写在多行

3、命令以;结束


显示有哪些数据库

show databases;


创建数据库

create database school;


删除数据库

drop database school;


使用数据库

use school;


查看表

show tables;


新建表

create table student(

id int(10) not null primary key,

name varchar(20),

age int(10),

sex varchar(10) default '男',

classid int(10)

);


查看表结构

desc student;


删除表

drop table student;


删除主键

alter table student drop primary key;


增加主键

alter table student add primary key(id);


删除字段

alter table student drop classid;


增加字段

alter table student add classid int(10);


增加记录

insert into student(id,name,age,sex,classid) values(1000,'张三',20,'男',1);

insert into student(id,name) values(1001,'张三');

insert into student values(1002,'李四',21,'女',1);


删除

delete from student ;//删除全部记录

delete from student where id = 1000;

delete from student where age>20;

delete from student where age is null; //is not null

delete from student where sex = '男';


修改

update student set sex = '男' where id=1004;

update student set sex = '男',name='王五' where id=1006;

update student set age = 21 where name = 'xiao' and sex = '女';

update student set age = 22 where name = 'xiao' or sex = '女';

update student set age = 19 where age between 19 and 22;


通配符 %:多个任意的字符 _:一个字符

update student set age = 20 where name like '%i%';

update student set age = 21 where name like '_i%';



查询

select * from student;

select id,name from student;

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

排序 默认升序asc 降序desc

select * from student order by classid asc;

select * from student order by classid desc;

select * from student order by classid desc,age asc;

组函数 min max count avg

select count(*) from student;

select min(age) from student;

select avg(age) from student;

分组

select classid,avg(age) from student group by classid;

select classid,avg(age) from student group by classid having avg(age)>20;

字句作为查询的条件

select * from student where age = (select min(age) from student);



    出自:何冰华个人网站

    地址:http://www.hebinghua.com/

    转载请注明出处


来说两句吧
最新评论