迁移到 https://github.com/lianginet/notes
连接
# 语法 mysql -h 127.0.0.1 -u root -p
$ sudo mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 40
Server version: 10.0.25-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> # mariadb用法如mysql
database管理
# create
mysql> create database samp_db default
->character set utf8
->collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
# show
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| samp_db |
+--------------------+
# use
mysql> use samp_db;
Database changed
# select database()
mysql> select database();
+------------+
| database() |
+------------+
| samp_db |
+------------+
# drop
mysql> create database test1;
Query OK, 1 row affected (0.00 sec)
mysql> drop database test1;
Query OK, 0 rows affected (0.00 sec)
table管理
均在sam_db下操作
# create
# create 直接创建表
mysql> create table user(
-> id int auto_increment primary key,
-> name varchar(16) not null,
-> age int not null,
-> birthday datetime
-> );
-----
# create 利用已有表创建
mysql> create table new_user select * from user;
Query OK, 0 rows affected (0.92 sec)
Records: 0 Duplicates: 0 Warnings: 0
# show
mysql> show tables;
+-------------------+
| Tables_in_samp_db |
+-------------------+
| new_user |
| user |
+-------------------+
2 rows in set (0.00 sec)
# desc
mysql> desc user;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(16) | NO | | NULL | |
| age | int(11) | NO | | NULL | |
| birthday | datetime | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.06 sec)
# alter
# alter修改表的编码
mysql> alter table user convert to character set utf8;
Query OK, 0 rows affected (0.82 sec)
Records: 0 Duplicates: 0 Warnings: 0
-----
# alter 添加列
mysql> alter table user add integral int;
Query OK, 0 rows affected (0.54 sec)
Records: 0 Duplicates: 0 Warnings: 0
-----
# alter 修改字段
mysql> alter table user modify integral varchar(12); # 修改类型
Query OK, 0 rows affected (0.88 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table user change integral level tinyint(1); # 修改名称
Query OK, 0 rows affected (0.93 sec)
Records: 0 Duplicates: 0 Warnings: 0
-----
# alter 删除字段
mysql> alter table user drop column level;
Query OK, 0 rows affected (0.57 sec)
Records: 0 Duplicates: 0 Warnings: 0
-----
# alter 设置null/not null
mysql> alter table user modify age int(3) null;
Query OK, 0 rows affected (0.59 sec)
Records: 0 Duplicates: 0 Warnings: 0
# rename
mysql> rename table user to users;
Query OK, 0 rows affected (0.47 sec)
数据操作
增删改查数据:
# 增
mysql> insert into user values (null, 'tom', 23, '1994-10-24');
Query OK, 1 row affected (0.14 sec)
mysql> insert into user values (null, 'sam', 24, '1993-10-24');
Query OK, 1 row affected (0.07 sec)
mysql> select * from user;
+----+------+------+---------------------+
| id | name | age | birthday |
+----+------+------+---------------------+
| 1 | tom | 23 | 1994-10-24 00:00:00 |
| 2 | sam | 24 | 1993-10-24 00:00:00 |
+----+------+------+---------------------+
2 rows in set (0.00 sec)
# 删
mysql> delete from user where name='tom';
Query OK, 1 row affected (0.09 sec)
mysql> select * from user;
+----+------+------+---------------------+
| id | name | age | birthday |
+----+------+------+---------------------+
| 2 | sam | 24 | 1993-10-24 00:00:00 |
+----+------+------+---------------------+
1 row in set (0.00 sec)
# 改
mysql> update user set age=23 where name='sam';
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from user;
+----+------+------+---------------------+
| id | name | age | birthday |
+----+------+------+---------------------+
| 2 | sam | 23 | 1993-10-24 00:00:00 |
+----+------+------+---------------------+
1 row in set (0.00 sec)
# 查
mysql> select name,age from user;
+------+------+
| name | age |
+------+------+
| sam | 23 |
+------+------+
1 row in set (0.00 sec)
视图
视图是从一个或多个表导出的虚拟表
# 创建视图
mysql> create view user_view (
-> name,age
-> ) as select name,age from user;
Query OK, 0 rows affected (0.10 sec)
mysql> select * from user_view;
+------+------+
| name | age |
+------+------+
| sam | 23 |
+------+------+
1 row in set (0.00 sec)
# 创建或替换视图
mysql> create or replace view user_view (
-> user_id,
-> user_name,
-> user_age
-> ) as select id, name, age from user;
mysql> show tables;
+-------------------+
| Tables_in_samp_db |
+-------------------+
| new_user |
| user |
| user_view |
+-------------------+
3 rows in set (0.00 sec)
mysql> select * from user_view;
+---------+-----------+----------+
| user_id | user_name | user_age |
+---------+-----------+----------+
| 2 | sam | 23 |
+---------+-----------+----------+
1 row in set (0.00 sec)
# 插入数据
# 操作视图即操作数据表
mysql> insert into user_view values (null, 'fmt', 22);
Query OK, 1 row affected (0.05 sec)
mysql> select * from user_view;
+---------+-----------+----------+
| user_id | user_name | user_age |
+---------+-----------+----------+
| 2 | sam | 23 |
| 3 | fmt | 22 |
+---------+-----------+----------+
2 rows in set (0.00 sec)
mysql> select * from user;
+----+------+------+---------------------+
| id | name | age | birthday |
+----+------+------+---------------------+
| 2 | sam | 23 | 1993-10-24 00:00:00 |
| 3 | fmt | 22 | NULL |
+----+------+------+---------------------+
2 rows in set (0.00 sec)
# 删除视图
mysql> drop view user_view;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+-------------------+
| Tables_in_samp_db |
+-------------------+
| new_user |
| user |
+-------------------+
2 rows in set (0.00 sec)
用户管理
# 创建用户
# 语法
# create user 'username'@'host' identified by 'password'
# host用'%' 则代表可以从任意远程登录
mysql> create user 'samp'@'localhost' identified by 'samp';
Query OK, 0 rows affected (0.10 sec)
# 授权
# 语法
# grant privileges on database.table to 'username'@'host'
# privileges - 用户的操作权限(select, update等 all所有权限)
# database.table 可使用*设置所有，如samp_db.* *.*
mysql> grant select on samp_db.* to samp@localhost;
Query OK, 0 rows affected (0.11 sec)
# 使用samp登录测试权限
mysql> use samp_db;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> update user set birthday='1991-10-24' where name='fmt';
ERROR 1142 (42000): UPDATE command denied to user 'samp'@'localhost' for table 'user'
# 授权所有
mysql> grant all on samp_db.* to samp@localhost;
Query OK, 0 rows affected (0.00 sec)
mysql> use samp_db;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> update user set birthday='1991-10-24' where name='fmt';
Query OK, 1 row affected (0.09 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 用以上命令授权的用户无法给其他用户授权
# 若要带上授权权限，则使用命令
# grant privileges on database.table to 'username'@'host' with grant option;
# 密码设置/更改
# 1.非要设置/修改的用户登录
# SET PASSWORD FOR 'username'@'host' = PASSWORD('password');
# 2 当前登录用户
# set password = password('password');
# samp用户登录
mysql> set password = password('samp_db');
Query OK, 0 rows affected (0.00 sec)
# root登录
mysql> set password for samp@localhost = password('samp');
Query OK, 0 rows affected (0.00 sec)
# 销毁用户权限
revoke privilege on database.table from 'username'@'host';
# 删除用户
drop user username@host
表的复制/备份/还原
# 复制表结构
# 1.含主键等信息的完整表
mysql> create table new_user like user;
Query OK, 0 rows affected (0.45 sec)
mysql> show tables;
+-------------------+
| Tables_in_samp_db |
+-------------------+
| new_user |
| user |
+-------------------+
2 rows in set (0.00 sec)
mysql> desc new_user;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(16) | NO | | NULL | |
| age | int(3) | YES | | NULL | |
| birthday | datetime | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
# 2.只有表结构，没有主键等信息
> create table new_user1 select * from user;
# 或 create table new_user1 as (select * from user);
Query OK, 2 rows affected (0.90 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> create table new_user2 select * from user where 1=2; # 不会复制数据
Query OK, 0 rows affected (0.60 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show tables;
+-------------------+
| Tables_in_samp_db |
+-------------------+
| new_user |
| new_user1 |
| new_user2 |
| user |
+-------------------+
4 rows in set (0.00 sec)
mysql> desc new_user1;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | 0 | |
| name | varchar(16) | NO | | NULL | |
| age | int(3) | YES | | NULL | |
| birthday | datetime | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> desc new_user2;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | 0 | |
| name | varchar(16) | NO | | NULL | |
| age | int(3) | YES | | NULL | |
| birthday | datetime | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
# 表数据复制
mysql> select * from new_user;
Empty set (0.00 sec)
mysql> insert into new_user select * from user;
Query OK, 2 rows affected (0.07 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from new_user;
+----+------+------+---------------------+
| id | name | age | birthday |
+----+------+------+---------------------+
| 2 | sam | 23 | 1993-10-24 00:00:00 |
| 3 | fmt | 22 | 1991-10-24 00:00:00 |
+----+------+------+---------------------+
2 rows in set (0.00 sec)
# 查看表的创建语句
> show create table user;
+-------+-------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------+
| user | CREATE TABLE `user` ( |
| | `id` int(11) NOT NULL AUTO_INCREMENT, |
| | `name` varchar(16) NOT NULL, |
| | `age` int(3) DEFAULT NULL, |
| | `birthday` datetime DEFAULT NULL, |
| | PRIMARY KEY (`id`) |
| | ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
+-------+----- -------------------------------------------------+
1 row in set (0.00 sec)
# 清空表
mysql> truncate new_user1;
Query OK, 0 rows affected (0.37 sec)
mysql> select * from new_user1;
Empty set (0.00 sec)
# 备份数据库
$ sudo mysqldump -u root -p samp_db > /data/backups/samp.sql
# 还原数据库
> create samp_db1;
MariaDB [(none)]> create database samp_db1;
Query OK, 1 row affected (0.00 sec)
MariaDB [samp_db1]> use samp_db1
Database changed
MariaDB [samp_db1]> source /data/backups/samp.sql
Query OK, 0 rows affected (0.00 sec)
......
Query OK, 0 rows affected (0.00 sec)
MariaDB [samp_db1]> show tables;
+--------------------+
| Tables_in_samp_db1 |
+--------------------+
| new_user |
| new_user1 |
| new_user2 |
| user |
+--------------------+
4 rows in set (0.00 sec)
