Mysql

select row, row+row, row-row, row*row, row/row from tb_book;
select id, books,  id=27 from tb_book;
select id, books,  id!=27 from tb_book;
select id, books,  row>27 from tb_book;
select id, books,  row IS NULL from tb_book;
select row,  row BETWEEN 10 AND 50, row BETWEEN 25 AND 28 from tb_book;
select row, row IN<10, 95, 30> from tb_book;
select user, user like 'mr' from tb_book;
select user, user REGEXP 'm', user regexp 'g$', user REGEXP '^m' from user;
select row, !row, row&&row, row||row, row XOR 0 from tb_book;

每个myisam在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD（MYData）。索引文件的扩展名是.MYI（MYIndex）。
Mysql支持的多种数据类型主要有：数值数据类型、日期/时间类型、字符串类型

create table tb_admin (
id int auto_increment primary key, 
user varchar(30) not null, 
password varchar(30) not null, 
createtime datetime);

show columns from tb_admin;
describe tb_admin;

alter table tb_admin add email varchar(50) not null, modify user varchar(40);
rename table tb_admin to tb_user;
drop table  if exists tb_user;
insert into tb_user(user, password, createtime, email) values('tsoft','111', '2019-8-15 16:43:00','hello@126.com');
select * from tb_user order by createtime desc;
select * from tb_user where user like('test_');
select concat(user,":",password) as info from tb_user where user like('test_');
 select *  from tb_user where id limit 1,4;


create table department(
    d_id int(4) not null primary key,
    d_name varchar(20) not null,
    unction varchar(50),
    address varchar(50));
    
create table worker(
	 id int(4) not null primary key auto_increment, 
	 num int(4), d_id int(4),
	 name varchar(20) not null, 
	 birthday date, 
	 constraint worker_fk foreign key(d_id) 
	 references department(d_id) );

drop table department;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

必须先删掉外键 才能删除主表
    alter table worker drop foreign key worker_fk;
    select version(), database(), user();

select ROUND(rand()*100), FLOOR(RAND()*100), CEILING(RAND()*100);
select format(1.987528, 2);
select lower("ABC");

show create table worker;

create table cards(
 id int(11) auto_increment primary key not null, 
 name varchar(50),
 number bigint(11), 
 info varchar(50), 
 FULLTEXT KEY cards_number(name))ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

create table telephone(
-> id int(11) primary key auto_increment not null,
-> name varchar(50) not null,
-> tel varchar(50) not null,
-> index tel_num(tel(20)));

create table information(
id int(11) auto_increment primary key not null,
name varchar(50) not null, 
sex varchar(5) not null,
INDEX info(name, sex));

创建唯一索引
CREATE UNIQUE INDEX index_1 ON information(id);
alter table information add index index_1  (id);
DROP INDEX index_1 ON information;

创建一个存储过程
delimiter //
create procedure count_of_student(OUT count_num INT) 
begin    
select count(*) into count_num from my_test;
end;
//

创建一个函数
delimiter //
create function name_of_student(std_id INT)
returns varchar(50)
begin
return (select name from studentinfo where sid=std_id);
end
//
delimiter //
create procedure p2()
begin
set @t=1;
begin
set @t=2;
select @t;
end;
select @t;
end;
//

声明一个光标，光标智能在存储过程或存储函数中使用，不能单独执行。
DECLARE info_of_student CURSOR FOR SELECT
sid, name, age, sex, age
FROM studentinfo
WHERE sid=1;

打开光标
OPEN info_of_student
使用光标
FETCH info_of_student INTO tmp_name, tmp_tel;
关闭光标
CLOSE info_of_student

查看存储过程
SHOW PROCEDURE | FUNCTION STATUS LIKE 'pattern';
SHOW CREATE PROCEDURE  | FUNCTION SP_NAME;

查看所有触发器
select * from information_schema.triggers;

创建视图
CREATE VIEW
   book_view(a_sort, a_talk, a_books)
   AS SELECT sort, talk, books
   FROM tb_book;

DESC book_view;

查看事务隔离等级

select @@tx_isolation;

备份一个数据库

mysqldump -u username -p dbname table1 table2 ... >Backupname.sql

mysqldump -u username -p --databases dbname1 dbname2... >Backupname.sql

mysqldump -u username -p --all -databases  >Backupname.sql

mydqlhotcopy 快速备份

还原

 mysql -u root -p <backup.sql;

SELECT * FROM student OUTFILE 'D:\student1.txt'
FIELDS TERMINATED BY '\,' OPTIONALLY ENCLOSED BY '\"' LINES STARTING BY '\>'
TERMINATED BY '\r\n';

mysqldump -u root -p111 -T D:\  test student
"--fields -terminated -by =," 
"-fields -optionally -enclosed -by ="

mysqldump -u root -pPassword -xml -X dbname table >D:\back.xml;

 mysqldump -u root -pPassword --html -H -e "select 语句" dbname >D:/back.html

加锁解锁

lock tables test read;
unlock tables;


explain select * from cards;
desc select * from cards;