mysql

[root@localhost ~]# yum list all |grep "mariadb"

[root@localhost ~]# yum -y install mariadb*

[root@localhost ~]# systemctl start mariadb
[root@localhost ~]# ss -antl  //确保3306号端口被成功监听
State      Recv-Q Send-Q Local Address:Port               Peer Address:Port              
LISTEN     0      128      *:22                   *:*                  
LISTEN     0      100    127.0.0.1:25                   *:*                  
LISTEN     0      50       *:3306                 *:*                  
LISTEN     0      128     :::22                  :::*                  
LISTEN     0      100    ::1:25                  :::*    

[root@localhost ~]# mysql -uroot

MariaDB [(none)]> set password=password('wangaifei123!');//设置密码为王爱妃123！
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> quit
Bye

[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> //看到这样的标识表示成功登陆

mysql_secure_installation：安全初始化，强烈建议安装完以后执行此命令

[root@client ~]# mysql -uroot -pwangaifei123! -h

 服务器监听的两种socket地址
socket类型 	说明
ip socket 	默认监听在tcp的3306端口，支持远程通信
unix sock 	监听在sock文件上（/tmp/mysql.sock，/var/lib/mysql/mysql.sock）
仅支持本地通信
server地址只能是：localhost，127.0.0.1

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

MariaDB [(none)]> create database runtime;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| runtime            |
+--------------------+

MariaDB [(none)]> create database  if not exists runtime;
Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| runtime            |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> drop database if  exists runtime;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

MariaDB [(none)]> create database runtime;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create table student(id int null,name varchar(50) not null,age tinyint null);
ERROR 1046 (3D000): No database selected
MariaDB [(none)]> use runtime;
Database changed
MariaDB [runtime]> create table student(id int null,name varchar(50) not null,age tinyint null);
Query OK, 0 rows affected (0.02 sec)

MariaDB [runtime]> show tables;
+-------------------+
| Tables_in_runtime |
+-------------------+
| student           |
+-------------------+
1 row in set (0.00 sec)

MariaDB [runtime]> desc student;//看表结构
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(50) | NO   |     | NULL    |       |
| age   | tinyint(4)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

MariaDB [runtime]> 

创建数据库用户

MariaDB [(none)]> create user 'tom'@'192.168.119.191' identified by '123';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.01 sec)

查看表结构

MariaDB [(none)]> desc runtime.student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(50) | NO   |     | NULL    |       |
| age   | tinyint(4)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

[root@server ~]# mysql -uroot -pwangaifei123! -e "use runtime;show table status like 'student_' \G"|grep 'Data_length'
    Data_length: 16384
    Data_length: 16384
[root@server ~]# mysql -uroot -pwangaifei123! -e "use runtime;show table status like 'student_' \G"|grep 'Data_length'|awk -F: '{print $2}'
 16384
 16384

修改字段类型
alter table tb_name modify column ziduanming data_name

MariaDB [runtime]> alter table info  modify column phone_number varchar(11);
Query OK, 1 row affected (0.02 sec)                
Records: 1  Duplicates: 0  Warnings: 0

MariaDB [runtime]> desc info;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| id           | int(11)     | NO   |     | NULL    |       |
| name         | varchar(50) | NO   |     | NULL    |       |
| age          | tinyint(4)  | NO   |     | NULL    |       |
| phone_number | varchar(11) | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

插入记录

MariaDB [runtime]> insert into info //完整插入 values(1,'tom',19,'13333333333'),(2,'jerry',20,'14444444444'),(3,'zahngsan',40,'15555555555'),(4,'lisi',60,'16666666666'),(5,'wangwu',80,'17777777777');
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

MariaDB [runtime]> select * from info;
+----+----------+-----+--------------+
| id | name     | age | phone_number |
+----+----------+-----+--------------+
|  1 | tom      | 100 | 2147483647   |
|  1 | tom      | 100 | 12345678910  |
|  1 | tom      | 100 | 12345678910  |
|  1 | tom      |  19 | 13333333333  |
|  2 | jerry    |  20 | 14444444444  |
|  3 | zahngsan |  40 | 15555555555  |
|  4 | lisi     |  60 | 16666666666  |
|  5 | wangwu   |  80 | 17777777777  |
+----+----------+-----+--------------+
8 rows in set (0.00 sec)

MariaDB [runtime]> insert into info(id,name,age) value(6,'haha',10);//给指定字段插入
Query OK, 1 row affected (0.00 sec)

MariaDB [runtime]> select * from info;
+----+----------+-----+--------------+
| id | name     | age | phone_number |
+----+----------+-----+--------------+
|  1 | tom      | 100 | 2147483647   |
|  1 | tom      | 100 | 12345678910  |
|  1 | tom      | 100 | 12345678910  |
|  1 | tom      |  19 | 13333333333  |
|  2 | jerry    |  20 | 14444444444  |
|  3 | zahngsan |  40 | 15555555555  |
|  4 | lisi     |  60 | 16666666666  |
|  5 | wangwu   |  80 | 17777777777  |
|  6 | haha     |  10 | NULL         |
+----+----------+-----+--------------+
9 rows in set (0.00 sec)

MariaDB [runtime]> 

MariaDB [runtime]> select name from info;
+----------+
| name     |
+----------+
| tom      |
| tom      |
| tom      |
| tom      |
| jerry    |
| zahngsan |
| lisi     |
| wangwu   |
| haha     |
+----------+

ORDER BY：排序，默认为升序（ASC）
ORDER BY语句 	意义
ORDER BY ‘column_name' 	根据column_name进行升序排序
ORDER BY 'column_name' DESC 	根据column_name进行降序排序
ORDER BY ’column_name' LIMIT 2 	根据column_name进行升序排序
并只取前2个结果
ORDER BY ‘column_name' LIMIT 1,2 	根据column_name进行升序排序
并且略过第1个结果取后面的2个结果

MariaDB [wangaifei]> create table student(id int not null,name varchar(100) not null,age tinyint );
Query OK, 0 rows affected (0.00 sec)