mysql常用操作二

一、创建一个数据库

create database testnewman;

show databases;

这个可以告诉你这个库在或者不在；不存在创建，如果存在则不创建

CREATE DATABASE IF NOT EXISTS testnewman DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

二、使用表

use testnewman;

select database();    #查看你当前使用的库是谁

# drop database testnewman;    #删除表，建议最后再操作；删完之后需重新创建

show tables;     #查询表

三、创建表

3.1创建student表

create table student(id int(11) NOT NULL AUTO_INCREMENT,

name varchar(20),

sex varchar(6),

submission_date date,

PRIMARY KEY (id)

)engine=innodb DEFAULT CHARSET=utf8;

3.2创建id表

create table id(id int);

insert into id values(2);

insert into id values(3);

insert into id values(4);

insert into id values(5);

select *from id;

四、插入数据

insert into student values(1,"小吴","男","2018-10-10");

insert into student values(2,"小张","女","2017-9-10");

insert into student values(3,"小李","女","2016-9-10");

insert into student values(4,"小程","男","2015-9-10");

五、查询数据

5.1 where查询

select * from student;

select * from student where id=1;

select * from student where sex="男";

select name as "男" from student where sex="男";

select name as "男" from student where sex="男" limit 1;

select name as "男" from student where sex="男" limit 1,1;

select name as "男" from student where sex ='男' and id =4;

select name as "男" from student where sex ='男' or id =4;

select name as "男" from student where submission_date >"2018-01-01";

select name as "男" from student where submission_date <>"2018-01-01";

select count(*),sex from student group by sex;

select count(*),sex from student where id <3 group by sex;

select count(*),sex from student where id >1 group by sex having count(*)=1;

5.2 in、not  in查询

select * from student where id in (1,2);

select * from student where id not in (1,2);

select * from student where id in (select * from id where id>3);

六、左连接、右连接

6.1 创建表及插入数据

6.1.1创建A表

create table A(id int not null auto_increment,

 name varchar(20) not null,

 sex varchar(6),

 salary varchar(20),

 department_id int,

 PRIMARY KEY (id)

 )

 engine=innodb DEFAULT CHARSET=utf8;

6.1.2创建department表

create table department(

 id int not null auto_increment,

 department_name varchar(20) not null,

 PRIMARY KEY (id)

)engine=innodb DEFAULT CHARSET=utf8;

 

6.1.3表中插入数据

insert into department values(1,"Hr");

insert into department values(2,"IT");

insert into department values(3,"OP");

insert into department values(4,"Finance");

insert into A values(1,"jone","male",100,1);

insert into A values(2,"Jane","female",60,2);

insert into A values(3,"Jason","female",50,3);

insert into A values(4,"Jordan","male",20,10);

6.2 内连接

select person.name,depart.department_name from

A as person inner join department as depart on

person.department_id = depart.id;

或者

select person.name,depart.department_name from

A as person join department as depart on

person.department_id = depart.id;

不推荐使用内连接，不会使用索引，扫描速度比较慢；数据多时可能带来灾难；

 

6.3 左连接

select person.name,depart.department_name from

A as person left join department as depart on

person.department_id = depart.id;

备注：会把(左表)A表中所有字段列出来

6.4 右连接

select person.name,depart.department_name from

A as person  right join department as depart on

person.department_id = depart.id;

备注：会把（右表）depart中所有字段列出来

 

取多个字段

 

select person.*,depart.* from 
A as person right join department as depart on 
person.department_id = depart.id;

 

6.5 union与union  all的使用

6.5.1 union的使用

select name from A

union

select department_name from department;

备注：把两个结果合并

6.5.2 union all的使用

select name from A

union all

select department_name from department;

6.5.3  union和union all的差异

备注：union 合并会排重

代码部分：

select id from A

union all

select id from department;

 

select id from A

union

select id from department;

七、用mysql造100条数据

7.1建表

7.1.1创建表studentInfo

 

create table studentInfo(

  ID int not null auto_increment comment "不为空的自增长的主键ID",

  student_id varchar(20) not null,

  name varchar(30) not null,

  sex char(4),

  tel varchar(13) unique not null,

  AdmissionDate datetime default '0000:00:00 00:00:00',

  primary key (ID),

  unique student_id(student_id)

)engine=innodb character set utf8 comment "学生信息表";

备注：unique当索引时不允许重复

 

7.1.2创建表grade

create table grade(

  ID int auto_increment not null,

  stuID varchar(20),

  course varchar(20) not null,

  score tinyint(4) default 0,

  primary key (ID),

  key idx_stuid(stuID),

  CONSTRAINT FK_ID FOREIGN KEY(stuID) REFERENCES studentInfo(student_id)

)engine=innodb character set utf8 comment "学生成绩表";

7.2 数据库中插入100条数据

 

import pymysql

import random

def insertData():

    conn=pymysql.connect("127.0.0.1","root","123456","testnewman",charset="utf8")

    cur = conn.cursor()

    conn.select_db("testnewman")

     

    courseList = ['python', 'java', 'mysql', 'linux', '接口测试', '性能测试', '自动化测试','数据结构与算法']

    for i in range(1, 101):

        student_id = '201803' + '0' * (3 - len(str(i))) + str(i)

        name = random.choice(['Lucy','Tom','Lily','Amy','Dave','Aaron','Baron']) + str(i)

        tel = '1' + str(random.choice([3, 5, 7, 8])) + str(random.random())[2:11]

        sex = random.choice(['女', '男'])

        stuinfo_sql = "insert into studentInfo(student_id, name, sex, tel, AdmissionDate) \

        values('%s', '%s', '%s', '%s', date_sub(now(),interval %s day))" \

        %(student_id, name, sex, tel, random.randint(90, 120))

        cur.execute(stuinfo_sql)

        conn.commit()

        for j in courseList:

            grade_sql = "insert into grade(stuID,course,score) values('%s','%s',%s)" %(student_id,j,random.randint(80, 100))

            cur.execute(grade_sql)

    conn.commit()

    cur.close()

    conn.close()

  

insertData()

print("数据插入结束！")

 

运行结果：

查看数据：

 

备注：

1、外键：保证数据一致性

两个表关联，必须同时存在

2、运行该程序前需要先在cmd中导入pymsql模块，命令如下：

py -3 -m pip install pymysql