sql
mysql install:
mutian@mutian:~$ sudo apt-get install mysql-server mutian@mutian:~$ sudo apt-get install mysql-client mutian@mutian:~$ sudo apt-get install libmysqlclient-dev
mutian@mutian:~/soft$ mysql -u root -p mysql> exit; mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | now | | performance_schema | | test | | ultrax | | x3 | +--------------------+ 7 rows in set (0.00 sec) mysql> create database f1; //创建f1数据库 mysql> show databases; mysql> drop database f1; //删除f1数据库 mysql> use now; //进入某个数据库 mysql> show tables; // 查看指定数据库的所有表 mysql> CREATE TABLE users ( uid int(6) NOT NULL auto_increment, username varchar(20) NOT NULL UNIQUE, password varchar(20) NOT NULL, email varchar(20) NOT NULL, score int(), PRIMARY KEY (uid) ); // uid作为主键 约束username // UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。 mysql> show tables; +---------------+ | Tables_in_now | +---------------+ | Persons | | users | +---------------+ 2 rows in set (0.00 sec) mysql> show columns from users; //查询表的字段名 +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | uid | int(6) | NO | PRI | NULL | auto_increment | | username | varchar(20) | NO | UNI | NULL | | | password | varchar(20) | NO | | NULL | | | email | varchar(20) | NO | | NULL | | | score | int(3) | YES | | NULL | | +----------+-------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec) mysql> insert into users (username, password, email, score) values ("admin", "admin", "admin@126.com", 10); Query OK, 1 row affected (0.03 sec) mysql> select * from users; +-----+----------+----------+-----------------+-------+ | uid | username | password | email | score | +-----+----------+----------+-----------------+-------+ | 1 | admin | admin | admin@126.com | 10 | | 3 | muhe221 | 123456 | muhe221@126.com | 100 | | 4 | muhe222 | 123 | muhe221@126.com | 96 | | 5 | muhe223 | 456789 | muhe223@126.com | 85 | +-----+----------+----------+-----------------+-------+ mysql> select username, password from users where email="muhe221@126.com"; +----------+----------+ | username | password | +----------+----------+ | muhe221 | 123456 | | muhe222 | 123 | +----------+----------+ 2 rows in set (0.00 sec) mysql> select distinct email from users; +-----------------+ | email | +-----------------+ | admin@126.com | | muhe221@126.com | | muhe223@126.com | +-----------------+ mysql> select * from users where score between 80 and 99; +-----+----------+----------+-----------------+-------+ | uid | username | password | email | score | +-----+----------+----------+-----------------+-------+ | 4 | muhe222 | 123 | muhe221@126.com | 96 | | 5 | muhe223 | 456789 | muhe223@126.com | 85 | +-----+----------+----------+-----------------+-------+ 2 rows in set (0.00 sec) mysql> select username from users where email like 'muhe%'; +----------+ | username | +----------+ | muhe221 | | muhe222 | | muhe223 | +----------+ 3 rows in set (0.00 sec) mysql> select username, score from users where score >= 80; +----------+-------+ | username | score | +----------+-------+ | muhe221 | 100 | | muhe222 | 96 | | muhe223 | 85 | +----------+-------+ 3 rows in set (0.00 sec) mysql> select * from users where email like "muhe2_3@126.com"; +-----+----------+----------+-----------------+-------+ | uid | username | password | email | score | +-----+----------+----------+-----------------+-------+ | 5 | muhe223 | 456789 | muhe223@126.com | 85 | +-----+----------+----------+-----------------+-------+ 1 row in set (0.00 sec) mysql> select * from users where email like "muhe2%"; +-----+----------+----------+-----------------+-------+ | uid | username | password | email | score | +-----+----------+----------+-----------------+-------+ | 3 | muhe221 | 123456 | muhe221@126.com | 100 | | 4 | muhe222 | 123 | muhe221@126.com | 96 | | 5 | muhe223 | 456789 | muhe223@126.com | 85 | +-----+----------+----------+-----------------+-------+ //like 后面的字符串中 _匹配单个字符 %匹配字符串(可为空) mysql> select * from users; +-----+----------+----------+-----------------+-------+ | uid | username | password | email | score | +-----+----------+----------+-----------------+-------+ | 1 | admin | admin | admin@126.com | 10 | | 4 | muhe222 | 123 | muhe221@126.com | 96 | | 5 | muhe223 | 456789 | muhe223@126.com | 85 | | 6 | muhe221 | 123456 | muhe221@126.com | 80 | +-----+----------+----------+-----------------+-------+ mysql> select * from users where score > 0 order by score limit 2; +-----+----------+----------+-----------------+-------+ | uid | username | password | email | score | +-----+----------+----------+-----------------+-------+ | 1 | admin | admin | admin@126.com | 10 | | 6 | muhe221 | 123456 | muhe221@126.com | 80 | +-----+----------+----------+-----------------+-------+ mysql> select * from users where score > 0 order by score desc limit 2; +-----+----------+----------+-----------------+-------+ | uid | username | password | email | score | +-----+----------+----------+-----------------+-------+ | 4 | muhe222 | 123 | muhe221@126.com | 96 | | 5 | muhe223 | 456789 | muhe223@126.com | 85 | +-----+----------+----------+-----------------+-------+ mysql> select * from users where username not between 'muhe221' and 'muhe223'; +-----+----------+----------+---------------+-------+ | uid | username | password | email | score | +-----+----------+----------+---------------+-------+ | 1 | admin | admin | admin@126.com | 10 | +-----+----------+----------+---------------+-------+ mysql> delete from users where username = "muhe221"; mysql> update users set email = "muhe221@126.com", score = 80 where username = "muhe221";
如现在数据库管理员决定将limit子句与order by子句一起使用。数据库一旦找到了排序结果的第一个rowcount行，则系统将会结束排序，而并不会对整个表进行排序。如果单独使用order by子句的话，则会对整个表进行排序。虽然如此，但是排序必定要浪费一定的时间。此时数据库管理员如果决定使用索引，则可以在很大程度上提高这个查询的效 率。
mysql> show create table user;
+-------+--------------------------------------------------
| Table | Create Table
+-------+--------------------------------------------------
| user | CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(32) NOT NULL COMMENT '用户名称',
`birthday` date DEFAULT NULL COMMENT '生日',
`sex` char(1) DEFAULT NULL COMMENT '性别',
`address` varchar(256) DEFAULT NULL COMMENT '地址',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------
1 row in set (0.00 sec)
zerofill
自动补零啊，比如你的类型是INT(4),你插入了3，数据库里保存的是0003
Primary Key和Unique Key的区别
简单的说，primary key = unique + not null
unique 就是唯一，当你需要限定你的某个表字段每个值都唯一,没有重复值时使用。比如说,如果你有一个person 表，并且表中有个身份证的column，那么你就可以指定该字段为unique。 从技术的角度来看，Primary Key和Unique Key有很多相似之处。但还是有以下区别：
一、作为Primary Key的域/域组不能为null，而Unique Key可以。
二、在一个表中只能有一个Primary Key，而多个Unique Key可以同时存在。
更大的区别在逻辑设计上。Primary Key一般在逻辑设计中用作记录标识，这也是设置Primary Key的本来用意，而Unique Key只是为了保证域/域组的唯一性。
oracle的constraint中有两种约束，都是对列的唯一性限制――unique与primary key，但其中是有区别的：
1、unique key要求列唯一，但不包括null字段，也就是约束的列可以为空且仅要求列中的值除null之外不重复即可；
2、primary key也要求列唯一，同时又限制字段的值不能为null，相当于Primary Key=unique + not null。
创建一个primary key和unique key都会相应的创建一个unique index。
PRI UNI MUL
1. 如果Key是空的, 那么该列值的可以重复, 表示该列没有索引, 或者是一个非唯一的复合索引的非前导列
2. 如果Key是PRI, 那么该列是主键的组成部分
3. 如果Key是UNI, 那么该列是一个唯一值索引的第一列(前导列),并别不能含有空值(NULL)
4. 如果Key是MUL, 那么该列的值可以重复, 该列是一个非唯一索引的前导列(第一列)或者是一个唯一性索引的组成部分但是可以含有空值NULL
C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot
mysql> \. c:\data\db_affairmanage.sql
C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot < C:\data\db_affairmanage.s
数据库查看工具：
HeidiSQL_9.3.0.4984_Setup
Navicat for mySQL
Host 'localhost' is not allowed to connect to this MySQL server..."
解决方案：删除默认数据文件
mysql安装时默认数据库路径： C:\Documents and Settings\All Users\MySQL\MySQL Server 5.5
导入sql数据库
mysql> source d:/db_affairmanage.sql
或者 >\. d:/db_affairmanage.sql
ERROR 1366 (HY000): Incorrect string value: '\xF8????<...' for column 'criticismContent' at row 1
汉字编码问题:
可以在sql文件中最开始的地方加上set character set gb2312;
导入数据库乱码问题
登陆的时候指定字符格式
mysql -uroot -p --default-character-set=gbk
use dbname
source /root/newsdata.sql
或
mysql -uroot -p --default-character-set=utf8
use dbname
source /root/newsdata.sql
show variables like '%char%';
mysql的cmd默认编码是GBK，而mysql默认使用的是utf-8 所以你可以设置cmd为gbk;
CREATE TABLE `orders` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `user_id` INT(11) NOT NULL COMMENT '下单用户id', `number` VARCHAR(32) NOT NULL COMMENT '订单号', `createtime` DATETIME NOT NULL COMMENT '创建订单时间', `note` VARCHAR(100) NULL DEFAULT NULL COMMENT '备注', PRIMARY KEY (`id`), INDEX `FK_orders_1` (`user_id`), CONSTRAINT `FK_orders_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION ) COLLATE='utf8_general_ci' ENGINE=InnoDB AUTO_INCREMENT=6 ;
CONSTRAINT `FK_orders_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
约束FK_orders_id 外键user_id引用user中的id
ON DELETE NO ACTION 指定如果试图删除某一行，而该行的键被其他表的现有行中的外键所引用，则产生错误并回滚 DELETE 语句。
Grant
hadoop@muhe221:~$ mysql -u root -p Your MySQL connection id is 3 Server version: 5.7.25-0ubuntu0.16.04.2 (Ubuntu) mysql> select user, host from mysql.user; +------------------+-----------+ | user | host | +------------------+-----------+ | hive | % | | debian-sys-maint | localhost | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +------------------+-----------+ 5 rows in set (0.00 sec) mysql> drop user hive; Query OK, 0 rows affected (0.00 sec) mysql> select user, host from mysql.user; +------------------+-----------+ | user | host | +------------------+-----------+ | debian-sys-maint | localhost | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +------------------+-----------+ 4 rows in set (0.00 sec) mysql> create user hive; Query OK, 0 rows affected (0.00 sec) mysql> select user, host from mysql.user; +------------------+-----------+ | user | host | +------------------+-----------+ | hive | % | | debian-sys-maint | localhost | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +------------------+-----------+ 5 rows in set (0.00 sec) mysql> show grants; +---------------------------------------------------------------------+ | Grants for root@localhost | +---------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | +---------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> show grants for 'hive'@'%'; +----------------------------------+ | Grants for hive@% | +----------------------------------+ | GRANT USAGE ON *.* TO 'hive'@'%' | #USAGE “无权限”的同义词 +----------------------------------+ 1 row in set (0.00 sec) mysql> GRANT ALL ON *.* TO 'hive'@'%'; Query OK, 0 rows affected (0.00 sec) mysql> show grants for 'hive'@'%'; +-------------------------------------------+ | Grants for hive@% | +-------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'hive'@'%' | +-------------------------------------------+ 1 row in set (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.01 sec)
mysql> create user 'user'@'%' identified by 'user_passwod'
修改密码：
mysql> update mysql.user set authentication_string=password('654321') where user='root';
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
浙公网安备 33010602011771号