1.进入数据库
mysql -u 用户名 -p
密码
退出数据库
quit exit
2.一个数据库服务器可以管理多个数据库
一般一个应用程序对应一个数据库
一个数据库可以管理多个表
每个表一般对应于一个实体
3.CRUD数据库
创建数据库
create database mydb1 character set utf8 collate utf8_general_ci;
显示数据库
show databases;
显示某一数据库的详细信息
show create database mydb1;
修改数据库
alter database mydb1 character set utf8 collate utf8_general_ci;
删除数据库
drop database mydb1;
4.CRUD表
要先用数据库，再操作表
use mydb1;
创建表
create table employee
(
id int(10) primary key auto_increment,
name varchar(40),
sex char(20),
birthday date,
entry_date date,
job varchar(50),
salary float,
resume text
)character set utf8 collate utf8_general_ci;
显示表
show tables;
显示某一表的详细信息
show create table employee;
或desc employee; show比desc的内容更多，desc更直观
修改表
列：添加列、修改列、删除列
alter table employee add image blob;
alter table employee modify job varchar(60);
alter table employee drop sex;
改表名
rename table employee to user;
修改表的字符集
alter table user character set utf8 collate utf8_general_ci;
删除表
drop table user;
5.数据类型
a.数值类型
int
bool,boolean
float,double
其他的就不罗列了
b.文本类型
char固定长度字符串
varchar可变长度字符串
blob longblob 二进制数据
text longtext 大文本
varchar blob text是变长类型，每个类型的存储需求取决于列值的实际长度
c.时间类型
date 年月日
datetime 年月日时分秒
timestamp 时间戳 用于自动记录insert,update操作的时间
6.CRUD语句
insert,update,delete,select
insert语句
insert into employee(id,name,sex,birthday,entry_date,job,salary,resume,image) values
(null,'美女','女','1988-10-17','2000-12-23','前台',4000.5,'漂亮MM',null);
insert into employee(id,name,sex,birthday,entry_date,job,salary,resume,image) values
(null,'大美女','女','1988-10-17','2000-12-23','前台',5000.5,'漂亮MM',null);
insert into employee(id,name,sex,birthday,entry_date,job,salary,resume,image) values
(null,'小美眉','女','1988-10-17','2000-12-23','前台',6000.5,'漂亮MM',null);
insert into employee(id,name,sex,birthday,entry_date,job,salary,resume,image) values
(null,'衰男','男','1988-10-17','2000-12-23','后台',9000.5,'喝水都塞牙',null);
插入语句的时候 数据类型要一致，位置要对应
字符和日期要在''
插入空值，不指定或者null
update语句
update employee set job='架构师' where name='衰男';
update employee set job='架构师',salary=1000000 where name='衰男';
update employee set salary=5000;
update employee set salary=3000 where name='小美眉';
update employee set salary=salary+1000 where name='衰男';
delete语句
删除行：如果不使用where子句，将删除表中的所有数据，
删除列：修改表中的删除
删除表：drop table user;
使用truncate删除表中的记录
delete是一行一行的删，truncate是先删表，再重建表的结构
delte和truncate表的结构没删，删的是记录
delete from employee where id=3;
同insert和update一样，从一个表中删除记录将引起其它表的参照完整性问题，在修改数据库数据时，
头脑中应该始终不要忘记这个潜在的问题。
select语句
(1)distinct
写程序的时候，不要写*
distinct剔除重复数据
create table student
(
id int,
name varchar(20),
chinese float,
english float,
math float
);
insert into student(id,name,chinese,english,math) values(1,'张小明',89,78,90);
insert into student(id,name,chinese,english,math) values(2,'李进',67,98,56);
insert into student(id,name,chinese,english,math) values(3,'王五',87,78,77);
insert into student(id,name,chinese,english,math) values(4,'李一',88,98,90);
insert into student(id,name,chinese,english,math) values(5,'李来财',82,84,67);
insert into student(id,name,chinese,english,math) values(6,'张进宝',55,85,45);
insert into student(id,name,chinese,english,math) values(7,'黄蓉',75,65,30);
在该表的基础上进行如下操作：
select * from student;
select name,english from student;
select distinct * from student;
(2)别名：
select name as '名字' from student; 或 select name '名字' from student;
(3)在select语句中可使用表达式对查询的列进行运算
select chinese+10,english+10,math+10 from student;
select name,(chinese+english+math) from student;
select name '名字',(chinese+english+math) as '总分' from student;
select name '名字',(chinese+english+math) as '总分' from student where name='黄蓉';
(4)在where子句中使用的运算法
比较运算符：
>, <, >=, <=, <> <>是不等于
select name,english from student where english>90;
select * from student where (chinese+english+math)>90;
between and
select name,english from student where english between 80 and 90;
int()
select name,math from student where math in(89,90,91);
like 'pattern' %代表0个或多个 _代表一个字符
select * from student where name like '李%';
is null
select * from student where name is null;
逻辑运算法：
与and
select * from student where math>80 and chinese>80;
或or
select * from student where math>80 or chinese>80;
非not
select * from student where not math>80;
(5)order by asc升序 desc降序
order by 要放到select语句的最后面
select name,math from student order by math desc;
select name,chinese+english+math as '总分' from student order by chinese+english+math;
(6)合计函数
a.count
COUNT(expr) 返回SELECT语句检索到的行中非NULL值的数目
COUNT(*) 的稍微不同之处在于，它返回检索行的数目， 不论其是否包含 NULL值
select count(*) from student;
select count(math) from student where math>90;
//这句话不对select name,count(chinese+english+math) from student where (chinese+english+math)>250;
要group by select name,count(chinese+english+math)
from student where (chinese+english+math)>250 group by chinese+english+math;
b.sum
select sum(math) from student;
select sum(chinese),sum(math),sum(english) from student;
select sum(chinese+english+math) from student;
select sum(chinese+english+math)/count(*) from student;
select sum(chinese)/count(chinese) from student;
sum只对数值起作用
c.avg
select avg(math) from student;
select avg(chinese+english+math) from student;
d.max/min
select max(chinese+english+math) as '最高分' ,
min(chinese+english+math) as '最低分' from student;
(7)group by
create table orders
(
id int,
product varchar(20),
price float
);
insert into orders(id,product,price) values(1,'电视',900);
insert into orders(id,product,price) values(2,'洗衣机',100);
insert into orders(id,product,price) values(3,'洗衣粉',90);
insert into orders(id,product,price) values(4,'桔子',9);
insert into orders(id,product,price) values(5,'洗衣粉',90);
根据以上表进行如下操作：
select product,sum(price) from orders group by product;
select product,sum(price) from orders group by product
having sum(price)>100;
Having和where均可实现过滤，但在having可以使用合计函数,
having通常跟在group by后，它作用于组。
7.时间日期相关函数
select addtime(now(), '2 2-2 10:23:13'); //注意年 -有无
select current_date();
select current_time();
select current_timestamp();时间戳
select date(entry_date) from employee;
select date_add(entry_date, interval 2 MONTH) from employee;
select date_sub(entry_date, interval 2 DAY) from employee;
select date(datediff(entry_date, birthday) from employee; //怎样把它转为日期类型
select now();
select YEAR(now());
8.字符串相关函数
select charset('abc'); //返回字符字符集
select concat('abc', '123');
select instr('abcd', 'bcd');
select ucase('abc');
select lcase('ABC');
select left('abcdefg', 2);
select length('abcdefghi');
select replace('abcdefghi', 'abc', '123');
select strcmp('agc', 'abc');
select substring('abcdefghi', 4, 2);
select ltrim(' abc ');
select rtrim(' abc ');
9.数学相关函数
不练了，没啥意思
10.定义表的约束
(1)主键 primary key
(2)非空 not null
(3)唯一 unique
(4)自动增长 auto_increment
create table user
(
id int(10) primary key,
name varchar(20) not null,
love varchar(30) unique
);
表的约束的CRUD
primary key:
alter table add primary key (id);
alter table drop primary key; //某列同时有primary key和auto_increment删出错
unique
alter table add unique (love);
11.导入SQL脚本
source d://stu.sql;
![【MySQL数据库】数据库索引事务1.索引2.事务[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)