MySQL总结

第一,数据库是什么?
有oracle,db2,SQLServer,MySql,SQLte
关系数据库,er图,01用户下了一个01订单,买了一个手机


第二,安装与卸载


第三,存储结构
我的电脑就是MySQL服务器
一个服务器中有多个数据库,一个数据库中有多个表,一张表中有多条记录


第四,标准sql是什么?
对数据库操作的语言
是非过程语言,没有判断语句
分为 DDL create,drop
DML insert ,update,delete
DCL 略
DQL select


第五,操作
连接 mysql -uroot -p
创建 create database testdb1
查看 show databases
删除 drop database testdb1
切换 use testdb1


第六,对表的操作
create table usre(
id int,
username varchar(40),
password varchar(40),
sex varchar(30)
);


常用数据类型
字符串 VARCHAR CHAR 一个长度可变,一个长度不可变
大数据类型 BLOB TEXT
数值新 TINYINT SMALLINT INT BIGINT FLOAT DOUBLE
逻辑 BIT
日期 DATE TIME DATETIME TIMESTAMP

看表结构
desc user


约束条件
not null
unique
primary key auto_increment


create table person(
id int primary key auto_increment,
username varchar(40) not null,
sex varchar(20) unique
);


drop table user


show tables 


第七,crud操作
insert into user values(1,'zhangsan','2088-08-08');
insert into user(id,username) values(2,'lisi');
insert into user values(null,'wangwu');


update user set id=4,username='zhaoliu' where id=3;


delete from user where id=3;
delete from user;/*删除所有*/


select * from user;


select username ,chinese from user;


select * from user where id=2;


select username as u ,chinese as c from user;


select distinct * from user;


select * from user where english in(20,30);


select * from user where chinese >50;


select * from user where chinese =100 and english =30;


select * from user where chinese >=70 and chinese <=100;
select * from user where chinese between 70 and 100;


select * from user where username like '%a_';

select database();


select * from usre order by chinese asc;
select * from user order by chinese desc;


第八,聚集函数
count()
select count(*) from user


sum()
select sum(chinese) from user;


avg()
select avg(chinese) from user;


max()
min()
select max(chinese),min(english) from user;


第九,分组
mysql> select * from orders;
+------+------+-------+
| id   | name | price |
+------+------+-------+
|    1 | 电视 |  2000 |
|    2 | 电视 |  2000 |
|    3 | 苹果 |    10 |
|    4 | 手机 |   500 |
|    5 | 手机 |   500 |
|    6 | 鼠标 |    33 |
|    7 | 鼠标 |    33 |
+------+------+-------+
select name sum(price) from orders group by name;
select name,sum(price) from orders group by price having sum(price)>66;


第十,mysql特有关键字limit
oracle是rownum
sqlserver是top

select * from user limit 3;
select * from user limit 0,3;


第十一,扩展
导入数据  source g:/数据.sql,看数据怎么写的

开MySQL服务 services.msc


将控制台数据和文件关联 tee g:/day04_my.sql;
tee g:/day04_my.sql;
tee g:/长城校区/3.就业班/day04-mysql数据库/re_day04/re_day04.sql;