mysql 安装 和简单使用技巧1-12336621
# mysql_install_db--user=mysql -datadir=/var/lib/mysql 初始化数据vncviewer172.40.50.117：6000
内存 cpu 硬盘
# yum grouplist
mysql（3306）
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1.用rpm装 使用光盘封装好的rpm包
# yum -y install mysql-server
..........................
# rpm -q mysql-server(查看是否安装和版本号)
mysql-server-5.1.73-5.el6_6.x86_64
# yum list |grep mysql-server
mysql-server.x86_64 5.1.73-5.el6_6 @abc
# servicemysqld start
初始化 MySQL 数据库： WARNING: The host 'host.tedu.cn' could not be looked up with resolveip.
# service mysqld status
mysqld (pid21119) 正在运行...
# chkconfig mysqld on
# chkconfig --listmysqld
mysqld 0:关闭1:关闭2:启用3:启用4:启用5:启用6:关闭
# grep mysql /etc/passwd
mysql:x:27:27:MySQL Server:/var/lib/mysql:/bin/bash
/var/lib/mysql (数据库目录) 下面目录和文件用户和组都是mysql
/etc/my.cnf 主配置文件
/var/log/mysqld.log （日志文件）
/var/run/mysqld/mysqld.conf（pid路径）
# which mysql
/usr/bin/mysql
# rpm -qf /usr/bin/mysql
mysql-5.1.73-5.el6_6.x86_64
低版本：
进程名和服务名都是mysqld
传输协议tcp
rmp包装 进程所有者和所属组是mysql这个用户
——--------------------------------------------------------------------
低版本：
（自己连：）mysql -hlocalhost-uroot -p (本机登也可 mysql)
（别人练：）# mysql -hip
-------------------------------------------------------------------------
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2.从官网上下载安装 （先把之前rpm装的删除）
1）删除之前安装的
# service mysqld stop
停止 mysqld： [确定]
# rpm -e --nodeps mysql-server mysql （--nodeps忽略依赖关系 就是依赖关系包不卸载 mysql连接工具卸载掉 因为下面装的会有）
# rm -fr/etc/my.cnf
# rm -fr /var/lib/mysql/*
2）把官网下的解包安装
# tar -xvf MySQL-5.6.rpm.tar
MySQL-shared-5.6.15-1.el6.x86_64.rpm
MySQL-devel-5.6.15-1.el6.x86_64.rpm
MySQL-embedded-5.6.15-1.el6.x86_64.rpm
MySQL-test-5.6.15-1.el6.x86_64.rpm
MySQL-server-5.6.15-1.el6.x86_64.rpm
MySQL-shared-compat-5.6.15-1.el6.x86_64.rpm
MySQL-client-5.6.15-1.el6.x86_64.rpm
# rpm -Uvh MySQL-*.rpm （因为上面只删除了主配置文件，和mysql-server这个包 所有的依赖包还在 所以大U直接升级）
# rpm -qa |grep -i mysql
MySQL-test-5.6.15-1.el6.x86_64
MySQL-server-5.6.15-1.el6.x86_64
MySQL-client-5.6.15-1.el6.x86_64
MySQL-embedded-5.6.15-1.el6.x86_64
MySQL-shared-5.6.15-1.el6.x86_64
perl-DBD-MySQL-4.013-3.el6.x86_64
MySQL-devel-5.6.15-1.el6.x86_64
MySQL-shared-compat-5.6.15-1.el6.x86_64
# service mysql start
Starting MySQL.. SUCCESS!
# netstat -anptu |grep :3306
tcp 0 0 :::3306 :::* LISTEN 21509/mysqld
# which mysql
/usr/bin/mysql
# rpm -qf /usr/bin/mysql
MySQL-client-5.6.15-1.el6.x86_64
# rpm -q MySQL-client (高版本mysql包名叫这）
MySQL-client-5.6.15-1.el6.x86_64
3）高版本使用：会有密码，在/root/.mysql_secret下 进去需要该密码才能使用。
# cat /root/.mysql_secret
# The random password set for the root user at Wed Feb 22 07:54:48 2017 (local time): 1eFb6CeJ
# mysql -hlocalhost -uroot -p1eFb6CeJ
mysql> set password forroot@"localhost"=password("123");
Query OK, 0 rows affected (0.03 sec)
____________________________________________________________________________________________________
# ls
auto.cnf ib_logfile1performance_schemaRPM_UPGRADE_HISTORY
ibdata1 mysql proxe.tedu.cn.err RPM_UPGRADE_MARKER-LAST
ib_logfile0mysql.sock proxe.tedu.cn.pid test
# mysql -hlocalhost -uroot -pHYwSHnwN
Warning: Using a password on the command line interface can be insecure.
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
# ln -s /var/lib/mysql/mysql.sock/tmp/mysql.sock或创建一个这样的文件在这
________________________________________________________________________________________________________
高版本：
服务名mysql
进程名mysqld（
http传输协议：tcp
rpm包装的，进程所有者和所有组都是mysql
数据库目录：/var/lib/mysql
主配置文件：/etc/my.cnf
/var/run/mysqld/mysqld.conf 主配置文件
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
数据迁移
mysql ---->mariadb
关系型数据库mysql难度大
非关系型数据库 redis简单 用户名密码
使用数据库服务
启动
停止
状态
连接
DDL 就是删表删库建表建库
以文件的形式存放在数据库目录下/var/lib/mydql
DB (/var/lib/mysql)
DBS(装列数据库和操作系统)
DBMS（mysql)
E-R数据模型
字符类型：charvarcharblobtext
数值类型：inttinyintsmallint
日期时间类型：year datetimedatetime
枚举类型 enmu（单选项）
set(多个)
字符：
char 255 定长不指默认是（1）
varchar 65532 变长一定要指
大文本类型blob/text不要指
数值：
整型 微小整形（-128—1270-255）tinyint默认有符号 tinyint unsigned(无符号)
小整形 中整型 大整形 极大整型
浮点型 float(单精度）double（双）
正整数 12
负整数-12
int（2）显示宽度 默认11位， 没到这么多位会补。 zerofill,
日期时间：
year 年 YYYY2017 --2155
date日期YYYYMMDD20170223
time时间HHMMSS 165425
datetime / timestamp
YYYYMMDDHHMMSS
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
基本操做：
# mysql -uroot -p123
mysql> show databases;
mysql> use yy;
mysql> select database();
mysql> create database y;
mysql> create table t1(
-> name char(4)
-> );
mysql> desc t1;
mysql> insert into t1
-> values
-> ("tom"
-> );
mysql> select * from t1;
mysql> delete from t1;
mysql> drop table t1;
mysql> drop database y;
______________________________________________________________________________________________
对表的操作：
————————————————————————————————————————
字符类型 (姓名家庭地址)
char 定长 desc t2;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| name| char(1) | YES| | NULL | |
| home| varchar(5) | YES| | NULL | |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into t2
-> values
-> ("tom","zhangh");
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> insert into t2values ("t","zhangh");
ERROR 1406 (22001): Data too long for column 'home' at row 1
mysql> insert into t2values ("t","zhang");
Query OK, 1 row affected (0.11 sec)
mysql> select * from t2;
+------+-------+
| name | home|
+------+-------+
| t | zhang |
+------+-------+
字符类型：char 最长不超过255个字符，默认不指是1，输值时不能对于指定字符但可以少于它。
varshar 最长不超65532个字符，默认不指报错，输值时不能对于指定字符但可以少于它。
数据比较大时，用blob,和text, 输入字符数据时要用双引引上。
————————————————————————————————————————————
整数类型
————————
根据存储数值的范围又分为：
有符号 无符号
tinyint微小整型 -128~127 0~255
smallint
MEDIUMINT
int
bigint
int（2）显示宽度 默认11位， 没到这么多位会补。 int(2) zerofill,
tinyint 默认是有符号数值为：-128--127 ，无符号数值为0--255 需要接unsigned（没有负数）
当碰到小数都四舍五入。
mysql> create table t4(
-> level tinyint);
Query OK, 0 rows affected (0.76 sec)
mysql> insert into t4values (128);
mysql> insert into t4values (127);
Query OK, 1 row affected (0.05 sec)
mysql> insert into t4values (-170);
ERROR 1264 (22003): Out of range value for column 'level' at row 1
mysql> insert into t4values (17.54);
Query OK, 1 row affected (0.05 sec)
mysql> insert into t4values (17.44);
Query OK, 1 row affected (0.08 sec)
mysql> select * from t4;
+-------+
| level |
+-------+
| 127 |
| 18 |
| 17 |
+-------+
3 rows in set (0.00 sec)
mysql> create table t7( age int(3) zerofill);int(也可默认不写）
mysql> insert into t8value (1.5);
Query OK, 1 row affected (0.08 sec)
mysql> insert into t8value (2.8);
Query OK, 1 row affected (0.08 sec)
mysql> insert into t8value (3.2);
Query OK, 1 row affected (0.10 sec)
mysql> insert into t8value (5452);
Query OK, 1 row affected (0.05 sec)
mysql> insert into t8value (54);
Query OK, 1 row affected (0.03 sec)
mysql> select * from t8;
+------+
| age|
+------+
|002 |
|003 |
|003 |
| 5452 |
|054 |
【int 后接zerofill 给定显示宽度为3当没达到时会用0补齐】
float(n,m)浮点型
mysql> create table t9( age float(7,2));
Query OK, 0 rows affected (0.56 sec)
mysql> insert into t9 value(12345.1);
Query OK, 1 row affected (0.04 sec)
mysql> insert into t9 value(12345);
Query OK, 1 row affected (0.03 sec)
mysql> insert into t9 value(15);
Query OK, 1 row affected (0.03 sec)
mysql> insert into t9 value(15.215);
Query OK, 1 row affected (0.23 sec)
mysql> insert into t9 value(-15.215);
Query OK, 1 row affected (0.03 sec)
mysql> select * from t9;
+----------+
| age |
+----------+
| 12345.10 |
| 12345.00 |
| 15.00 |
| 15.21 |
| -15.21 |
+----------+
数值类型的宽度与字符类型宽度的区别？
数值类型的宽度是显示宽度，不能够控制给字段赋值的大小，字段值的大小由字段类型决定。
日期时间类型 (生日注册时间入职时间)
年 year YYYY 2016
01-6920XX
70-9919XX
00 0000
日期 date YYYYMMDD20161219
时间 time HHMMSS 144518
日期时间 （ 约会时间 ）
datetime / timestamp(当给前面设置一个时间值是，它会自动取当前值)
YYYYMMDDHHMMSS
20170214183018
datetime 与 timestamp 的区别？
当不给timestamp类型的字段赋值时，用系统当前的时间给字段赋值。
createtablet16(
time1timestamp,
time2datetime
);
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++=
mysql> create table t17 ( time1year, time2 date, time3 time, time5 datetime, time6 time );
Query OK, 0 rows affected (0.70 sec)
mysql> insert into t17 value (2017, 20160721, 141223, 20170721151515,151615);
Query OK, 1 row affected (0.04 sec)
mysql> select * from t17;
+-------+------------+----------+---------------------+----------+
| time1 | time2 | time3 | time5 | time6
+-------+------------+----------+---------------------+----------+
|2017 | 2016-07-21 | 14:12:23 | 2017-07-21 15:15:15 | 15:16:15 |
+-------+------------+----------+---------------------+----------+
1 row in set (0.00 sec)
++++++++
mysql> create table t19 ( time1 timestamp, time2 datetime );
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-----------------------------+
| time1 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| time2 | datetime| YES| | NULL | |
+-------+-----------+------+-----+-------------------+-----------------------------+
mysql> insert into t19 values(20171219103003,20160521022223);
Query OK, 1 row affected (0.03 sec)
mysql> select * from t19;
+---------------------+---------------------+
| time1 | time2 |
+---------------------+---------------------+
| 2017-12-19 10:30:03 | 2016-05-21 02:22:23 |
+---------------------+---------------------+
1 row in set (0.00 sec)
+++++++
mysql> insert into t19(time1) value(20190512131452);
Query OK, 1 row affected (0.11 sec)
mysql> select * from t19;
+---------------------+---------------------+
| time1 | time2 |
+---------------------+---------------------+
| 2017-12-19 10:30:03 | 2016-05-21 02:22:23 |
| 2019-05-12 13:14:52 | NULL |
+---------------------+---------
++++
mysql> create table t20( name char(10), ageint, tim1 time, tim2 date);
mysql> insert into t20(name,tim1) value("tom",151516);
mysql> select * from t20;
+------+------+----------+------+
| name | age| tim1 | tim2 |
+------+------+----------+------+
| tom| NULL | 15:15:16 | NULL |
+------+------+----------+------+
1 row in set (0.00 sec)
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
使用时间函数获取时间给日期时间类型字段赋值?
now()获取当前系统时间
year()获取年份
date() 获取日期
month() 获取月份
day() 获取日期（几号）
time()获取时间
+++++++++++++++++++
select now();
selectyear( now() );
selectmonth(now());
select day (now());
aelect date(now());
selectyear( 20191224 );
selectdate( now() );
+++++++++++++++++++++++++++++++++++++
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2017-02-22 18:39:23 |
+---------------------+
1 row in set (0.00 sec)
mysql> select year(now());
+-------------+
| year(now()) |
+-------------+
| 2017 |
+-------------+
1 row in set (0.00 sec)
mysql> select year(20191224);
+----------------+
| year(20191224) |
+----------------+
| 2019 |
+----------------+
1 row in set (0.00 sec)
mysql> select date (now());
+--------------+
| date (now()) |
+--------------+
| 2017-02-22 |
+--------------+
1 row in set (0.00 sec)
mysql> select month(now());
+--------------+
| month(now()) |
+--------------+
| 2 |
+--------------+
1 row in set (0.00 sec)
mysql> select day(now());
+------------+
| day(now()) |
+------------+
| 22 |
+------------+
1 row in set (0.00 sec)
mysql> select date(now());
+-------------+
| date(now()) |
+-------------+
| 2017-02-22|
+-------------+
1 row in set (0.00 sec)
+++++++++++++++++++++++++++++++++++++++
insert intot15values("lili",21,18800.88,093000,20171008,1995,now());
insert intot15values("jerry",29,28800.88,now(),now(),now(),now());
insert intot15values("tom",21,18800.88,time(20171224201818),date(20171224201818),year(now()),now());
枚举类型（爱好 性别 专业 ）
字段的值只能在列举的范围内选择
enum(值列表)单选
set(值列表) 多选
create tablet177(
namechar(10),
sex enum(0,1),
likesset("book","game","film","music")
);
create tablet17(
namechar(10),
sex enum("boy","girl","no"),
likesset("book","game","film","music")
);
desct17;
insertintot17values("bob","boy","woman,game");
insertintot17values("bob","boy","book,game");
insertintot17values("alic",3,"game");
select*fromt17;
createtablet5( name text );
createtablet5( name blob );
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
做一个学生表：
mysql> create table student( name char(10), age tinyint unsigned, level float(3,2), pay float(7,2), sex enum("man","woman"), likes set("book","music","film"), homeadder char(10), phone bigint,numint(2) zerofill );
mysql> desc student
-> ;
+-----------+----------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------------------------+------+-----+---------+-------+
| name | char(10) | YES| | NULL | |
| age | tinyint(3) unsigned | YES| | NULL | |
| level | float(3,2) | YES| | NULL | |
| pay | float(7,2) | YES| | NULL | |
| sex | enum('man','woman') | YES| | NULL | |
| likes | set('book','music','film') | YES| | NULL | |
| homeadder | char(10) | YES| | NULL | |
| phone | bigint(20) | YES| | NULL | |
| num | int(2) unsigned zerofill | YES| | NULL | |
+-----------+----------------------------+------+-----+---------+-------+
9 rows in set (0.00 sec)
mysql> insert into student value ("zhangsbing", 25, 1.711, 21203.222, 1, "book,music","shanghai", 18500202211, 2 );
mysql> mysql> select * from student;
+------------+------+-------+----------+------+------------+-----------+-------------+------+
| name | age| level | pay | sex| likes | homeadder | phone | num|
+------------+------+-------+----------+------+------------+-----------+-------------+------+
| zhangsbing | 25 |1.71 | 21203.22 | man| book,music | shanghai| 18500202211 | 02 |
+------------+------+-------+----------+------+------------+-----------+-------------+------+
1 row in set (0.00 sec)
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
修改表结构
alter table 表名 执行动作；
mysql> desc student;
+-----------+----------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------------------------+------+-----+---------+-------+
| name | char(10) | YES| | NULL | |
| age | tinyint(3) unsigned | YES| | NULL | |
| level | float(3,2) | YES| | NULL | |
| pay | float(7,2) | YES| | NULL | |
| sex | enum('man','woman') | YES| | NULL | |
| likes | set('book','music','film') | YES| | NULL | |
| homeadder | char(10) | YES| | NULL | |
| phone | bigint(20) | YES| | NULL | |
| num | int(2) unsigned zerofill | YES| | NULL | |
+-----------+----------------------------+------+-----+---------+-------+
9 rows in set (0.00 sec)
________________
add 添加新字段
add字段名 类型(宽度)；
add字段名 类型(宽度)约束条件；
add字段名 类型(宽度)约束条件first；
add字段名 类型(宽度)约束条件after字段名；
mysql>> -> add
->> -> add
->> -> ;
mysql> mysql> select * from student;
+---------+------------+------+-------+----------+------+------------+-----------+-------------+------+--------------+
|> +---------+------------+------+-------+----------+------+------------+-----------+-------------+------+--------------+
| NSD1611 | zhangsbing | 25 |1.71 | 21203.22 | man| book,music | shanghai| 18500202211 | 02 | 08:30:00 |
+---------+------------+------+-------+----------+------+------------+-----------+-------------+------+--------------+
1 row in set (0.00 sec)
mysql>> mysql> select * from student;
+---------+-------------+------------+------+-------+----------+------+------------+-----------+-------------+------+--------------+
|> +---------+-------------+------------+------+-------+----------+------+------------+-----------+-------------+------+--------------+
| NSD1611 | nongyedaxue | zhangsbing | 25 |1.71 | 21203.22 | man| book,music | shanghai| 18500202211 | 02 | 08:30:00 |
+---------+-------------+------------+------+---
++++++++++++++++
drop删除字段
drop字段名
alter table t1dropname，dropsex;
mysql>> mysql> select * from student;
+---------+-------------+------+-------+----------+------------+-----------+-------------+------+--------------+
|> +---------+-------------+------+-------+----------+------------+-----------+-------------+------+--------------+
| NSD1611 | nongyedaxue | 25 |1.71 | 21203.22 | book,music | shanghai| 18500202211 | 02 | 08:30:00 |
+---------+-------------+------+-------+----------+------------+-----------+-------------+------+--------------+
mysql>> mysql> mysql> select * from student;
+---------+-------------+------+-----+-------+----------+------------+-----------+-------------+------+--------------+
|> +---------+-------------+------+-----+-------+----------+------------+-----------+-------------+------+--------------+
| NSD1611 | nongyedaxue | 25 | boy |1.71 | 21203.22 | book,music | shanghai| 18500202211 | 02 | 08:30:00 |
+---------+-------------+------+-----+-------+----------+------------+-----------+-------------+------+--------------
mysql>> -> modify
-> sex enum("boy","girl","no") not null default "no";
mysql> mysql> select * from student;
+---------+-------------+------+-----+-------+----------+------------+-----------+-------------+------+--------------+
|> +---------+-------------+------+-----+-------+----------+------------+-----------+-------------+------+--------------+
| NSD1611 | nongyedaxue | 25 | boy |1.71 | 21203.22 | book,music | shanghai| 18500202211 | 02 | 08:30:00 |
+---------+-------------+------+-----+-------+----------+------------+-----------+-------------+------+--------------+
1 row in set (0.00 sec)
mysql> desc student;
+--------------+----------------------------+------+-----+-------------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------------------------+------+-----+-------------+-------+
|> | university | char(20) | YES| | nongyedaxue | |
| age | tinyint(3) unsigned | YES| | NULL | |
| sex | enum('boy','girl','no') | NO | | no | |
| level | float(3,2) | YES| | NULL | |
| pay | float(7,2) | YES| | NULL | |
| likes | set('book','music','film') | YES| | NULL | |
| homeadder | char(10) | YES| | NULL | |
| phone | bigint(20) | YES| | NULL | |
| num | int(2) unsigned zerofill | YES| | NULL | |
|> +--------------+----------------------------+------+-----+-------------+-------+
页:
[1]