MySQL常用语句
1.删除表
DROP TABLE 表名;
2.创建表
CREATE TABLE books(
id INT(20) NOT NULL AUTO_INCREMENT,
book_no VARBINARY(200) NOT NULL COMMENT '图书编号',
book_name VARCHAR(200) NOT NULL COMMENT '书名',
book_author VARCHAR(200) NOT NULL COMMENT '作者',
book_out_time DATE COMMENT '出版时间',
book_infomation TEXT COMMENT '图书详情',
book_shelf VARCHAR(200) COMMENT '书架位置',
book_type VARCHAR(200) COMMENT '图书类型',
book_count INT(100) COMMENT '库存数量',
book_img_url VARCHAR(200) COMMENT '图片地址',
book_out_sum INT(100) COMMENT '图书借阅次数',
PRIMARY KEY(id)
);
3.查询表:
3.1 查询所有数据
SELECT * FROM books;-------查询所有数据
SELECT book_name FROM books;---------查询特定列
3.2 条件查询
SELECT * FROM books WHERE book_name='飞机制造';-------一个条件查询
SELECT * FROM books WHERE book_name='飞机制造' AND book_count=2;---------多条件 并 查询
SELECT * FROM books WHERE book_name='飞机制造' OR book_count=2;---------多条件 或 查询
SELECT * FROM books WHERE book_count>=0 AND book_count<=2;---------范围查询
SELECT * FROM books WHERE book_count BETWEEN 1 AND 2;---------范围查询
3.3 模糊查询
SELECT * FROM books WHERE book_name LIKE '%制造';------%通配符代表任意多个字符
SELECT * FROM books WHERE book_name LIKE '%制造%';
SELECT * FROM books WHERE book_name LIKE '_制造';-------- _通配符代表任意一个字符
3.4 排序
SELECT * FROM books ORDER BY book_count asc; -------按照价格升序排列
SELECT * FROM books ORDER BY book_count desc;-------- 按照价格降序排列
SELECT * FROM books ORDER BY book_count,book_out_sum; -------按照两列进行排序,前面的为主要的
3.5 统计函数(聚合函数)
SELECT COUNT(book_name) FROM books;---------查询表中有多少条数据
SELECT MAX(book_count) FROM books;---------查询图书库存数量最大值
SELECT MIN(book_count) FROM books;---------查询图书库存数量最小值
SELECT SUM(book_count) FROM books;---------查询图书库存数量总值
SELECT AVG(book_count) FROM books;---------查询图书库存数量平均值
3.6 分组查询
select Brand from Car group by Brand having count(*)>2 --------查询所有系列中数量大于2的
3.7 分页查询
select * from Car limit 0,5 -------跳过几条数据取几条数据
3.8 去重查询
select distinct Brand from Car
3.1 单表查询 :
3.2 多表连接查询:
3.3 内连接查询 :
4.修改表:
ALTER TABLE book(旧表名) RENAME books(新表名); ------修改表名
ALTER TABLE books ADD COLUMN book CHAR(10); --------插入表列
ALTER TABLE books DROP COLUMN book; ----------删除表列
ALTER TABLE books MODIFY book_no VARCHAR(10); ----------修改列表类型
ALTER TABLE books CHANGE book_no book_no INT(20);
ALTER TABLE books CHANGE COLUMN books books1 varchar(30); -----------修改表列名
5.删除:
关于删除一个很好的博客
https://www.cnblogs.com/tv151579/archive/2014/07/11/3837668.html
6.插入数据:
INSERT INTO table_name ( field1, field2,...fieldN )
VALUES
( value1, value2,...valueN );
例如:
INSERT INTO books ( book_no, book_name, book_out_time, book_infomation, book_shelf, book_type, book_count,book_img_url,book_out_sum)
VALUE
('1', '飞机制造', '1995-2-3','内容','A-12','科技类',1,'D:新建文件夹',2);
列名如果设定了AUTO_INCREMENT(自增)则不用写
Mysql 多表连接查询:
1.内连接
SELECT table_1.列名 ,table_2.列名 FROM table_1 INNER JOIN table_2 ON table_1.id=table_2.table_1的id
eg.
SELECT bs.`book_name`,bs.`book_author`,bb.borrow_date,mu.username,mu.sex FROM books_borrow bb INNER JOIN books bs ON bb.book_id=bs.id
INNER JOIN myuser mu ON bb.user_borrow_id=mu.id
2.左连接
SELECT table_1.列名 ,table_2.列名 FROM table_1 LEFT JOIN table_2 ON table_1.id=table_2.table_1的id
左连接以左表为基础,会查询出左表的全部内容,右表的有关联的查询出来,没关联的显示null
3.右连接
eg.SELECT * FROM books_borrow bb RIGHT JOIN books bs ON bb.book_id=bs.id
同上 以右表为基础 ,会查询出右表全部内容,左表有关联的会显示,没关联的显示null
4.子查询
eg.SELECT * FROM books WHERE id IN(SELECT book_id FROM books_borrow WHERE id=1)
select 需要查询的东西 from 表名 where 列名 in (select 需要查询的东西 from 表名 where 条件)