《MySQL的奇巧淫技》要點(diǎn):
本文介紹了MySQL的奇巧淫技,希望對您有用。如果有疑問,可以聯(lián)系我們。
mysql是目前最流行的關(guān)系型數(shù)據(jù)庫管理系統(tǒng),在WEB應(yīng)用方面MySQL是最好的RDBMS(Relational Database Management System:關(guān)系數(shù)據(jù)庫管理系統(tǒng))應(yīng)用軟件之一.在給大家分享之前,先介紹下 小編組織的一個學(xué)習(xí)交流企鵝群:526929231 有什么不懂的問題,都可以在群里問,學(xué)習(xí)氣氛很好,眾多大神都很熱情.群文件里面也有各種全面的python材料,以及自動化運(yùn)維學(xué)習(xí)材料,是一個非常適合學(xué)習(xí)的地方,小編期待大家的加入,大家一起學(xué)習(xí),共同成長!
mysql中的根本邏輯對象
mysql有這么幾種工具
mysqld--->庫---->表---->記錄(由行和列構(gòu)成)一條記錄中的一列叫做字段
什么是關(guān)系型數(shù)據(jù)庫
表與表產(chǎn)生關(guān)系,叫關(guān)系型
部分表(部分ID、部分名稱)
雇員表(員工ID、員工姓名、部分ID)
可以通過上面的例子知道在雇員表中寫入了部分id這個字段在部分表中也存在,2個表就通過部分id這個字段聯(lián)系起來了
action:
查詢數(shù)據(jù)(最多的動作) 便是查詢數(shù)據(jù)
改動數(shù)據(jù)
刪除數(shù)據(jù)
增加數(shù)據(jù)
mysql=國際尺度化+mysql官方的自定義尺度
pl/sql=國際尺度化+oracle官方的自定義尺度
Tsql=國際尺度化+microsoft官方的自定義尺度
總結(jié):
所有大家在學(xué)習(xí)一些SQL語句的時候會出現(xiàn) 我雖然沒有學(xué)過這個數(shù)據(jù)庫,但是它的語法我基本也能看的懂,便是因?yàn)镾QL有國際標(biāo)準(zhǔn)化的語句
create語句
創(chuàng)立數(shù)據(jù)庫
創(chuàng)立數(shù)據(jù)庫
mysql> create database ceshi1; #創(chuàng)建數(shù)據(jù)庫ceshi1Query OK, 1 row affected (0.00 sec)mysql> show create database ceshi1; 查看數(shù)據(jù)庫是如何建立的+----------+-------------------------------------------------------------------+| Database | Create Database |+----------+-------------------------------------------------------------------+| ceshi1 | CREATE DATABASE `ceshi1` /*!40100 DEFAULT CHARACTER SET latin1 */ |+----------+-------------------------------------------------------------------+1 row in set (0.00 sec)mysql> create database ceshi2 default charset utf8; 設(shè)置默認(rèn)的字符編碼集為utf-8Query OK, 1 row affected (0.00 sec)mysql> create database IF NOT EXISTS ceshi1 default charset utf8; #創(chuàng)建一個數(shù)據(jù)庫如果不存在就創(chuàng)建,如果存在就告警Query OK, 1 row affected, 1 warning (0.00 sec)mysql> show warnings; #查看最后一次的waring的信息+-------+------+-------------------------------------------------+| Level | Code | Message |+-------+------+-------------------------------------------------+| Note | 1007 | Can't create database 'ceshi1'; database exists |+-------+------+-------------------------------------------------+1 row in set (0.00 sec)
2個變量
varchar ----不定長 好比我設(shè)一個字段為50個字符 那么我只占用了30個字符 那么數(shù)據(jù)庫給我計(jì)算的時候只給我算30個字符
char ---定長 好比我設(shè)置一個字段為50個字符,我只占用了10個字符,但是數(shù)據(jù)庫還是會給我算50個字符的空間的占用
mysql> use ceshi1; #進(jìn)入ceshi1庫Database changedmysql> create table ceshi1.t1(id int,name varchar(50),salary decimal(10,2),dept varchar(50));Query OK, 0 rows affected (0.02 sec)解析: 在ceshi1庫中創(chuàng)建t1表 并創(chuàng)建4個字段1. 第一個字段 id 設(shè)置int類型2. 第二個字段 name 指定字符長度為503. 第三個字段 salary 指定保存小數(shù)點(diǎn)2位4. 第四個字段 dept 執(zhí)行字符長度為50mysql> DESCRIBE t1; #查看表結(jié)構(gòu) 可以看到有4個字段+--------+---------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+---------------+------+-----+---------+-------+| id | int(11) | YES | | NULL | || name | varchar(50) | YES | | NULL | || salary | decimal(10,2) | YES | | NULL | || dept | varchar(50) | YES | | NULL | |+--------+---------------+------+-----+---------+-------+4 rows in set (0.00 sec)mysql> select * from ceshi1.t1; 查詢表所有內(nèi)容Empty set (0.00 sec)mysql> insert into db01.t1 set id=1, name='zhang3',salary=5000,dept='生產(chǎn)部'; 插入數(shù)據(jù)Query OK, 1 row affected, 1 warning (0.07 sec)mysql> select * from ceshi1.t1; #可以看到數(shù)據(jù)已經(jīng)插入進(jìn)去了+------+--------+---------+-----------+| id | name | salary | dept |+------+--------+---------+-----------+| 1 | zhang3 | 5000.00 | 生產(chǎn)部 |+------+--------+---------+-----------+1 row in set (0.00 sec)mysql> show create table ceshi1.t1; #可以查看表示任何創(chuàng)建的| Table | Create Table | t1 | CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `name` varchar(50) DEFAULT NULL, `salary` decimal(10,2) DEFAULT NULL, `dept` varchar(50) DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1 可以查出使用神沒存儲引擎以及語言編碼1 row in set (0.00 sec)mysql> create table ceshi1.t2 ( 列數(shù)多的情況可以使用一下方式創(chuàng)建表 -> `id` int(11) DEFAULT NULL, -> `name` varchar(50) DEFAULT NULL, -> `salary` decimal(10,2) DEFAULT NULL, -> `dept` varchar(50) DEFAULT NULL -> );Query OK, 0 rows affected (0.11 sec)
創(chuàng)建用戶
創(chuàng)建一個用戶 user01用戶名 123暗碼mysql> create user user01@'localhost' identified by '123';Query OK, 0 rows affected (0.00 sec)mysql> set password for user01@'localhost'=password('123'); --設(shè)置用戶的暗碼mysql> create user u01@'localhost' ; --創(chuàng)建一個用戶mysql> select user,host,password from mysql.user; --查詢用戶是否創(chuàng)建成功| root | localhost | *425F1EBD8227A2B1E01C475B523E27A592CFF59A || root | vagrant-centos65.vagrantup.com | || root | 127.0.0.1 | || | localhost | || | vagrant-centos65.vagrantup.com | || user01 | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257mysql> show grants; #查看用戶權(quán)限| Grants for root@localhost || GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*425F1EBD8227A2B1E01C475B523E27A592CFF59A' WITH GRANT OPTION |1 row in set (0.00 sec)
drop --刪除數(shù)據(jù)庫對象(表/庫/用戶)
刪除表/庫 mysql> use ceshi1mysql> show tables;mysql> drop table t1; --刪除表mysql> drop table t2; mysql> use ceshi1 --驗(yàn)證表是否刪除勝利mysql> show tablesmysql> drop database ceshi1; --刪除庫Query OK, 0 rows affected (0.00 sec)mysql> drop database ceshi1 ;mysql> show databases; --驗(yàn)證庫是否刪除勝利
刪除用戶:
mysql> drop user user01@'localhost'; --刪除用戶Query OK, 0 rows affected (0.00 sec)mysql> select user from mysql.user where user='user01'; --驗(yàn)證用戶是否刪除勝利mysql> drop user ''@'192.168.1.1'; 刪除一個匿名用戶
alter(修改已經(jīng)存在數(shù)據(jù)庫對象(庫/表/列)的屬性)
mysql> ALTER DATABASE db01 DEFAULT CHARACTER SET latin1; --修改數(shù)據(jù)庫語言編碼Query OK, 1 row affected (0.05 sec)mysql> show create database db01; --驗(yàn)證修改是否勝利
修改表的屬性:mysql> create table t2(name varchar(50));mysql> ALTER TABLE t2 ADD id int FIRST; --增加一列成為第一列mysql> ALTER TABLE t2 add id2 int AFTER id; --在id后面增加一列叫id2mysql> alter table t2 drop id2; --刪除id2這個列mysql> alter table t2 change id ID bigint; --修改列名和數(shù)據(jù)類型mysql> alter table t2 modify ID int; --修改列的數(shù)據(jù)類型mysql> alter table t2 rename t20; --重命名表mysql>show engines; --查看數(shù)據(jù)庫有哪些存儲引擎mysql> alter table t20 engine MyISAM; --修改表的存儲引擎mysql> show create table t20; --查看修改存儲引擎是否勝利mysql> alter table t20 DEFAULT CHARSET=utf8; --修改表的語言編碼
insert 插入記錄
mysql> insert into ceshi1.t1 set id=1,name='zhang3'; --向指定列插入數(shù)據(jù)mysql> insert into ceshi1.t1 values(2,'li4'),(3,'wang5'),(4,'zhao6'); --插入多條記錄mysql> insert into ceshi1.t1(NAME) values('test01'),('test02'),('test03'); --不寫列名,表現(xiàn)按順序往所有的列插入數(shù)據(jù)mysql> select * from ceshi1.t1 where id<5; 查出前4張表mysql> insert into ceshi1.t2 select * from ceshi1.t1 where id<5; 查出前4張表,插入到當(dāng)前表(注意表結(jié)構(gòu)要一致)mysql> rename tables t1 to t01;
update 更新/修改記錄
mysql> update db01.t1 set id=5 where NAME='test01';mysql> update db01.t1 set id=6 where NAME='test02';mysql> update db01.t1 set id=7 where NAME='test03';
delete/truncate 刪除和清空表
mysql> delete from mysql.user where user=''; --刪除mysql中的匿名用戶mysql> flush privileges;mysql> create table t3 like t1; 復(fù)制表布局不復(fù)制數(shù)據(jù)mysql> insert into t3 select * from t1;mysql> delete from t3; --刪除整個表,一行一行刪除,所有的操作都會被記錄至事務(wù)日志中mysql> insert into t3 select * from t1;mysql> delete from t3 where id=1; --指定條件刪除mysql> truncate from t2 where id=2; --報(bào)錯
查詢語句 select--查詢
通配符: % 匹配0個或任意多個字符 _ 匹配一個字符 = 精確匹配 like 模糊匹配 regex(^ . .* .....) 使用正則表達(dá)式來匹配排序: order by 排序 asc 升序排列結(jié)果 desc 降序排列結(jié)果 group by 聚合 distinct 去除重復(fù)的行
mysql> use ceshi2mysql> create table ceshi2.t1(id int ,name varchar(50) ,math tinyint, english tinyint);mysql> insert into ceshi2.t1 set id=1,name='zhangsan',math=55,english=66;mysql> insert into ceshi2.t1 set id=2,name='lisi',math=66,english=77;mysql> insert into ceshi2.t1 set id=3,name='wangwu',math=65,english=30;mysql> insert into ceshi2.t1 set id=2,name='li04',math=88,english=99;mysql> insert into ceshi2.t1 set id=3,name='wang5',math=75,english=73;mysql> insert into ceshi2.t1 set id=4,name='zhao6',math=75,english=73;mysql> insert into ceshi2.t1 set id=5,name='liu3',math=85,english=43;mysql> select * from ceshi2.t1; --查詢表中所有的列對應(yīng)的值(全表掃描)mysql> select id,name from t1; --查詢表中指定列mysql> select host,password,user from mysql.user; --查詢表中指定列mysql> select host as '主機(jī)名',password as '暗碼',user as '用戶名' from mysql.user; --給列取別名,增加可讀性mysql> select * from t1 where name='i';mysql> select * from t1 where name like 'i';mysql> select * from t1 where name like '%i%'; --模糊匹配mysql> select * from t1 where name like '____';mysql> select * from t1 where id=2;mysql> select * from t1 where name='wang5'; --精確匹配mysql> select * from t1 where english < 60;mysql> select * from t1 where name regexp '.*[0-9]?.*' ; --支持正則表達(dá)式mysql> select * from t1 where name regexp '.*[0-9]+.*' ; 任意數(shù)字一次或一次以上mysql> select * from t1 where name regexp '.*[0-9]{2}.*' ;mysql> select user,password,host from mysql.user where host regexp '^l'; --支持正則表達(dá)式mysql> select user,password,host from mysql.user where host regexp '([0-9]{1,3}\.){3}([0-9]{1,3})';排序mysql> select * from t1 where name regexp '.*[0-9]?.*' order by id asc; 升序mysql> select * from t1 where name regexp '.*[0-9]?.*' order by id desc; 降序 去除重復(fù)行 distinctmysql> select distinct id from t1 ;聚合 group by 聚合以第一個為標(biāo)準(zhǔn)mysql> select * from t1 group by id;mysql> select * from t1 group by id having id <= 2;gruop by 不能用where語句 用having語句
分頁函數(shù)(limint)指定只顯示前幾條數(shù)據(jù)
mysql> select * from t1 limit 10; --顯示前10行mysql> select * from t1 limit 10,10; --顯示11至20行mysql> select * from t1 limit 1; 顯示第1行mysql> select * from t1 limit 0,2; 顯示第1,2行mysql> select * from t1 limit 2,2; 顯示3,4行起始地位,偏移量 第二行的下2行mysql> select * from t1 limit 4,2; 顯示5,6行mysql> select * from t1 limit 6,2; 顯示第七行
mysql> select * from t1 order by english desc limit 3;
顯示英語前三名
mysql> select name,(math+english) as sum from t1 order by sum desc;
顯示總分,以降序分列
mysql> select name,(math+english) as sum from t1 order by sum desc limit 1;
顯示總分第一名
mysql> select name,max((math+english)) from t1;
顯示總分第一名
mysql> select name,max(math+english) from t1 order by (math+english) desc;
顯示總分第一名
mysql> select name,english from t1;
mysql> select max(english) from t1;
顯示英語的最高分
mysql> select min(english) from t1;
顯示英語的最低分
mysql> select max(english) from t1;
顯示英語的最高分
mysql> select min(english) from t1;
顯示英語的最低分
mysql> select name,english from t1 where english in (select max(english) from t1);
子查詢
顯示英語的最高分
* and or not 邏輯運(yùn)算
1.and
mysql> select * from t1 where math >= 60 and english >= 60;
2.or
mysql> select * from t1 where math >= 60 or english >= 60; 此中一科及格
3.not
mysql> select * from t1 where math >= 60 and not english >= 60;
+------+--------+------+---------+
| id | name | math | english |
+------+--------+------+---------+
| 3 | wangwu | 65 | 30 |
| 5 | liu3 | 85 | 43 |
+------+--------+------+---------+
2 rows in set (0.00 sec)
* mysql常用函數(shù) sum() avg() max() min() count()
mysql> select name,sum(math),sum(english) from t1;
+----------+-----------+--------------+
| name | sum(math) | sum(english) |
+----------+-----------+--------------+
| zhangsan | 215 | 252 |
+----------+-----------+--------------+
1 row in set (0.00 sec)
mysql> select name,avg(math),avg(english) from t1;
+----------+-----------+--------------+
| name | avg(math) | avg(english) |
+----------+-----------+--------------+
| zhangsan | 53.7500 | 63.0000 |
+----------+-----------+--------------+
1 row in set (0.00 sec)
mysql> select name,max(english) from t1;
+----------+--------------+
| name | max(english) |
+----------+--------------+
| zhangsan | 90 |
+----------+--------------+
1 row in set (0.00 sec)
mysql> select name,min(english) from t1;
+----------+--------------+
| name | min(english) |
+----------+--------------+
| zhangsan | 33 |
+----------+--------------+
1 row in set (0.00 sec)
mysql> select count() from t1;
+----------+
| count() |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)
mysql> select max(english) - min(math) from t1; --英語的最高分與數(shù)學(xué)最低的差距
+--------------------------+
| max(english) - min(math) |
+--------------------------+
| 50 |
+--------------------------+
1 row in set (0.00 sec)
mysql> select * from t1 order by english desc limit 3;
+------+----------+------+---------+
| id | name | math | english |
+------+----------+------+---------+
| 1 | zhangsan | 70 | 90 |
| 4 | lisi02 | 55 | 69 |
| 2 | lisi | 50 | 60 |
+------+----------+------+---------+
3 rows in set (0.00 sec)
mysql> select * from t1 order by english asc limit 1,2; 顯示第二名和第三名
+------+--------+------+---------+
| id | name | math | english |
+------+--------+------+---------+
| 4 | lisi02 | 55 | 69 |
| 2 | lisi | 50 | 60 |
+------+--------+------+---------+
2 rows in set (0.00 sec)
mysql> select * from ceshi1.t1 where math >= 60 or english >=60 ;
+------+----------+------+---------+
| id | name | math | english |
+------+----------+------+---------+
| 1 | zhangsan | 70 | 90 |
| 2 | lisi | 50 | 60 |
| 4 | lisi02 | 55 | 69 |
+------+----------+------+---------+
3 rows in set (0.00 sec)
* 復(fù)制表布局 表布局的復(fù)制
mysql> create table t3 like t1; --復(fù)制表布局
mysql> create database db03;
mysql> use db03
mysql> create table t1(id int,name varchar(50));
mysql> create table t2(id int,socre int);
mysql> insert into t1 set id=1,name='lee';
mysql> insert into t1 set id=2,name='zhang';
mysql> insert into t1 set id=4,name='wang';
mysql> insert into t2 set id=1,socre='90';
mysql> insert into t2 set id=2,socre='100';
mysql> insert into t2 set id=3,socre='90';
mysql> select * from t1;
* 列類型
整數(shù)
create table t1(id tinyint(2), name varchar(50))
· TINYINT[(M)] [UNSIGNED] [ZEROFILL] [not null] [comment]
很小的整數(shù).帶符號的規(guī)模是-128到127.無符號的規(guī)模是0到255.
· MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
中等年夜小的整數(shù).帶符號的范圍是-8388608到8388607.無符號的范圍是0到16777215.
· INT[(M)] [UNSIGNED] [ZEROFILL]
普通年夜小的整數(shù).帶符號的范圍是-2147483648到2147483647.無符號的范圍是0到4294967295.
· INTEGER[(M)] [UNSIGNED] [ZEROFILL]
這是INT的同義詞.
· BIGINT[(M)] [UNSIGNED] [ZEROFILL]
年夜整數(shù).帶符號的范圍是-9223372036854775808到9223372036854775807.無符號的范圍是0到18446744073709551615.
mysql> use db01
mysql> show tables;
+----------------+
| Tables_in_db01 |
+----------------+
| t1 |
| t2 |
+----------------+
2 rows in set (0.00 sec)
mysql> create table t4(id int ,name char(50),salary decimal(10,2));
mysql> desc t4;
+--------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(50) | YES | | NULL | |
| salary | decimal(10,2) | YES | | NULL | |
+--------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into t4 set id='test01' ,name='asdasdad',salary='dadadadasd' ;
Query OK, 1 row affected, 2 warnings (0.00 sec)
mysql> show warnings;
+---------+------+--------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: 'test01' for column 'id' at row 1 |
| Warning | 1366 | Incorrect decimal value: 'dadadadasd' for column 'salary' at row 1 |
+---------+------+--------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> select * from t4;
+------+----------+--------+
| id | name | salary |
+------+----------+--------+
| 0 | asdasdad | 0.00 |
+------+----------+--------+
1 row in set (0.00 sec)
=============
mysql> create table t5(id tinyint);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t5 set id=-10;
Query OK, 1 row affected (0.00 sec)
mysql> select * from t5;
+------+
| id |
+------+
| -10 |
+------+
1 row in set (0.00 sec)
mysql> insert into t5 set id=-300;
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select * from t5;
+------+
| id |
+------+
| -10 |
| -128 |
+------+
2 rows in set (0.00 sec)
mysql> alter table t5 modify id tinyint unsigned;
mysql> insert t4 set id=2500;
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from t5;
+------+
| id |
+------+
| 0 |
| 0 |
| 250 |
| 255 |
+------+
4 rows in set (0.00 sec)
浮點(diǎn)數(shù) decimal指定小數(shù)點(diǎn)的位數(shù)
mysql> alter table t5 add salary decimal(7,2) unsigned after id;
mysql> update t5 set salary=1000000 where id=255;
mysql> select * from t5;
+------+-----------+
| id | salary |
+------+-----------+
| 0 | NULL |
| 0 | NULL |
| 250 | NULL |
| 255 | 999999.99 |
日期和光陰類型概述
mysql> alter table t5 add date date;
mysql> alter table t5 add datetime datetime;
mysql> alter table t5 add time TIMESTAMP;
mysql> insert into t5(id,salary,date,datetime) values(1,10000,'2011-09-01','2011-09-01 11:28:01');
mysql> select * from t5;
+------+----------+---------------------+------------+---------------------+
| id | salary | time | date | datetime |
+------+----------+---------------------+------------+---------------------+
| 1 | 10000.00 | 2011-09-01 11:28:27 | 2011-09-01 | 2011-09-01 11:28:01 |
+------+----------+---------------------+------------+---------------------+
1 row in set (0.00 sec)
寫在末了:
維易PHP培訓(xùn)學(xué)院每天發(fā)布《MySQL的奇巧淫技》等實(shí)戰(zhàn)技能,PHP、MYSQL、LINUX、APP、JS,CSS全面培養(yǎng)人才。
轉(zhuǎn)載請注明本頁網(wǎng)址:
http://www.snjht.com/jiaocheng/7892.html