mysql数据库存储圆形要素_Mysql基础篇

实验一：mysql二进制安装方法

mysql_secure_installation 系统初始化(更安全一点)

mysql  -uroot  -pcentos (登陆mysql,-u后跟root，-p后跟密码，之间没有空格)实验2：在centos6上安装二进制的mariadb

1.从downloads.mariadb.org  下载所需版本的mariadb的二进制包 mariadb-10.3.9-linux-x86_64.tar.gz

2.创建所需用户及组

[root@centos6(nanyibo) ~]# groupadd -g 36 -r mysql

[root@centos6(nanyibo) ~]# useradd -u 36 -r -g mysql -m -d /app/dbdata -s /sbin/nologin mysql

3.准备dbdata 建议做到lvm上

[root@centos6(nanyibo) ~]# fdisk /dev/sda

[root@centos6(nanyibo) ~]# partx -a /dev/sda

[root@centos6(nanyibo) ~]# pvcreate /dev/sda6

[root@centos6(nanyibo) ~]# vgcreate vgmysql /dev/sda6

[root@centos6(nanyibo) ~]# lvcreate -l +100%FREE -n lvmysql vgmysql

[root@centos6(nanyibo) ~]# mkfs.ext4 /dev/vgmysql/lvmysql

[root@centos6(nanyibo) ~]# vim /etc/fstab

/dev/vgmysql/lvmysql    /app/dbdata             ext4    defaults        0 0

[root@centos6(nanyibo) ~]# mount -a

[root@centos6(nanyibo) ~]# chown mysql.mysql /app/dbdata

[root@centos6(nanyibo) ~]# chmod 700 /app/dbdata

4.解压二进制目录

[root@centos6(nanyibo) ~]# tar -xvf mariadb-10.2.14-linux-x86_64.tar.gz -C /usr/local/

[root@centos6(nanyibo) ~]# cd /usr/local/

[root@centos6(nanyibo) local]# ln -sv mariadb-10.2.14-linux-x86_64 mysql

`mysql' -> `mariadb-10.2.14-linux-x86_64'

5.创建配置文件

[root@centos6(nanyibo) local]# cd /usr/local/mysql/

[root@centos6(nanyibo) mysql]# mkdir /etc/mysql

[root@centos6(nanyibo) mysql]# cp support-files/my-huge.cnf /etc/mysql/my.cnf

[root@centos6(nanyibo) mysql]# vim /etc/mysql/my.cnf

[mysqld]

……

datadir         = /app/dbdata

innodb_file_per_table   = on

skip_name_resolve = on

……

6.创建数据库

[root@centos6(nanyibo) mysql]# scripts/mysql_install_db --datadir=/app/dbdata --user=mysql

7.配置启动脚本

[root@centos6(nanyibo) mysql]# cp support-files/mysql.server /etc/init.d/mysqld

[root@centos6(nanyibo) mysql]# chkconfig --add mysqld

[root@centos6(nanyibo) mysql]# chkconfig mysqld on

[root@centos6(nanyibo) mysql]# service mysqld restart

MariaDB server PID file could not be found!                [FAILED]

Starting MariaDB.180710 18:00:14 mysqld_safe Logging to '/var/log/mysqld.log'.

180710 18:00:14 mysqld_safe Starting mysqld daemon with databases from /app/dbdata

[  OK  ]

8.配置环境变量PATH

[root@centos6(nanyibo) ~]# vim /etc/profile.d/mage.sh

export PATH=/usr/local/mysql/bin:$PATH

[root@centos6(nanyibo) ~]# source /etc/profile.d/mage.sh

9.初始化mysql

[root@centos6(nanyibo) ~]# mysql_secure_installation

基本语句

MariaDB [(none)]> select user();

+----------------+

| user()         |

+----------------+

| root@localhost |

+----------------+

1 row in set (0.00 sec)

MariaDB [(none)]> select version();

+---------------------+

| version()           |

+---------------------+

| 10.2.14-MariaDB-log |

+---------------------+

1 row in set (0.00 sec)

查看有哪些databases

MariaDB [(none)]> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| test               |

+--------------------+

4 rows in set (0.01 sec)

创建数据库

MariaDB [test]> create database magedb;

Query OK, 1 row affected (0.01 sec)

MariaDB [test]> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| magedb             |

| mysql              |

| performance_schema |

| test               |

+--------------------+

5 rows in set (0.00 sec)

DDL (create drop alter)

MariaDB [m33student]> create table student (id tinyint unsigned primary key, name varchar(20) not null, age tinyint unsigned,sex char(1) default "m" );

Query OK, 0 rows affected (0.05 sec)

MariaDB [m33student]> desc student;

+-------+---------------------+------+-----+---------+-------+

| Field | Type                | Null | Key | Default | Extra |

