PHP之路——MySql基礎動作陳述式

標籤：

1,建立資料庫,create datebase 資料庫名,在這裡kenan建立一個資料庫名為myfirstdb

create database myfirstdb; 

2,查看所有的資料庫

show databases;

3,選擇資料庫，只有先選擇資料庫，才能在選擇的資料庫中進行資料庫操作

use myfirstdb; 

4,刪除資料庫

drop database myfirstdb; 

資料庫的增刪改查 

這裡要先使用 use來選擇要操作的資料庫

1，建立資料庫表

use myfirstdb;  create table user(  id int auto_increment primary key,  username varchar(50) not null,  password varchar(50) not null,  createtime datetime,  )

這裡建立了一個名為user 的表，id為int型的，auto_increatement表明這個是自增欄位,primary key表明這個是主鍵,username varchar(50)表明userame是可變的字元長，not null表明該欄位不能夠為空白，createtime datetime 欄位cratetime是日期時間類型的。

2， 查看錶結構

mysql> desc user;  +------------+-------------+------+-----+---------+----------------+  | Field      | Type        | Null | Key | Default | Extra          |  +------------+-------------+------+-----+---------+----------------+  | id         | int(11)     | NO   | PRI | NULL    | auto_increment |  | username   | varchar(50) | NO   |     | NULL    |                |  | password   | varchar(50) | NO   |     | NULL    |                |  | createtime | datetime    | YES  |     | NULL    |                |  +------------+-------------+------+-----+---------+----------------+  4 rows in set (0.01 sec)   mysql> show columns from user;  +------------+-------------+------+-----+---------+----------------+  | Field      | Type        | Null | Key | Default | Extra          |  +------------+-------------+------+-----+---------+----------------+  | id         | int(11)     | NO   | PRI | NULL    | auto_increment |  | username   | varchar(50) | NO   |     | NULL    |                |  | password   | varchar(50) | NO   |     | NULL    |                |  | createtime | datetime    | YES  |     | NULL    |                |  +------------+-------------+------+-----+---------+----------------+  4 rows in set (0.01 sec) 

在這裡展示了兩種查看錶結構的方式

3,修改表結構

添加列

mysql> alter table user add age int;  Query OK, 0 rows affected (0.25 sec)  Records: 0  Duplicates: 0  Warnings: 0 

修改列

mysql> alter table user modify username varchar(30);  Query OK, 0 rows affected (0.19 sec)  Records: 0  Duplicates: 0  Warnings: 0 

注意關鍵詞 modify

修改後的表結構

mysql> desc user;  +------------+-------------+------+-----+---------+----------------+  | Field      | Type        | Null | Key | Default | Extra          |  +------------+-------------+------+-----+---------+----------------+  | id         | int(11)     | NO   | PRI | NULL    | auto_increment |  | username   | varchar(30) | YES  |     | NULL    |                |  | password   | varchar(50) | NO   |     | NULL    |                |  | createtime | datetime    | YES  |     | NULL    |                |  | age        | int(11)     | YES  |     | NULL    |                |  +------------+-------------+------+-----+---------+----------------+  5 rows in set (0.01 sec) 

　　

4,刪除表

mysql> drop table user;  Query OK, 0 rows affected (0.03 sec) 

5,插入資料

mysql> insert into user values(null,‘kenan‘,‘kenan‘,now());  Query OK, 1 row affected (0.13 sec) 

這裡使用了日期函數，表示目前時間和日期，字串要用單引號引起來

6,查詢資料

mysql> select * from user;  +----+----------+----------+---------------------+  | id | username | password | createtime          |  +----+----------+----------+---------------------+  |  1 | kenan    | kenan    | 2012-10-30 21:46:31 |  +----+----------+----------+---------------------+  1 row in set (0.00 sec)   mysql> select username,password from user;  +----------+----------+  | username | password |  +----------+----------+  | kenan    | kenan    |  +----------+----------+  1 row in set (0.00 sec) 

第一句表示查詢所有的欄位從user表裡邊，在這裡*表示所有，第二句表示單獨查詢username和password欄位

 

