MySQL常用语句

create table t_course(id int primary key auto_increment,//设置主键，自增长
                      course_name varchar(20) unique ,//唯一约束
                      stu_id int,
                      constraint d_fk foreign key(stu_id)//外键
                              references t_stu(stu_id),
                       index(id)//普通索引，
                    );
SELECT * FROM t_course t;

create unique index course_index on t_course(course_name asc);//创建唯一索引，在表，字段

drop index course_index on t_course;//删除索引名在表

alter table t_course add index stu_id_index(stu_id);//增加索引

create trigger trigger_name before insert on t_course for each row insert into t_stu values(2,'aa',1,now());//创建触发器名字在插入之前对表每行+语句

SHOW ENGINE INNODB STATUS;

select t_c.name,t_c.id from t_c,t_stu where  t_c.stu_id = t_stu.stu_id;//内连接查询

select t_c.id, t_c.name, t_stu.stu_id from t_c left join t_stu on t_c.stu_id = t_c.stu_id where t_c.name = '数学';//左连接查询


select * from t_c where stu_id  in (select t_stu.stu_id from t_stu);//字段in存在

select * from t_c where exists (select * from t_stu where stu_id  = 3);//后面条件成立

select * from t_c where stu_id >=any (select stu_id from t_stu where stu_id >2);//大于任何一个

select * from t_c where stu_id >all (select stu_id from t_stu where stu_id >2);//大于所有的

select *from t_c union all select * from t_stu;//联合所有，不去重，只联合，去重

insert into t_c values(5,'dd',5,2);//插入

update t_c set name='aaa' where stu_id=3;//修改

delete from t_c where stu_id = 5;//删除

alter table t_c drop xuefen;//删除字段

alter table t_c modify course_name varchar(30);//修改字段类型

alter table t_C change name name varchar(64);//修改字段名及新类型

alter table t_c add xuefen int;//增加新字段

alter table t_c add c_id int  first;//在最前增加

alter table t_c add xueyuan varchar(20) after xuefen;//在字段后增加

alter table t_c drop c_id;

alter table t_c engine = innodb;//修改表的存储引擎

alter table t_c drop foreign key d_fk;//删除外键

drop table t_course;//删除表

grant select,insert,update,delete on redmine1.* to jira@"%" identified by "jira";

 

新增超级权限并允许远程访问：

 GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;   

FLUSH   PRIVILEGES; 

 

授权命令GRANT 语句的语法如下：
GRANT privileges (columns)
ON what
TO user IDENTIFIEDBY "password"
WITH GRANT OPTION


例1:
增加一个用户test1密码为abc，让他可以在任何主机上登录，并对所有数据库有查询、插入、修改、删除的权限。
grant select,insert,update,delete on *.* to test1@"% " Identified by "abc";

ON 子句中*.* 说明符的意思是“所有数据库，所有的表”

例2:
增加一个用户test2密码为abc, 让他只可以在localhost上登录，并可以对数据库mydb进行查询、插入、修改、删除的操作。
grant select,insert,update,delete on mydb.* to test2@localhost identified by "abc";