+-------+---------------------+------+-----+---------+-------+

| id    | tinyint(3) unsigned | NO   | PRI | NULL    |       |

| name  | varchar(20)         | NO   |     | NULL    |       |

| age   | tinyint(3) unsigned | YES  |     | NULL    |       |

| sex   | char(1)             | YES  |     | m       |       |

+-------+---------------------+------+-----+---------+-------+

4 rows in set (0.02 sec)

MariaDB [m33student]> create table student2 (id tinyint unsigned, lesson varchar(20) not null default "linux" , score tinyint unsigned, primary key (id,lesson));

Query OK, 0 rows affected (0.02 sec)

MariaDB [m33student]> desc student2

-> ;

+--------+---------------------+------+-----+---------+-------+

| Field  | Type                | Null | Key | Default | Extra |

+--------+---------------------+------+-----+---------+-------+

| id     | tinyint(3) unsigned | NO   | PRI | NULL    |       |

| lesson | varchar(20)         | NO   | PRI | linux   |       |

| score  | tinyint(3) unsigned | YES  |     | NULL    |       |

+--------+---------------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

MariaDB [m33student]> drop table student2;

Query OK, 0 rows affected (0.04 sec)

MariaDB [m33student]> alter table student add phone char(11) after name;

Query OK, 0 rows affected (0.05 sec)

查看索引

MariaDB [m33student]> show indexes from student\G;

增加唯一性约束

MariaDB [m33student]> alter table student add unique key (phone);

删除列

MariaDB [m33student]> alter table student drop phone;

创建索引

MariaDB [m33student]> create index age_index on student(phone);

DML

insert

MariaDB [m33student]> insert into student values (1,'liuxin','18768896428',22,default);

Query OK, 1 row affected (0.02 sec)

MariaDB [m33student]> insert into student (id,name) values (2,'zhangxinglei');

Query OK, 1 row affected (0.02 sec)

MariaDB [m33student]> insert into student (name,id) values ('zhangxuechao',3);

Query OK, 1 row affected (0.01 sec)

一次性插入多行

MariaDB [m33student]> insert into student (id,name,sex) values (4,'meiwenjun','f'),(5,'chenhaiyan','f'),(6,'fanguodong',default);

使用查询语句创建表 as可以省略

MariaDB [m33student]> create table emp as select * from student;

删除表中的所有行

MariaDB [m33student]> delete from lady;

Query OK, 4 rows affected (0.01 sec)

通过查询结果来插入行

MariaDB [m33student]> insert into lady select * from student where sex='m';

Query OK, 2 rows affected (0.02 sec)

update

MariaDB [m33student]> update student set phone='18438613802' where id=2;

Query OK, 1 row affected (0.03 sec)

Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [m33student]> update emp set phone='18438613802' ;

Query OK, 6 rows affected (0.02 sec)

Rows matched: 6  Changed: 6  Warnings: 0

delete

MariaDB [m33student]> delete from emp where id=1;

Query OK, 1 row affected (0.02 sec)

MariaDB [m33student]> delete from emp;

Query OK, 6 rows affected (0.03 sec)

create table lesson (id tinyint unsigned, lesson varchar(20) not null default "linux" , score tinyint unsigned, primary key (id,lesson));

MariaDB [m33student]> select * from student;

+----+--------------+-------------+------+------+

| id | name         | phone       | age  | sex  |

+----+--------------+-------------+------+------+

|  1 | liuxin       | 18768896428 |   22 | m    |

|  2 | zhangxinglei | 18438613802 | NULL | m    |

|  3 | zhangxuechao | NULL        | NULL | m    |

|  4 | meiwenjun    | NULL        | NULL | f    |

|  5 | chenhaiyan   | NULL        | NULL | f    |

|  6 | fanguodong   | NULL        | NULL | m    |

| 10 | weishiyan    | 11515151511 |   18 | f    |

+----+--------------+-------------+------+------+

7 rows in set (0.00 sec)

MariaDB [m33student]> select name mingzi,phone from student;

+--------------+-------------+

| mingzi       | phone       |

+--------------+-------------+

| liuxin       | 18768896428 |

| zhangxinglei | 18438613802 |

| zhangxuechao | NULL        |

| meiwenjun    | NULL        |

| chenhaiyan   | NULL        |

| fanguodong   | NULL        |

| weishiyan    | 11515151511 |

+--------------+-------------+

7 rows in set (0.00 sec)

MariaDB [m33student]> select id,score+10 score from lesson;

+----+-------+

| id | score |

+----+-------+

|  1 |   100 |

|  2 |   105 |

+----+-------+

2 rows in set (0.00 sec)

rhel   7.0        7.1      7.3

mysql 5.5-1      5.5-2    5.5-2

函数

单行函数

字符串型

upper

lower

concat

select concat(name,' age is ',age),gender from students;

