mysql学习之数据库和表操作(一)

mysql是非关系型数据库管理系统，支持集群。

一、mysql下载与安装

1.mysql官网下载
2.安装设置为utf8编码
3.下载安装sqlyog/navicat图形化工具

二、mysql数据类型

1.数值类型

2.日期类型

3.字符串类型

4.二进制类型

三、数据库操作

1.显示数据库

show databases;

2.创建数据库

create database 数据库命

3.删除数据库

drop database 数据库名

四、数据库表的基本操作

1.创建表

表是数据库存储数据的基本单位。个一个表包含若干字段或记录；

语法： CREATETABLE 表名( 
    属性名 数据类型 [完整性约束条件], 
    属性名 数据类型 [完整性约束条件], 
    . . 
    属性名 数据表格 [完整性约束条件] 
);

CREATE TABLE t_bookType(
    id int primary key auto_increment,
    bookTypeName varchar(20),
    bookTypeDesc varchar(200)
);


CREATE TABLE t_book(
    id int primary key auto_increment,
    bookName varchar(20),
    author varchar(10),
    price decimal(6,2),
    bookTypeId int,
    constraint `fk` foreign key (`bookTypeId`) references `t_bookType`(`id`)
);

2.查看表结构

查看基本表结构： DESCRIBE(DESC) 表名； 
查看表详细结构： SHOWCREATETABLE 表名；

desc t_bookType;
show create table t_bookType;

3.修改表

修改表名
alter table t_book rename t_book2;
修改字段名
alter table t_book change bookName bookName2 varchar(20);
添加字段
alter table t_book add testField int first ;
删除字段
alter table t_book drop testField;

4.删除表

drop table t_bookType;
drop table t_book;

五、查询数据

1.单表查询

SELECT id,stuName,age,sex,gradeName FROM t_student ;

SELECT stuName,id,age,sex,gradeName FROM t_student ;

SELECT * FROM t_student;

SELECT stuName,gradeName FROM t_student;

SELECT * FROM t_student WHERE id=1;

SELECT * FROM t_student WHERE age>22;

SELECT * FROM t_student WHERE age IN (21,23);
SELECT * FROM t_student WHERE age NOT IN (21,23);

SELECT * FROM t_student WHERE age BETWEEN 21 AND 24;
SELECT * FROM t_student WHERE age NOT BETWEEN 21 AND 24;

SELECT * FROM t_student WHERE stuName LIKE '张三';
SELECT * FROM t_student WHERE stuName LIKE '张三%';
SELECT * FROM t_student WHERE stuName LIKE '张三__';
SELECT * FROM t_student WHERE stuName LIKE '%张三%';

SELECT * FROM t_student WHERE sex IS NULL;
SELECT * FROM t_student WHERE sex IS NOT NULL;

SELECT * FROM t_student WHERE gradeName='一年级' AND age=23
SELECT * FROM t_student WHERE gradeName='一年级' OR age=23

SELECT DISTINCT gradeName FROM t_student;

SELECT * FROM t_student ORDER BY age ASC;
SELECT * FROM t_student ORDER BY age DESC;

SELECT * FROM t_student GROUP BY gradeName;

SELECT gradeName,GROUP_CONCAT(stuName) FROM t_student GROUP BY gradeName;

SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName;

SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName HAVING COUNT(stuName)>3;

SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName WITH ROLLUP;
SELECT gradeName,GROUP_CONCAT(stuName) FROM t_student GROUP BY gradeName WITH ROLLUP;

SELECT * FROM t_student LIMIT 0,5;
SELECT * FROM t_student LIMIT 5,5;
SELECT * FROM t_student LIMIT 10,5;

2.使用聚合函数

SELECT COUNT(*) FROM t_grade;

SELECT COUNT(*) AS total FROM t_grade;

SELECT stuName,COUNT(*) FROM t_grade GROUP BY stuName;

SELECT stuName,SUM(score) FROM t_grade WHERE stuName="张三";

