MySQL basics
@1: MySQL有三大类数据类型, 分别为数字、日期\时间、字符串, 这三大类中又更细致的划分了许多子类型:
数字类型
整数: tinyint、smallint、mediumint、int、bigint
浮点数: float、double、real、decimal
日期和时间
date、time、datetime、timestamp、year
字符串类型
字符串: char、varchar
文本: tinytext、text、mediumtext、longtext
二进制(可用来存储图片、音乐等): tinyblob、blob、mediumblob、longblob
@2: 在MySQL中创建数据库中的表:
mysql> use db1;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> create table users(
-> id int(2) not null primary key auto_increment,
-> username varchar(40),
-> passwd text,
-> email text)default charset=utf8;
Query OK, 0 rows affected (0.05 sec)
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| users |
+---------------+
1 row in set (0.00 sec)
mysql> desc users;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(2) | NO | PRI | NULL | auto_increment |
| username | varchar(40) | YES | | NULL | |
| passwd | text | YES | | NULL | |
| email | text | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> insert into users(username, passwd, email)
-> values("lxw", "123", "lxw.ucas@gmail.com");
Query OK, 1 row affected (0.01 sec)
mysql> select * from users;
+----+----------+--------+--------------------+
| id | username | passwd | email |
+----+----------+--------+--------------------+
| 1 | lxw | 123 | lxw.ucas@gmail.com |
+----+----------+--------+--------------------+
1 row in set (0.01 sec)
desc table_name命令用于显示表的结构.
@3: alter table 语句用于创建后对表的修改, 基础用法如下:
添加列 alter table 表名 add 列名 列数据类型 [after 插入位置];
在表的最后追加列 address: alter table students add address char(60);
在名为 age 的列后插入列 birthday: alter table students add birthday date after age;
修改列 alter table 表名 change 列名称 列新名称 新数据类型;
将表 tel 列改名为 telphone: alter table students change tel telphone char(13) default "-";
将 name 列的数据类型改为 char(16): alter table students change name name char(16) not null;
删除列 alter table 表名 drop 列名称;
删除 birthday 列: alter table students drop birthday;
重命名表 alter table 表名 rename 新表名;
重命名 students 表为 workmates: alter table students rename workmates;
删除整张表 drop table 表名;
删除 workmates 表: drop table workmates;
删除整个数据库 drop database 数据库名;
删除 samp_db 数据库: drop database samp_db;
@4:
mysql> select * from users;
+----+----------+--------+-------------------+
| id | username | passwd | email |
+----+----------+--------+-------------------+
| 1 | lxw | 123 | lxwin@foxmail.com |
| 2 | lxw | 123 | lxwin@foxmail.com |
| 3 | lxw | 123 | lxwin@foxmail.com |
+----+----------+--------+-------------------+
3 rows in set (0.04 sec)
mysql> select * from users where id>2&username="lxw";
Empty set, 3 warnings (0.00 secG
mysql> select * from users where id>2 and username="lxw";
+----+----------+--------+-------------------+
| id | username | passwd | email |
+----+----------+--------+-------------------+
| 3 | lxw | 123 | lxwin@foxmail.com |
+----+----------+--------+-------------------+
1 row in set (0.02 sec)
mysql> select * from users where username like "%xw%";
+----+----------+--------+-------------------+
| id | username | passwd | email |
+----+----------+--------+-------------------+
| 1 | lxw | 123 | lxwin@foxmail.com |
| 2 | lxw | 123 | lxwin@foxmail.com |
| 3 | lxw | 123 | lxwin@foxmail.com |
+----+----------+--------+-------------------+
3 rows in set (0.00 sec)
mysql> update users set username="wxl" where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from users;
+----+----------+--------+-------------------+
| id | username | passwd | email |
+----+----------+--------+-------------------+
| 1 | lxw | 123 | lxwin@foxmail.com |
| 2 | wxl | 123 | lxwin@foxmail.com |
| 3 | lxw | 123 | lxwin@foxmail.com |
+----+----------+--------+-------------------+
3 rows in set (0.00 sec)
mysql> select * from users;
+----+----------+--------+---------------------+
| id | username | passwd | email |
+----+----------+--------+---------------------+
| 1 | lxw | 123 | lxwin@foxmail.com |
| 2 | wxl | 456 | wxl24life@gmail.com |
| 3 | lxw | 123 | lxwin@foxmail.com |
+----+----------+--------+---------------------+
3 rows in set (0.00 sec)
mysql> update users set username="lfc", passwd="789" where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from users;
+----+----------+--------+---------------------+
| id | username | passwd | email |
+----+----------+--------+---------------------+
| 1 | lxw | 123 | lxwin@foxmail.com |
| 2 | wxl | 456 | wxl24life@gmail.com |
| 3 | lfc | 789 | lxwin@foxmail.com |
+----+----------+--------+---------------------+
3 rows in set (0.00 sec)
mysql> update users set username="lfc", passwd=passwd+1, email=email+1 where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> desc users;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| username | varchar(40) | YES | | NULL | |
| passwd | text | YES | | NULL | |
| email | text | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> select * from users;
+----+----------+--------+---------------------+
| id | username | passwd | email |
+----+----------+--------+---------------------+
| 1 | lxw | 123 | lxwin@foxmail.com |
| 2 | wxl | 456 | wxl24life@gmail.com |
| 3 | lfc | 791 | 1 |
+----+----------+--------+---------------------+
3 rows in set (0.00 sec)
mysql> delete from users where id = 3;
Query OK, 1 row affected (0.01 sec)
mysql> select * from users;
+----+----------+--------+---------------------+
| id | username | passwd | email |
+----+----------+--------+---------------------+
| 1 | lxw | 123 | lxwin@foxmail.com |
| 2 | wxl | 456 | wxl24life@gmail.com |
+----+----------+--------+---------------------+
2 rows in set (0.00 sec)
where 子句不仅仅支持 "where 列名 = 值" 这种名等于值的查询形式, 对一般的比较运算的运算符都是支持的, 例如
=、>、<、>=、<、!= 以及一些扩展运算符 is [not] null、in、like 等等。 还可以对查询条件使用 or 和 and 进行组
合查询。
Reference:
21分钟MySQL入门教程:http://www.cnblogs.com/mr-wid/archive/2013/05/09/3068229.html#c8
