MySql学习笔记(一)

MySql学习记录

第一部分：数据库操作

/*显示数据库*/show DATABASES;/*创建数据库*/create DATABASE 数据库名/*删除数据库*/drop DATABASE 数据库名-- CTRL SHIFT + R 对选中的行执行CREATE database mytest; -- 创建数据库drop database mytest; -- 删除数据库

第二部分：数据库表操作

-- 创建表CREATE table t_BookType(id int  PRIMARY key auto_increment,booktypeName VARCHAR(20),bookTypeDesc VARCHAR(200) ) type=INNODB;-- 关联外键CREATE table t_Book(id int PRIMARY KEY auto_increment,bookName VARCHAR(20),author VARCHAR(10),price DECIMAL(6,2), bookTypeId int ,FOREIGN KEY(bookTypeId) REFERENCES t_BookType(id)) type=INNODB;-- 查看表结构DESC t_bookType-- 修改表ALTER TABLE t_book2 rename t_book -- x修改表名Alter table t_book change bookName bookName2 varchar (20); -- 修改变量名desc t_bookalTER  TABLE T_book add Publish varchar (20)  FIRST -- 增加字段（First 加到第一个）alter table T_book drop Publish -- 删除字段-- 删除表drop table t_book

第三部分：数据库查询操作

-- 单表查询-- 查询所有字段select id ,name,height,habit from t_student select * from t_student -- 两者的作用一样-- 查询指定字段select id,name from t_student-- Where 条件查询 select * from t_student where id =2 -- 查询id=2-- In 关键字查询select * from t_student where age in (20,22)-- 包含20 22的-- 带between and 的范围查询select * from t_student WHere age between 20 and 22  -- 查询范围在20 到22的-- not in 和not between 表达不包含 和不再范围之内-- LIKE 模糊查询 -- '%'表示任意字符-- ‘_’表示单个字符selECT * from T_student where name like 'dzb'selECT * from T_student where name like '%dzb' -- DZB 前面是任意字符selECT * from T_student where name like '%dzb%' -- 只要包含DZBselECT * from T_student where name like 'dzb%' -- DZB后面是任意字符-- 空值查询select * from T_student where sex is null; -- sex字段为空-- 带AND多条件查询select * from t_student where age=22 and sex='male'-- 带OR的 多条件查询select * from t_student where age=22 or sex='male'-- DISTINCT 去重复查询 select DISTINCT name from t_student-- 对查询结果排序 ORDER BYselect * from t_student ORDER BY age ASC-- 默认为升序select * from t_student ORDER BY age DESC -- 降序-- 分组查询 GROUP BY-- 单独使用没有意义-- 一般和聚合函数一起使用 COUNT-- 与having一起使用  --限制输出结果select sex ,GROUP_CONCAT(NAME) from t_student GROUP BY sex  -- 按照性别吧查询到的name分组select sex ,COUNT(NAME) from t_student GROUP BY sex -- 统计性别总人数select sex ,COUNT(NAME) from t_student GROUP BY sex having COUNT(NAME)>=2 -- 限制性别总人数大于2select sex ,COUNT(NAME) from t_student GROUP BY sex  WITH ROLLUP-- =多加一行统计性别总人数-- LIMIT分业查询select * from t_student LIMIT 0,3 -- 查询0到3条

第四部分 聚合函数查询

-- COUNT(expr)查询记录总条数-- 与group by 一起使用select COUNT(*) from t_scoreselect name, COUNT(*) from t_score GROUP BY `name`-- SUM(expr)函数-- 1.求和函数-- 2.与group by一起使用select name, SUM(score) from t_score WHERE name='张三'select name, SUM(score) from t_score GROUP BY `name`--AVG([DISTINCT] expr)求平均值select name, avg(score) from t_score GROUP BY `name`-- MAX(expr)求最大值select name,subject, max(score) from t_score WHERE name='张三'select name, max(score) from t_score GROUP BY `name`-- MIN(expr)求最小值