mysql命令整理

一、数据库操作

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”;