MySQL常用语句

来源：互联网发布：数控铣床编程简单图案编辑：程序博客网时间：2024/06/06 06:50

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";

0 0