常用sql语句

 

查看数据库列表：show databases;
选中要使用的数据库：use 数据库名;
查看数据库中表信息：show tables;
查看表结构：desc 表名; 或者 show columns from 表名;
查看系统配置参数 SHOW VARIABLES LIKE '***'

1.创建table

DROP TABLE IF EXISTS student;
CREATE TABLE student
(
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(48) NOT NULL,
`age` BIGINT NULL DEFAULT 1,
PRIMARY KEY(`id` ,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.修改table

添加字段  ALTER TABLE student ADD `tearcherId` INT UNSIGNED NOT NULL;  

修改字段  ALTER TABLE student MODIFY `tearcherId` INT UNSIGNED NULL;  

删除字段  ALTER TABLE student DROP `tearcherId` ;  

添加外键约束  ALTER TABLE `student` ADD CONSTRAINT `class_fk` FOREIGN KEY (`class_id`) REFERENCES class_info(`id`); 

3.删除表

清空表内容  TRUNCATE TABLE student; 

删除表  DROP TABLE student ; 

4.数据操作 

插入数据  INSERT INTO student (`name`,`age`) VALUES( 'zhangsan',25); 

更新数据  

UPDATE student SET company='电信' WHERE id IN (1,3,5);

删除数据  DELETE FROM student WHERE id =10 AND age>100; 

查询数据 ：在学生表中，按包含学生数量来划分公司并倒序排列，最后返回包含学生数目第二多的公司名称机器包含的学生数量

SELECT COUNT(*) AS count, company FROM student GROUP BY company HAVING count>=2 ORDER BY count DESC LIMIT 1,1;  

 

5.查询性能提升

通常查询语句中各关键字执行顺序为   select--from--where--group by--having--order by -- limit offert

a.小表优先能极大减少比对次数 效率更好。例子：a表1条记录  b表10000条记录   连接条件是a.id = b.id，

 

 6.索引操作

6.1普通索引操作

普通索引创建： CREATE INDEX company_index ON student(company(10));    

 

注意，这里的长度10是索引长度。在MyISAM表中，创建组合索引时，创建的索引长度不能超过1000。

对于CHAR和VARCHAR列，只用一列的一部分就可创建索引。创建索引时，使用col_name(length)语法，对前缀编制索引。前缀包括每列值的前length个字符。BLOB和TEXT列也可以编制索引，但是必须给出前缀长度。

　　

普通索引添加： ALTER table tableName ADD INDEX indexName(columnName) 

普通索引删除： DROP INDEX [indexName] ON mytable;  

 

6.2唯一索引操作

唯一索引创建： CREATE UNIQUE INDEX indexName ON mytable(username(length))  

唯一索引添加：

ALTER table mytable ADD UNIQUE [indexName] (username(length))

6.3 主键索引和全文索引

操作与普通索引类似，区别就是关键字不同。INDEX， UNIQUE，  PRIMARY KEy ，FULLTEXT

 

7.存储过程

创建：

 CREATE PROCEDURE `get_studenet_by_age`(IN `age` int)
 BEGIN
     #Routine body goes here...
  declare bottom_range INTEGER(12);  
  SET bottom_range=5;
  SELECT stud.id,stud.`name` from student as stud WHERE stud.age>age-bottom_range;
 END;

调用： CALL get_studenet_by_age(22) 

8.自定义函数

 创建 ：

CREATE DEFINER = `root`@`localhost` FUNCTION `NewProc`(`age` int)
 RETURNS varchar(11)
begin
       return 'SELECT stud.`name` from student as stud WHERE stud.age>age';
end;

调用： SELECT * FROM student WHERE `name`=get_name_by_age(20); 

9.触发器:

包括INSERT DELETE UPDATE和定时触发器 

创建语法如下：

create trigger 名称 after|before 事件 insert|update|delete  on 表名  for each row

 

两个关键字: new, old  在触发器中,当触发事件发生时,

new : 代表新行, old: 代表旧行

insert 操作: 成功之后,其后会自动产生一行新的数据. 在触发器中通过new获取新行字段值

delete 操作, 其成功之后, 对旧行数据进行删除,在触发器中可以通过old获取旧行字段值

update操作: 其会对原有的旧行,更新为新行. 既有新行又有旧行

 

 创建INSERT 触发器：

CREATE TRIGGER trigger_student_count_insert
AFTER INSERT
ON student FOR EACH ROW
UPDATE class_info SET student_count =student_count+1;

 

 创建DELETE 触发器：

CREATE TRIGGER trigger_student_count_delete
AFTER DELETE
ON student FOR EACH ROW
UPDATE class_info SET student_count=student_count-1;

 

 创建UPDATE触发器：

create trigger tg3 after update on tb_order for each row
begin
    update tb_goods  set inv = inv + old.num - new.num where  id = old.pid;
end;

 

10.事件调度器

事件调度器有时也可称为临时触发器(temporal triggers)，是基于特定时间周期触发来执行某些任务

创建

CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
DO CALL get_studenet_by_age(25);

 

11.视图

视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。修改view中的数据，同样会在table中生效
优点：视图能简化用户操作  2.视图使用户能以多种角度看待同一数据  3.视图能够对机密数据提供安全保护 

缺点：不支持触发器

创建 ： CREATE VIEW V_VIEW1(ID, NAME, SEX, AGE,DEPARTMENT) AS SELECT ID, NAME, SEX, AGE,DEPARTMENT FROM learning.t_employee; 

使用： SELECT * FROM V_VIEW1