首页 > 代码库 > MySQL常见问题和命令
MySQL常见问题和命令
问题:
1、centos MySQL启动失败:关闭selinux, vi /etc/selinux/config, 设置SELINUX=disabled,重启电脑;
命令:
停止、启动mysql服务器:/etc/init.d/mysqld {start|stop|status|restart|condrestart|try-restart|reload|force-reload}
DDL(Data Definition Languages)
一、数据库相关
创建数据库: create database test1;
查看数据库: show databases;
使用数据库: use test1;
删除数据库: drop database test1;
二、表相关
创建表: create table student(name varchar(10) primary key, birthday date, weight decimal(10,2), age int(3));
查看数据库的所有表: show tables;
查看表定义: desc student;
查看创建表的SQL语句:show create table student \G; (\G,表示分列显示)
删除表: drop table student;
修改表:
1、修改字段类型: alter table student modify column name varchar(20) first; //first表示将该列设为第一列,同样适用于其他修改表命令
2、修改字段名称: alter table student change column age age1 varchar(20); //同时也要指定字段类型
3、增加表字段: alter table student add column score int(3) after name; //after表示插入的列位于name列后,该关键字同样适用于其他修改表命令 4、删除表字段: alter table student drop column score;
5、修改表名称: alter table student rename student1;
DML(Data Manipulation Language)
插入记录: insert into student (name, birthday) values (‘pape‘, 19880522);
insert into student (name, birthday, weight, age) values (‘a‘, 19880522, 60, 22), (‘b‘, 19880523, 70, 25), (‘c‘, 19890312, 55, 22); //插入多行
更新记录: update student set weight=65, age=26 where name=‘a‘;
删除记录: delete from student where name=‘a‘;
查询记录:
1、查询不重记录: select distinct age from student;
2、条件查询: select * from student where age=23 and weight>60;
3、排序和限制: select * from student order by age desc limit 1,3;
4、聚合: select name, count(1) from student group by age;
select sum(name), max(age), min(weight) from student;
5、表连接: select name,age from student,student1 where student.number=student1.number;
6、子查询: select * from student where number in(select number from student1);
7、记录联合: select name from student union all select name from student1; //如果要去重,将union all改为union
DCL(Data Control Language)
授权: grant select,insert on test1.* to ‘pape‘@‘localhost‘ identified by ‘1234‘;
收回授权: revoke insert on test1.* from ‘pape‘@‘localhost‘;
可以通过? int; ? show;等进行帮助查询。
MySQL常见问题和命令