mysql客户端程序:
命令行交互式客户端程序:mysql
mysql
mysql [OPTIONS] [database]
# mysql --help
配置文件的读取次序:/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf rpm安装的读取方法
如果有相同的内容最后读取的会覆盖前面读取的,不同版本的顺序可能不同,用mysql --help查看
我们自己用二进制包安装的路径读取方法
[root@yph7s ~]# /usr/local/mysql/bin/mysqld --verbose --help ---
常用选项:---这些选项后可以有空格也可无空格
-uUSERNAME:用户名,默认为root;
-hHOST:远程主机(即mysql服务器)地址,默认为localhost;
-p[PASSWORD]:USERNAME所表示的用户的密码;可省略, 默认为空;
mysql的用户账号由两部分组成,即:'USERNAME'@'HOST';
其中HOST用于限制此用户可通过哪些远程主机连接当前的mysql服务;
HOST主机的表示方式,支持使用通配符:”%”,和”_”
%:匹配任意长度的任意字符;
172.16.%.%, 172.16.0.0/16
_:匹配任意单个字符;
-Ddb_name:连接到服务器端之后,设定默认数据库;如果连接后执行命令时没有指定某数据库,就默认在此数据库下工作。
[root@yph7s ~]# mysql -uroot -p -Dmysql
MariaDB [mysql]> ------中括号的指默认查看的数据库
之后可以用use db_name 切换数据库
-e'SQL_COMMAND;':连接至服务器并让其执行此命令后直接退出;不进入交互式模式
[root@yph7s ~]# mysql -e'SHOW DATABASES;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
客户端命令:本地执行
mysql> help ----查看有哪些本地命令
\u db_name:设定哪个库为默认数据库
\q:退出;
\d CHAR:设定新的语句结束符;
\g:go,语句结束标记;
\G:语句结束标记,结果竖排方式显式;
\s:status,连接状态
\c:类似shell的Ctrl+c;
注意:客户端每个语句必须有语句结束符,默认为分号(;),不加分号可能要等你输入分号,否则无法进一步执行。
MariaDB [(none)]> \s
mysql Ver 15.1 Distrib 5.5.44-MariaDB, for Linux (x86_64) using readline 5.1-----客户端版本
Connection id:9 -----当前连接的线程id
Current database: ------------当前默认数据库;现在是没指数据库
Current user:root@localhost -------当前登陆用户
SSL:Not in use -----有没有ssl方式的远程连接
Using delimiter:; --------默认结束符为分号
Server:MariaDB
Server version:5.5.46-MariaDB-log MariaDB Server---服务器版本
Protocol version:10
Connection:Localhost via UNIX socket
Server characterset:latin1 ------服务器字符集
Db characterset:latin1 --------数据库字符集
Client characterset:utf8 -----客户端字符集
Conn. characterset:utf8 ------连接字符集
UNIX socket:/tmp/mysql.sock ----Unix sock路径
Uptime:45 min 31 sec
服务端命令:通过mysql连接发往服务器执行并取回结果;
命令可以不区分大小写
DDL:数据定义语言,主要用于管理数据库组件,例如表、索引、视图、用户、存储过程
CREATE:创建 ALTER:修改 DROP:删除
DML:数据操纵语言,主要用管理表中的数据,实现数据的增、删、改、查;
INSERT:插入 DELETE:删除 UPDATE:修改 SELECT:查看
获取命令帮助:
mysql> help create
mysql> help create database
数据库管理:
CREATE:创建数据库:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name;
[DEFAULT] CHARACTER SET [=] charset_name
[DEFAULT] COLLATE [=] collation_name
创建:
MariaDB [(none)]> create schema mydb;
MariaDB [(none)]> create schema if not exists mydb;
查看指定数据库:
SHOW DATABASES LIKE ‘mysql’;
查看支持的所有字符集:SHOW CHARACTER SET
查看支持的所有排序规则:SHOW COLLATION
查看数据库支持的所有存储引擎类型:mysql> SHOW ENGINES;
数据库是什么:
我们手动在安装时指定数据库目录下创建一个mydb目录,然后把属主属组改成mysql,这样再查看数据库时就能看的刚才创建的目录了,现在知道数据库时什么了吧。但是这样的数据库不完整,不能用。
[root@yph7s ~]# cd /mydata/data
[root@yph7s data]# mkdir mytestdb
[root@yph7s data]# chown -R mysql:mysql mytestdb
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| |mytestdb
ALTER:修改数据库属性,一般为字符集和排序规则
ALTER {DATABASE | SCHEMA} [db_name] [DEFAULT] CHARACTER SET [=] charset_name
[DEFAULT] COLLATE [=] collation_name
DROP:删除数据库;
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
表管理:
查看表:
查看指定数据库中的表列表
MariaDB [(none)]> show tables from mysql;
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
查看某表结构:包含字段,主键等
MariaDB [mysql]> desc stu;
MariaDB [(none)]> desc testdb.mage;
| Field | Type | Null | Key | Default | Extra |
+------ --+---------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | PRI | NULL | |
| name | char(30) | NO | | NULL | |
查看指定表属性信息
MariaDB [mysql]> show table status like 'stu' \G
MariaDB [(none)]> show table status from mydb like 'mytab' \G
查看某表的存储引擎类型:
mysql> SHOW TABLE STATUS [LIKE 'tbl_name']
MariaDB [mysql]> SHOW TABLE STATUS like 'mytab'; -----不支持绝对路径mydb.mytab
创建表:
CREATE TABLE [IF NOT EXISTS] tbl_name (create_defination) [table_options]
表定义:create_defination:
字段:col_name data_type
键:键是索引的一部分
PRIMARY KEY (col1, col2, ...)主键
UNIQUE KEY (col1, col2,...)唯一键
FOREIGN KEY (column)外键
索引:
KEY|INDEX [index_name] (col1, col2,...)
表选项:
ENGINE [=] engine_name -----存储引擎类型
创建表:
MariaDB [(none)]> create table mydb.mytesttab(name int not null);
MariaDB [(none)]> create table mydb.mytab(id int unsigned not null,name char(30) not null, age tinyint unsigned, gender enum('f','m'),primary key(id,gender),class char(30));
表,名为mytab;有id ,name, age, gender 四个字段
id int unsigned not null:字段id,无符号整形,2^32个字符长度,并且不能为空
name char(30) not null:字段name,定长30位字符,非空
age tinyint unsigned:字段age,
gender enum('f','m'):字段gender性别,枚举型,只能从f和m选一。
primary key(id,gender):定义主键,把id和name定义为主键
默认数据库为mysql,所以可以不指定;
在指定数据库里创建表
MariaDB [mysql]> create table testdb.mage(name char(20) not null,age tinyint );
另外一种定义主键的方法:
(id int unsigned not null primary key,name char(30) not null)
MariaDB [mysql]> desc mydb.mytab; -----查看表结构
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | PRI | NULL | |
| name | char(30) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('f','m') | NO | PRI | f | |
+--------+---------------------+------+-----+---------+-------+
MariaDB [mysql]> drop table mytab; ---删除表
MariaDB [mysql]> show table status \G ----------查看该数据库内所有表的属性信息
MariaDB [mysql]> show table status like 'mytab' \G ---------查看指定表stu的属性信息
*************************** 1. row ***************************
Name: mytab
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2016-01-16 11:49:53
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
修改表属性:
ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name [alter_specification [, alter_specification] ...]
alter_specification:
字段:
添加字段:ADD [COLUMN] col_name data_type [FIRST | AFTER col_name ]
默认添加到最后一个字段后面,first指添加到第一个字段,after指添加到某字段后面
MariaDB [mysql]> alter table mytab add class varchar(100) not null after age;
MariaDB [mysql]> desc mytab
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| age | tinyint(3) unsigned | YES | | NULL | |
| class | varchar(100) | NO | | NULL | |
| gender | enum('f','m') | NO | PRI | f | |
+--------+---------------------+------+-----+---------+-------+
删除字段:DROP [COLUMN] col_name
MariaDB [mysql]> alter table mytab drop class;
修改字段:
修改字段名和字段属性:
CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]
仅修改字段属性:只要不改名字就可用modify
MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
键:
添加:ADD {PRIMARY|UNIQUE|FOREIGN} KEY (col1, col2,...)
MariaDB [mysql]> alter table mytab add unique key (name);
删除键:
MariaDB [mysql]> alter table mytab drop primary key;
一张表中只有一个主键,删除就不在有主键,不管主键有几个字段。
主键:DROP PRIMARY KEY
外键:DROP FOREIGN KEY fk_symbol
索引:
查看表索引:
mysql> SHOW INDEXES FROM tbl_name;
MariaDB [mysql]> show indexes from mytab;
给表添加索引:ADD {INDEX|KEY} [index_name] (col1, col2,...)
MariaDB [mysql]> alter table mytab add index ages (age); 给字段添加索引,索引名为ages;索引名与字段名可以相同
删除表索引:DROP {INDEX|KEY} index_name
MariaDB [mysql]> alter table mytab drop index ages ; ------删除式指定的是索引名,不是字段名。
修改表选项:
主要是改引擎,但是非常危险,ENGINE [=] engine_name
删除表:
DROP TABLE [IF EXISTS] tbl_name [, tbl_name] ...
MariaDB [(none)]> drop table mydb.mytab;
表的第二种创建方式:
复制表结构;
表的第三种创建方式:
复制表数据;
索引管理:
索引:要有索引名称;索引是特殊的数据结构;
查看索引:
MariaDB [(none)]> show indexes from mydb.mytab;
创建索引:
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [BTREE|HASH] ON tbl_name (col1, col2,,...)
唯一键,全文索引
btree索引,哈希索引,一般只有btree索引,哈希索引几乎很少受到创建
MariaDB [(none)]> create index name_class on mydb.mytab (name,class);
删除索引:
DROP INDEX index_name ON tbl_name
MariaDB [(none)]> drop index name_class on mydb.mytab;
DML:INSERT, DELETE, UPDATE, SELECT
INSERT INTO:
INSERT [INTO] tbl_name [(col1,...)] {VALUES|VALUE} (val1, ...),(...),...
MariaDB [(none)]> insert mydb.mytab values(1,'yang guo',36,'xikuang','m');
MariaDB [mydb]> insert mytab (id,name,class) values (2,'xiaolongnv','gumu'),(3,'jinlun','menggu');
MariaDB [mydb]> select * from mytab;
+----+------------+------+---------+--------+
| id | name | age | class | gender |
+----+------------+------+---------+--------+
| 1 | yang guo | 36 | xikuang | m |
| 2 | xiaolongnv | NULL | gumu | f |
| 3 | jinlun | NULL | menggu| f |
+----+------------+------+---------+--------+
SELECT:
(1) SELECT * FROM tbl_name;
(2) SELECT col1, col2, ... FROM tbl_name;
MariaDB [mydb]> select * from mytab;
MariaDB [mydb]> select name,class from mytab;
+------------+---------+
| name | class |
+------------+---------+
| yang guo | xikuang |
| xiaolongnv | gumu |
| jinlun | menggu |
+------------+---------+
显示时,字段可以显示为别名;
col_name AS col_alias
MariaDB [mydb]> select id as tabID,class,age from mytab; ---只是显示出的替换,文本并未替换。
+-------+---------+------+
| tabID | class | age |
+-------+---------+------+
| 1 | xikuang | 36 |
| 2 | gumu | NULL |
| 3 | menggu | NULL |
+-------+---------+------+
(3) SELECT col1, ... FROM tbl_name WHERE clause;
WHERE clause:用于指明挑选条件;
col_name 操作符 value:
age > 30;
操作符(1) :
>, <, >=, <=, ==, !=
MariaDB [mydb]> select * from mytab where id<=2;
+----+------------+------+---------+--------+
| id | name | age | class | gender |
+----+------------+------+---------+--------+
| 1 | yang guo | 36 | xikuang | m |
| 2 | xiaolongnv | NULL | gumu | f |
+----+------------+------+---------+--------+
MariaDB [mydb]> select * from mytab where name="xiaolongNV"; ----不区分大小写
+----+------------+------+-------+--------+
| id | name | age | class | gender |
+----+------------+------+-------+--------+
| 2 | xiaolongnv | NULL | gumu | f |
+----+------------+------+-------+--------+
组合条件:
and
or
not
MariaDB [mydb]> select name,class,age from mytab where age>30 and age<60;
+----------+---------+------+
| name | class | age |
+----------+---------+------+
| yang guo | xikuang | 36 |
+----------+---------+------+
操作符(2) :
BETWEEN ... AND ...
MariaDB [mydb]> select name,class,age from mytab where age between 30 and 70;
+----------+---------+------+
| name | class | age |
+----------+---------+------+
| yang guo | xikuang | 36 |
+----------+---------+------+
LIKE 'PATTERN'
NOT LIKE ‘PATTERN’
通配符:
%:任意长度的任意字符;
_:任意单个字符;
MariaDB [mydb]> select name,class from mytab where name like '%ng%';
+------------+---------+
| name | class |
+------------+---------+
| yang guo | xikuang |
| xiaolongnv | gumu |
+------------+---------+
RLIKE 'PATTERN'
MariaDB [mydb]> select name,class from mytab where name rlike '^.*ng.*$';
+------------+---------+
| name | class |
+------------+---------+
| yang guo | xikuang |
| xiaolongnv | gumu |
+------------+---------+
正则表达式对字符串做模式匹配;
IS NULL
IS NOT NULL
MariaDB [mydb]> select name,class,age from mytab where age is null;
+------------+--------+------+
| name | class | age |
+------------+--------+------+
| xiaolongnv | gumu | NULL |
| jinlun | menggu | NULL |
+------------+--------+------+
(4) SELECT col1, ... FROM tbl_name [WHERE clause] ORDER BY col_name, col_name2, ... [ASC|DESC];
ASC: 升序;
DESC: 降序;
MariaDB [mydb]> select name,id,age from mytab order by age asc;
+------------+----+------+
| name | id | age |
+------------+----+------+
| xiaolongnv | 2 | NULL |
| jinlun | 3 | NULL |
| yang guo | 1 | 36 |
+------------+----+------+
DELETE:删除表中所有行
DELETE FROM tbl_name [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]
(1) DELETE FROM tbl_name WHERE where_condition
(2) DELETE FROM tbl_name [ORDER BY ...] [LIMIT row_count]
给表添加年龄随机的100个用户,注意:是在shell中输入,不是mysql中。
for i in {1..100};do AGE=$[$RANDOM%100]; mysql -e "insert mydb.mytab (id,name,age) values($i,\"menggu$i\",$AGE);";done
MariaDB [mydb]> delete from mytab order by age desc limit 20;
UPDATE:更新所有行的属性
UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1=value1 [, col_name2=value2] ... [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]
MariaDB [mydb]> update mytab set age=age-5 order by id desc limit 10;
MariaDB [mydb]> update mytab set age=age-10 where name not like 'stu%';
用户账号及权限管理:
用户账号:'username'@'host'
host:此用户访问当前mysql服务器时,允许其通过哪些主机远程创建连接;
表示方式:IP,网络地址、主机名、通配符(%和_);
禁止检查主机名:my.cnf ----检测主机名会消耗大量系统资源
[mysqld]
skip_name_resolve = ON
创建用户账号:
CREATE USER 'username'@'host' [IDENTIFIED BY 'password'];
MariaDB [mydb]> create user 'myuser'@'172.16.%.%' identified by 'magedu';
MariaDB [(none)]> flush privileges;----------刷新授权表
[root@yph7s data]# mysql -umyuser -h172.16.59.3 -pmagedu
只有进来访问有限数据库的权限。
删除用户账号:
DROP USER ’user‘@’host' [, user@host] ...
授权:
权限级别:管理权限、数据库、表、字段、存储例程;
GRANT priv_type,... ON [object_type] db_name.tbl_name TO 'user'@'host' [IDENTIFIED BY 'password'];
给用户查看权限
MariaDB [(none)]> grant select on mydb.mytab to 'myuser'@'172.16.%.%';
MariaDB [(none)]> flush privileges;
重新用myuser登陆,此时有查看权限。
MariaDB [(none)]> select name,id from mydb.mytab where age > 70;
+--------+----+
| name | id |
+--------+----+
| sdxl16 | 16 |
| sdxl18 | 18 |
给用户写入权限
MariaDB [(none)]> grant insert on mydb.mytab to 'myuser'@'172.16.%.%';
MariaDB [(none)]> flush privileges;
切换到用户myuser
MariaDB [(none)]> insert mydb.mytab value (90,'jinlun',55,'menggu','m');
MariaDB [(none)]> select * from mydb.mytab where name like 'jinlun';
+----+--------+------+--------+--------+
| id | name | age | class | gender |
+----+--------+------+--------+--------+
| 90 | jinlun | 55 | menggu | m |
+----+--------+------+--------+--------+
MariaDB [(none)]> show grants for 'myuser'@'172.16.%.%'; ---查看某用户的所有授权
MariaDB [(none)]> revoke insert on mydb.mytab from 'myuser'@'172.16.%.%';
MariaDB [(none)]> flush privileges;
MariaDB [(none)]> show grants for 'myuser'@'172.16.%.%';
| GRANT SELECT ON `mydb`.`mytab` TO 'myuser'@'172.16.%.%'
priv_type: ALL [PRIVILEGES]
db_name.tbl_name:
*.*:所有库的所有表;
db_name.*:指定库的所有表;
db_name.tbl_name:指定库的特定表;
db_name.routine_name:指定库上的存储过程或存储函数;此时必须用object_name区分是否表名
[object_type]
TABLE
FUNCTION
PROCEDURE
查看指定用户所获得的授权:
SHOW GRANTS FOR 'user'@'host'
SHOW GRANTS FOR CURRENT_USER;
回收权限:
REVOKE priv_type, ... ON db_name.tbl_name FROM 'user'@'host';
注意:MariaDB服务进程启动时,会读取mysql库的所有授权表至内存中;
(1) GRANT或REVOKE命令等执行的权限操作会保存于表中,MariaDB此时一般会自动重读授权表,权限修改会立即生效;
(2) 其它方式实现的权限修改,要想生效,必须手动运行FLUSH PRIVILEGES命令方可;
在用rpm包安装完mysql后,加固mysql服务器,在安装完成后,运行mysql_secure_installation命令;给root加上密码。
在面试中mysql是收先被问到的一批知识之一。
数据库图形管理组件:
phpMyAdmin
运行于lamp;
Navicat
Mysql-Front
ToadForMySQL
SQLyog
安装phpMYadmin:
安装httpd php php-mysql mariadb-server
lftp 172.16.0.1/pub/Sources/source/httpd/phpMyAdmin mget 放到httpd的根目录下,unzip展开
[root@yph7s html]# unzip phpMyAdmin-4.4.14.1-all-languages.zip
[root@yph7s html]# ln -sv phpMyAdmin-4.4.14.1-all-languages pma
‘pma’ -> ‘phpMyAdmin-4.4.14.1-all-languages’
[root@yph7s html]# cd pma
[root@yph7s pma]# cp config.sample.inc.php config.inc.php
[root@yph7s pma]# openssl rand -base64 15
qy7Kc61lR73hl6WjBb56
[root@yph7s pma]# vim config.inc.php -----把刚才的代码填到这里
$cfg['blowfish_secret'] = 'qy7Kc61lR73hl6WjBb56'; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */
[root@yph7s pma]# systemctl start httpd.service
[root@yph7s pma]# yum install -y php-mbstring
[root@yph7s pma]# systemctl reload httpd.service
浏览器打开。浏览器输入pma不可以,但是输入目录原名可以,不知为啥。
创建用户:
MariaDB [(none)]> create database pmadb;
MariaDB [(none)]> grant all on pmadb.* to 'pma'@'172.16.%.%' identified by 'magedu';
MariaDB [(none)]> grant all privileges on pmadb.* to 'pma'@'172.16.%.%' identified by 'magedu';
数据类型:
表:行和列
创建表:定义表中的字段;
定义字段时,关键的一步即为确定其数据类型;
用于确定:数据存储格式、能参与运算种类、可表示的有效的数据范围;
整形:0-255或-127到128
字符型:字符集
码表:在字符和二进制数字之间建立映射关系;
MariaDB [(none)]> show character set; ----查看所有可用字符集
种类:
字符型:
定长字符型:会导致空间浪费
CHAR(#):不区分字符大小写;#表示定义多少个字符
BINARY(#):区分字符大小写
变长字符型:
VARCHAR(#):不区分字符大小写;#表示最长多少个字符,最终占用#+1个字符,因为有个结束符。
VARBINARY(#):区分字符大小写;
对象存储:只存一个指针
TEXT:2的32次方的字节
BLOB:
内置类型:
SET:集合,例如集合中有啊,a,b,c,d;必须是这些字符进行组合后的一个
ENUM:枚举,例如性别有两个选项,必须从中选择一个,不能有其他的
数值型:
精确数值型:
INT(TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT)
近似数值型:
FLOAT
DOBULE
日期时间型:
日期型:DATE
时间型:TIME
日期时间型:DATETIME
时间戳:TIMESTAMP
年份:YEAR(2), YEAR(4)
数据类型有修饰符:
UNSIGNED:无符号;
NOT NULL:非空;
DEFAULT value:默认值;