mysql初学
本文基于mysql5.1编写
1、创建表:
1 create table customer(mid char(5) primary key, name varchar(20),age int(2) default'0');
2、删除表:
1 drop table customer;
3、部分列插入元素:
1 insert into customer(mid,name) values('ID','姓名');
4、全部列插入元素可以简写:
1 insert into customer values('ID','姓名','年龄');
5、更新元素,将ID这个用户的姓名和年龄改成姓名1和年龄1:
1 update customer set name='姓名1',age='年龄1' where mid='ID';
6、删除元素,删除id这一行:
1 delete from customer where mid='ID';
7、普通查找:
1 select * from customer;
8、条件查找,找到年龄大于10但是不等于20的人的ID和姓名:
1 select mid,name from customer where age>=10 and age<>20;
9、模糊查找:
9.1、找到姓李的人的姓名
1 selsct name from customer where name like'李%';
9.2、找到对应ID的人的姓名
1 select name from customer where mid in ('ID1','ID2','ID3');
9.3、排序查找,按照年龄升序,然后降序找到ID和姓名
1 select mid,name from customer order by age asc,age desc;
10、内连接两表查询:
1 select name,age from customer inner join customerg on customer.mid=customerg.mid
上述式子等于利用表customer的mid和表customerg的mid进行匹配
匹配相当于将两个表共有的属性合并了一起
那么就可以在合并的表格里查找姓名和年龄
11、多个表连接
1 select customer.mid,customerg.age,customers.address 2 from 3 (customer inner join customerg on customer.mid=customerg.mid) 4 inner join customers on customer.mid=customers;
共有三个表
customer:ID和姓名
customerg:ID和age
customers:ID和地址
12、增加列:
1 alter table customers add address char(20);
13、修改数据类型
1 alter table customer modify address char(30);
14、修改列名称和数据类型:
1 alter table change adress address char(20);
15、删除列:
1 alter after customer drop address;
16、多表查询模板