第15次预习课-数据库mysql-20181017
第15次预习课-数据库mysql
1. 安装mysql
下载地址:https://dev.mysql.com/downloads/windows/installer/
2.安装完后,查看是否成功
C:\Users\Xue Feifei>cd C:\Program Files\MySQL\MySQL Server 5.7\bin
C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -V
mysql Ver 14.14 Distrib 5.7.23, for Win64 (x86_64)
3.将 C:\Program Files\MySQL\MySQL Server 5.7\bin 添加到环境变量path里。
C:\Users\Xue Feifei>mysql.exe -uroot -h127.0.0.1 -P3306 -p
Enter password: ******
show databases; #查看数据库
mysql> create database testmandb; #新建一个数据库
mysql> drop database testmandb; #删除库
mysql> use testmandb; #使用库
mysql> select database(); #查看当前使用的库
mysql> create table user(id int(11)); #创建一个表
mysql> show tables; #查看表
mysql> drop table user; #删除一个表
建立表
create table book(
id INT NOT NULL AUTO_INCREMENT,
book_name VARCHAR(100) NOT NULL,
author VARCHAR(40) NOT NULL,
publish_date DATE,
price float,
PRIMARY KEY(id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
mysql> desc book; #查看表结构
mysql> insert into book values(1,"selenium","wulao","2018-1-1",50); #插入数据
mysql> select * from book; #查询表数据
插入除自增的id之外的数据
insert into book(book_name,author,publish_date,price) values("mangodb","zhanglao","2019-3-1",100);
mysql> select count(*) from book; #查询数量
mysql> delete from book where id =4; #删除id=4的数据
修改:
update book set book_name="gloryroad db" where book_name = "bdb";
select book_name from book; #查询某一列数据
select book_name as 书名 from book;
select id as 序号,book_name as 书名 from book;
mysql> select * from book where id>=6;
select * from book where author<>"wulao"; #不等于是<>
select * from book order by price; #按照价格排序
select * from book order by price asc; 升序
select * from book order by price desc; 降序
select author,count(*) from book group by author;
select author,avg(price) from book group by author;
select author,sum(price) from book group by author having sum(price)>200;