用户名 + 主机域
- mysql> select user,host,password from mysql.user;
- +--------+------------+-------------------------------------------+
- | user | host | password |
- +--------+------------+-------------------------------------------+
- | root | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
- | root | 127.0.0.1 | |
- | znix | 172.16.1.% | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
- | clsn | 172.16.1.% | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
- | root | 10.0.0.1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
- +--------+------------+-------------------------------------------+
- 6 rows in set
1、用户登录
2、用于管理数据库及数据
定义用户:用户名 + 主机域,密码
定义权限:对不同的对象进行权限 (角色) 定义
命令:
- grant权限on权限范围to用户identified by '密码'
权限
- 对数据库的读、写等操作
- (insert update、select、delete、drop、create等)
角色
- 数据库定义好的一组权限的定义
- (all privileges、replication slave等)
权限范围
- 全库级别: *.*
- 单库级别:clsn.*
- 单表级别:clsn.t1
用户
- 'clsn'@'localhost' 本地
- 'clsn'@'192.168.66.149'
- 'clsn'@'192.168.66.%'
- 'clsn'@'192.168.66.14%'
用户只能通过 10.0.0.0/24 网段访问,用户名为 clsn 密码为 123
这个用户只能对 clsn 数据库下的对象进行增 insert create、改 update 、查 select;
创建命令:
- grant select,create,insert,update on clsn.* to 'clsn'@'10.0.0.%' identified by '123';
查看用户权限
- mysql> show grants for clsn@'172.16.1.%'\G
- *************************** 1. row ***************************
- Grants for clsn@172.16.1.%: GRANT USAGE ON *.* TO 'clsn'@'172.16.1.%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'
- 1 row in set (0.00 sec)
查看当前存在的用户:
- select user,host from mysql.user;
创建用户语法
- CREATE USER '用户'@'主机'IDENTIFIED BY '密码';
示例:
- create user 'clsn'@'localhost'identified by 'clsn123';
注意这个样创建的用户只有连接权限
企业里创建用户一般是授权一个内网网段登录,最常见的网段写法有两种。
方法 1:172.16.1.%(% 为通配符,匹配所有内容)。
方法 2:172.16.1.0/255.255.255.0,但是不能使用 172.16.1.0/24,是个小遗憾。
标准的建用户方法:
- create user 'web'@'172.16.1.%'identified by 'web123';
查看用户对应的权限
- show grants for oldboy@localhost\G
删除用户语法:
- drop user 'user'@'主机域'
【练习】用户优化:只保留
- | root | 127.0.0.1 |
- | root | localhost |
特殊的删除方法:(慎用,尽量不要直接去修改表)
- mysql > delete from mysql.user where user = 'clsn'and host = 'localhost';
- Query OK,
- 1 row affected(0.00 sec) mysql > flush privileges;
给用户授权
- # 创建用户
- create user 'clsn'@'localhost' identified by 'clsn123';
- # 查看用户
- select user,host from mysql.user;
- # 授权所有权限给clsn用户
- GRANT ALL ON *.* TO 'clsn'@'localhost';
- # 查看clsn用户的权限
- SHOW GRANTS FOR 'clsn'@'localhost'\G
创建用户的同时授权
- grant all on *.* to clsn@'172.16.1.%' identified by 'clsn123';
- # 刷新权限
- flush privileges; #<==可以不用。
创建用户然后授权
- create user 'clsn'@'localhost'identified by 'clsn123';
- GRANT ALL ON * . * TO 'clsn'@'localhost';
授权和 root 一样的权限
- grant all on * . * to system@'localhost'identified by 'clsn123'with grant option;
授权给用户 select,create,insert,update 权限
- grant select,create,insert,update on clsn.* to 'clsn'@'10.0.0.%' identified by '123';
回收权限
- REVOKE INSERT ON * . * FROM clsn@localhost;
可以授权的用户权限
- INSERT,SELECT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN,
- PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER,
- CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE,
- REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER
- ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE
【示例】博客授权收回示例
- grant select,insert,update,delete,create,drop on blog.* to 'blog'@'172.16.1.%' identified by 'blog123';
- revoke create,drop on blog.* from 'blog'@'172.16.1.%';
授权博客类的最多权限:select,insert,update,delete
mysql 命令客户端
用于数据库连接管理
将 用户 SQL 语句发送到服务器
mysqladmin 命令 :命令行管理工具
mysqldump 命令 :备份数据库和表的内容
用于连接数据库
用于管理数据库通过下列方式进行管理
命令接口自带命令
DDL:数据定义语言
DCL:数据控制语言
DML:数据操作语言
mysql 命令接口自带命令说明
命令 |
命令说明 |
\h 或 help 或 ? |
获取帮助 |
\G |
格式化输出(行转列) |
\T 或 tee |
记录操作日志 tee /tmp/mysql.log |
\c 或 CTRL+c |
退出 mysql |
\s 或 status |
查看数据库状态信息 |
\. 或 source |
mysql> source /tmp/world.sql |
\! |
使用 shell 中的命令 mysql> \! cat /etc/redhat-release CentOS release 6.9 (Final) |
\u 或 use |
use world show databases 看当前所有数据库的名字 show tables 查看当前 use 到的数据库所有的表 show tables from world 查看目标数据库下的表 |
快捷键 |
上下翻页、TAB 键、ctrl +C 、ctrl +L |
在 mysql 命令行中输入 mysql 或 ? 都可以查看帮助
- mysql > help
使用 help contents 查看完整的 sql 类别列表帮助
- mysql > help contents
有关特定 SQL 类别或语句的帮助
- mysql > help Account Management
查看 grant 的帮助
- mysql > help GRANT
有关与状态相关的 SQL 语句的帮助
- mysql > help status
在 mysql 中处理输入文件:
如果这些文件包含 SQL 语句,则称为 "脚本文件" 或 "批处理文件"。
使用 SOURCE 命令:
- mysql > SOURCE / data / mysql / world.sql
或者使用非交互式:(尽量避免使用 mysql 导入数据,会产生大量的无用日志)
- mysql < /data/mysql / world.sql
基本语法
- mysqladmin - u < name > -p < password > commands
命令说明图表
功能选项 |
说明 |
mysqladmin -u 用户 -p 密码 ping |
"强制回应 (Ping)" 服务器。 |
mysqladmin -u 用户 -p 密码 shutdown |
关闭服务器。 |
mysqladmin -u 用户 -p 密码 create databasename |
创建数据库。 |
mysqladmin -u 用户 -p 密码 drop databasename |
删除数据库 |
mysqladmin -u 用户 -p 密码 version |
显示服务器和版本信息 |
mysqladmin -u 用户 -p 密码 status |
显示或重置服务器状态变量 |
mysqladmin -u 用户 -p 密码 password |
设置口令 |
mysqladmin -u 用户 -p 密码 flush-privileges |
重新刷新授权表。 |
mysqladmin -u 用户 -p 密码 flush-logs |
刷新日志文件和高速缓存。 |
以上信息通过 mysqladmin --help 获得 |
mysqldump 是一款数据库备份工具。
命令帮助及基本语法:
- [root@db02 ~]# mysqldump --help
- Dumping structure and contents of MySQL databases and tables.
- Usage: mysqldump [OPTIONS] database [tables]
- OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
- OR mysqldump [OPTIONS] --all-databases [OPTIONS]
情参照 mysqldump --help
定义范围:
库 :名字、特性
表:表名字、列
查看数据库 -- 查看全部
- mysql> show databases;
- +--------------------+
- | Database |
- +--------------------+
- | information_schema |
- | clsn |
- +--------------------+
查看数据库 -- 模糊匹配
- mysql> show databases like "%s%";
- +--------------------+
- | Database (%s%) |
- +--------------------+
- | information_schema |
- | clsn |
- | mysql |
- | performance_schema |
- +--------------------+
- 4 rows in set (0.00 sec)
查看你相关的帮助
- mysql> ? show databases;
- Name: 'SHOW DATABASES'
- Description:
- Syntax:
- SHOW {DATABASES | SCHEMAS}
- [LIKE 'pattern' | WHERE expr]
创建一个数据库
- mysql > create database haha;
通过 show 命令能够查看创建的数据库的格式
- mysql> show create database haha;
- +----------+---------------------------------------------------------------+
- | Database | Create Database |
- +----------+---------------------------------------------------------------+
- | haha | CREATE DATABASE `haha` /*!40100 DEFAULT CHARACTER SET utf8 */ |
- +----------+---------------------------------------------------------------+
- 1 row in set (0.00 sec)
创建数据库时定义字符编码
- mysql > create database clsn charset utf8;
- mysql > show create database clsn;查询数据库定义信息。
存在的数据库修改字符编码:
- mysql > alter database clsn charset gbk;
修改数据库编码格式示例
- mysql> alter database haha charset gbk
- -> ;
- Query OK, 1 row affected (0.00 sec)
- mysql> show create database haha;
- +----------+--------------------------------------------------------------+
- | Database | Create Database |
- +----------+--------------------------------------------------------------+
- | haha | CREATE DATABASE `haha` /*!40100 DEFAULT CHARACTER SET gbk */ |
- +----------+--------------------------------------------------------------+
- 1 row in set (0.00 sec)
标准修改系统
- ALTER DATABASE[db_name] CHARACTER SET charset_name COLLATE collation_name;
- ALTER DATABASE clsn CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
查看支持的字符集和校对规则.
- mysql> show character set;
- +----------+-----------------------------+---------------------+--------+
- | Charset | Description | Default collation | Maxlen |
- +----------+-----------------------------+---------------------+--------+
- | big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
- | dec8 | DEC West European | dec8_swedish_ci | 1 |
删除数据库
- mysql> drop database haha;
- Query OK, 0 rows affected (0.00 sec)
- mysql> show databases;
- +--------------------+
- | Database |
切库
- mysql > use clsn;
- Database changed
查看当前所在数据库
- mysql> select database();
- +------------+
- | database() |
- +------------+
- | clsn |
- +------------+
- 1 row in set (0.00 sec)
查看当前登陆的用户
- mysql> select user();
- +----------------+
- | user() |
- +----------------+
- | root@localhost |
- +----------------+
- 1 row in set (0.00 sec)
查看库里面的表
- mysql> show tables;
- +----------------+
- | Tables_in_clsn |
- +----------------+
- | t1 |
- | t2 |
- | test |
- +----------------+
- 3 rows in set (0.00 sec)
表的属性:
字段、数据类型、索引
默认:字符集、引擎
表定义(列):
表名、列名
列属性(数据类型、列约束)
创建表
- create table t3(id int);
创建更多的表;
- create table test(id int);
- create table t1(idcard int, name char(30), sex char(4));
查看表结构
- mysql> desc t2
- -> ;
- +--------+----------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +--------+----------+------+-----+---------+-------+
- | idcard | int(11) | YES | | NULL | |
- | name | char(30) | YES | | NULL | |
- | sex | char(4) | YES | | NULL | |
- | addr | char(4) | NO | | NULL | |
- +--------+----------+------+-----+---------+-------+
- 4 rows in set (0.00 sec)
查看建表语句
- mysql> show create table t2\G
- *************************** 1. row ***************************
- Table: t2
- Create Table: CREATE TABLE `t2` (
- `idcard` int(11) DEFAULT NULL,
- `name` char(30) DEFAULT NULL,
- `sex` char(4) DEFAULT NULL,
- `addr` char(4) NOT NULL
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8
- 1 row in set (0.00 sec)
修改表名字
- mysql> rename table t3 to haha;
- mysql> show tables ;
- +----------------+
- | Tables_in_clsn |
- +----------------+
- | haha |
修改表名字,方法二。
- mysql> alter table haha rename to people;
- mysql> show tables;
- +----------------+
- | Tables_in_clsn |
- +----------------+
- | people |
修改表结构
- mysql> alter table people add addr char(40) NOT NULL;
- mysql> desc people;
- +-------+----------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-------+----------+------+-----+---------+-------+
- | id | int(11) | YES | | NULL | |
- | addr | char(40) | NO | | NULL | |
- +-------+----------+------+-----+---------+-------+
- 2 rows in set (0.00 sec)
指定添加年龄列到 name 列后面的位置,示例如下:
- mysql> alter table people add age int(4) after name;
- Query OK, 0 rows affected (0.05 sec)
- Records: 0 Duplicates: 0 Warnings: 0
- mysql> desc people;
- +-------+----------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-------+----------+------+-----+---------+-------+
- | id | int(11) | YES | | NULL | |
- | addr | char(40) | NO | | NULL | |
- | name | int(40) | YES | | NULL | |
- | age | int(4) | YES | | NULL | |
- +-------+----------+------+-----+---------+-------+
- 4 rows in set (0.00 sec)
通过下面的命令在第一列添加 qq 字段。
- mysql> alter table people add telnum int first;
- Query OK, 0 rows affected (0.03 sec)
- Records: 0 Duplicates: 0 Warnings: 0
- mysql> desc people;
- +--------+----------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +--------+----------+------+-----+---------+-------+
- | telnum | int(11) | YES | | NULL | |
- | id | int(11) | YES | | NULL | |
- | addr | char(40) | NO | | NULL | |
- | name | int(40) | YES | | NULL | |
- | age | int(4) | YES | | NULL | |
- +--------+----------+------+-----+---------+-------+
- 5 rows in set (0.00 sec)
同时添加多个列定义:
- mysql> alter table people add id1 int first ,add sex char(4) after name ;
- Query OK, 0 rows affected (0.06 sec)
- Records: 0 Duplicates: 0 Warnings: 0
- mysql> desc people;
- +--------+----------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +--------+----------+------+-----+---------+-------+
- | id1 | int(11) | YES | | NULL | |
- | telnum | int(11) | YES | | NULL | |
- | id | int(11) | YES | | NULL | |
- | addr | char(40) | NO | | NULL | |
- | name | int(40) | YES | | NULL | |
- | sex | char(4) | YES | | NULL | |
- | age | int(4) | YES | | NULL | |
- +--------+----------+------+-----+---------+-------+
- 7 rows in set (0.00 sec)
删除表结构:
- mysql> alter table people drop sex;
- Query OK, 0 rows affected (0.06 sec)
- Records: 0 Duplicates: 0 Warnings: 0
- mysql> desc people;
- +--------+----------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +--------+----------+------+-----+---------+-------+
- | id1 | int(11) | YES | | NULL | |
- | telnum | int(11) | YES | | NULL | |
- | id | int(11) | YES | | NULL | |
- | addr | char(40) | NO | | NULL | |
- | name | int(40) | YES | | NULL | |
- | age | int(4) | YES | | NULL | |
- +--------+----------+------+-----+---------+-------+
- 6 rows in set (0.00 sec)
修改表定义
mysql> alter table people modify name char(20); Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc people; +--------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+----------+------+-----+---------+-------+ | id1 | int(11) | YES | | NULL | | | telnum | int(11) | YES | | NULL | | | id | int(11) | YES | | NULL | | | addr | char(40) | NO | | NULL | | | name | char(20) | YES | | NULL | | | age | int(4) | YES | | NULL | | +--------+----------+------+-----+---------+-------+ 6 rows in set (0.00 sec)
修改列名:
mysql> alter table people change name people_name char(30) ; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc people; +-------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------+------+-----+---------+-------+ | id1 | int(11) | YES | | NULL | | | telnum | int(11) | YES | | NULL | | | id | int(11) | YES | | NULL | | | addr | char(40) | NO | | NULL | | | people_name | char(30) | YES | | NULL | | | age | int(4) | YES | | NULL | | +-------------+----------+------+-----+---------+-------+ 6 rows in set (0.00 sec)
create database xxx charset # 创建数据库 设置字符集 create table xxx (id int,xxxxxx) # 创建表 drop table # 删除表 drop database # 参数数据库 alter table add/drop/modify/change # 表内容修改 create user # 创建用户 drop user #删除用户
grant:用户授权
GRANT ALL ON * . * TO 'clsn'@'localhost'; SHOW GRANTS FOR 'clsn'@'localhost'\G
创建用户的同时进行授权
grant select,create,insert,update on clsn.* to 'clsn'@'10.0.0.%' identified by '123';
revoke:回收权限
REVOKE INSERT ON * . * FROM clsn@localhost;
收回权限示例:
revoke drop,delete on *.* from sys@localhost;
DML 是针对数据行的操作
insert 语法结构
insert into < 表名 > [( < 字段名1 > [, .. < 字段名n > ])] values(值1)[, (值n)]
创建表,插入数据
mysql > use clsn mysql > create table clsn(id int, name varchar(20)); Query OK, 0 rows affected(0.06 sec)
表的类型
mysql> desc clsn; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
在表中插入第一行数据
mysql> insert into clsn values(1,'clsn'); Query OK, 1 row affected (0.00 sec) mysql> select * from clsn; +------+------+ | id | name | +------+------+ | 1 | clsn | +------+------+ 1 row in set (0.00 sec)
插入两行数据
mysql> insert into clsn values(2,'yougboy'),(3,'youggilr'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from clsn; +------+----------+ | id | name | +------+----------+ | 1 | clsn | | 2 | yougboy | | 3 | youggilr | +------+----------+ 3 rows in set (0.00 sec)
仅在 name 下插入一个名字
mysql> insert into clsn(name) values('xiaoming'); Query OK, 1 row affected (0.01 sec) mysql> select * from clsn; +------+----------+ | id | name | +------+----------+ | 1 | clsn | | 2 | yougboy | | 3 | youggilr | | NULL | xiaoming | +------+----------+ 4 rows in set (0.00 sec)
一次插入多行数据
mysql> create table test1(id int ,name varchar(20)); INSERT INTO `test1` VALUES (1,'clsn'),(2,'znix'),(3,'inca'),(4,'zuma'),(5,'kaka'); mysql> select * from test1; +------+------+ | id | name | +------+------+ | 1 | clsn | | 2 | znix | | 3 | inca | | 4 | zuma | | 5 | kaka | +------+------+ 5 rows in set (0.00 sec)
使用 insert 复制表结构及内容,产生附表。
mysql> create table test2 like clsn; Query OK, 0 rows affected (0.02 sec) mysql> insert into test2 select * from clsn; Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from test2; +------+----------+ | id | name | +------+----------+ | 1 | clsn | | 2 | yougboy | | 3 | youggilr | | NULL | xiaoming | +------+----------+ 4 rows in set (0.00 sec)
更新表内容
mysql> select * from test2; +------+----------+ | id | name | +------+----------+ | 1 | clsn | | 2 | yougboy | | 3 | youggilr | | NULL | xiaoming | +------+----------+ 4 rows in set (0.00 sec)
将字段中的 youggirl 改为 haha
mysql > update test2 set name = 'haha'where name = 'youggilr'; Query OK, 1 row affected(0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
更改后
mysql> select * from test2; +------+----------+ | id | name | +------+----------+ | 1 | clsn | | 2 | yougboy | | 3 | haha | | NULL | xiaoming | +------+----------+ 4 rows in set (0.00 sec)
删除语句
delete from test; #逻辑删除,一行一行删。 truncate table test; #物理删除,pages(block),效率高。
删除之前
mysql> select * from test2; +------+----------+ | id | name | +------+----------+ | 1 | clsn | | 2 | yougboy | | 3 | haha | | NULL | xiaoming | +------+----------+ 4 rows in set (0.00 sec)
删除 xiaoming 的记录
mysql> delete from test2 where name='xiaoming'; Query OK, 1 row affected (0.04 sec)
删除之后
mysql> select * from test2; +------+---------+ | id | name | +------+---------+ | 1 | clsn | | 2 | yougboy | | 3 | haha | +------+---------+ 3 rows in set (0.00 sec)
为表添加一个 state 列
TINYINT,字段类型,如果设置为 UNSIGNED 类型,只能存储从 0 到 255 的整数, 不能用来储存负数。
mysql> alter table test2 add state tinyint(2) not null default 1; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc test2; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | state | tinyint(2) | NO | | 1 | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql > update test set state = 1 ;
查看当前的 state 状态
mysql> select * from test2; +------+---------+-------+ | id | name | state | +------+---------+-------+ | 1 | clsn | 1 | | 2 | yougboy | 1 | | 3 | haha | 1 | +------+---------+-------+ 3 rows in set (0.00 sec)
更新数据,将 clsn 记录的 state 改为 0
mysql> update test2 set state=0 where name='clsn'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from test2; +------+---------+-------+ | id | name | state | +------+---------+-------+ | 1 | clsn | 0 | | 2 | yougboy | 1 | | 3 | haha | 1 | +------+---------+-------+ 3 rows in set (0.00 sec)
查询的时候,使用 where 条件只显示 state=1 的记录,效果与删除类似。
mysql> select * from test2 where state=1; +------+---------+-------+ | id | name | state | +------+---------+-------+ | 2 | yougboy | 1 | | 3 | haha | 1 | +------+---------+-------+ 2 rows in set (0.00 sec)
备份数据备用
mysqldump以SQL语句形式将数据导出来。 mysqldump -B --compact clsn >/opt/bak.sql
mysql -U 参数实践
-U, --safe-updates Only allow UPDATE and DELETE that uses keys.
使用 update 与 delete 命令的时候不加 where 条件不会执行。
mysql > update test2 set name = 'xixi'; ERROR 1175(HY000) : You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
在 mysql 命令加上选项 - U 后,当发出没有 WHERE 或 LIMIT 关键字的 UPDATE 或 DELETE 时,mysql 程序拒绝执行。
select 命令语法:
select < 字段1,字段2,... > from < 表名 > where < 表达式 > and < 表达式 > 。
其中,select、from、where 是不能随便改的,是关键字,支持大小写。
select < 字段1,字段2,... > from < 表名 > where < 表达式 >
查看用户的连接信息
mysql> select user,host,password from mysql.user; +------+------------+-------------------------------------------+ | user | host | password | +------+------------+-------------------------------------------+ | root | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | | root | 127.0.0.1 | | | znix | 172.16.1.% | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | | clsn | 172.16.1.% | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | | root | 10.0.0.1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | +------+------------+-------------------------------------------+ 5 rows in set (0.00 sec)
查看 test2 表中的所有信息
mysql> select * from clsn.test2; +------+---------+-------+ | id | name | state | +------+---------+-------+ | 1 | clsn | 0 | | 2 | yougboy | 1 | | 3 | haha | 1 | +------+---------+-------+ 3 rows in set (0.00 sec)
查看 test 表中的 id 和 name
mysql> select id,name from clsn.test2; +------+---------+ | id | name | +------+---------+ | 1 | clsn | | 2 | yougboy | | 3 | haha | +------+---------+ 3 rows in set (0.00 sec)
查看 id 等于 2 的信息
mysql> select id,name from test2 where id=2; +------+---------+ | id | name | +------+---------+ | 2 | yougboy | +------+---------+ 1 row in set (0.00 sec)
查看名字是 clsn 的记录
mysql> select id,name from test2 where name='clsn'; +------+------+ | id | name | +------+------+ | 1 | clsn | +------+------+ 1 row in set (0.00 sec)
查看 id 大于 2 的记录
mysql> select id,name from test2 where id>2; +------+------+ | id | name | +------+------+ | 3 | haha | +------+------+ 1 row in set (0.00 sec)
查看 id 大于 2 并且 小于 4 的记录
mysql> select id,name from test2 where id>2 and id<4; +------+------+ | id | name | +------+------+ | 3 | haha | +------+------+ 1 row in set (0.00 sec)
查看 id 大于 2 或者 小于 4 的记录
mysql> select id,name from test2 where id>2 or id<4; +------+---------+ | id | name | +------+---------+ | 1 | clsn | | 2 | yougboy | | 3 | haha | +------+---------+ 3 rows in set (0.00 sec)
提取记录排序
mysql> select id,name from test2 order by id asc; +------+---------+ | id | name | +------+---------+ | 1 | clsn | | 2 | yougboy | | 3 | haha | +------+---------+ 3 rows in set (0.00 sec) mysql> select id,name from test2 order by id desc; +------+---------+ | id | name | +------+---------+ | 3 | haha | | 2 | yougboy | | 1 | clsn | +------+---------+ 3 rows in set (0.00 sec)
显示排错第一行后的三行 (需要与排序配合使用)
mysql> select id,name from test2 limit 1,3; +------+---------+ | id | name | +------+---------+ | 2 | yougboy | | 3 | haha | +------+---------+ 2 rows in set (0.00 sec)
MySQL 数据库的字符集:字符集(CHARACTER)、校对规则(COLLATION)
MySQL 中常见的字符集:UTF8、LATIN1、GBK
常见校对规则:ci:大小写不敏感、cs 或 bin:大小写敏感
我们可以使用以下命令查看:show charset; 、 show collation;
系统字符集说明
[root@db02 ~]# cat /etc/sysconfig/i18n LANG="en_US.UTF-8" SYSFONT="latarcyrheb-sun16" [root@db02 ~]# echo $LANG en_US.UTF-8
客户端字符集说明
方法 1:在编译安装时候就指定如下服务器端字符集。
cmake . -DDEFAULT_CHARSET=utf8 \ -DDEFAULT_COLLATION=utf8_general_ci \ -DWITH_EXTRA_CHARSETS=all \
方法 2:在 my.cnf 文件中添加上字符参数
[mysqld] character-set-server=utf8
数据库中的库级别设置
CREATE DATABASE `clsn` /*!40100 DEFAULT CHARACTER SET utf8 */ create database clsn DEFAULT CHARACTER SET UTF8 DEFAULT COLLATE = utf8_general_ci;
获取帮助并查询
help create database; show character set;
表级别(含字段级别)
CREATE TABLE `test` ( `id` int(4) NOT NULL AUTO_INCREMENT, `name` char(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8
方法 1:临时生效单条命令法。
mysql> set names utf8; Query OK, 0 rows affected (0.00 sec)
方法 2:通过修改 my.cnf 实现修改 mysql 客户端的字符集,配置方法如下
[client] default - character - set = utf8
程序代码级别:生产环境更改数据库(含数据)字符集的方法
alter database clsn CHARACTER SET utf8 collate utf8_general_ci; alter table t1 CHARACTER SET latin1;
查看 当前所在的数据库
mysql> select database(); +------------+ | database() | +------------+ | clsn | +------------+ 1 row in set (0.00 sec)
查看当前的登陆用户
mysql> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec)
ER 模型,全称为实体联系模型、实体关系模型或实体联系模式图(ERD)(英语:Entity-relationship model)由美籍华裔计算机科学家陈品山发明,是概念数据模型的高层描述所使用的数据模型或模式图。
ER 模型常用于信息系统设计中;比如它们在概念结构设计阶段用来描述信息需求和/或要存储在数据库中的信息的类型。但是数据建模技术可以用来描述特定论域(就是感兴趣的区域)的任何本体(就是对使用的术语和它们的联系的概述和分类)。在基于数据库的信息系统设计的情况下,在后面的阶段(通常叫做逻辑设计),概念模型要映射到逻辑模型如关系模型上;它依次要在物理设计期间映射到物理模型上。注意,有时这两个阶段被一起称为 "物理设计"。
实体联系模式图(ERD)有一些约定。本文的余下部分描述经典概念,并且主要与概念建模有关。有一些概念更加典型的在逻辑和物理数据库设计中采用,包括信息工程、IDEF1x(ICAM DEFinition Language)和空间建模。
四种主要类别:数值类型、字符类型、时间类型、二进制类型
数据类型的 ABC 要素: Appropriate(适当) Brief(简洁) Complete(完整)
例 1:列声明
CREATE TABLE people ( id INT, first_name CHAR(30), last_name CHAR(30) );
例 2:不允许负值和未知值
CREATE TABLE people ( id INT UNSIGNED NOT NULL, first_name CHAR(30), last_name CHAR(30) );
使用数值数据类型时的注意事项:
数值数据类型的类:
类 |
类型 |
说明 |
整数 |
TINYINT |
极小整数数据类型(0-255) |
整数 |
SMALLINT |
较小整数数据类型(-2^15 到 2^15-1) |
整数 |
MEDIUMINT |
中型整数数据类型 |
整数 |
INT |
常规(平均)大小的整数数据类型(-2^31 到 2^31-1) |
整数 |
BIGINT |
较大整数数据类型(-2^63 到 2^63-1) |
浮点数 |
FLOAT |
小型单精度(四个字节)浮点数 |
浮点数 |
DOUBLE |
常规双精度(八个字节)浮点数 |
定点数 |
DECIMAL |
包含整数部分、小数部分或同时包括二者的精确值数值 |
BIT |
BIT |
位字段值 |
表示给定字符集中的一个字母数字字符序列,用于存储文本或二进制数据,几乎在每种编程语言中都有实现,支持字符集和整理。
属于以下其中一类
文本:真实的非结构化字符串数据类型
整数:结构化字符串类型
类 |
类型 |
说明 |
文本 |
CHAR |
固定长度字符串,最多为 255 个字符 |
文本 |
VARCHAR |
可变长度字符串,最多为 65,535 个字符 |
文本 |
TINYTEXT |
可变长度字符串,最多为 255 个字符 |
文本 |
TEXT |
可变长度字符串,最多为 65,535 个字符 |
文本 |
MEDIUMTEXT |
可变长度字符串,最多为 16,777,215 个字符 |
文本 |
LONGTEXT |
可变长度字符串,最多为 4,294,967,295 个字符 |
整数 |
ENUM |
由一组固定的合法值组成的枚举 |
整数 |
SET |
由一组固定的合法值组成的集 |
字节序列:二进制位按八位分组
存储二进制值,例如:编译的计算机程序和应用程序、图像和声音文件
字符二进制数据类型的类:
二进制:固定长度和可变长度的二进制字符串
BLOB:二进制数据的可变长度非结构化集合
类 |
类型 |
说明 |
二进制 |
BINARY |
类似于 CHAR(固定长度)类型,但存储的是二进制字节字符串,而不是非二进制字符串 |
二进制 |
VARBINARY |
类似于 VARCHAR(可变长度)类型,但存储的是二进制字节字符串,而不是非二进制字符串 |
BLOB |
TINYBLOB |
最大长度为 255 个字节的 BLOB 列 |
BLOB |
BLOB |
最大长度为 65,535 个字节的 BLOB 列 |
BLOB |
MEDIUDMBLOB |
最大长度为 16,777,215 个字节的 BLOB 列 |
BLOB |
LONGBLOB |
最大长度为 4,294,967,295 个字节的 BLOB 列 |
类型 |
格式 |
示例 |
DATE |
YYYY-MM-DD |
2017-12-16 |
TIME |
hh:mm:ss[.uuuuuu] |
12:59:02.123456 |
DATETIME |
YYYY-MM-DD hh:mm:ss[.uuuuuu] |
2017-12-16 12:59:02.123 |
TIMESTAMP |
YYYY-MM-DD hh:mm:ss[.uuuuuu] |
2017-12-16 12:59:02.12 |
YEAR |
YYYY |
2017 |
列属性的类别:
数值:适用于数值数据类型(BIT 除外)
字符串:适用于非二进制字符串数据类型
常规:适用于所有数据类型
数据类型 |
属性 |
说明 |
数值 |
UNSIGNED |
禁止使用负值 |
仅整数 |
AUTO_INCREMENT |
生成包含连续唯一整数值的序列 |
字符串 |
CHARACTER SET |
指定要使用的字符集 |
字符串 |
COLLATE |
指定字符集整理 |
字符串 |
BINARY |
指定二进制整理 |
全部 * |
NULL 或 NOT NULL |
指示列是否可以包含 NULL 值 |
全部 |
DEFAULT |
如果未为新记录指定值,则为其提供默认值 |
使用固定长度数据类型:
如果存储的所有字符串值的长度相同
使用可变长度数据类型:
如果存储的字符串值不同、对于多字节字符集
对于频繁使用的字符,使用占用空间较少的多字节字符集。
使用基本多文种平面 (Basic Multilingual Plane, BMP) 之外的其他、Unicode 字符集。
来源: http://www.cnblogs.com/clsn/p/8047028.html