mysql笔记

mysql数据库操作(php执行速度比mysql快,所以能用php实现尽量不要用mysql编程)

1.创建数据库:

create database jiuxian;

2·创建表:

create table user(
    id int unsigned auto_increment primary key,
    username varchar(50) not null,
    age int
);
或者:
create table users(
    id int unsigned auto_increment,
    username varchar(50) not null,
    age int,
    primary key(id)
);

3·删除数据库:

drop database jiuxian;      

4·删除表:

drop table user;    

5·增:

insert into user(id,username,age)values(1,'donglianyou',30);

6·删:

delete from user where id=1;    

7·改:

update user set age=40 where id=1;

8·查:

select * from user where id=1;
int ,float ,char, varchar ,text

查询时字段使用别名:

select id,username as user,password as pass from t1;

​ 或者:

select id,username user,password pass from t1;

去除重复值查询:

select distinct class from user;

查找指定某些值:

select * from user where id in(1,3,5,6,10);

模糊查询:

select * from user where name like "%mysql%";

sql正则查询:

select * from user where username regexp '^php';
select * from user where username regexp '^php';
select * from user where username regexp 'php$';

sql查询排序:

select * from user order by id asc;
select * from user order by id desc;

sql字符串连接:

select concat(username,'-',class_id) as userclass from user;

查询最大id的记录:

select * from user where id in (select max(id) from user);

左连接(把左边的表数据全输出出来):

分组聚合查询每个班级多少人:

select class.name,count(user.id) as '人数' from class left join user on class.id=user.class_id group by class.id;

班级没人的写无:

select class.name,if(count(user.id),count(user.id),'无') from class left join user on class.id=user.class_id group by class.id;

内连接基本完全等于普通多表查询

查询所有学生中及格和不及格人数,使用一个sql语句:

select sum(if(score>=60,1,0)) 及格,sum(if(score<60,1,0)) 不及格 from score;

9.MYSQL索引优化

添加普通索引

alter table t1 add index idx_name(name);

删除普通、唯一索引

alter table t1 drop index idx_name;

添加唯一索引

alter table t1 add unique uni_name(name);

给用户表用户名添加唯一索引,用户名不重复,最佳实践

添加主键索引

创建表时添加:primary key

删除主键索引

先删除自增:

alter table t1 modify id int unsigned not null; 

然后删除主键索引:

alter table t1 drop primary key;

10.增、删、改字段

删除字段:

alter table t1 drop sex;

添加字段在最后:

alter table t1 add sex tinyint not null;

添加字段在某字段之后:

alter table t1 add sex tinyint not null after name;

添加字段在第一个:

alter table t1 add sex tinyint not null first;

修改字段:

alter table t1 change name username varchar(30) not null;

修改字段属性:

alter table t1 modify username varchar(50) not null;

11.表与表之间的关系

①合并

②分离

③一个表一个主体,两个表时一对一关系,则可以合并

④一对一

⑤一对多

⑥多对多

class表:

create table class(
    id int unsigned not null auto_increment,
    name varchar(30) not null,
    primary key(id)
);

user表:

create table user(
    id int unsigned not null auto_increment,
    username varchar(50) not null,
    password varcahr(50) not null,
    class_id int unsigned not null,
    primary key(id)
);

两表之间有关联的字段属性必须一致例如:

class表的id:

id int unsigned not null auto_increment,

user表的class_id:

class_id int unsigned not null,

12.having关键字使用(分组聚合加条件时使用)

select class_id from user group by class_id having class_id<2;  

13.PDO数据库操作:

PDO的作用

一套函数可以操作不同的数据库

①CUBRID

②MS SQL SERVER

③Firebird

④Informix

⑤Mysql

⑥MS Sql

⑦Oracle

⑧ODBC

⑨PostgreSQL

⑩SQLite

11.4D

12.DB2

PDO查询数据库

<?php
$pdo = new PDO('mysql:host=localhost;dbname=jiuxian','root','root');
$sql = "select * from user";
$smt = $pdo->query($sql);
$rows = $smt->fetchAll(PDO::FETCH_ASSOC);
echo "<pre>";
print_r($rows);

PDO插入数据

<?php
$pdo = new PDO('mysql:host=localhost;dbname=jiuxian','root','root');
$sql = "insert into user(username,password,class_id) values('user11',123,4)";
if($pdo->exec($sql)){
    echo "插入数据成功!";
}else{
    echo "插入失败!";
}

PDO删除数据

$pdo = new PDO('mysql:host=localhost;dbname=jiuxian','root','root');
$sql = "delete from user where id=12";
if($pdo->exec($sql)){
    echo "删除数据成功!";
}else{
    echo "删除失败!";
}

PDO修改数据

<?php
$pdo = new PDO('mysql:host=localhost;dbname=jiuxian','root','root');
//设置客户端字符集为utf8
$pdo->exec('set names utf8');
$sql = "update user set username='dong' where id=11";
if($pdo->exec($sql)){
    echo "修改数据成功!";
}else{
    echo "修改失败!";
}

PDO预处理

<?php
$pdo = new PDO('mysql:host=localhost;dbname=jiuxian','root','root');
$sql = "select * from user";
$smt=$pdo->prepare($sql);
$smt->execute();
$rows = $smt->fetchAll(PDO::FETCH_ASSOC);
echo "<pre>";
print_r($rows); 
0
如无特殊说明,文章均为本站原创,转载请注明出处
  • 转载请注明来源:mysql笔记
  • 本文永久链接地址:http://www.mobanw.com/php/219.html

该文章由 发布

这货来去如风,什么鬼都没留下!!!
发表我的评论

Hi,请填写昵称和邮箱!

取消评论
代码 贴图 加粗 链接 删除线 签到