mysql学习笔记1

create table book(
   id INT NOT NULL AUTO_INCREMENT,
   book_name VARCHAR(100) NOT NULL,
   author VARCHAR(40) NOT NULL,
   publish_date DATE,
   price float,
   PRIMARY KEY (id )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into book values(1,"seleinum","wulao","2018-1-1",50);
insert into book values(2,"mysql","wulao","2018-2-1",60);
insert into book values(3,"linux","wulao","2018-3-1",80);
insert into book(book_name,author,publish_date,price) values("mangodb","zhanglao","2019-3-1",100);
insert into book(book_name,author,publish_date,price) values("bdb","zhanglao","2019-4-1",110);

select * from book;

select count(*) from book;
select book_name from book;
select book_name  as 书名 from book;
select id as 序号,book_name  as 书名 from book;
select * from book where id = 1;
select * from book where id >= 6;
select * from book where author = 'zhanglao';
select * from book where author <> 'zhanglao';
select * from book order by price asc;
select * from book order by price desc;
select count(*),author from book group by author;
select avg(price),author from book group by author;
select sum(price),author from book group by author;
select sum(price),author from book group by author having sum(price)>200;
select count(*),author from book where id >5 group by author;


delete from book where id = 4;

update book set book_name="gloryroad db" where book_name="bdb";

create table student(
id INT NOT NULL AUTO_INCREMENT,
name varchar(20),
sex varchar(6),
submission_date date,
PRIMARY KEY (id)
)engine=innodb DEFAULT CHARSET=utf8;

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");

create table id(id int);
insert into id values(1);
insert into id values(2);
insert into id values(3);
insert into id values(4);
insert into id values(5);

子查询：
select * from student where id in (
select * from id where id>3);

内连接和左连接、右连接：
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;


create table department(
id int not null auto_increment,
department_name varchar(20) not null,
PRIMARY KEY (id)
)engine=innodb DEFAULT CHARSET=utf8;


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);

insert into department values(1,"Hr");
insert into department values(2,"IT");
insert into department values(3,"OP");
insert into department values(4,"Finance");

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 left join department as depart on
 person.department_id = depart.id;

select person.name,depart.department_name from
 a as person right join department as depart on
 person.department_id = depart.id;

select person.name from
 a as person right join department as depart on
 person.department_id = depart.id;

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

select name from a
union
select department_name from department;

select id from a
union
select id from department;

select id from a
union all
select id from department;

python安装mysql

py -3 -m pip install pymysql

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 "学生信息表";

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 "学生成绩表";

import pymysql
import random
def insertData():
    conn = pymysql.connect(
    host = "127.0.0.1", 
    port = 3306,
    user = "root", 
    passwd = "11111111", 
    db = "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.commit()
    conn.close()
   
insertData()
print("数据插入结束！")

select * from grade;