登录

  1. mysql -u root -p

数据库命令

  1. SHOW databases;
  2. USE databasename;
  3. CREATE database name;
  4. SHOW CREATE database name\g 
  5. SHOW engines;
  6. USE databasename;
  7. SHOW tables;
  8. DESC tablename; 查看表详情

表编辑

  1. ALTER b_emp7 RENAME tb_emp7; 修改表名
  2. ALTER TABLE MODIFY name varchar(60); 修改字段类型
  3. ALTER TABLE tb_dept change name name_1 text; 修改字段名称和类型
  4. ALTER TABLE tb_emp1 ADD ip varchar(11) PRIMARY KEY;增加字段
  5. ALTER TABLE tb_emp1 ADD ip1 varchar(11) FIRST;增加字段在开始位置
  6. ALTER TABLE tb_emp1 ADD ip0 varchar(11) AFTER ip; 在字段后添加，没有指定则默认添加到最后
  7. ALTER TABLE tb_emp1 DROP ip;删除字段
  8. ALTER TABLE tb_emp1 MODIFY id int FIRST;修改字段到第一个位置
  9. ALTER TABLE tb_emp1 MODIFY id int AFTER name;修改字段到字段之后位置
  10. ALTER TABLE tb_emp1 engine=myisam;修改数据库引擎
  11. ALTER TABLE tb_emp5 DROP FOREIGN KEY fk_emp_dept1;删除外键约束
  12. DROP TABLE tb_emp1,tb_emp00;删除表
  13. DROP TABLE IF EXISTS tb_emp1,tb_emp00;删除表，删除前判断是否存在

查询

  1. SELECT * FROM employee;
  2. SELECT name,age FROM employee;
  3. SELECT count(salary) as number, avg(salary), max(salary) as max_salary  FROM employee;
  4. SELECT name,age FROM employee WHERE age>25;
  5. SELECT name,age,phone FROM employee WHERE name='Mary'; 
  6. SELECT name,age FROM employee WHERE age<30 OR age>40;
  7. SELECT name,age,phone,in_dpt  FROM employee  WHERE in_dpt IN ('dpt3','dpt4');
  8. SELECT name,age,phone FROM employee WHERE phone LIKE '1101__';
  9. SELECT in_dpt, count(id), sum(salary), max(salary) FROM employee GROUP BY in_dpt;
  11. SELECT in_dpt, count(id), sum(salary), max(salary) FROM employee
  12.     GROUP BY in_dpt HAVING count(id) > 2;
  14. SELECT * FROM employee ORDER BY salary desc;
  16. SELECT COUNT(proj_name)  FROM project 
  17.     WHERE of_dpt IN (SELECT in_dpt FROM employee WHERE name='Tom');
  19. SELECT employee.id, employee.name, department.people_num FROM employee  
  20.     JOIN department 
  21.   ON employee.in_dpt = department.dpt_name
  22.   ORDER BY id;

更新

  1. UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing'
  2. WHERE LastName = 'Wilson'

常规语法

  1. SELECT (查询内容) 
  2. FROM <left TABLE> （查询主表） 
  3. <join type> JOIN <right TABLE> （关联的表） 
  4. ON <join condition> （两表关联条件） 
  5. WHERE （过滤查询结果的条件） 
  6. GROUP BY （分组条件） 
  7. HAVING （过滤分组的条件） 
  8. ORDER BY （排序条件）