Ubuntu 12.04 安装 MySQL--及简单的Show,Use,Create,drop,alter,CRUD,export,import
来源:互联网 发布:手机图片分类软件 编辑:程序博客网 时间:2024/05/22 03:21
Ubuntu 12.04 安装 MySQL
视频地址: 点击打开链接 http://v.youku.com/v_show/id_XNDU5MDU5Nzky.html
安装: jiangge@ubuntu:~$ sudo apt-get install mysql-server
设置MySQL root的密码
输入用户名和密码: jiangge@ubuntu:~$ mysql -uroot -p
mysql> SHOW DATABASES;+--------------------+| Database |+--------------------+| information_schema | | mysql | performance_schema || test | +--------------------+4 rows in set (0.00 sec)
创建数据库:
mysql> CREATE DATABASE forum;Query OK, 1 row affected (0.03 sec)
mysql> SHOW DATABASES;+--------------------+| Database |+--------------------+| information_schema mysql| || forum | | performance_schema || test |+--------------------+
选中数据库:
mysql> USE forum;Database changed
创建表:
mysql> CREATE TABLE users( -> id int NOT NULL AUTO_INCREMENT, -> username varchar(20) NOT NULL, -> PRIMARY KEY(id) -> );Query OK, 0 rows affected (0.39 sec)
查看表结构:
mysql> DESC users;+----------+-------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || username | varchar(20) | NO | | NULL | |+----------+-------------+------+-----+---------+----------------+2 rows in set (0.00 sec)
创建表:
mysql> CREATE TABLE comments( -> id int NOT NULL AUTO_INCREMENT, -> body text, -> PRIMARY KEY(id) -> );Query OK, 0 rows affected (0.53 sec)
查看表结构:
mysql> DESC comments;+-------+---------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------+---------+------+-----+---------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || body | text | YES | | NULL | |+-------+---------+------+-----+---------+----------------+2 rows in set (0.00 sec)
增加:
mysql> ALTER TABLE comments ADD created_at datetime;Query OK, 0 rows affected (0.71 sec)Records: 0 Duplicates: 0 Warnings: 0
查看:
mysql> DESC comments;+------------+----------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+------------+----------+------+-----+---------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || body | text | YES | | NULL | || created_at | datetime | YES | | NULL | |+------------+----------+------+-----+---------+----------------+3 rows in set (0.00 sec)
删除:
mysql> ALTER TABLE comments DROP created_at;Query OK, 0 rows affected (0.75 sec)Records: 0 Duplicates: 0 Warnings: 0
查看
mysql> DESC comments;+-------+---------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------+---------+------+-----+---------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || body | text | YES | | NULL | |+-------+---------+------+-----+---------+----------------+2 rows in set (0.00 sec)
安装图形化界面phpmyadmin:
jiangge@ubuntu:~$ sudo apt-get install phpmyadmin
会报错:
The following packages have unmet dependencies:
phpmyadmin : Depends: php5-mysql but it is not going to be installed or
php5-mysqli but it is not installable
Recommends: php5-gd but it is not going to be installed
E: Unable to correct problems, you have held broken packages.
解决方法:
apt-get -f install
sudo apt-get install apache2 php5-mysql libapache2-mod-php5
又报错:
The following packages have unmet dependencies:
php5-mysql : Depends: php5-common (= 5.3.10-1ubuntu3) but 5.3.10-1ubuntu3.4 is to be installed
E: Unable to correct problems, you have held broken package
继续解决:
jiangge@ubuntu:~$ sudo apt-get install php5-common
啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊没有解决掉啊.............
http://happycasts.net/episodes/39?autoplay=true 8分30秒..
TODO ......
=======================以下笔记根据网上内容整理=================================================
SQL structed query language
连接MYSQL服务器:mysql -uroot -proot
Database Server :
Database(DB)
Table
DDL (Data Definition Language )
create
alter
drop
DML:(Data Manipulation Language)
insert into
delete from
update
select
表的一行(row)称之为一条记录
表中一条记录对应一个java对象的数据
表的一列(column)称之为一个字段
1、Java是使用对象封装数据,例如程序产生了如上所示2个user对象,这些对象的数据要想保存到数据库中,需要在数据库中创建与之对应的表,一个user对象中封装的数据,要保存到数据库中,一般就要在数据库中创建一个与之对应的表。
2、对象的属性定义为表头,对象的数据对应于表中的一条记录。
3、每个对象对应于表中的一条记录。
数据库的操作:创建,查看,修改,删除
*创建:
创建一个名称为mydb1的数据库。
create database mydb1;
创建一个使用utf-8字符集的mydb2数据库。
create database mydb2 character set utf8;
创建一个使用utf-8字符集,并带校对规则的mydb3数据库。
create database mydb2 character set utf8 collate utf8_general_ci;
*查看:
显示所有数据库
show databases;
显示创建数据库的语句信息
show create database mydb2;
*修改:
修改mydb1的字符集为gbk(不能修改数据库名)
alter database mydb1 character set utf8;
*删除:
删除数据库mydb2
drop database mydb1;
表的操作:创建,查看,修改,删除
*创建:
根据实体类Person创建表person
Person {
int id;
String name;
}
create table person(
id int,
name varchar(20)
);
mysql中的数据类型:
bit 1位 但可以指定位数,如:bit<3>
int 2字节 可以指定最大位数,如:int<4> 最大为4位的整数
float 2个字节 可以指定最大的位数和最大的小数位数,如:float<5,2> 最大为一个5位的数,小数位最多2位
double 4个字节 可以指定最大的位数和最大的小数位数,如:float<6,4> 最大为一个6位的数,小数位最多4位
char 必须指定字符数,如char(5) 为不可变字符 即使存储的内容为'ab',也是用5个字符的空间存储这个数据
varchar 必须指定字符数,如varchar(5) 为可变字符 如果存储的内容为'ab',占用2个字符的空间;如果为'abc',则占用3个字符的空间
text: 大文本(大字符串)
blob:二进制大数据 如图片,音频文件,视频文件
date: 日期 如:'1921-01-02'
datetime: 日期时间 如:'1921-01-02 12:23:43'
timeStamp: 时间戳,自动赋值为当前日期时间
DATETIME 日期和时间的组合。支持的范围是'1000-01-01 00:00:00'到'9999-12-31 23:59:59'。MySQL以'YYYY-MM-DD HH:MM:SS'格式显示DATETIME值,但允许使用字符串或数字为DATETIME列分配值。
TIMESTAMP[(M)] 时间戳。范围是'1970-01-01 00:00:00'到2037年。 TIMESTAMP列用于INSERT或UPDATE操作时记录日期和时间。如果你不分配一个值,表中的第一个TIMESTAMP列自动设置为最近操作的日期和时间。也可以通过分配一个NULL值,将TIMESTAMP列设置为当前的日期和时间。 TIMESTAMP值返回后显示为'YYYY-MM-DD HH:MM:SS'格式的字符串,显示宽度固定为19个字符。如果想要获得数字值,应在TIMESTAMP 列添加+0。
创建一个员工表
create table employee(
id int,
name varchar(20),
sex bit,
birthday date,
entry_date date,
job varchar(20),
salary double,
resume text
);
*查看:
查看所有的表:
show tables;
查看指定表的创建语句
show create table employee;
显示指定表的结构:
desc employee;
*删除:
删除employee表
drop table employee;
*修改表:
增加一个字段:alter table worker add column height double;
修改一个字段:alter table worker modify column height float;
删除一个字段:alter table worker drop column height;
更改表名:rename table employee to worker;
修改表的字符集:alter table worker character set gbk;
表数据的CRUD
CRUD操作:指在做计算处理时的增加(Create)、查询(Retrieve)、更新(Update)和删除(Delete)
*C(create增加数据) Insert语句 :
注意:字符和日期型数据应包含在单引号中。
show variables like 'character%';
mysql -uroot -proot --default_character_set=gbk
新建Employee表并表中添加一些记录
create table employee(
id int,
name varchar(20),
sex bit,
birthday date,
salary double,
entry_date date,
resume text
);
insert into employee(name,id,sex,birthday,salary,entry_date,resume) values('张三',1,1,'1983-09-21',15000,'2012-06-24','一个大牛');
insert into employee(id,name,sex,birthday,salary,entry_date,resume) values(2,'李四',1,'1984-09-21',10000,'2012-07-24','一个中牛');
insert into employee(id,name,sex,birthday,salary,entry_date,resume) values(3,'王五',0,'1985-09-21',7000,'2012-08-24','一个小牛');
*U(update更新数据) Update语句 :
将所有员工薪水都增加500元。
update employee set salary=salary+500;
将王五的员工薪水修改为10000元,resume改为也是一个中牛
update employee set salary=10000,resume='也是一个中牛' where name='王五';
*D(drop删除数据) Delete语句 :
删除表中姓名为王五的记录。
delete from employee where name='王五';
删除表中所有记录。
delete from employee; --可以有条件,但删除所有记录差了一点
使用truncate删除表中记录。
truncate employee;--无条件 效率高
*R(Retrieve查找数据) Select语句:
准备环境:使用 SQLyog_Enterprise,企业版软件。
create table student(
id int,
name varchar(20),
chinese int,
english int,
math int
);
insert into student(id,name,chinese,english,math) values(1,'何东',80,85,90);
insert into student(id,name,chinese,english,math) values(2,'权金属',90,95,95);
insert into student(id,name,chinese,english,math) values(3,'何南',80,96,96);
insert into student(id,name,chinese,english,math) values(4,'叶子',81,97,85);
insert into student(id,name,chinese,english,math) values(5,'何西',85,84,90);
insert into student(id,name,chinese,english,math) values(6,'丁香',92,85,87);
insert into student(id,name,chinese,english,math) values(7,'何北',75,81,80);
insert into student(id,name,chinese,english,math) values(8,'唐中',77,80,79);
insert into student(id,name,chinese,english,math) values(9,'任已',95,85,85);
insert into student(id,name,chinese,english,math) values(10,'王八',94,85,84);
//关键字:select from where distinct
查询表中所有学生的信息。
select * from student;
查询表中所有学生的姓名和对应的英语成绩。
select name,english from student;
过滤表中重复数据。
select english from student;
select DISTINCT english from student;
select DISTINCT english,name from student;
select english+chinese+math from student;
select english+chinese+math as 总分 from student;
select name,english+chinese+math as 总分 from student;
//关键字 as 别名
在所有学生英语分数上加10分特长分。
select name,english+10 from student;
统计每个学生的总分。
select english+chinese+math from student;
使用别名表示学生分数
select name,english+chinese+math as 总分 from student;
select name,english+chinese+math 总分 from student;
//关键字:select from where
查询姓名为何东的学生成绩
select * from student where name='何东';
查询英语成绩大于90分的同学
select * from student where english>90;
查询总分大于250分的所有同学
select * from student where english+chinese+math>250;
查询英语分数在 85-95之间的同学。
select * from student whereenglish>=85 and english<=95;
select * from student where english between 85 and 95;
查询数学分数为84,90,91的同学。
select * from student where math=84 or math=90 or math=91;
select * from student where math in(84,90,91);
查询所有姓何的学生成绩。
select * from student where name like '何%';
select * from student where name like '何_%';
注意:Like语句中,% 代表零个或多个任意字符,_ 代表一个字符,例first_name like ‘_a%’;
查询数学分>85,语文分>90的同学。
select * from student where math>85 and chinese>90;
//关键字 order by(默认为升序) desc为降序 asc 升序 别名 as
对数学成绩排序后输出。
select * from student order by math;
对总分排序后输出,然后再按从高到低的顺序输出
select * from student order by math+chinese+english desc;
select name, math+chinese+english as sumScroe from student order by sumScroe desc;
对姓何的学生成绩排序输出
select * from student where name like '何%' order by math+chinese+english desc;
select name, math+chinese+english from student where name like '何%' order by math+chinese+english desc;
//关键字 count
统计一个班级共有多少学生?
select count(*) from student;
统计数学成绩大于90的学生有多少个?
select count(*) from student where math>90;
统计总分大于250的人数有多少?
select count(*) from student where math+chinese+english>250;
//关键字 sum
统计一个班级数学总成绩?
select sum(math) from student;
统计一个班级语文、英语、数学各科的总成绩
select sum(math), sum(chinese), sum(english) from student;
统计一个班级语文、英语、数学的成绩总和
select sum(math+chinese+english)from student;
select sum(math)+sum(chinese)+sum(english) from student;
//关键字 avg
求一个班级数学平均分?
select avg(math) from student;
求一个班级总分平均分
select avg(math+chinese+english)from student;
select avg(math)+avg(chinese)+avg(english) from student;
//关键字 max min
求班级最高分和最低分
select max(math+chinese+english),min(math+chinese+english) from student;
//关键字 group by having
查出各个班的总分,最高分
使用group by 子句对列进行分组
Having和where均可实现过滤,但在having可以使用合计函数,having通常跟在group by后,它作用于组。
准备环境:使用 SQLyog_Enterprise,企业版。
给表添加一个字段:alter table student add column class_id int;
更新表:
update student set class_id=1 where id<=5;
update student set class_id=2 where id>5;
select sum(math+chinese+english),max(math+chinese+english) from student group by class_id;
查询出班级总分大于1300分的班级ID
select class_id from student group by class_id having sum(math+chinese+english)>1300;
表的约束
*定义主键约束 primary key:不允许为空,不允许重复
*定义主键自动增长 auto_increment
*定义唯一约束 unique
*定义非空约束 not null
*定义外键约束 constraint ordersid_FK foreign key(ordersid) references orders(id)
*删除主键:alter table tablename drop primary key ;
CREATE TABLE user(
id INT PRIMARY KEY AUTO_INCREMENT, #主键,自增长
name VARCHAR(20) UNIQUE, #不可重复
password VARCHAR(15) NOT NULL #不允许为空
)
insert into user(name, password) values('MM', NULL);#执行不通过Column 'password' cannot be null
insert into user(name, password) values('MM', '123456');#通过
insert into user(name, password) values('MM', '123sdfd');#执行不通过Duplicate entry 'MM' for key 'name'
//测试外键
准备环境:
创建一个班级表:
create table class(
id int primary key auto_increment,
name varchar(16)
);
create table student(
id int(11) primary key auto_increment default null,
name varchar(20) default null,
chinese int(11) default null,
english int(11) default null,
math int(11) default null,
classid int(11) default null,
constraint student_classid_FK foreign key(classid) references class(id)
);
insert into class(name) values('三年一班');
insert into class(name) values('三年二班');
insert into student(name, classid) values('tom', 3); #classid的值只能是被引用的字段的某个值
insert into student(name, classid) values('tom', 2);
delete from class where id=2; #删除被引用的记录无法成功
select * from class;
select * from student;
数据的备份与恢复:
*准备数据:
create database mydb1;
use mydb1;
create table user(
id int,
name varchar(20)
);
insert into user(id,name) values(1,'Tom');
select * from user;
*备份backup export:
mysqldump -uroot -p mydb1>d:\test.sql;--备份数据库里的数据
Mysqldump -uroot -proot mydb1 > test_backup.sql
*恢复 import:
mysql -uroot -p mydb1<d:\test.sql
source d:\test.sql(在mysql客户端上执行,需要选中use要导进的库)
多表查询与子查询:
准备环境:
部门表
CREATE TABLE DEPARTMENT(
department_id int primary key auto_increment,
name varchar(20)
);
职位表
CREATE TABLE LOCATION (
location_id int PRIMARY KEY auto_increment,
name varchar(15)
);
员工表
CREATE TABLE EMPLOYEES(
employee_id int PRIMARY KEY auto_increment,
department_id int,
location_id int,
first_name varchar(10),
last_name varchar(10),
salary double,
hire_date date,
constraint employees_department_id_FK foreign key(department_id) references department(department_id),
constraint employees_location_id_FK foreign key(location_id) references location(location_id)
);
向department表中添加数据
insert into department(name) values ('人事');
insert into department(name) values ('财务');
insert into department(name) values ('后勤');
insert into department(name) values ('公关');
insert into department(name) values ('研发');
向location表中添加数据
insert into location(name) values ('总经理');
insert into location(name) values ('经理');
insert into location(name) values ('主管');
insert into location(name) values ('组长');
insert into location(name) values ('职员');
向employees表中添加数据
insert into employees(department_id,location_id , first_name, last_name, salary,hire_date)
values (1,1, 'A', 'z', 50000, '2005-02-21');
insert into employees(department_id,location_id, first_name, last_name, salary,hire_date)
values (1,2, 'B', 'x', 20000, '2009-03-21');
insert into employees(department_id,location_id , first_name, last_name, salary,hire_date)
values (2,3, 'C', 'v', 10000, '2009-08-23');
insert into employees(department_id,location_id, first_name, last_name, salary,hire_date)
values (4,2, 'D', 'n', 30000, '2004-09-28');
insert into employees(department_id,location_id, first_name, last_name, salary,hire_date)
values (3,5, 'E', 'm', 3000, '2009-04-11');
insert into employees(department_id,location_id, first_name, last_name, salary,hire_date)
values (5,5, 'F', 'l', 5000, '2008-03-11');
insert into employees(department_id,location_id, first_name, last_name, salary,hire_date)
values (5,3, 'G', 'p', 20000, '2005-05-09');
insert into employees(department_id,location_id, first_name, last_name, salary,hire_date)
values (5,4, 'H', 'o', 8000, '2006-07-21');
insert into employees(department_id,location_id, first_name, last_name, salary,hire_date)
values (5,5, 'I', 'u', 6000, '2006-09-21');
insert into employees(department_id,location_id, first_name, last_name, salary,hire_date)
values (5,5, 'J', 'y', 5500, '2007-08-21');
insert into employees(department_id,location_id, first_name, last_name, salary,hire_date)
values (5,5, 'K', 't', 6500, '2006-12-21');
insert into employees(department_id,location_id, first_name, last_name, salary,hire_date)
values (5,1, 'L', 'r', 100000, '2001-05-21');
练习:
--多表查询:查出姓z的员工的所有信息(使用别名)#联表查询
select * from employees e, department d, location l
where e.[department_id]=d.[department_id] and e.[location_id]=l.[location_id] and e.[last_name]='z';
select * from employees e, department d, location l where e.department_id=d.department_id and e.location_id=l.location_id and e.last_name='z';
--单行子查询: 查出所有工资大于平均工资的员工信息
select * from employees where salary >(select avg(salary) from employees)
--多行子查询: 查出所有所有姓z的部门名称
先得到部门ID:select department_id from employees where last_name='z'
select name from department where department_id in (select department_id from employees where last_name='z');
SQL structed query language连接MYSQL服务器:mysql -uroot -prootDatabase Server : Database(DB)TableDDL (Data Definition Language )createalterdropDML:(Data Manipulation Language) insert intodelete from updateselect 表的一行(row)称之为一条记录表中一条记录对应一个java对象的数据表的一列(column)称之为一个字段1、Java是使用对象封装数据,例如程序产生了如上所示2个user对象,这些对象的数据要想保存到数据库中,需要在数据库中创建与之对应的表,一个user对象中封装的数据,要保存到数据库中,一般就要在数据库中创建一个与之对应的表。2、对象的属性定义为表头,对象的数据对应于表中的一条记录。3、每个对象对应于表中的一条记录。数据库的操作:创建,查看,修改,删除*创建:创建一个名称为mydb1的数据库。create database mydb1;创建一个使用utf-8字符集的mydb2数据库。create database mydb2 character set utf8;创建一个使用utf-8字符集,并带校对规则的mydb3数据库。create database mydb2 character set utf8 collate utf8_general_ci;*查看:显示所有数据库show databases;显示创建数据库的语句信息show create database mydb2;*修改:修改mydb1的字符集为gbk(不能修改数据库名)alter database mydb1 character set utf8;*删除:删除数据库mydb2drop database mydb1;表的操作:创建,查看,修改,删除*创建:根据实体类Person创建表personPerson {int id;String name;}create table person(id int,name varchar(20));mysql中的数据类型:bit 1位 但可以指定位数,如:bit<3>int 2字节 可以指定最大位数,如:int<4> 最大为4位的整数float 2个字节 可以指定最大的位数和最大的小数位数,如:float<5,2> 最大为一个5位的数,小数位最多2位double 4个字节 可以指定最大的位数和最大的小数位数,如:float<6,4> 最大为一个6位的数,小数位最多4位char 必须指定字符数,如char(5) 为不可变字符 即使存储的内容为'ab',也是用5个字符的空间存储这个数据varchar 必须指定字符数,如varchar(5) 为可变字符 如果存储的内容为'ab',占用2个字符的空间;如果为'abc',则占用3个字符的空间text: 大文本(大字符串)blob:二进制大数据 如图片,音频文件,视频文件date: 日期 如:'1921-01-02'datetime: 日期时间 如:'1921-01-02 12:23:43'timeStamp: 时间戳,自动赋值为当前日期时间DATETIME 日期和时间的组合。支持的范围是'1000-01-01 00:00:00'到'9999-12-31 23:59:59'。MySQL以'YYYY-MM-DD HH:MM:SS'格式显示DATETIME值,但允许使用字符串或数字为DATETIME列分配值。 TIMESTAMP[(M)] 时间戳。范围是'1970-01-01 00:00:00'到2037年。 TIMESTAMP列用于INSERT或UPDATE操作时记录日期和时间。如果你不分配一个值,表中的第一个TIMESTAMP列自动设置为最近操作的日期和时间。也可以通过分配一个NULL值,将TIMESTAMP列设置为当前的日期和时间。 TIMESTAMP值返回后显示为'YYYY-MM-DD HH:MM:SS'格式的字符串,显示宽度固定为19个字符。如果想要获得数字值,应在TIMESTAMP 列添加+0。 创建一个员工表create table employee(id int,name varchar(20),sex bit,birthday date,entry_date date,job varchar(20),salary double,resume text);*查看:查看所有的表:show tables;查看指定表的创建语句show create table employee;显示指定表的结构:desc employee;*删除:删除employee表drop table employee;*修改表:增加一个字段:alter table worker add column height double;修改一个字段:alter table worker modify column height float;删除一个字段:alter table worker drop column height;更改表名:rename table employee to worker;修改表的字符集:alter table worker character set gbk;表数据的CRUDCRUD操作:指在做计算处理时的增加(Create)、查询(Retrieve)、更新(Update)和删除(Delete) *C(create增加数据) Insert语句 :注意:字符和日期型数据应包含在单引号中。show variables like 'character%';mysql -uroot -proot --default_character_set=gbk新建Employee表并表中添加一些记录create table employee(id int,name varchar(20),sex bit,birthday date,salary double,entry_date date,resume text);insert into employee(name,id,sex,birthday,salary,entry_date,resume) values('张三',1,1,'1983-09-21',15000,'2012-06-24','一个大牛');insert into employee(id,name,sex,birthday,salary,entry_date,resume) values(2,'李四',1,'1984-09-21',10000,'2012-07-24','一个中牛');insert into employee(id,name,sex,birthday,salary,entry_date,resume) values(3,'王五',0,'1985-09-21',7000,'2012-08-24','一个小牛');*U(update更新数据) Update语句 :将所有员工薪水都增加500元。update employee set salary=salary+500;将王五的员工薪水修改为10000元,resume改为也是一个中牛update employee set salary=10000,resume='也是一个中牛' where name='王五';*D(drop删除数据) Delete语句 :删除表中姓名为王五的记录。delete from employee where name='王五';删除表中所有记录。delete from employee; --可以有条件,但删除所有记录差了一点使用truncate删除表中记录。truncate employee;--无条件 效率高*R(Retrieve查找数据) Select语句:准备环境:使用 SQLyog_Enterprise,企业版。create table student(id int,name varchar(20),chinese int,english int,math int);insert into student(id,name,chinese,english,math) values(1,'何东',80,85,90);insert into student(id,name,chinese,english,math) values(2,'权金属',90,95,95);insert into student(id,name,chinese,english,math) values(3,'何南',80,96,96);insert into student(id,name,chinese,english,math) values(4,'叶子',81,97,85);insert into student(id,name,chinese,english,math) values(5,'何西',85,84,90);insert into student(id,name,chinese,english,math) values(6,'丁香',92,85,87);insert into student(id,name,chinese,english,math) values(7,'何北',75,81,80);insert into student(id,name,chinese,english,math) values(8,'唐中',77,80,79);insert into student(id,name,chinese,english,math) values(9,'任已',95,85,85);insert into student(id,name,chinese,english,math) values(10,'王八',94,85,84);//关键字:select from where distinct查询表中所有学生的信息。select * from student;查询表中所有学生的姓名和对应的英语成绩。select name,english from student;过滤表中重复数据。select english from student;select DISTINCT english from student;select DISTINCT english,name from student;select english+chinese+math from student;select english+chinese+math as 总分 from student;select name,english+chinese+math as 总分 from student;//关键字 as在所有学生英语分数上加10分特长分。select name,english+10 from student;统计每个学生的总分。select english+chinese+math from student;使用别名表示学生分数select name,english+chinese+math as 总分 from student;select name,english+chinese+math 总分 from student;//关键字:where查询姓名为何东的学生成绩select * from student where name='何东';查询英语成绩大于90分的同学select * from student where english>90;查询总分大于250分的所有同学select * from student where english+chinese+math>250;查询英语分数在 85-95之间的同学。select * from student whereenglish>=85 and english<=95;select * from student where english between 85 and 95;查询数学分数为84,90,91的同学。select * from student where math=84 or math=90 or math=91;select * from student where math in(84,90,91);查询所有姓何的学生成绩。select * from student where name like '何%';select * from student where name like '何_%';注意:Like语句中,% 代表零个或多个任意字符,_ 代表一个字符,例first_name like ‘_a%’;查询数学分>85,语文分>90的同学。select * from student where math>85 and chinese>90;//关键字 order by(默认为升序) desc为降序 asc 升序 别名 as对数学成绩排序后输出。select * from student order by math;对总分排序后输出,然后再按从高到低的顺序输出select * from student order by math+chinese+english desc;select name, math+chinese+english as sumScroe from student order by sumScroe desc;对姓何的学生成绩排序输出select * from student where name like '何%' order by math+chinese+english desc;select name, math+chinese+english from student where name like '何%' order by math+chinese+english desc;//关键字 count统计一个班级共有多少学生?select count(*) from student;统计数学成绩大于90的学生有多少个?select count(*) from student where math>90;统计总分大于250的人数有多少?select count(*) from student where math+chinese+english>250;//关键字 sum统计一个班级数学总成绩?select sum(math) from student;统计一个班级语文、英语、数学各科的总成绩select sum(math), sum(chinese), sum(english) from student;统计一个班级语文、英语、数学的成绩总和select sum(math+chinese+english)from student;select sum(math)+sum(chinese)+sum(english) from student;//关键字 avg求一个班级数学平均分?select avg(math) from student;求一个班级总分平均分select avg(math+chinese+english)from student;select avg(math)+avg(chinese)+avg(english) from student;//关键字 max min求班级最高分和最低分select max(math+chinese+english),min(math+chinese+english) from student;//关键字 group by having查出各个班的总分,最高分使用group by 子句对列进行分组Having和where均可实现过滤,但在having可以使用合计函数,having通常跟在group by后,它作用于组。准备环境:使用 SQLyog_Enterprise,企业版。给表添加一个字段:alter table student add column class_id int;更新表:update student set class_id=1 where id<=5;update student set class_id=2 where id>5;select sum(math+chinese+english),max(math+chinese+english) from student group by class_id;查询出班级总分大于1300分的班级IDselect class_id from student group by class_id having sum(math+chinese+english)>1300;表的约束 *定义主键约束 primary key:不允许为空,不允许重复*定义主键自动增长 auto_increment*定义唯一约束 unique*定义非空约束 not null*定义外键约束 constraint ordersid_FK foreign key(ordersid) references orders(id)*删除主键:alter table tablename drop primary key ;CREATE TABLE user(id INT PRIMARY KEY AUTO_INCREMENT, #主键,自增长name VARCHAR(20) UNIQUE, #不可重复password VARCHAR(15) NOT NULL #不允许为空)insert into user(name, password) values('MM', NULL);#执行不通过Column 'password' cannot be nullinsert into user(name, password) values('MM', '123456');#通过insert into user(name, password) values('MM', '123sdfd');#执行不通过Duplicate entry 'MM' for key 'name'//测试外键准备环境:创建一个班级表:create table class(id int primary key auto_increment,name varchar(16));create table student(id int(11) primary key auto_increment default null,name varchar(20) default null,chinese int(11) default null,english int(11) default null,math int(11) default null,classid int(11) default null,constraint student_classid_FK foreign key(classid) references class(id));insert into class(name) values('三年一班');insert into class(name) values('三年二班');insert into student(name, classid) values('tom', 3); #classid的值只能是被引用的字段的某个值insert into student(name, classid) values('tom', 2);delete from class where id=2; #删除被引用的记录无法成功select * from class;select * from student;数据的备份与恢复:*准备数据:create database mydb1;use mydb1;create table user(id int,name varchar(20));insert into user(id,name) values(1,'Tom');select * from user;*备份:mysqldump -uroot -p mydb1>d:\test.sql;--备份数据库里的数据Mysqldump -uroot -proot mydb1 > test_backup.sql*恢复;mysql -uroot -p mydb1<d:\test.sqlsource d:\test.sql(在mysql客户端上执行,需要选中use要导进的库)多表查询与子查询:准备环境:部门表CREATE TABLE DEPARTMENT( department_id int primary key auto_increment, name varchar(20));职位表CREATE TABLE LOCATION ( location_id int PRIMARY KEY auto_increment, name varchar(15));员工表CREATE TABLE EMPLOYEES( employee_id int PRIMARY KEY auto_increment, department_id int, location_id int, first_name varchar(10), last_name varchar(10), salary double, hire_date date, constraint employees_department_id_FK foreign key(department_id) references department(department_id), constraint employees_location_id_FK foreign key(location_id) references location(location_id));向department表中添加数据 insert into department(name) values ('人事');insert into department(name) values ('财务');insert into department(name) values ('后勤');insert into department(name) values ('公关');insert into department(name) values ('研发');向location表中添加数据insert into location(name) values ('总经理');insert into location(name) values ('经理');insert into location(name) values ('主管');insert into location(name) values ('组长');insert into location(name) values ('职员');向employees表中添加数据insert into employees(department_id,location_id , first_name, last_name, salary,hire_date) values (1,1, 'A', 'z', 50000, '2005-02-21'); insert into employees(department_id,location_id, first_name, last_name, salary,hire_date) values (1,2, 'B', 'x', 20000, '2009-03-21'); insert into employees(department_id,location_id , first_name, last_name, salary,hire_date) values (2,3, 'C', 'v', 10000, '2009-08-23'); insert into employees(department_id,location_id, first_name, last_name, salary,hire_date) values (4,2, 'D', 'n', 30000, '2004-09-28'); insert into employees(department_id,location_id, first_name, last_name, salary,hire_date) values (3,5, 'E', 'm', 3000, '2009-04-11'); insert into employees(department_id,location_id, first_name, last_name, salary,hire_date) values (5,5, 'F', 'l', 5000, '2008-03-11'); insert into employees(department_id,location_id, first_name, last_name, salary,hire_date) values (5,3, 'G', 'p', 20000, '2005-05-09'); insert into employees(department_id,location_id, first_name, last_name, salary,hire_date) values (5,4, 'H', 'o', 8000, '2006-07-21'); insert into employees(department_id,location_id, first_name, last_name, salary,hire_date) values (5,5, 'I', 'u', 6000, '2006-09-21'); insert into employees(department_id,location_id, first_name, last_name, salary,hire_date) values (5,5, 'J', 'y', 5500, '2007-08-21'); insert into employees(department_id,location_id, first_name, last_name, salary,hire_date) values (5,5, 'K', 't', 6500, '2006-12-21'); insert into employees(department_id,location_id, first_name, last_name, salary,hire_date) values (5,1, 'L', 'r', 100000, '2001-05-21'); 练习:--多表查询:查出姓z的员工的所有信息(使用别名)#联表查询select * from employees e, department d, location l where e.[department_id]=d.[department_id] and e.[location_id]=l.[location_id] and e.[last_name]='z';select * from employees e, department d, location l where e.department_id=d.department_id and e.location_id=l.location_id and e.last_name='z';--单行子查询: 查出所有工资大于平均工资的员工信息 select * from employees where salary >(select avg(salary) from employees) --多行子查询: 查出所有所有姓z的部门名称 先得到部门ID:select department_id from employees where last_name='z'select name from department where department_id in (select department_id from employees where last_name='z');
- Ubuntu 12.04 安装 MySQL--及简单的Show,Use,Create,drop,alter,CRUD,export,import
- hive Create/Drop/Alter/Use Database
- CREATE TABLE、ALTER TABLE、DROP TABLE等,及表内列的添加、删除以及列的修改
- MYSQL——DDL : 数据定义语句 (create alter drop)
- SQL-create-alter-drop-DDL
- ORA-14452: attempt to create, alter or drop an index on temporary table already in use
- ORA-14452: attempt to create, alter or drop an index on temporary table already in use
- DLL的Export和Import及extern
- ES6中export及export default、import的区别
- MSSQL扫盲系列(1)-CREATE,ALTER,DROP
- 数据库定义语言:CREATE、ALTER、DROP、RENAME
- Mysql import export procedure
- MySQL 4.1.0 中文参考手册 --- 6.5 数据定义: CREATE、DROP、ALTER
- MySQL数据定义语句:CREATE(创建)命令、ALTER(修改)命令、DROP(删除)
- MySQL show create table
- mysql建立索引的优缺点|创建索引alter或create索引分类(PRIMARY KEY,UNIQUE KEY,FULLTEXT,INDEX)作用查看索引show index from table
- 数据库SQL 表的基本操作(create,drop,alter,insert,update,delete,select)
- 关系数据库标准语言SQL的基本操作:SELECT, CREATE, DROP, ALTER, INSERT, UPDATE, DELETE
- Effective C++(七)模板与泛型编程
- 多元函数的极值
- MySQL Command Line Client
- vc++6.0出现fatal error C1853的解决办法
- 【Android开发笔记】4.简单基站定位程序
- Ubuntu 12.04 安装 MySQL--及简单的Show,Use,Create,drop,alter,CRUD,export,import
- DC--类库MFC中的结构体
- 【Cocos2D-X游戏开发笔记2】自定义场景及场景切换效果
- 上下文切换
- C# Socket实现聊天通信
- Linux常用命令之awk
- 判断一个数是否是回文数
- 软件工程-第四章- 面向过程的软件设计方法
- Linux驱动基本框架简介