6,帶有where字句的條件查詢

mysql> select * from user where id > 1;  mysql> select * from user where username = ‘kenan‘;  mysql> select * from user where username = ‘kenan‘ and id = 1; 

這裡分別進行了三個帶有where字句的子查詢，分別對id，和username進行限制，用and 串連兩個限制條件

7，group by 對查詢結果進行分組

mysql> select * from user;  +----+----------+----------+---------------------+  | id | username | password | createtime          |  +----+----------+----------+---------------------+  |  1 | kenan    | kenan    | 2012-10-30 21:46:31 |  |  2 | lele     | lele     | 2012-10-31 15:01:20 |  |  3 | kenan    | lele     | 2012-10-31 15:06:46 |  +----+----------+----------+---------------------+  3 rows in set (0.00 sec)   mysql> select * from user group by username;  +----+----------+----------+---------------------+  | id | username | password | createtime          |  +----+----------+----------+---------------------+  |  1 | kenan    | kenan    | 2012-10-30 21:46:31 |  |  2 | lele     | lele     | 2012-10-31 15:01:20 |  +----+----------+----------+---------------------+  2 rows in set (0.04 sec)   mysql> select username,avg(id) from user group by username;  +----------+---------+  | username | avg(id) |  +----------+---------+  | kenan    |  2.0000 |  | lele     |  2.0000 |  +----------+---------+  2 rows in set (0.04 sec)  mysql> select username,sum(id) from user group by username;  +----------+---------+  | username | sum(id) |  +----------+---------+  | kenan    |       4 |  | lele     |       2 |  +----------+---------+  2 rows in set (0.03 sec) 

這裡展示的group by的用法，首先第一句是按照username分組，所以 username相同的資料會認為是一組只顯示第一條資料，第二個sql語句和第三個sql語句用了兩個分組函數avg（），sum（）函數，這兩個函數呢，是跟group by 配合使用的，avg（）是用來求這一組資料的平均值，而sum（）是用來求這一組資料的和。

8，order by 對結果進行排序

mysql> select * from user;  +----+----------+----------+---------------------+  | id | username | password | createtime          |  +----+----------+----------+---------------------+  |  1 | kenan    | kenan    | 2012-10-30 21:46:31 |  |  2 | lele     | lele     | 2012-10-31 15:01:20 |  |  3 | kenan    | lele     | 2012-10-31 15:06:46 |  +----+----------+----------+---------------------+  3 rows in set (0.00 sec)   mysql> select * from user order by username;  +----+----------+----------+---------------------+  | id | username | password | createtime          |  +----+----------+----------+---------------------+  |  1 | kenan    | kenan    | 2012-10-30 21:46:31 |  |  3 | kenan    | lele     | 2012-10-31 15:06:46 |  |  2 | lele     | lele     | 2012-10-31 15:01:20 |  +----+----------+----------+---------------------+  3 rows in set (0.00 sec)   mysql> select * from user order by id;  +----+----------+----------+---------------------+  | id | username | password | createtime          |  +----+----------+----------+---------------------+  |  1 | kenan    | kenan    | 2012-10-30 21:46:31 |  |  2 | lele     | lele     | 2012-10-31 15:01:20 |  |  3 | kenan    | lele     | 2012-10-31 15:06:46 |  +----+----------+----------+---------------------+  3 rows in set (0.00 sec)   mysql> select * from user order by id desc     -> ;  +----+----------+----------+---------------------+  | id | username | password | createtime          |  +----+----------+----------+---------------------+  |  3 | kenan    | lele     | 2012-10-31 15:06:46 |  |  2 | lele     | lele     | 2012-10-31 15:01:20 |  |  1 | kenan    | kenan    | 2012-10-30 21:46:31 |  +----+----------+----------+---------------------+  3 rows in set (0.00 sec)   mysql> select * from user order by username,id;  +----+----------+----------+---------------------+  | id | username | password | createtime          |  +----+----------+----------+---------------------+  |  1 | kenan    | kenan    | 2012-10-30 21:46:31 |  |  3 | kenan    | lele     | 2012-10-31 15:06:46 |  |  2 | lele     | lele     | 2012-10-31 15:01:20 |  +----+----------+----------+---------------------+  3 rows in set (0.00 sec) 

