**数据库就是一个文件系统,通过标准的SQL语句获取数据**
- create database 数据库名称 character set 字符集 collate 字符集校对规则;
create database db1 character set utf8 collate utf8_bin;
- create database 数据库名称 character set 字符集 collate 字符集校对规则;
creata database db1 character set utf8 collate utf8_bin;
alter database db1 character set gbk;
- create table 表名称(字段名称 字段类型(长度) 约束,字段名称 字段类型(长度) 约束...);
| ------------------- | ---------------------------- |
| byte/short/int/long | tinyint/smallint/int/bigint |
| char/String | char/varchar |
| Date | date/time/datetime/timestamp |
- **char**和**varchar**的区别:
- **char**:固定长度,长度不足会被三个空格补全
- **datetime**和**timestamp**的区别:两者都是既有日期又有时间的类型
- datetime:如果不给值,默认为null;
- timestamp:如果不给值,默认为当前时间;
- 主键约束: primary key 主键约束默认就是唯一非空的;
create database web_test1;
id int primary key auto_increment,
alter table user add image varchar(100);
alter table user modify image varchar(150);
alter table user drop age;
alter table user change image pic varchar(150);
rename table user to employee;
alter table user character set gbk;
- 向表中插入某些列:`insert into user (列名1,列名2,列名3...) values(值1,值2,值3...)`
- 向表中插入所有列:`insert into user values(值1,值2,值3...)`
- 添加中文记录需要将MySQL数据库服务器中的客户端部分的字符改为gbk.
update user set password = 'abc';
update user set password = 'xyz' where username = 'bbb';
update user set password = '123',age = 34 where username = 'aaa';
delete from user where id = 2;
- **delete from user**和**truncate table user**的区别
- 删除所有记录,属于DML语句,一条记录一条记录删除.事务可以作用在DML语句上
- 删除说有记录,属于DDL语句,将表删除,然后重新创建一个结构一样的表.事务不能控制DDL语句.
- select distinct english from exam;
- select name,english+chinese+math as sum from exam;
- select * from exam where name = '李四';
- select * from exam where name like '李%';
- select * from exam order by chinese desc;
- select sum(english) from exam;
- select sum(english) from exam where name like '李%';
- select count(english) from exam;
- select max(english) from exam;
- select min(english) from exam
- select avg(english) from exam;
- select product,sum(price) from order group by product order by sum(price) desc;
- S(select)...F(from)...W(where)...G(group by)...H(having)...O(order by);