sql

1.关键词

SELECT - 从数据库中提取数据
UPDATE - 更新数据库中的数据
DELETE - 从数据库中删除数据
INSERT INTO - 向数据库中插入新数据
CREATE DATABASE - 创建新数据库
ALTER DATABASE - 修改数据库
CREATE TABLE - 创建新表
ALTER TABLE - 变更（改变）数据库表
DROP TABLE - 删除表
CREATE INDEX - 创建索引（搜索键）
DROP INDEX - 删除索引

字段名的修饰:
1.列名
unsigned
auto_increment 自增
default
comment
not null / null
unique
index
key
primary key

auto_increment 必须制定成primary key

1.数据库表操作(DDL, Data Definition Languages)

show databases;
use 数据库名
creat database xxx;
drop database xxx;

show tables;
desc xxx; 查看列表的列的类型
creat table xxx;
drop table xxx;

creat table AccountInfo (
id int not null auto_increment comment ＂用户id＂，
real_name varchar(20) not null comment “用户实名”，
nick_name varchar(20) not null comment “用户昵称”，
birth _date date not null comment＂出生日期＂，
primary key(id)
)
CREATE TABLE IF NOT EXISTS runoob_tbl( runoob_id INT UNSIGNED AUTO_INCREMENT, runoob_title VARCHAR(100) NOT NULL,runoob_author VARCHAR(40) NOT NULL, submission_date DATE , PRIMARY KEY (runoob_id))ENGINE=InnoDB, DEFAULT CHARSET=utf8;

alter修改表：

drop tablename 删除表
alter table 原表名 rename [to] 新表名
修改列属性：
alter table 表名 modify gender enus(‘男’,‘女’) not null default ‘男’ 修改列属性
--------------------- change gender gender enus(‘男’,‘女’) not null default ‘男’ （重复列名或修改之后的名字)
-------------------- add gender [first] [after coulumname]
-------------------- drop

ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
alter table testtable alter id set default 100;
alter table testtable alter id drop default;

2.数据操作语言(DML, Data Manipulation Language)

查询:
select * from 表名；
插入:
insert into 表名 (列名１，列名２) values(列值１，列值２)
insert into values()，必须按照列顺序插入
更改中文编码
show variables like ‘%char%’
若要长久修改，则需改配置文件，修改mysql.ini文件，把其中的编码格式改掉；
特别注意在做每次改变时，一定要输入 flush privilges 刷新权限

修改:
update 表名 set 字段名=字段值 where 条件;

update student set name=‘nico’ where id=1;

update student set name=‘nico’ where id in (2, 4, 6);同时改2,4,6

update student set name=‘nico’ where id between 2 and 5;同时改2,3,4,5

删除:
delete from 表名 where 条件(id=2);

truncate 表名;清空表

3.数据控制语言(DCL, Data Control Language)

修改密码:
方法一:进入usr表,只保存ip所在内容，其他删掉，或将ip改成自己的ip，这样更安全
方法二:mysql -u root -p 原密码 password 新密码;

如果忘记密码了:
mysql -skip -grant -tables跳过数据库的限制

创建普通用户:
creat user ＇用户名’@‘ip地址’ identified by ＇密码’;
ip地址如果是%，表示对所有ip都可以连接

grant 权限１，权限２,… on 数据库名.* to 用户名＠ip
grant all privileges on . to nico@localhost
grant privilegesCode on dbName.tableName to username@host identified by “password”;
撤销权限时
revoke 权限 数据库名.* from 用户名@ip

4.数据库查询语言

select 列名1,列名2,… from 表名(while条件)；　选择指定列
select distinct　列名１ fron table 过滤重复值
select concat(列名1,列名2) from table　将列内容链接起来
select concat_ws(分割符，列名1,列名2) from table　将列内容链接起来
select concat(列名1,列名2) as 新列名 from table　将列内容链接起来

列起别名用as: select studentId as Id from student

模糊查询:
select 列名 from 表名 where 列名 like ‘%ng%’ %ng%指前后都模糊
select * from table order by 字段名 (asc);　升序
select * from table order by 字段名 dsc;　　降序

聚合函数:
select count(*) from table; 　查询记录数
select sum(列名) fron table;
select avg(列名) fron table;
select max(列名) fron table;
select min(列名) fron table;

select 列名 from 表名 group by 列名（如 sex）; 按照性别分组；
select * from 表名 group by 列名 having 列名>条件值； 如 ：select * from hihi group by age having age>18;将年龄大于18的分组；
select count(*) from 表名 group by 列名 having 列名>条件值；
select real_name, count(nick_name) from accountInfo group by real_name 【with rollup】;
with rollup 可以在分组的基础上继续调用函数

联合查询：列数必须一致

select 列名 from 表名 union all select 表名 from 列名；

子查询

select * from 表名1 where id in (select 列名 from 表名2);
推荐内连接；
inner join
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

left join 将左边所有记录打印出来，即使右边不匹配
right join 将右边所有记录打印出来，即使左边不匹配

NULL

只有is NULL或者is not NULL

REGEXP，正则

SELECT name FROM person_tbl WHERE name REGEXP ‘ok$’;

复制表

1.show create table tablename;

2.复制表结构，然后然后改名字
student | CREATE TABLE newname (
-> id int(10) unsigned NOT NULL AUTO_INCREMENT,
-> name varchar(20) NOT NULL,
-> PRIMARY KEY (id)
-> ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
-> ;
3.insert into newname (列名1， 列名2…) select (列名1， 列名2…) from oldname;

重复数据

1.设置主键primary key
primary key(firstname, lastname)

2.设置唯一索引
unique （firstname, lastname)
3.过滤重复值
select distinct firstname, lastname from table;

select firstname ,lastname from table group by (firstname, lastname);

4.删除重复数据
a.新建tmp表

 CREATE TABLE tmp SELECT last_name, first_name, sex FROM person_tbl  GROUP BY (last_name, first_name, sex);
 DROP TABLE person_tbl;
ALTER TABLE tmp RENAME TO person_tbl;

b.建立索引方式

ALTER IGNORE TABLE person_tbl  ADD PRIMARY KEY (last_name, first_name);

in

mysql

if not exist

select 语句顺序

select 
from
where
group by
having
order by

将sqlalemy的sql语句打印出来

1.engine = create_engine("<db_rul>", echo=True) 创建引擎的时候加上echo打印

2.直接打印

query = session.query(Model).filter(*query_cond).order_by(order_info)
print(str(query))

3.上述两种方式只能打印基本语句，不会带上参数

from sqlalchemy.dialects import mysql

 # query就是第二种方式的query
print str(query.statement.compile(dialect=mysql.dialect(), compile_kwargs={"literal_binds": True}))

mysql方法

1.插入替换，存在就删除替换记录，没有就创建记录

REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99); 

2.插入更新，存在就更新记录由update指定的字段指示，没有就创建记录

INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99

3.插入忽略。存在记录就忽略改动

INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

复制表

复制表数据

insert into calendar(end, date, market, fund) select end, date, market, 2002 from calendar where fund = 1001;

复制表结构和数据

CREATE TABLE test_new SELECT * FROM test_old

复制表结构

CREATE TABLE test_new LIKE test_old;  #专门复制表结构，包括主键等信息
CREATE TABLE test_new SELECT * FROM test_old WHERE 1=2; # 只复制属性，不复制索引。外键等