第15章 MySQL数据库

程序员文章站 2024-01-25 14:49:04

...

打开 mySQL 客户端
第15章 MySQL数据库

博客分类：阅-PHP

Demo1.txt

Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.51b-community-nt-log MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select version();
+--------------------------+
| version()                |
+--------------------------+
| 5.0.51b-community-nt-log |
+--------------------------+
1 row in set (0.01 sec)

mysql> select version(),current_date();
+--------------------------+----------------+
| version()                | current_date() |
+--------------------------+----------------+
| 5.0.51b-community-nt-log | 2015-04-23     |
+--------------------------+----------------+
1 row in set (0.02 sec)

mysql> select version() as version;
+--------------------------+
| version                  |
+--------------------------+
| 5.0.51b-community-nt-log |
+--------------------------+
1 row in set (0.00 sec)

mysql> select version() as '版本号';
+--------------------------+
| 版本号                       |
+--------------------------+
| 5.0.51b-community-nt-log |
+--------------------------+
1 row in set (0.00 sec)

mysql> select (20+5)*4;
+----------+
| (20+5)*4 |
+----------+
|      100 |
+----------+
1 row in set (0.00 sec)

mysql> select (20+5)*4 as result;
+--------+
| result |
+--------+
|    100 |
+--------+
1 row in set (0.00 sec)

mysql> select version();
+--------------------------+
| version()                |
+--------------------------+
| 5.0.51b-community-nt-log |
+--------------------------+
1 row in set (0.01 sec)

mysql> select
    -> version();
+--------------------------+
| version()                |
+--------------------------+
| 5.0.51b-community-nt-log |
+--------------------------+
1 row in set (0.00 sec)

mysql> select
    ->
    ->
    -> now()
    ->
    -> ;
+---------------------+
| now()               |
+---------------------+
| 2015-04-23 13:34:26 |
+---------------------+
1 row in set (0.02 sec)

mysql> select user(),NOW();
+----------------+---------------------+
| user()         | NOW()               |
+----------------+---------------------+
| root@localhost | 2015-04-23 13:35:53 |
+----------------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT USER();SELECT NOW();
+----------------+
| USER()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

+---------------------+
| NOW()               |
+---------------------+
| 2015-04-23 13:36:20 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECIT
    -> \c
mysql> exit;

Demo2.txt

Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.0.51b-community-nt-log MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT VERSION();
+--------------------------+
| VERSION()                |
+--------------------------+
| 5.0.51b-community-nt-log |
+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT VERSION() AS '版本号';
+--------------------------+
| 版本号                       |
+--------------------------+
| 5.0.51b-community-nt-log |
+--------------------------+
1 row in set (0.00 sec)

mysql> fsdfssg
    -> \c
mysql>

Demo3.txt

'ab  '

//文本型
//这个如果采用 CHAR 类型，客格也是一个字符
//CHAR 定长类型 //访问速度快

//CHAR 用于，性别，密码

//VARCHAR 会把后面的空格给删除，可变长度的类型，自己本身的长度 +1
//占用容量小

//用户名，文章标题

//备注型：string + 1

//比较常用的是 TEXT,用于备注，大文章，贴子，新闻内容

