windows download address
https://dev.mysql.com/downloads/installer/
linux install
yum install mysql mysql-server -y
mysql modification password
mysqladmin -uroot -p password
1.添加和删除数据库
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| zabbix |
+--------------------+
5 rows in set (0.08 sec)
MariaDB [(none)]> create database gc;
Query OK, 1 row affected (0.04 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| gc |
| mysql |
| performance_schema |
| test |
| zabbix |
+--------------------+
6 rows in set (0.04 sec)
MariaDB [(none)]> drop database gc;
Query OK, 0 rows affected (0.08 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| zabbix |
+--------------------+
5 rows in set (0.01 sec)
MariaDB [(none)]>
2.MySQL 数据类型
MySQL中定义数据字段的类型对你数据库的优化是非常重要的。
MySQL支持多种类型,大致可以分为三类:数值、日期/时间和字符串(字符)类型
reference:
http://www.runoob.com/mysql/mysql-data-types.html
3.添加和删除数据表
创建数据表:
CREATE TABLE table_name (column_name column_type);
删除数据表:
DROP TABLE table_name ;
查看table:
describe tablename
MariaDB [(none)]> use gc
Database changed
MariaDB [gc]> show tables;
Empty set (0.00 sec)
MariaDB [gc]> create table account (id bigint(20) , creatTime datetime, ip~ varchar(255), mobile varchar(255), nickname varchar(255), passwd varchar(255), username varchar(255), avatar varchar(255), brief text, job varchar(255), location varchar(255), qq varchar(255), gender int(11), city varchar(255), province varchar(255));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '~ varchar(255), mobile varchar(255), nickname varchar(255), passwd varchar(255),' at line 1
MariaDB [gc]> create table account (id bigint(20) , creatTime datetime, ip varchar(255), mobile varchar(255), nickname varchar(255), passwd varchar(255), username varchar(255), avatar varchar(255), brief text, job varchar(255), location varchar(255), qq varchar(255), gender int(11), city varchar(255), province varchar(255));
Query OK, 0 rows affected (0.09 sec)
MariaDB [gc]> show tables;
+--------------+
| Tables_in_gc |
+--------------+
| account |
+--------------+
1 row in set (0.04 sec)
MariaDB [gc]> create table account1 (id bigint(20) , creatTime datetime, ip varchar(255), mobile varchar(255), nickname varchar(255), passwd varchar(255), username varchar(255), avatar varchar(255), brief text, job varchar(255), location varchar(255), qq varchar(255), gender int(11), city varchar(255), province varchar(255));
Query OK, 0 rows affected (1.26 sec)
MariaDB [gc]> show tables;
+--------------+
| Tables_in_gc |
+--------------+
| account |
| account1 |
+--------------+
2 rows in set (0.01 sec)
MariaDB [gc]> describe account;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id | bigint(20) | YES | | NULL | |
| creatTime | datetime | YES | | NULL | |
| ip | varchar(255) | YES | | NULL | |
| mobile | varchar(255) | YES | | NULL | |
| nickname | varchar(255) | YES | | NULL | |
| passwd | varchar(255) | YES | | NULL | |
| username | varchar(255) | YES | | NULL | |
| avatar | varchar(255) | YES | | NULL | |
| brief | text | YES | | NULL | |
| job | varchar(255) | YES | | NULL | |
| location | varchar(255) | YES | | NULL | |
| qq | varchar(255) | YES | | NULL | |
| gender | int(11) | YES | | NULL | |
| city | varchar(255) | YES | | NULL | |
| province | varchar(255) | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
15 rows in set (0.02 sec)
MariaDB [gc]> drop table account1;
Query OK, 0 rows affected (0.05 sec)
MariaDB [gc]> show tables;
+--------------+
| Tables_in_gc |
+--------------+
| account |
+--------------+
1 row in set (0.00 sec)
MariaDB [gc]>
4.给数据表添加或者删除列
MariaDB [gc]> alter table account add c1 int(11) not null default 1;
Query OK, 0 rows affected (0.36 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [gc]> alter table account drop c1;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
5.修改某个数据列的名字或者数据类型
修改数据列名字为newcity
MariaDB [gc]> alter table account change city newcity varchar(255);
修改tpye
MariaDB [gc]> alter table account change newcity newcity text;
修改表名
MariaDB [gc]> alter table account rename newaccount;
6.查看或者插入表数据
MySQL 插入数据
MySQL 表中使用 INSERT INTO SQL语句来插入数据。
你可以通过 mysql> 命令提示窗口中向数据表中插入数据,或者通过PHP脚本来插入数据。
语法
以下为向MySQL数据表插入数据通用的 INSERT INTO SQL语法:
INSERT INTO table_name ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN );
如果数据是字符型,必须使用单引号或者双引号,如:"value"。
MySQL 查询数据
MySQL 数据库使用SQL SELECT语句来查询数据。
你可以通过 mysql> 命令提示窗口中在数据库中查询数据,或者通过PHP脚本来查询数据。
语法
以下为在MySQL数据库中查询数据通用的 SELECT 语法:
SELECT column_name,column_name
FROM table_name[WHERE Clause][OFFSET M ][LIMIT N]
7.where条件查询
语法
以下是 SQL SELECT 语句使用 WHERE 子句从数据表中读取数据的通用语法:
SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....
MariaDB [zabbix]> select * from widget;
+----------+-------------+----------+-------------------+---+----+-------+--------+
| widgetid | dashboardid | type | name | x | y | width | height |
+----------+-------------+----------+-------------------+---+----+-------+--------+
| 2 | 1 | webovr | web monitoring | 0 | 4 | 5 | 2 |
| 3 | 1 | hoststat | ec system status | 0 | 6 | 5 | 3 |
| 4 | 1 | problems | | 0 | 14 | 6 | 8 |
| 7 | 1 | hoststat | | 5 | 5 | 7 | 5 |
| 8 | 1 | syssum | | 0 | 9 | 5 | 5 |
| 9 | 1 | stszbx | | 0 | 28 | 5 | 5 |
| 10 | 1 | sysmap | Network | 0 | 22 | 12 | 6 |
| 11 | 1 | graph | E5 NS Status | 5 | 10 | 7 | 4 |
| 16 | 1 | actlog | Action Log | 6 | 14 | 6 | 8 |
| 17 | 1 | graph | CN-Order | 0 | 0 | 5 | 4 |
| 18 | 1 | graph | CN PV | 5 | 0 | 7 | 5 |
+----------+-------------+----------+-------------------+---+----+-------+--------+
11 rows in set (0.00 sec)
MariaDB [zabbix]> select * from widget where widgetid = '2';
+----------+-------------+--------+----------------+---+---+-------+--------+
| widgetid | dashboardid | type | name | x | y | width | height |
+----------+-------------+--------+----------------+---+---+-------+--------+
| 2 | 1 | webovr | web monitoring | 0 | 4 | 5 | 2 |
+----------+-------------+--------+----------------+---+---+-------+--------+
1 row in set (0.00 sec)
MariaDB [zabbix]>
8.null字段的判断
为了处理这种情况,MySQL提供了三大运算符:
IS NULL: 当列的值是 NULL,此运算符返回 true。
IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
<=>: 比较操作符(不同于=运算符),当比较的的两个值为 NULL 时返回 true。
MariaDB [zabbix]> describe users;
+----------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------------+------+-----+---------+-------+
| userid | bigint(20) unsigned | NO | PRI | NULL | |
| alias | varchar(100) | NO | UNI | | |
| name | varchar(100) | NO | | | |
| surname | varchar(100) | NO | | | |
| passwd | char(32) | NO | | | |
| url | varchar(255) | NO | | | |
| autologin | int(11) | NO | | 0 | |
| autologout | varchar(32) | NO | | 15m | |
| lang | varchar(5) | NO | | en_GB | |
| refresh | varchar(32) | NO | | 30s | |
| type | int(11) | NO | | 1 | |
| theme | varchar(128) | NO | | default | |
| attempt_failed | int(11) | NO | | 0 | |
| attempt_ip | varchar(39) | NO | | | |
| attempt_clock | int(11) | NO | | 0 | |
| rows_per_page | int(11) | NO | | 50 | |
+----------------+---------------------+------+-----+---------+-------+
16 rows in set (0.00 sec)
MariaDB [zabbix]> select * from users where userid is null;
Empty set (0.00 sec)
MariaDB [zabbix]> select * from users where userid is not null;
9.select distinct去掉重复查询结果
SELECT DISTINCT column_name,column_name
FROM table_name;
10.使用order by对查询结果排序
语法
以下是 SQL SELECT 语句使用 ORDER BY 子句将查询数据排序后再返回数据:
SELECT field1, field2,...fieldN table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]]
你可以使用任何字段来作为排序的条件,从而返回排序后的查询结果。
你可以设定多个字段来排序。
你可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下,它是按升序排列。
你可以添加 WHERE...LIKE 子句来设置条件。
实例
尝试以下实例,结果将按升序及降序排列。
SQL 排序
mysql> use RUNOOB;
Database changed
mysql> SELECT * from runoob_tbl ORDER BY submission_date ASC;
+-----------+---------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+---------------+---------------+-----------------+
| 3 | 学习 Java | RUNOOB.COM | 2015-05-01 |
| 4 | 学习 Python | RUNOOB.COM | 2016-03-06 |
| 1 | 学习 PHP | 菜鸟教程 | 2017-04-12 |
| 2 | 学习 MySQL | 菜鸟教程 | 2017-04-12 |
+-----------+---------------+---------------+-----------------+
4 rows in set (0.01 sec)
mysql> SELECT * from runoob_tbl ORDER BY submission_date DESC;
+-----------+---------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+---------------+---------------+-----------------+
| 1 | 学习 PHP | 菜鸟教程 | 2017-04-12 |
| 2 | 学习 MySQL | 菜鸟教程 | 2017-04-12 |
| 4 | 学习 Python | RUNOOB.COM | 2016-03-06 |
| 3 | 学习 Java | RUNOOB.COM | 2015-05-01 |
+-----------+---------------+---------------+-----------------+
4 rows in set (0.01 sec)
读取 runoob_tbl 表中所有数据并按 submission_date 字段的升序排列。
11.使用limit截取查询结果
实例
SELECT * FROM Websites LIMIT 2;
MariaDB [zabbix]> select * from users order by name limit 5,7;
+--------+-------+----------------+----------------+----------------------------------+-----+-----------+------------+-------+---------+------+---------+----------------+--------------+---------------+---------------+
| userid | alias | name | surname | passwd | url | autologin | autologout | lang | refresh | type | theme | attempt_failed | attempt_ip | attempt_clock | rows_per_page |
+--------+-------+----------------+----------------+----------------------------------+-----+-----------+------------+-------+---------+------+---------+----------------+--------------+---------------+---------------+
| 23 | cy79 | Cloud.B.Yao | Cloud.B.Yao | 5fce1b3e34b520afeffb37ce08c7cd66 | | 1 | 0 | en_GB | 30s | 3 | default | 0 | 10.16.167.47 | 1515148581 | 50 |
| 25 | ds80 | Daniel.Y.Shen | Daniel.Y.Shen | 5fce1b3e34b520afeffb37ce08c7cd66 | | 0 | 0 | en_GB | 30s | 3 | default | 0 | | 0 | 50 |
| 27 | dt10 | Danny.W.Tang | Danny.W.Tang | 5fce1b3e34b520afeffb37ce08c7cd66 | | 0 | 0 | en_GB | 30s | 3 | default | 0 | 10.18.3.52 | 1520846135 | 50 |
| 42 | el48 | Elvis.Lin | Elvis.Lin | 5fce1b3e34b520afeffb37ce08c7cd66 | | 0 | 0 | en_GB | 30s | 1 | default | 0 | | 0 | 50 |
| 45 | gg82 | Grant Gao | Grant Gao | 5fce1b3e34b520afeffb37ce08c7cd66 | | 0 | 0 | en_GB | 30s | 3 | default | 0 | | 0 | 50 |
| 28 | gd03 | Gus.wook.W.Dai | Gus.wook.W.Dai | 5fce1b3e34b520afeffb37ce08c7cd66 | | 1 | 0 | en_GB | 30 | 3 | default | 0 | 10.16.167.42 | 1530866208 | 50 |
| 36 | jw04 | Jack wong | | 5fce1b3e34b520afeffb37ce08c7cd66 | | 0 | 0 | en_GB | 30s | 3 | default | 0 | | 0 | 50 |
+--------+-------+----------------+----------------+----------------------------------+-----+-----------+------------+-------+---------+------+---------+----------------+--------------+---------------+---------------+
7 rows in set (0.00 sec)
12.update语法
语法
以下是 UPDATE 命令修改 MySQL 数据表数据的通用 SQL 语法:
UPDATE table_name SET field1=new-value1, field2=new-value2[WHERE Clause]
你可以同时更新一个或多个字段。
你可以在 WHERE 子句中指定任何条件。
你可以在一个单独表中同时更新数据。
当你需要更新数据表中指定行的数据时 WHERE 子句是非常有用的。
实例
以下实例将更新数据表中 runoob_id 为 3 的 runoob_title 字段值:
SQL UPDATE 语句:
mysql> UPDATE runoob_tbl SET runoob_title='学习 C++' WHERE runoob_id=3;
Query OK, 1 rows affected (0.01 sec)
mysql> SELECT * from runoob_tbl WHERE runoob_id=3;
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
| 3 | 学习 C++ | RUNOOB.COM | 2016-05-06 |
+-----------+--------------+---------------+-----------------+
1 rows in set (0.01 sec)
从结果上看,runoob_id 为 3 的 runoob_title 已被修改。