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 直接創(chuàng)建表
mysql> create table user(
    -> id int auto_increment primary key,
    -> name varchar(16) not null,
    -> age int not null,
    -> birthday datetime
    -> );
-----
# create 利用已有表創(chuàng)建
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 設(shè)置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)

數(shù)據(jù)操作

增刪改查數(shù)據(jù):

# 增
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)

視圖

視圖是從一個或多個表導(dǎo)出的虛擬表

# 創(chuàng)建視圖
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)
# 創(chuàng)建或替換視圖
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)

# 插入數(shù)據(jù)
# 操作視圖即操作數(shù)據(jù)表
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)

用戶管理

# 創(chuàng)建用戶
# 語法
# create user 'username'@'host' identified by 'password'
# host用'%' 則代表可以從任意遠(yuǎn)程登錄

mysql> create user 'samp'@'localhost' identified by 'samp';
Query OK, 0 rows affected (0.10 sec)

# 授權(quán)
# 語法
# grant privileges on database.table to 'username'@'host'
# privileges - 用戶的操作權(quán)限(select, update等 all所有權(quán)限)
# database.table 可使用*設(shè)置所有穿剖，如samp_db.*  *.*

mysql> grant select on samp_db.* to samp@localhost;
Query OK, 0 rows affected (0.11 sec)

# 使用samp登錄測試權(quán)限
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'

# 授權(quán)所有
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

# 用以上命令授權(quán)的用戶無法給其他用戶授權(quán)
# 若要帶上授權(quán)權(quán)限携御，則使用命令
# grant privileges on database.table to 'username'@'host' with grant option;

# 密碼設(shè)置/更改
# 1.非要設(shè)置/修改的用戶登錄
# SET PASSWORD FOR 'username'@'host' = PASSWORD('password');
# 2 當(dāng)前登錄用戶
# 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)

# 銷毀用戶權(quán)限
revoke privilege on database.table from 'username'@'host';

# 刪除用戶
drop user username@host

表的復(fù)制/備份/還原

# 復(fù)制表結(jié)構(gòu)
# 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.只有表結(jié)構(gòu)啄刹，沒有主鍵等信息
> 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;  # 不會復(fù)制數(shù)據(jù)
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)

# 表數(shù)據(jù)復(fù)制
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)

# 查看表的創(chuàng)建語句
> 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)

# 備份數(shù)據(jù)庫
$ sudo mysqldump -u root -p samp_db > /data/backups/samp.sql

# 還原數(shù)據(jù)庫
> 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)

最后編輯于：2017.12.03 08:30:16

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者

人面猴
序言：七十年代末昵时，一起剝皮案震驚了整個濱河市椒丧，隨后出現(xiàn)的幾起案子壶熏，更是在濱河造成了極大的恐慌，老刑警劉巖溯职，帶你破解...
沈念sama閱讀 211,348評論 6贊 491
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件帽哑，死亡現(xiàn)場離奇詭異妻枕，居然都是意外死亡，警方通過查閱死者的電腦和手機(jī)鹰贵，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 90,122評論 2贊 385
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進(jìn)店門碉输，熙熙樓的掌柜王于貴愁眉苦臉地迎上來亭珍，“玉大人，你說我怎么就攤上這事阻荒∏壬模” “怎么了？”我有些...
開封第一講書人閱讀 156,936評論 0贊 347
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵蓖宦，是天一觀的道長稠茂。經(jīng)常有香客問我情妖，道長毡证，這世上最難降的妖魔是什么？我笑而不...
開封第一講書人閱讀 56,427評論 1贊 283
?港島之戀（遺憾婚禮）
正文為了忘掉前任藐不，我火速辦了婚禮，結(jié)果婚禮上阱州，老公的妹妹穿的比我還像新娘法梯。我一直安慰自己立哑，他們只是感情好，可當(dāng)我...
茶點(diǎn)故事閱讀 65,467評論 6贊 385
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布诈茧。她就那樣靜靜地躺著敢会，像睡著了一般鸥昏。火紅的嫁衣襯著肌膚如雪吏垮。梳的紋絲不亂的頭發(fā)上，一...
開封第一講書人閱讀 49,785評論 1贊 290
城市分裂傳說
那天，我揣著相機(jī)與錄音生棍，去河邊找鬼涂滴。笑死柔纵，一個胖子當(dāng)著我的面吹牛锤躁，可吹牛的內(nèi)容都是我干的系羞。我是一名探鬼主播椒振，決...
沈念sama閱讀 38,931評論 3贊 406
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼澎迎，長吁一口氣：“原來是場噩夢啊……” “哼夹供！你這毒婦竟也來了哮洽？” 一聲冷哼從身側(cè)響起，我...
開封第一講書人閱讀 37,696評論 0贊 266
萬榮殺人案實錄
序言：老撾萬榮一對情侶失蹤横浑，失蹤者是張志新（化名）和其女友劉穎徙融，沒想到半個月后欺冀，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體萨脑，經(jīng)...
沈念sama閱讀 44,141評論 1贊 303
?護(hù)林員之死
正文獨(dú)居荒郊野嶺守林人離奇死亡职车，尸身上長有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點(diǎn)故事閱讀 36,483評論 2贊 327
?白月光啟示錄
正文我和宋清朗相戀三年，在試婚紗的時候發(fā)現(xiàn)自己被綠了积瞒。大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片茫孔。...
茶點(diǎn)故事閱讀 38,625評論 1贊 340
活死人
序言：一個原本活蹦亂跳的男人離奇死亡，死狀恐怖揩瞪，靈堂內(nèi)的尸體忽然破棺而出，到底是詐尸還是另有隱情壹将，我是刑警寧澤毛嫉，帶...
沈念sama閱讀 34,291評論 4贊 329
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布暴区，位于F島的核電站仙粱，受9級特大地震影響伐割，放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜白群，卻給世界環(huán)境...
茶點(diǎn)故事閱讀 39,892評論 3贊 312
男人毒藥：我在死后第九天來索命
文/蒙蒙一、第九天我趴在偏房一處隱蔽的房頂上張望粱玲。院中可真熱鬧密幔，春花似錦胯甩、人聲如沸偎箫。這莊子的主人今日做“春日...
開封第一講書人閱讀 30,741評論 0贊 21
一樁弒父案怜森，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽副硅。三九已至恐疲，卻和暖如春培己，著一層夾襖步出監(jiān)牢的瞬間省咨，已是汗流浹背茸炒。一陣腳步聲響...
開封第一講書人閱讀 31,977評論 1贊 265
情欲美人皮
我被黑心中介騙來泰國打工感论，沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留比肄，地道東北人芳绩。一個月前我還...
沈念sama閱讀 46,324評論 2贊 360
代替公主和親
正文我出身青樓遏片，卻偏偏與公主長得像吮便，于是被迫代替她去往敵國和親髓需。傳聞我的和親對象是個殘疾皇子僚匆，可洞房花燭夜當(dāng)晚...
茶點(diǎn)故事閱讀 43,492評論 2贊 348

MySQL基本語句

連接

database管理

table管理

數(shù)據(jù)操作

視圖

用戶管理

表的復(fù)制/備份/還原

推薦閱讀更多精彩內(nèi)容