Demo4.txt

Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.0.51b-community-nt-log MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| guest              |
| mysql              |
| phpmyadmin         |
| test               |
| ultrax             |
+--------------------+
6 rows in set (0.05 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| guest              |
| mysql              |
| phpmyadmin         |
| test               |
| ultrax             |
+--------------------+
6 rows in set (0.00 sec)

mysql> USE guest;
Database changed
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| guest      |
+------------+
1 row in set (0.00 sec)

mysql> SHOW TABLES;
+-----------------+
| Tables_in_guest |
+-----------------+
| g_article       |
| g_flower        |
| g_friend        |
| g_message       |
| g_photo         |
| g_photocomment  |
| g_photolist     |
| g_system        |
| g_users         |
+-----------------+
9 rows in set (0.00 sec)

mysql> SELECT * FROM g_friend;
+------+--------------+-----------------+----------+---------------------+
| G_ID | G_ToUser     | G_FromUser      | G_Degree | G_Date              |
+------+--------------+-----------------+----------+---------------------+
|    1 | 澶у姘撮ズ     | 鐐庢棩            |        0 | 2009-04-21 14:56:03 |

|    2 | 灏忕嚂瀛?      | 鐐庢棩            |        0 | 2009-05-03 10:51:11 |
|    5 | 鐐庢棩         | 妯辨灏忎父瀛?     |        1 | 2009-05-03 10:56:31 |

+------+--------------+-----------------+----------+---------------------+
3 rows in set (0.02 sec)

mysql> SET NAMES GBK;
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT * FROM g_friend;
+------+----------+------------+----------+---------------------+
| G_ID | G_ToUser | G_FromUser | G_Degree | G_Date              |
+------+----------+------------+----------+---------------------+
|    1 | 大娘水饺        | 炎日           |        0 | 2009-04-21 14:56:03 |
|    2 | 小燕子        | 炎日           |        0 | 2009-05-03 10:51:11 |
|    5 | 炎日         | 樱桃小丸子         |        1 | 2009-05-03 10:56:31 |
+------+----------+------------+----------+---------------------+
3 rows in set (0.02 sec)

mysql>

Demo5.txt

+-----------------+
1 row in set (0.00 sec)

mysql> select * from users;
ERROR 1146 (42S02): Table 'books.users' doesn't exist
mysql> selct * form uses;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'selc
 * form uses' at line 1
mysql> select * from uses;
Empty set (0.00 sec)

mysql> select * from uses;
Empty set (0.00 sec)

mysql> DESCRIBE USES;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(20) | YES  |     | NULL    |       |
| sex      | char(1)     | YES  |     | NULL    |       |
| birth    | datetime    | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

mysql> INSERT INFO uses (username,sex birth) VALUES('oneStopWeb','0',NOW());
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'uses
(username,sex birth) VALUES('oneStopWeb','0',NOW())' at line 1
mysql> INSERT INFO uses (username,sex,birth) VALUES('oneStopWeb','0',NOW());
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'uses
(username,sex,birth) VALUES('oneStopWeb','0',NOW())' at line 1
mysql> INSERT INTO uses (username,sex,birth) VALUES('oneStopWeb','0',NOW());
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM uses;
+------------+------+---------------------+
| username   | sex  | birth               |
+------------+------+---------------------+
| oneStopWeb | 0    | 2015-04-23 14:46:16 |
+------------+------+---------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO uses (username,sex,birth) VALUES('chaoyv','1',NOW());
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM uses;
+------------+------+---------------------+
| username   | sex  | birth               |
+------------+------+---------------------+
| oneStopWeb | 0    | 2015-04-23 14:46:16 |
| chaoyv     | 1    | 2015-04-23 14:47:45 |
+------------+------+---------------------+
2 rows in set (0.00 sec)

mysql> INSERT INTO uses (username,sex,birth) VALUES('一站式建网站','0',NOW());
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SET NAMES GBK;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM uses;
+------------+------+---------------------+
| username   | sex  | birth               |
+------------+------+---------------------+
| oneStopWeb | 0    | 2015-04-23 14:46:16 |
| chaoyv     | 1    | 2015-04-23 14:47:45 |
| ???        | 0    | 2015-04-23 14:48:24 |
+------------+------+---------------------+
3 rows in set (0.00 sec)

mysql> INSERT INTO uses (username,sex,birth) VALUES('一站式建网站','0',NOW());
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM USES;
+--------------+------+---------------------+
| username     | sex  | birth               |
+--------------+------+---------------------+
| oneStopWeb   | 0    | 2015-04-23 14:46:16 |
| chaoyv       | 1    | 2015-04-23 14:47:45 |
| ???          | 0    | 2015-04-23 14:48:24 |
| 一站式建网站         | 0    | 2015-04-23 14:49:27 |
+--------------+------+---------------------+
4 rows in set (0.00 sec)

mysql> INSERT INTO uses (username,sex,birth) VALUES('冼生','0',NOW());
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM USES;
+--------------+------+---------------------+
| username     | sex  | birth               |
+--------------+------+---------------------+
| oneStopWeb   | 0    | 2015-04-23 14:46:16 |
| chaoyv       | 1    | 2015-04-23 14:47:45 |
| ???          | 0    | 2015-04-23 14:48:24 |
| 一站式建网站         | 0    | 2015-04-23 14:49:27 |
| 冼生             | 0    | 2015-04-23 14:50:14 |
+--------------+------+---------------------+
5 rows in set (0.02 sec)

mysql> SELECT username FROM USES;
+--------------+
| username     |
+--------------+
| oneStopWeb   |
| chaoyv       |
| ???          |
| 一站式建网站         |
| 冼生             |
+--------------+
5 rows in set (0.00 sec)

mysql> SELECT username,sex FROM USES;
+--------------+------+
| username     | sex  |
+--------------+------+
| oneStopWeb   | 0    |
| chaoyv       | 1    |
| ???          | 0    |
| 一站式建网站         | 0    |
| 冼生             | 0    |
+--------------+------+
5 rows in set (0.01 sec)

mysql> SELECT * FROM USES;
+--------------+------+---------------------+
| username     | sex  | birth               |
+--------------+------+---------------------+
| oneStopWeb   | 0    | 2015-04-23 14:46:16 |
| chaoyv       | 1    | 2015-04-23 14:47:45 |
| ???          | 0    | 2015-04-23 14:48:24 |
| 一站式建网站         | 0    | 2015-04-23 14:49:27 |
| 冼生             | 0    | 2015-04-23 14:50:14 |
+--------------+------+---------------------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM uses WHERE sex = '1';
+----------+------+---------------------+
| username | sex  | birth               |
+----------+------+---------------------+
| chaoyv   | 1    | 2015-04-23 14:47:45 |
+----------+------+---------------------+
1 row in set (0.02 sec)

mysql> DELETE FROM uses WHERE SEX = '1';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM USES;
+--------------+------+---------------------+
| username     | sex  | birth               |
+--------------+------+---------------------+
| oneStopWeb   | 0    | 2015-04-23 14:46:16 |
| ???          | 0    | 2015-04-23 14:48:24 |
| 一站式建网站         | 0    | 2015-04-23 14:49:27 |
| 冼生             | 0    | 2015-04-23 14:50:14 |
+--------------+------+---------------------+
4 rows in set (0.00 sec)

mysql> DELETE FROM uses WHERE username = '???';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM USES;
+--------------+------+---------------------+
| username     | sex  | birth               |
+--------------+------+---------------------+
| oneStopWeb   | 0    | 2015-04-23 14:46:16 |
| ???          | 0    | 2015-04-23 14:48:24 |
| 一站式建网站         | 0    | 2015-04-23 14:49:27 |
| 冼生             | 0    | 2015-04-23 14:50:14 |
+--------------+------+---------------------+
4 rows in set (0.02 sec)

mysql> DELETE FROM uses WHERE username = '%?%';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM USES;
+--------------+------+---------------------+
| username     | sex  | birth               |
+--------------+------+---------------------+
| oneStopWeb   | 0    | 2015-04-23 14:46:16 |
| ???          | 0    | 2015-04-23 14:48:24 |
| 一站式建网站         | 0    | 2015-04-23 14:49:27 |
| 冼生             | 0    | 2015-04-23 14:50:14 |
+--------------+------+---------------------+
4 rows in set (0.00 sec)

mysql> DELETE FROM uses WHERE username like '???';
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT * FROM USES;
+--------------+------+---------------------+
| username     | sex  | birth               |
+--------------+------+---------------------+
| oneStopWeb   | 0    | 2015-04-23 14:46:16 |
| ???          | 0    | 2015-04-23 14:48:24 |
| 一站式建网站         | 0    | 2015-04-23 14:49:27 |
| 冼生             | 0    | 2015-04-23 14:50:14 |
+--------------+------+---------------------+
4 rows in set (0.00 sec)

mysql> DELETE FROM uses WHERE username like '%?%';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM USES;
+--------------+------+---------------------+
| username     | sex  | birth               |
+--------------+------+---------------------+
| oneStopWeb   | 0    | 2015-04-23 14:46:16 |
| ???          | 0    | 2015-04-23 14:48:24 |
| 一站式建网站         | 0    | 2015-04-23 14:49:27 |
| 冼生             | 0    | 2015-04-23 14:50:14 |
+--------------+------+---------------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM USES ORDER BY birth DESC;
+--------------+------+---------------------+
| username     | sex  | birth               |
+--------------+------+---------------------+
| 冼生             | 0    | 2015-04-23 14:50:14 |
| 一站式建网站         | 0    | 2015-04-23 14:49:27 |
| ???          | 0    | 2015-04-23 14:48:24 |
| oneStopWeb   | 0    | 2015-04-23 14:46:16 |
+--------------+------+---------------------+
4 rows in set (0.00 sec)

mysql> SHOW TABLES;
+-----------------+
| Tables_in_books |
+-----------------+
| uses            |
+-----------------+
1 row in set (0.00 sec)

mysql> DROP TABLE USES;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW TABLES;
Empty set (0.00 sec)

mysql> SELECT * FROM USES;
ERROR 1146 (42S02): Table 'books.uses' doesn't exist
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| books              |
| guest              |
| mysql              |
| phpmyadmin         |
| test               |
| ultrax             |
+--------------------+
7 rows in set (0.00 sec)

mysql> DROP DATABASE BOOKS;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| guest              |
| mysql              |
| phpmyadmin         |
| test               |
| ultrax             |
+--------------------+
6 rows in set (0.00 sec)

mysql> use guest;
Database changed
mysql> select * from g_friend;
+------+----------+------------+----------+---------------------+
| G_ID | G_ToUser | G_FromUser | G_Degree | G_Date              |
+------+----------+------------+----------+---------------------+
|    1 | 大娘水饺        | 炎日           |        0 | 2009-04-21 14:56:03 |
|    2 | 小燕子        | 炎日           |        0 | 2009-05-03 10:51:11 |
|    5 | 炎日         | 樱桃小丸子         |        1 | 2009-05-03 10:56:31 |
+------+----------+------------+----------+---------------------+
3 rows in set (0.00 sec)

mysql> UPDATE g_friend set g_touser='oneStopWeb' where g_id =1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from g_friend;
+------+------------+------------+----------+---------------------+
| G_ID | G_ToUser   | G_FromUser | G_Degree | G_Date              |
+------+------------+------------+----------+---------------------+
|    1 | oneStopWeb | 炎日           |        0 | 2009-04-21 14:56:03 |
|    2 | 小燕子          | 炎日           |        0 | 2009-05-03 10:51:11 |
|    5 | 炎日           | 樱桃小丸子         |        1 | 2009-05-03 10:56:31 |

+------+------------+------------+----------+---------------------+
3 rows in set (0.00 sec)

mysql>

Demo6.txt

Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.0.51b-community-nt-log MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT CONCAT('Mr.','One');
+---------------------+
| CONCAT('Mr.','One') |
+---------------------+
| Mr.One              |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT CONCAT('Mr.','One') AS '两个字符串相连';
+----------------+
| 两个字符串相连              |
+----------------+
| Mr.One         |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT LENGTH('OneStopWeb');
+----------------------+
| LENGTH('OneStopWeb') |
+----------------------+
|                   10 |
+----------------------+
1 row in set (0.02 sec)

mysql> SELECT LEFT('2');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ')' at
 line 1
mysql> SELECT LEFT('OneStopWeb',2);
+----------------------+
| LEFT('OneStopWeb',2) |
+----------------------+
| On                   |
+----------------------+
1 row in set (0.02 sec)

mysql> SELECT TRIM('    5454     ');
+-----------------------+
| TRIM('    5454     ') |
+-----------------------+
| 5454                  |
+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('This is a OneStopWeb',2,5);
+---------------------------------------+
| SUBSTRING('This is a OneStopWeb',2,5) |
+---------------------------------------+
| his i                                 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT MD5('123456');
+----------------------------------+
| MD5('123456')                    |
+----------------------------------+
| e10adc3949ba59abbe56e057f20f883e |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT ABS(-5);
+---------+
| ABS(-5) |
+---------+
|       5 |
+---------+
1 row in set (0.01 sec)

mysql> SELECT ROUND(4.50);
+-------------+
| ROUND(4.50) |
+-------------+
|           5 |
+-------------+
1 row in set (0.00 sec)

mysql> SELECT FLOOR(4.67);
+-------------+
| FLOOR(4.67) |
+-------------+
|           4 |
+-------------+
1 row in set (0.00 sec)

mysql> SELECT CEILING(4.07);
+---------------+
| CEILING(4.07) |
+---------------+
|             5 |
+---------------+
1 row in set (0.00 sec)

mysql> SELECT RAND();
+------------------+
| RAND()           |
+------------------+
| 0.86286346042795 |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT RAND();
+------------------+
| RAND()           |
+------------------+
| 0.58029021376771 |
+------------------+
1 row in set (0.01 sec)

mysql> SELECT RAND()*10;
+-----------------+
| RAND()*10       |
+-----------------+
| 3.1286071828833 |
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT ROUND(RAND()*10);
+------------------+
| ROUND(RAND()*10) |
+------------------+
|                8 |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2015-04-23 16:39:11 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT HOUR(NOW());
+-------------+
| HOUR(NOW()) |
+-------------+
|          16 |
+-------------+
1 row in set (0.00 sec)

mysql> SELECT MINUTE(NOW());
+---------------+
| MINUTE(NOW()) |
+---------------+
|            40 |
+---------------+
1 row in set (0.00 sec)

mysql> SELECT DAYNAME(NOW());
+----------------+
| DAYNAME(NOW()) |
+----------------+
| Thursday       |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT DAYOFMONTY(NOW());
ERROR 1046 (3D000): No database selected
mysql> SELECT DAYOFMONTH(NOW());
+-------------------+
| DAYOFMONTH(NOW()) |
+-------------------+
|                23 |
+-------------------+
1 row in set (0.02 sec)

mysql> SELECT MONTHNAME(NOW());
+------------------+
| MONTHNAME(NOW()) |
+------------------+
| April            |
+------------------+
1 row in set (0.01 sec)

mysql> SELECT MONTH(NOW());
+--------------+
| MONTH(NOW()) |
+--------------+
|            4 |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT YEAR(NOW());
+-------------+
| YEAR(NOW()) |
+-------------+
|        2015 |
+-------------+
1 row in set (0.00 sec)

mysql> SELECT CORDATE();
ERROR 1046 (3D000): No database selected
mysql> SELECT CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2015-04-23 |
+------------+
1 row in set (0.02 sec)

mysql> SELECT CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 16:45:35  |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2015-04-23 16:45:48 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT DATA_FORMAT(NOT(),'%y');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '),'%y
')' at line 1
mysql> SELECT DATE\_FORMAT(NOT(),'%Y');
ERROR:
Unknown command '\_'.
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '\_FOR
MAT(NOT(),'%Y')' at line 1
mysql> SELECT DATE_FORMAT(NOT(),'%Y');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '),'%Y
')' at line 1
mysql> SELECT DATE_FORMAT(NOW(),'%Y');
+-------------------------+
| DATE_FORMAT(NOW(),'%Y') |
+-------------------------+
| 2015                    |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT(NOW(),'%e');
+-------------------------+
| DATE_FORMAT(NOW(),'%e') |
+-------------------------+
| 23                      |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT(NOW(),'%D');
+-------------------------+
| DATE_FORMAT(NOW(),'%D') |
+-------------------------+
| 23rd                    |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT(NOW(),'%W');
+-------------------------+
| DATE_FORMAT(NOW(),'%W') |
+-------------------------+
| Thursday                |
+-------------------------+
1 row in set (0.02 sec)

mysql> SELECT DATE_FORMAT(NOW(),'%m');
+-------------------------+
| DATE_FORMAT(NOW(),'%m') |
+-------------------------+
| 04                      |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT(NOW(),'%c');
+-------------------------+
| DATE_FORMAT(NOW(),'%c') |
+-------------------------+
| 4                       |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT(NOW(),'%T');
+-------------------------+
| DATE_FORMAT(NOW(),'%T') |
+-------------------------+
| 17:03:35                |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT(NOW(),'%r');
+-------------------------+
| DATE_FORMAT(NOW(),'%r') |
+-------------------------+
| 05:03:43 PM             |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT(NOW(),'%p');
+-------------------------+
| DATE_FORMAT(NOW(),'%p') |
+-------------------------+
| PM                      |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT(NOW(),'%H,%i,%S,%p');
+----------------------------------+
| DATE_FORMAT(NOW(),'%H,%i,%S,%p') |
+----------------------------------+
| 17,04,38,PM                      |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT(NOW(),'%H:%i:%S:%p');
+----------------------------------+
| DATE_FORMAT(NOW(),'%H:%i:%S:%p') |
+----------------------------------+
| 17:05:00:PM                      |
+----------------------------------+
1 row in set (0.00 sec)

mysql>

Demo7.txt

id:学员的编号，性质：每个人的编号不得重复，而且可以排序，
那么 ID 这个字段，采用的是整型，范围：0-99
	TINYINT(2) 0-255 UNSIGEND 表示无符号 TINYINT(2) 整数
	NOT NUll 表示这个字段不能为空
	AUTO_INCREMENT 表示 1，2，3，4，5 自动新增累计
	主键：表示一个字段唯一性，不得重复，方便搜索与排序

Demo8.txt

Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.0.51b-community-nt-log MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| guest              |
| mysql              |
| phpmyadmin         |
| test               |
| ultrax             |
+--------------------+
6 rows in set (0.02 sec)

mysql> CREATA DATABASE school;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'CREAT
A DATABASE school' at line 1
mysql> CREATE DATABASE school;
Query OK, 1 row affected (0.01 sec)

mysql> SHOW BATABASES;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'BATAB
ASES' at line 1
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| guest              |
| mysql              |
| phpmyadmin         |
| school             |
| test               |
| ultrax             |
+--------------------+
7 rows in set (0.00 sec)

mysql> SHOW school;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'schoo
l' at line 1
mysql> USE school;
Database changed
mysql> SHOW school;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'schoo
l' at line 1
mysql> show TABLES;
Empty set (0.00 sec)

mysql> CREATA TABLE grade(
    -> id TINYINT(2) UNSIGEND NOT NULL AUTO_INCREMENT,
    -> name VARCHAR(20) NOT NULL,
    -> email VARCHAR(40),
    -> point TINYINT(3) UNSIGNED NOT NULL,
    -> regdate DATETIME NOT NULL,
    -> PRIMARY KEY(id)
    -> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'CREAT
A TABLE grade(
id TINYINT(2) UNSIGEND NOT NULL AUTO_INCREMENT,
name VARCHAR' at line 1
mysql> CREATA TABLE grade(
    -> id TINYINT(2) UNSIGEND NOT NULL AUTO_INCREMENT,
    -> name VARCHAR(20) NOT NULL,
    -> \c
mysql> CREATE TABLE grade(
    -> id TINYINT(2) UNSIGEND NOT NULL AUTO_INCREMENT,
    -> name VARCHAR(20) NOT NULL,
    -> email VARCHAR(40),
    -> point TINYINT(3) UNSIGNED NOT NULL,
    -> regdate DATETIME NOT NULL,
    -> PRIMARY KEY(id)
    -> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'UNSIG
END NOT NULL AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
email VARCHAR(40),
' at line 2
mysql> CREATE TABLE grade(
    -> id TINYINT(2) UNSIGEND NOT NULL AUTO_INCREMENT,\C
ERROR:
Usage: \C char_setname | charset charset_name
    -> \c
mysql> CREATE TABLE grade(
    -> id TINYINT(2) UNSIGEND NOT NULL AUTO_INCREMENT,
    -> name VARCHAR(20) NOT NULL,
    -> email VARCHAR(40),
    -> point TINYINT(3) UNSIGNED NOT NULL,
    -> regdate DATETIME NOT NULL,
    -> PRIMARY KEY(id)
    -> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'UNSIG
END NOT NULL AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
email VARCHAR(40),
' at line 2
mysql> CREATE TABLE grade(
    -> id TINYINT(2) UNSIGNED NOT NULL AUTO_INCREMENT,
    -> name VARCHAR(20) NOT NULL,
    -> email VARCHAR(40),
    -> point TINYINT(3) UNSIGNED NOT NULL,
    -> regdate DATETIME NOT NULL,
    -> PRIMARY KEY(id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW TABLES;
+------------------+
| Tables_in_school |
+------------------+
| grade            |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM grade;
Empty set (0.00 sec)

mysql> DESC grade;
+---------+---------------------+------+-----+---------+----------------+
| Field   | Type                | Null | Key | Default | Extra          |
+---------+---------------------+------+-----+---------+----------------+
| id      | tinyint(2) unsigned | NO   | PRI | NULL    | auto_increment |
| name    | varchar(20)         | NO   |     | NULL    |                |
| email   | varchar(40)         | YES  |     | NULL    |                |
| point   | tinyint(3) unsigned | NO   |     | NULL    |                |
| regdate | datetime            | NO   |     | NULL    |                |
+---------+---------------------+------+-----+---------+----------------+
5 rows in set (0.02 sec)

mysql> INSERT INTO grage (name,email,point,regdate) VALUES ('oneStopWeb','oneSto
pWeb@163.com',88,NOW())
    -> ;
ERROR 1146 (42S02): Table 'school.grage' doesn't exist
mysql> INSERT INTO grade (name,email,point,regdate) VALUES ('oneStopWeb','oneSto
pWeb@163.com',88,NOW());
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM grade;
+----+------------+--------------------+-------+---------------------+
| id | name       | email              | point | regdate             |
+----+------------+--------------------+-------+---------------------+
|  1 | oneStopWeb | oneStopWeb@163.com |    88 | 2015-04-23 17:45:02 |
+----+------------+--------------------+-------+---------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO grade (name,email,point,regdate) VALUES ('chaoyi','chaoyi@163
.com',92,NOW());
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO grade (name,email,point,regdate) VALUES ('chaoyi77','chaoyi@1
63.com',92,NOW());
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO grade (name,email,point,regdate) VALUES ('chaoyv','chaoyv@163
.com',32,NOW());
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM grade;
+----+------------+--------------------+-------+---------------------+
| id | name       | email              | point | regdate             |
+----+------------+--------------------+-------+---------------------+
|  1 | oneStopWeb | oneStopWeb@163.com |    88 | 2015-04-23 17:45:02 |
|  2 | chaoyi     | chaoyi@163.com     |    92 | 2015-04-23 17:46:45 |
|  3 | chaoyi77   | chaoyi@163.com     |    92 | 2015-04-23 17:46:53 |
|  4 | chaoyv     | chaoyv@163.com     |    32 | 2015-04-23 17:47:17 |
+----+------------+--------------------+-------+---------------------+
4 rows in set (0.00 sec)

mysql> INSERT INTO grade (name,email,point,regdate) VALUES ('yike',NULL,32,NOW()
);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT id,name,email FROM grade;
+----+------------+--------------------+
| id | name       | email              |
+----+------------+--------------------+
|  1 | oneStopWeb | oneStopWeb@163.com |
|  2 | chaoyi     | chaoyi@163.com     |
|  3 | chaoyi77   | chaoyi@163.com     |
|  4 | chaoyv     | chaoyv@163.com     |
|  5 | yike       | NULL               |
+----+------------+--------------------+
5 rows in set (0.00 sec)

mysql>

Demo9.txt

Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.0.51b-community-nt-log MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> USE grade;
ERROR 1049 (42000): Unknown database 'grade'
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| guest              |
| mysql              |
| phpmyadmin         |
| school             |
| test               |
| ultrax             |
+--------------------+
7 rows in set (0.00 sec)

mysql> CREATA DATABASE school;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'CREAT
A DATABASE school' at line 1
mysql> USE school
Database changed
mysql> select * from grade;
+----+------------+--------------------+-------+---------------------+
| id | name       | email              | point | regdate             |
+----+------------+--------------------+-------+---------------------+
|  1 | oneStopWeb | oneStopWeb@163.com |    88 | 2015-04-23 17:45:02 |
|  2 | chaoyi     | chaoyi@163.com     |    92 | 2015-04-23 17:46:45 |
|  3 | chaoyi77   | chaoyi@163.com     |    92 | 2015-04-23 17:46:53 |
|  4 | chaoyv     | chaoyv@163.com     |    32 | 2015-04-23 17:47:17 |
|  5 | yike       | NULL               |    32 | 2015-04-23 17:51:27 |
+----+------------+--------------------+-------+---------------------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM grade WHERE name='chaoyi';
+----+--------+----------------+-------+---------------------+
| id | name   | email          | point | regdate             |
+----+--------+----------------+-------+---------------------+
|  2 | chaoyi | chaoyi@163.com |    92 | 2015-04-23 17:46:45 |
+----+--------+----------------+-------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM grade WHERE porint > 90;
ERROR 1054 (42S22): Unknown column 'porint' in 'where clause'
mysql> SELECT * FROM grade WHERE point > 90;
+----+----------+----------------+-------+---------------------+
| id | name     | email          | point | regdate             |
+----+----------+----------------+-------+---------------------+
|  2 | chaoyi   | chaoyi@163.com |    92 | 2015-04-23 17:46:45 |
|  3 | chaoyi77 | chaoyi@163.com |    92 | 2015-04-23 17:46:53 |
+----+----------+----------------+-------+---------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM grade WHERE email IS NOT NULL;
+----+------------+--------------------+-------+---------------------+
| id | name       | email              | point | regdate             |
+----+------------+--------------------+-------+---------------------+
|  1 | oneStopWeb | oneStopWeb@163.com |    88 | 2015-04-23 17:45:02 |
|  2 | chaoyi     | chaoyi@163.com     |    92 | 2015-04-23 17:46:45 |
|  3 | chaoyi77   | chaoyi@163.com     |    92 | 2015-04-23 17:46:53 |
|  4 | chaoyv     | chaoyv@163.com     |    32 | 2015-04-23 17:47:17 |
+----+------------+--------------------+-------+---------------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM grade WHERE email IS NULL;
+----+------+-------+-------+---------------------+
| id | name | email | point | regdate             |
+----+------+-------+-------+---------------------+
|  5 | yike | NULL  |    32 | 2015-04-23 17:51:27 |
+----+------+-------+-------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM grade WHERE point BETWEEN 70 AND 90;
+----+------------+--------------------+-------+---------------------+
| id | name       | email              | point | regdate             |
+----+------------+--------------------+-------+---------------------+
|  1 | oneStopWeb | oneStopWeb@163.com |    88 | 2015-04-23 17:45:02 |
+----+------------+--------------------+-------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM grade WHERE point IN(32,88,92);
+----+------------+--------------------+-------+---------------------+
| id | name       | email              | point | regdate             |
+----+------------+--------------------+-------+---------------------+
|  1 | oneStopWeb | oneStopWeb@163.com |    88 | 2015-04-23 17:45:02 |
|  2 | chaoyi     | chaoyi@163.com     |    92 | 2015-04-23 17:46:45 |
|  3 | chaoyi77   | chaoyi@163.com     |    92 | 2015-04-23 17:46:53 |
|  4 | chaoyv     | chaoyv@163.com     |    32 | 2015-04-23 17:47:17 |
|  5 | yike       | NULL               |    32 | 2015-04-23 17:51:27 |
+----+------------+--------------------+-------+---------------------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM grade WHERE name = 'oneStopWeb' or name = 'chaoyi'
    -> ;
+----+------------+--------------------+-------+---------------------+
| id | name       | email              | point | regdate             |
+----+------------+--------------------+-------+---------------------+
|  1 | oneStopWeb | oneStopWeb@163.com |    88 | 2015-04-23 17:45:02 |
|  2 | chaoyi     | chaoyi@163.com     |    92 | 2015-04-23 17:46:45 |
+----+------------+--------------------+-------+---------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM grade WHERE email LIKE '%163.com';
+----+------------+--------------------+-------+---------------------+
| id | name       | email              | point | regdate             |
+----+------------+--------------------+-------+---------------------+
|  1 | oneStopWeb | oneStopWeb@163.com |    88 | 2015-04-23 17:45:02 |
|  2 | chaoyi     | chaoyi@163.com     |    92 | 2015-04-23 17:46:45 |
|  3 | chaoyi77   | chaoyi@163.com     |    92 | 2015-04-23 17:46:53 |
|  4 | chaoyv     | chaoyv@163.com     |    32 | 2015-04-23 17:47:17 |
+----+------------+--------------------+-------+---------------------+
4 rows in set (0.02 sec)

mysql> SELECT * FROM grade WHERE email LIKE '163.com%';
Empty set (0.00 sec)

mysql> SELECT * FROM grade WHERE email LIKE '%chaoyi%';
+----+----------+----------------+-------+---------------------+
| id | name     | email          | point | regdate             |
+----+----------+----------------+-------+---------------------+
|  2 | chaoyi   | chaoyi@163.com |    92 | 2015-04-23 17:46:45 |
|  3 | chaoyi77 | chaoyi@163.com |    92 | 2015-04-23 17:46:53 |
+----+----------+----------------+-------+---------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM grade WHERE email NOT LIKE '%chaoyi%';
+----+------------+--------------------+-------+---------------------+
| id | name       | email              | point | regdate             |
+----+------------+--------------------+-------+---------------------+
|  1 | oneStopWeb | oneStopWeb@163.com |    88 | 2015-04-23 17:45:02 |
|  4 | chaoyv     | chaoyv@163.com     |    32 | 2015-04-23 17:47:17 |
+----+------------+--------------------+-------+---------------------+
2 rows in set (0.01 sec)

mysql> SELECT * FROM grade ORDER BY DESC;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'DESC'
 at line 1
mysql> SELECT * FROM grade ORDER BY regdate DESC;
+----+------------+--------------------+-------+---------------------+
| id | name       | email              | point | regdate             |
+----+------------+--------------------+-------+---------------------+
|  5 | yike       | NULL               |    32 | 2015-04-23 17:51:27 |
|  4 | chaoyv     | chaoyv@163.com     |    32 | 2015-04-23 17:47:17 |
|  3 | chaoyi77   | chaoyi@163.com     |    92 | 2015-04-23 17:46:53 |
|  2 | chaoyi     | chaoyi@163.com     |    92 | 2015-04-23 17:46:45 |
|  1 | oneStopWeb | oneStopWeb@163.com |    88 | 2015-04-23 17:45:02 |
+----+------------+--------------------+-------+---------------------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM grade LIMIT 3;
+----+------------+--------------------+-------+---------------------+
| id | name       | email              | point | regdate             |
+----+------------+--------------------+-------+---------------------+
|  1 | oneStopWeb | oneStopWeb@163.com |    88 | 2015-04-23 17:45:02 |
|  2 | chaoyi     | chaoyi@163.com     |    92 | 2015-04-23 17:46:45 |
|  3 | chaoyi77   | chaoyi@163.com     |    92 | 2015-04-23 17:46:53 |
+----+------------+--------------------+-------+---------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM grade LIMIT 3,3;
+----+--------+----------------+-------+---------------------+
| id | name   | email          | point | regdate             |
+----+--------+----------------+-------+---------------------+
|  4 | chaoyv | chaoyv@163.com |    32 | 2015-04-23 17:47:17 |
|  5 | yike   | NULL           |    32 | 2015-04-23 17:51:27 |
+----+--------+----------------+-------+---------------------+
2 rows in set (0.00 sec)

mysql> UPDATE grade SET email='yy@qq.com' WHERE name='chaoyi77'
    -> ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM grade;
+----+------------+--------------------+-------+---------------------+
| id | name       | email              | point | regdate             |
+----+------------+--------------------+-------+---------------------+
|  1 | oneStopWeb | oneStopWeb@163.com |    88 | 2015-04-23 17:45:02 |
|  2 | chaoyi     | chaoyi@163.com     |    92 | 2015-04-23 17:46:45 |
|  3 | chaoyi77   | yy@qq.com          |    92 | 2015-04-23 17:46:53 |
|  4 | chaoyv     | chaoyv@163.com     |    32 | 2015-04-23 17:47:17 |
|  5 | yike       | NULL               |    32 | 2015-04-23 17:51:27 |
+----+------------+--------------------+-------+---------------------+
5 rows in set (0.00 sec)

mysql> DELETE FROM grade WHERE id=3;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM grade;
+----+------------+--------------------+-------+---------------------+
| id | name       | email              | point | regdate             |
+----+------------+--------------------+-------+---------------------+
|  1 | oneStopWeb | oneStopWeb@163.com |    88 | 2015-04-23 17:45:02 |
|  2 | chaoyi     | chaoyi@163.com     |    92 | 2015-04-23 17:46:45 |
|  4 | chaoyv     | chaoyv@163.com     |    32 | 2015-04-23 17:47:17 |
|  5 | yike       | NULL               |    32 | 2015-04-23 17:51:27 |
+----+------------+--------------------+-------+---------------------+
4 rows in set (0.00 sec)

mysql> SELECT AVG(point) FROM grade;
+------------+
| AVG(point) |
+------------+
|    61.0000 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT AVG(point) as avg FROM grade;
+---------+
| avg     |
+---------+
| 61.0000 |
+---------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) AS count FROM grade;
+-------+
| count |
+-------+
|     4 |
+-------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(email) AS count FROM grade;
+-------+
| count |
+-------+
|     3 |
+-------+
1 row in set (0.00 sec)

mysql> SELECT MAX(point) FROM grade;
+------------+
| MAX(point) |
+------------+
|         92 |
+------------+
1 row in set (0.02 sec)

mysql> SELECT SUM(point) FROM grade;
+------------+
| SUM(point) |
+------------+
|        244 |
+------------+
1 row in set (0.00 sec)

mysql> SHOW TABLE STATUS\G;
*************************** 1. row ***************************
           Name: grade
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 4
 Avg_row_length: 38
    Data_length: 192
Max_data_length: 281474976710655
   Index_length: 2048
      Data_free: 40
 Auto_increment: 6
    Create_time: 2015-04-23 17:39:39
    Update_time: 2015-04-23 18:22:36
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> OPTIMIXE TABLE grade;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'OPTIM
IXE TABLE grade' at line 1
mysql> OPTIMIZE TABLE grade;
+--------------+----------+----------+----------+
| Table        | Op       | Msg_type | Msg_text |
+--------------+----------+----------+----------+
| school.grade | optimize | status   | OK       |
+--------------+----------+----------+----------+
1 row in set (0.02 sec)

mysql> SHOW TABLE STATUS\G;
*************************** 1. row ***************************
           Name: grade
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 4
 Avg_row_length: 38
    Data_length: 152
Max_data_length: 281474976710655
   Index_length: 2048
      Data_free: 0
 Auto_increment: 6
    Create_time: 2015-04-23 17:39:39
    Update_time: 2015-04-23 18:24:59
     Check_time: 2015-04-23 18:24:59
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.02 sec)

ERROR:
No query specified

mysql>

localhost.sql

-- phpMyAdmin SQL Dump
-- version 2.10.3
-- http://www.phpmyadmin.net
-- 
-- 主机: localhost
-- 生成日期: 2015 年 04 月 23 日 10:56
-- 服务器版本: 5.0.51
-- PHP 版本: 5.2.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

-- 
-- 数据库: `school`
-- 
CREATE DATABASE `school` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `school`;

-- --------------------------------------------------------

-- 
-- 表的结构 `grade`
-- 

CREATE TABLE `grade` (
  `id` tinyint(2) unsigned NOT NULL auto_increment,
  `name` varchar(20) NOT NULL,
  `email` varchar(40) default NULL,
  `point` tinyint(3) unsigned NOT NULL,
  `regdate` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;

-- 
-- 导出表中的数据 `grade`
-- 

INSERT INTO `grade` VALUES (1, '一站式建网站', 'oneStopWeb@163.com', 97, '2015-04-23 18:38:28');
INSERT INTO `grade` VALUES (2, '吴者然', 'chaoyi@163.com', 89, '2015-04-09 18:40:06');
INSERT INTO `grade` VALUES (3, '李雷', 'lilei@qq.com', 60, '2015-04-09 18:40:57');
INSERT INTO `grade` VALUES (4, '杰克', 'jack@163.com', 78, '2015-04-08 18:41:45');
INSERT INTO `grade` VALUES (5, '伊客', 'yike@163.com', 64, '2015-04-11 18:42:17');

创建数据库和字符编码
第15章 MySQL数据库

博客分类：阅-PHP