|
mysql客户端程序： 命令行交互式客户端程序：mysql mysql mysql [OPTIONS] [database] # mysql --help 配置文件的读取次序：/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf rpm安装的读取方法 如果有相同的内容最后读取的会覆盖前面读取的，不同版本的顺序可能不同，用mysql --help查看 我们自己用二进制包安装的路径读取方法 [iyunv@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：连接到服务器端之后，设定默认数据库；如果连接后执行命令时没有指定某数据库，就默认在此数据库下工作。 [iyunv@yph7s ~]# mysql -uroot -p -Dmysql MariaDB [mysql]> ------中括号的指默认查看的数据库 之后可以用use db_name 切换数据库 -e'SQL_COMMAND;'：连接至服务器并让其执行此命令后直接退出；不进入交互式模式 [iyunv@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，这样再查看数据库时就能看的刚才创建的目录了，现在知道数据库时什么了吧。但是这样的数据库不完整，不能用。 [iyunv@yph7s ~]# cd /mydata/data [iyunv@yph7s data]# mkdir mytestdb [iyunv@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;----------刷新授权表 [iyunv@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展开 [iyunv@yph7s html]# unzip phpMyAdmin-4.4.14.1-all-languages.zip [iyunv@yph7s html]# ln -sv phpMyAdmin-4.4.14.1-all-languages pma ‘pma’ -> ‘phpMyAdmin-4.4.14.1-all-languages’ [iyunv@yph7s html]# cd pma [iyunv@yph7s pma]# cp config.sample.inc.php config.inc.php [iyunv@yph7s pma]# openssl rand -base64 15 qy7Kc61lR73hl6WjBb56 [iyunv@yph7s pma]# vim config.inc.php -----把刚才的代码填到这里 $cfg['blowfish_secret'] = 'qy7Kc61lR73hl6WjBb56'; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */ [iyunv@yph7s pma]# systemctl start httpd.service [iyunv@yph7s pma]# yum install -y php-mbstring [iyunv@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：默认值；
| 
QQ群⑧：
窥视卡
雷达卡
发表于 2016-1-19 08:33:41
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