一、数据库操作
1、创建数据库
CREATE DATABASE IF NOT EXISTS bookmanage;
2、查看数据库
（1）查看所有数据库
SHOW DATABASES;
（2）查看数据库详细信息
SHOW CREATE DATABASE mylove;
3、修改数据库
ALTER DATABASE mylove CHARACTER SET gb2312;
4、删除数据库
DROP DATABASE mylove;
DROP DATABASE IF EXISTS mylove;
5、选择数据库
USE yy;
6、查看当前的数据库（已选择）
SELECT DATABASE();
7、查看数据库中存在的所有表
SHOW TABLES;
二、数据表操作
1、创建数据表
(1)
USE school;
CREATE TABLE student(sid INT UNSIGNED NOT NULL,sname VARCHAR(20) NOT NULL,sex VARCHAR(4) NULL,smajor VARCHAR(30) NULL,sbriday VARCHAR(30) NOT NULL);
(2)
CREATE TABLE yy.tt(id INT,age INT);
2、查看表结构
(1)
USE school;
DESC student;或DESCRIBE student;
(2)查看表的详细结构
USE school;
SHOW CREATE TABLE student;
3、添加3条数据
INSERT INTO books(id,name,author,press,publisheddate,price) VALUES(1,"name1","author1","press1","2017-07-19",12),(2,"name2","author2","press2","2017-07-20",13),(3,"name3","author3","press3","2017-07-21",14);
4、查看表数据
SELECT * FROM books;
5、删除表
(1)删除表中所有数据，但保留表的结构
DELETE FROM books;
(2)删除表
DROP TABLE books;
6、查看数据库中的表
SHOW TABLES;
7、修改表名
ALTER TABLE book RENAME books;
三、字段操作
1、添加字段
(1)在表的第一列添加字段
ALTER TABLE books ADD num INT FIRST;
(2)在表的指定列之后添加字段
ALTER TABLE books ADD pressaddress VARCHAR(20) AFTER press;
2、修改字段类型
ALTER TABLE books MODIFY price DOUBLE;
3、删除字段
ALTER TABLE books DROP id;
4、修改字段位置
(1)将字段1修改为表的第一个字段
ALTER TABLE book MODIFY author VARCHAR(20) FIRST;
(2)将字段1插入到字段2的后面
ALTER TABLE book MODIFY id INT AFTER name;
四、字段约束
1、主键约束
(1)在创建时，定义的最后可设置复合主键
PRIMARY KEY(bnum,typeid)
(2)修改主键
i.表中没有主键
USE warehouse;
ALTER TABLE good MODIFY gid INT PRIMARY KEY;
ii.表中已有主键
ALTER TABLE book DROP PRIMARY KEY;
ALTER TALBE book ADD PRIMARY KEY(bnum);
2、外键约束
(1)添加外键约束
ALTER TABLE Books ADD CONSTRAINT bpress FOREIGN KEY(bpress) REFERENCES Publisher(pid) ON DELETE RESTRICT ON UPDATE CASCADE;
(2)查看外键
SHOW CREATE TABLE Books;
(3)删除外键
ALTER TABLE Books DROP FOREIGN KEY bpress;
ALTER TABLE good ADD UNIQUE(gid);
3、非空约束
修改为非空约束
ALTER TABLE Books MODIFY wid INT NOT NULL;
4、默认值
(1)添加默认值
ALTER TABLE books ALTER bid SET DEFAULT 5;
(2)删除默认值
ALTER TABLE books ALTER bid DROP DEFAULT;
(3)修改默认值
ALTER TABLE books ALTER bid DROP DEFAULT;
ALTER TABLE books ALTER bid SET DEFAULT 5;
(4)设置默认值为当前时间
创建的时候：
starttime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
5、唯一性约束
(1)也可以在创建的过程中UNIQUE(bid);
(2)删除唯一性约束
ALTER TABLE tt MODIFY COLUMN id INT NOT NULL;
(3)添加唯一性约束
ALTER TALBE tt ADD UNIQUE(id);
6、自增约束
(1)添加自增约束
ALTER TABLE book MODIFY COLUMN bid INT NOT NULL AUTO_INCREMENT;
(2)删除自增约束
ALTER TABLE book MODIFY COLUMN bid INT NOT NULL;
7、删除指定名称的约束
ALTER TABLE book DROP INDEX bnum;
五、单表查询
(1)根据条件查询,分组查询,HAVING查询,排序查询,LIMIT限制数量
SELECT * FROM table WHERE id = 1 GROUP BY manid,manname HAVING price >= 32 ORDER BY price LIMIT 2,2;
注意：LIKE ‘字符串’
IN (2,4,6,8)
(2)避免重复查询
SELECT DISTINCT(name),age FROM students;
(3)查询集合函数
i.SELECT COUNT(*) FROM book;
SELECT manSex,COUNT(*) FROM book GROUP BY manSex;
ii.SELECT AVG(bookid) FROM book;
iii. SELECT SUM(bookid) AS ‘图书总价格’ FROM book;
iiii. SELECT MAX(price) FROM book GROUP BY id;
SELECT MIN(price) FROM book GROUP BY id;
六、多表查询
（1）内连接查询
SELECT bi.bookid,bi.bookname,bi.bookprice,bt.booktype FROM bookinfo AS bi INNER JOIN booktype AS bt ON bi.bookid = bt.booktypeid;
（1）外连接查询
SELECT bi.bookid,bi.bookname,bi.bookprice,bt.booktype FROM bookinfo AS bi LEFT/RIGHT OUTER JOIN booktype AS bt ON bi.bookid = bt.booktypeid;
七、字段值更新
UPDATE books SET bookid = 3 WHERE bookname = “AA”;