SQL 基础知识总结

1、创建table

mysql> create table tmd(id int(11) not null,name varchar(100),ts timestamp);
Query OK, 0 rows affected (0.14 sec)

mysql> desc tmd;
+-------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+-------------------+-----------------------------+
| id | int(11) | NO | | NULL | |
| name | varchar(100) | YES | | NULL | |
| ts | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+--------------+------+-----+-------------------+-----------------------------+
3 rows in set (0.09 sec)

2、修改表字段属性

mysql> alter table tmd modify id int(9) not null;
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc tmd;
+-------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+-------------------+-----------------------------+
| id | int(9) | NO | | NULL | |
| name | varchar(100) | YES | | NULL | |
| ts | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+--------------+------+-----+-------------------+-----------------------------+
3 rows in set (0.06 sec)

3、从现有表创建新表

mysql> create table love as select * from tmd;
Query OK, 1 row affected (0.12 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from love;
+----+-------+---------------------+
| id | name | ts |
+----+-------+---------------------+
| 1 | ricky | 2011-12-14 00:35:14 |
+----+-------+---------------------+
1 row in set (0.00 sec)

4、删除表

drop table table_name；

5、主键约束

primary_key(id)

6、唯一性约束

unique

7、外键约束

foreign key emp_id_fk(emp_id) references emp(emp_id)

alter table emp_pay add constraint id_fk foreign key(emp_id) reference emp(emp_id);

8、check约束

foreign key emp_id_fk(emp_id) references emp(emp_id) constraint check_id check (id >10);

9、增删改查

insert into table_name values('','');

update table_name set column_name = 'value' [where condition];

delete from table_name [where condition];

select * from table_name

 

10、事务控制

事务控制的命令有3条：

    commit

    rollback

    savepoint

    事务控制仅用户dml命令中（insert、update、delete）。

commit:

mysql> delete from love where id < 2;

Query OK, 1 row affected (0.00 sec)

mysql> commit
-> ;
Query OK, 0 rows affected (0.00 sec)

 

rollback:

mysql> update love set name = 'end' where id =2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from love;
+----+------+---------------------+
| id | name | ts |
+----+------+---------------------+
| 2 | end | 2011-12-14 00:55:15 |
+----+------+---------------------+
1 row in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

11、查询

select、from、where、order by

mysql> select count(*) from love;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)

12、操作符

= ，<>， <， >

相等，不等性，小于、大于

is null

between and

where in

like操作符连接的匹配符有两种：

百分号（％） 代表零个字符、一个字符或多个字符

下划线（_）代表单个数字或字符

exists

unique

all 用于将某个数值同另一数据集中的每个数值进行比较

any

and

or

not： not in、 not exists、not between、 not like、is not null

加减乘除

13、统计查询结果

count

sum

max

min

avg

14、数据的排序以及分组

group by & order by

mysql> select name,count(*) from tmd group by name;
+-------+----------+
| name | count(*) |
+-------+----------+
| munto | 1 |
| ricky | 1 |
+-------+----------+

mysql> select name,count(*) from tmd group by name order by id;
+-------+----------+
| name | count(*) |
+-------+----------+
| ricky | 1 |
| munto | 1 |
+-------+----------+
2 rows in set (0.00 sec)

15、复杂的数据库查询

表连接

equijoins(inner join)

nature joins

non-equijoins

outer joins

self joins

等号连接：使用一个公共列来连接两个表。

mysql> select userlocation.user_id from userlocation,userhobby where userlocation.user_id = userhobby.user_id and userhobby.hobby_id in (20,21,22,23);
+------------+
| user_id |
+------------+
| 1659078210 |
+------------+
1 row in set (0.00 sec)

自然连接：natural join

不等行连接：！＝或者<>

外部连接：（用于返回存在于一张表上的所有数据行，甚至相对应的行在连接的表中不存在。）

left outer join

right outer join

full outer join

mysql> select userhobby.user_id from userhobby join userlocation on userlocation.user_id = userhobby.user_id and userhobby.hobby_id in (20,21,22,23);
+------------+
| user_id |
+------------+
| 1659078210 |
| 1659078211 |
+------------+
2 rows in set (0.00 sec)

 

mysql> select userhobby.user_id from userhobby left join userlocation on userlocation.user_id = userhobby.user_id and userhobby.hobby_id in (20,21,22,23);
+------------+
| user_id |
+------------+
| 1659078210 |
| 1659078211 |
| 1659078212 |
+------------+
3 rows in set (0.00 sec)

暂时整理到此。。。