Mysql常用sql语句

INSERT INTO teacher (TId,Tname) VALUES("01","王五");//插入数据
DELETE FROM teacher WHERE TId='02';//删除数据
UPDATE teacher SET Tname='wxm' WHERE TId='03';//更新字段值
SELECT * FROM student WHERE Sname LIKE '%李%';//模糊查询
SELECT * FROM  student ORDER BY SId DESC,Sage DESC;//排序倒序,Sid相同 按Sage倒序排序
SELECT * FROM  student ORDER BY SId;//排序：正序
SELECT * FROM  student ORDER BY SId ASC;//排序：正序
SELECT count(SId) as total FROM student;//某字段的总条数
SELECT count(*) FROM student;//总条数
SELECT avg(SId) FROM student;//平均值
SELECT SUM(SId) FROM student;//求和
SELECT MAX(SId) FROM student;//最大值
SELECT MIN(SId) FROM student;//最小值
SELECT DISTINCT Sname FROM student;//字段值去重
SELECT * FROM student WHERE SId in ('04','05');//查询id在的所有值
SELECT * FROM student LIMIT 0,1;//从第一条数据开始算，取1条数据
SELECT * FROM student HAVING count(*)>2;
//当我们用到聚合函数，如sum，count后，又需要筛选条件时，having就派上用场了，因为WHERE是在聚合前筛选记录的，having和group by是组合着用的
select cid,count(id) nums from xzyd_question group by cid HAVING nums>2
CREATE INDEX indexname on student(SId(10));//创建索引
ALTER TABLE student ADD INDEX index_sid(SId);//创建索引
ALTER TABLE student ADD Ceo VARCHAR(10);//增加Ceo字段
ALTER TABLE student1 RENAME student; //修改表名
ALTER TABLE student CHANGE Sname Snames VARCHAR(10);//修改字段名
ALTER TABLE student DROP Ceo;//删除字段
ALTER TABLE student ADD Ceo VARCHAR(10) AFTER SId;//添加字段到指定位置
ALTER TABLE student ADD Css VARCHAR(10) FIRST;//添加字段为第一列
//创建表
CREATE TABLE `t_dept` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `deptName` VARCHAR(30) DEFAULT NULL,
 `address` VARCHAR(40) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

//查看所有的数据库
SHOW DATABASES;

//创建一个数据库
CREATE DATABASE if NOT EXISTS wxm;  
//删除一个数据库  
DROP DATABASE if EXISTS wxm;
//选择数据库
use dingding;
//在选中的数据库之中查看所有的表 
SHOW TABLES;

//显示表结构  以下三个语句都可以实现
DESCRIBE sys_log;
SHOW COLUMNS FROM sys_log;
DESC sys_log;

//查看创建数据库的语句
SHOW CREATE DATABASE dingding; 
//查看数据表的定义语句   
SHOW CREATE TABLE sys_log;