SELECT stuName,SUM(score) FROM t_grade GROUP BY stuName;

SELECT stuName,AVG(score) FROM t_grade WHERE stuName="张三";

SELECT stuName,AVG(score) FROM t_grade GROUP BY stuName;

SELECT stuName,course,MAX(score) FROM t_grade WHERE stuName="张三";

SELECT stuName,MAX(score) FROM t_grade GROUP BY stuName;

SELECT stuName,course,MIN(score) FROM t_grade WHERE stuName="张三";

SELECT stuName,MIN(score) FROM t_grade GROUP BY stuName;

3.连接查询

SELECT * FROM t_book,t_bookType;

SELECT * FROM t_book,t_bookType WHERE t_book.bookTypeId=t_bookType.id;

SELECT bookName,author,bookTypeName FROM t_book,t_bookType WHERE t_book.bookTypeId=t_bookType.id;

SELECT tb.bookName,tb.author,tby.bookTypeName FROM t_book tb,t_bookType tby WHERE tb.bookTypeId=tby.id;

SELECT * FROM t_book LEFT JOIN t_bookType ON t_book.bookTypeId=t_bookType.id;

SELECT tb.bookName,tb.author,tby.bookTypeName FROM t_book tb LEFT JOIN t_bookType tby ON tb.bookTypeId=tby.id;


SELECT * FROM t_book RIGHT JOIN t_bookType ON t_book.bookTypeId=t_bookType.id;

SELECT tb.bookName,tb.author,tby.bookTypeName FROM t_book tb RIGHT JOIN t_bookType tby ON tb.bookTypeId=tby.id;


SELECT tb.bookName,tb.author,tby.bookTypeName FROM t_book tb,t_bookType tby WHERE tb.bookTypeId=tby.id AND tb.price>70;

4.子查询

SELECT * FROM t_book WHERE booktypeId IN (SELECT id FROM t_booktype);

SELECT * FROM t_book WHERE booktypeId NOT IN (SELECT id FROM t_booktype);

SELECT * FROM t_book WHERE price>=(SELECT price FROM t_pricelevel WHERE priceLevel=1);


SELECT * FROM t_book WHERE EXISTS (SELECT * FROM t_booktype);

SELECT * FROM t_book WHERE NOT EXISTS (SELECT * FROM t_booktype);

SELECT * FROM t_book WHERE price>= ANY (SELECT price FROM t_pricelevel);

SELECT * FROM t_book WHERE price>= ALL (SELECT price FROM t_pricelevel);

5.合并查询和别名

SELECT id FROM t_book;

SELECT id FROM t_booktype;

SELECT id FROM t_book UNION SELECT id FROM t_booktype;

SELECT id FROM t_book UNION ALL SELECT id FROM t_booktype;

SELECT * FROM t_book WHERE id=1;

SELECT * FROM t_book t WHERE t.id=1;

SELECT t.bookName FROM t_book t WHERE t.id=1;

SELECT t.bookName bName FROM t_book t WHERE t.id=1;

SELECT t.bookName AS bName FROM t_book t WHERE t.id=1;

6.插入、修改和删除数据

INSERT INTO t_book VALUES(NULL,'我爱我家',20,'张三',1);

INSERT INTO t_book(id,bookName,price,author,bookTypeId) VALUES(NULL,'我爱我家',20,'张三',1);

INSERT INTO t_book(bookName,author) VALUES('我爱我家','张三');

INSERT INTO t_book(id,bookName,price,author,bookTypeId) VALUES (NULL,'我爱我家2',20,'张三',1),(NULL,'我爱我家3',20,'张三',1);

UPDATE t_book SET bookName='Java编程思想',price=120 WHERE id=1;

UPDATE t_book SET bookName='我' WHERE bookName LIKE '%我爱我家%';

DELETE FROM t_book WHERE id=5;

DELETE FROM t_book WHERE bookName='我';