mysql知识点
8、用文本方式将数据装入数据库表中(例如D:/mysql.txt)
mysql> LOAD DATA LOCAL INFILE “D:/mysql.txt” INTO TABLE MYTABLE;
9、导入.sql文件命令(例如D:/mysql.sql)
mysql>use database;
mysql>source d:/mysql.sql;
1.MySQL基本命令
DROP TABLE IF EXISTS teacher1,teacher2,teacher3;
DESC teacher3;
DESCRIBE teacher3;
SHOW COLUMNS FROM db;
SHOW FULL COLUMNS FROM db;
SHOW CREATE TABLE db;
SHOW DATABASES;
USE test;
SHOW TABLES LIKE 'teacher%';
SHOW TABLE STATUS FROM mysql WHERE NAME = 'db';
SHOW TABLE STATUS LIKE 'db';
SHOW FULL PROCESSLIST;
-- 变量操作
SHOW ENGINES;
SHOW VARIABLES LIKE '%storage_engine%';
SHOW VARIABLES LIKE '%engine%';
SHOW VARIABLES LIKE '%long_query%';
SHOW VARIABLES LIKE '%query%';
SHOW VARIABLES LIKE '%long_query%';
SET autocommit=1;
-- 复制表(结构和数据)
2. mysql 函数
-- 系统函数 7 个
SELECT DATABASE(),VERSION(),CURRENT_USER(),SYSTEM_USER(),CONNECTION_ID(),LAST_INSERT_ID(),BENCHMARK(100,'20*20'),
FOUND_ROWS()
-- 数学函数 10个
SELECT RAND(),CEILING(12.5),FLOOR(12.5),TRUNCATE(12.3,1),ROUND(45.1),POWER(2,3),MOD(10,2),ABS(-12),GREATEST(12,11,2),LEAST(12,11,10);
SELECT PI();
-- mysql 字符函数
SELECT UPPER('xyz'),LOWER('XYZ'),UCASE('x'),LCASE('Y'),RIGHT('abcdefg',2),LEFT('abcedefg',2),TRIM(LEADING 'x' FROM 'xxAAx'),TRIM(TRAILING 'x' FROM 'AAx')
SELECT TRIM('x' FROM 'xBBBx'),REPEAT('A',12),SPACE(12),REVERSE('abcdefg'),LENGTH('abcdefg'),CONCAT('ab','cd'),CONCAT_WS('_','aa','cc')
SELECT SUBSTR('abcdefg',2),SUBSTRING('abcdefg',2,2),LENGTH('abcd'),REPLACE('abcdef','a','A'),STRCMP('abc','abd');
SELECT LOCATE('de','abcdefg'),FIND_IN_SET('22','123,23,22,44'),INSTR('abcdefg',''),POSITION('de' IN 'abcdefg')
-- 条件函数（三个条件函数 if() ifnull(),case when then ）（参照 https://baijiahao.baidu.com/s?id=1652889669198489176&wfr=spider&for=pc）
SELECT IF(2019>2018,TRUE,FALSE), IF(2019>2018,FALSE,TRUE), IF(2019>2018,'对','你的数学是体育老师教的吗'),IF(STRCMP('ab','cd'),'yes',22)
SELECT IFNULL(NULL,NULL),IFNULL(0,1)
SELECT IF(NULL,'first','second'),IF(0,'first','second'),IF(FALSE,'first','second'),IF('0','first','second'),
IF('null','first','second') (条件表达式 会自动将 '0','false'等转为为boolean 型)
SELECT CASE WHEN DAYOFMONTH(NOW())=1 THEN '星期天'
WHEN DAYOFMONTH(NOW())=2 THEN '星期一'
WHEN DAYOFWEEK(NOW())=2 THEN '星期二'
WHEN DAYOFWEEK(NOW())=3 THEN '星期三'
ELSE '非上述值' END AS 'week';
SELECT CASE DAYOFMONTH(NOW())
WHEN 1 THEN '星期天'
WHEN 2 THEN '星期一'
WHEN 3 THEN '星期二'
ELSE '非上述值' END AS 'week';
-- 时间函数
SELECT NOW(),SYSDATE() ,CURRENT_DATE(),CURRENT_TIME(),CURDATE(),CURTIME(),CURRENT_TIMESTAMP(),CURRENT_USER()
SELECT YEAR(NOW()),MONTH(NOW()),DAY(NOW()),WEEK(NOW()),HOUR(NOW()),MINUTE(NOW()),SECOND(NOW())
SELECT UTC_DATE(),UTC_TIME(),UTC_TIMESTAMP(),LOCALTIME(),LOCALTIMESTAMP()
3.建表语句
CREATE TABLE if not exists `db` (
`Host` CHAR(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`Db` CHAR(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` CHAR(16) COLLATE utf8_bin NOT NULL DEFAULT '',
`Select_priv` ENUM('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Insert_priv` ENUM('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Update_priv` ENUM('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Delete_priv` ENUM('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_priv` ENUM('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Drop_priv` ENUM('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Grant_priv` ENUM('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`References_priv` ENUM('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Index_priv` ENUM('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_priv` ENUM('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_tmp_table_priv` ENUM('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Lock_tables_priv` ENUM('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_view_priv` ENUM('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Show_view_priv` ENUM('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_routine_priv` ENUM('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_routine_priv` ENUM('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Execute_priv` ENUM('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
PRIMARY KEY (`Host`,`Db`,`User`),
KEY `User` (`User`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Database privileges'
CREATE TABLE IF NOT EXISTS teacher1(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(12) COLLATE utf8_bin COMMENT '姓名' NOT NULL,
sex TINYINT COMMENT '性别（1 男/ 0女）' NOT NULL
)ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT '教师表'
