create database my-text character set utf8.
show databases.
use my-text.
show tables;查看有哪些表
create table student(int id,varchar(10) name).创建表
desc student: 查看表
alter table student add score int.插入一列
alter table student modify id bigint.修改数据类型
alter table student drop score.删除
rename table stedent to teach.修改表明
show creat table teach.查看表详细信息
alter table teach chracter set gbk.修改字符集
alter table student change id ids int.修改表名
drop table teach 删除表
select * from student.查看数据
insert into student() value (),()....
插入数据
mysql -u root -p
update student set id=4 where name=str
update mysql
exit 退出
quit
mysqladmin -u root -p password 密码
修改密码用
delete from student where name=str.删除数据
truncate table from student强删除,无法恢复数据
select * from student where id is NULL
select * from student where id is not NULL
select * from student where age>=18
and age=<20
select *from student where age between 18 and 20
_一个任意字符
%任意多个字符
select distinct name from student 去重
select *, age+id As sun from student
select *,IFNULL(age,0)+IFNULL(id,0) As sum from student
select name As rename from student
select * from student order by salary desc
,id desc 降序拍序
asc 升序拍序
聚合函数count()统计
select count(*)from student
select count(salary) from student
select count(*) from student salar>10,id<20
select sum(id+age) from student总和
select avg(id) drom student平均值
select salay ,group_concat(name) from student group by salay,id分组 按薪水分组
后面跟着该薪水的人数
select salary ,group_concat(name), sum(salay)from student by salay加上聚合函数
having在分组之后运行,where在分组之前运行。
having 可使用聚合函数,where不可
select salary ,group_concat(id), sum(id)
from student where id>100 having sum(id)>500
书写顺序
select from student where group by having order by limit
int cut
int page
select * from student limit (cut-1)*page ,page
select * from student limit 从几行开始,看几行
设置主键
creat table student (id int primary key,name verchar(10))
creat table student (id int,name varchar(10),salay int,primary key(id,name))
alter table student add constraint primary key(score)添加主键
creat table student (id int primary key,name verchar(10) unique)增加约束。约束后值不能重复,但可为Null
creat table student (id int primary key auto_increment,name verchar(10) unique)自动增长
creat table score (sid int ,score int ,constraint stu_sco foreing key(sid) references stu(id))关联score表
alter table score add constraint stu_sco foreign key (id) references stu(id))
select * from a union select * from b
查a和b表
union all不去掉重复的
select * from a,b where a.id=b.id用外健关联查表99链接
select * from student st inner join score sc on st.id=sc.id.内连接
select * from student st left join score sc on st.id=sc.id.左链接
select * from student natural join score 两表属性得相等
事务的ACID
原子性
一致性
隔离行
持久性
start transaction提交事务
*****************
commit 开启事务
提交完数据
rollback撤回
创建用户
creat user 名字 @127.0.0.1 identified by 密码
drop user 名字@localhost删除用户
creat view stu_scor as select * from student where salary scoee>70 with check option 创建视图。并且更新得在score>70的条件
delimiter $$修改;
create procedure insert _emp(in startNum int,in max_num int)
begin
declare i int default 0;
repeat
set i=i+1;
insert int emp values(startNum+i,rand_str(5),FLOOR(10+RAN()*30));
until i=mac_num;
end$$
delimiter ;