mysql学习笔记
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
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