DB (DataBase) 数据库
DBMS (DataBase Management System) 数据库管理系统
DBS (DataBase System ) 数据库系统 即DB +DBMS
MySQL 关系型数据库系统
配置MySQL
搭建仓库服务器
yum -y install httpd php php-mysqlnd php-xml php-json createrepo
systemctl start httpd
systemctl enable httpd
mkdir /var/www/html/mysql
ls
tar xf mysql-5.7.17.tar -C /var/www/html/mysql/
yum -y install createrepo
createrepo -d .
vim /etc/yum.repos.d/mysql.repo编写网络yum源
[mysql]
name=mysql5.7
baseurl=http://你主机的ip地址/mysql
enabled=1
gpgcheck=0
yum install mysql-community* 安装MySQL
systemctl start mysqld 启动MySQL
systemctl enable mysqld 开机自启MySQL
修改数据库密码
grep -i password /var/log/mysqld.log
mysqladmin -uroot -p'=qA>0=4o8lCx' password NSD2021@tedu.cn
mysql -uroot -p'NSD2021@tedu.cn' 测试
将数据传到虚拟机,解压上传到数据库
ls
unzip mysql.zip
ls
cd mysql/
ls
cd dbs/
ls
cd mysql_scripts/
ls
mysql -uroot -pNSD2021@tedu.cn < nsd2021_data.sql 将数据导入数据库
mysql -uroot -p'NSD2021@tedu.cn' 进入数据库查看数据
show databases ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| nsd2021 |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
安装 phpMyAdmin
yum -y install php php-mysqlnd php-xml php-json
systemctl start httpd
systemctl enable httpd
将 phpMyAdmin的压缩包传到虚拟机解压 生成配置文件
tar -xf phpMyAdmin-2.11.11-all-languages.tar.gz
mv phpMyAdmin-2.11.11-all-languages /var/www/html/mysqladmin
cd /var/www/html/mysqladmin/
cp config.sample.inc.php config.inc.php
vim config.inc.php
/*.........*/ 多行注释
1,mysql> desc departments;查看表结构
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| dept_id | int(4) | NO | PRI | NULL | auto_increment |
| dept_name | varchar(10) | YES | | NULL | |
+-----------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
2,use nsd2021; 进入库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
基本查询
mysql> show tables; 查看表
+-------------------+
| Tables_in_nsd2021 |
+-------------------+
| departments |
| employees |
| salary |
+-------------------+
3 rows in set (0.00 sec)
mysql> select count(*) from departments; 统计表共有多少行
+----------+
| count(*) |
+----------+
| 8 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from employees; 查看表所有
+----------+
| count(*) |
+----------+
| 133 |
+----------+
1 row in set (0.00 sec)
mysql> select 100; 查询常量
+-----+
| 100 |
+-----+
| 100 |
+-----+
1 row in set (0.00 sec)
select 10+5; 查询表达式
+------+
| 10+5 |
+------+
| 15 |
+------+
1 row in set (0.00 sec)
mysql> select version(); 查询版本
+-----------+
| version() |
+-----------+
| 5.7.17 |
+-----------+
1 row in set (0.00 sec)
mysql> select count(*) from salary; 统计表共有多少行
+----------+
| count(*) |
+----------+
| 8055 |
+----------+
1 row in set (0.00 sec)
mysql> select dept_id from employees;
+---------+
| dept_id |
+---------+
| 1 |
| 1 |
| 2 |
| 2 |
| 2 |
| 2 |
| 2 |
| 3 |
| 3 |
| 4 |
| 4 |
| 4 |
| 4 |
| 5 |
| 5 |
| 6 |
| 6 |
| 6 |
| 7 |
| 7 |
| 8 |
| 8 |
+---------+
133 rows in set (0.00 sec)
mysql> select distinct dept_id from employees; 去掉重复的数据
+---------+
| dept_id |
+---------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
+---------+
8 rows in set (0.00 sec)
concat函数实现拼接
mysql> select concat(name,'-',phone_number) AS 信息 from employees;
+-----------------------+
| 信息 |
+-----------------------+
| 梁伟-13591491431 |
| 郭岩-13845285867 |
| 张健-13835990213 |
...................
+-----------------------+
133 rows in set (0.00 sec)
条件测试(一个条件)
>: 大于
<: 小于
=: 等于
>=: 大于等于
<=: 小于等于
!=: 不等于
mysql> select * from departments where dept_id>3;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 4 | 开发部 |
| 5 | 测试部 |
| 6 | 市场部 |
| 7 | 销售部 |
| 8 | 法务部 |
+---------+-----------+
5 rows in set (0.00 sec)
mysql> select *from departments where dept_id<6 ;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 1 | 人事部 |
| 2 | 财务部 |
| 3 | 运维部 |
| 4 | 开发部 |
| 5 | 测试部 |
+---------+-----------+
5 rows in set (0.00 sec)
逻辑运算符,and(&&)、or(||)、not(!)
运算符有优先级 加 '()'里优先级高
mysql> select *from departments where !(dept_id<3);
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 3 | 运维部 |
| 4 | 开发部 |
| 5 | 测试部 |
| 6 | 市场部 |
| 7 | 销售部 |
| 8 | 法务部 |
+---------+-----------+
6 rows in set (0.00 sec)
模糊查询
1.like: 包含
'_' 匹配单个字符
'%'匹配多个字符
select name,email from employees where name like '张__';
+-----------+--------------------------+
| name | email |
+-----------+--------------------------+
| 张建平 | zhangjianping@tarena.com |
| 张冬梅 | zhangdongmei@tedu.cn |
| 张淑英 | zhangshuying@tarena.com |
+-----------+--------------------------+
3 rows in set (0.00 sec)
2.between xxx and yyy: 在xxx和yyy之间的
mysql> select * from departments where dept_id between 3 and 6;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 3 | 运维部 |
| 4 | 开发部 |
| 5 | 测试部 |
| 6 | 市场部 |
+---------+-----------+
4 rows in set (0.00 sec)
3.in:在列表中的
ysql> select * from departments where dept_id in (1,2,3,4);
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 1 | 人事部 |
| 2 | 财务部 |
| 3 | 运维部 |
| 4 | 开发部 |
+---------+-----------+
4 rows in set (0.00 sec)
4.is null:为空,相当于python的None
5is not null:非空
mysql> select * from departments where dept_name is not null; 部门名不为空
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 1 | 人事部 |
| 2 | 财务部 |
| 3 | 运维部 |
| 4 | 开发部 |
| 5 | 测试部 |
| 6 | 市场部 |
| 7 | 销售部 |
| 8 | 法务部 |
+---------+-----------+
8 rows in set (0.00 sec)
mysql> select * from departments where dept_name is null; 部门名为空
Empty set (0.00 sec)
排序(默认升序)
mysql> select name,birth_date from employees where birth_date>='19990101' ; 不排序
+-----------+------------+
| name | birth_date |
+-----------+------------+
| 聂想 | 1999-06-05 |
| 陈斌 | 2000-01-22 |
| 胡秀云 | 2000-05-14 |
| 张倩 | 2000-04-27 |
| 张伟 | 1999-04-30 |
| 王璐 | 2000-02-01 |
| 符燕 | 1999-12-12 |
| 韩丹 | 1999-06-08 |
| 蒋秀芳 | 2000-04-27 |
| 张宇 | 2000-07-16 |
| 陶红 | 2000-02-21 |
| 苏波 | 1999-12-08 |
| 游静 | 2000-02-14 |
| 王荣 | 1999-11-22 |
+-----------+------------+
14 rows in set (0.00 sec)
mysql> select name,birth_date from employees where birth_date>='19990101'order by birth_date; 升序排列
+-----------+------------+
| name | birth_date |
+-----------+------------+
| 张伟 | 1999-04-30 |
| 聂想 | 1999-06-05 |
| 韩丹 | 1999-06-08 |
| 王荣 | 1999-11-22 |
| 苏波 | 1999-12-08 |
| 符燕 | 1999-12-12 |
| 陈斌 | 2000-01-22 |
| 王璐 | 2000-02-01 |
| 游静 | 2000-02-14 |
| 陶红 | 2000-02-21 |
| 张倩 | 2000-04-27 |
| 蒋秀芳 | 2000-04-27 |
| 胡秀云 | 2000-05-14 |
| 张宇 | 2000-07-16 |
+-----------+------------+
14 rows in set (0.00 sec)
mysql> select name,birth_date from employees where birth_date>='19990101'order by birth_date desc; 降序排列
+-----------+------------+
| name | birth_date |
+-----------+------------+
| 张宇 | 2000-07-16 |
| 胡秀云 | 2000-05-14 |
| 张倩 | 2000-04-27 |
| 蒋秀芳 | 2000-04-27 |
| 陶红 | 2000-02-21 |
| 游静 | 2000-02-14 |
| 王璐 | 2000-02-01 |
| 陈斌 | 2000-01-22 |
| 符燕 | 1999-12-12 |
| 苏波 | 1999-12-08 |
| 王荣 | 1999-11-22 |
| 韩丹 | 1999-06-08 |
| 聂想 | 1999-06-05 |
| 张伟 | 1999-04-30 |
+-----------+------------+
14 rows in set (0.00 sec)
mysql> select date,employee_id,basic,bonus from salary where date='20150110'
-> order by basic,bonus desc;
select date,employee_id,basic+bonus as total from salary where date='20150110' order by total;