mysql课件

•安装mysql

•Mysql基本命令

•DDL语句

•DML语句

•常用的数据类型

Mysql的安装

•Windows平台下的安装

•Linux/Unix平台下的安装

Mysql基本命令

•启动：mysqld

•客户端登录：mysql -u root -p

Mysql基本命令

•显示所有当前用户的database：

show databases;

•选中一个database：

use mysql;

Mysql基本命令

•查看当前数据库下的所有的表

show tables;

•查看表的结构

desc user;

DDL创建删除数据库

•创建数据库: create database mytest;

•删除数据库: drop database mystest;

DDL创建删除表

•创建表

create table student(

 id  int primary key auto_increment,

 name varchar(20),

 age int

)

•删除表: drop table student;

DDL修改表结构—列的增删改

•添加列

alter table student add(school varchar(20));

•修改列

alter table student modify school varchar(30) not null;

•删除列

alter table student drop column school;

DDL修改表结构—默认值

•添加列的默认值

alter table student alter school set default ‘安博实训';

•删除列的默认值

alter table student alter school drop default;

DDL修改表结构—约束

•显示表的约束

show keys from student;

•主键约束的添加/删除

alter table student

modify id int primary key auto_increment;

alter table student drop primary key;

DDL修改表结构—约束

•非空约束的增加/删除

alter table student modify school varchar(30) not null;

alter table student modify school varchar(30);

•唯一性约束的增加/删除

alter table student modify school varchar(30) unique;

show keys from student;

alter table student drop key student;

DML插入数据

•插入一行数据

insert into student (id, name, age, school)

values(1,’pilipala’,11,’miao miao school’);

insert into student (id, name, age, school)

values(2,’lalilula’,10,’苗苗小学’);

DML删除数据

•删除所有数据

delete from student;

•删除一行数据

delete from student where id=2;

DML修改数据

•修改所有行的数据

update student set name=‘hehehe’;

•修改特定行的数据

update student set age=21 where id=1

查询语句

•查出表中所有的数据

select * from student;

•查出特定列的数据

select name, school from student;

查询语句—where子句

•含有逻辑判断的where子句

select id name, school from student

where id=2;

select name, school from student

where age >14;

select name, school from student

where name=‘hehe’;

查询语句—where子句

•含有逻辑判断的where子句

select name,age, school from student

where age >14 and school=‘rugao’;

select name, school from student

where age >20 or age<10;