（十四）mySql

1   基本语句

      show databases

      create database day14;

     create database day15 default character set gbk;

     alter database day15 default character set utf8;

     drop database day15;

     create table student(id int,name varchar(20),age int); 

    alter table student add column gender varchar(1);

    alter table student modify column gender varchar(2);

   alter table student change column gender sex varchar(2);

   alter table student drop column age;

  alter table student rename to teacher;

  desc student；

  show tables

  drop table teacher;

  INSERT INTO student VALUES(1,'张三',20);

  INSERT INTO student(id,NAME) VALUES(2,'李四');

  UPDATE student SET NAME='王五',age=40 WHERE id=1;

  DELETE FROM student;

  TRUNCATE TABLE student;

 注意：delete from 和 truncate table 的区别？

--  1）delete from删除全部，也可以按条件删除，但是truncate table只能全表删除，不能按条件删除

--  2）delete from删除的数据可以回滚，truncate table删除的数据不能回滚。

--  3）delete from不可以把自增长约束(auto_increment)重置，truncate table可以把自增长约束(auto_increment)重置

条件查询

  DELETE FROM student WHERE id=2;

  SELECT id AS '编号',NAME AS '姓名','特长班' AS '班级' FROM student;

 SELECT NAME AS '姓名',(servlet+mysql) AS '总成绩' FROM student;

 SELECT DISTINCT address FROM student;

SELECT * FROM student WHERE servlet>=80 AND servlet<=85;

SELECT * FROM student WHERE servlet BETWEEN 80 AND 85;

SELECT * FROM student WHERE age<>30;

SELECT * FROM student WHERE NAME LIKE '%四%';

SELECT * FROM student WHERE NAME LIKE '李_';

 聚合查询 

SELECT SUM(servlet) FROM student;

SELECT AVG(servlet) FROM student;

SELECT MAX(servlet) FROM student;

SELECT MIN(servlet) FROM student;

SELECT COUNT(*) FROM student;    

SELECT COUNT(id) FROM student; -- 效率会比count(*)效率高，且准确

分页查询

 

SELECT * FROM student LIMIT 0,2

分组查询

SELECT gender,COUNT(*) '人数' FROM student GROUP BY gender;

SELECT gender,COUNT(*) '人数' FROM student GROUP BY gender HAVING COUNT(*)>2;

 查询后排序

SELECT * FROM student ORDER BY id ASC；

SELECT * FROM student ORDER BY id DESC，age ASC；

char（10） 固定长度，一定占用10个长度

varchar（10） 最多10个长度，用多少，占多少。

int（4） 固定长度，一定占用4个长度

 int     最多十一个长度，用多少，占多少

date  日期

datetime  日期+时间

datestamp  最后修改时间

触发器

CREATE TRIGGER tri_empAdd AFTER INSERT ON employee FOR EACH ROW  

CREATE TRIGGER tri_empUpd AFTER UPDATE ON employee FOR EACH ROW  

CREATE TRIGGER tri_empDel AFTER DELETE ON employee FOR EACH ROW

mysql权限问题

root ：拥有所有权限

 权限账户：只拥有部分权限（CURD）例如，只能操作某个数据库的某张表

SELECT PASSWORD('root'); -- *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B

USE mysql;

SELECT * FROM USER;

-- 修改密码

UPDATE USER SET PASSWORD=PASSWORD('123456') WHERE USER='root';

分配权限：

GRANT SELECT ON day16.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';

GRANT DELETE ON day16.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';

备份和恢复