mysql 语句大全

数据  库操作

create database emin; create database if not exists emin；

drop database emin;  drop database if   exists emin；

use emin； show databases；

表操作

 create table students(id int(40),name char(30), age char(20));

show tables；查询库下所有表   drop table students;删除表格

describe students; explain students 查询表结构 

explain emin.students 不在当前数据库下

show create table students \G 显示创建表的过程

MariaDB [worker]> show create table students \G

*************************** 1. row ***************************
Table: students
Create Table: CREATE TABLE `students` (
`id` int(40) DEFAULT NULL,
`name` char(30) DEFAULT NULL,
`age` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)MariaDB [worker]>

修改表名alter table students rename student;  修改字段类型  alter table student modify id int(20);  

alter table student change age ages varchar(18); change 可以修改字段名称跟类型

 alter table student add sex enum('M','W') first; 增加字段  加一个first 就排第一，after age 就拍在改字段后面 

alter table student drop sex;  删除表字段

 记录操作

select * from student order by id asc ; desc升序；降序

insert into student values(1,"mingwang",35);  插入一条记录；insert into student values(1,"mingwang",35),(2,"test",38); 插入多条记录

 insert into student (id,name) values(3,"wang"),(5,"test"); 指定插入某些字段 的值

delete from student where id=5; 删除某条记录  delete from student where ages is NULL; 删除空记录

 update student set id=1 where name = "test";  修改某条记录     select distinct * from student  去重复的操作；    where 后面  or  and   binary区分大小写