mysql的使用.doc

2010年7月23日 星期五SQL学习都有哪些数据库：1.SQL Server （2k- 2005）微软公司 2.MySQL 3.Oracle 4.DB2 IBM（）公司 5.Infomix 6.vFox 7.SysbaseDDL（数据库操作语言）对 库和表操作数据库的一些命令使用：1 login2 show databases;（查看所有数据库）3 use mysql;（进入指定数据库）4 show tables;（查看数据库中得表）5 select user,host,password from user;（从指定表中查询指定字段）6 create daatabase persistdb;（创建数据库）7 use persistdb;（进入刚创建得数据库）8 create table student( （创建表） - id int(11), - varchar(32) - );9 show tables;（查询所有表）10 desc student;（）11 insert into student values(1,gefer);（向指定表中插入数据）12 insert into student values(2,xmyy);（向指定表中插入数据）13 select *from student;（查询指定表内容）14 grant all privileges on persistdb.* to myylocalhost identified by myy; (授权给myy管理数据库persistdb得权限)15 mysqldump -u root -p persistdb persistdb_20100723.sql;(备份SQL只有root有权限备份)16 drop database persistdb;(删除数据库persistdb)17 update student set name = 高 where id = 1;18 delete from student where name like %yy%;mysql alter table user add column gender enum(M,F) after name;Query OK, 3 rows affected (0.69 sec)Records: 3 Duplicates: 0 Warnings: 0mysql alter table user change name ename varchar(16);Query OK, 3 rows affected (0.23 sec)Records: 3 Duplicates: 0 Warnings: 0mysql alter table user modify salary decimal(10);Query OK, 3 rows affected (0.27 sec)Records: 3 Duplicates: 0 Warnings: 01.select * from dept;(查询表dept所有内容)2.select deptname from dept;（查询表内指定内容）3.select distinct age from user;（查询结果过滤，重复得不要）4.select * from user where gender = m;(按条件查询)5.select * from user where gender and salary = 2000;（2个条件）6.select ename,hiredate from user where 1=1 order by hiredate;(查询并排序)7.select ename,hiredate from user where 1=1 and hiredate=2009-01-01and hiredate2010-01-01order by hiredate;8.select ename,hiredate from user where 1=1 and hiredate between 2009-01-01 and 2010-01-01 order by hiredate;9.select ename,salary from user where 1=1 and salary !=2000;(工资不等于2000得人)10.select ename,salary from user order by salary DESC;(降序排列，如果没有DESC就是升序)11.select sum(salary),deptno from user group by deptno;12select