在這裡第一個sql語句，查出所有的user表中的資料，預設是按照你插入時的資料來排序，就是id遞增第二句是 按照username排序，這個會比較username的首字母order by id 是按照id遞增排序order by id desc 是按照id遞減排序，這裡大家明白 desc的作用了吧order by username，id是添加了兩個排序條件，首先按照username排序，username相同的話，會按照id排序在排序中如果遇到NULL值，把NULL值按照最小的值來處理

9，distinct的用法

mysql> select distinct username from user;  +----------+  | username |  +----------+  | kenan    |  | lele     |  +----------+  2 rows in set (0.00 sec) 

distinct的是去重，然後把不重複的查詢出來

10,like模糊查詢

mysql> select * from user where username like ‘ke%‘;  +----+----------+----------+---------------------+  | id | username | password | createtime          |  +----+----------+----------+---------------------+  |  1 | kenan    | kenan    | 2012-10-30 21:46:31 |  |  3 | kenan    | lele     | 2012-10-31 15:06:46 |  +----+----------+----------+---------------------+  2 rows in set (0.00 sec)   mysql> select * from user where username like ‘_e%‘;  +----+----------+----------+---------------------+  | id | username | password | createtime          |  +----+----------+----------+---------------------+  |  1 | kenan    | kenan    | 2012-10-30 21:46:31 |  |  2 | lele     | lele     | 2012-10-31 15:01:20 |  |  3 | kenan    | lele     | 2012-10-31 15:06:46 |  +----+----------+----------+---------------------+  3 rows in set (0.01 sec) 

在這裡用了like模糊查詢第一個sql語句，是查詢出來 uername 以 ke 開頭的 行第二個sql語句，是查詢出來 username 的第二個字母是e的行在這裡%表示0或多個字元，_代表一個字元

11,LIMIT 限定結果行數，用來進行分頁查詢

mysql> select * from user limit 1;  +----+----------+----------+---------------------+  | id | username | password | createtime          |  +----+----------+----------+---------------------+  |  1 | kenan    | kenan    | 2012-10-30 21:46:31 |  +----+----------+----------+---------------------+  1 row in set (0.00 sec)   mysql> select * from user limit 1,2;  +----+----------+----------+---------------------+  | id | username | password | createtime          |  +----+----------+----------+---------------------+  |  2 | lele     | lele     | 2012-10-31 15:01:20 |  |  3 | kenan    | lele     | 2012-10-31 15:06:46 |  +----+----------+----------+---------------------+  2 rows in set (0.00 sec) 

limit的兩種用法第一種limit 1這個是顯示查詢出來的資料的第一條 當讓1換成x，就是x條第二種limit 1,2這裡limit帶有的兩個參數              首先我們把查詢出來的資料進行編號，從0號開始(當然這個編號是不存在的,就是說表內不存在這個欄位)，它不是id              這個編號是便於我們理解人為的編號              然後第一個參數代表的含義呢，就是要顯示的資料的編號              第二個參數表示的就是從剛剛編號開始（包含選中的編號）顯示多少條資料分頁用法，要顯示第x頁的資料，每頁顯示y條首先計算第x也的資料的開始編號是（x-1）*y所以這個sql語句應該這樣寫select * from user limit (x-1)*y,y;

12,修改記錄　

mysql> update user set createtime = now() where username = ‘kenan‘;  Query OK, 2 rows affected (0.05 sec)  Rows matched: 2  Changed: 2  Warnings: 0

這裡把user表中的所有username叫做kenan的記錄更新createtime欄位，更新為目前時間

13，刪除記錄

mysql> delete from user where username = ‘lele‘;  Query OK, 1 row affected (0.06 sec) 

這裡刪除user表中所有username叫做lele的記錄

原文連結：http://soukenan.blog.51cto.com/5130995/1045340

PHP之路——MySql基礎動作陳述式