MySQL基本语句

作者: 小良叔 | 来源:发表于2016-07-13 13:49 被阅读109次

BigData-MySQL总结大全（一）苏暖人
MySql基本语句
MySQL基本语句
MySQL基本语句
MySQL基本语句
MySQL——基本语句
mysql基本语句
MySQL基本语句
MySQL基本语句
mysql基本语句

连接

# 语法 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)

BigData-MySQL总结大全（一）苏暖人
BigData之MySQL总结大全 MYSQL常用的基本语句 MYSQL常用的基本语句例：SELECT TOP ...
MySql基本语句
-建表、删除表 -更改表结构 -基本查询 -更新操作 -删除操作 -插入数据 -聚合函数 -连接语句 -Havin...
MySQL基本语句
库操作 [查] - 查看所有数据库 [增] - 创建新数据库 [查] - 查看创建数据库的SQL的语句 [用] -...
MySQL基本语句
一、连接查询 1.1内连接内连接(INNER JOIN)使用比较运算符进行表间列数据的比较操作，并列出这些表中与...
MySQL基本语句
一、数据库基础：二、数据库语句：三、数据表的操作语句：
MySQL——基本语句
Mysql-基础语法导语本博文主要是简述选择数据库和对表内容的增、删、改和查的一些基本语法 USE 语法： U...
mysql基本语句
1.show databases; （展示数据库,注意分号和s） 2.use mysql;(使用某个数据库) 3....
MySQL基本语句
迁移到 https://github.com/lianginet/notes 连接 database管理 tabl...
MySQL基本语句
一、DDL 语句（Data Definition Languages，数据定义语言）这些语句定义了不同的数据段、...
mysql基本语句
1.增 2.查 3.改（更新）如果遇到update users set realname...报错的话，先执行 ...