insert替换函数(列名，从第几位开始，替换几位，替换的内容)

MariaDB [hellodb]> select name,insert(phone,4,4,'****') phone from students;

+----------------+-------------+

| name           | phone       |

+----------------+-------------+

| Hou Yi         | 151****2972 |

| Ya Se          | 151****2972 |

| An Qila        | 135****9929 |

| Da Ji          | 135****9929 |

| Sun Shangxiang | 135****9929 |

length(name)  长度

lpad(phone,15,'*')

rpad(phone,15,'*')

replace(name,'Liu','Li')

substr(phone,5) 从第5位开始取到最后

substr(phone,5,3) 从第5位开始取，往后一共取3位

数字型

abs(-20); 取绝对值

mod(10,3); 取模

ceil(9.2)；取不小于X的最小整数

floor(3.6) 取不大于X的最大整数

round 四舍五入

round(3.41111)  得3

round(3.41111,2)  得3.41

truncate 截断

truncate(3.111111,0) 得3

truncate(113.119911,3) 得113.119

truncate(113.119911,-1) 得110

日期型

select now();  显示当前的时间

select curdate();  显示当前的日期

select curtime();  显示当前的时间

select year(birthday); 显示指定日期的年

select month(birthday); 显示指定日期的月

select day(birthday); 显示指定日期是几号

select dayname(now()); 显示指定日期是周几

字符转日期

str_to_date('24-11-1700','%d-%m-%Y')  字符转日期

日期转字符

select date_format(birthday,'%Y年%m月%d日') 生日 from students;

null函数

ifnull(score,0)

coalesce(score,0)

其他函数

MariaDB [hellodb]> select user();

+----------------+

| user()         |

+----------------+

| root@localhost |

+----------------+

1 row in set (0.00 sec)

MariaDB [hellodb]> select version;

ERROR 1054 (42S22): Unknown column 'version' in 'field list'

MariaDB [hellodb]> select version();

+----------------+

| version()      |

+----------------+

| 5.5.56-MariaDB |

+----------------+

1 row in set (0.00 sec)

MariaDB [hellodb]> select database();

+------------+

| database() |

+------------+

| hellodb    |

+------------+

1 row in set (0.00 sec)

case else

select courseid,case courseid

-> when 2 then score+10

-> when 3 then score+0

-> when 4 then score+20

-> else score

-> end

-> as score

-> from scores;

多行函数(聚合函数、组函数)

count() 返回表中满足where条件的行的数量，如没有Where条件，列出所有行的总数

MariaDB [hellodb]> Select count(*) from scores where score > 50;

+----------+

| count(*) |

+----------+

|       14 |

+----------+

MariaDB [hellodb]> select count(distinct classid) from students;

+-------------------------+

| count(distinct classid) |

+-------------------------+

|                       7 |

+-------------------------+

1 row in set (0.00 sec)

max()  min()  avg()  sum()

select max(score) from scores;

select courseid,sum(score) scoreall from scores group by courseid;

算平均值时，注意null不会参与组函数，所以要先用ifnull将null转为0，如下

MariaDB [hellodb]> select avg(ifnull(score,0)) from scores;

+----------------------+

| avg(ifnull(score,0)) |

+----------------------+

|              73.0588 |

+----------------------+

1 row in set (0.00 sec)

MariaDB [hellodb]> select courseid,avg(score) from scores group by courseid;

select courseid,avg(nullif(score,0)) as avg from scores group by courseid where avg>60; 错误

select courseid,avg(nullif(score,0)) as avg from scores group by courseid having avg>60; 对的

where发生在group by 之前，所以不能对group by 后的结果进行筛选，而要用having

select * from students limit 6  取前6行，即1-6行

select * from students limit 6,3 取6行后的3行，即7,8,9行

练习



导入hellodb.sql生成数据库



(1)在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄

MariaDB [hellodb]> select Name,age from students where age>25 and gender='m';

(2)以ClassID为分组依据，显示每组的平均年龄

MariaDB [hellodb]> select classid ,avg(age) age from students group by classid having classid is not null;

(3)显示第2题中平均年龄大于30的分组及平均年龄

MariaDB [hellodb]> select classid ,avg(age) age from students group by classid having age>30 and classid is not null;

(4)显示以L开头的名字的同学的信息

MariaDB [hellodb]> select * from students where name like 'L%';

(5)显示TeacherID非空的同学的相关信息

MariaDB [hellodb]> select * from students where TeacherID is NOT NULL;

(6)以年龄排序后，显示年龄最大的前10位同学的信息

MariaDB [hellodb]> select * from students order by age desc limit 10;

(7)查询年龄大于等于20岁，小于等于25岁的同学的信息

mysql> select * from students where age >= 20 and age <=25;

mysql> select * from students where age between 20 and 25;