在管理员用户下:service mysqld start
mysql -u root -p进入数据库
SQL语句
1.DDL 数据定义语言
create drop alter show
1.创建库
CREATE DATABASE [IF NOT EXISTS] dbname;
create database [if not exists] CY1207;
2.删除库
DROP DATABASE [IF EXISTS] dbname;
drop database [if exists] CY1207;
3.查询库
show databases;
1.创建表
CREATE TABLE tbname(
字段名称 字段类型 [字段约束],
...
字段名称 字段类型 [字段约束]
);
create table stu(
id varchar(20) primary key comment "学号",
name varchar(20) not null comment "姓名",
age int default null comment '年龄',
sex enum("man","woman") comment "性别"
)ENGINE = InnoDB default CHARSET = utf8;
2.查看表
1.SHOW CREATE TABLE tbname;
2.DESC tbname;
3.修改表
1.修改字段类型 modify
alter table stu modify name varchar(10);
2.修改字段名称 change [字段类型] [字段约束]
alter table stu change name mname vachar(100) default null;
3.添加字段 add
alter table stu add score float;
4.删除字段 drop
alter table stu drop score;
5.修改表名 rename
alter table stu rename student;
5.删除表
DROP TABLE tbname;
drop table stu;
学生:
1.学号 id varchar(20)
2.姓名 name varchar(20)
3.年龄 age int
4.性别 sex enum("man","woman")
成绩表
1.学号
2.科目编号
3.成绩
insert into result values(
"001","p01",53),(
"001","p02",90),(
"002","p01",65),(
"002","p02",88),(
"003","p01",43),(
"003","p02",65),(
"004","p01",59),(
"004","p02",70);
create table result(
id varchar(20) comment "学号",
pid varchar(20) not null comment "科目编号",
score float comment "成绩"
);
1.主键 非空+唯一
2.外键
3.唯一
4.非空
5.默认为空
select User,Host,password from user;
2.DML 数据操纵语言
insert delete update select
1.添加数据 insert load source replace
insert into tbname values(fieldval1,fieldval1..);
insert into stu(id,name,age,sex) values("001","zhangsan",19,"man");
insert into stu(id,name) values("002","lisi");
insert into stu values("003","wangwu",20,"woman"),
("004","zhaoliu",21,"man"),
("005","kaixin",17,"woman"),
("006","gaoxin",16,"woman");
load 大批量
replace insert
stu id 001 001 insert error
stu id 001 001 replace delete+insert
2.删除数据 delete truncate
delete from tbname [where];
delete from stu where id = "005";
3.修改数据 update
update tbname set Fieldname = newvalue [where];
update stu set sex = "woman";
update stu set age = 18 where id = "002";
4.查询数据 select
1.普通查询
select field1,field2... from tbname [where];
select * from stu;
select id,name,age,sex from stu;
select id,name,age,sex from stu where age > 20;
2.去重查询 distinct
select distinct age from stu;
3.排序 order by asc升序 desc降序
select distinct age from stu
order by age desc;
4.分组 group by
select id,SUM(score)
from result
group by id;
5.多表
查询年龄大于等20岁的学生的不及格成绩
1.等值查询
select name,score
from stu,result
where stu.id = result.id and age >= 20 and score < 60;
2.连接查询
1.外连接查询
1.左外连接查询 左表全部存在
select name,score
from (select id,name from stu where age >= 20) a
left join
(select id,score from result where score < 60) b
on a.id = b.id
where score is not null;
2.右外连接查询
select name,score
from (select id,name from stu where age >= 20) a
right join
(select id,score from result where score < 60) b
on a.id = b.id
where name is not null;
3.全外连接查询
select name,score
from (select id,name from stu where age >= 20) a
full join
(select id,score from result where score < 60) b
on a.id = b.id
where name is not null or score is not null;
2.内连接查询
1.内连接查询
select name,score
from (select id,name from stu where age >= 20) a
inner join
(select id,score from result where score < 60) b
on a.id = b.id;
6.联合查询 union || union all
查询所有师生的基本信息
select tid,name,age,sex from teacher
union all
select id,name,age,sex from stu;
create table teacher(
tid varchar(20) primary key comment "教师编号",
name varchar(20) not null comment "姓名",
age int default null comment '年龄',
sex enum("man","woman") comment "性别"
)ENGINE = InnoDB default CHARSET = utf8;
date time datetime
记录 log
A = 1;
delete
B =2;
delete
3.DCL 数据控制语言 (权限管理)
grant revoke
create user username@host [identified by ];
create user "u5"@localhost identified by "1111111";
14种
grant all on CY1207.* to u5;
revoke all on CY1207.* from u5;
返祖
root ==> u1 ==> u2 ==> u3