1.mysql的启动和关闭
启动mysql服务: # service mysql start
重启mysql服务: # service mysql restart
关闭mysql服务: # service mysql stop
连接到mysql服务器: $ mysql -uroot -proot
2.sql基础
1>DDL语句
创建数据库: mysql> create database test1;
查看数据库: mysql> show databases;
选择数据库: mysql> use test1;
查看所有表: mysql> show tables;
删除数据库: mysql> drop database test1;
创建表: mysql> create table emp(ename varchar(10),hiredate date, sal decima(10,2),deptno int(2));
查看表: mysql> desc emp;
查看创建表的sql语句: mysql> show create table emp /g;
删除表: mysql> drop table emp;
修改表的字段类型: mysql> alter table emp modify ename varchar(20);
增加表字段: mysql> alter table emp add column age int(3);
删除表字段: mysql> alter table emp drop column age;
修改表的字段改名: mysql> alter table emp change age age1 int(4);
修改字段排列顺序: mysql> alter table emp add birth date after ename;
mysql> alter table emp modify age int(3) first; 更改表名: mysql> alter table emp rename emp1; 2>DML语句 插入语句: mysql> insert into emp (ename,hiredate,sal,deptno) values ('zzx1','2000-01-01', 2000',1); mysql> insert into dept values(5,'dept5'),(6,'dept6'),(7,'dept7'); 更新记录: mysql> update emp set sal=4000 where ename='zzx1'; mysql> update emp a,dept b set a.sal=a.sal*b.id,b.name=a.ename where a.deptno=b.id; 删除记录: mysql> delete from dept where name='dept7'; 查询记录: mysql> select * from dept; 条件查询: mysql> select * from dept where name='dept7'; 排序和限制: mysql> select * from dept order by id desc limit 1; 聚合: mysql> select deptno,count(1) from emp group by deptno; 表连接: mysql> select ename,name from emp left join dept on emp.deptno=dept.id; mysql> select ename,name from emp right join dept on emp.deptno=dept.id; 子查询: mysql> select * from emp where deptno not in(select id from dept where id=1); mysql> select * from emp where deptno in(select id from dept where id=1); 联合: mysql> select deptno from emp -> union all -> select deptno from dept; 3>DCL语句 DCL语句主要是DBA用来管理系统中的对象权限时使用,一般的开发人员很少使用。 创建一个数据库用户bcw,密码为123,具有对某数据库所有表的select/insert权限: mysql> grant select ,insert on mysql.* to 'bcw'@'192.168.0.65' identified by '12 3'; 权限变更:mysql> revoke insert on mysql.* from 'bcw'@'192.168.0.65' ; 3.帮助的使用 显示所有帮助分类:mysql> ? contents 分类帮助: mysql> ? 分类名称 快速查阅帮助: mysql> ? show |