文档介绍：连接数据库
mysql -uroot -p111111;
查看所有库
Show databases;
创建库
Create database gy1 charset utf8;
删除库
drop database gy1;
选择数据库
use gy1;
查看表
Show tables;
创建表
Create table stu(
Snum int,
Sname varchar(10)
)engine myisam charset utf8;
查看表结构(desc stu)
describe stu;
删除表
drop table stu;
给表改名
rename table stu to newstu;
清空表
truncate stu;
插入一条
Insert into stu values (1,’zhangsan’), (2, ‘lisi’), (3,’wangwu’);
Insert into class (sname, gender, salary) values (‘张三’, ’1’, ), (‘李四’, ‘0’, );
修改update
update class set gender = ‘0’, fanbu = 123 where sname = ’zhangsan’;
update class set fanbu =100 where gender=’1’ and salary>5000;
删除delete
delete from class where salary>5000;
tinyint(8位)、smallint(16)、mediumint(32)、int、bigint
zerofill表示用0填充,(M)表示填充多少位
alter table class add snum smallint(5) zerofill not null default 0;
学号snum会为00012、01234 (查看表结构,会发现被自动声明为smallint(5) unsigned zerofill
)
alter table class add score tinyint unsigned not null default 0 after snum;(把新列指定在snum列之后)
alter table class add score tinyint unsigned not null default 0 first;(把新列放在第一位置)
float(M, D)、decimal(M, D)(float类型有时会损失精度,一般存钱用decimal/用整型存分)
alter table class add bonus float(5, 2) unsigned not null default ;
alter table class drop bonus; (删除bonus列)
alter table class modify bonus decimal(6,2) not null default ; (修改列类型)
alter table class change id uid int unsigned; (修改列名)
将一个数据库表中的数据插入到另外一个数据库的表中
insert into select goods_id, cat_id, goods_name, market_price from ;
18、不用or查询cat_id为4或者11的商品信息
select goods_id, cat_id, goods_name from goods where cat_id in (4, 11);
取出价格大于等于100、小于等于500之间的商品信息,不用 and
select goods_id, cat_id, goods_name from goods where shop_price between 100 and 500;
20、
select goods_id, cat_id, goods_name from goods where cat_id not in (3,11);
模糊查询
select goods_id, cat_id, goods_name from goods where goods_name like ‘诺基亚%’;
用_ 来匹配任意单个字符
select goods_id,cat_id, goods_name from goods where goods_name like ‘诺基